Install



mkdir /oracle/app/oracle/product/gg

GGATE_HOME=/oracle/app/oracle/product/gg
export GGATE_HOME
cd $GGATE_HOME

[oracle@localhost gg]$ unzip /oracle/fbo_ggs_Linux_x64_112100_ora11g_64bit.zip
Archive:  /oracle/fbo_ggs_Linux_x64_112100_ora11g_64bit.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.0.pdf 
  inflating: Oracle GoldenGate 11.2.1.0 README.txt 
[oracle@localhost gg]$

tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@localhost gg]$ ls
bcpfmt.tpl                 ddl_trace_on.sql                    libicuuc.so.38
bcrypt.txt                 defgen                              libxerces-c.so.28
cfg                        demo_more_ora_create.sql            libxml2.txt
chkpt_ora_create.sql       demo_more_ora_insert.sql            logdump
cobgen                     demo_ora_create.sql                 marker_remove.sql
convchk                    demo_ora_insert.sql                 marker_setup.sql
db2cntl.tpl                demo_ora_lob_create.sql             marker_status.sql
ddl_cleartrace.sql         demo_ora_misc.sql                   mgr
ddlcob                     demo_ora_pk_befores_create.sql      notices.txt
ddl_ddl2file.sql           demo_ora_pk_befores_insert.sql      oggerr
ddl_disable.sql            demo_ora_pk_befores_updates.sql     OGG_WinUnix_Rel_Notes_11.2.1.0.0.pdf
ddl_enable.sql dirjar Oracle GoldenGate 11.2.1.0 README.txt
ddl_filter.sql             dirprm                              params.sql
ddl_nopurgeRecyclebin.sql  emsclnt                             prvtclkm.plb
ddl_ora10.sql              extract                             pw_agent_util.sh
ddl_ora10upCommon.sql      fbo_ggs_Linux_x64_ora11g_64bit.tar  remove_seq.sql
ddl_ora11.sql              freeBSD.txt                         replicat
ddl_ora9.sql               ggcmd                               retrace
ddl_pin.sql                ggMessage.dat                       reverse
ddl_purgeRecyclebin.sql    ggsci                               role_setup.sql
ddl_remove.sql             help.txt                            sequence.sql
ddl_session1.sql           jagent.sh                           server
ddl_session.sql            keygen                              sqlldr.tpl
ddl_setup.sql              libantlr3c.so                       tcperrs
ddl_status.sql             libdb-5.2.so                        ucharset.h
ddl_staymetadata_off.sql   libgglog.so                         ulg.sql
ddl_staymetadata_on.sql    libggrepo.so                        UserExitExamples
ddl_tracelevel.sql         libicudata.so.38                    usrdecs.h
ddl_trace_off.sql          libicui18n.so.38                    zlib.txt


[oracle@localhost gg]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
[oracle@localhost gg]$

small fix :

[oracle@localhost gg]$ export LD_LIBRARY_PATH=$GGATE:$ORACLE_HOME/lib:
[oracle@localhost gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  1 2012 00:55:59

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



GGSCI (localhost.localdomain) 1>

Create the necessary working directories for GG.




GGSCI (localhost.localdomain) 1> create subdirs

Creating subdirectories under current directory /oracle/app/oracle/product/gg


Parameter files                /oracle/app/oracle/product/gg/dirprm: already exists
Report files                   /oracle/app/oracle/product/gg/dirrpt: created
Checkpoint files               /oracle/app/oracle/product/gg/dirchk: created
Process status files           /oracle/app/oracle/product/gg/dirpcs: created
SQL script files               /oracle/app/oracle/product/gg/dirsql: created
Database definitions files     /oracle/app/oracle/product/gg/dirdef: created
Extract data files             /oracle/app/oracle/product/gg/dirdat: created
Temporary files                /oracle/app/oracle/product/gg/dirtmp: created
Stdout files                   /oracle/app/oracle/product/gg/dirout: created


GGSCI (localhost.localdomain) 2>


Destination-side

We must repeat all the above steps on the destination side.

Source database

The GoldenGate software having been installed successfully, we must prepare the source database for replication.

    Switch the database to archivelog mode:

shutdown immediate
startup mount
alter database archivelog;
alter database open;

    Enable minimal supplemental logging:

Enabling database-level supplemental logging

Oracle GoldenGate requires enabling database-level supplemental logging.

1.  Log in to SQL*Plus as a user with ALTER SYSTEM privilege, and then issue the following
                           command to enable minimal supplemental logging at the database level. This logging
                           is required to process updates to primary keys and chained rows.


                           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2.  To start the supplemental logging, switch the log files.

                           ALTER SYSTEM SWITCH LOGFILE;

3.  Verify that supplemental logging is enabled at the database level with this command:

                           SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES


    Prepare the database to support ddl replication (optional).

    a) Turn off recyclebin for the database . . .

