试验之——丢失了所有的controlfile 和datafile情况下，完全恢复的过程（原创）

来源：互联网发布：then.js 使用示例编辑：程序博客网时间：2024/05/22 06:14

前提条件：存在文件丢失之前的备份，包括controlfile的备份
******************************************************************************
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            F:/oracle/ora92/RDBMS
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

SQL> shutdown immediate
ORA-01109: database not open

删除全部三个控制文件以及数据文件backup.dbf,模拟损坏情况

这时startup ，肯定会报错说找不到controlfile；

这时把备份好的controlfile 和backup.dbf文件restore

STARTUP MOUNT;
成功；
ALTER DATABASE OPEN;

报错，说控制文件太老，不行的

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

SQL> alter database backup controlfile to trace;

Database altered.(备份出当前数据库结构)

到udump目录下找出oracas_ora_3096.trc文件，对其中的内容进行编辑，写出重新生成控制文件的脚步
CREATE CONTROLFILE REUSE DATABASE "ORACAS" NORESETLOGS 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
;

Control file created.

用新的控制文件启动数据库

SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: 'F:/ORACLE/ORADATA/ORACAS/BACKUP.DBF'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from backup;

        ID
----------
         1
         2
         3
         4
         5
到此，已经全部恢复成功^_^

小节：
1、restore all the corrupt contrilfile and datafile;
2、starup mount;
3、alter database backup controlfile to trace;
4、recreate the new controlfiles;
5、reover database;
5、alter database open;
这个过程是一个complete recovery

从udump目录下的oracas_ora_2764.trc文件中编辑出下面需要的控制文件创建教本
******************************************************************************
CREATE CONTROLFILE REUSE DATABASE "ORACAS" NORESETLOGS 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
;