Oracle Golden Gate Scalable High Performance Database Machine

Oracle Golden Gate Scalable, High Performance Database Machine Presentation by: www. Easy. Reliable. COM Phone: +080 -41156843/+91 -9606734482 Email: easyreliable@gmail. com/support@easyreliable. com Website: http: //www. easyreliable. com

Agenda • • • • Oracle Golden. Gate Architecture Overview Installation Oracle Golden. Gate. Configuration Overview and Preparing the Environment Configuring the Initial Load Oracle Golden. Gate Components Overview Capture and Delivery Work Flow Important Keyword for Golden Gate Configuring Change Capture(Extract) Configuring Extract Trails and Files (Data Pump) Configuring Change Delivery(Replicat) Basic Microservices Architecture: Main Components Basic Golde Gate Commands Troubleshooting with Case Study

Oracle Golden. Gate Architecture Overview Oracle Golden. Gate software is a middleware product designed to work in a heterogeneous environment with different databases. It is a replication technology used to move data among many different platforms. Oracle Golden. Gate can move changes across a TCP/IP network and does not require Oracle Net. Oracle Golden. Gate uses its own system of checkpoint files to maintain transaction integrity Important of for Oracle Golden. Gate Delivers continuous operations for mission-critical applications to eliminate unplanned and planned downtime and the related costs Lowers IT costs through heterogeneous support for multiple platforms to leverage lower-cost infrastructure for query offloading Improves efficiencies through improved performance, scalability of realtime feeds, and data distribution Reduces risk by ensuring data integrity and reliability between source and target systems

Oracle Golden. Gate Architecture Overview Oracle Golden. Gate Solutions for Oracle Database • Continuous availability via active-active databases • Zero downtime upgrades, migrations, and maintenance I. Migrate from non-Oracle databases to Oracle 11 g. R 2. II. Upgrade Oracle Database 8 i, 9 i, 10 g to 11 g. R 2 III. Upgrade/migrate the database server or OS. IV. Perform database maintenance.

GG usage New HW DB Apps Legacy HW Reporting DB DB GG Active cloned DB Data warehouse/Mart 5

6

Supported Databases and Operating Systems Databases Oracle Golden. Gate Capture: § Oracle § My. SQL § DB 2 § Microsoft SQL Server § Sybase ASE § Teradata Operating Systems and Platforms • Windows • Linux • Sun Solaris • HP-UX • HP Open. VMS • IBM AIX

Oracle Golden. Gate Architecture Overview Oracle Golden. Gate has two architectures Classic Architecture: Provides the processes and files required to effectively move data across a variety of topologies. These processes and files form the main components of the classic Architecture and was the product design until this release. Microservices Architecture: It is a new architecture that illustrates server applications running as part of the Oracle Golden. Gate environment. It comprises services that provide REST API interfaces for configuration, administration and monitoring with included HTML 5 web applications. Both classic extracts and replicats, as well as integrated extracts and replicats are supported with classic architecture. REST is an acronym for Representational State Transfer.

Oracle Golden. Gate Architecture Overview

Oracle Golden. Gate Architecture Overview Golden Gate consist of Manager process, Extract (capture) process, data pump process, and replicate (delivery) process. In between these processes are proprietary binary files called trail files. The trail files are used to store the captured transactions. Oracle Golden. Gate Components Oracle Golden. Gate has the following components: • Manager • Collector • Extract • Data Pump • Replicat • Trails or extract files • Checkpoints

Oracle Golden. Gate Architecture Overview Oracle Golden. Gate Process Groups: A process group consists of the following: • Process (either Extract or Replicat) –Parameter file –Checkpoint file –Any other files associated with the process, such as Report (*. rpt) and Discard (*. dsc) • Groups can be defined by using the Add Extract and Add Replicat commands. • Each process group must have a unique name

Oracle Golden. Gate Architecture Overview Extract: Committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. Trail: Golden. Gate stages and queues data for routing Pump: Golden. Gate distributes data for routing to targets. Route: Data is compressed and encrypted for routing to targets. Replicat: Replicat applies data with transaction integrity, transforming the data as required Extract Source Oracle and non. Oracle Database(s) Trail Pump LAN/WAN Internet TCP/IP Bidirection al Replicat Target Oracle and non. Oracle Database(s)

Oracle Golden. Gate Architecture Overview • No performance degradation for the source system • Read-consistent changed data with referential integrity • Complete data recoverability via Trail files

Oracle Golden. Gate Architecture Overview Each of these components is covered in detail inlater lessons in this course. Manager: Is required to start and stop the other processes, but is not required for the ongoing operation of another process. That is, you can start the Manager, use the Manager to start the Extract, and then stop and restart the Manager (perhaps to pick up new Manager configuration parameters) without affecting the running Extract. Server Collector: Also known simply as Collector Extract: Also known as Change Data Capture Data Pump: Optional; highly recommended Replicat : It is on target system Trails or extract files: Can optionally be compressed and/or encrypted

Oracle Golden. Gate Architecture Overview Manager, Extract, Replicat: Oracle Golden. Gate processes ER: Multiple Extract and Replicat processes Ext. Trail: Local Extract trail Rmt. Trail: Remote trail Tran. Data: Enables supplemental logging of primary keys and/or indexes for a schema or table in the transaction logs. Also, use Schema. Tran. Data for all the present and future tables in a schema. Checkpoint. Table: Checkpoint table (on target database) Trace. Table: Oracle trace table (on target database)

Installing Oracle Golden. Gate • Memory requirements 1. The amount of memory that Golden. Gate requires depends on the number of concurrent processes that will be running. • Each Extract and Replicat process needs approximately 25 MB to 55 MB of memory. • Swap space must be sufficient for each Oracle Golden. Gate Extract and Replicat process. This can be determined after setting up one Extract or Replicat. • Disk requirements • 50 MB to 150 MB free disk space depending on the database and platform • Trail files: You can assign a separate disk for trial files. The default size is 10 MB, but to optimize space you can use the PURGEOLDEXTRACTS parameter. • TCP/IP • Configure the system to use TCP/IP services, including DNS. • Configure one port for communication between the Manager process and other Oracle Golden. Gate processes. • Configure a range of ports for local Oracle Golden. Gate

Downloading Oracle Golden. Gate 1. In http: //edelivery. oracle. com. 2. Enter your personal information. 3. In the Media Pack search: Select a Product Pack: Oracle Fusion Middleware. Select your platform from the drop-down list. 4. Select and download the Oracle Golden. Gate Media Pack.

11 g Installation on UNIX, Linux, or z/OS • Extract the Oracle Golden. Gate. zip file to the directory where you want Golden. Gate to be installed. Extract the. tar file to the current directory: tar -xvof <filename>. tar • From the Oracle Golden. Gate directory, run the GGSCI program. In GGSCI, create the working directories: GGSCI> CREATE SUBDIRS

12 c Installation on UNIX, Linux, or z/OS In Session We will install Golden Gate on Source Database (Amer) and Target Database Euro, locate and unzip the staged Oracle Golden. Gate installation files from /home/oracle/Software. • Install OGG for the amer database by using the following details. Window/Page Description Choices or Values Software location /u 01/oggsrc Port 7809 • Install OGG for the euro database by using the following details. Window/Page Description Software location Port Choices or Values /u 01/oggtrg 7909

12 C Installation 1. In the terminal window where you have unzipped the installation software, navigate to fbo*/Disk 1. [oracle@hostname Software]$ cd fbo_ggs_Linux_x 64_shiphome/Disk 1 2. List the contents of Disk 1 and ensure the installer file exists. [oracle@hostname Disk 1]$ ls Install response run. Installer stage 3 Starting Oracle Universal Installer. . . [oracle@hostname Disk 1]$. /run. Installer Run run. Installer to launch the GUI. 4. At the Installation Option window, accept the default of Oracle 12 c. Click Next.

12 C Installation

12 C Installation In the Summary window, click Install. It takes about a minute. Same , We need to install on target database

GG Directories Directory Contents dirchk Golden. Gate checkpoint files dirdat Golden. Gate trail and Extract files dirdef Data definitions produced by DEFGEN and used to translate heterogeneous data dirpcs Process status files dirprm Parameter filesa dirrpt Process report files dirsql SQL scripts dirtmp Temporary storage for transactions that exceed allocated memory

1. Gote Golden Gate Home, start ggsci and ensure that the manager is running on port 7809. [oracle@hostname oggsrc]$ ggsci Oracle Golden. Gate Command Interpreter for Oracle Version 12. 3. 0. 1. 0 OGGCORE_12. 3. 0. 1. 0_PLATFORMS_170721. 0154_FBO Linux, x 64, 64 bit (optimized), Oracle 12 c on Jul 21 2017 23: 31: 13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved. GGSCI (hostname) 1> view param mgr PORT 7809 GGSCI (hostname) 2> Same way , We can check golden on target as well 2. ensure that ggsci is running and invoke a help summary of all the GGSCI commands. a. In a ggsci terminal, enter help. GGSCI (hostname) 2> help 2. View your command history. GGSCI (hostname) 4> history GGSCI Command History 1: view param mgr 2: help 3: help add trandata 4: history

View an informational summary of all processes GGSCI (hostname) 5> info all Program Chkpt Status Group Lag at Chkpt MANAGER RUNNING Time Since

Configuration Overview and Preparing the Environment

Configuration Overview and Preparing the Environment Oracle Golden. Gate can be deployed quickly and easily in four steps 1. Prepare the environment. -Install the Oracle Golden. Gate software on source and target. -Set up a Golden. Gate database user. -Enable supplemental logging. -For a heterogeneous source and/or target, generate source definitions so Replicat can process trail data. 2. Configure and start change capture to Golden. Gate trail files (Extract processes: primary and data pump). 3. Perform initial load to synchronize databases by database-specific or Oracle Golden. Gate methods. The timing of the initial load can happen at several places: before or after Step 2. If you have an initially empty table, the initial load step can be skipped altogether. 4. Configure and start change delivery (Replicat process).

