Network Adapter Could not Establish Connection

This is a funny scenario: you have a java program that connects to an Oracle database using IP adresses, but cannot connect to the database even if you use the same exact connect string in tnsnames and you're able to connect to that database using the SQL/Plus client.

But let's start from the beginning, when you don't have any issues at all (because the Oracle database server name has a full-qualified domain name and is resolvable):

oracle@test:~$ cd $ORACLE_HOME/network/admin
oracle@test:~/app/oracle/product/10.2.0/server/network/admin$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hera.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

XE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = XE)
    )
  )

XE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

As you can see, there are three different connect strings defined in this tnsnames file: one with a FQDN as dedicated connection, one with an IP address as shared connection, and the last one with an IP address as dedicated connection. Now you test the name of the server and try to connect using these three services:

oracle@test:~$ ping hera
PING hera.localdomain (192.168.108.76) 56(84) bytes of data.
64 bytes from hera.localdomain (192.168.108.76): icmp_req=1 ttl=64 time=8.34 ms
64 bytes from hera.localdomain (192.168.108.76): icmp_req=2 ttl=64 time=3.58 ms

--- hera.localdomain ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1003ms
rtt min/avg/max/mdev = 3.589/5.964/8.340/2.376 ms

oracle@test:~$ sqlplus hr/hr@xe

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:16 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

oracle@test:~$ sqlplus hr/hr@xe2

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:21 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

oracle@test:~$ sqlplus hr/hr@xe3

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:26 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Everything was fine as expected, and even this little handy java test program is working:

oracle@test:~$ cat OracleJdbcExample.java |grep jdbc
        String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.108.76)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))";

oracle@test:~$ javac -g OracleJdbcExample.java 
oracle@test:~$ export CLASSPATH=$CLASSPATH:/usr/lib/oracle/MyJDBC/ojdbc6.jar
oracle@test:~$ java OracleJdbcExample
Current Date from Oracle : 2013-05-15 16:06:08
done

But what happens when the host name is not resolvable anymore? Like when commenting the nameserver lines in /etc/resolv.conf:

oracle@test:/etc# ping hera
ping: unknown host hera

oracle@test:~$ sqlplus hr/hr@xe

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:25 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name: 

oracle@test:~$ sqlplus hr/hr@xe2

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:44 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

oracle@test:~$ sqlplus hr/hr@xe3

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:50 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

As expected, the first connect string that uses the hostname is not working, but the other two that use IP addresses still work. Therefore, the java program has to work as well, right?

oracle@test:~$ java OracleJdbcExample
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Connection reset
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:421)
 at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:531)
 at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:221)
 at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
 at java.sql.DriverManager.getConnection(DriverManager.java:582)
 at java.sql.DriverManager.getConnection(DriverManager.java:154)
 at OracleJdbcExample.main(OracleJdbcExample.java:25)
Caused by: java.net.SocketException: Connection reset
 at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96)
 at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
 at oracle.net.ns.DataPacket.send(DataPacket.java:199)
 at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:211)
 at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227)
 at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
 at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
 at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
 at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
 at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
 at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
 at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
 at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
 at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
 ... 7 more

That's not the case. According to Oracle's Metalink note 139775.1, when you're trying to connect to an Oracle service via a dedicated connection using java, the connect string has IP addresses instead of full-qualified domain names, and those IP addresses are not resolvable in the client machine, you will get a Network Adapter Could not Establish Connection error message, even if from that client you can reach the database using the SQL/Plus client. In this example the error message is different, I guess because I'm using Oracle 10g XE.

More information:

Io exception: The Network Adapter could not establish the connection
NL Exception trying to connect to 10g RAC w/JDBC

Managing maintenance tasks in Oracle 11g

As you might know, if you want to change execution parameters of automated database maintenance tasks in Oracle 10g you have to modify the jobs and windows associated to those tasks; therefore, you might be tempted to change 11g maintenance tasks the same way but you'll find that there are no jobs that look like maintenance tasks, and the windows still exist but are disabled:

SQL> set linesize 120
SQL> column WINDOW_NAME format a20
SQL> column ENABLED format a7
SQL> column REPEAT_INTERVAL format a60
SQL> column DURATION format a15
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows

where window_name like 'WEEK%';

WINDOW_NAME          ENABLED REPEAT_INTERVAL                                              DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
WEEKNIGHT_WINDOW     FALSE   freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00
                             ysecond=0

