This page describes Dbvisit Snapshots feature, its prerequisites, limitations and benefits. Dbvisit snapshots are divided into two main functionalinities:

• Snapshots (referred to as Individial Snapshots, Test/Dev Snapshots further in the text)

• Reporting Replicas

“Snapshots” functionality is what enables you to create and delete snapshots manually. “Reporting replicas” functionality is the base Snapshot functionality with customizable automatic refresh module on top of it.

1. Dbvisit Snapshot Concept

Dbvisit Snapshots (regardless whether Individial Snapshots or reporting replica) are a “fast read-write copy of your standby database on standby server”. Because your standby database will be always near-real time sync with your primary, any snapshot will contain near real-time data of your primary. Snapshots however remain static and its data can’t be refreshed.

Snapshots are always composed of:

• Logical volume snapshot copy of source logical volume where your standby Database runs

• New Oracle Instance running on this logical volume snapshot copy

Dbvisit snapshots feature utilizes Linux Logical Volume manager functionality and operates with lvs, vgs, lvcreate and mount commands run in the OS. Snapshots only every happen on standby server. Because of this there are very specific prerequisites you need to fulfill discussed in chapter “Prerequisites”.

Every time snapshot is created (regardless whether manually or executed in reporting replicas) following happens:

• Source Standby database is put into read-only mode

• Logical volume snapshot copy is created an mounted on dedicated mountpoint

• Oracle database controlfile copy on logical volume snapshot copy is recreated with changed database file paths

• Snapshot Oracle Instance is started

• Snapshot Database is activated and opened read write or read only

• Source Standby database is put into MOUNT mode again

The snapshot create takes typically few minutes only, regardles of source standby database size. Once created, snapshot can’t be refreshed and will contain static data. The only way to refresh the snapshot is to recreate it again from the source standby database which will always have near real-time copy of data from your primary. The refresh is done automatically in intervals which you specify when using reporting replica.

Using reporting replicas will almost always mean that multiple snapshot instances (in adition to source standby instance) will be running on your standby server. For more detailed concept about reporting replica, see its dedicated section below.

2. Dbvisit Snapshot Intended Use

Dbvisit Snapshots are a very fast way how to get a read-write copy of your production database data. This can be helpful in several cases:

• Reporting (Reporting Replicas)

• Testing DBA scripts and admin tasks (Test/Dev Snapshots)

• Providing developers with complete copy of production environment for troubleshooting (Test/Dev Snapshots)

• DR Tests and New Application rollout functional testing (Test/Dev Snapshots)

There are however not suitable for:

• Realtime Reporting (Snapshot always contains static data and will be lagging some time behind primary: typically up to 20-30 minutes)

• Creating Long-Term Production Copy Environments (Snapshots grow in time and from near 0 size the would eventually be same size as source volume)

• Performance Tests / Task Duration Test (Because of copy-on-write concept, snapshots will always suffer from degraded disk performance which will not reflect duration of specific task on production)

3. Dbvisit Snapshot Prerequisites

Dbvisit Snapshots are complex and advanced feature which requires your standby server and standby database to be setup in very specific way. It’s recommended to consider all prerequistes before provisioning the standby server. Following prerequisites must be fulfilled without any exception before using Snapshots on your standby database.

3.1 Linux Only

Snapshots are available only on Linux, because only Linux provides us with features (LVM) necessary for creating fast volume copy. Snapshots will not work (nor are planned) on Windows platform.

3.2 Source Standby Database must be located wholly on single Logical Volume with correct filesystem type

When setting up standby database, you must ensure that all controlfile copies, all datafile, all tempfiles, all redolog members and certain init parameters are pointed to single logical volume. So following commands on standby database:

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$tempfile;
SQL> sho parameter control_files;
SQL> sho parameter db_recovery_file_dest;
SQL> sho parameter archive;

must return paths which point to single mountpoint. You can then use lsblk to determine whether that mountpoint is logical volume (marked as “LVM2_member” in this example):

$ lsblk -o name,size,fstype,MOUNTPOINT
NAME         SIZE FSTYPE      MOUNTPOINT
sda           75G
├─sda1      1000M xfs         /boot
└─sda2        74G LVM2_member
  ├─ol-root   27G xfs         /
  ├─ol-swap    7G swap        [SWAP]
  ├─ol-u02    15G xfs         /u02
  └─ol-u01    15G xfs         /u01
sr0         1024M
sr1         1024M

If your standby is spread across more mountpoints or non-lvm, you will have to recreate your standby database from the scratch so as it has correct layout. You can utilize convert and OMF parameters to achieve this:

Prepare Standby Database File Locations

Important is also the “fstype” - Dbvisit Snapshot supports only xfs, ext3 and ext4 filesystems

