Blog

Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

One single 11.2.0.4 instance Oracle database alert log shows following information;

 RFS[1]: No standby redo logfiles available for thread 1

Data Guard shows ORA-16857 error:

DGMGRL> show database "TESTSTY";

Database - TESTSTY

Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 10 minutes 32 seconds (computed 48 seconds ago)
 Apply Lag: 10 minutes 32 seconds (computed 48 seconds ago)
 Apply Rate: 39.97 MByte/s
 Real Time Query: OFF
 Instance(s):
 TESTSTY

Database Warning(s):
 ORA-16857: standby disconnected from redo source for longer than 
specified threshold

Database Status:
WARNING

Checked both primary and standby database, the  standby online redo logs have been created. It is strange to see some of the thread id is different between primary and secondary database.

— on standby :

SQL> select GROUP#,THREAD#,BYTES/1024/1024 ,ARCHIVED,STATUS
       from v$standby_log; 2

GROUP#     THREAD#    BYTES/1024/1024 ARC STATUS
---------- ---------- --------------- --- ----------
 4         1          50              NO UNASSIGNED
 5         0          50              YES UNASSIGNED
 6         0          50              YES UNASSIGNED
 7         0          50              YES UNASSIGNED

— On Primary

SQL> select GROUP#,THREAD#,BYTES/1024/1024 ,ARCHIVED,STATUS 
       from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024 ARC STATUS
---------- ---------- --------------- --- ----------
 4         0          50              YES UNASSIGNED
 5         0          50              YES UNASSIGNED
 6         0          50              YES UNASSIGNED
 7         0          50              YES UNASSIGNED

Drop all standby online redo logs on both primary and standby databases, and recreate then again by specify “thread 1” explicitly.

-- for standby db which is under recovery, recovery needs to be stopped first
SQL>alter database recover managed standby database cancel;

SQL>alter database add standby logfile thread 1 group 4 size 50m;

It will create standby online redo logs for one under +FRA, and another one under from “db_create_file_dest” parameter, if “db_create_online_log_dest_x” are not defined.

Finally restart the recovery process, everything is fine.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Database altered.

“ORA-16644: apply instance not available” While DGMGRL Switchover

For some reason, DG broker does not recognise the applying instance of a standby database.

DGMGRL> switchover to STDBYDB;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STDBYDB2" on database
 "STDBYDB"
Connecting to instance "STDBYDB2"...
Connected as SYSDBA.
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still "PRIMDB"
DGMGRL>

DIAGNOSIS

a) Check and find one of the instance STDBYDB1 is the recovery instance. Stop the standby recovery process, and then start it again.  Data Guard still cannot find it.

b) Stop whole standby database, and restart it again. Check standby applying is undergoing. Data Guard still cannot find it.

RESOLUTION

DGMGRL> edit database STDBYDB set property 'PreferredApplyInstance'
='STDBYDB1';
Property "PreferredApplyInstance" updated

$srvctl stop database -d STDBYDB;
$srvctl start database -d STDBYDB;

DGMGRL> edit database STDBYDB set property 'PreferredApplyInstance'='';
Property "PreferredApplyInstance" updated

“ORA-01017: invalid username/password; logon denied” While Data Guard Switchover

Data Guard switchover failed with “ORA-01017” error :

DGMGRL> connect /
Connected as SYSDG.

DGMGRL> switchover to STDBYDB;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STDBYDB2" on database "STDBYDB"
Connecting to instance "STDBYDB2"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

RESOLUTION

Logon as SYS, then switchover will be successful.

DGMGRL> connect SYS
Connected as SYSDG.
DGMGRL> switchover to STDBYDB;

Performing switchover NOW, please wait...

Operation requires a connection to instance "STDBYDB2" on database "STDBYDB"

Connecting to instance "STDBYDB2"...

Connected as SYSDBA.

New primary database "STDBYDB" is opening...

Oracle Clusterware is restarting database "PRIMDB" ...

Switchover succeeded, new primary is "STDBYDB"

“DGM-17016: failed to retrieve status for database” in Data Guard Broker

The following error occurred in DG broker command on primary database side:

DGMGRL> show database verbose 'STDBYDB'

Database - STDBYDB

 Role: PHYSICAL STANDBY
...
..
.
Database Status:
DGM-17016: failed to retrieve status for database "STDBYDB"
ORA-16664: unable to receive the result from a database

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-16849 ORA-16816 After Uncompleted Data Guard Switchover

For some reason, DG ( Data Guard ) switchover command is incomplete with following warnings:

Database Error(s):
 ORA-16849: a previous switchover from this database was interrupted 
            and aborted
 ORA-16816: incorrect database role

After detailed checks, it is confirmed that database role has been changed successfully.

-- on old primary database
--
SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

-- on old standby database
--
SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

So the issues is the final role change has not been recognized by the DG broker, so we have to rebuild the Data Guard Broker Configuration when the Databases have been restarted:

DGMGRL> remove configuration;
DGMGRL> create configuration 'PRIMDB' AS PRIMARY DATABASE IS 
       'PRIMDB' CONNECT IDENTIFIER IS 'PRIMDB'; 
DGMGRL> add database 'STDBYDB' AS CONNECT IDENTIFIER IS 'STDBYDB' 
        maintained as physical;
DGMGRL> enable configuration;
DGMGRL> show configuration;
DGMGRL> show database verbose 'PRIMDB';
DGMGRL> show database verbose 'STDBYDB';