Configuration Overview and Preparing the Environment The installation of Oracle Golden. Gate includes all of the components required to run and manage Oracle Golden. Gate processing and Oracle Golden. Gate utilities. The Manager process must be running on each system before Extract or Replicat can be started, and must remain running while those processes are running so that resource management functions are performed. The source definitions file contains the definitions of the source tables and is required on the target system in heterogeneous configurations. Replicat refers to the file when transforming data from the source to the target. Step 1: Preparing the Environment Enabling Oracle Golden. Gate in the Database ENABLE_GOLDENGATE_REPLICATION=true You set the ENABLE_GOLDENGATE_REPLICATION initialization parameter to TRUE to allow Oracle Golden. Gate to use RDBMS services. This parameter controls the supplemental logging feature that is required to support data replication. a. b. c. d. Log in to sqlplus as sysdba. Enter show parameter enable_goldengate. Enter alter system set enable_goldengate_replication=true scope=both. Verify the parameter is set.

Configuration Overview and Preparing the Environment Enabling Supplemental Logging To reconstruct an update operation, Oracle Golden. Gate needs more information than the transaction logs provide by default. Adding supplemental log data forces the log writer to also include the primary keys to logs instead of just the columns that have changed. You can set supplemental logging in three levels: • Database level Enable supplemental logging at the database level SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Set the database to forced logging mode. SQL> ALTER DATABASE FORCE LOGGING; SQL> select NAME, LOG_MODE, FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_MIN from v$database; NAME LOG_MODE FOR SUPPLEME ------------Amber ARCHIVELOG YES

Configuration Overview and Preparing the Environment • Schema level GGSCI> ADD SCHEMATRANDATA schema [ALLCOLS|NOSCHEDULINGCOLS] • Table level GGSCI> ADD TRANDATA [container. ]schema. table[ALLCOLS|NOSCHEDULINGCOLS] you set one of the following three logging levels when using Oracle Golden. Gate: • Database-level supplemental logging: Enables logging to add rowchaining information to the redo logs. You can verify the current values in the Oracle Database forced logging and database-level logging by issuing the following SQL command: SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; • Schema-level supplemental logging: Enables logging to add primary key and optionally unique keys and foreign keys of all tables in a schema • Table-level supplemental logging: Enables logging to add primary key and optionally unique keys and foreign keys of a table

Execute the dbms_goldengate_auth. grant_admin_privilege stored package that grants the C##OGG_ADMIN user the necessary privileges to operate as a Golden. Gate administrator SQL> exec dbms_goldengate_auth. grant_admin_privilege('C##OGG_ADMIN', contai ner=>'all'); PL/SQL procedure successfully completed. SQL> Log in to the database and enable supplemental logging at the schema level for the WEST schema in the source database. GGSCI (hostname) 2> DBLogin User. ID c##OGG_Admin@amer, Password ***** Successfully logged into database AMER. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 3> In the GGSCI_SOURCE terminal, enter DBLogin User. ID c##OGG_Admin@amer, Password <password>. Use your password sheet to look up the password for the C##OGG_ADMIN user in the AMER database. GGSCI (hostname) 2> DBLogin User. ID c##OGG_Admin@amer, Password ***** Successfully logged into database AMER. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 3>

2. Enter add schematrandata west. (The output below has been truncated here for display. ) GGSCI (hostname as c##OGG_Admin@orcl/AMER) 3> add schematrandata west 2018 -01 -23 19: 42: 50 INFO OGG-01788 SCHEMATRANDATA has been added on schema "west". . Oracle Goldengate marked following column as key columns on table WEST. TELLER_TRANS: TELLER_NUMBER, TRANS_NUMBER, TELLER_TRANS_TS. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 4> Verify that supplemental logging is enabled for each table in the WEST schema. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 4> info schematrandata west 2018 -01 -23 19: 43: 49 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "WEST". 2018 -01 -23 19: 43: 49 INFO OGG-01980 Schema level supplemental logging is enabled on schema "WEST" for all scheduling columns. 2018 -01 -23 19: 43: 49 INFO OGG-10462 Schema "WEST" have 6 prepared tables for instantiation. Ensure that you are connected to the database in the GGSCI_SOURCE terminal and enter info schematrandata west.

Preparing the Environment Starting the Manager To configure and start the manager: Create the parameter file by using GGSCI commands: GGSCI> Edit Params Mgr Start the Manager by using GGSCI: GGSCI> Start Manager Determine which port the Manager is using:

Manager is a controller process that must be running on each system before Extract or Replicat can be started. The Manager provides a command-line interface to perform a variety of tasks: Starting, stopping, and monitoring Oracle Golden. Gate processes Setting the parameters that configure Oracle Golden. Gate processes Error and lag reporting Resource management Trail file management You must start the Manager before most other configuration tasks are performed in GGSCI commands and parameter keywords are not case-sensitive. Putting Start Mgr (and some other useful commands) in a startup. oby file is a good practice Preparing the Environment: Sample Manager Parameter File

Preparing the Environment Generating a Source Definitions File defgenis initiated from the command prompt: defgen paramfile<paramfile>[ reportfile<reportfile>] Definitions are saved to the file that is specified in the parameter file. This file must be transferred to the target system as a text file. You use the defgen utility program to produce a file containing a definition of the layouts of the source files and tables. The output definitions are saved in an edait file and transferred to all target systems in text format. Replicat and Collector read the definitions at process startup and use the information to interpret the data from Oracle Golden. Gate trails.

User ID and Password Aliases (New with 12 c) • Oracle Wallet: • Contains user IDs and passwords • Is used in Extracts, ASM, and so on GGSCI> Create Wallet GGSCI> Add Credential. Store GGSCI> Alter Credential. Store Add User oggadmin. Password Welcome 1 Alias oggalias GGSCI> Info Credential. Store GGSCI> DBLogin User. IDAliasoggalias

User ID and Password Aliases (New with 12 c) d. Enter alter Credential. Store Add User C##OGG_Admin@euro Password GGSCI (hostname) 10> alter Credential. Store Add User c##OGG_Admin@euro Password <password> Alias oggadmin_euro Credential store in /u 01/oggsrc/dircrd/ altered. GGSCI (hostname) 11> <password> Alias oggadmin_euro. Use your password sheet to look up the password for the C##OGG_Admin user in the EURO database. GGSCI (hostname) 10> alter Credential. Store Add User c##OGG_Admin@euro Password <password> Alias oggadmin_euro Credential store in /u 01/oggsrc/dircrd/ altered. GGSCI (hostname) 11> e. Enter alter Credential. Store Add User c##OGG_Admin Password GGSCI (hostname) 11> alter Credential. Store Add User c##OGG_Admin Password <password> Alias oggadmin_root. Use your password sheet to look up the password for the C##OGG_Admin user in the ORCL container database. GGSCI (hostname) 11> alter Credential. Store Add User c##OGG_Admin Password <password> Alias oggadmin_root Credential store in /u 01/oggsrc/dircrd/ altered.

User ID and Password Aliases (New with 12 c) F. Enter Info Credential. Store GGSCI (hostname) 12> info Credential. Store Reading from /u 01/oggsrc/dircrd/: Default domain: Oracle. Golden. Gate Alias: oggadmin_euro Userid: c##OGG_Admin@euro Alias: oggadmin_amer Userid: c##OGG_Admin@amer Alias: oggadmin_root Userid: c##OGG_Admin GGSCI (hostname) 13> G. Enter DBLogin User. IDAlias oggadmin_euro. GGSCI (hostname) 13> DBLogin User. IDAlias oggadmin_euro Successfully logged into database EURO. GGSCI (hostname as c##OGG_Admin@orcl/EURO) 14> DBLogin User. IDAlias oggadmin_amer Successfully logged into database AMER. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 15> Enter DBLogin User. IDAlias oggadmin_amer.

User ID and Password Aliases (New with 12 c) H. Enter DBLogin User. IDAlias oggadmin_amer. GGSCI (hostname as c##OGG_Admin@orcl/EURO) 14> DBLogin User. IDAlias oggadmin_amer Successfully logged into database AMER. GGSCI (hostname as c##OGG_Admin@orcl/AMER) 15>

Configuring the Initial Load Oracle Golden. Gate can be used to load data in the following ways: • Direct load– Extract sends data directly to Replicat to apply using SQL. • Direct bulk load– Replicat uses the Oracle SQL*Loader API. • File to Replica– Extract writes to a file that Replicat applies using SQL • File to database utility– Extract writes to a file formatted for a DB bulk Loading

Configuring the Initial Load The initial load can be performed from an active source database. Users and applications can access and update data while the load is running. You can also perform an initial load from a quiesced (temporarily inactive) source database if you delay access to the source tables until the target load is completed

Configuring the Initial Load On the source system: 1. An Extract process captures source data directly from tables. 2. The Extract process sends data in large blocks across a TCP/IP network to the target system. On the target system, one of the following scenarios may occur: • Direct load: Replicat reads the data stream and concurrently applies the data to the target database by using SQL. • Direct bulk load (Oracle): Replicat can apply the data by using the Oracle SQL*Loader API to improve performance. • File to Replicat: Server Collector reassembles and writes the data to Extract files. Replicat applies the data to the target database by using SQL. • File to database utility: Server Collector reassembles and writes the data to files formatted for a bulk loader, which applies the data to the target database. Manager processes on both systems control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events.

Oracle Golden. Gate Components Overview 1) Manager Process: The Manager Process is the Oracle Golden. Gate parent process. It controls other Oracle Golden. Gate processes, resources, user interface, and reporting of thresholds and errors. Manager process is responsible for the following functions: Start and restart of Oracle Golden. Gate processes Starting of dynamic processes Maintaining port numbers for processes Trail file management Event, error, and threshold reports

2) Server Collector: Also known simply as Collector. The collector process is a background process that runs on the delivery (target) side when online change synchronization is active. Collector processes are needed to ensure that the following tasks are performed: Connection requests from a remote extract to manager can be scanned and bound to available ports. Then assign the port number to the requesting extract via the manager process. Receive extracted transactions that are sent by the extract and write them to the trail file. When a network connection is requested, the manager process automatically starts the collector process so there is no need for Oracle Golden. Gate users to interact with the collector process. Because the collector can only receive information from only one extract, this means that there will be one collector started for each extract started. The collector process is terminated when the extract process is terminated.

3) Replicate Process: The Replicate process is responsible for reading the trail files and applying the transactions found in chronological order. This ensures that the data is applied in the same order it was captured (SCN order). The Replicate process runs on the target system, It read extract file and replicate it to the target table. Replicate reads extract file and log files sequentially and processes the inserts , updates and deletes specified by selection parameters and Replicate reads extracted data in blocks to maximize throughput. Oracle Golden. Gate uses the SCN on an Oracle database to identify where the transactions are within the replication process. This number is also used when instantiating the replication process, so it is a very important piece of information to gather after setting up the capture process. SQL> select current_scn from v$database; SQL> select dbms_flashback. get_system_change_number from dual;

