1. Synchronize your test and production databases
  2. Create a Test Master

Create a Test Master

Determine the best method of cloning the data for each of your development and test environments based on how often and how quickly you require updated information.

Review the following data refresh frequency guidelines to help you determine the best method of cloning your production database to create your Test Master:

  • Frequent refreshes (daily, weekly): Create a Test Master as a physical standby database
  • Regular refreshes (bi-weekly, monthly): Create a Test Master from an active database using RMAN DUPLICATE
  • Occasional refreshes (quarterly, semi-annually): Create a Test Master using RMAN full database backup

Create a Test Master as a Physical Standby Database

Create a Test Master that is a physical standby database when you need to always be synchronized with production.

The physical standby is the Test Master for creating snapshots, don't use the Test Master for disaster recovery. The snapshots are created on the same server as the Test Master on Oracle ACFS.

This example uses Oracle Database version 12.1.0.2 with the following names:

  • Source production database instance: PRODDB (the PDB name is PDB1)
  • Test Master database instance: TSTMSTR (the PDB name is PDB1)
Add the Test Master hostname entry to the /etc/hosts directory of the production database, configure the ports on the Test Master, and configure Oracle Data Guard between the Production and Test Master databases.
  1. Clone the production database to create a Test Master as a physical standby database.

    Use the following gDBClone options to create a physical standby database:

    • -sdbname: Source Database Name
    • -sdbscan: Source DB Host SCAN Name
    • -sdbport: Source SCAN Listener Port (default 1521)
    • -tdbname: Target Database Name
    • -tdbhome: Target Database Home
    • -tdbport: Target SCAN Listener Port (default 1521)
    • -racmod: Oracle RAC type, use 0 for an Oracle Single Instance database
    • -dataacfs: Database datafiles target Oracle ACFS storage
    • -redoacfs: Database redologs target Oracle ACFS storage (default dataacfs)
    • -recoacfs: Database recovery target Oracle ACFS storage (default dataacfs)
    • -channels: RMAN allocated channels (default 3)
    • -standby: The clone (snap) is a physical standby database
    • -pmode: Standby mode options are maxperf, maxavail, and maxprot (default maxperf)
    • -activedg: Enable Active Data Guard
    • -syspwf: SYS encrypted password file 
    [root@tstmstr gDBClone]# /opt/gDBClone/gDBClone clone -sdbname PRODDB_iad1df.bass1.ebsvcn.examplevcn.com -sdbscan proddb.bass1.ebsvcn.examplevcn.com sdbport 1521 -tdbname TSTMSTR -tdbhome OraDB12102_home2 -tdomain bass1.ebsvcn.examplevcn.com -tdbport 1521 -racmod 0 -dataacfs /u02/app/oracle/oradata/TSTMSTR -redoacfs /u03/app/oracle/redo -recoacfs /u03/app/oracle/fast_recovery_area -channels 2 -standby -pmode maxperf -activedg -rtapply -noping -syspwf /opt/gDBClone/SYS.password
  2. Verify that the Test Master database information is accurate.
    [oracle@tstmstr TSTMSTR]$ cat /etc/oratab|grep -i TSTMSTR
  3. Display a list of the databases using the gDBClone listdbs command to confirm that the role and location are correct for the standby database.
    Use the gDBClone listdbs -tree command to display parent child relationships. 
    [root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs
Database Name    Database Type   Database Role      Master/Snapshot  Location/Parent
-------------    -------------   -------------      ---------------  ---------------
ORCL_iad1hb      SINGLE          PRIMARY            n/a              ASM            
TSTMSTR          SINGLE          PHYSICAL_STANDBY   Master           /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/

[root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs -tree
Parent	Child
------	-----
TSTMSTR
  4. Set the cluster environment variables for the Test Master.
    Configure the Test Master to be a read only physical standby database.
    [oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
[oracle@tstmstr ~]$ 
[oracle@tstmstr ~]$ srvctl setenv database -d TSTMSTR -envs "ORACLE_UNQNAME=TSTMSTR,TZ=UTC"
[oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
ORACLE_UNQNAME=TSTMSTR
TZ=UTC
[oracle@tstmstr ~]$ 

[oracle@tstmstr ~]$ . oraenv
ORACLE_SID = [oracle] ? TSTMSTR
The Oracle base has been set to /u01/app/oracle
[oracle@tstmstr ~]$ srvctl config database -d TSTMSTR
Database unique name: TSTMSTR
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/TSTMSTR/TSTMSTR/spfileTSTMSTR.ora
Password file: 
Domain: bass1.ebsvcn.examplevcn.com
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: 
Mount point paths: /u02/app/oracle/oradata/TSTMSTR,/u03/app/oracle/
Services: 
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: TSTMSTR
Configured nodes: tstmstr
Database is administrator managed
[oracle@tstmstr ~]$
  5. Restart the Test Master database.
    
[oracle@tstmstr ~]$ srvctl stop database -d TSTMSTR
[oracle@tstmstr ~]$ srvctl start database -d TSTMSTR
  6. Check the TDE wallet status in the Test Master database.
    The wallet status should be OPEN and the wallet type should be AUTOLOGIN. 
    [oracle@tstmstr ~]$ export 
ORACLE_UNQNAME=TSTMSTR
[oracle@tstmstr ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 30 06:44:14 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:Oracle Database 12c EE High Perf Release 
12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

SQL> set linesize 180
SQL> col wrl_parameter format a80
SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                                      STATUS       WALLET_TYPE  WALLET_OR  FULLY_BAC   CON_ID
--------  ------------ -------------------------------------  -----------  -----------  ---------  ---------   -------
FILE      /opt/oracle/dcs/commonstore/wallets/tde/TSTMSTR/    OPEN         AUTOLOGIN    SINGLE     NO         0
  7. Check Test Master database status.
    SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
PRODDB	  MOUNTED	       PHYSICAL STANDBY

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 3 PDB1 			     MOUNTED
  8. Verify that the physical standby is synchronized with production.
    You can switch some log files on production and check if redo is getting transported and applied on the Test Master database. 
    SQL> select sequence#,archived,applied from v$archived_log order by sequence#;
The Test Master is a physical standby database and is synchronized with the production database. You can create a snapshot of the database at any time.

Create a Test Master from an Active Database

Create a database clone from an online production database using Oracle Recovery Manager (RMAN) Active Duplication when you need to refresh a test environment regularly.

This example uses Oracle Database version 12.1.0.2 with the following names:

  • Source production database instance: PRODDB (the PDB name is PDB1)
  • Test Master database instance: TSTMSTR (the PDB name is PDB1)
Add the Test Master to the directory, configure the port, and configure Oracle Data Guard on the Production database instance.
  1. Create a Test Master using RMAN Active Duplication.

    Use the following gDBClone options to create a physical standby database:

    • -sdbname: Source Database Name
    • -sdbscan: Source DB Host SCAN Name
    • -sdbport: Source SCAN Listener Port (default 1521)
    • -tdbname: Target Database Name
    • -tdbhome: Target Database Home
    • -tdbport: Target SCAN Listener Port (default 1521)
    • -racmod: Oracle RAC type, use 0 for an Oracle Single Instance database
    • -dataacfs: Database datafiles target Oracle ACFS storage
    • -redoacfs: Database redologs target Oracle ACFS storage (default dataacfs)
    • -recoacfs: Database recovery target Oracle ACFS storage (default dataacfs)
    • -channels: RMAN allocated channels (default 3)
    • -standby: The clone (snap) is a physical standby database
    • -pmode: Standby mode options are maxperf, maxavail, and maxprot (default maxperf)
    • -activedg: Enable Active Data Guard
    • -syspwf: SYS encrypted password file 
    root@tstmstr gDBClone]# /opt/gDBClone/gDBClone clone -sdbname PRODDB_iad1df.bassubad1.ebsvcn.oraclevcn.com -sdbscan proddb.bassubad1.ebsvcn.oraclevcn.com sdbport 1521 -tdbname TSTMSTR -tdbhome OraDB12102_home2 -tdomain bassubad1.ebsvcn.oraclevcn.com -tdbport 1521 -racmod 0 -dataacfs /u02/app/oracle/oradata/TSTMSTR -redoacfs /u03/app/oracle/redo -recoacfs /u03/app/oracle/fast_recovery_area -channels 2 -noping -syspwf /opt/gDBClone/SYS.password
  2. Verify that the Test Master database information is accurate.
    [oracle@tstmstr TSTMSTR]$ cat /etc/oratab|grep -i TSTMSTR
  3. Display a list of the databases using the gDBClone listdbs command to confirm that the role and location are correct for the standby database.
    Use the gDBClone listdbs -tree command to display parent child relationships. 
    [root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs
Database Name    Database Type   Database Role      Master/Snapshot  Location/Parent
-------------    -------------   -------------      ---------------  ---------------
ORCL_iad1hb      SINGLE          PRIMARY            n/a              ASM            
TSTMSTR          SINGLE          PRIMARY            Master           /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/

[root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs -tree
Parent	Child
------	-----
TSTMSTR
  4. Set the cluster environment variables for the Test Master.
    Configure the Test Master to be a read only physical standby database.
    
[oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
[oracle@tstmstr ~]$ 
[oracle@tstmstr ~]$ srvctl setenv database -d TSTMSTR -envs "ORACLE_UNQNAME=TSTMSTR,TZ=UTC"
[oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
ORACLE_UNQNAME=TSTMSTR
TZ=UTC
[oracle@tstmstr ~]$ 

[oracle@tstmstr ~]$ . oraenv
ORACLE_SID = [oracle] ? TSTMSTR
The Oracle base has been set to /u01/app/oracle
[oracle@tstmstr TSTMSTR]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
ORACLE_UNQNAME=TSTMSTR
TZ=UTC
[oracle@tstmstr TSTMSTR]$ srvctl config database -d TSTMSTR
Database unique name: TSTMSTR
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/TSTMSTR/TSTMSTR/spfileTSTMSTR.ora
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: 
Mount point paths: /u02/app/oracle/oradata/TSTMSTR,/u03/app/oracle/
Services: 
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: TSTMSTR
Configured nodes: tstmstr
Database is administrator managed
[oracle@tstmstr TSTMSTR]$
  5. Restart the Test Master database.
    [oracle@tstmstr ~]$ srvctl stop database -d TSTMSTR
[oracle@tstmstr ~]$ srvctl start database -d TSTMSTR
  6. Check the Transparent Data Encryption (TDE) wallet status in the Test Master database.
    The wallet status should be OPEN and the wallet type should be AUTOLOGIN. 
    [oracle@tstmstr ~]$ export ORACLE_UNQNAME=TSTMSTR
[oracle@tstmstr ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 30 06:44:14 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

SQL> set linesize 180
SQL> col wrl_parameter format a80
SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER					   STATUS	WALLET_TYPE   WALLET_OR   FULLY_BAC   CON_ID
--------   -------------------------------------------------  ----------- -------------  ---------   ----------- -------
FILE	/opt/oracle/dcs/commonstore/wallets/tde/TSTMSTR/   OPEN	 AUTOLOGIN      SINGLE      NO	   0
  7. Check Test Master database status and confirm that the pluggable databases (PDBs) are mounted.
    SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
TSTMSTR   READ WRITE	       PRIMARY

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 3 PDB1 			     MOUNTED
When the Test Master is available, you can create a thin clone (snapshot) database. When the Test Master is out-dated, refresh the clone Test Master Database from the production database before creating a snapshot.

Create a Test Master from a Full Database Backup

Create a full backup copy of your source production database on Oracle Cloud Infrastructure File Storage service when your Test Master requires occasional refreshes. Create the backup with Oracle Recovery Manager (RMAN) Active Duplication and mount the backup on the Test Master Server.

This example uses Oracle Database version 12.1.0.2 with the following names:

  • Source production database instance: PRODDB (the PDB name is PDB1)
  • Test Master database instance: TSTMSTR (the PDB name is PDB1)
  1. Create a production database backup by setting up the clone environment and cloning the database using RMAN.
    Use the following gDBClone options:
    • -sdbname: Source Database Name
    • -sbckloc: Source RMAN Full Backup Location
    • -tdbname: Target Database Name
    • -tdbhome: Target Database Home
    • -sdbport: Source SCAN Listener Port (default 1521)
    • -racmod: Oracle Real Application Clusters (Oracle RAC) model, use 0 for an Oracle Single Instance database
    • -dataacfs: Database datafiles target Oracle ASM Cluster File System (Oracle ACFS) storage
    • -redoacfs: Database redo logs target Oracle ACFS storage (default dataacfs)
    • -recoacfs: Database recovery target Oracle ACFS storage (default dataacfs)
    • -channels: RMAN allocated channels (default 3)
    • -syspwf: SYS encrypted password file 
    [root@tstmstr gDBClone]# /opt/gDBClone/gDBClone clone -sdbname TSTMSTR -sbckloc '/mnt/fss/dbbackup'  -tdbname TSTDEV -tdbhome OraDB12102_home2 -tdbport 1521 -racmod 0 -dataacfs /u02/app/oracle/oradata/TSTMSTR -redoacfs /u03/app/oracle/redo -recoacfs /u03/app/oracle/fast_recovery_area -channels 2 -noping -syspwf /opt/gDBClone/SYS.password
    The script validates the setup, sets up the clone environment, and creates a clone database.
  2. Verify that the Test Master database information is accurate. 
    [oracle@tstmstr TSTMSTR]$ cat /etc/oratab|grep -i TSTMSTR
  3. Display a list of the databases using the gDBClone listdbs command to confirm that the role and location are correct for the standby database.
    Use the gDBClone listdbs -tree command to display parent child relationships. 
    [root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs
Database Name    Database Type   Database Role      Master/Snapshot  Location/Parent
-------------    -------------   -------------      ---------------  ---------------
ORCL_iad1hb      SINGLE          PRIMARY            n/a              ASM            
TSTMSTR          SINGLE          PRIMARY            Master           /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/
[root@tstmstr gDBClone]# 

[root@tstmstr gDBClone]# /opt/gDBClone/gDBClone listdbs -tree
Parent	Child
------	-----
TSTMSTR
  4. Set the cluster environment parameters for the Test Master database.
    [oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
[oracle@tstmstr ~]$ 
[oracle@tstmstr ~]$ srvctl setenv database -d TSTMSTR -envs "ORACLE_UNQNAME=TSTMSTR,TZ=UTC"
[oracle@tstmstr ~]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
ORACLE_UNQNAME=TSTMSTR
TZ=UTC
[oracle@tstmstr ~]$ 

[oracle@tstmstr ~]$ . oraenv
ORACLE_SID = [oracle] ? TSTMSTR
The Oracle base has been set to /u01/app/oracle
[oracle@tstmstr TSTMSTR]$ srvctl getenv database -d TSTMSTR
TSTMSTR:
ORACLE_UNQNAME=TSTMSTR
TZ=UTC
[oracle@tstmstr TSTMSTR]$ srvctl config database -d TSTMSTR
Database unique name: TSTMSTR
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/TSTMSTR/.ACFS/snaps/TSTMSTR/TSTMSTR/spfileTSTMSTR.ora
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: 
Mount point paths: /u02/app/oracle/oradata/TSTMSTR,/u03/app/oracle/
Services: 
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: TSTMSTR
Configured nodes: tstmstr
Database is administrator managed
[oracle@tstmstr TSTMSTR]$
  5. Restart Test Master database
    
[oracle@tstmstr ~]$ srvctl stop database -d TSTMSTR
[oracle@tstmstr ~]$ srvctl start database -d TSTMSTR
  6. Check the Transparent Data Encryption (TDE) wallet status in the Test Master database.
    The wallet should be OPEN with AUTOLOGIN.
    [oracle@tstmstr ~]$ export ORACLE_UNQNAME=TSTMSTR
[oracle@tstmstr ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 30 06:44:14 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

SQL> set linesize 180
SQL> col wrl_parameter format a80
SQL> select * from v$encryption_wallet;

WRL_TYPE    WRL_PARAMETER					    STATUS	WALLET_TYPE	   WALLET_OR  FULLY_BAC   CON_ID
--------    -------------------------------------------------   ----------- -------------------- ---------   ----------- -------
FILE	 /opt/oracle/dcs/commonstore/wallets/tde/TSTMSTR/    OPEN	 AUTOLOGIN	      SINGLE     NO	   0
  7. Check the status of the Test Master database and confirm that the pluggable databases (PDBs) are mounted.
    The TSTMSTR database should be in read write mode and in MOUNTED state.
    SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	  DATABASE_ROLE
--------- -------------------- ----------------
TSTMSTR   READ WRITE	    PRIMARY
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			 OPEN MODE  RESTRICTED
---------- ----------------------------- ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 3 PDB1 			     MOUNTED
The Test Master is a full backup copy of your source production database.
Create a snapshot of the database. When the Test Master is out-dated, refresh the backup Test Master Database from the production database before creating a snapshot.