歸檔模式下，線上刪除資料檔案的完全恢復

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> 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

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> 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

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

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>

歸檔模式下，線上刪除資料檔案的完全恢復

相關文章