2012年9月14日 星期五

Manually Creating an Oracle Database




Creating an Oracle Database

This chapter discusses the process of creating an Oracle Database, and contains the following topics:

Deciding How to Create an Oracle Database

You can create an Oracle Database in three ways:
  • Use the Database Configuration Assistant (DBCA).
    DBCA can be launched by the Oracle Universal Installer, depending upon the type of install that you select, and provides a graphical user interface (GUI) that guides you through the creation of a database. You can also launch DBCA as a standalone tool at any time after Oracle Database installation to create or make a copy (clone) of a database. Refer to Oracle Database 2 Day DBA for detailed information on creating a database using DBCA.
  • Use the CREATE DATABASE statement.
    You can use the CREATE DATABASE SQL statement to create a database. If you do so, you must complete additional actions before you have an operational database. These actions include creating users and temporary tablespaces, building views of the data dictionary tables, and installing Oracle built-in packages. These actions can be performed by executing prepared scripts, many of which are supplied for you.
    If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features. Oracle provides a sample database creation script and a sample initialization parameter file with the Oracle Database software files. Both the script and the file can be edited to suit your needs. See "Manually Creating an Oracle Database".
  • Upgrade an existing database.
    If you are already using a earlier release of Oracle Database, database creation is required only if you want an entirely new database. You can upgrade your existing Oracle Database and use it with the new release of the database software. The Oracle Database Upgrade Guide manual contains information about upgrading an existing Oracle Database.
The remainder of this chapter discusses creating a database manually.

Manually Creating an Oracle Database

This section takes you through the planning stage and the actual creation of the database.

Considerations Before Creating the Database

Database creation prepares several operating system files to work together as an Oracle Database. You need only create a database once, regardless of how many datafiles it has or how many instances access it. You can create a database to erase information in an existing database and create a new database with the same name and physical structure.
The following topics can help prepare you for database creation.

Planning for Database Creation

Prepare to create the database by research and careful planning. Table 2-1 lists some recommended actions:
Table 2-1 Database Planning Tasks
Action Additional Information
Plan the database tables and indexes and estimate the amount of space they will require.
 Part II, "Oracle Database Structure and Storage"
Part IV, "Schema Objects"
Plan the layout of the underlying operating system files your database will comprise. Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. You can distribute I/O in several ways when you install Oracle software and create your database. For example, you can place redo log files on separate disks or use striping. You can situate datafiles to reduce contention. And you can control data density (number of rows to a data block).
 Oracle Database Performance Tuning Guide
Your Oracle operating system specific documentation
Consider using Oracle-managed files and Automatic Storage Management to create and manage the operating system files that make up your database storage.
 Part III, "Automated File and Storage Management"
Select the global database name, which is the name and location of the database within the network structure. Create the global database name by setting both the DB_NAME and DB_DOMAIN initialization parameters.
 "Determining the Global Database Name"
Familiarize yourself with the initialization parameters contained in the initialization parameter file. Become familiar with the concept and operation of a server parameter file. A server parameter file lets you store and manage your initialization parameters persistently in a server-side disk file.
 "Understanding Initialization Parameters"
"What Is a Server Parameter File?"
Oracle Database Reference
Select the database character set.
All character data, including data in the data dictionary, is stored in the database character set. You must specify the database character set when you create the database.
If clients using different character sets will access the database, then choose a superset that includes all client character sets. Otherwise, character conversions may be necessary at the cost of increased overhead and potential data loss.
You can also specify an alternate character set.
Caution: AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.
Do not confuse Oracle Database database character set UTF8 (no hyphen) with database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.
Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually "?") is substituted for it. This will terminate parsing and raise an exception.
 Oracle Database Globalization Support Guide
Consider what time zones your database must support.
Oracle Database uses one of two time zone files as the source of valid time zones. The default time zone file is timezonelrg.dat. It contains more time zones than the other time zone file, timezone.dat.
 "Specifying the Database Time Zone File"
Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created.
The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four nonstandard block sizes when creating tablespaces.
 "Specifying Database Block Sizes"
Determine the appropriate initial sizing for the SYSAUX tablespace.
 "Creating the SYSAUX Tablespace"
Plan to use a default tablespace for non-SYSTEM users to prevent inadvertent saving of database objects in the SYSTEM tablespace.
 "Creating a Default Permanent Tablespace"
Plan to use an undo tablespace to manage your undo data.
 Chapter 10, "Managing the Undo Tablespace"
Develop a backup and recovery strategy to protect the database from failure. It is important to protect the control file by multiplexing, to choose the appropriate backup mode, and to manage the online and archived redo logs.
 Chapter 6, "Managing the Redo Log"
Chapter 7, "Managing Archived Redo Logs"
Chapter 5, "Managing Control Files"
Oracle Database Backup and Recovery Basics
Familiarize yourself with the principles and options of starting up and shutting down an instance and mounting and opening a database.
 Chapter 3, "Starting Up and Shutting Down"

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.

Creating the Database

This section presents the steps involved when you create a database manually. These steps should be followed in the order presented. The prerequisites described in the preceding section must already have been completed. That is, you have established the environment for creating your Oracle Database, including most operating system dependent environmental variables, as part of the Oracle software installation process.
Step 1: Decide on Your Instance Identifier (SID)
Step 2: Establish the Database Administrator Authentication Method
Step 3: Create the Initialization Parameter File
Step 4: Connect to the Instance
Step 5: Create a Server Parameter File (Recommended)
Step 6: Start the Instance
Step 7: Issue the CREATE DATABASE Statement
Step 8: Create Additional Tablespaces
Step 9: Run Scripts to Build Data Dictionary Views
Step 10: Run Scripts to Install Additional Options (Optional)
Step 11: Back Up the Database.
The examples shown in these steps create an example database mynewdb.
Notes:
  • The steps in this section contain cross-references to other parts of this book and to other books. These cross-references take you to material that will help you to learn about and understand the initialization parameters and database structures with which you are not yet familiar.
  • If you are using Oracle Automatic Storage Management to manage your disk storage, you must start the ASM instance and configure your disk groups before performing the following steps. For information about Automatic Storage Management, see Chapter 12, "Using Automatic Storage Management".

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 for UNIX operating systems sets the SID for the instance that you will connect to in Step 4: Connect to the Instance:
% setenv ORACLE_SID mynewdb

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:

