应用还原的控制文件进行不完全恢复

应用还原的控制文件进行不完全恢复
条件:归档日志文件完好（可能也会用到redo日志）

描述：有时当我们的数据库的在线日志丢失或损坏，我们可以恢复到最后一次归档的时间的时间点，而不使用redo log时进行不完全恢复，下面就是在没有redo log的情况下的一次不完全恢复实例。

backup datafile + backup controlfile + archivelog file    to recover database until time point

--备份数据文件
SQL> alter database begin backup;

Database altered.

SQL> host cp /u01/oracle/oradata/oemgc/*.dbf /u01/oracle/oradata/oemgc_bk

SQL> alter database end backup;--据说这个操作包含了归档操作

Database altered.

SQL> alter system switch logfile;--或是alter system archive log current;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

--备份控制文件
SQL> alter database backup controlfile to '/u01/oracle/oradata/ctr.ctl';

Database altered.

SQL> create table a1 as select * from dba_tables;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   5
Current log sequence           5

--查看当前时间为t1 = 2010-10-20 16:30:19
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2010-10-20 16:30:19

SQL> create table a3 as select * from dba_tables;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2010-10-20 16:31:25

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--还原控制文件和数据文件
[oracle@DB1 oradata]$ cp ctr.ctl oemgc/control01.ctl
[oracle@DB1 oradata]$ cp ctr.ctl oemgc/control02.ctl
[oracle@DB1 oradata]$ cp ctr.ctl oemgc/control03.ctl
[oracle@DB1 oradata]$ cd oemgc_bk
[oracle@DB1 oemgc_bk]$ cp * ../oemgc

--删除redo log
[oracle@DB1 oemgc_bk]$ cd ../oemgc
[oracle@DB1 oemgc]rm *.log

--恢复数据库到t1时点
SQL> startup mount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size                  1219016 bytes
Variable Size              71304760 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database using backup controlfile until time '2010-10-20 16:30:19';
ORA-00279: change 1270337 generated at 10/20/2010 16:19:03 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_1_%u_.arc
ORA-00280: change 1270337 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_1_6cx9r8ph_.arc
ORA-00279: change 1270439 generated at 10/20/2010 16:23:36 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_2_%u_.arc
ORA-00280: change 1270439 for thread 1 is in sequence #2
ORA-00278: log file
'/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_1_6cx9r8ph_
.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_2_6cx9tz5f_.arc
ORA-00279: change 1270477 generated at 10/20/2010 16:25:03 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_3_%u_.arc
ORA-00280: change 1270477 for thread 1 is in sequence #3
ORA-00278: log file
'/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_2_6cx9tz5f_
.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_3_6cx9wrt7_.arc
ORA-00279: change 1270501 generated at 10/20/2010 16:26:00 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_4_%u_.arc
ORA-00280: change 1270501 for thread 1 is in sequence #4
ORA-00278: log file
'/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_3_6cx9wrt7_
.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto                  
ORA-00279: change 1270797 generated at 10/20/2010 16:30:00 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_5_%u_.arc
ORA-00280: change 1270797 for thread 1 is in sequence #5
ORA-00278: log file
'/u01/oracle/flash_recovery_area/OEMGC/archivelog/2010_10_20/o1_mf_1_4_6cxb48fg_
.arc' no longer needed for this recovery

Log applied.
Media recovery complete.
---数据库恢复完成，注意这里恢复时只用到了T1时间以前的归档日志进行恢复，没有用到redo日志，所以这里即使redo日志丢失或损坏也能恢复成功。如果要恢复的时间点的数据用到redo日志（也就是数据还没归档），就必需用到在线日志进行恢复。

--恢复到了T1时间点，可以查看数据恢复成功。
SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from a1;

COUNT(1)
----------
      1570

--T1以后的数据已经不存在了。
SQL> select count(1) from a3;
select count(1) from a3
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>