用备份控制文件做不完全恢复下的完全恢复（数据文件备份<旧>--新建表空间--控制文件备份<次新>--日志归档文件<新>）

为什么会使用备份的控制文件? 实际工作中主要有两种情况：


第一种：当前控制文件全部损坏，而数据文件备份，控制文件备份及当前日志处于不同SCN版本，它们之间又增加过表空间（数据文件）。
第二种：当前控制文件没有损坏，但想要恢复被删除的表空间。


实验1：  有所有数据文件备份（老）------(新建表空间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> select * from v$tablespace;


       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 LZY                            YES NO  YES
         7 GOLDGATE                       YES NO  YES


7 rows selected.


SQL> create tablespace andy datafile '/home/oracle/app/oradata/orcl/andy01.dbf' size 1m;


Tablespace created.


SQL> select * from andy.andy;
select * from andy.andy
                   *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table andy.andy(id int) tablespace andy;


Table created.


SQL> insert into andy.andy values (1) ;


1 row created.


SQL> commit;


Commit complete.


SQL> select * from andy.andy;


        ID
----------
         1
SQL> alter system switch logfile;


-- 备份控制文件   


SQL> alter database backup controlfile to '/home/oracle/coldbak/ctl01.bak';


Database altered.


2）模拟andy01.dbf损坏


SQL> ho rm -rf /home/oracle/app/oradata/orcl/andy01.dbf


SQL> alter system flush buffer_cache;


System altered.


SQL> select * from andy;
select * from 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;
Database closed.
Database dismounted.
ORACLE instance shut down.


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/


SQL> alter database create datafile '/home/oracle/app/oradata/orcl/andy01.dbf';


Database altered.


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> col name for a50;
SQL> select file#,checkpoint_change#,name from v$datafile;


     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
         1            2036303 /home/oracle/app/oradata/orcl/system01.dbf
         2            2036303 /home/oracle/app/oradata/orcl/sysaux01.dbf
         3            2036303 /home/oracle/app/oradata/orcl/undotbs01.dbf
         4            2036303 /home/oracle/app/oradata/orcl/users01.dbf
         5            2036303 /home/oracle/app/oradata/orcl/tbtb01.dbf
         6            2036303 /home/oracle/app/oradata/orcl/ogg01.dbf
         7            2036424 /home/oracle/app/oradata/orcl/andy01.dbf


7 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
         7                  0


7 rows selected.
说明：
a）file7 在控制文件里记录是andy01.dbf,而与之对应的数据文件3是不存在的
b）备份的数据备份的scn比控制文件scn的还老。


5)使用备份控制文件恢复


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/app/oradata/orcl/andy01.dbf'


说明：
此错是因为老备份里没有andy表空间，但只要控制文件里记录了andy就好办，方法是建一个datafile的空文件，而其中内容可由日志文件recover(前滚)时填补出来。


SQL> alter database create datafile '/home/oracle/app/oradata/orcl/andy01.dbf';


Database altered.


--再次使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00279: change 2035583 generated at 12/12/2014 04:53:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_1_866090437.dbf
ORA-00280: change 2035583 for thread 1 is in sequence #1




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2036542 generated at 12/12/2014 05:11:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_2_866090437.dbf
ORA-00280: change 2036542 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/archivelog/1_1_866090437.dbf' no longer
needed for this recovery




ORA-00308: cannot open archived log '/home/oracle/archivelog/1_2_866090437.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


说明：
//archive日志前滚结束了，但当前日志里还有信息需要恢复
//注意: 对于这个例子来说，一定要看清提示：如果提示的不是归档的日志（是当前日志），则要直接要输入filename 不能输入auto，否则open时会失败。


--再次做恢复
SQL> recover database using backup controlfile;
ORA-00279: change 2036542 generated at 12/12/2014 05:11:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_2_866090437.dbf
ORA-00280: change 2036542 for thread 1 is in sequence #2




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oradata/orcl/redo02.log                  ## 把current日志给它
Log applied.
Media recovery complete.


6）resetlogs打开数据库


SQL> alter database open resetlogs;


Database altered.


7） 验证


SQL> select * from andy.andy;


        ID
----------

         1

本文转自 张冲andy 博客园博客，原文链接：http://www.cnblogs.com/andy6/p/6277637.html   ，如需转载请自行联系原作者