Step 3: Create the Initialization Parameter File

When an Oracle instance starts, it reads an initialization parameter file. This file can be a read-only text file, which must be modified with a text editor, or a read/write binary file, which can be modified dynamically by the database (for tuning) or with SQL commands that you submit. The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you can optionally create a server parameter file from the text file.
One way to create the text 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.
Note:
On Unix operating systems, the Oracle Universal Installer installs a sample text initialization parameter file in the following location: 
$ORACLE_HOME/dbs/init.ora
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 clause of the STARTUP command, because Oracle Database automatically looks in the default location for the initialization parameter file.
For name, location, and sample content for the initialization parameter file, and for a discussion of how to set initialization parameters, see "Understanding Initialization Parameters".

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. The server parameter file enables you to change initialization parameters with database commands and persist the changes across a shutdown and startup. You create the server parameter file from your edited text initialization file. For more information, see "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.
See Also:

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:

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:
Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.

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.

Understanding the CREATE DATABASE Statement

When you execute a CREATE DATABASE statement, Oracle Database performs (at least) a number of operations. The actual operations performed depend on the clauses that you specify in the CREATE DATABASE statement and the initialization parameters that you have set. Oracle Database performs at least these operations:
  • Creates the datafiles for the database
  • Creates the control files for the database
  • Creates the redo log files for the database and establishes the ARCHIVELOG mode.
  • Creates the SYSTEM tablespace
  • Creates the SYSAUX tablespace
  • Creates the data dictionary
  • Sets the character set that stores data in the database
  • Sets the database time zone
  • Mounts and opens the database for use
This section discusses several of the clauses of the CREATE DATABASE statement. It expands upon some of the clauses discussed in "Step 7: Issue the CREATE DATABASE Statement" and introduces additional ones. Many of the CREATE DATABASES clauses discussed here can be used to simplify the creation and management of your database.
The following topics are contained in this section:

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are:
  • USER SYS IDENTIFIED BY password
  • USER SYSTEM IDENTIFIED BY password
If you omit these clauses, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert log indicating that the default passwords were used. To protect your database, you should change these passwords using the ALTER USER statement immediately after database creation.
Oracle strongly recommends that you specify these clauses, even though they are optional in this release of Oracle Database. The default passwords are commonly known, and if you neglect to change them later, you leave database vulnerable to attack by malicious users.
See Also:
"Some Security Considerations"

Creating a Locally Managed SYSTEM Tablespace

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. The COMPATIBLE initialization parameter must be set to 9.2 or higher for this statement to be successful. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary-managed SYSTEM tablespace. Dictionary-managed tablespaces are deprecated.
A locally managed SYSTEM tablespace has AUTOALLOCATE enabled by default, which means that the system determines and controls the number and size of extents. You may notice an increase in the initial size of objects created in a locally managed SYSTEM tablespace because of the autoallocate policy. It is not possible to create a locally managed SYSTEM tablespace and specify UNIFORM extent size.
When you create your database with a locally managed SYSTEM tablespace, ensure that the following conditions are met:
  • A default temporary tablespace must exist, and that tablespace cannot be the SYSTEM tablespace.
    To meet this condition, you can specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement, or you can omit the clause and let Oracle Database create the tablespace for you using a default name and in a default location.
  • You can include the UNDO TABLESPACE clause in the CREATE DATABASE statement to create a specific undo tablespace. If you omit that clause, Oracle Database creates a locally managed undo tablespace for you using the default name and in a default location.
See Also:

Creating the SYSAUX Tablespace

The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database and that you must maintain. Other functionality or features that previously used the SYSTEM tablespace can now use the SYSAUX tablespace, thus reducing the load on the SYSTEM tablespace.
You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:
  • PERMANENT
  • READ WRITE
  • EXTENT MANAGMENT LOCAL
  • SEGMENT SPACE MANAGMENT AUTO
You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.
The size of the SYSAUX tablespace is determined by the size of the database components that occupy SYSAUX. See Table 2-2 for a list of all SYSAUX occupants. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed, depending on the nature of its use and workload. For more information on how to manage the space consumption of the SYSAUX tablespace on an ongoing basis, please refer to the "Managing the SYSAUX Tablespace".
If you include a DATAFILE clause for the SYSTEM tablespace, then you must specify the SYSAUX DATAFILE clause as well, or the CREATE DATABASE statement will fail. This requirement does not exist if the Oracle-managed files feature is enabled (see "Specifying Oracle-Managed Files at Database Creation").
If you issue the CREATE DATABASE statement with no other clauses, then the software creates a default database with datafiles for the SYSTEM and SYSAUX tablespaces stored in system-determined default locations, or where specified by an Oracle-managed files initialization parameter.
The SYSAUX tablespace has the same security attributes as the SYSTEM tablespace.
Note:
This book discusses the creation of the SYSAUX database at database creation. When upgrading from a release of Oracle Database that did not require the SYSAUX tablespace, you must create the SYSAUX tablespace as part of the upgrade process. This is discussed in Oracle Database Upgrade Guide.
Table 2-2 lists the components that use the SYSAUX tablespace as their default tablespace during installation, and the tablespace in which they were stored in earlier releases:
Table 2-2 Database Components and the SYSAUX Tablespace
Component Using SYSAUX Tablespace in Earlier Releases
Analytical Workspace Object Table
 SYSTEM
Enterprise Manager Repository
 OEM_REPOSITORY
LogMiner
 SYSTEM
Logical Standby
 SYSTEM
OLAP API History Tables
 CWMLITE
Oracle Data Mining
 ODM
Oracle Spatial
 SYSTEM
Oracle Streams
 SYSTEM
Oracle Text
 DRSYS
Oracle Ultra Search
 DRSYS
Oracle interMedia ORDPLUGINS Components
 SYSTEM
Oracle interMedia ORDSYS Components
 SYSTEM
Oracle interMedia SI_INFORMTN_SCHEMA Components
 SYSTEM
Server Manageability Components
 New in Oracle Database 10g
Statspack Repository
 User-defined
