Oracle Database Migration Mode one: Impdp+dblink

Lab Environment:

SOURCE Library: 192.168.2.200 Sid=testdb

Target Library: 192.168.2.100 Sid=testdb

Experimental Purpose:

Use Impdp+dblink to import all data from a jtrms user on a 192.168.2.200 database to the target database 192.168.2.100

Note: Using Impdp+dblink, this saves the data export (EXPDP) and then the import process.

Implementation steps:

First, the Source Library operation:

1, first check the source library to migrate user information:

Sql> Select Username,default_tablespace from dba_users where USERNAME like ' jtrms '; ---Check the user's default tablespace

USERNAME Default_tablespace

------------------------------ ------------------------------

Jtrms TEST

2. The user's table space size

Sql> Select Tablespace_name,bytes from dba_data_files where tablespace_name like ' TEST ';

Tablespace_name BYTES

------------------------------ ----------

TEST 314572800

Sql> Show user;

USER is "jtrms"

Sql> select * from tab; ----Check that the user has a total of 97 sheets

Tname Tabtype Clusterid

------------------------------ ------- ----------

Ecr_announcement TABLE

Ecr_approval TABLE

Ecr_approval_to_receive TABLE

Ecr_bp_syn TABLE

Ecr_change TABLE

Jforum_themes TABLE

Jforum_topics TABLE

Jforum_topics_watch TABLE

Jforum_users TABLE

Jforum_user_groups TABLE

Jforum_vote_desc TABLE

Jforum_vote_results TABLE

Jforum_vote_voters TABLE

Jforum_words TABLE

SYS_TEMP_FBT TABLE

。。。。。。。

。。。。。。。

Selected rows.

3. View the index information under this user:

Sql> Set Linesize pagesize 300

Sql> select Index_name,table_owner,status,num_rows from User_indexes;

4. View all job information under this user

Alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';

Set Linesize pagesize 300

Col What for A50

Col interval for A50

Select Job,what,last_date,next_date,schema_user,interval from User_jobs;

Second, operate on the target database

Note: Before creating user information in the target database, be sure to check the target to see if there is information about the user, and if the table space has duplicate names, etc.

1. Create user information on the target database---everything to the source Library as the standard

sql> Create tablespace Test datafile '/opt/oracle/test.dbf ' size 300M; ---Create Test table space

Sql> create user jtrms identified by jtrms default tablespace test; ---create jtrms users

Sql> Grant Connect,resource to jtrms; ---Authorization for this user

Sql> Grant DBA to Jtrms;

2. Increase the TNS file information of the source library in the target database and test the connection Source Library

[Email protected] ~]$ vim Tnsnames.ora

TestDB =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.200) (PORT = 1521))

)

(Connect_data =

(service_name = TestDB)

)

)

---Note that the added information for the Source library 192.168.2.200

[Email protected] ~]$ sqlplus jtrms/[email protected]; ----Use the jtrms user to connect to the source library 192.168.2.200

Sql*plus:release 11.2.0.4.0 Production on Mon June 5 00:54:31 2017

Copyright (c) 1982, Oracle. 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

Sql> select instance_name from V$instance;

Instance_name

----------------

TestDB

3. Create dblink on the target database

Create Public database link Testlink connect to jtrms identified by jtrms

Using ' (DESCRIPTION =

(address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.200) (PORT = 1521))

)

(Connect_data =

(service_name = TestDB)

)

) ';

Note: This dblink is used for connection to the Source library.

4. On the target database, start using the following command to import the data from the source Library Jtrms user to the target library using Dblink

[Email protected] ~]$ impdp jtrms network_link=testlink schemas=jtrms cluster=n parallel=2

Import:release 11.2.0.4.0-production on Mon June 5 01:03:16 2017

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

Password: ---Enter the user's password

5. Log in to the database to verify that the data is transmitted successfully:

[Email protected] ~]$ sqlplus '/as sysdba '

Sql*plus:release 11.2.0.4.0 Production on Mon June 5 01:06:34 2017

Copyright (c) 1982, Oracle. 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

Sql> Conn Jtrms/jtrms

Sql> Select COUNT (*) from Tab;

COUNT (*)

----------

97

Sql> Set Linesize pagesize 300

Sql> select Index_name,table_owner,status,num_rows from User_indexes;

Index_name Table_owner STATUS num_rows

------------------------------ ------------------------------ -------- ----------

Idx_bok_rel jtrms VALID 0

Idx_bok_user jtrms VALID 0

sys_c0011373 jtrms VALID 0

sys_c0011372 jtrms VALID 0

Idx_banlist_email jtrms VALID 0

-----So far, the implementation is complete;

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1933364

Oracle Database Migration Mode one: Impdp+dblink