ogg workshop 2

7/25/2019 OGG Workshop 2

1/28

Oracle GoldenGate 11g: Implementers Workshop

Advanced Heterogeneous Database Replication, Operational Business Intelligence

High-Availability and Zero-Downtime Migration and Upgrade

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

2-1


Advanced Heterogeneous Database Replication, Operational Business

Intelligence and Data Warehouse, Business Continuity and Zero-Downtime

Migration and Upgrade

Workshop 2

Performing Initial Data Load UsingFile to Replicat and Direct Load Methods

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

Khaled K. Hawashin

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


2/28





2-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/28





2-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/28





2-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/28





2-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 Data Pump Extract process




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 Remote Trial Files Configure, create and manage Replicat process


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 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





6/28





2-6


Oracle GoldenGate 11g Zero Downtime Migration and Upgrade

Configure, create and manage Source Extract process and local trails



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


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 Replicat process for MySQL Verify and testing data replication


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 onStandby Database




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/28





2-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/28





2-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.0

Oracle GoldenGate 11g 11.1.1.1.2

Global Database Name S1T2.precisetrace.comSID S1T2

Oracle Home /u01/app/oracle/product/10.2.0/db_1

Password for all accounts Oracle

Database File Directory /u01/app/oracle/oradata/S1T2/

Flush Recovery Area /u01/app/oracle/flash_recovery_area

Character Set AL32UTF8National Character Set AL16UTF16

Endian Format Little

2.2 Database Name: T1E2(Target)

Object name Setting


Oracle GoldenGate 11g 11.1.1.1.2Global Database Name T1E2.precisetrace.com

SID T1E2

Oracle Home /u01/app/oracle/product/11.2.0/dbhome_1

Password for all accounts oracle_4UDatabase File Directory +DATA

Fast Recovery Area +FRA

Character Set AL32UTF8

National Character Set AL16UTF16

Endian Format Little

2.3 ASM Instance: +ASM

Object name Setting

Clusterware Version 11.2.0.3.0

Global Database Name +ASM.precisetrace.com

SID +ASMOracle Home /u01/app/oracle/product/11.2.0/grid

Password for all accounts oracle_4U

Data Disk Group (DG) +DATA (Norma)

Fast Recovery Area DG +FRA (External)FRA Size 44GB

ASM Disks /dev/x*


9/28





2-9

2.4 Intermediate System

Object name Setting


Oracle GoldenGate 11g 11.1.1.1.2

Oracle 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 only

arg2 Oracle Database Release 1 or 2

arg3 Oracle Database Update 1, 2, 3, 4 or 5

arg4 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, etc

arg8 Username Systemarg9 Password Oracle

arg10 Option Connect string, target, local, etc.

$ora.env 10 2 0 s 1 localhost sqlplus


10/28





2-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/28





2-11

4.0 Preparation of Oracle GoldenGate 11g Initial Load

The diagram illustrates the

workshop workflow. The

major tasks are:

Initial Data load Extract Initial Data load Replicat

Verify initial Data Load

The initial data load

Extract (ELOAD01) reads

from the source tables and

create Extract file on the

Target Server.

The initial data load

Replicat (RLOAD01)

applies the data to the

Target Database. Extract

data can be on one or

more files.

Initload.dat

Extract File

1

2

Perform Initial Load

Extract

Perform Initial Load

Replicat

Initial Load Data

File

Workshop 2: File-To-Replicat Initial Load

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

Create the Source and Target Oracle GoldenGate 11g Globalparameter file.

Create the Source and Target Oracle GoldenGate 11gCheckpoint table.

Configure, start and verify successful Oracle GoldenGate 11gManager for Source and Target. Prepare, configure and perform initial data load Extract from

the source table using File-to-Replicat method. Prepare, configure and perform initial data load Replicat from

the Data file.

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/28


Advanced Heterogeneous Database Replication, Operational Business Intelligence,



2-12

5.0 Workshop Tasks

After successful completion of Workshop 1, in this exercise, you will use OracleGoldenGate 11g to perform initial data load from Source to Target Database. Theexercise to transport the Database for CUSTOMERS-POLICIES from the source tables

to the target already created tables.

Duration: 90 Minutes

Scenario

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

This is Workshop 2 of 5 to perform Oracle Database 11g: Zero-Downtime Migration andUpgrade.

Tasks Summary

No Tasks Step/Instructions Completed/Note

1 Create the GLOBALS parameterfile for Source

Step 1

2 Create the checkpoint table onthe Source

Step 2

3 Create the GLOBALS parameterfile for Target

Step 3

4 Create the checkpoint table onthe Target

