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.