1、数据库处于归档模式。
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/archive Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1
2、模拟环境。
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m;
SQL> create user test identified by test default tablespace test;
SQL> grant connect, resource to test;
SQL> select * from stu; ID NAME ---------- -------------------- 1 aa 2 bb
3、删掉数据文件。
SQL> host rm -f '/u01/app/oracle/oradata/orcl/test.dbf';
SQL> startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 125830736 bytes Database Buffers 155189248 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'
4、恢复数据文件。
(1)重建数据文件。
SQL> alter database create datafile '/u01/app/oracle/oradata/orcl/test.dbf' as '/u01/app/oracle/oradata/orcl/test.dbf'; Database altered.
(2)修复数据文件。
SQL> recover datafile 6; Media recovery complete.
(3)打开数据库。
SQL> alter database open; Database altered.
(4)检查数据。
SQL> select * from stu; ID NAME ---------- -------------------- 1 aa 2 bb
总结:丢失数据文件,从其创建时刻起所有的重做日志都在,因此可以在重建数据文件,通过recover命令应用所有重做日志,重建数据文件的内容。
这里也可以借助RMAN实现。
回到故障现场。
SQL> host rm -f '/u01/app/oracle/oradata/orcl/test.dbf';
SQL> startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 130025040 bytes Database Buffers 150994944 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'
修复数据文件。
RMAN> restore datafile 6; Starting restore at 2012-11-11 10:52:18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 devtype=DISK creating datafile fno=6 name=/u01/app/oracle/oradata/orcl/test.dbf restore not done; all files readonly, offline, or already restored Finished restore at 2012-11-11 10:52:25
应用重做日志。
RMAN> recover datafile 6; Starting recover at 2012-11-11 10:52:33 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2012-11-11 10:52:34
打开数据库。
RMAN> alter database open; database opened
检查数据。
SQL> select * from stu; ID NAME ---------- -------------------- 1 aa 2 bb