golden gate workshop 1

7/25/2019 Golden Gate Workshop 1

1/30

Oracle GoldenGate 11g: Implementers Workshop

Advanced Heterogeneous Database Replication, Operational Business Intelligence,

High-Availability and Zero-Downtime Migration and Upgrade

Copyrights 2013, PreciseTrace, Inc. | www.precisetrace.com/ebcc

1-1Version: 1.2Code: ORA10001EN10Date: 16-Feb-2013


Advanced Heterogeneous Database Replication, Operational Business

Intelligence and Data Warehouse, Business Continuity and Zero-Downtime

Migration and Upgrade

Workshop 1

Prepare and InstallOracle GoldenGate 11gEnvironment

Workshop Developer: Fawzi A. AlswaimilContributors: Deema F. Alswaimil

Khalid W. Hawashin


2/30





1-2

1.0 An Overview

This hands-on workshop has been designed to give attendees best practices forimplementing Oracle GoldenGate 11g. The workshop walks you through the process toinstall, configure and deploy Oracle GoldenGate 11g source and target instances. Thenperform business-specific implementation such as:

Oracle GoldenGate 11g for Report Offloading Oracle GoldenGate 11g for Disaster Recovery Oracle GoldenGate 11g for High-Availability Oracle GoldenGate 11g for Zero-Downtime Migration and Upgrade Oracle GoldenGate 11g for Operational Business Intelligence Oracle GoldenGate 11g for Data Warehouse Oracle GoldenGate 11g for Heterogeneous Replications Oracle GoldenGate 11g for Data Guard Integration

From the ground-up, attendees learn where to download Oracle GoldenGate 11g MediaPack, perform quick-install for Oracle GoldenGate 11g, Configure Data Capture, learnand perform initial data load techniques, Configure Data transfer and Configure Data

Apply.

For users who need to apply Oracle GoldenGate 11g for specific business areas, theworkshop guide them in step-by-step approach to learn the details of configuring themodular architecture to build flexible yet high-performance integrated systems forOracle-to-Oracle or Heterogeneous environment supporting in-house developed andPackage applications systems.

Packaged Applications Support

Oracle E-Business Suite SAP R3 Oracle Siebel Oracle JD Edward Salesforce.com

Audience

IT Managers and Team-Leaders Database Administrators Data Warehouse Analysts System Administrators

Network Administrators Storage Administrators

Pre-Requisites

Familiarity with Oracle Basic Administrative commands Familiarity with Basic SQL Data Manipulation Language (DML) Understands Distributed database concept


3/30





1-3

Objectives

By successfully completing the workshops, the attendees will be capable to work withOracle GoldenGate 11g and apply the learned best practices to their workplace. Theobjective focus on major application of Oracle GoldenGate 11g to:

Understand Oracle GoldenGate 11g applications Prepare and configure source and target database environments Work with Oracle GoldenGate components Configure Oracle GoldenGate 11g for Uni-Directional applications Configure Oracle GoldenGate 11g for Bi-Directional applications Configure Oracle GoldenGate 11g Advanced Replication Configure Oracle GoldenGate 11g DDL Replication Configure Oracle GoldenGate 11g for Heterogeneous Replication Configure Oracle GoldenGate 11g Intermediate Systems Configure Oracle GoldenGate 11g Integrated Capture Configure Oracle GoldenGate 11g on Data Guard Environment

Configure and work with Oracle GoldenGate 11g Management Pack

The workshop follows several routes, each depends on the need of your organization.Figure 1 shows the business objective and the recommended workshops to practice.

Additional workshops can be completed after running the cleanup procedure.

Fig.1: Workshop Routes


4/30





1-4

Topics

Understand Oracle GoldenGate 11g application areas

Configuring Oracle GoldenGate 11g for Query and Report Offloading Configuring Oracle GoldenGate 11g for Disaster Recovery Solutions Configuring Oracle GoldenGate 11g for High-Availability Active-Passive Configuring Oracle GoldenGate 11g for High-Availability Active-Active Configuring Oracle GoldenGate 11g for Zero Downtime Upgrade and Migration Configuring Oracle GoldenGate 11g for Operational BI and Data Warehouse Configuring Oracle GoldenGate 11g for Heterogeneous Replication Configuring Oracle GoldenGate 11g for Data Guard Integration

Review Understand Oracle GoldenGate 11g network topologies and Architecture

Un-Directional Configuration Query offloading Bi-Directional Configuration for Standby Database and Active-Active for HA Master-Master Configuration for Enterprise Load Balancing Cascade using Intermediate Systems for Data Transformation

Broadcast Configuration for Data Distribution Data Warehouse and Data mart Integration and consolidation configuration

Prepare Database Environment and Install Oracle GoldenGate 11g

Enable Database Supplemental Logging Review Oracle GoldenGate 11g requirements for Operating System and Databases Download and Install Oracle GoldenGate 11g Working with GoldenGate 11g Software Command Interface (GGSCI) Create Oracle GoldenGate 11g instance and directory structure Configure the Source and Target Managers processes Understand and configure Managers best-practice parameters Login to Oracle Database using Oracle GoldenGate 11g Run basic GGSCI commands Create and Manage Checkpoint table Working with TRANDATA command to enable Tables Supplemental Logging

