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!

Introduction to Auditing

A database’s audit trail is stored in the SYS.AUD$ table:

SQL> desc sys.aud$

Name                       Null?    Type

-------------------------- -------- ------------------

SESSIONID                  NOT NULL NUMBER

ENTRYID                    NOT NULL NUMBER

STATEMENT                  NOT NULL NUMBER

TIMESTAMP#                 NOT NULL DATE

USERID                              VARCHAR2(30)

USERHOST                            VARCHAR2(128)

TERMINAL                            VARCHAR2(255)

ACTION#                    NOT NULL NUMBER

RETURNCODE                 NOT NULL NUMBER

OBJ$CREATOR                         VARCHAR2(30)

OBJ$NAME                            VARCHAR2(128)

AUTH$PRIVILEGES                     VARCHAR2(16)

AUTH$GRANTEE                        VARCHAR2(30)

NEW$OWNER                           VARCHAR2(30)

NEW$NAME                            VARCHAR2(128)

SES$ACTIONS                         VARCHAR2(19)

SES$TID                             NUMBER

LOGOFF$LREAD                        NUMBER

LOGOFF$PREAD                        NUMBER

LOGOFF$LWRITE                       NUMBER

LOGOFF$DEAD                         NUMBER

LOGOFF$TIME                         DATE

COMMENT$TEXT                        VARCHAR2(4000)

CLIENTID                            VARCHAR2(64)

SPARE1                              VARCHAR2(255)

SPARE2                              NUMBER

OBJ$LABEL                           RAW(255)

SES$LABEL                           RAW(255)

PRIV$USED                           NUMBER

SESSIONCPU                          NUMBER

 

SQL>

 

You can tell Oracle what to monitor using the AUDIT command. The example below will record when the SYSTEM user connects to the database:

 

SQL> audit create session by system;

 

Audit succeeded.

 

SQL>

 

This database currently has auditing turned off i.e the audit_trail initialisation parameter is set to NONE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'audit_trail'

SQL> /

 

VALUE

------------------------------

NONE

 

SQL>

 

So subsequent logins by SYSTEM will not be recorded in SYS.AUD$:

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

In versions up to and including Oracle 11, you cannot turn auditing on while a database is open:

 

SQL> alter system set audit_trail = true;

alter system set audit_trail = true

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot

be modified

 

SQL>

 

You have to alter the pfile or spfile as appropriate and bounce the database. Once audit_trail is set to true, auditing will start working and a row will appear in SYS.AUD$ after SYSTEM has connected to the database:

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

------------------------------

TRUE

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

You can query records in SYS.AUD$ in the normal way. The TIMESTAMP# column has the logon time and a PRIV$USED of 5 stands for CREATE SESSION:

 

SQL> l

  1* select userid, timestamp#, priv$used from sys.aud$

SQL> /

 

USERID     TIMESTAMP#  PRIV$USED

---------- ---------- ----------

SYSTEM     21-MAR-12           5

 

SQL>

 

Various views record the auditing which you have asked Oracle to do in your database. The audit request we made above is stored in DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

USER_NAME            PRIVILEGE

-------------------- --------------------

SYSTEM               CREATE SESSION

 

SQL>

 

You can stop an audit request with the NOAUDIT command:

 

SQL> noaudit create session by system

  2  /

 

Noaudit succeeded.

 

SQL>

 

Once you have done this, the relevant entry disappears from DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

no rows selected

 

SQL>

 

And connections by SYSTEM will no longer be recorded in SYS.AUD$:

 

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

Unlike other objects owned by SYS, you are allowed to delete rows from SYS.AUD$.

 

SQL> delete sys.aud$

  2  /

 

1 row deleted.

 

SQL>

 

However, you should check first that it does not contain rows produced by your colleagues with separate audit requests!

One Possible Cause of ORA-12537

Some testers had a problem connecting to a database remotely. I tried it myself and saw the following message:

 

SQL> conn system@test11

Enter password: ******

ERROR:

ORA-12537: TNS:connection closed

 

Warning: You are no longer connected to ORACLE.

SQL>

I logged on to the server hosting the database and tried to connect from there. This showed a different error message:

 

SOLARIS > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 26 12:10:41 2011

 

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

 

ERROR:

ORA-00020: maximum number of processes (%s) exceeded

I guessed that this message indicated the true cause of the problem so I looked in the database's server parameter file. The processes initialisation parameter specifies the maximum number of operating system processes allowed for the database and it was set to 50:

 

*.processes=50

 

Another way to check this value is to wait for another session to complete, connect to the database and look in v$parameter as follows:

 

SOLARIS > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 26 12:39:28 2011

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> col value format a10

SQL> select value from v$parameter

  2  where name = 'processes';

 

VALUE

----------

50
 

SQL>
 

Once I had a session running in SQL*Plus, I tried to increase the value of the processes parameter but it's not one that you can change dynamically:

SQL> alter system set processes = 100;

alter system set processes = 100

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

SQL>

...  so you have to do it in the server parameter file then bounce the database (I will cover this in another post).

Another option, particularly if the problem is only affecting a test database, is to suggest to the users that they work together to make better use of the resources available to them.

I double checked the diagnosis by counting the number of processes the database had in the operating system:

 

SOLARIS > ps -ef|grep TEST11|wc -l

      47

SOLARIS >

 

At this point the number of processes had gone down so I could connect to the database:

 

SQL> conn system@test11

Enter password: ******

Connected.

SQL>

 

And (in a separate server session) I saw the number of processes go up again:

 

SOLARIS > ps -ef|grep TEST11|wc -l

      48

SOLARIS >