Oracle Scheduler
 New in Oracle Database 10g
Workspace Manager
 SYSTEM
The installation procedures for these components provide the means of establishing their occupancy of the SYSAUX tablespace.
See Also:
"Managing the SYSAUX Tablespace" for information about managing the SYSAUX tablespace

Using Automatic Undo Management: Creating an Undo Tablespace

Automatic undo management uses an undo tablespace.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database. If you want to define and name the undo tablespace yourself, you must also include the UNDO TABLESPACE clause in the CREATE DATABASE statement at database creation time. If you omit this clause, and automatic undo management is enabled (by setting the UNDO_MANAGEMENT initialization parameter to AUTO), the database creates a default undo tablespace named SYS_UNDOTBS.
See Also:

Creating a Default Permanent Tablespace

The DEFAULT TABLESPACE clause of the CREATE DATABASE statement specifies a default permanent tablespace for the database. Oracle Database assigns to this tablespace any non-SYSTEM users for whom you do not explicitly specify a different permanent tablespace. If you do not specify this clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users. Oracle strongly recommends that you create a default permanent tablespace.
See Also:
Oracle Database SQL Reference for the syntax of the DEFAULT TABLESPACE clause of CREATE DATABASE and ALTER DATABASE

Creating a Default Temporary Tablespace

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement creates a default temporary tablespace for the database. Oracle Database assigns this tablespace as the temporary tablespace for users who are not explicitly assigned a temporary tablespace.
You can explicitly assign a temporary tablespace or tablespace group to a user in the CREATE USER statement. However, if you do not do so, and if no default temporary tablespace has been specified for the database, then by default these users are assigned the SYSTEM tablespace as their temporary tablespace. It is not good practice to store temporary data in the SYSTEM tablespace, and it is cumbersome to assign every user a temporary tablespace individually. Therefore, Oracle recommends that you use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.
Note:
When you specify a locally managed SYSTEM tablespace, the SYSTEM tablespace cannot be used as a temporary tablespace. In this case the database creates a default temporary tablespace. This behavior is explained in "Creating a Locally Managed SYSTEM Tablespace".
You can add or change the default temporary tablespace after database creation. You do this by creating a new temporary tablespace or tablespace group with a CREATE TEMPORARY TABLESPACE statement, and then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new default temporary tablespace.
The following statement assigns a new default temporary tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;
The new default temporary tablespace must already exist. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.
You cannot drop or take offline a default temporary tablespace, but you can assign a new default temporary tablespace and then drop or take offline the former one. You cannot change a default temporary tablespace to a permanent tablespace.
Users can obtain the name of the current default temporary tablespace by querying the PROPERTY_NAME and PROPERTY_VALUE columns of the DATABASE_PROPERTIES view. These columns contain the values "DEFAULT_TEMP_TABLESPACE" and the default temporary tablespace name, respectively.
See Also:

Specifying Oracle-Managed Files at Database Creation

You can minimize the number of clauses and parameters that you specify in your CREATE DATABASE statement by using the Oracle-managed files feature. You do this either by specifying a directory in which your files are created and managed by Oracle Database, or by using Automatic Storage Management. When you use Automatic Storage Management, you specify a disk group in which the database creates and manages your files, including file redundancy and striping.
By including any of the initialization parameters DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n, or DB_RECOVERY_FILE_DEST in your initialization parameter file, you instruct Oracle Database to create and manage the underlying operating system files of your database. Oracle Database will automatically create and manage the operating system files for the following database structures, depending on which initialization parameters you specify and how you specify clauses in your CREATE DATABASE statement:
  • Tablespaces
  • Temporary tablespaces
  • Control files
  • Redo log files
  • Archive log files
  • Flashback logs
  • Block change tracking files
  • RMAN backups
See Also:
"Specifying a Flash Recovery Area" for information about setting initialization parameters that create a flash recovery area
The following CREATE DATABASE statement shows briefly how the Oracle-managed files feature works, assuming you have specified required initialization parameters:
CREATE DATABASE rbdb1
     USER SYS IDENTIFIED BY pz6r58
     USER SYSTEM IDENTIFIED BY y1tz5p
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1;
  • No DATAFILE clause is specified, so the database creates an Oracle-managed datafile for the SYSTEM tablespace.
  • No LOGFILE clauses are included, so the database creates two Oracle-managed redo log file groups.
  • No SYSAUX DATAFILE is included, so the database creates an Oracle-managed datafile for the SYSAUX tablespace.
  • No DATAFILE subclause is specified for the UNDO TABLESPACE clause, so the database creates an Oracle-managed datafile for the undo tablespace.
  • No TEMPFILE subclause is specified for the DEFAULT TEMPORARY TABLESPACE clause, so the database creates an Oracle-managed tempfile.
  • If no CONTROL_FILES initialization parameter is specified in the initialization parameter file, then the database also creates an Oracle-managed control file.
  • If you are using a server parameter file (see "Managing Initialization Parameters Using a Server Parameter File"), the database automatically sets the appropriate initialization parameters.
    See Also:

Supporting Bigfile Tablespaces During Database Creation

Oracle Database simplifies management of tablespaces and enables support for ultra-large databases by letting you create bigfile tablespaces. Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
This section discusses the clauses of the CREATE DATABASE statement that let you include support for bigfile tablespaces.
See Also:
"Bigfile Tablespaces" for more information about bigfile tablespaces

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 and Time Zone File

You can specify the database time zone and the supporting time zone file.

Setting the Database Time Zone

Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, then it defaults to the time zone of the server's operating system.
You can change the database time zone for a session by using the SET TIME_ZONE clause of the ALTER SESSION statement.
See Also:
Oracle Database Globalization Support Guide for more information about setting the database time zone

Specifying the Database Time Zone File

Two time zone files are included in the Oracle home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat. A smaller time zone file can be found in $ORACLE_HOME/oracore/zoneinfo/timezone.dat.
If you are already using the smaller time zone file and you want to continue to use it in an Oracle Database 10g environment or if you want to use the smaller time zone file instead of the default time zone file, then complete the following tasks:
  1. Shut down the database.
  2. Set the ORA_TZFILE environment variable to the full path name of the timezone.dat file.
  3. Restart the database.
