An instance starts from initialization parameters: the database name, memory, and the rest of the startup settings. Those settings come from a PFILE or an SPFILE. The value in effect now is not always the value saved for the next start.
Oracle DBA Lesson 23A — Where Oracle Gets Startup Settings
The examples are Oracle Database 19c, one instance, SQL*Plus connected to that instance.
PFILE and SPFILE
| File | Form | How you maintain it |
|---|---|---|
| PFILE | Plain text | Review it, and edit a separate copy, in a text editor. |
| SPFILE | Binary | Oracle maintains it through database commands. Do not edit the file in a text editor. |
Either file can supply the parameters at startup. The active SPFILE, when there is one, is on the database server. It is not on the computer where SQL*Plus happens to be running. A short text PFILE can also point at an SPFILE. In that case the SPFILE is the parameter source.
Which SPFILE is active
SHOW PARAMETER is a SQL*Plus command. No semicolon.
SHOW PARAMETER spfileRead VALUE. A path means that SPFILE is active. A blank value means this instance is not using an SPFILE. Blank does not name the PFILE. Get that path from the startup command and from how the server is configured to start.
A blank value also means CREATE PFILE ... FROM SPFILE is not an export of the settings that started this instance. With no active SPFILE, that command looks for a default SPFILE or returns an error. It does not silently write the PFILE that was used.
The command answers one question: which SPFILE is active. It does not list the parameter values in effect.
In effect now, and saved
| View | What it shows |
|---|---|
V$SYSTEM_PARAMETER |
Values in effect for the instance now. |
V$SPPARAMETER |
Values stored in the active SPFILE. |
V$PARAMETER |
Values in effect for this session. Not the instance-wide view. |
A stored value can differ from the current value when the change is meant for the next startup. A parameter can also have a current value with no explicit row stored in the SPFILE.
Current instance values:
SELECT name, display_value
FROM v$system_parameter
WHERE name IN ('db_name', 'sga_target')
ORDER BY name;When an SPFILE is active, the same names in the file:
SELECT name, display_value, isspecified
FROM v$spparameter
WHERE name IN ('db_name', 'sga_target')
ORDER BY name;ISSPECIFIED = 'TRUE' means that name is stored in the SPFILE. FALSE means it is not an explicit entry. Leave it. These queries only read.
A text copy of the SPFILE
When SHOW PARAMETER spfile returns a path, export the stored settings to a new text file:
CREATE PFILE='/private/oracle/init_review.ora' FROM SPFILE;/private/oracle/init_review.ora is an example. Use an unused path on the database server that the Oracle software owner can write. SQL*Plus may be on another computer. The file is still created on the database server.
This writes a copy. It does not point the running instance at that PFILE, it does not edit the SPFILE, and it does not change the values in effect. Keep the text file with the rest of the configuration. Do not leave it in a world-readable directory.
If spfile is blank, find the PFILE that started the instance. Do not treat FROM SPFILE as an export of the current settings. CREATE PFILE ... FROM MEMORY is a different command. It writes what is in memory, not what is stored in the SPFILE.
This stops at reading the source and making the copy. Changing a bad startup setting, and proving the instance starts from the replacement, is the next part.
Oracle's own pages for this: startup file search and SPFILE, CREATE PFILE, V$SYSTEM_PARAMETER, V$SPPARAMETER, V$PARAMETER.
No comments:
Post a Comment