As part of my OCM Exam preparation while practicing the Data Guard scenarios such as converting the database from
physical standby to
snapshot standby and from
snapshot standby to
physical standby using Data Guard Broker, the following warning messages were generated:
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the CONVERT command:
start up and mount instance "stby" of database "stby"
Here is how, I was testing:
DGMGRL> connect sys@orcl
Password: <
enter the password here>
Connected.
Show the configuration:
DGMGRL> show configuration;
Configuration - DGConfig01
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Convert the Physical Standby Database to Snapshot Standby Database:
DGMGRL> convert database 'stby' to snapshot standby;
Converting database "stby" to a Snapshot Standby database, please wait...
Database "stby" converted successfully
Note: Converting the database from Physical Standby to Snapshot Standby database was succeeded, but it failed while reverting from Snapshot Standby to Physical Standby.
DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the CONVERT command:
start up and mount instance "stby" of database "stby"
Solution A:
First of all this is just a warning message, all you need to do is startup mount the database as instructed in the above warning message to resolve the issue.
But, the issue is that the Data Guard Broker was attempting to connect to a service called < db_unique_name>_DGMGRL i.e.
stby_DGMGRL. On the server, the service name
stby_DGMGRL.sabdar.com in the listener.ora file was configured as follows
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL.sabdar.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = stby)
)
To overcome from this warning message being generated and to fix the issue permanently, add a static registration for
stby_DGMGRL to your
$ORACLE_HOME/network/admin/listener.ora. Below is an example.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = stby)
)
)
Or Solution B:
Check the value of StaticConnectIdentifier property for stby database:
DGMGRL> show database 'stby' 'StaticConnectIdentifier';
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm01.sabdar.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=
stby_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'
Here the SERVICE_NAME is
stby_DGMGRL (by default it was set like this when Data Guard Broker configured).
Change the SERVICE_NAME from stby_DGMGRL to stby_DGMGRL.sabdar.com using Data Guard Broker
DGMGRL> connect sys@stby
Password: <
enter the password here>
Connected.
Show the current value for StaticConnectIdentifier property
DGMGRL> show database 'stby' 'StaticConnectIdentifier';
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm01.sabdar.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=
stby_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'
Edit the value for StaticConnectIdentifier property
DGMGRL> edit database 'stby' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm01.sabdar.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL.sabdar.com)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))';
Property "StaticConnectIdentifier" updated
Note: Above is a one line command
DGMGRL> show database 'stby' 'StaticConnectIdentifier';
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm01.sabdar.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=
stby_DGMGRL.sabdar.com)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'
Now start conversion:
DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Error: ORA-16541: database is not enabled
Failed.
Failed to convert database "stby"
Note: Here we have to first start the standby database in mount mode. Then try the '
convert database' command.
[oracle@ocm01 ~]$ export ORACLE_SID=stby
[oracle@ocm01 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 10 23:34:47 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2216464 bytes
Variable Size 406851056 bytes
Database Buffers 264241152 bytes
Redo Buffers 7299072 bytes
Database mounted.
Now start conversion:
DGMGRL> connect sys@stby
Password: <
enter the password here>
Connected.
DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Database "stby" converted successfully
This time the conversion got succeeded. Sometimes, these warning messages may encountered while switch over activity as well.
Regards,
Sabdar Syed.