If you are already using the default time zone file, then it is not practical to change to the smaller time zone file because the database may contain data with time zones that are not part of the smaller time file.
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 also included for each time zone:
  • Offset from Coordinated Universal Time (UTC)
  • Transition times for Daylight Saving Time
  • Abbreviations for standard time and Daylight Saving Time
To view the time zone names in the file being used by your database, use the following query:
SELECT * FROM V$TIMEZONE_NAMES;

Specifying FORCE LOGGING Mode

Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect media recovery and standby databases.
Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.
See Also:
Oracle Database SQL Reference for information about operations that can be done in NOLOGGING mode

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.
See Also:
"Controlling the Writing of Redo Records" for information about using the FORCE LOGGING clause for tablespace creation.

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.

Understanding Initialization Parameters

When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file can be either a read-only text file, or a read/write binary file. The binary file is called a server parameter file, and it always resides on the server. A server parameter file enables you to change initialization parameters with ALTER SYSTEM commands and to persist the changes across a shutdown and startup. It also provides a basis for self-tuning by the Oracle Database server. For these reasons, it is recommended that you use a server parameter file. You can create one from your edited text initialization file or by using the Database Configuration Assistant.
Before you create a server parameter file, you can start an instance with a text initialization parameter file. Upon startup, the Oracle instance first searches for a server parameter file in a default location, and if it does not find one, searches for a text initialization parameter file. You can also override an existing server parameter file by naming a text initialization parameter file as an argument of the STARTUP command.
For more information on server parameter files, see "Managing Initialization Parameters Using a Server Parameter File". For more information on the STARTUP command, see "Understanding Initialization Parameter Files".
Default file names and locations for the text initialization parameter file are shown in the following table:
Platform Default Name Default Location
UNIX and Linux init$ORACLE_SID.ora For example, the initialization parameter file for the mynewdb database is named:
initmynewdb.ora
 $ORACLE_HOME/dbs For example, the initialization parameter file for the mynewdb database is stored in the following location:
/u01/oracle/dbs/initmynewdb.ora
Windows init%ORACLE_SID%.ora %ORACLE_HOME%\database
Sample Initialization Parameter File
The following is an example of an initialization parameter file used to create a 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.2.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
Oracle Database provides generally appropriate values in the sample initialization parameter file provided with your database software or created for you by the Database Configuration Assistant. You can edit these Oracle-supplied initialization parameters and add others, depending upon your configuration and options and how you plan to tune the database. For any relevant initialization parameters not specifically included in the initialization parameter file, the database supplies defaults.
If you are creating an Oracle Database for the first time, Oracle suggests that you minimize the number of parameter values that you alter. As you become more familiar with your database and environment, you can dynamically tune many initialization parameters using the ALTER SYSTEM statement. If you are using a text initialization parameter file, your changes are effective only for the current instance. To make them permanent, you must update them manually in the initialization parameter file, or they will be lost over the next shutdown and startup of the database. If you are using a server parameter file, initialization parameter file changes made by the ALTER SYSTEM statement can persist across shutdown and startup. This is discussed in "Managing Initialization Parameters Using a Server Parameter File".
This section introduces you to some of the basic initialization parameters you can add or edit before you create your new database.
The following topics are contained in this section:

Determining the Global Database Name

The global database name consists of the user-specified local database name and the location of the database within a network structure. The DB_NAME initialization parameter determines the local name component of the database name, and the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a database name that is unique within a network.
For example, to create a database with a global database name of test.us.acme.com, edit the parameters of the new parameter file as follows:
DB_NAME = test
DB_DOMAIN = us.acme.com
You can rename the GLOBAL_NAME of your database using the ALTER DATABASE RENAME GLOBAL_NAME statement. However, you must also shut down and restart the database after first changing the DB_NAME and DB_DOMAIN initialization parameters and re-creating the control file.
See Also:
Oracle Database Utilities for information about using the DBNEWID utility, which is another means of changing a database name

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.
See Also:
Part VII, "Distributed Database Management" for more information about distributed databases

Specifying a Flash Recovery Area

A flash recovery area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the Oracle-managed current database files (datafiles, control files, and online redo logs).
You specify a flash recovery area with the following initialization parameters:
  • DB_RECOVERY_FILE_DEST: Location of the flash recovery area. This can be a directory, file system, or Automatic Storage Management (ASM) disk group. It cannot be a raw file system.
    In a RAC environment, this location must be on a cluster file system, ASM disk group, or a shared directory configured through NFS.
  • DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the flash recovery area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.
In a RAC environment, the settings for these two parameters must be the same on all instances.
You cannot enable these parameters if you have set values for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up the flash recovery area. You can instead set values for the LOG_ARCHIVE_DEST_n parameters. If you do not set values for local LOG_ARCHIVE_DEST_n, then setting up the flash recovery area will implicitly set LOG_ARCHIVE_DEST_10 to the flash recovery area.
Oracle recommends using a flash recovery area, because it can simplify backup and recovery operations for your database.
See Also:
Oracle Database Backup and Recovery Basics to learn how to create and use a flash recovery area

Specifying Control Files

The CONTROL_FILES initialization parameter specifies one or more control filenames for the database. When you execute the CREATE DATABASE statement, the control files listed in the CONTROL_FILES parameter are created.
If you do not include CONTROL_FILES in the initialization parameter file, then Oracle Database creates a control file using a default operating system dependent filename or, if you have enabled Oracle-managed files, creates Oracle-managed control files.
If you want the database to create new operating system files when creating database control files, the filenames listed in the CONTROL_FILES parameter must not match any filenames that currently exist on your system. If you want the database to reuse or overwrite existing files when creating database control files, ensure that the filenames listed in the CONTROL_FILES parameter match the filenames that are to be reused.
Caution:
Use extreme caution when setting this specifying CONTROL_FILE filenames. If you inadvertently specify a file that already exists and execute the CREATE DATABASE statement, the previous contents of that file will be overwritten.
Oracle strongly recommends you use at least two control files stored on separate physical disk drives for each database.
See Also:

Specifying Database Block Sizes

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes.

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.
See Also:

Managing the System Global Area (SGA)

