Blog | Jul 3, 2013

Database Migration and Testing Using Data Guard and Restore Points

Working on a database migration project requires using the most recent database backup to create a new one on the target environment.  This is required to allow your end users to perform any required testing, just like in any project.  Typically, this migration process requires two to three iterations to allow for the appropriate amount of testing to be done before the final production cutover, and every iteration requires recreating a database on the new target environment.

Another way to handle a database migration is to create a standby database on the target environment and sync it with the Primary production database. This process requires Database Administrators (DBAs) to take the backup of the standby database to allow the end users to perform the required testing, eventually restoring the standby database to the original state and syncing it with the Primary one.

Although the above two processes are proven and common approaches to perform a database migration, they can also be time consuming and cumbersome. Some data migration projects involve moving multiple databases of various sizes with each requiring multiple rounds of testing before the final production cutover.  Given your time and resource constraints, neither of these approaches may be ideal to meet your needs, so how can you still move forward with your important project and get it done in the time you have?

Well, if you are on versions 10g or 11g of the Oracle database there is a guaranteed restore points feature on the physical standby/target database environment. With the help of this feature, you are able to open a physical standby database for the read-write operations where your end users can run the required test cases, processes, jobs etc. By leveraging this functionality you will be able to save time as it should take you only about 30 minutes to create restore points and open the standby database for testing. This will improve your timeline to completion and provide quick access to testing which will lead to more confidence about your new environment quickly.

In 11g, there is a new feature called "snapshot standby database" which can be used to perform similar database migrations and also for performing required testing of concurrent jobs, reports, etc. 

Along with the above information, Data Guard with flashback technology can provide you with functionality that can also be leveraged to help maintain high availability of the Production environment.  Below are some key points:

• If a table/schema are dropped or removed from primary, then those can be recovered using flashback ON option in a standby database mode. This helps avoid/reduce downtime of the Primary database.
• There is no need to configure Delay on redo apply when flashback is enabled in the standby database.
• Standby with flashback On feature will help recover from a possible physical/logical corruption or user errors in the primary database.

At TriCore we have been providing industry leading managed services to customers for many years and across many different industries.  By listening to our customers, we continue to advance our technologies and streamline our approaches to providing the best services. 

If you are at a point where you have a major database migration coming up, be sure to research the information above and see how that can help you with your important project!