Oracle Fusion Middleware and Oracle Application : SOA admin

Showing posts with label SOA admin. Show all posts

Saturday, 14 March 2015

File polling frequency setting in SOA

Issue : Files were not getting picked up from sftp location .

Workaround:

Touch the files in sftp location and go to EM console to do as shown in screenshot,

Click on the composites >>Service/Reference Properties>>FTP adapter get <specific composite>data and change polling frequency.

EM login slowness issue

Navigate to fmwc System mBean browser.

Access following AdminServer mBean for setting the cache property.

emoms.props:Location=AdminServer,name=emoms.properties,type=Properties,Application=em

Setting following three properties. Unless using non-default values, the last two properties are optional.

# Enable caching of FMw Discovery data and use it for other subsequent users.
# Values=true/false Default=false
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true

# If caching of discovery data is true, this parameter indicates how long the discovery data
# from cache should be used before requiring a fresh discovery.
# Time value is in milliseconds. Default is 7200000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_CACHE_AGE=7200000

# If caching of discovery data is true, a user logs in and a discovery session is in progress,
# this parameter indicates how long the user can wait for current discovery to complete.
# After this wait time is elapsed and discovery is still not finished: If there is already data
# in cache it will be used, else the user will launch a new discovery session.
# Time value is in milliseconds. Default is 10000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_WAIT_TIME=10000

Following is example of setting oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true

If new targets are added after enabling discovery cache and new targets are not displayed in fmwc, perform a manual refresh of the Farm to update the discovery cache.

Heap Resizement of SOA Server

Heap Size vale entry in last of setDomainEnv.sh file for SOA Servers and associated OSB.
==================================================
if [ "${SERVER_NAME}" == "AdminServer" ] ;
then
MEM_ARGS="-Xms1024m -Xmx1024m"
export MEM_ARGS
fi

if [[ "${SERVER_NAME}" == *osb_server* ]] ;
then
MEM_ARGS="-Xms2048m -Xmx2048m"
export MEM_ARGS
fi
Heap Size vale entry in last of setSOADomainEnv.sh file for SOA servers.
==================================================
if [[ "${SERVER_NAME}" == *soa_server* ]] ;
then
USER_MEM_ARGS="-Xms5192m -Xmx5192m -Xgc:gencon -Xverbose:gc -Xverboselog:/u01/SOA/Middleware/java/gclog/gclog.txt -XXgcTrigger=20"
export USER_MEM_ARGS
fi

GC logs of server : Xverboselog:/u01/SOA/Middleware/java/gclog/gclog.txt

SOA server polling issue

Resolution steps for SOA file polling issue:

1. Stop the Servers (whole domain Admin server,SOA).

2. Backup the controlDir folders.
Backup and delete the Controlfiles directory , when servers starts it will be created again.
3. Delete cache, tmp
Delete tmp, cacche when server starts these directory will be craeted again.
4. Backup the FILEADAPTER_IN table and remove status value as 2 (PROCESSED).
Check if table contains file_processed status=2.
select count(*) from FILEADAPTER_IN where file_processed=2;
If above return value is equal to row count of whole table then take a back up of table and truncate.

5. Touch the files to new timestamp.
put command : touch -m * inside locate where files are availble for pollling.
6. Restart the Servers.
Restat whole domain including Admin Server.

Now Check the server polling files or not .. hope it will start polling .
Same you can check from EM console with instance through interface.

Friday, 1 August 2014

Purging script for SOA

Purging script for SOA ,command as given below:

spool '&spoolfilename'
-- Start of Purge Script
----------------------------------
-- Purge the MEDIATOR data
----------------------------------
delete from mediator_case_instance;
delete from mediator_audit_document;
delete from mediator_callback;
delete from mediator_group_status;
delete from mediator_payload;
delete from mediator_deferred_message;
delete from mediator_resequencer_message;
delete from mediator_case_detail;
delete from mediator_correlation;
delete from mediator_instance;
commit;

