Voodoo with init parameters and Oracle Restart

Uwe Kirchhoff, one of the best techies I know in the entire Oracle CSS (formerly ACS) Support, mailed me before the Christmas holidays with a simple question. He discovered that altering init.ora parameters in a session does not necessarily write the parameter down into the spfile. Sounds a bit like Voodoo with init parameters,  and Oracle Restart has a role in this story as well. If you don’t have or do not plan to use Oracle Restart, then this is not interesting for you. But of course, you are free to still read it 🙂

Photo by Marko Manojlovic on Unsplash

What’s the case?

I know Uwe well enough that he would not contact me with simple things. And this one he shared with me sounded at first really like Voodoo to me. We both know that from time to time the database adjusts things silently in the background. Resource Manager for instance is well know for such habits. But this one here is too strange.

You alter a parameter, for instance from within SQL*Plus or SQLcli:

ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

When you check this now in the alert.log, you have proven that you are a long-year Oracle DBA – keep an eye on the second line from top and from bottom:

2023-12-13T10:22:07.056689+01:00
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
2023-12-13T10:22:11.776549+01:00
Shutting down ORACLE instance (abort) (OS id: 3535)
2023-12-13T10:22:11.779596+01:00
Shutdown is initiated by sqlplus@blg-ora-db44 (TNS V1-V3).
License high water mark = 4
USER (ospid: 3535): terminating the instance
2023-12-13T10:22:11.929710+01:00
ORA-1092 : opitsk aborting process
2023-12-13T10:22:12.792731+01:00
Instance terminated by USER, pid = 3535
2023-12-13T10:22:12.806038+01:00
Instance shutdown complete (OS id: 3535)
2023-12-13T10:22:16.114174+01:00
Starting ORACLE instance (normal) (OS id: 3713)
2023-12-13T10:22:16.158777+01:00
****************************************************
Sys-V shared memory will be used for creating SGA
 ****************************************************
2023-12-13T10:22:16.163717+01:00
**********************************************************************
2023-12-13T10:22:16.165085+01:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
 
...

WARNING: db_recovery_file_dest is same as db_create_file_dest
Autotune of undo retention is turned on.
IMODE=BR
ILAT =319
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Cluster configuration type = SIHA [3]
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0.
ORACLE_HOME:    /oracle/191900_2
System name:    Linux
Node name:       blg-ora-db44
Release:               5.4.17-2136.325.5.el7uek.x86_64
Version:               #2 SMP Thu Oct 19 15:40:03 PDT 2023
Machine:             x86_64
Using parameter settings in server-side spfile +DATA01/NFS02/PARAMETERFILE/spfile.835.1155464357
System parameters with non-default values:
  processes                = 1920
  nls_language             = "AMERICAN"
...

I cut out a few pieces which are not important.

But basically, the parameter PROCESSES to 1000 got adjusted, the database gets restarted – and it reads the old value of 1920 for PROCEESES from the SPFILE.

Another example

Uwe then mailed me a few days later with another test case.

1. Change PROCESSES in CDB$ROOT from 1100 to 1200 with ALTER SYSTEM SET PROCESSES=1200 SCOPE=SPFILE;
2. Restart the database with STARTUP FORCE
3. Check PROCESSES – it shows the old value of 1100 from before the change
4. Now it becomes even more scary since “show spparameter” displays 1200
5. The SPFILE is in ASM – it got copied then to /tmp
6. Execute CREATE PFILE=’tmp/initx.ora from spfile=’+DATA/…’
7. STRINGS on the SPFILE copied to /tmp shows the old value, 1100
8. The /tmp/initx.ora shows 1200

What’s true?

We discussed and exchanged some ideas, for instance that it may have to do with NFS. Well …

The antidote against Voodoo

Making a long story short: it is NFS, or to be more precise, dNFS (direct NFS). Or to be even more precise the fact that dNFS is not linked in by default when you install Oracle Restart.

When you install Oracle Restart, and you use ASM via NFS, then dNFS won’t be linked in. It is linked in always when you use the first option shown above, a normal Grid Infrastructure installation. But not when you install Oracle Restart.

As soon we Uwe linked in dNFS, the Voodoo-like behavior disappeared, and parameters were used and displayed as intended again.

Thanks to Uwe for sharing this with me!

Some additional information

Lance commented below that he runs a check query after patching to just check whether dNFS is still present and linked in:

select * from v$dnfs_servers;

And I found another useful blog post by Oracle ACE Director Ilmar Kerm which may be quite useful. Ilmar found out that a “relink all” actually could leave out dNFS. Read more here.

Now I understand why you double-check.

Furthermore, please see the Oracle Documentation on how to enable and disable dNFS.

If you want to check whether dNFS is linked into the kernel or not (i.e., dnfs_on or dnfs_off), then see whether in $ORACLE_HOME/rdbms/lib/odm the file libnfsodm19.so exists.

• libnfsodm19.so is in $ORACLE_HOME/rdbms/lib/odm ==> dnfs_on
• No libnfsodm19.so is in $ORACLE_HOME/rdbms/lib/odm ==> dnfs_off

Bug 36211007 – GRIDSETUP.SH – DNFS NOT LINKED IN GI WITH OPTION ORACLE RESTART has been filed to track the issue.

Further Links and Information

• MOS Note: 2719268.1 – DNFS Enabeld by Default
• MOS Note: 762374.1 – Step by Step – Configure Direct NFS Client (DNFS) on Linux
• MOS Note:  2247243.1 – How to Disable DNFS in Oracle 
• MOS Note:  2246221.1  – Top Database Admin Issues in DNFS Environment
• Blog Post by Ilmar Kerm: Relinking binaries in 19c removes dNFS
• Oracle Documentation on how to enable and disable dNFS

–Mike