SharePlex for Oracle 8.5

(1)

SharePlex for Oracle 8.5

Architecture, installation, usage, troubleshooting

Dina Kleiman

(2)

• Learning Objectives- Slide 3

• SharePlex for Oracle 8.5 Architecture – Slide 4-13

• Installation and configuration Slide 14-27

• Diagnose and Troubleshoot Slide 28-36

• Documentation-Slides 37

• Common Error Messages- Slide 38

(3)

Learning Objectives

Upon completion of this presentation, the student

should be able to:

–

Understand SharePlex architecture

–

Demonstrate How to install and start SharePlex

–

Know how to determine which SharePlex module is experiencing the

issue

–

Where to locate error messages

–

Where to gather information to resolve the issue

–

How to collect appropriate logs

(4)

Part 1 - Architecture

• Upon completion of this lesson, the student should be

able to:

–

Describe SharePlex architecture

–

Describe each process and queue in detail

• Click on below link to watch a video that describes

SharePlex architecture, its processes and queues.

http://supportpreview.prod.quest.corp/SolutionDetailPrintVie

w.aspx?id=SOL92662&st=Published

(5)

SharePlex for Oracle Architecture

Source

System

Capture

Q

Export

Q

Target

System

Post

Q

Post

Process

Import

Process

Export

Process

Network

Quest

Transport

Layer

Read

Process

Capture

Process

Oracle

Log Files

(6)

Capture process

• Reads redo/archive logs on source

• Looks for changes to objects listed

• Writes to the capture queue

• One capture per source database

• <proddir>/bin/sp_ocap

Capture

Process

(7)

Capture queue

• Resides on the source system

• Reads data from the Oracle logs

• Holds the data outside the Oracle

• Source instance is identified by datasource, for

example

o.oraA.

• One capture queue per datasource (SID)

Capture

Q

(8)

Read process

• Reads data from the capture queue

• Adds routing information to the data

• Builds the where clause when necessary

• Sends data to the export queue

• One read per source database

• <proddir>/bin/sp_ordr

Read

Process

(9)

Export queue

• Resides on the source system

• Holds data processed by Capture and Read

• Data is ready for transport to the target system

• One or many export queues (named queues)

Export

Q

(10)

Export process

• Runs on source

• Reads data from the export queue

• Sends it across the network to the target system’s

import process

• One process per target

• Part of Export/Import transport pair

• <proddir>/bin/sp_xport

Export

Process

(11)

Import process

• Second half of the Export/Import transport pair

• Runs on target

• Receives data from export

• Writes to post queue

• One import for each export queue

• One of many post queues

• <proddir>/bin/sp_mport

Import

Process

(12)

Post Queue

• Resides on target system

• Holds data that is ready for writing to the target database

• One or more post queues per source

SourceA and SourceB -> TargetC (2 post queues on

TargetC)

• Default name:

SourceHostname (o.SourceSID-o.targetSID)

• Named post queues can be created

Post

Q

(13)

Post process

• Runs on target system

• Read the post queue

• Constructs SQL statements to Oracle database

• Applies SQL to target objects

• One post process per each post queue

• <proddir>/bin/sp_opst_mt

Post

Process

(14)

Part 2 - Installation

• Upon completion of this lesson, the student should

be able to:

–

Download the SharePlex binary

–

Install SharePlex

–

Start SharePlex

–

Set up replication

–

Run basic commands

(15)

Part 2 - Installation

• Binary is located on supportlink

–

http://www.quest.com/

• Select binary for your OS and Oracle versions!

• Place binary on the server

• Extract using the tar xvf command

(16)

Running the install: Part 1

• Go over Pre-Installation checklist in the Install Guide

• Obtain the license key

• Execute the .tpm file and follow the prompts

–

./SharePlex-8.5.0-b40-oracle100-sun-10-x86-m64.tar

• This will create proddir, vardir and install binaries and

libraries

(17)

Running the install: Part 2

• Make sure database is installed and open

• Make sure the following are set:

–

ORACLE_HOME

–

ORACLE_SID

–

SP_SYS_VARDIR

• Run ora_setup and follow the prompts

–

./ora_setup

• Ora_setup:

(18)

Adding a new key

• Option 1

–

At time of install, when prompted

• Option 2

(19)

Start SharePlex

• Go to the binary directory

• Make sure the following are set:

–

ORACLE_HOME

–

ORACLE_SID

–

SP_SYS_VARDIR

• Locate sp_cop binary

• Execute it

