Oracle Incomplete recovery based on user management (iii) recover some time in the past

Case 2--a table (DML) that restores a point in time past

1, based on 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.

--Analyzing the SCN of Misoperation by LOGMR

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;

This is a lazy way, in the production environment can not be used

05:52:30 sql> Select Current_scn from V$database; SCN for DataBlock Records

Current_scn

-----------

1260285

Records in--test

Sql> select * from SCOTT.TB01;

Id

----------

1

2. Perform the restoration based on change

--in Mount state, restore and recover

--restore all the DataFile

Sql> Startup Force Mount

ORACLE instance started.

Total System Global area 314572800 bytes

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

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;--control file The SCN number is newer

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