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
