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.