Skip to main content
Delphix

Oracle Data Guard, Delphix dSources and Managing dSource Database Role Changes During Oracle Data Guard Failover (KBA1049)

 

 

 

Applicable Delphix Versions

Major Release

All Sub Releases

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

This document provides an example of how to approach Oracle dSource management within Delphix where Oracle Data Guard is configured and a Data Guard Primary Site failure has resulted in a Data Guard failover being initiated to enable the Standby Site and convert the database there to a Primary database role.  It is not meant to be a step by step guide for performing failover in production environments given this will be highly dependent on the local environment itself.  The databases used in the environment here are small test databases and therefor do not have the same complexities as those of production.  While the steps from a pure Oracle and Delphix perspective are likely to be the same its does not consider end user applications, application servers or other entities dependent on the Primary database environment.

It assumes that an Oracle Data Guard physical standby environment has been established and that this is operating normally, redo shipping is occurring successfully and managed recovery is applying redo at the standby site.  The failover to the Standby site has been forced due to a complete loss of the Primary Site.

Oracle Data Guard provides the ability to perform the failover operations on behalf of the DBA automatically via Fast Start Failover and a Fast Start Failover Observer or be initiated through Oracle Enterprise Manager or the dgmgrl command line by the DBA.  Failover is normally only performed in scenarios involving the complete loss of the Primary site and the Standby site is converted to a Primary role database. Typically after a failover operation the Primary site needs to be rebuilt and established as a Standby once it has been recovered from whatever failure took the site down.

Background Information
The Environment
  • Two target host environments have been discovered and appeared in the Delphix engine, oelvbc1n1 and oelvbc1n2.
  • One host oelvbc1n1, is running the Primary Site identified through db_unique_name of db112, this site is linked to Delphix as the dSource.
  • One host oelvbc1n2, is running the Standby Site identified through db_unique_name of db112_stb, this database while it has been discovered by the engine has not been linked as a dSource in anyway.
The Scenario
  • A failover is performed using the Data Guard Broker command line.
  • This results in the existing Primary (db112) needing to be rebuilt as this site has been lost.
  • The existing Standby site (db112_stb) becomes the Primary site as a result of the failover operation.
  • The dSource in Delphix is to follow the Primary database from one target host to the other as a result of the failover operation.
  • Moving the location of the dSource from one site to another means detaching the dSource from the Engine and attaching it from the new target host following the switchover operation within Oracle.

Note this document does not cover a switchover operation.

The current Data Guard Broker configuration.

The broker configuration is in place and looks healthy prior to the failure resulting in the loss of the Primary site and the ensuing Data Guard failover being initiated.

