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

it_horse · 发表于 2003-4-11 08:58

最初由 coolyl 发布
[B]

前面的理解没错，用rman备份的时候，我也同时备份当时的控制文件啊，我每次恢复的时候都先恢复控制文件的，然后用恢复的控制文件做的rman的恢复啊。 [/B]

恢复的关键点就是两个时间点的控制文件都是完好的！

其实就是恢复了两次！

第一次先用以前的控制文件恢复到resetlogs前！
（相当与作了resetlogs前的最近恢复）

第二次再用后面的控制文件恢复到最近！
（这一步应该是真正的through resetlogs操作了！）

但我看了，两次的归档日志是不一样的！

因此，以前的归档日志也要保存！

不知我说得对吗？

X_X · 发表于 2003-4-11 09:20

我第一次做的时候就不是用RMAN，是用冷备份，出错。
但是我第二次做时候两次是都是用recover database until chang integer;进行恢复，就可以了。

biti_rainy · 发表于 2003-4-11 09:31

最初由 it_horse 发布
[B]

恢复的关键点就是两个时间点的控制文件都是完好的！

其实就是恢复了两次！

第一次先用以前的控制文件恢复到resetlogs前！
（相当与作了resetlogs前的最近恢复）

第二次再用后面的控制文件恢复到最近！
（这一步应该是真正的through resetlogs操作了！）

但我看了，两次的归档日志是不一样的！

因此，以前的归档日志也要保存！

不知我说得对吗？ [/B]

当然要保存以前的归档日志

不过就我的理解
1： resetlogs以前的不完全恢复，只能恢复到归档日志的范围之内
2：其实控制文件是否完好并不是很重要，完全手工创建控制文件也应该可以，因为新的控制文件一样可以做不完全恢复或者完全恢复，如果控制文件都不存在的话，不过是要创建2次控制文件罢了（参考丢失控制文件的完全恢复）

eygle · 发表于 2003-4-11 12:04

我觉得不是用什么备份的问题，关键是恢复的时候把握两点：

lunar2000 · 发表于 2003-4-11 12:07

我觉得不是用什么备份的问题，关键是恢复的时候把握两点：
1，用A点的全备恢复到B点之前（发出resetlog之前）
恢复从A点到B点的事务

2，shutdown immeidate，并用B点（resetlog）之后的控制文件启动数据库，再次恢复
恢复从B点到C点的事务

我的过程（用os备份恢复）：
当前状态：
SQL> archive log list
数据库日志模式          存档模式
自动存档          启用
存档终点          e:\oracle\oradata\test\archive
最早的概要日志序列    8
下一个存档日志序列 11
当前日志序列          11
SQL>

假设当前点为A点，在A点做冷备：
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

C:\>cd E:\ColdBackup_for_WIN_9201

C:\>e:

E:\ColdBackup_for_WIN_9201>cold_backup

E:\ColdBackup_for_WIN_9201>cd E:\ColdBackup_for_WIN_9201

E:\ColdBackup_for_WIN_9201>echo Begin Cold_Backup Database...
Begin Cold_Backup Database...

E:\ColdBackup_for_WIN_9201>date /t
2003-04-11 星期五

E:\ColdBackup_for_WIN_9201>time /t
8:50

E:\ColdBackup_for_WIN_9201>sqlplus "/ as sysdba" @shutdown.sql

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 4月 11 08:50:57 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

已连接。
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。

系统已更改。

数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

E:\ColdBackup_for_WIN_9201>copy E:\oracle\ora92\test\*.* D:\COLD_BACKUP
E:\oracle\ora92\test\control02.ctl
E:\oracle\ora92\test\control03.ctl
E:\oracle\ora92\test\DRSYS01.DBF
E:\oracle\ora92\test\INDX01.DBF
E:\oracle\ora92\test\PERFSTAT01.DBF
E:\oracle\ora92\test\RMAN01.DBF
E:\oracle\ora92\test\SYSTEM01.DBF
E:\oracle\ora92\test\TEMP_SQLEXPORT01.DBF
E:\oracle\ora92\test\TEMP01.DBF
E:\oracle\ora92\test\TEST01.DBF
E:\oracle\ora92\test\TOOLS01.DBF
E:\oracle\ora92\test\UNDOTBS01.DBF
E:\oracle\ora92\test\UNDOTBS02.DBF
E:\oracle\ora92\test\USERS01.DBF
E:\oracle\ora92\test\XDB01.DBF
已复制       21 个文件。