(20)

Set up replication

• Log into sp_ctrl

–

./sp_ctrl

• Create the config file

sp_ctrl> create config demo.cfg

–

Specify datasource – source SID

–

Specify name of source and target tables

–

Specify target server name and SID

• Either specify one table per line or use wild cards to

replicate an entire schema

(21)

Set up replication – config file examples:

• Specify one table per entry

datasource:o.orcl1

splex.demo_src

splex.demo_dest [email protected]

• Replicate an entire schema

datasource:o.orcl1

splex.% splex.% [email protected]

• Setup a named post queue

datasource:o.orcl1

splex.demo_src

splex.demo_dest targetserver

:queue_name

@o.orcl2

(22)

Start replication

• Activate config

sp_ctrl> activate config demo.cfg

• To see a list of all config files

sp_ctrl> list config

(23)

Common sp_ctrl commands

• To see queues

sp_ctrl> qstatus

• To see processes

sp_ctrl> show

• To see parameters set for each process

sp_ctrl> list param all <process name>

• To see modified parameters

(24)

Common sp_ctrl commands cont:

• To see details about capture

sp_ctrl> show capture detail

• To see details about read

sp_ctrl> show read internal

• To see details about export

sp_ctrl> show export

• To see details about import

sp_ctrl> show import

(25)

Controlling DDL replication

• Set parameter SP_OCT_REPLICATE_DDL

• sp_ctrl> set param SP_OCT_REPLICATE_DDL 0|1|2|3

–

0 (disable replication of both ALTER and TRUNCATE)

–

1 (enable ALTER replication only)

–

2 (enable TRUNCATE replication only)

–

