[20170623]传输表空间补充测试.txt

[20170623]传输表空间补充测试.txt

--//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/
--//今天补充测试看看加参数SQLFILE生成的脚本是什么内容.

impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log  SQLFILE=sss.sql

--//注:前面的测试参考前面的链接

$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log SQLFILE=sss.sql

Import: Release 11.2.0.4.0 - Production on Fri Jun 23 08:53:59 2017

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_SQL_FILE_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.log SQLFILE=sss.sql

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SCOTT"."SYS_SQL_FILE_TRANSPORTABLE_01" successfully completed at Fri Jun 23 08:54:23 2017 elapsed 0 00:00:22

--//说明我前面的测试没有使用DIRECTORY参数,缺省使用DIRECTORY_NAME=DATA_PUMP_DIR.
SCOTT@book> column DIRECTORY_PATH format a40
SCOTT@book> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER  DIRECTORY_NAME DIRECTORY_PATH
------ -------------- ----------------------------------------
SYS    DATA_PUMP_DIR  /u01/app/oracle/admin/book/dpdump/

$ cd /u01/app/oracle/admin/book/dpdump

--//我仅仅摘录其中一段:

-- new object type path: TRANSPORTABLE_EXPORT/TABLE
CREATE TABLE "SSS"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
    "DNAME" VARCHAR2(14 BYTE),
    "LOC" VARCHAR2(13 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(SEG_FILE 4 SEG_BLOCK 130 OBJNO_REUSE 87106
  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SUAGR" ;
BEGIN
  SYS.DBMS_PLUGTSP.PATCHDICTIONARY('SSS','DEPT',736,'unused','unused','unused','unused',3);
END;
/

--//很明显这样的语句单独无法执行.有点像sys.bootstrap$的sql_text的内容.
--//不知道是否可以直接执行脚本sss.sql来实现.测试看看.

SYS@book> drop tablespace sugar including contents;
Tablespace dropped.

$ ls -l /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 5251072 2017-06-23 09:17:55 /home/oracle/backup/sugar01.dbf

--//文件还在.
--//不行报许多错误.重新引入看看是否可以打开读写.
SYS@dbcn1> alter tablespace users read only;
Tablespace altered.

$ cp /u01/app/oracle/oradata/dbcn1/datafile/users.307.862160493 /home/oracle/backup/sugar01.dbf
/bin/cp: overwrite `/home/oracle/backup/sugar01.dbf'? y

SYS@dbcn1> alter tablespace users read write ;
Tablespace altered.

$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log
Import: Release 11.2.0.4.0 - Production on Fri Jun 23 09:22:02 2017
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01":  scott/a* network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is
ORA-06512: at "SYS.DBMS_METADATA", line 7784
ORA-29335: tablespace 'USERS' is not read only
---//已经提示这样不行.
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Fri Jun 23 09:22:06 2017 elapsed 0 00:00:02

SYS@dbcn1> alter tablespace users read only;
Tablespace altered.

$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log

Import: Release 11.2.0.4.0 - Production on Fri Jun 23 09:28:08 2017
Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01":  scott/a** network_link=l_dbcn1 transport_tablespaces=users
transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss
logfile=impdp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jun 23 09:28:18 2017 elapsed 0 00:00:09

--//总结:
1.生成的脚本有点像bootstrap$.但是无法单独执行.
2.要设置成read only模式.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2141160/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2141160/