• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command

By admin

Question: You maintain several SPFILE files:

$ ls -l spfile*
-rw-r-----   1 sme      dba         2560 Mar 20 08:20 spfile.ora
-rw-r-----   1 sme      dba         2560 Mar 20 08:20 spfileRel14.ora

Which one is modified when you run the “ALTER SYSTEM” SQL command to permanently change parameter values in the spfile?

The DBAs must be cautious when using ALTER SYSTEM command since it permanently modifies an SPFILE parameter file. They need to know which parameter file will be modified.

Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command?

Determine which SPFILE was used at startup, if any was used:

1. At instance STARTUP, there are 3 possibilities for the instance to retrieve instance parameter values:

  • read the initSID.ora referring to an spfile referenced by the new parameter SPFILE=spfile.ora (CASE 1)
  • read spfileSID.ora containing all instance parameters (CASE 2)
  • read the initSID.ora containing all instance parameters (CASE 3)

2. When you execute an ALTER SYSTEM command to modify the spfile parameter values, the SPFILE impacted is always the spfile used at startup.

3. Which one was used at startup?

There are 3 places to retrieve this information:

  1. alert.log file
  2. V$PARAMETER
  3. V$SPPARAMETER view

4. There are 3 possible cases:

Case 1

1. If, in the alertSID.log file, the name of the spfile used is displayed, then it is clearly explicit that the instance started with the initSID.ora calling an spfile.

Starting up ORACLE RDBMS Version: 9.0.0.0.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 67108864
  large_pool_size          = 1048576
  java_pool_size           = 20971520
  spfile                   = spfile.ora

2. The V$PARAMETER view displays the full name of the spfile used:

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           /export/home1/ora900/dbs/spfile.ora

3. In this example, when you use the “ALTER SYSTEM” SQL command to change a parameter value, the modification is made to the spfile.ora and not to the spfileSID.ora file.

Example:

$ cp spfile.ora spfileRel14.ora
SQL> alter system set sort_area_size=65000 scope=spfile;
System altered.
$ more spfileRel14.ora
...
*.sort_area_size=524288
...
$ more spfile.ora
...
*.sort_area_size=65000
...

Case 2

If, in the alert.log file, no SPFILE parameter is mentioned, then an ALTER SYSTEM command would alter the spfileRel14.ora file by default if used at startup.

The V$PARAMETER view displays the name of the spfile used. The value can be:
– spfile@.ora for spfileSID.ora
– spfile.ora for spfile.ora

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           ?/dbs/spfile@.ora

You can check that the spfileRel14.ora file was used at startup – in V$SPPARAMETER view, some of the parameters have the ISSPECIFIED column
value set to TRUE and therefore the VALUE column is not null:

SQL> select name, value,isspecified 
  2  from v$spparameter where name like 'undo%'

NAME                           VALUE                          ISSPEC
------------------------------ ------------------------------ ------
undo_management                MANUAL                         TRUE
undo_tablespace                RBS                            TRUE
undo_suppress_errors                                          FALSE
undo_retention                                                FALSE
SQL> alter system set processes =100 scope=spfile;
System altered.
$ more spfileRel14.ora
...
*.processes=100
...

The previous spfile.ora showed above displays the following information:

$ more spfile.ora
...
processes=150
...

Case 3

If, in the alert.log file, no SPFILE parameter is mentioned, then an ALTER SYSTEM command would issue an error if no spfile was used at startup.

SQL> alter system set open_cursors=500 scope=spfile; 
alter system set open_cursors=500 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

The V$PARAMETER view displays a NULL value for the name of the spfile:

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           

You can check that no spfile file was used at startup: in V$SPPARAMETER view there are no parameters for which the ISSPECIFIED value is set to TRUE, nor is the VALUE column not null. For example:

In initRel12.ora, no SPFILE parameter used:

SQL> startup pfile=initRel12.ora
ORACLE instance started.

Total System Global Area  143433028 bytes
Fixed Size                   286020 bytes
Variable Size             100663296 bytes
Database Buffers           41943040 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.
SQL> select * from v$spparameter where VALUE is NOT NULL;
no rows selected
SQL> select * from v$spparameter where ISSPECIFIED ='TRUE';
no rows selected
How to Modify spfile in Oracle Database

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)
  2. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  3. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  4. How to Failover a Service During Instance Shutdown Using SRVCTL
  5. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network
  6. How to Backup and Restore Java Classes and Privileges only in Oracle Database
  7. Oracle SQL script to Show current Users and SQL being Executed
  8. Truncate Table Statement: REUSE STORAGE VS DROP STORAGE
  9. Oracle ASM – How ASM Disk Resync Works
  10. How To Create An ASM Diskgroup Using XML code in ASMCMD

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary