Oracle数据库迁移方式一:impdp+dblink
实验环境: 源库: 192.168.2.200 SID=testdb 目标库:192.168.2.100 SID=testdb 实验目的: 使用impdp+dblink的方式 将192.168.2.200数据库上的jtrms用户的所有数据导入到目标数据库192.168.2.100 注意:使用impdp+dblink 的方式,这样就节省了数据导出(expdp),然后再导入的过程。 实施步骤: 一、源库操作: 1、首先检查源库要迁移的用户信息: SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'JTRMS'; ---检查用户的默认表空间 USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ JTRMS TEST 2、该用户的表空间大小 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; ----检查该用户一共有97张表 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、查看该用户下的索引信息: SQL> set linesize 300 pagesize 300 SQL> select INDEX_NAME,TABLE_OWNER,STATUS,NUM_ROWS from user_indexes; 4、查看该用户下所有的job信息 alter session set nls_date_format = 'yyyy-mm-dd hh24: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; 二、在目标数据库上操作 注意:在目标数据库创建用户信息之前,一定要检查目标看是否有该用户的信息,和表空间是否有重名等 1、在目标数据库上创建用户信息---一切以源库为标准 SQL> create tablespace test datafile '/opt/oracle/test.dbf' size 300M; ---创建test表空间 SQL> create user jtrms identified by jtrms default tablespace test; ---创建jtrms用户 SQL> grant connect,resource to jtrms; ---对该用户授权 SQL> grant dba to jtrms; 2、在目标数据库增加源库的tns文件信息,并测试连接源库 [oracle@test ~]$ vim tnsnames.ora testdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb) ) ) ---注意,增加的是源库192.168.2.200的信息 [oracle@test ~]$ sqlplus jtrms/jtrms@testdb ; ----使用jtrms用户连接源库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、在目标数据库上创建dblink 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) ) )'; 注意:该dblink是连接源库用的 4、在目标数据库上,开始使用下面的命令将源库jtrms用户的数据使用dblink导入到目标库 [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: ---输入用户的密码 5、登录数据库,验证数据是否传输成功: [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,Data Mining and Real Application Testing options SQL> conn jtrms/jtrms SQL> select count(*) from tab; COUNT(*) ---------- 97
SQL> set linesize 300 pagesize 300 SQL> select INDEX_NAME,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 -----至此,实施完成; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |