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.

References:
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 Glucophage Buy Ativan Buy Adipex Buy Fioricet Adderall Levitra Norco Effexor Buy Ambien Buy Zyprexa Buy Lipitor Buy Effexor Butalbital Buy Diflucan Buy Hydrocodone Ephedrine Buy Adipex Buy Zithromax Percocet Buy Tramadol Buy Soma Lortab Lortab Buy Darvocet Flexeril Buy Xanax Online Buy Phentermine Online Buy Oxycontin Buy Effexor Bupropion Nexium Buy Ultram Buy Valium Buy Zyrtec Bontril Buy Cialis Buy Fioricet Ephedrine Buy Cipro Clonazepam Buy Biaxin Buy Xanax Buy Xanax On Line Biaxin Codeine Buy Diflucan Bupropion Cipro Buy Zocor Buy Darvocet Seroquel Buy Adderall Zyrtec Ultracet Buy Ephedrine Bupropion Buy Codeine Buy Clonazepam Buy Diflucan Buy Didrex Valium Online Propecia Buy Lortab Buy Percocet Buy Norco Didrex Ativan Buy Percocet Valium Online Buy Flexeril Ultram Buy Bontril Buy Ultram Buy Online Xanax Glucophage Buy Viagra Online Buy Soma Cheap Phentermine Buy Norvasc Buy Tenuate Valium Online