包含只读表空间的控制文件丢失恢复一例

1. 启动数据库

[oracle@rhserver guard1]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 4 23:05:52 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1267260 bytes
Variable Size 306186692 bytes
Database Buffers 4194304 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

2. 创建测试表空间，插入测试数据

SQL> create tablespace test datafile ‘/u01/app/oradata/guard1/test01.dbf’ reuse;

Tablespace created.

SQL> create table test (id number) tablespace users;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

3. 将表空间修改为只读模式

SQL> alter tablespace test read only;

Tablespace altered.

SQL> !

4. 删除所有控制文件，模拟丢失

[oracle@rhserver guard1]$ rm control0*
[oracle@rhserver guard1]$ ls
backup redo02.log redo04.log system01.dbf test01.dbf users01.dbf
redo01.log redo03.log sysaux01.dbf temp01.dbf undotbs01.dbf
[oracle@rhserver guard1]$ exit
exit

SQL> alter system switch logfile;

System altered.

5. abort数据库，模拟crash

SQL> shutdown abort
ORACLE instance shut down.

6. 尝试启动数据库，这时可以看到数据库已经起不来

SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1267260 bytes
Variable Size 306186692 bytes
Database Buffers 4194304 bytes
Redo Buffers 2924544 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> !

7. 从备份中恢复老的控制文件

[oracle@rhserver guard1]$ cp backup/control01.ctl control01.ctl
[oracle@rhserver guard1]$ cp backup/control01.ctl control02.ctl
[oracle@rhserver guard1]$ exit
exit

SQL> alter database mount;

Database altered.

8. 这时的数据库无法打开

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/u01/app/oradata/guard1/system01.dbf’
ORA-01207: file is more recent than control file – old control file

SQL> !oerr ora 1207
01207, 00000, “file is more recent than control file – old control file”
// *Cause: The control file change sequence number in the data file is
// greater than the number in the control file. This implies that
// the wrong control file is being used. Note that repeatedly causing
// this error can make it stop happening without correcting the real
// problem. Every attempt to open the database will advance the
// control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
// make the control file current. Be sure to follow all restrictions
// on doing a backup control file recovery.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/u01/app/oradata/guard1/system01.dbf’
ORA-01207: file is more recent than control file – old control file

9. 从下面的两个视图中可以看出，控制文件和数据文件中的SCN存在不一致

SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;

Session altered.

SQL> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
176463 2009-06-04 20:30:58
176463 2009-06-04 20:30:58
176463 2009-06-04 20:30:58
176463 2009-06-04 20:30:58

SQL> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
176465 2009-06-04 23:06:18
176465 2009-06-04 23:06:18
176465 2009-06-04 23:06:18
176465 2009-06-04 23:06:18

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
———- ——- ——- —————————— ———- ——————-
1 ONLINE ONLINE UNKNOWN ERROR 176465 2009-06-04 23:06:18
2 ONLINE ONLINE UNKNOWN ERROR 176465 2009-06-04 23:06:18
3 ONLINE ONLINE UNKNOWN ERROR 176465 2009-06-04 23:06:18
4 ONLINE ONLINE UNKNOWN ERROR 176465 2009-06-04 23:06:18

SQL> select name from v$datafile;

NAME
—————————————————
/u01/app/oradata/guard1/system01.dbf
/u01/app/oradata/guard1/undotbs01.dbf
/u01/app/oradata/guard1/sysaux01.dbf
/u01/app/oradata/guard1/users01.dbf

10. 使用using backup controlfile进行恢复

SQL> recover database using backup controlfile;
ORA-00279: change 176463 generated at 06/04/2009 20:21:46 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_7_687801845.dbf
ORA-00280: change 176463 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 176464 generated at 06/04/2009 23:06:18 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_8_687801845.dbf
ORA-00280: change 176464 for thread 1 is in sequence #8
ORA-00278: log file ‘/u01/app/admin/guard1/arch/1_7_687801845.dbf’ no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: ‘/u01/app/oradata/guard1/test01.dbf’

