2 Creating an Oracle Database

Planning for Database Creation

Prepare to create the database by research and careful planning. Table 2-1 lists some recommended actions:

Meeting Creation Prerequisites

Before you can create a new database, the following prerequisites must be met:

• The desired Oracle software must be installed. This includes setting various environment variables unique to your operating system and establishing the directory structure for software and database files.

• You must have the operating system privileges associated with a fully operational database administrator. You must be specially authenticated by your operating system or through a password file, allowing you to start up and shut down an instance before the database is created or opened. This authentication is discussed in "Database Administrator Authentication".

• Sufficient memory must be available to start the Oracle Database instance.

• Sufficient disk storage space must be available for the planned database on the computer that runs Oracle Database.

All of these are discussed in the Oracle Database installation guide specific to your operating system. If you use the Oracle Universal Installer, it will guide you through your installation and provide help in setting environment variables and establishing directory structure and authorizations.

Step 1: Decide on Your Instance Identifier (SID)

An instance is made up of the system global area (SGA) and the background processes of an Oracle Database. Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on your system.

The following example sets the SID for the instance and database we are about to create:

% setenv ORACLE_SID mynewdb

The value of the DB_NAME initialization parameter should match the SID setting.

Step 2: Establish the Database Administrator Authentication Method

You must be authenticated and granted appropriate system privileges in order to create a database. You can use the password file or operating system authentication method. Database administrator authentication and authorization is discussed in the following sections of this book:

• "Database Administrator Security and Privileges"

• "Database Administrator Authentication"

• "Creating and Maintaining a Password File"

Step 3: Create the Initialization Parameter File

The instance for any Oracle Database is started using an initialization parameter file. One way to create the initialization parameter file is to edit a copy of the sample initialization parameter file that Oracle provides on the distribution media, or the sample presented in this book.

For convenience, store your initialization parameter file in the Oracle Database default location, using the default name. Then when you start your database, it will not be necessary to specify the PFILE parameter of the STARTUP command, because Oracle Database automatically looks in the default location for the initialization parameter file.

Default initialization parameter file locations are shown in the following table:

The following is the initialization parameter file used to create the mynewdb database on a UNIX system.

control_files              = (/u0d/lcg03/control.001.dbf,
                              /u0d/lcg03/control.002.dbf,
                              /u0d/lcg03/control.003.dbf)
db_name                    = lcg03

db_domain                  = us.oracle.com

log_archive_dest_1         =
"LOCATION=/net/fstlcg03/private/yaliu/testlog/log.lcg03.fstlcg03/lcg03/arch"
log_archive_dest_state_1   = enable

db_block_size              = 8192
pga_aggregate_target       = 2500M

processes                  = 1000
sessions                   = 1200
open_cursors               = 1024

undo_management            = AUTO

shared_servers             = 3
remote_listener            = tnsfstlcg03

undo_tablespace            = smu_nd1
compatible                 = 10.1.0.0.0

sga_target                 = 1500M

nls_language               = AMERICAN
nls_territory              = AMERICA
db_recovery_file_dest      =
/net/fstlcg03/private/yaliu/testlog/log.lcg03.fstlcg03/lcg03/arch
db_recovery_file_dest_size = 100G

Step 4: Connect to the Instance

Start SQL*Plus and connect to your Oracle Database instance AS SYSDBA.

$ SQLPLUS /nolog
CONNECT SYS/password AS SYSDBA

Step 5: Create a Server Parameter File (Recommended)

Oracle recommends that you create a server parameter file as a dynamic means of maintaining initialization parameters. The server parameter file is discussed in "Managing Initialization Parameters Using a Server Parameter File".

The following script creates a server parameter file from the text initialization parameter file and writes it to the default location. The script can be executed before or after instance startup, but after you connect as SYSDBA. The database must be restarted before the server parameter file takes effect.

-- create the server parameter file 
CREATE SPFILE='/u01/oracle/dbs/spfilemynewdb.ora' FROM
       PFILE='/u01/oracle/admin/initmynewdb/scripts/init.ora';
SHUTDOWN 
-- the next startup will use the server parameter file
EXIT