Step 4

5 Prepare, start and verify themanager process for Source

Step 5 and Step 6

6 Prepare, start and verify themanager process for Target

Step 7 and Step 8

7 Prepare and Start the initial Dataload Extract

Step 9 and Step 10

8 Verify successful initial data loadfor Source Table

Step 11

9 Prepare, add and start ReplicatProcess on Target

Step 12 and Step 13

10 Verify the Replicat Processcompletion Report

Step 14

11 Verify the Database row countmatching for Source and Target

Step 15


13/28





2-13

Step 1: Prepare the GLOBALSparameter files on Source

Use GGSCI to create the

GLOBALS parameter file.

The GLOBALS parameters

file applies to Oracle

GoldenGate 11g instanceglobally.

The location of the

GLOBALS parameter file is

the Oracle GoldenGate

11g home. For UNIX/Linux

its case sensitive and

must be in Upper case.

GLOBALSParameters File

The GLOBALS parameters are used by all GoldenGate 11gExtracts, Data Pumps Extract and Replicats processes. Thesteps to create the GLOBALS parameter files are listed below:

Start Oracle GoldenGate 11g GGSCI

Invoke the EDIT command

GGSCI>edit params ./GLOBALS

Add the checkpoint table parameter

checkpointtable ogg_admin.ggschkpt

Requirement You must exist GGSCI, re-start GGSCI to read the GLOBALSparameter file. If you encounter an error, use the operatingsystem editor to correct the error, and then start GGSCI.

Two options are available to create the checkpoint table:

1. Use the checkpointtableparameter for GLOBALS

parameter file.2. Specify the checkpoint table parameter part of the add

checkpointtable command.


14/28





2-14

Step 2: Create the CheckpointTable on Source

The checkpoint table

provides more flexibility

than using dirchk file.

Because the workshop

designed for Bi-Directional configuration,

the checkpoint table will

be created for the Source

and Target Databases.

The command requires

login to the Database

from Oracle GoldenGate

11g instance. The table

resides on the default

tablespace of ogg_admin

user.

Login to SQL*Plus to

verify the table by

performing DESC

command.

Checkpoint Table File-based Checkpointing is used to record the write and readpositions of Extract, Data Pump Extract and Replicat, providingresilience against failures. For Replicat, its best-practice to useDatabase Table for Checkpointing.

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

GGSCI (edm6hp99.precisetrace.com) 2>add checkpointtable

No checkpoint table specified, using GLOBALS specification(ogg_admin.ggschkpt)...

Successfully created checkpoint table OGG_ADMIN.GGSCHKPT.

GGSCI (edm6hp99.precisetrace.com) 3>

The checkpoint table name is subject to the standard OracleDatabase table naming. Most importantly, you should avoidOracle Database reserved words and Oracle GoldenGate 11gcommands.


15/28





2-15

Step 3: Prepare the GLOBALSparameter files on Target

Use GGSCI to create the

GLOBALS parameter file.

The GLOBALS parameters

file applies to Oracle

GoldenGate 11g instanceglobally.

The location of the

GLOBALS parameter file is

the Oracle GoldenGate

11g home. For UNIX/Linux

its case sensitive and

must be in Upper case.

GLOBALSParameters File

The GLOBALS parameters are used by all GoldenGate 11gExtracts, Data Pumps Extract and Replicats processes. Thesteps to create the GLOBALS parameter files are listed below:

Start Oracle GoldenGate 11g GGSCI

Invoke the EDIT command

GGSCI>edit params ./GLOBALS

Add the checkpoint table parameter

checkpointtable ogg_admin.ggschkpt

Requirement You must exist GGSCI, re-start GGSCI to read the GLOBALSparameter file. If you encounter an error, use the operatingsystem editor to correct the error, and then start GGSCI.

Two options are available to create the checkpoint table:

3. Use the checkpointtableparameter for GLOBALS

parameter file.4. Specify the checkpoint table parameter part of the add

checkpointtable command.


16/28





2-16

Step 4: Create the CheckpointTable on Target

The checkpoint table

provides more flexibility

than using dirchk file.

Because the workshop

designed for Bi-Directional configuration,

the checkpoint table will

be created for the Source

and Target Databases.

The command requires

login to the Database

from Oracle GoldenGate

11g instance. The table

resides on the default

tablespace of ogg_admin

user.

Login to SQL*Plus to

verify the table by

performing DESC

command.

Checkpoint Table Checkpoint table is used to record the write and read positions ofExtract, Data Pump Extract and Replicat, avoiding re-performingcapture and replicat.

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

