ORACLE DATABASE

Control File | PFILE | SPFILE

A practical DBA reference — concepts, commands, scenarios, and interview Q&A

This document covers three of the most critical Oracle files: the Control File, the PFILE (initialization
parameter file), and the SPFILE (server parameter file). These files are small, but if any of them is
missing or corrupt, the database doesn't start. They come up constantly in DBA interviews because
problems with them are real production scenarios.

Every section follows the same pattern: explain how it works, show the actual commands, then
cover the interview questions — including scenario-based ones where they describe a broken
situation and ask how you'd fix it.

1. Control File

1.1 What Is the Control File?

The control file is a small binary file that Oracle reads during the MOUNT stage of startup. It
contains the physical map of the database — where the datafiles are, where the redo logs are, and
what the current state of the database is.

Without the control file, the database cannot mount. Period. You can have all your datafiles and
redo logs perfectly intact, but if the control file is missing and you have no backup, you're looking at
a database recreation.

What's stored inside the control file:

• Database name and DBID
• Timestamp of when the database was created
• Names and locations of all datafiles
• Names and locations of all online redo log files
• Current log sequence number
• Checkpoint information and the checkpoint SCN
• RMAN backup metadata (catalog stored in control file if not using a recovery catalog)
• Archive log history (limited — older entries get overwritten based on
CONTROL_FILE_RECORD_KEEP_TIME)

! The control file is updated constantly — every checkpoint, every log switch, every
NOTE
time RMAN records a backup. It's not a static file. If you copy it manually while
 the database is open, you'll get a corrupt control file.

1.2 Control File Location

You can find the control file locations from inside the database:
Finding Control Files
-- From SQL*Plus:
SQL> SHOW PARAMETER control_files

-- Or from the view:

SQL> SELECT name FROM v$controlfile;

-- Full path with status:

SQL> SELECT status, name FROM v$controlfile;

The parameter controlling the locations is CONTROL_FILES in the SPFILE or PFILE. You can have
multiple copies (multiplexing) by listing multiple paths separated by commas.

1.3 Multiplexing the Control File

Multiplexing means keeping multiple copies of the control file on different disks. Oracle writes to all
copies simultaneously and reads from the first one listed. If one copy is lost or on a failed disk,
Oracle keeps running and just alerts you. If all copies are lost, the database crashes.

In production, minimum two copies — ideally three, on separate physical disks or separate storage
paths. This is a basic requirement, not optional.

Always multiplex control files across separate storage paths. Losing all control
TIP files with no backup is one of the most painful recovery scenarios in Oracle. It's
RULE
completely preventable.

How to add a copy (while DB is shut down):

Adding a Control File Copy
-- Step 1: Find current locations
SQL> SHOW PARAMETER control_files

-- Step 2: Shut down the database cleanly

SQL> SHUTDOWN IMMEDIATE;

-- Step 3: Copy the control file at OS level

$ cp /u01/oradata/ORCL/[Link] /u02/oradata/ORCL/[Link]

-- Step 4: Edit the PFILE or SPFILE to include the new path

-- In PFILE (/u01/app/oracle/dbs/[Link]):
control_files =
 '/u01/oradata/ORCL/[Link]','/u02/oradata/ORCL/[Link]'

-- Or via SPFILE before startup:

SQL> STARTUP NOMOUNT;
SQL> ALTER SYSTEM SET control_files=
'/u01/oradata/ORCL/[Link]',
'/u02/oradata/ORCL/[Link]'
SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;

-- Step 5: Start the database

SQL> STARTUP;

1.4 Backing Up the Control File

You should back up the control file any time the physical structure of the database changes —
adding a datafile, adding a redo log group, dropping a tablespace. RMAN includes the control file
automatically in most backup jobs, but you can also back it up manually:

Backing Up the Control File

-- Backup control file to a binary trace (binary copy):
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/backup/[Link]';

-- Backup control file to a text trace (SQL script to recreate):

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-- Creates a .trc file in the diag trace directory

-- Find the trace file location:

SQL> SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Via RMAN (preferred):

RMAN> BACKUP CURRENT CONTROLFILE;

-- Include control file in every DB backup automatically:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Always have CONTROLFILE AUTOBACKUP ON in RMAN. This makes a control

TIP file backup automatically after every backup job and after structural changes. If
RMAN you ever need to restore, the first thing RMAN needs is the control file — you
can't restore without it.

