This post has
step by step procedure to Purge/Delete all Closed and Completed SOA composite service
instances for given specified period. This document is created on Virtual
Machine which has SOA Suite, Webcenter Portal and Webcenter Content installed
and integrated.
Instances which
will get deleted after Applying Script
v Faulted
v Terminated by user
v Stale
v
Unknown
Instances
which will NOT get deleted after Applying Script
v
Running
v Suspended
v
Pending Recovery
I have categorized the entire
activities into lists and the main activity will be done on step. If we want to
delete the instances later, please follow the activities mentioned in the step
onwards only.
Step 1: Stop all managed servers and admin server
Step 2:
Copy purge script to Database Server
Step 3:
Connect to the Database Server as sysdba
Step 4:
Grant required privileges to SOAINFRA schema
Step 5: Connect SOAINFRA Schema
Step 6: Execute SOA Purge Script by SOAINFRA user
Step 7: Create Purge Logs Directory
Step 8: Assign Purge Directory Created for SOAINFRA
Schema
Step 9: Execute Purge Procedure
Step 10: Check the Instances after deleting the
instances
Step 1: Stop all managed
servers and admin server
Stop all
running Managed servers (SOA, Spaces, UCM, BAM etc.) along with Admin Server.
[oracle@sit
~]$ cd /u01/app/oracle/product/fmw11g/user_projects/domains/<domain_name>/bin/
[oracle@sit
bin]$ ./stopManagedWebLogic.sh
WC_Spaces
[oracle@sit
bin]$ ./stopManagedWebLogic.sh
soa_server1
[oracle@sit
bin]$ ./stopManagedWebLogic.sh
WC_Collaboration
[oracle@sit
bin]$ ./stopManagedWebLogic.sh
UCM_server1
[oracle@sit
bin]$ ./stopWebLogic.sh
Connect
the database with sysdba and execute the below command to check the instances
before executing the purge.
[oracle@db
soa_purge]$ sqlplus
"/as sysdba"
SQL*Plus:
Release 11.2.0.1.0 Production on Tue Aug 8 09:47:40 2017
Copyright (c)
1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SELECT (CASE
WHEN
STATE=1 THEN 'OPEN AND RUNNING'
WHEN
STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN
STATE=3 THEN 'OPEN AND FAULTED'
WHEN
STATE=4 THEN 'CLOSED AND PENDING'
WHEN
STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN
STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7
THEN 'CLOSED AND CANCELLED'
WHEN
STATE=8 THEN 'CLOSED AND ABORTED'
WHEN
STATE=9 THEN 'CLOSED AND STALE'
WHEN
STATE=10 THEN 'NON-RECOVERABLE'
ELSE
'NON-RECOVERABLE' END)
AS
STATE,COMPOSITE_NAME, COUNT(*) AS NUM_OF_CUBE_INST FROM <SCHEMA_NAME>.CUBE_INSTANCE GROUP BY
STATE,COMPOSITE_NAME;
Step 2: Copy purge script to Database Server
Copy soa_purge.zip
file on some location like ' /u01' and extract this soa_purge zip file. After
extraction it should give you below files inside folder.
Download
the soa_purge.zip from below location.
[oracle@sit
u01]$ chmod a+x soa_purge.zip
[oracle@sit
u01]$ unzip soa_purge.zip
b2b
casemgmt
common
decision
fabric
mediator
orabpel
soa
workflow
README
soa_purge_scripts.sql
Step 3: Connect to the Database Server as sysdba
Log into
the Database host server (SQL * Plus) where SOA INFRA Schema is present. For login
user Sys DBA Username and password.
[oracle@db
soa_purge]$ sqlplus "/as sysdba"
SQL*Plus:
Release 11.2.0.1.0 Production on Tue Aug 8 09:47:40 2017
Copyright (c)
1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Step 4: Grant required privileges to SOAINFRA schema
You must
know the DB schema name of your SOAINFRA setup. Usually it will be like
<VAR>_SOAINFRA where <VAR> hold value like DEV, PROD, STG, etc.
Now we
need to grant some LOCKING and JOB Creation authorization to
<VAR>_SOAINFRA schema.
Here is
the syntax for first one.
Syntax: 'GRANT
EXECUTE ON DBMS_LOCK TO <VAR>_SOAINFRA;'
Syntax: ' GRANT
CREATE JOB TO <VAR>_SOAINFRA;'
Syntax: ' GRANT
CREATE EXTERNAL JOB TO <VAR>_SOAINFRA;'
SQL> GRANT
EXECUTE ON DBMS_LOCK TO SIT_SOAINFRA;
Grant
succeeded.
SQL> GRANT
CREATE JOB TO SIT_SOAINFRA;
Grant
succeeded.
SQL> GRANT
CREATE EXTERNAL JOB TO SIT_SOAINFRA;
Grant
succeeded.
SQL>
Step 5: Connect SOAINFRA
Schema
Connect to
<VAR>_SOAINFRA schema
Now you will
get connected to SQL *Plus as show in below screen.
[oracle@db
bin]$ cd /u01/soa_purge
[oracle@db
soa_purge]$ ls
b2b casemgmt
common decision fabric
mediator orabpel README
soa soa_purge_scripts.sql workflow
[oracle@db
soa_purge]$
[oracle@db
soa_purge]$ sqlplus SIT_SOAINFRA
SQL*Plus:
Release 11.2.0.1.0 Production on Tue Aug 8 10:04:38 2017
Copyright (c)
1982, 2009, Oracle. All rights reserved.
Enter
password:
Connected to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Step 6: Execute SOA Purge
Script by SOAINFRA user
Now in same
SQL plus session we will run 'soa_purge_scripts.sql' script file which will
create internal procedures, functions, types,pachakes etc for purge script to
run.
Syntax:
@soa_purge_scripts.sql
SQL>
@soa_purge_scripts.sql
Step 7: Create Purge Logs
Directory
Now Exit from
SQL * Plus using 'exit' command and we will create folder where purge logs will
be stored in case any error occurs. Our purge logs folder will be
'/oracle/PurgeLogs'
Syntax: mkdir
–p /u01/PurgeLogs
[oracle@db
/]$ mkdir –p /u01/PurgeLogs
[oracle@db
u01]$ cd PurgeLogs/
[oracle@db
PurgeLogs]$ ls
[oracle@db
PurgeLogs]$
And finally
exit terminal
Step 8: Assign Purge
Directory Created for SOAINFRA Schema
Open SQL *
Plus with System DBA username to grant read and write permissions to Purge logs
folder which we created in earlier step.
[oracle@db
PurgeLogs]$ sqlplus "/as sysdba"
SQL*Plus:
Release 11.2.0.1.0 Production on Tue Aug 8 10:31:13 2017
Copyright
(c) 1982, 2009, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
This will
connect to DB with DBA privilege
Now,
create SOA_PURGE_DIRECTORY
Syntax:
CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS '/u01/PurgeLogs';
SQL>
CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS '/u01/PurgeLogs';
Directory
created.
SQL>
Then,
Grant Read and write permission to SIT_SOAINFRA user to this directory.
Syntax:
GRANT READ, WRITE ON DIRECTORY SOA_PURGE_DIR TO SIT_SOAINFRA;
SQL> GRANT
READ, WRITE ON DIRECTORY SOA_PURGE_DIR TO SIT_SOAINFRA;
Grant
succeeded.
SQL>
Step 9: Execute Purge
Procedure
In this
step, we will actually run Delete Procedure to delete instances for specified
time.
While running procedure we need to
specify 'From Date' in min_creation_date and 'To Date' in max_creation_date.
Also we
need to specify Retention_Period: This parameter checks for and deletes records in the
cube_instance table. The value for this parameter
must be greater than 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.
Execute
below Procedure and wait for Response. If Number of instances to be deleted are
more than successful execution of procedure take 5-10 Minutes.
Syntax: DECLARE
max_creation_date
timestamp;
min_creation_date
timestamp;
batch_size
integer;
max_runtime
integer;
retention_period
timestamp;
BEGIN
min_creation_date
:= to_timestamp('2015-01-01','YYYY-MM-DD');
max_creation_date
:= to_timestamp('2017-08-08','YYYY-MM-DD');
max_runtime
:= 60;
retention_period
:= to_timestamp('2017-08-10','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);
END;
Step 10: Check the Instances
after deleting the instances
In this
step, after running delete procedure we will see how many instances got deleted
in CUBE Engine.
Run below
SELECT SQL on SOAINFRA DB connection we made in step 9, it will fetch all
instances along with their state, composite name and count.
You can
compare the instance count in step 10 and Step 12 that means Before execution
of delete procedure and after execution of delete procedure.
Syntax:
SELECT (CASE
WHEN
STATE=1 THEN 'OPEN AND RUNNING'
WHEN
STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN
STATE=3 THEN 'OPEN AND FAULTED'
WHEN
STATE=4 THEN 'CLOSED AND PENDING'
WHEN
STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN
STATE=6 THEN 'CLOSED AND FAUTED'
WHEN
STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN
STATE=8 THEN 'CLOSED AND ABORTED'
WHEN
STATE=9 THEN 'CLOSED AND STALE'
WHEN
STATE=10 THEN 'NON-RECOVERABLE'
ELSE
'NON-RECOVERABLE' END)
AS
STATE,COMPOSITE_NAME, COUNT(*) AS NUM_OF_CUBE_INST FROM <SCHEMA>.CUBE_INSTANCE
GROUP BY STATE,COMPOSITE_NAME;
Example
SELECT
(CASE
WHEN
STATE=1 THEN 'OPEN AND RUNNING'
WHEN
STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN
STATE=3 THEN 'OPEN AND FAULTED'
WHEN
STATE=4 THEN 'CLOSED AND PENDING'
WHEN
STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN
STATE=6 THEN 'CLOSED AND FAUTED'
WHEN
STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN
STATE=8 THEN 'CLOSED AND ABORTED'
WHEN
STATE=9 THEN 'CLOSED AND STALE'
WHEN
STATE=10 THEN 'NON-RECOVERABLE'
ELSE
'NON-RECOVERABLE' END)
AS
STATE,COMPOSITE_NAME, COUNT(*) AS NUM_OF_CUBE_INST FROM SIT_SOAINFRA.CUBE_INSTANCE
GROUP BY STATE,COMPOSITE_NAME;
