Oracle Incomplete recovery based on user Management (ii) a table that restores a past point in time

Case 1--restores a table in the past at some point in time

1, based on the point of time

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.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

--Through the above LOGMNR analysis, restore the TB01 table to delete (time:2012-03-22 17:31:08) "Specific analysis in Logminer use"

Sql> SELECT * FROM scott.tb01;--performance content

Id

----------

111

222

333

3 Rows selected.

--Start database to mount, restore and recover "must be in Mount State"

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 all the DataFile

[ORACLE@SOLARIS10 ~] $CP/disk1/backup/anny/cold_bak/*.dbf/u01/app/oracle/oradata/anny/

--Time based recovery (time for LOGMNR queries)

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';--if the time format is inconsistent set it up, generally permanent

Session altered.

sql> recover database until time ' 2012-03-22 17:31:07 ';--only database

Media recovery complete.

To view the alert log:

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 '

--Verify

sql> ALTER DATABASE open resetlogs;

Database altered.

Sql> SELECT * from scott.tb01;--Quack, back to the original look ~ ~

Id

----------

1

2

3

3 Rows selected.

Author: 51cto Blog Oracle Little Bastard