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-16751: failed to switchover to physical standby database and Switchover Ends with Two Physical Standby Databases

You might find yourself in a situation where your switchover fails and left your environment with two physical standby databases.
From the broker your might see the following error:
ORA-16816: incorrect database role
Solution:
!!!!!!!!!!!!!!!!! First of all you need to stop database instances on all servers.
1. Logon (as sysdba) to the instance that was your Primary database instance before the switchover.

2. Confirm the database role.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

3. Shutdown the instance.

SQL> shutdown immediate;

Oracle Instance Shutdown.

4. Mount the database.

SQL> startup mount;

Database opened.

5. Cancel the MRP process. You will receive "ORA-16136: Managed Standby Recovery not active" if it is not running, but you can ignore.

SQL> alter database recover managed standby database cancel;

6. Terminate the current switchover to Standby that never completed fully.

SQL> alter database recover managed standby database finish;

Database altered

7. Now switchover to Primary.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered

8. Open the database.

SQL> alter database open;

Database altered.

9. Confirm the database role.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

Additional Steps
When attempting to open the Primary Database you may suffer the following error:

SQL> alter database open

*

ERROR at line 1:

ORA-16649: possible failover to another database prevents this database being opened

In this case, before you can open the database, you must disable Data Guard Broker as follows:

 SQL> alter system set dg_broker_start=false scope=both;

 System altered.

SQL> alter database open;

 Database altered.

Now re-install Data Guard Broker.

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.

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database

The physical standby recovery process was terminated with the error below:

Standby Crash Recovery aborted due to error 1111.
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
Completed Standby Crash Recovery.
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'

How to resolve the errors so that the recovery process could proceed so that the physical standby could be in sync with the primary database?
Solution:
perform the following steps on the physical standby database to resolve the issue
On standby database run following command to find missing files

SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME 
from v$datafile_header where ERROR='FILE MISSING';

cancel the managed recovery

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Set standby_file_management=manual

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

If the physical standby database is RAC, then please make the change to all standby instances

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';

Rename the unknown datafile 8

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'absolute path/real datafile name' ;

For ASM

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'+DG01' size 'actual size of datafile on Primary';

Set standby_file_management=auto

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

Start managed recovery

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

WARNING! Recovering data file from a fuzzy .. file ORA-01194: file needs more recovery to be consistent

Oracle Release - 10.2.0.5.0,  platform - HP-UX (64-bit)

problem - Disk damaged on physical standby and dozens of data files were lost.  Instance was down and archive log gap presented between standby and primary databases.

Instead of restore from backup we just copied (fast way) online data files from primary and started applying missing logs.

At the start of apply process we saw warnings as follow in the standby alert log telling that we are supplying incorrect files.

WARNING! Recovering data file 291 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 529 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 530 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command. ...

Later, after all logs applied successfully we tried to open standby in read only mode:

SQL> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01194: file 291 needs more recovery to be consistent
ORA-01110: data file 291 ...

Although all logs applied and dbs are synchronized there was a problem with the data file in our warning list - 291.
Of course it indicates that files should have been transferred in backup mode.

Apparently, we found all relevant tablespaces and put them into backup mode:

ALTER TABLESPACE TS1 BEGIN BACKUP;
ALETR TABLESPACE TS2 BEGIN BACKUP;

As transfer finished we took them out from backup mode.

ALTER TABLESPACE TS1 END BACKUP;
ALETR TABLESPACE TS2 END BACKUP;

It is all, after applying all logs we could successfully open standby in read only mode.

Thanks,