GOLDEN GATE COMMANDS
*****GG_CONFIGURATION:-
Source : Configure Database for Capture
Create a database User ggusr for extraction
SQL> create user ogg identified by ogg;
SQL> CREATE TABLESPACE gg_tbs DATAFILE '/u01/app/oracle/oradata/PROD/gg_tbs_01.dbf' SIZE 1G;
SQL> alter user ogg QUOTA UNLIMITED ON gg_tbs;
SQL> grant create session, alter session, alter system, resource, connect to ogg;
SQL> grant select any dictionary, flashback any table, select any table to ogg;
SQL> grant select any transaction, execute on DBMS_FLASHBACK to ogg;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg');
Configure Database
-Enable archivelog
alter database archivelog;
– Enable Force Logging
(Why-Enabling Force Logging in an Oracle database ensures that all changes to the database are logged in the redo log files,
regardless of any individual object-level logging settings. This is particularly important for maintaining data integrity and
ensuring that the database can be fully recovered in case of a failure. Here are the key reasons for enabling Force Logging)
SQL> ALTER DATABASE FORCE LOGGING;
– Enable Supplemental Logging
(Why - Enabling supplemental logging is a critical step for supporting replication, logical standby databases, and detailed change capture.
It ensures that all necessary information about data changes is captured, facilitating accurate and reliable replication and data recovery processes.)
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
On table level supplemental enable-
ALTER TABLE sbz.rep_tab1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Verify enabeled or not -
SELECT log_group_name, supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk
FROM dba_log_groups
WHERE table_name = '<rep_tab>';
SQl> Alter system switch logfile;
– Enable goldengate Replication parameter
(Why - This ensures that all changes are captured from the source database and applied to the target database, maintaining data consistency across the environments.)
SQL> Alter system set enable_goldengate_replication=true;
Execute GoldenGate Script and input user ggusr (Not required if using integrated)
SQL> @$GG_HOME/marker_setup.sql
SQL> @ $GG_HOME/role_setup.sql
SQL> @ $GG_HOME/ddl_setup.sql
SQL> @$GG_HOME/ddl_enable.sql
SQL>@$GG_HOME/sequence.sql
Source: Follow below steps in any of machine to configure GoldenGate:
Create Subdirectories
cd $GG_HOME
./ggsci
ggsci> create subdirs
Edit the manager parameter file on the ggsci prompt
ggsci> edit params mgr
port <port_number>
DYNAMICPORTLIST <portlists_number>
USERID ggusr@<<<TNS_for_GG>>>, PASSWORD <password>
AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 120
AUTOSTART ER *
PURGEOLDEXTRACTS /u01/ogg19/dirdat/*, USECHECKPOINTS, MINKEEPDAYS
Type the below command for starting Golden Gate Manager process:
Ggsci> start manager
Encrypting Database User ggusr Password
ggsci> encrypt password <DB User Password> ,blowfish
This will return the password in encrypted format
This encrypted password can be used to login to Database through GoldenGate
ggsci> dblogin userid ggusr,password <encrypted password> , blowfish
Enable Trandata
Assuming scott is a user to be replicated
ggsci> add schematrandata scott;
Configure Capture
Ggsci> register extract cp1 database
Ggsci> add extract cp1, integrated tranlog, begin now
Ggsci> add exttrail /u01/ogg19/dirdat/xx, extract cp1
ggsci> edit params cp1
extract cp1
SETENV (ORACLE_HOME =”/<<<ORACLE_HOME>>>”)
SETENV (ORACLE_SID =”<<<ORACLE_SID>>>”)
Userid ggext@<<<ORACLE_SID>>>, PASSWORD <encrypted password> ,BLOWFISH
EXTTRAIL /u01/ogg19/dirdat/xx
DDL INCLUDE MAPPED
— Sequence and Table statements that identify data to capture.
SEQUENCE scott.*;
TABLE scott.*;
Configure Pump process
Ggsci> add extract pp1, exttrail /u01/ogg19/dirdat/xx
Ggsci> add rmttrail /u01/ogg19/dirdat/xx, extract pp1
ggsci> edit params pp1
extract pp1
PASSTHRU
RMTHOST <<<target_server_ip>>>, MGRPORT 7809
RMTTRAIL /u01/ogg19/dirdat/xx
SEQUENCE scott.*;
TABLE scott.*;
Configure the Target Machine (Single Instance Database):
Install the GoldenGate on target machine same steps as Source Machine under B) heading
Configure Database for Target Server same as in Source Server
add below additional privilege to ggusr in database
delete any table, alter any table, insert any table, update any table
Encrypt password as it is done for Source Server
Create Checkpoint TABLE for replication
ggsci> dblogin userid ggusr,password <> , blowfish
ggsci> delete CHECKPOINTTABLE GGREP.GGCHKPT
ggsci> add CHECKPOINTTABLE GGREP.GGCHKPT
Register Replicats to database
register replicat REP1 database
Add Replicats
Ggsci> add replicat REP1, INTEGRATED, exttrail /goldengate/ogg19/dirdat/xx CHECKPOINTTABLE GGREP.GGCHKPT
================================================================
Parameter file for Replicat
ggsci> edit params rep1
REPLICAT REP1
— Database environment variables.
SETENV (ORACLE_HOME=”<Target Server Oracle Home>”)
SETENV (ORACLE_SID = “<Target Database ID>”)
— Replicat database user login, with encrypted password.
USERID ggrep, PASSWORD <encrypted password> BLOWFISH, ENCRYPTKEY DEFAULT
— Discard file.
DISCARDFILE /u05/goldengate/ogg/dirrpt/carep5.dsc, PURGE
DISCARDROLLOVER ON SUNDAY
— MAP statement to MAP source objects to TARGET objects.
MAP IMPAUX.ESB_MAPPING_PKG TARGET CAADM.ESB_MAPPING_PKG;
Perform the Initial Load (using export Import):
Source:
Take Consistent Export at source server
SQL> Select current_scn from v$database;
expdp directory=dir1 dumpfile=scott.dmp logfile=scott.log schemas=scott flashback_Scn=<SCN>
copy the Dump file scott.dmp to the target Server
Import the Schema scott to target database
impdp directory=dir1 dumpfile=scott.dmp logfile=scott.log content=all table_exists_action=truncate flashback_SCN=<SCN>
Start the replication:
Source:
Ggsci> start cp1
ggsci> start pp1
Target:
ggsci> start rep1, aftercsn <SCN Captured during export>
----------------------------------------
Upgrade Oracle Golden Gate 12cR1 to 12cR2
In this article, we will look at Oracle Golden Gate upgrade from 12.1 to 12.2 on a Linux server. Make sure you Allow
golden gate processes to finish processing all current DML and DDL data in GG trail files.
Golden Gate Upgrade Prerequisite
Perform Golden Gate upgrade
If upgrading DB along with GG, upgrade DB first
Golden Gate Upgrade Prerequisite
Stop user activity on source objects which are involved in replication
Stop Extract
GGSCI> lag ext1
GGSCI> stop ext1
Make sure replicat applies all transactions on target
GGSCI> lag rep1
Stop pump and Replicat
send ex1 status
GGSCI> stop dp1
GGSCI> stop rep1
Stop Manager on both source and target
Checking the running transactions :
send sbzex showtrans
checking the BR details:-
info sbzex showch
GGSCI> stop mgr
To view the ggserr log report from ggsci :-
VIEW GGSEVT
ADD CHECKPOINTTABLE sbz.GGCHECKPOINT
alter sbzrep etrollover
ALTER REPLICAT sbzrep, SCN 6422728
ALTER extract sbz, EXTSEQNO 1
ALTER replicat sbzrep, EXTRBA 2039
network issue check in gg -
send sbzdp gettcpstats
Take backup of GG_HOME on both source and target
---> cp -R ogghome ogghome_bkp
cp /home/oracle/ogghome/dirprm/* /home/oracle/gg_21/dirprm
cp /home/oracle/ogghome/dirchk/* /home/oracle/gg_21/dirchk
cp /home/oracle/ogghome/dirrpt/* /home/oracle/gg_21/dirrpt
cp /home/oracle/ogghome/dirdef/* /home/oracle/gg_21/dirdef
cp /home/oracle/ogghome/dirdat/* /home/oracle/gg_21/dirdat
cp /home/oracle/ogghome/dircrd/* /home/oracle/gg_21/dircrd
------------------------------------------------------------------
If you want to upgrade source and target DBs, this is the time. Once DBs are upgraded still, make sure there is no activity on source objects involved in the replication
Perform Golden Gate upgrade
Download the latest 21.1 Golden Gate and copy the zip file to both source and target DBs
Unzip and start the runInstaller
Give same old GG_HOME location while installation – Do not worry, this option will auto-upgrade old GG
Do not start manager
Perform installation on both source and target DBs in the same old GG_HOME location
Run @ulg script for supplemental log upgrade from GG_HOME
cd $GG_HOME
Sqlplus / as sysdba
SQL> @ulg.sql press enter when prompted
Rollover Extract, Pump, and Replicat to next trail sequence number
On source DB:
=============
alter extract ext1 etrollover
alter extract dp1 etrollover
info ext1 detail
ALTER EXTRACT dp1, EXTSEQNO <above_seq_no>, EXTRBA 0
info dp1 detail
On target DB:
=============
alter replicat rep1, EXTSEQNO <above_seq_no>, EXTRBA 0
Start Manager, Extract, Pump, and Replicat
This is an oracle known error. You get prvtlmpg.plb script with GG binaries. This resides under $GG_HOME.
Run this script on both source and target database
cd $GG_HOME
Sqlplus / as sysdba
SQL> @prvtlmpg.plb
--> press enter when prompted
Allow activity on source objects involved in the replication.
create new directory and copy all the directory -
cp -r /home/oracle/gghome_bkp/dirprm /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirpcs /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirchk /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirdat /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirdef /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirdmp /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/diretc /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirrpt /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirsql /home/oracle/ogghome/
cp -r /home/oracle/gghome_bkp/dirwar /home/oracle/ogghome/
./runInstaller -silent -nowait -responseFile /home/oracle/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp
==================================================================
Silent Install of Oracle GoldenGate 12c
==================================================================
[oracle@oel Downloads]$ unzip ./121200_fbo_ggs_Linux_x64_shiphome.zip –d ggate
[oracle@oel response]$ pwd
/home/oracle/Downloads/ggate/fbo_ggs_Linux_x64_shiphome/Disk1/response
The response file that is provided is called oggcore.rsp. This is the file that needs to be edited to contain the items for silent install.
[oracle@oel response]$ vi oggcore.rsp
Once the file is open for editing in vi (or your favorite editor),
there are 7 items which you will have modify in order to install Oracle Golden Gate silently. The parameters and details are:
PARAMETERS - OPTIONS
INSTALL_OPTION - ORA12c or ORA11g
SOFTWARE_LOCATION - Where to install the software
START_MANAGER - TRUE or FALSE
MANAGER_PORT - Any port number, default 7809
DATABASE_LOCATION - Set to $ORACLE_HOME
INVENTORY_LOCATION - /u01/app/oracle/oraenventory
UNIX_GROUP_NAME - Group that should own the installation of Golden Gate
Once the response file is filled out, then it can be used with the runInstaller utility (just the same as with other Oracle tools).
Installation
With the oggcore.rsp file edited, it is time to install Oracle Golden Gate. Just as with other Oracle products, you use the response file with the runInstaller utility (as below).
The installer will start and tell you when finish.
[oracle@oel response]$ ../runInstaller -silent -responseFile /home/oracle/Downloads/ggate/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
===================================================================
Upgrading Oracle GoldenGate from 19c to 21c on Linux involves several steps:-
1) Pre-Upgrade Steps
Check Compatibility:
2)Verify that the source and target databases are compatible with Oracle GoldenGate 21c.
Backup Current Configuration:
3)Backup your current GoldenGate configuration and data. Ensure you have backups of parameter files, checkpoint files, and trail files.
Stop Oracle GoldenGate Processes:
4)Stop all GoldenGate processes (Extract, Pump, Replicat) gracefully.
./ggsci
stop extract <extract_name>
stop replicat <replicat_name>
stop mgr
exit
5)Check for Open Transactions:
Ensure that there are no open transactions by checking the status in GGSCI.
shell
./ggsci
info all
6)Upgrade Steps
Download Oracle GoldenGate 21c:
Download the Oracle GoldenGate 21c software from the Oracle Technology Network or Oracle Software Delivery Cloud.
Install Oracle GoldenGate 21c:
Install the new version of GoldenGate in a separate directory.
shell
mkdir -p /u01/app/gg_21
cd /u01/app/gg_21
unzip /path/to/ogg21c.zip
Copy Configuration Files:
7)Copy your existing parameter files, checkpoint files, and other configuration files from the 19c directory to the new 21c directory.
shell
cp /home/oracle/ogghome/dirprm/* /home/oracle/gg_21/dirprm
cp /home/oracle/ogghome/dirchk/* /home/oracle/gg_21/dirchk
cp /home/oracle/ogghome/dirrpt/* /home/oracle/gg_21/dirrpt
cp /home/oracle/ogghome/dirdef/* /home/oracle/gg_21/dirdef
cp /home/oracle/ogghome/dirdat/* /home/oracle/gg_21/dirdat
cp /home/oracle/ogghome/dircrd/* /home/oracle/gg_21/dircrd
cp /home/oracle/gghome/dirwlt/* /home/oracle/gg_21/dirwlt
cp /home/oracle/ogghome/dirsql/* /home/oracle/gg_21/dirsql
cp /home/oracle/ogghome/dirpcs/* /home/oracle/gg_21/dirpcs
cp /home/oracle/ogghome/diretc/* /home/oracle/gg_21/diretc
cp /home/oracle/ogghome/dirtmp/* /home/oracle/gg_21/dirtmp
Update Environment Variables:
8)Update your environment variables to point to the new GoldenGate 21c installation.
shell
export OGG_HOME=/u01/app/gg_21
export PATH=$OGG_HOME:$PATH
export TNS_ADMIN =$ORACLE_HOME/Network/admin
export library path
9)Run the Oracle GoldenGate Installer:
Run the Oracle GoldenGate installer to upgrade the instance.
shell
cd /u01/app/ogg21c
./ggsci
In GGSCI, run the following commands:
dblogin userid ogg@prod, password ogg
upgrade check
upgrade start
Re-enable Supplemental Logging:
Ensure that supplemental logging is enabled on the source database.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
IMP - Rename the old ogghome as ogghome_bkp and rename the new gg_21 as ogghome (before this stop mgr and after start mgr with all processes)
======================================================
MICROSERVICES INSTALLATION
mkdir sm_dir
mkdir dep_dir
mkdir -p /home/oracle/GGHOME/var/log/
export OGG_ETC_HOME=/home/oracle/DP_DIR/etc
export OGG_VAR_HOME=/home/oracle/DP_DIR/var
export OGG_HOME=/home/oracle/OGGHOME
export PATH=$OGG_HOME:$PATH
Parameter file in MSA- /home/oracle/DP_DIR/etc/conf/ogg
All Logs - /home/oracle/DP_DIR/var/log
create table hr.rep_tab
(
c1 int ,
c2 char(30),
c3 int ,
c4 int ,
primary key(c1)
);
insert into hr.rep_tab values(1,'Alpha',10,100);
create table tom.rep_tab(
c1 number primary key ,
c2 char (30),
c3 number ,
c4 number);
add exttrail C:\GGHOME\dirdat\ms, extract EX2
add exttrail C:\GGHOME\dirdat\ms, extract
EXTRACT MSEXT
SOURCEDB gg
EXTTRAIL C:\GGHOME\dirdat\ms
TABLE hr.rep_tab;
Add extract MSDP, EXTTRAILSOURCE C:\GGHOME\dirdat\ms
Add rmttrail /home/oracle/DP_DIR/var/lib/data/ms, extract MSDP
EXTRACT MSDP
SOURCEDB gg
RMTHOST 192.168.93.130 PORT 7003
RMTTRAIL /home/oracle/DP_DIR/var/lib/data/ms
TABLE sbz_rep_tab;
REPLICAT ORAREP
USERID ogg, PASSWORD ogg
SOURCEDEFS /home/oracle/DP_DIR/var/lib/def/rep_tab.def
MAP hr.rep_tab, TARGET sbz_rep_tab;
CREATE USER HR IDENTIFIED BY sbz
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
CREATE TABLE sbz_rep_tab
(
c1 number PRIMARY KEY,
c2 CHAR(30),
c3 number,
c4 number
);
===============================
Oracle Streams apply process is slow On Goldengate integrated replicat:
===============================
Usually If your environment having huge transaction (replication setup) definitely your sys.streams$_apply_progress segment also will increase randomly.
So my advice is instead of facing checkpoint lag on replication side ,Periodically we have to check STREAMS$_APPLY_PROGRESS segment size also Need
to perform re-org either manual way or else schedule the job using dbms_scheduler.
There are two type of re-org options is there.
1. OFFLINE
2. ONLINE
In this blog we are going to see offline method using below steps:
First check the size of stream apply table before going to re-org.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='STREAMS$_APPLY_PROGRESS';
SUM(BYTES/1024/1024)
——————–
12.063225
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
GGSCI (GG-Target.com) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:01:18 00:00:45
OFFLINE: Stop the replicat process.
GGSCI (GG-Target.com) 12> stop rep2
Sending STOP request to REPLICAT REP2 …
Request processed.
GGSCI (GG-Target.com) 14> info rep2
REPLICAT REP2 Last Started 2019-08-09 21:26 Status STOPPED
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/BB000001929
2019-08-09 22:04:42.658077 RBA 54619
GGSCI (GG-Target.com) 15> sh sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 9 22:06:23 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT COUNT(*) from STREAMS$_APPLY_PROGRESS;
COUNT(*)
———-
3591816
Create temporary table (Backup Table) using original stream apply table.
SQL> Create table stream_dbwr as select * from sys.streams$_apply_progress p where p.commit_scn >= (select m.commit_scn from streams$_apply_milestone m where m.apply# = p.apply#);
Table created.
Compare the steam table (original) and temporary table count.
SQL> SELECT COUNT(*) from stream_dbwr;
COUNT(*)
———-
3591816
Then truncate the stream table.
SQL> Truncate table sys.streams$_apply_progress;
Table truncated.
SQL> ALTER TABLE STREAMS$_APPLY_PROGRESS INITRANS 8 PCTFREE 10;
Table altered.
Again insert the data from temorary table to main table.
SQL> Insert into sys.streams$_apply_progress select * from stream_dbwr;
3591816 rows created.
Check the count of main stream apply table.
SQL> SELECT COUNT(*) from STREAMS$_APPLY_PROGRESS;
COUNT(*)
———-
3591816
Then enable the row movement for stream table
SQL> alter table sys.streams$_apply_progress enable row movement;
Table altered.
And shrink the space.
SQL> alter table sys.streams$_apply_progress shrink space;
Table altered.
Then disable the row movement.
SQL> alter table sys.streams$_apply_progress disable row movement;
Table altered.
Check the size of stream apply table size.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='STREAMS$_APPLY_PROGRESS';
SUM(BYTES/1024/1024/1024)
——————–
.0723625
Finally start your replication definitely your replicat process achieve more performance compare to past.
GGSCI (GG-Target.com) 16> start rep2
Sending START request to MANAGER …
REPLICAT REP2 starting
GGSCI (GG-Target.com) 17> info rep2
REPLICAT REP2 Last Started 2019-08-09 22:22 Status RUNNING
INTEGRATED
Checkpoint Lag 00:17:31 (updated 00:00:00 ago)
Process ID 4984
Log Read Checkpoint File ./dirdat/BB000001929
2019-08-09 22:04:42.658077 RBA 54619
====================================
Tune TCPBUFSIZE and TCPFLUSHBYTES parameters:(performance tune for datapump network in GG)
The two RMTHOST parameters, TCPBUFSIZE and TCPFLUSHBYTES, are very useful for increasing the buffer sizes and network packets sent by Data Pump over the network
from the source to the target system. This is especially beneficial for high latency networks.
It is recommended that you set these parameters to an initial value of 1MB (1,048,576 bytes) or the calculated value, whichever is larger.
Calculate the bandwidth-delay product (BDP):
For example, if the network between the source and target databases is 125 megabits per second (Mbits) and the latency is 45ms the calculation would be as
follows: BDP = (125,000,000 / 8) * 0.045 = 703125bytes
Multiply the result by 4 to determine 4xBDP:
For example: 4xBDP = 703125 x 4 = 2812500
In this example, because the result is more than 1MB, set the values of TCPBUFSIZE and TCPFLUSHBYTES to 2812500
The parameters are set in the Data Pump parameter file. For example: RMTHOST ggtarget, MGRPORT 7810, TCPBUFSIZE 2812500, TCPFLUSHBYTES 2812500
Note: The maximum socket buffer size for non-Windows systems is usually limited by default.
======================================
vi ggsci_report.sh
#!/bin/bash
# Set environment variables for Oracle GoldenGate
export OGG_HOME=/home/oracle/ogghome
export PATH=$OGG_HOME:$PATH
# Define the email address to send the report to
EMAIL="barakadesb2486@gmail.com"
# Define a temporary file to hold the report
REPORT_FILE="/tmp/ggsci_report.txt"
# Run GGSCI commands and save the output to the report file
$OGG_HOME/ggsci << EOF > $REPORT_FILE
INFO ALL
EOF
# Send the report via email
mail -s "Oracle GoldenGate Processes Report" $EMAIL < $REPORT_FILE
# Clean up the temporary report file
rm -f $REPORT_FILE
chmod +x /home/oracle/ggsci_report.sh
*/10 * * * * /home/oracle/ggsci_report.sh
echo "This is a test email" | mail -s "Test Email" barakadesb2486@gmail
mail -s "AWR" -a sbrpt.html barakadesb2486@gmail.com <<< "sbrpt.html"
desc SBZ.GGCHECKPOINT
select group_name,seqno,rba,LOG_BSN,LOG_CSN from SBZ.GGCHECKPOINT;
SELECT sbzex,
86400 *(available_message_create_time - capture_message_create_time) latency_in_seconds
FROM GV$GOLDENGATE_CAPTURE;
select sum(bytes/1024/1024) from dba_segments where segment_name='STREAMS$_APPLY_PROGRESS';
Comments
Post a Comment