Shulou(Shulou.com)06/01 Report--

Specifying CREATE DATABASE Statement Clauses

When you execute a CREATE DATABASE statement, Oracle Database performs several 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:

When the CREATE DATABASE statement is executed, the Oracle database performs multiple operations. The actual action you perform depends on the clause you specify in the CREATE DATABASE statement and the initialization parameters you set. The Oracle database does at least the following:

Creates the datafiles for the databaseCreates the control files for the databaseCreates the redo log files for the database and establishes the ARCHIVELOG modeCreates the SYSTEM tablespaceCreates the SYSAUX tablespaceCreates the data dictionarySets the character set that stores data in the databaseSets the database time zoneMounts and opens the database for useThis section discusses several of the clauses of the CREATE 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:

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEMCreating a Locally Managed SYSTEM TablespaceAbout the SYSAUX TablespaceUsing Automatic Undo Management: Creating an Undo TablespaceCreating a Default Temporary TablespaceSpecifying Oracle Managed Files at Database CreationSupporting Bigfile Tablespaces During Database CreationSpecifying the Database Time Zone and Time Zone FileSpecifying FORCE LOGGING ModeProtecting 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 passwordUSER SYSTEM IDENTIFIED BY passwordIf you omit these clauses, then 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 must change these passwords using the ALTER USER statement immediately after database creation.

If these clauses are omitted, the default passwords change_on_install and manager are assigned to these users, respectively. Writes a record to the alert log, indicating that the default password was used. To protect the database, you must use the ALTER USER statement to change these passwords immediately after the database is created.

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.

Oracle strongly recommends specifying these clauses, even if they are optional in this version of the Oracle database. The default password is well known, and if you ignore the password change later, it will make the database vulnerable to 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.

When choosing a password, keep in mind that, starting with version 11g, passwords are case-sensitive. In addition, there may be password formatting requirements in your database.

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

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create locally managed SYSTEM tablespaces. The COMPATIBLE initialization parameter must be set to 10.0.0 or higher for this statement to succeed. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default, the database creates a dictionary-managed SYSTEM tablespace. Dictionary managed tablespaces have been 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:

If you create a database using locally managed SYSTEM tablespaces, and if you do not use Oracle Managed Files, make sure that the following conditions are met:

You specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement.You include the UNDO TABLESPACE clause in the CREATE DATABASE statement.See Also:

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 SYSTEM tablespace

"Locally Managed Tablespaces"

"Migrating the SYSTEM Tablespace to a Locally Managed Tablespace"

About 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. It also reduces the load on the SYSTEM tablespace.

SYSAUX tablespaces are always created when the database is created. The SYSAUX table space is used as a secondary table space for the SYSTEM table space. Because it is the default tablespace for many Oracle database functions and products that previously required their own tablespaces, the number of tablespaces required by the database can be reduced. It also reduces the load on SYSTEM tablespaces.

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:

You can use the SYSAUX DATAFILE clause in the CREATE DATABASE statement to specify the data file properties of the SYSAUX tablespace. The required properties for SYSAUX tablespaces are set by the Oracle database, including:

PERMANENTREAD WRITEEXTENT MANAGMENT LOCALSEGMENT SPACE MANAGMENT AUTOYou 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 SYSAUXtablespace.

You cannot change these properties using the ALTER TABLESPACE statement, and any attempt will result in an error. You cannot delete or rename SYSAUX tablespaces.

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

The size of the SYSAUX tablespace is determined by the size of the database components that occupy the SYSAUX. You can view a list of these components by querying the V$SYSAUX_OCCUPANTS view. Depending on the initial size of these components, the SYSAUX tablespace must be at least 400 MB when creating the database. After the database is fully deployed, the space requirements for SYSAUX tablespaces will increase, depending on its usage and the nature of the workload.

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 the DATAFILE clause is included for the SYSTEM table space, the SYSAUX DATAFILE clause must be specified, or the CREATE DATABASE statement will fail. This requirement does not exist if the Oracle Managed Files feature is enabled

The SYSAUX tablespace has the same security attributes as the SYSTEM tablespace.

SYSAUX table spaces have the same security attributes as SYSTEM table spaces.

Note:

This documentation 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.

See Also: "Managing the SYSAUX Tablespace"

Create the SYSAUX database when you create the database. SYSAUX tablespaces are never required when upgrading in the Oracle database version, and SYSAUX tablespaces must be created during the upgrade process

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. 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 theCREATE 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 SYS_UNDOTBS.

Automatic undo management uses undo table spaces. To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in the initialization parameter file. Or, omitting this parameter, the 11g database defaults to automatic undo management. In this mode, undo data is stored in the undo tablespace and managed by the Oracle database. To define and name undo tablespaces yourself, you must include the UNDO TABLESPACE clause in the CROTE DATABASE statement when the database is created. If you omit this clause and enable automatic undo management, the database creates a default undo tablespace named SYS_UNDOTBS.

See Also:

"Specifying the Method of Undo Space Management"