alter system set recyclebin=off scope=spfile;

. . . and bounce it.

Create schema for ddl support replication . . .

create user ggate identified by qwerty default tablespace GG_TBS temporary tablespace temp;

     . . . and grant the necessary privileges to the new user..

cd $GGATE
    
grant connect,resource,unlimited tablespace to ggate;
grant execute on utl_file to ggate;

        Create the GLOBALS parameter file either using vi or ggsci like

        From the Oracle GoldenGate installation location, run GGSCI and enter the following
        ommand

                            EDIT PARAMS ./GLOBALS

                                      NOTE     The ./ portion of this command must be used, because the GLOBALS file must
                                               reside at the root of the Oracle GoldenGate installation file.


                            Specify the name of the DDL schema by adding the following parameter to the GLOBALS file.

                             GGSCHEMA   ggate

    Exit all Oracle sessions, including those of SQL*Plus, those of business applications,
    those of the Oracle GoldenGate processes, and those of any other software that uses
    Oracle. Prevent the start of any new sessions.


    Run scripts for creating all necessary objects for support ddl replication:
    Run SQL*Plus and log in as a user that has SYSDBA privilege. This privilege is required
    to install the DDL trigger in the SYS schema, which is required by Oracle. All other DDL
    objects are installed in the schema that you created in step 1.

    Run the marker_setup.sql script.

    @marker_setup.sql

    ❍    Supply the name of the Oracle GoldenGate schema when prompted, and then press Enter to execute the script.

    ❍   This script installs support for the Oracle GoldenGate DDL marker system.

Enter Oracle GoldenGate schema name: ggate


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>

    Run the ddl_setup.sql script. You are prompted to specify the name of the DDL schema from step 1.

    @ddl_setup.sql



Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/prim/PRIM/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>

Run the role_setup.sql script.

@role_setup.sql

❍   At the prompt, supply the DDL schema name.

❍   The script drops and creates the role that is needed for DDL synchronization, and
         it grants DML permissions on the Oracle GoldenGate DDL objects.

Enter GoldenGate schema name:ggate
ecrit file role_setup_set.txt

Procedure PL/SQL terminee avec succes.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

Grant the role that was created (default name is GGS_GGSUSER_ROLE to all Oracle GoldenGate Extract users.
You may need to make multiple grants if the processes have different user names.


grant GGS_GGSUSER_ROLE to ggate;

Run the ddl_enable.sql script to enable the DDL trigger.

@ddl_enable.sql

Declencheur modifie.

To install and use the optional performance tool
To improve the performance of the DDL trigger, make the ddl_pin script part of the database
startup. It must be invoked with the Oracle GoldenGate DDL user name, as in:

@ddl_pin ggate

This script pins the PL/SQL package that is used by the trigger into memory. If executing
this script from SQL*Plus, connect as SYSDBA from the Oracle GoldenGate installation
directory. This script relies on the Oracle dmbs_shared_pool system package, so install that
package before using ddl_pin.

Create test schemas for replication.

I will create a replication from schema sender to schema receiver (the receiving schema can be on the same database or on another).

a) Source database:

create user sender identified by qwerty default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to sender;

b) Destination database:

create user receiver identified by qwerty default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to receiver;

Replication

We’re going to create the simplest replication without the GG data pump (we can add it later).
Our goal is to create ddl and ddl replication from the sender schema on the source database to receiver schema on the destination.

Replication also works if you’re using only one database. This is replication between schemas.

    Create and start manager on the source and the destination.

    Source:

./ggsci
    
    GGSCI (localhost) 4> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    MANAGER     STOPPED
    
    GGSCI (localhost) 6> edit params mgr
    
PORT 7809

this edit param mgr creates a file $GG_HOME/dirprm/mgr.prm containing PORT 7809
    
    GGSCI (localhost) 7> start manager
    
    Manager started.

    We can check status of our processes:
    GGSCI (localhost) 8> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    
    MANAGER     RUNNING

