ORACLE GOLDEN GATE KHỞI TẠO DỮ LIỆU CHO TABLE

TÀI LIỆU THAM KHẢO:

GoldenGate – Online Change Synchronization with the initial data load

GoldenGate – Online Change Synchronization with the initial data load

Recently a question was posed to me as to how do we handle changes that are happening to the data while the initial data load extract process is in operation. Sometimes it may not be possible to have an application outage just to peform an initial data load and in most cases we will need to perform the initial data load using GoldenGate while users are connected to the database and changes are being made to the database via the application.

In my earlier tutorials we have discussed how to perform an initial data load and how to perform subsequent change synchronization to keep the data in sync. In this case we will just combine both those procedures into one.

So broadly speaking the steps would be :

  • Create the initial data load extract process or group
  • Create the online change synchronization extract process or group
  • Create the initial replicat group
  • Create the online online change synchronization replicat group
  • Start the online change synchronization extract process
  • Start the initial data load extract process
  • When the initial data load process has completed start the online change replicat group

Let us see a simple example where we will be peforming an initial data load of the MYOBJECTS table (copy of DBA_OBJECTS) and while the initial data load extract process is running and loading the 70,000 odd rows, we will from another session update the table while the data load is in progress. We will then see how these changes are also replicated to the target.

Source

SQL> select count(*) from myobjects;

  COUNT(*)
----------
     71338

Target

SQL> select count(*) from myobjects;

  COUNT(*)
----------
         0

Note – we will see that we have used the HANDLECOLLISIONS keyword in the replicat parameter file. If the source database will remain active during the initial load, include the HANDLECOLLISIONS parameter in the Replicat parameter file.

HANDLECOLLISIONS accounts for collisions that occur during the overlap of time between the initial load and the ongoing change replication. It reconciles insert operations for which the row already exists, and it reconciles update and delete operations.

We will turn this off after the initial data load has been completed.

Create the Initial Data Load Extract and Replicat Processes

Source

GGSCI (linux01.oncalldba.com) 20>  ADD EXTRACT ext1, SOURCEISTABLE
EXTRACT added.

GGSCI (linux01.oncalldba.com) 21> edit params ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 192.168.10.194, MGRPORT 7809
RMTTASK replicat, GROUP rep1
TABLE ggs_owner.myobjects;

Target

GGSCI (linux02.oncalldba.com) 7> add replicat rep1, specialrun
REPLICAT added.

GGSCI (linux02.oncalldba.com) 8> edit params rep1

REPLICAT rep1
HANDLECOLLISIONS
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects;

Create the Online Change Synchronization Extract and Replicat Processes

Source

GGSCI (linux01.oncalldba.com) 8> ADD EXTRACT ext2, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux01.oncalldba.com) 13> ADD RMTTRAIL /home/oracle/goldengate/dirdat/zz, EXTRACT ext2
RMTTRAIL added.

GGSCI (linux01.oncalldba.com) 9> edit params ext2

EXTRACT ext2
USERID ggs_owner, PASSWORD  ggs_owner
RMTHOST 192.168.10.194, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/zz
TABLE ggs_owner.myobjects;

Target

GGSCI (linux02.oncalldba.com) 2> add replicat rep2, exttrail  /home/oracle/goldengate/dirdat/zz
REPLICAT added.

GGSCI (linux02.oncalldba.com) 4>  edit params rep2

REPLICAT rep2
HANDLECOLLISIONS
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects ;

Start the Online Change Extract EXT2

GGSCI (linux01.oncalldba.com) 14> start extract ext2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (linux01.oncalldba.com) 15> info extract ext2

EXTRACT    EXT2      Last Started 2010-07-13 14:15   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:03:50 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2010-07-13 14:11:57  Seqno 149, RBA 19817488

Start the Initial Load Extract

GGSCI (linux01.oncalldba.com) 39> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (linux01.oncalldba.com) 41> info extract ext1

EXTRACT    EXT1      Last Started 2010-07-13 14:34   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                     2010-07-13 14:34:42  Record 2548

While the Initial Load Extract is in progress make some changes in the database

SQL> update myobjects set owner='GAVIN' where owner='SYS';

30001 rows updated.

SQL> commit;

Commit complete.

When the initial extract process has loaded all the rows, it will stop and so will the initial replicat process

GGSCI (linux01.oncalldba.com) 39> info extract ext1

EXTRACT    EXT1      Last Started 2010-07-13 15:01   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                     2010-07-13 15:04:22  Record 61964
Task                 SOURCEISTABLE


GGSCI (linux01.oncalldba.com) 40>  info extract ext1

EXTRACT    EXT1      Last Started 2010-07-13 15:01   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                     2010-07-13 15:04:44  Record 71307
Task                 SOURCEISTABLE

On Target

GGSCI (linux02.oncalldba.com) 15> send replicat rep1 getlag

ERROR: REPLICAT REP1 not currently running.

We will now start the online change replicat process. This will apply all the changes which have occurred during the initial data load. Note that once the replicat process has finished applying all the changes that are stored in the trail files (which have been written to by the extract process running on source) we will see the “At EOF, no more records ….”

GGSCI (linux02.oncalldba.com) 6> start replicat rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (linux02.oncalldba.com) 7> info replicat rep2

REPLICAT   REP2      Last Started 2010-07-13 15:06   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:05:47 ago)
Log Read Checkpoint  File /home/oracle/goldengate/dirdat/zz000000
                     First Record  RBA 0

GGSCI (linux02.oncalldba.com) 8> send replicat rep2 getlag

Sending GETLAG request to REPLICAT REP2 ...
Last record lag: 222 seconds.

GGSCI (linux02.oncalldba.com) 9> send replicat rep2 getlag

Sending GETLAG request to REPLICAT REP2 ...
Last record lag: 233 seconds.

GGSCI (linux02.oncalldba.com) 10> send replicat rep2 getlag

Sending GETLAG request to REPLICAT REP2 ...
Last record lag: 238 seconds.
At EOF, no more records to process.

Let us now check if both the initial data load and the updates have been propagated and applied on the target side.

SQL> select count(*) from myobjects;

  COUNT(*)
----------
     71307

SQL> select count(*) from myobjects where owner='GAVIN';

  COUNT(*)
----------
     30000

Now remove the HANDLECOLLISIONS clause …

GGSCI (linux02.oncalldba.com) 13> send replicat rep2,nohandlecollisions

Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ...
REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries

Also remove the line from the replicat parameter file via the “edit params replicat rep2 command”

Advertisements

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất /  Thay đổi )

Google photo

Bạn đang bình luận bằng tài khoản Google Đăng xuất /  Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất /  Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất /  Thay đổi )

Connecting to %s