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

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


第一种:当前控制文件全部损坏,而数据文件备份,控制文件备份及当前日志处于不同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   ,如需转载请自行联系原作者

上一篇:SQL Server数据库同步问题分享(二)---操作步骤[未完,待续]


下一篇:python自动化测试之检查一个json是否包含另外一个json