实验数据参考
http://blog.itpub.net/26524307/viewspace-1061246/
http://blog.itpub.net/26524307/viewspace-1061600/
归档日志
[oracle@mylinux arch]$ ll
total 2876
-rw-r----- 1 oracle oinstall 555520 Nov 7 22:04 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Nov 7 22:04 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 824320 Nov 7 22:04 1_5_797547884.dbf
先做一个冷备
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_04/*.log .
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_04/*.ctl .
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_04/*.dbf .
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
Database mounted.
Database opened.
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 5 INACTIVE YES
2 6 CURRENT NO
3 3 INACTIVE YES
4 4 INACTIVE YES
此时假如控制文件丢失，我们重新创建控制文件，且以resetlogs打开数据库
以下列语句创建控制文件（注意是resetlogs）
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf',
'/u01/oradata/orcl/test01.dbf'
CHARACTER SET AL32UTF8
;
注意以resetlogs方式重新创建控制文件之后，必须使用using backup controlfile来恢复数据库，即使控制文件的SCN与数据文件的SCN是一样的
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 2250114 generated at 11/10/2012 14:45:24 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_6_797547884.dbf
ORA-00280: change 2250114 for thread 1 is in sequence #6
Specify log: {
/u01/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.test01;--数据已经完全恢复
ID NAME
---------- ----------
1 a
2 b
由于我们是以resetlogs打开数据库，所以会归档之前的current联机重做日志，即1_6_797547884.dbf
(
[oracle@mylinux arch]$ ll
total 4668
-rw-r----- 1 oracle oinstall 555520 Nov 10 14:52 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Nov 10 14:52 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 1461248 Nov 10 14:52 1_5_797547884.dbf
-rw-r----- 1 oracle oinstall 1194496 Nov 10 14:52 1_6_797547884.dbf
)
而且新开一个incarnation
SQL> select incarnation#,status,resetlogs_id from v$database_incarnation;
INCARNATION# STATUS RESETLOGS_ID
------------ ------- ------------
1 PARENT 797547884
2 CURRENT 798996264
在alert_orcl.log里面我们发现resetlogs的时间点
ALTER DATABASE RECOVER database using backup controlfile
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Sat Nov 10 15:23:59 2012
ALTER DATABASE RECOVER LOGFILE '/u01/oradata/orcl/redo02.log'
Media Recovery Log /u01/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER LOGFILE '/u01/oradata/orcl/redo02.log'
Sat Nov 10 15:24:24 2012
alter database open resetlogs --------------------------------------------------------------------这时候开始resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Archived Log entry 1 added for thread 1 sequence 5 ID 0x4efe86b2 dest 1:
Archived Log entry 2 added for thread 1 sequence 6 ID 0x4efe86b2 dest 1:
Archived Log entry 3 added for thread 1 sequence 3 ID 0x4efe86b2 dest 1:
Archived Log entry 4 added for thread 1 sequence 4 ID 0x4efe86b2 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 2249134
Resetting resetlogs activation ID 1325303474 (0x4efe86b2)
Sat Nov 10 15:24:46 2012
Setting recovery target incarnation to 2
Sat Nov 10 15:24:46 2012
Assigning activation ID 1326743407 (0x4f147f6f)
LGWR: STARTING ARCH PROCESSES
Sat Nov 10 15:24:46 2012
ARC0 started with pid=18, OS id=6994
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Nov 10 15:24:47 2012
ARC1 started with pid=19, OS id=7011
ARC1: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/oradata/orcl/redo01.log
Successful open of redo thread 1
Sat Nov 10 15:24:48 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Nov 10 15:24:48 2012
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Nov 10 15:24:53 2012
QMNC started with pid=20, OS id=7015
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sat Nov 10 15:25:00 2012
Completed: alter database open resetlogs---------------------------------------------------------------这时候结束resetlogs
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 0 UNUSED YES
3 0 UNUSED YES
4 0 UNUSED YES
SQL> insert into hr.test01 values(3,'c');--现在我再对test01表做一些变更
1 row created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
(
[oracle@mylinux arch]$ ll
total 4408
-rw-r----- 1 oracle oinstall 602112 Nov 10 15:28 1_1_798996264.dbf----所以id=3这条数据在这个归档日志里，新的incarnation号
-rw-r----- 1 oracle oinstall 555520 Nov 10 15:24 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Nov 10 15:24 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 1461248 Nov 10 15:24 1_5_797547884.dbf
-rw-r----- 1 oracle oinstall 320512 Nov 10 15:24 1_6_797547884.dbf
)
SQL> alter system checkpoint;
System altered.
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE YES
2 2 CURRENT NO
3 0 UNUSED YES
4 0 UNUSED YES
SQL> insert into hr.test01 values(4,'d');--id=4这条数据在/u01/oradata/orcl/redo02.log里面
1 row created.
SQL> commit;
Commit complete.
下面我再做一个冷备
shutdown immediate
[oracle@mylinux coldbackup]$ mkdir 20121027_05
[oracle@mylinux coldbackup]$ cd 20121027_05
[oracle@mylinux 20121027_05]$ ll
total 0
[oracle@mylinux 20121027_05]$ cp /u01/oradata/orcl/*.ctl .
[oracle@mylinux 20121027_05]$ cp /u01/oradata/orcl/*.log .
[oracle@mylinux 20121027_05]$ cp /u01/oradata/orcl/*.dbf .
下面模拟控制文件丢失，且新数据文件test01.dbf丢失的情况，
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ rm test01.dbf
下面我开始重建创建控制文件，能否用noresetlogs，试试看
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf',
'/u01/oradata/orcl/test01.dbf' ----其实这句需要去掉的，因为test01.dbf已丢失，此时没有test01.dbf
CHARACTER SET AL32UTF8
;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 CREATE
CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/oradata/orcl/test01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> STARTUP NOMOUNT
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> 2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
11 GROUP 4 (
12 '/u01/oradata/orcl/redo04.log',
13 '/u01/oradata/orcl/redo04b.log'
14 ) SIZE 5M BLOCKSIZE 512
15 DATAFILE
16 '/u01/oradata/orcl/system01.dbf',
17 '/u01/oradata/orcl/sysaux01.dbf',
18 '/u01/oradata/orcl/undotbs01.dbf',
19 '/u01/oradata/orcl/users01.dbf',
20 '/u01/oradata/orcl/example01.dbf',
21 '/u01/oradata/orcl/tbsmn01.dbf',
22 '/u01/oradata/orcl/tbsmn02.dbf',
23 '/u01/oradata/orcl/manualsegs01.dbf',
24 '/u01/oradata/orcl/autosegs01.dbf',
25 '/u01/oradata/orcl/rman01.dbf',
26 '/u01/oradata/orcl/hello.dbf'
27 CHARACTER SET AL32UTF8
28 ;
Control file created.
SQL> recover database;--不行，这样不能达到恢复test01.dbf的目的
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover database using backup controlfile;--这样呢？因为数据文件和控制文件现在是最新的啦，在最新的情况下，还要认定此时的控制文件是旧的控
制文件，那么Oracle找归档日志肯定是找比当前的还新的归档日志了，那就是下一个还没有归档的，即1_2_798996264.dbf，哎，这种方式也行不通
ORA-00279: change 2250087 generated at 11/10/2012 15:31:37 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_2_798996264.dbf
ORA-00280: change 2250087 for thread 1 is in sequence #2
Specify log: {
ORA-00308: cannot open archived log '/u01/arch/1_2_798996264.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
重新删除控制文件后
SQL> STARTUP NOMOUNT
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG --使用resetlogs来创建控制文件
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf'
CHARACTER SET AL32UTF8
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 ;
Control file created.
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;--还是不行的，不能读到以前的归档日志文件，因为新建的控制文件的SCN比较新
ORA-00279: change 2250087 generated at 11/10/2012 15:31:37 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_2_798996264.dbf
ORA-00280: change 2250087 for thread 1 is in sequence #2
Specify log: {
ORA-00308: cannot open archived log '/u01/arch/1_2_798996264.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover tablespace test;--这个不行，因为此时数据库没有打开，Oracle是不知道test这个表空间的
ORA-00959: tablespace 'TEST' does not exist
SQL> recover datafile--也不能使用这个，因为控制文件中现在都没包含test01.dbf这个数据文件啊
现在的想法是，要么使用之前备份的控制文件或还原一个较早的数据文件备份，还是使用备份控制文件比较现实
重新删除控制文件，还原20121027_02的控制文件(在resetlogs之前)
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl .
SQL> startup --但是此时Oracle发现数据文件(在resetlogs之后)和控制文件(在resetlogs之前)是属于不同的incarnation的，不行哦
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> recover database using backup controlfile until time '2012-11-10 15:24:24';--"2012-11-10 15:24:24"是开始resetlogs的点，还是不行，我觉得还
是要用一个备份的数据文件
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
下面我还原一个20121027_02的数据文件
也就是
[oracle@mylinux arch]$ ll
total 4408
-rw-r----- 1 oracle oinstall 602112 Nov 10 15:28 1_1_798996264.dbf
-rw-r----- 1 oracle oinstall 555520 Nov 10 15:24 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Nov 10 15:24 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 1461248 Nov 10 15:24 1_5_797547884.dbf
-rw-r----- 1 oracle oinstall 320512 Nov 10 15:24 1_6_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_05/*.log .--最新
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl .--resetlogs之前的备份
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf .--resetlogs之前的备份
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4880
Session ID: 1 Serial number: 5
alert日志如下：
[root@mylinux trace]# tail -f alert_orcl.log
ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo01.log'
Errors in file /u01/diag/rdbms/orcl/orcl/trace/orcl_ora_4880.trc:
ORA-00338: log 1 of thread is more recent than control file
ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo01.log'
USER (ospid: 4880): terminating the instance due to error 338
Sat Nov 10 20:38:10 2012
Errors in file /u01/diag/rdbms/orcl/orcl/trace/orcl_m000_4901.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo01.log'
Instance terminated by USER, pid = 4880
下面进行恢复，需要退出sqlplus重新进
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 12: '/u01/oradata/orcl/test01.dbf' --因为加过一个数据文件
ORA-01112: media recovery not started
SQL> alter database create datafile 12 as '/u01/oradata/orcl/test01.dbf';--重新创建test01.dbf
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 2248158 generated at 10/31/2012 20:25:41 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2248158 for thread 1 is in sequence #5
Specify log: {
ORA-00279: change 2248722 generated at 10/31/2012 20:29:09 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_6_797547884.dbf
ORA-00280: change 2248722 for thread 1 is in sequence #6
ORA-00278: log file '/u01/arch/1_5_797547884.dbf' no longer needed for this
recovery
Specify log: {
Log applied.
Media recovery complete.
--至此，resetlogs之前的归档日志，恢复完毕
下面关闭数据库，并把当前的控制文件删除掉，下面我以noresetlogs方式来重建控制文件
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf',
'/u01/oradata/orcl/test01.dbf'
CHARACTER SET AL32UTF8
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 ;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01517: log member: '/u01/oradata/orcl/redo01.log'
--此时的情况，数据文件还是之前的incarnation，但是联机重做日志是现在的incarnation，
在resetlogs前后没有备份控制文件的情况（也就是通过重新创建控制文件的方式）如何实现跨resetlogs的恢复？？？暂时没找到答案
现在我就把20121027_05中的控制文件拷过来恢复看看
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
Database mounted.
SQL> recover database;--会自动应用resetlogs之后的归档日志和联机重做日志文件，但是这里为什么会提示1_6_797547884.dbf？1_6_797547884.dbf在上
面已经应用过了，不过虽然都提示1_6_797547884.dbf，但是提示的change是不一样的
ORA-00279: change 2249133 generated at 10/31/2012 20:33:16 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_6_797547884.dbf
ORA-00280: change 2249133 for thread 1 is in sequence #6
Specify log: {
Log applied.
Media recovery complete.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open;
Database altered.
SQL> select * from hr.test01;--成功恢复
ID NAME
---------- ----------
1 a
2 b
3 c
4 d
现在采用的方法是，使用resetlogs前和resetlogs后的备份控制文件进行恢复，如果没有备份控制文件，使用创建控制文件的方法能不能实现恢复呢？目前没找
到方法。
而对于后来有新加数据文件的情况，而最后丢失的是新加的数据文件，其他数据文件是最新的情况，
是无法使用最新的数据文件和最新的联机重做日志文件，重新创建控制文件的方法来进行恢复的，
因为重新创建的控制文件是根据联机重做日志文件的SCN来的，这样在恢复的时候就会判断数据文件和控制文件的SCN一致，不需要进行恢复
而对于后来有新加数据文件的情况，而最后丢失的是新加的数据文件，其他数据文件是最新的情况，
是否可以使用备份控制文件使用using backup controlfile来恢复呢？
应该是可以的，这样就可以应用包含创建数据文件的联机重做日志了，但是如果有resetlogs的情况，而新增的数据文件又是在resetlogs之前新增的，这样可能
不行，
因为数据文件和联机重做日志文件是新的incarnation，备份控制文件是旧的incarnation，这样可能会有问题
而对于后来有新加数据文件的情况，而最后丢失的是新加的数据文件，其他数据文件是最新的情况，
如果中间没有过resetlogs，且是用重新创建控制文件的方法来进行恢复，可以考虑还原较早以前的数据文件版本（在新加数据文件之前的），这样也可以达到应
用包含创建数据文件的联机重做日志文件的目的
下面再试下：
[oracle@mylinux arch]$ ll
total 4408
-rw-r----- 1 oracle oinstall 602112 Nov 10 15:28 1_1_798996264.dbf
-rw-r----- 1 oracle oinstall 555520 Nov 10 15:24 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Nov 10 15:24 1_4_797547884.dbf
-rw-r----- 1 oracle oinstall 1461248 Nov 10 15:24 1_5_797547884.dbf
-rw-r----- 1 oracle oinstall 320512 Nov 10 15:24 1_6_797547884.dbf
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_05/*.log .--最新
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.dbf .--resetlogs之前的备份
没有resetlogs之前和之后的备份控制文件，但是数据文件是resetlogs之前的备份
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG---------------------------------------------NORESETLOGS
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf' ------------------------------------------未包含test01.dbf
CHARACTER SET AL32UTF8;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 CREATE CONTROLFILE REUSE
DATABASE "ORCL" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01517: log member: '/u01/oradata/orcl/redo01.log'
其实重新创建resetlogs之前的控制文件不能用noresetlogs，因为你需要使用数据文件的SCN来进行恢复，这样就需要create controlfile resetlogs
SQL> STARTUP NOMOUNT
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG---------------------------------------------RESETLOGS
MAXLOGFILES 16
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> 2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
11 GROUP 4 (
12 '/u01/oradata/orcl/redo04.log',
13 '/u01/oradata/orcl/redo04b.log'
14 ) SIZE 5M BLOCKSIZE 512
15 DATAFILE
16 '/u01/oradata/orcl/system01.dbf',
17 '/u01/oradata/orcl/sysaux01.dbf',
18 '/u01/oradata/orcl/undotbs01.dbf',
19 '/u01/oradata/orcl/users01.dbf',
20 '/u01/oradata/orcl/example01.dbf',
21 '/u01/oradata/orcl/tbsmn01.dbf',
22 '/u01/oradata/orcl/tbsmn02.dbf',
23 '/u01/oradata/orcl/manualsegs01.dbf',
24 '/u01/oradata/orcl/autosegs01.dbf',
25 '/u01/oradata/orcl/rman01.dbf',
26 '/u01/oradata/orcl/hello.dbf'------------------------------------------未包含test01.dbf
27 CHARACTER SET AL32UTF8;
Control file created.
SQL> col name for a50
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected.
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2246274
/u01/oradata/orcl/sysaux01.dbf 2246274
/u01/oradata/orcl/undotbs01.dbf 2246274
/u01/oradata/orcl/users01.dbf 2246274
/u01/oradata/orcl/example01.dbf 2246274
/u01/oradata/orcl/tbsmn01.dbf 2246274
/u01/oradata/orcl/tbsmn02.dbf 2246274
/u01/oradata/orcl/manualsegs01.dbf 2246274
/u01/oradata/orcl/autosegs01.dbf 2246274
/u01/oradata/orcl/rman01.dbf 2246274
/u01/oradata/orcl/hello.dbf 2246274
11 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:06:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4
Specify log: {
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery
Specify log: {
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 12: '/u01/oradata/orcl/test01.dbf'
ORA-01112: media recovery not started
SQL> alter database create datafile 12 as '/u01/oradata/orcl/test01.dbf';
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 2248158 generated at 10/31/2012 20:25:41 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2248158 for thread 1 is in sequence #5
Specify log: {
ORA-00279: change 2248722 generated at 10/31/2012 20:29:09 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_6_797547884.dbf
ORA-00280: change 2248722 for thread 1 is in sequence #6
ORA-00278: log file '/u01/arch/1_5_797547884.dbf' no longer needed for this
recovery
Specify log: {
Log applied.
Media recovery complete.
--resetlogs之前的恢复结束
关闭数据库，删除当前的控制文件
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
'/u01/oradata/orcl/redo04.log',
'/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/orcl/manualsegs01.dbf',
'/u01/oradata/orcl/autosegs01.dbf',
'/u01/oradata/orcl/rman01.dbf',
'/u01/oradata/orcl/hello.dbf',
'/u01/oradata/orcl/test01.dbf'
CHARACTER SET AL32UTF8;
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 381683592 bytes
Database Buffers 109051904 bytes
Redo Buffers 5922816 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 CREATE CONTROLFILE
REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01517: log member: '/u01/oradata/orcl/redo01.log'
SQL> STARTUP NOMOUNT
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
11 GROUP 4 (
12 '/u01/oradata/orcl/redo04.log',
13 '/u01/oradata/orcl/redo04b.log'
14 ) SIZE 5M BLOCKSIZE 512
15 DATAFILE
16 '/u01/oradata/orcl/system01.dbf',
17 '/u01/oradata/orcl/sysaux01.dbf',
18 '/u01/oradata/orcl/undotbs01.dbf',
19 '/u01/oradata/orcl/users01.dbf',
20 '/u01/oradata/orcl/example01.dbf',
21 '/u01/oradata/orcl/tbsmn01.dbf',
22 '/u01/oradata/orcl/tbsmn02.dbf',
23 '/u01/oradata/orcl/manualsegs01.dbf',
24 '/u01/oradata/orcl/autosegs01.dbf',
25 '/u01/oradata/orcl/rman01.dbf',
26 '/u01/oradata/orcl/hello.dbf',
27 '/u01/oradata/orcl/test01.dbf'
28 CHARACTER SET AL32UTF8;
Control file created.
--其实这步resetlogs出来的控制文件跟我删除之前的是一样的，跟数据文件的SCN是一样的，这样估计不能成功恢复resetlogs之后的
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2249133
/u01/oradata/orcl/sysaux01.dbf 2249133
/u01/oradata/orcl/undotbs01.dbf 2249133
/u01/oradata/orcl/users01.dbf 2249133
/u01/oradata/orcl/example01.dbf 2249133
/u01/oradata/orcl/tbsmn01.dbf 2249133
/u01/oradata/orcl/tbsmn02.dbf 2249133
/u01/oradata/orcl/manualsegs01.dbf 2249133
/u01/oradata/orcl/autosegs01.dbf 2249133
/u01/oradata/orcl/rman01.dbf 2249133
/u01/oradata/orcl/hello.dbf 2249133
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/test01.dbf 2249133
12 rows selected.
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf 2249133
/u01/oradata/orcl/sysaux01.dbf 2249133
/u01/oradata/orcl/undotbs01.dbf 2249133
/u01/oradata/orcl/users01.dbf 2249133
/u01/oradata/orcl/example01.dbf 2249133
/u01/oradata/orcl/tbsmn01.dbf 2249133
/u01/oradata/orcl/tbsmn02.dbf 2249133
/u01/oradata/orcl/manualsegs01.dbf 2249133
/u01/oradata/orcl/autosegs01.dbf 2249133
/u01/oradata/orcl/rman01.dbf 2249133
/u01/oradata/orcl/hello.dbf 2249133
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/oradata/orcl/test01.dbf 2249133
12 rows selected.
SQL> desc v$database_incarnation;
Name Null? Type
----------------------------------------- -------- ----------------------------
INCARNATION# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
STATUS VARCHAR2(7)
RESETLOGS_ID NUMBER
PRIOR_INCARNATION# NUMBER
FLASHBACK_DATABASE_ALLOWED VARCHAR2(26)
SQL> select incarnation#,status,resetlogs_id from v$database_incarnation;--这时候还是之前的incarnation
INCARNATION# STATUS RESETLOGS_ID
------------ ------- ------------
1 CURRENT 797547884
SQL> recover database using backup controlfile;
ORA-00279: change 2249133 generated at 10/31/2012 20:33:16 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_6_797547884.dbf
ORA-00280: change 2249133 for thread 1 is in sequence #6
Specify log: {
Log applied.
Media recovery complete.
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.
SQL> select * from hr.test01;--看到了吧，不能完全恢复，不能恢复resetlogs之后的
ID NAME
---------- --------------------------------------------------
1 a
2 b
难道一定要有resetlogs之后的备份控制文件，就没有其他办法了吗？