Applies to:
Oracle Net Services - Version 11.2.0.2 and later
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 to 19.3.0.0.0 [Release 19]
Information in this document applies to any platform.
Goal
This document describes how to configure the listener for a static handler.
It is necessary in some circumstances to connect via the listener to an instance that is not open or running.
This document explains how to edit a listener.ora file so that a static handler is available for an instance that
Is not registered. It focuses on 4 situations where this might be necessary:
1) Static Listener Registration Syntax for Stand-Alone Listener
2) Static Listener Registration Syntax in RAC
3) Static Listener Registration for 12c Multitenant (PDB)
4) Static Listener Registration Syntax on a Primary , Standby Database (Data Guard)
Solution
1) Static Listener Registration Syntax for Stand-Alone Listener :
SID_LIST_listener_name_here=
(SID_LIST=
(SID_DESC=
(SID_NAME= <instance_name>) ================> The instance name which needs to appear on listener
(GLOBAL_DBNAME = <service_name> )
(ORACLE_HOME= <oracle home path>) ================> ORACLE_HOME path must be set correct.
)
)
SID_NAME = obtain the SID value from the INSTANCE_NAME parameter in the initialization parameter file.
GLOBAL_DBNAME = The listener tries to match the value of this parameter with the value of the SERVICE_NAME parameter in the client connect descriptor.
The value for this parameter is typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization parameter file,
But the value can also contain any valid name used by clients to identify the service.
2) Static Listener Registration Syntax for RAC Listener :
SID_LIST_listener_name_here=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=<db_unique_name>) ================> The service name which needs to appear on listener
(ORACLE_HOME= <oracle_home path>)
(SID_NAME=<sid_name_1>)) ================> The instance name which needs to appear on listener
(SID_DESC=
(GLOBAL_DBNAME=<db_unique_name>)
(ORACLE_HOME=<oracle_home path>)
(SID_NAME=<sid_name_2>)
)
)
For RAC One Node databases the SID_LIST entries in the listener.ora files on each possible host must each contain two SID_DESC clauses, one for each of the SID_NAME values that correspond to the two possible RAC One Node instances.
3) Static Listener Registration Syntax for 12c Listener :
SID_LIST_listener_name_here =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdbname) <== This is our static label for the static PDB. This must match the actual PDB service name
(ORACLE_HOME = <Oracle_Home_Path>) <== ORACLE_HOME path must be correct.
(SID_NAME = CDBname) <== This is our actual CDB instance name
)
)
USE_SID_AS_SERVICE_listener_name_here=ON
4) Static Listener Registration Syntax on a Primary , Standby Database for shipping logs :
Primary :
Unique name is decided updated in pfile :
db_unique_name=orcl_p
Update Listener with static entry Listener.ora file :
SID_LIST_listener_name_here =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_p) ===============> db_unique_name on Primary db
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl) ===============> Instance_name in Primary db
)
)
Standby:
Unique name is decided updated in pfile :
db_unique_name=orcl_s
Update Listener with static entry :
SID_LIST_listener_name_here =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_s) ===============> db_unique_name on Standby db
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcls) ===============> Instance_name in Standby db
)
)
Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
SERVICE_NAME Parameter - Resolving The ORA-12514 Error (Doc ID 77640.1)
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
Creating a Physical Standby from Primary on Version 12c (Doc ID 1570958.1)
References
NOTE:1136945.1 - GRID: Dead Connection Detection or DCD and RAC 11.2 and higher
NOTE:151972.1 - Archived: Dead Connection Detection (DCD) Explained
NOTE:363638.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:1387859.1 - Oracle Data Guard Broker and Static Service Registration
