Chat now with support
Chat with Support

SharePlex 9.0 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters General SharePlex utilities Database Setup utilities SharePlex environment variables

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 . 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.

  • The clean_vardir.sh script preserves the contents of the SharePlex internal tables.
    • To truncate the SharePlex tables, without cleaning out the variable-data directory, see cleanup.sql.
    • To clean out the variable-data directory and truncate the SharePlex tables, see the appropriate database_cleansp utility, where database is the type of database. This utility completely restore SharePlex to an initial state.

    Important! Contact Quest Technical Support before running clean_vardir.sh 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.

    What this utility does

    The clean_vardir.sh script removes the following:

    • the queue files in the rim sub-directory.
    • the log files in the log sub-directory. The Event log retains one entry reflecting the clean_vardir.sh procedure.
    • the contents of the statusdb file in the data sub-directory.
    • the contents of the dump and state sub-directories.

    The clean_vardir.sh script preserves user-created files such as configuration files, conflict-resolution files, hint files, the paramdb, and the oramsglist file.

    The clean_vardir.sh script deactivates configurations. To start replication after running clean_vardir.sh, you must activate a configuration.

    Supported databases

    Oracle on Unix and Linux

    Shell requirement

    To use this utility, the Korn (ksh) shell must be installed on the system. The utility calls this shell during processing.

    Run clean_vardir.sh

    Run this script on Unix and Linux systems only.

    1. Shut down sp_cop.
    2. Set the SP_SYS_VARDIR environment variable to point to the SharePlex variable-data directory. If SP_SYS_VARDIR is not set, clean_vardir.sh affects the directory listed in the proddir/data/defaults.yaml file, where the proddir is the bin sub-directory of the SharePlex product 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 clean_vardir.sh as a SharePlex Administrator. The script is in the bin sub-directory of the SharePlex product directory. Use the following syntax, where Oracle_version is one of the SharePlex-supported Oracle versions.

      proddir/bin/clean_vardir.sh Oracle_version

    When the script is finished running, you are returned to the command prompt.

    Note: If the script generates an error message stating that it cannot remove the save_SharePlex_version directory, you can remove that directory manually.

  • 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.

    hana_cleansp

    Description

    Use the hana_cleansp utility to remove the current replication state on a system where SharePlex is replicating to a HANA target.

    Caution: The effects of hana_cleansp are not reversible. To stop replication without restoring it to an initial state, you might be able to use the abort config or purge config command.

    Usage guidelines

    • To use this utility, the Korn (ksh) shell must be installed on the system. The utility calls this shell during processing.

    • hana_cleansp must be run on all HANA target systems in the replication configuration.
    • To fully remove the replication environment, run the ora_cleansp utility on all Oracle source systems in the replication configuration. To verify if and when hana_cleansp or ora_cleansp was run on a system, view the SharePlex event log on that system.

    What this utility does

    hana_cleansp does the following on the target system:

    • Truncates the SHAREPLEX_OPEN_TRANS internal table in the SharePlex schema and any other SharePlex-installed tables as applicable.
    • Removes the following from the variable-data directory: the queue files, the process log files, the contents of the statusdb (but not the file), the contents of the dump and state directories, and all but one entry in the event log (the status entry for hana_cleansp)

    hana_cleansp preserves the following:

    • The SharePlex database, account, and password, and only cleans the data in the SharePlex tables.
    • User-created files such as the paramdb and the target configuration settings

    Supported databases

    HANA on supported platforms

    Run hana_cleansp

    1. Stop all SharePlex processes on the system.
    2. Shut down sp_cop.
    3. Run the hana_cleansp utility from the bin sub-directory of the SharePlex product directory with the following syntax:

      C:\users\splex\bin>hana_cleansp [port number ] database_name/user_name

      where:

      • port number is the port number of the SharePlex instance, if other than the default of 2100.
      • database_name is the name of the SharePlex database.
      • user_name is the name of the SharePlex user account.
    4. Enter the password at the prompt.

      Enter the password for the Shareplex User :

    5. Type Y to confirm that you want to run cleanup for this SharePlex instance.

      Are you sure you want to perform the clean for '/splex/vardir/var2200' and port 2200? [N] : Y

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating