ON CALL DBA SUPPORT

– Database blog

Oracle Golden Gate Tutorial Part 2 – STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION

Posted by ssgottik on 12/07/2011

STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION

FOR NON RAC DATABASE( ACTIVE – PASSIVE)

STEP 0   ————— Hardware and O/S configuration

STEP 1   ————— Database Pre requisites

STEP 2   ————— GG Software Installation

STEP 3   ————— Preparing source database for replication

STEP 3a ————— GG DDL Support Replication

STEP 4   ————— Create Manager process @ Source

STEP 5   ————— Create Extract process @ Source

STEP 6   ————— Create Checkpoint table @ Target

STEP 7   ————— Create Replicate process @ Target

STEP 8   ————— Initial Data load (EXP/IMP)

STEP 9   ————— Start Extract

STEP 10 ————— Start Replicat

STEP 11————— TEST CASES

 

STEP

TASK

STEP 0:-

 

Hardware and O/S configuration

Now, let’s take a look at the Oracle database details which we will be using for our GOLDEN GATE configuration.( I am using VMWARE nodes)

SOURCE Database

Oracle Release: Oracle10 Release 2 – (10.2.0.5.0)
Machine Name: Source.gg.com
Operating System: Red Hat Linux 5
Oracle SID: DBSOURCE
Replication Schema SCOTT

TARGET Database

Oracle Release: Oracle10 Release 2 – (10.2.0.5.0)
Machine Name: Target.gg.com
Operating System: Red Hat Linux 5
Oracle SID: DBTARGET
Replication Schema SCOTT

Download the oracle Golden Gate software from below URL  and copy to both source and target servers

we need to download the required software from the Oracle E-Delivery web site

 

Step 1:–

 

Database Pre requisites

 

Need to be carried out at both source and target 

SQL> create tablespace ggs_data

    datafile ‘/oradata/<DBNAME>ggs_data01.dbf’ size 200m;

SQL> create user ggs_owner identified by ggs_owner

    default tablespace ggs_data

    temporary tablespace temp;

       grant connect, resource to ggs_owner;

      grant select any dictionary, select any table to ggs_owner;

      grant create table to ggs_owner;

      grant flashback any table to ggs_owner;

      grant execute on dbms_flashback to ggs_owner;

      grant execute on utl_file to ggs_owner;

      grant create any table to ggs_owner;

      grant insert any table to ggs_owner;

      grant update any table to ggs_owner;

      grant delete any table to ggs_owner;

      grant drop any table to ggs_owner;

    INIT_ORA change

  1. UNDO_MANAGEMENT=AUTO
  2. UNDO_RETENTION=86400

Step 2:–

 

GG Software Installation

 

Need to be carried out at both source and targetNote:

1 binary for SOURCE        

1 binary for TARGET REPLICAT

SOURCE

n  mkdir  /oradata/gg/

TARGET

n  mkdir /oradata/gg/

 set environment variables on both SOURCE and TARGET before starting the installation on all the three locations

 $mkdir /oradata/gg

$unzip V18157-01.zip

Archive:  V18157-01.zip

inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg

          $  . /ggsci

    — YOU WILL GET GG COMMAND PROMPT

                   GGSCI (hostname) 1> CREATE SUBDIRS

    — Issue the following command to exit GGSCI.    

                  GGSCI (hostname) 1> EXIT

STEP 3:-

 

Preparing source database for replication

 

Need to be carried out at  source Switch the database to archivelog mode: shutdown immediate startup mount 

alter database archivelog; 

alter database open;

 Enable minimal supplemental logging:

 SQL> alter database add supplemental log data;

 Prepare the database to support ddl replication.

 SQL> alter system set recyclebin=off scope=spfile;

 n  bounce the database

Step3a :-

 

Run scripts for creating all necessary objects for support DDL replication

 

Need to be carried out at  source

 Note – run the scripts as SYSDBA 

SQL> @/oradata/gg/marker_setup.sql 

 Marker setup script

 You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

 Enter GoldenGate schema name: GGS_OWNER

 Marker setup table script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

 MARKER TABLE

——————————-

OK

MARKER SEQUENCE

——————————-

OK

Script complete.

 SQL> @/oradata/gg/ddl_setup.sql 

 GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication…

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

 Enter GoldenGate schema name: GGS_OWNER

 You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

 Working, please wait …

Spooling to file ddl_setup_spool.txt

 Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.

 Working, please wait …

 RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

 Enter yes or no:yes

 DDL replication setup script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

 DDLORA_GETTABLESPACESIZE STATUS:

 Line/pos   Error

———- —————————————————————–

No errors  No errors

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

——————————————————————————–

/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log

 Analyzing installation status…

 STATUS OF DDL REPLICATION

——————————————————————————–

SUCCESSFUL installation of DDL Replication software components

 Script complete.

SQL>

 SQL> @/oradata/gg/role_setup.sql 

 GGS Role setup script

 This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change