Oracle GoldenGate 11g Initial Data Load Techniques

Work with Initial Data Load using File to Replicat method Work with Initial Data Load using Direct load method Understand Initial Data Load using Bulk-Direct load method Understand Initial Data Load using Database Utility Use handle collision for Initial Data Load Techniques Control collision handling using SEND Command

Control Replicat for Initial Load and RBA handling

Oracle GoldenGate 11g: Uni-Directional Setup for Report Offloading

Configure, create and manage Primary Extract processes and local trails Configure, create and manage Data Pump Extract process for routing to target Configure, create and manage Remote Trial Files Configure, create and manage Replicat process Use administrative command to view process reports and troubleshooting Understand Memory allocation by Oracle GoldenGate 11g


5/30





1-5

Use Oracle GoldenGate 11g Uni-Directional for Report Offloading Use Oracle Database 11g OLAP Materialized View with Oracle GoldenGate 11g Enable and verify Report offloading on Oracle GoldenGate 11g target

Oracle GoldenGate 11g: Bi-Directional Setup for Business Continuity

Configure, create and manage Primary Extract processes and local trails Configure, create and manage Data Pump Extract process Configure, create and manage Remote Trial Files Configure, create and manage Replicat process Use administrative command to view process reports and troubleshooting Detect and resolve conflict for Update and Delete DML operations Understand and apply Oracle GoldenGate 11g Techniques to resolve conflicts Develop stored procedure and SQL queries for managing and resolving conflicts

Oracle GoldenGate 11g Advanced Configuration and DDL Replication

Configure, create and manage Primary Extract processes and local trails Configure, create and manage Data Pump Extract process for routing to target

Configure, create and manage Remote Trial Files Configure, create and manage Replicat process Use administrative command to view process reports and troubleshooting Use COLSEXCEPT to exclude unsupported Data types Use COLMAP feature for mismatched table structures Use SQLEXEC to invoke PL/SQL Stored procedure Use WHERE clause to implement row filtering Use FILTER to apply complex row and column manipulations Use Oracle GoldenGate 11g Build-In functions Use DEFGEN Utility Use RANGE Function to deploy Replicat parallelism Perform basic and advanced DDL replications

Oracle GoldenGate 11g Intermediate System for Query Offloading

Configure, create and manage Primary Extract processes and local trails Configure, create and manage Data Pump Extract process for Source System Configure, create and manage Remote Trial Files for Source System Configure, create and manage Data Pump Extract process for intermediate systems Configure, create and manage Remote Trial Files for intermediate systems Configure, create and manage Replicat processes Use administrative command for reporting and troubleshooting Understand Memory allocation by Oracle GoldenGate 11g Use Oracle GoldenGate 11g Uni-Directional for Report Offloading

Use Oracle Database 11g OLAP Materialized View with Oracle GoldenGate 11g Enable and verify Report offloading on Oracle GoldenGate 11g target

Oracle GoldenGate 11g Integrated Capture

Configure, create and manage Integrated Capture processes Configure, create and manage Data Pump Extract process for routing to target Configure, create and manage Remote Trial Files Configure, create and manage Replicat process


6/30





1-6

Use administrative command for reporting and troubleshooting

Oracle GoldenGate 11g Zero Downtime Migration and Upgrade

Configure, create and manage Source Extract process and local trails Configure, create and manage Data Pump Extract process for routing to target Configure, create and manage Remote Trial Files Monitor Remote Trail Queue Use RMAN to perform Database Cloning Use RMAN to perform Migration to ASM Use DBUA to perform upgrade from Oracle Database Release 10.2 to 11.2 Configure, create and manage Replicat process Verify Source and Target Database are synchronized

Oracle GoldenGate 11g Heterogeneous Replication

Configure, create and manage Primary Extract processes and local trails for Oracle Configure, create and manage Data Pump Extract process for routing to target Configure, create and manage Remote Trial Files

Configure, create and manage Replicat process for MySQL Verify and testing data replication Use administrative command for reporting and troubleshooting

Oracle GoldenGate 11g Integration with Oracle Database 11g Data Guard

Configure Oracle Database 11g Data Guard Configure, create and manage Primary Extract processes and local trails on Standby Configure, create and manage Data Pump Extract process for routing to target on

Standby Database Configure, create and manage Remote Trial Files Configure, create and manage Replicat process Use administrative command for reporting and troubleshooting

Oracle GoldenGate 11g Management Packs, Veridata

Understand Oracle GoldenGate 11g Veridata Architecture Review Oracle GoldenGate 11g Veridata components Oracle GoldenGate 11g Veridata Installation Configuring GoldenGate 11g Veridata Java Agent Using GoldenGate 11g Veridata Web-Interface Perform Data verify and rectify operation Running GoldenGate 11g Veridata Reports

Oracle GoldenGate 11g Management Packs, Director

