Database Configuration on the Fly

---

When it comes to availability, one of the most important advancements within the RDBMS has been the ability to reconfigure the database without shutting it down. This starts with the initialization parameters: forever bound to the realm of the init.ora, changes in the init parameters have traditionally meant a database bounce to reconfigure the instance. That is typically not the case anymore, with most parameters now dynamically changeable with a SQL ALTER command:

SQL> alter system set shared_pool_size=300000000; SQL> alter session set nls_date_formate='MM-DD-YYYY'; 

spfile or init.ora?

The spfile was introduced in Oracle9i as an alternative to the ASCII text file init.ora for enabling or disabling system parameters for the database. It is a binary file that can only be modified via SQL commands in the database-you cannot edit this file manually, as you can the init.ora. This has led to an outpouring of grief and consternation on the part of the Oracle community, and rightly so, as the power to quickly review and update the init.ora has been a favorite of DBAs for years.

But the spfile was introduced for a very specific purpose: to be able to make a change dynamically in the database that was also permanently for the duration of the database. Until the spfile came along, if you issued an ALTER SYSTEM command, the change was made to your instance, but when you rebooted, the value reverted to whatever was in the init.ora. To combat this, you had to make sure that you issued the ALTER SYSTEM and then also updated the init.ora manually. The spfile, on the other hand, can take an ALTER SYSTEM command issued against the database and store it permanently for the next reboot. Of course, we could have had the best of both worlds, where the spfile was still just a human-readable text file that Oracle could also use… but alas, it is a binary file. To review the contents of the spfile, you can issue a command from SQL*Plus:

SQL> show parameter

You can limit this to a subset by naming a word string that Oracle will match against the parameter name list:

SQL> show parameter log_archive

You can also dump the spfile to an init file with a SQL command. Or, you can create an spfile from an init.ora file. This process can be used to manually modify a value-you first dump the spfile to an init.ora file, then make the change, and then rebuild the spfile from the init file.

SQL> create pfile='/u01/oracle/ora10/dbs/initORCL.ora' from spfile; SQL> create spfile from pfile='/u01/oracle/ora10/dbs/initORCL.ora';

To change a parameter value in the spfile, you issue the ALTER SYSTEM command, then provide the scope. The scope refers to how you want the change applied-just for the duration of the currently open database (Scope=Memory), or permanently each time the database is started (Scope=spfile). You can also choose both, like this:

alter system set shared_pool_size=300000000 scope=spfile; alter system set shared_pool_size=300000000 scope=both; 

So which should you use? Init or spfile? We personally like the spfile, despite the added headaches of making certain changes and the added SQL complexity. Once utilized, the spfile can be backed up and restored using RMAN, so we don't have to consider it as part of the OS file system backup. It also means we don't have to bother with init.ora configuration to make a dynamic change. As an HADBA, these are compelling reasons for adoption. However, if you've been in the game a while, it may be that you cannot give up the init.ora. Stick with what you are comfortable with.

Nondynamic Parameters

Because nearly every configuration parameter is dynamic, it actually makes more sense to talk about those few that are static and require a reboot. These are the changes that will require you to bring the database down. Some of them require only that the particular instance be down, but the clustered database can still be up-you just take down one thread at a time. Others require that the entire database be down, as the change affects all clustered nodes. Those parameters that require the cluster to be down will be discussed in Chapter 4. In Table 2-1 we provide a reference list of those parameters that cannot be modified with an ALTER SYSTEM or ALTER SESSION command.

Table 2-1: Static Database Parameters

