Tuesday, April 17, 2012

Zero down-time Migration using Goldengate of Oracle database from 8i to 11g.


Few months back , I did migration of  Oracle database from 8i to 11gR1 on cross-platform using GoldenGate V10 .  3 minute downtime was required.
I would like to share a Overview of whole plan , which could be helpful for reference.

Fundamentally this method works in most of the migration using Goldengate .
Data Loading from source to target has to be decided.

It could be either export/import, ETL tool, Transportable Tablespace,Backup.

Note: challenges we faced was to capture & replicate CLOB data.

We moved  4 TB Oracle 8i database from NY to North Carolina. It was geographically separated but in same Timezone.

Some facts.
  • Data moved across databases using Export / Import utility. 
  • FTP used to move dumpfiles from Source to target destination server. (NFS will be better)
  • Goldengate was configure to replicate schema to schema. (Tables which as has CLOB data were excluded). 
  • Index were created on Target database for big tables or table subject to heavy transactions to boost replicate on target db.( Index were dropped after migration ).
  • Big Tables or Heavy transactions Tables can be split into few capture processes using @RANGE parameter.
  • Triggers were disable for performance reason.  its not mandatory to disable triggers.  
  • As source database was Oracle 8i , Trandata need to add for every table on source Database.  
  • Handlecolision parameter must be used on replicate side. 
  • 3 Minutes downtime was Achieved. 
  • There should be enough space to accommodate remote trailfiles on Target server.
Below is step-by-step Migration from 8i to 11g.

New york (8i )
North Carolina (11g)
Sunday 8:00 Configure goldengate for capture and datapump processes.Configure goldengate for Replicate processes.Test this configureation in Dev/Qa.
Monday 8:00 Start capture and datapump processes. Dont start replicate now. Trailfiles from capture are transferred by datapump. This will be accumulate on target server.
Monday 9:00 start export from 8i database (Mark SCN when export started.)lag for replicate will increase but dont start replicate yet.
Tuesday 22:00Export completed. start FTP of dumpfile to target server.
Wednesday 14:00 FTP completed.Start Import on Target database using dumpfiles.
Friday 20:00 Import Finished. Index created & trigger were disabled after Import finished.
Friday 22:00Start replicat using atscn / Use HANDLECOLLITION it will apply all changes from trailfiles which was accumulated on target server til now.  Lag will decrease slowly for replicate.
Sunday 10:00 Replicate applied all changes from trailfiles. Monitor Lag for capture and datapump process.
sunday 11:00
(cut-over time)
when there are least transactions happens or Lock db for further login and wait till capture shows zero lag  , after this stop capture. when lag is zero for datapump ,stop datapump wait till replicate apply all changes from last trailfile  , lag should be zero for replicate. After this stop replicate.  This whole downtime depends on volume of transaction and latency between source and target in term of Goldengate. Drop indexes & Enable all triggers.
Sunday 11:03 (cut-over time)Redirect db connection point to target db. Redirect db connection point to target db. Migration completed.

We started capture process to capture all transaction on source db, till export and import finish.

HANDLECOLLISION was specially used on replicate side , so we can avoid duplicate transactions.

Cut-over is explained.

Source side.
Monitor lag of capture process on source side.
When there are very low transactions happen or we could also use approach to lock db for further loggin. Now when all capture processes shows zero lag. stop the all capture process. Once this is complete wait till datapump reach to zero lag, Now stop datapump too.
Idea behind this is to make sure that No transaction occurs after we stop capture.
As of now All transactions are captured , transmitted to target side on remote trailfile.

Target side.

Wait till replicate apply all changes till end of trailfile. There should be zero lag for all replicate processes.
Once this done , stop replicate.

Now change connection string to target database. which will redirect connection to new 11g database.

Drop indexes which was created to boost replicate processes. enable all triggers.

----------2nd approach-----------------

  • configure GG  extract/pump/replicat
  • start extract & pump (Make sure remote trailfile is generated), You can also take SCN based backup by RMAN. 
  • Take export of Whole Source database ,based on SCN. 
  • FTP dumpfiles to Target database server.
  • Import into Target database.
  • start replicat ,AFTERCSN  
  • Let replicat catchup.  
  • once extract/pump/replicat shows zero lag/checkpoint ,stop further login into source DB. 
  • switch tns to Target db. 

This whole cut-over time could be estimate and strongly depend on volume of transaction & latency between capture and replicate. Measure the redo generation & latency at peak-time.

Happy Migration.


  1. This is a great note. I am so happy to come across your introduciton. We are in process to come up a migration plan for a 10g db (on aix)to 11gr2 on linux.Our tables have clobs also.
    Can I ask more questions here:
    1).you said you did not include the tables with clobs in the extract process. What will happen to those tables during migration? if any changes to those tables, how do you capture those?
    2). What is the guidelines for which indexes should be created? How do you come up a list of those indexes?
    3). Triggers, do you disable all the triggers? If not disable, what will happen?
    Thank you in advance. Look forward to hearing from you soon.

  2. Handling Tables Without Primary Keys (PK) or Unique Indexes (UI). [ID 1379932.1]
    How to Handle Tables Without Primary Keys or Unique Indexes With Oracle GoldenGate [ID 1271578.1]
    Oracle GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) [ID 1298562.1]

    BR, Igor