Taking IT to the Limits: Tips for Advanced Uses of Oracle Enterprise Manager 11g

A new Oracle white paper explores advanced uses of Oracle Enterprise Manager 11g, offering creative approaches to IT management as well as step-by-step guides for specific issues—from architecture setup to database patching.

The new white paper is culled from a forthcoming book, Oracle Enterprise Manager Grid Control—Advanced Techniques for the Real World, by Porus Homi Havewala and scheduled to be published by Rampant Books in the fall of 2010.

“My idea was to sum up key tips for setting up and working with Oracle Enterprise Manager 11g Grid Control, based on my own, hands-on experience,” says Havewala.

Topics include:

* Architecting scalability. Based on the author’s experience working with a major telecom company, he offers tips for architecture planning, use of multiple servers, and effective load balancing. When done right, says Havewala, “it is possible to manage, with ease, hundreds or even thousands of Oracle Enterprise Manager Grid Control targets.”

* Strategies for optimizing backups.. Gain insight into various strategies for setting up and scheduling Oracle Recovery Manager backups via Oracle Enterprise Manager Grid Control, instead of the older, more time-consuming manual method of Unix shell scripting and “cron” jobs. Havewala describes how to set up both incremental and full weekly backups, including using snapshots on NetApp Filers.

* Enhancing security. The introduction of target groups in Oracle Enterprise Manager makes it easier to manage security, explains Havewala, because “when disparate database teams login to the Oracle Enterprise Manager Grid Control console, they have access only to their own target groups.”

* Patching databases. Oracle Provisioning and Patch Automation Pack, “one of Oracle’s biggest advantages over third-party database management tools,” according to Havewala, includes increasingly sophisticated features such as multiple patch application, patch flow customization, and pluggable authentication modules support.

* Using Oracle Active Data Guard. Learn how DBAs can use Oracle Enterprise Manager Grid Control to easily set up, manage, and convert standby databases to Oracle Active Data Guard configurations.

Download the white paper Advanced Uses of Oracle Enterprise Manager now.

Configuring SSL for Oracle APEX

Platform:  LINUX

All steps are done with ORACLE_HOME set to the middle tier home (Apache, Companion CD home)

1. You need to use Oracle Wallet Manager (OWM) to create a wallet and get it set up.
— a. set up ORACLE_HOME, etc.
— b. go to $ORACLE_HOME/bin
— c. run Oracle Wallet Manager ./owm
— d. create a new wallet and save in a new directory (e.g. /home/oracle/myWallet/)
— e. create a certificate request
— — i. the common name is the name of the url, for example http://www.concept2completion.net
— — ii. Organizational Unit and Organization are just text associated with your company
— — iii. You should spell out the state
— f. export the request to a text file
— g. You will need to go to a certificate authority (CA) to get a certificate and paste in the contents of the text file created in (f). I have had problems with both goDaddy and Verisign. I have had good luck with entrust.com and thawte.com
— h. If you get a trial certificate you will need to get the trial Root Certificate (aka trusted certificate) from the CA. Save it as a text file. Install that into OWM as a trusted cert.
— i. Now install your cert (from g) in OWM and save.
2. Configure your ssl.conf file, located in $ORACLE_HOME/Apache/Apache/conf
— a. You can use the default listen and port settings (probably 4443 or 4447) or you can change to 443. If you change to 443, you need to change all occurences. Also, the apachectl file will need to be owned by root (located in $ORACLE_HOME/Apache/Apache/bin/). Same requirement as running on Port 80.
—- 1. Setting up Apache to run on ports below 1024
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
— b. Besides the port change, you need to change the location of your wallet and give your wallet password. You can encrypt the password, but I’m not covering that here and now.
SSLWallet file:/home/oracle/myWallet/
SSLWalletPassword mySuperPW1
— c. Save ssl.conf
3. Here is the trick. You need to configure OPMN to run in SSL mode. Edit the file opmn.xml (located in $ORACLE_HOME/opmn/conf/)
— a. under start mode, look for ssl-disabled
— — change to
— — ssl-enabled
— b. Save opmn.xml
4. go to $ORACLE_HOME/opmn/bin and restart
— a. opmnctl stopall
— b. opmnctl startall

