SharePlex 9.2.6 - Reference Guide

Configuration Scripts

Description

SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.

• config.sql: configure all tables and optionally all sequences in the database.
• build_config.sql: configure multiple or all tables in a schema

Supported databases

Oracle

Use config.sql

The config.sql script enables you to build a configuration that lists all of the tables, and optionally all of the sequences, in all of the schemas of a database. This script saves time when establishing a high-availability replication strategy or other scenario where you want the entire database to be replicated to an identical secondary database.

Conditions for using config.sql

• Source and target table names must be the same.

• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration file that the script builds, then use the edit config command to add entries for tables that use partitioned replication. Activate the new configuration file, not the original one.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/config.sql

Refer to the following table when following the prompts.

Note: The name assigned by SharePlex to the configuration is config.file. If you run the script again to create another configuration file, it overwrites the first file. To preserve the original file, rename it before you create the second one.

Next steps:

• If any tables or owners are case-sensitive, open the configuration file with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

Use build_config.sql

The build_config.sql script enables you to build a configuration that contains multiple (or all) tables in a schema. It is an interactive script that prompts for each component of the configuration step by step. Instead of entering the information for each object and the routing individually, you can use a wildcard to select certain tables at once, or you can select all of the tables in the schema.

Conditions for using build_config.sql

• Source and target table names must be the same.
• The script does not support sequences. Before you activate the configuration that the script builds, you can use the edit config command in sp_ctrl to add entries for sequences.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration that the script builds, then use the edit config command to add entries for the tables that use partitioned replication. Activate the new configuration, not the original.
• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• You can run build_config.sql for different schemas, then combine those configurations into one configuration by using a text editor. Make certain to eliminate all but one Datasource:o.SID line, which is the first non-commented line of the file. Do not move the file out of the config sub-directory.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run build_config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The build_config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run build_config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/build_config.sql

Refer to the following table when following the prompts.

Next steps:

• If any tables or owners are case-sensitive, open the configuration with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

cleanup.sql

Description

Use the cleanup.sql script to truncate all of the SharePlex internal tables except the SHAREPLEX_PARTITION table (which contains partition schemes that might be needed again).

Note: The cleanup.sql script does not remove the SharePlex Oracle user, password, or demonstration objects from the SharePlex tables.

The cleanup.sql script preserves the replication data in the SharePlex variable-data directory. Other utilities provide related options:

• To clean out the variable-data directory without truncating the SharePlex tables, see clean_vardir.sh.
• To clean out the variable-data directory and truncate the SharePlex tables, see ora_cleansp. This utility completely restore SharePlex to an initial state.

Contact Quest Technical Support before running cleanup.sql for the first time. Unless a procedure in the SharePlex documentation requires running clean_vardir.sh, this utility rarely is appropriate in a production environment. It deactivates the configuration, and using it improperly can result in replication problems and the need to resynchronize the data. Usually, there is another alternative.

Supported databases

Oracle on Unix and Windows

Run cleanup.sql

1. Log into Oracle as the SharePlex database user. The SharePlex tables belong to that user. On Unix and Linux, If you are running multiple instances of sp_cop with multiple variable-data directories, there is a SharePlex Oracle user for each one. Make certain to run this script as the correct one.

2. (Unix and Linux) Set the SP_SYS_VARDIR environment variable to point to the SharePlex variable-data directory.

   ksh shell:

   export SP_SYS_VARDIR=/full_path_of_variable-data_directory

   csh shell:

   setenv SP_SYS_VARDIR=/full_path_of_variable-data_directory

3. Run cleanup.sql as a SharePlex Administrator. The script is in the util sub-directory of the SharePlex product directory. Use the following syntax, where Oracle_version is one of the SharePlex-supported Oracle versions.

   SQL> @proddir/util/cleanup.sql

clean_vardir.sh

Description

Use the clean_vardir.sh script to clean out the variable-data directory to restore it to an initial state.

create_ignore.sql

Description

Use the create_ignore.sql utility script to prevent DML transactions from being replicated to the target system. This script creates a public procedure named SHAREPLEX_IGNORE_TRANS in the source database. When executed at the start of a transaction, the procedure makes the Capture process ignore DML operations that occur from the point of execution until the transaction is either committed or rolled back. Thus, the affected operations are not replicated.

Only DML operations are affected by the SHAREPLEX_IGNORE_TRANS procedure. It does not cause SharePlex to skip DDL operations, including TRUNCATE. DDL operations are implicitly committed by Oracle, so they render the procedure invalid.

Supported databases

Oracle on Unix and Windows

Run create_ignore.sql

Run the create_ignore.sql script from the util sub-directory in the SharePlex product directory. Run it as the SharePlex Oracle user so that the procedure is created in the SharePlex schema.

The script executes the following PL/SQL:

CREATE OR REPLACE PROCEDURE SHAREPLEX_IGNORE_TRANS AS

TNUM NUMBER;

BEGIN

INSERT INTO SHAREPLEX_TRANS (TRANS_NUM, QUE_SEQ_NO_1, QUE_SEQ_NO_2,

COMBO, OP_TYPE) VALUES (-999,0,0,'DUMMY',0);

DELETE FROM SHAREPLEX_TRANS WHERE TRANS_NUM=-999 AND COMBO='DUMMY'

AND OP_TYPE=0;

END;

/

GRANT EXECUTE ON SHAREPLEX_IGNORE_TRANS TO PUBLIC;

/

Execute SHAREPLEX_IGNORE_TRANS

Call SHAREPLEX_IGNORE_TRANS only at the beginning of a transaction containing operations that you do not want replicated. If it is called in the middle of a transaction, replicated operations preceding the start of the procedure will remain in the post queue indefinitely awaiting a COMMIT, because SharePlex does not release messages without one. The COMMIT will not arrive because Capture ignores all operations in the transaction after the procedure is called. The Read process will retain unwanted cache information on those records indefinitely.

1. Log onto SQL*Plus as the SharePlex user.

2. Execute the SHAREPLEX_IGNORE_TRANS procedure at the beginning of the transaction that you want to skip.

   execute SharePlex_schema.SHAREPLEX_IGNORE_TRANS;

3. In one transaction, make the changes that you do not want replicated. They will be ignored by Capture.
4. After the COMMIT or ROLLBACK, replication of subsequent transactions resumes normally.