丢失活动或当前日志文件的恢复
oracle通过日志文件保证提交成功的数据不丢失,可是在故障中,用户可能损失了当前的
(current)日志文件.这又分为两种情况:此时数据是正常关闭的和此时数据库是异常关闭.
1.在损失当前日志时,数据库是正常关闭的.
由于关闭数据库前,oracle会执行全面检查点,当前日志在实例恢复中可以不再需要.
下面进行测试(数据库运行在非归档模式下).在oracle9i及以后版本中,是无法对当前
日志进行clear,需要通过until cancel恢复后再以resetlogs方式打开
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 00:39:59 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE ---------- ------- ------- ----------------------------------------------------------- --------------------- 3 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log NO 2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log NO 1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log NO SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !mv /u01/app/oracle/product/10.2.0/oradata/jingyong/redo* /u01/app/oracle SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 62916852 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log' SQL> alter database clear logfile group 1; Database altered. SQL> alter database clear logfile group 2; Database altered. SQL> alter database clear logfile group 3; alter database clear logfile group 3 * ERROR at line 1: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 0 52428800 1 NO UNUSED 0 06-JAN-13 3 1 0 52428800 1 NO CLEARING_CURRENT 914164 06-JAN-13 2 1 0 52428800 1 NO UNUSED 914157 06-JAN-13 SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.
2.在损失当前日志,数据库是异常关闭的
如果在损失当前日志时,数据库是异常关闭的,那么oracle在进行实例恢复时必须要求当前日志,
否则oracle将无法保证提交成功的数据不丢失(也就是意味着oracle会丢失数据),在这种情况下,
oracle数据库将无法启动.
对于这种情况,通常需要从备份中恢复数据文件,通过应用归档日志文件向前推演,直到最后一个
完好的日志文件,然后可以通过resetlogs启动数据库完成恢复.丢失的数据就是损坏的日志文件
中的数据.
如果没有备份,oracle有一类具有特殊作用的隐含参数,其中一个参数是_allow_resetlogs_corruption,来看一下
这个参数的说明:
SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
2 from x$ksppi x, x$ksppcv y
3 where x.inst_id=USERENV('Instance')
4 and y.inst_id=USERENV('Instance')
5 and x.indx=y.indx
6 and x.ksppinm like '%_allow_resetlogs_%';
NAME VALUE DESCRIB
------------------------------ -------------- --------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
该参数的含义是,允许在破坏一致性的情况下强制重置日志,打开数据库._allow_resetlogs_corruption将
使用所有数据文件中最旧的scn打开数据库,所以通常需要保证system表空间拥有最旧的scn.
在强制打开数据库之后,可能因为各种原因伴随出现ora-00600错误,有些可以依据常规途径解决,看一下下面
的例子:
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 02:04:02 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
查询当前的日志组
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 2 52428800 1 NO CURRENT 936058 07-JAN-13 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO INACTIVE 914918 07-JAN-13 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE ---------- ------- ------- ----------------------------------------------------------- --------------------- 3 STALE ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log NO 2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log NO 1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log NO
删除当前日志组的日志文件
SQL> !rm -rf /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 62916852 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
启动失败,日志文件损坏,在mount状态,可以查询v$log视图,发现此处损坏的是current的日志文件
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 2 52428800 1 NO CURRENT 936058 07-JAN-13 3 1 1 52428800 1 NO INACTIVE 914918 07-JAN-13 2 1 0 52428800 1 YES UNUSED 0
由于active和current日志没有完成检查点，在恢复中需要用到,丢失active和current日志情况类似.
如果没有备份,只好使用隐含参数_allow_resetlogs_corruption强制启动数据库,设置此参数之后,
在数据库open过程中,oracle会跳过某些一致性检查,从而使用权数据库可能跳过不一致状态,直接打开.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 936059 generated at 01/07/2013 02:01:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/flash_recovery_area/JINGYONG/archivelog/2013_01_0
7/o1_mf_1_2_%u_.arc
ORA-00280: change 936059 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
如果运气好的话,数据库就可以成功打开了,如果不幸,则可能会遇到一些ora-00600的错误
那么就需要使用其它方法来进行恢复
通过这种方法恢复可以在alert日志中看见类似以下的日志信息:
Mon Jan 7 02:11:19 2013 alter database open resetlogs Mon Jan 7 02:11:19 2013 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 936059 Resetting resetlogs activation ID 3141718551 (0xbb42d217) Mon Jan 7 02:11:19 2013
不一致恢复最后恢复到的change号是936059,信息中说明了强制resetlogs不进行一致性检查,
可能会导致数据库损坏,数据库应当重建.