为了达到实例宕掉但监听不宕的情况下也行, 监听需要全部是动态注册的, 不允许静态配置的服务名. 因为动态注册的话，当实例宕掉后
1. 主库检查和设置
假设新增的服务名为ORCL_TAF.LK.
SQL> exec dbms_service.create_service(service_name=>'ORCL_TAF.LK', network_name=>'ORCL_TAF.LK');
SQL> exec dbms_service.start_service(service_name=>'ORCL_TAF.LK');
SQL> CREATE OR REPLACE TRIGGER trg_oci_service
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role='PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('ORCL_TAF.LK');
END IF;
END;
/
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
service_names string ORCL_TAF.LK
若备库非实时应用,则手工归档确保触发器传到备库:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
确认可以显示新增的服务:
$ lsnrctl status
...
Service "ORCL_TAF.LK" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
2. 配置OCI客户端(包括纯OCI/OCCI客户端和JDBC-OCI客户端)
为了达到实例宕掉但监听不宕的情况下也行, 监听需要全部是动态注册的, 不允许静态配置的服务名. 因为动态注册的话，，当实例宕掉后，其服务名就会从监听中消失, 此时应用才会去偿试第二个IP地址. 如果有静态配置的话, 则由于监听是可以接受客户端的请求, 将不会去偿试第二个IP地址, 而是报出Oracle not available的错误.
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
--------------------------------------------------------------------------------
DB_ORCL=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=primary_host)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=standby_host)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ORCL_TAF.LK)
)
)
--------------------------------------------------------------------------------
注意: ADDRESS_LIST中包含主备库服务器和端口信息, 其中SERVICE_NAME为新增的服务名ORCL_TAF.LK.
对于JDBC-OCI客户端配置方法为"jdbc:oracle:oci8:@DB_ORCL".
--End--