WEEKEND_WINDOW      FALSE    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0          +002 00:00:00

Did nobody tell you? In Oracle 11g you have to use the DBMS_AUTO_TASK_ADMIN procedures to control the three maintenance tasks: Automatic Optimizer Statistics Collection, Automatic Segment Advisor and Automatic SQL Tuning Advisor.

To check tasks information you can do this:

SQL> column CLIENT_NAME format a32
SQL> column STATUS format a10
SQL> column WINDOW_GROUP format a20
SQL> column ATTRIBUTES format a40
SQL> select client_name, status, window_group, attributes from dba_autotask_client;

CLIENT_NAME                      STATUS     WINDOW_GROUP         ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection  ENABLED    ORA$AT_WGRP_OS       ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor               ENABLED    ORA$AT_WGRP_SA       ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor               ENABLED    ORA$AT_WGRP_SQ       ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
                                                                 , SAFE TO KILL

As you can see, all the three tasks are enabled by default and have its own window group, but all the window groups have the same window members, one per day of the week:

SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in
(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');

WINDOW_NAME          ENABLED REPEAT_INTERVAL                                              DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW      TRUE    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
WEDNESDAY_WINDOW     TRUE    freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW      TRUE    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SUNDAY_WINDOW        TRUE    freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00
FRIDAY_WINDOW        TRUE    freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW       TRUE    freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
MONDAY_WINDOW        TRUE    freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

7 rows selected.

Then, if you want to disable a task you can do it this way:

SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation     => NULL,
window_name   => NULL);
end;
/

PL/SQL procedure successfully completed.

SQL> select client_name, status, window_group, attributes from dba_autotask_client;

CLIENT_NAME                      STATUS     WINDOW_GROUP         ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection  ENABLED    ORA$AT_WGRP_OS       ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor               ENABLED    ORA$AT_WGRP_SA       ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor               DISABLED   ORA$AT_WGRP_SQ       ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
                                                                 , SAFE TO KILL

And for enabling it again:

SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation     => NULL,
window_name   => NULL);
end;
/

PL/SQL procedure successfully completed.

SQL> select client_name, status, window_group, attributes from dba_autotask_client;

CLIENT_NAME                      STATUS     WINDOW_GROUP         ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection  ENABLED    ORA$AT_WGRP_OS       ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor               ENABLED    ORA$AT_WGRP_SA       ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor               ENABLED    ORA$AT_WGRP_SQ       ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
                                                                 , SAFE TO KILL

Now, if you want to disable a task for a certain day (window), you can do this:

SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation     => NULL,
window_name   => 'MONDAY_WINDOW');
end;
/

PL/SQL procedure successfully completed.

SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;

WINDOW_NAME          OPTIMIZER_STATS          SEGMENT_ADVISOR          SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW     ENABLED                  ENABLED                  ENABLED
FRIDAY_WINDOW        ENABLED                  ENABLED                  ENABLED
SATURDAY_WINDOW      ENABLED                  ENABLED                  ENABLED
THURSDAY_WINDOW      ENABLED                  ENABLED                  ENABLED
TUESDAY_WINDOW       ENABLED                  ENABLED                  ENABLED
SUNDAY_WINDOW        ENABLED                  ENABLED                  ENABLED
MONDAY_WINDOW        ENABLED                  ENABLED                  DISABLED

7 rows selected.

And for enabling a task in a window:

SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation     => NULL,
window_name   => 'MONDAY_WINDOW');
end;
/

PL/SQL procedure successfully completed.

SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;

WINDOW_NAME          OPTIMIZER_STATS          SEGMENT_ADVISOR          SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW     ENABLED                  ENABLED                  ENABLED
FRIDAY_WINDOW        ENABLED                  ENABLED                  ENABLED
SATURDAY_WINDOW      ENABLED                  ENABLED                  ENABLED
THURSDAY_WINDOW      ENABLED                  ENABLED                  ENABLED
TUESDAY_WINDOW       ENABLED                  ENABLED                  ENABLED
SUNDAY_WINDOW        ENABLED                  ENABLED                  ENABLED
MONDAY_WINDOW        ENABLED                  ENABLED                  ENABLED

7 rows selected.

Finally, if you want to change window attributes like the repeat interval, you can do it as usual:

SQL> exec dbms_scheduler.set_attribute('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0');