This section discusses the initialization parameters that affect the amount of memory allocated to the System Global Area (SGA). Except for the SGA_MAX_SIZE initialization parameter, they are dynamic parameters whose values can be changed by the ALTER SYSTEM statement. The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters.
This section contains the following topics:

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 to a non-zero value. This parameter in effect replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized (tuned) as needed.
Note:
The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.
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—the automatically sized SGA components—and the initialization parameters corresponding to those components.
Table 2-3 Automatically Sized SGA Components and Corresponding Parameters
SGA Component Initialization Parameter
Fixed SGA and other internal allocations needed by the Oracle Database instance
 N/A
The shared pool
 SHARED_POOL_SIZE
The large pool
 LARGE_POOL_SIZE
The Java pool
 JAVA_POOL_SIZE
The buffer cache
 DB_CACHE_SIZE
The Streams pool
 STREAMS_POOL_SIZE
The parameters listed in Table 2-4, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 2-3.
Table 2-4 Manually Sized SGA Components that Use SGA_TARGET Space
SGA Component Initialization Parameter
The log buffer
 LOG_BUFFER
The keep and recycle buffer caches
 DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Nonstandard block size buffer caches
 DB_nK_CACHE_SIZE
In addition to setting SGA_TARGET to a non-zero value, you must set the value of all automatically sized SGA components to zero to enable full automatic tuning of these components.
Alternatively, you can set one or more of the automatically sized SGA components to a non-zero value, which is then used as the minimum setting for that component during SGA tuning. This is discussed in detail later in this section.
Note:
An easier way to enable automatic shared memory management is to use Oracle Enterprise Manager (EM). When you enable automatic shared memory management and set the Total SGA Size, EM automatically generates the ALTER SYSTEM statements to set SGA_TARGET to the specified size and to set all automatically sized SGA components to zero. If you use SQL*Plus to set SGA_TARGET, you must then set the automatically sized SGA components to zero or to a minimum value.
The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET. For more information, see Oracle Database Performance Tuning Guide.
Enabling Automatic Shared Memory Management
To enable automatic shared memory management:
  1. If 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;
  2. Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:
    ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
    where value is the value computed in step 1 or is some value between the sum of all SGA component sizes and SGA_MAX_SIZE. For more information on the SCOPE clause, see "Using ALTER SYSTEM to Change Initialization Parameter Values".
  3. Do one of the following:
    • For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table 2-3 to zero. Do this by editing the text initialization parameter file, or by issuing ALTER SYSTEM statements similar to the one in step 2.
    • To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. (See the next section for details.) Set the values of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file, or by issuing ALTER SYSTEM statements similar to the one in step 2.
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_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY = 208M
You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:
ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.
Setting Minimums for Automatically Sized SGA Components
You can exercise some control over the size of the automatically sized SGA 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
  • Actual Streams pool size = 0
The parameter values determine the minimum amount of SGA space allocated. The fixed views V$SGA_DYNAMIC_COMPONENTS and V$SGAINFO display the current actual size of each SGA component. You can also see the current actual values of the SGA components in the Oracle 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.
Automatic Tuning and the Shared Pool
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 algorithm 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.
Dynamic Modification of SGA Parameters
You can modify the value of SGA_TARGET and the parameters controlling individual components dynamically using the ALTER SYSTEM statement, as described in "Using ALTER SYSTEM to Change Initialization Parameter Values".
Dynamic Modification of SGA_TARGET
The SGA_TARGET parameter can be increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
When SGA_TARGET is resized, the only components affected are the automatically tuned components for which you have not set a minimum value in their corresponding initialization parameter. Any manually configured components remain unaffected.
For example, suppose you have an environment with the following configuration:
  • SGA_MAX_SIZE = 1024M
  • SGA_TARGET = 512M
  • DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or more of the automatically sized components reaches its minimum size. The exact value depends on environmental factors such as the number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M
When SGA_TARGET is reduced, if minimum values for any automatically tuned components are specified, those components are not reduced smaller than that minimum. Consider the following combination of parameters:
  • SGA_MAX_SIZE = 1024M
  • SGA_TARGET = 512M
  • DB_CACHE_SIZE = 96M
  • DB_8K_CACHE_SIZE = 128M
As in the last example, if SGA_TARGET is reduced, the DB_8K_CACHE_SIZE parameter is permanently fixed at 128M. In addition, the primary buffer cache (determined by the DB_CACHE_SIZE parameter) is not reduced smaller than 96M. Thus the amount that SGA_TARGET can be reduced is restricted.
Note:
When enabling automatic shared memory management, it is best to set SGA_TARGET to the desired non-zero value before starting the database. Dynamically modifying SGA_TARGET from zero to a non-zero value may not achieve the desired results because the shared pool may not be able to shrink. After startup, you can dynamically tune SGA_TARGET up or down as required.
Modifying Parameters for Automatically Managed Components
When SGA_TARGET is not set, the automatic shared memory management feature is not enabled. Therefore the rules governing resize for all component parameters are the same as in earlier releases. However, when automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components. You can modify this limit dynamically by changing the values of the corresponding parameters.
If the specified lower limit for the size of a given SGA component is less than its current size, there is no immediate change in the size of that component. The new setting only limits the automatic tuning algorithm to that reduced minimum size in the future. For example, consider the following configuration:
  • SGA_TARGET = 512M
  • LARGE_POOL_SIZE = 256M
  • Current actual large pool size = 284M
In this example, if you increase the value of LARGE_POOL_SIZE to a value greater than the actual current size of the component, the system expands the component to accommodate the increased minimum size. For example, if you increase the value of LARGE_POOL_SIZE to 300M, then the system increases the large pool incrementally until it reaches 300M. This resizing occurs at the expense of one or more automatically tuned components.If you decrease the value of LARGE_POOL_SIZE to 200, there is no immediate change in the size of that component. The new setting only limits the reduction of the large pool size to 200 M in the future.
Modifying Parameters for Manually Sized Components
Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component. When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components.
For example, consider this configuration:
  • SGA_TARGET = 512M
  • DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to the automatically sized components.

Using Manual Shared Memory Management

