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.
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.
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.
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.
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.
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
External Links
Oracle Data Guard role transitions
https://docs.oracle.com/cd/E11882_01/server.112/e41134/concepts.htm#i1033808
Oracle Data Guard Broker Concepts
https://docs.oracle.com/cd/E11882_01/server.112/e40771/concepts.htm#DGBKR001
Oracle Data Guard Broker Failover and Switchover
https://docs.oracle.com/cd/E11882_01/server.112/e40771/sofo.htm#DGBKR330