From Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions, Berkeley, Apress, September 2005.

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5

In this chapter, we will examine the eight file types that make up a database and instance. The files associated with an instance are simply

• Parameter files: These files tell the Oracle instance where to find the control files, and they also specify certain initialization parameters that define how big certain memory structures are, and so on. We will investigate the two options available for storing database parameter files.
• Trace files: These are diagnostic files created by a server process generally in response to some exceptional error condition.
• Alert file: This is similar to a trace file, but it contains information about “expected” events, and it also alerts the DBA in a single, centralized file of many database events

The files that make up the database are

• Data files: These files are for the database; they hold your tables, indexes, and all other segments.
• Temp files: These files are used for disk-based sorts and temporary storage.
• Control files: These files tell you where the data files, temp files, and redo log files are, as well as other relevant metadata about their state.
• Redo log files: These are your transaction logs.
• Password files: These files are used to authenticate users performing administrative activities over the network. We will not discuss these files in any detail.

Starting in Oracle 10g, there are a couple of new optional file types that are used by Oracle to facilitate faster backup and faster recovery operations. These two new files are

• Change tracking file: This file facilitates a true incremental backup of Oracle data. It does not have to be located in the Flash Recovery Area, but as it relates purely to database backup and recovery we’ll discuss it in the context of that area.
• Flashback log files: These files store “before images” of database blocks in order to facilitate the new FLASHBACK DATABASE command.

We’ll also take a look at other types of files commonly associated with the database, such as

• Dump (DMP) files: These files are generated by the Export database utility and consumed by the Import database utility.
• Data Pump files: These files are generated by the new Oracle 10g Data Pump Export process and consumed by the Data Pump Import process. This file format may also be created and consumed by external tables.
• Flat files: These are plain old files you can view in a text editor. You normally use these for loading data into the database.

The most important files in the previous lists are the data files and the redo log files, because they contain the data you worked so hard to accumulate. I can lose any and all of the remaining files and still get to my data. If I lose my redo log files, I may start to lose some data. If I lose my data files and all of their backups, I’ve definitely lost that data forever.

We will now take a look at the types of files and what we might expect to find in them.

Parameter Files

There are many different parameter files associated with an Oracle database, from a tnsnames.ora file on a client workstation (used to “find” a server on the network), to a listener.ora file on the server (for the network listener startup), to the sqlnet.ora, cman.ora, and ldap.ora files, to name a few. The most important parameter file, however, is the database’s parameter file — without this, we cannot even get a database started. The remaining files are important; all of them are related to networking and getting connected to the database. However, they are beyond the scope of our discussion. For information on their configuration and setup, I refer you to the Net Services Administrator’s Guide. Typically as a developer, these files would be set up for you, not by you.

The parameter file for a database is commonly known as an init file, or an init.ora file. This is due to its historic default name, which is init<ORACLE_SID>.ora. I term it the “historic” default name because starting with Oracle9i Release 1, a vastly improved method of storing parameter settings for the database was introduced: the server parameter file, or simply SPFILE. This file has the default name of spfile<ORACLE_SID>.ora. We’ll take a look at both kinds of parameter files in turn.

Note: If you’re unfamiliar with the term SID or ORACLE_SID, a full definition is called for. The SID is a site identifier. It and ORACLE_HOME (where the Oracle software is installed) are hashed together in UNIX to create a unique key name for attaching an SGA. If your ORACLE_SID or ORACLE_HOME is not set correctly, you’ll get the ORACLE NOT AVAILABLE error, since you can’t attach to a shared memory segment that is identified by this unique key. On Windows, shared memory isn’t used in the same fashion as UNIX, but the SID is still important. You can have more than one database on the same ORACLE_HOME, so you need a way to uniquely identify each one, along with their configuration files.

