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 the MW_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_DATA
and 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 1st Feb 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;