E:\ColdBackup_for_WIN_9201>copy E:\oracle\ora92\database\PWDtest.ORA D:\COLD_BA
KUP
已复制       1 个文件。

E:\ColdBackup_for_WIN_9201>copy E:\oracle\oradata\test\archive D:\COLD_BACKUP\a
chive
E:\oracle\oradata\test\archive\1_1.ARC
E:\oracle\oradata\test\archive\1_2.ARC
E:\oracle\oradata\test\archive\1_3.ARC
E:\oracle\oradata\test\archive\1_4.ARC
E:\oracle\oradata\test\archive\1_5.ARC
E:\oracle\oradata\test\archive\1_6.ARC
E:\oracle\oradata\test\archive\1_7.ARC
E:\oracle\oradata\test\archive\1_8.ARC
E:\oracle\oradata\test\archive\1_9.ARC
E:\oracle\oradata\test\archive\1_10.ARC
E:\oracle\oradata\test\archive\1_11.ARC
已复制       11 个文件。

E:\ColdBackup_for_WIN_9201>sqlplus "/ as sysdba" @startup.sql

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 4月 11 08:56:56 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

已连接到空闲例程。

已连接到空闲例程。
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。

系统已更改。

数据库已更改。

系统已更改。

从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

E:\ColdBackup_for_WIN_9201>date /t
2003-04-11 星期五

E:\ColdBackup_for_WIN_9201>time /t
8:57

E:\ColdBackup_for_WIN_9201>echo The Cold Backup Database Successed.
The Cold Backup Database Successed.

E:\ColdBackup_for_WIN_9201>
E:\ColdBackup_for_WIN_9201>

冷备之后的点称之为A1点，在A1点做测试数据：
E:\ColdBackup_for_WIN_9201>c:

C:\>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 11 08:58:31 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    9
Next log sequence to archive 12
Current log sequence          12

添加新的数据文件RBTEST:
SQL> create tablespace rbtest datafile 'E:\oracle\ora92\test\brtest.dbf' size 2m;

Tablespace created.

SQL> alter user lunar quota 10m on rbtest;

User altered.

在新的文件上建表lunar，并插入数据：
SQL> conn lunar/lunar
Connected.
SQL> create table lunar(a number) tablespace rbtest;

Table created.

SQL> insert into lunar values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lunar;

      A
----------
      1

SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    9
Next log sequence to archive 12
Current log sequence          12
SQL> alter system archive log current;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    17
Next log sequence to archive 20
Current log sequence          20
SQL>

然后模拟数据库crash（仍然在A1点）：
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\>del E:\oracle\ora92\test\*
E:\oracle\ora92\test\*, 是否确认 (Y/N) ? y

C:\>dir E:\oracle\ora92\test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C

E:\oracle\ora92\test 的目录

2002-01-13  21:59    <DIR>       .
2002-01-13  21:59    <DIR>       ..
2003-04-11  08:36    <DIR>       bak
            0 个文件             0 字节
            3 个目录  3,532,013,568 可用字节

C:\>

恢复数据库：
首先restore在A点备份的数据文件和控制文件
C:\>copy D:\COLD_BACKUP\*.dbf E:\oracle\ora92\test
D:\COLD_BACKUP\DRSYS01.DBF
D:\COLD_BACKUP\INDX01.DBF
D:\COLD_BACKUP\PERFSTAT01.DBF
D:\COLD_BACKUP\RMAN01.DBF
D:\COLD_BACKUP\SYSTEM01.DBF
D:\COLD_BACKUP\TEMP_SQLEXPORT01.DBF
D:\COLD_BACKUP\TEMP01.DBF
D:\COLD_BACKUP\TEST01.DBF
D:\COLD_BACKUP\TOOLS01.DBF
D:\COLD_BACKUP\UNDOTBS01.DBF
D:\COLD_BACKUP\UNDOTBS02.DBF
D:\COLD_BACKUP\USERS01.DBF
D:\COLD_BACKUP\XDB01.DBF
已复制       13 个文件。