3.3 Free Space in Logical Volume Group

Logical volume group which the source logical volume is part of must have free space available. Note that this is unallocated logical volume group free space, which is different from free space which you see by df -h commad. To determine available free space in your logical volume group, first run following command:

$ sudo lvs
  LV   VG Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  root ol -wi-ao---- 27.00g
  swap ol -wi-ao----  7.00g
  u01  ol -wi-ao---- 15.00g
  u02  ol -wi-ao---- 15.00g

this will tell you which logical volume group is your logical volume part of. In this example logical volumes u01 and u02 (along with root and swap) are part of “ol” volume group.

Afetrwards list the volume group information:

$ sudo vgs
  VG #PV #LV #SN Attr   VSize   VFree
  ol   1   4   0 wz--n- <74.02g <10.02g

The “VFree” in this example is the free unallocated logical volume group free space.

How much free space is needed is a complex question, depending on number of factors which are discussed further in this chapter. You can however consider 30g free as “good quickstart” value. Production database size has NO influence on this consideration.

The exact amount of free space needed will depend on number of Dbvisit Standby snapshots you intend to run.

3.4 Oracle user must have privileges to run lvm commands as root

It is important that lvm related commands can be executed by oracle user without password prompt. This can be achieved by either giving oracle full sudo permission or specifying individual commands. Run “visudo” command as root and:

Include / modify follwing line to give oracle full sudo permission:

oracle ALL=(ALL) NOPASSWD:ALL

OR

Include / modify follwing lines to give oracle sudo permission only for related lvm commands:

oracle ALL = (root) NOPASSWD: /usr/sbin/lvs
oracle ALL = (root) NOPASSWD: /usr/sbin/vgs
oracle ALL = (root) NOPASSWD: /usr/sbin/lvcreate
oracle ALL = (root) NOPASSWD: /usr/sbin/lvremove
oracle ALL = (root) NOPASSWD: /usr/bin/mount
oracle ALL = (root) NOPASSWD: /usr/bin/umount

We recommend that after you finish editing with visudo, to try all of those commands as oracle with sudo to ensure no password is asked:

for example:

$ sudo lvs
  LV   VG Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  root ol -wi-ao---- 27.00g
  swap ol -wi-ao----  7.00g
  u01  ol -wi-ao---- 15.00g
  u02  ol -wi-ao---- 15.00g
$ sudo lvremove
  No command with matching syntax recognised.  Run 'lvremove --help' for more information.
  Correct command syntax is:
  lvremove VG|LV|Tag|Select ...

etc ..

3.5 RAM Configuration of your standby

Each Dbvisit Snapshot is an actual new Oracle instance on your standby server. So additional memory will be needed to run your Dbvisit snapshots. We allow you to specify in a comprehensive way the memory for each specific snapshot. You can have for example source standby with 20GB of SGA and then 3 Dbvisit Snapshots with 5G SGA each.

The exact amount of free RAM needed will depend on number of Dbvisit Standby snapshots you intend to run.

3.6 Oracle Listener must be running

Snapshots have requirement that oracle listener is running on standby server. You can start Oracle listener on standby server as oracle user by running:

$ lsnrctl start

3.7 Redolog Configuration on the primary database

During each snapshot generation, snapshot database is opened with resetlogs. This means that the total size of your primary database redologs:

SQL> select sum(bytes)/1024/1024 from v$log;

Will need to be written to disk on standby server during snapshot creation. This affects in a major way how fast the snapshots are created. For example have 12 redo members with 2GB size each will mean that 24GB of data will need to be written to disk when snapshot is created.

4. Single Snapshot (Test/Dev Snapshots)

Once all prerequisites are in place, you will be able to create a single snapshot. Single snapshot is on-demand snapshot created from your source standby Database. Dbvisit doesn’t maintain the snapshot in any way are you’re responsible for deleting the snapshots manually.

• Snapshots always have to be deleted before Graceful Switchover (GS)

• Snapshots are not automatically started after server reboot

• Snapshot instance service will be automatically registered with the Oracle listener

Start by choosing “Snapshots” from the ACTIONS pane:

You will see your LVM configuration summary with available free space. Click on create New snapshot:

When creating new snapshot, you will need to complete the following form:

Once form is submitted, you will be able to see on standby server OS new volume being mounted and new Oracle Instance started. The whole snapshot creation can take few minutes.After completion, GUI will be refreshed with information about created snapshot:

it is possible to stop or delete the snapshot Instance (1).

