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

As part of my OCM Exam preparation while practicing the Data Guard scenarios such as converting the database from physical standby tosnapshot standby and from snapshot standby to physical standbyusing 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@PRIMARY
Password: <enter the password here>
Connected.

Show the configuration:

DGMGRL> show configuration;

Configuration - DGConfig01

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - 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.standbydb in the listener.ora file was configured as follows

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stby_DGMGRL.standbydb)
      (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=standby.mydomain)(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.standbydb 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=standby.mydomain)(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=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL.standbydb)(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=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL.standbydb)(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