我想问一下：Recovery through resetlogs 的内部机理是什莫？

alexzeng

用的是最新的Oracle10.2.0的版本， 在recover database using backup controlfile;时会稍有些不同（自动恢复时不使用前面的INCARNATION的arch log），oracle越来越聪明了，：）

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle10g/oradata/orcl/system01.dbf
/opt/oracle10g/oradata/orcl/undotbs01.dbf
/opt/oracle10g/oradata/orcl/sysaux01.dbf
/opt/oracle10g/oradata/orcl/users01.dbf
/opt/oracle10g/oradata/orcl/example01.dbf

SQL> select * from v$controlfile;

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/opt/oracle10g/oradata/orcl/control01.ctl
NO       16384            430


/opt/oracle10g/oradata/orcl/control02.ctl
NO       16384            430

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------


/opt/oracle10g/oradata/orcl/control03.ctl
NO       16384            430


SQL> desc v$logfile
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                             NUMBER
STATUS                                             VARCHAR2(7)
TYPE                                               VARCHAR2(7)
MEMBER                                             VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> select group#,member from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         3
/opt/oracle10g/oradata/orcl/redo03.log

         2
/opt/oracle10g/oradata/orcl/redo02.log

         1
/opt/oracle10g/oradata/orcl/redo01.log


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host   

--start cold backup
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/oradata/orcl/ /opt/oracle10g/oradata/orcl_cold -r
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/product/10.2.0/dbs/orapworcl /opt/oracle10g/product/10.2.0/dbs/orapworcl_cold
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16  /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16_cold -r
[oracle@rhes29-new oracle10g]$ exit
exit
--end of cold backup, THIS IS POINT A

SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  1218944 bytes
Variable Size              88082048 bytes
Database Buffers          184549376 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

--add some data to db
SQL> create user  t identified by t default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to t;

Grant succeeded.

