Oracle GoldenGate Real-Time Replication (Unidirectional)
Below is the specs of the source and target I used for this demonstration:
Source: OL 7.9 / DB 11.2.0.2 / GoldenGate 12.3 / Classic Extract
Target: OL 8 / DB 19c / GoldenGate 19.1 / Integrated Replicat
(The reason I used GoldenGate 12.3 in the source server is since GoldenGate 19c does not support Oracle Database 11.2.0.2
The source DB server use the classic extract method to extract the trail files since integrated extract method is not supported.)
Ports to be opened:
7809 Source --> Target -- Manager Port
7810-7820 Source --> Target -- Collector Ports
1521 Source --> Target -- Listener Port (Strictly not necessary but better to have opened)
7810-7820 Source --> Target -- Collector Ports
1521 Source --> Target -- Listener Port (Strictly not necessary but better to have opened)
22 Bidirectional -- SSH Port
Make sure the tns entries are clearly specified in the tnsnames.ora file.
Phase 0: OS Setup & Installation (Source & Target)
Use the same OS user as the database user for the installation process (oracle)
Setting up the Source:
1. Create the GoldenGate home and grant the necessary permission.
mkdir -p /u01/app/oracle/product/ogg123
chown -R oracle:oinstall /u01/app/oracle/product/ogg123
chmod -R 775 /u01/app/oracle/product/ogg123
2. Edit bash_profile:
export OGG_HOME=/u01/app/oracle/product/ogg123
3. Install the GoldenGate 12.3 software:
unzip /u01/soft/GoldenGate/V975837-01.zip
cd /u01/soft/GoldenGate/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller
If the graphical display is not appearing when running the installation wizard please check the below link:
4. From the Oracle GoldenGate directory run the GGSCI program.
Set DB environment:
. oraenv
cd $OGG_HOME
[oracle@gg-source ogg123]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 15 2018 21:16:09
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg-source) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/product/ogg123
Parameter file /u01/app/oracle/product/ogg123/dirprm: already exists.
Report file /u01/app/oracle/product/ogg123/dirrpt: already exists.
Checkpoint file /u01/app/oracle/product/ogg123/dirchk: already exists.
Process status files /u01/app/oracle/product/ogg123/dirpcs: already exists.
SQL script files /u01/app/oracle/product/ogg123/dirsql: already exists.
Database definitions files /u01/app/oracle/product/ogg123/dirdef: already exists.
Extract data files /u01/app/oracle/product/ogg123/dirdat: already exists.
Temporary files /u01/app/oracle/product/ogg123/dirtmp: already exists.
Credential store files /u01/app/oracle/product/ogg123/dircrd: already exists.
Masterkey wallet files /u01/app/oracle/product/ogg123/dirwlt: already exists.
Dump files /u01/app/oracle/product/ogg123/dirdmp: already exists.
GGSCI (gg-source) 2> EXIT
[oracle@gg-source ogg123]$
Setting up the Target:
5. Create the GoldenGate home and grant the necessary permission.
mkdir -p /u01/app/oracle/product/ogg19
chown -R oracle:oinstall /u01/app/oracle/product/ogg19
chmod -R 775 /u01/app/oracle/product/ogg19
6. Edit bash_profile:
export OGG_HOME=/u01/app/oracle/product/ogg19
7. Install the GoldenGate 19.1 software:
unzip /u01/soft/GoldenGate/V983658-01.zip
cd /u01/soft/GoldenGate/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller
If the graphical display is not appearing when running the installation wizard please check the below link:
(The installation steps are similar to the 12.3 installation.)
8. From the Oracle GoldenGate directory run the GGSCI program.
Set DB environment:
. oraenv
cd $OGG_HOME
./ggsci
GGSCI (gg-target) 1> CREATE SUBDIRS
GGSCI (gg-target) 2> EXIT
Phase 1: Source Database Preparation (11.2.0.2)
1. Enable Archivelog & Logging: The database must be in ARCHIVELOG mode with minimal supplemental logging enabled.
ARCHIVE LOG LIST;
-- Run as SYSDBA if ARCHIVELOG mode is disabled
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
2. Create GoldenGate User (Source)
CREATE TABLESPACE GG_TBS DATAFILE '/u01/app/oracle/oradata/GGSOURCE/gg_tbs.dbf' SIZE 100M AUTOEXTEND ON;
CREATE USER ggadmin IDENTIFIED BY <password> DEFAULT TABLESPACE GG_TBS QUOTA UNLIMITED ON GG_TBS;
GRANT CONNECT, RESOURCE, DBA TO ggadmin;
-- Specific privileges for Classic Extract
GRANT SELECT ANY DICTIONARY TO ggadmin;
GRANT FLASHBACK ANY TABLE TO ggadmin;
3. Enable Schema-Level Logging: Log in to ggsci and enable logging for all schemas you intend to replicate (e.g., SCHEMA_A, SCHEMA_B).
./ggsci
DBLOGIN USERID ggadmin, PASSWORD <password>
ADD TRANDATA GG1.*
ADD TRANDATA GG2.*
ADD TRANDATA GG3.*
ADD TRANDATA GG4.*
ADD TRANDATA GG5.*
ADD TRANDATA GG6.*
(If you receive the below warning when executing the TRANDATA command you can safely ignore it. This warning occurs due to the DB being an older version:
WARNING OGG-01988 Could not find schematrandata function in source database: failed to find function.
Phase 2: Source GoldenGate Configuration
1. Configure Manager (mgr.prm):
./ggsci
EDIT PARAMS MGR
--Add the following entries
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 2
2. Configure Classic Extract (ext_11g): Classic extract reads the redo logs directly.
EDIT PARAMS ext_11g
--Add the following entries
EXTRACT ext_11g
USERID ggadmin, PASSWORD <password>
EXTTRAIL ./dirdat/la
TABLE GG1.*;
TABLE GG2.*;
TABLE GG3.*;
TABLE GG4.*;
TABLE GG5.*;
TABLE GG6.*;
3. Configure Data Pump (pump_11g): Sends data to the target server to avoid network latency impacting the capture process.
EDIT PARAMS pump_11g
--Add the following entries
EXTRACT pump_11g
USERID ggadmin, PASSWORD <password>
RMTHOST <Target_Server_IP>, MGRPORT 7809
RMTTRAIL ./dirdat/rt
PASSTHRU
TABLE GG1.*;
TABLE GG2.*;
TABLE GG3.*;
TABLE GG4.*;
TABLE GG5.*;
TABLE GG6.*;
4. Register and Start Processes:
-- Add Extract (Classic)
ADD EXTRACT ext_11g, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/la, EXTRACT ext_11g
-- Add Pump
ADD EXTRACT pump_11g, EXTTRAILSOURCE ./dirdat/la
ADD RMTTRAIL ./dirdat/rt, EXTRACT pump_11g
START MGR
START EXTRACT ext_11g
START EXTRACT pump_11g
Phase 3: Target Database Preparation (19c)
ENABLE: alter system set enable_goldengate_replication=TRUE scope=BOTH;
1. Create GoldenGate User (Target):
-- Run as SYSDBA on Target
CREATE TABLESPACE GG_TBS DATAFILE '/u01/app/oracle/oradata/GGTARGET/datafile/gg_tbs.dbf' SIZE 100M AUTOEXTEND ON;
CREATE USER ggadmin IDENTIFIED BY <password> DEFAULT TABLESPACE GG_TBS QUOTA UNLIMITED ON GG_TBS;
GRANT CONNECT, RESOURCE, DBA TO ggadmin;
-- Grant API privileges for Integrated Replicat (Recommended for 19c)
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');
2. Configure Manager (mgr.prm):
cd $OGG_HOME
. oraenv
./ggsci
EDIT PARAMS MGR
--Add the following entries
PORT 7809
AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 5
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 2
Phase 4: Target GoldenGate Configuration
1. Create Checkpoint Table: Required for maintaining data integrity during restarts.
DBLOGIN USERID ggadmin, PASSWORD <password>
ADD CHECKPOINTTABLE ggadmin.chkpt
2. Configure Replicat (rep_19c): We will use Integrated Replicat for better performance on 19c.
EDIT PARAMS rep_19c
--Add the following entries
REPLICAT rep_19c
DBOPTIONS INTEGRATEDPARAMS(PARALLELISM 4)
USERID ggadmin@<target-alias>, PASSWORD <password>
ASSUMETARGETDEFS
MAP GG1.*, TARGET GG1.*;
MAP GG2.*, TARGET GG2.*;
MAP GG3.*, TARGET GG3.*;
MAP GG4.*, TARGET GG4.*;
MAP GG5.*, TARGET GG5.*;
MAP GG6.*, TARGET GG6.*;
3. Add and Start Replicat:
ADD REPLICAT rep_19c, INTEGRATED, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE ggadmin.chkpt
START MGR
Phase 5: Synchronization (The "Real-Time" Link)
1. Get Current SCN on Source:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1559893
2. Export Data (Data Pump Expdp): Export the schemas using the SCN from above to
expdp \"/ as sysdba\" schemas=GG1,GG2,GG3,GG4,GG5,GG6 directory=EXPDP_DIR dumpfile=init_load.dmp flashback_scn=1559893
3. Import Data on Target: Import the dump file into the 19c database.
impdp \"/ as sysdba\" DIRECTORY=EXPDP_DIR DUMPFILE=init_load.dmp LOGFILE=import.log SCHEMAS=GG1,GG2,GG3,GG4,GG5,GG6
4. Start Replicat at SCN: Tell the Replicat to start processing transactions after that SCN to avoid duplicates.
./ggsci
START REPLICAT rep_19c, AFTERCSN 1559893
Phase 6: Verifying
1. Basic verification.
Target:
GGSCI (gg-target) 3> INFO ALL
If all are running replicat process is functioning successfully.
If not you can access the report via:
GGSCI (gg-target) 3> VIEW REPORT <process-name>
Source:
GGSCI (gg-source) 3> INFO ALL
If all are running extract process is functioning successfully.
If not you can access the report via:
GGSCI (gg-source) 3> VIEW REPORT <process-name>
2. Verify Data Flow (Statistics):
If the processes are green verify that they are actually processing records.
On Target (ggsci):
STATS REPLICAT rep_19c
What to look for:
Look at the Total inserts, Total updates and Total deletes.
Run the command, wait 10 seconds and run it again. If operations are happening on the source these numbers should increase.
3. Troubleshooting Failures
If INFO ALL says RUNNING but data isn't arriving:
Check the Pump on Source: Does the Pump know where to send data?
STATS EXTRACT pump_11g
If the stats are zero the Pump isn't reading from the Extract's trail file.
Check the Report Files: This is where the detailed errors live.
Source: VIEW REPORT pump_11g
Target: VIEW REPORT rep_19c
General Commands
1. Monitoring
INFO ALL --Shows the status (RUNNING, STOPPED, ABENDED) and lag of all processes
INFO <PROCESS_NAME> --Shows specific details for one process, including the last checkpoint (read/write position)
INFO <PROCESS_NAME>, DETAIL --Deep dive. Shows exactly which trail file it is reading, which transaction it is working on and runtime statistics
LAG <PROCESS_NAME> --Pings the process to calculate the exact latency in seconds right now
2. Process Control
START <PROCESS_NAME> --Starts the process
STOP <PROCESS_NAME> --Performs a "Graceful Stop." It waits for the current transaction to finish before stopping
STOP <PROCESS_NAME>! --Kills the process immediately, rolling back the current transaction.
RESTART <PROCESS_NAME> --Stops and immediately starts the process. Useful after applying a parameter change.
3. Troubleshooting & Logs
VIEW REPORT <PROCESS_NAME> --Opens the active log file for that process.
VIEW GGSEVT --Views the global GoldenGate system event log
Gmmc
ReplyDelete