Short notes taken during the test phase for installation of patchset 10.2.0.4

Let me start with a bit harsh, but nevertheless a realistic warning: if your search engine landed you here and if you’re looking for quick and dirty list of steps to upgrade your Oracle database, then please, stop reading and keep searching! And don’t make fool of yourself asking on public forums questions like this: “…can you please give me step by step instructions for patchset installation…it’s really URGENT…” – someone asking questions like this one should not be the one who is installing anything on the server, including patchset. Personally, I’ll always make sure to keep such people as far away as I can from the back office (and hence away from myself!).
If you’re totally new to Oracle patchset installation, you need one and only one starting document, with interesting name – readme.html.

At the time of this writing (end of July 2008) there are some known issues listed in Metalink note 555579.1 “10.2.0.4 Patch Set – Availability and Known Issues”. Make sure you read it. In particular the following notes:

• Note:580561.1 “ORA-600 [kddummy_blkchk][file#][block#][18038] during extent operations like TRUNCATE”; if you’re using ASSM tablespaces and direct path loads/inserts (as well as drop/truncate on those segments), you should apply patch 5386204 (at the time of this writing patch is not yet available for Windows x64)
• if you’re running Oracle10g on Windows 2003 SP1 make sure you read Metalink note 464683.1 “Unexplained Database Slowdown Seen on Windows 2003 Service Pack 1”
• and a non-critical one; Metalink note:726418.1ALERT: “The 10.2.0.4 Windows Patchset Overwrites %ORACLE_HOME%\network\admin\sqlnet.ora”, nevertheless Oracle republished 10.2.0.4 patchset on 10. July 2008 (first release for x64 was on 19. May 2008). You can simply backup sqlnet.ora file an restore it after wards – meaning that you don’t need to re-download updated version just to fix this bug, if you have version from 19th of May.
• Bug 5082178 – Bind peeking may occur when it should not — this bug was fixed in 10.2.0.4, meaning that some queries that’re now running fine (due to the bind peeking taking place, even when it should not) can now experience performance problems. I don’t think we can truly test this due to a large number of schemas, applications etc. We’ll see how lucky we’re ;-)

We’ll take particular care for these three critical bugs introduced in 10.2.0.4:

• Bug 7038750 – Dump (ksuklms) / instance crash — it’s less likely to happen on non-RAC but still possible…fortunately there is workaround available to avoid crash.
• Bug 5868257 – Dump / memory corruption from UPDATE DML
• Bug 6917874 – Wrong results from multi level push of join predicates

In readme.html you’ll find in section “15.19 Native Full Outer Join Implementation” Oracle recommendation to turn on hidden parameter:

_optimizer_native_full_outer_join =force

I’ll certainly not follow their advice. If they’re confident in the benefit of turning this parameter on, then why is it listed in patchset as as step that should be performed by DBA. Are they afraid of possible regression? ;-)

It’s always interesting to examine platform specifix bugs that were fixed by patchset. The bugs fixed in 10.2.0.4 on Windows x64 that caught my attention:

• 5940550 VIRTUAL MEMORY OF ORACLE.EXE GROWS ON 64 BIT X86 WINDOWS PLATFORM
• 5205552 EXCEPTIONAL HIG VM SIZE USAGE FOR ORACLE.EXE ON 64 BIT X86 WINDOWS PLATFORM
• 5041672 64-BIT WIN2K3 OS IS INCORRECTLY REPORTED AS 32-BIT WIN2K3
• 4927774 UNRESPONSIVE SYSTEM WITH HIGH KERNEL CPU UTLIZATION

One thing that remains open at this stage of testing 10.2.0.4 is Large Pages support in 10g. We failed miserably trying to setup Oracle10g to use Large pages with 10.2.0.3. For now, it remains unclear which fault it is; Oracle, Microsoft or simply ours.

Someone on dba-Village forum asked about how to protect username and password for connecting to RMAN catalog database. Since I’m strongly against using remote os authentication (remote_os_auth=true) in real life production environment, I would probably choose Oracle Wallet for storing credentials for connecting to rman catalog.

Note: With Oracle Wallet implementation we’re relying on file system permissions that protects our wallet; it’s not perfect but I’m happy to trade this “risk” with (in my humble opinion) much riskier approach of using remote os authentication. Anyone with access to the wallet that has auto-login feature turned On, can connect as user stored in the wallet without a password! Approach described in this post should be used seldom and with care.

Here is a mini How-to (I was using Windows 2003 and Oracle11g):

###########################################
1) Create Oracle wallet
- the result of this step is directory D:\oracle\rmancat_wallet 
  with two files: cwallet.sso and ewallet.p12 . 
###########################################

