simulate all controlfile lost, use backed up controlfile or snapshot controlfile to recovery.
snaphost controlfile locates in ?/dbs/ --unix/linux or ?/database/ --windows
0.simulate the env before lost all controlfile
SQL> show user;
USER 为 "SCOTT"
SQL> create table t_dept as select * from dept;
表已创建。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1130663
1.simulate all controlfile lost
1.1 shutdown immediate
1.2 delete all controlfile
2.copy backed up controlfile or snapshot controlfile to location of lastest controlfile, here use snaphost controlfile.
omit
3.open database to mount status
startup mount;
4.check scn of controlfile
SQL> select file#, checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1127386
2 1127386
3 1127386
4 1127386
5 1127386
5.check scn of datafile
SQL> select file#, checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1130941
2 1130941
3 1130941
4 1130941
5 1130941
compare the preceding result, the scn of controlfile is less than the scn of datafile, we found that the controlfile is old, then it need rolling forward.
6.use backed up controlfile or snapshot controlfile to recovery controlfile and roll forward the scn of controlfile.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- --------------
1 1 1 52428800 1 NO CURRENT
1127385 02-9月 -11
3 1 0 52428800 1 YES UNUSED
0
2 1 0 52428800 1 YES UNUSED
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------------------------------
IS_
---
3 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOCTOR\REDO03.LOG
NO
2 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOCTOR\REDO02.LOG
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------------------------------
IS_
---
1 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOCTOR\REDO01.LOG
NO
SQL> recover database using backup controlfile;
ORA-00279: 更改 1126315 (在 09/02/2011 04:00:20 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DOCTOR\ARCHIVELOG\2011_09_02\O1_MF_
1_14_%U_.ARC
ORA-00280: 更改 1127605 (用于线程 1) 在序列 #1 中
指定日志: {=suggested | filename | AUTO | CANCEL}
D:\oracle\product\10.2.0\oradata\DOCTOR\REDO01.LOG
已应用的日志。
完成介质恢复。
7.以重设log的sequence方式打开数据库
SQL> alter database open resetlogs;
8.check the result of recovery
SQL> select file#, checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1130943
2 1130943
3 1130943
4 1130943
5 1130943
SQL> select file#, checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1130943
2 1130943
3 1130943
4 1130943
5 1130943
SQL> desc scott.t_dept;
名称 是否为空? 类型
----------------------------------------- -------- ------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26019288/viewspace-706495/,如需转载,请注明出处,否则将追究法律责任。