Skip to main content
Delphix

Oracle Data Guard Switchover and Delphix dSources (KBA1103)

  1. Last updated
  2. Save as PDF
 

 

This guide provides an overview of how to approach Oracle dSource management within Delphix where Oracle Data Guard is configured and switchover operations are performed.

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.

Oracle Data Guard provides the ability to perform controlled switchover and failover operations. Switchover allows a role reversal of the Primary and Standby sites. Switchover must be distinguished from Failover as a failover on the other hand is typically performed in scenarios involving the loss of the Primary site and the Standby site is converted to the Primary. 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 linked to the Delphix Engine, oelvbc1n1 and oelvbc1n2
  • One host is running the Primary Site identified through db_unique_name of db112
  • One host is running the Standby Site identified through db_unique_name of db112_stb, this site through the physical standby database are linked to Delphix as the dSource.
The Scenario
  • A controlled switchover is performed between the sites at an Oracle level using the Data Guard Broker command line
  • This results in the existing Primary (db112) becoming a physical Standby database
  • The existing standby site (db112_stb) becomes the Primary site as a result of the role change
  • The dSource in Delphix is to follow the the standby database from one target host to the other as a result of the switchover operation
  • Moving the location of the dSource from one site to another mean 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 failover operation.

The current state of the environment

The Standby Site DB112_STB:

The standby site is a Physical Standby that is currently mounted but not open and managed recovery is currently active.

SQL> set lines 180
SQL> set pages 500
SQL> col current_scn format 9999999999999
SQL> col switchover# format 9999999999999
SQL> select dbid,name,db_unique_name,database_role,open_mode,current_scn, switchover# from v$database;

   DBID NAME   DB_UNIQUE_NAME         DATABASE_ROLE  OPEN_MODE        CURRENT_SCN  SWITCHOVER#
---------- --------- ------------------------------ ---------------- -------------------- -------------- --------------
 902561741 DB112   db112_stb           PHYSICAL STANDBY MOUNTED          11763036   902563530

SQL> select process,pid,status,status,thread#,sequence#,block# from v$managed_standby;

PROCESS     PID STATUS    STATUS     THREAD# SEQUENCE#   BLOCK#
--------- ---------- ------------ ------------ ---------- ---------- ----------
ARCH      5256 CLOSING   CLOSING        1    3009   77824
ARCH      5258 CLOSING   CLOSING        1    3011     1
ARCH      5260 CONNECTED  CONNECTED       0     0     0
ARCH      5262 CLOSING   CLOSING        1    3010    4096
RFS       5280 IDLE     IDLE         0     0     0
RFS       5312 IDLE     IDLE         0     0     0
RFS       5286 IDLE     IDLE         1    3012    328
RFS       5288 IDLE     IDLE         0     0     0
MRP0      5290 APPLYING_LOG APPLYING_LOG     1    3012    327

9 rows selected.

The Primary Site:

The Primary database is open read write and is sending archive logs across to the standby site via log shipping through LOG_ARCHIVE_DEST_2.

SQL> set lines 180
SQL> set pages 500
SQL> col current_scn format 9999999999999
SQL> col switchover# format 9999999999999
SQL> col db_unique_name format a10
SQL> select dbid,name,db_unique_name,database_role,open_mode,current_scn, switchover# from v$database;

   DBID NAME   DB_UNIQUE_NAME         DATABASE_ROLE  OPEN_MODE        CURRENT_SCN  SWITCHOVER#
---------- --------- ------------------------------ ---------------- -------------------- -------------- --------------
 902561741 DB112   db112             PRIMARY     READ WRITE         11764093   902563530

SQL> set lines 180
SQL> set pages 500
SQL> col dest_name format a20
SQL> select dest_id,dest_name,type,database_mode,recovery_mode,archived_thread# as arcthead#, archived_seq#, applied_thread# as appthrd#,applied_seq#,synchronized,gap_status 
   from v$archive_dest_status where database_mode <> 'UNKNOWN';
  
  DEST_ID DEST_NAME      TYPE      DATABASE_MODE  RECOVERY_MODE      ARCTHEAD# ARCHIVED_SEQ#  APPTHRD# APPLIED_SEQ# SYN GAP_STATUS
---------- -------------------- -------------- --------------- ----------------------- ---------- ------------- ---------- ------------ --- ------------------------
     1 LOG_ARCHIVE_DEST_1  LOCAL     OPEN      IDLE               1     3017     0      0 NO
     2 LOG_ARCHIVE_DEST_2  PHYSICAL    MOUNTED-STANDBY MANAGED REAL TIME APPLY     1     3017     1     3016 NO NO GAP

The Data Guard Broker configuration:

The broker configuration is in place and looks healthy.

$ dgmgrl
DGMGRL> connect /
DGMGRL> show configuration;

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database db112

Database - db112
 Role:      PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
  db112

Database Status:
SUCCESS

DGMGRL> show database db112_stb

Database - db112_stb
 Role:      PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag:  0 seconds (computed 1 second ago)
 Apply Lag:    0 seconds (computed 1 second ago)
 Apply Rate:   2.16 MByte/s
 Real Time Query: OFF
 Instance(s):
  db112stb