C:\>copy D:\COLD_BACKUP\*.ctl E:\oracle\ora92\test
D:\COLD_BACKUP\control02.ctl
D:\COLD_BACKUP\control03.ctl
已复制       2 个文件。

C:\>

mount数据库：
C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 11 09:11:08 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 77641117 generated at 04/11/2003 08:51:38 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC
ORA-00280: change 77641117 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 13: 'E:\ORACLE\ORA92\TEST\BRTEST.DBF'

ORA-01112: media recovery not started

SQL> select name from v$datafile where file#=13;

NAME
-------------------------------------------------------------

E:\ORACLE\ORA92\DATABASE\UNNAMED00013

SQL> alter database create datafile
  2  'E:\ORACLE\ORA92\DATABASE\UNNAMED00013'
  3  as
  4  'E:\ORACLE\ORA92\TEST\BRTEST.DBF';

Database altered.

SQL> recover database using backup controlfile;
ORA-00279: change 77641289 generated at 04/11/2003 08:59:58 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC
ORA-00280: change 77641289 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 77641391 generated at 04/11/2003 09:03:19 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_13.ARC
ORA-00280: change 77641391 for thread 1 is in sequence #13
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC' no longer needed
for this recovery

ORA-00279: change 77641394 generated at 04/11/2003 09:03:28 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_14.ARC
ORA-00280: change 77641394 for thread 1 is in sequence #14
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_13.ARC' no longer needed
for this recovery

ORA-00279: change 77641397 generated at 04/11/2003 09:03:29 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_15.ARC
ORA-00280: change 77641397 for thread 1 is in sequence #15
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_14.ARC' no longer needed
for this recovery

ORA-00279: change 77641400 generated at 04/11/2003 09:03:34 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_16.ARC
ORA-00280: change 77641400 for thread 1 is in sequence #16
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_15.ARC' no longer needed
for this recovery

ORA-00279: change 77641402 generated at 04/11/2003 09:03:35 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_17.ARC
ORA-00280: change 77641402 for thread 1 is in sequence #17
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_16.ARC' no longer needed
for this recovery

ORA-00279: change 77641404 generated at 04/11/2003 09:03:35 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_18.ARC
ORA-00280: change 77641404 for thread 1 is in sequence #18
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_17.ARC' no longer needed
for this recovery

ORA-00279: change 77641407 generated at 04/11/2003 09:03:41 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_19.ARC
ORA-00280: change 77641407 for thread 1 is in sequence #19
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_18.ARC' no longer needed
for this recovery

ORA-00279: change 77641413 generated at 04/11/2003 09:03:57 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC
ORA-00280: change 77641413 for thread 1 is in sequence #20
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_19.ARC' no longer needed
for this recovery

ORA-00308: cannot open archived log 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) ???????????

SQL>

所有的archivelog都已经apply了，使用resetlogs打开数据库，这一点称之为B点：
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 77641413 generated at 04/11/2003 09:03:57 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC
ORA-00280: change 77641413 for thread 1 is in sequence #20

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

Database altered.

SQL>

验证B点的恢复情况：
SQL> conn lunar/lunar
Connected.
SQL> select * from lunar;

      A
----------
      1

SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    0
Next log sequence to archive 1
Current log sequence          1
SQL>

B点之后（resetlogs之后）称之为B1点，在B1点本分控制文件：
（理论上应当在B1点做数据的全备，但是考虑有时候情况不允许，暂时不能做数据库的全备，
那么一定要做B1点的控制文件的备份，这里我们做热备时候的控制文件备份）
SQL> alter database backup controlfile to 'D:\COLD_BACKUP\b1.ctl';

