Tuesday, 7 August 2012



My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks, that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a good choice of platform.  However they are easily adaptable for versions of UNIX/AIX and windows etc.


Specifying Database Block Sizes

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database.  This block size will be used by the SYSTEM table-space, and the other table-spaces by default.  There is support for four additional non-standard block sizes for the other table-spaces.

The most commonly used block size, should be chosen as the default block size.  Typically 4K or 8K.  If you do not set DB_BLOCK_SIZE, then it defaults to an OS specific default, typically 8K.  This is usually adequate for general purpose databases.  Note that the only way to change DB_BLOCK_SIZE, after database creation, is to recreate the database, you can’t change it otherwise.  So it worth spending some time, getting this parameter correct.  Make sure that the DB_BLOCK_SIZE is a multiple of the OS block size.  The sizes that are available to choose from are: 2k, 4k, 8k, 16, and 32k.

A larger block size tends to provide greater I/O efficiency, that is access and storage of data.  If you are not sure what the block size should be, then start with a block size of 8K, and increase or decrease according to the type of transactions that you are expecting, and whether you are going to use LOBS(Large Objects) or not.  An 8K block size should be adequate for most purposes, and you can consider 16K or 32K if you are using LOBS.  You should have a very good reason, to go down to a 4K or 2K block size. 

By a non-standard block size, we really mean a table-space that has a block size that is different to the DB_BLOCK_SIZE parameter.  You must first define sub-caches with the different block size in the SGA.  Then you can specify the Create Tablespace using the BlockSize clause.  The Objects you create in this tablespace will then be of the block size of the tablespace.

For example, if you were creating a 32K cache:
SQL>  alter system set db_32k_cache_size=100m scope=both;

Now when you create a tablespace, you just include the BLOCKSIZE 32K clause.
  '/u01/app/test/test_32k_001.dbf' SIZE 1000M AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED
Now when you create an object that needs to take advantage of the 32K blocksize, you just include the: Tablespace test_32K clause.

Note that a 32K blocksize is only valid on a 64Bit platform.

Tablespaces with custom block sizes, becomes very useful when you are transporting tablespaces between for example: from a 4K block size OLTP database to an 8K or 16K data-warehouse environment.

Specifying the maximum number of processes

This parameter refers to maximum number of operating system processes that can be concurrently connected to the oracle database.  So you should allow one process for each background process, plus one for each user process.  For each additional Oracle database feature that you are using you will need to allow for additional background processes.  For example if you use ASM(Automatic Storage Management), then you will need to cater for an additional 3 background processes.

As a rule of thumb, if you would expect around 50 user processes running against your database, then allow for about 20 background processes.  I also typically add at least 10% to this number to allow for busy processing conditions.  So that would come out to about 77 processes.  
Set the initialization parameter to 77.

Specifying the DDL Lock Timeout

DDL(Data Definition Language) statements, need to have or acquire an exclusive lock on the structure that it is attempting to update.  If for that moment in time when the DDL statement ran, the locks were not available, then the DDL statement will fail.  However if the DDL statement were to have run a moment before or afterwards, then the lock would have been available, and it would have been able to go through.

So we need to enable DDL statements to wait for locks if they are not immediately available.  We set this with the DDL_LOCK_TIMEOUT parameter which takes its value in seconds, and is set to 0 by default.

The allowable values for DDL_LOCK_TIMEOUT are from 0 to 1,000,000.   You can set this in the initialization parameter or on the fly at the system level or at the session level.  The session level is a good approach, if you are doing maintenance, and only need the timeout to be defined while you are busy.

SQL> alter session set DDL_LOCK_TIMEOUT=20 scope=memory;

When you sign out from the SQL*Plus session, it will return to its initialization parameter file setting. 

Specifying the Method of Undo Space Management

The Oracle Database is so robust because of the way that undo data is handled.  The undo stores the values of records that have been changed, mainly from before the data is committed.  So if at any time the transaction is rolled back, then the records are restored to the values they had, before the transaction started.  There are ways to set up the UNDO environment, that can enhance the process.