4) Extract Process: It captured committed transactions (and can be filtered) as they occur by reading the transaction logs. It is also known as Change Data Capture. The extract is used to synchronize data that is read from the online transaction log (in Oracle the online redo logs) or the associated archive logs. the extract persists the transaction to disk where it is stored in a series of files known as a trail file. Committed transactions will be stored in a trail file in sequentially organized transaction units. Once transactions are persisted to disk in a trail file, the transaction can be shipped using standard TCP/IP protocols. 5) Data Pump: Optional; highly recommended. The data pump group is a secondary extract group that is used to help ship data across network. An Extract data pump helps protect against network failures. Dump can write to one or multiple remote trails and/or servers This ability to protect the captured transactions from being lost is huge for many business reasons. If your network were to have an outage, there are two scenarios that can occur: With a network outage, the data pump process will continue to collect the trail files generated by the primary capture process and store them until the network is restored. If you are not using local trail files and only writing remote trail files, in the

Parameter Files Oracle Golden. Gate uses two types of parameter files: 1) GLOBALS file: Stores parameters that relate to the Oracle Golden. Gate instance as a whole. It control things that are common to all processes in an Oracle Golden. Gate instance and It can be overridden by parameters at the process level • GLOBALS parameters apply to all processes. – These are set when Manager starts. – They reside in <OGG_HOME>/GLOBALS. After you add or change any GLOBALS parameters, you must exit GGSCI for the new parameters to take effect. • Some of the most common parameters include: Mgr. Serv. Name ggsmanager 1: Defines a unique Manager service name on Windows Check. Point. Table oggadmin. ggschkpt: Defines the default table name used for the Replicat checkpoint table To create a parameter file in GGSCI, use the EDIT PARAM option. • The GLOBALS parameter file is identified by its file path: GGSCI> Edit Param. /GLOBALS

2) Run-time parameter file : It is specific to process. Each process has separate parameter files (Manager, Extract, Server Collector, Replicat, and utilities). – These are set when the process starts. – They override GLOBALS settings. – By default, they reside in the dirprm directory in files named <processname>. prm. – Most apply to all the tables that are processed, but some can be specified at table level. –Is coupled with a specific process (such as Extract)

GLOBALSVersus Process Parameters • GLOBALSparameters apply to all processes. These are set when Manager starts. They reside in <OGG_HOME>/GLOBALS. • Process parameters apply to a specific process (Manager, Extract, Server Collector, Replicat, and utilities). These are set when the process starts. They override GLOBALSsettings. By default, they reside in the dirprmdirectory in files named <processname>. prm. Most apply to all the tables that are processed, but some can be specified at the table level.

Trails files Overview of Trails • Trails are used in Oracle Golden. Gate to support the continuous extraction and replication of database changes. • A trail can exist on the source or target system, or on an intermediary system. • Only one primary Extract process writes to a trail. • Processes that read the trail include: –Data pump Extract –Replicat. /dirdat

Adding a Local or Remote Trail • Add a local or remote trail with the GGSCI command: GGSCI> Add Ext. Trail | Rmt. Trail<trail_name> , Extract <group_name> [, Megabytes <n>] If you are using a data pump: The primary Extract needs a local trail (Ext. Trail) The data pump Extract needs a remote trail (Rmt. Trail) GGSCI> Add Ext. Trail /u 01/app/ogg/dirdat/aa, Extract finance, Megabytes 10 GGSCI> Add Rmt. Trail /u 01/app/ogg/dirdat/bb, Extract parts, Megabytes 5 On the local system, a trail is known as an Extract trail (or local trail). On a remote system, it is known as a remote trail. The primary Extract writes to a local trail. The data pump Extract sends changes to a remote trail. The default trail file size is 500 MB. In this example, the parts Extract file is set to 5 MB.

Capture and Delivery Work Flow

Capture and Delivery Work Flow On the source system 1. An Extract process captures transactional changes from transaction logs. 2. The Extract process sends data across a TCP/IP network to the target system. On the target system 1. A Server Collector process reassembles and writes the data to an Oracle Golden. Gate remote trail. 2. A Replicat process reads the trail and applies it to the target database. (This can be concurrent with data capture or performed later. ) Manager processes on both systems control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events. In an Oracle Real Application Clusters (RAC) configuration with Oracle Golden. Gate, one primary Extract is used for each node, and each primary Extract uses its own thread of REDO.

Capture and Delivery Using a Data Pump

Capture and Delivery Using a Data Pump On the source system 1. An Extract process captures transactional changes from transaction logs. 2. The Extract process writes a local oracle golden gate trail. 3. A second Extract process (called a data pump) sends the data across the network to the target system. On the target system 1. A Server Collector process reassembles and writes the data to an Oracle Golden. Gate Remote trail. 2. A Replicat process reads the trail and applies it to the target database. (This can be concurrent with data capture or performed later. ) Manager processes on both systems control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events.

Important Keyword for Golden Gate Source. Is. Table : Source. Is. Table instructs Extract to read the source tables directly rather than from the transaction log. Rmt. Task instructs the Manager process on the target system to start a Replicat process with a group name specified in the Group clause Discard. File creates a log file to receive records that cannot be processed. MAP establishes the relationship between source table and the target table. Obey Files : Obey files are files that you create to run against an Oracle Golden. Gate environment. The content of an obey file is a sequenced set of Oracle Golden. Gate commands that can be run from a single file. The below is an example of an obey file that is used to create a capture and data pump process. .

Obey File Example --Adds Extract process ADD EXTRACT E_HR, TRANLOG, BEGIN NOW --Adds local trail file ADD EXTTRAIL. /dirdat/lt, EXTRACT E_HR, megabytes 1000 --Adds Data Pump Process ADD EXTRACT P_HR, EXTTRAILSOURCE. /dirdat/lt --Adds remote trail file ADD RMTTRAIL. /dirdat/rt, EXTRACT P_HR, megabytes 1000 You will notice in that all the commands have to do with adding the processes to Oracle Golden. Gate. Obey files are created for setting up environments and they can be created for any command you would like to run within the Oracle Golden. Gate environment. Until now, you have taken a look at the profiler that is run against an Oracle database to see if there is anything you need to worry about before setting up your replication environment. You have also taken a look at the different parameter files that are needed for the components of the replication environment. Now, let’s take a look at how to put these pieces together in a simple replication environment.

Checkpoint : Both Extract and Replicat maintain checkpoints into the trails. Checkpoints provide persistent processing whenever a failure occurs. Each process resumes where the last checkpoint was saved, guaranteeing that no data is lost. One Extract can write to one or many trails. Each trail can then be processed by one or many Replicat processes. replicate uses checkpoints to store the current read and write position. replicate maintain checkpoint that provide a known position in the trail from which to start after a expected or unexpected shutdown By default a record of these checkpoints is maintained in a trail file on disk in the oracle Golden gate directory, Optionally, the checkpoint record can also be maintained in a chechpoint table in the target database. Replicat maintains checkpoints that provide a known position in the trail from which to start after an expected or unexpected shutdown By default a record of these checkpoints is maintained in a trail file on disk in oracle Golden directory Optionally, the checkpoint record can also be maintained in a checkpoint table in the target database Using database checkpointing is recommended because It enables the checkpoint to be included within Replica's transaction which improves recovery in certain situations. The checkpoint table remains small because rows are deleted when no longer needed, and it does not affect database performance. It can reside in a schema of your choice, but Oracle Corporation

Process Report Files : It enable to view Runtime statistics for the number of operations processed. Every Extract, Replicat, and Manager process generates a report file at the end of each run. The report can help you diagnose problems that occurred during the run, such as invalid mapping syntax , SQL errors, and connection errors. To view the report, you can use the following within GGSCI: GGSCI> View Report groupname Min. Keep. Days parameter : It is used to purge used trail data (best practice). It is configured in the Manager parameter file. Tran. Data: It enables supplemental logging of primary keys and/or indexes for a schema or table in the transaction logs. Also, use Schema. Tran. Data for all the present and future tables in a schema. Creates an object or enables Tran. Data or Schema. Tran. Data capture Enabling Supplemental Logging : Adding supplemental log data forces the log writer to also include the primary keys to logs instead of just the columns that have changed.

SCHEMATRANDATA or TRANDATA There are two types of trandata that can be used within Oracle Golden. Gate. Both types do the same thing, but one is more specific to the schema level and the other is more specific to the database level. Both approaches can be used with enabling trandata, though. This section explains what each type of trandata is used for and why you should understand the use of it. SCHEMATRANDATA When you use SCHEMATRANDATAto enable schema-level supplemental logging for tables, logging will act on all of the current and future tables in an associated schema. This option automatically logs a superset of available keys that Oracle Golden. Gate requires for row identification. This option is valid for both classic and integrated capture modes with Oracle Golden. Gate. ADD SCHEMATRANDATA provides the following benefits: Enables Oracle supplemental logging for new tables created with the CREATE TABLE command. Updates supplemental logging information for tables affected by an ALTER TABLE to add or drop columns. Updates supplemental logging for tables that are renamed. Updates supplemental logging for tables where unique or primary keys are added or dropped.

An additional benefit of using ADD SCHEMATRANDATA is that by default it logs the key columns of a table in the following order of priority: Primary keys. If there are no primary keys, all unique indexes will be used for the table, including those that are disabled, unusable, or invisible. Additionally, foreign keys will be used for row dependency. If the prior two are not available, the all scalar columns of the table will be logged. TRANDATA When you have a need to capture the transaction information from the transaction records, then it is time to use ADD TRANDATAis valid for specific databases like, IBM DB 2, DB 2 LUW, DB 2 z/OS, MS SQL Server, and a few others, including the Oracle Database. This option expands on the information captured during the extraction process. Oracle recommends that force logging be enabled along with minimal supplemental logging at the database level when using Oracle Golden. Gate.

Now that you have an of the difference between ADD SCHEMATRANDATA and ADD TRANDATA, you will need to add it to database configuration through Oracle Golden. Gate. To do this, you need to log in to the Oracle Database as the Golden. Gate user through GGSCI. The steps for this process are outlined here. Tran. Data and Schema. Tran. Data do the same thing, except Tran. Data operates at the table level (including wildcards), and Schema. Tran. Data operates at the schema level. For Collision Detection, you need to add more columns than the default to the Tran. Data command defgen utility : The defgen utility program produces a file containing a definition of the layouts of the source files and tables. The output definitions are saved in an edit file and transferred to all target systems in text format. Replicat and Collector read in the definitions at process startup and use the information to interpret the data from Oracle Golden. Gate trails.