----------------------------
-- Purge the BPEL data
----------------------------
delete from headers_properties;
delete from ag_instance;
delete from audit_counter;
delete from audit_trail;
delete from audit_details;
delete from ci_indexes;
delete from work_item;
delete from wi_fault;
delete from xml_document_ref;
delete from xml_document;
delete from document_dlv_msg_ref;
delete from document_ci_ref;
delete from dlv_subscription;
delete from dlv_message;
delete from rejected_msg_native_payload;
delete from instance_payload;
delete from test_details;
delete from cube_scope;
delete from cube_instance;
commit;

---------------------------
-- Purge the BPM data
---------------------------
delete from bpm_audit_query;
delete from bpm_measurement_actions;
delete from bpm_measurement_action_exceps;
delete from bpm_cube_auditinstance;
delete from bpm_cube_taskperformance;
delete from bpm_cube_processperformance;
commit;

------------------------------------
-- Purge the WORKFLOW data
-- ---------------------------------
delete from wftask_tl;
delete from wftaskhistory;
delete from wftaskhistory_tl;
delete from wfcomments;
delete from wfmessageattribute;
delete from wfattachment;
delete from wfassignee;
delete from wfreviewer;
delete from wfcollectiontarget;
delete from wfroutingslip;
delete from wfnotification;
delete from wftasktimer;
delete from wftaskerror;
delete from wfheaderprops;
delete from wfevidence;
delete from wftaskassignmentstatistic;
delete from wftaskaggregation;
delete from wftask;
commit;

------------------------------------
-- Purge the COMPOSITE data
------------------------------------
delete from composite_sensor_value;
delete from composite_instance_assoc;
delete from attachment;
delete from attachment_ref;
delete from composite_instance_fault;
delete from reference_instance;
delete from component_instance;
delete from composite_instance;

commit;