Without a parameter file, you cannot start an Oracle database. This makes the parameter file fairly important, and as of Oracle9i Release 2 (versions 9.2 and above), the backup and recovery tool Recovery Manager (RMAN) recognizes this file’s importance and will allow you to include the server parameter file (but not the legacy init.ora parameter file type) in your backup set. However, since it is simply a plain text file, which you can create with any text editor, it is not a file you have to necessarily guard with your life. You can re-create it, as long as you know what was in it (e.g., you can retrieve that information from the database’s alert log, if you have access to that).

We will now examine each type of parameter file (init.ora and SPFILE) in turn, but before we do that, let’s see what a database parameter file looks like.

What Are Parameters?

In simple terms, a database parameter may be thought of as a “key” and “value” pair. You saw an important parameter, DB_NAME, in the preceding chapter. The DB_NAME parameter was stored simply as db_name = ora10g. The “key” here is DB_NAME and the “value” is ora10g — that is our key/value pair. In order to see the current value of an instance parameter, you can query the V$ view V$PARAMETER. Alternatively, in SQL*Plus you can use the SHOW PARAMETER command, for example:

sys@ORA10G> select value
  2  from v$parameter
  3  where name = 'pga_aggregate_target';

VALUE
-------------------------------------------------------------------------------
1073741824

sys@ORA10G> show parameter pga_agg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1G

Both outputs show basically the same information, although you can get more information from V$PARAMETER (there are many more columns to choose from than displayed in this example). But SHOW PARAMETER wins for me in ease of use and the fact that it “wildcards” automatically. Notice that I typed in only pga_agg; SHOW PARAMETER adds % to the front and back.

Note: All V$ views and all dictionary views are fully documented in the Oracle Database Reference manual. Please regard that manual as the definitive source of what is available in a given view.

If you counted the number of documented parameters that you may set in each of the database versions 9.0.1, 9.2.0, and 10.1.0, you would probably find 251, 258, and 255 different parameters, respectively (I’m sure there could be additional parameters available on an operating system–by–operating system basis). In other words, the number of parameters (and their names) varies by release. Most parameters, like DB_BLOCK_SIZE, are very long lived (they won’t go away from release to release), but over time many other parameters become obsolete as implementations change.

For example, in Oracle 9.0.1 there was a DISTRIBUTED_TRANSACTIONS parameter that could be set to some positive integer and that controlled the number of concurrent distributed transactions the database could perform. It was available in prior releases, but it is not found in any release subsequent to 9.0.1 and, in fact, attempting to use it with subsequent releases will raise an error:

ops$tkyte@ORA10G> alter system set distributed_transactions = 10;
alter system set distributed_transactions = 10
*
ERROR at line 1:
ORA-25138: DISTRIBUTED_TRANSACTIONS initialization parameter has been made
obsolete

If you would like to review these parameters and get a feeling for what is available and what each parameter does, you should refer to the Oracle Database Reference manual. The first chapter of this manual examines each and every documented parameter in detail. I would like to point out that in general, the default value assigned to these parameters (or the derived value for parameters that obtain their default settings from other parameters) is sufficient for most systems. In general, the values of parameters, such as the CONTROL_FILES parameter (which specifies the location of the control files on your system), DB_BLOCK_SIZE, and various memory-related parameters, need to be set uniquely for each database.

Notice I used the term “documented” in the preceding paragraph. There are undocumented parameters as well. You can identify these by the fact that their names begin with an underscore (_). There is a great deal of speculation about these parameters. Since they are undocumented, some people believe they must be “magical,” and many people assume that they are well known and used by Oracle “insiders.” In fact, I find the opposite to be true. They are not well known and they are hardly ever used. Most of these undocumented parameters are rather boring, actually, as they represent deprecated functionality and backwardcompatibility flags. Others help in the recovery of data, not of the database itself; for example, some of them enable the database to start up in certain extreme circumstances, but only long enough to get data out. You have to rebuild after that.

Unless you are so directed by Oracle Support, there is no reason to have an undocumented parameter in your configuration. Many have side effects that could be devastating. In my development database, I use only one undocumented setting, if any:

_TRACE_FILES_PUBLIC = TRUE

