View Only

Creating a DataGuard database using V2P

By Matt Watson posted 10-26-2021 12:47:34 AM

Recommend

Introduction

This post shows how to create a DataGuard standby database using V2P. The article uses a VDB as the source instance, however the procedure will also work with a V2P created directly from a dSource.

It should be noted, This procedure is neither officially supported or tested by Delphix, as such cannot be guaranteed to function as described in future versions. Instructions are provided as a guide only and should be tested and verified against specific Delphix and Oracle versions being used in your environment.

Any VDB refresh/rewind operation performed after the standby is created will result in the DataGuard log transport failing.

In this example, a VDB named "MYVDB" will be configured with a V2P standby database named "STBY". Both instances will be deployed to the same host.

For both the VDB and V2P databases, the instance_name,db_name and db_unique_name will be consistent, "MYVDB" for the VDB and "STBY" for the V2P. This is not a requirement, however simplifies configuration.

VDB Preparation

Provision a VDB named "MYVDB".

Add STANDBY LOGFILE groups to the VDB

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 SIZE 1073741824;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 1073741824;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 1073741824;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 1073741824;

Create a password file for the VDB. Note that the STBY will also require a password file. This can be copied now.

orapwd file=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwMYVDB password=P}assword1 entries=10
cp /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwMYVDB /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwSTBY

Create V2P STBY database

Create a target directory for the V2P operation
```
mkdir /work/stby
```
Take a fresh snapshot of MYVDB, This will minimize the V2P recovery time.

Perform V2P operation. This needs to be performed on the CLI as it uses options not avaliable in the GUI.

  mwdlpx-6.0.10.0.dcol2> /database/export
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.type=OracleSIConfig
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.databaseName=STBY
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.instance.instanceName=STBY
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.instance.instanceNumber=1
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.repository=vdbhost/'/u01/app/oracle/product/19.0.0.0/dbhome_1'
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.uniqueName=STBY
  mwdlpx-6.0.10.0.dcol2 database export *> set sourceConfig.environmentUser=vdbhost/oracle
  mwdlpx-6.0.10.0.dcol2 database export *> set timeflowPointParameters.container=MYVDB
  mwdlpx-6.0.10.0.dcol2 database export *> set openDatabase=false
  mwdlpx-6.0.10.0.dcol2 database export *> set filesystemLayout.targetDirectory=/work/stby
  mwdlpx-6.0.10.0.dcol2 database export *> ls
  Properties
      type: OracleExportParameters
      configParams: (unset)
      dspOptions: (unset)
      fileMappingRules: (unset)
      fileParallelism: 3
      filesystemLayout:
          type: OracleExportTimeflowFilesystemLayout
          archiveDirectory: (unset)
          dataDirectory: (unset)
          externalDirectory: (unset)
          scriptDirectory: (unset)
          targetDirectory: /work/stby (*)
          tempDirectory: (unset)
          useAbsolutePathForDataFiles: (unset)
      openDatabase: false (*)
      recoverDatabase: true
      sourceConfig:
          type: OracleSIConfig (*)
          databaseName: STBY (*)
          environmentUser: vdbhost/oracle (*)
          instance:
              type: OracleInstance (*)
              instanceName: STBY (*)
              instanceNumber: 1 (*)
          linkingEnabled: true
          nonSysCredentials: (unset)
          nonSysUser: (unset)
          repository: vdbhost/'/u01/app/oracle/product/19.0.0.0/dbhome_1' (*)
          services: (unset)
          tdeKeystorePassword: (unset)
          uniqueName: STBY (*)
      timeflowPointParameters:
          type: TimeflowPointSemantic
          container: MYVDB (*)
          location: LATEST_POINT
  mwdlpx-6.0.10.0.dcol2 database export *> commit
      Dispatched job JOB-61
      DB_EXPORT job started for "unknown".
....
      The database was successfully exported. Run "/work/stby/STBY/recover-vdb.sh" to recover the database.
      DB_EXPORT job for "unknown" completed successfully.
  mwdlpx-6.0.10.0.dcol2>

The STBY database has now been created, datafiles renamed and recovery performed. No reset logs has been performed, this allows it to be used as a standby database. An init file

Configure V2P STBY database

Create spfile from recovery pfile

[oracle@gi-rhel77 ~]$ export ORACLE_SID=STBY
[oracle@gi-rhel77 ~]$ sqlplus / as sysdba
....
SQL> create spfile from pfile='/work/stby/init.ora.recovery';

File created.

SQL> exit;
[oracle@gi-rhel77 ~]$

Create a standby controlfile and switch to using it.

[oracle@gi-rhel77 ~]$ sqlplus / as sysdba
....
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size            8904776 bytes
Variable Size          624951296 bytes
Database Buffers      432013312 bytes
Redo Buffers            7868416 bytes
Database mounted.

SQL> alter database create standby controlfile as '/work/stby/stby.ctl';

Database altered.

SQL> alter system set control_files='/work/stby/stby.ctl' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit;

Configure STBY instance for DataGuard

[oracle@gi-rhel77 ~]$ sqlplus  / as sysdba
...
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size            8904776 bytes
Variable Size          624951296 bytes
Database Buffers      432013312 bytes
Redo Buffers            7868416 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

Add standby redo logs to the STBY. Standby logfiles should be the same size as the online logfiles. Oracle recommands having at least 1 extra standby logfile than online logfiles.

SQL> select bytes,count(*) from v$log group by bytes;

     BYTES   COUNT(*)
---------- ----------
1073741824        3

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 SIZE 1073741824;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 1073741824;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 1073741824;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 1073741824;

Database altered.

Configure log_file_name_convert.