Database altered.

SQL> show parameter dump

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                string    partial
background_dump_dest                string    e:\oracle\admin\test\bdump
core_dump_dest                      string    e:\oracle\admin\test\cdump
max_dump_file_size                string    UNLIMITED
shadow_core_dump                   string    partial
user_dump_dest                      string    e:\oracle\admin\test\udump
SQL> alter system set user_dump_dest='D:\COLD_BACKUP';

System altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter system set user_dump_dest='e:\oracle\admin\test\udump';

System altered.

SQL>

在B1点制作测试数据：
SQL> conn lunar/lunar
Connected.
SQL> insert into lunar values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lunar;

      A
----------
      1
      2

SQL> conn / as sysdba
Connected.
SQL>

再增加两条提交的数据，其中一条有检查点发生，一点没有检查点发生
SQL> archive log list;
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    5
Next log sequence to archive 8
Current log sequence          8
SQL> conn lunar/lunar
Connected.
SQL> insert into lunar values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> insert into lunar values(4);

1 row created.

SQL>

然后switch logfile，以上两条数据应在以后的恢复过程中当被恢复出来。
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 4月 11 09:28:49 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> alter system switch logfile;

系统已更改。

SQL> archive log list;
数据库日志模式          存档模式
自动存档          启用
存档终点          e:\oracle\oradata\test\archive
最早的概要日志序列    6
下一个存档日志序列 9
当前日志序列          9
SQL>

再增加两条提交的数据，并发生检查点：
SQL> insert into lunar values(4);

1 row created.

SQL> insert into lunar values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into lunar values(6);

1 row created.

SQL>  alter system checkpoint;

System altered.

SQL>

不发生swtich logfile，这样前3条记录已经归档，后面的没有归档
如果所有文件都丢失（包括redo），可以恢复到前5条记录；
如果redo没有丢失，应该可以恢复到前5条记录。
SQL> select * from lunar;

      A
----------
      1
      2
      3
      4
      5
      6

6 rows selected.

SQL>
能否恢复到第3条记录。

模拟数据库crash，这一点我们称之为C点：
SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

C:\>del E:\oracle\ora92\test\*
E:\oracle\ora92\test\*, 是否确认 (Y/N) ? y

C:\>dir E:\oracle\ora92\test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C

E:\oracle\ora92\test 的目录

2002-01-13  21:59    <DIR>       .
2002-01-13  21:59    <DIR>       ..
2003-04-11  08:36    <DIR>       bak
            0 个文件             0 字节
            3 个目录  3,531,964,416 可用字节

C:\>

在C点恢复数据库
（此时我们有A点的全备，B1点的控制文件备份和所有的archive log）
resotre 在A点的全备：
C:\>copy D:\COLD_BACKUP\con*.ctl E:\oracle\ora92\test
D:\COLD_BACKUP\control02.ctl
D:\COLD_BACKUP\control03.ctl
已复制       2 个文件。

C:\>copy D:\COLD_BACKUP\*.dbf E:\oracle\ora92\test
D:\COLD_BACKUP\DRSYS01.DBF
D:\COLD_BACKUP\INDX01.DBF
D:\COLD_BACKUP\PERFSTAT01.DBF
D:\COLD_BACKUP\RMAN01.DBF
D:\COLD_BACKUP\SYSTEM01.DBF
D:\COLD_BACKUP\TEMP_SQLEXPORT01.DBF
D:\COLD_BACKUP\TEMP01.DBF
D:\COLD_BACKUP\TEST01.DBF
D:\COLD_BACKUP\TOOLS01.DBF
D:\COLD_BACKUP\UNDOTBS01.DBF
D:\COLD_BACKUP\UNDOTBS02.DBF
D:\COLD_BACKUP\USERS01.DBF
D:\COLD_BACKUP\XDB01.DBF
已复制       13 个文件。

C:\>

MOUNT数据库（此时用的是A点的控制文件）：
C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 11 09:44:47 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
Database mounted.
SQL> select RESETLOGS_CHANGE#-1 from v$database;

