Prepare 10g Database for OGG

Create GGS and GGS_MON Database Users

SQL> create tablespace ggs_tbs datafile ‘/u01/app/oracle/oradata/zwc/gg_tbs01.dbf‘ size 100M;

Tablespace created.

SQL> create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp;

User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user ggs_mon identified by ggs_mon default tablespace ggs_tbs temporary tablespace temp;

User created.

SQL> grant connect,resource to ggs_mon;

Grant succeeded.

Enable Database Level Supplemental Logging

SQL> select name,supplemental_log_data_min from v$database;

NAME      SUPPLEME
——— ——–
ZWC       NO

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select name,supplemental_log_data_min from v$database;

NAME      SUPPLEME
——— ——–
ZWC       YES

Enable Force Logging

SQL> select force_logging from v$database;

FOR
—
NO

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2083336 bytes
Variable Size             155190776 bytes
Database Buffers          117440512 bytes
Redo Buffers                6303744 bytes
Database mounted.
SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

SQL> select force_logging from v$database;

FOR
—
YES

Check Table-Level Supplemental Logging

SQL> select t.owner,
  2         t.tbl_cnt,
  3         s.sup_log_grp_cnt,
  4         t.tbl_cnt – s.sup_log_grp_cnt "Diff"
  5    from (select owner, count(*) tbl_cnt from dba_tables group by owner) t,
  6         (select owner, count(*) sup_log_grp_cnt
  7            from dba_log_groups
  8           group by owner) s
  9   where t.owner = s.owner(+)
 10     and t.owner in (‘HR‘, ‘OE‘, ‘PM‘);

OWNER    TBL_CNT SUP_LOG_GRP_CNT       Diff
—– ———- ————— ———-
HR             7
PM             2
OE            12

If you are planning to use sqlplus then you can use commands like:

alter database <table_name> add supplemental log data (all) columns;

alter database <table_name> add supplemental log data (primary key) columns;

For this demo,we will use Oracle GoldenGate command interface to add table level supplemental logging.The command from ggsci interface is "add trandata <table_name>".

[oracle@zwc ggs]$ sqlplus ggs

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 5 22:01:53 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool add_trandata.oby
SQL> set linesize 150 pagesize 0 feedback off
SQL> spool add_missing_trandata.oby
SQL> select ‘add trandata ‘ || t.owner || ‘.‘ || t.table_name stmt
  2    from (select owner, table_name from dba_tables) t,
  3         (select owner, table_name from dba_log_groups) s
  4   where t.owner = s.owner(+)
  5     and t.table_name = s.table_name(+)
  6     and s.table_name is null
  7     and t.owner in (‘HR‘, ‘OE‘, ‘PM‘);
add trandata HR.REGIONS
add trandata HR.LOCATIONS
add trandata HR.DEPARTMENTS
add trandata HR.JOBS
add trandata OE.WAREHOUSES
add trandata OE.ORDER_ITEMS
add trandata OE.ORDERS
add trandata OE.PRODUCT_INFORMATION
add trandata OE.PROMOTIONS
add trandata OE.SYS_IOT_OVER_52810
add trandata OE.SYS_IOT_OVER_52815
add trandata OE.PRODUCT_REF_LIST_NESTEDTAB
add trandata OE.SUBCATEGORY_REF_LIST_NESTEDTAB
add trandata HR.COUNTRIES
add trandata PM.ONLINE_MEDIA
add trandata PM.PRINT_MEDIA
add trandata OE.CUSTOMERS
add trandata HR.JOB_HISTORY
add trandata OE.PRODUCT_DESCRIPTIONS
add trandata OE.INVENTORIES
add trandata HR.EMPLOYEES
SQL> spool off

[oracle@zwc ggs]$ ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (zwc) 1> dblogin userid ggs password ggs
Successfully logged into database.

GGSCI (zwc) 2> obey ./diroby/add_missing_trandata.oby

GGSCI (zwc) 3> add trandata HR.REGIONS                                                                                                                               

Logging of supplemental redo data enabled for table HR.REGIONS.

GGSCI (zwc) 4> add trandata HR.LOCATIONS                                                                                                                             

Logging of supplemental redo data enabled for table HR.LOCATIONS.

GGSCI (zwc) 5> add trandata HR.DEPARTMENTS                                                                                                                           

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

GGSCI (zwc) 6> add trandata HR.JOBS                                                                                                                                  

Logging of supplemental redo data enabled for table HR.JOBS.

GGSCI (zwc) 7> add trandata OE.WAREHOUSES    

SQL> select t.owner,
  2         t.tbl_cnt,
  3         s.sup_log_grp_cnt,
  4         t.tbl_cnt – s.sup_log_grp_cnt "Diff"
  5    from (select owner, count(*) tbl_cnt from dba_tables group by owner) t,
  6         (select owner, count(*) sup_log_grp_cnt
  7            from dba_log_groups
  8           group by owner) s
  9   where t.owner = s.owner(+)
 10     and t.owner in (‘HR‘, ‘OE‘, ‘PM‘);

OWNER                             TBL_CNT SUP_LOG_GRP_CNT       Diff
—————————— ———- ————— ———-
HR                                      7               7          0
OE                                     12               8          4
PM                                      2               2          0

Create Tables for Heartbeat

SQL> create table ggs_mon.ggs_heartbeat(id number,ts date);

Table created.

SQL> insert into ggs_mon.ggs_heartbeat values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> create table ggs_mon.ggs_lagtime
  2  (id number,
  3  ts date,
  4  committime date,
  5  groupname varchar2(8),
  6  host varchar2(60),
  7  local_insert_time date);

Table created.