• In the CREATE DATABASE statement, include clauses that specify the password 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

When choosing a password, keep in mind that passwords are case-sensitive. Also, there may be password formatting requirements for your database.

• Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace.

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 DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement.

• You include the UNDO TABLESPACE clause in the CREATE DATABASE statement.

• To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. Alternatively, omit this parameter so that 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 SYS_UNDOTBS.

To specify a default permanent tablespace for the database:

• Include the DEFAULT TABLESPACE clause in the CREATE DATABASE statement

If you do not specify the DEFAULT TABLESPACE clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.

To create a default temporary tablespace for the CDB:

• Include the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement.

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.

• Specify 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_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 and their data files

• Temporary tablespaces and their temp files

• Control files

• Online redo log files

• Archived redo log files

• Flashback logs

• Block change tracking files

• RMAN backups

The following CREATE 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 undotbs1
     DEFAULT TEMPORARY TABLESPACE tempts1
     DEFAULT TABLESPACE users
     ENABLE PLUGGABLE DATABASE
       SEED
       SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
       SYSAUX DATAFILES SIZE 100M;

• 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 data file 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 data file for the SYSAUX tablespace.

• No DATAFILE subclause 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 subclause is specified for the DEFAULT TEMPORARY TABLESPACE clause, so the database creates an Oracle managed temp file.

• 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, then the database automatically sets the appropriate initialization parameters.

  See Also:

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

  • Oracle Database Administrator’s Guide 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

• Set the DB_NAME and DB_DOMAIN initialization parameters.

The DB_NAME initialization parameter determines the local name component of the database name, and the DB_DOMAIN parameter, which is optional, 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.example.com, edit the parameters of the new parameter file as follows:

DB_NAME = test
DB_DOMAIN = us.example.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 recreating the control files. Recreating the control files is easily accomplished with the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE. See Oracle Database Backup and Recovery User's Guide for more information.

Specify the Fast Recovery Area with the following initialization parameters:

• DB_RECOVERY_FILE_DEST: Location of the Fast Recovery Area. This can be a directory, file system, or Automatic Storage Management (Oracle ASM) disk group.

  In an Oracle Real Application Clusters (Oracle RAC) environment, this location must be on a cluster file system, Oracle 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 Fast Recovery Area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.

In an Oracle 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 Fast Recovery Area. You can instead set values for the LOG_ARCHIVE_DEST_n parameters.

Oracle recommends using a Fast Recovery Area, because it can simplify backup and recovery operations for your database.

• Set the CONTROL_FILES initialization parameter.

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 in the same directory as the initialization parameter file, using a default operating system–dependent file name. If you have enabled Oracle Managed Files, the database creates Oracle managed control files.

If you want the database to create new operating system files when creating database control files, the file names listed in the CONTROL_FILES parameter must not match any file names 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 file names listed in the CONTROL_FILES parameter match the file names that are to be reused, and include a CONTROLFILE REUSE clause in the CREATE DATABASE statement.

Oracle strongly recommends you use at least two control files stored on separate physical disk drives for each database.

• Set the DB_BLOCK_SIZE initialization parameter.

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.

• Set the PROCESSES initialization parameter.

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, then you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes for the database instance.

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

A data definition language (DDL) statement is either nonblocking or blocking, and both types of DDL statements require exclusive locks on internal structures. If these locks are unavailable when a DDL statement runs, then nonblocking and blocking DDL statements behave differently:

• Nonblocking DDL waits until every concurrent DML transaction that references the object affected by the DDL either commits or rolls back.

• Blocking DDL fails, though it might have succeeded if it had been executed subseconds later when the locks become available.

To enable blocking DDL statements to wait for locks, specify a DDL lock timeout—the number of seconds a DDL command waits for its required locks before failing.

• To specify a DDL lock timeout, set the DDL_LOCK_TIMEOUT parameter.

The permissible range of values for DDL_LOCK_TIMEOUT is 0 to 1,000,000. The default is 0. You can set DDL_LOCK_TIMEOUT at the system level, or at the session level with an ALTER SESSION statement.

To set up an environment for automatic undo management using an undo tablespace.

• Set the UNDO_MANAGEMENT initialization parameter to AUTO, which is the default.

To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database initialization parameter file.