Understand Oracle GoldenGate 11g Director Understand Oracle GoldenGate 11g Director Installation Configuring Oracle GoldenGate 11g Director Server Using Oracle GoldenGate 11g Client Interface Using Oracle GoldenGate 11g Director Web Interface

Oracle GoldenGate 11g Management Packs, Configuration Assistant

Understand Oracle GoldenGate 11g Configuration Assistant Use Oracle GoldenGate 11g Configuration Assistant to verify environments


7/30





1-7

Use Oracle GoldenGate 11g Configuration Assistant runtime options Oracle GoldenGate 11g Configuration Assistant to review and verify design Oracle GoldenGate 11g Configuration Assistant to deploy to Target Instance

Oracle GoldenGate 11g Optimization, Tuning and Troubleshooting

Implementing Compression, Encryption Implementing GGSCI security Working with Oracle GoldenGate 11g MACRO Working with LOGDUMP Utility Implement BATCHSQL feature for transaction optimization Monitoring Lag and Statistics Performance Tuning for Extract Performance Tuning for Data Pump Performance Tuning for Replicat

Oracle Database 11g: Data Guard Fundamentals

Understand Data Guard Standby Database

Configure and work with Data Guard Broker Data Guard Architecture Creating Physical Standby Database Understand Protection levels Work with Database Role Settings Monitor Data Guard Trace capabilities Understand Fast-Start Failover Understand Startup and Shutdown processes Apply best-practice Data Guard Configuration Perform Database Switchover and Failover

Oracle Database 11g: Upgrade Fundamentals

Oracle Database 11g Upgrade Techniques Oracle Database 11g Installation Workflow Oracle Database 11g Upgrades road Map Oracle Database 11g Upgrades Methods for Zero-Downtime Pre-Upgrade Scripts and configuration Pre-Upgrade Scripts report Gathering Dictionary statistics Oracle Database 11g Database Upgrade Assistant (DBUA) Performing Oracle Database 11g Upgrade using DBUA GUI

Performing Oracle Database 11g Upgrade using DBUA Silent mode Post-Upgrade configuration Time Zone file Version and Time Zone package Oracle Recovery Manager Catalog Upgrade


8/30





1-8

2.0 Workshop 1 Database Environment

The workshop consists of 1 Source Oracle Database 10g Release 2 (Local) and 1Target Oracle Database 11g Release 2 (Remote). Both Oracle Installations use OracleGoldenGate 11g.

2.1 Database Name: S1T2(Source)

Object name Setting

RDBMS Version 10.2.0.5.0Oracle GoldenGate 11g 11.1.1.1.2Global Database Name S1T2.precisetrace.comSID S1T2Oracle Home /u01/app/oracle/product/10.2.0/db_1Password for all accounts OracleDatabase File Directory /u01/app/oracle/oradata/S1T2/Flush Recovery Area /u01/app/oracle/flash_recovery_area

Character Set AL32UTF8National Character Set AL16UTF16Endian Format Little

2.2 Database Name: T1E2(Target)

Object name Setting

RDBMS Version 11.2.0.3.0Oracle GoldenGate 11g 11.1.1.1.2Global Database Name T1E2.precisetrace.comSID T1E2Oracle Home /u01/app/oracle/product/11.2.0/dbhome_1

Password for all accounts oracle_4UDatabase File Directory +DATAFast Recovery Area +FRACharacter Set AL32UTF8National Character Set AL16UTF16Endian Format Little

2.3 ASM Instance: +ASM

Object name Setting

Clusterware Version 11.2.0.3.0Global Database Name +ASM.precisetrace.comSID +ASMOracle Home /u01/app/oracle/product/11.2.0/gridPassword for all accounts oracle_4UData Disk Group (DG) +DATA (Norma)Fast Recovery Area DG +FRA (External)FRA Size 44GB

ASM Disks /dev/x*


9/30





1-9

2.4 Intermediate System

Object name Setting

RDBMS Version 11.2.0.3.0Oracle GoldenGate 11g 11.1.1.1.2Oracle Home /u01/app/oracle/product/11.2.0/dbhome_1

2.5 Workshop Environment and Credentials

2.6 ora.env Utility

$ ora.env arg1 arg2 arg3 arg4 arg5 [arg6][arg10]

Argument Description Example

arg1 Oracle Database Version 10 or 11 onlyarg2 Oracle Database Release 1 or 2arg3 Oracle Database Update 1, 2, 3, 4 or 5arg4 Database Type: Source or Target s, t or iarg5 Source or Target number 1, 2, 3, n-1, n

arg6 DISPLAY Localhost, Hostname or IParg7 Program executable sqlplus, rman, lsnrctl, gaskin, etcarg8 Username Systemarg9 Password Oraclearg10 Option Connect string, target, local, etc.

$ora.env 10 2 0 s 1 localhost sqlplus


10/30





1-10

3.0 Workload Simulator for Online System Monitor (OSM) Database

Testing Oracle GoldenGate 11g transactions handling from the source Database to thetarget Database for one way, or from the Target Database to the Source for Two-Way isdone by a PL/SQL Application Programmatic Interface (API) called TRANS_LOAD.

3.1 How to use TRANS_LOAD Simulator?

