手工完全恢复（四）

 

案例3：（recover tablespace ，database open状态）

——database在open 状态下恢复数据文件（除了system tablespace）

（1） 模拟环境：

06:10:52 SQL> insert into scott.t01 values (4);

1 row created.

06:13:12 SQL> insert into scott.t01 values (5);

1 row created.

06:13:13 SQL> insert into scott.t01 values (6);

1 row created.

06:13:15 SQL> commit;

Commit complete.

06:13:17 SQL> select * from scott.t01;

ID

----------

1

2

3

4

5

6

6 rows selected.

——在open 状态下删除datafile

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

[oracle@work ~]$

SQL> select * from scott.tb01;

ID

----------

1

2

3

4

5

6

7

8

8 rows selected.

SQL> alter system flush buffer_cache; //清除data buffer

System altered.

SQL> select * from scott.db01;

select * from scott.db01

*

ERROR at line 1:

ORA-00942: table or view does not exist

06:15:09 SQL> select * from scott.t01;

select * from scott.t01

*

ERROR at line 1:

ORA-01116: error in opening database file 8

ORA-01110: data file 8: '/u01/app/oracle/oradata/prod/test02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

（2）查看datafile信息

SQL> select a.name,b.file#,b.name from V$tablespace a,v$datafile b where a.ts#=b.ts#;

NAME                                                    FILE# NAME

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

SYSTEM                                                      1 /u01/app/oracle/oradata/anny/system01.dbf

SYSAUX                                                      3 /u01/app/oracle/oradata/anny/sysaux01.dbf

USERS                                                       4 /u01/app/oracle/oradata/anny/user01.dbf

TEST                                                       11 /u01/app/oracle/oradata/anny/test02.dbf

TEST                                                        5 /u01/app/oracle/oradata/anny/test01.dbf

LX01                                                        6 /u01/app/oracle/oradata/anny/lx01.dbf

UNDOTBS                                                     7 /u01/app/oracle/oradata/anny/undotbs01.dbf

LX02                                                        2 /u01/app/oracle/oradata/anny/lx02.dbf

LX03                                                        8 /u01/app/oracle/oradata/anny/lx03.dbf

LX04                                                        9 /u01/app/oracle/oradata/anny/lx04.dbf

INDEXES                                                    10 /u01/app/oracle/oradata/anny/index01.dbf

11 rows selected.

——对数据文件脱机

06:17:39 SQL> alter database datafile 6,8 offline;

Database altered.

（3）转储datafile

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

（4）recover datafile 或 recover tablespace

SQL> recover datafile 5,11;

Media recovery complete.

告警日志信息：

ALTER DATABASE RECOVER  datafile 5,11

Wed Mar 21 16:02:50 2012

Media Recovery Start

Wed Mar 21 16:02:50 2012

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

Mem# 0 errs 0: /disk3/oradata/anny/redo02a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo02b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo02c.log

Wed Mar 21 16:02:50 2012

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

Mem# 0 errs 0: /disk3/oradata/anny/redo04a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo04b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo04c.log

Wed Mar 21 16:02:50 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  datafile 5,11

（5）验证

SQL> alter database datafile 5,11 online;

Database altered.

SQL> select * from scott.tb01;

ID

----------

1

2

3

4

5

6

7

8

8 rows selected.