How can you test your OCI database before going live? With Logical Online migration it is possible using Flashback Database, restore or cloning. Let’s explore the options.

Flashback Database

Using Flashback Database is the easiest option and it is supported on all target platforms except Autonomous Database (shared and dedicated). In addition, your target database must be Enterprise Edition.

Does Oracle GoldenGate support Flashback Database? You can find the answer in the MOS note Does Goldengate Support Oracle RDBMS Flashback Features? (Doc ID 966212.1):

In a situation where there are only Replicats on a system, then FLASHBACK DATABASE… is fully supported if the Replicats are all using a checkpoint table and the trail files are available that go as far back as the flashback.

When you do Logical Online migrations you only have replicat process in your target database. In addition, checkpoint tables are enabled by default. All good!

Flashback Database – How To

1. Stop replicat process by logging on to the GoldenGate hub and navigate to the Target Administration Server:
2. Set a guaranteed restore point in the target PDB (named tgtpdb):

alter session set container=tgtpdb;
create restore point grp4test guarantee flashback database;

3. Do your tests.
4. Revert the changes by issuing a FLASHBACK PLUGGABLE DATABASE command:

alter session set container=cdb$root;
alter pluggable database tgtpdb close immediate;
flashback pluggable database tgtpdb to restore point grp4test;
alter pluggable database tgtpdb open resetlogs;

5. Restart replicat process:

The above procedure uses flashback of the pluggable database. This feature was introduced in Oracle Database 12.2. If your target database is 12.1 or older, you have to flashback the entire CDB. If your target database is a non-CDB database, you have to flashback back the entire database.

Restore

Backup/restore is also an option. In OCI it is easy to backup and restore, however, it does take longer than using Flashback Database. But the good thing is that you get to test your OCI backup and recovery strategy.

For Autonomous Database you can use the automatic backups which are enabled by default. In the other Database Cloud Offerings, you must enable automatic backup yourself.

• Autonomous Database
  • Manual backup
  • Restore
• Exadata DB System
  • Manual backup
  • Restore
• Bare Metal and VM DB System
  • Manual backup
  • Restore

Restore – How To

1. Stop replicat process (see above)
2. Create backup (or rely on automatic backup)
3. Do your tests
4. Restore backup
5. Restart replicat process (see above)

Clone

Cloning the database or the entire DB System is also an option. A benefit is that the GoldenGate replication can continue while you are doing the tests. When you use Flashback Database and restore the replication must be stopped. Trail files will accumulate on disk and at one point the target database will be very busy catching up with the lag. For very huge and active systems it might be desirable to work on a clone. However, cloning to a new system mean that you are no longer testing in that specific database that will be your future production database. Also, cloning involves creating new DB Systems which has a cost.

• Autonomous Database
  • Cloning
• Exadata DB System
  • Cloning
• VM DB System
  • Cloning

Clone – How To

1. Clone database or DB System
2. Do your tests
3. Discard database or DB System

Conclusion

Before going live in your new OCI database, you should test it. With Logical Online method it is easy, and you can use well-known techniques. If you combine it with the recommendations from our webinar Performance Stability, Tips and Tricks and Underscores, you are well underway towards a successful migration.

Other Blog Posts in This Series

• Introduction
• Install And Configure ZDM
• Physical Online Migration
• Physical Online Migration to DBCS
• Physical Online Migration to ExaCS
• Physical Online Migration and Testing
• Physical Online Migration of Very Large Databases
• Logical Online Migration
• Logical Online Migration to DBCS
• Logical Offline Migration to Autonomous Database
• Logical Online Migration and Testing (this post)
• Logical Online Migration of Very Large Databases
• Logical Online and Sequences
• Logical Migration and Statistics
• Logical Migration and the Final Touches
• Create GoldenGate Hub
• Monitor GoldenGate Replication
• The Pro Tips