RESETLOGS_CHANGE#-1
-------------------
         77640765

SQL> recover database using backup controlfile;
ORA-00279: change 77641117 generated at 04/11/2003 08:51:38 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC
ORA-00280: change 77641117 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 13: 'E:\ORACLE\ORA92\TEST\BRTEST.DBF'

ORA-01112: media recovery not started

SQL> recover database using backup controlfile;
ORA-00279: change 77641117 generated at 04/11/2003 08:51:38 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC
ORA-00280: change 77641117 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 13: 'E:\ORACLE\ORA92\TEST\BRTEST.DBF'

ORA-01112: media recovery not started

SQL> select name from v$datafile where file#=13;

NAME
--------------------------------------------------------------------------------

E:\ORACLE\ORA92\DATABASE\UNNAMED00013

SQL>  alter database create datafile
  2  'E:\ORACLE\ORA92\DATABASE\UNNAMED00013'
  3  as
  4  'E:\ORACLE\ORA92\TEST\BRTEST.DBF';

Database altered.

SQL> recover database using backup controlfile;
ORA-00279: change 77641289 generated at 04/11/2003 08:59:58 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC
ORA-00280: change 77641289 for thread 1 is in sequence #12

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 77641391 generated at 04/11/2003 09:03:19 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_13.ARC
ORA-00280: change 77641391 for thread 1 is in sequence #13
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_12.ARC' no longer needed
for this recovery

ORA-00279: change 77641394 generated at 04/11/2003 09:03:28 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_14.ARC
ORA-00280: change 77641394 for thread 1 is in sequence #14
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_13.ARC' no longer needed
for this recovery

ORA-00279: change 77641397 generated at 04/11/2003 09:03:29 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_15.ARC
ORA-00280: change 77641397 for thread 1 is in sequence #15
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_14.ARC' no longer needed
for this recovery

ORA-00279: change 77641400 generated at 04/11/2003 09:03:34 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_16.ARC
ORA-00280: change 77641400 for thread 1 is in sequence #16
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_15.ARC' no longer needed
for this recovery

ORA-00279: change 77641402 generated at 04/11/2003 09:03:35 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_17.ARC
ORA-00280: change 77641402 for thread 1 is in sequence #17
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_16.ARC' no longer needed
for this recovery

ORA-00279: change 77641404 generated at 04/11/2003 09:03:35 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_18.ARC
ORA-00280: change 77641404 for thread 1 is in sequence #18
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_17.ARC' no longer needed
for this recovery

ORA-00279: change 77641407 generated at 04/11/2003 09:03:41 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_19.ARC
ORA-00280: change 77641407 for thread 1 is in sequence #19
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_18.ARC' no longer needed
for this recovery

ORA-00279: change 77641413 generated at 04/11/2003 09:03:57 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC
ORA-00280: change 77641413 for thread 1 is in sequence #20
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_19.ARC' no longer needed
for this recovery

ORA-00308: cannot open archived log 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) ???????????

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 77641413 generated at 04/11/2003 09:03:57 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_20.ARC
ORA-00280: change 77641413 for thread 1 is in sequence #20

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
现在已经到B点的前一点（resetlog之前的一点）

修改init.ora文件，使用B1点的备份的控制文件
#control_files=("e:\oracle\ora92\test\control02.ctl", "e:\oracle\ora92\test\control03.ctl"