This parameter makes trace files readable by all, not just the DBA group. On my development database, I want my developers to use SQL_TRACE, TIMED_STATISTICS, and the TKPROF utility frequently (well, I demand it actually); hence they need to be able to read the trace files. With the advent of external tables in Oracle 9.0.1 and above, we’ll see that we need not use even this parameter to permit access to trace files.

In my production database, I don’t use any undocumented settings. In fact, the seemingly “safe” undocumented parameter just mentioned can have undesirable side effects in a live system. Think about the sensitive information that you might find in a trace file, such as SQL and even data values (see the upcoming section titled “Trace Files”), and ask yourself, “Do I really want any end user to have read access to that data?” The answer is most likely no.

Caution: Use undocumented parameters only at the request of Oracle Support. Their use can be damaging to a database, and their implementation can-and will-change from release to release.

You may set the various parameter values in one of two ways: either for the current instance or persistently. It is up to you make sure that the parameter files contain the values you want them to. When using legacy init.ora parameter files, this is a manual process. To change a parameter value persistently when using an init.ora file, to have that new setting be in place across server restarts, you must manually edit and modify the init.ora parameter file. With server parameter files, you’ll see that this has been more or less fully automated for you in a single command.

Legacy init.ora Parameter Files

The legacy Oracle init.ora file is a very simple file in terms of its construction. It is a series of variable key/value pairs. A sample init.ora file might look like this:

db_name = "ora9ir2"
db_block_size = 8192
control_files = ("C:\oradata\control01.ctl", "C:\oradata\control02.ctl")

In fact, this is pretty close to the most basic init.ora file that you could get away with in real life. If I had a block size that was the default on my platform (the default block size varies by platform), I could remove that. The parameter file is used at the very least to get the name of the database and the location of the control files. The control files tell Oracle the location of every other file, so they are very important to the “bootstrap” process of starting the instance.

Now that you know what these legacy database parameter files are and where to get more details about the valid parameters that you can set, the last thing you need to know is where to find them on disk. The naming convention for this file by default is

init$ORACLE_SID.ora     (Unix environment variable)
init%ORACLE_SID%.ora    (Windows environment variable)

and by default it will be found in

$ORACLE_HOME/dbs        (Unix)
%ORACLE_HOME%\DATABASE  (Windows)

It is interesting to note that, in many cases, you will find the entire contents of this parameter file to be something like

IFILE='C:\oracle\admin\ora10g\pfile\init.ora'

The IFILE directive works in a similar fashion to an #include in C. It includes in the current file the contents of the named file. The preceding directive includes an init.ora file from a nondefault location.

It should be noted that the parameter file does not have to be in any particular location. When starting an instance, you may use the pfile=filename option to the startup command. This is most useful when you would like to try out different init.ora parameters on your database to see the effects of having different settings.

Legacy parameter files can be maintained by using any plain text editor. For example, on UNIX/Linux, I would use vi; on the many Windows operating system versions, I would use Notepad; and on a mainframe, I would perhaps use Xedit. It is important to note that you are fully responsible for editing and maintaining this file. There are no commands within the Oracle database itself that you can use to maintain the values contained in the init.ora file. For example, when you use the init.ora parameter file, the issue of an ALTER SYSTEM command to change the size of an SGA component would not be reflected as a permanent change in the init.ora file. If you would like that change to be made permanent — in other words, if you would like it to be the default for subsequent restarts of the database — it is up to you to make sure all init.ora parameter files that might be used to start this database are manually updated.

The last interesting point of note is that the legacy parameter file is not necessarily located on the database server. One of the reasons the stored parameter that we’ll discuss shortly was introduced was to remedy this situation. The legacy parameter file must be present on the client machine attempting to start the database, meaning that if you run a UNIX server, but administer it using SQL*Plus installed on your Windows desktop machine over the network, then you would need the parameter file for the database on your desktop.