Commit Sequence Number (CSN) ◦ A CSN is an identifier that Oracle Golden. Gate constructs to identify a transaction for the purpose of maintaining transactional consistency and data integrity. ◦ A CSN uniquely identifies a particular point in time at which a transaction commits to the database. ◦ The CSN can be required to position Extract in the transaction log, to reposition Replicat in the trail, or to fulfill other purposes.

Extract Process Details • Oracle Golden. Gate moves only changed, committed transactional data, allowing it to operate with extremely high performance and very low impact. • Oracle Golden. Gate allows filtering at the schema, table, column, or row level. Row-level transformations can be applied either at this capture stage or later when delivering. • It capture committed transactions (and can be filtered) as they occur by reading the transaction logs. It is also known as Change Data Capture • Extract writes data to any of the following: – Local trail (Ext. Trail) on the local system – Local file (Ext. File) on the local system – Remote trail (Rmt. Trail) on a remote system – Remote file (Rmt. File) on a remote system

Extract Process Details Extract trails and files are unstructured, with variable length records. – I/O is performed using large block writes. • Extract writes checkpoints for trails during change capture: – This guarantees that no data is lost during restart. – Multiple Replicat processes may process the same trail. • Extract does not write checkpoints for files.

Types of Extract From 11 g. R 2 onwards, Oracle Goldengate introduced a new feature called as Integrated Extract. The main difference between the classic capture and integrated capture modes is that in the classic capture the extract reads the Oracle database online redo log files/archive log files to capture changes while in the integrated capture mode the database log mining server reads the redo log files and captures changes in the form of LCR’s (Logical Change Records) which are then accessed by the Golden Gate extract process. Integrated Extract: • Is an Oracle Golden. Gate Extract for Oracle databases • Is multithreaded • Relies on Oracle’s internal log parsing and processing implementation • Supports more data types • Supports downstream topologies • Is available with OGG version 11. 2. 1 and later Classic Extract: • Is traditional REDO log–based extract for Oracle • Works for all supported DB platforms and versions

Types of Extract

Benefits of integrated capture/Extract The following are some additional benefits of integrated capture: Because integrated capture is fully integrated with the database, no additional setup is required to work with Oracle RAC, ASM, and TDE. • Integrated capture uses the database logmining server to access the Oracle redo stream, with the benefit of being able to automatically switch between different copies of archive logs or different mirrored versions of the online logs. Thus integrated capture can transparently handle the absence of a log file caused by disk corruption, hardware failure, or operator error, assuming that additional copies of the archived and online logs are available • Integrated capture enables faster filtering of tables. • Integrated capture handles point-in-time recovery and RAC integration more efficiently. •

Benefits of integrated capture/Extract • Integrated capture features integrated log management. The Oracle Recovery Manager (RMAN) automatically retains the archive logs that are needed by Extract. • Integrated capture is the only mode that supports capture from a multitenant container database. One Extract can mine multiple pluggable databases within a multitenant container database. • For a release 11. 2. 0. 4 source database and later (with source compatibility set to 11. 2. 0. 4 or higher), the capture of DDL is performed by the logmining server asynchronously and requires no special triggers, tables, or other database objects to be installed. Oracle Golden. Gate upgrades can be performed without stopping user applications. The use of a DDL trigger and supporting objects is required when Extract is in integrated mode with an Oracle 11 g source database that is earlier than version 11. 2. 0. 4.

Example • Start a Manager process: GGSCI> Start Mgr • Add an Extract group: GGSCI> Add Extract myext, Integrated Tran. Log, Begin Now • Add a local trail: GGSCI> Add Ext. Trail /ggs/dirdat/lt, Extract myext • Start an Extract group: GGSCI> Start Extract myext In this example, a primary extract named myext extracts database changes from the transaction logs starting with records generated at the time the group was created. The changes will be written to a local trail (lt). then an extract is started, when extract is started. It creates a new trail file rather than appending to existing trail file.

Overview of Trails/Extract Parameter Files • Trails are used in Oracle Golden. Gate to support the continuous extraction and replication of database changes. • A trail can exist on the source or target system, or on an intermediary system. • Only one primary Extract process writes to a trail. • Processes that read the trail include: – Data pump Extract – Replicat If the output trail is remote, this normally triggers the target Manager process to start a Server Collector process with default parameters.

Extract Trails and Files Contents • After the capture , Oracle Golden. Gate converts the committed transactions into a canonical (universal) data format in “trail” files. Using source and target trail files, it ensures that data integrity is maintained—even in the event of a system error or outage. • Each record in the trail contains an operation that has been committed in the source database. • Committed transactional order is preserved. • Operations in a transaction are grouped together in the order in which they were applied. • By default, only the primary key and changed columns are recorded. • Flags indicate the first and last records in each transaction. • Starting from Oracle Golden. Gate release 12. 2, the information about database objects being replicated.

Adding a Local or Remote Trail Add a local or remote trail with the GGSCI command: GGSCI> Add Ext. Trail | Rmt. Trail <trail_name>, Extract <group_name> [, Megabytes <n>] • If you are using a data pump: – The primary Extract needs a local trail (Ext. Trail) – The data pump Extract needs a remote trail (Rmt. Trail) GGSCI> Add Extract Ext. Trail /u 01/app/ogg/dirdat/aa, finance, Megabytes 10 Rmt. Trail /u 01/app/ogg/dirdat/bb, parts, Megabytes 5 On the local system, a trail is known as Extract trail (or local trail). On a remote system, it is known as a remote trail the primary extract write to a local trail. The data pump Extract sends changes to a remote trail. The default file size is 500 MB. In this example, the parts Extract file is set to 5 MB. The Passthru parameter is used on a data pump if you do not need to perform any data transformations

Sizing and Retention of Trail Files Here a few recommendations that can be followed: Make the size the same as archive logs. Identify the size and frequency of redo log switches and use that as a guide. Monitor lag and trail file switch, based on size, and readjust as needed. Retention of trail files should be handled similar to retention of archive logs. Extract Trails and Files Contents • Each record in the trail contains an operation that has been committed in the source database. • Committed transactional order is preserved. • Operations in a transaction are grouped together in the order in which they were applied. • By default, only the primary key and changed columns are recorded. • Flags indicate the first and last records in each transaction. • Starting from Oracle Golden. Gate release 12. 2, the trail files also contain metadata information about database objects being replicated.

Primary Extract Configuration for Oracle on source system GGSCI> Edit Params finance Extract finance User. IDAlias oggalias Ext. Trail /ggs/dirdat/lt Table SALES. ORDERS; Table SALES. INVENTORY; Or Simple Extract Parameter File EXTRACT E_HR USERID ggate, PASSWORD ggate SETENV (ORACLE_HOME="/u 01/app/oracle/product/11. 2. 0/db_3") SETENV (ORACLE_SID="tst 12 c") TRANLOGOPTIONS DBLOGREADER EXTTRAIL. /dirdat/bt TABLE HR. *;

We can see that this is for a capture process named E_HR, uses a user named ggate to access the database, and sets up the environment to access the tst 12 c database. After the environment is set, the capture process is looking to read from the transaction log. TRANLOGOPTIONS is the parameter that controls the way the extract will interact with the database transaction log. By using the DBLOGREADER option, the extract knows to use a new application programming interface (API) that is available in Oracle 11. 2. 0. 2 and later. This API uses the database server access to mine the redo and archive logs. After accessing the transaction log, the extract checks for any long-running transactions on a 30 -minute interval, and moves captured data to the defined local trail file. Finally, the capture process captures all Data Manipulation Language (DML) coming from tables in the HR schema

Data pump A secondary Extract process that reads from a local trail and distributes that data to a remote system it allow a local trail on source system which is useful for recovery if the network or target system fails Rmt. Host is used to identify the name or IP address of the remote system and the port that is being used. • This configuration enhances the fault tolerance and reliability of the Trail overall Golden. Gate environment. In the event of a network failure (between the source and the target systems), the Oracle Golden. Gate Capture component can continue to capture transactions, because the data can be queued up locally in the trail files on the source, enhancing the recoverability in case of database failures.

Data pump Data Pump: Optional; highly recommended. Golden. Gate distributes data for routing to targets. • An Extract data pump can write to one or multiple remote trails and/or servers(not to be confused with Oracle Data Pump). • A data pump: – Reads the local trail – Manipulates the data or passes it through without change – Sends the data to one or more targets • A data pump is useful: – As a safeguard against network and target failures – To break complex data filtering and transformation into phases – To consolidate data from many sources – To synchronize one source with multiple targets.

Data pump • Trails or extract files: Can optionally be compressed and/or encrypted. Golden. Gate stages and queues data for routing. GGSCI> Edit Params mypump Extract mypump Passthru Rmt. Host <target>, Mgr. Port <port> Rmt. Trail. /dirdat/rt Table SALES. ORDERS; Table SALES. INVENTORY;

GGSCI> View Params somename -- Created by Joe Admin on 10/11/2017. Extract somename -- User. IDAlias oggalias Rmt. Host mytarget. example. com, Mgr. Port 7909 Rmt. Trail. /dirdat/rt Passthru Table SALES. ORDERS; Table SALES. INVENTORY; • User. ID and Password supply database credentials. • if running with Passthru , The User. ID lines are not required for data pumps, only for primary extracts. • Rmt. Host specifies the target system; the Mgr. Port option specifies the port where Manager is running • Rmt. Trail specifies the Oracle Golden. Gate path and trail file prefix on the target system. • The system will append 00000, 00001, 00002, and so on to the file name prefix. • Table specifies a source table for which activity will be extracted.

Data Pump Parameter File -- Verifies parameter file syntax. COMMENT OUT AFTER TESTING. CHECKPARAMS EXTRACT P_HR PASSTHRU RMTHOST 172. 16. 15. 132, MGRPORT 15000, COMPRESS RMTTRAIL. /dirdat/rt TABLE HR. *;

