Thinking Out Loud

January 30, 2012

My Bi-directional Replication Using GoldenGate Adventure, Ep2

Filed under: 11g,GoldenGate,oracle — mdinh @ 6:25 am

Create GLOBALS file in GG_HOME:

> cat GLOBALS
 -- Specifies the name of the schema that contains the database objects that support DDL
 -- synchronization for Oracle
 GGSCHEMA gguser
-- Specifies a default checkpoint table
 CHECKPOINTTABLE ggs_checkpoint

MACRO:

According to the Reference Guide (11g Release 1 Patch Set 1 (11.1.1.1)), MACRO are valid for Extract and Replicat.

The following SYNTAX must be used in the order shown:

MACRO <macrochar><macro name>
PARAMS (<macrochar><paramname> [, …])
BEGIN
<macro body>
END;

My MACRO example:

/u01/app/ggs/dirmac
 > cat macrolib.mac
 MACRO #dbconnect
 BEGIN
 userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
 END;
MACRO #dbenv
 BEGIN
 SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
 SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.3/dbhome_1")
 END;
MACRO #ggenv
 BEGIN
 STATOPTIONS RESETREPORTSTATS
 REPORT AT 00:01
 REPORTROLLOVER AT 00:01
 REPORTCOUNT EVERY 60 MINUTES, RATE
 DISCARDROLLOVER AT 00:01
 END;

Enable table-level supplemental logging

This can be done with ADD TRANDATA or ADD SCHEMATRANDATA (additional requirements must be met since this is a New Feature)

ADD SCHEMATRANDATA:

SCHEMAUSER@oracle> create table t as select * from dba_objects;
GGSCI> ADD SCHEMATRANDATA schemauser

In the above example, I create table without any PK and added enabled supplemental logging for all tables in the schema, existing and new, using GGSCI.

The caveat for using ADD SCHEMATRANDATA is that the supplemental logging information will NOT be in USER_LOG_GROUPS and USER_LOG_GROUP_COLUMNS.

Instead, the function logmnr$always_suplog_columns is used to determine supplemental logging.

Verify supplemental logging:

SCHEMAUSER@oracle> select * from table(logmnr$always_suplog_columns('SCHEMAUSER','T'));

OWNER                          TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------
SCHEMAUSER                     T                              OWNER                                   1          1          1
SCHEMAUSER                     T                              OBJECT_TYPE                             6          6          6
SCHEMAUSER                     T                              TEMPORARY                              11         11         11
SCHEMAUSER                     T                              SECONDARY                              13         13         13
SCHEMAUSER                     T                              OBJECT_NAME                             2          2          2
SCHEMAUSER                     T                              NAMESPACE                              14         14         14
SCHEMAUSER                     T                              OBJECT_ID                               4          4          4
SCHEMAUSER                     T                              DATA_OBJECT_ID                          5          5          5
SCHEMAUSER                     T                              LAST_DDL_TIME                           8          8          8
SCHEMAUSER                     T                              SUBOBJECT_NAME                          3          3          3
SCHEMAUSER                     T                              CREATED                                 7          7          7
SCHEMAUSER                     T                              TIMESTAMP                               9          9          9
SCHEMAUSER                     T                              STATUS                                 10         10         10
SCHEMAUSER                     T                              GENERATED                              12         12         12
SCHEMAUSER                     T                              EDITION_NAME                           15         15         15

Noticed ALL the columns are logged because there is no PK for the table. So let’s add one and query the information again.

Add PK and Verify supplemental logging:

SCHEMAUSER@oracle> alter table t add constraint tpk primary key(object_id);
SCHEMAUSER@oracle> select * from table(logmnr$always_suplog_columns('SCHEMAUSER','T'));

OWNER                          TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------
SCHEMAUSER                     T                              OBJECT_ID                               4          4          4

For the project I am working on, I will be using SCHEMATRANDATA.

If the application creates a lot of staging or temporary tables without any PK, then SCHEMATRANDATA will generate a lot of REDO.

Please reference the section Additional considerations for using ADD SCHEMATRANDATA in the Reference Guide for more details.

ADD TRANDATA:

MDINH@oracle> create table t as select * from dba_objects;
GGSCI> ADD TRANDATA mdinh.*
WARNING OGG-00869 No unique key is defined for table T. All viable columns will be used to represent the key, but may not guarantee uniquene.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table MDINH.T.

Verify supplemental logging:

MDINH@oracle> select * from USER_LOG_GROUPS;
OWNER      LOG_GROUP_NAME       TABLE_NAME LOG_GROUP_TYPE  ALWAYS      GENERATED
---------- -------------------- ---------- --------------- ----------- --------------
MDINH      GGS_T_13904          T          USER LOG GROUP  ALWAYS      USER NAME
MDINH@oracle> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OWNER                         1 LOG
MDINH      GGS_T_13904          T          OBJECT_NAME                   2 LOG
MDINH      GGS_T_13904          T          SUBOBJECT_NAME                3 LOG
MDINH      GGS_T_13904          T          OBJECT_ID                     4 LOG
MDINH      GGS_T_13904          T          DATA_OBJECT_ID                5 LOG
MDINH      GGS_T_13904          T          OBJECT_TYPE                   6 LOG
MDINH      GGS_T_13904          T          CREATED                       7 LOG
MDINH      GGS_T_13904          T          LAST_DDL_TIME                 8 LOG
MDINH      GGS_T_13904          T          TIMESTAMP                     9 LOG
MDINH      GGS_T_13904          T          STATUS                       10 LOG
MDINH      GGS_T_13904          T          TEMPORARY                    11 LOG
MDINH      GGS_T_13904          T          GENERATED                    12 LOG
MDINH      GGS_T_13904          T          SECONDARY                    13 LOG
MDINH      GGS_T_13904          T          NAMESPACE                    14 LOG
MDINH      GGS_T_13904          T          EDITION_NAME                 15 LOG
15 rows selected.