OS will show following similar information:

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
devtmpfs                   3.9G     0  3.9G   0% /dev
tmpfs                      3.9G     0  3.9G   0% /dev/shm
tmpfs                      3.9G  187M  3.7G   5% /run
tmpfs                      3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/mapper/ol-root         27G   17G   11G  62% /
/dev/mapper/ol-u02          15G  7.0G  8.0G  47% /u02
/dev/sda1                  994M  316M  679M  32% /boot
/dev/mapper/ol-u01          15G   12G  4.0G  74% /u01
tmpfs                      795M     0  795M   0% /run/user/54321
/dev/mapper/ol-test         15G  7.8G  7.2G  52% /usr/dbvisit/standbymp/oracle/snap/SLASH/test

$ ps -ef | grep pmon
oracle   1123163       1  0 09:31 ?        00:00:00 ora_pmon_SLASH
oracle   1124239       1  0 10:07 ?        00:00:00 ora_pmon_test
oracle   1124976 1124928  0 10:10 pts/2    00:00:00 grep --color=auto pmon

Where ol-test is our snapshot copy of source logical volume ol-u02, and Instance “test” is our snapshot Oracle Instance.

5. Reporting Replicas

5.1 Reporting Replicas Concept

Reporting replicas are single snapshots which are grouped together and automatically refreshed. Reporting replicas utilize dedicated database service, which is automatically registered with Oracle Listener and always points to newest snapshot

The flow of reporting replicas is:

• Create a new snapshot #1

• Register specified Service Name With Oracle Listener pointing to Snapshot #1 (so all new application connections will get connected to snapshot #1)

• Wait for specified amount of time (Refresh Interval)

• Create new snapshot #2

• Register specified Service Name With Oracle Listener pointing to Snapshot #2 (so all new application connections will get connected to snapshot #2)

• If number of number of reporting replica snapshots exceed defined number of Active snapshots, delete the oldest snapshot

This loop continues indefinitely.

The flow is also summarized on following diagram:

The result of this concept is following:

• any user connecting to the dedicated database service has guarantee that he will always connect to the snapshot with newest data

• specified number of active snapshots together with the refresh interval define maximum time to finish user SQL (after snapshot is deleted and session is disconnected any SQL progress is lost)

• when snapshot is deleted, all user sessions get disconnected and will reconnect to the snapshot with newest data

5.2 Create Reporting Replica with GUI

We recommend to create a single snapshot to test all works well and that prerequisites are fulfilled before attempting to create Reporting Replica. Start by choosing “Reporting Replica” from the ACTIONS pane:

On the following page click on Create Reporting Replica:

as

s

After the form is submitted, new reporting replica configuration is create, but not yet started:

Press (1) to start reporting replica snapshot generation. You can also Edit (2) and remove the configuration (3).

5.3 Reporting Replica Operations

Once reporting replica is started, you will be able to see easy overview of current progress:

You will be always able to see which snapshot is the current leader (1) = snapshot to which the Oracle Service registered with listener points to.

In addition, you will be able to perform following actions:

(2) Edit Reporting Replica

You can edit any parameter of your reporting replica which you specified earlier and you can edit the parameters even when reporting replicas is running. Whenever you edit parameters, creation of next is sequence snapshot is forced (same as if the refresh interval was reached).

(3) Pause Reporting Replica

When you pause reporting replicas, the refresh will be stopped, but the existing snapshot Instances will remain runinng. Snapshot volumes will remain mounted.

To resume the generation again, press the “play” button. Afterwards, creation of next is sequence snapshot is forced (same as if the refresh interval was reached).

(4) Stop Reporting Replica

When you stop reporting replicas, the refresh will be stopped and snapshot instances will be stopped as well. Snapshot volumes will remain mounted.

To resume the generation again, press the “play” button. Afterwards, creation of next is sequence snapshot is forced (same as if the refresh interval was reached). The snapshot instances which were shut down previously, will not be started. The oldest snapshot logical volume will be deleted

(5) Delete Reporting Replica

Deleting reporting Replica will shut down all Snapshot Instances and umount and delete all snapshot volumes. The state will be same as if you would just create the reporting replica configuration. The configuration will remain untouched.

6. Snapshot Manual Cleanup

You should always use GUI to cleanup or delete the snapshots. If you encounter situation when it is not possible to use GUI for snapshot cleanup, it is possible to do the cleanup manually on the OS level. There are three steps which are described below.

A. stop snapshot instance

You will need to export correct Oracle sid and shutdown the snapshot instance, for example:

export ORACLE_SID=test
sqlplus / as sysdba
SQL> shu abort;

B. umount the logical snapshot volume

You need to identify and umount the snapshot volume. First run “df” command to identify the snapshot volume and its path:

[oracle@czlin0232 trace]$ df -h
...
/dev/mapper/ol-test       15G  7.8G  7.2G  52% /usr/dbvisit/standbymp/oracle/snap/SLASH/test