Step 6: Start the Instance

Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database. Use the STARTUP command with the NOMOUNT clause. In this example, because the server parameter file is stored in the default location, you are not required to specify the PFILE clause:

STARTUP NOMOUNT

At this point, the SGA is created and background processes are started in preparation for the creation of a new database. The database itself does not yet exist.

Step 7: Issue the CREATE DATABASE Statement

To create the new database, use the CREATE DATABASE statement. The following statement creates database mynewdb:

CREATE DATABASE mynewdb
   USER SYS IDENTIFIED BY pz6r58
   USER SYSTEM IDENTIFIED BY y1tz5p
   LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE tbs_1
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' 
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs 
      DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

A database is created with the following characteristics:

• The database is named mynewdb. Its global database name is mynewdb.us.oracle.com. See "DB_NAME Initialization Parameter" and "DB_DOMAIN Initialization Parameter".

• Three control files are created as specified by the CONTROL_FILES initialization parameter, which was set before database creation in the initialization parameter file. See "Sample Initialization Parameter File " and "Specifying Control Files ".

• The password for user SYS is pz6r58 and the password for SYSTEM is y1tz5p. The two clauses that specify the passwords for SYS and SYSTEM are not mandatory in this release of Oracle Database. However, if you specify either clause, you must specify both clauses. For further information about the use of these clauses, see "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM".

• The new database has three redo log files as specified in the LOGFILE clause. MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY define limits for the redo log. See Chapter 6, " Managing the Redo Log".

• MAXDATAFILES specifies the maximum number of datafiles that can be open in the database. This number affects the initial sizing of the control file.

  
  

  Note: You can set several limits during database creation. Some of these limits are limited by and affected by operating system limits. For example, if you set MAXDATAFILES, Oracle Database allocates enough space in the control file to store MAXDATAFILES filenames, even if the database has only one datafile initially. However, because the maximum control file size is limited and operating system dependent, you might not be able to set all CREATE DATABASE parameters at their theoretical maximums. For more information about setting limits during database creation, see the Oracle Database SQL Reference and your operating system specific Oracle documentation.

  
  

• MAXINSTANCES specifies that only one instance can have this database mounted and open.

• The US7ASCII character set is used to store data in this database.

• The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET, used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.

• The SYSTEM tablespace, consisting of the operating system file /u01/oracle/oradata/mynewdb/system01.dbf is created as specified by the DATAFILE clause. If a file with that name already exists, it is overwritten.

• The SYSTEM tablespace is a locally managed tablespace. See "Creating a Locally Managed SYSTEM Tablespace".

• A SYSAUX tablespace is created, consisting of the operating system file /u01/oracle/oradata/mynewdb/sysaux01.dbf as specified in the SYSAUX DATAFILE clause. See "Creating the SYSAUX Tablespace".

• The DEFAULT TABLESPACE clause creates and names a default permanent tablespace for this database.

• The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary tablespace for this database. See "Creating a Default Temporary Tablespace".

• The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store undo data for this database if you have specified UNDO_MANAGEMENT=AUTO in the initialization parameter file. See "Using Automatic Undo Management: Creating an Undo Tablespace".

• Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified in this CREATE DATABASE statement. This is customary during database creation. You can later use an ALTER DATABASE statement to switch to ARCHIVELOG mode. The initialization parameters in the initialization parameter file for mynewdb relating to archiving are LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT. See Chapter 7, " Managing Archived Redo Logs".

  
  

  See Also: "Understanding the CREATE DATABASE Statement" Oracle Database SQL Reference for more information about specifying the clauses and parameter values for the CREATE DATABASE statement

  
  

Step 8: Create Additional Tablespaces

To make the database functional, you need to create additional files and tablespaces for users. The following sample script creates some additional tablespaces:

CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING 
     DATAFILE '/u01/oracle/oradata/mynewdb/users01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING 
     DATAFILE '/u01/oracle/oradata/mynewdb/indx01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;

For information about creating tablespaces, see Chapter 8, " Managing Tablespaces".

Step 9: Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build views, synonyms, and PL/SQL packages:

CONNECT SYS/password AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql
EXIT

The following table contains descriptions of the scripts:

Step 10: Run Scripts to Install Additional Options (Optional)

