一般备份控制文件的原则是： 在数据库物理结构发生变化后备份控制文件。
这里测试在备份控制文件后， 继续加入了两个数据文件，导致物理结构发生变化。
SQL> insert into test04 values (25,'CMM');
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter database backup controlfile to 'd:\control.bak' ;
数据库已更改。
SQL> insert into test04 values (26,'CMM');
已创建 1 行。
SQL> insert into test04 values (27,'CMM');
已创建 1 行。
SQL> insert into test04 values (28,'CMM');
已创建 1 行。
SQL> commit;
提交完成。
SQL>
SQL> alter tablespace users add datafile 'D:\oracle\product\10.2.0\oradata\test\
USERS02.DBF' size 10M;
表空间已更改。
SQL> insert into test04 values (29,'CMM');
SQL> insert into test04 values (30,'CMM');
SQL> insert into test04 values (31,'CMM');
SQL> COMMIT;
SQL> alter system switch logfile;
系统已更改。
SQL> insert into test04 values (32,'CMM');
SQL> insert into test04 values (33,'CMM');
SQL> insert into test04 values (34,'CMM');
SQL> COMMIT;
SQL> alter system switch logfile;
SQL> alter tablespace users add datafile 'D:\oracle\product\10.2.0\oradata\test\USERS03.DBF' size 10M;
SQL> alter system switch logfile;
SQL> insert into test04 values (35,'CMM');
SQL> insert into test04 values (36,'CMM');
SQL> COMMIT;
测试丢失控制文件。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL01.CTL
SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL02.CTL
SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL03.CTL
SQL>
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 524288000 bytes
Fixed Size 1249920 bytes
Variable Size 159387008 bytes
Database Buffers 356515840 bytes
Redo Buffers 7135232 bytes
ORA-00205: ?????????, ??????, ???????
开始恢复。
SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL01.C
TL
已复制 1 个文件。
SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL02.C
TL
已复制 1 个文件。
SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL03.C
TL
已复制 1 个文件。
SQL>
SQL> recover database;
ORA-00283: ??????????
ORA-01610: ?? BACKUP CONTROLFILE ??????????
SQL> recover database using backup controlfile ;
ORA-00279: ?? 593538 (? 04/18/2011 09:07:24 ??) ???? 1 ????
ORA-00289: ??:
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001
ORA-00280: ?? 593538 (???? 1) ??? #2 ?
指定日志: {
auto
ORA-00283: ??????????
ORA-01244: ????????????????????
ORA-01110: ???? 5: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF'
ORA-01112: ???????
SQL> select name from v$datafile;
NAME
--------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00005
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNA
MED00005' as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF' ;
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 593823 generated at 04/18/2011 09:13:31 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001
ORA-00280: change 593823 for thread 1 is in sequence #2
Specify log: {
auto
ORA-00279: change 593919 generated at 04/18/2011 09:15:49 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748774692.001
ORA-00280: change 593919 for thread 1 is in sequence #3
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001' no longer
needed for this recovery
ORA-00279: change 593978 generated at 04/18/2011 09:18:12 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00004_0748774692.001
ORA-00280: change 593978 for thread 1 is in sequence #4
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748774692.001' no longer
needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS03.DBF'
ORA-01112: media recovery not started
SQL>
SQL> select name from v$datafile;
NAME
----------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00006
6 rows selected.
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00006' as
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS03.DBF' ;
SQL> select file# , CHECKPOINT_CHANGE# from v$datafile; (控制文件中取出)
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 593087
2 593087
3 593087
4 593087
5 594564
6 594564
6 rows selected.
SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header; (数据文件头中取出)
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 594729
2 594729
3 594729
4 594729
5 594564
6 594564
6 rows selected.
SQL> recover database using backup controlfile ;
ORA-00279: change 594564 generated at 04/18/2011 09:19:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001
ORA-00280: change 594564 for thread 1 is in sequence #5
Specify log: {
auto
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001'
ORA-27041: unable to open file
OSD-04002: ？???？？?
O/S-Error: (OS 2) ？？？????？???？？???
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001'
ORA-27041: unable to open file
OSD-04002: ？???？？?
O/S-Error: (OS 2) ？？？????？???？？???
找不到最后一个归档文件(其实是需要在线日志来恢复)。手工apple online log .
SQL> recover database using backup controlfile ;
ORA-00279: change 594564 generated at 04/18/2011 09:19:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001
ORA-00280: change 594564 for thread 1 is in sequence #5
Specify log: {
D:\oracle\product\10.2.0\oradata\test\REDO01.LOG
Log applied.
Media recovery complete.
SQL>
SQL> select file# , CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 593087
2 593087
3 593087
4 593087
5 594729
6 594729
6 rows selected.
SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header; (数据文件中scn都一致了)
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 594729
2 594729
3 594729
4 594729
5 594729
6 594729
6 rows selected.
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 file# , CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 594731
2 594731
3 594731
4 594731
5 594731
6 594731
6 rows selected.
SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 594731
2 594731
3 594731
4 594731
5 594731
6 594731
6 rows selected.
SQL>
SQL> select * from tony.test04;
ID DEPT
---------- --------------------
20 CMM
21 CMM
22 CMM
23 CMM
24 CMM
25 CMM
26 CMM
27 CMM
28 CMM
29 CMM
30 CMM
31 CMM
32 CMM
33 CMM
ID DEPT
---------- --------------------
34 CMM
35 CMM
36 CMM
17 rows selected.
SQL>
可以看到是完全恢复，虽然使用了resetlogs打开数据库。
最后全备数据库。