TRNAS_LOAD API simulates Small-Medium-Large (S-M-L) Workload for CUSTOMERS-POLICIES Database. TRANS_LOAD can be invoked for different S-M-L workloads;examples of the options available are described in the table below.

EXEC Call Command option DescriptionBEGIN

TRANS_LOAD(1, 100001);END;

Insert one row only starting at row100001.

BEGINFOR i IN 100002..100012 LOOP

TRANS_LOAD(1, i);END LOOP;

End;

Insert 10 rows starting at row100002.

BEGINFOR i IN 100013..100113 LOOP

TRANS_LOAD(1, i);DBMS_LOCK.SLEEP(2);

END LOOP;END;

Insert 100 rows starting at row100013, with sleep option of 2seconds between each 2 rows.

The Database table structure described below.

S1T2>DESC osm$repapi.customersName Null? Type--------------------- -------- -------------------CUST_NO NOT NULL NUMBERCUST_NAME NOT NULL VARCHAR2(128)CUST_EMAIL NOT NULL VARCHAR2(128)CUST_MOBILE NOT NULL VARCHAR2(16)CUST_ADDRESS VARCHAR2(128)

S1T2>DESC osm$repapi.policiesName Null? Type--------------------- -------- -------------

POL_NO NOT NULL NUMBERPOL_FROM NOT NULL DATEPOL_TO NOT NULL DATEPOL_VALUE NOT NULL NUMBER(8,2)POL_SUB_TOTAL NOT NULL NUMBER(8,2)PT_CODE NOT NULL VARCHAR2(8)DT_CODE VARCHAR2(8)POL_TOTAL NOT NULL NUMBER(8,2)CUST_NO NOT NULL NUMBER


11/30





1-11

4.0 Preparation of Oracle GoldenGate 11g

The diagram illustrates the

workshop workflow. The

major tasks are:

Installation Database Configuration

Enable Table Logging

The Source Server

(localhost) and Database

(S1T2) are operational for

current applications. The

Target Server and

Database (T1E2) are ready

for configuration of Oracle

GoldenGate 11g.

This is a one-host setup

and configuration as a

localhostenvironment.

TCP/IP Network

Quick Install

2

Database Supplemental Logging

1

3

Table-Level Logging

Workshop 1: Prepare and Install

4.1 Objectives After completing this workshop lab, you will be able to:

Verify the Workshop environment and requirements Prepare Oracle GoldenGate 11g Software for Oracle

Database 10g and Oracle Database 11g. Perform Oracle GoldenGate 11g quick installation for Oracle

Database 10g and Oracle Database 11g Enable Oracle Database 10g and Oracle Database 11g

Supplemental Logging Enable Oracle Database 10g and Oracle Database 11g

Table-Level Logging

4.2 Lab Setup This lab requires that your environment has the followingConfiguration

Oracle Database 10g EE Release 10.2.0.5.0 Oracle Database 11g Grid Infrastructure Release 11.2.0.3.0

Oracle Database 11g EE Release 11.2.0.3.0

Source Database: S1T2 Source Instance: S1T2 Source Global Database: S1T2.precisetrace.com Network configuration files already setup for S1T2 and T1E2


12/30





1-12

5.0 Workshop Tasks

In this exercise, you will use Oracle SQL*Plus to configure the Source and TargetDatabase environment. From the Linux Shell Commands, a quick Oracle GoldenGate11g will be performed on the Source and Target Servers (localhost).

Duration: 45 Minutes

Scenario

You are assigned the task to configure Oracle GoldenGate 11g for the Source database(S1T2) running on Oracle Database 10g EE Version10.2.0.5.0 to T1E2. The TargetDatabase (T1E2) running on Oracle Database 11g EE Version 11.2.0.3.0.

Successful completion of Workshop 1 is mandatory before proceeding to Workshop 2.

Tasks Summary

No Tasks Step/Instructions Completed/Note

1 Verify the Source and TargetDatabase environment andOperating mode.

Step 1 and Step 2

2 Enable and Start SupplementalLogging for Source and TargetDatabases

Step 3 and Step 4

3 Perform Quick install for OracleGoldenGate 11gon Source and

Target Database Server

Step 5 and Step 6

4 Create Oracle GoldenGate 11gDatabase Administrator onSource and Target

Step 7 and Step 8

5 Start Oracle GoldenGate 11gSoftware Command Interface onboth Source and Target

Step 9 and Step 10

6 Login to Source Database fromOracle GoldenGate 11g anddetermine version details

Step 11 and Step 12

7 Login to Target Database fromOracle GoldenGate 11g and

determine version details

Step 13 and step 14

8 Create Source and TargetOracle GoldenGate 11g Sub-directories

Step 15 and Step 16

9 Create table-level logging forSource and Target Databasesusing GGSCI.

Step 17 and Step 18


13/30





1-13

Step 1: Verify SourceDatabase Environment

Familiarize yourself with

the Source Database

(S1T2) Operating

environment. Use the

provided utility ora.env toset the current instance

variables.

The Source Database

(S1T2) uses a workload

simulator application

(TRANS_LOAD) that will be

used to verify the result

