Wednesday, December 28, 2011

Oracle datafiles and Veritas Quick I/O files

If you want to have a maximum performance in datafile access you should use raw devices for datafiles, but most modern operating systems manage file access very well, you can use ASM for Oracle database storage management, and managing raw devices requires a skilled sysadmin and is time-consuming, therefore almost all the time the extra speed gained with raw devices is not worth the extra skills and effort required to maintain raw devices.

But if you have an Oracle database in a Solaris server with Veritas VxFS filesystems with Quick I/O included (that is, licensed), then you can use Quick I/O access with regular files; this way you can have and manage regular files but at the same time you can access them like raw devices. An unprivileged user can create Quick I/O links and is very easy to do; first you have to create your datafile at operating system level with qiomkfile:

oracle@myserver$ qiomkfile -h 32k -s 16000M /myfs/mydatabase/mydatafile.dbf

oracle@myserver$ ls -la /myfs/mydatabase
total 32768066
drwxr-xr-x 3 oracle dba 96 Sep 24 11:32 .
drwxr-xr-x 61 oracle dba 1024 Sep 23 16:57 ..
-rw-r--r-- 1 oracle dba 16777248768 Sep 24 11:32 .mydatafile.dbf
lrwxrwxrwx 1 oracle dba 26 Sep 24 11:32 mydatafile.dbf -> .mydatafile.dbf::cdev:vxfs:
drwxr-xr-x 2 oracle dba 96 Sep 23 16:50 lost+found

The secret is that .mydatafile.dbf is a regular (but single contiguous extent) file, and
mydatafile.dbf is a link to .mydatafile.dbf::cdev:vxfs: but the operating system recognizes the ::cdev:vxfs: suffix as Quick I/O and access .mydatafile.dbf like a raw device; that's why the .mydatafile.dbf::cdev:vxfs: file must not exist. The qiomkfile parameters are simple; -h is the extra space added to the file to use it as an Oracle datafile (in this case 16,000 megabytes plus 32 kilobytes) because Oracle adds one database block (DB_BLOCK_SIZE parameter) to each datafile created, and -s is the required size of the Oracle datafile that should match the DATAFILE ... SIZE clause.


This was the difficult part; in order to create datafiles that use Quick I/O features you just have to use the REUSE clause:

SQL> CREATE TABLESPACE MYTBL DATAFILE '/myfs/mydatabase/mydatafile.dbf' SIZE 16000M REUSE;

Tablespace created.

SQL> ALTER TABLESPACE MYTBL ADD DATAFILE '/myfs/mydatabase/mydatafile2.dbf' SIZE 16000M REUSE;

Tablespace altered.

And talking about dropping tablespaces with Quick I/O datafiles, don't use the INCLUDING CONTENTS AND DATAFILES clause or you will get an error, just drop the tablespace and erase the files with rm at operating system level.

More information:

Veritas Storage Foundation for Oracle Administrator’s Guide (chapter 4)
Veritas Storage Foundation 5.0 Software

Tuesday, December 20, 2011

About Oracle OCA and OCP certifications

Some time ago I got first the Oracle Certified Associate certification and later the Oracle Certified Professional certification, both on Oracle Database 10g. Since then there has been some people asking me about the procedure I followed and also the study material I used to prepare this tests, and it's until today I decided to write about it.

First of all, the tests are multiple-choice question exams that are difficult enought to not let to pass anybody that can pay the exam fees (I think low by the way), and not being a native English speaker adds a lot more complexity to the tests. If you have experience managing Oracle databases AND have a good and fresh knowledge of Oracle Database concepts you will have a good chance to pass the tests; in this kind of tests it is very important to have rock-solid concepts knowledge because all answers looks equal and the subtle differences are the key to choose the correct answer, not to mention the limited amount of time to answer the whole test. Oh, did I stress having good English skills?

On the other hand, if you're on budget you will find that a thorough study of the official (and free) Oracle documentation may be enough to pass this tests, and even the Express Edition (free) is enough to practice in your own computer. But if you can spare some money, I highly recommend to buy the SelfTest's Oracle Database 11g: SQL Fundamentals I, Oracle Database 10g: Administration I and Oracle Database 10g: Administration II practice tests; the price of this software is close to the price of each certification test but it might save you frustration and time, not to mention money, therefore is up to you to prepare for this tests just with free documentation (or expensive courses) or pay the extra money to practice tests with this software. If you buy this software be sure to score at least 90% a few times and you will have a good opportunity to pass the real tests.

But if you're a cheapskate like me you would prefer to study the official Oracle documentation. To be honest, at this time I can't remember exactly what I had to study for each certification test, but if you are willing to hold the OCP certification simply study all the following guides and you will be safe.

For the SQL test (Oracle Database 11g: SQL Fundamentals I, 1Z0-051):

Oracle Database 2 Day DBA
Oracle Database Express Edition 2 Day Developer Guide

For the OCA certification (Oracle Database 10g: Administration I, 1Z0-042):