Data Pump Parameter File Starting in Oracle Golden. Gate 12 c (12. 2), the PASSTHRU parameter is deprecated due to metadata being shipped in the trail files. When reading this parameter file, you can tell that an extract process is set up as a data pump when you see the parameter PASSTHRU. If the PASSTHRU parameter is not included, then the extract will operate as a normal extract. Additionally, in the parameter file you will see where the trail files and transactions are shipped to with the remote host (RMTHOST) parameters. As part of the RMTHOST parameter, you need to tell the data pump how to connect to the remote server with the MGRPORT option with port number. Finally, the COMPRESS option forces the data packets to be compressed across the network. The RMTTRAIL parameter tells Oracle Golden. Gate to create a new trail file on the remote side, containing data and transformations, when they are shipped. The data pump needs to know what data is being shipped. Note The MGRPORT is the port of the manager process on the target side. The number can be different for every manager.

Replicat • The Replicat process runs on the target system. • Multiple Replicat processes can be used with multiple Extract processes in parallel to increase throughput. • Replicat can: – Read data out of Oracle Golden. Gate trails – Perform data filtering by table, row, or operation – Perform data transformation – Perform database operations just as your application performed them Oracle Golden. Gate trails are temporary queues for the Replicat process. Each record header in the trail provides information about the database change record. Replicat reads these trail files sequentially and then processes INSERTs , UPDATEs and DELETs that meets your criteria. Replicat parameters specify the following: • A group name that is also associated with a checkpoint file • A list of source-to-target relationships: – Optional row-level selection criteria – Optional column mapping facilities

Replicat Parameter Defaults Replicat parameters can be modified or can assume a default value: • Apply all insert, update, or delete operations. • Smart transactional grouping is possible. – 1, 000 source operations are grouped into a single target transaction. • Process ABENDs on any operational failure: – Rollback of transactions to the last good checkpoint – Optional error handling – Optional mapping to secondary table for exceptions

Sample Replicat Parameter File - Created by Joe Admin on 10/11/2017. Replicat salesrpt Set. Env (ORACLE_SID = 'orcl') User. ID ggsuser@myorcl, Password ggspass -- User. IDAlias oggalias Discard. File. /dirrpt/SALESRPT. dsc, Append Map HR. STUDENT, Target HR. STUDENT Where (STUDENT_NUMBER < 400000); Map HR. CODES, Target HR. CODES; Map SALES. ORDERS, Target SALES. ORDERS, Where (STATE = 'CA' AND OFFICE = 'LA'); Replicat names the group that links the process, checkpoints, and log files together. Map links the source tables to the target tables and applies mapping, selection, error handling, and data transformation, depending on options. Because the Map statements are terminated with a semicolon, they may span multiple lines in the parameter text file without any special continuation characters

Replicat Parameter File The replicat parameter file is the parameter file that tells Oracle Golden. Gate what to apply. It provides the mapping command that links the incoming transactions to the targeted tables. In active-active or multimaster environments, this file is where you would configure conflict detection and resolution (CDR) to help resolve conflicts as they occur. Replicat Parameter File REPLICAT R_HR SETENV (ORACLE_HOME="/u 01/app/oracle/product/11. 2. 0/db_3") SETENV (ORACLE_SID="tst 12 cr") USERID ggate, PASSWORD ggate map HR. *, target HR. *; Just like the extract parameter file, the replicat parameter file needs to set up the environment that is needed to connect to an Oracle database. You then need to provide a username and password to interact with the database. In older versions of Oracle Golden. Gate, the parameter ASSUMETARGETDEFS is used to correlate the metadata structure between source and target if the tables match. If the metadata of tables are different, then this parameter needs to be replaced with a SOURCEDEF file that supports the mapping. At the end of the file, you see the map statement. This statement maps the incoming

Sample Replicat Parameter File -- Created by Joe Admin on 10/11/2017. Replicat salesrpt Set. Env (ORACLE_SID = 'orcl') User. IDggsuser@myorcl, Password ggspass --User. IDAliasoggalias Discard. File. /dirrpt/SALESRPT. dsc, Append Map HR. STUDENT, Target HR. STUDENT Where (STUDENT_NUMBER < 400000); Map HR. CODES, Target HR. CODES; Map SALES. ORDERS, Target SALES. ORDERS, Where (STATE = 'CA' AND OFFICE = 'LA'); • Replicat names the group that links the process, checkpoints, and log files together. • Indicate the SID in either the Set Environment or the User ID (probably, not both). • User. ID and Password provide the credentials to access the database. •

Replicat Parameter File • Discard. File identifies the file to receive records that cannot be processed for any reason. Records will be appended or the file will be purged at the beginning of the run, depending on the options. Starting with version 12. 1. 2, creation of the discard file is the default and does not need to be specified. If you do not specify this parameter, the file name will be the process name. If it is a Coordinated Replicat, the file name will be the process name plus the thread ID. If the file already exists, you must specify Append or Purge, else you will get an error. See also Discard. Rollover. By default, the discard file rolls over for each new process start. Rolled file names are the process name plus a one-digit sequence number starting with 0 (similar to the reports). • Map links the source tables to the target tables and applies mapping, selection, error handling, and data transformation, depending on options. Because the Map statements are terminated with a semi-colon, they may span multiple lines in the parameter text file without any special continuation characters.

Checkpoint. Table On the target system: Create a checkpoint table in the target database (best practice): GGSCI> DBLogin User. IDAlias oggadmin GGSCI> Add Checkpoint. Table GGSCI> Info Checkpoint. Table Create a parameter file for Replicat: GGSCI> Edit Params Create a Replicat group: GGSCI> Add Replicat<params> Start the Replicat process: GGSCI> Start Replicat reads the Oracle Golden. Gate trail and applies changes to the target database. Like Extract, Replicat uses checkpoints to store the current read and write position and is added and started using the processing group name

Checkpoint. Table Replicat maintains checkpoints that provide a known position in the trail from which to start after an expected or unexpected shutdown. By default, a record of these checkpoints is maintained in a trail file on disk in the Oracle Golden. Gate directory. Optionally, the checkpoint record can also be maintained in a checkpoint table in the target database. Using database checkpointing is recommended because it enables the checkpoint to be included within Replicat's transaction, which improves recovery in certain situations. The checkpoint table remains small because rows are deleted when no longer needed, and it does not affect database performance. It can reside in a schema of your choice, but Oracle Corporation recommends using one that is dedicated to Oracle Golden. Gate.

Checkpoint. Table In this example, DBLogin User. IDAlias logs the user in to the database to add the checkpoint table. Note that DBLogin and User. IDAlias in the Replicat might be different. For the Replicat parameters, User. IDAlias provides the credentials to access the database. Discard. File creates a log file to receive records that cannot be processed. MAP establishes the relationship between the source table and the target table. Add Replicat names the Replicat group REPORD and establishes a local trail (Ext. Trail) with the two-character identifier rt residing in the dirdat directory. As always, the Map statements end with a semicolon; the other statements do not. SQL> SELECT * FROM ogguser. checkpointtable;

Manager Process The Manager is the Oracle Golden. Gate parent process. • The Manager controls other Oracle Golden. Gate processes, resources, user interface and reporting of thresholds and errors. Sample Manager Parameter File -- Some Comment with leading double-dashes. -- Created by Joe Admin on 10/11/2017. Port 7809 Dynamic. Port. List 9001– 9100 Autostart ER * Auto. Restart Extract *, Wait. Minutes 2, Retries 5 Lag. Report. Hours 1 Lag. Info. Minutes 3 Lag. Critical. Minutes 5 Purge. Old. Extracts. /dirdat/rt*, Use. Checkpoints

Port: Establishes TCP/IP port number on which Manager listens for requests. Dynamic. Port. List : specifies the ports that Manager can dynamically allocate Autostart: specifies he processes that are to be automatically started when Manager starts Auto. Restart: Specifies the processes to be restarted after abnormal termination Lag. Report. Hours: Sets the interval, in hours, at which Manager checks the lag for Extract and Replicat processing. Alternatively, this can be set in minutes. Lag. Info. Minutes: Specifies the interval at which Extract and Replicat will send an informational message to the event log. Alternatively, this can be set in seconds or hours. Lag. Critical. Minutes: Specifies the interval at which Extract and Replicat will send a critical message to the event log. Alternatively, this can be set in seconds or hours. Purge. Old. Extracts: Purges the Oracle Golden. Gate trails that are no longer needed, based on option settings

Manager Parameter File Notice you do not need to name the manager. In Oracle Golden. Gate environments, the manager process has a default name of MGR, so the parameter file has to be named MGR. prm. Manager Parameter File PORT 15000 Listing 3 -6 shows a simple parameter file for the manager process. The only thing required in the manager parameter file is the port number that the manager will listen on. This allows Oracle Golden. Gate instances to connect with each other and write trails files. Additionally, as mentioned earlier, the manager process is used to keep track of items like events, up or down status of processes, and errors that occur. To make better use of the items that the manager keeps track of, the parameter file can be expanded on. Listing 3 -7 shows a detailed version of the manager parameter file with additional details.

Detailed Manager Parameter File PORT 15000 DYNAMICPORTLIST 15010 -15035 PURGEOLDEXTRACTS. /dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 AUTORESTART ER *, RETRIES 6, WAITMINUTES 2, RESETMINUTES 30 LAGCRITICALSECONDS 30 LAGREPORTMINUTES In this version of the manager parameter file, you see there are more parameters added to tell the manager what to do with old trail files (PURGEOLDEXTRACTS), when to restart (AUTORESTART) the other processes if they are terminated abnormally, and when to check for lag (LAGCRITITCALSECONDS/LAGREPORTMINUTES). You will also notice there is a parameter to use dynamic ports. The DYNAMICPORTLIST parameter is used to help Oracle Golden. Gate communicate beyond firewalls if needed and limits port allocations to specific ranges for communication. This is helpful, because Oracle Golden. Gate will grab random unused ports otherwise.

Process Report Files • Process reports (depending on the process) enable you to view the following: -Parameters in use -Table and column mapping -Database information -Runtime messages and errors -Runtime statistics for the number of operations processed • These reports can be viewed with: GGSCI>View Report Any text editor Oracle Management Pack for Oracle Golden. Gate

ASM connectivity • Tranlogoptions ASMUser sys@<ASM_instance>, ASMPassword <password> • Tranlogoptions DBLogreader, Dblogreader bufsize nnn • Performance improvements • DBLogreader option with 10. 2. 0. 5 and later 10 g R 2 and oracle 11. 2. 0. 2 and later • When using DBLOGREADER with ASM, do not use the ASMUSER or ASMUSERALIAS and ASMPASSWORD options of TRANLOGOPTIONS. The API uses the user and password specified with the USERID or USERIDALIAS parameter. 98

