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
Post a Comment