Tuesday, August 8, 2017

Oracle SOA Suite 11g Instances Purge Data from SOAINFRA Schema

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.

SOAINFRA Database schema will be created by running rcu utility provided by Oracle. SOAINFRA is contains instance and metadata objects for Oracle SOA Suite Components and it will be stored in the database table space. We can purge or delete the instances if we notice that table space size is increasing with huge volume and it is affecting database performances or making any size issue as we are deleting the instances with below category only.

Instances which will get deleted after Applying Script                                                         

v  Completed
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. 

Download

[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;


Posted by at

3 comments:

Subscribe to: Post Comments (Atom)