the gg_role parameter to the preferred name. (Do not run the script.)

 You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

 Enter GoldenGate schema name:GGS_OWNER

Wrote file role_setup_set.txt

 PL/SQL procedure successfully completed.

 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

 where  is the user assigned to the GoldenGate processes.

 SQL> grant ggs_ggsuser_role to ggs_owner;

 Grant succeeded.

 SQL> @/oradata/gg/ddl_enable

 Trigger altered.

 SQL> @/oradata/gg/ddl_pin GGS_OWNER

 PL/SQL procedure successfully completed.

 PL/SQL procedure successfully completed.

 PL/SQL procedure successfully completed.

 Enable additional logging at the table level

 Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.

GGSCI (soruce hostname) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

 SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’RPD_PRC’ and object_type=’TABLE’;

GGSCI(source hostname) 6> add trandata <owner>.<tablename>

 

 

STEP 4:-

Create and start manager on the source and the destination.

 

 NEED TO BE CARRIED OUT AT SOURCE

[oracle@DBSOURCE gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

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

 GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

 GGSCI (DBSOURCE) 44> info all

 Program     Status      Group       Lag           Time Since Chkpt

 MANAGER     STOPPED

 GGSCI (DBSOURCE) 45> edit params mgr

 PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /oradata/gg/dirdat/ex, USECHECKPOINTS

 GGSCI (DBSOURCE) 46> info all

 Program     Status      Group       Lag           Time Since Chkpt

 MANAGER     STOPPED

 GGSCI (DBSOURCE) 47> dblogin USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

 GGSCI (DBSOURCE) 48> start manager

 Manager started.

                      STEP 5 :-         Create the extract group                NEED TO BE CARRIED OUT AT SOURCE

GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin nowEXTRACT added.

GGSCI (DBSOURCE) 50> add exttrail /oradata/gg/dirdat/lt, extract ext1

EXTTRAIL added.

GGSCI (DBSOURCE) 51> edit params ext1

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

–extract group– 

extract ext1 

–connection to database– 

userid ggs_owner, password ggs_owner 

–hostname and port for trail– 

rmthost 172.168.10.108, mgrport 7810

–path and name for trail– 

rmttrail /oradata/gg/dirdat/lt 

–DDL support 

DDL INCLUDE ALL

ddl include mapped objname SCOTT.*; 

–DML 

table SCOTT.*;

table SCOTT.EMP;

table SCOTT.DEPT;

table SCOTT.SALARY;

:wq

STEP 6:-

 

CREATE CHECKPOINT TABLE

 

NEED TO BE CARRIED OUT AT TARGET

 [oracle@DBTARGET gg]$ ./ggsci add checkpoint table to the destination database

 GGSCI (DBTARGET) 1> edit params ./GLOBAL

 and put following lines to the global parameter file:

 GGSCHEMA ggs_owner

 CHECKPOINTTABLE ggs_owner. Checkpoint

 GGSCI (DBTARGET) 2> dblogin userid ggs_owner password password

 Successfully logged into database.

 GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.checkpoint

 Successfully created checkpoint table ggs_owner.CHECKPOINT.

STEP 7:-

 

CREATE REPLICAT

NEED TO BE CARRIED OUT AT TARGET

GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oradata/gg/dirdat/lt,checkpointtable ggs_owner.checkpointGGSCI (DBTARGET) 4> EDIT PARAMS rep1

edit params rep1

–target database login –

userid ggs_owner, password ggs_owner

–file for dicarded transaction –

–discardfile /oradata/gg/discard/rep1_discard.txt, append, megabytes 10

–ddl support

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP

–Specify table mapping —

MAP SCOTT.*,       TARGET SCOTT.*;

MAP SCOTT.EMP,   TARGET SCOTT.EMP;

MAP SCOTT.DEPT,   TARGET SCOTT.DEPT;

MAP SCOTT.SALARY,   TARGET SCOTT.SALARY;

STEP 8:-

 

INITIAL DATALOAD

 

EXPORT: @ SOURCE

$ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=scott Scp from SOURCE and TARGET

$scp –p schema_gg.dmp 172.168.10.108:/oradata

 IMPORT: @ TARGET

$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=SCOTT

STEP 9:-

 

START EXTRACT

NEED TO BE CARRIED OUT AT  SOURCE

GGSCI (DBSOURCE) 45>  start EXT1

 Sending START request to MANAGER …

EXTRACT EXT2 starting

STEP 10:-

 

START REPLICAT

NEED TO BE CARRIED OUT AT TARGET  

GGSCI (DBTARGET) 10> start replicat rep1

 Sending START request to MANAGER …

REPLICAT REP1 starting

STEP 11:-

 

TEST CASES

CASE 1: CREATE TABLE IN THE SOURCE DATABASE 

SOURCE:create table SCOTT.Employee(

ID                 VARCHAR2(4 BYTE) ,

First_Name         VARCHAR2(10 BYTE),

Last_Name          VARCHAR2(10 BYTE),

Start_Date         DATE,

End_Date           DATE,

Salary             Number(8,2),

City               VARCHAR2(10 BYTE),

Description        VARCHAR2(15 BYTE)

);

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

TARGET:

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

 Case 2:CREATE TABLE AND INSERT DATA INTO IT

SOURCE:

Create table test_gg_1(

Id             number2(10),

Username varchar2(20)

);

Insert into test_gg_1 values(

1, ‘USER1’);

Insert into test_gg_1 values(

2,’USER2’);

Insert into test_gg_1 values(

3,’USER3’);

Insert into test_gg_1 values(

4,’USER4’);

Insert into test_gg_1 values(

5,’USER5’);

COMMIT;

SELECT * FROM SCOTT.TEST_GG_1;

TARGET :

SELECT * FROM SCOTT.TEST_GG_1;

 CASE3: UPDATE THE RECORD

SOURCE:

Update scott.test_gg_1 set username=’USERDBA’ where id=1;

Commit;

TARGET:

 Select * from scott.test_gg_1 where id=1

n  you will find the updated record in the target database

 CASE4: DELETE THE RECORD

SOURCE :

Delete from scott.test_gg_1 where id=4;

Commit;

TARGET:

Select * from scott.test_gg_1 where id=4;

n  you will not find the deleted record in the target database

CASE 5: TRUNCAT THE RECORDS

SOURCE:

Truncate table scott.test_gg_1;

Commit;

Select * from scott.test_gg_1;

TARGET :

Select * from scott.test_gg_1;

n  since the records are truncated in the source you will not find any records in the target.

CASE 6: CREATE A TRIGGER

SOURCE:

create or replace trigger SCOTT.emp_biu

BEFORE INSERT OR UPDATE

of salary

on employee

for each row

declare

v_error VARCHAR2(2000);

begin

if :new.salary > 10000

then

v_error:=:old.first_name||’ cannot have that much!’;

raise_application_error(-20999,v_error);

end if;

end;

/

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

TARGET:

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

 CASE 7: create table with primary Key

If you create any table with constrains, Golden gate will not replicated it automatically. We need to perform below steps:

  1. stop the extract
  2. stop the replicat
  3. take the export of the table which is having constrains.
  4. Copy it to target server.
  5. Import the exported dump.
  6. Map the table in the replicat parameter file
  7. Enable HANDLECOLLUSION parameter in the replicat parameter.
  8. Start the replicat
  9. Start the extract.

 

About these ads

22 Responses to “Oracle Golden Gate Tutorial Part 2 – STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION”

  1. GoldenGate said

    After MAP SCOTT.*, TARGET SCOTT.*; do you need to remap specific tables again?
    Thanks

    • ssgottik said

      hi,

      MAP SCOTT.* , TARGET SCOTT.* —–> This is for the tables which are getting added in near feature.

      Remap is for tables which are already present in the database.

      Thanks and Regards,
      Satish.G.S

  2. Very nice post. I just stumbled upon your blog and wished to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your rss feed and I hope you write again very soon!

  3. Ramesh Kumar said

    This post is extreamly good and the explanation is as clear as crystal.

    Ramesh

  4. Muhammad Mateen said

    awesome ……

  5. Mannoj S V said

    Can We replicate 2 schema with DDL??

  6. Alem said

    hi Ssgottik,

    When adding trandata, error is shown below:

    —-
    add trandata SCOTT.BONUS

    2012-05-12 04:02:20 WARNING OGG-00869 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

    Logging of supplemental redo log data is already enabled for table SCOTT.BONUS.
    —-

    Is it okay to ignore the warning? If no, could you please help me find a work around
    for this.. Thanks a lot.

    Regards,
    alem

    • ssgottik said

      you can ignore this error. KEYCOLS is used by GG , meaning that few coloums are used as the key identifiers for the same which dont have any constrains assigned.

      Thanks,
      Satish.G.S

  7. hiren said

    Thanks
    good work.

  8. Mathew said

    Was searching on internet for a implementation doc for GG. I should say that this is the best. Will update once i finish the implementation.

  9. Kumar said

    i was followed this document for installing golden gate, am getting error while starting the extract OGG-00403 . please help me to clear this error.

    Advance in thanks.

  10. Vinay said

    Thanks Satish for this article. Really helpful. At the first look, Goldengate appears to be much easier than Streams. (specifically when it comes to setup and the configurations).

  11. Sujala Kanuri said

    Subject Simplified !!
    I am so greatful to you for this post …
    Thank you

  12. aftab said

    Hi,
    This post seems to be lovely,it will be of great help to me.

    I have few questions though
    1. I have a RAC env at both source and target end,what are the possible things to change in that case?
    2. I have to replicat entire database,what are the possible changed we need to perform.

  13. Amit said

    such a good article. Can it be updated how to replicate DML changes from one RAC DB to another RAC DB. Where does the main differnece lie when it comes to RAC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: