The relation between an Oracle instance and memory in Windows

Posted by David Yahalom under Windows, Hardware, Oracle

The Oracle instance memory allocation works differently in Windows then it does in UNIX-Linux like operating systems.

In Windows environments, on starting up the Oracle instance all global memory pages are committed (like the buffer cache, redo log buffer and the library cache). However, and this is an important difference compared to POSIX operating systems, only a small number of these memory pages are actually cached in memory upon instance startup causing most of the SGA not to be part of  Oracle’s active working set.

This forces Oracle to compete on equal grounds with other processes. The O/S might swap certain pages from Oracle’s working set in memory to disk during periods of increased activity or when other processes are paging more and compete for RAM. Windows pages according to activity using an MRU-like mechanism, so when paging is going-on system-wide, Windows will try to reserve RAM pages to the most active process, the one that is paging the most.

If Windows decides to swap the database instance to disk, this will cause a severe performance degradation for the Oracle Database and might even grind the instance to a complete halt.

There are two registry parameters that exist and allow us to manipulate the working set of the Oracle process. These are:

• ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN:
  Minimum working set for the ORACLE.EXE process (units = MB)
• ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX:
  Maximum working set for the ORACLE.EXE process (units = MB)

You can set these parameters under:

• For single ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
• For multiple ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

ORA_WORKINGSETMIN can be used to prevent the pages allocated to the Oracle process from dropping below the defined threshold (in MB) until the instance is shutdown.

The biggest benefit of setting these parameters will be in an environment where Oracle coexists with other applications. Although it can be beneficial in other scenraios as well, such as when production and test instances are running on the same physical machine.

Another very useful parameter that exist in the Win32/64 platform is: PRE_PAGE_SGA. This parameter causes Oracle will force Oracle to allocate all SGA pages upon instance startup thus bringing all of them to memory as the working set of the Oracle executable.

This will allow the instance to reach maximum performance more quickly rather than through an incremental build up as pages are loaded on a need-to basis.

Combining ORA_WORKINGSETMIN  with PRE_PAGE_SGA will force the Oracle instance to start above the minimum threshold and not drop below. Using ORA_WORKINGSETMIN in isolation will cause the Oracle working set rises above the threshold it will not drop below.

ORA-01031: insufficient privileges upon instance startup

Posted by David Yahalom under Windows, Oracle

A friend of mine who is a junior DBA approached me today with a strange problem that was bugging him for hours. He installed a new Windows Oracle 9i server and wanted to create the databases manually (something I always recommend over DBCA, as you get finger grain control over your database creation process and you feel more “in control” of whats going on inside your DB).

He created an init.ora file for his Instance and since he wanted to use OS authentication for Oracle, he left the parameter remote_login_passwordfile to its default value (”none”).

He then proceeded to creating his instance using the following command:

oradim -new -sid InstanceSID -startmode m -pfile C:location_to_pfileinit.ora -intpwd password

After which, the instance was created. He proceeded to creating the database itself. Since he was all keen about doing this manually he opened up a Windows CMD and did the following steps:

SET ORACLE_SID=InstanceSID
C:> sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 13 10:18:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/password as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=C:location_to_fileinit.ora;
ORA-01031: insufficient privileges
SQL>

As you see, he got an ORA-01031. He didn’t understand what is causing this. His O/S user was a member of the ORA_DBA group. He tried logging off windows and then logging backup in. Nothing.

The solution is actually quite simple. By default, if you install Oracle and don’t use the network configuration assistance you get a ORA_HOME without an SQLNET.ORA file. Without it, Oracle will not be able to use O/S authentication.

To solve this problem, simply create a file named sqlnet.ora in your %ORA_HOME%\network\network\admin directory and add to it the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Save the file.

After this, try starting the instance using the same procedure described above and your O/S based logon should work just fine!