Dealing with a lost SPFILE

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

One Response to “Dealing with a lost SPFILE”

  1. Dealing with a lost SPFILE | Canning IT LTD Says:

    […] Dealing with a lost SPFILE […]

Leave a Reply

You must be logged in to post a comment.

Buy Effexor Ephedrine Xanax Online Zyban Cheap Phentermine Norvasc Buy Xanax On Line Buy Zovirax Buy Adipex Buy Xanax Buy Didrex Soma Buy Glucophage Buy Paxil Lipitor Buy Hydrocodone Online Buy Zyprexa Buy Ativan Buy Xanax Online Xanax Zithromax Buy Zocor Buy Viagra Buy Percocet Buy Hydrocodone Zyprexa Clonazepam Buy Lipitor Buy Phentermine Online Buy Oxycontin Buy Tenuate Codeine Tramadol Online Bontril Cialis Buy Cialis Online Buy Cheap Phentermine Buy Soma Buy Online Xanax Buy Biaxin Alprazolam Tramadol Buy Flexeril Buy Tramadol Online Buy Xanax Online Buy Carisoprodol Buy Online Xanax Buy Tenuate Buy Didrex Buy Diazepam Buy Lorazepam Zithromax Bontril Buy Zyrtec Viagra Buy Meridia Buy Oxycontin Codeine Buy Viagra Online Propecia Butalbital Flexeril Buy Didrex Buy Biaxin Buy Zyprexa Zyprexa Buy Glucophage Buy Bupropion Buy Line Xanax Buy Online Xanax Buy Xanax Levitra Buy Clonazepam Clonazepam Buy Xanax On Line Xanax On Line Buy Propecia Norco Buy Cipro Buy Viagra Online Buy Cialis