PROD – track@oel6-node01.localdomain
Standby - trackdg@oel6-rac01.localdomain
We are doing a switchover
DGMGRL> show configuration;
Configuration - track
Protection Mode: MaxPerformance
Members:
track - Primary database
trackdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> SHOW DATABASE track
Database - track
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
track
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE trackdg
Database - trackdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: OFF
Instance(s):
trackdg
Database Status:
SUCCESS
try switchover
DGMGRL> SWITCHOVER TO trackdg
Performing switchover NOW, please wait...
New primary database "trackdg" is opening...
Operation requires start up of instance "track" on database "track"
Starting instance "track"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node01.localdomain)(PORT=1521))(CONNECT_DA TA=(SERVICE_NAME=track_DGMGRL)(INSTANCE_NAME=track)(SERVER=DEDICATED)))
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 to finish switchover:
start up and mount instance "track" of database "track"
Lets’s check how is the standby database. We can see that Standby is converted to primary but the primary failed to start as standby
SQL> select name,open_mode,database_role,host_name from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE HOST_NAME
--------- -------------------- ---------------- ----------------------------------------------------------------
TRACK READ WRITE PRIMARY oel6-rac01.localdomain
So we need to start the primary manually as the new standby database.
SQL> startup mount
ORACLE instance started.
Total System Global Area 729808896 bytes
Fixed Size 2928680 bytes
Variable Size 587206616 bytes
Database Buffers 134217728 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> set lines 150
SQL> select name,open_mode,database_role,host_name from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE HOST_NAME
--------- -------------------- ---------------- ----------------------------------------------------------------
TRACK MOUNTED PHYSICAL STANDBY oel6-node01.localdomain
Once it is done our configuration will show successful.
DGMGRL> SHOW DATABASE track
Database - track
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 14.00 KByte/s
Real Time Query: OFF
Instance(s):
track
Database Status:
SUCCESS
DGMGRL> show configuration;
Configuration - track
Protection Mode: MaxPerformance
Members:
trackdg - Primary database
track - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 19 seconds ago)
Now look at why this happened and how to fix this for the future
If you look closely at the error message you can see that track_DGMGRL service is not available on track database on oel6-node01
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node01.localdomain)(PORT=1521))(CONNECT_DA TA=(SERVICE_NAME=track_DGMGRL)(INSTANCE_NAME=track)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
But why it is connecting to track_dgmgrl service. Answer lies in this property
DGMGRL> show database track staticConnectidentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=track_DGMGRL)(INSTANCE_NAME=track)(SERVER=DEDICATED)))'
DGMGRL> show database trackdg staticConnectidentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-rac01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=trackdg_DGMGRL)(INSTANCE_NAME=trackdg)(SERVER=DEDICATED)))'
So make sure that listener on oel6-node01 is aware of track_DGMGRL and listener on oel6-rac01 is aware of trackdg_DGMGRL service. To do this you need to add static entries of these services in listener.ora file and reboot the listener to take it effect.
You can also verify our claim by checking the listener status on oel6-node01. As you can see that listener is not aware of any service named track_dgmgrl
[oracle@oel6-node01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-MAY-2020 13:47:52
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel6-node01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 11-MAY-2020 13:45:06
Uptime 0 days 0 hr. 2 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel6-node01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node01.localdomain)(PORT=1521)))
Services Summary...
Service "track" has 1 instance(s).
Instance "track", status READY, has 1 handler(s) for this service...
Service "track_DGB" has 1 instance(s).
Instance "track", status READY, has 1 handler(s) for this service...
The command completed successfully
So we need to add this static entry for track_dgmgrl in listener.ora on oel6-node01
[oracle@oel6-node01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = track_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = track)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node01.localdomain)(PORT = 1521))
)
)
Reboot the listener. You can see that listener now knows this service track_DGMGRL
[oracle@oel6-node01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-MAY-2020 13:53:13
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel6-node01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 11-MAY-2020 13:51:54
Uptime 0 days 0 hr. 1 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel6-node01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node01.localdomain)(PORT=1521)))
Services Summary...
Service "track" has 1 instance(s).
Instance "track", status READY, has 1 handler(s) for this service...
Service "track_DGB" has 1 instance(s).
Instance "track", status READY, has 1 handler(s) for this service...
Service "track_DGMGRL" has 1 instance(s).
Instance "track", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Similarly add static entry of trackdg_dgmgrl in oel6-rac01 in listener.ora and reboot the listener.
[oracle@oel6-rac01 admin]$ cat listener.ora
# listener7391382908636194357.ora Network Configuration File: /tmp/listener7391382908636194357.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = trackdg_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = trackdg)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6-rac01.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
aftre reboot of listener trackdg_DGMGRL service is known to listener
[oracle@oel6-rac01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-MAY-2020 14:40:03
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel6-rac01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 11-MAY-2020 07:14:16
Uptime 0 days 7 hr. 25 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel6-rac01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-rac01.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "emrep" has 1 instance(s).
Instance "emrep", status READY, has 1 handler(s) for this service...
Service "emrepXDB" has 1 instance(s).
Instance "emrep", status READY, has 1 handler(s) for this service...
Service "track1_DGMGRL" has 1 instance(s).
Instance "trackstdby", status UNKNOWN, has 1 handler(s) for this service...
Service "trackdg" has 1 instance(s).
Instance "trackdg", status READY, has 1 handler(s) for this service...
Service "trackdg_DGB" has 1 instance(s).
Instance "trackdg", status READY, has 1 handler(s) for this service...
Service "trackdg_DGMGRL" has 1 instance(s).
Instance "trackdg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Now try switchover. it should be sucessful.
DGMGRL> show configuration
Configuration - track
Protection Mode: MaxPerformance
Members:
track - Primary database
trackdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 9 seconds ago)
DGMGRL> switchover to trackdg
Performing switchover NOW, please wait...
New primary database "trackdg" is opening...
Operation requires start up of instance "track" on database "track"
Starting instance "track"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "trackdg"
DGMGRL> show configuration
Configuration - track
Protection Mode: MaxPerformance
Members:
trackdg - Primary database
track - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 15 seconds ago)
DBATracker 