Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to parse the data punp transmission table space, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Check whether the transmission tablespace example violates the independence rule.

[oracle@snow ~] $export ORACLE_SID=ora12c

[oracle@snow ~] $sqlplus / as sysdba

SYS@ora12c > exec dbms_tts.transport_set_check ('EXAMPLE',TRUE)

PL/SQL procedure successfully completed.

SYS@ora12c > select * from transport_set_violations

No rows selected

2, set tablespace example to read-only

SYS@ora12c > alter tablespace example read only

Tablespace altered.

Source data file path

SYS@ora12c > select name from v$datafile

NAME

/ u01/app/oracle/oradata/ora12c/system01.dbf

/ u01/app/oracle/oradata/ora12c/example01.dbf

/ u01/app/oracle/oradata/ora12c/sysaux01.dbf

/ u01/app/oracle/oradata/ora12c/undotbs01.dbf

/ u01/app/oracle/oradata/ora12c/users01.dbf

Destination data file path

SYS@OCM12C > select name from v$datafile

NAME

/ 12c/app/oracle/oradata/OCM12C/datafile/o1_mf_system_8xf29zsz_.dbf

/ 12c/app/oracle/oradata/OCM12C/datafile/o1_mf_sysaux_8xf1zgd7_.dbf

/ 12c/app/oracle/oradata/OCM12C/datafile/o1_mf_undotbs1_8xf2pgsg_.dbf

/ 12c/app/oracle/oradata/OCM12C/datafile/o1_mf_users_8xf2p505_.dbf

3. Scp the tablespace data file on the source side to the data file path on the destination side

SYS@ora12c >! scp / u01/app/oracle/oradata/ora12c/example01.dbf 172.16.228.9:/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf

Oracle@172.16.228.9's password:

Example01.dbf 100% 323MB 32.3MB/s 00:10

SYS@ora12c > exit

4. Use the data pump to export the metadata scp of the tablespace example to the data pump directory of the target side (set to dp_dir=/home/oracle as well as the source side)

[oracle@snow ~] $expdp dp/dp directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example

Export: Release 12.1.0.1.0-Production on Mon Feb 9 12:45:28 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DP". "SYS_EXPORT_TRANSPORTABLE_01": dp/* directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY

Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "DP". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

*

Dump file set for DP.SYS_EXPORT_TRANSPORTABLE_01 is:

/ home/oracle/trans.dmp

*

Datafiles required for transportable tablespace EXAMPLE:

/ u01/app/oracle/oradata/ora12c/example01.dbf

Job "DP". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Feb 9 12:46:34 2015 elapsed 0 00:01:04

[oracle@snow ~] $scp trans.dmp 172.16.228.9:/home/oracle

The authenticity of host '172.16.228.9 (172.16.228.9)' can't be established.

RSA key fingerprint is 70:7d:ec:8f:42:44:21:c9:24:d3:fc:23:1e:20:4b:ec.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '172.16.228.9' (RSA) to the list of known hosts.

Oracle@172.16.228.9's password:

Trans.dmp 100% 3172KB 3.1MB/s 00:00

6. Import the metadata into the target database

[oracle@test ~] $impdp hr/hr directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf

Import: Release 12.1.0.1.0-Production on Wed Feb 25 18:01:16 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "HR". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "HR". "SYS_IMPORT_TRANSPORTABLE_01": hr/* directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29342: user PM does not exist in the database

Job "HR". "SYS_IMPORT_TRANSPORTABLE_01" stopped due to fata

The error indicates that there is no PM user. After creating the user, re-executing impdp prompts that the SH,oe,ix user does not exist. Create the above users one by one.

SYS@OCM12C > create user pm identified by pm

SYS@OCM12C > create user sh identified by sh

SYS@OCM12C > create user oe identified by oe

SYS@OCM12C > create user ix identified by ix

Impdp executed successfully again after adding user

7, modify the tablespace to the read write state on the source side and the destination side, respectively

SYS@OCM12C > alter tablespace example read write

Tablespace altered.

SYS@OCM12C > select tablespace_name,status from dba_tablespaces

TABLESPACE_NAME STATUS

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

SYS@ora12c > alter tablespace example read write

Tablespace altered.

SYS@ora12c > select tablespace_name,status from dba_tablespaces

TABLESPACE_NAME STATUS

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

On how to parse the data punp transport table space to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.