Mediator Re-sequencer Purging

Mediator Re-sequencer purging

Earlier Mediator Re-sequencer tables were not considered for purging from purge scripts. We have included this from 11g Patch-set 5 onwards and did a back-port on the previous patches also

Purge scripts now considered below table while executing loop/parallel purge
1. MEDIATOR_GROUP_STATUS
2. MEDIATOR_RESEQUENCER_MESSAGE

There are couple constraints in executing purge on mediator re-sequencer tables

MEDIATOR_GROUP_STATUS
What can be purged

1. FOR BestEffort and FIFO groups in READY state,
2. We try to purge messages first and then the groups.This facilitate group purging.

What cannot be purged

1. Standard Mode Re-sequencer groups cannot be purged.
2. groups in running state cannot be purged(for all the strategy)


MEDIATOR_RESEQUENCER_MESSAGE.

1. Messages in COMPLETED and ABORTED state only can be purged. Rest all cannot be purged.


what are the different status available under above tables

Group states:

READY = 0; ->Purgeable
LOCKED = 1; ->NON Purgeable
ERROR = 3; ->NON Purgeable
TIMED_OUT=4; ->NON Purgeable
GROUP_ERROR=6; ->NON Purgeable

message states
READY = 0; ->Purgeable
LOCKED = 1; ->NON Purgeable
ERROR = 3; ->NON Purgeable
COMPLETED = 2; ->Purgeable
TIMED_OUT=4; ->Purgeable
ABORTED=5; ->Purgeable


Loop and parallel same scripts can be used from my old blog Executing Parallel Purge script andExecuting Loop Purge scrip

11g purge scripts growth

History of purge scripts and its portability

1st version of purge scripts came only in PS1 or 11.1.1.2.0 release this had purge based on instance state, composite name, and version


2nd version of purge scripts came only in PS2 or 11.1.1.3.0 release. for the 1st time Full partition concept was introduced


3rd version of purge scripts was available in PS3 or 11.1.1.4.0 release it was know as " purging for Extremely large environments support"
Purge script was changed and made it very simple. partition purge concept was also supported

this script was not compatible to work on older patch sets(PS1 amd ps2) as there was change in the way the scripts works.
So there is backport_scripts which would work fine on PS2 and PS1 was developed


4th version no enhancement were made available in PS4 or 11.1.1.5.0 release.


5th version more enhancement were made available in PS5 or 11.1.1.6.0 release. this includes

Partial Partitioning of Component tables
Purge By Composite DN
Partitioning Strategy for long running processes introducing "Row Movement strategy "
NOTE: there is no backport scripts available for this so far

