Delphix Products

Creating a DataGuard database using V2P

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

  

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 

Edit section
  1. Provision a VDB named "MYVDB".
  2. 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;
    
  3. 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 

Edit section
  1. Create a target directory for the V2P operation
    mkdir /work/stby
  2. Take a fresh snapshot of MYVDB, This will minimize the  V2P recovery time.
  3. 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>  
  4. 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

Edit section
  1. 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 ~]$
    
  2. 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;
  3. 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>
  4. 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.
  5. 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>
    
  6. 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.
    1. 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
      
    2. 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
      
    3. db_file_name_convert Command
      SQL> alter system set db_file_name_convert='/mnt/provision/MYVDB/datafile','/work/stby' scope=spfile;
      
      System altered.
      
  7. 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>
  8. 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)
        )
      )
  9. 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>
  10. 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>
    
  11. 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>
    
  12. 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.

Edit section

 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
11 views

Permalink