歸檔日誌滿造成資料庫當機

SYS@EMREP>startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             130025040 bytes
Database Buffers          150994944 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-16038: log 1 sequence# 61 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/product/10.2.0/EMREP/redo01.log'

SYS@EMREP>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G
SYS@EMREP>archive  log  list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     61
Next log sequence to archive   61
Current log sequence           63
SYS@EMREP>shutdown  abort
ORACLE instance shut down.
SYS@EMREP>startup  mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             130025040 bytes
Database Buffers          150994944 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@EMREP>alter  system set  db_recovery_file_dest_size=30g  scope=both;

System altered.

SYS@EMREP>select *from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                19.57                         0              41
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

SYS@EMREP>select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
                        .5871

SYS@EMREP>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
[oracle@secdb2 ~]$ rman  target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 21 20:53:10 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: EMREP (DBID=4017259525, not open)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_20_85fhs0gq_.arc recid=1 stamp=794239906
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_21_85fhtnls_.arc recid=2 stamp=794239957
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_22_85fhvpm7_.arc recid=3 stamp=794239992
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_23_85fhx1d1_.arc recid=4 stamp=794240036
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_24_85fhyq15_.arc recid=5 stamp=794240088
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_25_85fj2kv2_.arc recid=6 stamp=794240211
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_26_85fj507h_.arc recid=7 stamp=794240289
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_27_85fnxzs7_.arc recid=8 stamp=794245185
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_28_85fvt7gv_.arc recid=9 stamp=794252234
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_29_85g50cxt_.arc recid=10 stamp=794261646
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_30_85gbrrk3_.arc recid=11 stamp=794267547
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_17/o1_mf_1_31_85gcbbfl_.arc recid=12 stamp=794268107
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_32_85gl88gj_.arc recid=13 stamp=794275210
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_33_85gw5360_.arc recid=14 stamp=794285348
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_34_85h79yrt_.arc recid=15 stamp=794296769
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_35_85hlqlnq_.arc recid=16 stamp=794308468
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_36_85hy7hyh_.arc recid=17 stamp=794320241
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_37_85j9lvvx_.arc recid=18 stamp=794331869
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_38_85jkp51n_.arc recid=19 stamp=794340167
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_39_85jx8c80_.arc recid=20 stamp=794352012
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_18/o1_mf_1_40_85k28mnm_.arc recid=21 stamp=794357141
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_41_85kbwjh9_.arc recid=22 stamp=794365970
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_42_85knp9c7_.arc recid=23 stamp=794376010
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_43_85l02m97_.arc recid=24 stamp=794387668
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_44_85lcj7mq_.arc recid=25 stamp=794399369
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_45_85lq97s4_.arc recid=26 stamp=794411433
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_46_85m2chcb_.arc recid=27 stamp=794422769
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_47_85mb6cdn_.arc recid=28 stamp=794430797
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_48_85mmzgvx_.arc recid=29 stamp=794440816
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_19/o1_mf_1_49_85mtpch2_.arc recid=30 stamp=794447693
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_50_85n2qgtn_.arc recid=31 stamp=794455919
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_51_85nffzfd_.arc recid=32 stamp=794466880
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_52_85nppkvb_.arc recid=33 stamp=794476371
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_53_85o1cxv0_.arc recid=34 stamp=794487295
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_54_85odt99q_.arc recid=35 stamp=794499018
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_55_85or6psv_.arc recid=36 stamp=794510680
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_56_85p1906g_.arc recid=37 stamp=794519969
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_20/o1_mf_1_57_85p8dj9l_.arc recid=38 stamp=794527249
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_21/o1_mf_1_58_85phd7gc_.arc recid=39 stamp=794534410
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_21/o1_mf_1_59_85pr46p3_.arc recid=40 stamp=794543368
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/EMREP/archivelog/2012_09_21/o1_mf_1_60_85q2oqf4_.arc recid=41 stamp=794554169
Crosschecked 41 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found

RMAN> crosscheck backupset;

using channel ORA_DISK_1

RMAN> delete expired backupset;

using channel ORA_DISK_1

SYS@EMREP>alter database open;

Database altered.

向Secooler 致敬！
Johnson
2012-9-21

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20985606/viewspace-744775/，如需轉載，請註明出處，否則將追究法律責任。