Firstly we must set the UNDO_MANAGEMENT initialization parameter to AUTO.  From 11g onwards, this value is defaulted to AUTO if it is omitted or NULL.
UNDO_MANAGEMENT=AUTO,  If set to AUTO, then the instance will start in automatic undo management mode.  This mode means that the undo is stored in an undo tablespace.

This gets us to the next point.  You need to set the UNDO_TABLESPACE initialization parameter.  When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace to store the undo data in.  The default undo tablespace will be either the SYS_UNDOTPS or the user defined undo tablespace.  However if there is no available undo tablespace, then the oracle database will start writing the undo data to the SYSTEM tablespace.  You want to avoid having the undo written to the SYSTEM tablespace.  If you are running a RAC environment, then the UNDO_TABLESPACE parameter is useful for assigning different undo tablespaces to different instances.

When running the CREATE DATABASE statement, then it is better to include the UNDO TABLESPACE clause, in the statement, rather than including it in the Initialization parameter file. 

The Compatible Initialization Parameter

The COMPATIBLE initialization parameter, enables or disables the use of features in the database.

For example: if you have a 11.2 database with COMPATIBLE=10.0.0, then if you try to use features that require 11.2 compatibility, then an error is generated.  Your database is in the 10.0.0 compatible level.  If you need the features of 11.2, then you can set COMPATIBLE= in the initialization parameter file, and restart the database.  You can advance the compatibility parameter forward, but not backwards.

If you need to move the compatibility parameter backwards, then you will need to do a point in time recovery to a time, when the database was at the compatibility level that you want to set it to.

The default value for the compatibility parameter is the release number of the most recent major release.  For example in 11gR2, the default value is and the minimum that you can set it to is 10.0.0  The advantage of leaving this at the default value, is that you can then use all of the features of the new release of the Oracle Database.  If the database was created with the compatibility set to the most recent major release, then you can never downgrade the database.

Setting the License Parameter

LICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING have been deprecated in 11gR2.  Oracle no longer offer licensing by the number of concurrent sessions, which these parmeters were designed to measure.

If you are using named user licensing, then LICENSE_MAX_USERS is the initialization parameter that can help you to manage this.  This essentially creates a limit on the number of users that can be created in the database.  Once the limit is reached, you cannot create any more users.

The LICENSE_MAX_USERS mechanism assumes that each person accessing the database has an unique name, and no two people are sharing the same user name.  You need to manage this in order to ensure that you comply with your Oracle License Agreement.

Server Parameter File

Traditionally parameters were stored in the parameter file, which is a text based file.  For better manageability you can store these parameters in a server parameter file, which is a binary file.  The server parameter file is persistent across database startup and shutdown.

A server parameter file is a server-side parameter file.  The changes made to the parameters, while the instance is running can persist through shutdown and startup operations.  With the older parameter file, you would have had to update the parameter file, to get the new settings to persist through shutdown and startup operations.  You can update dynamic parameters with the SCOPE=BOTH clause to achieve this.  If you use the alter system scope=memory, then you will still need to make the changes in the parameter file, to make them permanent.

A server parameter file is initially build from the pfile, using the CREATE SPFILE statement.  It may also have been created by the DBCA(Database Configuration Assistant).  The spfile is a binary file that should not be edited with a text editor.

In an emergency, you can edit the spfile, remove the headers and footers, and other binary codes from it, and save it as a pfile, to be used to startup your database.  This should be an emergency and a last resort, because you do stand a chance to lose your database if you do this.

When you startup your database like this:
SQL> startup
The Oracle instance searches for the spfile in the default OS location.  If there is no spfile, the instance searches for a text based pfile to use to start the database up.
If you want to start up with a pfile you can.  Typically you will first do:
SQL> create pfile from spfile;
Then you can edit the pfile, knowing that everything is in it that was in the spfile.  Then you start up with the pfile.
SQL> startup pfile=<fully qualified pfile name>
Then you need to create an up todate spfile
SQL> create spfile from pfile;
Then you need to restart the database, so that the database will be running off of the spfile, and not the pfile.  You will find the parameter files in either $ORACLE_HOME/database, or  $ORACLE_HOME/dbs.  Best to check the date/timestamp if there are files in both locations.  The spfile tends to have a timestamp very close to the sysdate of the OS. 
Run SQL> create pfile from spfile;
Find where the pfile has just been created, and then you have verified the default location of your parameter files.

Migrating to a Server Parameter file:
To see if you are running in spfile mode, log into SQL*Plus:
SQL> show parameter spfile
If this parameter is not defined, then you are not running in spfile mode.  If it is defined, then it will show you the fully qualified path name to this file. 

·         You may also need to ftp a copy of the pfile from your client based machine, across to your server.  In a RAC environment, you must combine all of your parameters into a single pfile:
o   In a single instance the entry may look like this:
o   In a RAC environment the entry may look like this:
·         Create a spfile
SQL> create spfile from pfile;
The database does not need to be open in order to create an spfile.
·         Finally you can start or restart the database, so that the database is running in spfile mode.

When you create a spfile or pfile.  If the file already exists, it is just overwritten.  You can also create a spfile from what is currently configured in the SGA memory.
SQL> create spfile from memory;
This may be a good idea, if you are not sure what settings have been applied over a period of time, and which of them were applied to memory only and which ones were applied to the spfile.  By creating a spfile from memory, you will create a spfile that contains the current instance settings.  Then you just restart again, and the parameters are then embedded permanently in the spfile.

On Unix and linux the pfile’s default name is: initORACLE_SID.ora, the spfile’s default name is spfileORACLE_SID.ora, and the default location is: $ORACLE_HOME/dbs.  With ASM present then the default location is the same diskgroup as the datafiles.

On Windows the pfile’s default name is: initORACLE_SID.ora, the spfile’s default name is spfileORACLE_SID.ora, and the default location is: %ORACLE_HOME%\database.  With ASM present then the default location is the same diskgroup as the datafiles.

If you create a spfile that is not in the default location, then you must create in the default pfile location a “stub” pfile that points to the fully qualified spfile name.  With ASM, you will also find a “stub” pfile pointing to the diskgroup where the spfile resides.

The SPFILE parameter contains the name and location of the current SPFILE.  This will work when the default spfile is used by the database server.  Unless you startup the database and specify a pfile to startup with.

Changing initialization parameter values
The ALTER SYSTEM enables you to SET, CHANGE, or RESTORE default values the initialization parameters.  If you are using a pfile, then you will need to manually update the pfile and restart, in order to make the values permanent.
The spfile overcomes the problem of parameter values not being persistent over database start-ups and shutdowns.
There are two kinds of initialization parameters:
·         Dynamic parameters:
Can be changed for the current database instance, and the changes take place immediately.  If the SCOPE = both was used with the ALTER SYSTEM statement, then the new values will persist through shutdowns and start-ups.
·         Static parameters:
With these parameters you can sometimes change them in the spfile, but not in memory.  Which means that they will take effect after a restart.  Otherwise you will need to add them to an updated pfile, start the database from the pfile, and create a spfile from the pfile.  Then they will be in effect and persist through shutdowns and start-ups.

The SET clause of the ALTER SYSTEM statement is how we change the parameters.  The optional SCOPE clause specifies the scope of the change.

The change is made to the server parameter file(spfile) only.
·         No change is made to the current instance
·         For both static and dynamic parameters the change is effective at the next startup and is persistent.
·         You can modify static parameters in this way with the ALTER SYSTEM statement.

The change is applied to memory only. 
·         The change is made to the current instance, and is effective immediately
·         For dynamic parameters, the effect is immediate, but it is not persistent, because the spfile is not updated
·         For static parameters, this specification is not allowed.