[oracle@oelvbc1n1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / 
Connected.
DGMGRL> show configuration

Configuration - db112_dgb


  Protection Mode: MaxPerformance
  Databases:
    db112     - Primary database
    db112_stb - Physical standby database


Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
The Primary site is lost. 

In this case the primary site instance is aborted to simulate loss of the primary site. In one session a new table is created and some data inserted to simulate a work load in the middle of the failure to make the scenario a little more realistic.

SQL> create table plb as select * from emp;

Table created.

SQL> insert into plb select * from plb;

57344 rows created.

In a second session abort the instance. 

[oracle@oelvbc1n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 9 16:03:44 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
The Data Guard Broker details following the failure in the Primary site.

The configuration shows the following state with the Primary site database db112 is down.

[oracle@oelvbc1n2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@db112_stb
Password:
Connected.
DGMGRL> show configuration;

Configuration - db112_dgb

  Protection Mode: MaxPerformance
  Databases:
    db112     - Primary database
    db112_stb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "db112"
DGM-17017: unable to determine configuration status
 The Delphix Engine has also detected the fault in the Primary site.

A logsync error appears indicating that the Oracle database operating as the dSource db112 is not available for fetching archivelogs.

DGFailover_dSourceFault.jpg

A failover is performed via Oracles Data Guard Broker given the Primary site is now gone. 

This operation is performed from the standby site and is used to bring the standby site up in the Primary Database role rather than its current role as a standby database.

The current state of the Standby site the prior to the failover operation that will convert it to a Primary is the following:

oracle@oelvbc1n2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 9 16:17:53 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
db112_stb                      PHYSICAL STANDBY MOUNTED

The Data Guard Broker configuration prior to the failover shows connectivity issues with the primary site and the standby site is still operating as a standby.

[oracle@oelvbc1n2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@db112_stb
Password:
Connected.
DGMGRL> show configuration;

Configuration - db112_dgb
  Protection Mode: MaxPerformance
  Databases:
    db112     - Primary database
    db112_stb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "db112"
DGM-17017: unable to determine configuration status
The failover to the Standby site occurs.

The failover is initiated and the Standby site and database db112_stb becomes the Primary database in the Data Guard configuration.

DGMGRL> failover to db112_stb;
Performing failover NOW, please wait...
Failover succeeded, new primary is "db112_stb"

DGMGRL> show configuration;

Configuration - db112_dgb

  Protection Mode: MaxPerformance
  Databases:
    db112_stb - Primary database
    db112     - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

Examining the Standby site shows the database role has been altered from a Physical Standby role to a Primary role

[oracle@oelvbc1n2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 9 17:12:59 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
db112_stb                      PRIMARY          READ WRITE

SQL> 

Aligning the Delphix Engine with the new dSource environment

In an Oracle Data Guard failover scenario it is assumed that the Primary site has been completely lost due to some form of disaster in the Primary site.  

The scenario being covered has the Delphix Engine and dSource for this database linked to the lost site.

As the failover at an Oracle level has occurred the Delphix Engine needs to be aligned with the new site and environment.

From a Delphix perspective the dSource needs to be detached from the old Primary site and attached to the new Primary Site.  This is performed through the Delphix CLI. The following CLI commands and actions detail this process.

The current dSource environment must first be detached from the Delphix dSource configuration.

$ ssh delphix_admin@DOMAIN
delphix> database
delphix database> ls
Objects
NAME   PROVISIONCONTAINER  DESCRIPTION
db112  -                   

Children
template

Operations
createEmpty
createRestorationDataset
export
fileMapping
link
oracleSupportedCharacterSets
provision
validateXpp
xpp
delphix database> select db112
delphix database 'db112'> detachSource
delphix database 'db112' detachSource *> ls
Properties
    type: DetachSourceParameters
    source: (required)
delphix database 'db112' detachSource *> set source=db112
delphix database 'db112' detachSource *> ls
Properties
    type: DetachSourceParameters
    source: db112 (*)
delphix database 'db112' detachSource *> commit;
    Dispatched job JOB-1343
    DB_DETACH_SOURCE job started for "Sauces/db112".
    DB_DETACH_SOURCE job for "Sauces/db112" completed successfully.
delphix database 'db112'> 

Once the detach has been performed successfully the new environment and database can be attached back to the dSource configuration in the Delphix Engine.

delphix database 'db112'> attachSource
delphix database 'db112' attachSource *> ls
Properties
    type: AttachSourceParameters
    attachData:
        type: ASEAttachData
        config: (required)
        dbCredentials:
            type: PasswordCredential
            password: (required)
        dbUser: (required)
        dumpCredentials: (unset)
        externalFilePath: (unset)
        loadBackupPath: (required)
        loadLocation: (unset)
        operations: (unset)
        sourceHostUser: (required)
        stagingHostUser: (required)
        stagingPostScript: (unset)
        stagingPreScript: (unset)
        stagingRepository: (required)
        validatedSyncMode: ENABLED
delphix database 'db112' attachSource *> set attachData.type=OracleAttachData
delphix database 'db112' attachSource *> set attachData.config=db112_stb
delphix database 'db112' attachSource *> set attachData.dbUser=delphix
delphix database 'db112' attachSource *> set attachData.dbCredentials.password=delphix
delphix database 'db112' attachSource *> set attachData.environmentUser=
OELC10/delphix     OELC8/delphix      OELVBC1N1/delphix  OELVBC1N2/delphix  

delphix database 'db112' attachSource *> set attachData.environmentUser=OELVBC1N2/delphix
delphix database 'db112' attachSource *> ls
Properties
    type: AttachSourceParameters
    attachData:
        type: OracleAttachData (*)
        allowArchivelogNotFound: (unset)
        allowMissingResetlogs: (unset)
        allowRecreatedControlfile: (unset)
        backupLevelEnabled: (unset)
        bandwidthLimit: (unset)
        checkLogical: (unset)
        compressedLinkingEnabled: (unset)
        config: db112_stb (*)
        dbCredentials:
            type: PasswordCredential
            password: ******** (*)
        dbUser: delphix (*)
        doubleSync: (unset)
        encryptedLinkingEnabled: (unset)
        environmentUser: OELVBC1N2/delphix (*)
        externalFilePath: (unset)
        filesPerSet: (unset)
        linkNow: (unset)
        numberOfConnections: (unset)
        operations: (unset)
        rmanChannels: (unset)

delphix database 'db112' attachSource *> commit;
    ORACLE_DB_CONTAINER-53
    Dispatched job JOB-1344
    DB_ATTACH_SOURCE job started for "Sauces/db112".
    Obtaining information from source database "Sauces/db112".
    Creating new TimeFlow for dSource "Sauces/db112".
    The dSource "db112_stb" was successfully linked from source database "Sauces/db112".
    DB_ATTACH_SOURCE job for "Sauces/db112" completed successfully.
delphix database 'db112'> 
The Delphix Engine UI shows the dSource configuration has now altered.

Examining UI the new dSource has a new Environment OELVBC1N2 and is attached to a database with a new db_unique_name value. It has been aligned with what was the standby site and is now the new primary after the failover.

DGFailover_dSourceNewEnvironment.jpg

Failover ramifications for the dSource.

A failover operation in Oracle results in a reset logs operation occurring at the database level and a new database incarnation being created within Oracle.

A direct result of the open resetlogs occurring is Delphix detects this and throws a new fault.

DGFailover_dSourceResetLogsFault.jpg

In order for Delphix to continue using the dSource a new snapshot must be taken of the new site and its database.  This will result in a new timeflow and an RMAN backup will capture a new copy of the dSource database.

Taking a new snapshot to completes the attach of the new dSource.

DGFailover_dSourceNewSnapshot.jpg

A new timeflow is created and the initial timecard for that same timeflow is seen appearing in the Delphix UI once the snapshot has completed.

DGFailover_dSourceNewTimecard.jpg

https://cd.delphix.com/docs/latest/linking-oracle-physical-standby-databasesAdditional Information

Currently Delphix does not display the previous timeflow for the dSource in the Delphix UI.  Should provisioning of VDB's be required from a point in time prior to the failover and detach and attach operation then this must be performed via the CLI as this is only way the prior timeflow and its snapshots can ben accessed.

The Delphix Command Line Interface

https://docs.delphix.com/docs/reference/command-line-interface-guide

Delphix Documentation: Linking Standby Databases

https://docs.delphix.com/docs/delphix-administration/oracle-environments-and-data-sources/managing-oracle-oracle-rac-and-oracle-pdb-data-sources/linking-oracle-physical-standby-databases