基于时间点的不完全恢复实验2

实验目的：test1用户使用truncate table 和drop方式删除t1,t2 表，测试数据恢复。

1、源库建立环境：

建立2个表空间：
SQL> create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m;
Tablespace created.

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m;
Tablespace created.

 

建立2个用户并授权：
SQL> create user test1 identified by test1 default tablespace test1;
User created.

SQL> create user test2 identified by test2 default tablespace test2;
User created.

SQL> grant dba to test1;
Grant succeeded.

SQL> grant dba to test2;
Grant succeeded.

 

2、此时做一个rman全备数据库，步骤略过。

 

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive
Oldest online log sequence     75
Next log sequence to archive   77
Current log sequence           77
SQL> alter system switch logfile;

System altered.

 

3、建立实验表：

 

test1建立t1,t2表
SQL> conn test1/test1
Connected.
SQL> create table t1 as select * from all_users;
Table created.

SQL> create table t2 as select * from all_users;
Table created.

 

test2建立t1,t2表
SQL> conn test2/test2
Connected.
SQL> create table t1 (id number);
Table created.

SQL> create table t2 (id number);
Table created.

 

test1再建立t3表
SQL> conn test1/test1;
Connected.
SQL> create table t3 (id number);
Table created.

SQL> insert into t3 values (1);
1 row created.

SQL> commit;
Commit complete.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

记录t3表插入记录后时间，
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-11-27 11:11:00

SQL> select count(*) from t1;

  COUNT(*)
----------
        30

SQL> select count(*) from t2;

  COUNT(*)
----------
        30

SQL> select count(*) from t3;

  COUNT(*)
----------
         1

4、清空t1表。
SQL> truncate table t1;
Table truncated.

删除t2表。
SQL> drop table t2 purge;

 

SQL> col object_name format a20;
SQL>  select object_name,last_ddl_time from user_objects;

OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
T1                   2010-11-27 11:11:41
T3                   2010-11-27 11:10:18

5、准备目标库的环境：
$ pwd
/u01/app/oracle
$ mkdir -p ./admin/aux/bdump
$ mkdir -p ./admin/aux/cdump
$ mkdir -p ./admin/aux/udump
$ mkdir -p ./admin/aux/adump

$ ls -l
total 48
drwxr-s---    6 oracle   oinstall        512 Nov 27 11:33 admin
-rw-r--r--    1 oracle   oinstall       1042 Nov 27 08:53 dbs
drwxr-s---    4 oracle   oinstall        512 Nov 27 09:43 flash_recovery_area
drwxrwxr-x    6 oracle   oinstall        512 Oct 28 12:07 oraInventory
drwxr-s---    5 oracle   oinstall        512 Nov 27 10:05 oradata
drwxrws---    3 oracle   oinstall        512 Oct 27 15:58 product
$ mkdir ./oradata/aux

$ mkdir /u01/archive/aux

$ cd $ORACLE_HOME/dbs
$ pwd
/u01/app/oracle/product/10.2.0/db1/dbs

$ mv inittest.ora initaux.ora
$ orapwd file=orapwaux password=oracle entries=10

$ vi initaux.ora
"initaux.ora" 30 lines, 1143 characters 
aux.__db_cache_size=209715200
aux.__java_pool_size=4194304
aux.__large_pool_size=4194304
aux.__shared_pool_size=88080384
aux.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/aux/adump'
*.background_dump_dest='/u01/app/oracle/admin/aux/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/aux/control01.ctl','/u01/app/oracle/oradata/aux/control02.ctl','/u01/app/oracle/oradata/aux/control0
3.ctl'
*.core_dump_dest='/u01/app/oracle/admin/aux/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='aix5','aux'
*.db_name='aux'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=auxXDB)'
*.job_queue_processes=10
*.log_archive_dest_2='location=/u01/archive/aux'
*.log_file_name_convert='aix5','aux'
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=314572800
*.sga_target=314572800
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/aux/udump'

 