Oracle Database Administrator's Guide (chapters 1 to 20)
Oracle Database Backup and Recovery Basics

For the OCP certification (Oracle Database 10g: Administration II, 1Z0-043):

Oracle Database Administrator's Guide (chapters 21 to 28)
Oracle Database Backup and Recovery Advanced User's Guide

The problem of studying Oracle documentation is that it is not intended to be a self-learning guide, therefore you have to figure out exercises to practice what you read; just reading this documentation without practice nor experience will not be enough to pass the tests. But in my personal experience I think these guides are so well written that if you manage to learn them you will not have any need to buy (or read) other books; in fact I cannot recommend books because I didn't read anything besides the Oracle guides to prepare for the certification tests.

Another problem is that these guides are good for preparing the OCP and OCA tests, but for the SQL Fundamentals test I couldn't find one Oracle guide that covered everything; in fact I was very close to fail that test but the SelfTest software saved me. On the other hand, the OCA test was difficult to me but it was less than the SQL test, and the OCP test was the easiest one to me. Therefore, if you're more a DBA than a developer it would be better to study SQL sentences and functions a lot.

Now let's talk about the certification track and prerequisites. If you're pursuing the OCP or OCA certifications you first have to pass one of the SQL tests, more likely the Oracle Database 11g: SQL Fundamentals I. You can take the OCA or OCP tests first but you won't get any certification unless you clear all the certification prerequisites, therefore first try to pass the SQL test.

If you manage to pass the SQL test then you can take the Oracle Database 10g: Administration I test, and if you pass you will hold the OCA certification. Easy, isn't it?

But this is just the beginning, the goal should be the OCP certification but there are two prerequisites not so easy to cover: take an approved course and fill the Course Submission Form. The difficult part of taking an approved course is just paying for it, and if your course diploma has a course serial number and you took that course recently you will have no problem at all filling the Course Submission Form.

At of this writing, the submission of the form and the scheduling of tests should be done at the Pearson Vue site, and unfortunately I cannot remember what I have done exactly but you would get help at the moment of contacting a testing center.

Finally, if you cleared all the prerequisites and manage to pass the Oracle Database 10g: Administration II test, you will become an Oracle Certified Professional and get a nice plastic card just in case you are willing to boast about it.

There are people that put in question the value of this kind of certifications, but the truth is that I learned a lot studying for the certification tests, and because these certifications are valid worldwide they are a good point of reference for recruiters looking for talent. Therefore, if you have already took an Oracle approved course then put a little effort and money and go for the OCA and OCP certifications, I think they have good value for the money.

Friday, December 9, 2011

Patching an Oracle RAC database

Patching an Oracle RAC database is a little different from patching a single instance database because you have to patch at least two installations (CRS, database and ASM if exist), patch it in certain order, and shutdown and startup CRS services not existing in single instance installations; you can even apply patches with or without shutting down the entire RAC database, and apply them in each node or propagate patches to all nodes executing patches in just one node.

In this example we will patch a two-node Oracle 10g RAC database (10.2.0.1) with ASM, sharing the database and ASM the same home, first to upgrade it to patch 10.2.0.5.0 and finally to PSU 10.2.0.5.4, shutting down the whole RAC database and applying the patches to both nodes from one of them. Usual recommendations apply: read patch's documentation and backup your database and Oracle home before patching.

First, we will download patches 8202632 (patch 10.2.0.5.0), 12419392 (PSU 10.2.0.5.4), and 6880880 (Opatch 10.2.0.5.1), and uncompress it in one node at least:

oracle@myracn1$ ls -la
total 2659400
drwxr-xr-x 4 oracle dba 512 Nov 17 18:20 .
drwxr-xr-x 23 oracle dba 1024 Nov 17 13:03 ..
drwxr-xr-x 5 oracle dba 512 Jun 16 00:42 12419392
drwxr-xr-x 6 oracle dba 512 Nov 17 15:16 Disk1
-rw-r--r-- 1 oracle dba 11319286 Nov 15 21:33 p12419392_10205_SOLARIS64.zip
-rw-r--r-- 1 oracle dba 28674793 Nov 17 18:17 p6880880_102000_SOLARIS64.zip
-rw-r--r-- 1 oracle dba 1320728471 Nov 15 21:36 p8202632_10205_SOLARIS64.zip
-rwxr-xr-x 1 oracle dba 175026 May 19 2010 README.html

Next, shutdown all RAC instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

Next we will launch OUI to patch first the CRS software. It's very easy to follow the instructions of Oracle Universal Installer, you just have to select the CRS home when apply and be sure to select all the RAC nodes to install this patch; everything else is pretty straightforward.

oracle@myracn1$ cd /opt/oracle/parches/Disk1

oracle@myracn1$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10
Passed

Checking Temp space: must be greater than 250 MB. Actual 13017 MB Passed
Checking swap space: must be greater than 500 MB. Actual 15218 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_01-45-53PM. Please wait ...
oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

As the last step of this patch application and before leaving OUI, we have to run the root102.sh script as root after stopping CRS services, in all RAC nodes:

