DB Purge Partitioning Concept

Partitioning concept

DB Purge Partial partitioning concept
All the tables of a component/SE can be partitioned
The following select set of tables can be partitioned to achieve partial partitioning

COMPOSITE_INSTANCE (master)
REFERENCE_INSTANCE(dependent)
CUBE_INSTANCE (master)
CUBE_SCOPE(dependent)
XML_DOCUMENT
MEDIATOR_INSTANCE(master)
MEDIATOR_CASE_INSTANCE(dependent)
MEDIATOR_PAYLOAD

DB Purge Full partitioning concept

- All tables should be equi-partitioned along the same date ranges and the same name .
- Composite_instance table should always be partitioned . This constraint is essential when instance tracking is set to 'dev' or 'prod' in any of the composites. Since, the verify scripts checks for active flows based on the active composite instances within that partition, if the composite_instance is not partitioned the whole verify scripts logic based on the equi-partitioning of all the tables would fail.

- A dependent table cannot be partitioned unless its master is partitioned as well.


Below is the common set of requirements for setting base for testing partial or full partitioning

1. We need to first do partition(full or partiality)
2. Login as sysdba and execute below grant commands

SQL> sqlplus "sys/welcome1 as sysdba"

GRANT EXECUTE ON DBMS_LOCK to SOAINFRA_db_user;

GRANT CREATE ANY JOB TO SOAINFRA_db_user;

GRANT CREATE ANY TABLE TO SOAINFRA_db_user;

GRANT CREATE ANY DIRECTORY TO SOAINFRA_db_user;

GRANT EXECUTE ON UTL_FILE TO SOAINFRA_db_user;

3. Create folder $> mkdir -p /tmp/purge_dir (note: all sql logs will be stored under this folder)

4. Login as SOAINFRA_db_user and execute below command

SQL>create directory PART_DIR as '/tmp/purge_dir'

Below are the steps to execute verify scripts
ASSUMPTION : Schema partition is already done by now

6. verify script is present under loc /rcu/integration/soainfra/sql/verify/soa_exec_verify.sql

7.open soa_exec_verify.sql and enter your partition that needs to scanned and dropped.
Example: mySoa_drv_list.extend(2);
mySoa_drv_list(1) := 'P01_2011';
mySoa_drv_list(2) := 'P02_2011';

8. soa_exec_verify.sql takes 2 user input parameter namely 1 and 2 number.
parameter 1 -> checks all the master tables
parameter 2 -> checks all the dependent tables

NOTE: below is 2 ways of continuing

Steps to run when we don't have running instance in the partition

a. Run soa_exec_verify.sql with input as 1
b. check /tmp/purge_dir for log files. if file are clean/pass
c. proceed with loop/parallel Purge scripts
d. now run *.sql file which drops partition
e. Rerun soa_exec_verify.sql with input as 2
f. check /tmp/purge_dir for log files. if file are clean/pass
g. run exec B2B_DISABLE_CONSTRAINTS as soainfra user( note: this will disable constraint common to soainfra and b2b schema)
h. now run *.sql file which drops partition


Steps to run when we have Long running instance in the partition

a. Run soa_exec_verify.sql with input as 1
b. check /tmp/purge_dir for log files. if file are Fail or having running (running instances) you have to run rowmovement1 as below by giving following information

new_partition_date := TIMESTAMP'2011-10-31 06:00:00'; -> Specify which partition long running instances should go to

partition_name := 'P04_2010'; -> Specify from which partition you want to move running instances

script as below

DECLARE
new_partition_date timestamp;
partition_name varchar2(100);
BEGIN
new_partition_date := TIMESTAMP'2012-01-09 02:00:00';
partition_name := 'P02_2012';
verify_soa.exec_row_movement_1( partition_name => partition_name, new_partition_date => new_partition_date );
END;


c. rerun step b and check for the clean logs with all pass in it. this means partition is ready for dropping

d.proceed with loop/parallel Purge scripts

e. now run *.sql file which drop partition

f. clear temp tables by running below procedure

Truncate temp tables:

DECLARE
BEGIN
verify_soa.trunc_verify2_temp_tables;
END;
/
g. Rerun soa_exec_verify.sql with input as 2

h. check /tmp/purge_dir for log files. if file are Fail or running(running instances) you have to run rowmovement2 as below by giving following information

new_partition_date := TIMESTAMP'2011-10-31 06:00:00'; -> Specify which partition long running instances should go to

partition_name := 'P04_2010'; -> Specify from which partition you want to move running instances

script as bleow
DECLARE
new_partition_date timestamp;
partition_name varchar2(100);
BEGIN
new_partition_date := TIMESTAMP'2012-01-09 02:00:00';
partition_name := 'P02_2012';
verify_soa.exec_row_movement_2( partition_name => partition_name, new_partition_date => new_partition_date );
END;

i. rerun step b and check for the clean logs with all pass in it. this means partition is ready for dropping

j. now run *.sql file which drop partition

NOTE: when schema is Partial Partition in the above step replace " d.proceed with loop/parallel Purge scripts" with  "Execute the purge script to delete nonpartitioned tables. The purge_partitioned_component parameter of the purge procedures must be set to false. "

If you have an environment in which some components are partitioned, while other components are not partitioned, the nonpartitioned data set must be purged using the purge scripts


For example, assume human workflow is not partitioned, while other components are partitioned. The verification script reports that all SOA partitions can be dropped using the command for dropping partitions. However, the human workflow tables continue to hold workflow data until the data is purged using the loop/parallel purge scripts.


================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================