java.sql.SQLException: ORA-01654: unable to extend index DEV3_SOAINFRA.COMPOSITE_INSTANCE_STATE by 8 in tablespace DEV3_SOAINFRA[[

if you are hitting anything like the below issues or similar to the below issues then this may be because of dbf file size issues

Issues:
java.sql.SQLException: ORA-01654: unable to extend index DEV3_SOAINFRA.COMPOSITE_INSTANCE_STATE by 8 in tablespace DEV3_SOAINFRA[[

oracle.toplink.exceptions.DatabaseException[[
Internal Exception: java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment DEV3_SOAINFRA.SYS_LOB0000129222C00018$$ by 8 in tablespace DEV3_SOAINFRA

Solution: to extend datafile/dbf file size by 4gb
ALTER DATABASE DATAFILE ‘db1300/oradata/db1300/DB1300_soainfra.dbf’ RESIZE 4000M;

NOTE: make sure you take a backup of dbf files before executing above alter command

if the file got corrupted then u may have to recreat the file then use bleow command to recreate it

ALTER TABLESPACE DEV3_SOAINFRA ADD DATAFILE ‘db1300/oradata/db1300/DB1300_soainfra.dbf’ SIZE 4000M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;


refernce to some of the good material
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3002.htm

http://docs.oracle.com/cd/B10500_01/server.920/a96521/tspaces.htm

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

Purge bases on Composite Name

Purge bases on Composite Name
There are some scenarios where executing purge scripts for a single composite make sense then executing it on whole of the soainfra schema.
when we execute Purge script based on composite name the instances of the specific composite gets deleted. This can be again be more specific by providing composite versions and partition name.
Below is the script for both loop purge and parallel purge. Basic setup for executing purge based on composite name is same as loop purge or parallel purge(please refer blogs on this blog same names)

parameters used :

composite_name => 'HelowSoaPurge',
composite_revision => '2.0',
soa_partition_name => 'SoaPurge');



=====================================================================
Loop purge

DECLARE

MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
batch_size integer;
max_runtime integer;
retention_period timestamp;
BEGIN
max_runtime := 60;
retention_period := to_timestamp('2011-09-20','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => true,
composite_name => 'HelowSoaPurge',
composite_revision => '2.0',
soa_partition_name => 'SoaPurge');
END;
/

=====================================================================
Parallel Purge

DECLARE

MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 01:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';
batch_size integer;
max_runtime integer;
retention_period timestamp;
BEGIN
max_runtime := 60;
retention_period := to_timestamp('2011-09-21','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances_in_parallel(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => true,
composite_name => 'HelowSoaPurge',
composite_revision => '1.0',
soa_partition_name => 'SoaPurge');
END;

/

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

ORA-06512: at "SYS.UTL_FILE", line 536

ssue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20

Solution:

1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs

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

Partition cannot be dropped for XML_DOCUMENTS

Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.

Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.

solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS

to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================

Error Message = ORA-20001: Jobs still running or not shut down properly

Issue2 :(specific to Parallel Purge)

02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11

Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control

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

error "ORA-01861 literal does not match format string"

Hacks and Workarounds
Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"

Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

or like this

MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

Hacks and Workarounds

Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"

Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

or like this

MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue2 :(specific to Parallel Purge)

02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11

Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.

Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20

Solution:

1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.

solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS

to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS

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

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

Purge basics

When to use the different purge techniques

Instance can be purged in 5 different techniques

1. Using EM UI delete with options
2. Using purge scripts (loop purge and parallel purge)
3. Using Purge By Composite DN.
4. Using DB Partial partitioning concept
5. Using DB Full partitioning concept

How do we decide which purging strategy we need to use
1.Using EM UI delete with options :
This option is used when we have ~ 10000 instances in the DB

2.Using purge scripts (loop purge and parallel purge):
Loop purge : use this if there are more than greater then 100k and less than 2 million records
Parallel purge : use this if there are more than greater then 2 million record and less then 500 million records

3. Using Purge By Composite DN:
Here we try to use loop purge or parallel purge to purge instances of a specific composite,
Purge based on composite_dn would be supported by adding additional parameters - composite_name , composite_revision and soa_partition_name to the existing API's.

4.Using DB Full partitioning concept
Installations which generate more than 10GB of data per day OR retain more than 500GB of data

5.Using DB Partial partitioning concept
Long-running processes (over months) - Use a combination of Parallel purge and Partitioning( e.g. daily purge, monthly dropping partitions)

Instance state that can be purged
1. Completed
2. Completed-Faulted
3. Terminated
4. Stale
Instance state that cannot be purged
1. Running
2. Running-Faulted
3. RecoveryRequired

NOTE: Different table in SOAINFRA schema has different state representation, this will be explained in different blog

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

Executing Parallel Purge script

Parallel purge script

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

4. Login as SOAINFRA_db_user and execute below command

SQL>create directory SOA_PURGE_DIR as '/tmp/purge'

5. Enable Debug mode by executing soa_purge/common/debug_on.sql

6. set the serverout (sql> set serverout on)

7. Executing Parallel purge script by login as SOAINFRA_USER/password

8. logs are generated under /tmp/purge. Number of log files depends on number of threads used inthe client. In the below example it would generate 3 log files

Sample client

DECLARE

max_creation_date timestamp;
min_creation_date timestamp;
retention_period timestamp;
BEGIN

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

soa.delete_instances_in_parallel(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => false);

END;
/

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

Executing Loop Purge script

Loop purge script


1. First enable Debug mode by executing soa_purge/common/debug_on.sql
2. set the serverout (sql> set serverout on)

3. Executing loop purge script by login as SOAINFRA_USER/password

Sample client
DECLARE

MAX_CREATION_DATE timestamp;
MIN_CREATION_DATE timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;

BEGIN

MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => false);
END;
/


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