归档日志过大导致数据库连接失败,解决方法有三种
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.
三、更换归档日志路径
见http://blog.itpub.net/29618264/viewspace-2139879/
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/29618264/viewspace-2140729/
,如需转载,请注明出处,否则将追究法律责任。