9.6.1 使用alter database open resetlogs的场合
《大话Oracle RAC：集群、高可用性、备份与恢复》第9章恢复，本章介绍的是经典的恢复手段─基于备份的恢复，但这种恢复动作比较大，因为需要关闭数据库，尤其不完全恢复会把整个数据库回退到过去的某个点，之后的修改全部丢失。本节为大家介绍使用alter database open resetlogs的场合。
AD：
9.6 利用恢复的控制文件进行数据库恢复
Oracle文档中提到，一旦用备份的控制文件进行数据库恢复，就需要使用resetlogs的方法打开数据库，但是resetlogs通常意味着不完全恢复，而且更重要的是一旦用resetlogs方法打开数据库，日志的序号用重新从1开始。其实如果只是控制文件损坏，日志文件都完整的话，数据库是可以完全恢复的，而且不必非得用resetlogs打开。接下来，我们就对不完全恢复做出总结，如图9-10所示，然后再通过一些实验深入理解。
 |
| 图9-10 控制文件的重建方法及后续恢复操作 |
9.6.1 使用alter database open resetlogs的场合
使用resetlogs选项，会把当前的日志序号（log sequence number）重设为1，并抛弃所有日志信息。在以下条件时需要使用resetlogs选项：
在不完全恢复（介质恢复）；
使用备份控制文件。
使用resetlogs打开数据库后，务必要完整地进行一次数据库备份。
9.6.2 重建控制文件
如果数据库的控制文件损坏，有3种方法可以用来重建控制文件，每一种方法的后续处理又有不同，这3种方法分别是：
从自动备份或者备份中恢复；
用create controlfile resetlogs方法重建；
用create controlfile noresetlogs方法重建。
每一种方法所对应的后续处理是这样的。
（1）如果是从备份中恢复的控制文件，后续要：
使用using backup controlfile子句进行数据库恢复；
打开数据库时需要用resetlogs方式打开。
（2）如果使用resetlogs方法重建的控制文件，后续要：
使用using backup controlfile的方法进行数据库恢复；
要用resetlogs方式打开数据库。
（3）如果是用noresetlogs方法创建的控制文件，后续要：
恢复数据库时无须用using backup controlfile；
无须使用resetlogs方式打开数据库。
值得一提的是，create controlfile resetlogs/noresetlogs这两种重建方法的区别在于，noresetlogs重建控制文件时，控制文件中datafile Checkpoint来自Online logs中的Current log头。而用resetlogs重建控制文件时，控制文件中datafile Checkpoint来自各数据文件头。
9.6.3 使用using backup controlfile的场合
以下条件需要使用using backup controlfile：
使用备份控制文件；
用resetlogs方法重建控制文件,如果用noresetlogs重建不必要使用using backup controlfile。
接下来，我们通过3个例子深入分析这些场景的处理，注意，下面这些例子中日志文件都是好的，也就是从数据角度来看，做的都是完全恢复，没有数据丢失。但因为控制文件的来源不同，后续处理也就不一样。
9.6.4 例子1：使用备份的控制文件做恢复（1）
（1）备份控制文件：
- RMAN> backup datafile 1;
- Starting backup at 13-APR-11
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=2476 devtype=DISK
- channel ORA_DISK_1: starting full datafile backupset
- channel ORA_DISK_1: specifying datafile(s) in backupset
- input datafile fno=00001 name=/zxm/cindytest/system01.dbf
- channel ORA_DISK_1: starting piece 1 at 13-APR-11
- channel ORA_DISK_1: finished piece 1 at 13-APR-11
- piece handle=/oracle/product/10G_single/dbs/
0om9lsiu_1_1 tag=TAG20110413T113453 comment=NONE- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
- Finished backup at 13-APR-11
- Starting Control File and SPFILE Autobackup at 13-APR-11
- piece handle=/oracle/product/10G_single/dbs/c
-539593454-20110413-00 comment=NONE- Finished Control File and SPFILE Autobackup at 13-APR-11
备份的控制文件在/oracle/product/10G_single/dbs/c-539593454-20110413-00，稍后就利用这个备份进行恢复。
（2）进行了一堆操作和日志切换：
- SQL> alter system switch logfile;
- System altered.
- SQL> create table a as select * from tabs;
- Table created.
- SQL> alter system switch logfile;
- System altered.
- SQL> create table b as select * from a;
- Table created.
- SQL> alter system switch logfile;
当前的日志序列：
- SQL> select sequence# from v$log;
- SEQUENCE#
- ----------
- 24
- 22
- 23
（3）关闭数据库，模拟故障：
- SQL> shutdown abort;
- ORACLE instance shut down
删除控制文件。
（4）启动数据库，因为没有控制文件，所以只能打开到nomount状态，这也是恢复所必须的状态：
- SQL> startup
- ORACLE instance started.
- Total System Global Area 3221225472 bytes
- Fixed Size 2024200 bytes
- Variable Size 637537528 bytes
- Database Buffers 2566914048 bytes
- Redo Buffers 14749696 bytes
- ORA-00205: error in identifying control file,
check alert log for more info
（5）恢复控制文件：
- [oracle@dbs cindytest]$ rman target /
- Recovery Manager: Release 10.2.0.1.0 - Production
on Wed Apr 13 11:38:56 2011- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- connected to target database: ctest (not mounted)
- RMAN> restore controlfile from '/oracle/product/
10G_single/dbs/c-539593454-20110413-00';- Starting restore at 13-APR-11
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=2486 devtype=DISK
- channel ORA_DISK_1: restoring control file
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
- output filename=/zxm/cindytest/control01.ctl
- Finished restore at 13-APR-11
把数据库打开到mount状态：
- SQL> select status from v$instance;
- STATUS
- ------------
- STARTED
- SQL> alter database mount;
- Database altered.
- SQL> select sequence# from v$log;
- SEQUENCE#
- ----------
- 21
- 20
- 19
9.6.4 例子1：使用备份的控制文件做恢复（2）
（6）恢复数据库，如果不用using子句，rman会提示：
- SQL> recover database;
- ORA-00283: recovery session canceled due to errors
- ORA-01610: recovery using the BACKUP
CONTROLFILE option must be done- SQL> recover database using backup controlfile;
- ORA-00279: change 76850708247 generated at
04/13/2011 09:52:40 needed for- thread 1
- ORA-00289: suggestion : /zxm/cindytest/arch/1_21_747160176.dbf
- ORA-00280: change 76850708247 for thread 1 is in sequence #21
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 76850708257 generated at
04/13/2011 11:35:48 needed for- thread 1
- ORA-00289: suggestion : /zxm/cindytest/arch/
1_22_747160176.dbf- ORA-00280: change 76850708257 for thread 1
is in sequence #22- ORA-00278: log file '/zxm/cindytest/arch/1_2
1_747160176.dbf' no longer needed- for this recovery
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 76850708305 generated at 04/
13/2011 11:36:01 needed for- thread 1
- ORA-00289: suggestion : /zxm/cindytest/arch/1_23_747160176.dbf
- ORA-00280: change 76850708305 for thread 1 is in sequence #23
- ORA-00278: log file '/zxm/cindytest/arch/1_22_
747160176.dbf' no longer needed- for this recovery
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 76850708336 generated at
04/13/2011 11:36:21 needed for- thread 1
- ORA-00289: suggestion : /zxm/cindytest/arch/1_24_747160176.dbf
- ORA-00280: change 76850708336 for thread 1 is in sequence #24
- ORA-00278: log file '/zxm/cindytest/arch/1_23_
747160176.dbf' no longer needed- for this recovery
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00308: cannot open archived log '/zxm/
cindytest/arch/1_24_747160176.dbf'- ORA-27037: unable to obtain file status
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
rman会自己猜测归档日志文件，我们只需要按下回车键就可以了，不过最后一个需要注意，第24号日志文件实际是联机日志，并不是控制文件，所以需要我们明确告诉rman这个日志文件的位置。
如果不记得到底哪一个是当前日志了，那就从第一个开始吧，再次输入这个命令，在遇到提示时输入联机日志的位置；
- SQL> recover database using backup controlfile;
- ORA-00279: change 76850708336 generated at
04/13/2011 11:36:21 needed for- thread 1
- ORA-00289: suggestion : /zxm/cindytest/arch/1_24_747160176.dbf
- ORA-00280: change 76850708336 for thread 1 is in sequence #24
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
输入文件位置：
- /zxm/cindytest/redo01.log
恢复成功：
- Log applied.
- Media recovery complete.
（7）虽然进行的是完全恢复，没有数据丢失，但是因为使用了using子句，打开数据库时必须使用resetlogs方法打开；
- SQL> alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-01589: must use RESETLOGS or NORESETLOGS
option for database open- SQL> alter database open noresetlogs;
- alter database open noresetlogs
- *
- ERROR at line 1:
- ORA-01588: must use RESETLOGS option for database open
- SQL> alter database open resetlogs;
- Database altered.
现在日志序列重新从1开始编号。
- SQL> select group#,sequence#,status from v$log;
- GROUP# SEQUENCE# STATUS
- ---------- ---------- ----------------
- 1 1 INACTIVE
- 2 2 CURRENT
- 3 0 UNUSED
9.6.5 例子2：用noresetlogs方法重建控制文件（1）
（1）继续测试，再做一次备份：
- RMAN> backup datafile 1;
- Starting backup at 13-APR-11
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: sid=2489 devtype=DISK
- channel ORA_DISK_1: starting full datafile backupset
- channel ORA_DISK_1: specifying datafile(s) in backupset
- input datafile fno=00001 name=/zxm/cindytest/system01.dbf
- channel ORA_DISK_1: starting piece 1 at 13-APR-11
- channel ORA_DISK_1: finished piece 1 at 13-APR-11
- piece handle=/oracle/product/10G_single/dbs/
0sm9lufk_1_1 tag=TAG20110413T120716 comment=NONE- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
- Finished backup at 13-APR-11
- Starting Control File and SPFILE Autobackup at 13-APR-11
- piece handle=/oracle/product/10G_single/dbs
/c-539593454-20110413-01 comment=NONE- Finished Control File and SPFILE Autobackup at 13-APR-11
（2）做一些操作和日志切换：
- SQL> create table aa as select * from a;
- Table created.
- SQL> create table bb as select * from b;
- Table created.
- SQL> alter system switch logfile;
- System altered.
- SQL> create table cc as select * from a;
- Table created.
- SQL> alter system switch logfile;
- System altered.
当前日志序列：
- SQL> select group#,sequence#,status from v$log;
- GROUP# SEQUENCE# STATUS
- ---------- ---------- ----------------
- 1 4 CURRENT
- 2 2 ACTIVE
- 3 3 ACTIVE
（3）关闭数据库，模拟故障：
- SQL> shutdown abort;
- exit
- ORACLE instance shut down.
- SQL> Disconnected from Oracle Database 10g
Enterprise Edition Release 10.2.0.1.0 - 64bit Production- With the Partitioning, OLAP and Data Mining options
删除控制文件：
- SQL> startup
- ORACLE instance started.
- Total System Global Area 3221225472 bytes
- Fixed Size 2024200 bytes
- Variable Size 637537528 bytes
- Database Buffers 2566914048 bytes
- Redo Buffers 14749696 bytes
- ORA-00205: error in identifying control file,
check alert log for more info（4）恢复控制文件：
- RMAN> restore controlfile from '/oracle/product
/10G_single/dbs/c-539593454-20110413-01';- Starting restore at 13-APR-11
- using channel ORA_DISK_1
- channel ORA_DISK_1: restoring control file
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
- output filename=/zxm/cindytest/control01.ctl
- Finished restore at 13-APR-11
把控制文件备份到trace文件：
- SQL> alter database backup controlfile to trace;
- Database altered
（5）重建控制文件，先关闭数据库，删除之前从备份中恢复出来的控制文件，启动到nomount状态：
- SQL> shutdown immediate;
- ORA-01109: database not open
- Database dismounted.
- ORACLE instance shut down.
删除控制文件：
- SQL> startup nomount ;
- ORACLE instance started.
- Total System Global Area 3221225472 bytes
- Fixed Size 2024200 bytes
- Variable Size 637537528 bytes
- Database Buffers 2566914048 bytes
- Redo Buffers 14749696 bytes
9.6.5 例子2：用noresetlogs方法重建控制文件（2）
（6）用noresetlogs创建，因为联机日志还在，所以可以用noresetlogs的方法创建：
- SQL> CREATE CONTROLFILE REUSE DATABASE
"CTEST" NORESETLOGS ARCHIVELOG- 2 MAXLOGFILES 16
- 3 MAXLOGMEMBERS 3
- 4 MAXDATAFILES 100
- 5 MAXINSTANCES 8
- 6 MAXLOGHISTORY 292
- 7 LOGFILE
- 8 GROUP 1 '/zxm/cindytest/ctest/redo01.log' SIZE 50M,
- 9 GROUP 2 '/zxm/cindytest/ctest/redo02.log' SIZE 50M,
- 10 GROUP 3 '/zxm/cindytest/ctest/redo03.log' SIZE 50M
- 11 -- STANDBY LOGFILE
- 12 DATAFILE
- 13 '/zxm/cindytest/ctest/system01.dbf',
- 14 '/zxm/cindytest/ctest/undotbs01.dbf',
- 15 '/zxm/cindytest/ctest/sysaux01.dbf',
- 16 '/zxm/cindytest/ctest/users01.dbf'
- 17 CHARACTER SET ZHS16GBK;
- Control file created.
- SQL> alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-01113: file 1 needs media recovery
- ORA-01110: data file 1: '/zxm/cindytest/ctest/system01.dbf'
（7）目前数据文件还不一致，需要进行介质恢复，但是不用using子句：
- SQL> recover database;
- Media recovery complete.
（8）打开数据库：
- SQL> alter database open;
- Database altered.
9.6.6 例子3：用resetlogs方法重建控制文件
（1）模拟过程和之前的步骤都一样，就不再演示了，主要看重建控制文件之后的操作。
（2）因为联机日志还在，如果用resetlogs方法，要求联机日志中必须要没有活动事务才行。创建控制文件的操作并不会清空当前联机日志内容，因此，如果有数据文件的不一致，当前联机日志还是可以用来恢复的；
- SQL> CREATE CONTROLFILE REUSE DATABASE "CTEST"
RESETLOGS ARCHIVELOG- 2 MAXLOGFILES 16
- 3 MAXLOGMEMBERS 3
- 4 MAXDATAFILES 100
- 5 MAXINSTANCES 8
- 6 MAXLOGHISTORY 292
- 7 LOGFILE
- 8 GROUP 1 '/zxm/cindytest/ctest/redo01.log' SIZE 50M,
- 9 GROUP 2 '/zxm/cindytest/ctest/redo02.log' SIZE 50M,
- 10 GROUP 3 '/zxm/cindytest/ctest/redo03.log' SIZE 50M
- 11 -- STANDBY LOGFILE
- 12 DATAFILE
- 13 '/zxm/cindytest/ctest/system01.dbf',
- 14 '/zxm/cindytest/ctest/undotbs01.dbf',
- 15 '/zxm/cindytest/ctest/sysaux01.dbf',
- 16 '/zxm/cindytest/ctest/users01.dbf'
- 17 CHARACTER SET ZHS16GBK
- 18 ;
- Control file created.
控制文件创建之后，数据库自动到mount状态：
- SQL> alter database mount;
- alter database mount
- *
- ERROR at line 1:
- ORA-01100: database already mounted
（3）尝试用resetlogs方法打开数据库：
- SQL> alter database open resetlogs;
- alter database open resetlogs
- *
- ERROR at line 1:
- ORA-01194: file 1 needs more recovery to be consistent
- ORA-01110: data file 1: '/zxm/cindytest/ctest/system01.dbf'
（4）数据文件不一致，需要进行介质恢复：
- SQL> recover database;
- ORA-00283: recovery session canceled due to errors
- ORA-01610: recovery using the BACKUP
CONTROLFILE option must be done因为控制文件是用resetlogs的方法创建的，因此恢复时必须要用using子句；
- SQL> recover database using backup controlfile;
- ORA-00279: change 558697 generated at 04/
13/2011 14:25:26 needed for thread 1- ORA-00289: suggestion : /zxm/cindytest/arch/1_8_748360440.dbf
- ORA-00280: change 558697 for thread 1 is in sequence #8
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00308: cannot open archived log '/
zxm/cindytest/arch/1_8_748360440.dbf'- ORA-27037: unable to obtain file status
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
Oracle自己猜测的归档日志其实是联机日志，因此需要手工指定这个文件；
- SQL> recover database using backup controlfile;
- ORA-00279: change 558697 generated at 04/13
/2011 14:25:26 needed for thread 1- ORA-00289: suggestion : /zxm/cindytest/arch/1_8_748360440.dbf
- ORA-00280: change 558697 for thread 1 is in sequence #8
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
给出这个文件：
- /zxm/cindytest/ctest/redo01.log
恢复成功：
- Log applied.
- Media recovery complete.
（5）打开数据库，必须用resetlogs的方法打开才行：
- SQL> alter database open ;
- alter database open
- *
- ERROR at line 1:
- ORA-01589: must use RESETLOGS or NORESETLOGS
option for database open- SQL> alter database open resetlogs;
- Database altered.
1213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