when can check at the OS level for the manager process

GGSCI (dbserver01) 1> shell pgrep mgr -lf

10 async/mgr
3156 /usr/libexec/gdmgreeter
14305 ./mgr PARAMFILE /oracle/app/oracle/product/gg/dirprm/mgr.prm REPORTFILE /oracle/app/oracle/product/gg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

  Create the extract group on the source side:

    GGSCI (localhost) 1> add extract ext1, tranlog, begin now
    
    EXTRACT added.
    
    GGSCI (localhost) 2> add exttrail /oracle/app/oracle/product/gg/dirdat/lt, extract ext1
    
    EXTTRAIL added.
    
    GGSCI (localhost) 3> edit params ext1

    Add the following lines to the new parameter file for our extract:

-- Set some envs
SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "PRIM")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

--extract group--
extract ext1
--connection to database--
userid ggate, password qwerty
--hostname and port for trail--
rmthost dbserver02, mgrport 7809
--path and name for trail--
rmttrail /oracle/app/oracle/product/gg/dirdat/lt
--DDL support
ddl include mapped objname sender.*;
--DML
table sender.*;

    We can check our processes again:
    GGSCI (localhost) 6> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    
    MANAGER     STOPPED
    
    EXTRACT     STOPPED     EXT1        00:00:00      00:10:55

  Create replicat on the destination side:

    [oracle@db2 gg]$ cd $GG_HOME
    
    [oracle@db2 gg]$ ./ggsci
    
    add checkpoint table to the destination database
    
    GGSCI (db2) 1> edit params ./GLOBALS
    
    and put following lines to the global parameter file:
    
    GGSCHEMA ggate
    CHECKPOINTTABLE ggate.checkpoint
    
    ~
    
    GGSCI (db2) 2> dblogin userid ggate, password qwerty
    
 
    Successfully logged into database.
    
    GGSCI (db2) 3> add checkpointtable ggate.checkpoint
    
    Successfully created checkpoint table ggate.checkpoint.

    

  Create replicat group:

    
    GGSCI (db2) 4> add replicat rep1, exttrail /oracle/app/oracle/product/gg/dirdat/lt,checkpointtable ggate.checkpoint
    
    REPLICAT added.
    

  Create parameter file for replicat:

    
    GGSCI (db2) 5> edit params rep1

    And put following lines in the parameter file:

--Replicat group --
replicat rep1
--source and target definitions
ASSUMETARGETDEFS
--target database login --
userid ggate, password qwerty
--file for dicarded transaction --
discardfile /oracle/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
--ddl support
DDL
--Specify table mapping ---
map sender.*, target receiver.*;

  Start extract and replicat:

    Source:
    GGSCI (dbserver01) 14> start extract ext1

    GGSCI (dbserver01) 5> shell ps -elf | grep extract 

0 S oracle   17276 16727  0  80   0 - 50046 hrtime 11:59 ?        00:00:00 /oracle/app/oracle/product/gg/extract PARAMFILE /oracle/app/oracle/product/gg/dirprm/ext1.prm REPORTFILE /oracle/app/oracle/product/gg/dirrpt/EXT1.rpt PROCESSID EXT1 USESUBDIRS
0 S oracle   17312 17260  0  80   0 - 15961 wait   11:59 pts/3    00:00:00 sh -c ps -elf | grep extract
0 S oracle   17314 17312  0  80   0 - 15813 pipe_w 11:59 pts/3    00:00:00 grep extract


    Destination:
    GGSCI (dbserver02) 5> start replicat rep1
    Check all processes.

    Source:

    GGSCI (localhost) 8> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    
    MANAGER     RUNNING
    
    EXTRACT     RUNNING     EXT1        00:00:00      00:00:05

    Destination:

    GGSCI (localhost) 8> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    
    MANAGER     RUNNING
    
    REPLICAT    RUNNING     REP1        00:00:00      00:00:00

    Our replication has been successfully created.

6. Checking

Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.

    Source database:

create table sender.test_tab_1 (id number,rnd_str varchar2(12));
insert into sender.test_tab_1 values (1,'test_1');
commit;

    Destination database:

select * from receiver.test_tab_1;
    
    ID RND_STR
    ---------- ------------
    1 test_1

Our GoldenGate DDL and DML replication is now working. The table was created on the destination side and data were replicated.