Other key non-database files

Although there are only three types of file which technically make up an Oracle database, there are other disk files floating around the place which are extremely important tot he correct operation of an Oracle server. They are not considered to be technically part of the database, though, because if they ever get deleted or damaged, no-one will know about it and you won't be told about it. Compare that with losing your control file or a data file: the database will definitely report that as an error or even crash as a consequnce. In short, the key difference between a database file and a non-database one is whether or not the file is continually being written to and monitored. The three types of file I'm going to mention here are not continually monitored.

Archived Redo Logs

The online redo logs contain a record of every transaction that has modified data in the database. That 'stream of redo' is there so that if we ever need to re-perform those transactions (during a database recovery), we can. The trouble is that there is only a limited number of online redo log groups and LGWR eventually fills up the last group... and then switches back to the first and over-writes its contents. That means LGWR eventually destroys redo... redo which was supposed to be there to protect our database by making it possible to recover it!

Archived redo logs are the necessary answer to this paradox. Every time LGWR stops writing to one online log and starts writing to another, the ARCn background process makes a replica copy of the just-filled log in some new location on disk. LGWR can eventually do its worst in over-writing the redo in the online log, because we have a copy of its contents stored elsewhere. The stream of redo produced by the database is thus made continuous and permanent by the Archiver process -which means that archived redo logs are essential to truly being able to say, 'I can always recover my database'.

A database therefore operates with just a handful of online logs -but with an ever-increasing number of archives. You do not need to retain archives for ever, however: once they are a week or more old, they can usually be aged out to tape and deleted from the disk to which they were originally written. This means, of course, that whilst archives are great for protecting your database they impose a management and maintenance burden on the DBA, who has to make sure there is enough disk space for the next archive to be written (implying bulk deletion of old archives) but enough logs available on disk to make any conceivable recovery do-able as quickly as possible (implying retaining as many archives as feasible on disk). It's a management balancing act, in short.

Just as we care about redo enough to multiplex our online redo logs, it stands to reason that we should think about multiplexing our archived redo log copies, too -and, indeed, it is possible for Enterprise Edition users to create archives in up to ten different locations. Standard Edition users can only create duplexed copies (that is, archivees can be written to only two different locations). It is not compulsory to multiplex your archives, but since they are the only thing (ultimately) standing between you and data loss, it would not usually be a sensible move to neglect to do so.

Password File

Logic dictates that the record of who is allowed to start up instances cannot be held inside the database -because to read data from inside a database you need an instance... but you can't create an instance until you've read the record of who is allowed to start up the instance, which you can't read until you've got an instance... and so ad infinitum!

To break this sort of Catch-22, the details of who is allowed to start an instance is held outside the database, in a small binary file called the password file. The password file permits external authentication of privileged users -where the term "privileged user" means 'someone who can startup and shutdown an instance, backup and recover a database, and create a database in the first place'. These are the five basic privileged actions, and if your username and password are found inside the password file, you can perform any of them. If your username and password are instead only stored inside the database itself, then you are merely an "ordinary user" (however many rights and powers you might be granted) and you will never be able to issue the startup or shutdown commands.

Password files need to be created in a given location and with a specific name. The location is $ORACLE_HOME/dbs and the file name must be orapwXXX, where "XXX" is the name of your instance, given by the value assigned to the ORACLE_SID environment variable in force at the time. If you set ORACLE_SID to FRED, for example, then the startup command will create you an instance called FRED, provided we can find a password file called orapwFRED in the ORACLE_HOME/dbs directory. (Note that Windows users have to store their password files in %ORACLE_HOME%\database and the file is called PWDXXX.ora). There is no leeway on the naming or location of the password file, though Unix/Linux users can always store it anywhere they like and call it anything they like and leave behind a symbolic link of the right name in the right place that points to where the file really is. Windows users don't have that sort of luxury, though.

Password files are created using a special Oracle-supplied utility called orapwd. You might issue this sort of command, for example:

C:\oracle\product\10.2.0\db_1\database> orapwd file=PWDtkit.ora password=tkit entries=25

C:\oracle\product\10.2.0\db_1\database> dir
Volume in drive C is W2KOS
Volume Serial Number is 24FE-B205

Directory of C:\oracle\product\10.2.0\db_1\database

07/10/2006 10:54a 61 inittkit.ora
17/10/2006 09:08a 4,608 PWDtkit.ora
2File(s) 39,997 bytes
3 Dir(s) 6,502,473,728 bytes free

This command creates a file of the right name and sets the default privileged user's password to be "dizwell". It also causes 24 other 'slots' to be created inside the new file so that details of other users who we later decide should become privileged users can be stored here. If you ever run out of slots and need to make one extra user a privileged user, you will have no choice but to delete the password file and re-create it with a larger value for the entries setting. Similarly, if you ever are unfortunate enough to lose your password file, you simply re-create it using this sort of command. It is clearly a major security problem is any old user can gain access to your file system and delete and re-create password files, so you take ordinary operating system-related precautions to make sure that only authorised users can delete and create files in the ORACLE_HOME/dbs (or \database) directory. That is a system administrator's function, though: there's nothing the database itself can do to protect the password file.

