这几天再次系统学习了次oracle基于时间点不完全恢复的一些恢复方式，上一篇使用restore database的方式进行不完全恢复，见
这次使用的是duplicate复制数据库的方式进行不完全恢复，以下是实验例子：
实验目的：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 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.
Tablespace created.
建立2个用户并授权：
SQL> create user test1 identified by test1 default tablespace test1;
User created.
SQL> create user test1 identified by test1 default tablespace test1;
User created.
SQL> create user test2 identified by test2 default tablespace test2;
User created.
User created.
SQL> grant dba to test1;
Grant succeeded.
SQL> grant dba to test2;
Grant succeeded.
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;
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> 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.
Table created.
test2建立t1,t2表
SQL> conn test2/test2
Connected.
SQL> create table t1 (id number);
Table created.
SQL> conn test2/test2
Connected.
SQL> create table t1 (id number);
Table created.
SQL> create table t2 (id number);
Table created.
Table created.
test1再建立t3表
SQL> conn test1/test1;
Connected.
SQL> create table t3 (id number);
Table created.
SQL> conn test1/test1;
Connected.
SQL> create table t3 (id number);
Table created.
SQL> insert into t3 values (1);
1 row created.
1 row created.
SQL> commit;
Commit complete.
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 sysdate from dual;
SYSDATE
-------------------
2010-11-27 11:11:00
SQL> select count(*) from t1;
COUNT(*)
----------
30
----------
30
SQL> select count(*) from t2;
COUNT(*)
----------
30
----------
30
SQL> select count(*) from t3;
COUNT(*)
----------
1
----------
1
4、清空t1表。
SQL> truncate table t1;
Table truncated.
SQL> truncate table t1;
Table truncated.
删除t2表。
SQL> drop table t2 purge;
SQL> drop table t2 purge;
SQL> col object_name format a20;
SQL> select object_name,last_ddl_time from user_objects;
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
-------------------- -------------------
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
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
$ 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.
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
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 /
$ 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)
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> }
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: d1
channel d1: sid=155 devtype=DISK
allocated channel: c1
channel c1: sid=137 devtype=DISK
channel c1: sid=137 devtype=DISK
executing command: SET until clause
Starting Duplicate Db at 27-NOV-10
Datafile 7 skipped by request
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
{
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
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
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
{
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
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
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;
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
----------
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
$ 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.
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
$
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;
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
-------------------------------------------------- ---------- -------
/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
--------------------------------------------------
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> 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> 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.
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
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.
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
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> }
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: d1
channel d1: sid=155 devtype=DISK
allocated channel: c1
channel c1: sid=142 devtype=DISK
channel c1: sid=142 devtype=DISK
executing command: SET until clause
Starting Duplicate Db at 27-NOV-10
Datafile 7 skipped by request
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
{
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
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
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
{
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
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
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
{
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
~
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
----------
MOUNTED
SQL> alter database open resetlogs;
Database altered.
6、检查数据库，可以看到2010-11-27 11:11:00 前的数据已经全部恢复：
SQL> select count(*) from test1.t1;
COUNT(*)
----------
30
----------
30
SQL> select count(*) from test1.t2;
COUNT(*)
----------
30
----------
30
SQL> select count(*) from test1.t3;
COUNT(*)
----------
1
----------
1
SQL> col name format a60;
SQL> select name,bytes,status from v$datafile;
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
------------------------------------------------------------ ---------- -------
/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.
Tablespace dropped.
转载于:https://blog.51cto.com/gaoshan/433943
3676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
