以下是操作记录:
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 8 14:06:45 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate -- 长时间没有反应
alert.log 日志记录:
************************************************************************
ARC0: Failed to archive thread 1 sequence 77 (19809)
^[:Fri Apr 8 14:19:49 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc1_311352.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:49 2011
************************************************************************
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.
************************************************************************
ARC1: Failed to archive thread 1 sequence 77 (19809)
Fri Apr 8 14:19:49 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc0_356450.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:49 2011
************************************************************************
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.
************************************************************************
ARC0: Failed to archive thread 1 sequence 77 (19809)
Fri Apr 8 14:19:49 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc1_311352.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:49 2011
************************************************************************
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.
************************************************************************
ARC1: Failed to archive thread 1 sequence 77 (19809)
Fri Apr 8 14:19:49 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc0_356450.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:49 2011
************************************************************************
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.
************************************************************************
ARC0: Failed to archive thread 1 sequence 77 (19809)
Fri Apr 8 14:19:50 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc1_311352.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:50 2011
************************************************************************
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.
************************************************************************
ARC1: Failed to archive thread 1 sequence 77 (19809)
Fri Apr 8 14:19:50 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc0_356450.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Apr 8 14:19:50 2011
************************************************************************
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.
************************************************************************
ARC0: Failed to archive thread 1 sequence 77 (19809)
在另外的会话以SYSDBA登陆,利用SHUTDOWN ABORT可以关闭数据库。
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 8 14:23:28 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down
SQL> startup
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed Size 2027776 bytes
Variable Size 1124077312 bytes
Database Buffers 3892314112 bytes
Redo Buffers 14745600 bytes
Database mounted.
ORA-16014: log 1 sequence# 77 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/orainst/oradata/enovia/redo01.log'.
这里报错。
alert.log 日志记录:
Fri Apr 8 14:26:28 2011
Errors in file /orainst/admin/enovia/bdump/enovia_arc0_397376.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43423744 bytes disk space from 2147483648 limit
ARC0: Error 19809 Creating archive log file to '/orainst/flash_recovery_area/ENOVIA/archivelog/2011_04_08/o1_mf_1_77_10_.arc'
ARC0: Failed to archive thread 1 sequence 77 (19809)
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 77 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
解决方法:
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /orainst/flash_recovery_area
db_recovery_file_dest_size big integer 2G
检查flash recovery area的使用情况:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 98.48 0 66
BACKUPPIECE 1.03 0 4
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.9853
可以看到,这里已经有2.9853G使用了,这说明我们刚开始设置的
db_recovery_file_dest_size=2G不足,导致online redo log无法归档,在这里,我们可以通过设置db_recovery_file_dest_size参数,增大了flash recovery area来解决这个问题。也可以通过删除flash recovery area中不必要的备份来释放flash recovery area空间来解决这个问题。
首先:让我们删除不必要的备份来释放空间:
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 31 13-DEC-10
Backup Piece 36 13-DEC-10 /orainst/product/10/backup/ctl_c-3150239988-20101213-00
Backup Set 32 13-DEC-10
Backup Piece 37 13-DEC-10 /orainst/product/10/backup/ctl_c-3150239988-20101213-01
Backup Set 33 13-DEC-10
Backup Piece 38 13-DEC-10 /orainst/product/10/backup/ctl_c-3150239988-20101213-02
Backup Set 34 17-DEC-10
Backup Piece 39 17-DEC-10 /orainst/product/10/backup/ctl_c-3150239988-20101217-00
Backup Set 35 24-MAR-11
Backup Piece 40 24-MAR-11 /orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746662630_6rr29pqd_.bkp
Backup Set 36 24-MAR-11
Backup Piece 41 24-MAR-11 /orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746663851_6rr3hv6v_.bkp
Backup Set 37 24-MAR-11
Backup Piece 42 24-MAR-11 /orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746665173_6rr4s60o_.bkp
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746662630_6rr29pqd_.bkp recid=40 stamp=746662630
deleted backup piece
backup piece handle=/orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746663851_6rr3hv6v_.bkp recid=41 stamp=746663851
deleted backup piece
backup piece handle=/orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_24/o1_mf_n_746665173_6rr4s60o_.bkp recid=42 stamp=746665174
Deleted 3 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: Backup Piece /orainst/product/10/backup/ctl_c-3150239988-20101213-00
RMAN-06214: Backup Piece /orainst/product/10/backup/ctl_c-3150239988-20101213-01
RMAN-06214: Backup Piece /orainst/product/10/backup/ctl_c-3150239988-20101213-02
RMAN-06214: Backup Piece /orainst/product/10/backup/ctl_c-3150239988-20101217-00
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-00 recid=36 stamp=737632392
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-01 recid=37 stamp=737640162
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-02 recid=38 stamp=737641221
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101217-00 recid=39 stamp=737976289
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/orainst/flash_recovery_area/ENOVIA/autobackup/2011_03_25/o1_mf_s_746706614_6rsf8638_.bkp recid=43 stamp=746706614
Crosschecked 5 objects
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 31 1 1 EXPIRED DISK /orainst/product/10/backup/ctl_c-3150239988-20101213-00
37 32 1 1 EXPIRED DISK /orainst/product/10/backup/ctl_c-3150239988-20101213-01
38 33 1 1 EXPIRED DISK /orainst/product/10/backup/ctl_c-3150239988-20101213-02
39 34 1 1 EXPIRED DISK /orainst/product/10/backup/ctl_c-3150239988-20101217-00
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-00 recid=36 stamp=737632392
deleted backup piece
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-01 recid=37 stamp=737640162
deleted backup piece
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101213-02 recid=38 stamp=737641221
deleted backup piece
backup piece handle=/orainst/product/10/backup/ctl_c-3150239988-20101217-00 recid=39 stamp=737976289
Deleted 4 EXPIRED objects
删除不必要备份后重启:
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed Size 2027776 bytes
Variable Size 1124077312 bytes
Database Buffers 3892314112 bytes
Redo Buffers 14745600 bytes
Database mounted.
ORA-16038: log 1 sequence# 77 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/orainst/oradata/enovia/redo01.log'
仍然报错,我很无赖!
选择增大db_recovery_file_dest_size:
SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.
SQL> alter database open;
Database altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /orainst/flash_recovery_area
db_recovery_file_dest_size big integer 5G
再次检查flash recovery area的使用情况:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 41.82 0 69
BACKUPPIECE .1 0 1
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
再次计算flash recovery area已经占用的空间:
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
1.2576
到此,问题解决。