Dbvisit Multiplatform like previous versions has the option to create reporting replicas and Test/Dev snapshots. Let’s revisit the basics of snapshots and what are the benefits and how does it fit into Dbvisit Multiplatform.

Snapshots is actually a common technology concept that is used and implemented by many vendors of various different products. Some of these come with a large price tag, but they do provide a lot of advanced features of which many are focussed on the enterprise. One of Dbvisit's goals is to take complex things and make it simple, such as the creation and management of an Oracle Standby Database environment. When we started looking at Snapshots - this is no different. We looked at capability available in the Linux Operating System (OS) - with a specific focus on Oracle Linux - but note that these options are available on almost all Linux distributions - especially the ones that are supported by the Oracle Database which include RHEL and SLES.

One of the options available to you on Linux is the use of Logical Volumes (LV) - or Logical Volume Manager (LVM) - for more detail on this technology please see the documentation of the Linux distribution you are using - example - https://docs.oracle.com/cd/E37670_01/E41138/html/ch17s03.html

Now there are high level two new options introduced in Dbvisit Standby Snapshots. These are:

• Reporting Replicas

• Test/Dev Snapshots

Option 1:  Reporting Replicas

Reporting Replicas can be described as follow:

• This option is ideal for companies that are using Oracle Standard Edition (SE,SE1, SE2) where they would like to have a "database that is read-only/read-write but also being kept up to date" - we are not talking logical real-time replication here, but using an actual standby database to create a "logical container" consisting out of 2 or more (up to 4) read-only snapshots of a standby database at a time interval.  In short, a snapshot is created and it is opened read-only, then the listener on the system is updated with a new service that points to this latest read-only snapshot.  Then at a time interval, the next snapshot is created, opened read-only and the listener service is updated to point to this latest one. This process continues and the oldest snapshot is removed once the limit set (2,3 or 4) is reached.  The effect that this creates is a logical database that is perceived to be updated.  This is due to the standby (or cascading standby) database being updated on a regular basis. This is perfect for companies that need to run reports or data extractions on a regular basis.  

The diagram below provides and overview of how the Snapshot Group option work. It is described of the Cascading Standby database, but can also be configured from the 1st Standby database.

Using a Cascading Standby Database

• This option is ideal when you have a strict RPO/RTO where you do not want a big difference between the primary and standby database.  The best is to then off-load the snapshot option onto a cascading standby database.

• This can also be a good option if you cannot meet the requirements on the standby server for the snapshot option - for example you do not have the Database on a dedicated Logical Volume inside a Volume Group with free space for the snapshots.  You can then create a cascading standby database that meets these requirements and then use the snapshot option there.

Using the Standby Database

• When using this option keep in mind the time difference between the primary and the standby database. 

Option 2:  Test/Dev Snapshots

Test/Dev Snapshots can be described as follow:

• The second option is to allow for read-only as well as read-write snapshots to be created of the database. This is to cater for a number of use-cases including the following:

  1. Allowing reporting or data extraction from a snapshot (read-only) of the database at a particular point in time - the snapshot can be recreated on a regular basis if required as the process only takes a minute or two to complete.  

  2. Application Testing - when you need to test the upgrade of an application or new changes, you can create a read-write snapshot of the standby database, which is a copy of the primary and the ideal place to test an upgrade. Once done you can remove the snapshot, run the test again or when ready continue to doing the actual upgrade - remember to follow the recommended approach of testing and following change control.

  3. Development or Test environments - many sites do not really use their Standby Database environment.  Now you already have licensed this environment for Oracle (Remember a standby database site must be fully licensed similar to your primary - if not sure please discuss with your Oracle Partner or Account Manager).  Now what you can do is get more value of your standby database environment investment by not having the environment idle, but using it for Development and Testing.  You can create a full blown test or development environments on this system, but why not make use of Snapshots. Test and Development environments tend to be short term, meaning they are refreshed on a regular basis - a process that can take a lot of time.  Using snapshots of the standby database is another way to get more value of this.  As Copy-on-write technology is used (see LVM snapshot documentation for more in-depth knowledge on this) you will not require large amounts of disk space - now this depends on how long-lived the snapshots will be if they are short term, you might not need much storage, but if you use the longer term and your snapshots are used heavily, you might need more disk space.  

  4. Quick and easy DR testing.  When testing your DR site, many will activate the standby database, run the application test and then recreate or restore the standby database.  This can be a complex long process, why not make use of a snapshot - remember this is a snapshot of your standby database at a given point in time, meaning you can activate the snapshot, run your tests and then remove it.  In effect, you did test the standby database validity as a DR site. Thi sis a much faster approach and can save time.

