Server Parameter Files

This was tested on Oracle 11.2. Some initialisation parameters cannot be changed while the database is open. If you try to change one of these, you get an ORA-02095:

 

SQL> alter system set sessions = 30;

alter system set sessions = 30

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

SQL>

 

If you are using a server parameter file, you can make the change there and bounce the database. But if you are not then this option is not available to you either:

 

SQL> l

  1  alter system set sessions = 30

  2* scope = spfile

SQL> /

alter system set sessions = 30

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

 

SQL>

 

You can start using a server parameter file as follows. This assumes that the database is a test one and nobody else is using it. Otherwise you should check with the users first then do a shutdown normal to bounce the database instead:

 

SQL> create spfile from pfile;

 

File created.

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             255858368 bytes

Database Buffers          155189248 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

Then you can make the change which failed above:

 

SQL> alter system set sessions = 30

  2  scope = spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             260052672 bytes

Database Buffers          150994944 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

I will look at server parameter files in more detail in a future post but I wanted to show you this as it happened to me today. You may wonder why I set the sessions parameter so low. I did it on purpose to demonstrate ORA-00018. Unfortunately, the change did not work:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

776

 

SQL>

 

I checked the Oracle 11.2 documentation on this and it said that the minimum value allowed for the sessions parameter is processes * 1.5 + 22. The processes parameter for this database is set to 500:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'processes'

SQL> /

 

VALUE

----------

500

 

SQL>

 

This should give me a sessions value of 500 + 250 + 22 = 772 so it is not too far out. I'll just have to think of another way to demonstrate ORA-00018!

remote_os_authent

The Oracle RDBMS used to have a parameter called remote_os_authent. This specified whether or not you could connect to an instance remotely using OS authentication. Setting it to true was a security risk, especially if you used OS authentication for database users which had the DBA role. For example, you might have an externally identified user in your database called ORACLE and grant the DBA role to that user. A malicious user with admin rights on a remote machine could create a user called oracle on that machine and use it to connect to your database as an administrator without providing a password. In version 11, the Oracle RDBMS deprecated this parameter but have retained it (for now) for backward compatibility. The example below illustrates this. I ran it on a UNIX server as a UNIX user called oracle. First I connected to the database as SYS and set remote_os_authent to true in the server parameter file:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set

  2  remote_os_authent = true

  3  scope = spfile

  4  /

 

System altered.

 

SQL>

 

Then I bounced the database. The Oracle RDBMS displayed an error message when it saw the deprecated parameter:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              83888304 bytes

Database Buffers           67108864 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

I reconnected to the database remotely and reset remote_os_authent in the server parameter file:

 

SQL> conn /@test11

Connected.

SQL> show user

USER is "ORACLE"

SQL> alter system reset remote_os_authent

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again. This time there was no error message:

 

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              88082608 bytes

Database Buffers           62914560 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

Changing the remote_os_authent parameter stopped the remote connection working:

 

SQL> conn /@test11

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>