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