The change is applied in the spfile and in memory.
·         The change is made to the current instance, and is effective immediately
·         For dynamic parameters, the effect is persistent, because the spfile has been updated.
·         For static parameters, this specification is not allowed

If you started up with a pfile, you can specify SCOPE=MEMORY.  If you started up with a spfile, then you can also specify SCOPE=SPFILE or BOTH.

For dynamic parameters, you can also specify the DEFERRED keyword.  When you specify deferred, then the change is only effective for subsequent sessions.

When you specify SCOPE=SPFILE or BOTH, then you can specify the optional COMMENT, which is written to the spfile.

       COMMENT='Increase from 10 to prevent the user becoming locked.'

A value could also consist of a list of attributes.  In such a case, you can’t update the individual attributes, but will need to provide the complete description.
COMMENT=’Added the third log archive destination on August 2012 – Fred Redhead Consultants’

Clearing Initialization Parameter settings
ALTER SYSTEM RESET command will clear the setting/value in the parameter file.  This is useful, because then after you restart the database, the particular parameter will take on the default value.

The SCOPE clause is not required, but if used SCOPE=SPFILE is the only valid value that can be used.

Exporting the spfile
You can get a text copy of the spfile by issuing this command:  
SQL> create pfile=’<fully qualified name>’ from spfile ‘<fully qualified name>’;

This is a useful tool for examining the current values in the spfile.

Creating a pfile is also part of the process, when you want to add static parameters to the spfile, by first creating pfile from the spfile, editing this pfile, and then starting the database with the pfile.  You finish up by creating a spfile from the pfile, and restarting the database.

If you want to have a description  of what parameters have been defined in memory as well as in the pfile, then you could issue this command.
SQL> create pfile from memory;

To create new parameters using the pfile you  go through this process:
SQL> create pfile from spfile;
Edit the pfile, and when you are happy.
SQL> startup pfile=’<full pfile path>’
SQL> create spfile from pfile
Then you restart the database, so that the database is running in spfile mode.

To examine the parameters that are defined in the database you can also use these methods:
SQL> select name,value from v$parameter where name = ‘<parameter name>’;
SQL> select name,value from v$parameter2 where name = ‘<parameter name>’;
SQL> show parameter <parameter name>

If you are using RMAN to backup your database, then RMAN automatically creates a backup copy of your spfile.  You can also create a specific backup of the current spfile using RMAN. 

Recovering a lost or damaged Server Parameter File
If your spfile becomes lost or corrupted.  The instance may fail, or a restart of the instance may fail.  However there are few ways to recover the spfile.

·         If the instance is running
SQL> create spfile from memory;

·         If the pfile is valid and up to date, then you can recreate the spfile from the pfile
SQL> create spfile from pfile;

·         If you have made a RMAN backup, then you can restore the spfile from backup.

·         If none of the above methods are possible, then you can try this approach
o   Create a new pfile.  When an instance starts up, the initialization parameters used during startup are written to the alert log.  Use these values to create your new pfile.
o   Start the database up using the pfile
o   Then create the spfile from the pfile

Read/Write errors during a parameter update
If an error occurs during a parameter update, then the error is reported to the alert log.  After this all subsequent updates to the spfile are ignored.  There are two approaches you can take to this.  Firstly you can shut down the instance, and recover the spfile as described above.  Secondly you can continue to run the database, until you have a maintenance slot to recover the spfile.  In the meantime you will not be able to change any parameters in the spfie.

Viewing database parameter values
Show parameters            Shows parameter values for the current session.
Show spparameters       shows parameter values for the spfile
Create pfile                        Gives you a text file with all the spfile or memory parameters
V$parameter                     Parameter values for the current session
V$parameter2                   parameter values for current session, better output format
V$system_parameter    parameter values for the instance
V$system_parameter2 parameter values for the instance, better output format

Mark Tiger,
Need a database health check, remote monitoring, support & maintenance, or a security Audit?

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.

1 comment:

  1. BlueHost is ultimately the best web-hosting provider with plans for any hosting needs.