Skip to main content

How to setup Goldengate


How to Setup GoldenGate environment.

Scope

Bi-Directional Replication also known as Active-Active Replication, is a configuration where both databases are used as an active site and applications perform DML/DDL activities on both Databases. The Oracle Golden Gate (OGG) processes are configured to replicate the transactional data of both the source databases back and forth to each other. This document describes the steps to be followed to configure bidirectional replication between source and target.


Procedure


Database version: 12.1
GoldenGate Version: 12.2


I.                   Prepare source database for replication.

Step 1:

SQL> SHOW PARAMETER ENABLE_GOLDENGATE_REPLICATION.
If parameter value is set as “FALSE” then make it “TRUE”.

SQL>ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
Step 2: Check the current Stream Pool Size

SQL> SHOW PARAMETER STREAMS_POOL_SIZE;
If the value is set small, increase.

Note: Refer below calculations. Also, make sure to check available memory

For sizing, please refer to the Best Practices document. For Citi Global project, we have decided to use 4 Extract processes and 4 Replicat processes in Prod and just 2 in lower environments.

For Integrated Extracts, Set the STREAMS_POOL_SIZE initialization parameter for the database to the following value:
(SGA_MAX_SIZE * PARALLELISM) + 25% head room

For example, using the default values for the SGA_MAX_SIZE and PARALLELISM parameters:
( 1GB * 2 ) +0.25 = 2.25GB
STREAMS_POOL_SIZE = 2.25 G

For Integrated Replicat, replicat can use all available Streams pool area available. Just make sure it has additional room additional to the above calculated value for Integrated Extract.

So, we have sized Streams pool for 10GB in Prod, 5GB in PAT and 2GB in QA environment.


Step 3: Make sure below UNDO parameters are set,

UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=3600 

Step 4: Verify the database is in Archivelog mode

SQL> SELECT LOG_MODE FROM V$DATABASE;
 
LOG_MODE
------------------------------------
ARCHIVELOG

Step 5: Create a tablespace for Golden Gate admin DB User

SQL> CREATE BIGFILE TABLESPACE OGGTBS DATAFILE SIZE 100M AUTOEXTEND 
ON NEXT 10M MAXSIZE 30G;

Step 6: Enable Minimum Database-level Supplemental Logging and Force Logging

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

If any of the value is set as “No” then Alter as below,

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
SQL> ALTER DATABASE FORCE LOGGING;
 
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Step 7: Create a database user for Golden Gate Administration

Check if “Service_user” profile exists in Database:

SQL> SELECT PROFILE FROM DBA_PROFILES WHERE PROFILE=’SERVICE_USER’;

If no then create as below:

  CREATE PROFILE SERVICE_USER LIMIT
  SESSIONS_PER_USER 3000
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME UNLIMITED
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

Create user as below:

SQL> CREATE USER GGADMIN IDENTIFIED BY <Standard Schema Password> DEFAULT 
TABLESPACE OGGTBS profile SERVICE_USER;

Step 8: Grant system privileges for the GGADMIN account

SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE, ALTER ANY TABLE, 
ALTER SYSTEM TO GGADMIN;
 
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('GGADMIN');
 
SQL> GRANT DBA TO GGADMIN;
 
SQL> GRANT FLASHBACK ANY TABLE TO GGADMIN;
OR
SQL> GRANT FLASHBACK ON SCHEMA.TABLE TO GGADMIN;
** Please review for appropriate privileges needed for your solution if you have DB Vault or Redaction Exemption policy needs to be enabled.

Step 9: Install the UTL_SPADV package logging into the database as GGADMIN user.

sqlplus ggadmin/<password>
 
SQL> @$ORACLE_HOME/rdbms/admin/utlspadv.sql

Note: Ignore the table or view doesn’t exist error while executing above script.


II.                 Setup Golden gate Environment for Replication

Please check if HA Config & OEM agent install and monitoring configuration is complete.

Please Skip step 1, 2 & 3 if any other Goldengate replication enabled for other DB’s on same server

Step 1:
Setup parameters for Manager Process

Set the Database environment

Servername :<dbname>:/home/oracle >. oraenv
ORACLE_SID = [dbname] ? <dbname>

Server: > cd /acfs/app/goldengate/product/12.2.0.1/gg_1

Server:<dbname>:/acfs/app/goldengate/product/12.2.0.1/gg_1 >cat ./GLOBALS
ENABLEMONITORING
XAGENABLE

If “ENABLEMONITORING” and “XAGENABLE” is exists then ignore below step.

