The Oracle patching best practice

On windows, patching an Oracle database can be tricky.

Very often for example, the dll file will be used by some process which is a bit… Undefined. So to make sure everything run smoothly, the following process must be used.

It should in fact be used as a best practice on windows.

Stopping Oracle services might not be enough.

———
- the instances,
- the Intelligent Agent,
- the OLAP agent,
- the Oracle Management Server,
- the TNSListener,
- HTTP server.

- SNMP service,
- MSDTC (Microsoft Distributed Transaction Coordinator) service,
- Microsoft IIS (admin) service,
- Microsoft COM+ Event System,
- MTSrecovery service,
- BackupExec agent,
- ArcServe Agent,
- IBM Director Support Program.

————-

The list above comes from oracle support.

1. Stop OracleService, Listener, DBConsole and anything else related to Oracle + additional services as stated above.
2. 1. Change the services start mode to manual
3. Go to system>Environment variable.
   1. Backup PATH environment variable to notepad
   2. Remove all Oracle path from PATH (I had c:\oracle\product\102\bin)
4. Reboot the server
5. Install the patch:
   1. Make sure to select the correct oracle_home. By default, it will install the patch in a new home.
6. Put the oracle path back in PATH environment variable.
7. start the oracle service using the following command: C:\>oradim -startup -sid [SID] starttype srvc
8. Start the listener via windows service manager
9. In SqlPlus, log in as sysdba:
   1. C:\>set oracle_sid=[SID]
   2. C:\>sqlplus / as sysdba
   3. SQL>startup upgrade
   4. spool d:\somewhere\upgde.log
   5. @[oracle_home]\RDBMS\admin\catupgrd.sql (depending on the machine it can take up to 40 minutes)
   6. spool off
   7. Check if there is any ORA- error (this is long because there are a lot in the comments)
   8. shutdown
   9. startup
   10. @[oracle_home]\RDBMS\admin\utlrp.sql (takes up to 10 minutes)
   11. @[oracle_home]\RDBMS\admin\utlrp.sql (takes about 1 minute)

My RMAN tip of the week #1

How to run an RMAN script with windows scheduler?

Create a .rcv file and run it in a batch file.

 Let’s consider we got the following in a script called 10gbackup.rcv

 backup database plus archivelog not backed up 1 times;

we can then call the .rcv file in a batch file called scheduler.bat with the following line

set oracle_sid=MYDBSID

rman target / cmdfile D:\my\script\location\10gbackup.rcv log d:\my\log\location\somelogfilename.log

All that’s left to do is schedule it the normal way.

This is useful in the event where DBCONSOLE service is not working and the backup is scheduled using Oracle Enterprise Manager. When this service fails, then the backups stop working.

It’s always goot to have a fail safe.

Toad using instant client

I’ve seen plenty of search concerning Toad and instant client.

Instant client requires a bit of work to get it work. But if it works and toad doesn’t then look somewhere else

Working with toad is not much a problem with instant client it concerns the CONNECT_DATA parameter in the tnsnames.ora file.

MYDB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB.WORLD)
)
)

Try to add the parameter SID = MYDB instead.

Toad should work better like this. That’s a solution based on experience. If it’s not or if you have another solution, feel free to add your comment to this post. It will help others.