Day: March 1, 2017

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated

To start managed recovery process (MRP) with real time apply ( RTA):
11c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
12c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;

This warning will be seen in alert log if we issue the same command in 12c as we have done in 11c:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Instead we can use the following command to eliminate the WARNING:

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.
Advertisement

No standby redo logfiles available for T-1

It looks like a bug.

One single instance 12c Oracle database alert log shows the following information:

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

While 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 ?