In various internet forums you’ll find people asking the question - “I’ve lost my spfile - what do I do now?”
Below is a quick checklist of items that could be used as a starting point to resolve this issue:
1) Are you looking in the correct place for the PFILE/SPFILE?
- In follow-up postings to this one, you’ll see that these files may not always be in the default location of $ORACLE_HOME/dbs on Unix or %ORACLE_HOME%\database on Windows.
2) Use information in the database alert log. A useful new 11g feature is that you can just copy and paste the entries from the alert log that have been written when the database last started up, into a file called INITSID.ORA. (Replacing SID with the name of your database). If you then place this file in the default location, you can use it to start the database. (The entries have been formatted as they were written to the file, so that it can be used in this manner). Even in 9i and 10g, a listing of any non-default parameter file entries is still written to the alert log.
3) If you have previously run any “alter database backup controlfile to trace;” commands, then the output trace file doesn’t contain a full list of all database parameters, but in 11g it will provide you with details of the locations for the CONTROL_FILES parameter, together with a section entitled:
“…current System-scope REDO Log Archival related parameters … can be included in the database initialization file.”
which may be of use.
4) Check your backup location, as there should be a copy of the SPFILE stored in this location.
a) Can you restore it from tape?
b) Do you have a disk-based/SAN-based copy or backup of the files?
- Have you automated scripts which copy the files to a backup location.
- Any previous “Create pfile…” or “Create spfile…” commands could have been used to create backup copies elsewhere.
- If the database is currently open, then you can just run the new 11g commands: “Create spfile from memory;”
alternatively, you may prefer to just query v$parameter/v$spparameter or similar views.
c) Has the SPFILE been backed up by RMAN?
- If using RMAN with autobackup configured, then your spfile will have been backed up automatically along with the controlfile.
- You could also manually extract the SPFILE from the backuppiece using dbms_backup_restore.RestoreSpfileTo
5) Do you have a copy stored within your source control/version control, change management or similar system?
- Ideally all your database scripts and parameter files should be stored within these systems.
6) Oracle Enterprise Manager (OEM)/Grid Control/Database Control offer various options for restoring database files.
7) If your database in running in a clustered, replicated or standby database configuration, could you just copy the files from another site?
(Obviously they will need to be edited before use).
8) Are there other databases you can access that have a similar configuration whose files you could edit and then re-use?
9) If you have a DR site, are there copies of the files on the DR site?
- Do you also have a “hot box” for your DR site? - If so, then you should have electronic and paper-based copies of the files there.
10) At the final section of an Automatic Workload Repository (AWR) or Statspack report there is a section listing database parameters that could be used to re-construct the files. Other Oracle supplied scripts, tools and utilities such as the Remote Diagnostics Agent (RDA), Streams Configuration Report and Health Check script (streams_hc_11GR1.sql) and the RAC Diagnostic Information script (racdiag.sql) also report on parameter file settings.
As a basic guide don’t forget that Oracle also provide an example init.ora file in $ORACLE_HOME/dbs on unix and %ORACLE_HOME%\dbs on Windows.
Note: When editing pfiles manually, it would be good practice to ask someone else to check your work. This is to prevent errors from typing in parameter names incorrectly and also to check that the settings in the file are appropriate for the database.
Oracle Database Administrator’s Guide 11g Release 1 (11.1),232587.1,162491.1,811788.1,378021.1,135714.1,314422.1,273674.1