cmd> mkstore -wrl D:\oracle\rmancat_wallet -create
Enter password: mysecret

PASSWORD_POLICY : Passwords must have a minimum length of eight characters and
contain alphabetic characters combined with numbers or special characters.
Enter password: mysecret1

Enter password again: mysecret1


####################################################
2) Adding database user credentials to this wallet
ORA11   .... TNS alias for RMAN catalog database
rmancat .... database user 
test    .... password for rmancat
####################################################

mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test

Enter wallet password:

Create credential oracle.security.client.connect_string1


#######################################
3) Configure sqlnet.ora at client side
#######################################

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\oracle\rmancat_wallet)))
SQLNET.WALLET_OVERRIDE = TRUE

########################################
4) Test connection
########################################

D:\ORACLE>sqlplus /@ora511

SQL*Plus: Release 11.1.0.6.0 - Production on ╚et Jul 10 13:58:00 2008

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


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

SQL> show user
USER is "RMANCAT"

########################################
5) Additional security checks
########################################

I would recommend to save sqlnet.ora and tnsnames.ora separately
from the common oracle home, for example we can copy both files to
the wallet directory (in our example D:\ORACLE\rmancat_wallet).
Make sure that only user executing rman backup has read permissions on this 
directory. By default Oracle changes permissions only on files
cwallet.sso and ewallet.p12, leaving directory permission to be inhereted from
the parent (at least that's the case on Windows, I'm not sure about Linux/Unix)!
  
Don't forget to setup environment variable TNS_ADMIN pointing
to your wallet directory at the beggining of backup script.


##############################
6) Various handy commands 
##############################

>> ------------------------------
>> List the content of the Wallet
>> ------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -listCredential

Enter wallet password:

List credential (index: connect_string username)
1: ora11 rmancat

>> -------------------------------------------------
>> Modify credential stored in the wallet
>> -------------------------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -modifyCredential ora11 rmancat newpassword

Enter wallet password: mysecret1

Modify credential
Modify 1

>> -----------------------------------
>> Deleting credential from the wallet
>> -----------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -deleteCredential ora11

Enter wallet password:

Delete credential
Delete 1

>> ------------------------------------
>> Adding credential
>> ------------------------------------

Remember, you can have only ONE user per TNS alias stored in the wallet. If you need to store
two users (schemas) for one TNS, then you'll have to use two wallets!

For example, this is possible:

> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test
> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott tiger

If you try to add second account for the same TNS alias, you'll get error:

> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott2 tiger

Create credential Secret Store error occured: 
oracle.security.pki.OracleSecretStoreException: Credential already exists

In this case you'll need to create new wallet for scott2.

##################
7) References
##################

Metalink Note:340559.1 "Using The Secure External Password Store"

Chapter 9 Secure External Password Store:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#g1033548

While working on my Windows x64 test server, trying to reproduce (yet another) critical Oracle bug related to compressed tables, I thought some help from the GUI tool can easy my research. Of course, my first thought was to use SQL Developer, but in this particular case Toad was a better choice. Since all other Oracle Software installed on this test box is 64-bit, I didn’t want to install full blown 32-bit Oracle client just for the sake of running Toad. Instead I downloaded 32-bit Oracle instant client 11.1.0.6.

My first attempt of setting environment was something like this:

:: Oracle11g (32-bit, instant client)
title Oracle11 (32-bit, instant client)
set PATH=D:\ORACLE\instantclient_11_1;%PATH%
set ORACLE_HOME=D:\ORACLE\instantclient_11_1
set TNS_ADMIN=D:\ORACLE\instantclient_11_1
start D:\ORACLE\Toad\Toad

The important part is line with SET PATH, where I put 11g client in the front of other directories, already in the path variable, including some 10g homes. It happens that this is not enough. It’s mandatory that you exclude from the path all other Oracle client software that is not part of the instant client.

So the version that works is slightly modified:

:: Oracle11g (32-bit, instant client)
title Oracle11 (32-bit, instant client)
set PATH=D:\ORACLE\instantclient_11_1;%WINDIR%;%WINDIR%\SYSTEM32
set oracle_home=D:\ORACLE\instantclient_11_1
set TNS_ADMIN=D:\ORACLE\instantclient_11_1
start D:\ORACLE\Toad\Toad

When you run Toad for the first time you’ll likely see combo box with empty list of TNS aliases. Don’t worry, just key in Oracle TNS alias that you want to connect to (assuming that you put valid TNSNAMES.ORA, SQLNET.ORA in the directory from TNS_ADMIN variable).

Disclaimer:
Installing client GUI tools, such as Toad on production server(s) is not recommended – don’t do it :-). I chose to install Toad on this particular test server because the machine is in the sand-box environment that prevents connectivity from the net.

