Recover a Lost or Damaged Server Parameter File (SPFILE)

Before You Begin

This 15-minute tutorial shows you how to recover the lost or corrupt server parameter file (SPFILE).

Background

If your server parameter file is lost or corrupted, then the current instance, or the next attempt at starting the database instance may fail.

One way to recover SPFILE is to create the text initialization parameter file (PFILE) from the parameter value listings in the alert log, and then create SPFILE from the PFILE.

When an instance starts, Oracle Database writes the initialization parameters used for startup to the alert log. You can copy and paste this section from the text version of the alert log (without XML tags) into a new PFILE.

For testing purposes, install and configure Oracle Database 18c using the RPM packages.

When you install Oracle Database 18c on Oracle Linux using an RPM, the RPM creates a script that allows you to create a demo database ORCLCDB with a pluggable database (PDB) ORCLPDB1.

Note that the ORACLE_HOME for the software installation is /opt/oracle/product/18c/dbhome_1.

What Do You Need?

Create PFILE from the parameter value listings in the alert log (alert_SID.log).
Create SPFILE from PFILE.

Create PFILE

Run the grep command to find the ORACLE_HOME directory.

$ . oraenv
ORACLE_SID = [oracle] ? ORCLCDB
The Oracle base has been set to /opt/oracle
$ env|grep -i ORACLE_HOME
ORACLE_HOME=/opt/oracle/product/18c/dbhome_1

Go to the ORACLE_HOME directory /opt/oracle/product/18c/dbhome_1/dbs and run the list command to make sure that the parameter files are there.
```
$ ls
hc_ORCLCDB.dat  init.ora  lkORCLCDB  orapwORCLCDB  spfileORCLCDB.ora
```

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Nov 5 10:43:54 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

Start the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1660940400 bytes
Fixed Size		    8896624 bytes
Variable Size		 1056964608 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.

Query the V$DIAG_INFO view to find the location of the trace files.

Note the path that corresponds to the Diag Trace entry.

SQL> SELECT * FROM V$DIAG_INFO where NAME='Diag Trace';

INST_ID  NAME         VALUE                                         CON_ID
-------  ----------   --------------------------------------------  -------
1        Diag Trace   /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace   0

Disconnect from the database.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Go to the trace files directory and run the list command.
```
$ cd /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace
$ ls
```
Locate the alert_ORCLCDB.log file, which you need to use to extract the initialization parameters and their values.
Open the alert log file in a text editor.
```
$ vi alert_ORCLCDB.log
```
Copy the system parameters that follow, Using parameter settings in server-side spfile /opt/oracle/product/18c/dbhome_1/dbs/spfileORCLCDB.ora System parameters with non-default values: and save them to a text file.
System Parameters

Shut down the database before you perform any operations on the initialization parameter files PFILE and SPFILE.

$ cd /opt/oracle/product/18c/dbhome_1/bin/

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 28 23:33:01 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Go to the dbs directory and run the list command.

$ cd ../
$ cd dbs
$ ls
hc_ORCLCDB.dat init.ora lkORCLCDB orapwORCLCDB spfileORCLCDB.ora

Since we are going to simulate a situation where the SPFILE is corrupt or lost, delete the init.ora and spfleORCLCDB.ora files.
```
$ rm -rf init.ora spfileORCLCDB.ora
```
Create the PFILE.
The name of the PFILE must be in the format, initSID_of_the_database_instance.ora.

In this case, ORCLCDB is the SID of the demo database.
```
$ vi initORCLCDB.ora
```
Copy the content from the text file that you saved earlier, paste, and then save the PFILE.

Connect to the Database Using PFILE

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Nov 5 11:11:52 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

Start the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1660940400 bytes
Fixed Size		    8896624 bytes
Variable Size		 1056964608 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.

Run the show pdbs command to view the list of pluggable databases.
```
SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  MOUNTED
```
The pluggable databases are in the MOUNTED state when you start the database using the initialization parameter file.
Run the ALTER PLUGGABLE statement to change the mode of the PDB from MOUNTED to READ WRITE. Otherwise, you cannot use the PDB ORCLPDB1.
```
SQL> alter pluggable database ORCLPDB1 open;

Pluggable database altered.
```

Run again the show pdbs command.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ WRITE NO

Now, the PDB ORCLPDB1 is in READ WRITE mode.

Create SPFILE

Run the CREATE SPFILE FROM PFILE statement to create the SPFILE in the default directory.
The name of the SPFILE must be in the format, spfileSID_of_the_database_instance.ora.

In this case, ORCLCDB is the SID of the demo database.
```
SQL> CREATE SPFILE FROM PFILE='/opt/oracle/product/18c/dbhome_1/dbs/initORCLCDB.ora';

File created.
```

Shut down the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Because you have created the SPFILE, you can safely delete the PFILE initORCLCDB.ora.
```
$ rm -rf initORCLCDB.ora
```

Connect to the Database using SPFILE

Log in to SQL*Plus again and connect to the database as oracle user to check if you have properly configured the SPFILE spfileORCLCDB.ora.

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Nov 5 11:42:46 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

Start the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1660940400 bytes
Fixed Size		    8896624 bytes
Variable Size		 1056964608 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.

Run the show con_name command to view the current container.
```
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
```
Run the show pdbs command to view the list of pluggable databases.
The pluggable databases are in the MOUNTED state when you start the database using initialization parameter file.
Run the ALTER PLUGGABLE statement to use the PDB ORCLPDB1.

Query the v$instance view to check the state of the current database instance.

SQL> SELECT INSTANCE_NAME, HOST_NAME, VERSION, STATUS FROM V$INSTANCE;

INSTANCE_NAME  HOST_NAME            VERSION     STATUS
-------------  ---------            -------     -------
ORCLCDB        ol7-183.localdomain  18.0.0.0.0  OPEN

Shut down the database.

Recover a Lost or Damaged Server Parameter File

Before You Begin

Background

What Do You Need?

Create PFILE

Connect to the Database Using PFILE

Create SPFILE

Connect to the Database using SPFILE

Want to Learn More?

Associated Learning Paths