--查看数据库状态
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> 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日志的情况下）
版权声明：本文博客原创文章。博客，未经同意，不得转载。