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 theORACLE_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
- Log in to SQL*Plus and connect to the database as
oracle
user.$ 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. - 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
andspfleORCLCDB.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
- Log in to SQL*Plus and connect to the database to check if
you have properly configured the 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 fromMOUNTED
toREAD WRITE
. Otherwise, you cannot use the PDBORCLPDB1
.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 inREAD 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 SPFILEspfileORCLCDB.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 PDBORCLPDB1
. - 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.