Licensing

Dbvisit Standby Reporting Replicas and Test/DEV Snapshots do not require additional licenses from Version 10.0 onwards. This option is available only on Linux platforms and can be used with the regular licenses provided as part of support agreements.

Pre-Requisites

Before starting with this new option, it is important to understand what the requirements are.  

As mentioned Dbvisit is aiming to make technology available in Linux to our users by implementing an easy to use interface to allow the use of these features.  Now in this case the first version of the Snapshot functionality is based on Linux LVM (Logical Volume Manager) which allows for snapshots to be created of Logical Volumes (LV).  Now we will not go into the details of LVM as this can get really complex fast, but having an understanding of what LVM is - which most Linux Administrators do, can be useful.  We will however aim to provide a few high level examples here to get you started.

The key requirements for using this new option are:

1. The Operating System (OS) must be Linux:

   • Supported distributions include Oracle Linux, RedHad Linux, and SLES - we highly recommend using Oracle Linux or Red Hat version 6 or 7 with the latest updates applied.

   • Please make sure the OS used is also a valid supported OS for the Oracle Database software - running unsupported configurations is not supported

2. The Standby Database (which can be a cascading standby database) must be located on a single Logical Volume (LVM)

   • Example:

   • Filesystem layout - in the example below we see that filesystems /u01 and /u02 are making use of Logical Volumes.  In the example we will discuss here, the Oracle Database software or ORACLE_HOME as most DBAs will know it as, is installed and configured in /u01.  The database files (including recovery area and controlfiles) are located on /u02.  This is important as when we are looking at snapshots, we want to focus on /u02 filesystem.  It is a Logical Volume (LV) contains only the Standby Database - created using Dbvisit Standby.

oracle@dbvrosh02 ~ : df -h
Filesystem                 Size  Used Avail Use% Mounted on
devtmpfs                   2.3G     0  2.3G   0% /dev
tmpfs                      2.3G     0  2.3G   0% /dev/shm
tmpfs                      2.3G  8.5M  2.3G   1% /run
tmpfs                      2.3G     0  2.3G   0% /sys/fs/cgroup
/dev/sda3                  6.3G  4.2G  1.9G  70% /
tmpfs                      2.0G  4.0K  2.0G   1% /tmp
/dev/sda1                  488M  279M  175M  62% /boot
/dev/mapper/oradatavg-u02   21G  1.5G   19G   8% /u02
/dev/mapper/orahomevg-u01   16G  5.2G  9.7G  35% /u01
tmpfs                      469M     0  469M   0% /run/user/501

• Later in this section, we will provide a quick overview of Logical Volumes - how they can be created and used, but for now, the key is to take into account that to use the Dbvisit Standby Snapshot options you will have to use a single logical volume where the standby database is located.

1. You must have sufficient IO capability in the Volume Group underlying storage (Physical Disks - Using SSD based storage is always recommended for Database Storage)

   • Databases will perform IO and if you create snapshots there will also be IO requirements for these.  Make sure you test your configuration and have sufficient IO capacity - especially if you are looking at using snapshots for Development or Testing environments.

2. Make sure your Volume Group (VG) in which the Logical Volume (LV) is located has sufficient disk space
   

   • When creating a Logical Volume (LV) it is created in the Volume Group which you can kind of see as a storage pool, made up of a number of disks - now please note this is just a simplification of it.  

   • Make sure that the Volume Group have sufficient free space, this is important.  Snapshots will initially not take much space, but if left long term and if you have a busy origin (source of the snapshot) then more disk space will be required.  

   • When you create snapshots based on the specific Logical Volume, a small portion of storage will be used for the snapshot.  Now the beauty of snapshots is that it does not require a lot of disk space, it is however depending on how much the database from which the snapshot was created is changing as well as if you are using a read/write snapshot - which means the snapshot will use more storage for new changes. 

   • When you create the snapshot you have to specify a size for the snapshot, this should be set to an appropriate value to allow the snapshot to keep enough data to stay valid (consistent).  Example, if you do not expect more than 30% changes to your database during the life time of the snapshot, then setting the snapshot size to 30% of the standby database (source/origin) size is a good start.  

   • IMPORTANT:  The ideal is to use snapshots for short term - and not days or weeks. Again the longer they are used - more storage might be required.