The volume will be always mounted as standbymp/oracle/snap/<DDC>/<snapshot name>. Remember the device path, in this case: “/dev/mapper/ol-test”. Proceed with mounting the volume:

[oracle@czlin0232 trace]$ sudo umount /usr/dbvisit/standbymp/oracle/snap/SLASH/test

It is possible that you will see error message “resource busy”. In that case, it’s possible to run “umount -l” to force the umount and disregard any processes accessing the mountpoint.

C. remove the logical volume

This is the last step. Here you will need to use the string you remembered earlier, in this example it was “/dev/mapper/ol-test”:

[oracle@czlin0232 mp]$ sudo lvremove /dev/mapper/ol-test
Do you really want to remove active logical volume ol/test? [y/n]: y
  Logical volume "test" successfully removed.

The Dbvisit Snapshot is now successfully deleted from your system.

7. Snapshot Hardware Recomendations

When cosidering the snapshots feature for your environment, it is necessary to consider several factors in order to approximate how to size your standby server properly. There are three main areas for Hardware considerations: Disk Space, Disk Performance and Memory allocation.

7.1 Free Volume Group Space

The disk space needed for snapshot is very individual and specific for each environment. The amount of space needed is dictated by following:

Snapshot size will grow, whenever data are written on source volume or whenever data are written to snapshot itself

In general the space needed for your snapshots is defined by:

A. Total Online Redo Size on you primary database

After the initial logical volume snapshot is created, the snapshot database will be opened with resetlogs. This means that snapshot size will grow for this amount of data (run select on primary):

SQL> select sum(bytes)/1024/1024 from v$log;

B. Total Amount of archivelogs generated by primary during snapshot existence

Source volume for snapshot is your original standby database volume. Each time an archivelog is applied to the original standby database, the snapshot size will grow by the size of the archivelog, because snapshot needs to retain its consistency. Run following select to determine your primary database archivelog generation:

select to_char(trunc(l.first_time,'hh24'),'yyyy-mm-dd hh24:mi') date_time
, count(l.recid) archivelog_count
, round(sum(l.blocks*l.block_size)/1024 /1024) MB
from v$archived_log l
where l.first_time > sysdate-14
group by to_char(trunc(l.first_time,'hh24'),'yyyy-mm-dd hh24:mi') order by date_time desc;

You will need to relate the result to your intended snapshot existence duration.

C. Total Amount of changes written to the snapshot itself

Snapshots will also grow for the exact amount of data you write directly to the snapshot. For example, if you would create a snapshot where you would like to test adding a new PDB, the snapshot size will grow by the exact amount of the PDB.

7.2 Disk Performance

LVM snapshots principle copy-on-write is demanding and we recommend to run snapshots on systems with SSDs or NVMEs. Some cloud environments might not be suitable as they suffer from poor disk performance.

7.3 Memory Allocation

You standby server needs to have sufficient free RAM to be able to run snapshots where each snapshot is a separate oracle instance. You can define different SGA and PGA size for each snapshot. It’s also possible to decrease memory allocated to your source standby database as it will be doing recovery only.

8. Snapshot Pre/Post Processing

You can use Pre/Post processing to automate even further your snapshot processing. This Pre/Post processing is snapshot specific and not related to Pre/Post Processing

Typical use case could be obfuscation of production data or changing user passwords. Dbvisit Standby is installed with snapshot Pre/Post script template:

$ ls -l /usr/dbvisit/standbymp/oracle/doc/snap_pre_post_proc.sh
-rw-r--r--. 1 oracle oinstall 5612 Feb 24 09:23 /usr/dbvisit/standbymp/oracle/doc/snap_pre_post_proc.sh

By checking the contents of the script, you will be able to uderstood how this shell script works. Once you do, you will be able to run your custom code before or after snapshot creation. Example for creating new tablespace and running custom script after the snapshot is created:

A. create custom Pre/Post script

vi /usr/dbvisit/standbymp/oracle/snap_pre_post_proc.sh

Modify script contents:

export ORACLE_SID=$SNAP_ORACLE_SID

case ${1} in
   post)
           case ${2} in
           create)
                echo "alter tablespace temp add tempfile autoextend on maxsize unlimited;" | sqlplus / as sysdba
                sqlplus / as sysdba @/usr/dbvisit/standbymp/oracle/doc/test.sql
                   ;;
           esac
           exit 0
           ;;
   esac

B. Set the script permission to allow execution

chmod +x /usr/dbvisit/standbymp/oracle/snap_pre_post_proc.sh

The script will be then automatically included in any snapshot processing (reporting replica or Tes/Dev Snapshot).