Rapid database refresh using RMAN Duplicate and Dbvisit Snapshots - Part 1

Part 1 - Rapid database refresh using RMAN Duplicate and Dbvisit Snapshots

Refreshing another environment from Production is almost a rite of passage for all DBAs. There is always a requirement for ‘current’ production data to be captured into a myriad of other environments. Be they Pre-Production for Performance testing a support issue or Test/Dev for developing the latest release of software on a current Production data copy.

Over the years the way these refreshes have been performed has evolved, from full RMAN production restores for environments lucky enough to have space to accommodate a production-sized copy, through to a cut-down Data pump export/import of particular schemas.

Often the end-to-end process can be time-consuming. Planning access to production to take a specific backup, then restoring it to the required environment is only part of the story. In cases where data is to be cut down or masked, the process can take even longer, and often the environment where the cutting and masking actions are performed is time-limited too. All the while your data is growing older and less relevant.

When Dbvisit released the Standby™️ Snapshot Option, I immediately thought of refreshes. This is possible because I’ve seen many variations on how to perform them over the years. For me, the release of our Snapshot Option can service this requirement in a pretty subtle way.

In the first of two blog posts, I will look at how with RMAN duplicate from an active database we can use a snapshot taken from the standby to refresh a database on another server. Then, in part 2, we will examine how this can be scheduled with a DevOps orchestration tool such as Ansible and we’ll work through the Ansible playbook steps to perform the same.

The snapshot could be scheduled to fit with software release cycles, such as a weekly capture. However, the point with using this feature is that many snapshots could be taken to satisfy your requirements, giving you clear access to current production data without physically accessing the production database or impacting your DR position in any way.

Let’s examine the infrastructure used within this post. On the Production Server, we have Database SRC. On the Standby Server, the Standby SRC database is managed by Standby™️. On the same server we have a single read/write snapshot taken on demand named SRCREF. Then on the Development server we have a database refreshed by the RMAN duplicate action called SRCDEV.

As the point of this blog post is to examine the snapshot functionality and its uses, we will start with a running Dbvisit primary/standby configuration and make the assumption that the standby is kept up to date. Our standby server also meets the requirements for a Snapshot Option configuration.

We begin by taking an adhoc single snapshot using the command line utility dbvsnap. Throughout this post we will use only the command line as this plays nicely into part 2 which is the automation of the process using Ansible. The snapshot creation is highlighted below.

oracle@standby[/usr/dbvisit/standby/conf]: dbvsnap -h
 DBVSNAP

 USAGE:
  dbvsnap -V
  dbvsnap -h
  dbvsnap -d DDC -D start|pause|stop|delete|status [-debug_status]
  dbvsnap -d DDC -csnap [-sname <name>] -j <json>
  dbvsnap -d DDC -dsnap -sname <name>
  dbvsnap [-d DDC] -fsnap -sname <name>
  dbvsnap -d DDC -info
  dbvsnap -d DDC -setup
  dbvsnap -d DDC -enabled
  dbvsnap -d DDC -support_package -pid <pid>
  dbvsnap -d DDC -support_package -trace <trace>

=============================================================
DBVSNAP ended on : Tue May  5 12: 17: 58 2020
=============================================================

This command requires additional parameters passed in via a json file. Our DDC is SRC and I wish to call the snapshot SRCREF. Therefore, the json file (SRCREF.json) used is:

{
    "pfile": {
        "sga_target": "650M"
    },
    "permission": "w",
    "ssize": "2048M",
    "activate": "Y",
    "retry_sec": 60
}

Pay careful attention to the activate = Y parameter. This ensures that the snapshot will be created as a database that is open in read/write mode.

oracle@standby[/usr/dbvisit/standby]: dbvsnap -d SRC -sname SRCREF -j conf/SRCREF.json -csnap
=============================================================
Standby™️ Database Technology (9.0.12_0_g1268187) (pid 28128)
Dbvisit Snapshot (pid 28128)
DBVSNAP started on ip-10-0-1-187: Mon May  4 16:55:21 2020
=============================================================

Snapshot SRCREF created

=============================================================
DBVSNAP ended on ip-10-0-1-187: Mon May  4 16:55:30 2020
=============================================================

When we check the snapshot, we can see it’s open in read/write mode:

oracle@standby[/usr/dbvisit/standby]: export ORACLE_SID=SRCREF
oracle@standby[/usr/dbvisit/standby]: sqlplus / as sysdba
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
SRCREF    READ WRITE

SQL>

Preparing the Snapshot for RMAN Duplicate Action

We can now prepare the snapshot to be used as a target from which to perform the RMAN duplicate action to the auxiliary database SRCDEV. In order to successfully do this, we need to ensure we have the following, many of which are one-time actions and can remain in place for when the action is repeated:

