备份与恢复--重建控制文件后数据文件损坏的恢复

恢复办法知用于：
控制文件重建后的数据文件损坏

前提条件：
归档模式，有完整的归档日志和重建控制文件前的数据文件

方法：
将以前备份的文件拷贝过来，做一次recover就行了

[@more@]

重建控制文件之前的归档日志。

SQL> SELECT RECID, NAME, FIRST_TIME FROM V$ARCHIVED_LOG;

RECID NAME FIRST_TIM
----- ------------------------------------------------------------------------------------------ ---------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_4_4poo2f69_.arc 09-JAN-09
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_5_4poo2hrt_.arc 10-JAN-09
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_12/o1_mf_1_6_4poo3yk0_.arc 12-JAN-09
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_7_4pqk7z7q_.arc 12-JAN-09
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqk8lmn_.arc 13-JAN-09
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqk9l4t_.arc 13-JAN-09

6 rows selected.

做一个冷备份。

SQL> shutdown immediate;

[oracle@rhel131 orcl]$ ls
control01.bak control02.ctl example01.dbf redo01.log sysaux01.dbf test01.bak users01.dbf
control01.ctl control03.bak jglu01.dbf redo02.log system01.dbf test02.dbf
control02.bak control03.ctl jglu02.dbf redo03.log temp01.dbf undotbs01.dbf

[oracle@rhel131 orcl]$ cp *.* /u01/backup

SQL> startup nomount;
ORACLE instance started.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/u01/app/oracle/oradata/orcl/users01.dbf',
17 '/u01/app/oracle/oradata/orcl/example01.dbf',
18 '/u01/app/oracle/oradata/orcl/jglu01.dbf',
19 '/u01/app/oracle/oradata/orcl/jglu02.dbf',
20 '/u01/app/oracle/oradata/orcl/test02.dbf'
21 CHARACTER SET WE8ISO8859P1
22 ;

Control file created.

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> column recid format 99
SQL> column name format a90

SSQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;

RECID NAME TO_CHAR

(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------

------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-

13:06:32:31
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-

13:06:32:50
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-

13:06:33:22

重建控制文件后，会丢失所有的归档重做日志的历史，这三个归档日志是重建控制文件后新产生的。

SQL> alter tablespace users offline;

Tablespace altered.

SQL> host rm /u01/app/oracle/oradata/orcl/users01.dbf

SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------

---
4 OFFLINE OFFLINE OFFLINE NORMAL 0

SQL> recover tablespace users
Media recovery complete.

SQL> alter tablespace users online;

Tablespace altered.

可以完全恢复过来,看看日志文件，恢复时只用到了online redo,没有用到archived log
Tue Jan 13 06:52:33 2009
ALTER DATABASE RECOVER tablespace users
Tue Jan 13 06:52:33 2009
Media Recovery Start
Tue Jan 13 06:52:33 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Tue Jan 13 06:52:33 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 11 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Tue Jan 13 06:52:33 2009
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER tablespace users

下面再来做一些测试性的操作和switch log,以使recover时用到归档日志后再重建一次control file看看情况。

SQL> create table jglu.a(
2 id number)
3 tablespace users;

Table created.

SQL> insert into jglu.a values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into jglu.a values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into jglu.a values(3);

1 row created.

SQL> commit;

SQL> alter system switch logfile;

System altered.

SQL> SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;

RECID NAME TO_CHAR

(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------

------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_8_4pqkt8d6_.arc 2009-01-

13:06:32:31
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_9_4pqkt8g7_.arc 2009-01-

13:06:32:50
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc 2009-01-

13:06:33:22
4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc 2009-01-

13:06:42:19
5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc 2009-01-

13:07:02:23
6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm1mpq_.arc 2009-01-

13:07:03:04

6 rows selected.

下面重建controlfile

SQL> startup nomount;
ORACLE instance started.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/u01/app/oracle/oradata/orcl/users01.dbf',
17 '/u01/app/oracle/oradata/orcl/example01.dbf',
18 '/u01/app/oracle/oradata/orcl/jglu01.dbf',
19 '/u01/app/oracle/oradata/orcl/jglu02.dbf',
20 '/u01/app/oracle/oradata/orcl/test02.dbf'
21 CHARACTER SET WE8ISO8859P1
22 ;

Control file created.

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> SELECT RECID, NAME,to_char(first_time,'yyyy-mm-dd:hh24:mi:ss') from V$ARCHIVED_LOG;

RECID NAME TO_CHAR

(FIRST_TIME,
----- ------------------------------------------------------------------------------------------ -------------

------
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm516c_.arc 2009-01-

13:07:02:23
2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_13_4pqm5185_.arc 2009-01-

13:07:03:04
3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_14_4pqm54v0_.arc 2009-01-

13:07:03:15

删除users表空间

SQL> alter tablespace users offline;

Tablespace altered.

SQL> host rm /u01/app/oracle/oradata/orcl/users01.dbf

SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl

恢复users表空间

SQL> recover tablespace users;
ORA-00279: change 673659 generated at 01/13/2009 06:34:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_%u_.ar
c
ORA-00280: change 673659 for thread 1 is in sequence #10

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 674058 generated at 01/13/2009 06:42:19 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_%u_.ar
c
ORA-00280: change 674058 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqkt
cw0_.arc' no longer needed for this recovery

ORA-00279: change 675373 generated at 01/13/2009 07:02:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_%u_.ar
c
ORA-00280: change 675373 for thread 1 is in sequence #12
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlz
z7c_.arc' no longer needed for this recovery

Log applied.
Media recovery complete.

日志文件如下信息：
Tue Jan 13 07:09:08 2009
ALTER DATABASE RECOVER tablespace users
Tue Jan 13 07:09:08 2009
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER tablespace users ...
Tue Jan 13 07:09:22 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_10_4pqktcw0_.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jan 13 07:09:23 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_11_4pqlzz7c_.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jan 13 07:09:23 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jan 13 07:09:23 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_01_13/o1_mf_1_12_4pqm18gj_.arc
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 14 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Tue Jan 13 07:09:23 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 15 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo02.log
Tue Jan 13 07:09:23 2009
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT

SQL> alter tablespace users online;

Tablespace altered.

SQL> select * from jglu.a;

ID
----------
1
2
3

综合以上，说明数据文件损坏时，只要有以前的备份，哪怕控制文件重新建立后，用归档日志是可以完全恢复的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1016124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1016124/