[20170622]传输表空间与dblink.txt
--//测试看看使用dblink+传输表空间的情况.写的情况相对复杂一点,具体看测试:1.环境:--//2个数据库版本一致.实际上在1台主机上.SCOTT@book> @ &r/ver1PORT_STRING VERSION BANNER------------------- ---------- --------------------------------------------------------------------------------x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSYS@dbcn1> @ &r/ver1PORT_STRING VERSION BANNER------------------------------ -------------- --------------------------------------------------------------------------------x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSYS@dbcn1> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where tablespace_name='USERS';FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ---------------- 4 12499575795 2017-06-22 09:04:40 16143 12499554377 ONLINE 1286 YES /u01/app/oracle/oradata/dbcn1/datafile/users.307.8 USERS 62160493--//传输表空间users到实例book.2.检查表空间相容性:SYS@dbcn1> Execute sys.dbms_tts.transport_set_check('users',TRUE,TRUE);PL/SQL procedure successfully completed.SYS@dbcn1> SELECT * FROM sys.transport_set_violations;VIOLATIONS--------------------------------------------------------------------------------------------------------------------------------------ORA-39908: Index JSTJ.SYS_C0012915 in tablespace USERS enforces primary constraints of table JSTJ.XTLAB_RESULT in tablespace TSP_JSTJ.--//移动索引 JSTJ.SYS_C0012915 到TSP_JSTJ表空间.SYS@dbcn1> Execute sys.dbms_tts.transport_set_check('users',TRUE,TRUE);PL/SQL procedure successfully completed.SYS@dbcn1> SELECT * FROM sys.transport_set_violations;no rows selected3.开始:--//设置表空间只读:SYS@dbcn1> alter tablespace users read only;Tablespace altered.--//拷贝相关数据文件到本机,我这里在同一台电脑,使用cp命令.(注我该了名)$ cp /u01/app/oracle/oradata/dbcn1/datafile/users.307.862160493 /home/oracle/backup/sugar01.dbf4.建立dblink:create public database link "l_dbcn1"connect to SYSTEMidentified by "xxxx"using '192.168.100.78:1521/DBCN1';--//测试是否ok.SCOTT@book> select * from scott.dept@l_dbcn1 where rownum=1; DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK--//由于表空间users在实例book已经存在,必须映射一个新的空间,另外我本机也存在scott.dept.必须建立新的用户.SCOTT@book> grant dba to sss IDENTIFIED BY sss;Grant succeeded.--//补充说明1点,这样直接grant 可以直接建立用户,并且赋予dba权限.测试ok.5.开始导入操作:impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbfREMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.log--//命令有点长.^_^,意思很容易理解.$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.logImport: Release 11.2.0.4.0 - Production on Thu Jun 22 09:35:21 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01": scott/a** network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEX/INDEXProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Jun 22 09:35:45 2017 elapsed 0 00:00:22$ echo 'select * from dept where rownum=1;' | sqlplus -s sss/sss DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORKSCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- --------------- 1 13277598620 2017-06-22 03:00:48 7 925702 ONLINE 912 YES /mnt/ramdisk/book/system01.dbf SYSTEM 2 13277598620 2017-06-22 03:00:48 1834 925702 ONLINE 901 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX 3 13277598620 2017-06-22 03:00:48 923328 925702 ONLINE 822 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1 4 13277598620 2017-06-22 03:00:48 16143 925702 ONLINE 931 YES /mnt/ramdisk/book/users01.dbf USERS 5 13277598620 2017-06-22 03:00:48 952916 925702 ONLINE 818 YES /mnt/ramdisk/book/example01.dbf EXAMPLE 6 13277598620 2017-06-22 03:00:48 13276257767 925702 ONLINE 309 YES /mnt/ramdisk/book/tea01.dbf TEA 7 12499578739 2017-06-22 09:21:14 16143 12499554377 ONLINE 1287 NO /home/oracle/backup/sugar01.dbf USERS7 rows selected.--//昏,前面不小心把单词sugar写成SUAGR了(REMAP_TABLESPACE=USERS:SUAGR),不影响测试结论.^_^.等一会在修改表空间名字.--//不过这里注意这个时候表空间还是users,因为这个时候数据文件还是只读的.SCOTT@book> select tablespace_name,status from dba_tablespaces where tablespace_name='SUAGR';TABLESPACE_NAME STATUS--------------- ---------SUAGR READ ONLY--//先改名看看是否可以.SCOTT@book> alter tablespace SUAGR rename to SUGAR;Tablespace altered.6.接着看看一些现象:SCOTT@book> select rowid,dept.* from scott.dept ;ROWID DEPTNO DNAME LOC------------------ ------ -------------- -------------AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORKAAAVRCAAEAAAACHAAB 20 RESEARCH DALLASAAAVRCAAEAAAACHAAC 30 SALES CHICAGOAAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTONSCOTT@book> select rowid,dept.* from sss.dept ;ROWID DEPTNO DNAME LOC------------------ ------ -------------- -------------AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORKAAAVRCAAEAAAACHAAB 20 RESEARCH DALLASAAAVRCAAEAAAACHAAC 30 SALES CHICAGOAAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTON--//你可以发现rowid两个表是一样的.SCOTT@book> column EDITION_NAME noprintSCOTT@book> select * from dba_objects where object_name='DEPT';OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE------ ----------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ------------SCOTT DEPT 87106 87106 TABLE 2013-08-24 12:04:21 2013-08-24 12:04:21 2013-08-24:12:04:21 VALID N N N 1SSS DEPT 104210 87106 TABLE 2017-06-22 09:35:32 2017-06-22 09:35:36 2017-06-22:09:35:32 VALID N N N 1--//你可以发现DATA_OBJECT_ID是一样的.打开读写看看.SCOTT@book> alter tablespace sugar read write ;Tablespace altered.SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- --------------- 1 13277598620 2017-06-22 03:00:48 7 925702 ONLINE 912 YES /mnt/ramdisk/book/system01.dbf SYSTEM 2 13277598620 2017-06-22 03:00:48 1834 925702 ONLINE 901 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX 3 13277598620 2017-06-22 03:00:48 923328 925702 ONLINE 822 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1 4 13277598620 2017-06-22 03:00:48 16143 925702 ONLINE 931 YES /mnt/ramdisk/book/users01.dbf USERS 5 13277598620 2017-06-22 03:00:48 952916 925702 ONLINE 818 YES /mnt/ramdisk/book/example01.dbf EXAMPLE 6 13277598620 2017-06-22 03:00:48 13276257767 925702 ONLINE 309 YES /mnt/ramdisk/book/tea01.dbf TEA 7 13277642074 2017-06-22 09:54:55 13277642064 925702 ONLINE 3 YES /home/oracle/backup/sugar01.dbf SUGAR7 rows selected.--//注意file#=7这行.你可以发现CHECKPOINT_COUNT=3(重新记数了),CHECKPOINT_CHANGE#从12499578739=>13277642074.--//你可以反过来想像一下,加入传输的表空间记录的scn大于当前数据库的scn,这样可以大幅提升数据库的scn.SCOTT@book> select rowid,a.* from scott.dept a where rownum=1 union all select rowid,b.* from sss.dept b where rownum=1;ROWID DEPTNO DNAME LOC------------------ ---------- -------------- -------------AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORKAAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK--//你可以发现两个表的rowid一样,因为什么呢?对象的DATA_OBJECT_ID,file号,block号,行号都一样.SCOTT@book> @ &r/rowid AAAVRCAAEAAAACHAAAOBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT------ ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 87106 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;--//注意看这里file=4,而不是前面查看FILE#=7.这也是oralce引入的绝对文件号与相对文件号的概念.换一句话讲rowid看到的相对文件--//号.SCOTT@book> select * FROM dba_data_files;FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_------------------------------- ------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------/mnt/ramdisk/book/users01.dbf 4 USERS 157810688 19264 AVAILABLE 4 YES 3.4360E+10 4194302 160 156762112 19136 ONLINE/mnt/ramdisk/book/undotbs01.dbf 3 UNDOTBS1 907018240 110720 AVAILABLE 3 YES 3.4360E+10 4194302 640 905969664 110592 ONLINE/mnt/ramdisk/book/sysaux01.dbf 2 SYSAUX 985661440 120320 AVAILABLE 2 YES 3.4360E+10 4194302 1280 984612864 120192 ONLINE/mnt/ramdisk/book/system01.dbf 1 SYSTEM 796917760 97280 AVAILABLE 1 YES 3.4360E+10 4194302 1280 795869184 97152 SYSTEM/mnt/ramdisk/book/example01.dbf 5 EXAMPLE 363069440 44320 AVAILABLE 5 YES 3.4360E+10 4194302 80 362020864 44192 ONLINE/mnt/ramdisk/book/tea01.dbf 6 TEA 41943040 5120 AVAILABLE 6 YES 3.4360E+10 4194302 128 40894464 4992 ONLINE/home/oracle/backup/sugar01.dbf 7 SUGAR 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 4194304 512 ONLINE7 rows selected.--//注意看file_id=7行,RELATIVE_FNO=4.总结:1.通过dblink的方式实际上就是减少一步,使用expdp生成元数据的过程.直接通过dblink从源数据库取.2.另外实际上做这个测试我心里有一个想法,比如不小心删除不该删除的数据,一般我们选择在另外的机器恢复到出问题前的scn,然后再想 办法处理,是否可以借助传输表空间来恢复数据,就导入本库来处理呢?还是没想好,留待以后测试.3.另外注意的问题,传输表空间建立的数据文件要手工传输数据文件到dg数据库中.4.还有一个问题要注意就是该方法仅仅导出了元数据，其它像FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT, SYSTEM_GRANT,trigger等都没有导出.必须采用别的方式导入.以及临时表(自己有1次这样的经历)^_^.--//需要单独在导入:$ cat stage2.parschemas=SCOTTinclude=TABLE:"IN (SELECT table_name from dba_tables where owner='DAILY' and temporary='Y')"include=FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,SYSTEM_GRANT,TRIGGERdumpfile=stage2.dmp