Flashback Database After Failover: Correcting "Error ORA-38754: FLASHBACK DATABASE not started"

If the alert log file of the former primary DB contains the following error messages:

. . .

FLASHBACK DATABASE TO SCN 6693334

ORA-38754 signalled during: FLASHBACK DATABASE TO SCN 6693334...

. . .

Try to perform a flashback manually and you might get the following error:

SQL> FLASHBACK DATABASE TO SCN 6693334;

FLASHBACK DATABASE TO SCN 6693334

*

ERROR at line 1:

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

ORA-38762: redo logs needed for SCN 6691520 to SCN 6693334

ORA-38761: redo log sequence 124 in thread 1, incarnation 13 could not be accessed

In this case . . .

. . . on the new standby DB (former primary DB - before the failover),

use rman by connecting to the local DB and using the control file (NOCATALOG):

rman target=/ NOCATALOG

or (if baseenv from Triivadis is available - with history)

rmanh target=/ NOCATALOG

Since this command using the SCN did not return any archivelogs . . .

RMAN> LIST ARCHIVELOG SCN BETWEEN 6691520 AND 6693334;

. . . I searched for the backup of the archivelog with sequence 124 by guessing the time range . . .

LIST BACKUP OF ARCHIVELOG

      FROM TIME "TO_DATE('2018.05.01 20:00:00','YYYY.MM.DD HH24:MI:SS')"

      UNTIL TIME "TO_DATE('2018.05.02 12:00:00','YYYY.MM.DD HH24:MI:SS')";

. . . and was lucky and got the SCN of the desired archivelog (SCN 6691790) (but for restoring I choose the Low SCN of the previous archivelog i.e. 6689656 )

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

343     60.00M     SBT_TAPE    00:00:52     2018-05-02 10:57:31

        BP Key: 343   Status: AVAILABLE  Compressed: NO  Tag: TAG20180502T105639

        Handle: 6446830_C0200Z01_c9t1sib7_1_1   Media: V_3683633_19130778

  List of Archived Logs in backup set 343

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

. . .

  1    122     6687465    2018-05-02 10:27:49 6689656    2018-05-02 10:42:47

  1    123     6689656    2018-05-02 10:42:47 6691790    2018-05-02 10:56:38

  1    124     6691790    2018-05-02 10:56:38 6691798    2018-05-02 10:56:38

HINT

It can happen, that the needed archivelog, belongs to another incarnation of the DB.

(If this is the case - on Oracle 12c - you can use the incarnation number with the rman restore statement.)

This statement list your incarnations.

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       DB200Z01 2998214488       PARENT  1          2018-04-20 08:00:24

2       2       DB200Z01 2998214488       PARENT  2449245    2018-04-23 07:56:18

. . .

11      11      DB200Z01 2998214488       PARENT  5928815    2018-05-01 09:13:12

12      12      DB200Z01 2998214488       PARENT  6070931    2018-05-01 13:49:26

13      13      DB200Z01 2998214488       CURRENT 6179737    2018-05-01 14:47:30

==> Incarnation 13 is current (I do not need to use it with the restore statement)

Performing the restore of the archivelog (adapt the PARMS= for your needs):

RUN {

  ALLOCATE CHANNEL ch1 TYPE 'sbt_tape' PARMS="SBT_LIBRARY=/opt/hds/Base/libobk.so";

  # ALLOCATE CHANNEL ch1 TYPE disk;

  #

  RESTORE ARCHIVELOG FROM SCN 6689656;  # INCARNATION 13;

}

Output

allocated channel: ch1

channel ch1: SID=778 device type=SBT_TAPE

channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)

Starting restore at 2018-05-02 13:15:52

archived log for thread 1 with sequence 125 is already on disk as file +U02/DB200Z01_HSB1/ARCHIVELOG/2018_05_02/thread_1_seq_125.759.975063515

. . .

archived log for thread 1 with sequence 133 is already on disk as file +U02/DB200Z01_HSB1/ARCHIVELOG/2018_05_02/thread_1_seq_133.741.975064009

channel ch1: starting archived log restore to default destination