using different workloads.

If you encounter any type

of error on Step 1, pleasedo not continue and seek

assistance to resolve the

issue.

Source Database(S1T2)

The SourceDatabase S1T2.precisetrace.comshould beoperating on ARCHIVELOG mode. This is a basic requirementfor Oracle GoldenGate 11g.

$ ora.env 10 2 0 s 1 localhost sqlplus system oracle local

S1T2>select log_mode2 fromv$database;

LOG_MODE------------ARCHIVELOG

S1T2>

CurrentEnvironmentSettings

ORACLE_SID=S1T2ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1OGG_HOME=/u01/app/ogg/10.2.0DISPLAY=localhost:0.0

Exit the current SQL*Plus session, use the echoShellcommand to confirm the values for the above variables.

$echo $ORACLE_SID$echo $ORACLE_HOME$echo $OGG_HOME$echo $DISPLAY


14/30





1-14

Step 2: Verify TargetDatabase Environment

Familiarize yourself with

the Target Database

(T1E2) Operating

environment. Use the

provided utility ora.env toset the current instance

variables.

The target Database is

using Automatic Storage

Management, and under

Oracle Restart

infrastructure for higher

availability.

If you encounter any type

of error on Step 2, pleasedo not continue and seek

assistance to resolve the

issue.

Target Database(T1E2)

The target Database T1E2.precisetrace.comshould beoperating on ARCHIVELOG mode.

$ ora.env 11 2 0 t 1 localhost sqlplus system oracle_4U local

T1E2>select log_mode

2 fromv$database;

LOG_MODE------------ARCHIVELOG

T1E2>

CurrentEnvironmentSettings

ORACLE_SID=T1E2ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1OGG_HOME=/u01/app/ogg/11.2.0DISPLAY=localhost:0.0

Exit the current SQL*Plus session, use theecho

Shell commandto confirm the values for the above environment variables.

$echo $ORACLE_SID$echo $ORACLE_HOME$echo $OGG_HOME$echo $DISPLAY


15/30





1-15

Step 3: Enable SourceSupplemental Logging

Enable Source (S1T2)

Database Supplemental

Logging. Multiple

Supplemental Logging

options available. Oracle

GoldenGate 11g requiresthe PRIMARY KEY option.

If disk space is of concern,

Specify the PRIMARY

supplemental only.

The default options enable

all types of supplemental.

Specific option can be set

for:

PRIMARY EKY

UNIQUE KEY

FOREIGN KEY

SupplementalLogging (S1T2)

Supplemental logging enables Oracle GoldenGate 11g to locatethe target row for update and delete transaction type, leading tothe entire row containing that columnwill be placed in the redolog file.

$ ora.env 10 2 0 s 1 localhost sqlplus system oracle local

S1T2>alter database add supplemental log data;

Database altered.

S1T2>alter system switch logfile;

System altered.

S1T2>

The second SQL*Plus command starts the Databasesupplemental logging. Other Supplemental logging commandsspecific to Primary Key and Unique Key constraints.

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

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


16/30





1-16

Step 4: Enable TargetSupplemental Logging

Enable Source (T1E2)

Database Supplemental

Logging. Number of

Supplemental commands

available. Oracle

GoldenGate 11g requiresthe PRIMARY KEY type of

supplemental logging only.

Not specify the

supplemental type, the

command enable all types

of supplemental, which

are:

PRIMARY EKY

UNIQUE KEY

FOREIGN KEY

SupplementalLogging (T1E2)

Supplemental logging enables Oracle GoldenGate 11g to locatethe target row for update and delete type of transactions.

$ ora.env 11 2 0 t 1 localhost sqlplus system oracle_4U local

T1E2>alter database add supplemental log data;

Database altered.

T1E2>alter system switch logfile;

System altered.

T1E2>

The second SQL*Plus command starts the Databasesupplemental logging. Other Supplemental logging commandsspecific to Primary Key and Unique Key constraints.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGNKEY) COLUMNS;

To find out the current Database Supplemental logging, queryv$database dynamic view. In addition, when enablingsupplemental logging consider increasing the size of the onlineredo log groups to support the redo entries generatedbydatabase supplemental logging.


17/30





1-17

Step 5: SourceOracle GoldenGate 11g, Quick Install

The workshop uses Oracle

Enterprise Linux 5 update

4, which requires Oracle

Database 11g and

GoldenGate 11g of 32-bit

size. When downloadingthe software from Oracle

eDelivery cloud, ensure

the download the 32-bit

release.

Oracle GoldenGate 11g for

Oracle Database 10g

requires Oracle Database

10g libraries.

Quick Installation forSource

Oracle GoldenGate 11g does not have an installation interface;instead it uses a quick type of installation method, by extractingthe software using the Linux/UNIX command tar xvf.

S1T2>exitDisconnected from Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

$ cd $OGG_HOME$ lsfbo_ggs_Linux_x86_ora10g_32bit.tar$ tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar

UserExitExamples/UserExitExamples/ExitDemo_pk_befores/UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcprojUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIXUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUXUserExitExamples/ExitDemo_pk_befores/readme.txtUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUXUserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.cUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.S

