configuring goldengate

configuring goldengate

Pre req:  Install Goldengate software on both the servers

golden gate home: /u02/app/goldengate


Update bash_profile on both Source and Target side

[oracle@localhost ~]$ vi .bash_profile

if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

export ORACLE_SID=test
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export GRID_HOME=/u01/app/oracle/product/12.1.0/grid
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH:
export PATH=$GRID_HOME/bin:$PATH:
export PATH=$ORACLE_HOME/OPatch:$PATH:
PATH=$PATH:$HOME/bin
export PATH
export GGS_HOME=/u02/app/goldengate
alias ggn='cd ${GGS_HOME}'
alias ggsci='cd ${GGS_HOME}/ggsci'
export LD_LIBRARY_PATH=$GGS_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
".bash_profile" 25L, 684C                                     24,1          75%

Source db_name: TEST
target db_name: CLONE

1. check for force logging and supplemental logging enabled or not

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

SUPPLEME FORCE_LOGGING

-------- ---------------------------------------

NO       YES

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

SUPPLEME FORCE_LOGGING

-------- ---------------------------------------

YES      YES

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

2. check for archive log is enabled or not

Select log_mode from v$database;

If archive log is disabled, Enable it.

3.create a tablespace for golden gate on both sides

SOURCE SIDE:

SQL> CREATE TABLESPACE ogg_data LOGGING DATAFILE '/u01/app/oracle/oradata/TEST/datafile/ogg_data_01.dbf' size 2048m reuse autoextend on next 512m maxsize unlimited extent management local segment space management auto;

4.create goldengate user on both sides:

create user ogg_user identified by oracle default tablespace ogg_data temporary tablespace temp;

5. grant privileges to ogg_user:

SQL> GRANT CONNECT, RESOURCE TO ogg_user;

GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO ogg_user;

GRANT CREATE TABLE TO ogg_user;

GRANT FLASHBACK ANY TABLE TO ogg_user;

GRANT EXECUTE ON dbms_flashback TO ogg_user;

GRANT EXECUTE ON utl_file TO ogg_user;

GRANT CREATE ANY TABLE TO ogg_user;

GRANT INSERT ANY TABLE TO ogg_user;

GRANT UPDATE ANY TABLE TO ogg_user;

GRANT DELETE ANY TABLE TO ogg_user;

GRANT DROP ANY TABLE TO ogg_user;

GRANT ALTER ANY TABLE TO ogg_user;

GRANT ALTER SYSTEM TO ogg_user;

GRANT LOCK ANY TABLE TO ogg_user;

GRANT SELECT ANY TRANSACTION to ogg_user;

ALTER USER ogg_user QUOTA UNLIMITED ON ogg_data;

6. Authenticate user ogg_user with goldendate package:

SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg_user')

PL/SQL procedure successfully completed.

7. enable golden gate on both sides

SQL>  alter system set enable_goldengate_replication=true;

System altered.

8. edit manager paramter in both source and target

source:

cd $GGS_HOME (goldengate home)

then run

./ggsci

GGSCI (localhost.localdomain) 1> 

GGSCI (localhost.localdomain) 1> edit param mgr

PORT 7809

PurgeOldExtracts /u02/app/goldengate/dirdat/* UseCheckPoints, MinKeepHours 48

GGSCI (localhost.localdomain) 2> stop mgr

Manager is already stopped.

GGSCI (localhost.localdomain) 3> start mgr

Manager started.

GGSCI (localhost.localdomain) 4> info mgr detail

Manager is running (IP port localhost.localdomain.7809, Process ID 5516).

target:

cd $GGS_HOME

then run

./ggsci

GGSCI (clone.localdomain) 1>

GGSCI (clone.localdomain) 1> edit param mgr

PORT 7809

GSCI (clone.localdomain) 1> stop mgr

Manager is already stopped.

GGSCI (clone.localdomain) 2> start mgr

Manager started.

GGSCI (clone.localdomain) 3> info mgr detail

Manager is running (IP port clone.localdomain.7809, Process ID 5076).

9.Storing credentials on wallet. perform this step on both source and target:

source:

GGSCI (localhost.localdomain) 5> create wallet

 Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 6> add credentialstore

Credential store created in ./dircrd/.

GGSCI (localhost.localdomain) 7> alter credentialstore add user ogg_user@test password oracle alias ogg_user

Credential store in ./dircrd/ altered.

GGSCI (localhost.localdomain) 8> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ogg_user
  Userid: ogg_user@test

GGSCI (localhost.localdomain) 9> DBLogin UserIDAlias ogg_user

Successfully logged into database.

target:

GGSCI (clone.localdomain) 4> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (clone.localdomain) 8> add credentialstore

Credential store created in ./dircrd/.

GGSCI (clone.localdomain) 9> alter credentialstore add user ogg_user@clone password oracle alias ogg_user

Credential store in ./dircrd/ altered.

GGSCI (clone.localdomain) 10>  info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ogg_user
  Userid: ogg_user@clone

GGSCI (clone.localdomain) 11> DBLogin UserIDAlias ogg_user

Successfully logged into database.

uni-directional
#For sample extract, pump and replicat of uni-directional golden gatego to the link:
https://oracleinsightspk.blogspot.com/2017/10/sample-extractpump-and.html

Bi-directional
#For sample extract, pump and replicat of Bi-directional golden gatego to the link:
https://oracleinsightspk.blogspot.com/2017/10/bi-directional-golden-gate.html