在线删除全部数据文件的恢复案例

--查看数据库状态 SQL select open_mode from v$database; OPEN_MODE----------READ WRITE --创建测试表 SQL create table zlm.test2 as select object_id id,object_name name from dba_objects where rownum=3; Table created. SQL set lin 130 pages 130

--查看数据库状态 SQL> select open_mode from v$database;
OPEN_MODE ---------- READ WRITE
--创建测试表 SQL> create table zlm.test2 as select object_id id,object_name name from dba_objects where rownum
Table created.
SQL> set lin 130 pages 130
SQL> col name for a15 SQL> select * from zlm.test2;
ID NAME ---------- --------------- 20 ICOL$ 44 I_USER1 28 CON$
--切换日志使online日志归档 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 0 52428800 1 YES UNUSED 0 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO CURRENT 1000917 19-914
由于之前已经恢复过一次，是用open resetlogs打开数据库的，因此这里sequence为1，
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 2 52428800 1 NO CURRENT 1001252 19-914 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO ACTIVE 1000917 19-914
注意：使用alter system switch logfile与使用alter system archive log current;是有区别的，前者只是对日志做一个切换，而并非进行归档，可以看到，切换一次归档后，3号日志文件组文件状态为"ACTIVE"，归档状态为"NO"
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 2 52428800 1 YES ACTIVE 1001252 19-914 2 1 3 52428800 1 NO CURRENT 1001255 19-914 3 1 1 52428800 1 YES ACTIVE 1000917 19-914
注意：第2次切换日志后，3号日志组文件才进行了归档，可以看到ARCHIVED列的值为"YES",但STATUS列依然是ACTIVE,可以对比一下用alter system archive log current;来归档日志后，STATUS列的值应该为"INACTIVE"
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 2 52428800 1 YES ACTIVE 1001252 19-914 2 1 3 52428800 1 YES ACTIVE 1001255 19-914 3 1 4 52428800 1 NO CURRENT 1001283 19-914
第3次切换归档后，当前日志组又变成3，注意此时online日志仍然是未归档状态
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 5 52428800 1 NO CURRENT 1001295 19-914 2 1 3 52428800 1 YES ACTIVE 1001255 19-914 3 1 4 52428800 1 YES ACTIVE 1001283 19-914
第4次切换日志，使online日志刷新到归档日志
SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@ora10g ora10g]$ pwd /u01/app/oracle/oradata/ora10g [oracle@ora10g ora10g]$ ll -lrth total 1.4G -rw-r----- 1 oracle oinstall 51M Sep 19 15:04 temp01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 redo02.log -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 zlm01.dbf -rw-r----- 1 oracle oinstall 31M Sep 19 15:11 users01.dbf -rw-r----- 1 oracle oinstall 166M Sep 19 15:11 undotbs01.dbf -rw-r----- 1 oracle oinstall 561M Sep 19 15:11 system01.dbf -rw-r----- 1 oracle oinstall 271M Sep 19 15:11 sysaux01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 redo03.log -rw-r----- 1 oracle oinstall 101M Sep 19 15:11 example01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:13 redo01.log -rw-r----- 1 oracle oinstall 7.2M Sep 19 15:13 control03.ctl -rw-r----- 1 oracle oinstall 7.2M Sep 19 15:13 control02.ctl -rw-r----- 1 oracle oinstall 7.2M Sep 19 15:13 control01.ctl
--删除数据库全部文件（模拟认为破坏） [oracle@ora10g ora10g]$ rm -f * [oracle@ora10g ora10g]$ ll total 0 [oracle@ora10g ora10g]$ exit logout [root@ora10g ~]# su - oracle s[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 9 15:13:43 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
--退出数据库并重启（模拟断电） SQL> shutdown abort ORACLE instance shut down.
此时也只能用abort关闭，因为已经无法正常关闭数据库，数据文件物理上都不存在了，数据库无法写入一致性的信息
SQL> startup ORACLE instance started.
Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 109053520 bytes Database Buffers 171966464 bytes Redo Buffers 2973696 bytes ORA-00205: error in identifying control file, check alert log for more info
重启报错是必然的，因为控制文件也被一并删除了，此时数据库只能启动到nomount状态，因为进入mount状态需要读取控制文件信息
SQL> select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted
nomount状态下无法使用v$动态性能视图，因为这是存放在数据库的数据字典中的
--用RMAN对数据库进行恢复 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@ora10g ~]$ cd /u01/orabackup/backupsets/
[oracle@ora10g backupsets]$ ll -lrth
total 1.1G -rw-r----- 1 oracle oinstall 167M Sep 17 15:50 ora10g-4175411955_20140917_858527326_265.db -rw-r----- 1 oracle oinstall 125K Sep 17 15:50 ora10g-4175411955_20140917_858527453_266.arc -rw-r----- 1 oracle oinstall 3.3M Sep 18 10:11 ora10g-4175411955_20140918_858593460_275.arc -rw-r----- 1 oracle oinstall 166M Sep 18 10:14 ora10g-4175411955_20140918_858593466_276.db -rw-r----- 1 oracle oinstall 471K Sep 18 10:14 ora10g-4175411955_20140918_858593694_277.arc -rw-r----- 1 oracle oinstall 7.3M Sep 18 13:47 ora10g-c-4175411955-20140918-03.ctl -rw-r----- 1 oracle oinstall 7.3M Sep 18 14:34 ora10g-c-4175411955-20140918-04.ctl -rw-r----- 1 oracle oinstall 6.7M Sep 19 10:10 ora10g-4175411955_20140919_858679818_283.arc -rw-r----- 1 oracle oinstall 166M Sep 19 10:12 ora10g-4175411955_20140919_858679823_284.db -rw-r----- 1 oracle oinstall 11K Sep 19 10:12 ora10g-4175411955_20140919_858679939_285.arc -rw-r----- 1 oracle oinstall 7.0M Sep 19 11:28 ora10g-4175411955_20140919_858684487_285.arc -rw-r----- 1 oracle oinstall 236K Sep 19 11:28 ora10g-4175411955_20140919_858684491_286.arc -rw-r----- 1 oracle oinstall 166M Sep 19 11:30 ora10g-4175411955_20140919_858684494_287.db -rw-r----- 1 oracle oinstall 16K Sep 19 11:30 ora10g-4175411955_20140919_858684611_288.arc -rw-r----- 1 oracle oinstall 7.3M Sep 19 11:30 ora10g-c-4175411955-20140919-01.ctl -rw-r----- 1 oracle oinstall 1.5M Sep 19 12:38 ora10g-4175411955_20140919_858688709_290.arc -rw-r----- 1 oracle oinstall 166M Sep 19 12:42 ora10g-4175411955_20140919_858688713_291.db -rw-r----- 1 oracle oinstall 66K Sep 19 12:42 ora10g-4175411955_20140919_858688941_292.arc -rw-r----- 1 oracle oinstall 7.3M Sep 19 12:42 ora10g-c-4175411955-20140919-02.ctl -rw-r----- 1 oracle oinstall 1.3M Sep 19 14:30 ora10g-4175411955_20140919_858695413_294.arc -rw-r----- 1 oracle oinstall 166M Sep 19 14:32 ora10g-4175411955_20140919_858695416_295.db -rw-r----- 1 oracle oinstall 619K Sep 19 14:32 ora10g-4175411955_20140919_858695532_296.arc -rw-r----- 1 oracle oinstall 7.3M Sep 19 14:32 ora10g-c-4175411955-20140919-03.ctl -rw-r----- 1 oracle oinstall 7.3M Sep 19 15:04 ora10g-c-4175411955-20140919-00.ctl
利用最新的那个控制文件进行恢复，即“ora10g-c-4175411955-20140919-00.ctl”
[oracle@ora10g backupsets]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on 9 15:16:52 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore controlfile from '/u01/orabackup/backupsets/ora10g-c-4175411955-20140919-00.ctl';
Starting restore at 19-914 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output filename=/u01/app/oracle/oradata/ora10g/control01.ctl output filename=/u01/app/oracle/oradata/ora10g/control02.ctl output filename=/u01/app/oracle/oradata/ora10g/control03.ctl Finished restore at 19-914
RMAN> alter database mount;
database mounted released channel: ORA_DISK_1
RMAN> list incarnation;

