试验之——丢失数据文件，全部控制文件，全部redo log file（原创）

1、备份之前
SQL> select * from backup;

        ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected.
2、冷备份除了online redo文件外的所有文件

3、删除所有文件，模拟出错

4、开始恢复
SQL> startup
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
启动失败

把备份好的所有文件恢复到原位置 ，发现可以启动到mount,但是不能open

SQL> startup nomount
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount
  2  ;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'F:/ORACLE/ORADATA/ORACAS/REDO01.LOG'

重新创建控制文件
SQL> alter database backup controlfile to trace;

Database altered.

CREATE CONTROLFILE REUSE DATABASE "ORACAS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
LOGFILE
  GROUP 1 'F:/ORACLE/ORADATA/ORACAS/REDO01.LOG'  SIZE 100M,
  GROUP 2 'F:/ORACLE/ORADATA/ORACAS/REDO02.LOG'  SIZE 100M,
  GROUP 3 'F:/ORACLE/ORADATA/ORACAS/REDO03.LOG'  SIZE 100M
DATAFILE
  'F:/ORACLE/ORADATA/ORACAS/SYSTEM01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/UNDOTBS01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/DRSYS01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/EXAMPLE01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/INDX01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/ODM01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/TOOLS01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/USERS01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/XDB01.DBF',
  'F:/ORACLE/ORADATA/ORACAS/OEM_REPOSITORY.DBF',
  'F:/ORACLE/ORADATA/ORACAS/BACKUP.DBF'
CHARACTER SET ZHS16GBK
;

STARTUP NOMOUNT;
执行以上脚本，创建新的控制文件


用 recover database using backup controlfile恢复

SQL> recover database using backup controlfile
ORA-00279: change 3161863 generated at 11/04/2004 20:44:47 needed for thread 1
ORA-00289: suggestion : F:/ORACLE/ORA92/RDBMS/ARC00006.001
ORA-00280: change 3161863 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'F:/ORACLE/ORA92/RDBMS/ARC00006.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

ORA-00308: cannot open archived log 'F:/ORACLE/ORA92/RDBMS/ARC00006.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'F:/ORACLE/ORADATA/ORACAS/SYSTEM01.DBF'

用recover database until cancel using backup controlfile来恢复
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 3161863 generated at 11/04/2004 20:44:47 needed for thread 1
ORA-00289: suggestion : F:/ORACLE/ORA92/RDBMS/ARC00006.001
ORA-00280: change 3161863 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

至此，成功打开了数据库

验证恢复后的数据

SQL> select * from backup;

        ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected.
这是一种不完全恢复，在online redo中的信息将丢失

小节：
1、resotore all the controlfile,datafile
2、recreate the controlfile
3、alter database mount;
4、recover database using backup controlfile（auto）
5、recover database until cancel using backup controlfile（cancel）;
6、alter database open resetlogs(再此重建了online redolog...)