You may want to run other scripts. The scripts that you run are determined by the features and options you choose to use or install. Many of the scripts available to you are described in the Oracle Database Reference.

If you plan to install other Oracle products to work with this database, see the installation instructions for those products. Some products require you to create additional data dictionary tables. Usually, command files are provided to create and load these tables into the database data dictionary.

See your Oracle documentation for the specific products that you plan to install for installation and administration instructions.

Step 11: Back Up the Database.

Take a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs. For information on backing up a database, see Oracle Database Backup and Recovery Basics.

Specifying the Default Tablespace Type

The SET DEFAULT ... TABLESPACE clause of the CREATE DATABASE statement to determines the default type of tablespace for this database in subsequent CREATE TABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a smallfile tablespace, which is the traditional type of Oracle Database tablespace. A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

The use of bigfile tablespaces further enhances the Oracle-managed files feature, because bigfile tablespaces make datafiles completely transparent for users. SQL syntax for the ALTER TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.

The CREATE DATABASE statement shown in "Specifying Oracle-Managed Files at Database Creation" can be modified as follows to specify that the default type of tablespace is a bigfile tablespace:

CREATE DATABASE rbdb1
     USER SYS IDENTIFIED BY pz6r58
     USER SYSTEM IDENTIFIED BY y1tz5p
     SET DEFAULT BIGFILE TABLESPACE
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1;

To dynamically change the default tablespace type after database creation, use the SET DEFAULT TABLESPACE clause of the ALTER DATABASE statement:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

You can determine the current default tablespace type for the database by querying the DATABASE_PROPERTIES data dictionary view as follows:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES 
   WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';

Overriding the Default Tablespace Type

The SYSTEM and SYSAUX tablespaces are always created with the default tablespace type. However, you can explicitly override the default tablespace type for the UNDO and DEFAULT TEMPORARY tablespace during the CREATE DATABASE operation.

For example, you can create a bigfile UNDO tablespace in a database with the default tablespace type of smallfile as follows:

CREATE DATABASE rbdb1
...
     BIGFILE UNDO TABLESPACE undotbs
        DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

You can create a smallfile DEFAULT TEMPORARY tablespace in a database with the default tablespace type of bigfile as follows:

CREATE DATABASE rbdb1
   SET DEFAULT BIGFILE TABLSPACE
...
     SMALLFILE DEFAULT TEMPORARY TABLESPACE tempts1
        TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' 
        SIZE 20M REUSE
...

Specifying the Database Time Zone

You set the database default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone. The database time zone can be changed for a session with an ALTER SESSION statement.

Specifying the Database Time Zone File

This section provides information on the time zone files used to support the database time zone, specifically on Solaris platforms. Names of directories, filenames, and environment variables may differ for on other platforms but will probably be the same for all UNIX platforms.

Two time zone files are provided in the Oracle Database installation directory:

• $ORACLE_HOME/oracore/zoneinfo/timezone.dat

  This is the default file. It contains the most commonly used time zones and is smaller, thus enabling better database performance.

• $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

  This file contains a larger set of defined time zones and should be used if you require zones that are not defined in the default timezone.dat file. Use of this larger set of time zone information can affect performance.

To enable the use of the larger time zone datafile, do the following:

1. Shut down the database.

2. Set the environment variable ORA_TZFILE to the full path name of the location for the timezlrg.dat file.

3. Restart the database.

Once the larger timezlrg.dat is used, it must continue to be used unless you are sure that none of the nondefault zones are used for data that is stored in the database. Also, all databases that share information must use the same time zone datafile.

The time zone files contain the valid time zone names. The following information is included for each zone (note that abbreviations are only used in conjunction with the zone names):

• Offset from UTC (formerly GMT)

• Transition times for daylight savings

• Abbreviation for standard time

• Abbreviation for daylight savings time

To view the time zone names in the file being used by your database, use the following query:

SELECT * FROM V$TIMEZONE_NAMES;

The time zone names contained in both the larger and smaller time zone files are listed in the Oracle Database Globalization Support Guide.

Using the FORCE LOGGING Clause

To put the database into FORCE LOGGING mode, use the FORCE LOGGING clause in the CREATE DATABASE statement. If you do not specify this clause, the database is not placed into FORCE LOGGING mode.

