What files and SharePlex internal database objects are preserved by SharePlex when running ora_cleansp (4300126)

When ora_cleansp is run, it cleans up certain files and entities (including Shareplex internal tables in database) yet it preserves other files and entities. This solution delves on the reasons for preserving some of these.

General information.

The purpose of running ora_cleansp is to restore replication to an initial state, so as to start afresh. In doing so it ensures that anything that is created or configured by users by putting their time and efforts, and is static in the near term, is not removed. Things that are variable in nature such as replicating data in queue and current content of Shareplex internal tables are removed. The following entities are preserved, among others:

Shareplex user in the database.

The Shareplex internal table shareplex_partition: This table contains entries for horizontal partitioning for tables. Such entries are formulated after careful consideration of the column condition for qualifying a row for replication.

The paramdb file: It contains not only the login credentials of Sharepelx user but also contains Shareplex parameters that are other than the default (most likely because of tuning effort done earlier).

The conflict_resoultion.SID file: It references the conflict resolution procedures created for objects in a peer to peer environment. Losing its entries inadvertently would deprive users of the cross referencing between the objects and their conflict resolution procedures for various DML.

Hints.SID file: It contains the hints to be provided to Oracle optimizer in the form of entries of the table name and the most suitable index. This information is derived after careful consideration of the access path taken by the SQL running on the object, though it is subject to change. But the entries cannot be removed in a blanket manner.

Transformation.SID file: This contains the table names as well as the name of the transformation procedure that needs to be invoked to populate the table (for the purpose of manipulating certain fields in the target table). Again, this is a cross referencing between the table and the PL/SQL procedure created in the database and hence the link needs to be preserved for referencing.

Config files: There are various config files in the config subdirectory under the variable directory. These files contain the names of the source and target tables in replication and are created after painstaking effort involving selecting the tables that are good candidate for replication. At times the database may contain tens of thousands of tables and thus the config files are too valuable to be lost without users consent.

The oramsglist file: This file contains the Oracle error messages that are ignored by user by configuring the parameter SP_OPO_CONT_ON_ERR to 1. Since there is no way for user to track which Oracle errors were ignored if the file is reset to the default, the ora_cleansp does not alter this file.

The Shareplex Administrator Guide Chapter titled Shareplex Utilities section titled Restoring replication to an initial state with ora_cleansp delves on what is removed and what is preserved:

How ora_cleansp works

Ora_cleansp works in two stages to restore replication to an initial state, as follows:
Ora_cleansp first truncates all of the SharePlex internal tables, except for the
SHAREPLEX_PARTITION table, which contains partition schemes that might be
needed again. It does not remove the SharePlex Oracle user, password, or demonstration
objects.
After the tables are truncated, ora_cleansp removes the following components from the
SharePlex variable-data directory:
the queue files in the rim sub-directory.
the log files in the log sub-directory. The Commands and Event logs each retain one
entry reflecting the outcome of the ora_cleansp procedure.
the contents of the statusdb file in the data sub-directory.
the contents of the dump and state sub-directories.
Ora_cleansp preserves user-created files such as configuration files, conflict-resolution
files, transformation files, hint files, the paramdb, and the oramsglist file.
Ora_cleansp deactivates configurations. To start replication after running
ora_cleansp, you must activate a configuration.