OK, now you are running in SSL. You might want to run everything in SSL, or just some things. To do this, you can set an Apache Rewrite Rule. Here is an example that will rewrite everything that is in pls/apex to https (assumes you are running on port 443). Edit your httpd.conf file, add the lines below at the end of the file, opmnctl stopall, opmnctl startall:
RewriteEngine On
RewriteCond %{SERVER_PORT} !^443$
RewriteRule ^/pls/apex/(.*)$ https://concept2completion.net/pls/apex/$1 [L,R]

Hope this captures all of the steps. Let me know if this helps.

Run Weblogic in Production mode

I always go with the default installation of Weblogic and always get the Sun JVM in Development mode. If you have installed Weblogic in Development mode but want to run it in the Production mode, its very simple. Start the weblogic as follows:

$./startWebLogic.sh -Dweblogic.ProductionModeEnabled=true

The key-value pair “weblogic.ProductionModeEnabled=true” will start the weblogic server in Production mode. The key difference between Development mode and Production mode (by default, without any JDK installations specified in the installation) is that the Development mode runs with Sun JVM and Production mode runs with JRockit JVM.

JRockit is enhanced JVM and enhanced for its performance. Its one of the best and fastest JVM until now (as claimed by WL aka Oracle). If you install JRockit Mission Control, then you can measure the performance of your Weblogic server (JRockit also supports Sun JVM and other JVMs).

If you want to start Weblogic in the background and want to get the stdoutput in a log start it as follows:

$ nohup ./startWebLogic.sh -Dweblogic.ProductionModeEnabled=true &

Oracle Database 11g Release 2 RPM requirements

Linux 64-bit Requirements for Red Hat 5 for Oracle Database 11g Release 2
(Also includes Asianux Server 3, Oracle Enterprise Linux 5,)

compat-libstdc++-33-3.2.3 (32 bit)
glibc-2.5-24 (32 bit)
glibc-devel-2.5 (32 bit)
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2 (32 bit)
unixODBC-devel-2.2.11 (32 bit)

Oracle Recommended Patches

Excerpt from Introduction to Oracle Recommended Patches [ID 756388.1]
What are Recommended Patches?

Oracle has introduced a set of Recommended Patches which make it easier for customers to obtain and deploy fixes for known critical issues encountered in targeted environments and configurations. As part of Oracle’s overall maintenance strategy, these provide proactive patch recommendations to customers seeking to upgrade or to improve the stability of their current environments. Customers are advised to install Recommended Patches that apply to their environment.

Recommended Patches are available for products listed below. For details, please review the My Oracle Support notes.

Product My Oracle Support Note
Oracle Database Note:756671.1
Oracle Enterprise Manager Note:822485.1
Oracle Fusion Middleware Note:859115.1

This note will be updated as Oracle announces Recommended Patches for other Oracle products.

Posted by Charles Kim
Oracle ACE Director

Finding the Bind Values in a Query

Extended SQL trace is one of the oldest and most complete methods for capturing the values of bind variables used in SQL statements. You can enable extended SQL trace in your own session with the following statement:

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;

You can enable extended SQL trace in another user’s session using one of the following procedure calls:

SYS.dbms_system.set_ev (sid, serial#, 10046, 4, ”)
SYS.dbms_support.start_trace_in_session (sid, serial#, waits=>FALSE, binds=>TRUE)
SYS.dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE)

The dbms_monitor package is new in Oracle 10g. Any of these will cause Oracle to write a trace file on the database server in the directory specified by the user_dump_dest instance parameter. The trace file will contain text like:

PARSING IN CURSOR #1 len=116 dep=0 uid=77 oct=3 lid=77 tim=4528731877418 hv=3407
047714 ad=’b8cd9050′
FROM emp e,
dept d
WHERE e.deptno = d.deptno AND
d.deptno = :l_deptno AND
e.sal > :l_sal
PARSE #1:c=0,e=1644,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=4528731877382
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
size=48 offset=0 bfp=800003fb800697f0 bln=22 avl=02 flg=05 value=10
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
size=0 offset=24 bfp=800003fb80069808 bln=22 avl=02 flg=01 value=300
EXEC #1:c=0,e=810,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4528731879874
FETCH #1:c=10000,e=14607,p=3,cr=6,cu=0,mis=0,r=1,dep=0,og=4,tim=4528731894755
FETCH #1:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=4,tim=4528731898231

In the above example, “BINDS #1″ indicates values bound into the bind variables of the statement in cursor #1. (See the previous “PARSING IN CURSOR #1″ entry for the text of the statement in cursor #1.) Below “BINDS #1″, the “bind 0″ entry corresponds to the first bind variable in the statement i.e. :l_deptno. Similarly, “bind 1″ corresponds to the second bind variable, :l_sal. The last item on the “bind 0″ and “bind 1″ entries shows the value of the bind variable at the time of binding. If a statement is executed multiple times in a traced session, there will be additional “BINDS” entries as new values are bound.

Beginning in Oracle 10g, it is also possible to monitor bind variable values in your own session or other sessions witho
ut performing an extended SQL trace. A new v$ view called v$sql_bind_capture shows bind variable data across all sessions. There are a few important restrictions on v$sql_bind_capture: Only simple-type bind variables that appear in the WHERE a nd HAVING clauses are captured. (Thus it is not possible to obtain the values used in INSERT statements or SET clauses of UPDATE statements, or LOBs or LONGs.) Also, to reduce overhead, bind variable information is captured no more frequently than once every 15 minutes for a given cursor. Finally, this view is only populated when the statistics_level parameter is set to TYPICAL or ALL.

For example, we can retrieve bind variable information for all statements parsed by us with a query such as:

SELECT b.name, b.value_string, sq.sql_text
FROM v$sql_bind_capture b, v$sql sq, v$session s
WHERE sq.sql_id = b.sql_id
AND sq.address = b.address
AND sq.child_address = b.child_address
AND sq.parsing_user_id = s.user#
AND s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;

This might produce results such as:

———– —————– ———————————————————-
:L_DEPTNO 10 SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno…
:L_SAL 300 SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno…

Even though the data collected in v$sql_bind_capture is only sampled periodically, it can still help DBAs track down performance issues in code they are not familiar with. For example, since v$sql_bind_capture indicates the data type of thebind variables, it is possible to use the view to discover statements that have performance problems due to implicit type conversions. Recently we were asked by a development group why the following statement would not use an index on the image_no column:

SELECT * FROM images WHERE image_no = :B1;

By comparing the data type of the bind variable against the data type of the image_no column, it was straightforward to show a type conversion was taking place that was defeating the index. When the developer changed the bind variable datatype from numeric to character, the query began using the index.

Extended SQL trace allows you to see all bind variable values for all executions of SQL statements captured in the tracefile. Beginning in Oracle 10g, the v$sql_bind_capture view allows you to see sampled bind variable information for all sessions without having to use tracing. Both tools can be extremely useful to the Oracle DBA.

11g Clusterware Upgrade

The upgrade process to 11gR1 is the same process as upgrading from to The steps for the upgrade process is:

Shutdown CRS

The following steps are optional:

* Modify /etc/inittab and comment out last three lines
* init q

$ORA_CRS_HOME/bin/crsctl stop crs

Execute pre-update script from the unzipped software/clusterware/upgrade directory
./preupdate.sh -crshome $ORA_CRS_HOME -crsuser oracrs

Install new software binaries on all the RAC nodes

From $ORA_CRS_HOME/install directory

Check upgrade status by querying activeversion of the CRS
crsctl query crs activeversion