If you decide not to use automatic shared memory management by not setting the SGA_TARGET parameter, you must manually configure each component of the SGA. This section provides guidelines on setting the parameters that control the size of each SGA components.
Setting the Buffer Cache Initialization Parameters
The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic.
If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle Database assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.
The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping.
DB_CACHE_SIZE Initialization Parameter The DB_CACHE_SIZE initialization parameter has replaced the DB_BLOCK_BUFFERS initialization parameter, which was used in earlier releases. The DB_CACHE_SIZE parameter specifies the size in bytes of the cache of standard block size buffers. Thus, to specify a value for DB_CACHE_SIZE, you would determine the number of buffers that you need and multiple that value times the block size specified in DB_BLOCK_SIZE.
For backward compatibility, the DB_BLOCK_BUFFERS parameter still functions, but it remains a static parameter and cannot be combined with any of the dynamic sizing parameters.
The DB_nK_CACHE_SIZE Initialization Parameters The sizes and numbers of nonstandard block size buffers are specified by the following initialization parameters:
  • DB_2K_CACHE_SIZE
  • DB_4K_CACHE_SIZE
  • DB_8K_CACHE_SIZE
  • DB_16K_CACHE_SIZE
  • DB_32K_CACHE_SIZE
Each parameter specifies the size of the buffer cache for the corresponding block size. For example:
DB_BLOCK_SIZE=4096

DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=8M
DB_8K_CACHE_SIZE=4M
In this example, the parameters specify that the standard block size of the database is 4K. The size of the cache of standard block size buffers is 12M. Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively.
Note:
You cannot use a DB_nK_CACHE_SIZE parameter to size the cache for the standard block size. For example, if the value of DB_BLOCK_SIZE is 2K, it is invalid to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE.
Specifying the Shared Pool Size
The SHARED_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the shared pool component of the SGA. Oracle Database selects an appropriate default value.
In releases before Oracle Database 10g Release 1, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. The internal SGA overhead refers to memory that is allocated by Oracle during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. For example, if the SHARED_POOL_SIZE parameter is set to 64MB and the internal SGA overhead is computed to be 12MB, the real size of shared pool in the SGA is 64+12=76MB, although the value of the SHARED_POOL_SIZE parameter is still displayed as 64MB.Starting with Oracle Database 10g Release 1, the size of internal SGA overhead is included in the user-specified value of SHARED_POOL_SIZE. In other words, if you are not using the automatic shared memory management feature, then the amount of shared pool memory that is allocated at startup is exactly equal to the value of SHARED_POOL_SIZE initialization parameter. In manual SGA mode, this parameter must be set so that it includes the internal SGA overhead in addition to the desired value of shared pool size. In the previous example, if the SHARED_POOL_SIZE parameter is set to 64MB at startup, then the available shared pool after startup is 64-12=52MB, assuming the value of internal SGA overhead remains unchanged. In order to maintain an effective value of 64MB for shared pool memory after startup, you must set the SHARED_POOL_SIZE parameter to 64+12=76MB.
The Oracle Database 10g migration utilities recommend a new value for this parameter based on the value of internal SGA overhead in the pre-upgrade environment and based on the old value of this parameter. In Oracle Database 10g, the exact value of internal SGA overhead, also known as startup overhead in the shared pool, can be queried from the V$SGAINFO view. Also, in manual SGA mode, if the user-specified value of SHARED_POOL_SIZE is too small to accommodate even the requirements of internal SGA overhead, then Oracle generates an ORA-371 error during startup, along with a suggested value to use for the SHARED_POOL_SIZE parameter.When you use automatic shared memory management in Oracle Database 10g, the shared pool is automatically tuned, and an ORA-371 error would not be generated by Oracle.
Specifying the Large Pool Size
The LARGE_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the large pool component of the SGA. The large pool is an optional component of the SGA. You must specifically set the LARGE_POOL_SIZE parameter if you want to create a large pool. Configuring the large pool is discussed in Oracle Database Performance Tuning Guide.
Specifying the Java Pool Size
The JAVA_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the java pool component of the SGA. Oracle Database selects an appropriate default value. Configuration of the java pool is discussed in Oracle Database Java Developer's Guide.
Specifying the Streams Pool Size
The STREAMS_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the Streams Pool component of the SGA. If STREAMS_POOL_SIZE is set to 0, then the Oracle Streams product transfers memory from the buffer cache to the Streams Pool when it is needed. For details, see the discussion of the Streams Pool in Oracle Streams Concepts and Administration.

Viewing Information About the SGA

The following views provide information about the SGA components and their dynamic resizing:
View Description
V$SGA Displays summary information about the system global area (SGA).
V$SGAINFO Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT Displays detailed information about the SGA.
V$SGA_DYNAMIC_COMPONENTS Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_DYNAMIC_FREE_MEMORY Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$SGA_RESIZE_OPS Displays information about the last 400 completed SGA resize operations.
V$SGA_CURRENT_RESIZE_OPS Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.
V$SGA_TARGET_ADVICE Displays information that helps you tune SGA_TARGET. For more information, see Oracle Database Performance Tuning Guide.

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 70.

Specifying the Method of Undo Space Management

Every Oracle Database must have a method of maintaining information that is used to 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.This section provides instructions for setting up an environment for automatic undo management using an undo tablespace.
See Also:
Chapter 10, "Managing the Undo Tablespace"

UNDO_MANAGEMENT Initialization Parameter

The UNDO_MANAGEMENT initialization parameter determines whether an instance starts in automatic undo management mode which stores undo in an undo tablespace. By default, this parameter is set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode.

UNDO_TABLESPACE Initialization Parameter

When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data. If the database was created in undo management mode, then the default undo tablespace (either the system-created SYS_UNDOTS tablespace or the user-specified undo tablespace) is the undo tablespace used at instance startup. You can override this default for the instance by specifying a value for the UNDO_TABLESPACE initialization parameter. This parameter is especially useful for assigning a particular undo tablespace to an instance in an Oracle Real Application Clusters environment.
If no undo tablespace has been specified during database creation or by the UNDO_TABLESPACE initialization parameter, then the first available undo tablespace in the database is chosen. If no undo tablespace is available, then the instance starts without an undo tablespace. You should avoid running in this mode.

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, 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.
Note:
For Oracle Database 10g Release 2 (10.2), the default value of the COMPATIBLE parameter is 10.2.0. The minimum value is 9.2.0. If you create an Oracle Database using the default value, you can immediately use all the new features in this release, and you can never downgrade the database.
See Also:

