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,)

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2
libstdc++-devel
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11
unixODBC-devel-2.2.11
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′
SELECT e.*
FROM emp e,
dept d
WHERE e.deptno = d.deptno AND
d.deptno = :l_deptno AND
e.sal > :l_sal
END OF STMT
PARSE #1:c=0,e=1644,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=4528731877382
BINDS #1:
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:

NAME VALUE_STRING SQL_TEXT
———– —————– ———————————————————-
: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 10.2.0.1 to 10.2.0.3. 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
./runInstaller

From $ORA_CRS_HOME/install directory
./rootupgrade

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

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!