PL/SQL procedure successfully completed.

SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in

(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');

WINDOW_NAME          ENABLED REPEAT_INTERVAL                                              DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW      TRUE    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
MONDAY_WINDOW        TRUE    freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW     TRUE    freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW      TRUE    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SUNDAY_WINDOW        TRUE    freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00
FRIDAY_WINDOW        TRUE    freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW       TRUE    freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00

7 rows selected.

And just in case you want to know what operation is associated to what client:

SQL> column OPERATION_NAME format a40
SQL> select client_name, operation_name from dba_autotask_operation;

CLIENT_NAME                      OPERATION_NAME
-------------------------------- ----------------------------------------
auto optimizer stats collection  auto optimizer stats job
auto space advisor               auto space advisor job
sql tuning advisor               automatic sql tuning task

More information:

Managing Automated Database Maintenance Tasks
DBMS_AUTO_TASK_ADMIN

Fixing failures in Oracle 11g with DRA and RMAN

The Data Recovery Advisor was introduced in Oracle 11g, a feature that gives advice regarding database failures and also repairs them if possible. The DRA relies in the Health Monitor for diagnostics and can be used by means of RMAN or Enterprise Manager. It looks fine but since I don't have a lot of faulty databases I don't have experience with it; however, I have to study it in order to get the OCP 11g certification upgrade. At first look it seems like a good feature for administrators in a hurry or without so much experience, but personally I prefer to do things by hand and by myself.

Anyway, if you want to check the DRA capabilities and you have a database for practicing it might be worth the time trying it. The following is an exercise for doing that; first of all, back up your database because backups are an important part of recovering using the DRA. Next, shut down your database and rename (or delete if you're faithful enough) a datafile, and try to start up your database:

oracle@olimpo:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 6 13:35:56 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size      1335892 bytes
Variable Size    213913004 bytes
Database Buffers    41943040 bytes
Redo Buffers      6447104 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/orcl11g/example01.dbf'

The database didn't open as expected so the next step is running RMAN to use the DRA. As you might know, it's necessary to have the database at least mounted in order to be able to use RMAN:

oracle@olimpo:~$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 6 13:36:56 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL11G (DBID=915822427, not open)

The next step is listing the current known failures:

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
42         HIGH     OPEN      06-MAR-13     One or more non-system datafiles are missing

If you want to know more details about certain failure you can do it as well:

RMAN> list failure 42 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
42         HIGH     OPEN      06-MAR-13     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 42
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  145        HIGH     OPEN      06-MAR-13     Datafile 5: '/opt/oracle/oradata/orcl11g/example01.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable

The Health Monitor runs diagnostics automatically when some high or critical failure happens, but if for some reason there is not a diagnostic performed first then you cannot get advice regarding a failure. The next step involves getting advice regarding the open failures:

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
42         HIGH     OPEN      06-MAR-13     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 42
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  145        HIGH     OPEN      06-MAR-13     Datafile 5: '/opt/oracle/oradata/orcl11g/example01.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /opt/oracle/oradata/orcl11g/example01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      NOARCHIVELOG mode restore datafile 5  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm

As you might guess, the DRA first checks relevant and mandatory prerequisites before giving any automated repair option, like database backups. The optional manual actions are recommendations about solving the failure by hand, instead of letting the ADR fix the failures automatically. Sometimes you have to carry on mandatory manual actions before being able to do any manual or automated repair, and in this case there is no mandatory manual actions to do.

Moreover, there are two kinds of repair options: with no data loss and with data loss, be sure to check the strategy to know if the selected repair option involves no data loss, or at least to understand what you're going to lose if you select that option in case there is no better option.

Also, if you want you can check and modify the repair script listed before doing any repair, or you can preview the repair procedure without doing any repair at all this way:

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm

contents of repair script:
   # NOARCHIVELOG mode restore datafile
   restore datafile 5;
   recover datafile 5;

If you're happy with the proposed automatic repair options then you can repair the open database failures this way:

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm

contents of repair script:
   # NOARCHIVELOG mode restore datafile
   restore datafile 5;
   recover datafile 5;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 06-MAR-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/orcl11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-MAR-13

Starting recover at 06-MAR-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 06-MAR-13
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

RMAN> exit


Recovery Manager complete.

You have to confirm that you really want to execute repairs, and as a final step you might want to open the database before leaving RMAN. And that's all! In this case everything went fine and the database was repaired with no data loss:

oracle@olimpo:~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 6 13:43:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column INSTANCE_NAME format a20
SQL> column STATUS format a20
SQL> column DATABASE_STATUS format a20
SQL> select INSTANCE_NAME, STATUS, DATABASE_STATUS from v$instance;

INSTANCE_NAME      STATUS    DATABASE_STATUS
-------------------- -------------------- --------------------
orcl11g       OPEN    ACTIVE

More information:

Diagnosing and Repairing Failures with Data Recovery Advisor

RMAN crash course

It has been a long time since I posted here! In my previous job (this is my second week in my new one) I didn't have so much chance to keep posting because my position didn't involve neither UNIX server administration nor database administration, but I hope this new job will give me plenty of material to write interesting posts.

So let's begin with something simple, backing up an Oracle database with RMAN. Let's say that you're learning database management or you have a small and hopefully non-critical database that you want to backup, and you don't mind using the Flash Recovery Area; the directory used as FRA is set by the db_recovery_file_dest parameter in the database. First of all, you have to set the Oracle environment variables needed to connect to your selected database:

oracle@olimpo:~/rman$ set|grep ORACLE
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/server
ORACLE_SID=orcl11g

Then you can run RMAN to connect to the current target database (orcl11g):

oracle@olimpo:~/rman$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 16:53:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL11G (DBID=915822427, not open)

You might have noticed that my database is not opened, that's because it has no archive logs so in order to make a consistent backup I put it in mount state. If this is the first time you launch RMAN then it would be better to check its parameters:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default

For example, you might want to check the compression algorithm and retention policy (how many backups you want to keep at the same time); if you want to change something you just have to issue the command the same way it's shown:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default

Then if you're ready to backup your database issue this simple backup command:

RMAN> backup check logical database;

Starting backup at 05-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/orcl11g/system01.dbf
input datafile file number=00002 name=/opt/oracle/oradata/orcl11g/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/orcl11g/example01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/orcl11g/undotbs01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/orcl11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-13

This way you can backup a database with no archive logs enabled; if you have a database with archive logs enabled then append the plus archivelog subclause at the end of the backup command. Also, the check logical subclause is for checking logical errors in the database while performing the backup, the command for backing up a database can be as simple as backup database.

Now, if you want to check that your backup is really there you can do it this way:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        05-MAR-13       1       1       NO         TAG20130305T165413
2       B  F  A DISK        05-MAR-13       1       1       NO         TAG20130305T165413

But wait, why there are two backups instead of one? Maybe a detailed list would show us why:

RMAN> list backup;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.00G      DISK        00:01:15     05-MAR-13      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130305T165413
        Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/system01.dbf
  2       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/sysaux01.dbf
  3       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/undotbs01.dbf
  4       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/users01.dbf
  5       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:02     05-MAR-13      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20130305T165413
        Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp
  SPFILE Included: Modification time: 05-MAR-13
  SPFILE db_unique_name: ORCL11G
  Control File Included: Ckp SCN: 854520       Ckp time: 05-MAR-13

The first backup set is the full backup of the database, and the second one is the backup of the database's SPFILE, made by default. And before exiting from RMAN and in order to not fill up the FRA (its size set in the db_recovery_file_dest_size parameter), let's delete the obsolete backups, that is, backups no longer needed according to the retention policy:

RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
no obsolete backups found

RMAN> quit

Recovery Manager complete.

More information:

Getting Started with RMAN
Oracle Database Backup and Recovery Reference (Backup)

ORA-09817 error and full file systems

Let's say that you're trying to log into your database and suddenly got this error message:

oracle@mydb$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 13 15:47:27 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-01075: you are currently logged on


Enter user-name: 

oracle@mydb$ 

This is an easy problem to guess and the key is in the message No space left on device. The instance cannot write audit files because the file system is full so you have to figure out why is that file system full:

oracle@mydb$ set | grep ORA
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/orahome
ORACLE_SID=mydb

oracle@mydb$ strings $ORACLE_HOME/dbs/*mydb.ora | grep audit_file_dest
*.audit_file_dest='/oracle/admin/mydb/adump'

oracle@mydb$ df -k | egrep '(Filesystem)|(/oracle)'
Filesystem                 kbytes    used   avail capacity  Mounted on
/dev/vx/dsk/orafs/oracle 16327680 16327680       0   100%    /oracle

In this case the audit file destination is in the Oracle base file system, therefore the problem might be in other directory:

oracle@mydb$ du -k /oracle | sort -nr | head 
16035094        /oracle
8412062 /oracle/diag
8297523 /oracle/diag/rdbms/mydb/mydb
8297523 /oracle/diag/rdbms/mydb
8297523 /oracle/diag/rdbms
8250027 /oracle/diag/rdbms/mydb/mydb/trace
6926099 /oracle/orahome
1631834 /oracle/orahome/mydirectory
938085  /oracle/orahome/mydirectory/somefiles
727017  /oracle/orahome/bin

oracle@mydb$ du -ka /oracle/diag/rdbms/mydb/mydb/trace | sort -nr | head
8250027 /oracle/diag/rdbms/mydb/mydb/trace
5737533 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trc
2506955 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trc
1946    /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trm
969     /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trm
457     /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_3695.trc
380     /oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_8800.trc
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_13295.trc
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j000_23311.trc

Here is the problem: there are two very big trace files that occupy 8 gigabytes, half the file system size. When you know why your file system is full you have to decide what to do, like erasing, moving or compressing files, just be sure not to discard files you might need.

Creating partitioned tables and ORA-00922 error

There is a lot of different version of the Oracle Database software and even if the logic and operation of the database is more or less the same across versions, it is also more or less different across versions; for example, there is no drop database sentence in Oracle 9i.

So you work usually with 11g databases and love the tables partitioned by intervals, and you create one:

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:43:42 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

Table created.

And you try to create that same table in a 10g database just to find that you can't:

oracle@my10gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 8 12:46:15 2012

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


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

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);
INTERVAL (NUMTODSINTERVAL(1,'day'))
*
ERROR at line 7:
ORA-00922: missing or invalid option

Look no more: the ORA-00922 error in a 10g database means that you're trying to use a functionality that doesn't exist; in a 11g database it would mean missing brackets or syntax errors.

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:50:54 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'));
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
*
ERROR at line 8:
ORA-00922: missing or invalid option

More information:

CREATE TABLE (10g Release 2)
CREATE TABLE (11g Release 1)
Partition Administration

Locking statistics of tables

Having accurate statistics of database objects is mandatory in order to get the best execution path, and usually you can collect statistics using the DBMS_STATS package with no problem at all. But sometimes you have special tables that needs special treatment, like "temporary" tables (normal tables that are populated and its data modified very fast) that can change a lot the execution path of a SQL sentence and drive you crazy trying to pinpoint the root cause of that execution path changes.

As a demonstration of this behavior we will create two tables, one that will change a lot (test1) and other that is more or less static (test2):

SQL> create table test1 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create table test2 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 100000 loop
  insert into test2 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

At the beginning test1 table has 9 records, and we want to join test1 and test2 tables to get a few records, but first we will gather statistics of test1:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

SQL> select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

 ID         ID_CHAR
---------- ---------------
 4          4
 5          5
 6          6
 7          7

Next we will get the current execution plan for this small query:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  4    | 56    |  4   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  4    | 56    |  4   (0)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  5    | 55    |  4   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  5    |       |  3   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  3    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4 AND "B"."ID"<=7)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4 AND "A"."ID"<=7)

18 rows selected.

This execution plan looks good and really doesn't matter a lot because we got just a few records, but wait! Nine records is not the typical size of test1 table; now we will create more records:

SQL> delete from test1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

You might think that there is not a lot of difference having ten or ten thousand records in test1, but Oracle's optimizer does not think the same way and this time the execution plan is different:

SQL> EXPLAIN PLAN FOR
  2  select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2418654178

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1    | 14    |  3   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  1    | 14    |  3   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004857 |  1    |  3    |  1   (0)   | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C004858 |  1    |       |  1   (0)   | 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  1    | 11    |  2   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("A"."ID">=4000 AND "A"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("B"."ID"<=7000 AND "B"."ID">=4000)

19 rows selected.

You might think that retrieving four records is different of retrieving four thousand and that's right, but the optimizer choose the execution path based in wrong data; look the amount of rows Oracle thinks it will work to.

You will see it clearly refreshing the test1 table statistics and getting the new execution path:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYS', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  3002 | 33022 | 15   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  3002 |       |  8   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

Can you see the problem? Having to deal with this kind of tables you may: use hints to force the optimal execution of the SQL sentence, refresh statistics every time you change a table like this, or lock the statistics when you have a typical table size or optimal execution path.

We will consider that at this point the test1 table has a typical size and its statistics help the optimizer to chose an optimal execution plan, therefore we will lock its statistics this way:

SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

Now we will repopulate again the test1 table with nine records:

SQL> delete from TEST1;

9999 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /
BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

As long as the statistics of test1 table were locked you cannot gather new statistics unless you unlock the statistics of the table. Anyway, the point of doing this is to help the optimizer to chose a good execution plan:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  4    | 60    |  4   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  4    | 60    |  4   (0)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  5    | 55    |  4   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  5    |       |  3   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4 AND "B"."ID"<=7)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID"<=7 AND "A"."ID">=4)

18 rows selected.

SQL> delete from TEST1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  3002 | 33022 | 15   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  3002 |       |  8   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

As you can see, this time the execution plan was the same for ten or ten thousand records in test1 table and the number of rows managed in the execution plans were more or less accurate.

By the way, if you want to unlock the statistics of a table you can do it this way:

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

More information:

Locking or Unlocking Statistics
Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS

Oracle and OCFS2 not releasing space used

Sometimes you have to drop a tablespace to recover storage space and use it to create other tablespace, like when you have to resize an undo tablespace. And usually is not a big issue unless you have OCFS2 file systems:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem           1M-blocks      Used Available Use% Mounted on
                         24576     24572         4 100% /mydb/undo

oracle@myserver$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 26 22:57:35 2012

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


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

SQL> drop tablespace myundo including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g;
create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g
*
ERROR at line 1:
ORA-01119: error in creating database file '/mydb/undo/undo.dbf'
ORA-27044: unable to write the header block of file
Linux-ia64 Error: 28: No space left on device
Additional information: 4

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem           1M-blocks      Used Available Use% Mounted on
                         24576     24572         4 100% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x  4 root   root 4096 Jan 20  2010 .
drwxr-xr-x  4 root   root 4096 Dec 17  2009 ..
drwxr-xr-x  2 root   root 4096 Dec 17  2009 lost+found

As you might notice there is no space recovered from dropping that undo tablespace, and since we need to create a new undo tablespace in the same file system we have to do something.

I don't have a lot of dabatases placed in OCFS2 file systems so I didn't care about finding the root cause of this problem, and since I had this database with no users connected I just created a small undo tablespace somewhere else, shut down the instance, and after doing it I got that space back:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem           1M-blocks      Used Available Use% Mounted on
                         24576       359     24217   2% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x  4 root   root 4096 Jan 20  2010 .
drwxr-xr-x  4 root   root 4096 Dec 17  2009 ..
drwxr-xr-x  2 root   root 4096 Dec 17  2009 lost+found

oracle@myserver$ mount|grep /mydb/undo
/dev/mapper/myvg-undo on /mydb/undo type ocfs2 (rw,_netdev,datavolume,nointr,heartbeat=local)

oracle@myserver$ uname -a
Linux myserver.localdomain 2.6.9-67.EL #1 SMP Wed Nov 7 13:43:35 EST 2007 ia64 ia64 ia64 GNU/Linux

oracle@myserver$ /sbin/lsmod|grep ocf
ocfs2                 744096  26 
debugfs                27340  2 ocfs2
ocfs2_dlmfs            53416  1 
ocfs2_dlm             424192  2 ocfs2,ocfs2_dlmfs
ocfs2_nodemanager     316791  32 ocfs2,ocfs2_dlmfs,ocfs2_dlm
configfs               65468  2 ocfs2_nodemanager
jbd                   149272  2 ocfs2,ext3

oracle@myserver$ /sbin/modinfo ocfs2
filename:       /lib/modules/2.6.9-67.EL/kernel/fs/ocfs2/ocfs2.ko
license:        GPL
author:         Oracle
version:        1.2.9 1B43458FD47258934A48F1C
description:    OCFS2 1.2.9 Mon May 19 14:04:34 PDT 2008 (build a693806cb619dd7f225004092b675ede)
depends:        ocfs2_nodemanager,ocfs2_dlm,jbd,debugfs
vermagic:       2.6.9-67.EL SMP ia64gcc-3.4

I know this is just a workaround and not a good one, but it works fine and you can move on the next thing.

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

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.

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