channel ch1: restoring archived log

archived log thread=1 sequence=123

channel ch1: restoring archived log

archived log thread=1 sequence=124

channel ch1: reading from backup piece 6446830_DB200Z01_c9t1sib7_1_1

Now the standby DB should by able to automatically perform a flashback.

I hope it helps.

[ ]

Correcting Oracle DB Errors: ORA-01153, ORA-16136, ORA-16766, ORA-16826, ORA-38706, ORA-38788

After having re-build the standby DB, I've got the following error trying to enable the flashback:

SQL> 

alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38788: More standby database recovery is needed

In order to solve the problem, I tried to start the recovery . . .

SQL> 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

ORA-16136: Managed Standby Recovery not active

. . . the error from above, reminded my that I forgot to start the DataGuard Broker on the primary DB. So I did it:

SQL> alter system set dg_broker_start=true;

This was not enough.

On the Data Guard Manager console, I've remarked the following error for the standby DB

. . .

DGMGRL> show configuration verbose;

. . .

        Error: ORA-16766: Redo Apply is stopped

Since I remembered, that I once solved this error by making sure that the physical standby DB really starts on MOUNT mode, I've checked it an restarted the standby DB:

OS>

srvctl modify database -d <MyPhysicalStandbyDbUniqueName> -r PHYSICAL_STANDBY -s mount

srvctl stop database -d <MyPhysicalStandbyDbUniqueName>

srvctl start database -d <MyPhysicalStandbyDbUniqueName>

And checked its configuration

OS>

srvctl config database -d <MyPhysicalStandbyDbUniqueName>

But when re-trying to start the recovery I've got another error:

SQL>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

This was caused by the running recovery process (MRP0) . . .

SQL> 

select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#

--------- -------- ---------- ---------- ----------

RFS LGWR 1 1551 125

MRP0 N/A 1 1551 125

. . . that I've stopped:

SQL>

alter database recover managed standby database cancel;

Database altered.

Check:

SQL>

select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#

--------- -------- ---------- ---------- ----------

RFS LGWR 1 1551 125

Not I've re-tryied to enable the flash back again => with success

SQL>

alter database flashback on;

Database altered.

But the Data Guard Manager console shows that the redo apply is still stopped

DGMGRL>

show configuration verbose;

. . .

      Error: ORA-16766: Redo Apply is stopped

So, I re-tried to start it again:

SQL>

alter database recover managed standby database disconnect from session;

Database altered.

But the DataGuard Manager console shows that 

DGMGRL>

show configuration verbose;

. . .

      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Ops, I used the wrong option when re-trying to start the recovery (I did not start it on the "MANAGED REAL TIME APPLY" mode).

Verify this with this query on the PRIMARY SITE:

SQL>

SET LINES 200

column DEST_NAME format A20;

column RECOVERY_MODE format A30;

select DEST_ID, DEST_NAME, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

Output example:

   DEST_ID DEST_NAME            RECOVERY_MODE

---------- -------------------- --------------------

         1 LOG_ARCHIVE_DEST_1   IDLE

         2 LOG_ARCHIVE_DEST_2   MANAGED

         3 LOG_ARCHIVE_DEST_3   IDLE

. . .

It is only MANAGER, not 

After stopping the recovery again and restarting it "with REAL TIME APPLY"

SQL>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

Database altered.

The recovery is now on "REAL TIME APPLY" mode:

SQL>

SET LINES 200

column DEST_NAME format A20;

column RECOVERY_MODE format A30;

select DEST_ID, DEST_NAME, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

   DEST_ID DEST_NAME            RECOVERY_MODE

---------- -------------------- ------------------------------

         1 LOG_ARCHIVE_DEST_1   IDLE

         2 LOG_ARCHIVE_DEST_2   MANAGED REAL TIME APPLY

         3 LOG_ARCHIVE_DEST_3   IDLE

         4 LOG_ARCHIVE_DEST_4   IDLE

. . .

And the DataGuard Manager console shows that all is OK:

DGMGRL> show configuration verbose;

Configuration Status:

SUCCESS