GOLDEN GATE INSTALLATION

$ unzip V18159-01.zip
$tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
$ export PATH=$PATH:/u01/oracle/ggs

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/oracle/ggs

$ ggsci

GGSCI (redhat346.localdomain) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/oracle/product/11.2.0/dbhome_1

Parameter files /u01/oracle/ggs/dirprm: created
Report files /u01/oracle/ggs/dirrpt: created
Checkpoint files /u01/oracle/ggs/dirchk: created
Process status files /u01/oracle/ggs/dirpcs: created
SQL script files /u01/oracle/ggs/dirsql: created
Database definitions files /u01/oracle/ggs/dirdef: created
Extract data files /u01/oracle/ggs/dirdat: created
Temporary files /u01/oracle/ggs/dirtmp: created
Veridata files /u01/oracle/ggs/dirver: created
Veridata Lock files /u01/oracle/ggs/dirver/lock: created
Veridata Out-Of-Sync files /u01/oracle/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/oracle/ggs/dirver/oosxml: created
Veridata Parameter files /u01/oracle/ggs/dirver/params: created
Veridata Report files /u01/oracle/ggs/dirver/report: created
Veridata Status files /u01/oracle/ggs/dirver/status: created
Veridata Trace files /u01/oracle/ggs/dirver/trace: created
Stdout files /u01/oracle/ggs/dirout: created

We then need to create a database user which will be used by the GoldenGate Manager, Extract and Replicat processes. We can create individual users for each process or configure just a common user – in our case we will create the one user GGS_OWNER and grant it the required privileges

SET ORACLE SID

1) CREATE GOLDEN GATE DIRECTORY

 2)ENABLE ARCHIVE LOG MODE

SETUP GOLDEN GATE USERS.OBJECT
sqlplus / as sysdba
CREATE USER GGUSER IDENTIFIED BY india123
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP;
GRANT CONNECT ,RESOURCE, UNLIMITED TABLESPACE TO GGUSER;
GRANT EXECUTE ON UTL_FILE TO GGUSER;


SQL> grant connect,resource to gguser;

Grant succeeded.

SQL> grant select any dictionary, select any table to gguser;

Grant succeeded.

SQL> grant create table to gguser;

Grant succeeded.

SQL> grant flashback any table to gguser;

Grant succeeded.

SQL> grant execute on dbms_flashback to gguser;

Grant succeeded.

SQL> grant execute on utl_file to gguser;

Grant succeeded.

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

$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
AIX 5L, ppc, 64bit (optimized), Oracle 11 on Sep 17 2009 23:54:16

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

GGSCI (devu007) 1> DBLOGIN USERID gguser, PASSWORD india123
Successfully logged into database.

We also need to enable supplemental logging at the database level otherwise we will get this error when we try to start the Extract process –

2010-02-08 13:51:21 GGS ERROR 190 No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key
column is not in first row piece.

2010-02-08 13:51:21 GGS ERROR 190 PROCESS ABENDING.

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

Database altered

Configuring the Manager process

The Oracle GoldenGate Manager performs a number of functions like starting the other GoldenGate processes, trail log file management and reporting.

The Manager process needs to be configured on both the source as well as target systems and configuration is carried out via a parameter file just as in the case of the other GoldenGate processes like Extract and Replicat.

After installation of the software, we launch the GoldenGate Software Command Interface (GGSCI) and issue the following command to edit the Manager parameter file

EDIT PARAMS MGR

The only mandatory parameter that we need to specify is the PORT which defines the port on the local system where the manager process is running. The default port is 7809 and we can either specify the default port or some other port provided the port is available and not restricted in any way.

Some other recommended optional parameters are AUTOSTART which which automatically start the Extract and Replicat processes when the Manager starts.

The USERID and PASSWORD parameter and required if you enable GoldenGate DDL support and this is the Oracle user account that we created for the Manager(and Extract/Replicat) as described in the earlier tutorial.

The Manager process can also clean up trail files from disk when GoldenGate has finished processing them via the PURGEOLDEXTRACTS parameter. Used with the USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data contained in the trail files, they will not be deleted.

The following is an example of a manager parameter file

[oracle@redhat346 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

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

GGSCI 2> EDIT PARAMS MGR

PORT 7809
USERID gguser, PASSWORD india123
PURGEOLDEXTRACTS /u01/oracle/ggs/dirdat/ex, USECHECKPOINTS

The manager can be stopped and started via the GSSCI commands START MANAGER and STOP MANAGER .

Information on the status of the Manager can be obtained via the INFO MANAGER command

GGSCI (PrimeDG) 4> info manager

Manager is running (IP port PrimeDG.7809).

This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.

The table PRODUCTS in the SH schema on the source has 72 rows and on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.

On Source

1) Create the Initial data extract process ‘load1’

GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.

Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.

2) Create the parameter file for the extract group load1

EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.

GGSCI (redhat346.localdomain) 6> EDIT PARAMS load1

EXTRACT load1
USERID gguser, PASSWORD india123
RMTHOST PrimeDG, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;

On Target

3) Create the initial data load task ‘load2’

Since this is a one time data load task, we are using the keyword SPECIALRUN

GGSCI (PrimeDG) 1> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.

4) Create the parameter file for the Replicat group, load2

REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case

GGSCI (PrimeDG) 2> EDIT PARAMS load2

“/u01/oracle/software/goldengate/dirprm/rep4.prm” [New file]

REPLICAT load2
USERID gguser, PASSWORD india123
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;

On Source

SQL> select count(*) from products;

COUNT(*)
———-
72

On Target

SQL> select count(*) from products;

COUNT(*)
———-
0

On Source

5) Start the initial load data extract task on the source system

We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.

On Source

GGSCI (redhat346.localdomain) 16> START EXTRACT load1

Sending START request to MANAGER …
EXTRACT LOAD1 starting

GGSCI (redhat346.localdomain) 28> info extract load1

EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE

GGSCI (redhat346.localdomain) 29> info extract load1

EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE

On Target

SQL> select count(*) from products;

COUNT(*)
———-
72

Comi

========================================================================
Coming Next – Configuring an Online Extract and Replicat Group …..
INSTALL GOLDENGATE MANAGER SERVICE 'GGMGR'
SQL@>marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>grant gg_ggsuser_role to ggate
SQL>ddl_enable.sql

Enable supplemental login for tables