I still remember how I made the painful discovery that the parameter files are not stored on the server. This goes back many years to when a brand-new tool called SQL*DBA was introduced. This tool allowed us to perform remote operations (specifically, remote administrative operations). From my server (running SunOS at the time), I was able to connect remotely to a mainframe database server. I was also able to issue the “shutdown” command. However, it was at that point I realized that I was in a bit of a jam — when I tried to start up the instance, SQL*DBA would complain about not being able to find the parameter file. I learned that these parameter files — the init.ora plain text files — were located on the machine with the client, not on the server. SQL*DBA was looking for a parameter file on my local system with which to start the mainframe database. Not only did I have no such file, but I also had no idea what to put into one to get the system started up again! I didn’t know the db_name or control file locations (even just getting the correct naming convention for the mainframe files would have been a bit of stretch), and I didn’t have access to log into the mainframe system itself. I’ve not made that same mistake since; it was a painful lesson to learn.

When DBAs realized that the init.ora parameter file had to reside on the client’s machine that starts the database, it led to a proliferation of these files. Every DBA wanted to run the administrative tools from his desktop, and so every DBA needed a copy of the parameter file on his desktop machine. Tools such as Oracle Enterprise Manager (OEM) would add yet another parameter file to the mix. These tools would attempt to centralize the administration of all databases in an enterprise on a single machine, sometimes referred to as a “management server.” This single machine would run software that would be used by all DBAs to start up, shut down, back up, and otherwise administer a database. That sounds like a perfect solution: centralize all parameters files in one location and use the GUI tools to perform all operations. But the reality is that sometimes it was much more convenient to issue the administrative startup command from within SQL*Plus on the database server machine itself during the course of some administrative task, so we ended up with multiple parameter files again: one on the management server and one on the database server. These parameter files would then proceed to get out of sync with each other, and people would wonder why the parameter change they made last month “disappeared,” but seemingly randomly made a reappearance.

Enter the server parameter file (SPFILE), which can now be a single source of truth for the database.

Server Parameter Files (SPFILEs)

SPFILEs represent a fundamental change in the way Oracle accesses and now maintains parameter settings for the instance. An SPFILE removes the two serious issues associated with legacy parameter files:

• It removes the proliferation of parameter files. An SPFILE is always stored on the database server; the SPFILE must exist on the server machine itself and cannot be located on the client machine. This makes it practical to have a single source of “truth” with regard to parameter settings.
• It removes the need (in fact, it removes the ability) to manually maintain parameter files using text editors outside of the database. The ALTER SYSTEM command allows you to write values directly into the SPFILE. Administrators no longer have to find and maintain all of the parameter files by hand.

The naming convention for this file by default is

spfile$ORACLE_SID.ora    (Unix environment variable)
spfile%ORACLE_SID%.ora   (Windows environment variable)

I strongly recommend using the default location; doing otherwise defeats the simplicity SPFILEs represent. When an SPFILE is in its default location, everything is more or less done for you. Moving the SPFILE to a nondefault location involves you having to tell Oracle where to find the SPFILE, leading to the original problems of legacy parameter files all over again!

Converting to SPFILEs

Suppose we have a database that is using a legacy parameter file described previously. The move to an SPFILE is quite simple; we use the CREATE SPFILE command.

Note: You can also use a “reverse” command to create a parameter file (PFILE) from an SPFILE. (I’ll explain shortly why you might want to do that.)

So, assuming we are using an init.ora parameter file, and that init.ora parameter file is in fact in the default location on the server, we simply issue the CREATE SPFILE command and restart our server instance:

sys@ORA10G> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
sys@ORA10G> create spfile from pfile;
File created.

sys@ORA10G> startup force;
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                   780300 bytes
Variable Size             166729716 bytes
Database Buffers          436207616 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/ora10g/dbs/spfileora10g.ora

The SHOW PARAMETER command was used here to show that initially we were not using an SPFILE, but after we created one and restarted the instance, we were in fact using one and it had the default name.

Note: In a clustered environment, using Oracle RAC, all instances share the same SPFILE, so this process of converting over to an SPFILE from a PFILE should be done in a controlled fashion. The single SPFILE can contain all of the parameter settings, even instance-specific settings in the single SPFILE, but you will have to merge all of the necessary parameter files into a single PFILE using the format that follows.

