News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

GoldenGate real-time replication from Active Standby Database to SQL Server 2012 target

  • Posted by Gavin Soorma
  • On March 11, 2014
  • 0 Comments
  • ACTIVE STANDBY, ALO, ARCHIVEDLOGONLY, data guard, GoldenGate, SQL server 2012, tranlogoptions

This note describes how to run an Initial Load along with Change Data Capture from a source Oracle 11g R2 Active Standby database (ALO Archived Log Only mode capture) to a MS SQL Server 2012 target database.

The table is a 6.3 million row table – AC_AMOUNT in the IDIT_PRD schema.

Steps

Create the Initial Load Extract

GGSCI (db02) 2> add extract testini1 sourceistable
EXTRACT added.


extract testini1
setenv (ORACLE_SID="DEVSB2")
setenv (ORACLE_HOME="/opt/oracle/product/server/11.2.0.3")
userid ggate_owner password ggate
RMTHOST DCV-RORSQL-N001.corp, MGRPORT 7809,   tcpbufsize 10485760, tcpflushbytes 10485760
rmtfile ./dirdat/rr, maxfiles 999999, megabytes 200, append
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST  /u03/oracle/DEVSB2/arch/
TABLE IDIT_PRD.AC_ACCOUNT;

Notes on using ALO mode:

1) The connection is to the open Active Standby database and not the primary database

2) The TRANLOGOPTIONS ARCHIVEDLOGONLY parameter has to be used to indicate the extract needs to read the archive log files on the standby database host and not the online redo log files on the primary database host

3) If we are using the FRA as a location for the archive redo log files, then we need to ensure that the LOG_ARCHIVE_DEST_1 parameter on the standby database has to be set to a directory other than the FRA because in ALO mode OGG cannot read archive log files from directories based on date formats as is the case in the FRA where for every day, a new directory is created based on the date.

Refer MOS note: ALO OGG Extract Unable to Find Archive Logs Under Date Coded sub Directories (Doc ID 1359776.1)

4) We have not specified the parameter COMPLETEARCHIVEDLOGONLY. This is the default in ALO mode. It forces Extract to wait for the archived log to be written to disk completely before starting to process redo data.

It is recommended NOT to use the NOCOMPLETEARCHIVEDLOGONLY parameter which is the default value for Classic Extract if we are using the ALO mode.

Create the Initial Load Replicat

GGSCI (DCV-RORSQL-N001) 125> add replicat testrep1 exttrail ./dirdat/rr
REPLICAT added.


GGSCI (DCV-RORSQL-N001) 126> edit params testrep1
REPLICAT testrep1
TARGETDB sqlserver2012
SOURCEDEFS ./dirdef/source9.def
BATCHSQL
MAP IDIT_PRD.AC_ACCOUNT, TARGET IDIT_PRD.AC_ACCOUNT;


Create the CDC Extract

GGSCI (db02) 5> add extract cdcext tranlog begin now
EXTRACT added.


GGSCI (db02) 6> add rmttrail ./dirdat/rs extract cdcext
RMTTRAIL added.



GGSCI (db02) 2> edit params cdcext

Extract cdcext
setenv (ORACLE_SID="DEVSB2")
setenv (ORACLE_HOME="/opt/oracle/product/server/11.2.0.3")
userid ggate_owner password ggate
RMTHOST DCV-RORSQL-N001.corp, MGRPORT 7809
RMTTRAIL ./dirdat/rs
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST  /u03/oracle/DEVSB2/arch/
TABLE IDIT_PRD.AC_ACCOUNT;

Note:
Since the CDC extract is reading archive log files from the Active Standby we have to specify the archive log sequence and position to start reading from


GGSCI (db02) 1> alter extract cdcext extseqno 105 extrba 188119040
EXTRACT altered.

Create the CDC Replicat on MS SQL Server 2012 target

GGSCI (DCV-RORSQL-N001) 131> add replicat repcdc exttrail ./dirdat/rs
REPLICAT added.


GGSCI (DCV-RORSQL-N001) 133> edit params repcdc

REPLICAT repcdc
TARGETDB sqlserver2012
SOURCEDEFS ./dirdef/source9.def
MAP IDIT_PRD.AC_ACCOUNT, TARGET IDIT_PRD.AC_ACCOUNT;

Start the CDC Extract before the Initial Load Extract – Do not start the CDC Replicat!

GGSCI (db02) 2> start extract extcdc

Sending START request to MANAGER ...
EXTRACT EXTCDC starting


GGSCI (db02) 3> info extract extcdc

EXTRACT    EXTCDC    Last Started 2014-03-06 12:48   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:05:48 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     First Record
                     SCN 0.0 (0)

Start the Initial Load Extract

GGSCI (db02) 6> start extract testini1

Sending START request to MANAGER ...
EXTRACT TESTINI1 starting


GGSCI (db02) 7> info extract testini1

EXTRACT    TESTINI1  Initialized   2014-03-06 12:25   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


GGSCI (db02) 12> !
info extract testini1

