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 (>).
Database in Archive Mode, alter database add supplemental log data, GoldenGate installed at source and target.
Changes made based on GG documentation
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 22.214.171.124.2 OGGCORE_126.96.36.199.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 188.8.131.52.0 - 64bit Production PL/SQL Release 184.108.40.206.0 - Production CORE 220.127.116.11.0 Production TNS for Solaris: Version 18.104.22.168.0 - Production NLSRTL Version 22.214.171.124.0 - Production GGSCI 4> exit
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.
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.