Add PK and Verify supplemental logging:

MDINH@oracle> alter table t add constraint tpk primary key(object_id);
Table altered.
MDINH@oracle> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OWNER                         1 LOG
MDINH      GGS_T_13904          T          OBJECT_NAME                   2 LOG
MDINH      GGS_T_13904          T          SUBOBJECT_NAME                3 LOG
MDINH      GGS_T_13904          T          OBJECT_ID                     4 LOG
MDINH      GGS_T_13904          T          DATA_OBJECT_ID                5 LOG
MDINH      GGS_T_13904          T          OBJECT_TYPE                   6 LOG
MDINH      GGS_T_13904          T          CREATED                       7 LOG
MDINH      GGS_T_13904          T          LAST_DDL_TIME                 8 LOG
MDINH      GGS_T_13904          T          TIMESTAMP                     9 LOG
MDINH      GGS_T_13904          T          STATUS                       10 LOG
MDINH      GGS_T_13904          T          TEMPORARY                    11 LOG
MDINH      GGS_T_13904          T          GENERATED                    12 LOG
MDINH      GGS_T_13904          T          SECONDARY                    13 LOG
MDINH      GGS_T_13904          T          NAMESPACE                    14 LOG
MDINH      GGS_T_13904          T          EDITION_NAME                 15 LOG
15 rows selected.

Notice ALL columns logging is still enabled. In order to resolve this, DELETE TRANDATA and ADD TRANDATA.

GGSCI> info trandata mdinh.*
Logging of supplemental redo log data is enabled for table MDINH.T
GGSCI> delete trandata mdinh.t
Logging of supplemental redo log data disabled for table MDINH.T.
GGSCI> add trandata mdinh.*
Logging of supplemental redo data enabled for table MDINH.T.

MDINH@xgoldengate01> select * from USER_LOG_GROUP_COLUMNS;
OWNER      LOG_GROUP_NAME       TABLE_NAME COLUMN_NAME            POSITION LOGGIN
---------- -------------------- ---------- -------------------- ---------- ------
MDINH      GGS_T_13904          T          OBJECT_ID                     1 LOG

To  avoid having to DELETE and ADD TRANDATA, the following option can be added to EXTRACT parameter

DDLOPTIONS ADDTRANDATA, REPORT

When to use ADD TRANDATA for an Oracle source database

Use ADD TRANDATA only if you are not using the Oracle GoldenGate DDL replication feature.

If you are using the Oracle GoldenGate DDL replication feature, use the ADD SCHEMATRANDATA command to log the required supplemental data. It is possible to use ADD TRANDATA when DDL support is enabled, but only if you can guarantee one of the following:

  • You can stop DML activity on any and all tables before users or applications perform DDL on them.
  • You cannot stop DML activity before the DDL occurs, but you can guarantee that:
    • There is no possibility that users or applications will issue DDL that adds new tables whose names satisfy an explicit or wildcarded specification in a TABLE or MAP statement.
    • There is no possibility that users or applications will issue DDL that changes the key definitions of any tables that are already in the Oracle GoldenGate configuration.

ADD SCHEMATRANDATA ensures replication continuity should DML ever occur on an object for  which DDL has just been performed. For more information, see “ADD SCHEMATRANDATA” on page 81. You can use ADD TRANDATA even when using ADD SCHEMATRANDATA if you need to use the COLS option to log any non-key columns, such as those needed for FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.

When to use ADD SCHEMATRANDATA

ADD SCHEMATRANDATA should be used when DDL replication is active and DML is concurrent with DDL that creates new tables or alters key columns. It best handles scenarios where DML can be applied to objects very shortly after DDL is issued on them. ADD SCHEMATRANDATA causes the appropriate key values to be logged in the redo log atomically with each DDL operation, thus ensuring metadata continuity for the DML when it is captured from the log, despite any lag in Extract processing.

Retrieved from http://docs.oracle.com/cd/E22355_01/doc.11111/e21512.pdf on January 29,  2012

REFERENCE: >>Oracle GoldenGate Windows and UNIX Reference Guide

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.

January 19, 2012

Rotate Alert Log 11g

Filed under: 11g,oracle — mdinh @ 5:14 am

Tested on Solaris 10

> cat rotate_alert.sh
#!/bin/sh

DN=`dirname $0`
BN=`basename $0`

. /home/oracle/.common.conf > /dev/null
month=`date “+%m”`
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ ‘{print $3}’`
do
ORACLE_SID=$sid
. oraenv
diag_trace=`$ORACLE_HOME/bin/sqlplus -SL / <<EOF
set echo off ver off feedb off head off pages 0
select value from v\\$diag_info where name=’Diag Trace’;
exit;
EOF
`
alert_log=$diag_trace/alert_$sid.log
mv $alert_log $alert_log.$month
gzip -f $alert_log.$month
touch ${alert_log}
done
exit

NOTE: there is a back-tick (`) in the line after EOF

Blog at WordPress.com.