oracle@myracn1$ su -
Password:

root@myracn1# /opt/oracle/crs/bin/crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

root@myracn1# /opt/oracle/crs/install/root102.sh
Creating pre-patch directory for saving pre-patch clusterware files
Completed patching clusterware files to /opt/oracle/crs
Relinking some shared libraries.
ar: writing /opt/oracle/crs/lib/libn10.a
ar: writing /opt/oracle/crs/lib32/libn10.a
ar: writing /opt/oracle/crs/lib/libn10.a
Relinking of patched files is complete.
WARNING: directory '/opt/oracle' is not owned by root
Preparing to recopy patched init and RC scripts.
Recopying init and RC scripts.
Startup will be queued to init within 30 seconds.
Starting up the CRS daemons.
Waiting for the patched CRS daemons to start.
This may take a while on some systems.
.
10205 patch successfully applied.
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully deleted 1 values from OCR.
Successfully deleted 1 keys from OCR.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: myracn1 myracn1-priv myracn1
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
Creating '/opt/oracle/crs/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /opt/oracle/crs/install/paramfile.crs

root@myracn1# exit

At this point we have the CRS software patched in all nodes, therefore we will patch the ASM and database homes next. In this example ASM and the database has the same Oracle home, but otherwise you have to patch first the ASM home and next the database home.

Just in case you didn't notice yet, the 10.2.0.5 patch is the same patch for CRS, ASM and databases, therefore we will apply the same patch almost the same way as before but this time we will select the ASM and database home, of course after shutting down all RAC database instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd -
/opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

oracle@myracn1$ cd -
/opt/oracle/parches/Disk1

oracle@myracn1$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10
Passed

Checking Temp space: must be greater than 250 MB. Actual 12967 MB Passed
Checking swap space: must be greater than 500 MB. Actual 15169 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_02-23-05PM. Please wait ...
oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

Like before, we have to run as root the root.sh script before exiting the Oracle Universal Installer, in all RAC nodes:

oracle@myracn1$ su -
Password:
root@myracn1# /opt/oracle/db/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/oracle/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /var/opt/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

root@myracn1# exit

At this point we have the 10.2.0.5 patch applied at software level so next step is to start up ASM and RAC services without database instances:

oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2

oracle@myracn1$ ps -fea|grep pmon
oracle 8812 6323 0 15:08:58 pts/1 0:00 grep pmon
oracle 5280 1 0 15:03:28 ? 0:01 asm_pmon_+ASM1

In order to update the database catalog, we will set the CLUSTER_DATABASE parameter as false to start up just one instance; you have to do this only in the instance you will work but the whole database catalog upgrade procedure should be done for every database in the RAC.

Just to be clear, in this example we are working with one ASM and one normal database (myrcdb) in a RAC cluster with two nodes; there is one instance of ASM running in each node (two ASM instances in total), and also one instance of the myrcdb database running in each node (two database instances in total).

Also, you don't have to run a catalog upgrade in an ASM instance, just in database instances.

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:09:17 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 385879712 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14721024 bytes
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;

System altered.

SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Before running the database catalog upgrade script we will start up the instance in upgrade mode, and as a good practice, run the upgrade information script to check if everything is fine to upgrade the catalog.

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:11:33 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 385879712 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14721024 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 11-17-2011 15:13:03
.
**********************************************************************
Database:
**********************************************************************
--> name: myrcdb
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 420 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 402 MB
.... AUTOEXTEND additional space required: 202 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 185 MB
.... AUTOEXTEND additional space required: 5 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Real Application Clusters [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
.

PL/SQL procedure successfully completed.

Read carefully the report of the Upgrade Information Utility script and make any changes suggested; you can just execute the catalog upgrade script but if you ignore suggestions of this report the upgrade might fail, and you don't want to end with a corrupted catalog, believe me.

After this we will execute the upgrade catalog script; remember, you have to execute this script for every database you have in your RAC cluster, but just in one instance per database:

SQL> spool patch.log
SQL> @?/rdbms/admin/catupgrd.sql

...

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-11-17 17:20:37

1 row selected.

.
Oracle Database 10.2 Upgrade Status Utility 11-17-2011 17:20:37
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.5.0 00:54:11
JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:15:23
Oracle XDK VALID 10.2.0.5.0 00:02:40
Oracle Database Java Packages VALID 10.2.0.5.0 00:01:21
Oracle XML Database VALID 10.2.0.5.0 00:12:22
Oracle Real Application Clusters VALID 10.2.0.5.0 00:00:06
Oracle Workspace Manager VALID 10.2.0.5.0 00:04:02
Oracle interMedia VALID 10.2.0.5.0 00:24:00
Oracle Expression Filter VALID 10.2.0.5.0 00:00:58
Oracle Rule Manager VALID 10.2.0.5.0 00:00:47
.
Total Upgrade Time: 02:04:12

PL/SQL procedure successfully completed.

DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> spool off

As stated in the final message of the catupgrd.sql script check that all the components are present, has a valid status and proper version, and just to be sure check the patch.log file for ORA error messages.

At this point we have almost finished applying the 10.2.0.5 patch, and we will recompile and look for invalid objects:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 17:23:06 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2052448 bytes
Variable Size 436211360 bytes
Database Buffers 1157627904 bytes
Redo Buffers 14721024 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-17 17:24:02

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.


...


DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

SQL> column COMP_NAME format a50
SQL> column VERSION format a15
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

COMP_NAME VERSION STATUS
-------------------------------------------------- --------------- -----------
Oracle interMedia 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Real Application Clusters 10.2.0.5.0 VALID

11 rows selected.

If everything looks fine, we just have to restore the CLUSTER_DATABASE parameter value to true and start the RAC database and services:

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2
oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2
oracle@myracn1$ srvctl start database -d myrcdb

oracle@myracn1$ cd -
/opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE myracn1
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE ONLINE myracn2
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE ONLINE myracn2
ora....l1.inst application ONLINE ONLINE myracn1
ora....l2.inst application ONLINE ONLINE myracn2

We finished applying the 10.2.0.5 patch in our Oracle RAC! Now we will apply the PSU 10.2.0.5.4 just to be completely up to date, but first a quick check to the inventory:

oracle@myracn1$ export PATH=$PATH:$ORACLE_HOME/OPatch

oracle@myracn1$ opatch lsinventory
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-04PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_17-48-04PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


Rac system comprising of multiple nodes
Local node = myracn1
Remote node = myracn2

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

OPatch succeeded.

Next, shutdown all RAC instances and services:

oracle@myracn1$ srvctl stop database -d myrcdb
oracle@myracn1$ srvctl stop asm -n myracn1
oracle@myracn1$ srvctl stop asm -n myracn2
oracle@myracn1$ srvctl stop listener -n myracn1
oracle@myracn1$ srvctl stop listener -n myracn2
oracle@myracn1$ srvctl stop nodeapps -n myracn1
oracle@myracn1$ srvctl stop nodeapps -n myracn2

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE OFFLINE
ora....T2.lsnr application OFFLINE OFFLINE
ora.myracn1.gsd application OFFLINE OFFLINE
ora.myracn1.ons application OFFLINE OFFLINE
ora.myracn1.vip application OFFLINE OFFLINE
ora....SM2.asm application ONLINE OFFLINE
ora....T4.lsnr application OFFLINE OFFLINE
ora.myracn2.gsd application OFFLINE OFFLINE
ora.myracn2.ons application OFFLINE OFFLINE
ora.myracn2.vip application OFFLINE OFFLINE
ora.myrcdb.db application ONLINE OFFLINE
ora....l1.inst application ONLINE OFFLINE
ora....l2.inst application ONLINE OFFLINE

Then, we try to apply the PSU but ...

oracle@myracn1$ cd /opt/oracle/parches
oracle@myracn1$ opatch napply 12419392 -all_nodes
Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-44PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
UtilSession failed:

Patch 12419392 requires OPatch version 10.2.0.5.0.
The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.


OPatch failed with error code 73

This is why we downloaded the 6880880 patch; in order to apply the PSU 10.2.0.5.4 we need an OPatch version 10.2.0.5.0 or above.

Anyway, it's very easy to upgrade OPatch, we just have to remove the original OPatch directory and unzip the new in the Oracle Home:

oracle@myracn1$ cd $ORACLE_HOME
oracle@myracn1$ rm -r OPatch
oracle@myracn1$ unzip /opt/oracle/parches/p6880880_102000_SOLARIS64.zip
Archive: /opt/oracle/parches/p6880880_102000_SOLARIS64.zip
creating: OPatch/
creating: OPatch/ocm/
extracting: OPatch/ocm/ocm.zip
creating: OPatch/ocm/lib/
inflating: OPatch/ocm/lib/osdt_jce.jar

...


inflating: OPatch/docs/Prereq_Users_Guide.txt
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/README.txt

oracle@myracn1$ ls -la OPatch/
total 120
drwxr-xr-x 7 oracle dba 512 Nov 17 18:20 .
drwxr-x--- 62 oracle dba 1536 Nov 17 18:20 ..
drwxr-xr-x 3 oracle dba 512 Nov 3 2010 crs
drwxr-xr-x 2 oracle dba 512 Nov 3 2010 docs
-rw-r--r-- 1 oracle dba 23695 Nov 3 2010 emdpatch.pl
drwxr-xr-x 2 oracle dba 512 Nov 3 2010 jlib
drwxr-xr-x 4 oracle dba 512 Nov 3 2010 ocm
-r-x--x--- 1 oracle dba 13252 Nov 3 2010 opatch
-rwxr-xr-x 1 oracle dba 8085 Nov 3 2010 opatch.bat
-rw-r--r-- 1 oracle dba 49 Nov 3 2010 opatch.ini
-rw-r--r-- 1 oracle dba 2576 Nov 3 2010 opatch.pl
drwxr-xr-x 4 oracle dba 512 Nov 3 2010 opatchprereqs
-rw-r--r-- 1 oracle dba 2417 Nov 3 2010 README.txt

With the OPatch upgraded now we are really ready to apply the PSU to all RAC nodes at the same time, and fortunately for this patch we need to answer just a few easy questions:

oracle@myracn1$ cd /opt/oracle/parches

oracle@myracn1$ opatch napply 12419392 -all_nodes
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-23-00PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking conflicts against Oracle Home...
OPatch continues with these patches: 12419392

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y

OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate the patch to the remote nodes.


This node is part of an Oracle Real Application Cluster.
Remote nodes: 'myracn2'
Local node: 'myracn1'
Please shut down Oracle instances running out of this ORACLE_HOME on all the nodes.
(Oracle Home = '/opt/oracle/db')


Are all the nodes ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
Execution of 'sh /opt/oracle/parches/12419392/custom/scripts/pre -apply 12419392 ':


Return Code = 0

Applying patch 12419392...

ApplySession applying interim patch '12419392' to OH '/opt/oracle/db'
ApplySession: Optional component(s) [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] not present in the Oracle Home or a higher version is found.
Backing up files affected by the patch '12419392' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/qecsel.o"


...


Updating jar file "/opt/oracle/db/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_dbObjectsList.class"
Copying file to "/opt/oracle/db/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"

Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/opt/oracle/db/lib/libxml10.a" with "lib/libxml10.a/lpxpar.o"
Updating archive file "/opt/oracle/db/lib32/libxml10.a" with "lib32/libxml10.a/lpxpar.o"

Patching component oracle.precomp.common, 10.2.0.5.0...

Patching component oracle.rdbms.rman, 10.2.0.5.0...
ApplySession adding interim patch '12419392' to inventory

Verifying the update...
Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12419392 are present in Oracle Home.
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman

The local system has been patched and can be restarted.


Patching in all-node mode.

Updating nodes 'myracn2'
Apply-related files are:
FP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt"
DP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt"
MP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt"
RC = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/remote_cmds.txt"

Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt" with actual path.
Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/plsql/lib; /usr/ccs/bin/make -f ins_plsql.mk iwrap ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/network/lib; /usr/ccs/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/precomp/lib; /usr/ccs/bin/make -f ins_precomp.mk proc ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

Running command on remote node 'myracn2':
cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk irman ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2

RC file not exist. There are no commands to be run on the remote nodes.

All nodes have been patched. You may start Oracle instances on the local system and nodes 'myracn2'

UtilSession: N-Apply done.

OPatch succeeded.

Applying a PSU is faster than applying a patch that needs OUI, but for this particular PSU we also need to upgrade the database catalog after starting up the RAC database and services:

oracle@myracn1$ srvctl start nodeapps -n myracn1
oracle@myracn1$ srvctl start nodeapps -n myracn2
oracle@myracn1$ srvctl start listener -n myracn1
oracle@myracn1$ srvctl start listener -n myracn2
oracle@myracn1$ srvctl start asm -n myracn1
oracle@myracn1$ srvctl start asm -n myracn2
oracle@myracn1$ srvctl start database -d myrcdb

oracle@myracn1$ cd /opt/oracle/crs/bin

oracle@myracn1$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE myracn1
ora....T2.lsnr application ONLINE ONLINE myracn1
ora.myracn1.gsd application ONLINE ONLINE myracn1
ora.myracn1.ons application ONLINE ONLINE myracn1
ora.myracn1.vip application ONLINE ONLINE myracn1
ora....SM2.asm application ONLINE ONLINE myracn2
ora....T4.lsnr application ONLINE ONLINE myracn2
ora.myracn2.gsd application ONLINE ONLINE myracn2
ora.myracn2.ons application ONLINE ONLINE myracn2
ora.myracn2.vip application ONLINE ONLINE myracn2
ora.myrcdb.db application ONLINE ONLINE myracn1
ora....l1.inst application ONLINE ONLINE myracn1
ora....l2.inst application ONLINE ONLINE myracn2

To upgrade the database catalog, we will run the catbundle.sql script for every database in the RAC but just once; for this example we will run it for the myrcdb just one time:

oracle@myracn1$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 18:43:44 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Generating apply and rollback scripts...


...


Updating registry...

1 row created.


Commit complete.

Check the following log file for errors:
/opt/oracle/db/cfgtoollogs/catbundle/catbundle_PSU_myrcdb_APPLY_2011Nov17_18_44_18.log

As suggested, it might be good to check the log file for ORA errors just to be sure. And as the last step, we will recompile and look for invalid objects:

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-17 18:44:47

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.


...


DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

SQL> exit

That's it! At this point we have an up to date RAC cluster with Oracle 10.2.0.5.4; we had to shut down the database service to apply this patch but this way is easier and quicker than applying a patch with the rolling upgrade method. Doing a rolling upgrade means applying a patch with no downtime, but the patch must be suitable for this method and is more complex and time-consuming than just shutting down a RAC and applying a patch.

Listing the inventory you can check that the 12419392 patch is registered:

oracle@myracn1$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/db
Central Inventory : /opt/oracle/oraInventory
from : /var/opt/opt/oracle/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /opt/oracle/db/oui
Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-46-17PM.log

Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_18-46-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch 12419392 : applied on Thu Nov 17 18:28:53 CST 2011
Unique Patch ID: 13856866
Created on 15 Jun 2011, 22:41:17 hrs PST8PDT
Bugs fixed:
6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356
9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406
10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865
11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739
8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576
7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422
10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700
12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269
12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296
10157402, 11737047



Rac system comprising of multiple nodes
Local node = myracn1
Remote node = myracn2

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

OPatch succeeded.

By the way, did you notice that there was no PSU for CRS? Usually there is the same patch and PSU for all Oracle Database components (CRS, ASM, databases), and also for all platforms, but in this particular case there was no 10.2.0.5.4 PSU for CRS. If there is, it would be preferably (or even mandatory) to have the same patch and PSU across all Oracle Database components, but this time it's fine this way.

More information:

10.2.0.5 Current Recommended Patches

Tuesday, December 6, 2011

Getting Oracle SQL execution plans

In order to tune SQL sentences you need to get its execution plan, that is, information about how Oracle database is going to process your SQL sentence to deliver results in the best way possible. This an important topic and a very complex one, and this post is intended only to show how to get execution plans, not how to analyze them.

The easiest way to get an execution plan is to use the EXPLAIN PLAN FOR statement and utlxpls.sql script:

SQL> set linesize 130
SQL> set pagesize 9999
SQL> EXPLAIN PLAN FOR
select OBJECT_NAME from dba_objects where OWNER='SYS' order by OBJECT_NAME;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2325770649

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1025 | 85075 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 1025 | 85075 | 7 (29)| 00:00:01 |
| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 6 (17)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 1179 | 101K| 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 3 (34)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
7 - access("U"."NAME"='SYS')
8 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")
9 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
10 - access("I"."OBJ#"=:B1)
13 - access("U"."NAME"='SYS')
14 - access("L"."OWNER#"="U"."USER#")

36 rows selected.

As you can see there is a lot of information about this very simple SQL sentence, therefore don't forget to read the documentation listed at the end of this post to have a better knowledge about execution plans.

By the way, if you're executing sentences with parallelism you would get more information using the utlxplp.sql script:

SQL> EXPLAIN PLAN FOR
select /*+ PARALLEL(b,4) */ b.OBJECT_NAME from dba_objects b where OWNER='SYS' order by OBJECT_NAME;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2762843742

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |
| 1 | SORT ORDER BY | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |
| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 14 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |
| 13 | PX BLOCK ITERATOR | | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE
"I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"
=7 OR

"I"."TYPE#"=9))=1)
12 - access("U"."NAME"='SYS')
14 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")

