Oracle Golden Gate method for initial load :
1.File to Replicat: Here, extract writes to trail file in canonical format and is replicated via SQL.
2.File to Database Utility: Here, extract writes to a formatted text file and is replicated via database utility.
3.Direct Load: Direct load replicat directly to the target server.
4.Direct Bulk Load: It also replicat directly to the target server using SQL Loader API.
Today we will see the first method for Initial Load using File to Replicat ;
Assumptions :
1. Oracle Database is already installed on the Source and Target server.
2. Oracle Golden Gate Software is already installed on both the Source and the Target server.
3. Both servers have connectivity for 7809 manager port.
Step 1: Login to ./ggsci prompt of Oracle Golden Gate
[oracle@dbatesting gg]$ pwd /home/appndb/gg [oracle@dbatesting gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (dbatesting) 1>
Step 2: Add extract file at the source
GGSCI (dbatesting) 6> edit params ini_g SourceIsTable Userid ggs_owner, password oracle RmtHost 192.168.1.204, MgrPort 7809 RmtFile /appndb/gg/dirdat/init, Purge Table TST.*;
Step 3: You can check and record database scn at this time so you can start your replicat from this point
Note: You must start your replication extract before you start you initial load extract and after initial load extract finishes its works you can start replicat which is created for normal replication and not for the initial load with this SCN number.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1767025
Step 4: Now go to OS prompts of $GG_HOME directory and apply the following command
[oracle@dbatesting gg]$ ./extract paramfile dirprm/ini_gg.prm reportfile dirrpt/ini_gg.rpt
Step 5: Check .rpt file
[oracle@dbatesting ~]$ tail -200f /home/appndb/gg/dirrpt/ini_gg.rpt
2018-12-01 14:05:00 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 01:10:08
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2018-12-01 14:05:00
***********************************************************************
Operating System Version:
Linux
Version #2 SMP Wed Nov 14 14:37:56 PST 2018, Release 4.1.12-124.22.2.el6uek.x86_64
Node: dbatesting
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 24577
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2018-12-01 14:05:00 INFO OGG-03059 Operating system character set identified as UTF-8.
2018-12-01 14:05:00 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
SourceIsTable
Userid ggs_owner, password ***
2018-12-01 14:05:01 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.
RmtHost 192.168.1.204, MgrPort 7809
RmtFile /appndb/gg/dirdat/init, Purge
Table TST.*;
2018-12-01 14:05:01 INFO OGG-06508 Wildcard MAP (TABLE) resolved (entry TST.*): Table "TST"."TEST".
2018-12-01 14:05:01 INFO OGG-06509 Using the following key columns for source table TST.TEST: NO.
2018-12-01 14:05:01 INFO OGG-06508 Wildcard MAP (TABLE) resolved (entry TST.*): Table "TST"."TT".
2018-12-01 14:05:01 INFO OGG-06509 Using the following key columns for source table TST.TT: NO.
2018-12-01 14:05:01 INFO OGG-01851 filecaching started: thread ID: 139984578426624.
2018-12-01 14:05:01 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/appndb/gg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 128G
CACHESIZEMAX (strict force to disk): 96G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
2018-12-01 14:05:07 INFO OGG-01226 Socket buffer size set to 43520 (flush size 27985).
2018-12-01 14:05:07 INFO OGG-01478 Output file /appndb/gg/dirdat/init is using format RELEASE 12.2.
2018-12-01 14:05:07 INFO OGG-02911 Processing table TST.TEST.
2018-12-01 14:05:07 INFO OGG-02911 Processing table TST.TT.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2018-12-01 14:05:07 (activity since 2018-12-01 14:05:01)
Output to /appndb/gg/dirdat/init:
From Table TST.TEST:
# inserts: 4
# updates: 0
# deletes: 0
# discards: 0
From Table TST.TT:
# inserts: 3
# updates: 0
# deletes: 0
# discards: 0
REDO Log Statistics
Bytes parsed 0
Bytes output 1046
It shows it has recorded 4 inserts in TEST table and 3 inserts in TT table.
Step 6: Add replicat in the Target server
GGSCI (localhost.localdomain) 6> edit params rep_init SpecialRun End Runtime Userid ggs_owner@uat2, password oracle HandleCollisions AssumeTargetDefs ExtFile /appndb/gg/dirdat/init Map TST.*, Target TST.*;
Step 7: Now go to os prompt of $GG_HOME directory and run following command
[oracle@localhost gg]$./replicat paramfile dirprm/rep_init.prm reportfile dirrpt/rep_init.rpt
Step 8: Check report file
[oracle@localhost gg]$ tail -200f /appndb/gg/dirrpt/rep_init.rpt
2018-12-01 18:38:37 WARNING OGG-06439 No unique key is defined for table TT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2018-12-01 18:38:37 INFO OGG-02756 The definition for table TST.TT is obtained from the trail file.
2018-12-01 18:38:37 INFO OGG-06511 Using following columns in default map by name: NO.
2018-12-01 18:38:37 INFO OGG-06510 Using the following key columns for target table TST.TT: NO.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : /appndb/gg/dirdat/init
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 11 (x000b) IO Time : 2018-12-01 14:05:17.497066
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2018-12-01 14:05:17.497066 Insert Len 11 RBA 2346
TDR Index: 2
___________________________________________________________________
Reading /appndb/gg/dirdat/init, current RBA 2409, 7 records, m_file_seqno = -1, m_file_rba = 2409
Report at 2018-12-01 18:38:37 (activity since 2018-12-01 18:38:37)
From Table TST.TEST to TST.TEST:
# inserts: 4
# updates: 0
# deletes: 0
# discards: 0
From Table TST.TT to TST.TT:
# inserts: 3
# updates: 0
# deletes: 0
# discards: 0
Last log location read:
FILE: /appndb/gg/dirdat/init
RBA: 2409
TIMESTAMP: 2018-12-01 14:05:17.497066
EOF: NO
READERR: 400
CACHE OBJECT MANAGER statistics
CACHE MANAGER VM USAGE
vm current = 0 vm anon queues = 0
vm anon in use = 0 vm file = 0
vm used max = 0 ==> CACHE BALANCED
CACHE CONFIGURATION
cache size = 2G cache force paging = 3.41G
buffer min = 64K buffer max (soft) = 4M
pageout eligible size = 4M
===============================================================================
We can see here , 4 records are inserted in TEST table and 3 records are inserted in the TT table by replicat table.
Let’s check in the table that these records are actually inserted.
[oracle@localhost gg]$ sqlplus tst/oracle SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 3 15:02:43 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from test; COUNT(*) ---------- 4 SQL> select count(*) from tt; COUNT(*) ---------- 3
The initial load is successfully completed..!!!
Now you can start your replicat with above recorded SCN number.
GGSCI (localhost.localdomain as ggs_owner@uat2) 18> start replicat rep_gg , aftercsn 1767025 Sending START request to MANAGER ... REPLICAT REP_GG starting
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
