recover from cold backup of control files - 恢复冷备份的控制文件

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/19
Subject: RE: 答复: 答复: test for lesson 14
To: wen xie <xiewenxiewen at googlemail.com>


1.丢失一个控制文件，实例将终止
>control03.ctl清空一个控制文件内容，模拟磁盘出错

[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ ls -l *.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control01.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control02.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control03.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ >control01.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ ls -l *.ctl
-rw-r----- 1 oracle oinstall 65536 Jan 20 04:29 control01.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control02.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control03.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$

alter.log报错

Tue Jan 20 04:29:27 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_7105.trc:
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 1
Tue Jan 20 04:29:27 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_7105.trc:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 1
Tue Jan 20 04:29:27 2009
LGWR: terminating instance due to error 204
Instance terminated by LGWR, pid = 7105

实例终止了



2.archivelog数据库丢失所有控制文件的恢复

a.先关闭数据库，做个全库冷备份
cp -rp test test.bak

b.启动数据库，随便修改一些数据
conn / as sysdba
startup
conn a/a
create table tmp as select * from all_objects;
select count(*) from tmp;

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2083592 bytes
Variable Size 188744952 bytes
Database Buffers 117440512 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> conn a/a
Connected.
SQL> create table tmp as select * from all_objects;

Table created.

SQL> select count(*) from tmp;

COUNT(*)
----------
4562

SQL>

c.清空所有控制文件
>control01.ctl;>control02.ctl;>control03.ctl
alert.log报错，实例终止

Tue Jan 20 06:21:54 2009
Hex dump of (file 0, block 1) in trace file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Completely zero block found during control file header read
Tue Jan 20 06:21:54 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc:
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
Tue Jan 20 06:21:54 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
LGWR: terminating instance due to error 227
Instance terminated by LGWR, pid = 11683

d.恢复数据前，对坏了的数据库也冷备份一下，保证不会因为误操作破坏数据库
cp -rp test test.bad.bak

e.从冷备份中恢复旧的控制文件
cp -p test.bak/control0{1,2,3}.ctl test/

f.启动数据库
conn / as sysdba
startup

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2083592 bytes
Variable Size 188744952 bytes
Database Buffers 117440512 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/test/system01.dbf'
ORA-01207: file is more recent than control file - old control file


SQL>

报告控制文件是旧的，无法打开数据库

f.恢复数据库
recover database using backup controlfile;

SQL> recover database using backup controlfile;
ORA-00279: change 700297 generated at 01/20/2009 06:15:17 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/oradata/test/archive/1_658_675146492.arc
ORA-00280: change 700297 for thread 1 is in sequence #658


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

提示说需要一个归档日志

alter.log显示

Tue Jan 20 06:26:56 2009
ALTER DATABASE RECOVER database using backup controlfile
Tue Jan 20 06:26:56 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 3 processes
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...

因为没有这个归档，查询v$log,v$logfile，得知在线日志文件名
set pages 9999 line 130
col member for a50
select * from v$log;
select * from v$logfile;

SQL> set pages 9999 line 130
SQL> col member for a50
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 656 104857600 2 YES INACTIVE 699823 20-JAN-09
3 1 658 104857600 2 NO CURRENT 699844 20-JAN-09
2 1 657 104857600 2 YES INACTIVE 699840 20-JAN-09

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /home/oracle/app/oracle/oradata/test/redo01.rdo NO
2 ONLINE /home/oracle/app/oracle/oradata/test/redo02.rdo NO
3 ONLINE /home/oracle/app/oracle/oradata/test/redo03.rdo NO
1 ONLINE /home/oracle/app/oracle/oradata/test/redo01b.rdo NO
2 ONLINE /home/oracle/app/oracle/oradata/test/redo02b.rdo NO
3 ONLINE /home/oracle/app/oracle/oradata/test/redo03b.rdo NO

6 rows selected.

SQL>

所以输入对应的文件名

/home/oracle/app/oracle/oradata/test/redo03.rdo
Log applied.
Media recovery complete.
SQL>

恢复完成

alert.log显示

Tue Jan 20 06:30:22 2009
ALTER DATABASE RECOVER LOGFILE '/home/oracle/app/oracle/oradata/test/redo03.rdo'
Tue Jan 20 06:30:22 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test/redo03.rdo
Tue Jan 20 06:30:23 2009
Media Recovery Complete (test)
Completed: ALTER DATABASE RECOVER LOGFILE '/home/oracle/app/oracle/oradata/test/redo03.rdo'

g.打开数据库
alter database open resetlogs;
查询select count(*) from a.tmp;
没问题
说明成功恢复了.


3.noarchivelog恢复所有控制文件
过程同2，也可以恢复

但是，如果在线日志中没有恢复所要用到的全部重做记录

SQL> recover database using backup controlfile;
ORA-00279: change 699614 generated at 01/20/2009 05:01:58 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/oradata/test/archive/1_655_675146492.arc
ORA-00280: change 699614 for thread 1 is in sequence #655


Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/test/redo03b.rdo
ORA-00326: log begins at change 700047, need earlier change 699614
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/test/redo03b.rdo'

SQL>

提示说需要更早的重做日志

这种情况下就不能用备份的控制文件恢复了，必须重建控制文件

alter database backup controlfile to trace;
shutdown immediate

startup nomount
create controlfile reuse database test noresetlogs force logging noarchivelog
...
logfile
...
datafile
...
character set al32utf8
;

recover database;
alter database open;

所以noarchivelog非归档模式下,有时可以用备份的控制文件恢复, 有时不行. C说的不严谨, 所以不选C


P.S.
如果是重建控制文件，恢复完了还要手工给临时表空间加上临时文件

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' REUSE;

P.P.S.
4.
19. Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A) The instance aborts.
B) The database cannot be recovered.
C) The database can be recovered by restoring the control files from the backup.
D) The database remains opened and you have to shut it down with the ABORT option.
E) The database can be restored till the point of the last closed whole database backup.
A.E.


外部链接:

Restore Control File from Backup After Loss of All Current Control Files



-fin-