Oracle Virtualization and Cloud Consulting
view counter

"Warning: You are no longer connected to ORACLE" while converting database using DGMGRL.

Thanks to Sabdar Syed for this story

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 databaseORA-12514: TNS:listener does not currently know of service requested in connect descriptorFailed.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@orclPassword: <enter the password here>Connected.Show the configuration:DGMGRL> show configuration;Configuration - DGConfig01  Protection Mode: MaxPerformance  Databases:    orcl - Primary database    stby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESSConvert 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 successfullyNote: 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 databaseORA-12514: TNS:listener does not currently know of service requested in connect descriptorFailed.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 followsSID_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@stbyPassword: <enter the password here>Connected.Show the current value for StaticConnectIdentifier propertyDGMGRL> 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 propertyDGMGRL> 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" updatedNote: Above is a one line commandDGMGRL> 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 enabledFailed.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 /nologSQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 10 23:34:47 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> connect /as sysdbaConnected to an idle instance.SQL> startup mount;ORACLE instance started.Total System Global Area  680607744 bytesFixed Size                  2216464 bytesVariable Size             406851056 bytesDatabase Buffers          264241152 bytesRedo Buffers                7299072 bytesDatabase mounted.Now start conversion:DGMGRL> connect sys@stbyPassword: <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 openDatabase 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 successfullyThis time the conversion got succeeded. Sometimes, these warning messages may encountered while switch over activity as well.Regards,Sabdar Syed.http://sabdarsyed.blogspot.com/

Oracle Virtualization and Cloud Consulting
view counter

Read the entire article at its source

view counter