In a clustered environment, in order to convert from using individual PFILEs to a common SPFILE shared by all, you would merge your individual PFILEs into a single file resembling this:

*.cluster_database_instances=2
*.cluster_database=TRUE
*.cluster_interconnects='10.10.10.0'
*.compatible='10.1.0.2.0'
*.control_files='/ocfs/O10G/control01.ctl','/ocfs/O10G/control02.ctl'
*.db_name='O10G'
...
*.processes=150
*.undo_management='AUTO'
O10G1.instance_number=1
O10G2.instance_number=2
O10G1.local_listener='LISTENER_O10G1'
O10G2.local_listener='LISTENER_O10G2'
O10G1.remote_listener='LISTENER_O10G2'
O10G2.remote_listener='LISTENER_O10G1'
O10G1.thread=1
O10G2.thread=2
O10G1.undo_tablespace='UNDOTBS1'
O10G2.undo_tablespace='UNDOTBS2'

That is, parameter settings that are common to all instances in the cluster would start with *.. Parameter settings that are specific to a single instance, such as the NSTANCE_NUMBER and the THREAD of redo to be used, are prefixed with the instance name (the Oracle SID). In the preceding example,

• The PFILE would be for a two-node cluster with instances named O10G1 and O10G2.
• The *.db_name = 'O10G' assignment indicates that all instances using this SPFILE will be mounting a database named O10G.
• O10G1.undo_tablespace='UNDOTBS1' indicates that the instance named O10G1 will use that specific undo tablespace, and so on.

Setting Values in SPFILEs

Once our database is up and running on the SPFILE, the next question relates to how we set and change values contained therein. Remember, SPFILEs are binary files and we cannot just edit them using a text editor. The answer is to use the ALTER SYSTEM command, which has the following syntax (portions in <> are optional, and the presence of the pipe symbol indicates “one of the list”):

Alter system set parameter=value <comment='text'> <deferred>
                 <scope=memory|spfile|both> <sid='sid|*'>

The ALTER SYSTEM SET command, by default, will update the currently running instance and make the change to the SPFILE for you, greatly easing administration and removing the problems that arose when parameter settings were made via the ALTER SYSTEM command, but you forgot to update or missed an init.ora parameter file.

With that in mind, let’s take a look at each element of the command:

• The parameter=value assignment supplies the parameter name and the new value for the parameter. For example, pga_aggregate_target = 1024m would set the PGA_AGGREGATE_TARGET parameter to a value of 1,024MB (1GB).
• comment='text' is an optional comment we may associate with this setting of the parameter. The comment will appear in the UPDATE_COMMENT field of the V$PARAMETER view. If we use the options to also save the change to the SPFILE, the comment will be written into the SPFILE and preserved across server restarts as well, so future restarts of the database will see the comment.
• deferred specifies whether the system change takes place for subsequent sessions only (not currently established sessions, including the one making the change). By default, the ALTER SYSTEM command will take effect immediately, but some parameters cannot be changed “immediately” — they can be changed only for newly established sessions. We can use the following query to see what parameters mandate the use of deferred:

        ops$tkyte@ORA10G> select name
          2  from v$parameter
          3  where ISSYS_MODIFIABLE = 'DEFERRED';

        NAME
        ------------------------------
        backup_tape_io_slaves
        audit_file_dest
        object_cache_optimal_size
        object_cache_max_size_percent
        sort_area_size
        sort_area_retained_size
        olap_page_pool_size

        7 rows selected.

The code shows that SORT_AREA_SIZE is modifiable at the system level, but only in a deferred manner. The following code shows what happens if we try to modify its value with and without the deferred option:

        ops$tkyte@ORA10G> alter system set sort_area_size = 65536;
        alter system set sort_area_size = 65536
                                              *
        ERROR at line 1:
        ORA-02096: specified initialization parameter is not modifiable with this
        option
        ops$tkyte@ORA10G> alter system set sort_area_size = 65536 deferred;
        System altered.