Setting the License Parameter

Note:
Oracle no longer offers licensing by the number of concurrent sessions. Therefore the LICENSE_MAX_SESSIONS and LICENSE_SESSIONS_WARNING initialization parameters are no longer needed and have been deprecated.
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.
Note:
This mechanism assumes that each person accessing the database has a unique user name and that no people share a user name. Therefore, so that named user licensing can help you ensure compliance with your Oracle license agreement, do not allow multiple users to log in using the same user name.
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

Troubleshooting Database Creation

If database creation fails, you can look at the alert log to determine the reason for the failure and to determine corrective action. The alert log is discussed in "Monitoring the Operation of Your Database".
You should shut down the instance and delete any files created by the CREATE DATABASE statement before you attempt to create it again. After correcting the error that caused the failure of the database creation, try re-creating the database.

Dropping a Database

Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions must apply:
  • The database must be mounted and closed.
  • The database must be mounted exclusively--not in shared mode.
  • The database must be mounted as RESTRICTED.
An example of this statement is:
DROP DATABASE;
The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.
If you used the Database Configuration Assistant to create your database, you can use that tool to delete (drop) your database and remove the files.

Managing Initialization Parameters Using a Server Parameter File

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file. For better manageability, you can choose to maintain initialization parameters in a binary server parameter file that is persistent across database startup and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.

What Is a Server Parameter File?

A server parameter file 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 persistent any changes effected by ALTER SYSTEM statements. It also provides a basis for self-tuning by the Oracle Database server.
A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. (It can also be created directly by the Database Configuration Assistant.) The server parameter file is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file.
Caution:
Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it could fail.
When you issue a STARTUP command with no PFILE clause, the Oracle instance searches an operating system–specific default location for a server parameter file from which to read initialization parameter settings. If no server parameter file is found, the instance searches for a text initialization parameter file. If a server parameter file exists but you want to override it with settings in a 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 text 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:
  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

You use the CREATE SPFILE statement to create a server parameter file from a text initialization parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.
Note:
When you use the Database Configuration Assistant to create a database, it can automatically create a server parameter file for you.
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 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 with the platform-specific default name and location shown in Table 2-5.
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.
Table 2-5 Server Parameter File Default Name and Location on UNIX and Windows
Platform Default Name Default Location
UNIX and Linux
 spfile$ORACLE_SID.ora
 $ORACLE_HOME/dbs or the same location as the datafiles
Windows
 spfile%ORACLE_SID%.ora
 %ORACLE_HOME%\database
Note:
Upon startup, the instance first searches for the server parameter file named spfile$ORACLE_SID.ora, and if not found, searches for spfile.ora. Using spfile.ora enables all Real Application Cluster (RAC) instances to use the same server parameter file. If neither server parameter file is found, the instance searches for the text initialization parameter file init$ORACLE_SID.ora.
If you create a server parameter file in a location other than the default location, you must create a text initialization parameter file that points to the server parameter file. For more information, see "Starting Up a Database".

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.

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 text 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.

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. The optional SCOPE clause specifies the scope of a change as described in the following table:
SCOPE Clause Description
SCOPE = SPFILE The change is applied in the server parameter file only. The effect is as follows:
  • For dynamic parameters, the change is effective at the next startup and is persistent.
  • For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY The change is applied in memory only. The effect is as follows:
  • For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
  • For static parameters, this specification is not allowed.
SCOPE = BOTH The change is applied in both the server parameter file and memory. The effect is as follows:
  • For dynamic parameters, the effect is immediate and persistent.
  • For static parameters, this specification is not allowed.
It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.
For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.
The following statement changes the maximum number of job queue processes allowed for the instance. It includes a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
                 COMMENT='temporary change on Nov 29'
                 SCOPE=MEMORY;
The next example sets a complex initialization parameter that takes a list of attributes. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_n initialization parameter. This statement could change an existing setting for this parameter of create a new archive destination.
ALTER SYSTEM 
     SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
         COMMENT='Add new destimation on Nov 29'
         SCOPE=SPFILE;
When a value consists of a list of parameters, you cannot edit individual attributes by the position or ordinal number. You must specify the complete list of values each time the parameter is updated, and the new list completely replaces the old list.

Exporting the Server Parameter File

You can use the CREATE PFILE statement to export a server parameter file to a text initialization parameter file. Doing so might be necessary for several reasons:
  • 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.
See Also:
Oracle Database Backup and Recovery Basics

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 log 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.
Method Description
SHOW PARAMETERS This SQL*Plus command displays the values of parameters currently in use.
CREATE PFILE This SQL statement creates a text initialization parameter file from the binary server parameter file.
V$PARAMETER This view displays the values of parameters currently in effect.
V$PARAMETER2 This view displays the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.
V$SPPARAMETER This view displays the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.
See Also:
Oracle Database Reference for a complete description of views

Defining Application Services for Oracle Database 10g

This section describes Oracle Database 10g services and includes the following topics:
Services are logical abstractions for managing workloads in Oracle Database 10g. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities. The grouping is based on attributes of work that might include the application function to be used, the priority of execution for the application function, the job class to be managed, or the data range used in the application function or job class. For example, the Oracle E-Business suite defines a service for each responsibility, such as general ledger, accounts receivable, order entry, and so on.
In Oracle Database 10g, services are built into the Oracle Database providing single system image for workloads, prioritization for workloads, performance measures for real transactions, and alerts and actions when performance goals are violated. Services enable you to configure a workload, administer it, enable and disable it, and measure the workload as a single entity. You can do this using standard tools such as the Database Configuration Assistant (DBCA), Net Configuration Assistant (NetCA), and Enterprise Manager (EM). Enterprise Manager supports viewing and operating services as a whole, with drill down to the instance-level when needed.
In Real Application Clusters (RAC), a service can span one or more instances and facilitate real workload balancing based on real transaction performance. This provides end-to-end unattended recovery, rolling changes by workload, and full location transparency. RAC also enables you to manage a number of service features with Enterprise Manager, the DBCA, and the Server Control utility (SRVCTL).
Services also offer an extra dimension in performance tuning. Tuning by "service and SQL" can replace tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared. With services, workloads are visible and measurable. Resource consumption and waits are attributable by application. Additionally, resources assigned to services can be augmented when loads increase or decrease. This dynamic resource allocation enables a cost-effective solution for meeting demands as they occur. For example, services are measured automatically and the performance is compared to service-level thresholds. Performance violations are reported to Enterprise Manager, enabling the execution of automatic or scheduled solutions.
See Also:
Oracle Real Application Clusters Deployment and Performance Guide for more information about services in RAC

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
See Also:
Oracle Database Reference for detailed information about these views
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.

