» 关于控制文件的恢复 OracleBlog.cn -天堂向左 DBA向右

关于控制文件的恢复

· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/backup-recover/on-controlfile-recovery/

我们很多不太重要的数据库（如测试库）都是工作在noarchive模式，下面我们就来谈谈，在noarchive模式下一些文件丢失的恢复：

关于控制文件的恢复，必须要有控制文件的备份（to trace备份或者to XX路径的备份），或者控制文件不是完全丢失——即存在至少一个控制文件。
条件：
1.shutdown immediate
2.控制文件全部丢失
3.redolog、数据文件正常
4.存在控制文件的备份（曾经备份的控制文件，或者backup to trace的控制文件，或者至少存在一个未丢失的控制文件）

情况1：控制文件完全丢失，但是redolog中的文件没有完全被覆盖：

sys@ORALOCAL(192.168.0.12)> startup
ORACLE instance started.

Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 109051904 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:01.63
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
   5792350

Elapsed: 00:00:05.32
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:01.72
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07
   2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07
   3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07

Elapsed: 00:00:00.92
sys@ORALOCAL(192.168.0.12)> --CHECKPOINT_CHANGE#:5792350 and FIRST_CHANGE#_CURRENT:5792177
sys@ORALOCAL(192.168.0.12)>--this mean last logfile change at scn 5792177,but system current scn is 5792350
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to 'd:\cfile.ctl';

Database altered.

Elapsed: 00:00:01.49
sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to trace as 'd:\cfile.txt';

Database altered.

Elapsed: 00:00:01.47
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:00.47
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07
   2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07
   3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07

Elapsed: 00:00:00.60
sys@ORALOCAL(192.168.0.12)> --this time ,we backup controlfile at checkpoint_change# 5792350,
sys@ORALOCAL(192.168.0.12)> --and the log file is group 2
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> alter system checkpoint;

System altered.

Elapsed: 00:00:02.96
sys@ORALOCAL(192.168.0.12)> alter system switch logfile;

System altered.

Elapsed: 00:00:00.92
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07
   2 1 6 10485760 3 NO INACTIVE 5792177 30-OCT-07
   3 1 7 10485760 3 NO CURRENT 5793848 30-OCT-07

Elapsed: 00:00:00.65
sys@ORALOCAL(192.168.0.12)> --this time logfile switch to group 3,but group 2 still not be resue,
sys@ORALOCAL(192.168.0.12)> -- and we shutdown immediate database and remove all controlfile
sys@ORALOCAL(192.168.0.12)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> --remove all controlfile
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> startup
ORACLE instance started.

Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 109051904 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL01.CTL
sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL02.CTL
sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL03.CTL
sys@ORALOCAL(192.168.0.12)> --we cp the backup file'd:\cfile.ctl' to controlfile
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.12)> startup
ORACLE instance started.

Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 109051904 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

sys@ORALOCAL(192.168.0.12)> --database mounted but not open
sys@ORALOCAL(192.168.0.12)> --check the scn
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:00.51
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
   5792350

Elapsed: 00:00:02.64
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   5794376

Elapsed: 00:00:03.49
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> --CHECKPOINT_CHANGE# <> LAST_CHANGE#, need media recover
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07
   2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07
   3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07

Elapsed: 00:00:00.70
sys@ORALOCAL(192.168.0.12)> --v$datafile_header.CHECKPOINT_CHANGE# <> v$datafile.CHECKPOINT_CHANGE# too ,
sys@ORALOCAL(192.168.0.12)> --need instance recover
sys@ORALOCAL(192.168.0.12)> --first to media recover
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> select group#,member from v$logfile order by 1,2;

   GROUP#
----------
MEMBER
----------------------------------------------------------------------------------------------------
   1
D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG

   1
D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG

   1
D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG

   2
D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG

   2
D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG

   2
D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG

   3
D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG

   3
D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG

   3
D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG

9 rows selected.

Elapsed: 00:00:04.55
sys@ORALOCAL(192.168.0.12)> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC
ORA-00280: change 5792350 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG
ORA-00326: log begins at change 5793848, need earlier change 5792350
ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC
ORA-00280: change 5792350 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG
ORA-00328: archived log ends at change 5792176, need later change 5792350
ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC
ORA-00280: change 5792350 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG
ORA-00279: change 5793848 generated at 10/30/2007 00:30:33 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00007.ARC
ORA-00280: change 5793848 for thread 1 is in sequence #7
ORA-00278: log file 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG
Log applied.
Media recovery complete.
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> --media recover finish here
sys@ORALOCAL(192.168.0.12)> --check if need instance recover
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:00.49
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
   5792350 5794375

