How to deploy MDS files to SOA suite 11g/12c

How to deploy mds using Jdeveloper?

One of the new features in Jdeveloper 12c is the ability to transfer mds data directly from the JDeveloper to the server.

• Create new local MDS connection in JDeveloper
• File > New > From Gallery > Search for SOA-MDS connection

• Similarly create MDS connection to the remote server that you want to deploy to.

• Now right click on your local file based mds and select Transfer To
• And select the folders, files and your target mds connection.

• Finally click OK

Unfortunately it doesn't work on jdeveloper 11g and your gonna have to rely on ant script.

How to deploy mds using ANT?

This is a really simple script to deploy MDS on Oracle SOA suite and it should work on both 11g and 12c SOA suite.

Create both build.properties file and build.xml file in the same folder as your MDS folder.

Folder structure should look as follows:
Project/.
Project/mds
Project/mds/apps/
Project/mds/apps/ApplicationObjectLibrary/
Project/mds/apps/DVM/
Project/build.xml
Project/build.properties


build.properties
#MDS folder in your current directory
mds.repository=mds

#this is the oracle middleware home.
middleware.home=C:/Oracle/Middleware/Oracle_Home
deploy.serverURL=http://localhost:7101
deploy.user=weblogic
deploy.password=welcome1


build.xml
<?xml version="1.0" encoding="iso-8859-1"?>
<!--
   Deployment Scripts for SOA Suite 12c, MDS
   Author : Prasanna Jayaraman
   Version: 4.0
-->
<project name="exportMDS" default="exportMDS">
  <property file="build.properties"/>
  <target name="exportMDS">
    <echo>export and create local MDS temp</echo>
    <echo>create zip from file MDS store</echo>
    
    <delete dir="deploy"/>
    <mkdir dir="deploy"/>
    <zip destfile="deploy/mds.jar" compress="false">
      <fileset dir="${mds.repository}/apps"/>
    </zip>
    
    <ant antfile="${middleware.home}/soa/bin/ant-sca-deploy.xml" inheritall="false" target="deploy">
      <property name="wl_home" value="${middleware.home}/wlserver"/>
      <property name="oracle.home" value="${middleware.home}/soa"/>
      <property name="serverURL" value="${deploy.serverURL}"/>
      <property name="user" value="${deploy.user}"/>
      <property name="password" value="${deploy.password}"/>
      <property name="overwrite" value="true"/>
      <property name="forceDefault" value="true"/>
      <property name="sarLocation" value="deploy/mds.jar"/>
      <property name="failOnError" value="true"/>
    </ant>
    <echo message="finish" level="info"></echo>
  </target>
</project>

How to delete deployed MDS?

If you deployed wrong files or deployed file to a wrong path, you could delete the document in the mds using the following commands. It works on both 11g and 12c SOA suite. 

Open cmd and enter the following
set JAVA_HOME=C:\UBM\Programs\jdk1.7.0_79

set path=C:\UBM\Programs\jdk1.7.0_79\bin;C:\Oracle\Middleware12c\Oracle_Home\oracle_common\modules\org.apache.ant_1.9.2\bin;%PATH%

cd C:\Oracle\Middleware12c\Oracle_Home\soa\common\bin

wlst.cmd

connect('weblogic', 'welcome1', 't3://localhost:7101');

deleteMetadata(application='soa-infra',server='DefaultServer',docs='/apps/**');


This command will delete all the files under /apps directory in mds. If your using unix or linux then use linux syntax for setting path variable and java_home and then run wlst.sh under the same oracle path.


Other Useful Links

• Refresh MDS cache in SOA suite 11g/12c
• How to export MDS from SOA suite

How to zip the folder in windows using bat script

It often happens that when you working on ADF and SOA suite using Jdeveloper and something goes wrong and you haven't got a clue what it is. This is mainly because the jdevelopers error reporting is really poor and it doesn't actually say which recent change has caused the XX error in your project.