Use the ALTER DATABASE statement to place the database into FORCE LOGGING mode after database creation. This statement can take a considerable time for completion, because it waits for all unlogged direct writes to complete.

You can cancel FORCE LOGGING mode using the following SQL statement:

ALTER DATABASE NO FORCE LOGGING;

Independent of specifying FORCE LOGGING for the database, you can selectively specify FORCE LOGGING or NO FORCE LOGGING at the tablespace level. However, if FORCE LOGGING mode is in effect for the database, it takes precedence over the tablespace setting. If it is not in effect for the database, then the individual tablespace settings are enforced. Oracle recommends that either the entire database is placed into FORCE LOGGING mode, or individual tablespaces be placed into FORCE LOGGING mode, but not both.

The FORCE LOGGING mode is a persistent attribute of the database. That is, if the database is shut down and restarted, it remains in the same logging mode. However, if you re-create the control file, the database is not restarted in the FORCE LOGGING mode unless you specify the FORCE LOGGING clause in the CREATE CONTROL FILE statement.

Performance Considerations of FORCE LOGGING Mode

FORCE LOGGING mode results in some performance degradation. If the primary reason for specifying FORCE LOGGING is to ensure complete media recovery, and there is no standby database active, then consider the following:

• How many media failures are likely to happen?

• How serious is the damage if unlogged direct writes cannot be recovered?

• Is the performance degradation caused by forced logging tolerable?

If the database is running in NOARCHIVELOG mode, then generally there is no benefit to placing the database in FORCE LOGGING mode. Media recovery is not possible in NOARCHIVELOG mode, so if you combine it with FORCE LOGGING, the result may be performance degradation with little benefit.

DB_NAME Initialization Parameter

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database name in the control file are not the same, the database does not start.

DB_DOMAIN Initialization Parameter

DB_DOMAIN is a text string that specifies the network domain where the database is created. This is typically the name of the organization that owns the database. If the database you are about to create will ever be part of a distributed database system, give special attention to this initialization parameter before database creation.

DB_BLOCK_SIZE Initialization Parameter

The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific, which is generally adequate.

You cannot change the block size after database creation except by re-creating the database. If the database block size is different from the operating system block size, ensure that the database block size is a multiple of the operating system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:

DB_BLOCK_SIZE=4096

A larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Therefore, consider specifying a block size larger than your operating system block size if the following conditions exist:

• Oracle Database is on a large computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater.

• The operating system that runs Oracle Database uses a small operating system block size. For example, if the operating system block size is 1K and the default data block size matches this, the database may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.

  
  

  See Also: Your operating system specific Oracle documentation for details about the default block size.

  
  

Nonstandard Block Sizes

Tablespaces of nonstandard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.

To use nonstandard block sizes, you must configure subcaches within the buffer cache area of the SGA memory for all of the nonstandard block sizes that you intend to use. The initialization parameters used for configuring these subcaches are described in the next section, "Managing the System Global Area (SGA) ".

The ability to specify multiple block sizes for your database is especially useful if you are transporting tablespaces between databases. You can, for example, transport a tablespace that uses a 4K block size from an OLTP environment to a data warehouse environment that uses a standard block size of 8K.

Components and Granules in the SGA

The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.

You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, the database actually allocates 12 MB.

Limiting the Size of the SGA

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA_MAX_SIZE.

If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE.

Using Automatic Shared Memory Management

You enable the automatic shared memory management feature by setting the SGA_TARGET parameter. This parameter in effect replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically managed. In addition, you must ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL (the default) or ALL.

The SGA_TARGET initialization parameter reflects the total size of the SGA. Table 2-3 lists the SGA components for which SGA_TARGET includes memory and the initialization parameters corresponding to those components.

The parameters listed in Table 2-4, if they are set, take their memory from SGA_TARGET, leaving what is available for the four components listed in Table 2-3.

When you are migrating from a manual management scheme, execute the following query on the instance running in manual mode to get a value for SGA_TARGET:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

You can then remove the parameters that formerly controlled memory for the now automatically sized components.