• SCOPE=MEMORY|SPFILE|BOTH indicates the “scope” of this parameter setting. We have the choice of setting the parameter value with the following:
• SCOPE=MEMORY changes it in the instance(s) only; it will not survive a database restart. The next time you restart the database, the setting will be whatever it was before the change.
• SCOPE=SPFILE changes the value in the SPFILE only. The change will not take place until the database is restarted and the SPFILE is processed again. Some parameters may only be changed using this option — for example, the processes parameter must use SCOPE=SPFILE, as we cannot change the active instance value.
• SCOPE=BOTH means the parameter change takes place both in memory and in the SPFILE. The change will be reflected in the current instance and, the next time you restart, this change will still be in effect. This is the default value for scope when using an SPFILE. When using an init.ora parameter file, the default and only valid value is SCOPE=MEMORY.
• sid='sid|*' is mostly useful in a clustered environment; sid='*' is the default. This allows you to specify a parameter setting uniquely for any given instance in the cluster. Unless you are using Oracle RAC, you will not need to specify the sid= setting.

A typical use of this command is simply

ops$tkyte@ORA10G> alter system set pga_aggregate_target=1024m;
System altered.

or, better yet, perhaps, using the COMMENT= assignment to document when and why a particular change was made:

ops$tkyte@ORA10G> alter system set pga_aggregate_target=1024m
  2  comment = 'changed 01-jan-2005 as per recommendation of George';

System altered.

ops$tkyte@ORA10G> select value, update_comment
  2  from v$parameter
  3  where name = 'pga_aggregate_target';

VALUE
-------------------------------------------------------------------------------
UPDATE_COMMENT
-------------------------------------------------------------------------------
1073741824
changed 01-jan-2005 as per recommendation of George

Unsetting Values in SPFILEs

The next question that arises is, “OK, so we set a value, but we would now like to ‘unset’ it — in other words, we don’t want that parameter setting in our SPFILE at all, and we would like it removed. Since we cannot edit the file using a text editor, how do we accomplish that?” This, too, is done via the ALTER SYSTEM command, but using the RESET clause:

Alter system reset parameter <scope=memory|spfile|both> sid='sid|*'

Here the SCOPE/SID settings have the same meaning as before, but the SID= component is not optional. The documentation in the Oracle SQL Reference manual is a bit misleading on this particular command, as it seems to indicate that it is only valid for RAC (clustered) databases. In fact, it states the following:

“The alter_system_reset_clause is for use in a Real Application Clusters environment.”

Later, it does go on to state

“In a non-RAC environment, you can specify SID='*' for this clause.”

But this is a bit confusing. Nonetheless, this is the command we would use to “remove” a parameter setting from the SPFILE, allowing it to default. So, for example, if we wanted to remove the SORT_AREA_SIZE, to allow it to assume the default value we specified previously, we could do so as follows:

sys@ORA10G> alter system reset sort_area_size scope=spfile sid='*';
System altered.

The SORT_AREA_SIZE is removed from the SPFILE, a fact you can verify by issuing the following:

sys@ORA10G> create pfile='/tmp/pfile.tst' from spfile;
File created.

You can then review the contents of /tmp/pfile.tst, which will be generated on the database server. You will find the SORT_AREA_SIZE parameter does not exist in the parameter files anymore.

Creating PFILEs from SPFILEs

The CREATE PFILE...FROM SPFILE command from the previous section is the opposite of CREATE SPFILE. It takes the binary SPFILE and creates a plain text file from it — one that can be edited in any text editor and subsequently used to start up the database. You might use this command for at least two things on a regular basis:

