归档模式有备份丢失数据文件后恢复

说明

         数据库已经开启了归档，控制文件记录了RMAN备份的信息，下面分两种情况说明此种备份恢复情况：

a)       创建数据文件后做数据库备份，之后重做了控制文件，即控制文件比数据文件新，然后丢了了数据文件

b)       创建数据文件后做数据库备份，没有重做控制文件，即控制文件比数据文件旧，然后丢失数据文件

重做控制文件后的恢复

1        创建新的表空间、数据文件、用户

SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m; SQL>grant connect,resource to ttt identified by ttt; SQL>alter user ttt default tablespace ttt; SQL>alter user ttt account unlock;

 

2        构造数据

SQL>conn ttt/ttt; SQL>create table ttt (id int); SQL> insert into ttt values(1); 1 row created. SQL> insert into ttt values(2); 1 row created. SQL> insert into ttt values(3); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt; ID ---- 1 2 3

         ttt用户默认表空间为ttt,创建表ttt，并插入3数据。

3        切换日志    

SQL>conn / as sysdba; SQL>alter system switch logfile;

         切换日志，保证数据都写到DBF中。

4        备份数据库

RMAN>backup database;

 

5        备份控制文件

SQL>conn / as sysdba; SQL>alter database backup controlfile to trace as ‘/home/oracle/control.sql’;

        

6        利用trace重做控制文件

SQL>conn / as sysdba; SQL>shutdown immediate; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/control0*.ctl SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.   Total System Global Area 1224736768 bytes Fixed Size                  2020384 bytes Variable Size             352324576 bytes Database Buffers          855638016 bytes Redo Buffers               14753792 bytes ORA-00205: error in identifying control file, check alert log for more info

         此时在启动数据库时报错找不到控制文件而只能启动到nomount状态。

         打开/home/oracle/control.sql备份的控制文件脚本，执行语句创建控制文件

SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 ‘/oracle/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,   GROUP 2 ‘/oracle/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,   GROUP 3 ‘/oracle/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M -- STANDBY LOGFILE   DATAFILE   ‘/oracle/u01/app/oracle/oradata/orcl/system01.dbf’,   ‘/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf’,   ‘/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf’,   ‘/oracle/u01/app/oracle/oradata/orcl/users01.dbf’,   ‘/oracle/u01/app/oracle/oradata/orcl/admin01.dbf’,   ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ CHARACTER SET AL32UTF8;

         执行完毕数据库自动启动到mount状态。

SQL>alter database open;

         此时数据库启动完成，重做了控制文件，此时控制文件中不再包含之前做的rman备份。

7        模拟主机断电丢失数据文件

         新插入一些数据    

SQL>conn ttt/ttt SQL> insert into ttt values(4); 1 row created. SQL> insert into ttt values(5); 1 row created. SQL> insert into ttt values(6); 1 row created. SQL> commit; Commit complete.

 

SQL>conn / as sysdba; SQL>shutdown abort; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/ttt.dbf SQL>startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.   Total System Global Area 1224736768 bytes Fixed Size                  2020384 bytes Variable Size             352324576 bytes Database Buffers          855638016 bytes Redo Buffers               14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此时找不到数据文件6 ttt.dbf

SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’   SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         通过recover不能恢复数据文件。      

8        手动创建数据文件   

SQL> alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’; alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ * ERROR at line 1: ORA-01178: file 6 created before last CREATE CONTROLFILE, cannot recreate ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此时报错，不能创建

         原因是：控制文件比数据文件新，oracle不允许在重建控制文件后通过”alter database                              create datafile” 来创建控制文件之前的文件。

9        通过备份恢复数据库

SQL>rman target /; RMAN>list backup

                   此时没有备份信息，因为我们重做了控制文件，备份信息已经不存在，我们需要手         工注册来获取备份信息。        

RMAN>catalog start with ‘/oracle/u01/app/oracle/flash_recover_area’; RMAN> restore datafile 6;   Starting restore at 06-MAR-13 using channel ORA_DISK_1   channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00006 to /oracle/u01/app/oracle/oradata/orcl/ttt.dbf channel ORA_DISK_1: reading from backup piece /oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_06/o1_mf_nnndf_TAG20130306T143952_8mfs5s6w_.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_06/o1_mf_nnndf_TAG20130306T143952_8mfs5s6w_.bkp tag=TAG20130306T143952 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 06-MAR-13   RMAN> recover database;   Starting recover at 06-MAR-13 using channel ORA_DISK_1   starting media recovery media recovery complete, elapsed time: 00:00:00   Finished recover at 06-MAR-13

         启动数据库    

RMAN>alter database open;

 

10  检查数据

SQL> select count(*) from ttt.ttt;         ID ----------          1          2          3          4          5          6

         数据库恢复完成.

 

 

未重做控制文件后的恢复

1        创建新的表空间、数据文件、用户

SQL>create tablespace ttt datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ size 10m; SQL>grant connect,resource to ttt identified by ttt; SQL>alter user ttt default tablespace ttt; SQL>alter user ttt account unlock;

 

2        构造数据

SQL>conn ttt/ttt; SQL>create table ttt (id int); SQL> insert into ttt values(1); 1 row created. SQL> insert into ttt values(2); 1 row created. SQL> insert into ttt values(3); 1 row created. SQL> commit; Commit complete. SQL>select * from ttt; ID ---- 1 2 3

         ttt用户默认表空间为ttt,创建表ttt，并插入3数据。

3        切换日志    

SQL>conn / as sysdba; SQL>alter system switch logfile;

         切换日志，保证数据都写到DBF中。

4        备份数据库

RMAN>backup database;

 

5        模拟主机断电丢失数据文件

         新插入一些数据    

SQL>conn ttt/ttt SQL> insert into ttt values(4); 1 row created. SQL> insert into ttt values(5); 1 row created. SQL> insert into ttt values(6); 1 row created. SQL> commit; Commit complete.

 

SQL>conn / as sysdba; SQL>shutdown abort; [oracle@dev206 ~]$ rm -rf /oracle/u01/app/oracle/oradata/orcl/ttt.dbf SQL>startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.   Total System Global Area 1224736768 bytes Fixed Size                  2020384 bytes Variable Size             352324576 bytes Database Buffers          855638016 bytes Redo Buffers               14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         此时找不到数据文件6 ttt.dbf

SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’   SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’

         通过recover不能恢复数据文件。      

6        手工创建数据文件或者RMAN提取数据文件    

SQL> alter database create datafile ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’ as ‘/oracle/u01/app/oracle/oradata/orcl/ttt.dbf’;

         数据文件重新创建完成

                   注意：这一步为什么能执行成功呢，因为我们的数据文件创建在控制文件之后，  即数据文件要新于控制文件。

 

         或者通过RMAN备份来提取数据文件   

RMAN>restore datafile 6;

 

7        恢复数据文件

SQL>recover datafile 6; 或者 RMAN>recover datafile 6;

         启动数据库

SQL>alter database open; 或者 RMAN>alter database open;

 

8        检查数据

SQL> select count(*) from ttt.ttt;         ID ----------          1          2          3          4          5          6

         数据库恢复完成.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26252014/viewspace-755506/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26252014/viewspace-755506/