手动删除归档日志-故障重现

最新推荐文章于 2024-06-26 19:47:04 发布

congzanshan3398

最新推荐文章于 2024-06-26 19:47:04 发布

阅读量115

点赞数

文章标签：数据库

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

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 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