Elapsed: 00:00:02.51
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   5794376

Elapsed: 00:00:03.72
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5792350

Elapsed: 00:00:00.47
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
   5792350 5794375

Elapsed: 00:00:02.56
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   5794376

Elapsed: 00:00:03.83
sys@ORALOCAL(192.168.0.12)> alter database open resetlogs;

Database altered.

Elapsed: 00:01:01.34
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> --check scn again
sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
   5794377

Elapsed: 00:00:00.57
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
   5794377

Elapsed: 00:00:05.70
sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   5794377

Elapsed: 00:00:01.80
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 0 10485760 3 YES UNUSED 0
   2 1 1 10485760 3 NO CURRENT 5794376 30-OCT-07
   3 1 0 10485760 3 YES UNUSED 0

Elapsed: 00:00:00.79
sys@ORALOCAL(192.168.0.12)>

情况2：控制文件完全丢失，但是redolog中的文件已经被完全被覆盖：

sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to 'd:\cfile.ctl';

Database altered.

Elapsed: 00:00:01.01
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO CURRENT 5795700 30-OCT-07
   2 1 4 10485760 3 NO INACTIVE 5795630 30-OCT-07
   3 1 3 10485760 3 NO INACTIVE 5795627 30-OCT-07

Elapsed: 00:00:00.09
sys@ORALOCAL(192.168.0.12)> alter system switch logfile;

System altered.

Elapsed: 00:00:00.86
sys@ORALOCAL(192.168.0.12)> /

System altered.

Elapsed: 00:00:00.92
sys@ORALOCAL(192.168.0.12)> /

System altered.

Elapsed: 00:00:06.14
sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 8 10485760 3 NO CURRENT 5795958 30-OCT-07
   2 1 7 10485760 3 NO INACTIVE 5795954 30-OCT-07
   3 1 6 10485760 3 NO INACTIVE 5795952 30-OCT-07

Elapsed: 00:00:00.69
sys@ORALOCAL(192.168.0.12)> alter system switch logfile;

System altered.

Elapsed: 00:00:00.93
sys@ORALOCAL(192.168.0.12)> /

System altered.

Elapsed: 00:00:01.11
sys@ORALOCAL(192.168.0.12)> /

System altered.

Elapsed: 00:00:00.94
sys@ORALOCAL(192.168.0.12)> /

System altered.

Elapsed: 00:00:06.62
sys@ORALOCAL(192.168.0.12)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> startup
ORACLE instance started.

Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 109051904 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted

Elapsed: 00:00:00.16
sys@ORALOCAL(192.168.0.12)> alter database mount;

Database altered.

Elapsed: 00:00:04.76
sys@ORALOCAL(192.168.0.12)> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC
ORA-00280: change 5795703 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

ORA-01112: media recovery not started

sys@ORALOCAL(192.168.0.12)> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

Elapsed: 00:00:02.27
sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC
ORA-00280: change 5795703 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

ORA-01112: media recovery not started

sys@ORALOCAL(192.168.0.12)> select * from v$log;

   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   1 1 5 10485760 3 NO CURRENT 5795700 30-OCT-07
   2 1 4 10485760 3 NO INACTIVE 5795630 30-OCT-07
   3 1 3 10485760 3 NO INACTIVE 5795627 30-OCT-07

Elapsed: 00:00:00.71
sys@ORALOCAL(192.168.0.12)> select member from v$logfile
2 /

MEMBER
----------------------------------------------------------------------------------------------------
D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG
D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG

9 rows selected.

Elapsed: 00:00:02.70
sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC
ORA-00280: change 5795703 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG
ORA-00326: log begins at change 5796041, need earlier change 5795703
ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC
ORA-00280: change 5795703 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG
ORA-00326: log begins at change 5796035, need earlier change 5795703
ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel;
ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC
ORA-00280: change 5795703 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG
ORA-00326: log begins at change 5796037, need earlier change 5795703
ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

sys@ORALOCAL(192.168.0.12)> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF'

Elapsed: 00:00:02.47
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>

