GoldenGate Prerequisites for Multiple Sources
Sometimes, we may only require a specific set of GoldenGate Prerequisites precisely instead of scrolling the entire documentation for each Database product. So to make our life easier here is a list of prerequisites that needs to be considered for each different databases before setting up GoldenGate.
FOR EXTRACTS
CHECK ARCHIVE LOG MODE
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG
CHECK SUPPLEMENTAL LOGGING
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
If the result is YES, the database meets the Oracle GoldenGate requirement.
If the result is NO,
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
FOR EXTRACTS & REPLICATS
ENABLE_GOLDENGATE_REPLICATION PARAMETER
SQL> alter system set enable_goldengate_replication=true scope=both sid=’*’;
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
Value should be True.
CREATE USERS AND TABLESPACES IN CDB+PDB
SQL> create tablespace TBS_GGS datafile ‘+DATAC1’ size 500m;
Tablespace created.
SQL> create user c##ggadmin identified by “Password_123” default tablespace TBS_GGS temporary tablespace temp;
User created.
SQL> alter user c##ggadmin quota unlimited on TBS_GGS;
User altered.
SQL> alter user c##ggadmin identified by “Password_123”;
User altered.
SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘c##ggadmin’,container=>’all’);
PL/SQL procedure successfully completed.
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> create tablespace TBS_GGS datafile ‘+DATAC1’ size 500m;
Tablespace created.
SQL> create user c##ggadmin identified by “Password_123” default tablespace TBS_GGS temporary tablespace temp;
User created.
SQL> alter user c##ggadmin quota unlimited on TBS_GGS;
User altered.
SQL> alter user c##ggadmin identified by “Password_123”;
User altered.
GRANT PRIVILEGES in CDB + PDB
grant connect,resource to c##ggadmin;
grant create session to c##ggadmin;
grant select any dictionary, select any table,SELECT ANY TRANSACTION to c##ggadmin;
grant create table, alter any table to c##ggadmin;
grant flashback any table to c##ggadmin;
grant execute on dbms_flashback to c##ggadmin;
grant execute on utl_file to c##ggadmin;
grant select on system.logmnr_session$ to c##ggadmin;
CHECK CONNECTIVITY
sqlplus /nolog connect c##ggadmin/Pasword_124@CONNECTION_STRING
You can find the complete oracle documentation about GoldenGate for Oracle prerequisites here
Provision GoldenGate for Postgres either through a Marketplace Image or by performing a custom installation on VM, the following steps must be completed as prerequisites for GoldenGate
#SET THESE PARAMETERS AT DB LEVEL — postgresql.conf
listen_addresses = *
wal_level = logical
max_replication_slots = 2
max_wal_senders = 1
track_commit_timestamp = on
–Please Tune the numbers according to your environment and restart the database
#CREATE USERS
create user ggadmin with password ‘Password_123’ login;
alter user ggadmin with replication;
#SET ENVIORNMENT VARIABLES
export PG_HOME=/var/lib/pgsql
export OGG_HOME=/opt/app/oracle19
export ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=$PG_HOME/lib:$OGG_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ODBCINI:$PATH
#ODBC.ini SAMPLE
[ODBC Data Sources]
PGSRC=DataDirect 7.1 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/u01/app/ogg
[PGSRC]
Driver=/u01/app/ogg/lib/ggpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=postgres
HostName=10.0.0.236
PortNumber=5432
You can find the complete oracle documentation about GoldenGate for Postgres prerequisites here
Provision GoldenGate for SQL Server either through a Marketplace Image or by performing a custom installation on VM, the following steps must be completed as prerequisites for GoldenGate
sudo vi /etc/passwd
#Edit the password file
#root:x:0:0:root:/root:/usr/sbin/nologin
To this one:
root:x:0:0:root:/root:/bin/bash
sudo -s
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
sudo ACCEPT_EULA=Y yum install mssql-tools
echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile
echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc
source ~/.bashrc
#Edit the passwd file and change the default value back to original
sudo vi /etc/passwd
root:x:0:0:root:/root:/usr/sbin/nologin
Connection Open : Make sure to open the port 1433 on the DB Side is open for remote connection.
SQL Server side Preparation.
#Create oggadmin User/Schema in SQL Server.
CREATE SCHEMA OGGADMIN;
#Privileges to oggadmin
Add sysadmin role to oggadmim user;
#Enable CDC on SQL Server
EXECUTE sys.sp_cdc_enable_db
You can find the complete oracle documentation about GoldenGate for SQL Server here
Modify the /etc/my.cnf file to add the following configurations.
vi /etc/my.cnf
# For the Extract process ll columns in the table has to be present in the binary log.
binlog_row_image=full
# To ensure DML statements are logged in binary format, the value of the logging format must be set to ROW.
binlog_format=row
# Disable or prevent DDL logging history table entries in the binlog
binlog-ignore-db=oggddl
# binlog path
log-bin=/usr/local/mysql/logs/log
# binlog index path
log-bin-index=/usr/local/mysql/logs/binlog.index
# enable binlog must set server-id, you can modify if there are multiple services
server-id=1
#Stop the mysql
sudo systemctl stop mysqld.service
#Start the mysql and check the status
sudo systemctl start mysqld.service
sudo systemctl status mysqld
# Create a database named OGGMYSQL
create database oggmysql;
## Create a user named GGADMIN for OGGMYSQL.
CREATE USER ‘ggadmin’@’localhost’ IDENTIFIED BY ‘Oggmysql#_123’;
GRANT ALL PRIVILEGES ON *.* TO ‘ggadmin’@’localhost’ WITH GRANT OPTION;
flush privileges;
show databases;
You can find the complete oracle documentation about GoldenGate for MYSQL prerequisites here