Download - 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
Oracle GoldenGate 11g: Implementers Workshop
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
-
7/25/2019 Golden Gate Workshop 1
2/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-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
-
7/25/2019 Golden Gate Workshop 1
3/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-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
-
7/25/2019 Golden Gate Workshop 1
4/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-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
-
7/25/2019 Golden Gate Workshop 1
5/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-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
-
7/25/2019 Golden Gate Workshop 1
6/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-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/25/2019 Golden Gate Workshop 1
7/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-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
-
7/25/2019 Golden Gate Workshop 1
8/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-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*
-
7/25/2019 Golden Gate Workshop 1
9/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-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
-
7/25/2019 Golden Gate Workshop 1
10/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-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
-
7/25/2019 Golden Gate Workshop 1
11/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-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
-
7/25/2019 Golden Gate Workshop 1
12/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-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
-
7/25/2019 Golden Gate Workshop 1
13/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-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
-
7/25/2019 Golden Gate Workshop 1
14/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-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
-
7/25/2019 Golden Gate Workshop 1
15/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-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;
-
7/25/2019 Golden Gate Workshop 1
16/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-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.
-
7/25/2019 Golden Gate Workshop 1
17/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-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.
-
7/25/2019 Golden Gate Workshop 1
18/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-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.
-
7/25/2019 Golden Gate Workshop 1
19/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-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;
-
7/25/2019 Golden Gate Workshop 1
20/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-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.
-
7/25/2019 Golden Gate Workshop 1
21/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-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,
Oracle GoldenGate 11g
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.
ORACLE_SID=S1T2ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1OGG_HOME=/u01/app/ogg/10.2.0DISPLAY=localhost:0.0
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>
-
7/25/2019 Golden Gate Workshop 1
22/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-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,
Oracle GoldenGate 11g
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 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
ora.env Utility: Release 1.2 (Beta) on Mon May 21 08:51:56 AS2012
Copyright (c) 2008, 2012, PreciseTrace Inc. All rightsreserved.
ORACLE_SID=T1E2ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1OGG_HOME=/u01/app/ogg/11.2.0DISPLAY=localhost:0.0
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 11g on Oct 4 2011
23:53:33
Copyright (C) 1995, 2011, Oracle and/or its affiliates. Allrights reserved.
GGSCI (edm6hp99.precisetrace.com) 1>
-
7/25/2019 Golden Gate Workshop 1
23/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-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
Oracle GoldenGate 11g
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.
ORACLE_SID=S1T2ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1OGG_HOME=/u01/app/ogg/10.2.0DISPLAY=localhost:0.0
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. All
rights reserved.
GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,password oracleSuccessfully logged into database.
GGSCI (edm6hp99.precisetrace.com) 2>
-
7/25/2019 Golden Gate Workshop 1
24/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-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.
Oracle GoldenGate 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
Oracle Database 11g.
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
GGSCI (edm6hp99.precisetrace.com) 3>
The Source Oracle Database 10g Version 10.2.0.5 installedincludes the majority of Oracle Database 10g Enterpriseoptions.
-
7/25/2019 Golden Gate Workshop 1
25/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-25
Step 13: Login to TargetDatabase
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.
Oracle GoldenGate 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
Oracle Database 11g.
Login Using GGSCI Login to the Target Database from Oracle GoldenGate 11g.
$ ora.env 11 2 0 t 1 localhost ggsci
ora.env Utility: Release 1.2 (Beta) on Mon May 21 09:05:45 AS2012
Copyright (c) 2008, 2012, PreciseTrace Inc. All rights
reserved.
ORACLE_SID=T1E2ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1OGG_HOME=/u01/app/ogg/11.2.0DISPLAY=localhost:0.0
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 11g on Oct 4 201123:53:33
Copyright (C) 1995, 2011, Oracle and/or its affiliates. Allrights reserved.
GGSCI (edm6hp99.precisetrace.com) 1>dblogin userid ogg_admin,password oracleSuccessfully logged into database.
-
7/25/2019 Golden Gate Workshop 1
26/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-26
Step 14: TargetDatabase 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.
Oracle GoldenGate 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
Oracle Database 11g.
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
GGSCI (edm6hp99.precisetrace.com) 3>
-
7/25/2019 Golden Gate Workshop 1
27/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-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>
-
7/25/2019 Golden Gate Workshop 1
28/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-28
Step 16: Create Target Oracle GoldenGate 11g Sub-directories
Oracle GoldenGate 11g
creates a list of directories
outside of the Database
structure. Each directory
store specific type of files.
The directory dirchk stores
the Extract and Replicat
group write/read
positions.
The directory dirdef stores
the Extract and Replicat
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.
-
7/25/2019 Golden Gate Workshop 1
29/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-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.
GGSCI (edm6hp99.precisetrace.com) 4>
Alternatively, use Oracle GoldenGate 11g wild-card syntax.
GGSCI (edm6hp99.precisetrace.com) 3>add trandataosm$repapi.*
-
7/25/2019 Golden Gate Workshop 1
30/30
Oracle GoldenGate 11g: Implementers Workshop
Advanced Heterogeneous Database Replication, Operational Business Intelligence,
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) 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.
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