一、打開資料庫報錯

 

[oracle@node1 ora11g]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 15:42:04 2011

 

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

 

Connected to an idle instance.

 

SQL> startup nomount

 

ORACLE instance started.

 

Total System Global Area 2137886720 bytes

 

Fixed Size 2230072 bytes

 

Variable Size 1493174472 bytes

 

Database Buffers 637534208 bytes

 

Redo Buffers 4947968 bytes

 

SQL> alter database mount;

 

Database altered.

 

SQL> alter database open;

 

alter database open

 

*

 

ERROR at line 1:

 

ORA-03113: end-of-file on communication channel

 

Process ID: 19489

 

Session ID: 96 Serial number: 1

 

二、錯誤資訊

 

1.alert日誌內容

 

Mon Dec 19 15:23:29 2011

 

alter database open

 

Beginning crash recovery of 1 threads

 

parallel recovery started with 7 processes

 

Started redo scan

 

Completed redo scan

 

read 0 KB redo, 0 data blocks need recovery

 

Started redo application at

 

Thread 1: logseq 3, block 93169, scn 12899730

 

Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0

 

Mem# 0: /opt/oracle/oradata/ora11g/redo03.log

 

Completed redo application of 0.00MB

 

Completed crash recovery at

 

Thread 1: logseq 3, block 93169, scn 12919731

 

0 data blocks read, 0 data blocks written, 0 redo k-bytes read

 

Mon Dec 19 15:23:29 2011

 

LGWR: STARTING ARCH PROCESSES

 

Mon Dec 19 15:23:29 2011

 

ARC0 started with pid=27, OS id=19539

 

ARC0: Archival started

 

LGWR: STARTING ARCH PROCESSES COMPLETE

 

ARC0: STARTING ARCH PROCESSES

 

Mon Dec 19 15:23:30 2011

 

ARC1 started with pid=28, OS id=19548

 

Mon Dec 19 15:23:30 2011

 

ARC2 started with pid=29, OS id=19550

 

Mon Dec 19 15:23:30 2011

 

ARC3 started with pid=30, OS id=19552

 

ARC1: Archival started

 

ARC2: Archival started

 

ARC2: Becoming the 'no FAL' ARCH

 

ARC2: Becoming the 'no SRL' ARCH

 

ARC1: Becoming the heartbeat ARCH

 

Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_arc2_19550.trc:

 

ORA-19815: 警告: db_recovery_file_dest_size 位元組 (共 4322230272 位元組) 已使用 100.00%, 尚有 0 位元組可用。

 

************************************************************************

 

You have following choices to free up space from recovery area:

 

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

 

then consider changing RMAN ARCHIVELOG DELETION POLICY.

 

2. Back up files to tertiary device such as tape using RMAN

 

BACKUP RECOVERY AREA command.

 

3. Add disk space and increase db_recovery_file_dest_size parameter to

 

reflect the new space.

 

4. Delete unnecessary files using RMAN DELETE command. If an operating

 

system command was used to delete files, then use RMAN CROSSCHECK and

 

DELETE EXPIRED commands.

 

************************************************************************

 

ARC2: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_%u_.arc'

 

Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:

 

ORA-19815: WARNING: db_recovery_file_dest_size of 4322230272 bytes is 100.00% used, and has 0 remaining bytes available.

 

************************************************************************

 

You have following choices to free up space from recovery area:

 

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

 

then consider changing RMAN ARCHIVELOG DELETION POLICY.

 

2. Back up files to tertiary device such as tape using RMAN

 

BACKUP RECOVERY AREA command.

 

3. Add disk space and increase db_recovery_file_dest_size parameter to

 

reflect the new space.

 

4. Delete unnecessary files using RMAN DELETE command. If an operating

 

system command was used to delete files, then use RMAN CROSSCHECK and

 

DELETE EXPIRED commands.

 

************************************************************************

 

ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'

 

ARCH: Archival stopped, error occurred. Will continue retrying

 

ORACLE Instance ora11g - Archival Error

 

ORA-16038: 日誌 2 sequence# 2 無法歸檔

 

ORA-19809: 超出了恢復檔數的限制

 

ORA-00312: 連線日誌 2 執行緒 1: '/opt/oracle/oradata/ora11g/redo02.log'

 

Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:

 

ORA-16038: log 1 sequence# 1 cannot be archived

 

ORA-19809: limit exceeded for recovery files

 

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'

 

