Oracle基于时间点恢复测试

一、前提开启归档日志
SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/Oracle/oradata/ora9i/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL>

1、冷备数据库
SQL> !date
24 20:56:34 CST 2012

SQL> select count(*) from t;

  COUNT(*)
----------
     9

SQL>
SQL> conn / as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[oracle@test cold]$ cp  /u01/Oracle/oradata/ora9i/*.dbf .

SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  235999648 bytes
Fixed Size             450976 bytes
Variable Size          201326592 bytes
Database Buffers       33554432 bytes
Redo Buffers             667648 bytes
Database mounted.
Database opened.


SQL> !date
24 21:03:44 CST 2012

SQL> select count(*) from t;

  COUNT(*)
----------
    15

SQL>

SQL> delete from t;

15 rows deleted.

SQL> date
SP2-0042: unknown command "date" - rest of line ignored.
SQL> !date
24 21:07:31 CST 2012

SQL> select count(*) from t;

  COUNT(*)
----------
     0

SQL>

SQL> conn / as sysdba;
Connected.
SQL> shutdow abort;
ORACLE instance shut down.
SQL> exit


[oracle@test ora9i]$ ll
drwxr-xr-x  2 oracle oinstall      4096  624 20:51 arch
-rw-r-----  1 oracle oinstall   2285568  624 21:07 control01.ctl
-rw-r-----  1 oracle oinstall   2285568  6 24 21:07 control02.ctl
-rw-r-----  1 oracle oinstall   2285568  624 21:07 control03.ctl
-rw-r-----  1 oracle oinstall      1536  623 12:06 orapwora9i
-rw-r-----  1 oracle oinstall 104858112  6 24 21:02 redo01.log
-rw-r-----  1 oracle oinstall 104858112  6 24 21:07 redo02.log
-rw-r-----  1 oracle oinstall 104858112  6 24 21:02 redo03.log
[oracle@test ora9i]$

cp *.dbf /u01/Oracle/oradata/ora9i/
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  235999648 bytes
Fixed Size             450976 bytes
Variable Size          201326592 bytes
Database Buffers       33554432 bytes
Redo Buffers             667648 bytes
Database mounted.
SQL> recover database until time '2012-06-24:21:03:44';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> select * from usr1.t;

    ID NAME
---------- --------------------
      1234 tank
      1234 tank
      1234 tank
      1234 tank
      1234 tank
      1234 tank
       122 jjjao
       122 jjjao
       122 jjjao
       122 jjjao
       122 jjjao

    ID NAME
---------- --------------------
       122 jjjao
       122 jjjao
       122 jjjao
       122 jjjao

15 rows selected.

SQL> select count(*) from usr1.t;

  COUNT(*)
----------
    15

SQL>


完成。