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

 

案例1——恢复过去某个时间点误操作的table

1、基于时间点

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TB01';

USERNAME               SCN TIMESTAMP           SQL_REDO

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

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '1' and RO

WID = 'AAACc0AAFAAAAAYAAA';

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '2' and RO

WID = 'AAACc0AAFAAAAAYAAB';

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '3' and RO

WID = 'AAACc0AAFAAAAAYAAC';

849599 2012-03-22 17:31:35 insert into "SCOTT"."TB01"("ID") values ('111');

849621 2012-03-22 17:32:41 insert into "SCOTT"."TB01"("ID") values ('222');

849623 2012-03-22 17:32:47 insert into "SCOTT"."TB01"("ID") values ('333');

6 rows selected.

——通过以上logmnr 分析，将tb01表恢复到delete 之前（time：2012-03-22 17:31:08）【具体分析在logminer使用中】

SQL> select * from scott.tb01;——表现有的内容

ID

----------

111

222

333

3 rows selected.

 

——将database启动到mount ，进行restore 和recover【一定要在mount状态下】

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup 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.

 

——restore 所有的datafile

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

 

——基于时间的恢复（时间点为logmnr查询的时间）

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';——如果时间格式不一致就设一下咯，一般都永久设定

Session altered.

SQL> recover database until time '2012-03-22 17:31:07';——只能是database

Media recovery complete.

查看告警日志：

ALTER DATABASE RECOVER  database until time '2012-03-22 17:31:07'

Thu Mar 22 18:52:51 2012

Media Recovery Start

Thu Mar 22 18:52:52 2012

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

Thu Mar 22 18:52:52 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

Thu Mar 22 18:52:53 2012

Incomplete Recovery applied until change 849589

Thu Mar 22 18:52:53 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  database until time '2012-03-22 17:31:07'

——验证

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.tb01;——嘎嘎嘎，恢复到原来的样子啦~~

ID

----------

1

2

3

3 rows selected.