oracle丢失active或current日志文件的恢复操作过程

丢失活动或当前日志文件的恢复
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不进行一致性检查,
可能会导致数据库损坏,数据库应当重建.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.