EXTRACT    TESTINI1  Last Started 2014-03-06 12:50   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table IDIT_PRD.AC_ACCOUNT
                     2014-03-06 12:50:29  Record 304670
Task                 SOURCEISTABLE


While the Initial Load Extract is running we perform a transaction on the Primary Oracle database

SQL> update idit_prd.ac_account
 2  set FREEZE_DATE='01-JAN-2020'
  3  where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

While the Initial Load Extract is running we start the Initial Load Replicat

GGSCI (DCV-RORSQL-N001) 135> start replicat testrep1

Sending START request to MANAGER ('MANAGER') ...
REPLICAT TESTREP1 starting


GGSCI (DCV-RORSQL-N001) 136> info replicat testrep1

REPLICAT   TESTREP1  Last Started 2014-03-06 12:53   Status RUNNING
Checkpoint Lag       00:02:39 (updated 00:00:00 ago)
Process ID           6764
Log Read Checkpoint  File ./dirdat/rr000000
                     2014-03-06 12:50:39.244754  RBA 21561942

We will start the CDC Replicat only after the Initial load has been completed on the target database

At this point in time initial load replicat is still inserting rows on target

GGSCI (DCV-RORSQL-N001) 137> stats replicat testrep1

Sending STATS request to REPLICAT TESTREP1 ...

Start of Statistics at 2014-03-06 12:53:54.

Replicating from IDIT_PRD.AC_ACCOUNT to IDIT_PRD.AC_ACCOUNT:

*** Total statistics since 2014-03-06 12:53:11 ***
        Total inserts                                 276172.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                              276172.00

Now the initial load is completed and we see that 6359427 rows have been extracted

GGSCI (db02) 17> info extract testini1

EXTRACT    TESTINI1  Last Started 2014-03-06 12:50   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table IDIT_PRD.AC_ACCOUNT
                     2014-03-06 12:53:27  Record 6359427
Task                 SOURCEISTABLE

The CDC extract is meanwhile running and we see that it has captured the UPDATE statement we executed

GGSCI (db02) 30> info extract cdcext

EXTRACT    CDCEXT    Last Started 2014-03-06 13:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-03-06 12:55:11  Seqno 107, RBA 3587072
                     SCN 2.2325484702 (10915419294)


GGSCI (db02) 31> stats extract cdcext

Sending STATS request to EXTRACT CDCEXT ...

Start of Statistics at 2014-03-06 13:04:54.

Output to ./dirdat/rs:

Extracting from IDIT_PRD.AC_ACCOUNT to IDIT_PRD.AC_ACCOUNT:

*** Total statistics since 2014-03-06 13:01:43 ***
        Total inserts                                      0.00
        Total updates                                  1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               1.00

We will start the CDC Replicat only after the initial load replicat has inserted all the rows into the MS SQL Server 2012 target

GGSCI (DCV-RORSQL-N001) 141> send replicat testrep1 getlag

Sending GETLAG request to REPLICAT TESTREP1 ...
Last record lag 690 seconds.


GGSCI (DCV-RORSQL-N001) 142> !
send replicat testrep1 getlag

Sending GETLAG request to REPLICAT TESTREP1 ...
Last record lag 715 seconds.


GGSCI (DCV-RORSQL-N001) 143> !
send replicat testrep1 getlag

Sending GETLAG request to REPLICAT TESTREP1 ...
Last record lag 759 seconds.

When we see the “At EOF , no more records to process” it means the initial load is now complete

GGSCI (DCV-RORSQL-N001) 146> send replicat testrep1 getlag

Sending GETLAG request to REPLICAT TESTREP1 ...
Last record lag 1,072 seconds.
At EOF, no more records to process.



GGSCI (DCV-RORSQL-N001) 147> stats replicat testrep1 latest

Sending STATS request to REPLICAT TESTREP1 ...

Start of Statistics at 2014-03-06 13:10:26.

Replicating from IDIT_PRD.AC_ACCOUNT to IDIT_PRD.AC_ACCOUNT:

*** Latest statistics since 2014-03-06 12:53:11 ***
        Total inserts                                6359427.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                             6359427.00


We now can start the CDC Replicat on target

GGSCI (DCV-RORSQL-N001) 182> start replicat repcdc

Sending START request to MANAGER ('MANAGER') ...
REPLICAT REPCDC starting


GGSCI (DCV-RORSQL-N001) 183> info replicat repcdc

REPLICAT   REPCDC    Last Started 2014-03-06 13:49   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           6472
Log Read Checkpoint  File ./dirdat/rs000001
                     2014-03-06 13:49:28.714735  RBA 4111468

We can see that it has applied the one single UPDATE statement on the target SQL Server database

GGSCI (DCV-RORSQL-N001) 184> stats replicat repcdc

Sending STATS request to REPLICAT REPCDC ...

Start of Statistics at 2014-03-06 13:49:35.

Replicating from IDIT_PRD.AC_ACCOUNT to IDIT_PRD.AC_ACCOUNT:

*** Total statistics since 2014-03-06 13:49:21 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Verify the UPDATE statement in the SQL Server database– note the value for the FREEZE_DATE column

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *