oracle双机rac配制dblink,RAC创建DBlink并使用impdp抽取源库数据

赋权并创建dblink

[oracle@zhongwc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 10:26:45 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> grant create database link to zwc;

Grant succeeded.

SQL> conn zwc/

Enter password:

Connected.

SQL> show user

USER is "ZWC"

SQL> select tname from tab;

no rows selected

create public database link ZWC

connect to zhongwc identified by zhongwc

using '(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ZWC)

)

9 )';

Database link created.

SQL> select * from dual@zwc;

D

-

X

SQL> show user

USER is "ZWC"

SQL> select tname from tab;

no rows selected

SQL> select tname from tab@zwc;

TNAME

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

T_ZHONGWC

SQL> select count(*) from t_zhongwc;

select count(*) from t_zhongwc

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select count(*) from t_zhongwc@zwc;

COUNT(*)

----------

75453

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[oracle@zhongwc1 ~]$ impdp system/oracle network_link=zwc schemas=zhongwc remap_schema=zhongwc:zwc

Import: Release 11.2.0.3.0 - Production on Mon Feb 4 10:47:45 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=zwc schemas=zhongwc remap_schema=zhongwc:zwc

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 9 MB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"ZWC" already exists

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/TABLE/TABLE

. . imported "ZWC"."T_ZHONGWC" 75453 rows

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 10:48:32

验证

[oracle@zhongwc1 ~]$ sqlplus zwc

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 10:50:09 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select count(*) from t_zhongwc@zwc;

COUNT(*)

----------

75453

SQL> select count(*) from t_zhongwc;

COUNT(*)

----------

75453

SQL> drop public database link zwc;

Database link dropped.

SQL> select count(*) from t_zhongwc@zwc;

select count(*) from t_zhongwc@zwc

*

ERROR at line 1:

ORA-02019: connection description for remote database not found

SQL> select count(*) from t_zhongwc;

COUNT(*)

----------

75453