Oracle 将一台机器上的数据库注册到另一台机器的监听上

本文简单介绍一下把一台机器上的数据库实例注册到另外一台机器上的步骤,比如把机器A的数据库实例注册到机器B.

1,在机器A注册到机器B之前查看一下机器B的listener的status,如下:

C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-JAN-2012 17:10:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=147.151.240.89)(PORT=1521)))
STATUS of the LISTENER
------------------------
Services Summary...
Service "ORCL" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
现在还没有机器A的注册信息.

2,在机器A的tnsnames.ora中加入:

LISTENE_R =
(ADDRESS = (PROTOCOL = TCP)(HOST =147.151.240.89)(PORT = 1521))

3,修改机器A的remote_listener为第一步配置的LISTENE_R

SQL> alter system set remote_listener=LISTENE_R;
System altered.

4,查看机器B的listener的status如下:

C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-JAN-2012 17:27:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=147.151.240.89)(PORT=1521)))
STATUS of the LISTENER
------------------------

Services Summary...
Service "ORCL" has 3 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ORCLTEST" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 2 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 2 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

可以看到机器A的数据库实例注册到机器B的监听上,机器A的db_name和service_name会注册到机器B的监听.

下面是在机器A上查看的信息:

SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- --------------
db_name string orcl
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- --------------
service_names string ORCLTEST

5,在机器B的tnsnames.ora里加上:

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcltest)
)
)

orcltest是从机器A注册到机器B的服务.

在机器B上通过sqlplus连接到机器A的数据库实例

C:\Documents and Settings\Administrator>sqlplus yorker/yorker@testdb
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 16 17:45:24 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from test;

ID TYPE
---------- ----------
1 1

可以成功的访问机器A上的表.

在实际中很少有这种需求应用,RAC需要实例相互注册到对方的监听中,这里仅仅是模拟把一台机器的数据库实例注册到另一台机器.