Unfortunately there is no quick reference that could help resolving the issues. I often rollback to the last checked-in version to fix the issue. If your recent work is not checked in to source control its going to be a nightmare finding the problem.


The work around I have been using, to roll back unchecked-in work, is to zip all the files up using a scheduled windows script, so that I could go back whenever I want or to compare previous files.

Note: If you never used JDeveloper, you will have hard time understanding why we do this.


How to zip the folder in windows using script (No extra Software needed)

• Create a file zip.vbs with following content

'Get command-line arguments.
Set objArgs = WScript.Arguments
InputFolder = objArgs(0)
ZipFile = objArgs(1)

'Create empty ZIP file.
CreateObject("Scripting.FileSystemObject").CreateTextFile(ZipFile, True).Write "PK" & Chr(5) & Chr(6) & String(18, vbNullChar)

Set objShell = CreateObject("Shell.Application")

Set source = objShell.NameSpace(InputFolder).Items

objShell.NameSpace(ZipFile).CopyHere(source)

'Required!
wScript.Sleep 20000

• Create archieve.bat script with following content in the same folder

for /f "delims=" %%a in ('wmic OS Get localdatetime ^| find "."') do set "dt=%%a"
set "DATE=%dt:~0,8%"
set "HOUR=%dt:~8,4%"

set datestamp=%DATE%_%HOUR%
echo datestamp: "%datestamp%"

CScript C:\JDeveloper\mywork\zip.vbs  C:\JDeveloper\mywork\Project12c C:\JDeveloper\mywork\Project12c_%datestamp%.zip



This script will zip the Project12c folder into Project12c_YYYYMMDD_HHMM.zip format. e.g. Project12c_20151126_1000.zip

Now it could be either scheduled in windows or double click the archieve.bat file to zip the project whenever it's needed.

Note 2: If there is a better solution, please leave it on the comments section.

JDeveloper shortcuts

These are some of the useful shortcuts for Jdeveloper

Navigation

Find Java class: CTRL+ Minus (-)
Find Any file: CTRL + ALT + Minus (-)
Go to Recent File: CTRL + =
Go to Line Number: CTRL + g
Switch between open files: CTRL + tab
Back: ALT + Left
Forward: ALT + Right
Go to Last Edit: CTRL+Shift + Backspace
Locate a file in Project window: ALT + Home
Find Usages: CTRL + ALT + U


Editing

Completion Insight: CTRL + Space
Smart Insight: CTRL + ALT + Space
Parameter Insight: CTRL + SHIFT + Space
Complete Statement: CTRL + Shift + Enter
Expand Template: CTRL + Enter
Toggle Comment: CTRL + Slash (/)


Refactoring

Rename: CTRL + ALT + R
Move: CTRL + ALT + M
Inline: CTRL + ALT + N
Delete Safely: ALT + Delete
Introduce Field: CTRL + ALT + F
Introduce Variable: CTRL + ALT + V
Introduce Parameter: CTRL + ALT + P
Introduce Constant: CTRL + ALT + C
Extract Method: CTRL + ALT + X


Searching

Incremental Find Forward: CTRL + E
Incremental Find Backward Shift + E
Highlight Text: CTRL + ALT + H
Clear Highlight: CTRL + ALT + K


Run

Run Project: F11
Make Project: CTRL + F9
Rebuild Project: ALT+ F9


You could find more of these in JDeveloper > Tools > Preferences > Shortcuts and the keys could be reassigned as well.

Refresh MDS in SOA Suite

How to refresh or reload MDS in SOA suite (11g/12c)?

MDS is the best way to store the common artifacts in SOA suite.The soa suite caches these artifacts in the memory when it is accessed for the first, oracle does it to improve performance of the MDS access.

But the problem here is when the MDS artifacts, like DVM and wsdls, are redeployed with changes, the cache won't get cleared automatically leaving stale values in the cache.

The cached mds objects are cleared when the server is restarted and it takes a lot of time to restart SOA servers.