To find your database version details and the bit-size, querythe dynamic performance view v$version. It displays thecomponents, version and the bit-size.


18/30





1-18

Step 6: TargetOracle GoldenGate 11g, Quick Install

The workshop uses Oracle

Enterprise Linux 5 update

4, which requires Oracle

Database 11g and

GoldenGate 11g of 32-bit

size. When downloadingthe software from Oracle

eDelivery cloud, ensure

the download the 32-bit

release.

Oracle GoldenGate 11g for

Oracle Database 11g

requires Oracle Database

11g libraries.

Quick Installationfor Target

Oracle GoldenGate 11g does not have an installation interface;instead it uses a quick type of installation method, by extractingthe software using the Linux/UNIX command tar xvf.

T1E2>exitDisconnected from Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - ProductionWith the Partitioning, Automatic Storage Management, OLAP, DaMiningand Real Application Testing options$ cd $OGG_HOME$ lsfbo_ggs_Linux_x86_ora11g_32bit.tar$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

UserExitExamples/UserExitExamples/ExitDemo_pk_befores/UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcprUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIXUserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUXUserExitExamples/ExitDemo_pk_befores/readme.txt

UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX

To find details about Oracle GoldenGate 11g release, startOracle GoldenGate Software Command Interface (GGSCI), andthen enter the GoldenGate command versionsafter logging tothe database from Oracle GoldenGate 11g.


19/30





1-19

Step 7: SourceOracle GoldenGate 11g Administrator

Even though the

workshop grants DBA to

Oracle GoldenGate 11g

Administrator, In

production environment,

the principle of leastprivileges should be the

practice. The Oracle

GoldenGate 11g

administrator for Source

uses different set of

Database privileges for

Target Database.

Do not subject the

administrator to very strict

Database profile to avoid

aborting Extract and

Replicat operations and/or

other type of commands.

ogg_adminDatabase User

While connected as SYSDBA, perform the below command tocreate and grant ogg_admin. The command firstcreateogg_admin, then grant CREATE SESSION and DBAprivileges to the administrator.

S1T2>grant create session, dba to ogg_admin identified byoracle;

Grant succeeded.

S1T2>alter user ogg_admin quota unlimited on users;

User altered.

S1T2>

For production environment, you should create a dedicatedpermanent default tablespace(OGG_DAT) for the OracleGoldenGate 11g Administrator. This practice very essentialwhen the Database is of type OLAP where the Oracle

GoldenGate 11g should be the only Tablespace that needsfrequent online backups.

RMAN> backup tablespace OGG_DAT;


20/30





1-20

Step 8: TargetOracle GoldenGate 11g Administrator

Even though the workshop

grants DBA to Oracle

GoldenGate 11g

Administrator, In

production environment,

the principle of leastprivileges should be the

practice. The Oracle

GoldenGate 11g

administrator for Source

uses different set of

Database privileges for

Target Database.

Do not subject the

administrator to very strict

Database profile to avoid

aborting Extract and

Replicat operations and/or

other type of commands.

ogg_adminDatabase User

While connected as SYSDBA, perform the below command tocreate and grant ogg_admin. The command first createogg_admin, then grant CREATE SESSION and DBA privilegesto the administrator.

T1E2>grant create session, dba to ogg_admin identified byoracle;

Grant succeeded.

T1E2>alter user ogg_admin quota unlimited on users;

User altered.

T1E2>

Alternatively, you may grant only the specific privileges requiredby the Replicat process on the target.

T1E2>grant CREATE SESSION, ALTER SESSION, RESOURCE,2>CONNECT, SELECT ANY DICTIONAY, SELECT ANY3>TABLE, INSERT, UPDATE, DELETE , CREATE4>TABLE to ogg_admin;

Delete transaction on the Source result on delete transaction ofthe target, ogg_admin need DELETE privilege on these tables,otherwise, DBA privilege required.


21/30





1-21

Step 9: Start Oracle GoldenGate 11g GGSCI on Source

The Workshops is

Command-Line oridented.

This will give you an

excellent understanding of

the commands used

during the workshop.

An alternative is to use the

Graphical User Interface,


Director and Monitor.

Most of the command on

the workshop does not

require Database Login, as

it will be creating file-

based parameter files and

adding processes to Oracle

GoldenGate 11g instance.

Start GGSCI Start GoldenGate 11g Software Command Interface (GGSCI).

$ ora.env 10 2 0 s 1 localhost ggsci

ora.env Utility: Release 1.2 (Beta) on Mon May 21 08:44:40 AS2012

Copyright (c) 2008, 2012, PreciseTrace Inc. All rights

reserved.


Successful Setting...

Oracle GoldenGate Command Interpreter for OracleVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100Linux, x86, 32bit (optimized), Oracle 10g on Oct 4 201123:54:04

Copyright (C) 1995, 2011, Oracle and/or its affiliates. Allrights reserved.

GGSCI (edm6hp99.precisetrace.com) 1>


22/30





1-22

Step 10: Start Oracle GoldenGate 11g GGSCI on Target

The Workshops is

Command-Line oridented.

