Detailed explanation of Oracle 12C realizing cross network database transmission

Time:2020-1-15

Preface

When the database is transmitted across the network, you can perform the import operation by using the network link parameter. The import operation will use the database link and does not need to generate the dump file.

The operation steps are as follows:

1. Create a data link in the target database that is linked to the source database. The user who performs the import operation must have the datapump? Imp? Full? Database permission, and the data link to the source database must also be connected to a user with the role of datapump? Exp? Full? Database. Users cannot have SYSDBA administration rights in the source database.

2. Make all user tablespaces read-only on the source database

3. Transfer data files related to all user tablespaces in the source database to the target database. If the byte encoding of the source platform is different from that of the target platform, query the V $transportable platform view to view it. And you will be able to transform the data file using one of the following methods:
. use the get file or put file procedure in the DBMS file transfer package to transfer data files. These processes automatically convert the byte encoding of the data file to the byte encoding of the target platform.

. use RMAN’s convert command to convert the byte encoding of the data file to the byte encoding of the target platform.

4. Perform the import operation on the target database. Use the data pump tool to import the metadata of all user tablespaces and manage the metadata and real data of tablespaces.

Make sure the following parameters are set correctly:


.transportable=always
.transport_datafiles=list_of_datafiles
.full=y
.network_link=database_link
.version=12

If the source database is 11.2.0.3 or later, version = 12 must be set. If the source database and the target database are both 12C, the version parameter is not set.

If the source database contains any encrypted tablespaces or if the tablespaces contain encrypted columns, you must either specify encryption \ PWD \ prompt = yes or specify the encryption \ password parameter.

Data pump cross network import will copy the metadata of all the objects stored in the user table space and the real data of the meta and user objects in the management table space. When the import is complete, the user tablespace will be placed in read-write mode.

5. Optional sets all user tablespaces in the source database to read-write mode.

The following example is to transfer the source database jyrac to the target database jypdb

1. Use sys user in the target database to create data link to the source database. The user in the source database is JY


SQL> conn sys/[email protected] as sysdba
Connected.


SQL> create public database link jyrac_link
 2  connect to jy identified by "jy"
 3  using '(DESCRIPTION =
 4   (ADDRESS_LIST =
 5    (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
 6   )
 7   (CONNECT_DATA =
 8    (SERVER = DEDICATED)
 9    (SERVICE_NAME =jyrac)
 10   )
 11  )';

Database link created.

2. Set all user tablespaces to read-only mode on the source database


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME    STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1      ONLINE
TEMP       ONLINE
USERS       ONLINE
UNDOTBS2      ONLINE
EXAMPLE      ONLINE
TEST       ONLINE

8 rows selected.

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME    STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1      ONLINE
TEMP       ONLINE
USERS       READ ONLY
UNDOTBS2      ONLINE
EXAMPLE      READ ONLY
TEST       READ ONLY

8 rows selected.

3. Use the get file procedure in the DBMS file transfer package in the target database to transfer all user tablespace related data files in the source database to the target database

Create the directory TTS? Datafile in the source database


SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

Create the directory TTS? Datafile in the target database


SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

Execute the DBMS file transfer.get file procedure in the target database to transfer the data files related to all user tablespaces in the source database to the target database


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf');

PL/SQL procedure successfully completed.

ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type  Redund Striped Time    Sys Name
DATAFILE UNPROT COARSE JUN 02 16:00:00 N users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE UNPROT COARSE JUN 02 16:00:00 N test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE UNPROT COARSE JUN 02 16:00:00 N example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.300.945620337
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.299.945620391
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.298.945620417
DATAFILE UNPROT COARSE JUN 02 00:00:00 N testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDO_2.277.939167063
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS2.278.945029905
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS1.273.939167015
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y TESTTB.295.944828399
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSTEM.274.939167015
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSAUX.275.939167015

4. Perform the import operation on the target database. Use the data pump tool to import the metadata of all user tablespaces and manage the metadata and real data of tablespaces.


[oracle@jytest1 tts]$ impdp system/[email protected]_175 full=y network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' version=12 directory=TTS_DUMP_LOG logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri Jun 2 16:30:40 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf version=12 directory=TTS_DUMP_LOG logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
.......
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1689 error(s) at Fri Jun 2 16:43:47 2017 elapsed 0 00:13:03

After the transfer, we will check whether the data of user JY’s DBA table is consistent with the data in the source database after the transfer.

Source database


SQL> conn sys/[email protected] as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

 COUNT(*)
----------
  2141

Target database


SQL> conn sys/[email protected] as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

 COUNT(*)
----------
  2141

Query whether the state of user tablespace is online after transmission. You can see that the state of test, example and users tablespace is online


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME    STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1      ONLINE
TEMP       ONLINE
UNDO_2       ONLINE
USERS       ONLINE
TESTTB       ONLINE
TEMP2       ONLINE
TEMP3       ONLINE
EXAMPLE      ONLINE
TEST       ONLINE
UNDOTBS2      ONLINE

12 rows selected.

5. Set all user tablespaces in the source database to read-write mode


SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME    STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1      ONLINE
TEMP       ONLINE
USERS       ONLINE
UNDOTBS2      ONLINE
EXAMPLE      ONLINE
TEST       ONLINE

8 rows selected.

This completes the operation of performing the complete database transmission through the network.

summary

The above is the whole content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message and communicate with us. Thank you for your support for developpaer.

Recommended Today

Rust and python: why rust can replace Python

In this guide, we compare the rust and python programming languages. We will discuss the applicable use cases in each case, review the advantages and disadvantages of using rust and python, and explain why rust might replace python. I will introduce the following: What is rust? What is Python? When to use rust When to […]