Alternatively SOA suite provides an MBEAN which can be used to clear the cache after deploying the MDS changes. Here are the steps


Steps to clear MDS cache

• Login in to Oracle Weblogic em console 

• e.g.; http://localhost:7001/em

• Expand the Weblogic domain > DefaultDomain from the left hand side navigation tree
• Right click on the DefaultDomain and select System Mbean Browser

                  

• In the MBean Browser navigate to Application Define Mbeans -> oracle.mds.lcm > Server: DefaultServer > Application: soa-infra -> MDSAppRuntime > MDSAppRuntime Bean

• And switch to Operations tab

• Click on clearCache link and Invoke the method

How to export MDS to a file from SOA Suite

What is MDS in SOA suite?

MDS or Metadata Services is a key infrastructure component in Fusion Middleware which allows for sharing common artifacts among the SOA components. Some of the examples inlcude wsdls, xsd, dvm(domain value maps), transformations, policies, config files etc.

And in SOA Suite, SOA composites are also stored in the MDS when deployed to the servers.


How to export a MDS from SOA suite?

1. Go to em console
1. http://soaserver.name:portnumber/em
2. Expand soa_domain and SOA on the left hand side tree
3. Right click on the soa-infra > Administration > MDS Configuration





4. Click on the Export button and save the zip file. This file should contain all the artifacts deployed on the SOA suite.

SQL Query tips

Multiple row generation from DUAL

select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>
This query works on both 9i and 10g

Desc query

In oracle, you would have used desc to describe the table. Here is how to generate DESC output through sql query.

SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';

How to convert row values into columns. or how to perform multi dimensional query?

For Eg
Dept
Year
Amount
A
2005
100
B
2005
500
C
2005
344
A
2006
400
B
2006
122

Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.

Dept
2005
2006
A
100
400
B
500
122
C
344
0

The challenge is to bring the row values into columns. And here is the solution.

SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;

How can one dump/ examine the exact content of a database column?

SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;

DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32


For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

Find out foreign constraints referring your table

select table_name, constraint_name from user_constraints
where r_constraint_name IN (select constraint_name from user_constraints where table_name = 'XML_DOCUMENT' and constraint_type = 'P')
and constraint_type = 'R' ;

ADF EL expressions reference

ADF EL expressions

Security:

Checking if the user has the role assigned
#{securityContext.userInRole['ORDERS-EDIT-ROLE']}

Styling:

Conditionally setting in-line styles.
Eg: Changing background of the tree table rows based on a condition.

#{node.level ==1 ? 'background-color:#FFAD52' :  ( node.level == 2 ?  'background-color:#3173C4;color:white;' : 'background-color:#A5C6FF;'  )  }

Text Values:

EL expressions used along with text values
text="Score Card: (#{bindings.ScoreValue.inputValue})"

Passing Arguments in EL expression

Eg: this function get differrent values for outputtext based on the passed input values.

value="#{backingBeanScope.BackingBean.getDateLabel(pageFlowScope.pbb.startDate,pageFlowScope.pbb.endDate)}"

public String getDateLabel(Object from, Object to) {
        if (from == null && to != null) {
            return "TO:";
        } else if (from != null && to == null) {
            return "FROM:";
        }
        return null;
    }

Setting Bind Variables in ADF

In this post we will see some of the ways to programmatically set bind variables in ADF ViewObject.

Setting Value to Bind Variable

The following code sets the value for the the bind variable defined in the whereClause of the ViewObject.

ViewObject viewobj = am.findViewObject(“OrdersVO”);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”, 1001);
viewobj.executeQuery();

Bind Variable in View Criteria

But most often we use ViewCriterias to define the whereClause of the viewObject so that the ViewObject definition could be reused in multiple screens. The following code sets the value for the bind variable defined in the whereClause.

ViewObject vo = getOrdersVO();
ViewCriteria vc = vo.getViewCriteriaManager().getViewCriteria("OrdersViewCriteria");
vc.resetCriteria();
vo.ensureVariableManager().setVariableValue(“BndOrderNumber”, order_number );
vo.applyViewCriteria(vc);
vo.executeQuery();