This will give you an

excellent understanding of

the commands used

during the workshop.

An alternative is to use the

Graphical User Interface,


Director and Monitor.

Most of the command on

the workshop does not

require Database Login, as

it will be creating file-

based parameter files and

adding processes to


instance.

Start GGSCI Start Oracle GoldenGate 11g Software Command Interface(GGSCI). Use the provided ora.env utility to start GGSCI.

$ ora.env 11 2 0 t 1 localhost ggsci


Copyright (c) 2008, 2012, PreciseTrace Inc. All rightsreserved.



Oracle GoldenGate Command Interpreter for OracleVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100Linux, x86, 32bit (optimized), Oracle 11g on Oct 4 2011

23:53:33




23/30





1-23

Step 11: Login to SourceDatabase

Certain Oracle Golden

Gate 11g commands

requires login to the

Database. Successful login

to the Database requires

to parameters, dbloginand password. For

additional password

security measures, the

password can be

encrypted using Oracle

GoldenGate 11g utility.

The same encrypted

password is stored in the

configuration files of


Extract and Repicat

processes.

Login Using GGSCI Use GGSCI to login to the Database.

$ ora.env 10 2 0 s 1 localhost ggsci

ora.env Utility: Release 1.2 (Beta) on Mon May 21 08:55:42

Copyright(c)2008,2012,PreciseTrace Inc. All rights reserved.




Copyright (C) 1995, 2011, Oracle and/or its affiliates. All

rights reserved.

GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,password oracleSuccessfully logged into database.



24/30





1-24

Step 12: SourceDatabase Version

The Workshop is intended

to perform Migration and

Upgrade from Oracle

Database 10g Version

10.2.0.5 to Oracle

Database 11g Version11.2.0.3. Hence, the

Source is Oracle Database

10g and the Target is

Oracle Database 11g.


support lower Oracle

Database Version such as

Oracle 8i and Oracle

Database 9i. Avoiding

interim upgrade when

going from 8i or Oracle

Database 9i Release 1 to


Database, OSversions

To determine the Operating System and Database version,login to the Database from Oracle GoldenGate 11g.

GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,password oracle

Successfully logged into database.

GGSCI (edm6hp99.precisetrace.com) 2>versionsOperating System:LinuxVersion #1 SMP Thu Sep 3 02:28:20 EDT 2009, Release 2.6.18-164.el5PAENode: edm6hp99.precisetrace.comMachine: i686

Database:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 -ProdPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production


The Source Oracle Database 10g Version 10.2.0.5 installedincludes the majority of Oracle Database 10g Enterpriseoptions.


25/30





1-25

Step 13: Login to TargetDatabase



Upgrade from Oracle


10.2.0.5 to Oracle














Login Using GGSCI Login to the Target Database from Oracle GoldenGate 11g.

$ ora.env 11 2 0 t 1 localhost ggsci


Copyright (c) 2008, 2012, PreciseTrace Inc. All rights

reserved.







26/30





1-26

Step 14: TargetDatabase Version



Upgrade from Oracle


10.2.0.5 to Oracle














Database, OSversions

Verify the release and version of the Operating System andDatabase.

GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,password oracle

Successfully logged into database.

GGSCI (edm6hp99.precisetrace.com) 2> versionsOperating System:LinuxVersion #1 SMP Thu Sep 3 02:28:20 EDT 2009, Release 2.6.18-164.el5PAENode: edm6hp99.precisetrace.comMachine: i686

Database:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production



27/30





1-27

Step 15: Create SourceOracle GoldenGate 11gSub-directories

OracleGoldenGate 11g

creates a list of directories

outside of the Database

structure. Each directory

store specific type of files.

The directory dirprm

stores the Extract and

Replicat group parameter

files.

The directory dirrpt stores

the Extract and Replicat

group lot output files.

The directory dirdat stores

initial load data, when

performing file to Replicat

technique to initiate the

Target Database.

Source Sub-directories

The location of the sub-directories is based on OracleGoldenGate 11g Home directory. Oracle GoldenGate 11gcommands expect the file on the default location, otherwise theabsolute path must be specified when entering the commands.

GGSCI (edmr1s35) 1>create subdirs

Creating subdirectories under current directory /u01/app/ogg/10.2.0

Parameter files /u01/app/ogg/10.2.0/dirprm: createdReport files /u01/app/ogg/10.2.0/dirrpt: createdCheckpoint files /u01/app/ogg/10.2.0/dirchk: createdProcess status files /u01/app/ogg/10.2.0/dirpcs: createdSQL script files /u01/app/ogg/10.2.0/dirsql: createdDatabase definitions files /u01/app/ogg/10.2.0/dirdef: createdExtract data files /u01/app/ogg/10.2.0/dirdat: createdTemporary files /u01/app/ogg/10.2.0/dirtmp: createdVeridata files /u01/app/ogg/10.2.0/dirver: createdVeridata Lock files /u01/app/ogg/10.2.0/dirver/lock: createdVeridata Out-Of-Sync files /u01/app/ogg/10.2.0/dirver/oos: createdVeridata Out-Of-Sync XML files /u01/app/ogg/10.2.0/dirver/oosxml: createdVeridata Parameter files /u01/app/ogg/10.2.0/dirver/params: createdVeridata Report files /u01/app/ogg/10.2.0/dirver/report: createdVeridata Status files /u01/app/ogg/10.2.0/dirver/status: createdVeridata Trace files /u01/app/ogg/10.2.0/dirver/trace: created

