Oracle uses SQL to transmit tablespaces

Oracle uses SQL to transmit tablespaces

Source environment: RHEL 6.4 + Oracle 11.2.0.4
Target environment: RHEL 6.4 + Oracle 11.2.0.4 dual-node DG
Requirement: use SQL to transmit the tablespace DBS_D_JINGYU from the source environment to the target environment.

• 1. Create a directory
• 2. Check the consistency of the table space.
• 3. expdp export table space metadata
• 4. Copy the dump file and data file to the target database server.
• 5. Import the impdp tablespace to the target database.
• 6. Set tablespace to read/write (source database and target database)

1. Create a directory

-- Create directory src_dpump_dir as '/tmp' in the source database; create directory src_dbf_dir as '/oradata/data/SHITAN/datafile'; -- create directory dest_dpump_dir as '/tmp' in the target database '; create directory dest_dbf_dir as '/u01/oradata01/CHICAGO/datafile ';

2. Check the consistency of the table space.

Exec dbms_tts.transport_set_check ('dbs _ D_JINGYU ', TRUE); select * from transport_set_violations; -- determine whether the tablespace is set to read-only alter tablespace DBS_D_JINGYU read only;

3. expdp export table space metadata

Expdp system/oracle dumpfile = EXPDP_META_SRC.DMP directory = src_dpump_dir transport_tablespaces = DBS_D_JINGYU -- exported successfully. Some log information is as follows: **************************************** * ************************************ Dump file set for SYSTEM. SYS_EXPORT_TRANSPORTABLE_01 is: /tmp/EXPDP_META_SRC.DMP ************************************ **************************************** ** Datafiles required for transportable tablespace DBS_D_JINGYU: /oradata/data/SHITAN/datafile/o1_mf_dbs_d_ji_cb0dbxt0 _. dbfJob "SYSTEM ". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 21 16:22:50 2016 elapsed 0 00:01:15

4. Copy the dump file and data file to the target database server.

-- Create public database link dest34 connect to system identified by oracle using 'destdb'; select * from dual @ dest34; -- copy the database file begin dbms_file_transfer.put_file ('src _ dbf_dir ', 'o1 _ mf_dbs_d_ji_cb0dbxt0 _. dbf', 'dest_dbf_dir ', 'o1 _ mf_dbs_d_ji_cb0dbxt0 _. dbf ', 'dest34'); end;/-- copy the dump file begin dbms_file_transfer.put_file ('src _ dpump_dir', 'expdp _ META_SRC.DMP ', 'dest_dpump_dir ', 'expdp _ META_SRC.DMP ', 'dest34'); end ;/

5. Import the impdp tablespace to the target database.

-- You must first create a user jingyu and grant the permission to create user jingyu identified by jingyu; grant dba to jingyu; -- import tablespace impdp system/oracle dumpfile = EXPDP_META_SRC.DMP directory = dest_dpump_dir transport_datafiles =/u01/oradata01/CHICAGO/datafile/ow.mf_dbs_d_ji_cb0dbxt0 _. dbf -- change the default tablespace and temporary tablespace of jingyu (optional, recommended) create temporary tablespace temp_jingyu tempfile size 30 M autoextend off; alter user jingyu temporary tablespace temp_jingyu default tablespace dbs_d_jingyu quota unlimited on dbs_d_jingyu;

6. Set tablespace to read/write (source database and target database)

-- Check the space STATUS select TABLESPACE_NAME, STATUS from dba_tablespaces; -- set the tablespace to read/write STATUS alter tablespace DBS_D_JINGYU read write;