42 rows selected.

As said above, using EXPLAIN PLAN FOR is the easiest way to have an idea about an SQL sentence's execution plan without having to execute it, but it might not be the same plan used to execute it because plan selection happens at execution time and depends of a lot of factors like load, accuracy of statistics and hints to name a few. In fact, you would be more interested in knowing the execution plan of a running SQL rather than the execution plan of an SQL sentence not executed yet; if that's the case you can get it this way:

SQL> column SID format a6
SQL> column USERNAME format a10
SQL> column PROGRAM format a50
SQL> column EVENT format a30

SQL> select to_char(s.sid) AS sid, s.username, s.status, s.program, s.event
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE s.username = 'SYS' ORDER BY 1;

SID USERNAME STATUS PROGRAM EVENT
---- ---------- ---------- ------------------------------------------------ ------------------------------
1065 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) SQL*Net message to client
1087 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) db file scattered read
1259 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client
1275 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client
1276 SYS ACTIVE racgimon@myracnode (TNS V1-V3) Streams AQ: waiting for messag
es in the queue

1302 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client

6 rows selected.

SQL> SELECT p.plan_table_output FROM v$session s, table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where s.sid = 1087;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8qdgqzzzgk4as, child number 0
-------------------------------------
select count(*) from my_big_table where some_date < '25-JAN-08'

