How to convert physical standby database to snapshot standby database

Snapshot Standby database feature in dataguard environment gives us opportunity to open standby database in read write mode and then return to back as physical standby in read only.

We can use dgmgrl or sqlplus to convert physical standby database to snapsho standby database, here I'll write both method.

To open physical standby database in read write mode as snapshot standby database, flashback must be on.

Using SQLPLUS to convert phyical standby database to snapshot standby database:

SQL> alter system set db_recovery_file_dest='+DG';
System altered.

SQL> alter system set db_recovery_file_dest_size=500G;
System altered.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     MOUNTED              NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> startup

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     READ WRITE           NONE    SNAPSHOT STANDBY

Convert snapshot standby database back to physical standby database:

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     READ WRITE           NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> Startup nomount

SQL> alter database mount;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

SQL> shut immediate

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     MOUNTED              NONE    PHYSICAL STANDBY

Using DGMGRL to convert snapshot standby database to physical standby and return back:

-- convert physical standby to snapshot standby
DGMGRL> CONVERT DATABASE TESTDB TO SNAPSHOT STANDBY;

-- convert snapshot satndby to physical standby
DGMGRL> CONVERT DATABASE TESTDB to PHYSICAL STANDBY;

ORA-16854: apply lag could not be determined in DGMGRL

On one of our Data Guard Architecure appeared below error in DGMGRL, when checked the configuration status but the primary and standby databases are fine with no delay and in syncronize.

ORA-16854: apply lag could not be determined

DGMGRL> show database verbose testdbst;

Database - testdbst

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    ON
  Instance(s):
    testdbst

  Properties:
    DGConnectIdentifier             = 'testdbstby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '900'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = '+DATA1/testdb, +DATA2/testdbst, +RECO1/testdb, +REC2/testdbst'
    LogFileNameConvert              = '+DATA1/testdb, +DATA2/testdbst, +RECO1/testdb, +RECO2/testdbst'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdbst-dg-vip)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=testdbst)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/testdbst/testdbst/trace/alert_testdbst.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/testdbst/testdbst/trace/drctestdbst.log

Database Status:
SUCCESS

Solution: 

1) Connect to the standby database and check if MRP process is still running If MRP process is still running, cancell it with command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

and start MRP process with command:

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

2) if above Solution will not work try to just restart standby database. 

3) If there is still prolem with warning "about apply lag could not be determined" you can disable lag computation with next command in DGMGRL

DGMGRL> edit database 'testdbst' set property 'ApplyLagThreshold'=0;

This definitely will solve your problem.

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

In Oracle Database 12c command

SQL> alter database recover managed standby database disconnect from session using current logfile;

is deprecated and if you use it in alert log file you'll see warnings about it:

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
ALTER DATABASE RECOVER  managed standby database disconnect from session using current logfile  

Instead of this command we can use next command in oracle database 12c

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;

To start apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

This also automatically enables real-time apply provided the standby database is configured with a standby redo log and is in ARCHIVELOG mode.
Redo Apply can be run either as a foreground session or as a background process. To start Redo Apply in the foreground, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

or

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This command does not disconnect the current SQL session.