An oracle instances characteristics are established by parameter specified within the initialization parameter file. The initialization parameters are either stored in PFILE or SPFILE.
A PFILE is static, client side text file, that can be easily updated whenever required. This is normally resides on the server but you need a local copy if you need to start oracle from remote machine where as SPFILE is persistent server side binary file, that cannot modified directly with text editor. You can use ALTER SYSTEM SET command to change the parameter in spfile. It is maintained by the server by checking parameters before any changes (chances of human error reduces). There is no need to have local PFILE copy to start the database from remote machine. You can use following views to check the status of spfile or pfile: V$parameter, V$parameter2, V$spparameter, Show parameters command.
This article describes how to resolve the situation when your database will not start because of spfile corruption due to any reason either someone try to edit the spfile or by saving an invalid string in the spfile and you are not able to mount the database giving the following error:
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-09291: sksachk: invalid device specified for archive destination
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 3) The system cannot find the path specified.
– If you lost the spfile but having pfile (init.ora)
Rename bad or corrupted spfile from the original location. Then shutdown and restart your database with pfile (init.ora)
SQL> STARTUP PFILE=’D:\oracle\admin\sadhan\pfile\initTEST.ora’;
Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.
SQL> Create spfile from pfile=’D:\oracle\admin\sadhan\pfile\initTEST.ora’;
– If you lost or corrupted your spfile as well as pfile and your database is up.
If your database is up then nothing will going to happen as long as you will bounce back your database. Just query with V$parameter and verify the paramter.log to create pfile and restart your database with this pfile. Later you can create spfile from this pfile.
select name,value from v$parameter
where ISDEFAULT=’FALSE’ order by name;
– If you are not able to discover your spfile and pfile both and you database is down.
In that case you have remaining option to create new pfile (consider your controlfile is ok). Just go through the alert.log non-default parameter (all the changes to the init parameters are recorded in the alert.log files) and create new pfile by deleting the unnecessary parameters from the file and copy that file to the desired location and restart the database. Later you can create spfile from that pfile.
compatible=10.2.0.1.0
db_name=ORCL3
In fact only the database name is enough to initialize the database in nomount phase. In that case it will create new controlfile at the default location ORACLE_HOME\DBS\”DBNAME”. However if you provide the controlfile parameter inside the pfile then oracle will attempt to create controlfile in specified location: control_files=’d:\oracle\oradata\orcl3\control01.ctl’,’D:\oracle\oradata\orcl3\control02.ctl’
If you do not give any location of controlfile parameter inside pfile then further startup will fails with ORA-00205: error in identifying control file, check alert.log for more information.
– If you Configured spfile autobackup with RMAN Backup.
If you already configures control & spfile autobackup with your RMAN Backup then RMAN will automatically create backup of control & spfile after each schedule backup. In that case you need only to restore your spfile from autobackup.
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
Note: It is recommended to configure controlfile autobackup as well as have a copy of pfile in different location server.
