oracle数据介质恢复,搞懂ORACLE数据库介质恢复(二)

搞懂介质恢复-使用备份的控制文恢复数据库[@more@]

数据文件user01.dbf丢失，控制文件丢失

--restore controlfile;

--restore datafile 4;

--目前数据库为mount状态：

查看相关SCN:

--备份控制文件记录的CN

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

5277885

--备份控制文件记录的SCN

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 5277930

2 5277930

3 5277930

4 5277930

5 5277930

6 5277930

7 5277930

8 5277930

--当前数据库数据文件的SCN

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

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

1 5278823

2 5278823

3 5278823

4 5277930

5 5278823

6 5278823

7 5278823

8 5278823

--备份控制文件记录的redo SCN

SQL> select checkpoint_change# from v$thread;

CHECKPOINT_CHANGE#

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

5277885

--备份控制文件记录的联机重做日志的文件：

SQL> select * from v$log;

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

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

1 1 4 52428800 1 YES INACTIVE 5277883 08-DEC-12

3 1 3 52428800 1 YES INACTIVE 5277875 08-DEC-12

2 1 5 52428800 1 NO CURRENT 5277885 08-DEC-12

上述的SCN,很显然，备份控制文件记录的SCN比数据文件的SCN要旧。

--恢复数据库

SQL> recover database using backup controlfile;

ORA-00279: change 5277930 generated at 12/08/2012 21:44:58 needed for thread 1

ORA-00289: suggestion : /archivelog/1_5_801524425.dbf

ORA-00280: change 5277930 for thread 1 is in sequence #5

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

ORA-00279: change 5278265 generated at 12/08/2012 21:54:42 needed for thread 1

ORA-00289: suggestion : /archivelog/1_6_801524425.dbf

ORA-00280: change 5278265 for thread 1 is in sequence #6

ORA-00278: log file '/archivelog/1_5_801524425.dbf' no longer needed for this

recovery

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

ORA-00279: change 5278267 generated at 12/08/2012 21:54:43 needed for thread 1

ORA-00289: suggestion : /archivelog/1_7_801524425.dbf

ORA-00280: change 5278267 for thread 1 is in sequence #7

ORA-00278: log file '/archivelog/1_6_801524425.dbf' no longer needed for this

recovery

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

ORA-00279: change 5278271 generated at 12/08/2012 21:54:50 needed for thread 1

ORA-00289: suggestion : /archivelog/1_8_801524425.dbf

ORA-00280: change 5278271 for thread 1 is in sequence #8

ORA-00278: log file '/archivelog/1_7_801524425.dbf' no longer needed for this

recovery

......

......

ORA-00279: change 5278817 generated at 12/08/2012 22:00:49 needed for thread 1

ORA-00289: suggestion : /archivelog/1_35_801524425.dbf

ORA-00280: change 5278817 for thread 1 is in sequence #35

ORA-00278: log file '/archivelog/1_34_801524425.dbf' no longer needed for this

recovery

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

ORA-00279: change 5278819 generated at 12/08/2012 22:00:52 needed for thread 1

ORA-00289: suggestion : /archivelog/1_36_801524425.dbf

ORA-00280: change 5278819 for thread 1 is in sequence #36

ORA-00278: log file '/archivelog/1_35_801524425.dbf' no longer needed for this

recovery

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

ORA-00279: change 5278821 generated at 12/08/2012 22:00:53 needed for thread 1

ORA-00289: suggestion : /archivelog/1_37_801524425.dbf

ORA-00280: change 5278821 for thread 1 is in sequence #37

ORA-00278: log file '/archivelog/1_36_801524425.dbf' no longer needed for this

recovery

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

ORA-00279: change 5278823 generated at 12/08/2012 22:00:55 needed for thread 1

ORA-00289: suggestion : /archivelog/1_38_801524425.dbf

ORA-00280: change 5278823 for thread 1 is in sequence #38

ORA-00278: log file '/archivelog/1_37_801524425.dbf' no longer needed for this

recovery

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

ORA-00308: cannot open archived log '/archivelog/1_38_801524425.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

最后显示

ORA-00308: cannot open archived log '/archivelog/1_38_801524425.dbf'

ORA-27037: unable to obtain file status

说明sequence为38是状态为CURRENT的联机重做日志文件.

--转储最新的日志文件：

SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo01.log';

System altered.

descrip:"Thread 0001, Seq# 0000000037, SCN 0x000000508c65-0x000000508c67"

Low scn: 0x0000.00508c65 (5278821) 12/08/2012 22:00:53

Next scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55

SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo02.log';

System altered.

descrip:"Thread 0001, Seq# 0000000038, SCN 0x000000508c67-0xffffffffffff"

Low scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo03.log';

System altered.

descrip:"Thread 0001, Seq# 0000000036, SCN 0x000000508c63-0x000000508c65"

Low scn: 0x0000.00508c63 (5278819) 12/08/2012 22:00:52

Next scn: 0x0000.00508c65 (5278821) 12/08/2012 22:00:53

从Low scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55看，

sequence为38日志文件，对应的联机重做日志文件为：/u01/app/oracle/oradata/dbwdn/redo02.log

记录的SCN 5278823就是数据库要恢复的最后的SCN.这个SCN恢复之后，数据库就可以打开，数据没有

任何丢失，因为应用全部的归档日志，应用全部的联机重做日志。

尝试不应用/u01/app/oracle/oradata/dbwdn/redo02.log，能否打开数据库？

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 1: '/u01/app/oracle/oradata/dbwdn/system01.dbf'

打开不开数据库

--下面进行应用/u01/app/oracle/oradata/dbwdn/redo02.log，因为备份控制文件并没有该日志信息，故需要手工进入即可：

SQL> recover database using backup controlfile;

ORA-00279: change 5278823 generated at 12/08/2012 22:00:55 needed for thread 1

ORA-00289: suggestion : /archivelog/1_38_801524425.dbf

ORA-00280: change 5278823 for thread 1 is in sequence #38

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

/u01/app/oracle/oradata/dbwdn/redo02.log

Log applied.

Media recovery complete.

--成功打开数据库

SQL> alter database open resetlogs;

Database altered.

因为应用了全部的归档日志，全部的联机重做日志，虽然已resetlogs打开数据库，但是没有丢失任何数据。

--立刻全备数据库。