ODA and Dbvisit : Create a SNAPSHOT database?

As you might know, Oracle doesn’t offer any disaster recovery solution for their Oracle Standard Edition databases. Oracle Data Guard ensures high availability, data protection and disaster recovery for Oracle Enterprise Edition database only. Dbvisit is a great disaster recovery solution for Oracle Standard Edition database, and you might be aware that you can now, as well, create snapshot databases on your Oracle Standby database through dbvisit. We have Reporting Replicas option and Test/Dev Snapshots option. This will give you the advantage to take benefit from your standby database for reporting application or for development team to have a refreshed production database to develop and test new application version. The only problem is that one of the requirement of Dbvisit snapshot is to have the Oracle database files stored on LVM file system. On an ODA the database files will be stored either directly on the ASM or on an ACFS file system. Thus this Dbvisit snapshot feature is for the moment not compatible with the ODA. What a pity! But ACFS is a layer up to the ASM having one advantage to be able to create snapshots. Let’s have a try then and have our Dbvisit standby Oracle database be used for an ACFS snapshot database!

My lab environment

For my test I will be using 2 ODAs :

• an ODA X5-2-HA with 2 nodes dbioda02 and dbioda03
• an ODA X8-2M with node named dbi-oda-x8

My primary database is an ACFS database:

My standby database is an ACFS database as well:

And both are synchronized with dbvisit StandbyMP software:

Create the instance for the snapshot database

We first need to create an instance for the future snapshot database. Let’s call it SNAPDEV. On the ODA this will be performed with odacli create-database command and option -io (instance only). Neither database file nor srvctl service will be created at this point.

I have currently only an instance, and my database is started in nomount:

Get and adapt spfile from the standby database

Create a pfile from spfile on the standby database:

Copy the created instance parameter file to the snapshot database admin directory:

Update new instance parameter file for snapshot database:

• Remove all dynamique value DBISNAP.__parameter
• Adapt *.audit_file_dest with snapshot database entry
• Remove *.control_files
• Update OMF parameter db_create_file_dest and db_create_online_log_dest_X with snapshot database ACFS file system. Any new datafile value for snapshot database should go in its file system
• Update *.db_name and *.db_unique_name with snapshot database name
• Update *.dispatchers with snapshot database name
• Update any log_archive_dest_X with snapshot database name if needed

Create spfile from the new pfile.

Create init file in $ORACLE_HOME/dbs folder.

Create missing folders.

Startup the snapshot database in nomount with the spfile.

Copy password file

We will also copy the password file from the standby database to the new snapshot database. Remember that both databases are using same ORACLE_HOME.

Stop dbvisit on standby side

We will stop dbvisit on the standby side and ensure there is no modification done on the database. So no new archive log transaction will be executed on the standby database.

Start the standby database in read only

We will start the standby database in read only to ensure the database is in a coherent status and perform next steps in that mode.

And get the current SCN from the standby database.

We will use this SCN when recovering the snapshot database. We can get the timestamp running following query on the primary database.

Create control file for the snapshot database

Generate a backup control file from the standby database.

Update the generated control file as following:

• CREATE CONTROLFILE line should be : CREATE CONTROLFILE SET DATABASE "SNAPDEV" RESETLOGS FORCE LOGGING ARCHIVELOG
• Online log file should go in the new snapshot database redo log folder. Update /u04/app/oracle/redo/DBISNAP/DBISNAP_STD/onlinelog/ with /u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/
• The path to the datafiles should now be the one from the future snapshot files. We will name the ACFS snapshot SNAPDEV. The path /u02/app/oracle/oradata/DBISNAP_STD/DBISNAP_STD/datafile/ needs then to be updated with /u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/
• Temp file should go in the new snapshot database datafile folder. Update /u02/app/oracle/oradata/DBISNAP_STD/DBISNAP_STD/datafile/ with /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/

The updated control file script is the following one:

Create the ACFS snapshot

We will now create the ACFS snapshot. We do not need to put the database in begin/end backup knowing the database is opened read only. There is no modification done on any data file.

There is no snapshot for the moment.

We will connect as grid user and create the snapshot named SNAPDEV. Here we will create a snapshot as read/write (option -w).

We now have a snapshot named SNAPDEV.

We have our snapshot files.

Recover the snapshot database

We will use the updated control file script content to recover the snapshot database.

We will first create the control file.

The database is now in mount status, and we can recover it. We will use the timestamp of the SCN taken at the beginning on the standby database and go one second beyond.

Start the snapshot database

We can now start the snapshot database in open read/write.

The snapshot database is opened read/write!

We can add a tempfile to the temp tablespace.

Start Dbvisit synchronisation again

We can put the standby database back to the MOUNT mode and synchronise it again with the primary database.

Starting the standby database back in mount mode will make the new archive log application possible again.

Create a few archive logs from the primary side.

Send the new archive logs to the standby side.

Apply the last archive logs on the standby side.

And both primary and standby database are in sync.

Using the snapshot database

A development team has now the ability to use a database in read/write mode, which has just been refreshed from the production database, using the dbvisit standby database.

And of course this table is not existing neither on the standby database, nor on the primary database.

Standby database.

Primary database.

Oracle restart database service

We can create an Oracle restart service for the snapshot database.

Let’s test it stopping and starting the snapshot database.

Easily refresh the snapshot database

Let’s see how easy it is to refresh the snapshot database.

Create some new data on the primary database

Let’s create a table with rows on the primary database.

Archive the current online log.

Synchronise primary and standby databases with dbvisit.

The information can be seen on the standby database after been opened in read/only. Remember that as long as the standby database is opened read only, dbvisit can not apply any new change vector through the archive log files.

Of course this information can not been seen on the current snapshot database. It is an old snapshot which has not been refreshed so far.

Let’s refresh it!

Refresh snapshot database

Stop the snapshot database.

Remove the ACFS Snapshot with grid user.

We can also cleanup any datafile or tempfile present in the SNAPDEV datafile folder : /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile.

The standby database is already in read/only. Take the SCN from the standby database.

Using the primary, let’s get its timestamp.

Take a new ACFS snapshot with grid user on the standby database.

The structure of the database did not change, thus we don’t need to create a new control file backup. We can use the previous one and the same commands as during the initial creation.

Start the snapshot database in NOMOUNT mode.

Remove the control file and restore it.

The snapshot database is now in MOUNT mode. We will recover the database using the timestamp of the SCN taken previously on the standby database and go that time 2 seconds beyond, so until 20-FEB-23 23.31.39, otherwise I was getting RMAN-06555: datafile 1 must be restored from backup created before 20-FEB-2023 23:31:38 error. If needed, we could also, I guess, use the archive log files from the primary database during the recovering process.

We can start the database resetting the redo logs.

And finally add the tempfile to the temp tablespace.

Checking the refreshed snapshot database

Let’s check if we have our REFRESHED table on the snapshot database.

Yes WE HAVE!!!

Do not forget to put the standby database back in the MOUNT status, so the standby can be kept in sync with the primary database through Dbvisit again.

We will restart the snapshot database with srvctl as well.

Summary

Of course creating a snapshot on the standby database is not as user friendly and easy as doing it with Dbvisit Central Console, but this solution is quite a good alternative to take benefit of the dbvisit standby database on the ODA. Remember, Dbvisit snapshot solution is today only possible with LVM, thus not compatible with the ODA. With ACFS snapshot we have a good solution to also use snapshot on Dbvisit standby database.