1.5 Recreating a Lost Control File

If all control file copies are lost and you have no RMAN backup, you can recreate it manually using
the CREATE CONTROLFILE command — but you need to know all the datafile and redo log
names. This is why the text trace backup is valuable.
 Recreating Control File from Scratch
-- Startup to NOMOUNT (instance starts, no control file needed):
SQL> STARTUP NOMOUNT;

-- Recreate control file (example):

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
LOGFILE
GROUP 1 '/u01/oradata/ORCL/[Link]' SIZE 200M,
GROUP 2 '/u01/oradata/ORCL/[Link]' SIZE 200M,
GROUP 3 '/u01/oradata/ORCL/[Link]' SIZE 200M
DATAFILE
'/u01/oradata/ORCL/[Link]',
'/u01/oradata/ORCL/[Link]',
'/u01/oradata/ORCL/[Link]',
'/u01/oradata/ORCL/[Link]'
CHARACTER SET AL32UTF8;

-- After CREATE CONTROLFILE, recover and open:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
SQL> ALTER DATABASE OPEN RESETLOGS;

1.6 Key Control File Views

View What it shows
V$CONTROLFILE Paths and status of all control file copies
V$CONTROLFILE_RECORD_ What's stored in each section and how full it is
SECTION
V$DATABASE DB name, creation time, open mode, log mode
V$PARAMETER Shows current value of CONTROL_FILES parameter

2. PFILE — Initialization Parameter File

2.1 What Is the PFILE?

The PFILE (also called [Link]) is a plain text file that contains the initialization parameters Oracle
reads when starting up. This is the old way of managing parameters — before Oracle 9i, this was
the only way. It's still supported and useful in certain situations today.

Default location: $ORACLE_HOME/dbs/[Link] (Linux/Unix) or %ORACLE_HOME%\database\

[Link] (Windows)
Where SID is your ORACLE_SID value. For ORACLE_SID=ORCL, the file would be [Link]

2.2 Sample PFILE Content

Sample [Link]
# [Link] — Sample PFILE
db_name = ORCL
db_block_size = 8192
sga_target = 2G
pga_aggregate_target = 512M
memory_target = 0
db_files = 200
undo_management = AUTO
undo_tablespace = UNDOTBS1
control_files = '/u01/oradata/ORCL/[Link]',
'/u02/oradata/ORCL/[Link]'
log_archive_dest_1 = 'LOCATION=/u03/archivelog/ORCL'
log_archive_format = '%t_%s_%[Link]'
audit_trail = NONE
diagnostic_dest = /u01/app/oracle
processes = 300
open_cursors = 300
remote_login_passwordfile = EXCLUSIVE

2.3 Starting with a PFILE

Starting DB with PFILE
-- Start using default PFILE location ($ORACLE_HOME/dbs/[Link]):
SQL> STARTUP

-- Start using a specific PFILE path:

SQL> STARTUP PFILE='/tmp/initORCL_backup.ora';

-- Start NOMOUNT using specific PFILE (useful for recovery):

SQL> STARTUP NOMOUNT PFILE='/tmp/[Link]';

STARTUP with no arguments looks for the SPFILE first. If SPFILE is not found, it
KEY falls back to PFILE. If neither is found, startup fails with ORA-01078. Knowing this
KNOW
fallback behavior is a common interview question.

2.4 PFILE Limitations

• Text file — changes take effect only after restart (no dynamic changes)
 • Not shared in RAC — each node needs its own PFILE or they all need to point to a shared
SPFILE
• No history of changes — if someone edits it manually, there's no audit trail
• Easy to corrupt — a typo breaks the startup
• No ALTER SYSTEM persistence — parameter changes made with ALTER SYSTEM don't
write back to the PFILE

3. SPFILE — Server Parameter File

3.1 What Is the SPFILE?

The SPFILE (Server Parameter File) is the binary version of the PFILE. It was introduced in Oracle
9i and is now the standard way to manage initialization parameters. Unlike PFILE, you never edit it
directly — you always use ALTER SYSTEM commands, and Oracle writes to the file for you.

Default location: $ORACLE_HOME/dbs/[Link] (Linux/Unix)

For ORACLE_SID=ORCL, the file is: [Link]

3.2 SPFILE vs PFILE — Quick Comparison

