While I am converting Snapshot standby to Physical standby database in RAC environment, below issue may be expected if other instances are not down.
Issue Replicated:
DGMGRL> convert database testdr to physical standby;
Converting database "testdr" to a Physical Standby database, please wait...
Error:
ORA-03113: end-of-file on communication channel
Process ID: 44589
Session ID: 1355 Serial number: 43829
Failed to convert database "testdr"
DGMGRL>
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 4 hours 33 minutes 30 seconds (computed 1 second ago)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
Database Status:
WARNING
DGMGRL>
How to Fix?
Don't panic. If you check other database instances, all are already closed or down.
So Now do the follow the steps one by one:
-- shut the instance where it is open
SQL> shut immediate;
-- Start with mount ( operate from one node)
SQL> startup mount
-- Now convert to Physical Standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
test testdr MOUNTED PHYSICAL STANDBY
-- Enable Replication
SQL> alter database recover managed standby database disconnect from session using current logfile;
-- But Dataguad broker is not updated. If you will check below message will be expected.
DGMGRL> connect testdr
Password:
Connected as SYSDG.
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
Database Error(s):
ORA-16816: incorrect database role
Database Status:
ERROR
-- So, Now do the following
DGMGRL> CONVERT DATABASE testdr TO PHYSICAL STANDBY;
Converting database "testdr" to a Physical Standby database, please wait...
Database "testdr" converted successfully
DGMGRL>
-- Now verify in broker also, Issue is fixed
DGMGRL> show database testdr
Database - testdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 16.91 MByte/s
Real Time Query: OFF
Instance(s):
testdr1 (apply instance)
testdr2
Database Status:
SUCCESS
Now the issue is resolved.
Issue Replicated:
DGMGRL> convert database testdr to physical standby;
Converting database "testdr" to a Physical Standby database, please wait...
Error:
ORA-03113: end-of-file on communication channel
Process ID: 44589
Session ID: 1355 Serial number: 43829
Failed to convert database "testdr"
DGMGRL>
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 4 hours 33 minutes 30 seconds (computed 1 second ago)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
Database Status:
WARNING
DGMGRL>
How to Fix?
Don't panic. If you check other database instances, all are already closed or down.
So Now do the follow the steps one by one:
-- shut the instance where it is open
SQL> shut immediate;
-- Start with mount ( operate from one node)
SQL> startup mount
-- Now convert to Physical Standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
test testdr MOUNTED PHYSICAL STANDBY
-- Enable Replication
SQL> alter database recover managed standby database disconnect from session using current logfile;
-- But Dataguad broker is not updated. If you will check below message will be expected.
DGMGRL> connect testdr
Password:
Connected as SYSDG.
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
Database Error(s):
ORA-16816: incorrect database role
Database Status:
ERROR
-- So, Now do the following
DGMGRL> CONVERT DATABASE testdr TO PHYSICAL STANDBY;
Converting database "testdr" to a Physical Standby database, please wait...
Database "testdr" converted successfully
DGMGRL>
-- Now verify in broker also, Issue is fixed
DGMGRL> show database testdr
Database - testdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 16.91 MByte/s
Real Time Query: OFF
Instance(s):
testdr1 (apply instance)
testdr2
Database Status:
SUCCESS
Now the issue is resolved.