control_files=("e:\oracle\ora92\test\b1.ctl"

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile=E:\oracle\admin\test\pfile\init.ora
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
Database mounted.
SQL>

检查B点之前（resetlogs之前的一点的SCN）：
方法1：找到在alert.log中记录的resetlog前的SCN：
Fri Apr 11 09:18:49 2003
alter database open resetlogs
Fri Apr 11 09:18:50 2003
RESETLOGS after incomplete recovery UNTIL CHANGE 77641413
Resetting resetlogs activation ID 868054966 (0x33bd77b6)

方法2：
SQL> select RESETLOGS_CHANGE#-1 from v$database;

RESETLOGS_CHANGE#-1
-------------------
         77641413

SQL>
以上两种方法找到的应该是同一个SCN

恢复数据库：
可以使用until change 刚才找到的SCN来恢复，也可以通过观察提交的archive logs来恢复：
SQL> recover database using backup controlfile;
ORA-00279: change 77641414 generated at 04/11/2003 09:18:49 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_1.ARC
ORA-00280: change 77641414 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 77641660 generated at 04/11/2003 09:22:29 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_2.ARC
ORA-00280: change 77641660 for thread 1 is in sequence #2
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_1.ARC' no longer needed
for this recovery

ORA-00279: change 77641664 generated at 04/11/2003 09:22:38 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_3.ARC
ORA-00280: change 77641664 for thread 1 is in sequence #3
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_2.ARC' no longer needed
for this recovery

ORA-00279: change 77641668 generated at 04/11/2003 09:22:44 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_4.ARC
ORA-00280: change 77641668 for thread 1 is in sequence #4
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_3.ARC' no longer needed
for this recovery

ORA-00279: change 77641670 generated at 04/11/2003 09:22:45 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_5.ARC
ORA-00280: change 77641670 for thread 1 is in sequence #5
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_4.ARC' no longer needed
for this recovery

ORA-00279: change 77641672 generated at 04/11/2003 09:22:45 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_6.ARC
ORA-00280: change 77641672 for thread 1 is in sequence #6
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_5.ARC' no longer needed
for this recovery

ORA-00279: change 77641675 generated at 04/11/2003 09:22:51 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_7.ARC
ORA-00280: change 77641675 for thread 1 is in sequence #7
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_6.ARC' no longer needed
for this recovery

ORA-00279: change 77641677 generated at 04/11/2003 09:22:52 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_8.ARC
ORA-00280: change 77641677 for thread 1 is in sequence #8
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_7.ARC' no longer needed
for this recovery

ORA-00279: change 77641812 generated at 04/11/2003 09:29:26 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_9.ARC
ORA-00280: change 77641812 for thread 1 is in sequence #9
ORA-00278: log file 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_8.ARC' no longer needed
for this recovery

ORA-00328: archived log ends at change 77640944, need later change 77641812
ORA-00334: archived log: 'E:\ORACLE\ORADATA\TEST\ARCHIVE\1_9.ARC'

SQL>
已经应用了所有的archive logs

在C点resetlogs打开数据库：
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 77641812 generated at 04/11/2003 09:29:26 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\TEST\ARCHIVE\1_9.ARC
ORA-00280: change 77641812 for thread 1 is in sequence #9

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

Database altered.

SQL>

验证恢复情况：
SQL> archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          e:\oracle\oradata\test\archive
Oldest online log sequence    0
Next log sequence to archive 1
Current log sequence          1
SQL> conn lunar/lunar
Connected.
SQL> select * from lunar;

      A
----------
      1
      2
      3

SQL>
这是当前情况合理的恢复结果。

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

修改控制文件的路径和名称
control_files=("e:\oracle\ora92\test\control02.ctl", "e:\oracle\ora92\test\control03.ctl"

#control_files=("e:\oracle\ora92\test\b1.ctl"

SQL> startup
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
Database mounted.
Database opened.
SQL>

lunar2000 · 发表于 2003-4-11 12:18

最初由 biti_rainy 发布
[B]你做这一步的时候不这样做？

不要CANCEL
而是指定时间或者SCN
然后 open resetlogs

……

等到重新执行恢复的时候，仍然恢复到这个时间点或者SCN，再用新的控制文件和归档日志来恢复，其实这个过程，也就是模拟 "只要是在resetlogs打开数据库之前刚刚作的备份",你用cancel ，在2次恢复过程中却是不一样的过程，因为第一次恢复的时候l 实际上也可能应用到 online log ,但open resetlogs 的时候讲online log清空了，第二次做恢复的时候就没有办法保持一致了，这样就造成 SCN的不连续

第二次恢复也不能是 auto 来做，是先恢复到第一次恢复的SCN or 时间点，然后再换控制文件用新的归档日志 [/B]

晕！

其实没那么复杂，原理很好想的：
两个条件：
1 ，a点全备和archive（a到B的）
2 ，resetlogs之后备份控制文件（一定要，否则不能从B到C）和archive（B到C的）

原理：
a点的数据文件和控制文件可以恢复到resetlogs之前（B点之前），也就是你恢复到应用了所有的resetlog之前的archive log ，然后按理说要仅仅恢复到B那么就可以resetlogs了，但是我们需要恢复到C，也就是越过Resetlog点，继续恢复。

那么只要有在Resetlog之后的所有的archive logs，resetlog之后备份的控制文件，我们就可以恢复了（数据文件就是刚刚恢复的结果。）

这是更换控制文件（resetlogs之后备份控制文件），相当于用备份的控制文件恢复数据库，然后就ok了

lunar2000 · 发表于 2003-4-11 12:25

最初由 it_horse 发布
[B]

恢复的关键点就是两个时间点的控制文件都是完好的！

其实就是恢复了两次！

第一次先用以前的控制文件恢复到resetlogs前！
（相当与作了resetlogs前的最近恢复）

第二次再用后面的控制文件恢复到最近！
（这一步应该是真正的through resetlogs操作了！）

但我看了，两次的归档日志是不一样的！

因此，以前的归档日志也要保存！

不知我说得对吗？ [/B]

是的，归档要其全。
如果不记得归档的情况可以通过查找scn的方式，在从A到B的时候，用until change来恢复。B到C地恢复和一般的恢复无异（注意在B点，shutdown 更换了B点以后的控制文件，其他就没什么不同了）

it_horse · 发表于 2003-4-11 12:28

[B]我觉得不是用什么备份的问题，关键是恢复的时候把握两点：
1，用A点的全备恢复到B点之前（发出resetlog之前）
恢复从A点到B点的事务

2，shutdown immeidate，并用B点（resetlog）之后的控制文件启动数据库，再次恢复
恢复从B点到C点的事务......[/B]

coolyl · 发表于 2003-4-11 12:33

嗯，我也发现错误了，就是因为没有备份resetlog后的控制文件，所以最后的恢复才有问题，备份后就没有问题了。
谢谢lunar。

biti_rainy · 发表于 2003-4-11 12:34

最初由 lunar2000 发布
[B]

晕！

其实没那么复杂，原理很好想的：
a点的数据文件和控制文件可以恢复到resetlogs之前（B点之前），也就是你恢复到应用了所有的resetlog之前的archive log ，然后按理说要仅仅恢复到B那么就可以resetlogs了，但是我们需要恢复到C，也就是越过Resetlog点，继续恢复。

那么只要有在Resetlog之后的所有的archive logs，备份的控制文件，我们就可以恢复了（数据文件就是刚刚恢复的结果。）

这是更换控制文件，相当于用备份的控制文件恢复数据库，然后就ok了 [/B]

这个原理早就理解了：

模拟 "在resetlogs打开数据库之前刚刚作的备份",也就是模拟B点的数据库状态来继续恢复到C点

只不过，对备份与恢复不是很理解或者不曾去做过的时候
这种抽象的描述始终不容易理解

我再重复我的两个观点，不知道大家有没有什么意见

不过就我的理解
1： resetlogs以前的不完全恢复，只能恢复到归档日志的范围之内（防止resetlogs 丢失 online log）
2：其实控制文件是否完好并不是很重要，完全手工创建控制文件也应该可以，因为新的控制文件一样可以做不完全恢复或者完全恢复，如果控制文件都不存在的话，不过是要创建2次控制文件罢了（参考丢失控制文件的完全恢复）

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

谢谢biti、coolyl！

Re: 谢谢biti、coolyl！

Re: SVRMGR> recover database until cancel

Re: 谢谢biti、coolyl！

然也！lunar2000理解的真正确！

Re: Re: SVRMGR> recover database until cancel