Server:<dbname>:/acfs/app/goldengate/product/12.2.0.1/gg_1 >vi  ./GLOBALS
ENABLEMONITORING
XAGENABLE

Connect to “ggsci” console and check the status of Manager

Server:<dbname>:/acfs/app/goldengate/product/12.2.0.1/gg_1 > ggsci
GGSCI > info all



Step 2:

Note: Below steps are only required once per Golden Gate Home.


GGSCI > Create Wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
 
GGSCI > Add CredentialStore
Credential store created in ./dircrd/.

Step 3:

Note: Below steps are only required once per Golden Gate Home.

GGSCI 1> stop mgr!
 
GGSCI 2> edit params mgr
PORT 7809
DYNAMICPORTLIST 9101-9356
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepDays 5
AutoStart ER *
--AutoRestart ER *, WaitMinutes 10, Retries 3, ResetMinutes 60
StartupValidationDelay 5
 
GGSCI 4> start mgr
GGSCI 5> info all

Step 4:

GGSCI > Alter CredentialStore Add User GGADMIN@<TNSAlias_For_DB_DBA_SERVICE> Password <password> Alias GGADMIN_<DB shortname>

** ‘useridalias’ created above is CASE SENSITIVE
*** For Alias, <DB Shortname>
**** <TNSAliasForDatabase> Use TNS alias for DBA Service to connect the database

Add schema trandata for all schema’s that are being replicated.

GGSCI > dblogin useridalias GGADMIN_<db>
GGSCI) > ADD SCHEMATRANDATA <Schema_Name> ALLCOLS

Verify if TRANDATA is added for all schema’s in the list

GGSCI) > INFO SCHEMATRANDATA <schemaname>

It should display like below
Schema level supplemental logging is enabled on schema <schemaname> for all columns.

Step 5:

Create subdirectory for trail file, under $GG_HOME/dirdat

Example: $ cd $GG_HOME/dirdat
     $ mkdir q31
Create parameter file for extract,
GGSCI > dblogin useridalias GGADMIN_<db>
GGSCI > edit params <extract name>
EXTRACT <Extract Name>
USERIDALIAS GGADMIN_H1
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
EXTTRAIL ./dirdat/h1/ea
DDL INCLUDE MAPPED &
    EXCLUDE OBJTYPE 'SEQUENCE'
DDLOPTIONS REPORT
 
TRANLOGOPTIONS EXCLUDEUSER GGADMIN IntegratedParams (max_sga_size 512)
TABLE LSP.*;
TABLE EXT_EMAIL.*;
TABLE COGNOS_REPORT.*;

Note: Please use same parameters as production if we setup lower DB replication

GGSCI > register extract <extract name> database

**Please note the SCN at which extract is registered.

GGSCI> add extract <extract name>, integrated tranlog, BEGIN NOW
GGSCI > add exttrail ./dirdat/<db>/ea, extract <extract name>, megabytes 1024
GGSCI > start extract <extract name>
GGSCI > view params <extract name>
GGSCI > info all

Step 6:
Create Pump Processes

Create subdirectory for trail file, under $GG_HOME/dirdat

Example: $ cd $GG_HOME/dirdat
     $ mkdir q31

GGSCI > edit params <pump name>
GGSCI > add extract <pump name>, exttrailsource ./dirdat/<db>/ea
GGSCI > add rmttrail ./dirdat/<db>/ab, extract <pump name>, megabytes 1024
GGSCI > start <pump name>



Target database instantiation

Duplicate Source database using RMAN to create target database

Note: Please refer Doc ID 1276058.1 or cloning doc for steps.

After completion of cloning check alert log for SCN number

Please make a note of this SCN until which database is duplicated.



III.             Configuring Data Delivery ( Replicat ) using Integrated Replicat


Note: After cloning is complete, if the target is a new host/server, repeat the steps to create Wallet, add credential store, add userid alias for GGADMIN account and setup manager process.
If everything was already setup then continue with step 1.

Step 1:

GGSCI > Alter CredentialStore Add User GGADMIN@<TNSAliasForDatabase> 
Password <password> Alias GGADMIN_<DB shortname>
 
GGSCI > dblogin useridalias GGADMIN_<db>
 