password oracleSuccessfully logged into database.

GGSCI (edm6hp99.precisetrace.com) 2>add checkpointtable

No checkpoint table specified, using GLOBALS specification(ogg_admin.ggschkpt)...

Successfully created checkpoint table OGG_ADMIN.GGSCHKPT.

GGSCI (edm6hp99.precisetrace.com) 3>

Note If the table already exists, an error message is displayed. From

SQL*Plus verify the existence of the checkpoint table.


17/28





2-17

Step 5: Prepare the Manager Process Parameter File (mgr.prm) on Source

The Manager Process is

the first Oracle

GoldenGate 11g to be

created. The only

required parameter is thePORT used by the

manager process and

defined on mgr.prm

parameter file.

Before continuing the

creation of other Oracle

GoldenGate 11g

processes, ensure the

manager process is on

RUNNING status.

If not, reviews the filereport file dirrpt/mgr.rpt

for details about the error

when trying to start it.

Manager Process The manager process is a required Oracle GoldenGate 11gprocess that must be running for other processes to functionnormally.

GGSCI (edm6hp99.precisetrace.com) 1>edit params mgr

port 7810dynamicportlist 9001-9100purgeoldextracts ./dirdat/*, &usecheckpoints, &

minkeephours 0

The Manager process is responsible for the following functions:

Monitor the health of Oracle GoldenGate 11g instance

Control activities such as starting and restring processes

Report error of the instance

Allocating of space

The manager processes has the status of:

STOPPED

RUNNING

The character & allows multi-line sub-parameters.


18/28





2-18

Step 6: Start the Manager Process on Sourceand verify Status

Save the manager process

parameter file (mgr.prm),

ensure its saved on the

directory

dirprm/mgr.prm.

Also, investigate the

manager process report

file from dirrpt/mgr.prt

ensuring there no

indication of incorrect

configuration; also correct

any INFO warning, if

possible.

Even though the

configuration can be

directly done from theeditor, it preferably done

using GGSCI, ensuring the

correct location.

Online StatusReport

You have two option to display the manager status report, whichvery necessary to confirm before proceeding further Extract andReplicat configurations.

GGSCI (edm6r1s30.precisetrace.com) 1>start mgr

Manager started.GGSCI (edm6r1s30.precisetrace.com) 2>info mgr

Manager is running (IP port edm6r1s30.precisetrace.com.7810).

GGSCI (edm6r1s30.precisetrace.com) 3>info all

Program Status Group Lag Time SinceChkpt

MANAGER RUNNING

GGSCI (edm6r1s30.precisetrace.com) 4>

Ensure the correct IP port number, which is 7810 for the Sourceand 7811 for the Target.


19/28





2-19

Step 7: Prepare the Manager Process Parameter File (mgr.prm)on Target

The Manager Process is

the first Oracle

GoldenGate 11g to be

created. The only

required parameter is thePORT used by the

manager process and

defined on mgr.prm

parameter file.

Before continuing the

creation of other Oracle

GoldenGate 11g

processes, ensure the

manager process is on

RUNNING status.

If not, reviews the filereport file dirrpt/mgr.rpt

for details about the error

when trying to start it.

Manager Process The manager process is required Oracle GoldenGate 11gprocess that must be running for other processes to functionnormally.

GGSCI (edm6hp99.precisetrace.com) 1>edit params mgr

port 7811dynamicportlist 9001-9100purgeoldextracts ./dirdat/*, &usecheckpoints, &

minkeephours 0

The Manager process is responsible for the following functions:

Monitor the health of Oracle GoldenGate 11g instance

Control activities such as starting and restring processes

Report error of the instance

Allocating of space

The manager processes has the status of:

STOPPED

RUNNING


20/28





2-20

Step 8: Start the Manager Process on Targetand verify Status

Save the manager process

parameter file (mgr.prm),

ensure its saved on the

directory

dirprm/mgr.prm.

Also, investigate the

manager process report

file from dirrpt/mgr.prt

ensuring there no

indication of incorrect

configuration; also correct

any INFO warning, if

possible.

Even though the

configuration can be

directly done from theeditor, it preferably done

using GGSCI, ensuring the

correct location.

Online StatusReport

You have two option to display the manager status report, whichvery necessary to confirm before proceeding further Extract andReplicat configurations.

GGSCI (edm6r1s30.precisetrace.com) 1>start mgr

Manager started.GGSCI (edm6r1s30.precisetrace.com) 2>info mgr

Manager is running (IP port edm6r1s30.precisetrace.com.7811).

GGSCI (edm6r1s30.precisetrace.com) 3>info all


MANAGER RUNNING

GGSCI (edm6r1s30.precisetrace.com) 4>

Ensure the correct IP Port number, which is 7810 for the Sourceand 7811 for the Target.


21/28





2-21

Step 9: Prepare ELOAD01Extract Parameter file (eload01.prm)on Source

Several methods are

available to perform the

initial load from the

Source Database to the

Target Database, themethods require creating

the schema definition

before the initial load

starts.

The File to Replicat

method requires the

tables are created, to

avoid massive collision,

ensure the table has no

data. Up on completion,

verify the data from SQL

by comparing the rowcount from both the

Source and the Target.

ELOAD01 Extract Initial load File to Replicat configuration read the tables directlyfrom the Source Database, indicated by the soruceistable

parameter.

GGSCI (edm6hp99.precisetrace.com) 1>edit params eload01

sourceistableuserid ogg_admin, password oraclermthost localhost, mgrport 7811rmtfile ./dirdat/initload01.dat, purgetable osm$repapi.customers;table osm$repapi.policies;

The initial data load file is created on the Target Server using theparameters rmthostand rmtfilewhich refer to:

Target host name

Target manager port

Target initial data file location and name

The rmtfile purgeoption removes an existing initial load file,avoiding failure of the extract command.


22/28





2-22

Step 10: Perform initial Extract from SourceTables

The initial load File to

Replicat will be executed

from the Operating

System using the Oracle

GoldenGate 11g extractcommand.

The initial load Extract

Process (ELOAD01) will

run for the duration of the

extract and will terminate

up on completion.

Depending on the amount

of Source data, it will take

a while for the Extract

command to complete, as

this method extractingrow-by-row and writing

remotely.

ELOAD01 Extract The extract command for File to Replicat method is executedfrom the Operating System using the command extract.

The extract command takes the following parameters:

The Extract parameter name

The Extract report file name (Log file)

[email protected]$pwd/u01/app/ogg/10.2.0

[email protected]$ extract paramfiledirprm/eload01.prm reportfile dirrpt/eload01.rpt

[email protected]$

Note Because the default location of the file eload01.prm is dirprmdirectory, and the Extract command is started from OracleGoldenGate 11g home, the paramfileargument take the value

dirprm/elaod01.prm

The parameter paramfilecan be used as pf, and

reportfileas rf.


23/28





2-23

Step 11: Verify Replicatdata File and View Extract Report

After successful

completion of the initial

load, verify the data file

location on the Target

Oracle GoldenGate 11g.

The location should is

configured using the

parameter rmtfile on the

Source Extract parameter

file.

If the file does not exist,

then the initial load had

failed and must be re-run.

Before re-running the

extract command,

investigate the report file,correct the issue and then

re-run the command.

Verify Initial LoadData File

The initial load creates the data file on the Target server. Thiswill be used by the Replicat to load data into the Database.

GGSCI (edm6hp99.precisetrace.com) 2> [email protected]$pwd/u01/app/ogg/10.2.0

[email protected]$ cd ../11.2.0/[email protected]$ ls -altotal 37148

drwxrwxr-x 2 oracle oinstall 4096 May 21 19:11 .drwxrwxr-x 15 oracle oinstall 4096 May 21 18:33 ..-rw-rw-rw- 1 oracle oinstall 37986149 May 21 19:11initload01.dat

[email protected]$

The size of the data file depends on the amount of tables data.The table should not be compressed, and if its, then tablecompressing need to be disabled before performing initial loadextract.


24/28





2-24

View Report (Source) GGSCI (edm6r1s30.precisetrace.com) 3>view reportdirrpt/eload01.rpt

***************************************************Oracle GoldenGate Capture for Oracle

Version 11.1.1.1.2OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100

Linux, x86, 32bit (optimized), Oracle 10g on Oct 72011 14:35:40

Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.

Starting at 2012-05-21 19:11:29***************************************************

Operating System Version:LinuxVersion #1 SMP Thu Sep 3 02:28:20 EDT 2009, Release

2.6.18-164.el5PAENode: edm6hp99.precisetrace.comMachine: i686soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU Time : unlimited unlimited

Process id: 5472

Report at 2012-05-21 19:11:38 (activity since 2012-05-21 19:11:29)

Output to ./dirdat/initload01.dat:

From Table OSM$REPAPI.CUSTOMERS:# inserts: 100000# updates: 0# deletes: 0# discards: 0

From Table OSM$REPAPI.POLICIES:# inserts: 100000# updates: 0# deletes: 0# discards: 0

REDO Log StatisticsBytes parsed 0Bytes output 37985196


25/28





2-25

Step 12: Prepare RLOAD01Replicat parameter file (rload01.prm)on Target

Performing File to

Replicat requires the

configuration of Targets

Replicat as special run

process.

Use Oracle GoldenGate

11g EDIT command to

prepare the Replicat

(RLOAD01), which will

read the initial file to load

into the Target Database.

Before starting the

Replicat process, the ADD

RELICAT command is used

to add the Replicat

RLOAD01 to OracleGoldenGate 11g instance.

RLOAD01 Replicat After moving the data from the Source Database to the TargetServer, its nowready for upload into the Target Database. Toavoid termination of process due to collision, ensure theparameter handlecollisions is included in the parameter

file rload01.rpm.

GGSCI (edm6hp99.precisetrace.com) 1>edit params rload01

replicat rload01specialrunassumetargetdefshandlecollisionsuserid ogg_admin, password oracleextfile ./dirdat/initload01.datdiscardfile ./dirrpt/rload01.dsc, purge

map osm$repapi.*, target osm$repapi.*;end runtime

After successful run of the initial load Replicat, the process will

automatically terminate with status STOPPED.

To avoid future start of this Replict unintentionally, itsrecommended to delete this Replicat from the configuration.Delete the Replicat requires login to the Database from OracleGoldenGate 11g.


26/28





2-26

Step 13: Add and Start Initial Load Replicat Process on Target

The initial to Replicat load

method requires adding

the Replicat process to

Oracle GoldenGate 11g

instance.

Because its a special run

process, do no start and

the status should be on

STOPPED status.

From the Operating

System, run the command

replicat. The process will

be started, and after the

load on the Target

Database is completed, it

will remain on STOPPEDstatus, and should be

deleted.

ADD REPLICATCommand

Execute the ADD REPLICAT command. Then exit and run thereplicat process from the Operating System.

GGSCI (edm6hp99.precisetrace.com) 2>add replicat rload01,extfile ./dirdat/initload01.datREPLICAT added.

GGSCI (edm6hp99.precisetrace.com) 3> info all


MANAGER RUNNINGREPLICAT STOPPED RLOAD01 00:00:00 00:00:04

GGSCI (edm6hp99.precisetrace.com) 4> exit

[email protected]$pwd/u01/app/ogg/11.2.0/[email protected]$ cd ..

[email protected]$pwd/u01/app/ogg/[email protected]$ replicat pf dirprm/rload01.prf dirrpt/rload01.rpt

[email protected]$


27/28





2-27

Step 14: View Report and Verify initial Data load

After the loading of data

by the Replicat, ensure

the data moved

successfully.

This should be done by

viewing the Replicat

report, and the Database

utility such as SQL*Plus.

If you find any failure,

investigate the report and

discard files. The report

display the number of row

inserted for each table.

Only insert will performed

during initial load, andzero for update and

delete transactions.

View Report Reading ./dirdat/initload01.dat, current RBA37986149, 200000 records

Report at 2012-05-21 19:36:51 (activity since 2012-05-21 19:35:20)

From Table OSM$REPAPI.CUSTOMERS to

OSM$REPAPI.CUSTOMERS:# inserts: 100000# updates: 0# deletes: 0# discards: 0

From Table OSM$REPAPI.POLICIES toOSM$REPAPI.POLICIES:

# inserts: 100000# updates: 0# deletes: 0# discards: 0

Last log location read:FILE: ./dirdat/initload01.datRBA: 37986149TIMESTAMP: 2012-05-21 19:11:36.367903EOF: NOREADERR: 400


28/28



High-Availability and Zero-Downtime Migration and Upgrade2-28

Step 15: Confirm the TargetDatabase

Login to the Target

Database using SQL*Plus,

return the number of

rows for the mapped

tables.

The initial data load is

completed; the next

workshop is to configure

the Capture Data Change

(CDC).

Workshop 3 is the Uni-

Directional configuration

is one way configuration

with a Data Pump Extract

option.

Workshop 4 extends the

configuration to Bi-

Directional configuration.

SQL*Plus $ora.env 11 2 0 t 1 localhost sqlplus osm$repapi oraclelocal

T1E2>select count(*)2 from customers;

COUNT(*)----------

100000

T1E2>select count(*)2 from policies;

COUNT(*)----------

100000

T1E2>

If the result in not 100,000 then there was collision and the

discard file should be reviewed. For best practice, beforestarting the Replicat process, disable DDL replication, disableconstraint, and use wild-card if necessary instead of the tablenames. If the source Database is active, then an Extractprocess should be configured and started before the initial load.

ogg workshop 2

Documents