天萃荷净

运维DBA反映数据库出现宕机，alert日志报错ORA-19815，分析原因为归档日志满导致日志无法归档，提出fast_recovery_area无剩余空间解决办法

一、打开数据库报错ORA-03113

[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

二、ORA-19815错误信息

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

+274

----- 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

五、删除Oracle归档的补充说明

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处理垃圾数据

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-19815 fast_recovery_area无剩余空间解决办法