• A suitable TNS entry for both the target and auxiliary databases on both servers e.g.
  

  SRCREF =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SRCREF))
  )
  
  SRCDEV =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = develop)(PORT = 1521))
  (CONNECT_DATA = (ORACLE_SID = SRCDEV)(UR = A))
  )

• If the SRCDEV is a pre-existing database then it needs to be dropped and all datafiles, logfiles and controlfiles removed. A pfile for SRCDEV is needed with only the db_name as a requirement. Any pre-existing spfile can be renamed or removed.
• A valid password file needs to exist. In this example, a copy of the password file from the standby database was created as orapwSRCREF and on the Development server this same file was copied as orapwSRCDEV and both placed in the $ORACLE_HOME/dbs directory.
• Finally, the listener.ora on the Development server needs a static entry for the SRCDEV database.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1/dbhome_1)
      (SID_NAME = SRCDEV)
    )
  )

A further requirement of RMAN duplicate is that the target database be started with a spfile.  By default, Standby™️ creates the snapshot with a pfile. So first create a spfile from this pfile and then restart the database to use it.

SQL> create spfile from pfile = '/usr/dbvisit/standby/meta/SRC/SRCREF/init.ora'
SQL> shutdown immediate
SQL> startup
SQL> show parameter spfile

NAME                    TYPE      VALUE
----------------------- --------- ------------------------------
spfile                  string    /u01/app/oracle/product/12.1/dbhome_1/dbs/spfileSRCREF.ora

Starting the Auxiliary Database in nomount Mode and Testing Connections

With all this in place we can now start the Auxiliary database in nomount mode and test our connections. The duplicate command is run from the development server so we will test our connection from there.

RMAN> connect target sys/kiwi123@SRCREF
connected to target database: SRCREF (DBID=1084673255)

RMAN> connect auxiliary sys/kiwi123@SRCDEV
connected to auxiliary database: SRCDEV (not mounted)

As the target database is a Standby™️ Snapshot, the naming convention for the datafiles and logfiles is a little irregular. We can correct this with db_file_name_convert and log_file_name_convert parameters in our duplicate command. 

In addition to this, in previous releases, active duplicates were performed using implicit image copy backups, transferred directly to the destination server. From 12.1 it is also possible to perform active duplicates using backup sets by including the USING BACKUPSET clause. Compared to image copy backups, the unused block compression associated with a backup set can greatly reduce the amount of data pulled across the network for databases containing lots of unused blocks.

Our Final Command is:

duplicate database to SRCDEV
    from active database
    using backupset
    spfile
        parameter_value_convert ('SRCREF','SRCDEV')
        set db_file_name_convert='/usr/dbvisit/standby/snap/SRC/SRCREF/data/SRC/','/u02/app/oracle/oradata/SRCDEV/'
        set log_file_name_convert='/usr/dbvisit/standby/snap/SRC/SRCREF/data/SRC/','/u02/app/oracle/oradata/SRCDEV/'
        set control_files='/u02/app/oracle/oradata/SRCDEV/control01.ctl', '/u02/app/oracle/fra/SRCDEV/control02.ctl'
        set db_recovery_file_dest='/u02/app/oracle/fra/'
        set audit_file_dest='/u02/app/oracle/oradata/SRCDEV/adump'
        set DB_NAME='SRCDEV'
    NOFILENAMECHECK;

This demo is with a relatively small database so the action doesn’t take long. An excerpt from the action is highlighted below:

Starting Duplicate Db at 05/05/2020:00:11:36
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=13 device type=DISK
current log archived
.
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 05/05/2020:00:13:22
RMAN>

Once this has been completed, we can safely remove the snapshot from the standby server.

oracle@standby[/usr/dbvisit/standby]: dbvsnap -d SRC -dsnap -sname SRCREF
=============================================================
Standby™️ Database Technology (9.0.12_0_g1268187) (pid 9975)
Dbvisit Snapshot (pid 9975)
DBVSNAP started on ip-10-0-1-187: Tue May  5 14:32:08 2020
=============================================================

Snapshot SRCREF deleted.

=============================================================
DBVSNAP ended on ip-10-0-1-187: Tue May  5 14:32:13 2020
=============================================================

Summary

In conclusion, this short post has given an example of how to refresh an entire database on a separate host with production data, without impacting either the existing standby database or the production database using RMAN Duplicate and the Standby™️ Snapshot Option.

Alternatively, as the snapshot is in read/write mode, it could also be used as a source for Database Utilities such as datapump across TNS. In the next post, we will run this entire process from an Ansible playbook.