Use the OTS utility (ODBC Test Suite) to test the ability of SharePlex to connect to an Open Target target database through a selected ODBC driver, and then post test data to different column types.
Notes:
Open Target databases being considered for use with SharePlex.
Install the ODBC driver that you want to test.
(Linux) Set the following environment variables:
Set LD_LIBRARY_PATH to both of the following places:
OTS is available in the SharePlex build as well as a standalone version.
To install the standalone version:
Linux: Download the OTS package SPOodbc_test_suite-version-Beta-db_version-rh-40-amd64-m64.tpm. You are prompted for an installation directory.
Windows: Download the ots.exe file and place it in any directory.
Run OTS. If you are running it from within an installation of SharePlex or the standalone OTS package on Linux, run it from the util subdirectory. If you are running the Windows standalone version of OTS, run it from the installation directory.
A data source name (DSN) for the target database. The DSN must already exist. You are then prompted for the name and password.
OTS requires connection information for the tested database. Enter data source name (DSN) or connection string: mydsn
The OTS utility performs the following tests:
Create a report, dump files, and logs to support debugging and support cases.
Note: The report file is only generated when OTS can connect to the database. If the connection fails, the file is empty.
The OTS screen summary provides the following:
The detail results are organized into tables, one per data type category, for example character data types or integer data types, as shown in the example.
Figure 1: Example detail table for character data types
Test: Character data types
--------------------------------------------------------------------------------
Name Size Nullable Insert Insert Update Delete Add Drop Trunc Pass/Fail
Null Col Col
--------------------------------------------------------------------------------
char 4096 Y P P P P P P P P
nchar 4096 Y P P P P P P P P
varchar 4096 Y P P P P P P P P
sysname 30 Y P P P P P P P P
nvarchar 4096 Y P P P P P P P P
The first three columns of each table (Name, Size, Nullable) are taken from a query to the ODBC driver. If the data type is allowed to contain NULL, the Nullable column shows a Y (yes). Otherwise, it shows an N (no).
Following the query columns are columns that represent the tests that were performed. There is a row for each data type that was tested. The SharePlex-supported operations that OTS tests are as follows:
The results also show the maximum and minimum (where applicable) storage values that were used for CHAR and VARCHAR data types in each test:
For each test of a data type and operation, a P (pass) or F (fail) result is given, indicating whether or not SharePlex was able to perform that specific DML or DDL operation with that specific data type.
The final column, Pass/Fail, shows at a glance whether the test passed or failed as a whole for a specific data type. An F for any given operation type across the row triggers an F in the Pass/Fail column, meaning an overall failure of testing for that data type.
The OTS utility is an initial screening tool for Open Target databases that are not yet Quest SharePlex Certified. Its purpose is to determine whether the types of operations that you want to replicate to a given target, with a given set of metadata, are likely to succeed, without requiring the installation or setup of SharePlex itself. For example, you could assume that SharePlex probably can post to a target if the outcome of the test is the following:
The OTS utility is only a first step. It is not a substitute for testing a full installation of SharePlex with your actual data, using actual operations that your source Oracle applications generate, and posting those replicated operations to your actual target tables in a test environment. Additionally, a successful test with OTS does not necessarily mean the database will be fully supported by SharePlex. See How to run OTS
To determine whether SharePlex will support a given Open Target database, the following are considered:
Does SharePlex Product Management approve the technical and market feasibility of supporting the database?
If the answer to questions 1 and 2 are yes, then the database is ODBC-approved. This means the database is then eligible for testing in the QA labs to answer question 3.
It is not realistic to test every possible edition, version, and platform of a given database. Based on market and technical factors, Product Management will determine which variants of a database will be tested. If those tests are successful, Product Management can then consider assigning the database a designation of SharePlex Certified.
Use the provision utility to change a host name or IP address in the SharePlex configuration.
The SharePlex processes rely on the host names or IP addresses of the source and target machines to route data properly. The provision utility enables you to change host names or IP addresses within an active SharePlex instance, without reactivating a new configuration.
Note: The provision utility does not change anything in the database. It only affects SharePlex internal objects.
All databases supported by SharePlex on all supported platforms
(SharePlex 7.0.2 only) Add the following parameter to the param-defaults file before you run provision:
SP_CFP_DEBUG I live int() 0x00000000
If running SharePlex on an AIX machine, set EXTSHM before running provision.
export EXTSHM=ON
Run provision on all of the machines in the SharePlex configuration. Each machine can reference the IP addresses of all the other machines.
Using the command line of the operating system, run provision from the SharePlex variable-data directory with the following syntax:
provision -f old_name[:old_ipaddress] -t new_name[:new_ipaddress] [-p port] [-n]
| Argument | Input |
|---|---|
| -f old_hostname[:old_ipaddress] |
|
| -t new_hostname[:new_ipaddress] |
|
| -p port | For Windows systems, specifies the port of the SharePlex instance for which provision is being run. |
| -n |
Runs provision without actually making any changes. Generates a report on the changes that provision will make. Important! The best practice is to run provision with -n first, to make certain you agree with the potential changes, then run it without -n to make the changes. |
provision -f oldname -t newname -n
The provision utility creates an undo_provision script that can be used to restore the host names and IP addresses to their previous state. Run the undo_provision script from the util subdirectory of the SharePlex product directory. There are no input arguments to this script.
The following may occur but do not affect the integrity of the replication environment:
Through the qview utility, you can view queue names and remove old queue files. The qview tools described here do not deactivate the configuration.
IMPORTANT!Do not use qview for the first time without the assistance of Quest Technical Support. If this utility is not used properly, it can damage the replication environment and require resynchronization and reactivation.
All SharePlex-supported databases on all supported platforms
Log on to the system as a SharePlex Administrator, and use the command line of the operating system to run qview from the bin sub-directory of the SharePlex product directory. The utility is an interactive command session.
To run qview on the Windows platform while the SharePlex service is running, log onto the system as the Administrator user. On Windows, there is only one Administrator user. Other users, whether or not they have Administrator privileges (members of Administrators group), cannot run qview. Other Windows users with Administrator privileges can run qview if the SharePlex service is shut down.
On Windows, run qview with the -p option to specify the port number of the SharePlex instance for which you want to view queues.
qview -pport list
The qview utility provides the following commands:
| Command | Description |
|---|---|
| list | Lists all queues for all active configurations on a system. |
| trim | Clean up obsolete subqueue files. |
| fullrb | Create a full rollback message. |
| otrans | Scans for a specified number of messages in the Capture queue. |
Use the list command to list all queues for all active configurations on a system.
The qview list command lists each queue, the replication process that writes to it, and the replication process that reads it. For example, for the capture queue, it lists the Capture process and the Read process. The queues are designated as follows:
In this example, the writer to the capture queue o.ora11+C is the Capture process, as indicated by the sp_ocap in its name string. The reader is the Read process, as indicated by the sp_ord in its name string. The same naming logic applies to the other queues shown in the output (export queue expdsg+X and post queue expdsg+P).
The following queues exist: o.ora11+C WRITER +PA+o.ora11+sp_ocap+o.ora11 READER +PR+o.ora11+sp_ordr+o.ora11 elliot+X WRITER +PR+o.ora11+sp_ordr+o.ora11 READER +PX+elliot+sp_xport+0x0a01014e (11.1.1.78) elliot+P+o.ora11-o.ora11 subqueues range from 2 to 6 WRITER +PI+elliot+sp_mport+0x0a01014e (11.1.1.78) READER +PP+elliot+sp_opst_mt+o.ora11-o.ora11
list
Use the trim command to clean up obsolete subqueue files on the source system.
The SharePlex post queue actually consists of a number of subqueues, each approximately corresponding to a user session on the source system. The Post process uses the subqueues to establish Oracle sessions for the target instance. The number of subqueues that exist at a given time on a target system reflects the peak activity on the source system since replication started.
SharePlex routinely writes replicated data from the subqueues to associated datafiles on disk as part of its checkpoint recovery system. Each subqueue can have one or more datafiles associated with it, each with a default size of 8 MB. If the entire 8 MB file size is not consumed, a datafile remains on the system even though the data was posted and read/released. Consequently, the higher the activity level on the source system, the more datafiles on disk. The size in megabytes (MB) for the post queue in a qstatus display is the actual disk space that the datafiles occupy.
For example, suppose there were 100 concurrent sessions on the source system, creating 100 subqueues in the post queue on the target system. And, suppose the datafiles were only partially full when the activity level dropped—half full, for example, or 4 MB of 8 MB used—and thus were not deleted. The post queue on that system would consist of 100 datafiles at 4 MB each, totalling 400 MB of disk space.
Using the trim command in qview, you can routinely eliminate obsolete subqueue files that were read-released, while preserving the ones containing data not yet committed to the target database. The trim command does not eliminate queue files for subqueues 0 or 1, because those are the most heavily used subqueues.
Run this command on the target system only.
Stop Import and Post before running qview to issue this command. You can leave sp_cop running.
Note: If one or both of those processes is not stopped, qview returns this error message: que_INUSE: Que is already open.
You can only trim one queue at a time. If there are more than one post queue, you are prompted to select the one you want the command to affect:
Queue zac+P+o.ora920-o.ora920 (y/n) <n>? n Queue elliot+P+o.ora920-o.ora920 (y/n) <n>? y
Note: If you do not select a queue, qview returns this error message: que_NOEXIST: Queue does not exist.
trim
Use the otrans and fullrb commands to create a full rollback message.
Use the otrans command to scan a specified number of messages in the Capture queue, starting at the read release point. The qview utility then prints the transaction id, the number of operations (records), the DML type operation (if there is only one) and the object id modified (if there is only one).
Use the transaction id obtained from otrans to execute fullrb. The qview utility opens the Capture queue, writes an out-of-band full rollback message to the Capture queue, and then writes a commit.
Issue the otrans command.
vqiew> otrans 500000
The output is similar to the following:
Full rollback 8(7).752562-3(139) --- 99999 Update operations on object id 466857
Open transaction 8(23).752700-2(14162) --- 2001 Update operations (1000 backward operations)
on object id 466857
Issue the fullrb command using the transaction ID from the otrans output.
vqiew> fullrb 8(7).752562-3(139)
The output is similar to the following:
Current queue o.ora920+C user +PA+o.ora920+sp_ocap+o.ora920 Full rollback record written to capture queue at 378744, id 1102 odr_magic 0x4f445235 odr_op ODR_FULL_ROLLBACK (50) odr_trans 8(7).752562-3(139) odr_time 01/01/88 00:00:00 (0)
Start Capture.
sp_ctrl> start capture
otrans number
where: number is the number of messages to scan in the queue.
fullrb transaction_ID
where: transaction_ID is the transaction ID that was returned from otrans.
Use the show_scn utility to view the correct Oracle SCN values to supply with the following commands during a Resume Replication recovery procedure:
Oracle source and target
The show_scn utility is run during the Resume Replication procedure after the source, target or both have failed. To use Resume Replication and the show_scn utility, there must be the following in place at the onset of replication:
IMPORTANT: For more information about how to use show_scn in context, see "Resume replication after failure and recovery" in the SharePlex Administration Guide.
To run show_scn
From the command line of the target system, run the show_scn utility from the bin subdirectory of the SharePlex product directory. For datasource, use the ORACLE_SID of the source database.
$ /productdir/bin/show_scn datasource
The utility provides output similar to the following:
$> show_scn o.ora112
On source activate to scn=510012416
For resume replication from ora112
reconcile queue sp01 for o.ora112-o.ora112 scn 4517993781
reconcile queue sp02 for o.ora112-o.ora112 scn 4517994532
reconcile queue sp03 for o.ora112-o.ora112 scn 4517995633
© 2020 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy