1.配置生产库监听

listener.ora在SID_LIST_LISTENER内加入：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /u01/ora10g)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:/u01/ora10g/oltp/bin/oraclr11.dll")
) --- 以上为系统自动建立的不用管它 ---- 一下内容是需要我们手动添加的

(SID_DESC =
(SID_NAME = PROD) -- 生产库的ORACLE_SID
(ORACLE_HOME = /u01/ora10g) --生产库的ORACLE_HOME
(GLOBAL_NAME = prod) --生产库对外提供的全局服务名可以与ORACLE_SID不一样
)
)

2.配置历史库监听

listener.ora在SID_LIST_LISTENER内加入：

(SID_DESC =

(SID_NAME = BK)

(ORACLE_HOME = /u01/ora10g)

(GLOBAL_NAME = hist)

)

3.配置生产库tns

prod =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

4.配置历史库的tns

tnsnames.ora：

hist =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = hist)

)

5.启动监听服务

分别启用lsnrctl，并使用tnsping测试是否生效。

[oracle@hp183 admin]$ lsnrctl start

[oracle@rac52 admin ]$ lsnrctl start

生产库：

bash-3.00$ tnsping hist

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.155)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = hist)))

OK (10 msec)

历史库：

[oracle@rac52 admin]$ tnsping pord

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.8.183)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = prod)))

OK (10 msec)

6.为scott用户分配权限

历史库：

SQL> GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK,CREATE MATERIALIZED VIEW TO scott;

SQL> CONN scott/tiger

SQL> CREATE PUBLIC DATABASE LINK prod_link CONNECT TO scott IDENTIFIED BY tiger USING 'to_prod';

create public database link + 远程的数据库的global_name

connect to + 用户名 identified by + 密码

using + 是本地建立的连接tns服务名;

7.DBLINK测试连接

在历史库上测试，连接是否成功,如果返回结果如下则连接成功：

SQL>SELECT * FROM dual@prod_link;

D

--

X

ITPub博客

【Oracle】配置DBLINK

最新文章