Plan hash value: 857495206

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2678 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 101K| 1382K| 2678 (27)| 00:00:27 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(("B"."SOME_DATE"<'25-JAN-08'))


21 rows selected.

As you can see, you should first identify the SID of the session running the SQL sentence you're interested in, and with dbms_xplan.display_cursor function you can get the sentence and the actual execution plan used.

On the other hand, if you want to check the execution plan of certain query that were executed recently you might try to find its SQL_ID in v$sqlarea; remember that SQL_TEXT is case-sensitive:

SQL> column SQL_TEXT format a80
SQL> select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
grq6tgwtun603 select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%
'

dfg3s1x621u6b select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'
cgyvau8fub4rq /* OracleOEM */ SELECT P.OBJECT_NAME,O.OBJECT_TYPE,P.OBJECT_OWNER,P.POLICY_NAM
E,P.POLICY_GROUP,P.ENABLE FROM DBA_POLICIES P, DBA_OBJECTS O WHERE P.OBJECT_NAME
=O.OBJECT_NAME


SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor('dfg3s1x621u6b')) p;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'

Plan hash value: 3694869755

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")


46 rows selected.

Also, if you're executing one by one SQL sentences you can get the execution plan calling dbms_xplan.display_cursor with no parameters immediately after the SQL to analize:

SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';

COUNT(*)
----------
7107

SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'

Plan hash value: 3694869755

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")


46 rows selected.

This can be useful even if you're debugging an SQL script, but if you need to get execution plans of SQL sentences placed in a PL/SQL script then you need to print the execution plan line by line:

SQL> SET TERMOUT ON
SQL> SET PAGESIZE 0
SQL> SET SERVEROUTPUT ON
SQL> SET LINESIZE 200

SQL> declare
v_count number;
v_plan varchar2(200);
CURSOR c1 IS SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;

begin
select /*+ PARALLEL(b,4) */ count(*) into v_count from dba_objects b where OWNER='SYS';
dbms_output.put_line ('Number of rows: '||v_count);

OPEN c1;
LOOP
FETCH c1 INTO v_plan;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line (v_plan);
END LOOP;
CLOSE c1;
end;
/

Number of rows: 7107
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'

Plan hash value: 3694869755

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")

PL/SQL procedure successfully completed.

Finally, it would be useful to know the real execution time of sentences you're running, and you can know that with the set timing on sentence:

SQL> set timing on
SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';

COUNT(*)
----------
7092

Elapsed: 00:00:00.46

More information:

Understanding Explain Plan
DBMS_XPLAN : Display Oracle Execution Plans
Using EXPLAIN PLAN

Wednesday, November 30, 2011

Monitoring a JVM in Grid Control

As you might know, Oracle Enterprise Manager Grid Control lets you monitor and manage Oracle databases, listeners, instances and things like that as expected, but you can also monitor and manage other systems and software like JVM.

Let's say that you want to monitor a custom service written in Java:

root@myserver # ps ax|grep mycustomserver
8945 ? Sl 11:04 /opt/mycustomserver/server -JVMargs /opt/mycustomserver/server.ini -n mycustomserver
14810 pts/2 S+ 0:00 grep mycustomserver

You can do it by means of JMX, setting JVM's jmxremote parameters as arguments or in a configuration file. You have to set at least jmxremote.port, there is no default value for it:

root@myserver # cat /opt/mycustomserver/server.ini|grep jmxremote
JVMOPTS3=-Dcom.sun.management.jmxremote.port=2055
JVMOPTS4=-Dcom.sun.management.jmxremote.authenticate=false
JVMOPTS5=-Dcom.sun.management.jmxremote.ssl=false

And if you don't know the application but want to try to monitor it, you can try as root to guess the monitoring port number with netstat:

root@myserver # netstat -tuanp|grep 8945|grep LISTEN
tcp 0 0 0.0.0.0:2055 0.0.0.0:* LISTEN 8945/mycustomserver
tcp 0 0 127.0.0.1:1852 0.0.0.0:* LISTEN 8945/mycustomserver

With the remote port number, and in case authentication is turned on with the corresponding user and password, you can add your Java application to Grid Control clicking in the agent link in Targets -> All Targets tab, and in the next page select JVM from the Add drop-down menu. In the Monitoring Configuration page you have to set at least the Machine name (would be 127.0.0.1) and the Admin Port number fields, and click the OK button.

After adding your JVM properly to Oracle Enterprise Manager, you will be able to check in the All Metrics link a lot of interesting metrics, at least interesting if you have knowledge of Java.

More information:

Monitoring and Management Using JMX
Monitoring and Management Using JMX Technology

Tuesday, November 29, 2011

Installing an Enterprise Manager agent for a TimesTen database

This is a small howto about installing an Enterprise Manager 11g agent for a TimesTen 11 database; this howto assumes that you have already downloaded the agent in the client machine and configured the Management Plug-in for Oracle TimesTen In-Memory Database in your Enterprise Manager server.

First, you have to configure a response file to do a silent installation; an installation with OUI should be easy enough to not have to cover it here. In the response directory below your agent staging directory you should find an additional_agent.rsp file, copy it and change at least this fields:

timesten@myserver $ diff additional_agent.rsp my.rsp
41,42c41,42
< SECURITY_UPDATES_VIA_MYORACLESUPPORT=
< DECLINE_SECURITY_UPDATES=
---
> SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
> DECLINE_SECURITY_UPDATES=TRUE
85c85
< ORACLE_AGENT_HOME_LOCATION=
---
> ORACLE_AGENT_HOME_LOCATION=/myagent
169,170c169,170
< OMS_HOST=
< OMS_PORT=
---
> OMS_HOST=myoemserver
> OMS_PORT=4889

Next, launch runInstaller with the full path of your response file:

timesten@myserver $ cd /myagent/linux_x64/agent
timesten@myserver $ ./runInstaller -silent -responseFile /myagent/linux_x64/response/my.rsp

