建立存放auxiliary数据库的目录auxi,建立存放传输表空间中间文件的目录ttbs
[oracle@rhel5 oradata]$ mkdir auxi
[oracle@rhel5 oradata]$ mkdir ttbs
[oracle@rhel5 oradata]$ ls
auxi shujukuai ttbs
[oracle@rhel5 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 16 17:24:12 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list 必须保证在归档模式下
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
创建两测试用的表空间
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/shujukuai/tbs1.dbf ' size 10m;
Tablespace created.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/shujukuai/tbs2.dbf' size 10m;
Tablespace created.
这里给来个全备
[oracle@rhel5 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jun 16 17:30:19 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHUJUKUA (DBID=1082844903)
RMAN> run{
2> backup database include current controlfile;
3> backup archivelog all;
4> }
Starting backup at 16-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/shujukuai/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/shujukuai/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/shujukuai/system02.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/shujukuai/system03.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/shujukuai/system04.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/shujukuai/users01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/shujukuai/undotbs01.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/shujukuai/undotbs03.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/shujukuai/undo2.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/shujukuai/undo2_2.dbf
input datafile fno=00011 name=/u01/app/oracle/oradata/shujukuai/tbs1.dbf
input datafile fno=00012 name=/u01/app/oracle/oradata/shujukuai/tbs2.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-10
channel ORA_DISK_1: finished piece 1 at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/backupset/2010_06_16/ o1_mf_nnndf_TAG20100616T173058_61ljnnv0_.bkp tag=TAG20100616T173058 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 16-JUN-10
channel ORA_DISK_1: finished piece 1 at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/backupset/2010_06_16/ o1_mf_ncnnf_TAG20100616T173058_61ljr85r_.bkp tag=TAG20100616T173058 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 16-JUN-10
Starting backup at 16-JUN-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=23 stamp=721712954
input archive log thread=1 sequence=2 recid=24 stamp=721712957
input archive log thread=1 sequence=3 recid=25 stamp=721712964
input archive log thread=1 sequence=4 recid=26 stamp=721712969
input archive log thread=1 sequence=5 recid=27 stamp=721714219
input archive log thread=1 sequence=6 recid=28 stamp=721727237
input archive log thread=1 sequence=7 recid=29 stamp=721731629
input archive log thread=1 sequence=8 recid=30 stamp=721748409
input archive log thread=1 sequence=9 recid=31 stamp=721755684
input archive log thread=1 sequence=10 recid=32 stamp=721756520
input archive log thread=1 sequence=11 recid=34 stamp=721767220
input archive log thread=1 sequence=12 recid=33 stamp=721767205
input archive log thread=1 sequence=13 recid=35 stamp=721767229
input archive log thread=1 sequence=14 recid=36 stamp=721769369
input archive log thread=1 sequence=15 recid=37 stamp=721779149
input archive log thread=1 sequence=16 recid=38 stamp=721791029
input archive log thread=1 sequence=17 recid=39 stamp=721829049
input archive log thread=1 sequence=18 recid=40 stamp=721848784
channel ORA_DISK_1: starting piece 1 at 16-JUN-10
channel ORA_DISK_1: finished piece 1 at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/backupset/2010_06_16/o1_mf_annnn_TAG20100616T173304_61ljrlqw_.bkp tag=TAG20100616T173304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=11 stamp=721703504
input archive log thread=1 sequence=2 recid=6 stamp=721703504
input archive log thread=1 sequence=3 recid=10 stamp=721703504
input archive log thread=1 sequence=4 recid=13 stamp=721703504
input archive log thread=1 sequence=5 recid=12 stamp=721703504
input archive log thread=1 sequence=6 recid=14 stamp=721703504
input archive log thread=1 sequence=7 recid=3 stamp=721702058
input archive log thread=1 sequence=8 recid=16 stamp=721703976
input archive log thread=1 sequence=9 recid=17 stamp=721703976
input archive log thread=1 sequence=10 recid=18 stamp=721703976
channel ORA_DISK_1: starting piece 1 at 16-JUN-10
channel ORA_DISK_1: finished piece 1 at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/backupset/2010_06_16/o1_mf_annnn_TAG20100616T173304_61ljt177_.bkp tag=TAG20100616T173304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=19 stamp=721709678
input archive log thread=1 sequence=2 recid=20 stamp=721710351
input archive log thread=1 sequence=3 recid=21 stamp=721710920
input archive log thread=1 sequence=4 recid=22 stamp=721712943
channel ORA_DISK_1: starting piece 1 at 16-JUN-10
channel ORA_DISK_1: finished piece 1 at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/backupset/2010_06_16/o1_mf_annnn_TAG20100616T173304_61ljtkkv_.bkp tag=TAG20100616T173304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 16-JUN-10
Starting Control File and SPFILE Autobackup at 16-JUN-10
piece handle=/u01/app/oracle/flash_recovery_area/SHUJUKUAI/autobackup/2010_06_16/o1_mf_s_721848852_61ljtonq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-JUN-10
SQL> create user u01 identified by u01 default tablespace tbs1;
User created.
SQL> grant connect,resource to u01;
Grant succeeded.
SQL> conn u01/u01
Connected.
SQL> create table timer1(a number(5),b date) tablespace tbs1;
Table created.
SQL> create table timer2(a number(5),b date) tablespace tbs2;
Table created.
SQL> insert into timer1 values(1,sysdate);
1 row created.
SQL> insert into timer2 values(1,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into timer1 values(2,sysdate);
1 row created.
SQL> insert into timer1 values(2,sysdate);
1 row created.
SQL> rollback;
Rollback complete.
SQL> insert into timer1 values(2,sysdate);
1 row created.
SQL> insert into timer2 values(2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select a,to_char(b,'hh24:mi:ss') bb from timer1 order by 1;
A BB
---------- --------
1 17:36:53
2 17:37:45
SQL> select a,to_char(b,'hh24:mi:ss') bb from timer2 order by 1;
A BB
---------- --------
1 17:36:58
2 17:37:49
SQL> drop table timer1;
Table dropped.
SQL> insert into timer2 values(3,sysdate);
1 row created.
SQL> insert into timer2 values(4,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select a,to_char(b,'hh24:mi:ss') bb from timer2 order by 1;
A BB
---------- --------
1 17:36:58
2 17:37:49
3 17:41:06
4 17:41:09
这里,我们在17:41分的时候对表timer1进行了DROP操作。但是基于时间点恢复的话,会导致T2的表数据丢失,那我们用TSPITR来进行恢复。
先在验证表空间是否自包含的,非自包含表空间是不能使用TSPITR的。
SQL> conn / as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('TBS1',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL> create tables ttt(x int) tablespace tbs1;
create tables ttt(x int) tablespace tbs1
*
ERROR at line 1:
ORA-00901: invalid CREATE command
SQL> create table ttt(x int) tablespace tbs1;
Table created.
SQL> exec dbms_tts.transport_set_check('TBS1',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object TTT in tablespace TBS1 not allowed in pluggable set 说明系统用户创建的表不是自包含的
SQL> drop table ttt;
Table dropped.
SQL> conn u01/u01
Connected.
SQL> create table ttt2(x int) partition by range(x)(partition p1 values less than (10) tablespace tbs1,partition p2 values less than (maxvalue) tablespace tbs2) ;
Table created.
SQL> conn / as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('TBS1',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Default Partition (Table) Tablespace TBS1 for TTT2 not contained in transportabl
e set
Partitioned table U01.TTT2 is partially contained in the transportable set: chec
k table partitions by querying sys.dba_tab_partitions
Default Partition (Table) Tablespace TBS2 for TTT2 not contained in transportabl
e set
发现TBS1上含分区表的部分分区,验证不通过
SQL> drop table ttt2;
drop table ttt2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table u01.ttt2;
Table dropped.
RMAN> run {
2> transport tablespace 'TBS1'
3> auxiliary destination '/u01/app/oracle/oradata/auxi'
4> dump file 'tbs1.dmp'
5> export log 'tbs1.log'
6> import script 'imptbs1.sql'
7> tablespace destination '/u01/app/oracle/oradata/ttbs'
8> until time "to_date('2010-6-16 17:41:06','yyyy-mm-dd hh24:mi:ss')";
}
执行完成后。。。。
SQL> conn / as sysdba
Connected.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string shujukuai
SQL> create directory ttbs as '/u01/app/oracle/oradata/ttbs/';
Directory created.
[oracle@rhel5 auxi]$ cd /u01/app/oracle/oradata/ttbs/
[oracle@rhel5 ttbs]$ impdp system/root directory=ttbs dumpfile='tbs1.dmp' transport_datafiles='tbs1.dbf'
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 16 June, 2010 19:36:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=ttbs dumpfile=tbs1.dmp transport_datafiles=tbs1.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file 'tbs1.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 19:37:26
下面使用绝对路径
[oracle@rhel5 ttbs]$ impdp system/root directory=ttbs dumpfile='tbs1.dmp' transport_datafiles='/u01/app/oracle/oradata/ttbs/tbs1.dbf'
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 16 June, 2010 19:41:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=ttbs dumpfile=tbs1.dmp transport_datafiles=/u01/app/oracle/oradata/ttbs/tbs1.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'TBS1' already exists
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 19:41:32
上面说TBS1已经存在了,我们把他删除掉
SQL> drop tablespace tbs1;
Tablespace dropped.
[oracle@rhel5 ttbs]$ impdp system/root directory=ttbs dumpfile='tbs1.dmp' transport_datafiles='/u01/app/oracle/oradata/ttbs/tbs1.dbf'
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 16 June, 2010 19:42:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=ttbs dumpfile=tbs1.dmp transport_datafiles=/u01/app/oracle/oradata/ttbs/tbs1.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:43:03
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21158541/viewspace-672161/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21158541/viewspace-672161/