How Listener Configure SID_LIST_LISTENER

  • by

SID_LIST_LISTENER

Dynamic service is a representative which is registered with the listener when the instance is up and running on the server, whereas static service (SID_LIST_LISTENER) can be used to service external connections with or without a running instance on the server, it's extremely useful to connect to an idle or NOMOUNT database.

According to Oracle 19c Advanced Features of Oracle Net Services, the use cases of static services can be any one of the following:

  • External procedure calls
  • Oracle Heterogeneous Services
  • Oracle Data Guard
  • Remote database startup from a tool other than Oracle Enterprise Manager Cloud Control
  • Connections to Oracle databases earlier than Oracle8i release 2 (8.1)

1. Add Single Static Service

The configuration to enable the feature of static service registration is pretty easy, just add an entry called SID_LIST_LISTENER to the listener configuration file, which is usually at $ORACLE_HOME/network/admin/listener.ora and then restart the listener.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=ORCL))
  )

Basically, the static service registration needs to know at least two items, ORACLE_HOME and ORACLE_SID. As you can see, we have provided them in the entry.

2. Add Multiple Static Services

For more ORACLE_SID, you can just append SID_LIST to the entry like this.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=ORCL))
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=SMALLDB))

  )

3. Use GLOBAL_DBNAME

For those database whose DB_DOMAIN is not empty, you should add one more information GLOBAL_DBNAME in the SID_LIST.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=ORCL))
      (GLOBAL_DBNAME=orcl.example.com)
  )

The format of GLOBAL_DBNAME is as following:

<DB_UNIQUE_NAME>.<DB_DOMAIN>
In the above case, the database's DB_DOMAIN is example.com. Please note that, if your DB_DOMAIN is empty, you don't have to add it.

Don't forget to restart the listener to take effect immediately.

4. Static Service For Data Guard Broker

You have to add a special static service <DB_UNIQUE_NAME>_DGMGRL.<DB_DOMAIN> for data guard broker to enable broker operations that require restarting instances without manual intervention, such as role transition among primary and standby databases by switchover.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=ORCL))
      (GLOBAL_DBNAME=ORCL_DGMGRL)
  )

Please note that, if your DB_DOMAIN is empty, you don't have to add it. All service names had better be upper-cased.

Status of Service Names

Let's see what status they are in listener.

[oracle@test ~]$ lsnrctl status
...
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 "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

The status of the static service ORCL is tagged as UNKNOWN, whereas the dynamic one is READY which means the instance does exist and is ready to service.

Leave a Reply

Your email address will not be published. Required fields are marked *