Chapter 16, "Managing Undo", for information about the creation and use of undo tablespaces

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 SYSTEMtablespace is the default permanent tablespace for non-SYSTEM users. Oracle strongly recommends that you create a default permanent tablespace.

The DEFAULT TABLESPACE clause in the CREATE DATABASE statement specifies the default permanent tablespace for the database. The Oracle database assigns any non-SYSTEM users to this tablespace that do not explicitly specify a different permanent tablespace. If this clause is not specified, the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users. Oracle strongly recommends creating a default permanent tablespace.

See Also:

Oracle Database SQL Language 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.

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement creates the default temporary tablespace for the database. The Oracle database allocates this table space to users who do not explicitly allocate temporary table space.

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.

You can explicitly assign temporary tablespaces or tablespace groups to users in the CREATE USER statement. However, if you do not, and if you do not specify a default temporary tablespace for the database, these users will be assigned as SYSTEM tablespaces as their temporary tablespaces by default. Storing temporary data in SYSTEM tablespaces is a bad practice, and allocating one temporary tablespace per user is troublesome. Therefore, Oracle recommends using 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 you must create a default temporary tablespace. This behavior is explained in "Creating a Locally Managed SYSTEM Tablespace".

When specifying locally managed SYSTEM table spaces, SYSTEM table spaces cannot be used as temporary table spaces. In this case, you must create a default temporary tablespace.

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

You can minimize the number of clauses and parameters you specify in CREATE DATABASE statements by using the Oracle managed files feature. You can do this by specifying the directory or Oracle automatic Storage Management (Oracle ASM) disk group where files are created and managed by the Oracle database.

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:

By setting the initialization parameter DB_CREATE_FILE_DEST,DB_CREATE_ONLINE_LOG_DEST_n or DB_RECOVERY_FILE_DEST in the initialization parameter file, you can guide the Oracle database to create and manage the underlying operating system files of the database. The Oracle database automatically creates and manages operating system files for the following database structures, depending on the initialization parameters you specify and the clauses specified in the CREATE DATABASE statement:

Tablespaces and their datafilesTemporary tablespaces and their tempfilesControl filesRedo log filesArchived redo log filesFlashback logsBlock change tracking filesRMAN backupsSee Also:

"Specifying a Fast Recovery Area" for information about setting initialization parameters that create a Fast Recovery Area

The following CREATE DATABASE statement shows briefly how the Oracle Managed Files feature works, assuming you have specified required initialization parameters:

The following CREATE DATABASE statement briefly describes how the Oracle managed file feature works, assuming that you have specified the 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

The SYSTEM tablespace is created as a locally managed tablespace. Without the EXTENT MANAGEMENT LOCAL clause, the SYSTEM tablespace is created as dictionary managed, which is not recommended.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 and DEFAULT TABLESPACE clauses, so the database creates an Oracle managed datafile for each of these tablespaces.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"), then the database automatically sets the appropriate initialization parameters.SYSTEM table spaces are created as locally managed table spaces. Without the EXTENT MANAGEMENT LOCAL clause, SYSTEM tablespaces are created for dictionary management and are not recommended. No DATAFILE clause is specified, so the database creates an Oracle managed data file for the SYSTEM tablespace. The LOGFILE clause is not included, so the database creates two Oracle-managed redo log filegroups. SYSAUX DATAFILE is not included, so the database creates an Oracle managed data file for the SYSAUX tablespace. No DATAFILE clause is specified for the UNDO TABLESPACE and DEFAULT TABLESPACE clauses, so the database creates an Oracle managed data file for each of these tablespaces. No TEMPFILE clause is specified for the DEFAULT TEMPORARY TABLESPACE clause, so the database creates a temporary file hosted by Oracle. If the CONTROL_FILES initialization parameter is not specified in the initialization parameter file, the database also creates an Oracle managed control file.

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.

Oracle databases simplify the management of tablespaces and support very large databases by creating bigfile tablespaces. The Bigfile tablespace can contain only one data file, but the file can have up to 4G blocks. The number of most big data files in the Oracle database is limited (usually 64K files). Therefore bigfile tablespaces can significantly enhance the storage capacity of Oracle databases.

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 determines the default type of tablespace for this database in subsequent CREATETABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, then the default is asmallfile 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 SET DEFAULT of the CREATE DATABASE statement. The TABLESPACE clause determines the default type of tablespaces in the CREATE TABLESPACE statement. Specify SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is an smallfile tablespace, which is a traditional type of Oracle database tablespace. An smallfile tablespace can contain up to 1022 files and up to 4m blocks.

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 use of bigfile tablespaces further enhances Oracle Managed Files capabilities because bigfile tablespaces make data files completely transparent to users. The SQL syntax of ALTER TABLESPACE statements has been extended to allow operations on tablespaces rather than data files.

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:

The CREATE DATABASE statement in "specify an Oracle managed file when the database is created" can be modified as follows to specify that the default type of table space is an bigfile table space:

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

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

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

