When No Locks Are A Good Thing

One of the most powerful features of the Dbvisit Replicate 2.7 release is the No Lock feature.

This feature will allow you to truly take advantage of zero down time migrations as well as not taking any downtime at all during setup. Before we dive into the No Lock feature let’s review a bit about Dbvisit Replicate’s Setup Wizard and how Dbvisit Replicate picks the initial starting point to capture data. Step 2 of the Setup Wizard asks “ Lock and copy the data initially one-by-one or at a single SCN?”

You are then prompted with some options.

One-By-One

The first option is one-by-one. This is by far the simplest option. This method will have Replicate get a unique SCN for each table. (SCN is the Oracle System Change Number. This number is basically Oracle’s ‘clock’.) These SCNs are going to be the point in time that the MINE process will start reading from. So getting the correct SCN to start at is crucial. Because each table that you are replicating may have different SCNs per table it can be tricky to do the initial data load, especially if the tables have foreign key constraints.

For this reason many people prefer to use the default and easier to use, single SCN option.

Single SCN

The single SCN option is when Dbvisit Replicate (during the running of the –all.sh script) will lock every table that you are replicating so that it can get a consistent SCN across all tables. This share lock (remember that a share lock won’t prevent people from reading the data) will block other transactions. Although the actual lock will only be held for a brief time to get the same consistent SCN across all tables, it may have to lock those tables for quite some time as it waits on other transactions to finish. This is especially true on a busy system.

For that reason, many customers have often taken a brief application outage when running the –all.sh script to get a ‘clean’ SCN without having to wait for transactions to finish. By having MINE start at that one SCN number the initial data load will be much simpler and tools such as data pump, RMAN, etc can be used with the ‘as of SCN’ option.

Resetlogs

RESETLOGS will take the SCN from the database after it has recovered. This would be used if having you are using a standby database or you used rman to create a backup of your database as your target. (Which is a great way to instantiate your target database, which we will discuss in a future blog post). The DDL-only option will do just that and provide you the DDL for those source tables.

No Lock

The new option in Dbvisit Replicate 2.7 is the No Lock feature. As the name implies it is all about NOT locking your tables. When you are setting this option, there are a few prerequisites. The first is that prior to running the -all.sh script that sets up replication you, would need to run the aptly named –pre-all.sh script. This script does two things; it sets the database in supplemental logging mode and turns on supplemental logging on for the tables that you are interested in replicating. We do that before running the –all.sh script as Oracle needs to lock those tables for a brief second to turn on the supplemental logging. We recommend that you run this at a ‘quieter’ time so as to not affect your application. Turning on supplemental logging is required; indeed it is one of the cornerstones of all replication products that use the Oracle redo logs as a source. When you are ready to run the –all.sh script, I typically recommend that you run it when no long running transactions are present. While you can do so it will make your life a bit easier if you wait until that long transaction has been committed. When you run the –all.sh script there is a command in there, that will find the oldest open transaction and it will start the MINE process from that point.

The best part about the No Lock feature is that you don’t have to do anything special, the logic is in the code. After you run the –all.sh script you proceed with the Nextsteps.txt just like you normally would. You start the MINE and APPLY just like normal. The No Lock feature is handled ‘under the covers’ and the user will not have to worry about setting any additional parameters. This takes all the guess work out determining if there any in-flight transaction that need special attention. Let’s take a look at an example. Sample of –all.sh output:

I have highlighted a few SCNs of interest. The current SCN in the example is 23538990. Note however that there is an open transaction from SCN 23533272. Because we want to ensure that we capture that transaction as well, the MINE process will make 23533272 the starting point for capturing data. What this means is that open transactions will be accounted for and there is no longer a need to either pause the application or take an outage.

We are very excited that the No Lock feature is now a part of our standard setup and we know that customers have requested this feature and look forward to using it! Let me know what you think.