For example, suppose you currently have the following configuration of parameters on a manual mode instance with SGA_MAX_SIZE set to 1200M:

• SHARED_POOL_SIZE = 200M

• DB_CACHE_SIZE = 500M

• LARGE_POOL_SIZE=200M

Also assume that the result of the queries is as follows:

SELECT SUM(value) FROM V$SGA = 1200M
SELECT CURRENT_VALUE FROM V$SGA_DYNAMIC_FREE_MEMORY = 208M

You can take advantage of automatic shared memory management by replacing the manually sized parameters (SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE) with the following setting:

SGA_TARGET = 992M

where 992M = 1200M minus 208M.

By default, when you set a value for SGA_TARGET, the value of the parameters corresponding to all the automatically sized SGA components is set to zero. However, you can still exercise some control over the size of the automatically sized components by specifying minimum values for the parameters corresponding to these components. Doing so can be useful if you know that an application cannot function properly without a minimum amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corresponding initialization parameter. Here is an example configuration:

• SGA_TARGET = 256M

• SHARED_POOL_SIZE = 32M

• DB_CACHE_SIZE = 100M

In this example, the shared pool and the default buffer pool will not be sized smaller than the specified values (32 M and 100M, respectively). The remaining 124M (256 minus 132) is available for use by all the manually and automatically sized components.

The actual distribution of values among the SGA components might look like this:

• Actual shared pool size = 64M

• Actual buffer cache size = 128M

• Actual Java pool size = 60M

• Actual large pool size = 4M

The parameter values determine the minimum amount of SGA space allocated. The fixed view V$SGA_DYNAMIC_COMPONENTS displays the current actual size of each SGA component. You can also see the current actual values of the SGA components in the Enterprise Manager memory configuration page.

Manually limiting the minimum size of one or more automatically sized components reduces the total amount of memory available for dynamic adjustment. This reduction in turn limits the ability of the system to adapt to workload changes. Therefore, this practice is not recommended except in exceptional cases. The default automatic management behavior maximizes both system performance and the use of available resources.

When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithmn only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.

DB_BLOCK_SIZE=4096

DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=8M
DB_8K_CACHE_SIZE=4M

Specifying the Maximum Number of Processes

The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes.

If you plan on running 50 user processes, a good estimate would be to set the PROCESSES initialization parameter to 60.

Specifying the Method of Undo Space Management

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Collectively these records are called undo data.

The use of manual undo management mode, which stores undo data in rollback segments, has been deprecated. This section provides instructions for setting up an environment for automatic undo management using an undo tablespace. An undo tablespace is easier to administer than rollback segments, and automatic undo management lets you explicitly set a retention guarantee time.

The COMPATIBLE Initialization Parameter and Irreversible Compatibility

The COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format on disk. For example, if you create an Oracle Database 10g database, but specify COMPATIBLE = 9.2.0.2 in the initialization parameter file, then features that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2 compatibility level.

You can advance the compatibility level of your database. If you do advance the compatibility of your database with the COMPATIBLE initialization parameter, then there is no way to start the database using a lower compatibility level setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.

The default value for the COMPATIBLE parameter is the release number of the most recent major release.

Setting the License Parameter

If you use named user licensing, Oracle Database can help you enforce this form of licensing. You can set a limit on the number of users created in the database. Once this limit is reached, you cannot create more users.

To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database initialization parameter file, as shown in the following example:

LICENSE_MAX_USERS = 200

What Is a Server Parameter File?

A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent. It also provides a basis for self-tuning by the Oracle Database server.

A server parameter file is initially built from a traditional text initialization parameter file using the CREATE SPFILE statement. It is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings.

At system startup, the default behavior of the STARTUP command is to read a server parameter file to obtain initialization parameter settings. The STARTUP command with no PFILE clause reads the server parameter file from an operating system specific location. If you use a traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command. Instructions for starting an instance using a server parameter file are contained in "Starting Up a Database".

Migrating to a Server Parameter File

If you are currently using a traditional initialization parameter file, use the following steps to migrate to a server parameter file.