------------------------
-- Reclaim disk space
------------------------
alter table mediator_case_instance enable row movement;
alter table mediator_case_instance shrink space;
alter table mediator_case_instance disable row movement;
alter table mediator_audit_document enable row movement;
alter table mediator_audit_document shrink space;
alter table mediator_audit_document disable row movement;
alter table mediator_callback enable row movement;
alter table mediator_callback shrink space;
alter table mediator_callback disable row movement;
alter table mediator_group_status enable row movement;
alter table mediator_group_status shrink space;
alter table mediator_group_status disable row movement;
alter table mediator_payload enable row movement;
alter table mediator_payload shrink space;
alter table mediator_payload disable row movement;
alter table mediator_deferred_message enable row movement;
alter table mediator_deferred_message shrink space;
alter table mediator_deferred_message disable row movement;
alter table mediator_resequencer_message enable row movement;
alter table mediator_resequencer_message shrink space;
alter table mediator_resequencer_message disable row movement;
alter table mediator_case_detail enable row movement;
alter table mediator_case_detail shrink space;
alter table mediator_case_detail disable row movement;
alter table mediator_correlation enable row movement;
alter table mediator_correlation shrink space;
alter table mediator_correlation disable row movement;
alter table mediator_instance enable row movement;
alter table mediator_instance shrink space;
alter table mediator_instance disable row movement;
alter table headers_properties enable row movement;
alter table headers_properties shrink space;
alter table headers_properties disable row movement;
alter table ag_instance enable row movement;
alter table ag_instance shrink space;
alter table ag_instance disable row movement;
alter table audit_counter enable row movement;
alter table audit_counter shrink space;
alter table audit_counter disable row movement;
alter table audit_trail enable row movement;
alter table audit_trail shrink space;
alter table audit_trail disable row movement;
alter table audit_details enable row movement;
alter table audit_details shrink space;
alter table audit_details disable row movement;
alter table ci_indexes enable row movement;
alter table ci_indexes shrink space;
alter table ci_indexes disable row movement;
alter table work_item enable row movement;
alter table work_item shrink space;
alter table work_item disable row movement;
alter table wi_fault enable row movement;
alter table wi_fault shrink space;
alter table wi_fault disable row movement;
alter table xml_document_ref enable row movement;
alter table xml_document_ref shrink space;
alter table xml_document_ref disable row movement;
alter table document_dlv_msg_ref enable row movement;
alter table document_dlv_msg_ref shrink space;
alter table document_dlv_msg_ref disable row movement;
alter table document_ci_ref enable row movement;
alter table document_ci_ref shrink space;
alter table document_ci_ref disable row movement;
alter table dlv_subscription enable row movement;
alter table dlv_subscription shrink space;
alter table dlv_subscription disable row movement;
alter table dlv_message enable row movement;
alter table dlv_message shrink space;
alter table dlv_message disable row movement;
alter table rejected_msg_native_payload enable row movement;
alter table rejected_msg_native_payload shrink space;
alter table rejected_msg_native_payload disable row movement;
alter table instance_payload enable row movement;
alter table instance_payload shrink space;
alter table instance_payload disable row movement;
alter table test_details enable row movement;
alter table test_details shrink space;
alter table test_details disable row movement;
alter table cube_scope enable row movement;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;
alter table cube_instance enable row movement;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;
alter table bpm_audit_query enable row movement;
alter table bpm_audit_query shrink space;
alter table bpm_audit_query disable row movement;
alter table bpm_measurement_actions enable row movement;
alter table bpm_measurement_actions shrink space;
alter table bpm_measurement_actions disable row movement;
alter table bpm_measurement_action_exceps enable row movement;
alter table bpm_measurement_action_exceps shrink space;
alter table bpm_measurement_action_exceps disable row movement;
alter table bpm_cube_auditinstance enable row movement;
alter table bpm_cube_auditinstance shrink space;
alter table bpm_cube_auditinstance disable row movement;
alter table bpm_cube_taskperformance enable row movement;
alter table bpm_cube_taskperformance shrink space;
alter table bpm_cube_taskperformance disable row movement;
alter table bpm_cube_processperformance enable row movement;
alter table bpm_cube_processperformance shrink space;
alter table bpm_cube_processperformance disable row movement;
alter table wftask_tl enable row movement;
alter table wftask_tl shrink space;
alter table wftask_tl disable row movement;
alter table wftaskhistory enable row movement;
alter table wftaskhistory shrink space;
alter table wftaskhistory disable row movement;
alter table wftaskhistory_tl enable row movement;
alter table wftaskhistory_tl shrink space;
alter table wftaskhistory_tl disable row movement;
alter table wfcomments enable row movement;
alter table wfcomments shrink space;
alter table wfcomments disable row movement;
alter table wfmessageattribute enable row movement;
alter table wfmessageattribute shrink space;
alter table wfmessageattribute disable row movement;
alter table wfattachment enable row movement;
alter table wfattachment shrink space;
alter table wfattachment disable row movement;
alter table wfassignee enable row movement;
alter table wfassignee shrink space;
alter table wfassignee disable row movement;
alter table wfreviewer enable row movement;
alter table wfreviewer shrink space;
alter table wfreviewer disable row movement;
alter table wfcollectiontarget enable row movement;
alter table wfcollectiontarget shrink space;
alter table wfcollectiontarget disable row movement;
alter table wfroutingslip enable row movement;
alter table wfroutingslip shrink space;
alter table wfroutingslip disable row movement;
alter table wfnotification enable row movement;
alter table wfnotification shrink space;
alter table wfnotification disable row movement;
alter table wftasktimer enable row movement;
alter table wftasktimer shrink space;
alter table wftasktimer disable row movement;
alter table wftaskerror enable row movement;
alter table wftaskerror shrink space;
alter table wftaskerror disable row movement;
alter table wfheaderprops enable row movement;
alter table wfheaderprops shrink space;
alter table wfheaderprops disable row movement;
alter table wfevidence enable row movement;
alter table wfevidence shrink space;
alter table wfevidence disable row movement;
DROP INDEX WFTASKASSIGNMENTSTATISTICID_I;
DROP INDEX WFTASKASSTARTENDDATE_I;
DROP INDEX WFTASKASASSIGNEETYPE_I;
alter table wftaskassignmentstatistic enable row movement;
alter table wftaskassignmentstatistic shrink space;
alter table wftaskassignmentstatistic disable row movement;
CREATE INDEX "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTICID_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" ("TASKID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."WFTASKASSTARTENDDATE_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" ("STARTDATE", "ENDDATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."WFTASKASASSIGNEETYPE_I" ON "DEV_SOAINFRA"."WFTASKASSIGNMENTSTATISTIC" (LOWER("ASSIGNEE"), "ASSIGNEETYPE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table wftaskaggregation enable row movement;
alter table wftaskaggregation shrink space;
alter table wftaskaggregation disable row movement;
alter table wftask enable row movement;
alter table wftask shrink space;
alter table wftask disable row movement;
alter table composite_sensor_value enable row movement;
alter table composite_sensor_value shrink space;
alter table composite_sensor_value disable row movement;
alter table composite_instance_assoc enable row movement;
alter table composite_instance_assoc shrink space;
alter table composite_instance_assoc disable row movement;
alter table attachment enable row movement;
alter table attachment shrink space;
alter table attachment disable row movement;
alter table attachment_ref enable row movement;
alter table attachment_ref shrink space;
alter table attachment_ref disable row movement;
DROP INDEX COMPOSITE_INSTANCE_FAULT_CIDN;
DROP INDEX COMPOSITE_INSTANCE_FAULT_CT_EC;
DROP INDEX COMPOSITE_INSTANCE_FAULT_EC_CT;
DROP INDEX COMPOSITE_INSTANCE_FAULT_ECID;
alter table composite_instance_fault enable row movement;
alter table composite_instance_fault shrink space;
alter table composite_instance_fault disable row movement;
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_CIDN" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("COMPOSITE_DN", "ERROR_CATEGORY") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_CT_EC" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("CREATED_TIME" DESC, "ERROR_CATEGORY") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_EC_CT" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("ERROR_CATEGORY", "CREATED_TIME" DESC) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT_ECID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE_FAULT" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
DROP INDEX REFERENCE_INSTANCE_CDN_STATE;
DROP INDEX REFERENCE_INSTANCE_CO_ID;
DROP INDEX REFERENCE_INSTANCE_ECID;
DROP INDEX REFERENCE_INSTANCE_ID;
DROP INDEX REFERENCE_INSTANCE_STATE;
DROP INDEX REFERENCE_INSTANCE_TIME_CDN;
alter table reference_instance enable row movement;
alter table reference_instance shrink space;
alter table reference_instance disable row movement;
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_CDN_STATE" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_CO_ID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("PROTOCOL_CORRELATION_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_ECID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_ID" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_STATE" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."REFERENCE_INSTANCE_TIME_CDN" ON "DEV_SOAINFRA"."REFERENCE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table component_instance enable row movement;
alter table component_instance shrink space;
alter table component_instance disable row movement;
DROP INDEX COMPOSITE_INSTANCE_CIDN;
DROP INDEX COMPOSITE_INSTANCE_CO_ID;
DROP INDEX COMPOSITE_INSTANCE_CREATED;
DROP INDEX COMPOSITE_INSTANCE_ECID;
DROP INDEX COMPOSITE_INSTANCE_ID;
DROP INDEX COMPOSITE_INSTANCE_STATE;
alter table composite_instance enable row movement;
alter table composite_instance shrink space;
alter table composite_instance disable row movement;
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CIDN" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("COMPOSITE_DN", "STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CO_ID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("CONVERSATION_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_CREATED" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("CREATED_TIME" DESC, "COMPOSITE_DN") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_ECID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("ECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_ID" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
CREATE INDEX "DEV_SOAINFRA"."COMPOSITE_INSTANCE_STATE" ON "DEV_SOAINFRA"."COMPOSITE_INSTANCE" ("STATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DEV_SOAINFRA";
alter table audit_details modify lob (bin) (shrink space);
alter table composite_instance_fault modify lob (error_message) (shrink space);
alter table composite_instance_fault modify lob (stack_trace) (shrink space);
alter table cube_scope modify lob (scope_bin) (shrink space);
alter table reference_instance modify lob (error_message) (shrink space);
alter table reference_instance modify lob (stack_trace) (shrink space);
alter table test_definitions modify lob (definition) (shrink space);
alter table wi_fault modify lob (message) (shrink space);
alter table xml_document modify lob (document) (shrink space);

-- The below statements cannot run on Oracle XE

alter index ad_pk rebuild online;
alter index at_pk rebuild online;
alter index ci_creation_date rebuild online;
alter index ci_custom3 rebuild online;
alter index ci_ecid rebuild online;
alter index ci_name_rev_state rebuild online;
alter index ci_pk rebuild online;
alter index composite_instance_cidn rebuild online;
alter index composite_instance_co_id rebuild online;
alter index composite_instance_created rebuild online;
alter index composite_instance_ecid rebuild online;
alter index composite_instance_id rebuild online;
alter index composite_instance_state rebuild online;
alter index cs_pk rebuild online;
alter index dm_conversation rebuild online;
alter index dm_pk rebuild online;
alter index doc_dlv_msg_guid_index rebuild online;
alter index doc_store_pk rebuild online;
alter index ds_conversation rebuild online;
alter index ds_conv_state rebuild online;
alter index ds_fk rebuild online;
alter index ds_pk rebuild online;
alter index header_properties_pk rebuild online;
alter index instance_payload_key rebuild online;
alter index reference_instance_cdn_state rebuild online;
alter index reference_instance_co_id rebuild online;
alter index reference_instance_ecid rebuild online;
alter index reference_instance_id rebuild online;
alter index reference_instance_state rebuild online;
alter index reference_instance_time_cdn rebuild online;
alter index state_type_date rebuild online;
alter index wf_crdate_cikey rebuild online;
alter index wf_crdate_type rebuild online;
alter index wf_fk2 rebuild online;
alter index wifault_pk rebuild online;
alter index wi_expired rebuild online;
alter index wi_key_crdate_state rebuild online;
alter index wi_pk rebuild online;
alter index wi_stranded rebuild online;
alter index xml_doc_reference_pk rebuild online;
commit;

--- End of Purge script
spool off
exit

Thursday, 22 May 2014

ORA-01653: unable to extend table

Issue:

[oracle@soauat132 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/

[oracle@soauat132 bin]$ . oraenv

ORACLE_SID = [oracle] ? soauat

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app

[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 22 09:11:24 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oradata/soauat/system01.dbf

/u01/app/oradata/soauat/sysaux01.dbf

/u01/app/oradata/soauat/undotbs01.dbf

/u01/app/oradata/soauat/users01.dbf

/u01/app/oradata/soauat/example01.dbf

/u01/app/oradata/soauat/DEV_iassdpm.dbf

/u01/app/oradata/soauat/DEV_mds.dbf

/u01/app/oradata/soauat/DEV_soainfra.dbf

/u01/app/oradata/soauat/DEV_orabam.dbf

9 rows selected.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

DEV_IAS_ORASDPM

DEV_MDS

DEV_SOAINFRA

DEV_IAS_TEMP

DEV_ORABAM

11 rows selected.

SQL> alter tablespace add datafile'/u01/app/oradata/soauat/DEV_soainfra01.dbf' size 1024m;

alter tablespace add datafile'/u01/app/oradata/soauat/DEV_soainfra01.dbf' size 1024m

ERROR at line 1:

ORA-02140: invalid tablespace name

SQL> alter tablespace DEV_SOAINFRA add datafile'/u01/app/oradata/soauat/DEV_soainfra01.dbf' size 1024m;

Tablespace altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oradata/soauat/system01.dbf

/u01/app/oradata/soauat/sysaux01.dbf

/u01/app/oradata/soauat/undotbs01.dbf

/u01/app/oradata/soauat/users01.dbf

/u01/app/oradata/soauat/example01.dbf

/u01/app/oradata/soauat/DEV_iassdpm.dbf

/u01/app/oradata/soauat/DEV_mds.dbf

/u01/app/oradata/soauat/DEV_soainfra.dbf

/u01/app/oradata/soauat/DEV_orabam.dbf

/u01/app/oradata/soauat/DEV_soainfra01.dbf

10 rows selected.

SQL>

Now onward you can deal with tablespace... DEV_SOAINFRA

Wednesday, 21 May 2014

Purge BPEL Process Manager Instances from SOA

Purge BPEL Process Manager Instances from SOA

The objective of this BLOG thread is to provide step by step instructions as well as examples showing how to purge Oracle BPEL Process Manager 11g instances using purge scripts.

1. Log in to the schema.

If you're using 11g Oracle BPEL Process Manager installed as part of an 11g Oracle SOA Suite or 11g Oracle BPM Suite, you'll start by granting the privileges to the <DEV>_SOAINFRA user, then logging in to the <DEV>_SOAINFRA schema.

sqlplus /nolog
>sqlplus / as sysdba

SQL> GRANT EXECUTE ON DBMS_LOCK TO <DEV>_SOAINFRA;

Grant succeeded.

SQL> GRANT CREATE ANY JOB TO <DEV>_SOAINFRA;

Grant succeeded.

SQL>exit

>sqlplus <DEV>_SOAINFRA/<password>

2. Install the Procedures (First time Users Only )

Run the script to install the procedures. This is a one time installation step. If you're not sure where the scripts to install the procedures are located in your environment

In server :

SQL>/u01/Middleware/Oracle_SOA1/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql

SQL> @<Oracle SOA Home>/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql
Procedure created.
Function created.
Type created.
Type body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.
SQL>

3. Identify and/or Customize the Purge Procedure

You will need to identify the syntax and possibly customize the procedure according to your purging goal. Depending upon the version, there may be several possible procedures available for use. Review the text of the installation script or the product documentation for more details on the procedures.

4. Call the Procedure

Once the procedure is loaded and you've identified the method you'll use to purge instances, you'll run the procedure. You may want to perform a simple select statement to count the existing instances prior to purging.

· To clear out instances from a specific date/time (ie...January 1, 2011 to January 31, 2011):

SQL> call soa.delete_instances(to_timestamp('2010-01-01','YYYY-MM-DD'), to_timestamp('2010-01-31','YYYY-MM-DD'));

· To call the purge_instances procedure to clear out data older than 2 weeks (14 days)

SQL> call soa.delete_instances(SYSDATE -14, SYSDATE);

To clear out instances using the explicit single threaded script as shown in the readme (ie.. January 1, 2011 to January 31, 2011):

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

Example
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('2011-09-23','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-09-23','YYYY-MM-DD'); //Retain everything before 2010-09-23
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;
/

To clear out instances using the explicit parallel script as shown in the readme (ie.. January 1, 2011 to January 31, 2011):

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

5. Recover Space as needed - optional

Space recovery is not included in the Purge Script as this is a specific Database Maintenance task that needs to be performed by a DBA. The following sample instructions can help in space recovery:

1. Run an index rebuild, see:
Oracle Database Administrator's Guide
11g Release 2 (11.2)

2. Running PL/SQL for space release:
alter table <TABLE_NAME> deallocate unused;
alter table <TABLE_NAME> enable row movement;
alter table <TABLE_NAME> shrink space compact;
alter table <TABLE_NAME> shrink space;
alter table <TABLE_NAME> disable row movement;