配置db_recovery_file_dest_size文件大小

扩大闪回恢复区db_recovery_file_dest_size大小

一、概述

今天数据库发生报错，大概意思是db_recovery_file_dest_size达到上限，被占满。所以就需要扩大flash_recovery_area的大小。

二、错误信息：

Fri Jul 11 02:11:24 2014

Errors in file /orainst/admin/cba/bdump/cba_arc0_123080.trc:

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

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:12:24 2014

ARCH: Archival stopped, error occurred. Will continue retrying

Fri Jul 11 02:12:24 2014

ORACLE Instance cba - Archival Error

Fri Jul 11 02:12:24 2014

ORA-16014: log 1 sequence# 18214 not archived, no available destinations

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:12:24 2014

Errors in file /orainst/admin/cba/bdump/cba_arc1_356430.trc:

ORA-16014: log 1 sequence# 18214 not archived, no available destinations

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:17:24 2014

Errors in file /orainst/admin/cba/bdump/cba_arc0_123080.trc:

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

Fri Jul 11 02:17:24 2014

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

You have following choices to free up space from flash 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: Archival stopped, error occurred. Will continue retrying

Fri Jul 11 02:17:24 2014

ORACLE Instance cba - Archival Error

Fri Jul 11 02:17:24 2014

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

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:17:24 2014

Errors in file /orainst/admin/cba/bdump/cba_arc0_123080.trc:

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

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:18:24 2014

ARCH: Archival stopped, error occurred. Will continue retrying

Fri Jul 11 02:18:24 2014

ORACLE Instance cba - Archival Error

Fri Jul 11 02:18:24 2014

ORA-16014: log 1 sequence# 18214 not archived, no available destinations

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

Fri Jul 11 02:18:24 2014

Errors in file /orainst/admin/cba/bdump/cba_arc1_356430.trc:

ORA-16014: log 1 sequence# 18214 not archived, no available destinations

ORA-00312: online log 1 thread 1: '/oradata/cba/redo01.log'

三、操作步骤：

3.1 查看当前recovery的限制大小
SQL> show parameter db_recovery_file_dest_size;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 6G

3.2 查看recovery的实际大小：
SQL> select * from v$recovery_file_dest;


NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/orainst/flash_recovery_area
 6442450944 6400745472                 0             147

3.3 切换到相应目录
# cd /orainst/flash_recovery_area/CBA/
3.4 查看目录下的所有文件大小：
# du -g 
0.00    ./archivelog/2014_06_29
0.00    ./archivelog/2014_06_30
0.00    ./archivelog/2014_07_01
0.00    ./archivelog/2014_07_02
0.00    ./archivelog/2014_07_03
0.00    ./archivelog/2014_07_04
1.71    ./archivelog/2014_07_05
0.24    ./archivelog/2014_07_06
0.65    ./archivelog/2014_07_07
0.81    ./archivelog/2014_07_08
1.05    ./archivelog/2014_07_09
1.46    ./archivelog/2014_07_10
0.04    ./archivelog/2014_07_11
5.96    ./archivelog
0.00    ./onlinelog
5.96    .
3.5 修改recovery大小：
SQL> alter system set db_recovery_file_dest_size=10G;


System altered.

3.6 再查看一遍recovery大小：
SQL> show parameter db_recovery_file_dest_size;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G
SQL> 
3.7 生效。
3.8 最后查看一遍日志：
# tail -100 alert_cba.log
Fri Jul 11 03:13:05 2014
Starting ORACLE instance (normal)
Fri Jul 11 03:13:52 2014
ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Fri Jul 11 03:14:11 2014
alter database open
Fri Jul 11 03:14:11 2014
ORA-1531 signalled during: alter database open...
Fri Jul 11 03:16:15 2014
db_recovery_file_dest_size of 10240 MB is 60.02% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Fri Jul 11 03:16:16 2014
Thread 1 advanced to log sequence 18217 (LGWR switch)
  Current log# 1 seq# 18217 mem# 0: /oradata/cba/redo01.log
Fri Jul 11 03:18:17 2014
Thread 1 advanced to log sequence 18218 (LGWR switch)
  Current log# 2 seq# 18218 mem# 0: /oradata/cba/redo02.log

3.9 证明配置生效。
3.10 重新用客户端连接一下数据库。
3.11 大功告成。