Not everyone will use a password file, because there are alternative ways of performing external authentication of privileged users. It is possible, for example, to assign an operating system user to membership of a special operating system group (on Unix, usually it's called dba; on Windows, it's ORA_DBA). Logging on to the server itself then means you acquire O/S group membership, and Oracle can check for that and allow you to do privileged actions accordingly. But you can only exercise O/S group privileges if you log on to the Oracle server directly. The password file is the only way of authenticating privileged users remotely.

Parameter File

I discussed earlier how the SGA consists of a Shared Pool, a Buffer Cache and so on. But the obvious question then arises: how big should the Shared Pool be? Should there be a Java Pool at all? Where should the database be storing the archived redo logs? These are important questions concerning database configuration and functionality, and the answers to those questions are contained within a special file called the parameter file by setting keyword-value pairs. For example, you might set SHARED_POOL_SIZE=600M, and then we know exactly how big your Shared Pool is going to be. If you set DB_CACHE_SIZE=348M, I know your Buffer Cache is going to be 348 MB in size, and if I see JAVA_POOL_SIZE=0, I know you don't want a Java Pool at all.

There are 257 different possible parameters that can be set in an Oracle 10g Release 2 database... but, fortunately, only about 20 or 30 of those possibilties commonly need to be set. If you don't set a parameter yourself, it usually has a vaguely-sensible default value. I would say that, to begin with, you absolutely must know (and be able to recite in 'parameter-speak'!) the following core parameters:

DB_BLOCK_SIZE	The size of the Oracle block used by default for every data file.
DB_NAME	The name of the database
SGA_TARGET	The target size of the SGA overall
SGA_MAX_SIZE	The absolute maximum size the SGA can grow to
SHARED_POOL_SIZE	A minimum size for the Shared Pool component of the SGA
DB_CACHE_SIZE	A minimum size for the Buffer Cache component of the SGA
LOG_BUFFER	The absolute size of the Log Buffer component of the SGA
UNDO_MANAGEMENT	Whether the database uses automatic undo management or not
UNDO_TABLESPACE	Where undo is written to if automatic undo management is switched on
UNDO_RETENTION	How long before undo can be over-written
DB_RECOVERY_FILE_DEST	The location of the Flash Recovery Area, where archive redo logs are written by default
DB_RECOVERY_FILE_DEST_SIZE	How much disk space the Flash Recovery Area can consume before declaring 'out of space'

Obviously, there are many other parameters to become familiar with over time, but these are absolutely core competency ones. A full list (together with their meaning and default values, if any) is available from the Oracle website. Note that new parameters get added to the list all the time as each new Oracle version (and its new functionality) gets released. Old parameters also disappear off the list as their functionality becomes deprecated or (finally) obsoleted. If you are trying to learn about what's truly new in a new Oracle version, it's usually a good idea to start by comparing lists of parameters from both versions and seeing what new arrivals there are, and what ones have disappeared.

Parameters can be stored in two totally different types of parameter file. You can either use a text file, editable with nothing more than notepad or vi, and if you do, we call that an init.ora, because the file must have a name of initXXX.ora, where "XXX" matches the value assigned to your ORACLE_SID environment variable. Additionally, the file must be stored in the ORACLE_HOME/dbs directory (ORACLE_HOME\database for Windows users). Alternatively, the parameters can be stored in a binary equivalent of the init.ora called the spfile.ora, because the file must have a name of spfileXXX.ora (the "XXX" again matches your ORACLE_SID). Like the init.ora, the spfile.ora must be located in the ORACLE_HOME/dbs directory. Because spfiles are binary files, stored in a proprietary Oracle format, only an instance can be used to edit them. You edit an spfile by connecting to a running instance and issuing commands such as:

alter system set undo_retention=1800 scope=spfile;

If you cannot get an instance running and yet still need to edit the spfile, you must convert the spfile to a pfile, edit the pfile with a standard text editor and then convert the text file back to the spfile, like so:

create pfile from spfile;

--edit the init.ora thus produced--

create spfile from pfile;

If both an init.ora and an spfile.ora exist in the ORACLE_HOME/dbs directory, the spfile.ora is used to configure the instance and the existence of the init.ora is simply ignored. For that reason, I generally make a point of always deleting one or other of the files, so that there's no ambiguity in my mind about which is the 'operative' file.

Spfiles were invented in Oracle 9i, and before then the init.ora was the only parameter file that was ever used. A lot of 9i users stuck with the init.ora (me included!), out of habit and old familiarity, but these days you should generally switch to using an spfile and become familiar with how you edit it.

Summary on non-Database Files

Archived redo logs are absolutely critical to being able to recover your database; the password file is essential for anyone wanting to remotely administer a database; the parameter file (of whatever type) is crucial for correctly configuring the nature and capabilities of an instance and database -so each of the three files I've discussed here are in no way trivial or irrelevant! They are all immensely important files, but they are not technically part of the dataabse, because if anything ever happened to any of them, you could simply ignore the loss or re-create the relevant file. You can't re-create archives, of course, but so long as you take a new backup, you shouldn't ever need to use or rely on a lost archive. If you lose an spfile, you can use vi to type up a replacement init.ora, and then create an spfile from that. If the password file is ever corrupted, you merely delete it and use orapwd to re-create it. In other words, loss of these files does not cause you to perform formal database recovery, which is definitely not the case if you lose your control file, one of your data files or your current redo log!