GGSCI >edit params <Replicat Name>
REPLICAT <replicat name>
USERIDALIAS GGADMIN_Q31F
DBOPTIONS INTEGRATEDPARAMS(parallelism 1,max_parallelism 1)
DiscardFile ./dirrpt/rablpq21.dsc, MEGABYTES 10, append
DDL INCLUDE MAPPED
MAPINVISIBLECOLUMNS
REPERROR(60, TRANSABORT, MAXRETRIES 10, DELAYSECS 10)
--include ./dirprm/heartbeat_replicat.inc
MAP LSP.*, TARGET LSP.*, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL);
MAP EXT_EMAIL.*, TARGET EXT_EMAIL.*, COMPARECOLS 
(ON UPDATE ALL, ON DELETE ALL);
MAP COGNOS_REPORT.*, TARGET COGNOS_REPORT.*, COMPARECOLS
 (ON UPDATE ALL, ON DELETE ALL);

Note: Please copy parameters from production database and change accordingly.

GGSCI>Add replicat <Replicat Name> Integrated exttrail ./dirdat/q31/ab
GGSCI> start replicat <Replicat Name> ATCSN <SCN number>
Note: <SCN number> will be same as noted SCN after cloning.


Testing:
Please create a test table in replication schema at Source database and check if it is reflected in target database. If it is creating then Uni-Directional replication is completed.

Please continue further steps for setup bi-directional replication.

Bi-directional replication setup

Step 1:

GGSCI > ADD SCHEMATRANDATA <Schemaname> ALLCOLS
Verify if TRANDATA is added for all schemas which are being replicated.

GGSCI > INFO SCHEMATRANDATA <schemaname>
It should display like below
Schema level supplemental logging is enabled on schema <schemaname> for all columns.


GGSCI > dblogin useridalias GGADMIN_<db>
 
GGSCI> edit params <extract name>
EXTRACT <extract name>
USERIDALIAS GGADMIN_Q31F
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
EXTTRAIL ./dirdat/q31/eb
DDL INCLUDE MAPPED &
    EXCLUDE OBJTYPE 'SEQUENCE'
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDEUSER GGADMIN IntegratedParams (max_sga_size 512)
include ./dirprm/heartbeat_extract.inc
TABLE LSP.*;
TABLE EXT_EMAIL.*;
TABLE COGNOS_REPORT.*;


GGSCI> register extract <extract name> database
GGSCI> add extract <extract name>, integrated tranlog, BEGIN NOW
GGSCI> add exttrail ./dirdat/<db>/eb, extract <extract name>, megabytes 1024
GGSCI> start extract <extract name>

Step 2:
Pump process

GGSCI>edit params <pump name>
EXTRACT <pump name>
USERIDALIAS GGADMIN_Q31F
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL ./dirdat/q31/ba
REPORTCOUNT EVERY 15 MINUTES, RATE
PASSTHRU
DDL INCLUDE MAPPED
include ./dirprm/heartbeat_pump.inc
TABLE LSP.*;
TABLE EXT_EMAIL.*;
TABLE COGNOS_REPORT.*;
 
GGSCI>add extract <pump name>, exttrailsource ./dirdat/<db>/eb
GGSCI>add rmttrail ./dirdat/<db>/ba, extract <pump name>, megabytes 1024

Note: Please make sure necessary directories have been created

GGSCI>start <pump name>

Step 3:

            Source database (Replicated DB)

GGSCI > dblogin useridalias GGADMIN_<db
GGSCI>edit params <Replicat name>
REPLICAT <replicat name>
USERIDALIAS GGADMIN_Q31P
DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
DiscardFile ./dirrpt/rbalpq31.dsc, MEGABYTES 10, append
DDL INCLUDE MAPPED
MAPINVISIBLECOLUMNS
--include ./dirprm/heartbeat_replicat.inc
MAP LSP.*, TARGET LSP.*, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL);
MAP EXT_EMAIL.*, TARGET EXT_EMAIL.*, COMPARECOLS 
(ON UPDATE ALL, ON DELETE ALL);
MAP COGNOS_REPORT.*, TARGET COGNOS_REPORT.*, COMPARECOLS 
(ON UPDATE ALL, ON DELETE ALL);
 
GGSCI>Add replicat <Replicat name> Integrated exttrail ./dirdat/<db>/ba
GGSCI>start replicat <Replicat name>

Comments

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

To fix huge lag between primary and DR using 12c feature: 12c Recover from service

Scope If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”. Prerequisites : Oracle Net connectivity is established between the physical standby database and the primary database. You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. The password files on the primary database and the physical standby database are the same. The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher. Start RMAN and connect as target to the physical standby database. Detailed Steps: Step 1: Stop the MRP process in standby database. If dg broker: Edit database <dbname> set state=’APPLY-OFF’; Step 2:   Shut down the standby database and mount the same. Shut immediate;   (srvctl stop database –d <dbname>)  ...