Parameter Name	Notes
ACTIVE_INSTANCE_COUNT	RAC parameter
AUDIT_SYS_OPERATIONS
AUDIT_TRAIL
BACKGROUND_CORE_DUMP
BITMAP_MERGE_AREA_SIZE	BITMAP_MERGE_AREA_SIZE has been deprecated in favor of PGA_AGGREGATE_TARGET
BLANK_TRIMMING
CLUSTER_DATABASE	RAC parameter
CLUSTER_DATABASE_INSTANCES	RAC parameter
CLUSTER_INTERCONNECTS	RAC parameter
COMMIT_POINT_STRENGTH
COMPATIBLE
CONTROL_FILES
CPU_COUNT
CREATE_BITMAP_AREA_SIZE	CREATE_BITMAP_AREA_SIZE has been deprecated in favor of PGA_AGGREGATE_TARGET.
CURSOR_SPACE_FOR_TIME
DB_BLOCK_BUFFERS	DB_BLOCK_BUFFERS has been deprecated in favor of DB_CACHE_SIZE.
DB_BLOCK_SIZE	Can't change this one-ever
DB_DOMAIN
DB_FILES	DB_FILES must be the same for all instances in RAC.
DB_NAME
DB_UNIQUE_NAME
DB_WRITER_PROCESSES
DBWR_IO_SLAVES
DISK_ASYNCH_IO
DISTRIBUTED_LOCK_TIMEOUT
DML_LOCKS
ENQUEUE_RESOURCES
EVENT
FILEIO_NETWORK_ADAPTERS
GC_FILES_TO_LOCKS	RAC parameter
GCS_SERVER_PROCESSES	RAC parameter
GLOBAL_CONTEXT_POOL_SIZE
HI_SHARED_MEMORY_ADDRESS
IFILE
INSTANCE_GROUPS	RAC parameter
INSTANCE_NAME	RAC parameter
INSTANCE_NUMBER	RAC parameter
INSTANCE_TYPE	RAC parameter
JAVA_MAX_SESSIONSPACE_SIZE
JAVA_SOFT_SESSIONSPACE_LIMIT
LOCK_NAME_SPACE
LOCK_SGA
LOG_ARCHIVE_FORMAT
LOG_BUFFER
LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
MAX_ENABLED_ROLES
O7_DICTIONARY_ACCESSIBILITY
OPEN_LINKS
OPEN_LINKS_PER_INSTANCE
OS_AUTHENT_PREFIX
OS_ROLES
PARALLEL_AUTOMATIC_TUNING	PARALLEL_AUTOMATIC_TUNING has been deprecated. See Oracle Database 10g Reference Guide.
PARALLEL_EXECUTION_MESSAGE_SIZE
PRE_PAGE_SGA
PROCESSES
RDBMS_SERVER_DN
READ_ONLY_OPEN_DELAYED
RECOVERY_PARALLELISM
REMOTE_ARCHIVE_ENABLE
REMOTE_LOGIN_PASSWORDFILE
REMOTE_OS_AUTHENT
REMOTE_OS_ROLES
REPLICATION_DEPENDENCY_TRACKING
ROLLBACK_SEGMENTS	ROLLBACK_SEGMENTS has been deprecated in favor of UNDO_MANAGEMENT.
SERIAL _REUSE
SESSION_MAX_OPEN_FILES
SESSIONS
SGA_MAX_SIZE
SHADOW_CORE_DUMP
SHARED_MEMORY_ADDRESS
SHARED_POOL_RESERVED_SIZE
SMTP_OUT_SERVER
SPFILE
SQL92_SECURITY
TAPE_ASYNCH_IO
THREAD	RAC parameter
TRANSACTIONS
TRANSACTIONS_PER_ROLLBACK_SEGMENT
UNDO_MANAGEMENT
USE_INDIRECT_DATA_BUFFERS
UTL_FILE_DIR	Use DIRECTORIES instead of UTL_FILE_DIR for dynamic changes-i.e., create directory '/u02/out';.

At this time, there are 218 published parameters for Oracle Database 10g, and inumerable unpublished parameters (starting with an underscore). We have not listed them all, but there are 82 static parameters in Table 2-1 that range from important to wildly obscure. We have listed in alphabetical order and then put in bold those parameters that typically you should be concerned with, as they will affect your database and its operations. Some of these have been deprecated in favor of new parameters. We have noted these, as well as the new parameters (if any), in Table 2-1.

In addition, there is an interesting subset of parameters that can be modified at the session level but not at the system level, meaning you can change them for a particular session but not for the entire system. In a sense, this makes them nondynamic because you cannot make a permanent change without bouncing the instance. The impact is small, as you can see from the type of parameters-they have to do more with session-level issues and typically do not affect the entire database. However, we felt it important to make the distinction here, as shown in Table 2-2.

Table 2-2: Session-Only Dynamic Parameters

Parameter Name	Parameter Name
DB_FILE_NAME_CONVERT	NLS_TERRITORY
HASH_AREA_SIZE	NLS_TIMESTAMP_FORMAT
NLS_CALENDAR	NLS_TIMESTAMP_TZ_FORMAT
NLS_COMP	OBJECT_CACHE_MAX_SIZE_PERCENT
NLS_CURRENCY	OBJECT_CACHE_OPTIMAL_SIZE
NLS_DATE_FORMAT	OLAP_PAGE_POOL_SIZE
NLS_DATE_LANGUAGE	PARALLEL_MIN_PERCENT
NLS_DUAL_CURRENCY	SESSION_CACHED_CURSORS
NLS_ISO_CURRENCY	SORT_AREA_RETAINED_SIZE
NLS_LANGUAGE	SORT_AREA_SIZE
NLS_NUMERIC_CHARACTERS	TRACEFILE_IDENTIFIER
NLS_SORT

---