This question someone asked on dba-village.com forum and got short (and incorrect) answer: NO.

Actually, this is possible because RMAN can read from backupset files written by older versions. The same is true for patchset differences, 10.2.0.2 backupset can be used for a restore with RMAN 10.2.0.4. We can also restore 32-bit database backup set with 64-bit version. Of course, keep in mind that simply restoring older database with newer version doesn’t give you “instant” upgraded database – all upgrade steps must be performed after a restore (yes, you’ll have to read that manual, afterall;-). I’m not sure how far in the past this backward compatibility goes – I don’t have test environment with 8i or even 8.0 to try this out.

Refer to Metalink Note:369644.1 “Answers To FAQ For Restoring Or Duplicating Between Different Versions And Platforms”.

I tested 9.2->10.2 scenario by restoring 9.2.0.8 backupset with 10.2.0.4 RMAN.

--
-- Connecting to Oracle 9.2.0.8 (noarchivelog) database
-- 
Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: ORA9 (DBID=422058228)
using target database controlfile instead of recovery catalog

D:\Oracle>rman target / nocatalog

-- -------------------------------------
-- Clean shutdown
-- -------------------------------------
RMAN> shutdown

database closed
database dismounted
Oracle instance shut down

-- ------------------------------------
-- Let's mount the database... 
-- (for the sake of simplicity I 
-- left 9i db in noarchivelog mode) 
-- ------------------------------------

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     705765992 bytes

Fixed Size                      455272 bytes
Variable Size                301989888 bytes
Database Buffers             402653184 bytes
Redo Buffers                    667648 bytes

-- --------------------------------------
-- Backup 9i database with 9i RMAN
-- --------------------------------------

RMAN> run {
2>  allocate channel d1 type disk;
3>  backup format 'D:\ORADATA\ORA9\BACKUP\%d_%t_%s_%p.bak' database;
4>  backup current controlfile format 'D:\ORADATA\ORA9\BACKUP\ctrl_%d_%u_%s.bak';
5>  release channel d1;
6> }

allocated channel: d1
channel d1: sid=11 devtype=DISK

Starting backup at 05.06.08
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORADATA\ORA9\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORADATA\ORA9\UNDOTBS01.DBF
input datafile fno=00007 name=D:\ORADATA\ORA9\XDB01.DBF
input datafile fno=00006 name=D:\ORADATA\ORA9\USERS01.DBF
input datafile fno=00004 name=D:\ORADATA\ORA9\INDX01.DBF
input datafile fno=00003 name=D:\ORADATA\ORA9\DRSYS01.DBF
input datafile fno=00005 name=D:\ORADATA\ORA9\TOOLS01.DBF
channel d1: starting piece 1 at 05.06.08
channel d1: finished piece 1 at 05.06.08
piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK comment=NONE
channel d1: backup set complete, elapsed time: 00:01:15
Finished backup at 05.06.08

Starting backup at 05.06.08
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current controlfile in backupset
channel d1: starting piece 1 at 05.06.08
channel d1: finished piece 1 at 05.06.08
piece handle=D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05.06.08

Starting Control File and SPFILE Autobackup at 05.06.08
piece handle=D:\ORACLE\ORA9\DATABASE\C-422058228-20080605-02 comment=NONE
Finished Control File and SPFILE Autobackup at 05.06.08

released channel: d1

-- ---------------------
-- Shutdown 9i instance
-- ---------------------
RMAN> shutdown

database dismounted
Oracle instance shut down

-- -----------------------------------------
-- Prepare 10g instance with which you'll
-- restore 9i backup (this step is out of 
-- the scope of this note!)
-- -----------------------------------------

Prior to excecuting restore of 9i RMAN backup with
10g RMAN I took backup of 9i files and deleted them 
from original location.

As shown below, there are no
datafile in directory D:\ORADATA\ORA9 directory 

D:\ORADATA\ORA9>dir
 Volume in drive D is SW
 Volume Serial Number is 408C-7E40

 Directory of D:\ORADATA\ORA9

05.06.2008  23:24    <DIR>          .
05.06.2008  23:24    <DIR>          ..
05.06.2008  23:08    <DIR>          BACKUP
05.06.2008  23:23    <DIR>          BACKUP9_ORIGINAL
               0 File(s)              0 bytes 

And the RMAN backup directory contains:

D:\ORADATA\ORA9\BACKUP>dir
 Volume in drive D is SW
 Volume Serial Number is 408C-7E40

 Directory of D:\ORADATA\ORA9\BACKUP

