Have you ever wondered if a database could have a different behavior after a restart? Maybe a parameter was changed and not taken into account, but you have no idea which one?

You can have a look in the “alert log” to find out if someone has recently changed a parameter :

But what if the “alert log” recently rotated, and the modification does not appear in the current file?

You can also try to use strings command on the spfile, if it resides on a regular file system, for example. strings will display the content of the file in a human-readable way. First, you need to locate the spfile :

Then display its content :

I would not recommend this method because it can be confusing. By using strings , you will likely have a weird display of the content, with newlines appearing in the middle of some parameters definition. If you want to understand why, then you should read Frits Hoogland’s blog post.

With this method, you can quickly have an idea of what is defined in the spfile. But still, you will have to guess what changed compared to the current state of the parameters.

However, there are 2 very useful dynamic performance views to help you with that : v$spparameter and v$parameter.

v$parameter

V$PARAMETER displays information about the initialization parameters that are currently in effect for the session.

v$spparameter

V$SPPARAMETER displays information about the contents of the server parameter file.

So even if the spfile contains a specific parameter, it is not necessarily in effect in the current session. It seems now easier to answer the question of this blog post. Let’s join those 2 views and find the parameters with a different value.

With this example, we understand that parameter sga_max_size will have a different value once the database is restarted :

In view v$parameter, column ISDEFAULT is worth noticing because it “Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE).” You can have a better understanding of which parameters were modified.

To finish, it is very useful to add a comment whenever you modify a parameter, to track modifications and remember their purpose :

And of course, your comment will appear in the “alert log” as well :

Now, you should be in a better position to foresee any different behavior, due to parameter modification, after an instance restart.

=======================================================================

[oracle@central-oracle ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 2 13:03:38 2024

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL>

SQL> SHOW PARAMETER SPFILE;

 

SQL> show parameter transactions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     2516
transactions_per_rollback_segment    integer     5
SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     2288
shared_server_sessions               integer
SQL> alter system set processes=6000 scope=spfile;

System altered.

SQL> alter system set sessions=12064 scope=spfile;

System altered.

SQL> alter system set transactions=13270 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2.0133E+10 bytes
Fixed Size                 19247880 bytes
Variable Size            5771365624 bytes
Database Buffers         1.4294E+10 bytes
Redo Buffers               47857664 bytes
Database mounted.
Database opened.
SQL> show parameter transactions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     13270
transactions_per_rollback_segment    integer     5
SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     12064
shared_server_sessions               integer
SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
asm_io_processes                     integer     20
db_writer_processes                  integer     2
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     4000
log_archive_max_processes            integer     4
processes                            integer     6000
SQL>