Shulou(Shulou.com)06/01 Report--

Experimental environment:

Source database: 192.168.2.200 SID=testdb

Target library: 192.168.2.100 SID=testdb

Experimental purpose:

Use impdp+dblink to import all the data of jtrms users on the 192.168.2.200 database into the target database 192.168.2.100

Note: using impdp+dblink saves the process of data export (expdp) and then import.

Implementation steps:

1. Source database operation:

1. First check the user information of the source database to be migrated:

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 size of the user's tablespace

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 tables

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

.

.

97 rows selected.

3. View the index information under the user:

SQL > set linesize 300 pagesize 300

SQL > select INDEX_NAME,TABLE_OWNER,STATUS,NUM_ROWS from user_indexes

4. View all the job information under this user

Alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss'

Set linesize 300 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 whether the tablespace has duplicate names, etc.

1. Create user information on the target database-everything is based on the source database

SQL > create tablespace test datafile'/ opt/oracle/test.dbf' size 300m;-create test tablespace

SQL > create user jtrms identified by jtrms default tablespace test;-create jtrms user

SQL > grant connect,resource to jtrms;-authorize this user

SQL > grant dba to jtrms

2. Add the tns file information of the source database to the target database, and test the connection to the source database.

[oracle@test ~] $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 information added to the source database is 192.168.2.200

[oracle@test ~] $sqlplus jtrms/jtrms@testdb;-use jtrms users to connect to the source database 192.168.2.200

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 00:54:31 2017

Copyright (c) 1982, 2013, 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 a 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 to connect to the source library.

4. On the target database, start importing the data of the jtrms user of the source database into the target database using dblink using the following command

[oracle@test ~] $impdp jtrms network_link=testlink schemas=jtrms cluster=N PARALLEL=2

Import: Release 11.2.0.4.0-Production on Mon Jun 5 01:03:16 2017

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

Password:-enter the user's password

5. Log in to the database to verify whether the data has been transferred successfully:

[oracle@test ~] $sqlplus'/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 01:06:34 2017

Copyright (c) 1982, 2013, 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 (*)

-

ninety-seven

SQL > set linesize 300 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

-at this point, the implementation is complete

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.