Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Saturday, December 04, 2010

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

1- Download the GG software from the following location
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
I used the following for my platform (Windows 2003 Server)
Oracle GoldenGate v11.1.1.0.0 for Oracle 11g 64 bit on Windows XP, 2003, 2008(13 MB)

2- Download Oracle Client ( if you don't have already installed), test your client with some existing Oracle database. You don't need to install the client if you are using the same machine for Oracle Golden Gate where Oracle database is running in my case, my Oracle Databases 10g (source + target) were running on Windows 32bit and I had to install the OGG on Windows 64bit so I installed the Oracle Client on that machine with necessary modification in tnsnames.ora for Source and Target databases.
3- As OGG must be running on source and target do the following to get it installed on both systems.
i) Extract the Oracle GoldenGate zip file by winzip to a drive eg; E:
ii) Run the command shell and change directories to the new Oracle GoldenGate directory.
     C:\Documents and Settings\inam>e:
    
iii) From the Oracle GoldenGate directory, run the GGSCI program.
D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows x64 (optimized), Oracle 10 on Jul 28 2010 14:52:12
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (DB2) 1>
iv) In GGSCI, issue the following command to create the Oracle GoldenGate working directories.
GGSCI (DB2) 1> create subdirs
Creating subdirectories under current directory E:\OGG\GoldenGate
Parameter files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirprm: created
Report files                   D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirrpt: created
Checkpoint files             D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirchk: created
Process status files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirpcs: created
SQL script files            D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirsql: created
Database definitions files    D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdef: created
Extract data files             D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirdat: created
Temporary files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirtmp: created
Veridata files                D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver: created
Veridata Lock files            D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\lock: created
Veridata Out-Of-Sync files     D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\oos: created
Veridata Out-Of-Sync XML files D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\oosxml: created
Veridata Parameter files      D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\params: created
Veridata Report files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\report: created
Veridata Status files          D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\status: created
Veridata Trace files           D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirver\trace: created
Stdout files                   D:\Temp\GG\GG_SOURCE_HOMEDEV_ONSRV1\dirout: created

v) Issue the following command to exit GGSCI.
GGSCI (DB2) 2> Exit

Preparing Database for Golden Gate
We then need to create a database user which will be used by the GoldenGate Manager, Extract and Replicat processes. We create the user GGS_OWNER and grant it the required privileges.
SQL> create tablespace ggs_data
2 datafile ‘D:\oracle\product\10.2.0\oradata\HOMEDEV\HOMEDEV\GGS_DATA.DBF’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
2 default tablespace ggs_data
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to ggs_owner;
Grant succeeded.
SQL> grant select any dictionary, select any table to ggs_owner;
Grant succeeded.
SQL> grant create table to ggs_owner;
Grant succeeded.
SQL> grant flashback any table to ggs_owner;
Grant succeeded.
SQL> grant execute on dbms_flashback to ggs_owner;
Grant succeeded.
SQL> grant execute on utl_file to ggs_owner;
Grant succeeded.

 We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database

GGSCI (DB2) 4> DBLOGIN  USERID ggs_owner@homedev, PASSWORD ggs_owner
Successfully logged into database.

Enable Supplemental Logging
We need to enable supplemental logging at the database level otherwise we will get this error when we try to start the Extract process -

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_MIN,supplemental_log_data_pk, supplemental_log_data_ui FROM V$DATABASE;

Related Posts:
Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture 
Oracle GoldenGate Tutorial Part 3 - Manager process 
Oracle GoldenGate Tutorial Part 4 - Working with OGG 
Live Reporting with OGG 
Live Reporting with Data Pump -GG 
OGG Reference - Commands and Options 
SQL Server to SQL Server Replication using GG

No comments: