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:
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.
Here we add in the listener the following SID_DESC to the listener.
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:
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.
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
In version 10.2 of the RDBMS when an instance registers with its listeners it will register a default service with the name
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:Summary:_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.
Setup a Dataguard enviroment do not forget to define the
The global dbname in listener configuration will be the static service name.
Before we start, remember that DG broker does not have the ability to create standby and is used for managing the dataguard configuration. Before proceeding with step-by-step instructions on how to set up DG broker, I would recommend you to get comfortable with the concepts first.
Physical Standby Concepts
The following configuration was tested on RHEL4U2 64-bit with Oracle 10.1.0.5 database.
Pri db_unique_name = 'TESTPRI'
DG db_unique_name = 'TESTDG'
Configuration(any name) = 'DG_TEST'
sys password = 'sys'
Pri conn stg = 'TESTPRI'
DG db_unique_name = 'TESTDG'
Configuration(any name) = 'DG_TEST'
sys password = 'sys'
Pri conn stg = 'TESTPRI'
1. Set up init parameters on primary to enable broker
Note: For RAC, ensure dg_broker_config_files are on shared storage and accessible to all the instances.
Note: Broker config files are named as dr1<<>.dat and < db_unique_name>>.dat
SQL> alter system set dg_broker_start=false sid='*';
System altered.
SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
System altered.
SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
2. Verify if DMON process has started on all the instances of primary. Example:
$ ps -ef|grep dmon|grep -v grep
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR1
$ ps -ef|grep dmon|grep -v grep
oracle 29723 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR2
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR1
$ ps -ef|grep dmon|grep -v grep
oracle 29723 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR2
3. Set up init parameters on standby
SQL> alter system set dg_broker_start=false sid='*';
System altered.
SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
System altered.
SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
4. GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>>
This is important otherwise you'll have TNS-12154 error during switchover operation.
Example:
SID_LIST_LISTENER_TESTPRI =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/10g/db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = TESTPRIR1)
(GLOBAL_DBNAME = TESTPRI_DGMGRL)
(ORACLE_HOME = /apps/oracle/product/10g/db)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/10g/db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = TESTPRIR1)
(GLOBAL_DBNAME = TESTPRI_DGMGRL)
(ORACLE_HOME = /apps/oracle/product/10g/db)
)
)
5. DGMGRL Configuration
5.1Connect
DGMGRL> CONNECT sys/sys
Connected.
5.2Create Configuration
DGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;
Configuration "DG_TEST" created with primary database "TESTPRI".
5.3Verify configuration
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_TEST
Enabled: NO
Protection Mode: MaxPerformance
Databases:
TESTPRI - Primary database
Current status for "DG_TEST":
DISABLED
5.4Verify database; if RAC verify if all instances are validated
DGMGRL> show database 'TESTPRI';
Database
Name: TESTPRI
Role: PRIMARY
Enabled: NO
Intended State: ONLINE
Instance(s):
TESTPRIR1
TESTPRIR2
Current status for "TESTPRI":
DISABLED
5.5Add standby database to the configuration
DGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;
Database "TESTDG" added.
5.6Enable the broker
DGMGRL> ENABLE CONFIGURATION;
Enabled.
5.7Verfying again
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_TEST
Enabled: YES
Protection Mode: MaxPerformance
Databases:
TESTPRI - Primary database
TESTDG - Physical standby database
Current status for "DG_TEST":
SUCCESS
6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available
Fix
Set dg_broker_start=true
Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings
Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.
Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';
Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
TESTPRIR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
TESTPRIR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.
Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
TESTPRIR2 ArchiveLagTarget 0 0
TESTPRIR2 LogArchiveMaxProcesses 4 2 4
Example
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
Fix
DGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;
or
SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
More commands
DGMGRL> SHOW DATABASE VERBOSE 'dbname';
This will show all property values in detail
DGMGRL> HELP;
List of all broker commands with usage help
5.1Connect
DGMGRL> CONNECT sys/sys
Connected.
5.2Create Configuration
DGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;
Configuration "DG_TEST" created with primary database "TESTPRI".
5.3Verify configuration
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_TEST
Enabled: NO
Protection Mode: MaxPerformance
Databases:
TESTPRI - Primary database
Current status for "DG_TEST":
DISABLED
5.4Verify database; if RAC verify if all instances are validated
DGMGRL> show database 'TESTPRI';
Database
Name: TESTPRI
Role: PRIMARY
Enabled: NO
Intended State: ONLINE
Instance(s):
TESTPRIR1
TESTPRIR2
Current status for "TESTPRI":
DISABLED
5.5Add standby database to the configuration
DGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;
Database "TESTDG" added.
5.6Enable the broker
DGMGRL> ENABLE CONFIGURATION;
Enabled.
5.7Verfying again
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_TEST
Enabled: YES
Protection Mode: MaxPerformance
Databases:
TESTPRI - Primary database
TESTDG - Physical standby database
Current status for "DG_TEST":
SUCCESS
6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available
Fix
Set dg_broker_start=true
Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings
Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.
Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';
Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
TESTPRIR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
TESTPRIR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.
Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
TESTPRIR2 ArchiveLagTarget 0 0
TESTPRIR2 LogArchiveMaxProcesses 4 2 4
Example
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
Fix
DGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;
or
SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
More commands
DGMGRL> SHOW DATABASE VERBOSE 'dbname';
This will show all property values in detail
DGMGRL> HELP;
List of all broker commands with usage help
Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';
SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';
SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;
SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';
SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';
DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';
SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';
SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;
SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';
SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';
DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
================================================================================
Comments
Post a Comment
Oracle DBA Information