Feature PFILE
Feature PFILE SPFILE
Format Plain text Binary
Edit directly Yes (vi, notepad) No — use ALTER SYSTEM
only
Dynamic changes persist No — restart required Yes — SCOPE=BOTH or
SPFILE
RAC support Each node needs own file Single shared file for all nodes
ALTER SYSTEM writes No Yes
back
Corruption risk Manual typos Only via ALTER SYSTEM
Default search order Second (fallback) First
Good for recovery ops Yes (easy to edit) Use CREATE PFILE first

3.3 ALTER SYSTEM — SCOPE Options

When you change a parameter with ALTER SYSTEM, you specify the SCOPE to control where the
change is written:
 SCOPE What it does
SCOPE=MEMORY Change takes effect immediately in the running instance only. Not
written to SPFILE — lost on next restart.
SCOPE=SPFILE Written to SPFILE only. Takes effect after the next restart. Instance is
not changed now.
SCOPE=BOTH Change takes effect now AND is written to SPFILE. Persists across
restarts. This is the default when on SPFILE.

ALTER SYSTEM with SCOPE Examples

-- Change SGA_TARGET immediately AND persist across restarts:
SQL> ALTER SYSTEM SET sga_target = 3G SCOPE=BOTH;

-- Increase processes — static parameter, can't change in MEMORY:

SQL> ALTER SYSTEM SET processes = 400 SCOPE=SPFILE;
-- Requires restart to take effect

-- Emergency: change in memory only (troubleshooting, temp fix):

SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u04/arch' SCOPE=MEMORY;

-- Reset a parameter back to default:

SQL> ALTER SYSTEM RESET open_cursors SCOPE=BOTH SID='*';

-- Check current value and source (SPFILE or default):

SQL> SHOW PARAMETER sga_target
SQL> SELECT name, value, description FROM v$parameter WHERE name =
'sga_target';

-- Check if parameter is in SPFILE (non-null = explicitly set):

SQL> SELECT name, value FROM v$spparameter WHERE name = 'sga_target';

3.4 Creating SPFILE from PFILE and Vice Versa

Converting Between PFILE and SPFILE
-- Create SPFILE from currently running instance parameters:
SQL> CREATE SPFILE FROM MEMORY;

-- Create SPFILE from a PFILE:

SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/dbs/[Link]';

-- Create SPFILE to a specific location from PFILE:

SQL> CREATE SPFILE='/u01/app/oracle/dbs/[Link]'
FROM PFILE='/tmp/initORCL_clean.ora';

-- Create PFILE from SPFILE (very useful for editing):

SQL> CREATE PFILE='/tmp/initORCL_edit.ora' FROM SPFILE;
 -- Create PFILE from SPFILE at default location:
SQL> CREATE PFILE FROM SPFILE;
-- Creates $ORACLE_HOME/dbs/[Link]

-- Create PFILE from current memory (captures runtime changes too):

SQL> CREATE PFILE='/tmp/initORCL_runtime.ora' FROM MEMORY;

3.5 Checking Which File Oracle Is Using

Checking PFILE vs SPFILE
-- Check if using SPFILE or PFILE:
SQL> SHOW PARAMETER spfile
-- If VALUE is non-empty, you're on SPFILE
-- If VALUE is blank, you're on PFILE

-- Alternative:
SQL> SELECT value FROM v$parameter WHERE name = 'spfile';

-- Also check v$spparameter — populated only when SPFILE is in use:

SQL> SELECT count(*) FROM v$spparameter WHERE value IS NOT NULL;

4. Database Startup — What Oracle Looks For and When

4.1 Parameter File Search Order

When you type STARTUP with no arguments, Oracle searches for the parameter file in this exact
order:

1. SPFILE — Looks for $ORACLE_HOME/dbs/[Link]

2. SPFILE (generic) — Looks for $ORACLE_HOME/dbs/[Link] (no SID in name)
3. PFILE — Looks for $ORACLE_HOME/dbs/[Link]

If none of these are found, Oracle throws ORA-01078: failure in processing system parameters.
The database does not start.

4.2 Startup Stages and What Each Needs

Stage What Oracle Does / What's Required
NOMOUNT Reads parameter file → allocates SGA → starts background
processes. Only needs: PFILE or SPFILE.
MOUNT Reads the control file paths from the parameter file, opens the
control file. Needs: control file at the path specified.
 OPEN Opens datafiles and redo logs, verifies consistency, performs
