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博客 ” ,链接:http://blog.itpub.net/13304800/viewspace-736391/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13304800/viewspace-736391/