Dynamic Bind Variable

In some scenarios we would have to set whereClause dynamically. In this case we could create a bind variable on the viewObject and then set its value.

viewobj.setWhereClause(“order_no = :BndOrderNumber”);
viewobj.defineNamedWhereClauseParam(“BndOrderNumber”,null ,null);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”,234)
viewobj.executeQuery();

SQL Notes and Tips

SQL Tips and Notes

Some SQL queries and useful sql statements for ADF/SOA Developers or at least I use them so often.

Database size

Find out table sizes

select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables order by 2 desc;

Gather Schema status

EXEC DBMS_STATS.gather_schema_stats('DEV_SOAINFRA');

Find out the schema sizes

SELECT *

FROM

( SELECT

   c.tablespace_name,

   ROUND(a.bytes/1048576,2)                    MB_Allocated,

   ROUND(b.bytes/1048576,2)                    MB_Free,

   ROUND((a.bytes-b.bytes)/1048576,2)          MB_Used,

   ROUND(b.bytes/a.bytes * 100,2)              tot_Pct_Free,

   ROUND((a.bytes-b.bytes)/a.bytes,2) * 100    tot_Pct_Used

 FROM

   ( SELECT

       tablespace_name,

       SUM(a.bytes) bytes

     FROM

       sys.DBA_DATA_FILES a

     GROUP BY

       tablespace_name

   ) a,

   ( SELECT

       a.tablespace_name,

       NVL(SUM(b.bytes),0) bytes

     FROM

       sys.DBA_DATA_FILES a,

       sys.DBA_FREE_SPACE b

     WHERE

       a.tablespace_name = b.tablespace_name (+)

       AND a.file_id = b.file_id (+)

     GROUP BY

       a.tablespace_name

   ) b,

   sys.DBA_TABLESPACES c

 WHERE

   a.tablespace_name = b.tablespace_name(+)

   AND a.tablespace_name = c.tablespace_name

 )

WHERE

 tot_Pct_Used >=0

ORDER BY

 tablespace_name;

Database Back and Restore

Cold Backup

Find out files:

select name from sys.v_$datafile;

select member from sys.v_$logfile;

select name from sys.v_$controlfile;

Shutdown the Database

alter system checkpoint;

shutdown abort

startup restrict

shutdown immediate

Change DB password

select username, account_status from dba_users;

alter user dev_soainfra identified by welcome1;

alter user test2_soainfra account unlock;

ALTER PROFILE DEFAULT LIMIT

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED;

change password for all expired users

set hea off

select 'alter user '||username||' identified by welcome1;' from dba_users where account_status like 'EXPIRED%';

select 'alter user '||username||' account unlock;' from dba_users where account_status like 'LOCK%';

-- To find out the profiles

select profile, username from dba_users;

-- To find out LOGIN ATTEMPT LIMIT for the profiles

select LIMIT, RESOURCE_NAME from dba_profiles

where PROFILE = (select profile

                 from dba_users

                where username = 'DEV_SOAINFRA');

Logging in SQL

drop table test_log;

create table test_log (a number(10), b varchar2(4000));

create sequence test_seq;

create or replace PROCEDURE test_logger(p_text IN varchar2)

as

PRAGMA AUTONOMOUS_TRANSACTION;

begin

 insert into test_log values (test_seq.nextval, p_text);

 commit;

END;

/

CALL TEST_LOGGER('HELLO');

SELECT * FROM TEST_LOG ORDER BY A DESC;

TRUNCATE TABLE TEST_LOG;

Redo Log generation rate

SELECT TRUNC(NEXT_TIME,'HH24'),ROUND(SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024),2) REDO_GB

FROM GV$ARCHIVED_LOG

where next_time > sysdate -4

GROUP BY TRUNC(NEXT_TIME,'HH24')