RAC GG Node 1 Node 2 Node 3 Manager Target DB <Any Node> Archive log /redo log Extract Collector Replicate Remote Trail (Optional) Local Trail Data dump Change Synchronization 99

Basic Golden command • GGSCI is the command interface that executes Golden. Gate commands. • Start GGSCI from the Golden. Gate install directory: Shell> cd <Golden. Gate install location> Shell> GGSCI For the Help Summary page: Help: Displays information about a GGSCI command GGSCI> HELP For Help on a specific command: GGSCI> HELP <command> <object> GGSCI> HELP ADD EXTRACT

Basic Golden command • [oracle@ggtest 12 c 1 dirprm]$ $OGG_HOME/ggsci Oracle Golden. Gate Command Interpreter for Oracle Version 12. 1. 0 OGGCORE_12. 1. 0_PLATFORMS_140727. 2135. 1_FBO Linux, x 64, 64 bit (optimized), Oracle 12 c on Aug 7 2014 10: 21: 34 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (ggtest 12 c 1. acme. com) 1> Log in as the Golden. Gate database user. GGSCI (ggtest 12 c 1. acme. com) 1> dblogin userid ggate password ggate Successfully logged into database. GGSCI (ggtest 12 c 1. acme. com as ggate@src 12 c) 2> Run the desired TRANDATA option. In our case we will use SCHEMATRANDATA because this an Oracle-to-Oracle setup. GGSCI (ggtest 12 c 1. acme. com as ggate@src 12 c) 4> add schematrandata scott 2015 -07 -25 14: 06: 26 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott. 2015 -07 -25 14: 06: 26 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott. GGSCI (ggtest 12 c 1. acme. com as ggate@src 12 c) 5>

Basic Golden command • History: Lists the most recent GGSCI commands issued • Info All: Displays the status and lag for all Oracle Golden. Gate online processes on a system (Info All. Processes also displays tasks. ) • Obey: Runs a file containing a list of GGSCI commands • Shell: Runs shell commands from within GGSCI • Show: Displays the Oracle Golden. Gate environment • Versions: Displays OS and database versions • View GGSEvt: Displays the Oracle Golden. Gate event/error log ggserr. log -- known as error log • View Report: Displays a process report for Extract or Replicat health check 102

Basic Golden command Info {Extract | Replicat} <group> [Detail]: Run status, checkpoints, approximate lag, and environmental information Info Manager | Mgr: Run status and port number Stats {Extract | Replicat} <group>: Statistics for operations processed Status {Extract | Replicat} <group>: Run status (starting, running, stopped, and abended) Status Manager | Mgr: Run status Lag {Extract | Replicat} <group>: Latency between last record processed and time stamp in the data source 103

Basic Golden command Info {Ext. Trail | Rmt. Trail} <path_name>: Name of the associated process, position of the last data processed, and maximum file size Send Manager: Run status, information about child processes, port information, and trail purge settings Send {Extract | Replicat}: Depending on the process, information about memory pool, lag, TCP stats, long-running transactions, process status, recovery progress, and so on View Report <group>: Contents of the process report View GGSEvt: Contents of the Oracle Golden. Gate error log <command> ER <wildcard>: Information dependent on the <command> type: -Info -Lag -Send -Stats -Status <wildcard> is either * or ? . 104

Basic Golden command • To start and monitor golden Gate , Go to golden Gate home folder and run GGSCI Server 1: Test. DB: /ogg/Test. DB/ggs $ls –ltr drwxr-xr-x 26 oracle dba 4096 May 05 15: 35 gghome drwxr-xr-x 2 oracle dba 16384 Jun 09 12: 48 backup_arch Server 1: Test. DB: /ogg/Test. DB/ggs $cd gghome Server 1: Test. DB: /ogg/Test. DB/ggs/gghome $. /ggsci for windows C: GG> ggsci. exe TO CHECK PROCESS GGSCI (Server 1) 2> info all Program MANAGER Status Group Lag at Chkpt Time Since Chkpt RUNNING EXTRACT ABENDED CAM_CAP 00: 03 EXTRACT RUNNING CAM_PUMP 00: 00 03: 48: 38 00: 08 GGSCI (Server 1) 3> info mgr Manager is running (IP port Server 1. 7809, Process ID 47317384). 105

• GGSCI (Server 1) 3> SHOW ALL Parameter settings: SET SUBDIRS ON SET DEBUG OFF Current directory: /ogg/Test. DB/ggs/gghome Using subdirectories for all process files Editor: vi Reports (. rpt) /ogg/Test. DB/ggs/gghome/dirrpt Parameters (. prm) /ogg/Test. DB/ggs/gghome/dirprm Replicat Checkpoints (. cpr) /ogg/Test. DB/ggs/gghome/dirchk Extract Checkpoints (. cpe) Process Status (. pcs) SQL Scripts (. sql) /ogg/Test. DB/ggs/gghome/dirchk /ogg/Test. DB/ggs/gghome/dirpcs /ogg/Test. DB/ggs/gghome/dirsql Database Definitions (. def) /ogg/Test. DB/ggs/gghome/dirdef Dump files (. dmp) /ogg/Test. DB/ggs/gghome/dirdmp Masterkey wallet files (. wlt) /ogg/Test. DB/ggs/gghome/dirwlt Credential store files (. crd) /ogg/Test. DB/ggs/gghome/dircrd 106

GGSCI (Server 1) 6> info extract CAM_CAP EXTRACT CAM_CAP Last Started 2017 -06 -13 10: 43 Status RUNNING Checkpoint Lag Process ID 00: 08 (updated 00: 03 ago) 49480138 Log Read Checkpoint Oracle Integrated Redo Logs 2017 -06 -14 15: 44: 30 SCN 2355. 4077014034 (10118724996114) GGSCI (Server 1) 7> info extract CAM_PUMP EXTRACT CAM_PUMP Last Started 2017 -06 -13 10: 46 Status RUNNING Checkpoint Lag Process ID 00: 00 (updated 00: 00 ago) 32440748 Log Read Checkpoint File /ogg/Test. DB/ggs/gghome/dirdat/cc 000000013 2017 -06 -14 15: 44: 17. 000000 RBA 3570596 107

• GGSCI (TARGET) > Lag Replicat rep_2 a Sending GETLAG request to REPLICAT REP_2 A. . . Last record lag: 34 seconds. At EOF, no more records to process. • GGSCI (TARGET) > GGSCI (SOURCE) > Info Rmt. Trail * Extract Trail: . /dirdat/vv Extract: EXT_2 A Seqno: 2 RBA: 2399277 File Size: 5 M Extract Trail: . /dirdat/rt Extract: PUMP_2 A Seqno: 2 RBA: 2399565 File Size: 5 M GGSCI (SOURCE) > Info All, All. Processes 108

GGSCI (TARGET) > Stats Replicat (. . . many lines omitted for clarity. . . ) rep_3 a *** Hourly statistics since 2014 -06 -07 16: 44: 26 *** Total inserts 0. 00 Total updates 1. 00 Total deletes 0. 00 Total discards 0. 00 Total operations 1. 00 ***Latest statistics since 2014 -06 -07 16: 44: 26 *** Total inserts 0. 00 Total updates 1. 00 Total deletes 0. 00 Total discards 0. 00 Total operations 1. 00 End of Statistics. GGSCI (TARGET) > 109

ggsci> HELP [command] [object] ggsci> help GGSCI Command Summary: Object: Command: SUBDIRS CREATE ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP EXTTRAIL ADD, ALTER, DELETE, INFO GGSEVT VIEW MANAGER INFO, REFRESH, SEND, START, STOP, STATUS MARKER INFO PARAMS EDIT, VIEW REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP REPORT VIEW RMTTRAIL ADD, ALTER, DELETE, INFO TRACETABLE ADD, DELETE, INFO 110

ggsci> INFO EXTRACT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] - To display Status of Extract, Approximate Extract lag, Checkpoint information, Process run history ggsci> info extract emp_ext ggsci> info extract cust_ext, detail ggsci> info extract ext*, showch ggsci> info extract *, detail ggsci> info extract hr, tasks ggsci> INFO REPLICAT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] - To display status of Replicat, Approximate replication lag, the trail from which Replicat is reading, Replicat run history, including checkpoints in the trail, Information about the Replicat environment. ggsci> info replicat emp_rep, detail ggsci> info replicat prd*, detail, allprocesses ggsci> info replicat *, tasks ggsci> info replicat fin, showch ggsci> INFO EXTTRAIL trail_name -- To retrieve configuration information for a local trail ggsci> info exttrail * 111

Microservices Architecture: Main Components The MA consists of five main components (servers or services: ) Service Manager It acts as a watchdog for other services available with Microservices Architecture. Administration Server It supervises, administers, manages, and monitors processes operating within an Oracle Golden. Gate deployment. Distribution Server It functions as a networked data distribution agent in support of conveying and processing data and commands in a distributed networked deployment. Receiver Server It is the central control service that handles all incoming trail files. Performance Metrics Server It uses the metrics service to collect and store instance deployment performance results. 112

113

114

115

The seven steps for a data path in the Oracle Golden. Gate MA environment: 1. Data is captured from the source database 2. The Extract process stores the captured data in trail files 3. The Distribution Server accesses the trail files 4. The Distribution Server ships the trail files over the network to the Receiver Server 5. The Receiver Server saves to disk the trail files that it receives from the Distribution Server 6. The Replicat process reads the trail files that are made available by the Receiver Server 7. The Replicat process applies all committed changes to the target database 116

