Golden Gate Database Setup Part 2

In the first post, we did a bit on setting up the schemas for GG Replication both on the source and target databases.
Now we have to do a few things at the source database.

Connect as sqlplus '/ as sysdba'
--enable supplemental logging
alter database add supplemental log data
/
--switch logfile so that the next logfile has supplemental logging
alter system switch logfile
/
--check whether supplemental login was really enabled.As if you did not even bother to read output of step 1.
select supplemental_log_data_min from v$database
/

Now the fun part, login to golden gate on the source side
./ggsci


dblogin userid ggddlusr,password welcome123

add schematrandata SCOTT

The above will enable logging at schema level and makes your life easier compared to
ADD TRANDATA <table name>, as new tables are automatically picked up from the schema SCOTT.
By the way , the above command is supported on 11gR2 and needs PATCH 10423000.
To verify whether the above command worked
select * from table(logmnr$always_suplog_columns('owner in upper case','table in upper case'))

Or I have neatly scripted the below


declare
l_schema varchar2(20) := upper('PINNACLE') ;
l_table_name varchar2(200);
x_ctr number(5);
x_refcur sys_refcursor;
begin
open x_refcur for 'select table_name from dba_tables where owner='''||l_schema||''' order by table_name';
loop
fetch x_refcur into l_table_name;
exit when x_refcur%NOTFOUND;

execute immediate 'select count(*) from table(logmnr$always_suplog_columns(:1,:2))'
into x_ctr using l_schema,l_table_name ;
if x_ctr =0 then

dbms_output.put_line('Analyzing  '||l_table_name||' ..**ERROR**');
else
dbms_output.put_line('Analyzing  '||l_table_name||' ..OK');
end if;

end loop;
end;
/



As of now, this patch is not available on our platform HP-UX Itanium and we have to still use
ADD TRANDATA which is very painful.

But here is the script to generate the script

set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off
spool /tmp/scott.obey
select 'add trandata SCOTT.'||table_name
from dba_tables where owner = 'SCOTT' ;
spool off


Once the above file is generated , go back in ggsci and type

obey /tmp/scott.obey

By the way , a small note on the above step ---

1. The "add trandata" does something like "alter table <name> add supplemental log group ggs_<table name>_object id (comma seperated columns) ALWAYS"
Its just to give you an idea and the actual "alter table" sql executed behind the scenes might differ depending on the db versions etc

2. To drop the table level supplemental logging you could either do it from ggsci or sqlplus

ggsci> dblogin userid <id> password <pw>
ggsci> info trandata schema.table_name
ggsci> DELETE TRANDATA schema.table_name

or

sql> select * from dba_log_groups where table_name=upper('&table_name') and owner=upper('&owner');
sql> alter table <table name> drop supplemental log group <log group name>;

By the way, if you want to add table level logging for all columns, you use 'supplemental log data' instead of supplemental log group -

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
The valid options are ALL,PRIMARY,UNIQUE and FOREIGN KEY.
The above sql creates a supplemental log group starting with SYS_C instead of GGS_xxxx

To drop , you can use
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
OR
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG GROUP SYS_Cxxxxxx

/

**PLEASE NOTE, IF YOU HAD USED ADD SCHEMATRANDATA THERE WILL BE NO ENTRIES IN DBA_LOG_GROUPS TABLE.YOU WILL HAVE TO USE THE BELOW QUERY

select * from table(logmnr$always_suplog_columns('owner in upper case','table in upper case'))

**

We are now all done and go to the next step.