Database Status:
SUCCESS

The Oracle Standby Database has been ingested by the engine for use as the dSource.

Connecting a Standby database that is mounted (not open) with managed recovery occurring will need to be linked to Delphix using the SYS user. Should Active Data Guard be in place then a regular Delphix database user could be used during the linking process. The Standby database is running from the environment identified in Delphix as OELVBC1N1.

The screen shot below details the Physical Standby database DB112_STB linking to Delphix.

DGuard_dSourceConfig1.jpg

Perform the Data Guard Switchover.

The role change in this case is performed using the Data Guard Broker command line utility dgmgrl. This assumes that the Data Guard Broker configuration has already been built and is functioning normally. The process to perform the switchover is the following

Check to ensure the Data Guard environment is ready for the switchover.

Check the Primary Site.

  • The Primary site should be indicating that its ready to make the change to a Physical Standby, "TO STANDBY".
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY
  • There should be no archive log gap detected between the sites, "NO GAP".
SQL> set lines 180
SQL> set pages 500
SQL> col dest_name format a20
SQL> col db_unique_name format a10
SQL> select dest_id,dest_name,type,database_mode,recovery_mode,archived_thread# as arcthead#, archived_seq#, applied_thread# as appthrd#,applied_seq#,synchronized,gap_status from v$archive_dest_status where database_mode <> 'UNKNOWN';

  DEST_ID DEST_NAME      TYPE      DATABASE_MODE  RECOVERY_MODE      ARCTHEAD# ARCHIVED_SEQ#  APPTHRD# APPLIED_SEQ# SYN GAP_STATUS
---------- -------------------- -------------- --------------- ----------------------- ---------- ------------- ---------- ------------ --- ------------------------
     1 LOG_ARCHIVE_DEST_1  LOCAL     OPEN      IDLE               1     3017     0      0 NO
     2 LOG_ARCHIVE_DEST_2  PHYSICAL    MOUNTED-STANDBY MANAGED REAL TIME APPLY     1     3017     1     3016 NO NO GAP

Check the Standby Site.

  • The Standby site should be indicating that its ready to make the change to a Primary. There should be little or no lag in either transport or apply.
SQL> set lines 132
SQL> set pages 500
SQL> col name format a24
SQL> col value format a15 
SQL> col datum_time format a25
SQL> select name,value,datum_time from v$dataguard_stats; 

NAME           VALUE      DATUM_TIME
------------------------ --------------- -------------------------
transport lag      +00 00:00:00  08/29/2017 13:10:52
apply lag        +00 00:00:00  08/29/2017 13:10:52
apply finish time    +00 00:00:00.00
             0

estimated startup time  12

Execute the switchover to reverse the roles in each of the sites.

  • From the Primary perform the switchover using the Data Guard Broker command line utility.
DGMGRL> switchover to db112_stb;
Performing switchover NOW, please wait...
Operation requires a connection to instance "db112stb" on database "db112_stb"
Connecting to instance "db112stb"...
Connected.
New primary database "db112_stb" is opening...
Operation requires startup of instance "db112" on database "db112"
Starting instance "db112"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db112_stb"
DGMGRL>
  • The roles should now show they have reversed.
DGMGRL> show configuration;

Configuration - db112

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

SQLPlus can be used to confirm the role change.

  • The new primary database will be now appear at was the the standby site host (oelvbc1n2).
