Learn About Synchronizing Your Test and Production Databases

When you have development and test environments deployed in the cloud, you need ways to quickly and efficiently refresh, or synchronize, the test data with your production environment without incurring large storage costs.

Managing environments in the cloud doesn't need to be complex or storage-intensive. You can create a thin clone of an Oracle Database to synchronize the data while using a fraction of the storage capacity and time needed to create a full copy of a database.

Before You Begin

Before you begin synchronizing your test and production databases, review the concepts involved in migrating a database. Read Learn about reducing downtime during database migrations.

Architecture

This architecture shows three options for creating a Test Master version of your production database, and then deploying thin clones, or snapshots, in your development and test environments.

Description of the illustration prod-testmaster-snap-db.png

The following components are part of the architecture:

• Production database: The database with production data, which is the source you'll use to create a Test Master
• Test Master database: A copy of the production database, which you'll use to create a snapshot
• Snapshot: A thin clone created from the Test Master, which you can use for development or test environments

The first step is to create a Test Master database of your production database. You can do this one of three ways:

• Create a physical standby database from the production database. The physical standby acts as the Test Master.

  Use this option if you frequently create clones of the production database, such as daily or weekly, want to create clones quickly, or if you have a very large production database. When the Test Master is a physical standby database, the Test Master is always synchronized with the production and you never need to refresh the data. For very large databases, it isn't possible to quickly refresh the Test Master.

• Create a clone of the production database using Oracle Recovery Manager (RMAN) active duplicate and use the clone as the Test Master.

  Use this option if you occasionally create clones of the production environment, such as semi-monthly or monthly. With this method of creating a Test Master, you can create the Test Master once using RMAN active duplication and then use the Test Master to create clones multiple times in a month.

• Create a full backup copy of the production database and use the backup to create a clone and use the clone as the Test Master.

  Use this option if you refresh the clones infrequently, such as quarterly, semi-annually, or annually. With this method of creating a Test Master, a full backup of database is taken from the production database and the Test Master is created using that production backup. You can use the Test Master to create clones multiple times, as needed.

You can use the Test Master to create space efficient copies (snapshots) of the database for your test and development environments.

The following architecture diagrams provide some guidance on the different methods of creating and managing your Test Master. Choose the option that works best for your environment and the frequency with which you need to refresh the database.

Option I: If your production database is a very large database and you need to refresh development and test environments from production quickly (within a few minutes), then use Oracle Data Guard to create a standby database as your Test Master. The advantage of this method is that the Test Master is always synchronized with your production database and you never need to refresh the Test Master.

Description of the illustration testmaster-dataguard.png

This architecture supports the following flow:

1. Create a Test Master database using Oracle Data Guard. In this scenario, the Test Master is a physical standby of the production database and is always synchronized with production.
2. Create Read/Write snapshots for your test environments. The snapshots are created on the Test Master Server. The Test Master Server uses Oracle ASM Cluster File System (Oracle ACFS).

This architecture supports the following components:

• Production Oracle Database: The production database can use an Oracle Automatic Storage Management (Oracle ASM) , Oracle ACFS, or UNIX filesystem.
• Test Master Database: A fully synchronized physical standby of the production database on Oracle ACFS.
• Snapshots: One or more read/write copies of the Test Master created for test environments.

Option II: If you need to regularly refresh your test environments, such as bi-weekly or monthly, then clone your production database using RMAN Active Duplicate to create a Test Master.

Description of the illustration testmaster-rman-duplicate.png

This architecture supports the following flow:

1. Clone your production database to create a Test Master database RMAN Active Duplicate. Run the RMAN Active Duplicate command from a production database to create the Test Master database.
2. Create Read/Write snapshots for your test environments. The snapshots are created on the same server as the Test Master (Test Master Server). The Test Master Server uses Oracle ACFS.
3. Refresh the Test Master by deleting the Test Master and re-creating a clone of the production database.

This architecture supports the following components:

• Production Oracle Database: The production database can use an Oracle ASM, Oracle ACFS, or UNIX filesystem.
• Test Master Database: A full clone of the production database on an Oracle ACFS filesystem.
• Snapshots: One or more read/write snapshots of the Test Master created for test environments.

Option III: If you need to refresh from production occasionally (every few months), then you can use RMAN full backup from production to create a Test Master database. To simplify the refresh process, create an Oracle Cloud Infrastructure File Storage service and mount it across the production and test master database server. When you take the RMAN backup of the production database on Oracle Cloud Infrastructure File Storage, the backup is also available on the test master server and available to create a Test Master.

Description of the illustration testmaster-rman-full.png

This architecture supports the following flow:

