2 利用db link迁移数据库：参数 network_link

2 利用db link迁移数据库：参数 network_link
  impdp+network link 跳过expdp直接导入目标库：
 示例：
实验环境：linux下11G，一式两份 （手头只有11G虚拟机）
要点：需要用到监听，以下是源库的 监听信息和 目标库的tnsname信息


源库  listener.ora:
[oracle@primary admin]$ cat l*
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2)
      (SID_NAME = orcl)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    )
  )


源库
[oracle@primary admin]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-OCT-2011 10:52:07


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-OCT-2011 09:55:14
Uptime                    0 days 0 hr. 56 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB.demo" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "primary.demo" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully




目标库   tnsname.ora
[oracle@standby admin]$ cat t*
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )


--service_name里的orcl对应 源库lsnrctl status 是显示的instance名字


配置好监听和tnsnames后步骤：


源库：创建测试用表
SQL> conn system/zzzzzz;
SQL> create table test123 (id number(9));


Table created.


SQL> commit;


Commit complete.




目标库：
SQL> create public database link test111  connect to system identified by zzzzzz using 'PRIMARY';
 
--test123为自定义的link 名字
--using ''内的 primary是对应tnsname里的tnsname


目标库：
[oracle@standby admin]$ impdp system/zzzzzz  network_link=test111 schemas=system;
即 将原库 system用户下信息导入目标库 system用户下，
如果是异用户，参考：
impdp system/zzzzzz  network_link=test111 schemas=user1  remap_schema=user1:user2


以下为impdp过程中的日志信息，比较多，贴了一部分
Import: Release 11.2.0.1.0 - Production on Mon Oct 24 10:22:50 2011


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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** network_link=test123 schemas=system
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"SYSTEM"."PUBLICSYN" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."PRODUCT_USER_PROFILE" already exists
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"SYSTEM"."REPCAT$_OBJECT_NULL_VECTOR" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_PARM_VALUES_S" already exists


ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_GENERIC" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SYSTEM"."DEF$_ERROR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
skipped due to table_exists_action of skip
ORA-39151: Table "SYSTEM"."DEF$_ORIGIN" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
. . imported "SYSTEM"."TEST123"                               0 rows
. . imported "SYSTEM"."TESTIMPDP2"                            0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 100 error(s) at 10:28:58


目标库：测试源库创建的test123表是否已经impdp到目标库上
SQL> conn system/zzzzzz;
Connected.
SQL> desc test123;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                            NUMBER(9)




sccussfull!