|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-11
|PDF · Mobi · ePub|
When you execute a
DATABASE statement, Oracle Database performs several operations. The actual operations performed depend on the clauses that you specify in the
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
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
DATABASE statement. It expands upon some of the clauses discussed in "Step 9: Issue the CREATE DATABASE Statement" and introduces additional ones. Many of the
CREATE DATABASE clauses discussed here can be used to simplify the creation and management of your database.
The following topics are contained in this section:
If you omit these clauses, then these users are assigned the default passwords
manager, respectively. A record is written to the alert log indicating that the default passwords were used. To protect your database, you must 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, then you leave database vulnerable to attack by malicious users.
When choosing a password, keep in mind that beginning in Release 11g, passwords are case-sensitive. Also, there may be password formatting requirements for your database. See the section entitled "How Oracle Database Checks the Complexity of Passwords" in Oracle Database Security Guide for more information.
See Also:"Some Security Considerations"
EXTENT MANAGEMENT LOCAL clause in the
DATABASE statement to create a locally managed
SYSTEM tablespace. The
COMPATIBLE initialization parameter must be set to 10.0.0 or higher for this statement to be successful. If you do not specify the
EXTENT MANAGEMENT LOCAL clause, then by default the database creates a dictionary-managed
SYSTEM tablespace. Dictionary-managed tablespaces are deprecated.
If you create your database with a locally managed
SYSTEM tablespace, and if you are not using Oracle Managed Files, then ensure that the following conditions are met:
You specify the
TABLESPACE clause in the
You include the
UNDO TABLESPACE clause in the
Oracle Database SQL Language Reference for more specific information about the use of the
DEFAULT TEMPORARY TABLESPACE and
UNDO TABLESPACE clauses when
EXTENT MANAGEMENT LOCAL is specified for 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. It also reduces the load on the
You can specify only datafile attributes for the
SYSAUX tablespace, using the
SYSAUX DATAFILE clause in the
DATABASE statement. Mandatory attributes of the
SYSAUX tablespace are set by Oracle Database and include:
You cannot alter these attributes with an
TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the
The size of the
SYSAUX tablespace is determined by the size of the database components that occupy
SYSAUX. You can view a list of these components by querying the
V$SYSAUX_OCCUPANTS view. Based on the initial sizes of these components, the
SYSAUX tablespace must be at least 400 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, see 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
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").
SYSAUX tablespace has the same security attributes as the
Note:This documentation discusses the creation of the
SYSAUXdatabase at database creation. When upgrading from a release of Oracle Database that did not require the
SYSAUXtablespace, you must create the
SYSAUXtablespace as part of the upgrade process. This is discussed in Oracle Database Upgrade Guide.
See Also:"Managing the SYSAUX 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. Or, omit this parameter, and the database defaults to automatic undo management. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database. To define and name the undo tablespace yourself, you must 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, then the database creates a default undo tablespace named
TABLESPACE clause of the
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 Language Reference for the syntax of the
DEFAULT TABLESPACEclause of
DEFAULT TEMPORARY TABLESPACE clause of the
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
Note:When you specify a locally managed
SYSTEMtablespace cannot be used as a temporary tablespace. In this case you must create a default temporary tablespace. This behavior is explained in "Creating a Locally Managed SYSTEM Tablespace".
Oracle Database SQL Language Reference for the syntax of the
DEFAULT TEMPORARY TABLESPACE clause of
CREATE DATABASE and
"Temporary Tablespaces" for information about creating and using temporary tablespaces
"Multiple Temporary Tablespaces: Using Tablespace Groups" for information about creating and using temporary tablespace groups
You can minimize the number of clauses and parameters that you specify in your
DATABASE statement by using the Oracle Managed Files feature. You do this by specifying either a directory or Oracle Automatic Storage Management (Oracle ASM) disk group in which your files are created and managed by Oracle Database.
By including any of the initialization parameters
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
Tablespaces and their datafiles
Temporary tablespaces and their tempfiles
Redo log files
Archived redo log files
Block change tracking files
See Also:"Specifying a Fast Recovery Area" for information about setting initialization parameters that create a Fast Recovery Area
DATABASE statement shows briefly how the Oracle Managed Files feature works, assuming you have specified required initialization parameters:
CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password EXTENT MANAGEMENT LOCAL UNDO TABLESPACE undotbs DEFAULT TEMPORARY TABLESPACE tempts1 DEFAULT TABLESPACE users;
SYSTEM tablespace is created as a locally managed tablespace. Without the
LOCAL clause, the
SYSTEM tablespace is created as dictionary managed, which is not recommended.
DATAFILE clause is specified, so the database creates an Oracle managed datafile for the
LOGFILE clauses are included, so the database creates two Oracle managed redo log file groups.
SYSAUX DATAFILE is included, so the database creates an Oracle managed datafile for the
DATAFILE subclause is specified for the
UNDO TABLESPACE and
TABLESPACE clauses, so the database creates an Oracle managed datafile for each of these tablespaces.
TEMPFILE subclause is specified for the
DEFAULT TEMPORARY TABLESPACE clause, so the database creates an Oracle managed tempfile.
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"), then the database automatically sets the appropriate initialization parameters.
Chapter 17, "Using Oracle Managed Files", for information about the Oracle Managed Files feature and how to use it
Oracle Automatic Storage Management Administrator's Guide. for information about Automatic Storage Management
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
DATABASE statement that let you include support for bigfile tablespaces.
See Also:"Bigfile Tablespaces" for more information about bigfile tablespaces
SET DEFAULT...TABLESPACE clause of the
DATABASE statement determines the default type of tablespace for this database in subsequent
TABLESPACE statements. Specify either
SET DEFAULT BIGFILE TABLESPACE or
SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, then 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
TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.
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 mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password 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';
SYSAUX tablespaces are always created with the default tablespace type. However, you can explicitly override the default tablespace type for the
TEMPORARY tablespace during the
CREATE DATABASE mynewdb ... BIGFILE UNDO TABLESPACE undotbs DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE DATABASE mynewdb SET DEFAULT BIGFILE TABLSPACE ... SMALLFILE DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE ...
This section contains:
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 host 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
Offset from Coordinated Universal Time (UTC)
Transition times for Daylight Saving Time
Abbreviations for standard time and Daylight Saving Time
The default time zone file is ORACLE_HOME/oracore/zoneinfo/timezlrg_11.dat. A smaller time zone file with fewer time zones can be found in ORACLE_HOME/oracore/zoneinfo/timezone_11.dat.
SELECT * FROM V$TIMEZONE_NAMES;
See Also:Oracle Database Globalization Support Guide for more information about managing and selecting time zone files
All databases that share information must use the same time zone datafile.
The database server always uses the large time zone file by default. If you would like to use the small time zone file on the client and know that all your data will refer only to regions in the small file, you can set the
ORA_TZFILE environment variable on the client to the full path name of the timezone_
version.dat file on the client, where
version matches the time zone file version that is being used by the database server.
If you are already using the default larger time zone file on the client, 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 file.
Some data definition language statements (such as
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 Language Reference for information about operations that can be done in
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.
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.
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 LOGGINGclause for tablespace creation.
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.