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.

Monday May 13, 2013

My Take on Getting Started With WebLogic Scripting Tool (WLST)

Introduction

WebLogic Scripting Tool (WLST) has been a long established favourite for experienced WebLogic Server administrators. Over the past two / three weeks I have spent time getting to know WLST - and by getting to know I mean developing a script to produce a solution rather than randomly running three or four commands :-). For the final result of my efforts, see my previous blog entry

Get an Offline or Online WebLogic Domain Summary Using WLST! 

This is a short summary of the resources I used. 

Initial reading and understanding

If you are never encountered WLST, spend a few minutes reading:

Oracle Fusion Middleware Administrator's Guide 11g Release 1 (11.1.1)
3.5.1 Getting Started Using the Oracle WebLogic Scripting Tool (WLST)

Oracle Fusion Middleware WebLogic Scripting Tool Command Reference 11g
Overview of WLST Command Categories

Unsurprisingly, you will also find yourself using the Command Reference as a look up for available commands, functionality and syntax.

WLST Code Snippets and Examples

(There are many more WLST postings (questions, examples, discussions) to be found on the web.)

Jython

WLST is based on the Java scripting interpreter, Jython and as such supports standard Jython features such as local variables, conditional variables, and flow-control statements. Therefore, to achieve something more interesting and exciting you must prepared to learn (by doing) a little Jython. Fortunately, the Jython documentation is comprehensive and provides some good examples:

Use an editor which recognizes Jython / Python

Jython (like it's "parent" language Python) is very sensitive to syntax errors. In particular, the language requires the use of indents when coding conditions (e.g IF statements) and loops. Attempt to write Jython using a plain text editor, and before long stress levels will rise. You can make life easier by using an editor which recognizes Jython. Editors like Oracle Eclipse ** and SCITE assist the developer by for example:

  • auto-formatting lines of code
  • color coding of syntax
  • providing option to show line numbers

** Eclipse requires the Python and Jython Interpreters to be configured. Instructions can be found here.

My Oracle Support Documents

And finally, if you have access to My Oracle Support, here are a few knowledge articles which provide some nice WLST examples:

  • How To Debug WLST Jython Scripts? [ID 1360744.1]
  • WLST sample script To Obtain Managed Server Status [ID 1373532.1]
  • WLST Script for monitoring and getting statistics of WebLogic JMS Destinations. [ID 1327086.1]
  • How to get JDBC and Connection Pool data from a WLST or shell script [ID 1453818.1]
  • How to Startup or Shutdown Multiple WebLogic Managed Servers With a WLST Script [ID 1383150.1]
  • How To Take Thread Dumps With WLST [ID 1274713.1]

Tuesday May 07, 2013

Get an Offline or Online WebLogic Domain Summary Using WLST!

Introduction

Further to blog entry "WLST Offline Script to Create Summary of WebLogic Domain", I have created a solution which extends the capability to collecting an online summary if a connection to the domain's Admin Server can be established.

Search the web, and you will find many WLST examples which summarize managed server status and performance, JMS, JDBC and more. In this respect, my efforts are nothing new. However, I thought I would share as my scripts have the following "bells and whistles".

1. If you are unable to connect to the domain's Admin Server, an offline summary of the domain will automatically be obtained

2. The script renders the collected data in HTML, using some javascript to give the final result some interactive formatting for ease of reading. See screenshot snippet below:

Screenshot : Snippet of WLST Summarizer HTML Output

Screenshot showing WLST Summarizer HTML output

3. If a managed server has a "FAILED" health state or there is 1 or more thread hogs in its JVM thread pool, three thread dumps will be captured (15 seconds apart).

Download Sample Script

You can grab the sample script by clicking here.

Instructions to Run:

1. After download, extract the zip to the machine hosting the WebLogic environment. You should have three directories along with a readme.txt

  • output
  • Sample_Output
  • scripts

2. In the scripts directory, find the start wrapper script startWLSTDomainSummarizer.sh (Unix) or startWLSTDomainSummarizer.cmd (MS Windows). Open the appropriate file in an editor and change the environment variable settings to suit your system.

Example - startWLSTDomainSummarizer.cmd

set WL_HOME=D:\product\FMW11g\wlserver_10.3
set DOMAIN_HOME=D:\product\FMW11g\user_projects\domains\MyDomain
set WLST_OUTPUT_PATH=D:\WLSTDomainSummarizer\output\
set WLST_OUTPUT_FILE=WLST_Domain_Summary_Via_MBeans.html

call "%WL_HOME%\common\bin\wlst.cmd" StartDomainSummarizer.py

Note: The WLST_OUTPUT_PATH directory value must have a trailing slash. If there is no trailing slash, the script will error and not continue. 

3. Run the shell / command line wrapper script. It should launch WLST and kick off "StartDomainSummarizer.py". This initial python script will hit you with some prompts e.g.

Is your domain Admin Server up and running and do you have the connection details? (Y /N ):
Y
Enter connection URL to Admin Server e.g t3://mymachine.acme.com:7001 :
t3://localhost:7001
Enter weblogic username:
weblogic
Enter weblogic username password (function prompt 1):
welcome1

(Note: the value typed in for password will not be echoed back to the console).

Depending on your responses it will call "WLSDomainInfoOffline.py" or "WLSDomainInfoOnline.py". 

4. If the scripts run successfully, you should get a HTML summary in the specified output directory. 

5. For the HTML to render correctly, ensure the .js and .css files provided (review the output directory created by the zip file extraction) are accessible. For example, to view the HTML locally (without using a web server), place the HTML output, jquery-ui.js, spry.js and wlstsummarizer.css in the same directory.

Disclaimer

This is a sample script. I have tested it against WebLogic Server 10.3.6 domains on MS Windows and Unix.  I cannot guarantee that the script will run error free or produce the expected output on your system. If you have any feedback add a comment to the blog. I will endeavour to fix any problems with my WLST code.

Credits

JQuery: http://jquery.com/
Spry (Adobe) : https://github.com/adobe/Spry
http://www.red-team-design.com/cool-headings-with-pseudo-elements

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
« May 2013 »
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
8
9
10
11
12
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
       
Today