List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 3 3 ORA10G 4175411955 PARENT 446075 25-814 1 1 ORA10G 4175411955 PARENT 502765 26-814 2 2 ORA10G 4175411955 PARENT 547304 05-914 4 4 ORA10G 4175411955 PARENT 994427 19-914 5 5 ORA10G 4175411955 CURRENT 1000917 19-914
RMAN> restore database;
Starting restore at 19-914 Starting implicit crosscheck backup at 19-914 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK Crosschecked 21 objects Finished implicit crosscheck backup at 19-914
Starting implicit crosscheck copy at 19-914 using channel ORA_DISK_1 Finished implicit crosscheck copy at 19-914
searching for all files in the recovery area cataloging files... cataloging done
List of Cataloged Files ======================= File Name: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_3_b1qos1rq_.arc File Name: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_4_b1qoszsr_.arc File Name: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_2_b1qoq0o7_.arc File Name: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_1_b1qopyxq_.arc
using channel ORA_DISK_1
creating datafile fno=6 name=/u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20140918_858593466_276.db channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20140918_858593466_276.db tag=DB_BAK channel ORA_DISK_1: restore complete, elapsed time: 00:02:46 Finished restore at 19-914
数据库文件已经全部恢复，下面开始recover（前滚）
RMAN> recover database;
Starting recover at 19-914 using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 173 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_173_b1nj0wxp_.arc archive log thread 1 sequence 174 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc archive log thread 1 sequence 175 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_b1nwmzo4_.arc archive log thread 1 sequence 176 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_b1nwn43r_.arc archive log thread 1 sequence 177 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_b1nwpwxb_.arc archive log thread 1 sequence 178 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_b1nx9ry9_.arc archive log thread 1 sequence 179 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_179_b1nx9y1k_.arc archive log thread 1 sequence 180 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_180_b1nxb6q1_.arc archive log thread 1 sequence 181 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_181_b1q448j9_.arc archive log thread 1 sequence 182 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_182_b1q7g07g_.arc archive log thread 1 sequence 183 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_183_b1q7g099_.arc archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_1_b1q8p6o1_.arc archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_2_b1q8t2lf_.arc archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_3_b1qdt41b_.arc archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_4_b1qf1f1o_.arc archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_5_b1qmco65_.arc archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_6_b1qmhd6w_.arc archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_1_b1qopyxq_.arc archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_2_b1qoq0o7_.arc archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_3_b1qos1rq_.arc archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_4_b1qoszsr_.arc archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_173_b1nj0wxp_.arc thread=1 sequence=173 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc thread=1 sequence=174 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_b1nwmzo4_.arc thread=1 sequence=175 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_b1nwn43r_.arc thread=1 sequence=176 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_b1nwpwxb_.arc thread=1 sequence=177 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_b1nx9ry9_.arc thread=1 sequence=178 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_179_b1nx9y1k_.arc thread=1 sequence=179 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_180_b1nxb6q1_.arc thread=1 sequence=180 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_181_b1q448j9_.arc thread=1 sequence=181 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_182_b1q7g07g_.arc thread=1 sequence=182 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_183_b1q7g099_.arc thread=1 sequence=183 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_1_b1q8p6o1_.arc thread=1 sequence=1 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_2_b1q8t2lf_.arc thread=1 sequence=2 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_3_b1qdt41b_.arc thread=1 sequence=3 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_4_b1qf1f1o_.arc thread=1 sequence=4 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_5_b1qmco65_.arc thread=1 sequence=5 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_6_b1qmhd6w_.arc thread=1 sequence=6 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_1_b1qopyxq_.arc thread=1 sequence=1 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_2_b1qoq0o7_.arc thread=1 sequence=2 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_3_b1qos1rq_.arc thread=1 sequence=3 archive log filename=/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_19/o1_mf_1_4_b1qoszsr_.arc thread=1 sequence=4 unable to find archive log archive log thread=1 sequence=5 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 09/19/2014 15:22:07 RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 1001295
使用的是最新的控制文件恢复的数据库文件，首先会把所有可以获得的归档日志文件扫一遍，然后用归档日志对数据库文件进行前滚，一直到最后提示要求一个位置归档日志文件，实际上这个归档是不存在的，而Oracle以为还会有下一个，因为是之前是abort关闭数据库的，如果在我们把刚才的3号日志组文件刷到归档后，数据库又有别的操作，而这些操作还没有写到归档日志的话，那么这部分数据就只能丢失了，相当于做了一个不完全恢复
--打开数据库 RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/19/2014 15:22:41 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
可以看到，由于是执行了不完全恢复，必须用resetlogs来打开数据库
RMAN> alter database open resetlogs;
database opened
RMAN> exit

Recovery Manager complete. [oracle@ora10g backupsets]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 9 15:23:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
--检验测试数据 SQL> set lin 130 pages 130
SQL> col name for a15 SQL> select * from zlm.test2;
ID NAME ---------- --------------- 20 ICOL$ 44 I_USER1 28 CON$
可以看到，之前的测试数据由于被人为地写入了归档，因此并未丢失，而那些未归档的部分，无奈只能丢失，这个测试也再一次证明了归档日志文件的重要性，有了数据库全备+全部归档日志，即使把数据库的数据库文件、控制文件、online日志文件全删除，还是可以恢复回来的，如果能保证online日志未丢失，仅仅删除控制文件和数据文件，那么是会有一定几率零数据丢失的（内存中的数据及时写入到online日志的情况下）