3. Memory 

   • You may ask, why is the memory required, the answer is easy - for every new snapshot, you are going to mount and start an Oracle Database instance, and this will require memory.  When Dbvisit Standby creates the snapshot, you will be asked to supply the memory to be used for the SGA.  Now in most cases, you might want to keep this small, but depending on what you are planning to do with the snapshot, whether it is for reporting or development or testing, you might want to allocate a lot more memory to it.  So make sure you have sufficient memory on the system you are planning to use the snapshot option.  You do not want to run out of memory and start using SWAP space as this is really bad for database environments and will slow the whole system down.  You should aim to always have a little bit of free memory on the system, never over allocate.

4. The Dbvisit Standby executable - dbvsnap - must be able to execute the LVM commands as the root user.  

   • There are a number of ways you can do this.  Normally the Dbvisit Standby software is run as the "oracle" software owner (usually the "oracle" UNIX user).  And many sites will have provided this user with the permission to run commands as the super user via "sudo" rights.  Now to allow the Dbvisit Standby Snapshot option, you will be required to allow this "oracle" UNIX account running Dbvisit Standby to execute the following commands as the root user:  lvcreate, lvremove, lvs and vgs

   • For more detail on how to do this, please review the "sudo" option provided by Linux where you can add the "oracle" user to the "sudoers" file with the required permission to execute these commands.

5. Sufficient disk space under the DBVISIT_BASE folder

   1. When creating the snapshots there will be metadata written to the DBVISIT_BASE/standby/meta folder for each snapshot.

Recommendations

There are a number of ways you can go about using this new Dbvisit Standby Snapshot option. The first is that you can run it on your current standby database environment - if you meet the above requirements, most important having the standby database fully located on a Logical Volume (LV).  

The second option is that you can also create a new configuration, using the Dbvisit Standby Cascading option on a second Standby Database Server where you have created a specific area for the database using a Logical Volume.

Important Warnings & Best Practice

Following these rules & suggestions will ensure you have a trouble-free experience using the Dbvisit Standby Snapshot Utility:

• Do not use the APPLY_DELAY_LAG setting/feature when using Snapshot Groups - this is not supported.

• Do not use Dbvisit Standby Snapshots on RAC-to-RAC environments - this is also not supported. You are however able to use Dbvisit Snapshots on RAC-to-single environments.

• Ensure the lvs, mount & lsof functions are in your environment PATH.

• After pausing or stopping a Snapshot Group (i.e. stopping the snapshot creation daemon), make sure to check the status of your standby database. If you stop the daemon while it is in the middle of a snapshot creation process, it does not have the opportunity to restore the standby database back to recovery mode, and thus all future attempts to apply logs will fail. If you find your standby database is in read-only mode, you will need to restart it. You can do this from the Central Console by going to the Manage Databases page, clicking Database Actions, selecting the standby host, and clicking Restart.

• Use common sense when preparing to run major actions on your Dbvisit DDC configuration. For example, if you are planning to perform a Graceful Switchover, recreate a standby control file, or Synchronize after a major log gap, you should pause or stop the Snapshot Group daemon so that it does not interfere. Keep in mind also that major updates to the standby database will cause significant increases to the sizes of your existing Snapshots, potentially putting your system at risk of running out of resources. If in doubt, it is always best to stop the Snapshot daemon and delete all Snapshots before commencing major actions. Remember: you can always create new Snapshots immediately after you have finished.

• Try to keep the Snapshot Creation Interval as long as is possible while still meeting your requirements. We recommend at least 10 minutes (600 seconds), however you should try to increase this if possible.

• Ensure you have space on your system for at least "+1" Snapshot to the number you have specified. For example, if you are running a Snapshot Group with 2 Snapshots each 3Gb in size (so 6Gb in total), make sure you have at least another 3Gbs available. This is because the Utility waits new Snapshots are created successfully before deleting old ones. So, even though you have specified 2 Snapshots in your Group, you will occasionally see a third Snapshot appear (and take up some space), while the Utility creates the newest and then removes the oldest Snapshot.

• Keep an eye on your system resources. Snapshot creation can be resource-intensive, especially if you have more than a couple of Snapshots in play at any one time. You should also occasionally clear the /trace and /log folders to ensure these are not filled up unnecessarily.

