Thursday, 8 December 2011

Number of Archives Generation is too high

Number of Archives Generation is too high.Normally around 40 archives got generated from both nodes.But on Monday i was shocked when i checked the no of archives in a day.Around 700 archives were generated in 10 hrs.

Findings:
select trunc(first_time), count(*)
from v$archived_log
where name not like '%dr'
group by trunc(first_time)
order by 1;

(first_time)   count(*)
---------     ----------
27-NOV-11          2
28-NOV-11         20
29-NOV-11         28
30-NOV-11         14
01-DEC-11         17
02-DEC-11         17
03-DEC-11         18
04-DEC-11        359
05-DEC-11        386

DB DATE       TOTAL  H00 H01 H02 H03 H04 H05 H06 H07 H08 H09 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23
--- ---------- ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
1 2011-12-01     12   0   0   2   0   0   0   0   0   0   2   0   0   0   2   0   2   0   0   0   0   0   0   2   2
2 2011-12-01     22   0   0   2   0   0   0   2   0   0   2   0   4   0   0   2   2   2   0   0   2   0   0   2   2
1 2011-12-02     12   0   0   2   0   0   0   0   0   0   2   0   0   0   2   0   0   0   0   2   0   0   0   2   2
2 2011-12-02     22   0   0   2   0   0   0   2   0   0   2   0   2   0   2   0   0   2   2   2   2   0   0   0   4
1 2011-12-03     10   0   0   0   0   0   0   0   0   0   2   0   0   2   0   0   2   0   0   0   2   0   0   0   2
2 2011-12-03     26   0   0   2   0   0   0   2   0   0   2   2   0   2   2   0   4   2   2   0   2   0   0   2   2
1 2011-12-04    352   0   0   0   0   0   0   0   0   0   2   0   0   0   2  16  36  42  38  44  34  40  32  28  38
2 2011-12-04    366   0   0   2   0   0   0   2   0   0   2   0   2   0   4  16  36  42  38  44  34  40  36  32  36
1 2011-12-05    384  44  42  22  40  38  36  40  32  30  32  28   0   0   0   0   0   0   0   0   0   0   0   0   0
2 2011-12-05    388  44  42  24  40  38  36  40  32  30  32  28   0   2   0   0   0   0   0   0   0   0   0   0   0

1) The no of archives generated were 700 which inturns can cause me space issue also.Hmmm...But few GB's was used by archives.
Then i checked the size of archive files.The size of archives was in KBs and MBs.

2) It means number of log switch is tooo high.

3) Then i checked the alert log which helped me to diagnose the issue.

Error messages in alert log:
Sun Dec 04 19:51:10 2011
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_arc3_2306070.trc:
ORA-12582: TNS:invalid operation
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_arc3_2306070.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance ebsdbprd1 - Archival Error. Archiver continuing.
Sun Dec 04 20:07:13 2011
Errors in file /orahome/app/oracle/diag/rdbms/ebsdbprd/ebsdbprd1/trace/ebsdbprd1_nsa2_1601550.trc:
ORA-00270: error creating archive log


4) It means there is some problem at Standby database.I checked the space at archive destination on standby server.
It was full :( .RMAN backup was failed.I manually deleted old archive files.

No comments:

Post a Comment