You can determine the current default tablespace type of the database by querying the DATABASE_PROPERTIES data dictionary view, as shown below:

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 theUNDO and DEFAULT TEMPORARY tablespace during the CREATE DATABASE operation.

SYSTEM and SYSAUX tablespaces are always created using the default tablespace type. However, you can explicitly override the default tablespace types of UNDO and DEFAULT TEMPORARY tablespaces during CREATE DATABASE.

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

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

CREATE DATABASE mynewdb

...

BIGFILE UNDO TABLESPACE undotbs

DATAFILE'/ u01Accord oradataUndotbs01.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 mynewdb

SET DEFAULT BIGFILE TABLESPACE

...

SMALLFILE DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE'/ u01Accord oradataAccording to MynewdbUnitemp01.dbf'

SIZE 20M REUSE

...

Specifying the Database Time Zone and Time Zone File

This section contains:

Setting the Database Time ZoneAbout the Database Time Zone FilesSpecifying the Database Time Zone FileSetting 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 host operating system.

Sets the database time zone when you create a database using the SET TIME_ zone clause of the CREATE DATABASE statement. If you do not set the database time zone, the default is 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.

You can use the SET TIME_ZONE clause of the ALTER SESSION statement to change the database time zone of the session.

See Also:

Oracle Database Globalization Support Guide for more information about setting the database time zone

About the Database Time Zone Files

Two time zone files are included in a subdirectory of the Oracle home directory. The time zone files contain the valid time zone names. The following information is also included for each time zone:

The two time zone files are contained in a subdirectory of the Oracle home directory. The time zone file contains a valid time zone name. Each time zone also includes the following information:

Offset from Coordinated Universal Time (UTC) Transition times for Daylight Saving TimeAbbreviations for standard time and Daylight Saving TimeThe default time zone file is ORACLE_HOME/oracore/zoneinfo/timezlrg_11.dat. A smaller timezone file with fewer time zones can be found inORACLE_HOME/oracore/zoneinfo/timezone_11.dat.

The default time zone file is $ORACLE_HOME / oracore / zoneinfo / timezlrg_11.dat. The smaller zone file can be found in $ORACLE_HOME / oracore / zoneinfo / timezone_11.dat.

To view the time zone names in the files used by the database, use the following query

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

SELECT * FROM V$TIMEZONE_NAMES

See Also:

Oracle Database Globalization Support Guide for more information about managing and selecting time zone files

Specifying the Database Time Zone File

All databases that share information must use the same time zone datafile.

All databases that share information must use the same time zone data file.

The database server always uses the large time zone file by default. If you would like to use the small timezone 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 timezone file version that is being used by the database server.

The database server uses large time zone files by default. If you want to use a small time zone file on the client and know that all data can only refer to the zone in the small file, you can set the ORA_TZFILE environment variable on the client to the full pathname of the timezone_version.dat, where the version matches the version of the time zone file 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.

If you already use the default larger time zone file on the client, it is impractical to change to a smaller time zone file, because the database may contain data that does not belong to the smaller file.

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.

Some data definition language statements, such as CREATE TABLE, allow NOLOGGING clauses, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting speeds up easy recovery outside of the database recovery mechanism, but has a negative impact on 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.

NOLOGGING,Oracle records can be forced to be written even if the NOLOGGING,Oracle database is specified in the DDL statement. The database never generates redo records for temporary tablespaces and temporary periods, so forcing logging has no effect on objects.

See Also:

Oracle Database SQL Language 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.

To place the database in FORCE LOGGING mode, use the FORCE LOGGING clause in the CREATE DATABASE statement. If you do not specify this clause, the database will not be placed in 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.

After the database is created, use the ALTER DATABASE statement to place the database in FORCE LOGGING mode. This statement may take quite a long time to complete because it waits for all unwritten writes to complete.

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

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, ifFORCE 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 is specified independently of the database, and you can optionally specify FORCE LOGGING or NO FORCE LOGGING at the tablespace level. However, if the FORCE LOGGING schema is valid for the database, it takes precedence over the tablespace setting. If the database does not work, individual tablespace settings are enforced. Oracle recommends that you put the entire database in FORCE LOGGING schema, otherwise place individual tablespaces in FORCE LOGGING schema, but not at the same time.

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.

The FORCE LOGGING schema is a persistent property of the database. That is, if the database is shut down and restarted, it will remain in the same logging mode. However, if you recreate the control file, the database will not restart in FORCE LOGGING mode unless the FORCE LOGGING clause is specified 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:

FORCE LOGGING mode causes performance degradation. If the main reason for specifying FORCE LOGGING is to ensure full media recovery and that no standby database is active, 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? how many media failures are likely to occur?

What is the degree of damage when direct writes cannot be recovered?

Does forced recording tolerate performance degradation?

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.

If the database is running in NOARCHIVELOG mode, it is generally not beneficial to put the database in FORCE LOGGING mode. In NOARCHIVELOG mode, media recovery is not possible, so if you use it in combination with forced login, the result may be slow performance and less effective.

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.