The next step that will be required before you can use the new Dbvisit Standby Snapshot option is to make sure the user which in most cases will be the "oracle" UNIX account have the appropriate entries in the /etc/sudoers file.

There are a number of options available to you, you can add the "oracle" user to have full permission, but this will give it full permission, which is not something you might want to do, or you can give it only the specific permissions you want.

Below are the options you can follow:

Option 1:  Give "oracle" UNIX account full admin permission

/etc/sudoers

...
oracle ALL=(ALL) NOPASSWD:ALL

Option 2:  Give the "oracle" UNIX account only permission on certain commands:

/etc/sudoers

...
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

Note that the snapshots will be created, then mounted, and when not required anymore will be unmounted then removed, which is why the above options are required.

Configuration

It is important to mention early on that when you are looking at using this new Dbvisit Standby Snapshot option, you should use the Central Console (GUI).

Even though most of the options are available via the Command Line Interface (CLI) utility - dbvsnap, using the Central Console is strongly recommended as it is easy to use and get started.  Using the CLI option (dbvsnap) is only recommended for experienced users.

Before you can create any Snapshots or Snapshot Groups there are a few important things to take into account:

1. You must have a Primary => Standby configuration

2. You must have the standby database created and it should be located on a Logical Volume (LV)

   1. You can also create a Cascading Standby database on a new environment if your existing environment does not cater for Logical Volumes or meet the requirements.  Using Cascading Standby databases does provide you with a number of extra flexible options and protection and would be a good approach to using the new Dbvisit Standby Snapshots on.

Once these requirements are met, we recommend using the Central Console (GUI) to configure and use the new Snapshot option.

Reporting Replicas

From the multiplatform control center, choose the configuration and you can see the options for Reporting Replicas ( 1 ) and Snapshots( 2 )

1. The Logic Volume that will be used for the Snapshot.

2. The Mount Point that will be used for the Snapshot. This is always the same as the mount point of the standby DB, for simplicity's sake.

3. The Volume Group that the Snapshot will belong to. This is also the same as the standby DB.

4. The Oracle Service Name is chosen by the user. This is the name that will execute the dbvsnap utility.

5. If you need your snapshot groups to be in read-only or read-write mode (The database is activated)

6. The desired number of Snapshots to create/maintain. Allowed values are currently 1 - 4. This is the number of Snapshots that will be kept and switched between every Creation Interval setting (below).

7. Snapshot Creation Interval - in minutes, how frequently to create new Snapshots (and potentially delete old ones). We recommend this is no less than 10 minutes.

8. A prefix that will be applied to the names of all created Snapshots. This is provided so that users can easily tell which Snapshots on their system were created by this utility. This is limited to 5 characters, alphanumeric only.

9. An optional field you can use to specify custom parameters for the mount operation. For advanced users.

10. The maximum allowed size for a Snapshot to reach. A default value is provided, but it is up to the user to ensure this is set appropriately to their intended use for the Snapshot. Make sure this is not too small - you wouldn't want your Snapshots to run out of space!

11. Advanced option to provide custom database parameter. The default value is provided for sga_target, should only be changed by expert users. The other database parameters can be added/modified. Below figure shows how.

After the parameters are set, Click the start Snapshot generation button to start the reporting replicas generation

The new snapshot is created and name is DBV001 and size occupied is 44.21% out of 1500M and the database status of the snapshot is open read-write.

( 1 ) When a snapshot is created to maintain the database in a consistent state the standby is opened in readonly mode

( 2 ) Snapshot options to Pause Snapshot generation. No new Snapshots will be created, however, all existing Snapshots will remain mounted. Stop Snapshot generation, shut down database instances but does not umount the snapshots. Stop Snapshot generation, shut down database instances but does not umount the snapshots.

( 3 ) Leader indicator, i.e. which Snapshot will all new connections to the DB connect to.

( 4 ) SCN number at the time of the creation.

( 5 ) Snapshot database status.

( 6 ) Creation time and how long the snapshots are running.

( 7 ) Number of active connections connected to snapshot

The server in which snapshots are running has the following

Test/Dev Snapshots

The test/dev snapshots are single snapshots that can be used for reporting/testing purposes. These snapshots can also be used for DR testing purposes. The single snapshots can be retained based on the space available in the logical volume as the space occupied by the snapshots can increase based on the test/development happening on the database and also compensate for the changes happening in the DR database due to archivelogs being applied on the standby.

The settings for a Test/Dev Snapshot are largely the same as those used for Reporting Replicas (discussed above).