用备份控制文件做不完全恢复下的完全恢复（全备<老>--备份控制文件<次新>--新建表空间andy--日志文件<新>）
为什么会使用备份的控制文件? 实际工作中主要有两种情况：
第一种：当前控制文件全部损坏，而数据文件备份，控制文件备份及当前日志处于不同SCN版本，它们之间又增加过表空间（数据文件）。
第二种：当前控制文件没有损坏，但想要恢复被删除的表空间。
实验2 ：全备<老>--备份控制文件<次新>--新建表空间andy--日志文件<新>
分析说明： 整个恢复过程中datafile结构有了变化，变化发生在备份控制文件之后，新增了表空间andy,控制文件备份里没有此表空间记录，但日志里有。
1）准备环境
--生成要备份的数据文件的命令
SQL> select 'ho cp ' || name || ' /home/oracle/coldbak' from v$datafile;
'HOCP'||NAME||'/HOME/ORACLE/COLDBAK'
-------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak
6 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to '/home/oracle/coldbak/ctl01.bak';
Database altered.
SQL> create tablespace andy datafile '/home/oracle/app/oradata/orcl/andy01.dbf' size 1m;
Tablespace created.
SQL> create table andy.andy(id int) tablespace andy;
Table created.
SQL> insert into andy.andy values (100);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from andy.andy;
ID
----------
100
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 1 52428800 512 1 NO CURRENT
2 1 0 52428800 512 1 YES UNUSED
3 1 0 52428800 512 1 YES UNUSED
2）模拟新建数据文件损坏
[oracle@11g orcl]$ rm -rf andy01.dbf
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from andy.andy;
select * from andy.andy
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
3) 关闭数据库
SQL> shutdown immediate;
4）还原所有数据文件，以老控制文件替换当前控制文件
[oracle@11g orcl]$ rm -rf *.dbf
[oracle@11g orcl]$ rm -rf /home/oracle/app/oradata/orcl/control01.ctl
[oracle@11g orcl]$ rm -rf /home/oracle/app/flash_recovery_area/orcl/control02.ctl
[oracle@11g coldbak]$ cp ctl01.bak /home/oracle/app/oradata/orcl/control01.ctl
[oracle@11g coldbak]$ cp ctl01.bak /home/oracle/app/flash_recovery_area/orcl/control02.ctl
[oracle@11g coldbak]$ cp *.dbf /home/oracle/app/oradata/orcl/
5）启动数据库
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ---------------------------------------------------
1 2036796 /home/oracle/app/oradata/orcl/system01.dbf
2 2036796 /home/oracle/app/oradata/orcl/sysaux01.dbf
3 2036796 /home/oracle/app/oradata/orcl/undotbs01.dbf
4 2036796 /home/oracle/app/oradata/orcl/users01.dbf
5 2036796 /home/oracle/app/oradata/orcl/tbtb01.dbf
6 2036796 /home/oracle/app/oradata/orcl/ogg01.dbf
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2035583
2 2035583
3 2035583
4 2035583
5 2035583
6 2035583
6 rows selected.
6）使用备份控制文件恢复数据库
SQL> recover database using backup controlfile;
ORA-00279: change 2036793 generated at 12/12/2014 05:56:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866095003.dbf
ORA-00280: change 2036793 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo01.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 7: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-01112: media recovery not started
SQL> select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ------------------------------------------------------------
1 2036796 /home/oracle/app/oradata/orcl/system01.dbf
2 2036796 /home/oracle/app/oradata/orcl/sysaux01.dbf
3 2036796 /home/oracle/app/oradata/orcl/undotbs01.dbf
4 2036796 /home/oracle/app/oradata/orcl/users01.dbf
5 2036796 /home/oracle/app/oradata/orcl/tbtb01.dbf
6 2036796 /home/oracle/app/oradata/orcl/ogg01.dbf
7 2039190 /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED0
0007 <-老控制文件不知道之后的andy01.dbf
7 rows selected.
说明：老控制文件不知道之后的andy01.dbf
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2039192
2 2039192
3 2039192
4 2039192
5 2039192
6 2039192
7 0
7 rows selected.
7）重命名数据文件
SQL> alter database create datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007' as '/home/oracle/app/oradata/orcl/andy01.dbf';
Database altered.
说明：上面自动完成了两个动作1）加了一个数据文件andy01.dbf，2）重命名控制文件UNNAMED00007为andy01.dbf
SQL> select file#,checkpoint_change#,name from v$datafile;
FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ -----------------------------------------------
1 2036796 /home/oracle/app/oradata/orcl/system01.dbf
2 2036796 /home/oracle/app/oradata/orcl/sysaux01.dbf
3 2036796 /home/oracle/app/oradata/orcl/undotbs01.dbf
4 2036796 /home/oracle/app/oradata/orcl/users01.dbf
5 2036796 /home/oracle/app/oradata/orcl/tbtb01.dbf
6 2036796 /home/oracle/app/oradata/orcl/ogg01.dbf
7 2039190 /home/oracle/app/oradata/orcl/andy01.dbf
7 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 2039190 generated at 12/12/2014 06:24:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866095003.dbf
ORA-00280: change 2039190 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
8）resetlogs打开数据库
SQL> alter database open resetlogs;
Database altered.
9)验证
SQL> select * from andy.andy;
ID
----------
100
OK，结束。 转载请标明出处。
