Opening a standby READ_ONLY and continuing to apply logs

After reading Guenadi’s post I decided to try it myself. Creating Oracle 11g active standby database from physical standby database.

10:34:21 PROD-DB1SYS>select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION
--------------- ---------------- ---------------------------------------------------------------- -----------------
              1 PROD-DB             dev-box                                                     11.1.0.7.0
1 row selected.
Elapsed: 00:00:00.01
10:37:23 PROD-DB1SYS>select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
1 row selected.
Elapsed: 00:00:00.03
10:37:48 PROD-DB1SYS>alter database recover managed standby database cancel;
Database altered.
Elapsed: 00:00:02.06
10:38:52 PROD-DB1SYS>alter database open read only;
Database altered.
Elapsed: 00:00:23.96
10:39:45 PROD-DB1SYS>alter database recover managed standby database using current logfile disconnect;
Database altered.
Elapsed: 00:00:09.08
10:40:40 PROD-DB1SYS>select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
1 row selected.
Elapsed: 00:00:00.02
……………………………………………………………………………….
SQL> select HOST_NAME from v$instance;

HOST_NAME
----------------------------------------------------------------

prod-box

SQL> create user sharville identified by secret account unlock;
User created.
SQL> grant unlimited tablespace to sharville;
Grant succeeded.
SQL> create table sharville.sh (col_id number(10));
Table created.
……………………………………………………………………………..
SQL> select HOST_NAME from v$instance;

HOST_NAME
----------------------------------------------------------------
  dev-box  
11.1.0.7.0        04-APR-11 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
SQL> select object_name from all_objects where owner = 'SHARVILLE';
OBJECT_NAME
------------------------------
SH

WAIT_FOR_GAP status on standby

This morning I noticed one of the managed standby databases was in a pending shutdown state. I restarted it and verified the logs were getting applied from production. However, I noticed the WAIT_FOR_GAP status on one of the old logs. Some of the logs had not been applied and were no longer on the production system. So I found out which logs were in the gap and used RMAN to recover them in production. Oracle automatically shipped them to the standby and applied them.

Discover a gap:
 1* SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY 
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH         136626 CLOSING
ARCH      ARCH              0 CONNECTED
ARCH      ARCH         136627 CLOSING
ARCH      ARCH         136628 CLOSING
RFS       LGWR         136629 IDLE
RFS       UNKNOWN           0 IDLE
RFS       UNKNOWN           0 IDLE
MRP0      N/A          130107 WAIT_FOR_GAP
RFS       UNKNOWN           0 IDLE

Find out the extent of the gap:

  1  SELECT high.thread#, "LowGap#", "HighGap#"
  2  FROM
  3       (
  4       SELECT thread#, MIN(sequence#)-1 "HighGap#"
  5       FROM
  6       (
  7           SELECT a.thread#, a.sequence#
  8           FROM
  9           (
 10               SELECT *
 11               FROM v$archived_log
 12           ) a,
 13           (
 14               SELECT thread#, MAX(next_change#)gap1
 15               FROM v$log_history
 16               GROUP BY thread#
 17           ) b
 18           WHERE a.thread# = b.thread#
 19           AND a.next_change# > gap1
 20       )
 21       GROUP BY thread#
 22   ) high,
 23   (
 24       SELECT thread#, MIN(sequence#) "LowGap#"
 25       FROM
 26       (
 27           SELECT thread#, sequence#
 28           FROM v$log_history, v$datafile
 29           WHERE checkpoint_change# <= next_change#
 30           AND checkpoint_change# >= first_change#
 31       )
 32       GROUP BY thread#
 33   ) low
 34*  WHERE low.thread# = high.thread#
11:13:08 BIPROD1SYS>/
   THREAD#    LowGap#   HighGap#
---------- ---------- ----------
         1     130106     130153

Recover the logs in Production:

RMAN>  restore archivelog from sequence 130106 until sequence 130153;

I had to repeat this sequence of commands several times because there were multiple gaps.

ORA-38794: Flashback target time not in current incarnation

I got this error this morning when I attempted to flashback a standby database to an earlier time.

 flashback database to timestamp
   to_timestamp(’05/01/10 04:00:00′, ‘MM/DD/YY HH24:MI:SS’)
SQL> /
flashback database to timestamp
*
ERROR at line 1:
ORA-38794: Flashback target time not in current incarnation

This is Bug 6843554 – ORA-38794 during Flashback Database on Standby previously activated with Restore Point [ID 6843554.8]. It’s fixed in 11.2 but we are on 11.1.0.7. It’s caused by “orphaned incarnations” which you can see using this query:
select * from v$database_incarnation;

There is an easy work-around, flash back to the scn.
 flashback database to scn 44062315534;

Also, flashback database does a recovery so it needs the archived redo log from before the scn. We had to restore that from tape because it had already been backed up and deleted by RMAN.