instance recovery if needed. Needs: all datafiles and redo logs
accessible and consistent.

Startup Commands
-- Full startup (goes through all three stages automatically):
SQL> STARTUP

-- Stop at NOMOUNT (for control file recreation or CREATE DATABASE):

SQL> STARTUP NOMOUNT

-- Stop at MOUNT (for ARCHIVELOG mode change, recovery, file rename):

SQL> STARTUP MOUNT

-- Advance from NOMOUNT to MOUNT:

SQL> ALTER DATABASE MOUNT;

-- Advance from MOUNT to OPEN:

SQL> ALTER DATABASE OPEN;

-- Force open with RESETLOGS (after incomplete recovery):

SQL> ALTER DATABASE OPEN RESETLOGS;

-- Restrict access during startup (only users with RESTRICTED SESSION

privilege):
SQL> STARTUP RESTRICT

-- Startup and force recovery (skip consistency checks — dangerous):

SQL> STARTUP FORCE

5. Real Scenarios — What Breaks and How to Fix It

Scenario 1: SPFILE Is Missing — How Do You Start the Database?

This is one of the most common scenario questions. The SPFILE gets deleted, corrupted, or
someone accidentally overwrote it. STARTUP fails with ORA-01078 or ORA-29283.

SCENARIO: SPFILE missing, database won't start

Situation:
$ sqlplus / as sysdba SQL> STARTUP ORA-01078: failure in processing system parameters
LRS-00200: cannot open parameter file
Step 1 — Check what's available:
 Before doing anything, check if you have: (a) A PFILE at
$ORACLE_HOME/dbs/[Link] (b) A CREATE PFILE backup somewhere (/backup,
/tmp, etc.) (c) An RMAN autobackup of the SPFILE (d) The SPFILE on another node (in
RAC)
Step 2a — If you have a PFILE backup, start with it:
SQL> STARTUP PFILE='/backup/[Link]';

This will bring the DB up. Then recreate the SPFILE: SQL> CREATE SPFILE FROM
MEMORY; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
Step 2b — If you have an RMAN SPFILE autobackup:
RMAN> STARTUP NOMOUNT; RMAN> RESTORE SPFILE FROM AUTOBACKUP; RMAN> SHUTDOWN
IMMEDIATE; RMAN> STARTUP;

Step 2c — If you have NOTHING:

You need to recreate the PFILE from scratch. Build [Link] manually with the minimum
required parameters (db_name, control_files, sga_target, undo_tablespace). Start with
PFILE, then CREATE SPFILE FROM MEMORY once up. This is why every DBA should
keep a PFILE backup somewhere.
Prevention:
Always keep a PFILE copy: SQL> CREATE PFILE='/backup/initORCL_$(date).ora' FROM
SPFILE; Run this after any parameter change.

Scenario 2: Control File Is Lost

SCENARIO: One control file copy is damaged / all copies lost

