数据库热备份后的新库恢复。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            579501

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            579501
            579501
            579501
            579501
            579501

SQL> select checkpoint_change#,last_change#,online_change#,offline_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE# ONLINE_CHANGE# OFFLINE_CHANGE# CHECKPOIN
------------------ ------------ -------------- --------------- ---------
            579501                      579500          579499 10-NOV-09
            579501                      579500          579499 10-NOV-09
            579501                      579500          579499 10-NOV-09
            579501                      579500          579499 10-NOV-09
            579501                      579500          579499 10-NOV-09

SQL> select * from v$log;    --查询日志文件中，组3中的FIRST_CHANGE# 号是579500

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1          0   52428800          1 YES UNUSED
            0

         3          1          1   52428800          1 NO  CURRENT
       579500 10-NOV-09

         2          1          0   52428800          1 YES UNUSED
            0

--开始恢复，用DIGOAL的REDO日志文件。
SQL> recover database using backup controlfile;
ORA-00279: change 579501 generated at 11/10/2009 04:30:43 needed for thread 1
ORA-00289: suggestion : /u01/archive/digoal/1_1_702534642.dbf
ORA-00280: change 579501 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/digoal/redo01.log
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u01/oradata/digoal/redo01.log'

SQL> recover database using backup controlfile;
ORA-00279: change 579501 generated at 11/10/2009 04:30:43 needed for thread 1
ORA-00289: suggestion : /u01/archive/digoal/1_1_702534642.dbf
ORA-00280: change 579501 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/digoal/redo02.log
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u01/oradata/digoal/redo02.log'

SQL> recover database using backup controlfile;
ORA-00279: change 579501 generated at 11/10/2009 04:30:43 needed for thread 1
ORA-00289: suggestion : /u01/archive/digoal/1_1_702534642.dbf
ORA-00280: change 579501 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/digoal/redo03.log
Log applied.
Media recovery complete.                    ---应用到REDO03.LOG的时候，提示恢复完成
SQL> alter database open resetlogs;         --但OPEN 的时候，还是提示错误了。
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit    --退出重新登录
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@myoracle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 05:33:40 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
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> select * from v$log;     --查看SCN号已经变化。

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1          0   52428800          1 YES UNUSED
            0

         3          1          1   52428800          1 NO  CURRENT
       579504 10-NOV-09

         2          1          0   52428800          1 YES UNUSED
            0

---查看以下3个文件的SCN号已经变化成579505。

SQL> select checkpoint_change#,last_change#,online_change#,offline_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE# ONLINE_CHANGE# OFFLINE_CHANGE# CHECKPOIN
------------------ ------------ -------------- --------------- ---------
            579505                      579504          579503 10-NOV-09
            579505                      579504          579503 10-NOV-09
            579505                      579504          579503 10-NOV-09
            579505                      579504          579503 10-NOV-09
            579505                      579504          579503 10-NOV-09

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            579505

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            579505
            579505
            579505
            579505
            579505

SQL> alter database open;     --打开数据库的时候，提示错误。要恢复datafile 1.
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/digoal/system01.dbf'

 

SQL> recover datafile 1;   --按照提示恢复datafile 1
Media recovery complete.
SQL> alter database open;  --打开数据库的时候，提示错误。要恢复datafile 2.
alter database open
*
ERROR at line 1:        
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/oradata/digoal/undotbs01.dbf'

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            579507     --SYSTEM表空间的SCN号已经改变，其它的也都需要恢复
            579505
            579505
            579505
            579505

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            579507
            579505
            579505
            579505
            579505

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            579505

SQL> recover datafile 2;    --按照提示恢复datafile 2
Media recovery complete.
SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            579507
            579507
            579505
            579505
            579505

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u01/oradata/digoal/sysaux01.dbf'

SQL> recover datafile 3;   --按照提示恢复datafile 3；
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/oradata/digoal/users01.dbf'

SQL> recover datafile 4;  --按照提示恢复datafile 4
Media recovery complete.  --按照提示恢复datafile 5
SQL> recover datafile 5;  --按照提示恢复datafile 6
Media recovery complete.

--恢复表文件后的。SCN 变成了579507， 但v$database 中的SCN 没有变化
SQL> select checkpoint_change# from v$datafile;   --

CHECKPOINT_CHANGE#
------------------
            579507
            579507
            579507
            579507
            579507

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            579505

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            579507
            579507
            579507
            579507
            579507

SQL> alter database open resetlogs;    --尝试打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open unresetlogs;  --- -尝试打开数据库
alter database open unresetlogs
                    *
ERROR at line 1:
ORA-02288: invalid OPEN mode 

SQL> alter database open;    ---尝试打开数据库，哈哈居然可以了，都感觉像做梦的！！！

Database altered.

SQL>

本文转自 gjm008 51CTO博客，原文链接：http://blog.51cto.com/gaoshan/224556，如需转载请自行联系原作者