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

This article mainly shows you "Oracle 12C how to achieve cross-network database transmission", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Oracle 12C how to achieve cross-network database transmission" this article.

Preface

To transfer the database across the network, you can perform the import operation by using the network_link parameter, which uses the database link and does not need to generate a dump file.

The steps are as follows:

1. Create a data link in the target database to the source database. The user performing the import operation must have datapump_imp_full_database privileges, and the data link connected to the source database must be connected to a user with the datapump_exp_full_database role. Users cannot have sysdba administrative privileges in the source database.

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

3. Transfer all the data files related to the user tablespace in the source database to the target database. If the byte encodings of the source and target platforms are different, query the v$transportable_platform view to see. And you will be able to convert data files in one of the following ways:

. Use the get_file or put_file procedures 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 metadata for all user tablespaces and manage metadata and real data for tablespaces.

Ensure that the following parameters are set correctly:

.transplants = 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 than 11g, version=12 must be set. If both the source and target databases are 12c, the version parameter does not need to be set.

If the source database contains any encrypted tablespaces or tablespaces that contain encrypted columns, you must specify encryption_pwd_prompt=yes or specify the encryption_password parameter.

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

5. The optional operation 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. Create a data link to the source database as a sys user in the target database. The user in the source database is jy

SQL > conn sys/xxzx7817600@jypdb as sysdbaConnected.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.

two。 Set all user tablespaces to read-only mode on the source database

SQL > select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS---SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEUNDOTBS2 ONLINEEXAMPLE ONLINETEST ONLINE8 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 ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS READ ONLYUNDOTBS2 ONLINEEXAMPLE READ ONLYTEST READ ONLY8 rows selected.

3. Use the get_file procedure in the dbms_file_transfer package to transfer all user tablespace-related data files in the source database to the target database in the target database

Create a directory tts_datafile (store data files) 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 a directory tts_datafile (store data files) 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 process in the target database to transfer 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-ltType Redund Striped Time Sys NameDATAFILE UNPROT COARSE JUN 02 16:00:00 N users01.dbf = > + DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417DATAFILE UNPROT COARSE JUN 02 16:00:00 N test01.dbf = > + DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337DATAFILE UNPROT COARSE JUN 02 16:00:00 N example01.dbf = > + DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391DATAFILE UNPROT COARSE JUN 02 16: 00:00 Y FILE_TRANSFER.300.945620337DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.299.945620391DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.298.945620417DATAFILE UNPROT COARSE JUN 02 00:00:00 N testtb01.dbf = > + DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDO_2.277.939167063DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS2.278.945029905DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS1.273.939167015DATAFILE UNPROT COARSE JUN 02 00:00:00 Y TESTTB.295.944828399DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSTEM.274.939167015DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSAUX.275.939167015

4. Perform an import operation on the target database. Use the Data Pump tool to import metadata for all user tablespaces and manage metadata and real data for tablespaces.

[oracle@jytest1 tts] $impdp system/xxzx7817600@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.logImport: Release 12.2.0.1.0-Production on Fri Jun 2 16:30:40 2017Copyright (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 ProductionStarting "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.logEstimate in progress using BLOCKS method...Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACEProcessing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLKProcessing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATAProcessing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATAProcessing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA.Processing object type DATABASE_ EXPORT/SCHEMA/DIMENSIONProcessing object type DATABASE_EXPORT/END_PLUGTS_BLKProcessing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKERProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCEProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJProcessing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJProcessing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMAProcessing object type DATABASE_EXPORT/AUDITProcessing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKERORA-39082: Object type PROCEDURE: "APEX _ 030200 ".F" created with compilation warningsORA-39082: Object type PROCEDURE: "APEX_030200". "APEX_ADMIN" created with compilation warningsORA-39082: Object type PROCEDURE: "APEX_030200". "HTMLDB_ADMIN" created with compilation warningsJob "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 transmission is completed, we randomly check whether the data of the dba_tables table of the user jy is consistent with the data in the source database after transmission.

Source database

SQL > conn sys/xxzx7817600@jyrac as sysdbaConnected.SQL > select count (*) from jy.dba_tables; COUNT (*)-2141

Target database

SQL > conn sys/xxzx7817600@jypdb as sysdbaConnected.SQL > select count (*) from jy.dba_tables; COUNT (*)-2141

If you query whether the status of user tablespace is online after transmission, you can see that the status of test,example,users tablespace is online.

SQL > select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS---SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUNDO_2 ONLINEUSERS ONLINETESTTB ONLINETEMP2 ONLINETEMP3 ONLINEEXAMPLE ONLINETEST ONLINEUNDOTBS2 ONLINE12 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 ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEUNDOTBS2 ONLINEEXAMPLE ONLINETEST ONLINE8 rows selected.

At this point, the operation of performing a complete database transfer over the network is complete.

These are all the contents of the article "how to transfer databases across the network with Oracle 12C". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.