Oracle 基于用户管理的不完全恢复（三）

 

案例2——恢复过去某个时间点误操作的表（DML）

1、基于change （scn）

SQL> conn scott/tiger

Connected.

SQL> select * from tb01;

ID

----------

1

2

3

SQL> drop table tb01 purge;

Table dropped.

SQL> create table tb01(id int) tablespace test;

Table created.

SQL> insert into tb01 values(1);

1 row created.

SQL> commit;

Commit complete.

——通过logmr 分析出误操作的scn

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents

2  where username='SCOTT' and lower(sql_redo) like '%table%';

USERNAME          SCN TIMESTAMP           SQL_REDO

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

SCOTT          851229 2012-03-23 15:45:22 drop table tb01 purge;

SCOTT          851264 2012-03-23 15:45:45 create table tb01(id int) tablespace test;

——这是一种偷懒的方法，在生产环境不能使用

05:52:30 SQL> select current_scn from v$database;        //datablock 记录的scn

CURRENT_SCN

-----------

1260285

——test 表里的记录

SQL> select * from scott.tb01;

ID

----------

1

2、进行基于change的恢复

——在mount状态，进行restore 和recover

——restore 所有的datafile

SQL> startup force mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1279964 bytes

Variable Size              58722340 bytes

Database Buffers          251658240 bytes

Redo Buffers                2912256 bytes

Database mounted.

SQL> !

[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/*.dbf /u01/app/oracle/oradata/anny/

SQL> select file#,checkpoint_change# from v$datafile;——控制文件的scn号较新

FILE# CHECKPOINT_CHANGE#

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

1             856024

3             856024

4             856024

5             856024

7             856024

10             856024

11             856024

12             856024

8 rows selected.

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

FILE# CHECKPOINT_CHANGE#

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

1             850758

3             850758

4             850758

5             850758

7             850758

10             850758

11             850758

12             850758

8 rows selected.

——基于change 的recover

SQL> recover database until change 851229;

ORA-00279: change 850758 generated at 03/23/2012 15:29:42 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_1_778691360.log

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

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 850903 generated at 03/23/2012 15:35:17 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_2_778691360.log

ORA-00280: change 850903 for thread 1 is in sequence #2

ORA-00278: log file '/disk1/arch/anny/arch_1_1_778691360.log' no longer needed for this recovery

Log applied.

Media recovery complete.

查看告警日志：

ALTER DATABASE RECOVER  database until change 851229

Fri Mar 23 16:02:19 2012

Media Recovery Start

ORA-279 signalled during: ALTER DATABASE RECOVER  database until change 851229  ...

Fri Mar 23 16:02:24 2012

ALTER DATABASE RECOVER    CONTINUE DEFAULT

Fri Mar 23 16:02:24 2012

Media Recovery Log /disk1/arch/anny/arch_1_1_778691360.log

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

Fri Mar 23 16:02:25 2012

ALTER DATABASE RECOVER    CONTINUE DEFAULT

Fri Mar 23 16:02:25 2012

Media Recovery Log /disk1/arch/anny/arch_1_2_778691360.log

Fri Mar 23 16:02:25 2012

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

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

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

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

Fri Mar 23 16:02:25 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

Fri Mar 23 16:02:25 2012

Recovery of Online Redo Log: Thread 1 Group 2 Seq 5 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

Fri Mar 23 16:02:25 2012

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

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

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

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

Fri Mar 23 16:02:25 2012

Incomplete Recovery applied until change 851229

Fri Mar 23 16:02:25 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

——验证

SQL> select * from scott.tb01;

ID

----------

1

2

3

3 rows selected.