리커버리 데스트 사이즈 확인 및 수정
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_14$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 15 00:04:29 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 4G
SQL> alter system set db_recovery_file_dest_size=1G scope=both ;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 1G
SQL>
insert(dml 수행)
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_14$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 14 23:56:00 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
SQL> conn scott/tiger
Connected.
SQL> insert into emp select * from emp ;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> commit ;
Commit complete.
SQL> insert into emp select * from emp ;
229376 rows created.
SQL> /
458752 rows created.
SQL> /
917504 rows created.
SQL> /
1835008 rows created.
SQL> commit ;
Commit complete.
SQL> insert into emp select * from emp ;
3670016 rows created.
SQL> commit ;
Commit complete.
SQL> insert into emp select * from emp ;
7340032 rows created.
에러발생 및 아카이브 full 확인
Thu Jan 15 00:04:52 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_m000_5319.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 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.
************************************************************************
Thu Jan 15 00:05:58 2015
Thread 1 advanced to log sequence 44 (LGWR switch)
Current log# 2 seq# 44 mem# 0: /u01/app/oracle/oradata/orcl1/redo02.log
Thu Jan 15 00:05:59 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_arc2_1811.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 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.
************************************************************************
ARC2: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_43_%u_.arc’
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl1 – Archival Error
ORA-16038: log 1 sequence# 43 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/orcl1/redo01.log’
해결방법
recovery size 증가
SQL> show parameter recovery
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
SQL>
SQL>
SQL> alter system set db_recovery_file_dest_size=5G scope=both ;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 5G
==
************************************************************************
You have following choices to free up space from recovery area:
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_arc3_1812.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 100.00% used, and has 0 remaining bytes available.
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
************************************************************************
then consider changing RMAN ARCHIVELOG DELETION POLICY.
You have following choices to free up space from recovery area:
2. Back up files to tertiary device such as tape using RMAN
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
BACKUP RECOVERY AREA command.
then consider changing RMAN ARCHIVELOG DELETION POLICY.
3. Add disk space and increase db_recovery_file_dest_size parameter to
2. Back up files to tertiary device such as tape using RMAN
reflect the new space.
BACKUP RECOVERY AREA command.
4. Delete unnecessary files using RMAN DELETE command. If an operating
3. Add disk space and increase db_recovery_file_dest_size parameter to
system command was used to delete files, then use RMAN CROSSCHECK and
reflect the new space.
DELETE EXPIRED commands.
4. Delete unnecessary files using RMAN DELETE command. If an operating
************************************************************************
ARC2: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_43_%u_.arc’
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC3: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_44_%u_.arc’
Thu Jan 15 00:16:39 2015
ALTER SYSTEM SET db_recovery_file_dest_size=’5G’ SCOPE=BOTH;
Thu Jan 15 00:16:44 2015
Archived Log entry 39 added for thread 1 sequence 43 ID 0x4a516e3d dest 1:
Archiver process freed from errors. No longer stopped
Thu Jan 15 00:16:45 2015
Thread 1 advanced to log sequence 46 (LGWR switch)
Current log# 1 seq# 46 mem# 0: /u01/app/oracle/oradata/orcl1/redo01.log
Thu Jan 15 00:16:45 2015
Archived Log entry 40 added for thread 1 sequence 44 ID 0x4a516e3d dest 1:
Thu Jan 15 00:16:45 2015
db_recovery_file_dest_size of 5120 MB is 26.55% 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.
Thu Jan 15 00:16:47 2015
Archived Log entry 41 added for thread 1 sequence 45 ID 0x4a516e3d dest 1:
os rm
SQL> alter system set db_recovery_file_dest_size=1536M scope=both ;
System altered.
ALTER SYSTEM SET db_recovery_file_dest_size=’1536M’ SCOPE=BOTH;
Thu Jan 15 01:06:59 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_m000_5422.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1610612736 bytes is 89.36% used, and has 171366400 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.
************************************************************************
## insert 재 수행
SQL> insert into dept select * from dept ;
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> insert into dept select * from dept ;
4194304 rows created.
SQL> commit ;
Commit complete.
## 장애 발생
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_m000_5422.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1610612736 bytes is 89.36% used, and has 171366400 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.
************************************************************************
Thu Jan 15 01:11:16 2015
Thread 1 advanced to log sequence 50 (LGWR switch)
Current log# 2 seq# 50 mem# 0: /u01/app/oracle/oradata/orcl1/redo02.log
////
Thu Jan 15 01:35:33 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_arc2_1811.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1610612736 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.
************************************************************************
ARC2: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_57_%u_.arc’
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl1 – Archival Error
ORA-16038: log 3 sequence# 57 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/orcl1/redo03.log’
## 사이즈 확인
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ pwd
/u01/app/oracle/fast_recovery_area/ORCL1/archivelog
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ ls -la
total 30
drwxr-x— 10 oracle dba 10 1월 15일 00:00 .
drwxr-x— 4 oracle dba 4 10월 31일 22:00 ..
drwxr-x— 2 oracle dba 3 10월 31일 22:00 2014_10_31
drwxr-x— 2 oracle dba 4 11월 1일 20:00 2014_11_01
drwxr-x— 2 oracle dba 4 11월 2일 16:00 2014_11_02
drwxr-x— 2 oracle dba 4 11월 3일 22:00 2014_11_03
drwxr-x— 2 oracle dba 5 12월 2일 05:00 2014_12_02
drwxr-x— 2 oracle dba 5 1월 13일 20:10 2015_01_13
drwxr-x— 2 oracle dba 16 1월 14일 23:59 2015_01_14
drwxr-x— 2 oracle dba 27 1월 15일 01:35 2015_01_15
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ du -sm
1534 .
## os 아카이브 삭제
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ ls -ltr
total 24
drwxr-x— 2 oracle dba 3 10월 31일 22:00 2014_10_31
drwxr-x— 2 oracle dba 4 11월 1일 20:00 2014_11_01
drwxr-x— 2 oracle dba 4 11월 2일 16:00 2014_11_02
drwxr-x— 2 oracle dba 4 11월 3일 22:00 2014_11_03
drwxr-x— 2 oracle dba 5 12월 2일 05:00 2014_12_02
drwxr-x— 2 oracle dba 5 1월 13일 20:10 2015_01_13
drwxr-x— 2 oracle dba 16 1월 14일 23:59 2015_01_14
drwxr-x— 2 oracle dba 27 1월 15일 01:35 2015_01_15
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ rm -rf 2014*
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ ls -ltr
total 9
drwxr-x— 2 oracle dba 5 1월 13일 20:10 2015_01_13
drwxr-x— 2 oracle dba 16 1월 14일 23:59 2015_01_14
drwxr-x— 2 oracle dba 27 1월 15일 01:35 2015_01_15
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ du -sm
1171 .
## 장애 지속
************************************************************************
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.
************************************************************************
ARC0: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_58_%u_.arc’
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC3: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_57_%u_.arc’
Errors in file /u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_arc2_1811.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1610612736 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.
## RMAN 작업
### crosscheck
oracle@solaris:/u01/app/oracle/fast_recovery_area/ORCL1/archivelog$ rman target /
Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jan 15 01:39:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1 (DBID=1246882109)
RMAN> crosscheck archivelog all ;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
validation failed for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_10_31/o1_mf_1_5_b571z17q_.arc RECID=1 STAMP=862437618
validation failed for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_11_01/o1_mf_1_6_b58d8ob5_.arc RECID=2 STAMP=862480934
////
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_01_15/o1_mf_1_56_bcf6pf6m_.arc RECID=52 STAMP=869016926
Crosschecked 52 objects
### delete archive
RMAN> delete expired archivelog all ;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL1
=====================================================================
Key Thrd Seq S Low Time
——- —- ——- – ———
1 1 5 X 31-OCT-14
Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_10_31/o1_mf_1_5_b571z17q_.arc
2 1 6 X 31-OCT-14
Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_11_01/o1_mf_1_6_b58d8ob5_.arc
///
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_10_31/o1_mf_1_5_b571z17q_.arc RECID=1 STAMP=862437618
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2014_11_01/o1_mf_1_6_b58d8ob5_.arc RECID=2 STAMP=862480934
///
Deleted 10 EXPIRED objects
## 장애 해결
Thu Jan 15 01:40:49 2015
Archived Log entry 54 added for thread 1 sequence 57 ID 0x4a516e3d dest 1:
Thu Jan 15 01:41:13 2015
db_recovery_file_dest_size of 1536 MB is 82.19% 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.
?ALTER SYSTEM SET db_recovery_file_dest_size=’1536M’ SCOPE=BOTH;
By haisins
오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.