1. If the initialization parameter file is located on a client machine, transfer the file (for example, FTP) from the client machine to the server machine.

   
   

   Note: If you are migrating to a server parameter file in an Oracle Real Application Clusters environment, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for instances that are part of an Oracle Real Application Clusters installation, are discussed in: Oracle Real Application Clusters Installation and Configuration Guide Oracle Real Application Clusters Administrator's Guide

   
   

2. Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

3. Start up the instance using the newly created server parameter file.

Creating a Server Parameter File

The server parameter file is initially created from a traditional text initialization parameter file. It must be created prior to its use in the STARTUP command. The CREATE SPFILE statement is used to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

The following example creates a server parameter file from initialization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

Another example, which follows, illustrates creating a server parameter file and supplying a name.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
       FROM PFILE='/u01/oracle/dbs/test_init.ora';

When you create a server parameter file from an initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All other comments are ignored.

The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.

Oracle recommends that you allow the database server to default the name and location of the server parameter file. This will ease administration of your database. For example, the STARTUP command assumes this default location to read the parameter file. The table that follows shows the default name and location of the server parameter file. The table assumes that the SPFILE is a file. If it is a raw device, the default name could be a logical volume name or partition device name, and the default location could differ.

If you create a server parameter file in a location other than the default location, then you must create a parameter file with the default parameter file name in the default location that points to the user-generated SPFILE. The parameter file would contain one line:

SPFILE = 'location'

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to re-create the same server parameter file that is currently being used by the instance.

The SPFILE Initialization Parameter

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the server (that is, you issue a STARTUP command and do not specify a PFILE), the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.

The SPFILE parameter can also be set in a traditional parameter file to indicate the server parameter file to use. You use the SPFILE parameter to specify a server parameter file located in a nondefault location. Do not use an IFILE initialization parameter within a traditional initialization parameter file to point to a server parameter file; instead, use the SPFILE parameter. See "Starting Up a Database" for details about:

• Starting up a database that uses a server parameter file

• Using the SPFILE parameter to specify the name of a server parameter file to use at instance startup

Using ALTER SYSTEM to Change Initialization Parameter Values

The ALTER SYSTEM statement lets you set, change, or restore to default the values of initialization parameter. If you are using a traditional initialization parameter file, the ALTER SYSTEM statement changes the value of a parameter only for the current instance, because there is no mechanism for automatically updating initialization parameters on disk. You must update them manually to be passed to a future instance. Using a server parameter file overcomes this limitation.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
                 COMMENT='temporary change on Nov 29'
                 SCOPE=MEMORY;

ALTER SYSTEM 
     SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
         COMMENT='Add new destimation on Nov 29'
         SCOPE=SPFILE;

ALTER SYSTEM SET parameter = '';

Exporting the Server Parameter File

You can use the CREATE PFILE statement to export a server parameter file to a traditional text initialization parameter file. Doing so might be necessary for several reasons:

• To create backups of the server parameter file

• For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

• To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

The exported file can also be used to start up an instance using the PFILE clause.

You must have the SYSDBA or the SYSOPER system privilege to execute the CREATE PFILE statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the server parameter file:

CREATE PFILE FROM SPFILE;

Because no names were specified for the files, the database creates an initialization parameter file with a platform-specific name, and it is created from the platform-specific default server parameter file.

The following example creates a text initialization parameter file from a server parameter file, but in this example the names of the files are specified:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'
       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Backing Up the Server Parameter File

You can create a backup of your server parameter file by exporting it, as described in "Exporting the Server Parameter File". If the backup and recovery strategy for your database is implemented using Recovery Manager (RMAN), then you can use RMAN to create a backup. The server parameter file is backed up automatically by RMAN when you back up your database, but RMAN also lets you specifically create a backup of the currently active server parameter file.

Errors and Recovery for the Server Parameter File

If an error occurs while reading the server parameter file (during startup or an export operation), or while writing the server parameter file during its creation, the operation terminates with an error reported to the user.

If an error occurs while reading or writing the server parameter file during a parameter update, the error is reported in the alert file and all subsequent parameter updates to the server parameter file are ignored. At this point, you can take one of the following actions:

• Shut down the instance, recover the server parameter file, and then restart the instance.

• Continue to run the database if you do not care that subsequent parameter updates will not be persistent.

Viewing Parameter Settings

You can view parameter settings in several ways, as shown in the following table.

