探索Oracle不完全恢复之--基于cancel的恢复第二篇

探索Oracle不完全恢复之--基于cancel的恢复 第二篇

基于cancel 的不一致性恢复（归档丢失） 第二篇

创建测试表

SQL> conn wwl/wwl

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

WWL001 TABLE

SQL> create table WWL002 as select *from wwl001;

Table created.

SQL> conn / as sysdba

Connected.

切换日志

SQL> alter system switch logfile;

System altered.

后再创建第二张表

SQL> conn wwl/wwl

Connected.

SQL> create table wwl003 as select *from wwl001;

Table created.

查看当前日志组，，确定当前活动的日志组，是组4

SQL> conn / as sysdba

Connected.

SQL> set line 200

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------

4 1 2 134217728 2 YES ACTIVE 1716929 30-JUL-12

5 1 1 134217728 2 YES INACTIVE 1692728 27-JUL-12

6 1 3 134217728 2 NO CURRENT 1720396 30-JUL-12

7 1 0 134217728 2 YES UNUSED 0

定位当前日志组的日志文件，有两个。

SQL> col member format a30

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------------------------------------- ---

7 ONLINE /DBBak2/oradata/WWL/redo7a.log NO

7 ONLINE /DBBak2/oradata/WWL/redo7b.log NO

6 ONLINE /DBBak2/oradata/WWL/redo6a.log NO

6 ONLINE /DBBak2/oradata/WWL/redo6b.log NO

5 STALE ONLINE /DBBak2/oradata/WWL/redo5a.log NO

5 STALE ONLINE /DBBak2/oradata/WWL/redo5b.log NO

4 ONLINE /DBBak2/oradata/WWL/redo4a.log NO

4 ONLINE /DBBak2/oradata/WWL/redo4b.log NO

8 rows selected.

删除当前日志组文件，模拟在线事务丢失：

SQL> !rm -f/DBBak2/oradata/WWL/redo4a.log

SQL> !rm -f/DBBak2/oradata/WWL/redo4b.log

模拟服务器断电

SQL> shutdown abort;

Oracle instance shut down.

恢复步骤:

1、尝试启动数据库的时候报当前日志丢失。

SQL> startup

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-00313: open failedfor members of log group 4 of thread 1

ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4b.log'

ORA-27037: unable toobtain file status

Linux Error: 2: No suchfile or directory

Additional information: 3

ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4a.log'

ORA-27037: unable toobtain file status

Linux Error: 2: No suchfile or directory

Additional information: 3

2、尝试Clear redo4

SQL> alter database clear logfile group4;

alter database clear logfile group 4

*

ERROR at line 1:

ORA-01624: log 4 needed for crash recoveryof instance WWL (thread 1)

ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4a.log'

ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4b.log'

3、在当前库做基于Cancel的不完全恢复

SQL> recover database until cancel;

ORA-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf

ORA-00280: change 1716930 for thread 1 isin sequence #2

Specify log: {=suggested |filename | AUTO | CANCEL}

auto

ORA-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf

ORA-00280: change 1720396 for thread 1 isin sequence #3

ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf' no longer needed forthis recovery

ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'

做完恢复之后必须使用resetlogs选项打开数据库：

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'

重建下控制文件：

SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

'/DBBak2/oradata/WWL/redo4a.log',

MAXDATAFILES 100

MAXINSTANCES 8

GROUP 5 (

MAXLOGHISTORY 292

LOGFILE

GROUP 4 (

'/DBBak2/oradata/WWL/redo4a.log',

'/DBBak2/oradata/WWL/redo4b.log'

GROUP 6 (

)SIZE 128M,

GROUP 5 (

'/DBBak2/oradata/WWL/redo5a.log',

'/DBBak2/oradata/WWL/redo5b.log'

'/DBBak2/oradata/WWL/redo7b.log'

)SIZE 128M,

GROUP 6 (

'/DBBak2/oradata/WWL/redo6a.log',

'/DBBak2/oradata/WWL/redo6b.log'

)SIZE 128M,

GROUP 7 (

'/DBBak2/oradata/WWL/redo7a.log',

'/DBBak2/oradata/WWL/redo7b.log'

)SIZE 128M

-- STANDBY LOGFILE

DATAFILE

'/DBBak2/oradata/WWL/system01.dbf',

'/DBBak2/oradata/WWL/undotbs01.dbf',

'/DBBak2/oradata/WWL/sysaux01.dbf',

'/DBBak2/oradata/WWL/users01.dbf',

'/DBBak2/oradata/WWL/wwl001',

'/DBBak2/oradata/WWL/wwl002',

'/DBBak2/oradata/WWL/wwl003'

CHARACTER SET ZHS16CGB231280

34 ;

Control file created.

再次打开，结果还是不行

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'

可以尝试使用_allow_resetlogs_corruption隐含参数来打开数据库

SQL> alter system set"_allow_resetlogs_corruption"=true scope=spfile;

System altered.

修改完参数之后重启数据库到mount状态

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

同样以resetlogs模式启动数据库

SQL> alter database open resetlogs;

Database altered.

一定记得关闭该参数

SQL> alter system set"_allow_resetlogs_corruption"=FALSE scope=spfile;

System altered.

让参数关闭生效，再次启动数据库

SQL> startup force;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter_allow_resetlogs_corruption

NAME TYPE VALUE

----------------------------------------------- ------------------------------

_allow_resetlogs_corruption boolean FALSE

SQL>

SQL> ALTER TABLESPACE TEMP01 ADDTEMPFILE '/DBBak2/oradata/WWL/temp01.dbf' REUSE;

Tablespace altered.

检查数据

SQL> conn wwl/wwl

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

WWL001 TABLE

WWL002 TABLE

相关阅读：

探索Oracle之RMAN_01概念

探索Oracle之RMAN_02基本使用

探索Oracle之RMAN_03非一致性备份

探索Oracle之RMAN_04非一致性备份

探索Oracle之RMAN_05增量备份

探索Oracle之RMAN_06备份策略

探索Oracle之RMAN_07单个数据文件丢失恢复

探索Oracle之RMAN_07整个业务表空间丢失恢复

探索Oracle之RMAN_07 磁盘损坏数据丢失恢复

探索Oracle之RMAN_07 数据库所有文件全部丢失恢复

探索Oracle之RMAN_07 重做日志redu文件丢失恢复

探索Oracle之RMAN_07 参数文件丢失恢复

探索Oracle之RMAN_07控制文件丢失恢复

探索Oracle之RMAN_07 system表空间丢失恢复