[oracle@oelvbc1n2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 29 14:54:02 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 from v$database;

DB_UNIQUE_NAME         DATABASE_ROLE
------------------------------ ----------------
db112_stb           PRIMARY
  • The new physical standby database will now appear at what was the primary site host (oelvbc1n1).
[oracle@oelvbc1n1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 29 14:54:24 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 from v$database;

DB_UNIQUE_NAME         DATABASE_ROLE
------------------------------ ----------------
db112             PHYSICAL STANDBY

Data Guard database role changes and Delphix.

A role change in a Data Guard Physical Standby environment can be transparent to Delphix. Should you not wish to alter the site that Delphix is using as its dSource and are happy for the dSource to be attached to what was a Standby database and is now a Primary database then the nothing more needs to be done at a Delphix level to accommodate the change.

Delphix will happily allow you to take further snap syncs of the dSource even though its role has altered at an Oracle level.

DGuard_Snapshots_Continue1.jpg

Aligning the dSource in Delphix to the new Standby site.

The use of the standby site as a dSource has been a very deliberate decision and after a role change within Oracle you will typically want to have Delphix reflect the new location of the standby and be using this as the dSource.

Given this after switchover in Oracle has been performed Delphix needs to be pointed at what is the now the new standby site. The mechanism for performing this is to perform the switchover and detach the dSource from the original standby site and attach the new Standby database at what was the original primary site but is now running the current Standby database.

After the role change the Data Guard Broker command line shows that the new standby site now resides at what was the original primary site.

  • The broker shows the Physical Standby is now operating from the Data Guard database db112 (db_unique_name=db112).
[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

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
  • Oracle shows the standby site is now running on node oelvbc1n1 and database db112 (db_unique_name=db112).
[oracle@oelvbc1n1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 29 14:54:24 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 from v$database;

DB_UNIQUE_NAME         DATABASE_ROLE
------------------------------ ----------------
db112             PHYSICAL STANDBY
  • Delphix on the other hand is still pointing to the original site OELVBC1N2 which is now operating as the Primary database.

DGuard_dSourceConfig2.jpg

Aligning the Delphix dSource location with the new Standby site.

The process for aligning Delphix with the new Primary site and environment is to detach the current dSource environment (oelvbc1n2) and database (db_unique_name db112_stb) from Delphix and re-attach the dSource in the Engine to the new Primary site environment (oelvbc1n1) and database (db_unique_name db112).

This is performed through the Delphix Command Line Interface (CLI) as the delphix_admin user using the steps outlined in the following documentation.

Detaching and Attaching an Oracle dSource

Note the CLI alters across releases and the commands and the options set may differ from those in the documentation and detailed below.

  • From the CLI I performed the following to detach the database currently set as the source (the old Standby database that is now a Primary site).
$ ssh delphix_admin@192.168.0.106
Password:

delphix> database
delphix database> ls
Objects
NAME   PROVISIONCONTAINER DESCRIPTION
db112stb -

Children
template

Operations
createEmpty
createRestorationDataset
export
fileMapping
link
oracleSupportedCharacterSets
provision
validateXpp
xpp

delphix database> select db112stb
delphix database 'db112stb'> detachSource
delphix database 'db112stb' detachSource *> ls
Properties
  type: DetachSourceParameters
  source: (required)
delphix database 'db112stb' detachSource *> set source=db112stb
delphix database 'db112stb' detachSource *> ls
Properties
delphix database 'db112stb' detachSource *> ls
Properties
  type: DetachSourceParameters
  source: db112stb (*)

delphix database 'db112stb' detachSource *> commit;
  Dispatched job JOB-703
  DB_DETACH_SOURCE job started for "Sauces/db112stb".
  DB_DETACH_SOURCE job for "Sauces/db112stb" completed successfully.
delphix database 'db112stb'>
  • To attach the old primary site environment/new standby site target host environment must have already been discovered by the Delphix Engine and should appear as an environment and an un-attached database in the Delphix UI.
delphix> database
delphix database> ls
Objects
NAME   PROVISIONCONTAINER DESCRIPTION
db112stb -          

Children
template

Operations
createEmpty
createRestorationDataset
export
fileMapping
link
oracleSupportedCharacterSets
provision
validateXpp
xpp
delphix database> select db112stb
delphix database 'db112stb'> ls
Properties
  type: OracleDatabaseContainer
  name: db112stb
  contentType: NON_CDB
  creationTime: 2017-08-28T05:21:53.658Z
  crossPlatformReady: false
  currentTimeflow: default
  databaseFraction: false
  description: 
  diagnoseNoLoggingFaults: true
  endianness: LITTLE_ENDIAN
  group: Sauces
  liveSource: false
  masked: false
  os: Linux
  performanceMode: DISABLED
  physicalStandby: false
  preProvisioningEnabled: false
  processor: x86
  reference: ORACLE_DB_CONTAINER-30
  restoration: false
  runtime:
    type: OracleDBContainerRuntime
    crossPlatformEligible: false
    crossPlatformScriptUploaded: false
    liveSourceEligible: false
    logSyncActive: false
  sourcingPolicy:
    type: OracleSourcingPolicy
    loadFromBackup: false
    logsyncEnabled: true
    logsyncInterval: 5sec
    logsyncMode: ARCHIVE_ONLY_MODE
  transformation: false


Operations
delete
update
addLiveSource
applyLiveSourceResync
attachSource
connectionInfo
deleteXppUpload
detachSource
discardLiveSourceResync
purgeLogs
refresh
removeLiveSource
requestXppDownload
requestXppUpload
rollback
startLiveSourceResync
switchTimeflow
sync
testPerformanceModeDataLoss
undo
xppStatus

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

delphix database 'db112stb' attachSource *> commit;
  ORACLE_DB_CONTAINER-30
  Dispatched job JOB-704
  DB_ATTACH_SOURCE job started for "Sauces/db112stb".
  Obtaining information from source database "Sauces/db112stb".
  Creating new TimeFlow for dSource "Sauces/db112stb".
  The dSource "db112" was successfully linked from source database "Sauces/db112stb".
  DB_ATTACH_SOURCE job for "Sauces/db112stb" completed successfully.
  • The Delphix UI will show the attach source job running as an action.

DGuard_reSttachDsource1.jpg

  • The dSource should show as active once the attach operations has completed.

DGuard_reAttachDsource2.jpg

 

  • SnapSync operations can now be performed against the relocated dSource as they normally would.

DGuard_reAttachDsource3.jpg

From the UI it can be seen that the dSource is now attached the the database db112 (db_unique_name db112) running from the target host OELVBC1N1.

 

Additional Information:

Delphix Documentation: Linking Standby Databases

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

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp