• 리커버리 데스트 사이즈 확인 및 수정

    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;

         

         

사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기

'DB - ORACLE > Oracle Trouble Shooting' 카테고리의 다른 글

ORADEBUG 사용법 - 3  (0) 2015.12.04
ORADEBUG 사용법 - 2  (0) 2015.12.04
Oracle Linux Huge Page  (0) 2015.11.23
Oracle Hang Manager (Diag Process)  (0) 2015.11.23
Oracle DB Hang 상태  (0) 2015.11.20

+ Recent posts