3 (enable replication of DDL

NOTE: For more information about this parameter and other SharePlex user

configurable parameters, see SharePlex for Oracle Reference Guide

.

(26)

Controlling posting on target

• Disable posting to one object

1. SOURCE: sqplus> select object_id from dba_objects where object_name='<SOURCE

TABLE NAME>‘

2. TARGET: sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM <SOURCE OBJID

from step 1>

NOTE: click on the below link to watch a video about setting SP_OPO_DISABLE_OBJECT_NUM

http://supportpreview.prod.quest.corp/SolutionDetailPrintView.aspx?id=SOL82441&st=Published

• Ignore DDL errors on target

–

TARGET: sp_ctrl> SP_OPO_STOP_ON_DDL_ERR

(27)

Synchronize tables

• Using repair command

Log into source

sp_ctrl> repair splex.demo_src

sp_ctrl> repair status

• Using copy command

TARGET: sp_ctrl> start launcher

SOURCE: sp_ctrl> copy splex.demo_src

SOURCE: sp_ctrl> copy status

NOTE: For more information about copy, repair, and other SharePlex utilities,

commands and parameters, see SharePlex for Oracle Administrator’s Guide and

Reference Guide.

(28)

Part 3: How to diagnose & troubleshoot

• Upon completion of this lesson, the student should

be able to:

–

Determine the SharePlex module that is experiencing the issue

–

Locate the error message

–

Search SupportLink and SharePlex documentation for this error message

–

Either resolve the issue or collect appropriate information to further

troubleshot through collaboration.

–

Be the customer advocate until the issue is fully resolved

–

Assumption: Participant had thoroughly read and implemented what’s written in the

SharePlex for Oracle Installation Guide. SharePlex Installation Guide is available for

download via our

website

. This guide includes SharePlex product architecture,

(29)

Business Scenario

• A SharePlex customer says that SharePlex is

experiencing an issue…

(30)

Initial Steps

• Determine what was happening prior to or at the time of the issue

– What changed? New code release? Batch job? Upgrade? Etc….

• Determine which module is down

– sp_ctrl> show

– sp_ctrl> qstatus

• Determine the SharePlex version

– sp_ctrl> version full

• Locate the error message in $SP_SYS_VARDIR/log/event_log

• Search the KnowledgeBase, Release Notes, Administrator’s Guide, Reference Guide, Installation

Guide, and “Known Issues List” for this error message (the links are provided in the later slides)

• Determine whether the issue is with the SharePlex product or with the 3

rd

_{party software (Oracle,}

Unix, Linux, Windows) or hardware

(31)

If the issue cannot be resolved

• Collect appropriate SharePlex files:

– logs from $SP_SYS_VARDIR/log

– $SP_SYS_VARDIR/data/paramdb

• Collect SharePlex, Oracle and OS version information

– sp_ctrl> version full (for SharePlex version info)

• Collect the output of

– sp_ctrl> show

– sp_ctrl> qstatus

• For core dump issues collect

– $SP_SYS_VARDIR/dump/core.out file

• Document

– work performed so far

– customer replication setup

(32)

After Contacting SharePlex Support

• Be the customer advocate throughout the process

• Be an active participant until the issue is fully resolved

• Make sure SharePlex Support receives the requested logs in a timely

manner

• Make sure the customer understands the issue and the resolution

once it is provided

• Keep the customer regularly updated with current status

• If the issue requires a long time to resolve make sure the customer

understands why

(33)

Collecting logs

• Core.out and core files are in $SP_SYS_VARDIR/dump

• Paramdb is in $SP_SYS_VARDIR/data

• All logs are in $SP_SYS_VARDIR/log

–

Main log: event_log look here first

–

Capture: <SID>_ocap*.log or “ocap.log” or “capture log”

–

Read: <SID>_ord*.log or “ordr.log” or “reader log”

(34)

Collecting logs (continued)

• All logs are in $SP_SYS_VARDIR/log

–

Copy:

–

sync_svr*<SID>_ <src_server>_p<PID>.log (source) or “sync svr.log”

–

sync_clt*<SID>_<src_server>_p<PID>.log (target) or “sync clt.log”

–

Compare:

–

<SID>_desvr-<PID>*.log or “desvr.log” (source)

–

<SID>_ declt-<OWNER>-<TABLE_NAME>-<PID>*.log or “declt.log” (target)

–

<TABLE_NAME>.sql (target)

–

Activation:

–

<SID>_oconf*.log or “oconf.log” or “activation log” (source)

–

Export/Import:

–

without debug all messages go into the event_log

(35)

Review Questions

• Match up a process and its logs

• capture

• desvr.log, declt.log

• read

• sync_svr.log, sync_clt.log

• post

• ocap.log

• compare

• ord.log

• copy

• opo.log

(36)

Review Question Answers

• Match up process and its logs

• capture

• desvr.log and declt.log

• read

• sync_svr.log, sync_clt.log

• post

• ocap.log

• compare

• ordr.log

• copy

• opo.log

(37)

Links to SharePlex Documentation

• http://support.quest.com

–

KnowledgeBase

–

Documentation (Administrators, Reference, Install Guides)

–

Product Life Cycle

–

Supported Platforms

–

Release Notes

–

Product Download

• https://commons.quest.com/

(38)

Common issues and solutions

Click on each link and read the articles for more information.

• Post stopped due to error: "Poster: 17006 - Cannot open object cache“

http://supportpreview.prod.quest.corp/SolutionDetail.aspx?id=102109

• Basic trouble shooing in SharePlex

http://supportpreview.prod.quest.corp/SolutionDetail.aspx?id=92663

• Post stopped with error: “ORA-00955”.

http://supportpreview.prod.quest.corp/SolutionDetailPrintView.aspx?id=SOL57560&st=Published

• Capture core dumps with error: "A portion of the redo log could not be parsed'

http://supportpreview/Search/SolutionDetailPrintView.aspx?id=SOL63600&category=Solutions

• Compare fails with: "Warning 79 calling connect(de_sock_connectToServer) (IP: , Port: ) failed".

http://supportpreview/Search/SolutionDetailPrintView.aspx?id=SOL47503&category=Solutions

(39)

Prerequisites

for

Contacting

Support

(40)

• Problem Description

• Diagnostic logs, screenshots, etc.

• Environmental details (system versions, physical/virtual

hardware, federation, High Availability, architecture, etc.)

• Issue severity and customer business impact, timeframes, etc.

• If a performance issue, provide specific details as specified in

the Notes section of this slide.

When opening a support case submit

the following:

(41)

Feedback on the Documentation

We are interested in receiving feedback from you about our Support Technical Training. When submitting feedback

please include:

Product / Version

Indicate if you are providing Feedback on:

–

Support Training Documentation

–

Support Training Exam

–

Practical Exam

Comments

All comments are welcome. Please submit your feedback to the following email address:

[email protected]

(42)

DELL CONFIDENTIAL AND PROPRIETARY

This document SharePlex for Oracle 8.5 Support Training contains confidential

information of Dell and embodies trade secret and proprietary intellectual

property of Dell. It is legally protected and shall not be copied, modified,

reverse engineered, published, disclosed, disseminated outside of Dell or

otherwise used, in whole or in part, without Dell’s written consent, provided,