手工完全恢复（二）

 

案例1：recover database （介质失败，丢失大部分数据文件）

解决方法：需要做介质恢复，先restore丢失的数据文件，然后recover database，起库。

（1）模拟环境

05:45:49 SQL> select * from test;

ID

----------

1

2

3

05:45:52 SQL> insert into test values (4);

1 row created.

05:46:01 SQL> commit;

Commit complete.

05:46:02 SQL> insert into test values (5);

1 row created.

05:46:32 SQL> commit;

Commit complete.

05:46:34 SQL> insert into test values (6);

1 row created.

05:46:48 SQL> commit;

Commit complete.

05:46:49 SQL> insert into test values (7);

1 row created.

05:47:15 SQL> commit;

Commit complete.

05:46:08 SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1          1         38   52428800          1 NO  CURRENT                1187992 16-AUG-11

2          1         36   52428800          1 YES INACTIVE               1184326 16-AUG-11

3          1         37   52428800          1 YES INACTIVE               1187989 16-AUG-11

05:46:13 SQL> alter system switch logfile;

System altered.

05:46:43 SQL> alter system archive log current;

System altered.

05:46:58 SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1          1         38   52428800          1 YES ACTIVE                 1187992 16-AUG-11

2          1         39   52428800          1 YES ACTIVE                 1188675 16-AUG-11

3          1         40   52428800          1 NO  CURRENT                1188689 16-AUG-11

05:47:03 SQL> alter system archive log current;

System altered.

05:47:25 SQL>

05:47:16 SQL> insert into test values (8);

1 row created.

05:47:29 SQL> commit;

Commit complete.

05:47:30 SQL> insert into test values (9);

1 row created.

05:47:32 SQL> select * from test;

ID

----------

1

2

3

4

5

6

7

8

9

9 rows selected.

05:47:38 SQL>

（2）模拟介质失败

[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf

（3）启动database

05:48:57 SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'

05:49:03 SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 FILE NOT FOUND

3 FILE NOT FOUND

5 FILE NOT FOUND

6 FILE NOT FOUND

7 FILE NOT FOUND

（4） 启动失败，需要做介质恢复 ，首先restore

[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

--------recover database

05:51:48 SQL> select * from v$recovery_log;

THREAD#  SEQUENCE# TIME

---------- ---------- ---------

ARCHIVE_NAME

------------------------------------------------------------------------------------------------------------------------

1         38 16-AUG-11

/disk1/arch/prod/arch_38_1_758481658.log

---------查看恢复需要的归档日志

05:51:58 SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1            1188700

2            1188700

3            1188700

4            1188700

5            1188700

6            1188700

7            1188700

7 rows selected.

05:52:42 SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1            1188419

2            1188700

3            1188419

4            1188700

5            1188419

6            1188419

7            1188419

7 rows selected.

-----------控制文件记录的scn 应大于需恢复的数据文件头部的scn

（5） recover database（恢复数据库）

05:52:49 SQL> recover database;

ORA-00279: change 1188419 generated at 08/16/2011 05:43:18 needed for thread 1

ORA-00289: suggestion : /disk1/arch/prod/arch_38_1_758481658.log

ORA-00280: change 1188419 for thread 1 is in sequence #38

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

auto

Log applied.

Media recovery complete.

查看告警日志：

ALTER DATABASE RECOVER  database

Tue Aug 16 05:53:46 2011

Media Recovery Start

ORA-279 signalled during: ALTER DATABASE RECOVER  database  ...

Tue Aug 16 05:54:13 2011

ALTER DATABASE RECOVER    CONTINUE DEFAULT

Tue Aug 16 05:54:13 2011

Media Recovery Log /disk1/arch/prod/arch_38_1_758481658.log

Tue Aug 16 05:54:14 2011

Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0

Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log

Tue Aug 16 05:54:14 2011

Recovery of Online Redo Log: Thread 1 Group 3 Seq 40 Reading mem 0

Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log

Tue Aug 16 05:54:14 2011

Recovery of Online Redo Log: Thread 1 Group 1 Seq 41 Reading mem 0

Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log

Tue Aug 16 05:54:14 2011

Media Recovery Complete (prod)

Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

（6）验证：

05:54:17 SQL> alter database open;

Database altered.

05:55:31 SQL> select * from scott.test;

ID

----------

1

2

3

4

5

6

7

8

8 rows selected.

05:55:40 SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1            1208722

2            1208722

3            1208722

4            1208722

5            1208722

6            1208722

7            1208722

7 rows selected.

05:57:58 SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1            1208722

2            1208722

3            1208722

4            1208722

5            1208722

6            1208722

7            1208722

7 rows selected.