This post describe how to manage database growth for Oracle Fusion Middleware SOA Suite Backend Dehydration store (‘SOAINFRA‘ schema) which grows over the period of time a lots if not monitored regularly and will result major performance issue, disk out of space issue and increase latency etc.
This will increase the complexity to maintain SOA Database instance as well.
To address this challenge, several methods for managing database growth are provided such as
1. Running purge script
2. Running parallel purge script
3. Dropping partitioning if implemented
4. Purging Data from EM console, B2B console and OSB Console.
In this article, I will only focus on how we can run Oracle out-of-box provided purge script to clean up the SOAINFRA schema tables which contains tables for various SOA Suite component such as Mediator, Workflow, BPEL/SCA and decision etc.
Note: B2B components records will not be purged using below script. If B2B implementation has been done then use this link to know who we can purge B2B Meta data and B2B Instance data. It’s very simple.
Fortunately, Oracle SOA Suite 11.1.1.7 or previous versions provides out-of-box purge scripts. These scripts comes along with the setup and once you install the product it can be located at -
MW_HOME/SOA_HOME/rcu/integration/soainfra/sql/soa_purge
e.g.
Pre-Implementation Verification
1) You must need to take backup of SOA DB instance where purge script has to run, to make sure if something goes wrong we can restore the database easily.
2) Run the below SQL query to check the current usage of tablespace for SOA DB instance, so later you can do comparison study whether tablespace size has been reduced or not
select tablespace_name,bytes/1024/1024/1024 space,maxbytes/1024/1024/1024 max,autoextensible from dba_data_files;
3) Get the rows count of all the tables for SOA_INFRA schema using below command and then after purging run these sql again to compare how many tables and row has been purged. Before running the sql we need to run one db package to refresh the stats of tables rows.
Step1:
Execute below SQL to refresh the tables row stats-
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('AIOSIT_SOAINFRA')
Step2:
Run below sql to generate count of each tables rows.
select table_name, num_rows from dba_tables where owner = 'AIOSIT_SOAINFRA' and num_rows is not NULL and num_rows > 0 order by num_rows desc;
Implementation Steps
Follow the below steps to run the purging script
Step1:
Open putty or command prompt
Step2:
Change directory to $SOA_HOME\rcu\integration\soainfra\sql\soa_purge
Step3:
In SQL*Plus 'CONNECT SYS AS SYSDBA' and run below commands
GRANT EXECUTE ON DBMS_LOCK TO DEV_SOAINFRA;
GRANT CREATE JOB TO DEV_SOAINFRA;
Note: 'DEV' is the prefix of particular environment which we specify while runnning the RCU for particular environment, so change this value as per your environment
Step4:
Exit from sysdba session
Step5:
Connect to SOAINFRA DB instance now
e.g.
sqlplus DEV_SOAINFRA/Welcome1@SOA11DEV
Note: SOA11DEV should be defined in ‘tnsnames.ora’ file possibly located at this location
/opt/oracle/11.2.0.3/client/network/admin/tnsnames.ora
Step6:
Load the purge scripts by running the main purge script in theMW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge directory.
e.g. @soa_purge_scripts.sql
This should create some procedures, functions, views, types and packages under DEV_SOAINFRA schema which required for purge script.
Step7:
If you want to execute the scripts in debug mode, run common/debug_on.sql
Step8:
If you want to spool the PLSQL program's output to a log file then set serveroutput on. This would help you understand which are the tables getting purged and also what are the eligible records getting purged.
SQL> SET SERVEROUTPUT ON;
SQL> spool '/tmp/spool.log'
Step9:
Run the below code segment to perform 'Looped purge' option using below script
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;
/
Highlighted values has to be change accordingly to individual environment requirements. Please refer below table to get detailed understanding of these items.
Parameter
|
Description
|
min_creation_date
|
Beginning creation date for the composite instances.
|
max_creation_date
|
Ending creation date for the composite instances.
|
batch_size
|
Batch size used to loop the purge. The default value is 20000.
|
max_runtime
|
Expiration at which the purge script exits the loop. The default value is 60. This value is specified in minutes.
|
retention_period
|
Retention period is only used by the BPEL process service engine. This functionality is not extended to other components. This parameter checks for and deletes records in the cube_instance table. The value for this parameter must be greater then or equal to max_creation_date. The default value is null. Specify a retention period if you want to retain the composite instances based on the modify_date of the BPEL instances (cube_instance).
In this example, the modify_date of the BPEL instances table, which can be different than the composite created_date, is used as a second level of filtering:
min_creation_date = 1st June 2011
max_creation_date = 30 June 2011
retention_period = 1st July 2011
This deletes all composite instances in which the creation_time of the composite is between 1st June 2011 and 30 June 2011 and the modify_date of the cube_instance is less than 1st July 2011
|
Step10:
Stop the logging
SQL> spool off
Step11:
Verify the generated log file for purging status
Reference:
http://docs.oracle.com/cd/E28280_01/admin.1111/e10226/soaadmin_partition.htm
http://myexperienceswithsoa.blogspot.com.au/2011/10/using-out-of-box-purge-scripts-in.html
http://oraclefusiontechnologies.blogspot.com.au/2014/03/purging-instances-using-sql-scripts.html
OSB Reporting Data Purging
The reporting module of Oracle Service Bus use to display the reporting data for various Business Transactions. In OSB we use 'Report' action to log the messages in Database. OSB maintains two backend tables to store this data namely WLI_QS_REPORT_DATAand WLI_QS_REPORT_ATTRIBUTE . Reporting data also grow overs the period of time and consume lot of disk space. Oracle Service Bus provide out-of-box capability to purge this data in two ways. We have below two options-
1) Purge OSB Reporting Data based on Date Range
2) Purge All OSB Reporting Data
Apart from above out-of-box option, we have few more options as stated below -
3) Write custom PL/SQL procedure or statement to purge this data on regular basis.
4) Use out of box scripts reporting_runtime_drop.sql and reporting_runtime.sql to delete and create tables if outage approved located at this location $OSB_HOME/dbscripts/oracle
In below example I will show to purge based on given date range using out of box purging facility-
Out-of-Box Purging Option
Step1:
Login to WLS console. Go to " Deployments” and verify below module. It must be active. If it is not active then make this module active.
Step2:
Login to OSB console. Go to Operation >> Reporting >> Message Report >> Click on “Purge Messages”
Step3:
Specify date range and time.
Note: The format of date is M/D/YY. Provided date strictly need to follow this format. E.g. if month is Feb, so we can‘t give 02. We must give 2 only. Similarly If date is 1stFeb suppose then we should give 2/1 not 02/01. Year is only two digit only.
SQL to Purge the OSB Report Data
Run the below sql to purge OSB Reporting Data.
delete from WLI_QS_REPORT_DATA where MSG_GUID in (select MSG_GUID from WLI_QS_REPORT_ATTRIBUTE where trunc(DB_TIMESTAMP) < sysdate-60);
delete from WLI_QS_REPORT_ATTRIBUTE where trunc(DB_TIMESTAMP) < sysdate-60
commit;
Oracle Notes Reference:-
- Automated Methods Of Purging The Reporting Tables In Oracle Service Bus (Doc ID 1080146.1)
- OSB Best Practice For Clearing OSB Report DB (Doc ID 1479520.1
Custom Table Data Purging
Apart of above SOA Components data purging, there could be requirement of purging some custom table data which has been created for custom application (if exists)
If that’s the case then below two different PL/SQL approach could be useful to purge that data as well.
Data Purging using Truncate
In below code we will be deleting data for a particular table called ‘UTILITY_LOG’ based on date clause using truncate option. It does perform below tasks-
a) Count the rows from UTILITY_LOG and print the same
b) Create UTILITY_LOG_TMP table using UTILITY_LOG table definition based up on search criteria for the data which needs to be retain.
c) Truncate original UTILITY_LOG table
d) Copy data back from UTILITY_LOG_TMP to UTILITY_LOG
e) Drop the UTILITY_LOG_TMP table
set serveroutput on;
DECLARE
CNT NUMBER(10) := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('UTILITY_LOG Purging Started');
select count(1) into CNT from UTILITY_LOG;
DBMS_OUTPUT.PUT_LINE('Total Rows in UTILITY_LOG table :' || CNT);
DBMS_OUTPUT.PUT_LINE('Creating and Inserting UTILITY_LOG Temporary Table Based on Search Criteria');
EXECUTE IMMEDIATE 'CREATE TABLE UTILITY_LOG_tmp as select * from UTILITY_LOG where trunc(CREATED_TIME_STAMP) > sysdate-180';
DBMS_OUTPUT.PUT_LINE('Truncating UTILITY_LOG Table');
EXECUTE IMMEDIATE 'truncate table UTILITY_LOG';
DBMS_OUTPUT.PUT_LINE('Inserting rows into UTILITY_LOG from UTILITY_LOG_TMP Temporary Table');
EXECUTE IMMEDIATE 'insert into UTILITY_LOG select * from UTILITY_LOG_tmp';
DBMS_OUTPUT.PUT_LINE('Dropping UTILITY_LOG Temporary Table');
EXECUTE IMMEDIATE 'drop table UTILITY_LOG_tmp';
DBMS_OUTPUT.PUT_LINE('UTILITY_LOG Purging has been completed');
commit;
END;
/
Data Purging using simple ‘Delete’ clause
In this example, it’s simply use the standard delete clause to remove the rows from UTILITY_LOG table.
set serveroutput on;
DECLARE
CNT NUMBER(10) := 0;
CNT1 NUMBER(10) := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('UTILITY_LOG Purging Started');
select count(1) into CNT from UTILITY_CONTROL_REPROCESS1;
DBMS_OUTPUT.PUT_LINE('Total Rows in UTILITY_LOG table :' || CNT);
EXECUTE IMMEDIATE 'Delete from UTILITY_LOG where trunc(CREATED_TIME_STAMP) < sysdate-90';
DBMS_OUTPUT.PUT_LINE('Rows has been deleted from UTILITY_LOG');
select count(1) into CNT1 from UTILITY_LOG;
DBMS_OUTPUT.PUT_LINE('Total Rows in UTILITY_LOG table remains :' || CNT1);
DBMS_OUTPUT.PUT_LINE('UTILITY_LOG Purging has been completed');
commit;
END;
/
B2B Messages Purging
select table_name, num_rows from dba_tables where owner = 'AIOPRD_SOAINFRA' and table_name like '%B2B_%' and num_rows is not NULL and num_rows > 0 order by num_rows desc;
