用备份控制文件做不完全恢复下的完全恢复（全备<老>--备份控制文件<次新>--新建表空间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，结束。 转载请标明出处。