辅助数据库

建立存放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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值