The following example sets the LICENSE_MAX_USERS initialization parameter:

LICENSE_MAX_USERS = 200

To migrate to a server parameter file:

To create a server parameter file:

• Run the CREATE SPFILE statement.

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.

You can create a server parameter file (SPFILE) from an existing text initialization parameter file or from memory. Creating the SPFILE from memory means copying the current values of initialization parameters in the running instance to the SPFILE.

The following example creates a server parameter file from text 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 4-1.

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

The next example illustrates creating a server parameter file and supplying a name and location.

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

The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in memory.

CREATE SPFILE FROM MEMORY;

Whether you use the default SPFILE name and default location or specify an SPFILE name and location, if an SPFILE of the same name already exists in the location, it is overwritten without a warning message.

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

The ALTER SYSTEM RESET statement includes a SCOPE clause. When executed in a non-CDB or a multitenant container database (CDB) root, the ALTER SYSTEM RESET statement and SCOPE clause behave differently than when the statement is executed in a pluggable database (PDB), an application root, or an application PDB.

The startup value of a parameter is the value of the parameter in memory after the instance's startup or PDB open has completed. This value can be seen in the VALUE and DISPLAY_VALUE columns in the V$SYSTEM_PARAMETER view immediately after startup. The startup value can be different from the value in the spfile or the default value (if the parameter is not set in the spfile), since the value of the parameter can be adjusted internally at startup.

The SCOPE values for the ALTER SYSTEM RESET statement behave as follows in a non-CDB and in the CDB$ROOT of a CDB:

• SCOPE=SPFILE: If an instance is using spfile, removes the parameter from the spfile; the default value takes effect upon the next instance startup.

• SCOPE=MEMORY: The startup value takes effect immediately. However, the change is not stored in instance's spfile and will be lost upon instance restart.

• SCOPE=BOTH: If an instance is using spfile, removes the parameter from the spfile; the default value takes effect immediately and the change is available across instance restart.

The SCOPE values for the ALTER SYSTEM RESET statement behave as follows in a PDB, an application root, or an application PDB:

• SCOPE=SPFILE: Removes the parameter from the container's spfile; the container will inherit the parameter value from its root upon the next PDB open.

• SCOPE=MEMORY: There are two cases:

  • The parameter is present in container's spfile when the container is opened. The parameter value is updated to the startup value for the parameter. This change is not stored in container’s spfile and will be lost upon the next container open.

  • The parameter is not present in container’s spfile when the container is opened. The container starts inheriting the parameter value from its root.

• SCOPE=BOTH: Removes the parameter from the container’s spfile; the container will inherit the parameter value from its root.

• Run a CREATE PFILE statement.

Exporting the server parameter file 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 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, SYSOPER, or SYSBACKUP administrative privilege to execute the CREATE PFILE statement. The exported file is created on the database server system. 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 SPFILE:

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';

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

• Back up the server parameter file either by exporting it or by using RMAN.

There are several ways to recover the SPFILE:

• If the instance is running, issue the following command to re-create the SPFILE from the current values of initialization parameters in memory:

  CREATE SPFILE FROM MEMORY;

  This command creates the SPFILE with the default name and in the default location. You can also create the SPFILE with a new name or in a specified location. See "Creating a Server Parameter File" for examples.

• If you have a valid text initialization parameter file (PFILE), re-create the SPFILE from the PFILE with the following statement:

  CREATE SPFILE FROM PFILE;

  This command assumes that the PFILE is in the default location and has the default name. See "Creating a Server Parameter File" for the command syntax to use when the PFILE is not in the default location or has a nondefault name.

• Restore the SPFILE from backup.

  See "Backing Up the Server Parameter File" for more information.

• If none of the previous methods are possible in your situation, perform these steps:

  1. Create a text initialization parameter file (PFILE) from the parameter value listings in the alert log.

     When an instance starts up, the initialization parameters used for startup are written to the alert log. You can copy and paste this section from the text version of the alert log (without XML tags) into a new PFILE.

     See Oracle Database Administrator’s Guide for more information.

  2. Create the SPFILE from the PFILE.

     See "Creating a Server Parameter File" for instructions.

Read/Write Errors During a Parameter Update

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 described earlier in this section, and then restart the instance.

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