6、目标库启动到nomount状态。

$ export ORACLE_SID=aux
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 27 11:34:30 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size              96470096 bytes
Database Buffers          209715200 bytes
Redo Buffers                6303744 bytes

7、开始duplicate ,可以使用skip tablespace 命令跳过不需要的表空间。

$ export  ORACLE_SID=aux
$ rman target  sys/oracle@aix5 auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 27 11:15:08 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: AIX5 (DBID=2940324364)
connected to auxiliary database: AUX (not mounted)

RMAN> run{
2> allocate auxiliary channel d1 device type disk;
3> allocate channel c1 device type disk;
4> set until time "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";
5> duplicate target database to aux  skip tablespace test2 nofilenamecheck;
6> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: sid=155 devtype=DISK

allocated channel: c1
channel c1: sid=137 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-NOV-10
Datafile 7 skipped by request

contents of Memory Script:
{
   set until scn  2360497;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/aux/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/aux/undotbs01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/aux/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/aux/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/aux/example01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/aux/test01.dbf";
   restore
   check readonly
   clone database
   skip tablespace  TEST2   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-NOV-10

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/aux/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/aux/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/aux/users01.dbf
channel d1: reading from backup piece /u01/orabk/db_AIX5_0blu20ga_1_1.dbf
channel d1: restored backup piece 1
piece handle=/u01/orabk/db_AIX5_0blu20ga_1_1.dbf tag=TAG20101127T104009
channel d1: restore complete, elapsed time: 00:00:46
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/aux/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/aux/test01.dbf
channel d1: reading from backup piece /u01/orabk/db_AIX5_0clu20ga_1_1.dbf
channel d1: restored backup piece 1
piece handle=/u01/orabk/db_AIX5_0clu20ga_1_1.dbf tag=TAG20101127T104009
channel d1: restore complete, elapsed time: 00:01:06
Finished restore at 27-NOV-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/aux/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/aux/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/aux/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/aux/system01.dbf'
 CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=736169850 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=736169850 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=736169850 filename=/u01/app/oracle/oradata/aux/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=736169850 filename=/u01/app/oracle/oradata/aux/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=736169850 filename=/u01/app/oracle/oradata/aux/test01.dbf

contents of Memory Script:
{
   set until time  "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-NOV-10
datafile 7 not processed because file is offline

starting media recovery

archive log thread 1 sequence 77 is already on disk as file /u01/archive/1_77_733512527.dbf
archive log filename=/u01/archive/1_77_733512527.dbf thread=1 sequence=77
unable to find archive log
archive log thread=1 sequence=78
released channel: d1
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/27/2010 11:37:35
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown log: thread 1 seq 78 lowscn 2361416

 

8.复制完成后检查：

复制到最后有错误信息，提示需要78号日志，检查发现78号日志是源库的当前SEQUENCE，还没有进行归档。

检查目标数据库，已经到mount状态。
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

查看oradata下文件。
$ ls -l
total 2808256
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control01.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control02.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control03.ctl
-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:37 example01.dbf
-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:37 sysaux01.dbf
-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:37 system01.dbf
-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:37 test01.dbf
-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:37 undotbs01.dbf
-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:37 users01.dbf
$ ls -l

 

9、尝试打开数据库：

SQL> alter database open resetlogs;
Database altered.

 

再次检查数据文件，redo文件已经自动生成，复制数据库完成。
total 3115480
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control01.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control02.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control03.ctl
-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:43 example01.dbf
-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo01.log
-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo02.log
-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo03.log
-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:43 sysaux01.dbf
-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:43 system01.dbf
-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:43 test01.dbf
-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:43 undotbs01.dbf
-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:43 users01.dbf
$

检查其它数据文件，发现复制表空间时跳过的test2表空间状态还是recover的，这里直接删除它就可以了。
SQL> select name,bytes,status from v$datafile;

NAME                                                    BYTES STATUS
-------------------------------------------------- ---------- -------
/u01/app/oracle/oradata/aux/system01.dbf            534773760 SYSTEM
/u01/app/oracle/oradata/aux/undotbs01.dbf           335544320 ONLINE
/u01/app/oracle/oradata/aux/sysaux01.dbf            377487360 ONLINE
/u01/app/oracle/oradata/aux/users01.dbf               5242880 ONLINE
/u01/app/oracle/oradata/aux/example01.dbf           157286400 ONLINE
/u01/app/oracle/oradata/aux/test01.dbf               10485760 ONLINE
/u01/app/oracle/product/10.2.0/db1/dbs/MISSING0000          0 RECOVER
7

7 rows selected.

SQL> select name from v$tablespace;

NAME
--------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
EXAMPLE
TEST1
TEMP
TEST2

8 rows selected.

 

10、数据完整性检查：

 

使用test1登录，检查数据：
SQL> conn test1/test1
Connected.
SQL> select count(*) from test1.t1;
select count(*) from test1.t1
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from tab;
no rows selected

 

发现用户下的t1,t2表都不存在了，不完全恢复没有成功，估计是要78号日志时发生了错误造成的。

 

重新进行duplicate 复制，复制前源库先switch 切换日志：

继续实验：

1、源库操作switch：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive
Oldest online log sequence     76
Next log sequence to archive   78
Current log sequence           78

 

SQL> alter system switch logfile;
System altered.

 

SQL> !ls -l /u01/archive
total 397584
-rw-r-----    1 oracle   oinstall   47127552 Nov 25 01:17 1_73_733512527.dbf
-rw-r-----    1 oracle   oinstall   47127552 Nov 25 21:26 1_74_733512527.dbf
-rw-r-----    1 oracle   oinstall   47127552 Nov 26 21:00 1_75_733512527.dbf
-rw-r-----    1 oracle   oinstall   47510016 Nov 27 05:00 1_76_733512527.dbf
-rw-r-----    1 oracle   oinstall   14043648 Nov 27 11:09 1_77_733512527.dbf
-rw-r-----    1 oracle   oinstall     607744 Nov 27 11:55 1_78_733512527.dbf
drwxr-sr-x    2 oracle   oinstall        512 Nov 27 11:34 aux
drwxr-sr-x    2 oracle   oinstall        512 Nov 27 10:08 test

 

2、目标库启动到Nomount：

SQL> conn / as sysdba
Connected.
SQL> startup nomount force;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size              96470096 bytes
Database Buffers          209715200 bytes
Redo Buffers                6303744 bytes

 

3、开始duplicate：

$ rman target  sys/oracle@aix5 auxiliary /;

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 27 11:56:59 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: AIX5 (DBID=2940324364)
connected to auxiliary database: AUX (not mounted)
RMAN> run{
2> allocate auxiliary channel d1 device type disk;
3> allocate channel c1 device type disk;
4> set until time "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";
5> duplicate target database to aux skip tablespace test2 nofilenamecheck;
6> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: sid=155 devtype=DISK

allocated channel: c1
channel c1: sid=142 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-NOV-10
Datafile 7 skipped by request

contents of Memory Script:
{
   set until scn  2361416;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/aux/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/aux/undotbs01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/aux/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/aux/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/aux/example01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/aux/test01.dbf";
   restore
   check readonly
   clone database
   skip tablespace  TEST2   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-NOV-10

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/aux/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/aux/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/aux/users01.dbf
channel d1: reading from backup piece /u01/orabk/db_AIX5_0blu20ga_1_1.dbf
channel d1: restored backup piece 1
piece handle=/u01/orabk/db_AIX5_0blu20ga_1_1.dbf tag=TAG20101127T104009
channel d1: restore complete, elapsed time: 00:00:46
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/aux/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/aux/test01.dbf
channel d1: reading from backup piece /u01/orabk/db_AIX5_0clu20ga_1_1.dbf
channel d1: restored backup piece 1
piece handle=/u01/orabk/db_AIX5_0clu20ga_1_1.dbf tag=TAG20101127T104009
channel d1: restore complete, elapsed time: 00:00:56
Finished restore at 27-NOV-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/aux/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/aux/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/aux/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/aux/system01.dbf'
 CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=736171151 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=736171151 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=736171151 filename=/u01/app/oracle/oradata/aux/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=736171151 filename=/u01/app/oracle/oradata/aux/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=736171151 filename=/u01/app/oracle/oradata/aux/test01.dbf

contents of Memory Script:
{
   set until time  "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-NOV-10
datafile 7 not processed because file is offline

starting media recovery

archive log thread 1 sequence 77 is already on disk as file /u01/archive/1_77_733512527.dbf
archive log thread 1 sequence 78 is already on disk as file /u01/archive/1_78_733512527.dbf
archive log filename=/u01/archive/1_77_733512527.dbf thread=1 sequence=77
archive log filename =/u01/archive/1_78_733512527.dbf thread=1 sequence=78
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-10

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

 

 

user interrupt received
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/27/2010 12:15:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

 

4、当复制到上面红色字executing Memory Script 时长时间没有反应。

查看目标库的alert文件，应该是在创建test2表空间时给卡死了，日志信息如下：

 

Thread 1 advanced to log sequence 77 (LGWR switch)
  Current log# 1 seq# 77 mem# 0: /u01/app/oracle/oradata/aix5/redo01.log
Sat Nov 27 10:10:56 2010
create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m
Sat Nov 27 10:10:57 2010
Completed: create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m
Sat Nov 27 10:11:18 2010
create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m
Sat Nov 27 10:11:18 2010
Completed: create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m
~

再查看生成的数据文件，除redo02,redo03外都大于原先生成的11:43 分了，
$ ls -l
total 3115480
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control01.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control02.ctl
-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control03.ctl
-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:59 example01.dbf
-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:55 redo01.log
-rw-r-----    1 oracle   oinstall   52429312 Nov 27  11:43 redo02.log
-rw-r-----    1 oracle   oinstall   52429312 Nov 27  11:43 redo03.log
-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:59 sysaux01.dbf
-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:59 system01.dbf
-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:59 test01.dbf
-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:59 undotbs01.dbf
-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:59 users01.dbf

所以按 ctrl+c 结束复制任务。

5、目标库打开数据库：
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open resetlogs;

Database altered.

6、检查数据库，可以看到2010-11-27 11:11:00 前的数据已经全部恢复：
SQL>  select count(*) from test1.t1;

  COUNT(*)
----------
        30

SQL>  select count(*) from test1.t2;

  COUNT(*)
----------
        30

SQL> select count(*) from test1.t3;

  COUNT(*)
----------
         1

SQL> col name format a60;
SQL> select name,bytes,status from v$datafile;

NAME                                                              BYTES STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/aux/system01.dbf                      534773760 SYSTEM
/u01/app/oracle/oradata/aux/undotbs01.dbf                     335544320 ONLINE
/u01/app/oracle/oradata/aux/sysaux01.dbf                      377487360 ONLINE
/u01/app/oracle/oradata/aux/users01.dbf                         5242880 ONLINE
/u01/app/oracle/oradata/aux/example01.dbf                     157286400 ONLINE
/u01/app/oracle/oradata/aux/test01.dbf                         10485760 ONLINE
/u01/app/oracle/product/10.2.0/db1/dbs/MISSING00007                   0 RECOVER

 

SQL> drop tablespace test2 including contents and datafiles;
Tablespace dropped.

本文转自 gjm008 51CTO博客，原文链接：http://blog.51cto.com/gaoshan/433943，如需转载请自行联系原作者