• To create a “one-time” parameter file used to start up the database for maintenance, with some special settings. So, you would issue CREATE PFILE...FROM SPFILE and edit the resulting text PFILE, modifying the required settings. You would then start up the database, using the PFILE=<FILENAME> option to specify use of your PFILE instead of the SPFILE. After you are finished, you would just start up normally, and the database would use the SPFILE.
• To maintain a history of commented changes. In the past, many DBAs heavily commented their parameter files with a change history. If they changed the size of the buffer cache 20 times over the period of a year, for example, they would have 20 comments in front of the db_cache_size init.ora parameter setting, stating the date and reason for making the change. The SPFILE does not support this, but you can achieve the same effect if you get into the habit of doing the following:

        sys@ORA10G> create pfile='init_01_jan_2005_ora10g.ora' from spfile;
        File created.

        sys@ORA10G> !ls -l $ORACLE_HOME/dbs/init_*
        -rw-rw-r-- 1 ora10g ora10g 871 Jan 1 17:04 init_01_jan_2005_ora10g.ora
        sys@ORA10G> alter system set pga_aggregate_target=1024m
        2  comment = 'changed 01-jan-2005 as per recommendation of George';

In this way, your history will be saved in the series of parameter files over time.

Fixing Corrupted SPFILEs

The last question that comes up with regard to SPFILEs is, “SPFILEs are binary files, so what happens if one gets corrupted and the database won’t start? At least the init.ora file was just text, so we could edit it and fix it.” Well, SPFILEs shouldn’t go corrupt any more than should a data file, redo log file, control file, and so forth. However, in the event that one does, you have a couple of options.

First, the amount of binary data in the SPFILE is very small. If you are on a UNIX platform, a simple strings command will extract all of your settings:

[tkyte@localhost dbs]$ strings spfile$ORACLE_SID.ora
*.compatible='10.1.0.2.0'
*.control_files='/home/ora10g/oradata/ora10g/control01.ctl','/home/ora10g/oradata/or
a10g/control02.ctl','/home/ora10g/oradata/ora10g/control03.ctl'
...
*.user_dump_dest='/home/ora10g/admin/ora10g/udump'

On Windows, simply open the file with write.exe (WordPad). WordPad will display for you all of the clear text in the file, and a simple cut and paste into init<ORACLE_SID>.ora will allow you to create a PFILE you can use to start your instance.

In the event that the SPFILE has just “gone missing” (for whatever reason — not that I have seen an SPFILE disappear), you can also resurrect the information for your parameter file from the database’s alert log (more information on the alert log shortly). Every time you start the database, the alert log will contain a section like this:

System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 67108864
  large_pool_size          = 8388608
  java_pool_size           = 33554432
  control_files            = C:\oracle\oradata\ora9ir2w\CONTROL01.CTL,
                             C:\oracle\oradata\ora9ir2w\CONTROL02.CTL,
                             C:\oracle\oradata\ora9ir2w\CONTROL03.CTL
....
  pga_aggregate_target     = 25165824
  aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3

From this section, you can easily create a PFILE to be converted into a new SPFILE using the CREATE SPFILE command.

Parameter File Wrap-Up

In this section, we covered all of the basics of managing Oracle initialization parameters and parameter files. We looked at how to set parameters, view parameter values, and have those settings persist across database restarts. We explored the two types of database parameter files: legacy PFILEs (simple text files) and SPFILEs. For all existing databases, using SPFILEs is recommended for the ease of administration and clarity they bring to the table. The ability to have a single source of parameter “truth” for the database, coupled with the ability of the ALTER SYSTEM command to persist the parameter values, make SPFILEs a compelling feature. I started using them the instant they became available and haven’t looked back.

--

Thomas Kyte is the Vice President of the Core Technologies Group at Oracle Corporation and has been with them since version 7.0.9 was released in 1993. Kyte, however, has been working with Oracle since version 5.1.5c. At Oracle Corp., Kyte works with the Oracle database, and more specifically, he helps clients who are using the Oracle database and works directly with them specifying and building their systems or rebuilding and tuning them. Prior to working at Oracle, Kyte was a systems integrator who built large scale, heterogeneous databases and applications for military and government clients. Tom Kyte is the same “Ask Tom” whose column appears in Oracle Magazine, where he answers questions about the Oracle database and tools that developers and DBAs struggle with every day.