No standby redo logfiles available for T-1

One single instance 12c Oracle database alert log shows below information;

 RFS[1]: No standby redo logfiles available for T-1

DataGuard shows ORA-16857 error:

DGMGRL> show database verbose "TESTSTY";

Database - TESTSTY

 Role: PHYSICAL STANDBY
 Intended State: APPLY-OFF
 Transport Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Apply Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Average Apply Rate: (unknown)
 Active Apply Rate: (unknown)
 Maximum Apply Rate: (unknown)
 Real Time Query: OFF
 Instance(s):
 TESTSTY

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

DGMGRL> validate database "TESTSTY"
...
..
.
Warning: standby redo logs not configured for thread 1 on TESTSTY

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

— on standby :

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

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

— On Primary

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

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

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 1024m;

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 disconnect nodelay;

Database altered.

Only see this thread issue for single instance database in 12c, which is a new feature or bug ?

Advertisements
This entry was posted in 12c new features, CDB/PDB, DataGuard, ORA- ERRORS and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s