Thinking Out Loud

January 21, 2012

My Bi-directional Replication Using GoldenGate Adventure, Ep1

Filed under: 11g,GoldenGate,oracle — mdinh @ 4:14 pm

I want to share my beginning adventure for configuring bi-directional replication using GoldenGate.

GoldenGate installation will not be covered since it is well documented and blogged about in many sites.

Note: When you see GGSCI>, this means the command was executed in GG Command Interpreter and not OS shell (>).

Prerequisite:

Database in Archive Mode,  alter database add supplemental log data, GoldenGate installed at source and target.

Parameter file:

Changes made based on GG documentation

undo_retention=86400
recyclebin=off

Environment:

Using an OBEY script with ENCRYPTED password to determine environment. More on this later.

> cd /u01/app/ggs/
> ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Solaris, x64, 64bit (optimized), Oracle 11g on Oct 4 2011 23:54:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI 1> obey chk_version.obey
GGSCI 2> dblogin userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
Successfully logged into database.
GGSCI 3> versions
Operating System:
SunOS
Version Generic_141445-09, Release 5.10
Node: <hostname>
Machine: i86pc
Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
GGSCI 4> exit

SYSDBA Privilege:

A SQL script was failing because there was no SYSDBA privilege and I was connected as “/ as sysdba”. It turns out that SYS was not in v$pwfile_users.

Most of the SQL used sys_context(‘userenv’,’ISDBA’), but one was using v$pwfile_users.

> grep -i PWFILE_USERS *.sql
marker_remove.sql: FROM v$pwfile_users
> grep ISDBA *.sql
ddl_remove.sql: SELECT sys_context('userenv','ISDBA') INTO :isdba FROM DUAL; -- use network method for isdba determination
ddl_setup.sql:-- FP 17971: use ISDBA as well to determine dba privs
ddl_setup.sql: SELECT sys_context('userenv','ISDBA') INTO :isdba FROM DUAL; -- use network method for isdba determination
remove_seq.sql: SELECT sys_context('userenv','ISDBA') INTO :isdba FROM DUAL; -- use network
sequence.sql: SELECT sys_context('userenv','ISDBA') INTO :isdba FROM DUAL; -- use network method for isdba determination

CREATE USER (gguser) with DDL Replication Configuration:

GRANT CREATE SESSION,ALTER SESSION to gguser identified by oracle_4u;
GRANT CONNECT,SELECT ANY DICTIONARY,FLASHBACK ANY TABLE,SELECT ANY TABLE to gguser;
GRANT SELECT on dba_clusters to gguser;
GRANT EXECUTE on DBMS_FLASHBACK to gguser;
GRANT SELECT ANY TRANSACTION to gguser;
GRANT EXECUTE on utl_file TO gguser;
EXEC dbms_goldengate_auth.grant_admin_privilege('gguser');
@@sequence
@@marker_setup.sql
@@ddl_setup.sql
@@role_setup.sql
@@ddl_enable.sql
@@ddl_pin gguser
alter user gguser default tablespace ggs_data;
revoke unlimited tablespace from gguser; 
alter user gguser quota unlimited on ggs_data;

No DBA role was granted to GGUSER and it turns out that GGUSER had DBA role from marker_setup.sql: GRANT CONNECT, RESOURCE, DBA TO &gg_user;

Create ENCKEYS file in GG_HOME:

ENCKEYS is a file containing key values used to encrypt password. In the example: keygen is used to create 2 keys having 128 bit.

keygen 128 2 > ENCKEYS

Add KeyName to ENCKEYS  file:

Open ENCKEYS to add KeyName. You can provide any KeyName for the KeyValue. In the example, the KeyValue were named key1 and key2.

## KeyName KeyValue
key1 0x59038C335C69746FE0CA751368AB294E
key2 0x38374124C2D3AD74DFB443065D08160F

Generate Encrypted Password:

GGSCI> encrypt password oracle_4u, encryptkey key1
AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH

Test Encrypted Password:

It’s now possible to use encrypted versus plain text password.

GGSCI 1> dblogin userid gguser, password oracle_4u
Successfully logged into database.
GGSCI 2> dblogin userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH,encryptkey key1
Successfully logged into database.

OBEY script:

In GoldenGate, an obey script is equivalent to a SQL script. One caveat is when an obey script calls another obey script, allownested  must be used.

Here is an example of a nested obey script.

> cat add_config_ggs01.obey
dblogin userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
allownested
versions
obey add_eggs01.obey
obey add_rggs02.obey
exit

Updated Feb 21, 2012: Don’t revoke unlimited tablespace from gguser since the DDL replication is performed by gguser. I was trying to be too creative.

Advertisements

1 Comment »

  1. […] First, I made an amendment to by first post – My Bi-directional Replication Using GoldenGate Adventure, Ep1 […]

    Pingback by My Bi-directional Replication Using GoldenGate Adventure, Ep5 « Thinking Out Loud — February 23, 2012 @ 4:03 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: