基于cancel 的不一致性恢復（歸檔丟失） 第二篇

?? 主要適用于：基于Cancel的不完全恢復適用場景：Recover時，所需的某個歸檔日志損壞，或主機斷電，current狀態的聯機日志損壞。

?

創建測試表

??? 創建wwl002表，切換日志，再創建新的wwl003表，主機斷電，刪除當前日志，模擬文件損壞。

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'

?

? ??恢復之后，使用Resetlogs選項，仍無法打開數據庫，提示數據文件不一致，System表空間需要進一步的恢復。

??? 當前日志損壞時，不能基于當前的數據庫做不完全恢復。只能用以前的備份，做一個基于Cancel的不完全恢復。

?

重建下控制文件：

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.

?

檢查_allow_resetlogs_corruption隱含參數是否關閉，確定已經關閉

SQL> show parameter_allow_resetlogs_corruption

NAME???????????????????????????????? TYPE??????? VALUE

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

_allow_resetlogs_corruption????????? boolean???? FALSE

SQL>

?

因為重建了控制文件，默認是沒有制定temp表空間，這里制定下：

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

-----------------------oracle修復------- -----------------

WWL001???????????????????????? TABLE

WWL002???????????????????????? TABLE

?

可以看到表WWL002存在，表WWL003不存在。因為WWL003的創建，是保存在當前REDO日志中的，而當前REDO日志損壞，所有當前日志中保存的操作全部丟失了。

??? 歸檔日志、或者REDO日志損失，數據庫就只能恢復到丟失的日志之前了。

oracle修復