ORA-01112: media recovery not started

11. 将只读表空间数据文件修改为正确的文件

SQL> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178471 2009-06-04 23:07:15

SQL> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
0

SQL> select name from v$datafile;

NAME
——————————————————————–
/u01/app/oradata/guard1/system01.dbf
/u01/app/oradata/guard1/undotbs01.dbf
/u01/app/oradata/guard1/sysaux01.dbf
/u01/app/oradata/guard1/users01.dbf
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

SQL> alter database create datafile ‘/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005′ as
‘/u01/app/oradata/guard1/test01.dbf’;

Database altered.

SQL> select name from v$datafile;

NAME
——————————————————————
/u01/app/oradata/guard1/system01.dbf
/u01/app/oradata/guard1/undotbs01.dbf
/u01/app/oradata/guard1/sysaux01.dbf
/u01/app/oradata/guard1/users01.dbf
/u01/app/oradata/guard1/test01.dbf

SQL> select checkpoint_change#, checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178471 2009-06-04 23:07:15

SQL> select checkpoint_change#, checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178473 2009-06-04 23:07:15
178471 2009-06-04 23:07:15

12. 再次对数据库进行recover

SQL> recover database using backup controlfile;
ORA-00279: change 178471 generated at 06/04/2009 23:07:15 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_8_687801845.dbf
ORA-00280: change 178471 for thread 1 is in sequence #8

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 178538 generated at 06/04/2009 23:08:35 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_9_687801845.dbf
ORA-00280: change 178538 for thread 1 is in sequence #9
ORA-00278: log file ‘/u01/app/admin/guard1/arch/1_8_687801845.dbf’ no longer needed for this recovery

ORA-00326: log begins at change 196705, need earlier change 178538
ORA-00334: archived log: ‘/u01/app/admin/guard1/arch/1_9_687801845.dbf’

SQL> select checkpoint_change#, checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178538 2009-06-04 23:08:35
178538 2009-06-04 23:08:35
178538 2009-06-04 23:08:35
178538 2009-06-04 23:08:35
178517 2009-06-04 23:08:00

SQL> recover database using backup controlfile;
ORA-00279: change 178538 generated at 06/04/2009 23:08:35 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_9_687801845.dbf
ORA-00280: change 178538 for thread 1 is in sequence #9

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/guard1/redo02.dbf
ORA-00308: cannot open archived log ‘/u01/app/oradata/guard1/redo02.dbf’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/guard1/redo02.log
ORA-00310: archived log contains sequence 8; sequence 9 required
ORA-00334: archived log: ‘/u01/app/oradata/guard1/redo02.log’

SQL> recover database using backup controlfile;
ORA-00279: change 178538 generated at 06/04/2009 23:08:35 needed for thread 1
ORA-00289: suggestion : /u01/app/admin/guard1/arch/1_9_687801845.dbf
ORA-00280: change 178538 for thread 1 is in sequence #9

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/guard1/redo03.log
Log applied.
Media recovery complete.

13. 验证结果

SQL> select checkpoint_change#, checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178539 2009-06-04 23:08:35
178539 2009-06-04 23:08:35
178539 2009-06-04 23:08:35
178539 2009-06-04 23:08:35
178517 2009-06-04 23:08:00

SQL> alter database open resetlogs;

Database altered.

SQL> select checkpoint_change#, checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
—————— ——————-
178541 2009-06-04 23:24:08
178541 2009-06-04 23:24:08
178541 2009-06-04 23:24:08
178541 2009-06-04 23:24:08
178517

SQL> select status from dba_tablespaces;

STATUS
———
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
READ ONLY

6 rows selected.

SQL> select * from test;

ID
———-
1
1
1
1
1

从上面的结果可以看出，数据文件已经恢复到一致的状态，并且只读表空间的数据也已经能够访问。这个例子和普通的控制文件丢失的恢复其实没什么太大的区别，不同之处在于这个库中存在一个只读表空间，给恢复的过程带来了一点点的麻烦，特地记录下来。

转