How to migrate a Database using GoldenGate

There are many ways to migrate a database from A server to B server like Datapump, RMAN,etc. Using the combination of datapump and GoldenGate to migrate your database on cross-platform will minimize your down-time to even three minutes.

This method can be used for any size database from MB to TB level. Here is a simple sample to demonstrate this idea.

The prerequisites I assume that the GoldenGate has been configured in the source database and target database. To simulate the OLTP database, in my source database “SOURCE” there is a job will keep inserting a record into the table HOWIE.TEST as shown below.

CREATE PROCEDURE howie.insert_test
IS
BEGIN
   insert into test values(test_seq.nextval,sysdate);
   commit;
END;
/ 

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11
         
		 .....................
           
        21 12/15/2014 19:14:25
        22 12/15/2014 19:15:25
        23 12/15/2014 19:16:25

23 rows selected.

2nd step, you need to start capture process on the source database and stop replicate process on the target database

SOURCE:

GGSCI (11gGG1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        44:56:46      00:00:01

TARGET:

GGSCI (11gGG2) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     REP1        00:00:00      00:00:53

3rd step, export the source database using datapump with flashback_scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     284867
	 
[ggadmin@11gGG1 11.2.0]$ expdp directory=DATA_PUMP_DIR dumpfile=source.dmp logfile=source.log schemas=HOWIE flashback_scn=284867

4th step, transferred the dumpfile to the target server

[ggadmin@11gGG1 11.2.0]$ scp /u01/app/oracle/admin/SOURCE/dpdump/source.dmp 11gGG2:/u01/app/oracle/admin/TARGET/dpdump/

5th step, import the dumpfile into the target database.

[ggadmin@11gGG2 11.2.0]$ impdp directory=DATA_PUMP_DIR dumpfile=source.dmp logfile=source.log schemas=HOWIE

6th step, verify the data in the target database

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11

		 ...............

    	21 12/15/2014 19:14:25
        22 12/15/2014 19:15:25
        23 12/15/2014 19:16:25

23 rows selected.

7th step, start replicate process on the target database using ATCSN

GGSCI (11gGG2) 8> start rep rep1 atcsn 284867

Sending START request to MANAGER ...

8th step, confirm the data has been synced

SQL> SELECT * FROM HOWIE.TEST ORDER BY ID;

        ID MOD_DATE
---------- -------------------
         1 12/13/2014 21:19:17
         2 12/13/2014 21:24:03
         3 12/13/2014 21:31:11
         4 12/13/2014 21:44:33
         5 12/13/2014 21:45:33
         6 12/13/2014 21:46:33
         7 12/13/2014 21:47:33

		 ...............

        60 12/15/2014 19:53:33
        61 12/15/2014 19:54:33
        62 12/15/2014 19:55:33
        63 12/15/2014 19:56:33

63 rows selected.

Action plan Summary

Step	Source	Target
	Source DB (11g)	Target DB (11g)
1	Configure goldengate for capture processes.	Configure goldengate for Replicate processes.
2	Start capture processes.	Don’t start replicate now.
3	start export from the source database (Mark SCN when export started.)
4	Export completed. start SCP of dumpfile to target server.
5	SCP completed.	Start Import on Target database using dumpfiles.
6		Import Finished.
7		Start replicat using atcsn
8		Replicate applied all changes
9	when lag is zero for capture,stop capture	wait till replicate apply all changes , lag should be zero for replicate. After this stop replicate.
10	Redirect db connection point to target db.	Redirect db connection point to target db.