Stdout files /u01/app/ogg/10.2.0/dirout: created

GGSCI (edmr1s35) 2>


28/30





1-28

Step 16: Create Target Oracle GoldenGate 11g Sub-directories


creates a list of directories

outside of the Database

structure. Each directory

store specific type of files.

The directory dirchk stores


group write/read

positions.

The directory dirdef stores


group redefinition files.

The directory dirpcs stores

the process id for Extract

and Replicat groups. While

dirtmp dedicated for

temporary storage.

Source Sub-directories

In case, the subdirscommand already executed, theoperating system message, directory already exist returned.

GGSCI (edmr1s35) 1>create subdirs

Creating subdirectories under current directory /u01/app/ogg/11.2.0

Parameter files /u01/app/ogg/11.2.0/dirprm: created

Report files /u01/app/ogg/11.2.0/dirrpt: createdCheckpoint files /u01/app/ogg/11.2.0/dirchk: createdProcess status files /u01/app/ogg/11.2.0/dirpcs: createdSQL script files /u01/app/ogg/11.2.0/dirsql: createdDatabase definitions files /u01/app/ogg/11.2.0/dirdef: createdExtract data files /u01/app/ogg/11.2.0/dirdat: createdTemporary files /u01/app/ogg/11.2.0/dirtmp: createdVeridata files /u01/app/ogg/11.2.0/dirver: createdVeridata Lock files /u01/app/ogg/11.2.0/dirver/lock: createdVeridata Out-Of-Sync files /u01/app/ogg/11.2.0/dirver/oos: createdVeridata Out-Of-Sync XML files /u01/app/ogg/11.2.0/dirver/oosxml: createdVeridata Parameter files /u01/app/ogg/11.2.0/dirver/params: createdVeridata Report files /u01/app/ogg/11.2.0/dirver/report: createdVeridata Status files /u01/app/ogg/11.2.0/dirver/status: createdVeridata Trace files /u01/app/ogg/11.2.0/dirver/trace: createdStdout files /u01/app/ogg/11.2.0/dirout: created

GGSCI (edmr1s35) 2>

To re-run the command, either execute the operating Systemcommand rm, or de-install Oracle GoldenGate 11g usingrm Rcommand.


29/30





1-29

Step 17: Add TRANDATA on the SourceTables

In the case of missing

primary/unique key of the

table, Oracle GoldeGate

11g TRANDATA command

is used to enable table-

level supplementallogging.

TRANDATA command

requires successful login

to the Database, then

prefixing the table name

with schema name.

These steps need to be

scripted in case of large

number of table, or use

the wild-card feature

instead of the table

names.

TRANDATACommand

Supplemental enable database supplemental logging,TRANDATA GoldenGate 11g command supplement databaselogging by adding metadata and supplemental logging at the tablelevel, so that Oracle GoldenGate 11g can perform the relevantoperation on target database for DML Database transactions.

GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,

password oracleSuccessfully logged into database.

GGSCI (edm6hp99.precisetrace.com) 2>add trandataosm$repapi.customers

Logging of supplemental redo data enabled for tableOSM$REPAPI.CUSTOMERS.

GGSCI (edm6hp99.precisetrace.com) 3>add trandataosm$repapi.policies

Logging of supplemental redo data enabled for tableOSM$REPAPI.POLICIES.


Alternatively, use Oracle GoldenGate 11g wild-card syntax.

GGSCI (edm6hp99.precisetrace.com) 3>add trandataosm$repapi.*


30/30



h l b l d d d1-30

Step 18: Add TRANDATA on the TargetTables

In the case of missing

primary/unique key of the

table, Oracle GoldeGate

11g TRANDATA command

is used to enable table-level supplemental

logging.

TRANDATA command

requires successful login

to the Database, then

prefixing the table name

with schema name.

These steps need to be

scripted in case of large

number of table, or usethe wild-card feature

instead of the table

names.

TRANDATACommand

Enable table-level logging at the Target Database. This isneeded for Workshop 4, configuring Bi-Directional configuration.


GGSCI (edm6hp99.precisetrace.com) 2>add trandataosm$repapi.customers

Logging of supplemental redo data enabled for tableOSM$REPAPI.CUSTOMERS.

GGSCI (edm6hp99.precisetrace.com) 3>add trandataosm$repapi.policies

Logging of supplemental redo data enabled for tableOSM$REPAPI.POLICIES.

For large number of tables, Scripting may need to be used asshown below. Then use Oracle GoldenGate 11g OBEYcommand.

S1T2> spool /tmp/trandata.obyS1T2> Select add trandataosm$repapi.||table_name2> from dba_tables where owner=OSM$REPAPI;S1T2> spool off

golden gate workshop 1

Documents