ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 手动删除归档日志-故障重现

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

原创 Linux操作系统 作者:kaapa 时间:2012-06-07 20:40:54 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-06-07

  • 博文量
    2
  • 访问量
    970