SQL> conn t/t
Connected.
SQL> create table t (c1 number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        C1
----------
         1

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
--END OF ADD DATA, THIS IS POINT A1

--SIMULATE DATABASE CRASH
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

$ rm /opt/oracle10g/oradata/orcl -r

--RESTORE AND RECOVER DATABASE
--including control file and datafile, no redo files(actually, backup redo is not userful)
[oracle@rhes29-new oracle10g]$ cp -r /opt/oracle10g/oradata/orcl_cold/ /opt/oracle10g/oradata/orcl
[oracle@rhes29-new oracle10g]$ rm /opt/oracle10g/oradata/orcl/redo0*

--recover database
[oracle@rhes29-new oracle10g]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 16 14:13:36 2006

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  281018368 bytes
Fixed Size                  1218944 bytes
Variable Size              88082048 bytes
Database Buffers          184549376 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 484519 generated at 01/16/2006 14:02:06 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_%u_.arc
ORA-00280: change 484519 for thread 1 is in sequence #6
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgj
d_.arc' no longer needed for this recovery


ORA-00279: change 484522 generated at 01/16/2006 14:02:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_%u_.arc
ORA-00280: change 484522 for thread 1 is in sequence #7
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn
2_.arc' no longer needed for this recovery


ORA-00279: change 484526 generated at 01/16/2006 14:02:12 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
ORA-00280: change 484526 for thread 1 is in sequence #8
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_1wqvfn8
m_.arc' no longer needed for this recovery


ORA-00308: cannot open archived log
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 484526 generated at 01/16/2006 14:02:12 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
ORA-00280: change 484526 for thread 1 is in sequence #8


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
--试图用noresetlogs打开数据库，失败！ 因为当前的redo log丢失，所以要用resetlogs打开数据库。

SQL> alter database open resetlogs;

Database altered.

--THIS IS POINT B. 应用了所有的archive log之后，用resetlogs打开数据库
SQL> conn t/t
Connected.
SQL> select * from t;

        C1
----------
         1
--检查一下B点的数据， 没有丢失。应该在resetlogs之后，马上做数据库的全备份，这样才是安全的备份方式。
--这里我们只备份控制文件
SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to '/opt/oracle10g/oradata/b1.ctl';

Database altered.

SQL> alter database backup controlfile to trace as '/opt/oracle10g/oradata/b1_ctl.trc';  

Database altered.

SQL> conn t/t
Connected.
SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        C1
----------
         1
         2

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

--THIS IS POINT B1 WHICH AFTER B.

--SIMULATE DATABASE CRASH
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
[oracle@rhes29-new oracle10g]$ rm -r /opt/oracle10g/oradata/orcl
--THIS IS POINT C
--WE HAVE A FULL BACKUP OF POINT 'A' AND CONTROL FILE OF 'B' AND ALL ARCHIVE LOG FILES

--LET'S RECOVE DB FROM A -> C
--restore data file and control file
[oracle@rhes29-new oracle10g]$ rm -r /opt/oracle10g/oradata/orcl
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/oradata/orcl_cold/ /opt/oracle10g/oradata/orcl -r
[oracle@rhes29-new oracle10g]$ rm /opt/oracle10g/oradata/orcl/*log

--recover db to B
[oracle@rhes29-new oracle10g]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 16 14:50:18 2006

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  281018368 bytes
Fixed Size                  1218944 bytes
Variable Size              88082048 bytes
Database Buffers          184549376 bytes
Redo Buffers                7168000 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery


ORA-01112: media recovery not started
--它发现恢复的目标对象DB有更改，而不使用archlog。大概因为是10g。
--下面手动来恢复它
SQL> host ls -lt /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16
total 24064
-rw-r-----    1 oracle   dba         11776 Jan 16 14:30 o1_mf_1_3_1wqx20fk_.arc
-rw-r-----    1 oracle   dba          1024 Jan 16 14:30 o1_mf_1_2_1wqx1x9f_.arc
-rw-r-----    1 oracle   dba       1823232 Jan 16 14:30 o1_mf_1_1_1wqx1vnw_.arc
-rw-r-----    1 oracle   dba          2560 Jan 16 14:02 o1_mf_1_7_1wqvfn8m_.arc
-rw-r-----    1 oracle   dba          1024 Jan 16 14:02 o1_mf_1_6_1wqvfjn2_.arc
-rw-r-----    1 oracle   dba        583680 Jan 16 14:02 o1_mf_1_5_1wqvfgjd_.arc
-rw-r-----    1 oracle   dba          2560 Jan 16 13:42 o1_mf_1_4_1wqt89gs_.arc
-rw-r-----    1 oracle   dba          8192 Jan 16 13:41 o1_mf_1_3_1wqt7lo6_.arc
-rw-r-----    1 oracle   dba      22152704 Jan 16 13:40 o1_mf_1_2_1wqt5tkr_.arc

--对照上面的恢复错误信息，应该从o1_mf_1_5_%u_.arc开始
SQL>  recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgjd_.arc
ORA-00279: change 484519 generated at 01/16/2006 14:02:06 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_%u_.arc
ORA-00280: change 484519 for thread 1 is in sequence #6
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgj
d_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn2_.arc
ORA-00279: change 484522 generated at 01/16/2006 14:02:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_%u_.arc
ORA-00280: change 484522 for thread 1 is in sequence #7
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn
2_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_1wqvfn8m_.arc
ORA-00279: change 484527 generated at 01/16/2006 14:17:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_%u_.arc
ORA-00280: change 484527 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

--Now is the POINT BEFORE B (BEFORE RESETLOGS)
--这里我们不能再resetlog了， 要偷梁换柱， 替换controlfile为B点之后B1点的controlfile
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /opt/oracle10g/oradata/orcl/co
                                                 ntrol01.ctl, /opt/oracle10g/or
                                                 adata/orcl/control02.ctl, /opt
                                                 /oracle10g/oradata/orcl/contro
                                                 l03.ctl
SQL> alter system set control_files='/opt/oracle10g/oradata/b1.ctl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  1218944 bytes
Variable Size              88082048 bytes
Database Buffers          184549376 bytes
Redo Buffers                7168000 bytes
Database mounted.


SQL>  recover database using backup controlfile;
ORA-00279: change 484527 generated at 01/16/2006 14:02:13 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_%u_.arc
ORA-00280: change 484527 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 485323 generated at 01/16/2006 14:30:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_2_%u_.arc
ORA-00280: change 485323 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_1wqx1vn
w_.arc' no longer needed for this recovery


ORA-00279: change 485326 generated at 01/16/2006 14:30:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_3_%u_.arc
ORA-00280: change 485326 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_2_1wqx1x9
f_.arc' no longer needed for this recovery


ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_3_1wqx20f
k_.arc' no longer needed for this recovery


ORA-00308: cannot open archived log
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL>  recover database using backup controlfile;
ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4
--注意这里要用until cancel， 否则下面 alter database open resetlogs是oracle会以为还需要恢复。

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle10g/oradata/orcl/system01.dbf'


SQL>  recover database using backup controlfile until cancel;
ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4
--再试一遍， 用until cancel就好了

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> conn t/t
Connected.
SQL> select * from t;

        C1
----------
         1
         2
--数据完成了从A到C的恢复，成功跨越resetlogs点。

总结：关键在于需要resetlogs之后的控制文件， 如果resetlogs后没有备份数据文件，就crash了就每戏了，：）

licup123

今天才看到此帖，相见恨晚！

paulyibinyi

今天学习了一下 测试成功

ora110

http://www.infomaxweb.com/Oracle8i/ClosedProcedure.htm
不错，我也测了一遍。
Recovery Through Resetlogs

By making backups after every incomplete recovery, this procedure should never have to be performed. However, if media failure occurs before the backup is completed, this recovery might be required.

Prerequisites:

In order to use this method, you must meet the following criteria:
bullet        The database currently must be an Oracle RDBMS version 7.3.3(or higher) as well as before the resetlogs.
bullet        There is no whole closed or open database backup after "resetlogs."
bullet        There is a whole closed or open database backup prior to "resetlogs."
bullet        Control files before and after "resetlogs" exist.
bullet        All archived logs and online redo logs are present for recovery.
bullet        The alert log contains information from the previous "resetlogs."

Follow every step in this procedure, without skipping any.

   1. Copy the current database Control Files to another location.
      bullet        NT > !copy c:\data\*.ctl  d:\backup02\
   2. Restore the datafiles and control files from a backup before the last "resetlogs" operation.
      bullet        NT > !copy d:\backup\*.dbf  c:\data\
      bullet        NT > !copy d:\backup\*.ctl  c:\data\
   3. Mount the database:
      bullet        SVRMGR> startup mount;
   4. From the alert log, locate the change number calculated at the completion of the previous "resetlogs" operation.

      ...
      Incomplete recovery done UNTIL CHANGE 309121
      Media Recovery Complete.
      ...

   5. Recover the database until change number located above:
      bullet        SVRMGR> recover database until change 309121 using backup controlfile;

   6. NOTES: Type "auto" to apply logs automatically. Shutdown the database using the "normal" option:
      bullet        SVRMGR> shutdown normal;
   7. Restore the current files from the location specified in step 1:
      bullet        NT > !copy d:\backup02\*.ctl  c:\data\
   8. Mount the database:
      bullet        SVRMGR> startup mount;
   9. Recover the database using the appropriate option in RECOVER DATABASE command.
  10. Open the database:
      bullet        SVRMGR> alter database open;
  11. Validate the data you were trying to recover.
  12. Check v$log to ensure recovery to the correct log sequence number.
  13. Backup the database to prevent recovery through reset logs.