Client-Side Use

Middle-tier applications and client-server applications use a service by specifying the service as part of the connection in TNS connect data. This connect data may be in the TNSnames file for thick Net drivers, in the URL specification for thin drivers, or may be maintained in the Oracle Internet Directory. For example, data sources for the Oracle Application Server 10g are set to route to a service. Using Easy Connect Naming, this connection needs only the host name and service name (for example, hr/hr@myDBhost/myservice). For Oracle E-Business Suite, the service is also maintained in the application database identifier and in the cookie for the ICX parameters.

Server-Side Use

Server-side work, such as the Oracle Scheduler, parallel execution, and Oracle Streams Advanced Queuing, set the service name as part of the workload definition.
For the Oracle Scheduler, the service that the job class uses is defined when the job class is created. During execution, jobs are assigned to job classes, and job classes run within services. Using services with job classes ensures that the work executed by the job scheduler is identified for workload management and performance tuning.
For parallel query and parallel DML, the query coordinator connects to a service just like any other client. The parallel query processes inherit the service for the duration of the execution. At the end of query execution, the parallel execution processes revert to the default service.
See Also:
Chapter 27, "Using the Scheduler" for more information about the Oracle Scheduler.

Considerations After Creating a Database

After you create a database, the instance is left running, and the database is open and available for normal database use. You may want to perform other actions, some of which are discussed in this section.

Some Security Considerations

Note Regarding Security Enhancements:
In this release of Oracle Database and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts. You can find a security checklist for this release in Oracle Database Security Guide. Oracle recommends that you read this checklist and configure your database accordingly.
After 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 Database Enterprise User 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.
Caution:
To prevent unauthorized access and protect the integrity of your database, it is important that new passwords for user accounts SYS and SYSTEM be specified when the database is created. This is accomplished by specifying the following CREATE DATABASE clauses when manually creating you database, or by using DBCA to create the database:
  • USER SYS IDENTIFIED BY
  • USER SYSTEM IDENTIFIED BY
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-6 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.
Table 2-6 Administrative User Accounts Provided by Oracle Database
Username Password Description See Also
CTXSYS
 CTXSYS
 The Oracle Text account
 Oracle Text Reference
DBSNMP
 DBSNMP
 The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database
 Oracle Enterprise Manager Grid Control Installation and Basic Configuration
LBACSYS
 LBACSYS
 The Oracle Label Security administrator account
 Oracle Label Security Administrator's Guide
MDDATA
 MDDATA
 The schema used by Oracle Spatial for storing Geocoder and router data
 Oracle Spatial User's Guide and Reference
MDSYS
 MDSYS
 The Oracle Spatial and Oracle interMedia Locator administrator account
 Oracle Spatial User's Guide and Reference
DMSYS
 DMSYS
 The Oracle Data Mining account.
 Oracle Data Mining Administrator's Guide
Oracle Data Mining Concepts
OLAPSYS
 MANAGER
 The account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite).
 Oracle OLAP Application Developer's Guide
ORDPLUGINS
 ORDPLUGINS
 The Oracle interMedia user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema.
 Oracle interMedia User's Guide
ORDSYS
 ORDSYS
 The Oracle interMedia administrator account
 Oracle interMedia User's Guide
OUTLN
 OUTLN
 The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.
 Oracle Database Performance Tuning Guide
SI_INFORMTN_SCHEMA
 SI_INFORMTN_SCHEMA
 The account that stores the information views for the SQL/MM Still Image Standard
 Oracle interMedia User's Guide
SYS
 CHANGE_ON_INSTALL
 The account used to perform database administration tasks
 Oracle Database Administrator's Guide
SYSMAN
 CHANGE_ON_INSTALL
 The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks.
 Oracle Enterprise Manager Grid Control Installation and Basic Configuration
SYSTEM
 MANAGER
 Another account used to perform database administration tasks.
 Oracle Database Administrator's Guide
See Also:

Enabling Transparent Data Encryption

Transparent data encryption is a feature that enables encryption of database columns before storing them in the datafile. If users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, transparent data encryption prevents such users from viewing sensitive information.
Users who have the CREATE TABLE privilege can choose one or more columns in a table to be encrypted. The data is encrypted in the data files and in the audit logs (if audit is turned on). Database users with appropriate privileges can view the data in unencrypted format. For information on enabling and disabling transparent data encryption, see Oracle Database Security Guide

Creating a Secure External Password Store

For large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.
Storing database password credentials in a client-side Oracle wallet eliminates the need to embed usernames and passwords in application code, batch jobs, or scripts. This reduces the risk of exposing passwords in the clear in scripts and application code, and simplifies maintenance because you need not change your code each time usernames and passwords change. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.
When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:
CONNECT /@database_alias
Note that you need not specify database login credentials in this CONNECT statement. Instead your system looks for database login credentials in the client wallet.
See Also:
Oracle Database Advanced Security Administrator's Guide for information about configuring your client to use a secure external password store and for information about managing credentials in it.

Installing the Oracle Database Sample Schemas

The Oracle Database distribution media 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.

Viewing Information About the Database

In addition to the views listed previously in "Viewing Parameter Settings", you can view information about your database content and structure using the following views:
View Description
DATABASE_PROPERTIES Displays permanent database properties
GLOBAL_NAME Displays the global database name
V$DATABASE Contains database information from the control file







張貼者： 於 沒有留言:
標籤： ,
訂閱： 文章 (Atom)