SQL> alter system set log_file_name_convert='/mnt/provision/MYVDB/datafile/MYVDB/onlinelog/','/work/stby/MYVDB/onlinelog/' scope=spfile;

System altered.

SQL>

Configure db_file_name_convert. Note the specific values here will largely depend on the source MYVDB and target STBY paths. Review v$datafile on source MYVDB and target STBY to determine correct values.

File list on MYVDB

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------------------------------
/mnt/provision/MYVDB/datafile/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_system_gtxz85sq_.dbf
/mnt/provision/MYVDB/datafile/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_sysaux_gtxz9m3g_.dbf
/mnt/provision/MYVDB/datafile/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_undotbs1_gtxzbd7p_.dbf
/mnt/provision/MYVDB/datafile/work/v2p/data/test01.dbf
/mnt/provision/MYVDB/datafile/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_users_gtxzbfcn_.dbf

File list on STBY

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------------------
/work/stby/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_system_gtxz85sq_.dbf
/work/stby/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_sysaux_gtxz9m3g_.dbf
/work/stby/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_undotbs1_gtxzbd7p_.dbf
/work/stby/work/v2p/data/test01.dbf
/work/stby/u01/app/oracle/oradata/DBOMSR8A1718/datafile/o1_mf_users_gtxzbfcn_.dbf

db_file_name_convert Command

SQL> alter system set db_file_name_convert='/mnt/provision/MYVDB/datafile','/work/stby' scope=spfile;

System altered.

Enable DataGuard broker. This step needs to be performed on both the source MYVDB and target STBY.
```
SQL> alter system set dg_broker_start=true;

System altered.

SQL>
```

Configure $ORACLE_HOME/network/admin/tnsnames.ora. While this is not strictly required, it simplifies the the DataGuard broker configuration. If the source VDB and target STBY are on different hosts, this configuration needs to be performed on both hosts.

[oracle@gi-rhel77 admin]$ cat tnsnames.ora
MYVDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vdbhost.dcol2.delphix.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MYVDB)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vdbhost.dcol2.delphix.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = STBY)
    )
  )

Verify that both the source VDB and target STBY can be connected to using password file. If this fails, A previous step has been performed correctly, resolve this before proceeding

[oracle@gi-rhel77 admin]$ sqlplus sys/P}assword1@MYVDB as sysdba
....
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

STBY instance needs to be restarted to ensure all changed values in parameter file are set.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size            8904776 bytes
Variable Size          624951296 bytes
Database Buffers      432013312 bytes
Redo Buffers            7868416 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

Configure DataGuard Manager.

[oracle@gi-rhel77 admin]$ dgmgrl sys/P}assword1@MYVDB
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Oct 17 21:52:11 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "MYVDB"
Connected as SYSDBA.
DGMGRL> create configuration dg_config as primary database is MYVDB connect identifier is MYVDB;
Configuration "dg_config" created with primary database "myvdb"
DGMGRL> add database STBY as connect identifier is STBY MAINTAINED AS PHYSICAL;
Database "stby" added
DGMGRL> enable configuration
Enabled.
DGMGRL>

Verify configuration ( Note: Configuration may initially show "Warning: ORA-16853: apply lag has exceeded specified threshold" until the STBY instance has applied logs generated between the snapshot used for the V2P operation and configuration setup ).

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  myvdb - Primary database
    stby  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> show database stby

Database - stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STBY

Database Status:
SUCCESS

DGMGRL>

Performing a Failover to STBY.

At this point, the MYVDB and STBY are running side by side, any logs generated on the VDB MYVDB will be applied to physical STBY. When ready the VDB can be disabled, and a switchover operation performed on STBY, allowing it to be opened for read/write operations.

mwdlpx-6.0.10.0.dcol2> /source
mwdlpx-6.0.10.0.dcol2 source> select MYVDB
mwdlpx-6.0.10.0.dcol2 source 'MYVDB'> disable
mwdlpx-6.0.10.0.dcol2 source 'MYVDB' disable *> commit
    Dispatched job JOB-62
    SOURCE_DISABLE job started for "MYVDB".
    Disabling virtual database "MYVDB".
    Unexporting storage containers.
    Virtual database "MYVDB" disabled.
    SOURCE_DISABLE job for "MYVDB" completed successfully.

[oracle@gi-rhel77 ~]$ dgmgrl sys/P}assword1@STBY
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Oct 17 22:52:15 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "STBY"
Connected as SYSDBA.
DGMGRL> failover to stby
Performing failover NOW, please wait...
Failover succeeded, new primary is "stby"D
GMGRL> exit
[oracle@gi-rhel77 ~]$ export ORACLE_SID=STBY
[oracle@gi-rhel77 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 17 22:53:42 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>

#dataguard
#v2p
#failover
#Virtualization

0 comments

20 views

Delphix Products

Creating a DataGuard database using V2P

By Matt Watson posted 10-26-2021 12:47:34 AM

Introduction

VDB Preparation

Create V2P STBY database

Configure V2P STBY database

Performing a Failover to STBY.

Permalink

Product

Solutions

Company

Resources

Support

Delphix Products

Creating a DataGuard database using V2P

By Matt Watson posted 10-26-2021 12:47:34 AM

Introduction

VDB Preparation

Create V2P STBY database

Configure V2P STBY database

Performing a Failover to STBY.

Permalink

Related Content

Some interesting facts about Oracle HCC (Hybrid Columnar Compression)

Migrating Prod-DR DB's to New Server

BCP/Standby/DR Testing with Delphix Standby dSources

Failed to mount database instance during provisioning

DB2 standby server requirements

Product

Solutions

Company

Resources

Support