1. Create a full backup copy of your production database on Oracle Cloud Infrastructure File Storage service and mount the backup on the Test Master Server.
2. Create Read/Write snapshots for your test environments. The snapshots are created on the Test Master Server. The Test Master Server uses Oracle ACFS.
3. Refresh the Test Master, as needed, by deleting the existing Test Master and recreating it using a fresh backup of the production database.

This architecture supports the following components:

• Production Oracle Database: The production database can use an Oracle ASM, Oracle ACFS, or UNIX filesystem.
• Oracle RMAN Full Backup: The RMAN utility creates a full backup of the production database on the Oracle Cloud Infrastructure File Storage service.
• Test Master Database: A full clone of the production database on an Oracle ACFS filesystem.
• Snapshots: One or more read/write snapshots of the Test Master created for test environments.

About Thin Cloning with the gDBClone Utility

The Database Clone Snapshot Management (gDBClone) utility is designed to provide simple and efficient methods of creating snapshot clones and managing databases for test and development environments.

The utility uses Oracle ASM Cluster File System (Oracle ACFS). The Oracle ACFS point-in-time snapshot technology and the gDBClone utility provide powerful, flexible, and simple tools that reduce the time and complexity of managing test and development servers. Use these tools to quickly and efficiently synchronize data between your test, development, and production databases on a regular schedule, or as needed.

An Oracle ACFS snapshot is an online, read only or read write, point in time copy of an Oracle ACFS file system. The snapshot copy is space efficient and uses Oracle ACFS Redirect-on-Write (ROW) functionality. Before an Oracle ACFS file extent is modified or deleted, its current value is preserved in the snapshot to maintain the point in time view of the file system. Oracle ACFS supports 1023 snapshots per file system.

The gDBClone script enables you to perform the following key functions:

• Clone: Creates a clone database from a production database. The clone is your Test Master database. The amount of time to create a database clone depends on the database size and on the network throughput.
  • The source database may be on any type of file system, including Oracle Automatic Storage Management (Oracle ASM).
  • If the database is remote, then gDBClone must connect the remote database through the Listener or SCAN (Single Client Access Network) listener for Oracle Real Application Clusters (Oracle RAC).
  • The clone, or Test Master, can be a Primary or Standby database.
  • When using Oracle Recovery Manager (RMAN) Duplicate from Active Database, gDBClone allocates 3RMAN channels. You can overwrite the default by using the -channels RMAN channels number command option.
• Snap: Creates a sparse snapshot (thin clone) of an Oracle Database. The snapshot copy is space-efficient and uses Redirect-on-Write (ACFS ROW) functionality.
• Convert: Converts a given database to Oracle Real Application Clusters (Oracle RAC) One Node, Oracle RAC, or from a non-container database to a pluggable database (PDB) of a given container database (CDB)
• ListDBs: Lists the cloned databases and its snapshots
• DelDB: Deletes cloned databases or its snapshots
• ListHomes: Lists the available Oracle home
• ACFS
• SYSPwF: Creates an encrypted password file

About Required Services, Products, and Roles

This solution requires the following products, services, and roles:

The source system (production database) can be any of the following:

• 1-node Oracle Cloud Infrastructure virtual machine database system
• 2-node Oracle RAC virtual machine database system
• 1-node bare metal Oracle Database system
• Oracle Database Exadata Cloud Service database system

The production database can use Oracle Automatic Storage Management (Oracle ASM), Oracle ASM Cluster File System (Oracle ACFS), or a UNIX filesystem.

The target system (Test Master Server) can be any of the following:

• 1-node (single instance) bare metal database system

  The Test Master cannot be a virtual machine database system because a virtual machine database system doesn't support multiple databases.

• Oracle Database Exadata Cloud Service

For the Test Master database instance, the Oracle Database must be stored on a local Oracle ACFS filesystem.

It is recommended that the source and target DB Homes are the same edition and version. Both DB Homes are recommended to be at the same patch level, both bundle and one-off patches.

These are the roles needed for each service.

Product Name: Role	Required to...
Oracle Database: root system privileges or sudo with the ability to run commands as root.	Create the Test Master by either cloning the production database, creating a standby database, or creating a full backup copy.
gDBClone utility: root system privileges or sudo with the ability to run commands as root.	Run most gDBClone tasks, including cloning and snapshots.
Oracle Cloud Infrastructure File Storage service: Member of the OCI_Administrators group.	Create a full database backup with Oracle Recovery Manager (RMAN) on Oracle Cloud Infrastructure File Storage service.

See Learn how to get Oracle Cloud services for Oracle Solutions to get the cloud services you need.