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博客

user backed up controlfile to recover database

最新文章