Hi all,
A few weeks ago, I faced a problem switching from a primary to a standby database. It was not the first time it occurred to me, so I decided to write about it.
THE PROBLEM
When executing the switchover, I faced an "ORA-03113: end-of-file on communication channel".
DGMGRL> switchover to stddb
Performing switchover NOW, please wait...
Error:
ORA-03113: end-of-file on communication channel
Process ID: 52627
Session ID: 1190 Serial number: 19862
Unable to switchover, primary database is still "pridb"
Despite the DGMRL saying that the primary database was not switched, it was not the reality. I logged into the databases and checked their roles; as we can see, their roles were reversed.
([email protected],sid=10390)>select open_mode, database_role from gv$database;
OPEN_MODE DATABASE_ROLE
------------------------- ----------------------
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY
([email protected],sid=11881)>select open_mode, database_role from gv$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
READ WRITE PRIMARY
READ WRITE PRIMARY
The DGMRL also did not update the databases' CRS information:
--output clipped
[[email protected] admin]$ srvctl confidatabase -db pridb
Database unique name: pridb
Database name: pridb
Start options: open
Stop options: immediate
Database role: PRIMARY
--output clipped
[email protected]:/home/oracle $> srvctl config database -db stddb
Database unique name: stddb
Database name: pridb
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
FIXING THE PROBLEM
Firstly I checked the current DGMGRL configuration and did take notes.
DGMGRL> show configuration
Configuration - dg_conf
Protection Mode: MaxPerformance
Members:
pridb - Primary database
stddb - Physical standby database
Secondly, I changed the databases' CRS configuration:
srvctl stop database -db pridb -stopoption immediate
srvctl stop database -db stddb -stopoption immediate
srvctl modify database -db pridb -startoption "read only"
srvctl modify database -db pridb -role physical_standby
srvctl config database -db pridb
srvctl modify database -db stddb -startoption open
srvctl modify database -db stddb -role primary
srvctl config database -db stddb
srvctl start database -db pridb
srvctl start database -db stddb
Lastly, I recreated the DGMGRL configuration:
dgmgrl sys/password
DGMGRL>remove configuration
DGMGRL>create configuration dg_conf as primary database is stddb connect identifier is stddb;
DGMGRL>add database pridb as connect identifier is pridb maintained as physical;
DGMGRL>enable configuration
DGMGRL>show configuration
Configuration - dg_conf
Protection Mode: MaxPerformance
Members:
stddb - Primary database
pridb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 40 seconds ago)
DGMGRL> show database stddb
Database - stddb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
stddb1
stddb2
stddb3
Database Status:
SUCCESS
DGMGRL> show database pridb
Database - pridb
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: 10.06 MByte/s
Real Time Query: OFF
Instance(s):
pridb1 (apply instance)
pridb2
pridb3
Database Status:
SUCCESS
Those switchover errors occur to me more frequently than I would like, often due to processes preventing the database shutdown. So, before executing a switchover, I usually stop, then start the primary and standby databases, and this small procedure avoids so much pain during the night shifts. :-)
That's all, folks, and I hope I have helped.
Rodrigo Lima.