Troubleshooting with Case Study GGSCI information commands • The ggserr. log file (known as the error log) • Process reports (dirrpt/*. rpt) • The discard file (dirrpt/*. dsc) • The Event Viewer on Windows systems or the syslog on UNIX systems to view errors at the operating-system level Handling TCP/IP Errors The tcperrs file (TCP Errors), which is located in the Oracle Golden. Gate installation directory of the target system, can help with troubleshooting TCP/IP errors like Bottlenecks , Connection refused error , Not enough bandwidth

Troubleshooting with Case Study Process Report Each Extract, Replicat, and Manager has its own report file that shows: • A banner with startup time • Parameters that are in use • Table and column mapping • Database and environmental information • Runtime messages and errors The Process report provides initial clues, such as: • Invalid or out-of-order parameters • Data mapping errors You can: • View Process reports using View Report <group> in GGSCI • The default location is the dirrpt directory in the Oracle Golden. Gate home location. 118

Troubleshooting with Case Study GGSCI (SOURCE) > View Report ext_4 a Source Context : Source. Module : [er. init] Source. ID : [/scratch/aime 1/adestore/views/aime 1_adc 4150256/o ggcore/Open. Sys/src/app/er/init. cpp] Source. Function : [init_functions] Source. Line : [3985] Thread. Backtrace : [7] elements [/u 01/app/oracle/product/ogg_src/libgglog. so(CMessage. Context: : Add. Thread. Context ()+0 x 1 e) [0 x 7 f 6 d 61 a 1670 e]] [/u 01/app/oracle/product/ogg_src/libgglog. so(CMessage. Factory: : Create. Message(CS ource. Context*, unsigned int, . . . )+0 x 2 cc) [0 x 7 f 6 d 61 a 0 f 6 ac]] : [/u 01/app/oracle/product/ogg_src/libgglog. so(_MSG_ERR_STARTUP_PARAMERROR_ERRO R TEXT(CSource. Context*, char const*, CMessage. Factory: : Message. Disposition)+0 x 31) [0 x 7 f 6 d 61 a 02 b 8 d]] : [/u 01/app/oracle/product/ogg_src/extract(init_functions(int, char**)+0 x 880) [0 x 54 b 5 f 0]] : [/u 01/app/oracle/product/ogg_src/extract(main+0 xbf) [0 x 57359 f]] : [/lib 64/libc. so. 6( libc_start_main+0 xfd) [0 x 3 fe 861 ec 9 d]] : [/u 01/app/oracle/product/ogg_src/extract( gxx_personality_v 0+0 x 38 a) [0 x 4 edaba]] 2014 -06 -07 20: 22: 42 ERROR OGG-00303 match PROCESS ID [EXT_4 A]. Group name [ext 4 a] in param file does not 119

Troubleshooting with Case Study

Process Report: Sample

Event (Error) Log • Oracle Golden. Gate Event Log lists show: A. A history of GGSCI commands B. Processes that started and stopped C. Errors that occurred D. Informational messages • The Event Log also shows events leading to an error. • For example, you might discover one of the following: -Someone stopped a process. -A process failed to make a TCP/IP or database connection. -A process could not open a file. • Use one of the following to view the Event Log: -A standard text editor or a shell command -The GGSCI command View GGSEVT 122

The log� s name is ggserr. log and it is located in the root Oracle Golden. Gate directory. You can also locate the file by using the Info Extract <group>, Detail command. The location of the ggserr. logfile is listed with the other Oracle Golden. Gate working directories: GGSCI> Info Extract oraext, Detail EXTRACT ORAEXT Last Started 2014 -03 -28 10: 45 Status STOPPED Checkpoint Lag 00: 00 (updated 161: 55: 17 ago) Log Read Checkpoint File u 01/app/oracle/oradata/orcl/REDO 03. LOG 2014 -03 -29 17: 55: 57 Seqno 34, RBA 104843776 <some contents deliberately omitted> Current directory /home/oracle/OGG_T/ Report file /home/oracle/OGG_T/dirrptORAEXT. rpt Parameter file /home/oracle/OGG_T/dirprmORAEXT. prm Checkpoint file /home/oracle/OGG_T/dirchkORAEXT. cpe Process file / home/oracle/OGG_T/dirpcsORAEXT. pce Error log /home/oracle/OGG_T/ggserr. log 123

124

125

Filtering the Log for Errors Because the file can become quite large, you may want to parse the file for errors or commands only, as in the following example: $ more ggserr. log | grep ERROR The preceding filter shows only errors: Shell>cat ggserr. log | grep ERROR 2014 -03 -13 10: 48: 28 GGS ERROR 112 Golden. Gate Capture for Oracle, EXT_CTG 1. prm: Could not create /home/oracle/OGG_T/dirdat/g 1000000 (error 2, No such file or directory). Other ways to read and filter this data: 126

Discard File A discard file: • Is created if the Discard. File <file_name> parameter is used in the Extract or Replicat parameter file • Is created when Extract or Replicat has a problem with the record that it is processing • Contains column-level details for operations that the process cannot handle Is usually used for Replicat to log operations that cannot be reconstructed or applied • Can help you resolve data-mapping issues 127

Logdump utility The logdump utility enables you to: Display or search for information that is stored in Oracle Golden. Gate trails or extract files Save a portion of an Oracle Golden. Gate trail to a separate trail file To start logdumpfrom the Oracle Golden. Gate installation directory: [OS prompt]. /logdump To access help: [OS prompt] Logdump 1>help Opening a Trail Logdump>open dirdat/rt 00000 Current Log. Trail is /ggs/dirdat/rt 00000 To view the trail file header: Logdump 1>fileheader on To view the record header with data: Logdump 2>ghdr on To add column information: Logdump 3>detail on To add hex and ASCII data values to the column list: Logdump 4>detail data To control how much record data is displayed: 130

Logdump utility Viewing Trail Records To go to the first record and to move from one record to another in sequence: Logdump 6 > pos 0 Logdump 7 > next Or just type n. To position at an approximate starting point and locate the next good header record: Logdump 8 > pos <approximate RBA> Logdump 9 > scanforheader Or just type sfh. 131

Logdump utility 132

Logdump utility 133

Startup Problems: Overview Oracle Golden. Gate Configuration • • • – – Is the database running? Is Manager running? Does the group exist? Use Info All to view all processes and groups on the system. Is the group name spelled wrong? Does a trail or extract file exist? If not, Extract cannot write its initial checkpoint, and Replicat has no data source to read. Use the Detailoption with Info Extract <group> or with Info Replicat <group> to see if a trail or file exists. • Is the parameter file missing? – Check for the file in the Oracle Golden. Gate dirprm/ subdirectory. – The file must have the same name as the group. — Check the case of the name. If you copied the file from Windows to UNIX, the case might be wrong; it should be all lowercase. – If the file is not there, did you store it somewhere else? — To store a parameter file elsewhere (not recommended), use the PARAMSargument with Add Extract or Alter Extract. 134 • Is the parameter file inaccessible?

Startup Problems: Overview Startup Problem: When Extract or Replicat starts, it queries the database for table attributes and builds an object record. When Oracle Golden. Gate analyzes a large volume of tables, it takes a long time and seems to be stalled. Use Wildcard. Resolve If wildcards are being used for table names, you can use the Wildcard. Resolveparameter with the Dynamicoption. Source tables that satisfy wildcard definitions are resolved each time the wildcard rule is satisfied (instead of all together at startup). Best Practice You should use Dynamic. Resolutionwhen Extract is configured for a large number of tables. 135

Common Issues • The Table and Map parameters must end with a semicolon. • In the syntax, a comma must be followed by a space. • When using the Col. Map parameter, be sure to include all commas, quotation marks, and parentheses. • If "userid" and "pswd" are enclosed in double quotation marks, they might be case sensitive in some databases What should you do if a process fails? • View the process report: – View Report <group> – You can also use Oracle Golden. Gate Director to view the report. • The report shows basic troubleshooting information: – Parameters that are used – Tables that are mapped – Key-column information – Specific details about the error

Basic Troubleshooting: Example 1 (Slide 1 of 2)

138

139

140

Extract Is Slow • • • Is Extract waiting for an archive log? Can Extract locate the archive logs? Is the slowness due to low volume? • Is Extract waiting for an archive log? -If Extract is restarted when there is a long-running transaction, it may need to read many old logs that have already been processed. • Can Extract locate the archive logs? -If the archives are not in the default Oracle Database location, use the Alt. Archive. Log. Dest <path_name> Extract parameter to specify the alternative location. • Is the slowness due to low volume? -If there are only 100 transactions being generated per second, the maximum speed is 100 transactions per second. For Oracle tables, query the v$transactiontable to see if there are open transactions

Extract can be slow when there are numerous large transactions and when tables contain many LOBs. • If necessary, use the Cache. Mgrparameter (with the assistance of Oracle Technical Support) to control the virtual memory and temporary disk space that are available for caching uncommitted transaction data. • To view the Cache. Mgrsettings, issue a View Report command for the Extract. When to Adjust Cache. Mgr The memory manager generates statistics that can be viewed with the Send Extract command when used with the Cache. Mgr option. The statistics show the size of the memory pool, the paging frequency, the size of the transactions, and other information that creates a system profile. Based on this profile, you might need to make adjustments to the memory cache if you see performance problems that appear to be related to file caching. The first step is to modify the Cache. Size and Cache. Page. Out. Size parameters. You might need to use a higher or lower cache size, a larger or smaller page size, or a combination of both, based on the size and type of transactions that are being generated. You might also need to adjust the initial memory allocation with the Cache. Buffer. Size option. Syntax Cache. Mgr { [, Cache. Size <size>] [, Cache. Buffer. Size <size>] 142 [, Cache. Directory <path> [<size>] [, . . . ]] [, Cache. Page. Out. Size <size>]

Extract Abends • Is Extract unable to open the transaction logs? – Check to see if the disk is full. If it is not full, the Extract user may not have read permissions on the files. • After permissions are granted, perform the following steps: 1. Stop Manager. 2. Exit GGSCI. 3. Close the terminal session. 4. Start the processes again from a new session. • Is an archive log missing? – Restore the archive log that is specified in the error message in the report file (and subsequent archive logs)

Using Archive Logs Extract reads the archive logs when an online log is not available. If Extract is shut down for any length of time, the records that it needs on restart were probably archived away. On startup, Extract searches for the missing data. When the log cannot be found, Extract abends. Extract should never be stopped intentionally―at least not for long. If it is stopped either intentionally or otherwise, make certain that archives are available all the way back to the time of failure. If the length of time during which Extract was down is exceptionally long, you might find it more practical to resynchronize the tables rather than wait for Extract to go through the archives and catch up. If the naming of the archive logs is changed, Oracle Golden. Gate may not be able to find them because Oracle Golden. Gate simply increments the number and looks for the next archive. It is possible to prevent missing archive log errors by enabling an Extract group in classic capture mode to work with Oracle Recovery Manager (RMAN) to retain the logs that Extract needs for recovery. Consider the following example: GGSCI> Register Extract <group> Log. Retention This example creates an underlying Oracle Streams capture process that is dedicated to the Extract group and is used only for log retention. The Streams capture process has a similar name. 144

Solving Connection Problems Overview In this practice, you look at Extract connection problems. Tasks Problem: “Return to sender, address unknown” 1. Start the Extract group, ext_5 b.

3. View the report file. a. Execute the command to view the report: GGSCI (SOURCE) > View Report ext_5 b b. Notice the following error: OGG-00303 Could not getaddrinfo for host. (. . . many lines omitted for clarity. . . ) : [/u 01/app/oracle/product/ogg_src/extract(main+0 x 3 f) [0 x 5 b 8 b 0 f]] : [/lib 64/libc. so. 6( libc_start_main+0 xfd) [0 x 3 da 7 c 1 ed 1 d]] : [/u 01/app/oracle/product/ogg_src/extract() [0 x 526289]] 2014 -06 -07 23: 10: 02 ERROR OGG-00303 Could not getaddrinfo for host. 2014 -06 -07 23: 10: 02 ERROR OGG-01668 PROCESS ABENDING. GGSCI (SOURCE) > c. Check the error log for any additional information. You see messages similar to the following: 146

147

Using Archive Logs Extract reads the archive logs when an online log is not available. If Extract is shut down for any length of time, the records that it needs on restart were probably archived away. On startup, Extract searches for the missing data. When the log cannot be found, Extract abends. Extract should never be stopped intentionally―at least not for long. If it is stopped either intentionally or otherwise, make certain that archives are available all the way back to the time of failure. If the length of time during which Extract was down is exceptionally long, you might find it more practical to resynchronize the tables rather than wait for Extract to go through the archives and catch up. If the naming of the archive logs is changed, Oracle Golden. Gate may not be able to find them because Oracle Golden. Gate simply increments the number and looks for the next archive. It is possible to prevent missing archive log errors by enabling an Extract group in classic capture mode to work with Oracle Recovery Manager (RMAN) to retain the logs that Extract needs for recovery. Consider the following example: GGSCI> Register Extract <group> Log. Retention This example creates an underlying Oracle Streams capture process that is dedicated to the Extract group and is used only for log retention. The Streams capture process has a similar name. 148

Replicat Abends Is Replicat unable to locate a trail? • Issue Info Extract <group> and note the trail name. • Make sure that the trail was created: 1. Issue Info Rmt. Trail * (or Info Ext. Trail * if local). 2. Create the trail (if necessary). 3. Issue Alter Replicat to read that trail. Is Replicat unable to open a trail file? • Verify that the Replicat user has read and write permissions on trail files You can confirm a trail’s existence by looking in the dirdat directory of the Oracle Golden. Gate home location. You should see the same information that is shown by Info Extract and Info Rmt. Trail, and the same information that is specified with Rmt. Trail when you view the Extract parameter file 152

In this example of a Replicat discard file, the CUST_CODE column was not updated and therefore has a NULL value because supplemental logging using the Add Tran. Data command was not used. For Oracle databases, you have the option of using Add Schema. Tran. Data. This command does not work on non-Oracle databases.

Solving Replicat Startup Problems Overview In this practice, you explore problems that prevent Replicat from starting successfully. You look at problems with Replicat parameters, at security problems, and at problems with connecting to the database. Note: The problems in this section again exist in the same parameter file. Carefully consider the report information and the possible causes of the problem you are working on to ensure that if you see an error in the file, it is the error that will actually solve that problem.

3. View the report file. You should see an error similar to the following: GGSCI (TARGET) > View Report rep_4 a (. . . many lines omitted for clarity. . . ) : [/lib 64/libc. so. 6( libc_start_main+0 xfd) [0 x 3 da 7 c 1 ed 1 d]] : [/u 01/app/oracle/product/ogg_trg/replicat() [0 x 508339]] 2014 -06 -07 22: 16: 17 ERROR OGG-01453 Database login information not specified in parameter file. 2014 -06 -07 22: 16: 17 ERROR OGG-01668 PROCESS ABENDING. GGSCI (TARGET > 4. What database user ID are you using to log in? 5. Edit the rep_4 a parameter file. What deficiency in this file does the clue in the report point to? GGSCI (TARGET) > Edit Param rep_4 a 6. Fix the problem

Contact Information Tel: +91 9606734482 Email: easyreliable@gmail. com/ support@easyreliable. com For more details: http: //easyreliable. com/training/dba-trainingcourses/oracle-database-11 g/ Web: www. easyreliable. com

Bi Directional Replication Change Synchronization Data dump Replicate Remote Trail Extract Archive log /redo log Local Trail Manager Source DB Target DB Archive log /redo log Extract Collector Replicate Remote Trail (Optional) Local Trail Data dump Change Synchronization

Conflict Detection What is considered a data conflict? A conflict is a mismatch between the before image of a record in the trail and the current record in the target table. How is current record on the target table found? By replication key Conflict Type Insert conflict Uniqueness violation on replication key, primary key or unique key Update row missing The row can not be found by the replication key. Update row exists The row is found by replication key but before image of incoming record doesn’t match current record on compared columns in target database. Delete row missing The row can not be found by the replication key. Delete row exists The row is found by replication key but before image of incoming record doesn’t match current record on compared columns in target database.

Conflict Detection Oracle Golden. Gate uses key columns to identity the records and then detects conflicts by comparing the data before and after the transaction updates. You need the following configurations for the conflict detection: Replicating the full before-image of each record: you need to use ADD TRANDATA to include the information in the redo log and use LOGALLSUPCOLS in the extract to include the data in the Golden. Gate trail files. Use COMPARECOLS to specify the SQL operation (UPDATE | DELETE) and key columns (ALL | KEYINCLUDING | ALLEXCLUDING) to detect conflicts. In the example, Oracle Golden. Gate checks the conflicts of all the UPDATE operations on all but the comment column on the fin. src table. Why do I get the " WARNING OGG-02180 Table xxx will use legacy trail format to support parameter UPDATEBEFORES. " message? In Oracle Golden. Gate 12. 3, you would use LOGALLSUPCOLS instead of GETBEFOREUPDATES. You might get the following error message for all of your tables: "WARNING OGG-02180 Table xxx will use legacy trail format to support parameter UPDATEBEFORES. "

Conflict Resolution Oracle Golden. Gate provides REOLVECONFLICT to defines the resolutions for each conflict. The following is the list of conflicts: INSERTROWEXISTS: Insert to an existing record. UPDATEROWMISSING: Update a non-existing record. UPDATEROWEXISTS: Update a record with different value (the before image is different). DELETEROWMISSING: Delete a non-existing record. DELETEROWEXISTS: Delete a record with different value (the before image is different). The following is the list of resolution functions: USEMAX and USEMAXEQ USEMIN and USEMINEQ USEDELTA DISCARD OVERWRITE IGNORE

Enable Conflict Detection Specify the columns that replicats use to detect update and delete conflict in replicat file MAP scott. table 1, TARGET scott. table 1, & COMPARECOLS (ON UPDATE <columns>, ON DELETE <columns> ) Different ways to specify <columns> ALL (highest workload, best data convergence) KEY (replication key)(fastest, least data quality) KEYINCLUDING (col 1, . . . ) ALLEXCLUDING (col 1, . . . ) KEYANDMOD (key and modified columns)

COMPARECOLS requires that The before image must be present in the trail file Specified columns must exist in the target database Can only compare scalar data types Scalar data types Numeric, Date, Character Non Scalar data columns must be excluded from the comparison. For example, LOBs, user defined, spatial, reference, raw etc.

Put before image into DB log files DB default logging is not enough Enable DB level force logging and minimum supplemental logging ALTER DATABASE FORCE LOGGING; force logging of all transactions ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; add row chaining info into log ALTER SYSTEM SWITCH LOGFILE; Enable schema or table level supplemental logging ADD SCHEMATRANDATA (when DDL replication is included) ADD TRANDATA By default, log primary key unconditionally weather the key is changed or not and log scheduling columns (primary key, unique key, and foreign key columns) if one of them is changed. NOSCHEDULINGCOLS –

Effect of ADD TRANDATA scott. table 1 ALLCOLS SQL> select * from dba_log_groups where table_name =‘TABLE 1'; OWNER LOG_GROUP_NAME ---------------TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED SCOTT TABLE 1 GGS_18413 USER LOG GROUP ALWAYS USER NAME SCOTT TABLE 1 SYS_C 006351 PRIMARY KEY LOGGING ALWAYS GENERATED NAME SCOTT TABLE 1 SYS_C 006352 UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME SCOTT TABLE 1 SYS_C 006353 FOREIGN KEY LOGGING GENERATED NAME SCOTT SYS_C 006354 CONDITIONAL

Put before image into GG trail file � In extract parameter file � All columns will be captured when there is no PK, UK column(s), or alternate key specified by TABLE. . . KEYCOLS (columns) � LOGALLSUPCOLS in GG 12. � Record the before image of all supplemental logged columns for both UPDATE and DELETE operations. � NOLOGALLSUPCOLS is default. � GETUPDATEBEFORES and NOCOMPRESSUPDATES below GG 12 � IGNOREUPDATEBFORES and COMPRESSUPDATES are default � TABLE scott. table 1 COLS (col 1, col 2), must include key columns in the list � TABLE scott. table 1 COLSEXCEPT (col 1, col 2), does not exclude key columns � TABLE scott. table 1, GETBEFORECOLS (ON UPDATE <option>, ON DELETE <option> );

Conflict Resolution Out of the box

Difference between Discard and Ignore Discard Retain the current value in the target database, and write the data in the trail record to the discard file. Ignore Retain the current value in the target database, and ignore the trail record. No record is written into discard file.

Conflict Resolution out of the box � Example MAP scott. table 1, TARGET scott. table 1, & COMPARECOLS (ON UPDATE ALL, ON DELETE ALL), & RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, IGNORE)), & RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)), & RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)), & RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), & RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX ( last_updated )) );

Conflict Resolution other considerations Adjust sequences used to populate primary or unique keys Using triggers to populate resolution based columns such as last updated timestamp is preferred, Use same database/host time zone for replicated databases if timestamp based resolution is chosen. Group tables with parent child relationship, or tables that can have logical relationship in one transaction in same replication pipe Exception table or discard files Discard file DISCARDFILE <directory>/replicat. dsc, MEGABYTES 50, APPEND DISCARDROLLOVER at 12: 00 Exception table MAP scott. table 1, TARGET

Conflict Resolution other considerations REPERROR (error, response) in replicate file Can be global or at each table level (in MAP statement) REPERROR ( DEFAULT |DEFAULT 2|SQL error| ABEND -default behavior IGNORE -ignore the error. DISCARD -send to discard file EXCEPTION -to be handled by MAP exception statement TRANSABORT -abort at transaction level TRANSDISCARD