1. 环境
window7 Oracle10G
2. 背景
因为自己手动删除备份文件,导致数据启动失败。最基本原因还是手动删除，数据库的相关记录并没有更新。导致文件已经没有了,但是
Oracle还是报空间已满
3. 故障代码
|
ORA-19815: ??: db_recovery_file_dest_size ?? (? 2147483648 ??) ??? 100.00%, ?? 0 ?????
Thu Jun 07 20:07:36 2012 ************************************************************************ You have following choices to free up space from flash recovery area: ARC1 started with pid=19, OS id=11124 Thu Jun 07 20:07:36 2012 Errors in file d:\oracle\oracle\admin\orcl\bdump\orcl_arc0_5280.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, Thu Jun 07 20:07:36 2012 ************************************************************************ Thu Jun 07 20:07:36 2012 ARC1: Becoming the heartbeat ARCH then consider changing RMAN ARCHIVELOG DELETION POLICY. You have following choices to free up space from flash recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, 2. Back up files to tertiary device such as tape using RMAN then consider changing RMAN ARCHIVELOG DELETION POLICY. BACKUP RECOVERY AREA command. 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 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating 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 Jun 07 20:07:36 2012 Errors in file d:\oracle\oracle\admin\orcl\bdump\orcl_arc0_5280.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 47709184 bytes disk space from 2147483648 limit
Thu Jun 07 20:07:36 2012 ARC0: Error 19809 Creating archive log file to 'D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_06_07\O1_MF_1_129_U_.ARC' system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************ ARC0: Failed to archive thread 1 sequence 129 (19809) Thu Jun 07 20:07:37 2012 Errors in file d:\oracle\oracle\admin\orcl\udump\orcl_ora_12448.trc: ORA-19809: ??????????? ORA-19804: ???? 47713280 ?????? (? 2147483648 ???)
ARCH: Archival stopped, error occurred. Will continue retrying Thu Jun 07 20:07:37 2012 ARCH: Error 19809 Creating archive log file to 'D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_06_07\O1_MF_1_128_U_.ARC' Thu Jun 07 20:07:37 2012 Errors in file d:\oracle\oracle\admin\orcl\bdump\orcl_arc0_5280.trc: ORA-16038: log 2 sequence# 129 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\ORCL\REDO02.LOG'
ARCH: Failed to archive thread 1 sequence 128 (19809) ORA-16038 signalled during: alter database open...
|
4. 故障重现
(1) 原来的闪回区空间查询
|
sys_155@ORCL> 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 0 0 0 BACKUPPIECE 0 0 0 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
6 rows selected.
|
(2) 进行RMAN一级备份和加数据之后的空间
|
idle> 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 2.22 0 1 BACKUPPIECE 97.46 0 9 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
6 rows selected. |
(3) 试图手动删除BACKUPPIECE空间文件
(4) 删除之后空间还是占用，说明手工删除出现问题
|
idle> 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 2.22 0 1 BACKUPPIECE 97.46 0 9 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
6 rows selected.
|
(5) 使用RMAN删除和检查
|
RMAN> delete obsolete;
RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 using channel ORA_DISK_1 no obsolete backups found
RMAN> crosscheck backup;
using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND0_TAG20120607T141352_7X0KO2D2_.BKP recid=30 stamp=785340834 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T141806_7X0KX0RQ_.BKP recid=32 stamp=785341088 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T141857_7X0KYMMF_.BKP recid=34 stamp=785341139 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T141941_7X0KZZN1_.BKP recid=36 stamp=785341183 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T142110_7X0L2S0T_.BKP recid=38 stamp=785341272 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T142254_7X0L60CF_.BKP recid=40 stamp=785341376 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NCSN1_TAG20120607T142254_7X0L68F3_.BKP recid=41 stamp=785341384 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T155549_7X0QN8BB_.BKP recid=42 stamp=785346952 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND1_TAG20120607T155656_7X0QP9OQ_.BKP recid=43 stamp=785347017 Crosschecked 9 objects
|
(6) 单独对手工的文件删除
|
RMAN> delete backupset 30;
using channel ORA_DISK_1
List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 30 30 1 1 EXPIRED DISK D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND0_TAG20120607T141352_7X0KO2D2_.BKP
Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=D:\ORACLE\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2012_06_07\O1_MF_NNND0_TAG20120607T141352_7X0KO2D2_.BKP recid=30 stamp=785340834 Deleted 1 objects
|
(7) 查询删除的空间大小
|
idle> 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 6.67 0 3 BACKUPPIECE .95 0 8 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
6 rows selected. |
感谢的人: liu_qingwei2008和快乐的大个子没有他们，这个问题我很难解决和发现。这是我第一次通过网友一起解决问题。虽然是小问题。但是由衷感谢他们。
结论:每个对数据库操作都要注意,特别是删东西。像RMAN要想到有自己的管理，而不是手动删除。通过这次血的教训,也换来对数据库的理解。像OCP考试。RMAN可以备份啥？从V$FLASH_RECOVERY_AREA_USAGE就可以看出来了。而不是死记硬背。死记硬背的OCP永远都不是好的COP管理员。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27038270/viewspace-732192/，如需转载，请注明出处，否则将追究法律责任。
