归档模式下，在线删除数据文件的完全恢复

归档模式下，在线删除数据文件的完全恢复

SQL> select * from cc;

        ID

----------

         1

         2

         3

第一步：首先用如下脚本做一个0级备份

run{

configure retention policy to recovery window of 14 days;

backup incremental level=0 database include current controlfile format '/home/oracle/backup/fullback0_%d_%T_%s' plus archivelog format '/home/oracle/backup/arch0_%d_%T_%s';

}

第二步：再在cc表中插入几条数据，并切换日志

SQL> insert into cc values(4);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into cc values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL>  insert into cc values(6);

1 row created.

SQL> commit;

Commit complete.

SQL> 

SQL> 

SQL> select * from cc;

        ID

----------

         1

         2

         3

         4

         5

         6

6 rows selected.

SQL> 

第三步：然后在数据库open状态下，命令行删除 cc.dbf 文件

[oracle@sp ora11g]$ rm cc.dbf 

第四步：将cc.dbf文件先offline，然后关闭数据库 

[oracle@sp backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 5 02:22:52 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> shutdown immediate;

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/home/oracle/oradata/ora11g/cc.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> 

SQL> 

SQL> alter database datafile 6 offline;

Database altered.

SQL> 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

第五步：在rman下将db启动到mount状态进行恢复

[oracle@sp backup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 5 02:02:05 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area    1322467328 bytes

Fixed Size                     1336316 bytes

Variable Size                452987908 bytes

Database Buffers             855638016 bytes

Redo Buffers                  12505088 bytes

RMAN> 

RMAN> restore datafile 6;   //从备份中还原6号数据文件

Starting restore at 05-JUL-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/ora11g/cc.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/fullback0_ORA11G_20130705_49

channel ORA_DISK_1: piece handle=/home/oracle/backup/fullback0_ORA11G_20130705_49 tag=TAG20130705T021825

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 05-JUL-13

RMAN> recover datafile 6 ; //恢复6号数据文件

Starting recover at 05-JUL-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 274 is already on disk as file /home/oracle/archivelog/1_274_785786041.dbf

archived log for thread 1 with sequence 275 is already on disk as file /home/oracle/archivelog/1_275_785786041.dbf

archived log for thread 1 with sequence 276 is already on disk as file /home/oracle/archivelog/1_276_785786041.dbf

archived log file name=/home/oracle/archivelog/1_274_785786041.dbf thread=1 sequence=274

media recovery complete, elapsed time: 00:00:00

Finished recover at 05-JUL-13

RMAN> alter database open; //打开数据库

database opened

RMAN> 

第六步：此时在db处于open状态下，sqlplus中查看cc表，报错

[oracle@sp ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 5 02:27:46 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from cc;

select * from cc

              *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/home/oracle/oradata/ora11g/cc.dbf'

SQL> alter database datafile 6 online; //将6号数据文件online

Database altered.

查看误删除数据文件上的表数据，一切正常

SQL> select * from cc;

        ID

----------

         1

         2

         3

         4

         5

         6

6 rows selected.

SQL> 

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

转载于:http://blog.itpub.net/24862808/viewspace-765707/