Database Reference
In-Depth Information
•
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, 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:
PUBLIC@ORA12CR1> connect / as sysdba
Connected.
SYS@ORA12CR1> create pfile='init_14_aug_2013_ora12cr1.ora' from spfile;
File created.
SYS@ORA12CR1> alter system set pga_aggregate_target=512m
2 comment = 'Changed 14-aug-2013, AWR recommendation';
System altered.
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 one does—or if you have set a value in your
SPFILE
that does not allow the database to start—you have a couple
of options.
First, the amount of binary data in the
SPFILE
is very small. If you are on a UNIX/Linux platform, a simple
strings
command will extract all of your settings:
[ora12cr1@dellpe dbs]$ strings $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
*.audit_file_dest='/home/ora12cr1/app/ora12cr1/admin
/ora12cr1/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
...
On Windows, simply open the file with
write.exe
(WordPad). WordPad will display all of the clear text in the file,
and by simply cutting and pasting into
init<ORACLE_SID>.ora,
you can create a
PFILE
to use to start your instance.
In the event that the
SPFILE
has just “gone missing” (for whatever reason—not that I've seen an
SPFILE
disappear), you can also resurrect the information for your parameter file from the database's alert log (more on the
alert log shortly). Every time you start the database, the alert log will contain a section like this:
Starting up:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1
System name: Linux
Node name: dellpe
Release: 2.6.39-400.109.1.el6uek.x86_64
Version: #1 SMP Tue Jun 4 23:21:51 PDT 2013
Machine: x86_64