This way you will install an agent in /myagent directory; don't forget to run as root the root.sh configuration script after finishing runInstaller:

root@myserver # /myagent/agent11g/root.sh

If you want to check if the agent is running and communicating with the Grid Control server, run emctl and pay attention to the Last successful upload message:

timesten@myserver $ cd /myagent/agent11g/bin/
timesten@myserver $ ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /myagent/agent11g
Agent binaries : /myagent/agent11g
Agent Process ID : 5493
Parent Process ID : 14892
Agent URL : http://myserver:3872/emd/main/
Repository URL : http://myoemserver:4889/em/upload/
Started at : 2011-11-29 09:48:12
Started by user : timesten
Last Reload : 2011-11-29 11:50:40
Last successful upload : 2011-11-29 12:53:25
Total Megabytes of XML files uploaded so far : 13.84
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 66.57%
Last successful heartbeat to OMS : 2011-11-29 12:53:29
---------------------------------------------------------------
Agent is Running and Ready

If you need to change the configuration of the agent for some reason then stop the agent, edit the emd.properties and targets.xml files, and optionally delete previous files:

timesten@myserver $ cd /myagent/agent11g/bin
timesten@myserver $ ./emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
timesten@myserver $ cd ..
timesten@myserver $ vi sysman/config/emd.properties
timesten@myserver $ vi sysman/emd/targets.xml
timesten@myserver $ cd sysman/emd
timesten@myserver $ rm -rf recv/* protocol.ini lastupld.xml collection/* state/* cputrack/* agntstmp.txt upload/* ../log/* emagent_storage.config core* sqlnet.log *sql
timesten@myserver $ cd /myagent/agent11g/bin
timesten@myserver $ ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Starting agent ..... started.

After these steps you have to be able to see the host and its agent in Grid Control at Targets tab, All Targets option. But in order to add a new TimesTen database to Enterprise Manager you have to first set the agent's password at Preferences -> Preferred Credentials -> Agent's Set Credentials icon; in that page you have to find your new agent and fill in the Host Username and Host Password fields. Next you will go to the Setup -> Management Plug-ins page and click the deploy icon of TimesTen management plug-in, in the next page add the new agent and click the Next button, and finally the Finish button in the last page.

Now you'll be able to add the TimesTen database clicking in the agent link in Targets page; it might be a delay to appear the TimesTen In Memory Database 11g option in the Add drop-down menu so take this into account. In that page you have to select the TimesTen In Memory Database 11g option from the Add menu, and then fill in the TimesTen instance name, Data Source Name, TimesTen user name and TimesTen password fields. Finally, click the Test Connection button and the OK button to complete the operation.

And if you're new to TimesTen and have no idea what the instance name and data source name would be, but the TimesTen user has the TT_HOME environment variable set, there is only one database at the server, and you know the username and password's database, you could guess TimesTen instance name and data source name this way:

timesten@myserver $ basename $TT_HOME
tt1121
timesten@myserver $ ttstatus | grep Data
Data store /TimesTen/DataStore/MyDataStore/MyDataStore

More information:

Meeting Hardware Requirements
Meeting Package, Kernel Parameter, and Library Requirements

Monday, November 28, 2011

About the RHCSA certification

From time to time there are people that ask me how I got the OCP certification and I regret not having posted about it, therefore now that I'm a shiny, brand-new Red Hat Certified System Administrator I would like to write about this.

First of all, this is a practical exam so if you enjoy taking multiple choice quizzes this is not the case. You can check all the information about this test in the Red Hat Certified System Administrator page. Something worth of reading it is the RHCSA Exam Objectives page, mandatory if you choose to take the test without enrolling into Red Hat recommended courses. By the way, if you are unsure if you have the level required for passing the test without enrolling into a course, it would be a good idea to take the pre-assessment quiz; you have to fill in a form with personal information in order to take the quiz, but if you take the certification test you'll have to give it anyway.

I prefer a lot to study a book and practice with my home computers, and for this test I studied the RHCE Red Hat Certified Engineer Linux Study Guide, by Michael Jang. Talking about other certification book, I read some time ago a review that blasted that book because there were some things that weren't covered in the book that were in the test, and unfortunately this is the case for this book too. But taking into account that no book can just put the exact test with its answers and that at this time I don't know a guide for studying for the new Red Hat Enterprise Linux 6 exams, I think that Jang's book is a good one for preparing for the RHCSA exam. I recommend reading the whole book and PRACTICING its exercises, even if this book is intended to be a guide for the (now obsolete) RHCT and RHCE exams; if you study the RHCE part you'll be a lot more confident while taking the test than if you just study the RHCT part.

Finally, as any good certification test, it's aimed to check your skills and knowledge about a concrete field, therefore having experience in this topics will help a lot and would be decisive in passing the test; if you don't have experience in some topics try at lest to practice it a lot before taking the test.

I think that holding a certification is a good way to back your skills and studying for the test is a good opportunity to keep up to date your knowledge, therefore I wish good luck for everybody that decide to take the RHCSA test.