Oracle on Windows Part 3 - The Registry

July 8, 2004

In this third article about Oracle on Windows, we will take a look at the registry. Generally speaking, the registry is a database that stores information about the setup and configuration of a PC. If you are not a Windows administrator, and want to stay in the Oracle world, so to speak, there are two excellent references you can use to help demystify the Windows environment. One of them is free, and the other is well worth the money. The free documentation is Oracle9i Database Getting Started Release 2 (9.2) for Windows, and the other one is ORACLE9i for Windows 2000 Tips & Techniques, an Oracle Press book by Scott Jesse, Matthew Hart and Michael Sale. Oracle's documentation is pretty thorough, but if you want even more detail about using Oracle on Windows, read the book.

Linux and UNIX seem to get most of the press when it comes to discussing the underlying operation system or platform, but as mentioned in previous articles, Oracle's presence on Windows is not uncommon anymore. If you consider Oracle's campaign to capture more of the small to medium business (SMB) market, (more so with 10g and the idea of a "2 Day DBA"), chances are the SMBs are pure Windows shops where UNIX is just thought of as that operating system managed by introverts working in cold, dimly lit server rooms whose mortal enemies are sunlight and any outdoor activity.

The registry

Keep in mind two things about Oracle on Windows: it is easy to manage, and it is just as easy to cause a significant problem with the OS. Why is that? The answer is related to looking at both sides of a coin. On one side, you have Microsoft and Oracle issuing strong advisories against manually editing the registry, and on the other side, both of them tell you how to do just that. There is no bottom line answer (to edit or not), so if you are not sure what you are doing when it comes to editing the registry, but feel you must do so without the requisite knowledge and skills, simply ask yourself if you feel lucky today. Well, do you?

Actually, there is no need for luck. And, there is no "try" involved either. "Try not. Do. Or do not. There is no try" (okay, so I like using lines from movies to help make a point). You can read 24 pages out of the Getting Started guide (Chapter 9) and be sufficiently well-armed (or dangerous?) when it comes to working with the registry. By knowing a handful of parameters, you can easily customize your database (or instance, depending on the context of what you are trying to do) and make it do things you cannot normally do via Oracle itself. Some of the registry parameters have counterparts in UNIX, so it is useful to know what can be done even if you do not know how it is done in UNIX.

Some useful registry parameters

SQLPATH
If you prefer to keep all of your scripts in one place, or have multiple users on a PC (who may or may not have set the "start in" property of a SQL*Plus desktop icon), you can use SQLPATH to centrally manage and store your scripts. The default location is ORACLE_HOME\dbs, and by specifying a different location, you can keep extraneous files out of the software installation folders.

ORA_SID_AUTOSTART
This parameter is similar to init.d rc scripts that start an instance as part of the boot process on UNIX. In Windows, your instance may or may not be running, but if the service is not running, the instance will not be running either. Upon a reboot or startup, you can have your database (or databases) automatically start up (the service is set to start automatically, which in turn enables the instance to be started, which in turn means your database is open for business). With this parameter set, you can also perform shutdown and startup simply by stopping and starting the service (see the next parameter).

ORA_SID_SHUTDOWN
This goes hand in hand with the autostart parameter because if set to true, the database is shutdown when the service is stopped. Okay, shutdown how?

ORA_SID_SHUTDOWNTYPE
You can set one of three values for this parameter: "a" for abort, "n" for normal, and "i" for immediate (and is the default value).

ORA_SID_SHUTDOWN_TIMEOUT
To be on the safe side of things, (i.e., ensure you have a clean shutdown if not using the abort option), you can set a time (in seconds) for the service to wait before stopping.

Putting the parameters to practical use

Oracle's documentation lists many more parameters than what was just mentioned. I highlighted the aforementioned parameters for a specific reason. Suppose you walk into a new DBA job, to find that your recently departed/terminated predecessor did not leave behind the passwords for sys or system. Users can still access the database, but none of them has been granted the sysdba privilege. Not to worry. Log in as an administrator and add yourself to the ORA_DBA group. You can always re-create the password file, and by accessing the registry and services control panel, you now have the ability to reset the passwords. In the meantime, you can manage shutdowns and startups via the service.

It is easy to make a mistake and fix it

Before you start hacking away at the registry, here is an exercise you can perform to help develop skills and gain confidence in what you are doing. Take something innocuous in the registry, such as your typed URLs, and export them. The exported file is a "reg" file. Save it to your desktop or some other easy to find location. Delete the TypedURLs folder ("key," to be more precise) in the registry and start up an Internet Explorer session. Note that your typed URL history in the address list is gone. Exit the browser. Import your typed URLs and see how IE behaves. If you are comfortable performing this exercise--and there is absolutely no harm done if you make a mistake--then you will be sure of what you are doing when performing a registry export of your Oracle entries. The "Typed URLs" folder is found at HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\TypedURLs.

After importing, if you see the message shown below:

you are good to go.

In Closing

If you have been reluctant to edit your registry because of the horror stories you have heard about how a PC can be damaged (the technical term is "trashed") due to an incorrect or deleted registry parameter or value, then this article will have helped you overcome some of that trepidation. The information in Chapter 9 of the Getting Started guide clearly tells you how to edit the registry. There are times and reasons why and when you should or can edit the registry, and there are times when you should not (as a matter of routine). For example, if you want to set an environment variable, later versions of Windows allow you to perform this task via other means, so by all means, do it one of those ways. In Part 4 of this series, I'll go into more detail about services.

» See All Articles by Columnist Steve Callan