Situation A — Only one of multiple copies is lost:
Oracle will still be running (if the DB was open) because it has the other copies. But
eventually it will crash or alert you. Fix: 1. Shut down cleanly: SHUTDOWN IMMEDIATE
2. Copy a good control file copy to the missing path at OS level 3. Start up normally:
STARTUP
Situation B — All control file copies are lost, DB was open:
Oracle crashes. On restart, STARTUP MOUNT fails with ORA-00205. Steps: 1. STARTUP
NOMOUNT (succeeds — only needs SPFILE/PFILE) 2. Restore control file from RMAN:
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; 3. ALTER DATABASE
MOUNT; 4. RECOVER DATABASE; (apply any needed archive logs) 5. ALTER
DATABASE OPEN RESETLOGS;
Situation C — All copies lost, no RMAN backup, but you have the TO TRACE backup:
Find the trace file from ALTER DATABASE BACKUP CONTROLFILE TO TRACE. Edit it to
use CREATE CONTROLFILE (not CREATE CONTROLFILE REUSE if the DB doesn't exist
yet in Oracle's view), fill in your datafile and redo log names, run it in NOMOUNT stage, then
recover and open.
 Prevention:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; — this is non-negotiable in
production.

Scenario 3: SPFILE Has a Bad Parameter — Database Won't Start

SCENARIO: Someone set a wrong parameter via ALTER SYSTEM and now the DB
won't start
Situation:
A DBA ran: ALTER SYSTEM SET sga_target=50G SCOPE=SPFILE on a server with 16GB
RAM. After restart, Oracle tries to allocate 50GB SGA and fails immediately.
The fix — start with a temporary PFILE:
1. Create a PFILE from the bad SPFILE (you may need to do this from another instance or
manually): SQL> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/[Link]' If
[Link] doesn't exist, the SPFILE text can sometimes be dumped with strings
command at OS level.
2. Or if you can get to NOMOUNT with the SPFILE before it fails (sometimes possible):
SQL> STARTUP NOMOUNT PFILE='/tmp/[Link]'
3. Best approach — create a PFILE from the SPFILE, then edit it: -- If DB is down and you
can't start it, use strings on the SPFILE binary to see params: $ strings
$ORACLE_HOME/dbs/[Link] | grep sga_target
4. Create a minimal PFILE pointing to control files, start NOMOUNT, then: SQL> CREATE
SPFILE FROM PFILE='/tmp/initORCL_fixed.ora';
Smarter fix — if the DB is still running:
SQL> ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE; SQL> SHUTDOWN
IMMEDIATE; SQL> STARTUP; The parameter was only written to SPFILE, so fixing it
before restart solves it cleanly.
Prevention:
Before any SCOPE=SPFILE change: SQL> CREATE
PFILE='/backup/initORCL_before_change.ora' FROM SPFILE; Always have a before-
change backup.

Scenario 4: Database Opens but You See ORA-32004 on Startup

SCENARIO: ORA-32004: obsolete or deprecated parameter(s) specified

Situation:
The DB starts fine but you see ORA-32004 in the alert log or on screen. This means a
 deprecated parameter is in the SPFILE — typically something like DB_CACHE_ADVICE,
BACKGROUND_DUMP_DEST, or USER_DUMP_DEST.
Fix:
1. Find the offending parameter: SQL> CREATE PFILE='/tmp/[Link]' FROM SPFILE;
$ vi /tmp/[Link] -- look for crossed-out params 2. Remove or replace it: SQL> ALTER
SYSTEM RESET background_dump_dest SCOPE=SPFILE SID='*'; -- Or set the
replacement parameter 3. Restart DB to confirm clean startup.
ORA-32004 is a warning, not a failure — the DB still opens. But clean it up; leaving
deprecated params causes noise in the alert log and can confuse monitoring tools.

Scenario 5: RAC — SPFILE Not Accessible After Node Failure

SCENARIO: In a RAC cluster, the SPFILE is on shared storage that becomes

inaccessible to one node
Situation:
Node 2 of a 2-node RAC can't access ASM or the shared filesystem where the SPFILE lives.
Node 2 won't start its instance.
Diagnosis:
On node 2: SQL> STARTUP -- fails with ORA-29283 or ORA-01078 Check alert log for
the exact path it tried Check ASM status: asmcmd lsdg Check cluster: crsctl stat res -t
If ASM diskgroup is mounted but the SPFILE path in the [Link]/OCR is wrong, or the
ASM instance isn't started: $ srvctl start asm -n node2 Then retry: SQL> STARTUP
If storage is genuinely inaccessible: Fix the storage path, remount the diskgroup, or use a
local PFILE temporarily to get the instance running, then sort out the shared SPFILE path.
Prevention:
In RAC, always store the SPFILE in ASM (not local filesystem) and make sure
ORACLE_SID and SPFILE path in the cluster registry are correct: srvctl config database -d
ORCL

6. Interview Questions and Answers

Control File Questions

Q: What is a control file and what happens if it's lost?

 A: The control file is a small binary file that Oracle reads during the MOUNT stage. It stores
the physical structure of the database — datafile names, redo log names, current SCN,
checkpoint info, and RMAN backup records. If the control file is lost, the database can't
mount — it will fail with ORA-00205 trying to identify and open the control file. If you have an
RMAN backup, you restore the control file, mount, recover, and open with RESETLOGS. If
you have no backup but have the TO TRACE backup (a text script generated by ALTER
DATABASE BACKUP CONTROLFILE TO TRACE), you can recreate it manually using
CREATE CONTROLFILE in NOMOUNT stage. If you have nothing, you're looking at a full
database recreation — which is exactly why multiplexing and RMAN autobackups exist.

Q: Why should you multiplex the control file?

A: Because if the disk hosting the only control file fails and you don't have a backup, the
database can't mount and you face a complex recovery. With multiplexing, you keep copies
on different disks — Oracle writes to all of them simultaneously. If one disk fails, Oracle
continues running with the remaining copies and just flags a warning. You copy the good file
to replace the bad one and move on. In production, I'd say minimum two copies on separate
physical storage, and ideally three. It's a cheap insurance policy — control files are small,
the cost is negligible.

Q: How do you add a new control file copy to a running database?

A: You can't hot-add a control file while the database is open — it requires a shutdown. The
steps are: first check current locations with SHOW PARAMETER control_files, then shut
down cleanly with SHUTDOWN IMMEDIATE, copy an existing control file to the new
location at the OS level, then update the CONTROL_FILES parameter in the SPFILE using
ALTER SYSTEM SET control_files=... SCOPE=SPFILE, and finally restart. Some people
update the SPFILE before shutdown: STARTUP NOMOUNT, ALTER SYSTEM SET
control_files with new list SCOPE=SPFILE, SHUTDOWN, copy the file, then STARTUP.

Q: What information is stored in the control file?

A: Database name and DBID, creation timestamp, names and locations of all datafiles and
online redo log files, current log sequence number, checkpoint SCN, RESETLOGS SCN and
timestamp, and RMAN backup metadata — including backup sets, datafile copies, and
archive log records. The amount of RMAN history kept is controlled by
CONTROL_FILE_RECORD_KEEP_TIME, which defaults to 7 days. After that, older RMAN
records get overwritten. This is one reason large environments use a dedicated RMAN
recovery catalog instead of relying solely on the control file for backup history.

PFILE and SPFILE Questions

Q: What is the difference between a PFILE and an SPFILE?

A: PFILE is a plain text [Link] file you can open and edit in vi. Changes to it only take
effect after a restart because Oracle reads it only at startup. SPFILE is a binary file
managed by Oracle — you never edit it directly, only through ALTER SYSTEM commands,
and Oracle writes changes to it for you. SPFILE supports dynamic parameter changes with
SCOPE=BOTH, so a parameter change takes effect immediately and persists across
restarts. PFILE doesn't do that. In RAC, there's one SPFILE on shared storage that all
nodes use — with PFILE you'd need a copy per node. SPFILE is the default and preferred in
all modern Oracle installations.

Q: What is the search order Oracle uses to find the parameter file on startup?

A: Oracle looks in this order: first [Link] in $ORACLE_HOME/dbs, then [Link] (no
SID) in the same directory, then [Link]. If none of these are found, startup fails with
ORA-01078. You can bypass this entirely by specifying STARTUP PFILE='/path/to/[Link]',
in which case Oracle uses exactly what you tell it and skips the search. This override is very
useful in recovery situations where the SPFILE is broken.

Q: What does SCOPE=BOTH mean in ALTER SYSTEM?

A: SCOPE=BOTH means the parameter change takes effect immediately in the running
instance AND gets written to the SPFILE so it persists after restart. SCOPE=MEMORY
changes only the running instance — lost on restart. SCOPE=SPFILE writes only to the
SPFILE — doesn't take effect until restart. SCOPE=BOTH is what you use most of the time
when changing a dynamic parameter in production. For static parameters like processes or
db_files, you have to use SCOPE=SPFILE because Oracle won't let you change them in
memory — the database needs a restart anyway.

Q: How do you check if Oracle is running with a PFILE or SPFILE?

A: Run SHOW PARAMETER spfile. If the VALUE column shows a file path, you're on
SPFILE. If it's blank or null, Oracle is running from a PFILE. You can also query
v$parameter WHERE name='spfile', same logic. Another way is to check v$spparameter —
if there are rows with non-null values, an SPFILE is in use. In practice, any properly set up
Oracle database should be on SPFILE. If someone tells you their production database is
running on PFILE, that's a red flag.

Q: How do you create an SPFILE from a PFILE?

A: If the database is up on PFILE: CREATE SPFILE FROM PFILE — this creates the
SPFILE at the default location from the PFILE that was used for startup. Or CREATE
SPFILE FROM MEMORY — this captures the current in-memory parameter values,
 including any runtime changes made with ALTER SYSTEM SCOPE=MEMORY. If you want
a specific path: CREATE SPFILE='/path/[Link]' FROM PFILE='/path/[Link]'.
After creating the SPFILE, bounce the database — SHUTDOWN IMMEDIATE, then
STARTUP — and verify with SHOW PARAMETER spfile that it's now using the SPFILE.

Q: What is CONTROL_FILE_RECORD_KEEP_TIME and why does it matter?

A: It controls how long Oracle keeps records of RMAN backups in the control file. The
default is 7 days. After that, older backup records get overwritten. Why it matters: if you run
RMAN and it says 'no backups found' for something older than 7 days, it might actually have
been overwritten in the control file records — not that the backup files don't exist, but that
Oracle forgot about them. If you rely on the control file as your RMAN catalog (no separate
recovery catalog), set this to at least as long as your backup retention policy. If your policy is
30-day retention, set it to 30 or higher: ALTER SYSTEM SET
control_file_record_keep_time=30 SCOPE=BOTH.

Scenario-Based Questions

Q: The SPFILE got deleted accidentally. The database is currently down. How do you
bring it back up?

A: First check what's available. Does a PFILE exist at $ORACLE_HOME/dbs/[Link]?

Is there a PFILE backup anywhere? Does RMAN have an SPFILE autobackup? If there's a
PFILE backup anywhere, use STARTUP PFILE='/path/to/[Link]' to get the DB up, then
CREATE SPFILE FROM MEMORY to recreate the SPFILE, then bounce the DB cleanly. If
no PFILE exists but RMAN autobackup is on, do STARTUP NOMOUNT (even without
SPFILE, you can sometimes coax Oracle to start NOMOUNT if you create a minimal stub
PFILE), then RMAN> RESTORE SPFILE FROM AUTOBACKUP, then SHUTDOWN, then
STARTUP normally. Worst case — no PFILE, no RMAN backup — build a minimal [Link]
with just db_name, control_files, and sga_target, start with that, and work your way up. This
is why every DBA should run CREATE PFILE FROM SPFILE after every major parameter
change and keep the copy somewhere safe.

Q: You changed a memory parameter using ALTER SYSTEM SCOPE=SPFILE and

now the database won't start after a reboot. What do you do?

A: The parameter is only in the SPFILE and it's causing the startup to fail. The fix is to start
using a PFILE. First, create a temporary PFILE from the SPFILE at OS level — you can use
'strings' on the SPFILE binary to see its contents and identify the bad value: strings
$ORACLE_HOME/dbs/[Link]. Then either edit an existing PFILE backup or create
[Link] with the correct parameter value. Start the database: STARTUP
PFILE='/tmp/[Link]'. Once up, fix the bad parameter: ALTER SYSTEM SET
bad_param=correct_value SCOPE=SPFILE, or reset it to default: ALTER SYSTEM RESET
bad_param SCOPE=SPFILE. Then bounce the DB and confirm it starts without the PFILE
override.

Q: How would you perform point-in-time recovery if the control file is also lost?

A: This is the hardest recovery scenario. If both the datafiles need recovery and the control
file is gone, you need to restore the control file first — then mount — then recover the
datafiles. With RMAN and CONTROLFILE AUTOBACKUP ON: STARTUP NOMOUNT,
RESTORE CONTROLFILE FROM AUTOBACKUP — RMAN searches the FRA or the
configured autobackup location. Once restored, ALTER DATABASE MOUNT, then
RESTORE DATABASE, RECOVER DATABASE UNTIL TIME 'timestamp', ALTER
DATABASE OPEN RESETLOGS. The RESETLOGS is mandatory after point-in-time
recovery because you're diverging from the timeline. Without an autobackup, you need the
TO TRACE script and all datafile names — which is why that trace backup is valuable even
if you have RMAN.

Q: In a production environment, what would you do if you see this in the alert log:
ORA-00210 cannot open the specified control file?

A: This means Oracle can't open one of the control files at the path listed. First check
what's happening at the OS level — is the file missing, or is the filesystem full, or is it a
permissions issue? Check: ls -lh /path/to/[Link]. If the file is missing but another copy
exists, shut down cleanly, copy the good control file to the missing path, start up. If the
filesystem is full, free space and start up. If it's a permissions issue, fix ownership and
permissions (should be oracle:oinstall) and start up. The key question is — are there other
control file copies that Oracle still has? If yes, the database may still be running or may be
recoverable without RMAN. If all copies are gone, you're into full control file recovery as
described above.

Q: What is the difference between STARTUP NOMOUNT, STARTUP MOUNT, and

STARTUP OPEN? When would you use each?

A: NOMOUNT: Oracle reads the parameter file, allocates the SGA, starts background
processes. No database files are touched. You use NOMOUNT when creating a new
database (CREATE DATABASE), recreating a control file (CREATE CONTROLFILE), or
restoring an SPFILE from RMAN. MOUNT: Oracle reads the control file, learns the database
structure, but doesn't open datafiles. You use MOUNT for enabling or disabling
ARCHIVELOG mode, performing media recovery, renaming datafiles or redo logs, and
opening a standby database in managed recovery. OPEN: Oracle opens all datafiles and
redo logs, performs crash recovery if needed, and makes the DB available for connections.
 This is normal production state. The intermediate stages exist because certain operations
need the instance running but the DB not fully open — they would be dangerous or
impossible to do on a live open database.

Q: How do you change the location of the SPFILE itself?

A: The SPFILE location is determined by two things: the search path Oracle uses
($ORACLE_HOME/dbs/[Link]) and the SPFILE parameter in the PFILE if you're
using one. To move the SPFILE: shut down the database, move the file at OS level to the
new location, then create a PFILE at the default location ($ORACLE_HOME/dbs/[Link])
with a single line: SPFILE='/new/path/[Link]'. Oracle will read that PFILE, see the
SPFILE directive, and use the SPFILE at the new path. SHOW PARAMETER spfile will then
show the new path. This trick is also how you can have the SPFILE on a non-default path
permanently — a stub PFILE that just points to the real SPFILE.

7. Quick Command Reference

Control File Commands

Command What it does
SHOW PARAMETER Show current control file paths
control_files
SELECT name FROM List control file paths from view
v$controlfile;
ALTER DATABASE BACKUP Binary backup of control file
CONTROLFILE TO
'/path/[Link]';
ALTER DATABASE BACKUP Dump SQL script to recreate control file
CONTROLFILE TO TRACE;
RMAN> BACKUP CURRENT RMAN backup of control file
CONTROLFILE;
RMAN> CONFIGURE Auto-backup CF after every RMAN job
CONTROLFILE AUTOBACKUP ON;
RMAN> RESTORE CONTROLFILE Restore CF from RMAN autobackup
FROM AUTOBACKUP;
CREATE CONTROLFILE REUSE Recreate control file manually in NOMOUNT
DATABASE ...

PFILE / SPFILE Commands

Command What it does
STARTUP Start DB using a specific PFILE
PFILE='/path/[Link]';
SHOW PARAMETER spfile Check if using SPFILE (blank = PFILE)
CREATE SPFILE FROM PFILE; Create SPFILE from default PFILE location
CREATE SPFILE FROM MEMORY; Create SPFILE from current running params
CREATE PFILE FROM SPFILE; Dump SPFILE to text PFILE (for editing)
CREATE Dump current params to a PFILE
PFILE='/tmp/[Link]' FROM
MEMORY;
ALTER SYSTEM SET param=val Change param now + persist to SPFILE
SCOPE=BOTH;
ALTER SYSTEM SET param=val Change in SPFILE only (needs restart)
SCOPE=SPFILE;
ALTER SYSTEM SET param=val Change in memory only (lost on restart)
SCOPE=MEMORY;
ALTER SYSTEM RESET param Remove param from SPFILE (back to default)
SCOPE=SPFILE;
SELECT * FROM View all parameters stored in SPFILE
v$spparameter;
strings OS-level: read SPFILE contents as text
$ORACLE_HOME/dbs/spfileORCL
.ora

Key Views
View What it shows
V$CONTROLFILE Control file paths and status
V$CONTROLFILE_RECORD_ Sections inside control file and space usage
SECTION
V$PARAMETER All current parameter values (memory)
V$SPPARAMETER Parameters stored in SPFILE (null if not set)
V$DATABASE DB name, log mode, open mode, resetlogs info
V$INSTANCE Instance state, startup time
V$DIAG_INFO Alert log and trace file locations
V$RMAN_CONFIGURATION RMAN configuration including autobackup setting

End of Document — Control File, PFILE, SPFILE Reference