USER (ospid: 19489): terminating the instance due to error 16038

 

Mon Dec 19 15:23:31 2011

 

System state dump requested by (instance=1, osid=19489), summary=[abnormal instance termination].

 

System State dumped to trace file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_19463.trc

 

Dumping diagnostic data in directory=[cdmp_20111219152331], requested by (instance=1, osid=19489), summary=[abnormal instance termination].

 

Instance terminated by USER, pid = 19489

 

2.trace檔資訊(ora11g_ora_19489.trc)

 

2011-12-19 15:23:31.026 4320 krsh.c

 

ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'

2011-12-19 15:23:31.026 2932 krsi.c

krsi_dst_fail: dest:1 err:19809 force:0 blast:1

DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'

ORA-16038: log 1 sequence# 1 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'

kjzduptcctx: Notifying DIAG for crash event

----- Abridged Call Stack Trace -----

ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1332<-kcfopd()+5962<-adbdrv()+51834<-opiexe()+18384<-opiosq0()+3870<-kpooprx()

+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570

<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252

----- End of Abridged Call Stack Trace -----

這個錯誤很明顯:因為資料庫歸檔日誌放置在fast_recovery_area中,而空間已滿,導致連線日誌sequence# 2不能被歸檔,資料庫無法打開



三、修改db_recovery_file_dest_size,打開資料庫

SQL> Alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> alter database open;

Database altered.

設置較大db_recovery_file_dest_size,先打開資料庫,再解決問題(減少down機時間是dba一大準則)



四、刪除歷史歸檔日誌

從sequence# 為2中很明顯看出來,資料庫進行了resetlogs打開,所以前面的歸檔日誌,在原則上已經無效(不再使用原始備份組恢復),因為資料庫的控制檔中,無原歸檔日誌資訊,所以無法使用rman刪除歸檔日誌。那只能使用os命令先刪除掉歷史歸檔日誌,然後再使用rman處理

[root@node1 archivelog]# find ./ -mtime +1| xargs rm -rf

[root@node1 archivelog]# ll

總計 72

drwxr-x--- 2 oracle oinstall 4096 12-18 22:35 2011_12_18

drwxr-x--- 2 oracle oinstall 4096 12-19 13:25 2011_12_19

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_10_31/o1_mf_1_5_7bxbhkof_.arc RECID=1 STAMP=766015219

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_11_01/o1_mf_1_6_7bxw2gpo_.arc RECID=2 STAMP=766033231

……………………………………

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_105_7gc3co97_.arc RECID=132 STAMP=770306728

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc RECID=123 STAMP=770306728

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc RECID=127 STAMP=770306728

validation succeeded for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_7gxtrlnq_.arc RECID=134 STAMP=770312597

validation succeeded for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_7gxtrloz_.arc RECID=135 STAMP=770312597

validation succeeded for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_3_7gxtrodg_.arc RECID=136 STAMP=770312599

validation failed for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc RECID=126 STAMP=770306728

……………………………………

validation succeeded for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_20_7gxlq29k_.arc RECID=113 STAMP=770306728

validation succeeded for archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_21_7gxl3zdm_.arc RECID=114 STAMP=770306728

Crosschecked 136 objects

RMAN> DELETE EXPIRED archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

List of Archived Log Copies for database with db_unique_name ORA11G

……………………………………

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log

……………………………………

deleted archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_16/o1_mf_1_14_7gpood3n_.arc RECID=115 STAMP=770306728

deleted archived log

archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_17/o1_mf_1_15_7gqhvvhh_.arc RECID=106 STAMP=770306727

Deleted 124 EXPIRED objects

五、補充說明

1.使用os命令刪除fast_recovery_area內容後,需要使用crosscheck檢測(如:archivelog all,backup等)。

2.然後使用 DELETE EXPIRED命令刪除(archivelog all,backup等)

3.fast_recovery_area設置合適大小+合適的策略

4.resetlogs打開資料庫後,做好備份

5.fast_recovery_area無剩餘空間處理思路

5.1)如果資料庫不能登錄:重啟至mount,增大fast_recovery_area,open資料庫,然後使用rman刪除歷史垃圾資料(備份組,日誌,閃回日誌等)

5.2)如果資料庫可以使用sys登錄,增大fast_recovery_area(使其資料庫可以正常工作),然後使用rman處理垃圾資料

 

創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()