Nov 13, 2018

Warning: ORA-16782: instance not open for read and write access

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.

Translate >>