sys@ORALOCAL(192.168.0.12)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.12)> startup nomount;
ORACLE instance started.

Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 109051904 bytes
Database Buffers 29360128 bytes
Redo Buffers 667648 bytes
sys@ORALOCAL(192.168.0.12)> CREATE CONTROLFILE REUSE DATABASE "ORALOCAL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 50
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 (
9 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG',
10 'D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG',
11 'D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG'
12 ) SIZE 10M,
13 GROUP 2 (
14 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG',
15 'D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG',
16 'D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG'
17 ) SIZE 10M,
18 GROUP 3 (
19 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG',
20 'D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG',
21 'D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG'
22 ) SIZE 10M
23 DATAFILE
24 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF',
25 'D:\ORACLE\ORADATA\ORALOCAL\UNDOTBS01.DBF',
26 'D:\ORACLE\ORADATA\ORALOCAL\CWMLITE01.DBF',
27 'D:\ORACLE\ORADATA\ORALOCAL\DRSYS01.DBF',
28 'D:\ORACLE\ORADATA\ORALOCAL\EXAMPLE01.DBF',
29 'D:\ORACLE\ORADATA\ORALOCAL\INDX01.DBF',
30 'D:\ORACLE\ORADATA\ORALOCAL\ODM01.DBF',
31 'D:\ORACLE\ORADATA\ORALOCAL\TOOLS01.DBF',
32 'D:\ORACLE\ORADATA\ORALOCAL\USERS01.DBF',
33 'D:\ORACLE\ORADATA\ORALOCAL\XDB01.DBF',
34 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN08.DBF',
35 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN06.DBF',
36 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN05.DBF',
37 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT07.DBF',
38 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT06.DBF',
39 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT04.DBF',
40 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT03.DBF',
41 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT02.DBF',
42 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT01.DBF',
43 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR04.DBF',
44 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR03.DBF',
45 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR02.DBF',
46 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN04.DBF',
47 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN03.DBF',
48 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_STATIC.DBF',
49 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR04.DBF',
50 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR01.DBF',
51 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN07.DBF',
52 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_CDR.DBF',
53 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_ADMIN.DBF',
54 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HIS02.DBF',
55 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN02.DBF',
56 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN01.DBF',
57 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT08.DBF',
58 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT05.DBF',
59 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR03.DBF',
60 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR02.DBF',
61 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR01.DBF',
62 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN08.DBF',
63 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN07.DBF',
64 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN06.DBF',
65 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN05.DBF',
66 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN04.DBF',
67 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN03.DBF',
68 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN02.DBF',
69 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN01.DBF',
70 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_STATIC.DBF',
71 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT08.DBF',
72 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT07.DBF',
73 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT06.DBF',
74 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT05.DBF',
75 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT04.DBF',
76 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT03.DBF',
77 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT02.DBF',
78 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT01.DBF',
79 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT.DBF',
80 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_PORTAL.DBF',
81 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HISTORY.DBF',
82 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HIS01.DBF',
83 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_DYNAMIC.DBF',
84 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_CDR.DBF',
85 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_ADMIN.DBF',
86 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_HISTORY.DBF',
87 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_DYNAMIC.DBF',
88 'D:\ORACLE\ORADATA\ORALOCAL\TS_HEJIANMIN_DEFAULT.DBF',
89 'D:\ORACLE\ORADATA\ORALOCAL\TS_DD.DBF'
90 CHARACTER SET ZHS16GBK
91 ;

Control file created.

Elapsed: 00:00:12.07
sys@ORALOCAL(192.168.0.12)> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

Elapsed: 00:00:00.58
sys@ORALOCAL(192.168.0.12)> alter database open;

Database altered.

Elapsed: 00:00:30.82
sys@ORALOCAL(192.168.0.12)>
sys@ORALOCAL(192.168.0.12)>

总结：在noarchive模式，控制文件丢失的情况下，如果redolog没有完全被覆盖（即替换的或者剩余的控制文件的信息仍在其中一个redolog中），可以用redolog中的信息做介质恢复到当前，不会有数据丢失。
如果redolog完全被覆盖（即替换的文件或者剩余的控制文件的信息已经不在redolog中，信息已经被覆盖，redolog被重复使用了），此时要重建控制文件。

· 【文章发布信息】发表于： 2007-10-30 @ 23:30:24 · ||分类： Backup & recover

OracleBlog.cn -天堂向左 DBA向右

关于控制文件的恢复

留条评论

ACOUG Member

分类

标签云

搜索

随机文章

最新回复

归档

订阅和更新

网站管理

访客位置分布

知识共享许可