前提条件:存在文件丢失之前的备份,包括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
;