Deploying Services

Installations configure Oracle Database 10g services in the database giving each service a unique global name, associated performance goals, and associated importance. The services are tightly integrated with the Oracle Database and are maintained in the data dictionary. You can find service information in the following service-specific views:

• DBA_SERVICES

• ALL_SERVICES or V$SERVICES

• V$ACTIVE_SERVICES

• V$SERVICE_STATS

• V$SERVICE_EVENTS

• V$SERVICE_WAIT_CLASSES

• V$SERV_MOD_ACT_STATS

• V$SERVICE_METRICS

• V$SERVICE_METRICS_HISTORY

The following additional views also contain some information about services:

• V$SESSION

• V$ACTIVE_SESSION_HISTORY

• DBA_RSRC_GROUP_MAPPINGS

• DBA_SCHEDULER_JOB_CLASSES

• DBA_THRESHOLDS

Several Oracle Database features support services. The Automatic Workload Repository (AWR) manages the performance of services. AWR records service performance, including execution times, wait classes, and resources consumed by service. AWR alerts warn when service response time thresholds are exceeded. The dynamic views report current service performance metrics with one hour of history. Each service has quality-of-service thresholds for response time and CPU consumption.

In addition, the Database Resource Manager maps services to consumer groups. This enables you to automatically manage the priority of one service relative to others. You can use consumer groups to define relative priority in terms of either ratios or resource consumption. This is described in more detail, for example, in Oracle Real Application Clusters Deployment and Performance Guide.

Configuring Services

Services describe applications, application functions, and data ranges as either functional services or data-dependent services. Functional services are the most common mapping of workloads. Sessions using a particular function are grouped together. For Oracle*Applications, ERP, CRM, and iSupport functions create a functional division of the work. For SAP, dialog and update functions create a functional division of the work.

In contrast, data-dependent routing routes sessions to services based on data keys. The mapping of work requests to services occurs in the object relational mapping layer for application servers and TP monitors. For example, in RAC, these ranges can be completely dynamic and based on demand because the database is shared.

You can also define preconnect application services in RAC databases. Preconnect services span instances to support a service in the event of a failure. The preconnect service supports TAF preconnect mode and is managed transparently when using RAC.

In addition to application services, Oracle Database also supports two internal services: SYS$BACKGROUND is used by the background processes only and SYS$USERS is the default service for user sessions that are not associated with services.

Use the DBMS_SERVICE package or set the SERVICE_NAMES parameter to create application services on a single-instance Oracle Database. You can later define the response time goal or importance of each service through EM, either individually or by using the Enterprise Manager feature "Copy Thresholds From a Baseline" on the Manage Metrics/Edit Threshold pages. You can also do this using PL/SQL.

Using Services

Using services requires no changes to your application code. Client-side work connects to a service. Server-side work specifies the service when creating the job class for the Job Scheduler and the database links for distributed databases. Work requests executing under a service inherit the performance thresholds for the service and are measured as part of the service.

Some Security Considerations

Once the database is created, you can configure it to take advantage of Oracle Identity Management. For information on how to do this, please refer to Oracle Advanced Security Administrator's Guide.

A newly created database has at least three user accounts that are important for administering your database: SYS, SYSTEM, and SYSMAN.

Additional administrative accounts are provided by Oracle Database that should be used only by authorized users. To protect these accounts from being used by unauthorized users familiar with their Oracle-supplied passwords, these accounts are initially locked with their passwords expired. As the database administrator, you are responsible for the unlocking and resetting of these accounts.

Table 2-5 lists the administrative accounts that are provided by Oracle Database. Not all accounts may be present on your system, depending upon the options that you selected for your database.

Installing the Oracle Database Sample Schemas

The Oracle Database distribution media can includes various SQL files that let you experiment with the system, learn SQL, or create additional tables, views, or synonyms.

Oracle Database includes sample schemas that help you to become familiar with Oracle Database functionality. All Oracle Database documentation and training materials are being converted to the Sample Schemas environment as those materials are updated.

The Sample Schemas can be installed automatically by the Database Configuration Assistant, or you can install them manually. The schemas and installation instructions are described in detail in Oracle Database Sample Schemas.