Monday, February 22, 2010

The global dbname in listener configuration will be the static service name (Solving ORA-12514:)

Static service name is an  important part in the failover and snapshot mechanisme by the Standby database for using as a HA and test enviroment.

Oracle Net Service uses the TNSNAME to resolve the path to the database and initiates a connection to the listener at the target system using the service name in the TNSNAME entry.

Command to change the snapshot database to physical standby database:
DGMRL> CONVERT DATABASE "JOORDS_DR0" TO PHYSICAL STANDBY;
During these operations the the snapshot database needs a restart of the database. When the database is down, the service that is specified by TNSNAME is not registerd so an Oracle Net Service connection cannot be made to the database.Result is a failed operation of the convert database to physical standby. Monitoring the listener.log will give you the information of the error. and what the request is

ORA-12514: listener does not currently know of the service request in the connect descriptor



Installing Dataguard Broker has a prerequisite. It has been there since Oracle Database 10G Release. The prerequisite configurqation for the listener is to create a specially named static listener entry for each database in the Broker configuration. This named static listener entry will be used by the Broker to connect to an Idle instance as SYSDBA.

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain in the LISTENER.ORA file.

How to setup a Static Service name in the listener:

You add a SID_LIST with the GLOBAL_DBNAME to the listener. The GLOBAL_DBNAME entry will be used as the referenced static service_name in the listener. With  this specially named static listener entry it is now possible to connect to and idle instance as sysdba by service names ,
Here we add in the listener the following SID_DESC to the listener.


(SID_DESC =
  (GLOBAL_DBNAME = DG1_DGMGRL.wh.com)
  (ORACLE_HOME = /u01/app/oracle/product/11.1.0.6/db_1)
  (SID_NAME = DG1)
)

The listener.ora configuration

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.wh.com)(PORT = 1532))
 )
 )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DG1)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0.6/db_1)
      (SID_NAME = DG1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DG1_DGMGRL.wh.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0.6/db_1)
      (SID_NAME = DG1)
    )
  )

Validation of the static service after reload with LSNRCTL command:

lsnrctl reload

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 16-FEB-2010 10:18:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.wh.com)(PORT=1532)))
The command completed successfully
lsnrctl services

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 16-FEB-2010 10:19:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.wh.com)(PORT=1532)))
Services Summary...
Service "DG1" has 2 instance(s).
  Instance "DG1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "DG1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DG1_XPT" has 1 instance(s).
  Instance "DG1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DG1_DGMGRL.wh.com" has 1 instance(s).
  Instance "DG1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


Service name _XPT
In version 10.2 of the RDBMS when an instance registers with its listeners it will register a default service with the name _XPT (e.g. DG1_XPT) in addition to the normal service names. This service does not cause a problem and is intended for use within Data Guard environments. Set init.ora parameter for the instance "__dg_broker_service_names=''". Please note that this setting begins with two underscore characters.
The instance will need to be restarted for this to take effect.
or execute in sqlplus:
alter system set "__dg_broker_service_names = '' scope=spfile;

  • How to stop the _XPT service from registering with the listener” Metalink Note 339940.1.
  • Diagnosing Connection Problems with an active Data Guard Broker Configuration Metalink Note 745201.1.
  • 10.2 Data Guard Physical Standby Switchover Metalink Note 751600.1.


Data Guard Broker Connection Model

When you start the Data Guard Broker (DMON)-Process on your Primary and Standby Database, it will start and register Services on the Local Listener:

_DGB.: This Service is used by the DMON-Processes to communicate between each other

_XPT.: This Service is used for Log Transport Services and FAL (corresponding Initialization Parameters are set once a Configuration is enabled) - (Oracle 10g only)

Starting with Oracle 11.1.0.x you can use your own Service and/or TNS-Alias used for Log Transport Services and FAL (corresponding Initialization Parameters are set once a Configuration is enabled). This Service is specified by the Data Guard Broker Property 'DGConnectIdentifier'. If you specify a TNS-Alias here you have to ensure this Alias is configured on all TNSNAMES.ORA's in the Data Guard Configuration.
If you want to perform Operations with the Data Guard Broker that require a Restart of any Database using DGMGRL (like Switchover), you also have to register an additional Static Service with the local Listener

_DGMGRL.: This Service is used to connect to a Database by the Data Guard Broker while it is shutdown
Starting with Oracle 11.2.0.x you can also setup and use your own Static Listener Entry. You will then have to set the Data Guard Broker Property 'StaticConnectIdentifier' to this Entry. The default Value still points to _DGMGRL-Entry.
Summary:
Setup a Dataguard enviroment do not forget to define the _DGMGRL.domain static entry in the listener.ora file of eache database including the primary database, even if you use Grid Control.
The global dbname in listener configuration will be the static service name.