It's all about migrating data from one environment to another. This blog is about one way replication, the simplest of its kind. Data is replicated from the source to the target database in an uni-directional format. All sorts of modifications are done only at the source and the same would be replicated to the target system. Only Migration is covered below and will add fallback options in forth coming blogs.
Obtaining sufficient downtime is always a challenge and Oracle GoldenGate (OGG) comes here for our rescue. The real challenge in migration is when the end users are allowed to perform modifications to the system until the migration cutover. The below set of steps explains the Oracle GoldenGate execution flow for Zero Downtime Migration in multiple steps.
As a first step let's create a source environment which has got continuous data modifications,
SQL> REM CREATING SOURCE AND TARGET TABLE
SQL>
SQL> create table GG_SRC as select * from dba_objects;
Table created.
Elapsed: 00:00:00.54
SQL> ALTER TABLE GG_SRC add CONSTRAINT PK_GG_SRC PRIMARY KEY (object_id);
Table altered.
Elapsed: 00:00:00.36
SQL>
SQL>
SQL> create table GG_TRGT as select * from dba_objects where 1 = 2;
Table created.
Elapsed: 00:00:00.03
SQL>
SQL> ALTER TABLE GG_TRGT add CONSTRAINT PK_GG_TRGT PRIMARY KEY (object_id);
Table altered.
Elapsed: 00:00:00.01
SQL>
SQL> exec dbms_stats.gather_table_stats('HARI','GG_SRC');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
-------------
Creating procedures to perform data modifications, later these are scheduled as an on-going process
SQL - INSERT - Stored Procedure to Insert 20 records scheduled to execute every minute
create or replace
PROCEDURE sp_ins_gg AS
BEGIN
INSERT
INTO GG_SRC(object_id, object_name,subobject_name,data_object_id,object_type,created) WITH temp_max AS
(SELECT MAX(object_id) AS
max_id
FROM GG_SRC)
SELECT max_id + rownum AS
object_id,
'ORACLE' ||(max_id + rownum)||chr(rownum+60) AS object_name,
'ORACLE' ||(max_id + rownum)||chr(rownum+60) AS subobject_name,
rownum AS data_object_id,
'ORACLE' ||(max_id + rownum) AS object_type,
sysdate AS created
FROM temp_max CONNECT BY LEVEL < 21;
COMMIT;
END sp_ins_gg;
/
Scheduling 20 INSERT's Every Minute
begin
-- Every minute - Every day
dbms_scheduler.create_schedule(
schedule_name => 'MIN_BY_MIN',
start_date => trunc(sysdate),
repeat_interval => 'freq=MINUTELY;interval=1',
comments => 'Execute: ALL DAY EVERY MINUTE');
end;
/
begin
-- Invoke the Procedure
dbms_scheduler.create_program
(program_name=> 'GG_INSERT',
program_type=> 'STORED_PROCEDURE',
program_action=> 'sp_ins_gg',
enabled=>true,
comments=>'Procedure to insert source data'
);
end;
/
begin
-- Create the Job
dbms_scheduler.create_job
(job_name => 'JOB_GG_INSERT',
program_name=> 'GG_INSERT',
schedule_name=>'MIN_BY_MIN',
enabled=>true,
auto_drop=>false,
comments=>'Job to insert source data every minute');
end;
/
SQL - UPDATE - Stored Procedure to update two records is scheduled to execute every 10 seconds
create or replace
PROCEDURE sp_upd_gg AS
BEGIN
UPDATE GG_SRC
SET subobject_name = 'ORACLE'
WHERE subobject_name IS NULL
AND rownum < 2;
UPDATE GG_SRC
SET object_type = 'ORACLE'
WHERE owner IS NULL
AND rownum < 2;
COMMIT;
END sp_upd_gg;
/
Scheduling 2 UPDATEs Every 10 Seconds
begin
-- Every 10 seconds - Every day
dbms_scheduler.create_schedule(
schedule_name => 'SEC_10',
start_date => trunc(sysdate),
repeat_interval => 'FREQ=SECONDLY; INTERVAL=10',
comments => 'Execute: ALL DAY EVERY MINUTE');
end;
/
begin
-- Invoke the Procedure
dbms_scheduler.create_program
(program_name=> 'GG_UPDATE',
program_type=> 'STORED_PROCEDURE',
program_action=> 'sp_upd_gg',
enabled=>true,
comments=>'Procedure to insert source data'
);
end;
/
begin
-- Create the Job
dbms_scheduler.create_job
(job_name => 'JOB_GG_UPDATE',
program_name=> 'GG_UPDATE',
schedule_name=>'SEC_10',
enabled=>true,
auto_drop=>false,
comments=>'Job to update source data every 10 seconds');
end;
/
SQL - DELETE - Stored Procedure to delete two records is scheduled to execute every 15 seconds
create or replace
PROCEDURE sp_del_gg AS
BEGIN
DELETE FROM GG_SRC
WHERE object_id =
(SELECT object_id
FROM GG_SRC
WHERE subobject_name != 'ORACLE'
AND rownum < 2);
DELETE FROM GG_SRC
WHERE object_id =
(SELECT object_id
FROM GG_SRC
WHERE object_type != 'TABLE'
AND rownum < 2);
COMMIT;
END sp_del_gg;
/
Scheduling 2 DELETEs Every 15 Seconds
begin
-- Every 15 seconds - Every day
dbms_scheduler.create_schedule(
schedule_name => 'SEC_15',
start_date => trunc(sysdate),
repeat_interval => 'FREQ=SECONDLY; INTERVAL=15',
comments => 'Execute: ALL DAY EVERY MINUTE');
end;
/
begin
-- Invoke the Procedure
dbms_scheduler.create_program
(program_name=> 'GG_DELETE',
program_type=> 'STORED_PROCEDURE',
program_action=> 'sp_del_gg',
enabled=>true,
comments=>'Procedure to insert source data'
);
end;
/
begin
-- Create the Job
dbms_scheduler.create_job
(job_name => 'JOB_GG_DELETE',
program_name=> 'GG_DELETE',
schedule_name=>'SEC_15',
enabled=>true,
auto_drop=>false,
comments=>'Job to delete source data every 15 seconds');
end;
/
Lets check the number records in source and target table
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
05-OCT-12 12.34.43.028000 AM +05:30
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) as SOURCE_COUNT FROM GG_SRC;
SOURCE_COUNT
------------
72287
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) as TARGET_COUNT FROM GG_TRGT;
TARGET_COUNT
------------
0
Elapsed: 00:00:00.00
SQL>
SQL> --REM "Checking UPDATE RECORDS COUNT"---
SQL> SELECT COUNT(*) as Source_subobject_count FROM GG_SRC WHERE subobject_name = 'ORACLE';
SOURCE_SUBOBJECT_COUNT
----------------------
79
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) as Target_subobject_count FROM GG_TRGT WHERE subobject_name = 'ORACLE';
TARGET_SUBOBJECT_COUNT
----------------------
0
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) as Source_object_type FROM GG_SRC WHERE object_type = 'ORACLE';
SOURCE_OBJECT_TYPE
------------------
4
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) as Target_object_type FROM GG_TRGT WHERE object_type = 'ORACLE';
TARGET_OBJECT_TYPE
------------------
0
Elapsed: 00:00:00.00
SQL>
SQL> REM--"Checking DELETE RECORDS COUNT"--
SQL> SELECT COUNT(*) FROM GG_SRC WHERE subobject_name != 'ORACLE';
COUNT(*)
----------
603
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) FROM GG_TRGT WHERE subobject_name != 'ORACLE';
COUNT(*)
----------
0
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) FROM GG_SRC WHERE object_type != 'TABLE';
COUNT(*)
----------
69487
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) FROM GG_TRGT WHERE object_type != 'TABLE';
COUNT(*)
----------
0
Elapsed: 00:00:00.00
SQL>
Now the environment is set, let’s start Oracle GoldenGate. As a very first step start GoldenGate Manager Process in both Source and Target
Start GoldenGate Manager Process@SOURCE
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:50:57
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (HARI-PC) 1> start mgr
Manager started.
--------------------------------------------------------------------
/* SOURCE MANAGER RUN TIME INFO */
--------------------------------------------------------------------
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:28:27
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2012-10-05 00:35:13
***********************************************************************
Operating System Version:
Microsoft Windows 7 , on x86
Version 6.1 (Build 7600: )
Process id: 3308
Parameters...
PORT 1350
DYNAMICPORTLIST 12010-12030,1350,1230
***********************************************************************
** Run Time Messages **
***********************************************************************
2012-10-05 00:35:13 INFO OGG-00983 Manager started (port 1350)
Start GoldenGate Manager Process@TARGET
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:50:57
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (HARI-PC) 1> start mgr
Manager started.
--------------------------------------------------------------------
/* TARGET MANAGER RUN TIME INFO */
--------------------------------------------------------------------
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:28:27
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2012-10-05 00:35:34
***********************************************************************
Operating System Version:
Microsoft Windows 7 , on x86
Version 6.1 (Build 7600: )
Process id: 3976
Parameters...
PORT 7809
***********************************************************************
** Run Time Messages **
***********************************************************************
2012-10-05 00:35:34 INFO OGG-00983 Manager started (port 7809)
Its normally Initial Load which is the next step to start, but in scenario like above the key is to handle continuous data modifications coming in. So in a Zero downtime replication (ZDR) the initial step is to capture the current activity in the source database and then start the initial load i.e
Step 1 - ZDR - Extract and Datapump
OGG- Extract and Datapump
GGSCI (HARI-PC) 2> START EXTRACT OEXT1
Sending START request to MANAGER ...
EXTRACT OEXT1 starting
GGSCI (HARI-PC) 3> INFO ALL
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ODPUMP1 00:00:00 00:19:10
EXTRACT RUNNING OEXT1 00:19:16 00:00:03
GGSCI (HARI-PC) 4> START EXTRACT ODPUMP1
Sending START request to MANAGER ...
EXTRACT ODPUMP1 starting
GGSCI (HARI-PC) 5> INFO ALL
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ODPUMP1 00:00:00 00:19:19
EXTRACT RUNNING OEXT1 00:00:00 00:00:02
Runtime Logs
/* Extract - Runtime Info */
***********************************************************************
** Run Time Messages **
***********************************************************************
2012-10-05 00:39:17 INFO OGG-01517 Position of first record processed Seque
nce 160, RBA 19020816, SCN 0.4713843, Oct 3, 2012 12:56:36 AM.
TABLE resolved (entry HARI.GG_SRC):
TABLE HARI.GG_SRC;
Using the following key columns for source table HARI.GG_SRC: OBJECT_ID.
/* Datapump - Runtime Info */
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file I:\OGG\SOURCE\dirdat\EX000014 at 2012-10-05 00:39:37
Switching to next trail file I:\OGG\SOURCE\dirdat\EX000015 at 2012-10-05 00:39:3
7 due to EOF, with current RBA 196334
Opened trail file I:\OGG\SOURCE\dirdat\EX000015 at 2012-10-05 00:39:37
TABLE resolved (entry HARI.GG_SRC):
TABLE HARI.GG_SRC;
PASSTHRU mapping resolved for source table HARI.GG_SRC
Ok, my Extract and Datapump are running. But are they capturing the current modifications occurring at source , Let's check that
GGSCI (HARI-PC) 13> stats extract oext1
Sending STATS request to EXTRACT OEXT1 ...
Start of Statistics at 2012-10-05 00:40:06.
Output to I:\OGG\SOURCE\dirdat\EX:
Extracting from HARI.GG_SRC to HARI.GG_SRC:
*** Total statistics since 2012-10-05 00:39:27 ***
Total inserts 520.00
Total updates 301.00
Total deletes 202.00
Total discards 0.00
Total operations 1023.00
*** Daily statistics since 2012-10-05 00:39:27 ***
Total inserts 520.00
Total updates 301.00
Total deletes 202.00
Total discards 0.00
Total operations 1023.00
*** Hourly statistics since 2012-10-05 00:39:27 ***
Total inserts 520.00
Total updates 301.00
Total deletes 202.00
Total discards 0.00
Total operations 1023.00
*** Latest statistics since 2012-10-05 00:39:27 ***
Total inserts 520.00
Total updates 301.00
Total deletes 202.00
Total discards 0.00
Total operations 1023.00
End of Statistics.
Yes the data modifications are captured by the Extract process. Have got sufficient number of DML's as of now. While this is running, lets start the next step
Step 2 - ZDR - Initial Load
As a part of Initial Load the Target tables are synchronized with that of source. OGG takes the snapshot of commited transaction as of the start time of the Inital Load and migrates that data alone.
OGG - Initial Load
This is gonna be a special run as it will be executed only once,
GGSCI (HARI-PC) 15> start extract iext1
Sending START request to MANAGER ...
EXTRACT IEXT1 starting
/* From the Logs */
GGSCI command (HARI): start extract iext1.
Command received from GGSCI on host 192.168.1.2 (START EXTRACT IEXT1 ).
EXTRACT IEXT1 starting.
Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
EXTRACT IEXT1 starting.
EXTRACT IEXT1 started.
EXTRACT IEXT1 stopped normally.
The initial load extract process would start and stop its respective replicate process automatically. Now while the initial is running lets check the target database to validate if there are any data load,
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
--------------------------------------------------
05-OCT-12 12.41.03.718000 AM +05:30
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) as SOURCE_COUNT FROM GG_SRC;
SOURCE_COUNT
------------
72375
Elapsed: 00:00:00.01
SQL> SELECT COUNT(*) as TARGET_COUNT FROM GG_TRGT
2 ;
TARGET_COUNT
------------
72359
Elapsed: 00:00:00.01
SQL>
SQL>
SQL> SELECT COUNT(*) as TARGET_COUNT FROM GG_TRGT;
TARGET_COUNT
------------
72359
Elapsed: 00:00:00.01
Let's also check the stats of initial load avaliable at OGG
GGSCI (HARI-PC) 15> info extract iext1
EXTRACT IEXT1 Last Started 2012-10-05 00:40 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table HARI.GG_SRC
2012-10-05 00:41:01 Record 72359
Task SOURCEISTABLE
Since there are continuous changes happening in the database, let compare the records count in source and target again
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------------------
05-OCT-12 12.41.48.520000 AM +05:30
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) as SOURCE_COUNT FROM GG_SRC;
SOURCE_COUNT
------------
72369
Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) as TARGET_COUNT FROM GG_TRGT;
TARGET_COUNT
------------
72359
Elapsed: 00:00:00.01
SQL>
The last step is to start the replicate process at the target so as so apply the ongoing changes
Step 3 - ZDR - Start Replicate Process
The replicate process picks the remote trail files and applies them on the target database.
OGG - Replicate Process
GGSCI (HARI-PC) 2> start replicat orep1
Sending START request to MANAGER ...
REPLICAT OREP1 starting
/*-------------------------------------------------------------------*/
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file I:\OGG\TARGET\dirdat\RT000012 at 2012-10-05 00:42:30
Switching to next trail file I:\OGG\TARGET\dirdat\RT000013 at 2012-10-05 00:42:3
0 due to EOF, with current RBA 196899
Opened trail file I:\OGG\TARGET\dirdat\RT000013 at 2012-10-05 00:42:30
Processed extract process graceful restart record at seq 13, rba 1426.
Processed extract process graceful restart record at seq 13, rba 1488.
MAP resolved (entry HARI.GG_SRC):
MAP HARI.GG_SRC, TARGET hari.GG_TRGT;
Using following columns in default map by name:
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
Using the following key columns for target table HARI.GG_TRGT: OBJECT_ID.
Applying Lagging Transactions
GGSCI (HARI-PC) 5> send replicat orep1 getlag
Sending GETLAG request to REPLICAT OREP1 ...
Last record lag: 7 seconds.
At EOF, no more records to process.
Did OGG handle Collisions at the target since Extract process is started prior to Initial Load ?
GGSCI (HARI-PC) 6> stats replicat orep1
Sending STATS request to REPLICAT OREP1 ...
Start of Statistics at 2012-10-05 00:46:08.
Replicating from HARI.GG_SRC to HARI.GG_TRGT:
*** Total statistics since 2012-10-05 00:42:31 ***
Total inserts 631.00
Total updates 373.00
Total deletes 250.00
Total discards 0.00
Total operations 1254.00
Total insert collisions 391.00
Total update collisions 40.00
Total delete collisions 77.00
*** Daily statistics since 2012-10-05 00:42:31 ***
Total inserts 631.00
Total updates 373.00
Total deletes 250.00
Total discards 0.00
Total operations 1254.00
Total insert collisions 391.00
Total update collisions 40.00
Total delete collisions 77.00
*** Hourly statistics since 2012-10-05 00:42:31 ***
Total inserts 631.00
Total updates 373.00
Total deletes 250.00
Total discards 0.00
Total operations 1254.00
Total insert collisions 391.00
Total update collisions 40.00
Total delete collisions 77.00
*** Latest statistics since 2012-10-05 00:42:31 ***
Total inserts 631.00
Total updates 373.00
Total deletes 250.00
Total discards 0.00
Total operations 1254.00
Total insert collisions 391.00
Total update collisions 40.00
Total delete collisions 77.00
End of Statistics.
Yes OGG handles collisions and the same is obtained through stats. Let's validate the data in the database
SQL>
SQL> EXEC DBMS_SCHEDULER.drop_job(job_name => 'JOB_GG_DELETE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> EXEC DBMS_SCHEDULER.drop_job(job_name => 'JOB_GG_UPDATE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> EXEC DBMS_SCHEDULER.drop_job(job_name => 'JOB_GG_INSERT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL>
Have stopped the scheduler jobs just like what we do during cutover/downtime. Let's check both source and target tables,
SQL>
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
05-OCT-12 12.47.39.329000 AM +05:30
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) as SOURCE_COUNT FROM GG_SRC;
SOURCE_COUNT
------------
72445
Elapsed: 00:00:00.01
SQL>
SQL> SELECT COUNT(*) as TARGET_COUNT FROM GG_TRGT;
TARGET_COUNT
------------
72445
Elapsed: 00:00:00.00
SQL>
SQL> --REM "Checking UPDATE RECORDS COUNT"---
SQL> SELECT COUNT(*) as Source_subobject_count FROM GG_SRC WHERE subobject_name = 'ORACLE';
SOURCE_SUBOBJECT_COUNT
----------------------
155
Elapsed: 00:00:00.01
SQL>
SQL> SELECT COUNT(*) as Target_subobject_count FROM GG_TRGT WHERE subobject_name = 'ORACLE';
TARGET_SUBOBJECT_COUNT
----------------------
155
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) as Source_object_type FROM GG_SRC WHERE object_type = 'ORACLE';
SOURCE_OBJECT_TYPE
------------------
4
Elapsed: 00:00:00.01
SQL>
SQL> SELECT COUNT(*) as Target_object_type FROM GG_TRGT WHERE object_type = 'ORACLE';
TARGET_OBJECT_TYPE
------------------
4
Elapsed: 00:00:00.00
SQL>
SQL> REM--"Checking DELETE RECORDS COUNT"--
SQL> SELECT COUNT(*) FROM GG_SRC WHERE subobject_name != 'ORACLE';
COUNT(*)
----------
761
Elapsed: 00:00:00.00
SQL>
SQL> SELECT COUNT(*) FROM GG_TRGT WHERE subobject_name != 'ORACLE';
COUNT(*)
----------
761
Elapsed: 00:00:00.01
SQL>
SQL> SELECT COUNT(*) FROM GG_SRC WHERE object_type != 'TABLE';
COUNT(*)
----------
69645
Elapsed: 00:00:00.01
SQL>
SQL> SELECT COUNT(*) FROM GG_TRGT WHERE object_type != 'TABLE';
COUNT(*)
----------
69645
Elapsed: 00:00:00.01
SQL>
SQL> spool off
Thus we are able to migrate the data to the target table while the data modifications are still active at the source end.
