oracle歸檔日誌過滿清理

歸檔日誌過大導致資料庫連線失敗，解決方法有三種
1.增大db_recovery_file_dest_size值
2：更換歸檔日誌路徑
3：檢查失效的歸檔日誌檔案，然後刪除（或者刪除N天以前的歸檔日誌）


一.增大db_recovery_file_dest_size值
[oracle@XXXX ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 8 08:18:56 2017


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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     22
Next log sequence to archive   26
Current log sequence           26
SQL> show parameter db_recovery_dest
SQL> show parameter db_recovery


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string    /u01/arch
db_recovery_file_dest_size           big integer 2G

SQL> alter system set db_recovery_file_dest_size=4294967296;                    ---------（4x1024x1024x1024）

System altered.

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string    /u01/arch
db_recovery_file_dest_size           big integer 4G
二、刪除歸檔日誌
[oracle@XXXX ~]$ rman target /


Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 8 08:20:28 2017


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


connected to target database: PROD (DBID=322088611)


RMAN> list archivelog all;


using target database control file instead of recovery catalog


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
9       1    8       A 11-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_8_dk9ht6jf_.arc
10      1    9       A 11-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_9_dk9ht6mk_.arc
11      1    10      A 12-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_10_dk9ht885_.arc
12      1    1       A 12-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_15/o1_mf_1_1_dkk7df0n_.arc
13      1    2       A 15-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_16/o1_mf_1_2_dkmybt18_.arc
14      1    3       A 16-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_22/o1_mf_1_3_dl3t8q9o_.arc
15      1    4       A 22-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_24/o1_mf_1_4_dl8wdxm6_.arc
16      1    5       A 24-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_5_dlcpcjbg_.arc
17      1    6       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_6_dlcq4sj5_.arc
18      1    7       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_7_dlcr1zpt_.arc
19      1    8       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_8_dld2rxo4_.arc
20      1    9       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_9_dld2s0dn_.arc
21      1    10      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_10_dld3j48q_.arc
22      1    11      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_11_dld3j5ol_.arc
23      1    12      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_12_dld3j6v4_.arc
24      1    13      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_13_dld3j7dr_.arc
25      1    14      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_14_dld3jfv7_.arc
26      1    15      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_15_dld3jfy6_.arc
27      1    16      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_16_dld3jg0c_.arc
28      1    17      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_17_dld3jg0o_.arc
29      1    18      A 25-MAY-17 /u01/arch1_18_943760710.dbf
30      1    19      A 26-MAY-17 /u01/arch1_19_943760710.dbf
31      1    20      A 26-MAY-17 /u01/arch1_20_943760710.dbf
32      1    21      A 26-MAY-17 /u01/arch1_21_943760710.dbf
33      1    22      A 31-MAY-17 /u01/arch1_22_943760710.dbf
34      1    23      A 08-JUN-17 /u01/arch/1_23_943760710.dbf
35      1    24      A 08-JUN-17 /u01/arch/1_24_943760710.dbf
36      1    25      A 08-JUN-17 /u01/arch/1_25_943760710.dbf


RMAN> delete archivelog all completed before 'sysdate-1';             
（刪除當前系統時間-1天之前的歸檔日誌）

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
9       1    8       A 11-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_8_dk9ht6jf_.arc
10      1    9       A 11-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_9_dk9ht6mk_.arc
11      1    10      A 12-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_10_dk9ht885_.arc
12      1    1       A 12-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_15/o1_mf_1_1_dkk7df0n_.arc
13      1    2       A 15-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_16/o1_mf_1_2_dkmybt18_.arc
14      1    3       A 16-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_22/o1_mf_1_3_dl3t8q9o_.arc
15      1    4       A 22-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_24/o1_mf_1_4_dl8wdxm6_.arc
16      1    5       A 24-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_5_dlcpcjbg_.arc
17      1    6       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_6_dlcq4sj5_.arc
18      1    7       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_7_dlcr1zpt_.arc
19      1    8       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_8_dld2rxo4_.arc
20      1    9       A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_9_dld2s0dn_.arc
21      1    10      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_10_dld3j48q_.arc
22      1    11      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_11_dld3j5ol_.arc
23      1    12      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_12_dld3j6v4_.arc
24      1    13      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_13_dld3j7dr_.arc
25      1    14      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_14_dld3jfv7_.arc
26      1    15      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_15_dld3jfy6_.arc
27      1    16      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_16_dld3jg0c_.arc
28      1    17      A 25-MAY-17 /u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_17_dld3jg0o_.arc
29      1    18      A 25-MAY-17 /u01/arch1_18_943760710.dbf
30      1    19      A 26-MAY-17 /u01/arch1_19_943760710.dbf
31      1    20      A 26-MAY-17 /u01/arch1_20_943760710.dbf
32      1    21      A 26-MAY-17 /u01/arch1_21_943760710.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_8_dk9ht6jf_.arc recid=9 stamp=943760710
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_9_dk9ht6mk_.arc recid=10 stamp=943760712
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_12/o1_mf_1_10_dk9ht885_.arc recid=11 stamp=943760715
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_15/o1_mf_1_1_dkk7df0n_.arc recid=12 stamp=944014224
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_16/o1_mf_1_2_dkmybt18_.arc recid=13 stamp=944103261
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_22/o1_mf_1_3_dl3t8q9o_.arc recid=14 stamp=944623387
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_24/o1_mf_1_4_dl8wdxm6_.arc recid=15 stamp=944789408
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_5_dlcpcjbg_.arc recid=16 stamp=944881522
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_6_dlcq4sj5_.arc recid=17 stamp=944882331
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_7_dlcr1zpt_.arc recid=18 stamp=944883266
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_8_dld2rxo4_.arc recid=19 stamp=944894238
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_9_dld2s0dn_.arc recid=20 stamp=944894240
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_10_dld3j48q_.arc recid=21 stamp=944894980
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_11_dld3j5ol_.arc recid=22 stamp=944894981
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_12_dld3j6v4_.arc recid=23 stamp=944894982
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_13_dld3j7dr_.arc recid=24 stamp=944894983
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_14_dld3jfv7_.arc recid=25 stamp=944894989
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_15_dld3jfy6_.arc recid=26 stamp=944894989
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_16_dld3jg0c_.arc recid=27 stamp=944894990
deleted archive log
archive log filename=/u01/oracle/app/oracle/flash_recovery_area/PROD/archivelog/2017_05_25/o1_mf_1_17_dld3jg0o_.arc recid=28 stamp=944894990
Deleted 20 objects


RMAN-06207: WARNING: 4 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /u01/arch1_18_943760710.dbf
RMAN-06214: Archivelog      /u01/arch1_19_943760710.dbf
RMAN-06214: Archivelog      /u01/arch1_20_943760710.dbf
RMAN-06214: Archivelog      /u01/arch1_21_943760710.dbf




RMAN> crosscheck archivelog all;


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
validation failed for archived log
archive log filename=/u01/arch1_18_943760710.dbf recid=29 stamp=944960303
validation failed for archived log
archive log filename=/u01/arch1_19_943760710.dbf recid=30 stamp=944969753
validation failed for archived log
archive log filename=/u01/arch1_20_943760710.dbf recid=31 stamp=944969887
validation failed for archived log
archive log filename=/u01/arch1_21_943760710.dbf recid=32 stamp=945401231
validation failed for archived log
archive log filename=/u01/arch1_22_943760710.dbf recid=33 stamp=946109008
validation succeeded for archived log
archive log filename=/u01/arch/1_23_943760710.dbf recid=34 stamp=946109739
validation succeeded for archived log
archive log filename=/u01/arch/1_24_943760710.dbf recid=35 stamp=946109739
validation succeeded for archived log
archive log filename=/u01/arch/1_25_943760710.dbf recid=36 stamp=946109739
Crosschecked 8 objects




RMAN> delete expired archivelog all;


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
29      1    18      X 25-MAY-17 /u01/arch1_18_943760710.dbf
30      1    19      X 26-MAY-17 /u01/arch1_19_943760710.dbf
31      1    20      X 26-MAY-17 /u01/arch1_20_943760710.dbf
32      1    21      X 26-MAY-17 /u01/arch1_21_943760710.dbf
33      1    22      X 31-MAY-17 /u01/arch1_22_943760710.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u01/arch1_18_943760710.dbf recid=29 stamp=944960303
deleted archive log
archive log filename=/u01/arch1_19_943760710.dbf recid=30 stamp=944969753
deleted archive log
archive log filename=/u01/arch1_20_943760710.dbf recid=31 stamp=944969887
deleted archive log
archive log filename=/u01/arch1_21_943760710.dbf recid=32 stamp=945401231
deleted archive log
archive log filename=/u01/arch1_22_943760710.dbf recid=33 stamp=946109008
Deleted 5 EXPIRED objects




RMAN> list archivelog all;




List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
34      1    23      A 08-JUN-17 /u01/arch/1_23_943760710.dbf
35      1    24      A 08-JUN-17 /u01/arch/1_24_943760710.dbf
36      1    25      A 08-JUN-17 /u01/arch/1_25_943760710.dbf


RMAN> delete archivelog from time 'sysdate-7';
（刪除7天內的歸檔日誌）

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
34      1    23      A 08-JUN-17 /u01/arch/1_23_943760710.dbf
35      1    24      A 08-JUN-17 /u01/arch/1_24_943760710.dbf
36      1    25      A 08-JUN-17 /u01/arch/1_25_943760710.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u01/arch/1_23_943760710.dbf recid=34 stamp=946109739
deleted archive log
archive log filename=/u01/arch/1_24_943760710.dbf recid=35 stamp=946109739
deleted archive log
archive log filename=/u01/arch/1_25_943760710.dbf recid=36 stamp=946109739
Deleted 3 objects


RMAN> list archivelog all;


specification does not match any archive log in the recovery catalog


RMAN> exit


Recovery Manager complete.

三、更換歸檔日誌路徑