Oracle GoldenGate 12c Installation And Configuration -2

Goldengate is a middleware product of Oracle which has a high-performance for real-time data transfer. Goldengate is most popular replication software especially for real-time data replication. Because goldengate is the most successful product in the CDC (change data capture) subject.

Goldengate may be used in disaster recovery solutions also. But Oracle already offers a disaster recovery solution with Dataguard. Goldengate is hybrid structure replication software, so it can replicate not only Oracle-to-Oracle replication, but also cross-platform or database.

It is also possible to install a real-time data warehouse system with Goldengate. you can use following Types of replication with GoldenGate.

One-to-one (source to target) ( Unidirectional )

One-to-many (one source to many targets) ( Broadcasting )

Many to one (hub and spoke) ( Consolidation )

Cascading

Bi-directional (active active)

• Bi-directional (active passive)

Download Oracle GoldenGate from oracle downloads or edelivery.com with following link.https://download.oracle.com/otn/goldengate/12301/123014_fbo_ggs_linux_x64_shiphome.zip

Unzip file and connect to VNC server, open a terminal and run runinstaller from unzipped directory. cd fbo_ggs_Linux_x64_shiphome/Disk1/

./runInstaller

Click Next button, and You just need to select database version in the next step. and Click install button.

You should install Goldengate both source ( extraction ) and target ( replication ). So what i have done in source should do target site also.

Actually goldengate installation is very simple, there was not already GUI prior Goldengate 12c. We had just extracted installation file and started to use with typing ggsci command.

 1. First of all we need to create tablespace, user and grant permission for the goldengate user.

CREATE USER GOLDENGATE IDENTIFIED BY gg_123 DEFAULT TABLESPACE GOLDENGATE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for GOLDENGATE 
GRANT RESOURCE TO GOLDENGATE;
GRANT CONNECT TO GOLDENGATE;
GRANT SELECT_CATALOG_ROLE TO GOLDENGATE;
ALTER USER GOLDENGATE DEFAULT ROLE ALL;
-- 10 System Privileges forGOLDENGATE 
GRANT CREATE SESSION TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY TABLE TO GOLDENGATE;
GRANT UNLIMITED TABLESPACE TO GOLDENGATE;
-- 1 Tablespace Quota for GOLDENGATE 
ALTER USER GOLDENGATE QUOTA UNLIMITED ON TSGOLDENGATE;
-- 2 Object Privileges for GOLDENGATE 
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO GOLDENGATE;
GRANT FLASHBACK ON SYS.USER_SOURCE TO GOLDENGATE;
exec dbms_goldengate_auth.grant_admin_privilege(‘GOLDENGATE’);

CREATE TABLESPACE goldengate DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2. Create same user on ASM instance

[oracle@admin]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 1 09:43:07 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user GOLDENGATE identified by gg_123;
User Created
SQL> grant sysdba to GOLDENGATE;
User Granted

3. Add ASM tns entry to tnsnames.ora file

[oracle@admin]$ cat tnsnames.ora

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = Host_name.dev.local)(PORT = 1521))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Host_name.dev.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.Dev.local)
)
)

ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Host_name.dev.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)

4. Please make sure database in archive log mode

sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode 
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 217747
Next log sequence to archive 217749
Current log sequence 217749

If Database is Noarchive Mode

SQL>shutdown
SQL> startup mount;
Database Mounted

SQL>alter database archivelog;
Database altered.

SQL>alter database open;
Database altered.

5. Enable Force and Supplemental Log

alter database force logging;
Database altered.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS;
Database altered.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(UNIQUE) COLUMNS;
Database altered.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(FOREIGN KEY) COLUMNS;
Database altered.

6. Enable Goldengate Parameter ( Database 12c Version )

alter system set enable_goldengate_replication=true scope=both;

Database altered.

7. Download Oracle GoldenGate from oracle downloads or edelivery.com

https://download.oracle.com/otn/goldengate/12301/123014_fbo_ggs_Linux_x64_shiphome.zip

8. Unzip file and run runinstaller from unzipped directory.

cd fbo_ggs_Linux_x64_shiphome/Disk1/

./runInstaller

Install Next option, You just need to select database version.

9. Before run goldengate, set environment

[oracle@goldengate]$ . oraenv

ORACLE_SID = [oracle] ? orcl

The Oracle base has been set to /u01/app/oracle

10. From the installation directory, you can see some important directories, starting with dir*. For example dirprm is parameter files directory, dirrpt is reports ( log ) directory, dirsql is sql directory and etc.

11. From the installation path you can run goldengate to configure

[oracle@goldengate]$ ./ggsci

12. Check what is running on default when Goldengate is started

GGSCI 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER    RUNNING

13. Configuring Manager ( Apply this steps on Source and Target DB site )

Stop Manager

GGSCI 4> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

Modify Manager Parameter File

GGSCI 6> edit params mgr

Paste Below parameters to Purge, Auto Restart and Lag Control, save and exit

PORT 7809

--Deletion interval of old trail file  

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

-- Autorestart Parameters
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 30
--AUTOSTART ER *

-- Lag control parameters
LAGCRITICALMINUTES 20
LAGINFOMINUTES 20
LAGREPORTMINUTES 20

Start Manager

GGSCI 7> start mgr
Manager started.

You should run all steps on source goldengate and target goldengate.

Once you completed installation of Goldengate, you can start performing the Replication using Goldengate.

We can replicate the records of the transactions made on the source database to the target databases with Oracle GoldenGate, we can replicate in real time or on a delayed basis. This replication may involve all database operations performed in the source database, but can also be performed as a replication of only one seqeunce, table or schema.

One of the key features of Oracle GoldenGate is its hybrid structure. As the source database can be Oracle, the target database can be MS-SQL Server or vice versa. Thanks to its Oracle GoldenGate hybrid structure, it can support replication in cross-platform or database. Examples include; Oracle, MS-SQL, Postgre SQL, MySQL and IBM DB2.

Oracle GoldenGate is installed separately to the source and target servers, and the configurations of the servers can be different. While the processes on the source server are called Extract and Pumper , the processes on the target server are called Collector and Replicat . In addition to these, both sides have the Manager process. You can see this process architecture like following picture.

Manager: As the name implies, this process is main process and the management process of Oracle GoldenGate. Works on both source and destination servers. The configuration of GoldenGate is managed via this process. Sample; The trail files produced by the Extract process and reporting are under the management of this processes . In order for Extract, Pumper and Replicat processes to work, the Manager must be run. The length of time the trail files produced by the Extract process are stored is checked through the Manager process.

Extract: This process is running on the source database is responsible for capturing the records of all transactions performed in the database. These records are collected primarily from the Online Redolog (removed from RBA numbers) files or from archive files.

Pumper: The task of this process is to send the trail files generated by the Extract process to the target server via the defined 7809 port (TCP / IP) on the Manager service.

Replicat: Replicat process , as the name suggests, is responsible for processing the trail files sent by the Pumper process running on the source server to the target database. You can run incoming trail files in real time (online) or you can operate them in a delayed manner by means of parameters.

Collector: The Collector process is a background service of GoldenGate and only works on the target server. This process is responsible for processing the posted trail files and bringing them into the format that the Replicat process can use.