Friday May 31, 2013

How to Query Fusion Middleware Metadata Schemas Via JDBC

Introduction

You are dealing with a Fusion Middleware product, such as SOA, which uses Metadata Schemas, typically created in an Oracle Database via the Repository Creation Utility (RCU). As a Middleware administrator you would like to run a script which queries the schemas. However, the database resides on a different host from the Middleware Home, and there is no SQL*Plus or similar tool available. 

The solution? Although Fusion Middleware products do not ship SQL*Plus, Weblogic Server does include JDBC drivers. This presents an opportunity to try out / brush up your java program coding skills.

Example Solution

Having recently dabbled in WLST to query Weblogic Domain MBeans - Get an Offline or Online WebLogic Domain Summary Using WLST! - I have written an example java program to tackle the problem outlined above. The program utilizes the standard Oracle JDBC driver to:

  • establish a connection to an Oracle Database (which can be remote from the machine hosting Weblogic)
  • attempts three queries listed below
  • renders the output in HTML. (I have reused the html / javascript techniques I employed for the WLST Domain Summarizer to present the results in a nice readable format.)

Query ALL_USERS for owners / "prefixes" of Fusion Middleware schemas.

SELECT USERNAME, CREATED
FROM ALL_USERS
WHERE USERNAME LIKE  '%_MDS'
OR USERNAME LIKE '%_IAU'
OR USERNAME LIKE '%_IAU_%'
OR USERNAME LIKE '%_WLS'
OR USERNAME LIKE '%_STB'
OR USERNAME LIKE '%_UMS'
OR USERNAME LIKE '%_UCSCC'
OR USERNAME LIKE '%_OPSS' 
OR USERNAME LIKE '%_SOAINFRA'
ORDER BY 2

Schema Version Registry Summary

SELECT COMP_ID, COMP_NAME, VERSION, STATUS, UPGRADED
FROM
SCHEMA_VERSION_REGISTRY
ORDER BY 1,2

SOAINFRA - Cube Instance Summary

SELECT COMPOSITE_NAME, COMPONENT_NAME, COMPONENTTYPE,
(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 FAULTED'
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 STATE || ''  END) AS STATE,
COUNT(*) AS NUM_OF_CUBE_INST
FROM " + v_dbuser_soainfra + ".CUBE_INSTANCE
GROUP BY COMPOSITE_NAME, COMPONENT_NAME, COMPONENTTYPE,STATE
ORDER BY COUNT(*) DESC

Download

You can grab the zip package containing  the class, java source and example output by clicking here.

Instructions

1. After download, extract the zip.

2. cd to the directory labelled "class"

3. Edit the LaunchMetadataCollector.sh (Unix) or LaunchMetadataCollector.cmd (Windows) and change the environment variable setting - WL_HOME - to point at your Weblogic Server home.

4. Then run the LaunchMetadataCollector wrapper script. This will prompt for database connection details e.g.

>Enter the database hostname or ip address: mydbhost.acme.com
>Enter the database port number: 1521
>Enter the database sid: orcl
>Enter the database username: system
>Enter the password: 
>Enter the password again: 
[Optional] Enter the database user which owns the SOAINFRA schema: DEV

5. If the program runs successfully you should see the result of the queries in an output file labelled MetadataCollectorOutput.html

If you want to amend / adapt / enhance the program feel free to take a look at the java source file which is located in the "source" directory.

About

This is the blog of the Oracle Fusion Middleware Proactive Support Delivery Team. Here we will provide information about our activities, publications, product related information and more. Feedback welcome.

Follow OracleMWSupport on Twitter

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today