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

说明

         数据库已经开启了归档，但是没有数据库全备这种恢复是有条件的：

a)       System表空间在没有借助备份的情况下是不能恢复的；

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        备份控制文件

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

        

5        利用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备份。

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

         新插入一些数据    

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不能恢复数据文件。      

7        手动创建数据文件   

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” 来创建控制文件之前的文件。

8        尝试通过RMAN恢复数据库

RMAN> restore datafile 6;   Starting restore at 07-MAR-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=540 devtype=DISK   RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 03/07/2013 11:36:10 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 6 found to restore

         控制文件重新创建过，RMAN找不到数据文件创建过程。

         我们试一试手工注册RMAN信息        

RMAN> catalog start with '/oracle/u01/app/oracle/flash_recovery_area';   searching for all files that match the pattern /oracle/u01/app/oracle/flash_recovery_area no files found to be unknown to the database

         并没有找到备份信息，因为没有备份，所以不能恢复。

                 

9        打开数据库

                   此时数据库因为ttt.dbf文件的丢失不能恢复，从而只能启动到mount状态。

         要想打开数据库，我们只能将丢失的数据文件offline，这样在打开数据库，但是数据已 经丢失，并且不能恢复。    

SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/ttt.dbf' offline;

         启动数据库    

SQL>alter database open;

         尝试恢复表空间ttt,数据文件ttt.dbf  

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' 失败 RMAN> recover database;   Starting recover at 07-MAR-13 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/07/2013 13:41:35 RMAN-06094: datafile 6 must be restored 失败

         我们只能删除表空间ttt重建

SQL> drop tablespace ttt including contents and datafiles;

 

未重做控制文件后的恢复

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        模拟主机断电丢失数据文件

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

5        创建数据文件

         因为此时没有备份，指定手工创建数据文件

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;

                  注意：为什么能用RMAN来提取数据文件呢，是因为数据文件是在控制文件之后   创建的，控制文件中包含数据文件的创建过程。

6        恢复数据文件 

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

         启动数据库

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

7        检查数据

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

         数据库恢复完成.

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

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