ORDER BY TRUNC(NEXT_TIME,'HH24');

Creating procedure objects to use with SOA

CREATE TYPE security_challenge_obj

AS

 OBJECT

 (user_name varchar2(254),

  max_val_attempts number(3),

  val_attempts_period varchar2(3),

  activity_type varchar2(30));   

 /

set serveroutput on;

DECLARE

 P_SECURITY_CHALLENGE AR_SOA.SECURITY_CHALLENGE_OBJ;

 P_PERMIT_VALIDATION VARCHAR2(200);

 P_STATUS VARCHAR2(200);

 P_ERROR_MESSAGE VARCHAR2(200);

 p_count number;

BEGIN

 -- Modify the code to initialize the variable

 p_security_challenge := ar_soa.SECURITY_CHALLENGE_OBJ(null,null,null,null);

 P_SECURITY_CHALLENGE.user_name := 'helasdfasdfasdfasdfasdfasdfasdfasdflo';

 P_SECURITY_CHALLENGE.max_val_attempts := '3';

 P_SECURITY_CHALLENGE.val_attempts_period := '24';

 P_SECURITY_CHALLENGE.activity_type := 'accounter';

  USER_VALIDATION_PKG.CHECK_VALIDATION_ATTEMPTS(

   P_SECURITY_CHALLENGE => P_SECURITY_CHALLENGE,

   P_PERMIT_VALIDATION => P_PERMIT_VALIDATION,

   p_validation_count => p_count

  );

  --:P_PERMIT_VALIDATION := P_PERMIT_VALIDATION;

DBMS_OUTPUT.PUT_LINE('P_STATUS = ' || P_STATUS);

  --:P_STATUS := P_STATUS;

DBMS_OUTPUT.PUT_LINE('P_ERROR_MESSAGE = ' || P_ERROR_MESSAGE);

DBMS_OUTPUT.PUT_LINE('P_count = ' || p_count);

DBMS_OUTPUT.PUT_LINE('P_PERMIT_VALIDATION  = ' || P_PERMIT_VALIDATION);

--  :P_ERROR_MESSAGE := P_ERROR_MESSAGE;

END;

/

Find and Kill Table Locks

SELECT s.username dbuser,owner || '.' || object_name "Object",

s.osuser,

s.PROGRAM,

lk.sid,

lk.type lock_type,

decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,

decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode

FROM v$lock lk,

v$session s,

dba_objects ao

WHERE lk.lmode > 1

AND object_name LIKE 'USER%'

AND s.username IS NOT NULL

AND lk.sid = s.sid

AND ao.object_id(+) = lk.id1;

select session_id from dba_dml_locks where name = 'USER_REMINDER_STATUS' ;

select sid, serial# from v$session where sid in ( 9);

alter system kill session '9,10679';

Monitor JDBC connections

select

   inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI'),

   count(*) COUNT

from gv$session

where username IN ('PERF_SOAINFRA','AR_SOA_RW','PERF_MDS')

group by inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI')

order by inst_id,

   machine,

   program,

   username,

   osuser,

   failed_over,

   to_char(logon_time,'DD/MM/YYYY HH:MI')

  INST_ID MACHINE                   PROGRAM                        USERNAME        OSUSER          FAI  COUNT

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

        1 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        1 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        2 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2

        2 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

         1 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

        1 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

        2 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      28

        2 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29

Sleep Function using Java

CREATE OR REPLACE PROCEDURE Sleep_fn(sec1 NUMBER)

AS LANGUAGE JAVA

NAME 'java.lang.Thread.sleep(long)';

/

Find Database processes limit

select resource_name, current_utilization, max_utilization, limit_value

   from v$resource_limit

   where resource_name in ('sessions', 'processes');

Purge AQ table

DECLARE

 po_t dbms_aqadm.aq$_purge_options_t;

BEGIN

  dbms_aqadm.purge_queue_table('XXCUST_TABLE', NULL, po_t);

END;

/