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

SDYTXM發表於2012-09-21

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

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/，如需轉載，請註明出處，否則將追究法律責任。

RAC資料庫大量載入資料造成歸檔日誌空間滿處理
2013-12-22
資料庫
當ORACLE歸檔日誌滿後如何正確刪除歸檔日誌
2017-11-17
Oracle
歸檔日誌滿導致的資料庫掛起故障處理
2011-10-06
資料庫
Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌
2020-10-12
資料庫
oracle資料庫歸檔日誌空間滿引起的錯誤處理
2013-11-30
Oracle資料庫
歸檔日誌無法歸檔導致資料庫hang住
2010-04-20
資料庫
oracle資料庫歸檔日誌量陡增分析
2021-06-21
Oracle資料庫
歸檔日誌滿導致的資料庫掛起故障處理【轉載】
2016-07-29
資料庫
Oracle資料庫的歸檔日誌寫滿磁碟空間解決辦法
2009-02-19
Oracle資料庫
ORA-00257歸檔日誌滿
2010-12-13
將資料庫轉換為歸檔日誌模式
2011-10-18
資料庫模式
Oracle資料庫重做日誌及歸檔日誌的工作原理說明
2017-10-20
Oracle資料庫
Oracle叢集資料庫中恢復歸檔日誌
2011-10-08
Oracle資料庫
oracle歸檔切換以及歸檔日誌滿報錯問題
2014-03-31
Oracle
丟失已歸檔日誌檔案下恢復資料庫
2016-11-28
資料庫
聯機重做日誌、歸檔日誌、備用重做日誌
2007-12-25
一次oracle資料庫down事件（歸檔量較大，造成ASM空間滿）
2014-11-12
Oracle資料庫事件ASM
oracle歸檔日誌丟失後的資料庫恢復
2012-07-17
Oracle資料庫
將RAC資料庫更改為不歸檔日誌的模式
2007-12-25
資料庫模式
因歸檔日誌無法歸檔造成的 oracle shutdown immediate無法關閉
2009-11-21
Oracle
資料檔案重建，從歸檔日誌中回退資料
2010-08-20
rman清除歸檔日誌經典資料
2014-04-04
無歸檔日誌恢復rman資料
2010-07-06
歸檔日誌
2016-11-14
【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘
2018-12-18
Oracle
ARCHIVE歸檔日誌爆滿故障ORA-00257
2015-06-23
Hive
ORA-00257歸檔日誌空間已滿
2010-02-26
如果資料庫日誌已滿，怎麼辦?
2007-03-02
資料庫
兩個日誌組未能歸檔之後恢復資料庫
2017-02-07
資料庫
Oracle資料庫恢復：歸檔日誌損壞案例一則
2015-04-13
Oracle資料庫
達夢資料庫DM8之刪除歸檔日誌檔案
2022-05-06
資料庫
對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法
2019-07-20
模式資料庫
RMAN備份資料檔案+控制檔案+歸檔日誌
2015-01-28
logminer異機挖掘歸檔日誌
2022-10-09
oracle歸檔日誌
2021-04-02
Oracle
Oracle 歸檔日誌
2013-12-05
Oracle
歸檔日誌挖掘
2024-02-29
建立資料庫檔案-日誌檔案-次要資料庫檔案
2017-04-01
資料庫

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

相關文章