05.06.2008  23:08    <DIR>          .
05.06.2008  23:08    <DIR>          ..
05.06.2008  23:08         1.638.400 CTRL_ORA9_08JI6V06_8.BAK
05.06.2008  23:08       517.414.912 ORA9_656636859_7_1.BAK
               2 File(s)    519.053.312 bytes
               2 Dir(s)   4.094.455.808 bytes free

-- -----------------------------------------
-- Restore 9i backupset with 10g 
-- 
-- (Note: I used 9i pfile to start 10g instance 
-- that's what caused some warnings about
-- obsolete parameters.)
-- -----------------------------------------

D:\Oracle>rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Cet Jun 5 23:29:08 2008

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     683671552 bytes

Fixed Size                     1298592 bytes
Variable Size                276828000 bytes
Database Buffers             402653184 bytes
Redo Buffers                   2891776 bytes

-- -----------------------
-- Restore control file...
-- -----------------------

RMAN> run {
2>  allocate channel d1 type disk;
3>  restore controlfile from 'D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK';
4>  release channel d1;
5> }

allocated channel: d1
channel d1: sid=157 devtype=DISK

Starting restore at 05.06.08

channel d1: restoring control file
channel d1: restore complete, elapsed time: 00:00:03
output filename=D:\ORADATA\ORA9\CONTROL01.CTL
output filename=D:\ORADATA\ORA9\CONTROL02.CTL
output filename=D:\ORADATA\ORA9\CONTROL03.CTL
Finished restore at 05.06.08

released channel: d1

-- ------------------------
-- Mount database...
-- ------------------------

RMAN> alter database mount;

database mounted

-- ------------------------
-- Restore database...
-- ------------------------

RMAN> run {
2>  allocate channel d1 type disk;
3>  restore database;
4> }

allocated channel: d1
channel d1: sid=157 devtype=DISK

Starting restore at 05.06.08

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\ORA9\SYSTEM01.DBF
restoring datafile 00002 to D:\ORADATA\ORA9\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORADATA\ORA9\DRSYS01.DBF
restoring datafile 00004 to D:\ORADATA\ORA9\INDX01.DBF
restoring datafile 00005 to D:\ORADATA\ORA9\TOOLS01.DBF
restoring datafile 00006 to D:\ORADATA\ORA9\USERS01.DBF
restoring datafile 00007 to D:\ORADATA\ORA9\XDB01.DBF
channel d1: reading from backup piece D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK
channel d1: restored backup piece 1
piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK tag=TAG20080605T230738
channel d1: restore complete, elapsed time: 00:01:25
Finished restore at 05.06.08
released channel: d1

-- ----------------------------
-- Open database with resetlogs
-- ----------------------------

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/05/2008 23:41:57
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/05/2008 23:41:57
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

This error is normal, since we're now running 9i database with 10g binaries,
so we'll still have to upgrade 9i to 10g...

-- ---------------------------------
-- Startup database in upgrade mode
-- ---------------------------------

D:\Oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Cet Jun 5 23:42:53 2008

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

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup upgrade;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  683671552 bytes
Fixed Size                  1298592 bytes
Variable Size             276828000 bytes
Database Buffers          402653184 bytes
Redo Buffers                2891776 bytes
Database mounted.
Database opened.

-- ------------------------------------------
-- from here on you have to follow 
-- instructions from Oracle Upgrade Guide...
-- ------------------------------------------

From time to time I receive log files from one Oracle customer that is using Windows robocopy tool for backing up RMAN backup set files to their backup server. Robocopy report constains lines such as:

...
New File  186.0 m    \\oraprod1\BACKUP$\RMAN01\PROD_647989204_520_1.BAK
New File      3.0 g     \\oraprod1\BACKUP$\RMAN01\PROD_651902404_608_1.BAK
...

Since I don’t see file timestamps in the report, the only timestamp that is available to me is the one that is the result of RMAN format parameter %t in the backup piece name, such as 647989204 or 651902404 from example above. What is the meaning of %t? According to Oracle documentation the parameter %t means:

Quote:
%t Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.

They’re not mentioning what is actually a “fixed reference time” (known as an epoch), but it’s not hard to figure this out with a little help from python time module. Someone at Oracle has chosen an epoch that starts with 17.08.1987 00:00:00 (dd.mm.yyyy hh24:mi:ss) and here is my handy python script that takes integer (such as 651902404 from an example above) and print out human readable time stamp:

# 
# rman_t.py - Converting RMAN %t timestamp to human readable timestamp
# Example:
# cmd> python rman_t.py 651902404
#cmd>  Timestamp [RMAN %t] = Sun Apr 13 04:00:04 2008
#
import sys
import time
epoch = 556149600             # ctime(556149600) represents 17.8.1987 00:00:00
print "Timestamp [RMAN %t] = " + time.ctime(epoch + int(sys.argv[1]))
# End rman_t.py