Archive log generation script - Hourly / Daily

The below script will provide you the daily archive log generation in GB.

Set lines 132
Column date format a20
Column "Size in GB" format 50
select to_char(completion_time, 'DD-MON-YY') "Date", sum(blocks*block_size/(1024*1024/1024)) "Size in GB", count(*) "     No of Archives"
from v$archived_log
group by to_char(completion_time, 'DD-MON-YY')
order by to_date(to_char(completion_time, 'DD-MON-YY'));

For getting the hourly generation of archivelogs, the below mentioned script will be helpful.

set pagesize 50000

set veri off

set colsep ""

set termout off

def time="time"                    -- Oracle7

col time new_value time

select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time

from   dual

-- where  &&_O_RELEASE like '8%' or   &&_O_RELEASE like '9%'   -- Oracle8

-- for 10g where  &&_O_RELEASE like '8%' or   &&_O_RELEASE like '9%'   -- Oracle8

/

Column "day" format a20

column " 00" format a4

column " 01" format a4

column " 02" format a4

column " 03" format a4

column " 04" format a4

column " 05" format a4

column " 06" format a4

column " 07" format a4

column " 08" format a4

column " 09" format a4

column " 10" format a4

column " 11" format a4

column " 12" format a4

column " 13" format a4

column " 14" format a4

column " 15" format a4

column " 16" format a4

column " 17" format a4

column " 18" format a4

column " 19" format a4

column " 20" format a4

column " 21" format a4

column " 22" format a4

column " 23" format a4

set termout on

select substr(&&time, 1, 5) day,

       to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'999') " 00",

       to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'999') " 01",

       to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'999') " 02",

       to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'999') " 03",

       to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'999') " 04",

       to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'999') " 05",

       to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'999') " 06",

       to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'999') " 07",

       to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'999') " 08",

       to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'999') " 09",

       to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'999') " 10",

       to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'999') " 11",

       to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'999') " 12",

       to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'999') " 13",

       to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'999') " 14",

       to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'999') " 15",

       to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'999') " 16",

       to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'999') " 17",

       to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'999') " 18",

       to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'999') " 19",

       to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'999') " 20",

       to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'999') " 21",

       to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'999') " 22",

       to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'999') " 23"

from   sys.v_$log_history

where  substr(&&time,4,2)=substr(to_char(sysdate,'dd/mm/yy'),4,2)

group  by substr(&&time,1,5)

/

Not all the contents are defined/ derived by myself.

I have consolidated/added accordingly for ease of access.

ORA-609 : opiodr aborting process unknown ospid

Cause:

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection

process before the server process was completely spawned.

Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.

This is also triggered, when a DB session is killed/aborted manually from the OS prompt.

Solution:

Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.

If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

For more details -- refer to metalink note [ID 1121357.1]

All the contents are not defined/ derived by myself.

I have consolidated/added accordingly for ease of access.