Oracle Database 11g R2 Administration Part 1

Administrator’s Guide Oracle-Database-11g R2

Getting Started with Database Administration

Types of Oracle Database Users

The types of users and their roles and responsibilities depend on the database site. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people and among several areas of specialization.

Database Administrators

Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.

A database administrator’s responsibilities can include the following tasks:

  1. Installing and upgrading the Oracle Database server and application tools
  2. Allocating system storage and planning future storage requirements for the database system
  3. Creating primary database storage structures (tablespaces) after application developers have designed an application
  4. Creating primary objects (tables, views, indexes) once application developers have designed an application
  5. Modifying the database structure, as necessary, from information given by application developers
  6. Enrolling users and maintaining system security
  7. Ensuring compliance with Oracle license agreements
  8. Controlling and monitoring user access to the database
  9. Monitoring and optimizing the performance of the database
  10. Planning for backup and recovery of database information
  11. Maintaining archived data on tape
  12. Backing up and restoring the database
  13. Contacting Oracle for technical support

Security Officers

In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DBA, you might not be responsible for these duties if your site has a separate security officer. See Oracle Database Security Guide for information about the duties of security officers.


Network Administrators

Some sites have one or more network administrators. A network administrator, for example, administers Oracle networking products, such as Oracle Net Services. See Oracle Database Net Services Administrator’s Guide for information about the duties of network administrators.

Application Developers

Application developers design and implement database applications. Their responsibilities include the following tasks:

  1. Designing and developing the database application
  2. Designing the database structure for an application
  3. Estimating storage requirements for an application
  4. Specifying modifications of the database structure for an application
  5. Relaying this information to a database administrator
  6. Tuning the application during development
  7. Establishing security measures for an application during development

Application developers can perform some of these tasks in collaboration with DBAs. See Oracle Database Advanced Application Developer’s Guide for information about application development tasks.

Application Administrators

An Oracle Database site can assign one or more application administrators to administer a particular application. Each application can have its own administrator.

Database Users

Database users interact with the database through applications or utilities. A typical user’s responsibilities include the following tasks:

  1. Entering, modifying, and deleting data, where permitted
  2. Generating reports from the data

Tasks of a Database Administrator

Note: When upgrading to a new release, back up your existing production environment, both software and database, before installation. For information on preserving your existing production database, see Oracle Database Upgrade Guide.

Task 1: Evaluate the Database Server Hardware

Evaluate how Oracle Database and its applications can best use the available computer resources. This evaluation should reveal the following information:

  1. How many disk drives are available to the Oracle products
  2. How many, if any, dedicated tape drives are available to Oracle products
  3. How much memory is available to the instances of Oracle Database you will run (see your system configuration documentation)

Task 2: Install the Oracle Database Software

As the database administrator, you install the Oracle Database server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote systems to the computer that executes Oracle Database.

For more information on what software to install, see “Identifying Your Oracle Database Software Release” on page 1-12.

Task 3: Plan the Database

As the database administrator, you must plan:

  1. The logical storage structure of the database
  2. The overall database design

    1. A backup strategy for the database
    2. It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many data files will comprise the tablespace, what type of information will be stored in each tablespace, and on which disk drives the data files will be physically stored. When planning the overall logical storage of the database structure, consider the effects that this structure will have when the database is actually created and running. Consider how the logical storage structure of the database will affect:
  3. The performance of the computer running Oracle Database
  4. The performance of the database during data access operations
  5. The efficiency of backup and recovery procedures for the database

Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects application performance.

During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database to improve backup efficiency.

It is beyond the scope of this book to discuss relational and distributed database design. If you are not familiar with such design issues, see accepted industry-standard documentation.

Part II, “Oracle Database Structure and Storage” , and Part III, “Schema Objects” , provide specific information on creating logical storage structures, objects, and integrity constraints for your database.

Task 4: Create and Open the Database

After you complete the database design, you can create the database and open it for normal use. You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.

See Chapter 2, “Creating and Configuring an Oracle Database” , for information on creating a database and Chapter 3, “Starting Up and Shutting Down” for guidance in starting up the database.

Task 5: Back Up the Database

After you create the database structure, perform the backup strategy you planned for the database. Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.

Task 6: Enroll System Users

After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, and grant appropriate privileges and roles to these users. See Chapter 7, “Managing Users and Securing the Database” for guidance in this task.

Task 7: Implement the Database Design

After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespaces, you can create the database objects.

Part II, “Oracle Database Structure and Storage” and Part III, “Schema Objects” provide information on creating logical storage structures and objects for your database.


Task 8: Back Up the Fully Functional Database

When the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.

Task 9: Tune Database Performance

Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of resources among various user groups. The database resource manager is described in Chapter 27, “Managing Resources with Oracle Database Resource Manager” .

Task 10: Download and Install Patches

After installation and on a regular basis, download and install patches. Patches are available as single interim patches and as patchsets (or patch releases). Interim patches address individual software bugs and may or may not be needed at your installation. Patch releases are collections of bug fixes that are applicable for all customers. Patch releases have release numbers. For example, if you installed Oracle Database 11.2.0.1, the first patch release will have a release number of 11.2.0.2.

Task 11: Roll Out to Additional Hosts

After you have an Oracle Database installation properly configured, tuned, patched, and tested, you may want to roll that exact installation out to other hosts. Reasons to do this include the following:

  1. You have multiple production database systems.

    1. You want to create development and test systems that are identical to your production system.
    2. Instead of installing, tuning, and patching on each additional host, you can clone your tested Oracle Database installation to other hosts, saving time and avoiding inconsistencies. There are two types of cloning available to you:
    3. Cloning an Oracle home—Just the configured and patched binaries from the Oracle home directory and subdirectories are copied to the destination host and “fixed” to match the new environment. You can then start an instance with this cloned home and create a database.
    4. You can use the Enterprise Manager Clone Oracle Home tool to clone an Oracle home to one or more destination hosts. You can also manually clone an Oracle home using a set of provided scripts and Oracle Universal Installer.
  2. Cloning a database—The tuned database, including database files, initialization parameters, and so on, are cloned to an existing Oracle home (possibly a cloned home).

You can use the Enterprise Manager Clone Database tool to clone an Oracle database instance to an existing Oracle home.

Submitting Commands and SQL to the Database

The primary means of communicating with Oracle Database is by submitting SQL statements. Oracle Database also supports a superset of SQL, which includes commands for starting up and shutting down the database, modifying database configuration, and so on. There are three ways to submit these SQL statements and commands to Oracle Database:

  1. Directly, using the command-line interface of SQL*Plus
  2. Indirectly, using the graphical user interface of Oracle Enterprise Manager

With Oracle Enterprise Manager (Enterprise Manager), you use an intuitive graphical interface to administer the database, and Enterprise Manager submits SQL statements and commands behind the scenes.

See Oracle Database 2 Day DBA for more information.

  1. Directly, using SQL Developer

Developers use SQL Developer to create and test database schemas and

applications, although you can also use it for database administration tasks.

See Oracle Database 2 Day Developer’s Guide for more information.

This section focuses on using SQL*Plus to submit SQL statements and commands to the database. It includes the following topics:

  1. About SQL*Plus
  2. Connecting to the Database with SQL*Plus

About SQL*Plus

SQL*Plus is the primary command-line interface to your Oracle database. You use SQL*Plus to start up and shut down the database, set database initialization parameters, create and manage users, create and alter database objects (such as tables and indexes), insert and update data, run SQL queries, and more.

Before you can submit SQL statements and commands, you must connect to the database. With SQL*Plus, you can connect locally or remotely. Connecting locally means connecting to an Oracle database running on the same computer on which you are running SQL*Plus. Connecting remotely means connecting over a network to an Oracle database that is running

on a remote computer. Such a database is referred to as a remote database. The SQL*Plus executable on the local computer is provided by a full Oracle Database installation, an Oracle Client installation, or an Instant Client installation.

Connecting to the Database with SQL*Plus

Oracle Database includes the following components:

  1. The Oracle Database instance, which is a collection of processes and memory
  2. A set of disk files that contain user data and system data

When you connect with SQL*Plus, you are connecting to the Oracle instance. Each instance has an instance ID, also known as a system ID (SID). Because there can be multiple Oracle instances on a host computer, each with its own set of data files, you must identify the instance to which you want to connect. For a local connection, you identify the instance by setting operating system environment variables. For a remote connection, you identify the instance by specifying a network address and a database service name. For both local and remote connections, you must set environment variables to help the operating system find the SQL*Plus executable and to provide the executable with a path to its support files and scripts. To connect to an Oracle instance with SQL*Plus, therefore, you must complete the following steps:

Step 1: Open a Command Window Step 2: Set Operating System Environment Variables Step 3:

Start SQL*Plus Step 4: Submit the SQL*Plus CONNECT Statement

Step 1: Open a Command Window

Take the necessary action on your platform to open a window into which you can enter operating system commands.

Step 2: Set Operating System Environment Variables

Depending on your platform, you may have to set environment variables before starting SQL*Plus, or at least verify that they are set properly.

For example, on most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH environment variable to include the ORACLE_ HOME/bin directory. Some platforms may require additional environment variables:

  1. On the UNIX and Linux platforms, you must set environment variables by entering operating system commands.
  2. On the Windows platform, Oracle Universal Installer (OUI) automatically assigns values to ORACLE_HOME and ORACLE_SID in the Windows registry.

If you did not create a database upon installation, OUI does not set ORACLE_SID in the registry; after you create your database at a later time, you must set the ORACLE_SID environment variable from a command window.

UNIX and Linux installations come with two scripts, oraenvand coraenv, that you can use to easily set environment variables. For more information, see Administrator’s Reference for UNIX Systems.

For all platforms, when switching between instances with different Oracle homes, you must change the ORACLE_HOME environment variable. If multiple instances share the same Oracle home, you must change only ORACLE_SID when switching instances.

See the Oracle Database Installation Guide or administration guide for your operating system for details on environment variables and for information on switching instances.

Example 1–1 Setting Environment Variables in UNIX (C Shell)

setenv ORACLE_SID orcl setenv ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Example 1–2 Setting Environment Variables in Windows

SET ORACLE_SID=orawin2

Example 1–2 assumes that ORACLE_HOME and ORACLE_SID are set in the registry but that you want to override the registry value of ORACLE_SID to connect to a different instance.

On Windows, environment variable values that you set in a command prompt window override the values in the registry.

Step 3: Start SQL*Plus

To start SQL*Plus:

  1. Do one of the following:

    1. Ensure that the PATH environment variable contains ORACLE_HOME/bin.
    2. Change directory to ORACLE_HOME/bin.

      1. Enter the following command (case-sensitive on UNIX and Linux):

sqlplus /nolog

Step 4: Submit the SQL*Plus CONNECT Statement

You submit the SQL*Plus CONNECT statement to initially connect to the Oracle instance or at any time to reconnect as a different user. The syntax of the CONNECT statement is as follows:

CONN[ECT] [logon] [AS {SYSOPER | SYSDBA}]

The syntax of logon is as follows: {username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]

When you provide username, SQL*Plus prompts for a password. The password is not echoed as you type it.

The following table describes the syntax components of the CONNECT statement.

Syntax Component Description
/ Calls for external authentication of the connection
request. A database password is not used in this type
of authentication. The most common form of
external authentication is operating system
authentication, where the database user is
authenticated by having logged in to the host
operating system with a certain host user account.
External authentication can also be performed with
an Oracle wallet or by a network service. See Oracle
Database Security Guide for more information. See
also “Using Operating System Authentication” on
page 1-20.
AS {SYSOPER | Indicates that the database user is connecting with
SYSDBA} either the SYSOPER or SYSDBA system privilege. Only
certain predefined administrative users or users who
have been added to the password file may connect
with these privileges. See “Administrative Privileges”
on page 1-16 for more information.
username A valid database user name. The database
authenticates the connection request by matching
username against the data dictionary and prompting
for a user password.

connect_identifier (1) An Oracle Net connect identifier, for a remote connection. The exact syntax depends on the Oracle Net configuration. If omitted, SQL*Plus attempts connection to a local instance. A common connect identifier is a net service name. This is an alias for an Oracle Net connect descriptor (network address and database service name). The alias is typically resolved in the tnsnames.ora file on the local computer, but can be resolved in other ways. See Oracle Database Net Services Administrator’s Guide for more information on connect identifiers.

Syntax Component Description

connect_identifier (2) As an alternative, a connect identifier can use easy connect syntax. Easy connect provides out-of-the-box TCP/IP connectivity for remote databases without having to configure Oracle Net Services on the client (local) computer.

Easy connect syntax for the connect identifier is as follows (the enclosing double-quotes must be included):

host[:port][/service_name][:server][/instance_name]”

where:

    1. host is the host name or IP address of the computer hosting the remote database.
    2. Both IP version 4 (IPv4) and IP version 6 (IPv6) addresses are supported. IPv6 addresses must be enclosed in square brackets. See Oracle Database Net Services Administrator’s Guide for information about IPv6 addressing.
  1. port is the TCP port on which the Oracle Net listener on host listens for database connections. If omitted, 1521 is assumed.
  2. service_name is the database service name to which to connect. Can be omitted if the Net Services listener configuration on the remote host designates a default service. If no default service is configured, service_name must be supplied. Each database typically offers a standard service with a name equal to the global database name, which is made up of the DB_NAME and DB_DOMAIN initialization parameters as follows:

DB_NAME.DB_DOMAIN

If DB_DOMAIN is null, then the standard service name is just the DB_NAME. For example, if DB_NAME is orcl and DB_ DOMAINis us.example.com, then the standard service name is orcl.us.example.com.


See “Managing Application Workloads with Database

Services” on page 2-39 for more information.

  1. server is the type of service handler. Acceptable values are dedicated, shared, and pooled. If omitted, the default type of server is chosen by the listener: shared server if configured, otherwise dedicated server.
  2. instance_name is the instance to which to connect. You can specify both service name and instance name, which you would typically do only for Oracle Real Application Clusters (Oracle RAC) environments. For Oracle RAC or single instance environments, if you specify only instance name, you connect to the default database service. If there is no default service configured in the listener.ora file, an error is generated.You can obtain the instance name from the instance_name initialization parameter.

See Oracle Database Net Services Administrator’s Guide for more information on easy connect.

edition={edition_name | DATABASE_DEFAULT}

Specifies the edition in which the new database session starts. If you specify an edition, it must exist and you must have the USE privilege on it. If this clause is not specified, the database default edition is used for the session.

See Oracle Database Advanced Application Developer’s Guide for information on editions and edition-based redefinition.

Example 1–3

This simple example connects to a local database as user SYSTEM. SQL*Plus prompts for the SYSTEM user password.

connect system

Example 1–4

This example connects to a local database as user SYS with the SYSDBA privilege. SQL*Plus prompts for the SYS user password.

connect sys as sysdba

When connecting as user SYS, you must connect AS SYSDBA.

Example 1–5

This example connects locally with the SYSDBA privilege with operating system authentication.


connect / as sysdba

Example 1–6

This example uses easy connect syntax to connect as user salesadmin to a remote database running on the host dbhost.example.com. The Oracle Net listener (the listener) is listening on the default port (1521). The database service is sales.example.com. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@”dbhost.example.com/sales.example.com”

Example 1–7

This example is identical to Example 1–6 , except that the service handler type is indicated.

connect salesadmin@”dbhost.example.com/sales.example.com:dedicated”

Example 1–8

This example is identical to Example 1–6 , except that the listener is listening on the nondefault port number 1522.

connect salesadmin@”dbhost.example.com:1522/sales.example.com”

Example 1–9

This example is identical to Example 1–6 , except that the host IP address is substituted for the host name.

connect salesadmin@”192.0.2.5/sales.example.com”

Example 1–10

This example connects using an IPv6 address. Note the enclosing square brackets.

connect salesadmin@”[2001:0DB8:0:0::200C:417A]/sales.example.com”

Example 1–11

This example specifies the instance to which to connect and omits the database service name. A default database service must have been specified, otherwise an error is generated. Note that when you specify the instance only, you cannot specify the service handler type.

connect salesadmin@”dbhost.example.com//orcl”

Example 1–12

This example connects remotely as user salesadmin to the database service designated by the net service name sales1. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@sales1

Example 1–13

This example connects remotely with external authentication to the database service designated by the net service name sales1.

connect /@sales1

Example 1–14

This example connects remotely with the SYSDBA privilege and with external authentication to the database service designated by the net service name sales1.

connect /@sales1 as sysdba

Example 1–15

This example connects remotely as user salesadmin to the database service designated by the net service name sales1. The database session starts in the rev21 edition. SQL*Plus prompts for the salesadmin user password.

connect salesadmin@sales1 edition=rev21

Identifying Your Oracle Database Software Release

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database release labeled “11.2.0.1.0”.

Figure 1–1 Example of an Oracle Database Release Number

11.2.0.1.0

Major database release number

Database maintenance release number Platform specific release number

Component specific release number

Fusion Middleware release number

Note: Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second numeral of a release number. In previous releases, the third numeral indicated a particular maintenance release.

Major Database Release Number

The first numeral is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second numeral represents a maintenance release level. Some new features may also be included.

Fusion Middleware Release Number

The third numeral reflects the release level of Oracle Fusion Middleware.

Component-Specific Release Number

The fourth numeral identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth numeral identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this numeral will be the same across the affected platforms.

Checking Your Current Release Number


To identify the release of Oracle Database that is currently installed and to see the release

levels of other database components you are using, query the data dictionary view

PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION

view to see component-level information.) Other product release levels may increment

independent of the database server.

COL PRODUCT FORMAT A40 COL VERSION FORMAT A15 COL STATUS FORMAT A15

SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS

NLSRTL 11.2.0.0.1 Production Oracle Database 11g Enterprise Edition 11.2.0.0.1 Production PL/SQL 11.2.0.0.1 Production …

It is important to convey to Oracle the results of this query when you report problems with the software.

About Database Administrator Security and Privileges

To perform the administrative tasks of an Oracle Database DBA, you need specific privileges within the database and possibly in the operating system of the server on which the database runs. Access to a database administrator’s account should be tightly controlled.

The Database Administrator’s Operating System Account

To perform many of the administrative duties for a database, you must be able to execute operating system commands. Depending on the operating system on which Oracle Database is running, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require operating system privileges or access rights that other database users do not require (for example, to perform Oracle Database software installation). Although you do not need the Oracle Database files to be stored in your account, you should have access to them.

Administrative User Accounts

Two administrative user accounts are automatically created when Oracle Database is installed:

  1. SYS (default password: CHANGE_ON_INSTALL)
  2. SYSTEM (default password: MANAGER)

Note: Both Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) now prompt for SYS and SYSTEM passwords and do not accept the default passwords “change_on_ install” or “manager”, respectively.

If you create the database manually, Oracle strongly recommends that you specify passwords for SYS and SYSTEM at database creation time, rather than using these default passwords. See “Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM”

on page 2-16 for more information.

Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.

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.

SYS

When you create an Oracle database, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect to Oracle Database using the SYS account.

SYSTEM

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM user name is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.

The DBA Role

A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.

Note: The DBA role does not include the SYSDBA or SYSOPER system privileges. These are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database and instance startup and shutdown. These system privileges are discussed in “Administrative Privileges” on page 1-16.

Database Administrator Authentication

As a DBA, you often perform special operations such as shutting down or starting up a database. Because only a DBA should perform these operations, the database administrator usernames require a secure authentication scheme.

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.

Note: The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSDBA and SYSOPER

The following operations are authorized by the SYSDBA and SYSOPER system privileges:

System Privilege Operations Authorized

SYSDBA ■ Perform STARTUP and SHUTDOWN operations

  1. ALTER DATABASE: open, mount, back up, or change character set
  2. CREATE DATABASE
  3. DROP DATABASE
  4. CREATE SPFILE
  5. ALTER DATABASE ARCHIVELOG
  6. ALTER DATABASE RECOVER
  7. Includes the RESTRICTED SESSION privilege Effectively, this system privilege allows a user to connect as user SYS.


SYSOPER ■ Perform STARTUP and SHUTDOWN operations

  1. CREATE SPFILE
  2. ALTER DATABASE OPEN/MOUNT/BACKUP
  3. ALTER DATABASE ARCHIVELOG
  4. ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
  5. Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.

When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

Connecting with Administrative Privileges: Example

This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:

CONNECT oe CREATE TABLE admin_test(name VARCHAR2(20));

Later, user oe issues these statements:

CONNECT oe AS SYSDBA SELECT * FROM admin_test;

User oe now receives the following error:

ORA-00942: table or view does not exist

Having connected as SYSDBA, user oe now references the SYS schema, but the table was created in the oe schema.

Selecting an Authentication Method for Database Administrators

Database Administrators can authenticate database administrators through the data dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and

return to pre–Release 11g behavior by setting the SEC_ CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

  1. Operating system (OS) authentication
  2. Password files
  3. Strong authentication with a network-based authentication service, such as Oracle Internet Directory

These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

The remainder of this section focuses on operating system authentication and password file authentication. See Oracle Database Security Guide for information about authenticating database administrators with network-based authentication services.

Notes:

  1. These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle Database. CONNECT INTERNAL is no longer supported.
  2. Operating system authentication takes precedence over password file authentication. If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.

Your choice will be influenced by whether you intend to administer your database locally on the same system where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1–2 illustrates the choices you have for database administrator authentication schemes.

Figure 1–2 Database Administrator Authentication Methods

Remote Database Local Database Administration Administration


If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database user names that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in “Using Password File Authentication” on page 1-21.

Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

  1. If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
  2. If the server is not using a password file, or if you have not been granted SYSDBAor SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.

Using Operating System Authentication

This section describes how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Membership in one of two special operating system groups enables a DBA to authenticate to the database through the operating system rather than with a database user name and password. This is known as operating system authentication. These operating system groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The default names vary depending upon your operating system,

and are listed in the following table:

Oracle Universal Installer uses these default names, but you can

Operating System Group

OSDBA

OSOPER

UNIX User Group Windows User Group

dba ORA_DBA

oper ORA_OPER

override them. One reason to override them is if you have multiple instances running on the same host computer. If each instance is to have a different person as the principal DBA, you can improve the security of each instance by creating a different OSDBA group for each instance. For example, for two instances on the same host, the OSDBA group for the first instance could be named dba1, and OSDBA for the second instance could be named dba2. The first DBA would be a member of dba1 only, and the second DBA would be a member of dba2 only. Thus, when using operating system authentication, each DBA would be able to connect only to his assigned instance.

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

  1. If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
  2. If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
  3. If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.

Preparing to Use Operating System Authentication

To enable operating system authentication of an administrative user:

  1. Create an operating system account for the user.
  2. Add the account to the OSDBA or OSOPER operating system defined groups.

Connecting Using Operating System Authentication

A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:


CONNECT / AS SYSDBA CONNECT / AS SYSOPER

For the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:

CONNECT /@net_service_name AS SYSDBA CONNECT /@net_service_name AS SYSOPER

Both the client computer and database host computer must be on a Windows domain.

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

  1. If not already created, create the password file using the ORAPWD utility: ORAPWD FILE=filename ENTRIES=max_users

Notes:

  1. When you invoke Database Configuration Assistant (DBCA) as part of the Oracle Database installation process, DBCA creates a password file.
  2. Beginning with Oracle Database 11g Release 1, passwords in the password file are case-sensitive unless you include the IGNORECASE =Y command-line argument.

    1. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).
    2. Note: REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and

therefore cannot be changed without restarting the database.

  1. Connect to the database as user SYS (or as another user with the administrative privileges).
  2. If the user does not already exist in the database, create the user and assign a password.

Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to

Getting Started with Database Administration 1-21

pre–Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

  1. Grant the SYSDBA or SYSOPER system privilege to the user: GRANT SYSDBA to oe;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

Connecting Using Password File Authentication

Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. Note that beginning with Oracle Database 11g Release 1, passwords are case-sensitive unless the password file was created with the IGNORECASE =Y option.

For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following command will fail:

CONNECT oe AS SYSOPER

Note: Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.

If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails.

Creating and Maintaining a Password File

You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.

Creating a Password File with ORAPWD

The syntax of the ORAPWD command is as follows: ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

Command arguments are summarized in the following table.


Argument Description

FILE Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory. ENTRIES (Optional) Maximum number of entries (user accounts) to permit in the file. FORCE (Optional) If y, permits overwriting an existing password file. IGNORECASE (Optional) If y, passwords are treated as case-insensitive.

There are no spaces permitted around the equal-to (=) character.

The command prompts for the SYS password and stores the password in the created password file.

Example

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.

orapwd FILE=orapworcl ENTRIES=30

ORAPWD Command Line Argument Descriptions

The following sections describe the ORAPWD command line arguments.

FILE

This argument sets the name of the password file being created. You must specify the full path name for the file. This argument is mandatory.

The file name required for the password file is operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

Table 1–1 lists the required name and location for the password file on the UNIX, Linux, and Windows platforms. For other platforms, consult your platform-specific documentation.

Table 1–1 Required Password File Name and Location on UNIX, Linux, and Windows

Platform Required Name

Required Location)

UNIX

orapwORACLE_SID

ORACLE_HOME/dbs

and

Linux

Windows PWDORACLE_SID.ora ORACLE_HOME\database

For example, for a database instance with the SID orcldw, the password file must be named orapworcldw on Linux and PWDorcldw.ora on Windows.

In an Oracle Real Application Clusters environment on a platform that requires an environment variable to be set to the path of the password file, the environment variable for each instance must point to the same password file.

Caution: It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

ENTRIES

This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.

Caution: When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate more entries than you think you will ever need.

FORCE

This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE

If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

Sharing and Disabling the Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances. You can also use


this parameter to disable password file authentication. The values recognized for

REMOTE_LOGIN_PASSWORDFILE are:

  1. NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
  2. EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
  3. SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

This option is useful if you are administering multiple databases or an Oracle RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

Note: You cannot change the password for SYS if REMOTE_LOGIN_ PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

Keeping Administrator Passwords Synchronized with the Data Dictionary

If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYS password, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.

To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.

To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:

1. Find all users who have been granted the SYSDBA privilege.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != ‘SYS’ AND SYSDBA=’TRUE’;

  1. Revoke and then re-grant the SYSDBA privilege to these users. REVOKE SYSDBA FROM non-SYS-user; GRANT SYSDBA TO non-SYS-user;
  2. Find all users who have been granted the SYSOPER privilege.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != ‘SYS’ AND SYSOPER=’TRUE’;

4. Revoke and regrant the SYSOPER privilege to these users.

REVOKE SYSOPER FROM non-SYS-user; GRANT SYSOPER TO non-SYS-user;

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user’s name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

A user’s name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

Creating a Password File and Adding New Users to It

Use the following procedure to create a password and add new users to it:

  1. Follow the instructions for creating a password file as explained in “Creating a Password File with ORAPWD” on page 1-23.

    1. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)
    2. Note: REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.
  2. Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:

CONNECT SYS AS SYSDBA

  1. Start up the instance and create the database if necessary, or mount and open an existing database.
  2. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See “Granting and Revoking SYSDBA and SYSOPER Privileges” , later in this section.


Granting and Revoking SYSDBA and SYSOPER Privileges

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the

SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe;

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user’s SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted the SYSDBA, SYSOPER, or SYSASM system privileges. The columns displayed by this view are as follows:

Column Description

USERNAME This column contains the name of the user that is recognized by the password file.

SYSDBA If the value of this column is TRUE, then the user can log on with the SYSDBA system privileges.

SYSOPER If the value of this column is TRUE, then the user can log on with the SYSOPER system privileges.

SYSASM If the value of this column is TRUE, then the user can log on with the SYSASM system privileges.

Note: SYSASM is valid only for Oracle Automatic Storage Management instances.

Maintaining a Password File

This section describes how to:

  1. Expand the number of password file users if the password file becomes full

  2. Remove the password file

Expanding the Number of Password File Users

If you receive an error when you try to grant SYSDBA or SYSOPER system privileges to a user because the file is full, then you must create a larger password file and regrant the privileges to the users.

Replacing a Password File

Use the following procedure to replace a password file:

  1. Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
  2. Delete the existing password file.
  3. Follow the instructions for creating a new password file using the ORAPWD utility in “Creating a Password File with ORAPWD” on page 1-23. Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
  4. Follow the instructions in “Adding Users to a Password File” on page 1-26.

Removing a Password File

If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA or SYSOPER database administration operations.

Data Utilities

Oracle utilities are available to help you maintain the data in your Oracle Database.

SQL*Loader

SQL*Loader is used both by database administrators and by other users of Oracle Database. It loads data from standard operating system files (such as, files in text or C data format) into database tables.

Export and Import Utilities

The Data Pump utility enables you to archive data and to move data between one Oracle Database and another. Also available are the original Import (IMP) and Export (EXP) utilities for importing and exporting data from and to earlier releases.


About Creating an Oracle Database

After you plan your database using some of the guidelines presented in this section, you can create the database with a graphical tool or a SQL command. You typically create a database during Oracle Database software installation. However, you can also create a database after installation. Reasons to create a database after installation are as follows:

  1. You used Oracle Universal Installer (OUI) to install software only, and did not create a database.
  2. You want to create another database (and database instance) on the same host computer as an existing Oracle database. In this case, this chapter assumes that the

new database uses the same Oracle home as the existing database. You can also

create the database in a new Oracle home by running OUI again.

  1. You want to make a copy of (clone) a database. The specific methods for creating a database are:
  2. With Database Configuration Assistant (DBCA), a graphical tool. See “Creating a Database with DBCA” on page 2-5
  3. With the CREATEDATABASE SQL statement. See “Creating a Database with the CREATE DATABASE Statement” on page 2-6

Considerations Before Creating the Database

Database creation prepares several operating system files to work together as an Oracle Database. You only need to create a database once, regardless of how many data files 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 Part II, “Oracle
the amount of Database
space they will require. Structure and

Plan the layout of

the underlying

operating system

files your

Storage”

Part III, “Schema

Objects”

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 data files to reduce contention. And you can control data density (number of rows to a data block). If you create a Fast Recovery Area, Oracle recommends that you place it on a storage device that is different from that of the data files.

To greatly simplify this planning task, consider using Oracle Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.

Oracle Automatic Storage Management Administrator’s Guide

Oracle Database Performance Tuning Guide

Oracle Database Backup and Recovery User’s Guide

Your Oracle operating system–specific documentation, including the appropriate Oracle Database installation guide.

Select the global database name, which is the name and location of “Determining the Global the database within the network structure. Create the global Database Name” on database name by setting both the DB_NAME and DB_DOMAIN page 2-27 initialization parameters.

Table 2–1 (Cont.) Database Planning Tasks

Action Additional Information

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.

“About Initialization Parameters and Initialization Parameter Files” on page 2-24

“What Is a Server Parameter File?” on page 2-33

Oracle Database Reference


Select the database character set. Oracle Database Globalization Support

All character data, including data in the data dictionary, is stored in

Guide

the database character set. You specify the database character set when you create the database.

Consider what time zones your database must support. “Specifying the Database Time Zone File” on

Oracle Database uses one of two time zone files as the source of page 2-22 valid time zones. The default time zone file is timezlrg_11.dat. It contains more time zones than the smaller time zone file, timezone_ 11.dat.

Select the standard database block size. This is specified at database “Specifying Database creation by the DB_BLOCK_SIZE initialization parameter and cannot Block Sizes” on page 2-28 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.

If you plan to store online redo log files on disks with a 4K byte “Planning the Block Size sector size, determine whether you must manually specify redo log of Redo Log Files” on block size. page 12-7

Determine the appropriate initial sizing for the SYSAUX tablespace. “About the SYSAUX Tablespace” on page 2-17

Plan to use a default tablespace for non-SYSTEM users to prevent “Creating a Default inadvertently saving database objects in the SYSTEM tablespace. Permanent Tablespace” on page 2-18

Plan to use an undo tablespace to manage your undo data. Chapter 16, “Managing Undo”

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.

Oracle Database Backup and Recovery User’s Guide

Familiarize yourself with the principles and options of starting up Chapter 3, “Starting Up and shutting down an instance and mounting and opening a and Shutting Down” database.

Selecting a Character Set Oracle recommends AL32UTF8 as the database character set. AL32UTF8 is Oracle’s name for the UTF-8 encoding of the Unicode standard. The Unicode standard is the universal character set that supports most of the currently

Creating and Configuring an Oracle Database 2-3

spoken languages of the world. The use of the Unicode standard is indispensable for any multilingual technology, including database processing.

After a database is created and accumulates production data, changing the database character set is a time consuming and complex project. Therefore, it is very important to select the right character set at installation time. Even if the database does not currently store multilingual data but is expected to store multilingual data within a few years, the choice of AL32UTF8 for the database character set is usually the only good decision.

Even so, the default character set used by Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) for the UNIX, Linux, and Microsoft Windows platforms is not AL32UTF8, but a Microsoft Windows character set known as an ANSI code page. The particular character set is selected based on the current language (locale) of the operating system session that started OUI or DBCA. If the language is American English or one of the Western European languages, the default character set is WE8MSWIN1252. Each Microsoft Windows ANSI Code Page is capable of storing data only from one language or a limited group of languages, such as only Western European, or only Eastern European, or only Japanese.

A Microsoft Windows character set is the default even for databases created on UNIX and Linux platforms because Microsoft Windows is the prevalent platform for client workstations. Oracle Client libraries automatically perform the necessary character set conversion between the database character set and the character sets used by non-Windows client applications.

You may also choose to use any other character set from the presented list of character sets. You can use this option to select a particular character set required by an application vendor, or choose a particular character set that is the common character set used by all clients connecting to this database.

As AL32UTF8 is a multibyte character set, database operations on character data may be slightly slower when compared to single-byte database character sets, such as WE8MSWIN1252. Storage space requirements for text in most languages that use characters outside of the ASCII repertoire are higher in AL32UTF8 compared to legacy character sets supporting the language. The increase in storage space concerns only character data and only data that is not in English. The universality and flexibility of Unicode usually outweighs these additional costs.

Caution: Do not use the character set named UTF8 as the database character set unless required for compatibility with Oracle Database clients and servers in version 8.1.7 and earlier,

or unless explicitly requested by your application vendor. Despite having a very similar name, UTF8 is not a proper implementation of the Unicode encoding UTF-8. If the UTF8 character set is used where UTF-8 processing is expected, data loss and security issues may occur. This is especially true for Web related data, such as XML and URL addresses.

Meeting Creation Prerequisites

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

    1. 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.
    2. Sufficient memory must be available to start the Oracle Database instance.
  1. 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 a Database with DBCA

Database Configuration Assistant (DBCA) is the preferred way to create a database, because it is a more automated approach, and your database is ready to use when DBCA completes. DBCA can be launched by the Oracle Universal Installer (OUI), depending upon the type of install that you select. You can also launch DBCA as a standalone tool at any time after Oracle Database installation.

You can run DBCA in interactive mode or noninteractive/silent mode. Interactive mode provides a graphical interface and guided workflow for creating and configuring a database. Noninteractive/silent mode enables you to script database creation. You can run DBCA in noninteractive/silent mode by specifying command-line arguments, a response file, or both.

Creating a Database with Interactive DBCA

See Oracle Database 2 Day DBA for detailed information about creating a database interactively with DBCA.

Creating a Database with Noninteractive/Silent DBCA

See Appendix A of the installation guide for your platform for details on using the noninteractive/silent mode of DBCA.


The following example creates a database by passing command-line arguments to DBCA:

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 – emConfiguration LOCAL

Enter SYSTEM user password:

password

Enter SYS user password:

password

Copying database files

1% complete

3% complete

To ensure completely silent operation, you can redirect stdout to a file. In this case, however, you must supply passwords for the administrative accounts in command-line arguments or the response file.

To view brief help for DBCA command-line arguments, enter the following command:

dbca -help

For more detailed argument information, including defaults, view the response file template found on your distribution media. Appendix A of your platform installation guide provides the name and location of this file.

Creating a Database with the CREATE DATABASE Statement

Using the CREATEDATABASE SQL statement is a more manual approach to creating a database. One advantage of using this statement over using DBCA is that you can create databases from within scripts.

If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running the supplied scripts.

If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features.

The instructions in this section apply to single-instance installations only. See the Oracle Real Application Clusters (Oracle RAC) installation guide for your platform for instructions for creating an Oracle RAC database.

Note: Single-instance does not mean that only one Oracle instance can reside on a single host computer. In fact, multiple Oracle instances (and their associated databases) can run on a single host computer. A single-instance database is a database that is accessed by only one Oracle instance, as opposed to an Oracle RAC database, which is accessed concurrently by multiple Oracle instances on multiple nodes. See Oracle Real Application Clusters Administration and Deployment Guide for more information on Oracle RAC.

Complete the following steps to create a database with the CREATEDATABASE statement. The examples create a database named mynewdb.

Tip: If you are using Oracle Automatic Storage Management (Oracle ASM) to manage your disk storage, you must start the Oracle ASM instance and configure your disk groups before performing these steps. For information about Automatic Storage Management, see Oracle Automatic Storage Management Administrator’s Guide.

Step 1: Specify an Instance Identifier (SID)

Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the ORACLE_SIDenvironment variable. Use this command window for the subsequent steps.

ORACLE_SID is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. Restrictions related to the valid characters in an ORACLE_SID are platform-specific. On some platforms, the SID is case-sensitive.

Note: It is common practice to set the SID to be equal to the database name. The maximum number of characters for the database name is eight. For more information, see the discussion of the DB_NAME initialization parameter in Oracle Database Reference.

The following example for UNIX and Linux operating systems sets the SID for the instance that you will connect to in Step 6: Connect to the Instance :

  1. Bourne, Bash, or Korn shell:

ORACLE_SID=mynewdb export ORACLE_SID


  1. C shell:

setenv ORACLE_SID mynewdb

The following example sets the SID for the Windows operating system:

set ORACLE_SID=mynewdb

Step 2: Ensure That the Required Environment Variables Are Set

Depending on your platform, before you can start SQL*Plus (as required in Step 6: Connect to the Instance ), you may have to set environment variables, or at least verify that they are set properly.

For example, on most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH variable to include the ORACLE_HOME/bin directory. On the UNIX and Linux platforms, you must set these environment variables manually. On the Windows platform, OUI automatically assigns values to ORACLE_HOME and ORACLE_SID in the Windows registry. If you did not create a database upon installation, OUI does not set ORACLE_SID in the registry, and you will have to set the ORACLE_SID environment variable when you create your database later.

Step 3: Choose a Database Administrator Authentication Method

You must be authenticated and granted appropriate system privileges in order to create a database. You can be authenticated as an administrator with the required privileges in the following ways:

  1. With a password file
  2. With operating system authentication In this step, you decide on an authentication method.

To be authenticated with a password file, create the password file as described in “Creating and Maintaining a Password File” on page 1-22. To be authenticated with operating system authentication, ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group. On the UNIX and Linux platforms, for example, this is typically the dba user group. On the Windows platform, the user installing the Oracle software is automatically placed in the required user group.

Step 4: Create the Initialization Parameter File

When an Oracle instance starts, it reads an initialization parameter file. This file can be a text file, which can be created and modified with a text editor, or a binary file, which is created and dynamically modified by the database. 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 create a server parameter file from the text file.

One way to create the text initialization parameter file is to edit the sample presented in “Sample Initialization Parameter File” on page 2-26.

If you create the initialization parameter file manually, ensure that it contains at least the parameters listed in Table 2–2 . All other parameters not listed have default values.

Table 2–2 Recommended Minimum Initialization Parameters

Parameter Name Mandatory Notes
DB_NAME Yes Database identifier. Must correspond to the
value used in the CREATE DATABASE
statement. Maximum 8 characters.
No Strongly recommended. If not provided, then
CONTROL_FILES the database instance creates one control file
in the same location as the
initialization parameter file. Providing this
parameter enables you to multiplex control
files. See “Creating Initial Control Files” on page
11-3 for more information.
MEMORY_TARGET No Sets the total amount of memory used by the
instance and enables automatic memory
management. You can choose other

initialization parameters instead of this one for

more manual control of memory usage. See

“Configuring Memory Manually” on page 6-7.


For convenience, store your initialization parameter file in the Oracle Database default location, using the default file 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 more information about initialization parameters and the initialization parameter file, including the default name and location of the initialization parameter file for your platform, see “About Initialization Parameters and Initialization Parameter Files” on page 2-24.

Step 5: (Windows Only) Create an Instance

On the Windows platform, before you can connect to an instance, you must manually create it if it does not already exist. The ORADIM command creates an Oracle instance by creating a new Windows service.


To create an instance:

  1. Enter the following command at a Windows command prompt: oradim -NEW -SID sid -STARTMODE MANUAL -PFILE pfile

where sid is the desired SID (for example mynewdb) and pfile is the full path to the text initialization parameter file. This command creates the instance but does not start it.

Caution: Do not set the -STARTMODE argument to AUTO at this point, because this causes the new instance to start and attempt to mount the database, which does not exist yet. You can change this parameter to AUTO, if desired, in Step 14.

See the section “Using ORADIM to Administer an Oracle Database Instance” in Oracle Database Platform Guide for Microsoft Windows for more information on the ORADIM command.

Step 6: Connect to the Instance

Start SQL*Plus and connect to your Oracle Database instance with the SYSDBA system privilege.

  1. To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

$ sqlplus /nolog SQL> CONNECT SYS AS SYSDBA

  1. To authenticate with operating system authentication, enter the following commands: $ sqlplus /nolog SQL> CONNECT / AS SYSDBA

SQL*Plus outputs the following message: Connected to an idle instance.

Note: SQL*Plus may output a message similar to the following:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options

If so, the instance is already started. You may have connected to the wrong instance. Exit SQL*Plus with the EXIT command, check that ORACLE_SID is set properly, and repeat this step.

Step 7: Create a Server Parameter File

The server parameter file enables you to change initialization parameters with the ALTERSYSTEM command and persist the changes across a database shutdown and startup. You create the server parameter file from your edited text initialization file.

The following SQL*Plus command reads the text initialization parameter file (PFILE) with the default name from the default location, creates a server parameter file (SPFILE) from the text initialization parameter file, and writes the SPFILE to the default location with the default SPFILE name.

CREATE SPFILE FROM PFILE;

You can also supply the file name and path for both the PFILE and SPFILE if you are not using default names and locations.

Tip: The database must be restarted before the server parameter file takes effect.

Note: Although creating a server parameter file is optional at this point, it is recommended. If you do not create a server parameter file, the instance continues to read the text initialization parameter file whenever it starts.

Important—If you are using Oracle Managed Files and your initialization parameter file does not contain the CONTROL_FILES parameter, then you must create a server parameter file now so the database can save the names and location of the control files that it creates during the CREATEDATABASE statement. See “Specifying Oracle Managed Files at Database Creation” on page 2-19 for more information.

Step 8: 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 initialization parameter file or server parameter file is stored in the default location, you are not required to specify the PFILE clause:

STARTUP NOMOUNT

At this point, the instance memory is allocated and its processes are started. The database itself does not yet exist.

Step 9: Issue the CREATE DATABASE Statement

To create the new database, use the CREATEDATABASE statement.

Example 1


The following statement creates database mynewdb. This database name must agree with the

DB_NAME parameter in the initialization parameter file. This example assumes the following:

  1. The initialization parameter file specifies the number and location of control files with the CONTROL_FILES parameter.
  2. The directory /u01/app/oracle/oradata/mynewdb exists.
  3. The directories /u01/logs/my and /u02/logs/my exist.

CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED

BY system_password LOGFILE GROUP 1

(‘/u01/logs/my/redo01a.log’,’/u02/logs/my/redo01b.log’) SIZE 100M BLOCKSIZE 512,

GROUP 2 (‘/u01/logs/my/redo02a.log’,’/u02/logs/my/redo02b.log’) SIZE 100M BLOCKSIZE 512,

GROUP 3 (‘/u01/logs/my/redo03a.log’,’/u02/logs/my/redo03b.log’) SIZE 100M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE ‘/u01/app/oracle/oradata/mynewdb/system01.dbf’ SIZE 325M REUSE SYSAUX DATAFILE ‘/u01/app/oracle/oradata/mynewdb/sysaux01.dbf’ SIZE 325M REUSE DEFAULT TABLESPACE users

DATAFILE ‘/u01/app/oracle/oradata/mynewdb/users01.dbf’ SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/u01/app/oracle/oradata/mynewdb/temp01.dbf’ SIZE 20M REUSE

UNDO TABLESPACE undotbs DATAFILE ‘/u01/app/oracle/oradata/mynewdb/undotbs01.dbf’ SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

A database is created with the following characteristics:

  1. The database is named mynewdb. Its global database name is mynewdb.us.example.com, where the domain portion (us.example.com) is taken from the initialization parameter file. See “Determining the Global Database Name” on page 2-27.

    1. 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” on page 2-26 and “Specifying Control Files” on page 2-28.
    2. The passwords for user accounts SYS and SYSTEM are set to the values that you specified. Beginning with Release 11g, the passwords are case-sensitive. 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, then 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” on page 2-16.

  1. The new database has three redo log file groups, each with two members, as specified in the LOGFILE clause. MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY define limits for the redo log. See “Choosing the Number of Redo Log Files” on page 12-8. The block size for the redo logs is set to 512 bytes, the same size as physical sectors on disk. The BLOCKSIZE clause is optional if block size is to be the same as physical sector size (the default). Typical sector size and thus typical block size is 512. Permissible values for BLOCKSIZE are 512, 1024, and 4096. For newer disks with a 4K sector size, optionally specify BLOCKSIZE as 4096. See “Planning the Block Size of Redo Log Files” on page 12-7 for more information.
  2. MAXDATAFILES specifies the maximum number of data files 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 data file 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 Language Reference and your operating system–specific Oracle documentation.

  1. The AL32UTF8 character set is used to store data in this database.
  2. The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET, used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.
  3. The SYSTEM tablespace, consisting of the operating system file /u01/app/oracle/oradata/mynewdb/system01.dbf is created as specified by the DATAFILE clause. If a file with that name already exists, then it is overwritten.
  4. The SYSTEM tablespace is created as a locally managed tablespace. See “Creating a Locally Managed SYSTEM Tablespace” on page 2-17.
  5. A SYSAUX tablespace is created, consisting of the operating system file /u01/app/oracle/oradata/mynewdb/sysaux01.dbf as specified in the SYSAUX DATAFILE clause. See “About the SYSAUX Tablespace” on page 2-17.
  6. The DEFAULTTABLESPACE clause creates and names a default permanent tablespace for this database.
  7. The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary tablespace for this database. See “Creating a Default Temporary Tablespace” on page 2-19.
  8. 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. If you omit this parameter, it defaults to AUTO. See “Using Automatic Undo Management: Creating an Undo Tablespace” on page 2-18.

  1. Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified in this CREATEDATABASE 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 13, “Managing Archived Redo Logs” .

Tips:

  1. Ensure that all directories used in the CREATEDATABASE statement exist. The CREATEDATABASE statement does not create directories.
  2. If you are not using Oracle Managed Files, then every tablespace clause must include a DATAFILE or TEMPFILE clause.
  3. If database creation fails, then you can look at the alert log to determine the reason for the failure and to determine corrective actions. See “Viewing the Alert Log” on page 9-20. If you receive an error message that contains a process number, then examine the trace file for that process. Look for the trace file that contains the process number in the trace file name. See “Finding Trace Files” on page 9-21 for more information.
  4. To resubmit the CREATEDATABASE statement after a failure, you must first shut down the instance and delete any files created by the previous CREATEDATABASE statement.

Example 2

This example illustrates creating a database with Oracle Managed Files, which enables you to use a much simpler CREATEDATABASE statement. To use Oracle Managed Files, the initialization parameter DB_CREATE_FILE_DEST must be set. This parameter defines the base directory for the various database files that the database creates and automatically names. The following statement is an example of setting this parameter in the initialization parameter file:

DB_CREATE_FILE_DEST=’/u01/app/oracle/oradata’

With Oracle Managed Files and the following CREATEDATABASE statement, the database creates the SYSTEM and SYSAUX tablespaces, creates the additional tablespaces specified in the statement, and chooses default sizes and properties for all data files, control files, and redo log files. Note that these properties and the other default database properties set by this method may not be suitable for your production environment, so it is recommended that you examine the resulting configuration and modify it if necessary.

CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE undotbs1 DEFAULT TABLESPACE users;

Tip: If your CREATEDATABASE statement fails, and if you did not complete Step 7, then ensure that there is not a pre-existing server parameter file (SPFILE) for this instance that is setting initialization parameters in an unexpected way. For example, an SPFILE contains a setting for the complete path to all control files, and the CREATE DATABASE statement fails if those control files do not exist. Ensure that you shut down and restart the instance (with STARTUPNOMOUNT) after removing an unwanted SPFILE. See “Managing Initialization Parameters Using a Server Parameter File” on page 2-32 for more information.

Step 10: Create Additional Tablespaces

To make the database functional, you must create additional tablespaces for your application data. The following sample script creates some additional tablespaces:

CREATE TABLESPACE apps_tbs LOGGING DATAFILE ‘/u01/app/oracle/oradata/mynewdb/apps01.dbf’ SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

— create a tablespace for indexes, separate from user tablespace (optional)

CREATE TABLESPACE indx_tbs LOGGING DATAFILE ‘/u01/app/oracle/oradata/mynewdb/indx01.dbf’ SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

For information about creating tablespaces, see Chapter 14, “Managing Tablespaces” .

Step 11: Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus.

In SQL*Plus, connect to your Oracle Database instance with the SYSDBA administrative privilege:

@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/rdbms/admin/utlrp.sql

In SQL*Plus, connect to your Oracle Database instance as SYSTEM user:

@?/sqlplus/admin/pupbld.sql

The at-sign (@) is shorthand for the command that runs a SQL*Plus script. The question mark

  1. is a SQL*Plus variable indicating the Oracle home directory. 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.

UTLRP.SQL

Recompiles all PL/SQL modules that are in an invalid state, including packages, procedures, and types.

PUPBLD.SQL

Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

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

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, then 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 13: 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 User’s Guide.

Step 14: (Optional) Enable Automatic Instance Startup

You might want to configure the Oracle instance to start automatically when its host computer restarts. See your operating system documentation for instructions. For example, on Windows, use the following command to configure the database service to start the instance upon computer restart:

ORADIM -EDIT -SID sid -STARTMODE AUTO -SRVCSTART SYSTEM [-SPFILE]

You must use the -SPFILE argument if you want the instance to read an SPFILE upon automatic restart.

Specifying CREATE DATABASE Statement Clauses

When you execute a CREATEDATABASE statement, Oracle Database performs several operations. The actual operations performed depend on the clauses that you specify in the CREATEDATABASE statement and the initialization parameters that you have set. Oracle Database performs at least these operations:

  1. Creates the data files for the database
  2. Creates the control files for the database
  3. Creates the redo log files for the database and establishes the ARCHIVELOG mode
  4. Creates the SYSTEM tablespace
  5. Creates the SYSAUX tablespace
  6. Creates the data dictionary
  7. Sets the character set that stores data in the database
  8. Sets the database time zone
  9. Mounts and opens the database for use

This section discusses several of the clauses of the CREATEDATABASE statement. It expands upon some of the clauses discussed in “Step 9: Issue the CREATE DATABASE Statement” on page 2-11 and introduces additional ones. Many of the CREATE DATABASE clauses discussed here can be used to simplify the creation and management of your database.

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM

The clauses of the CREATEDATABASE statement used for specifying the passwords for users SYS and SYSTEM are:

  1. USERSYSIDENTIFIEDBY password
  2. USERSYSTEMIDENTIFIEDBY password

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

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.

Creating a Locally Managed SYSTEM Tablespace

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATEDATABASE 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:

  1. You specify the DEFAULTTEMPORARYTABLESPACE clause in the CREATEDATABASE statement.
  2. You include the UNDO TABLESPACE clause in the CREATEDATABASE statement.

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.

You can specify only data file attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATEDATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

  1. PERMANENT
  2. READWRITE
  3. EXTENTMANAGMENTLOCAL
  4. SEGMENTSPACEMANAGMENTAUTO

You cannot alter these attributes with an ALTERTABLESPACEstatement, 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. 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” on page 14-25.

If you include a DATAFILE clause for the SYSTEM tablespace, then you must specify the SYSAUX DATAFILE clause as well, or the CREATEDATABASE statement will fail. This requirement does

not exist if the Oracle Managed Files feature is enabled (see “Specifying Oracle Managed Files at Database Creation” on page 2-19).

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

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.

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

Creating a Default Permanent Tablespace

The DEFAULTTABLESPACE clause of the CREATEDATABASE 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.

Creating a Default Temporary Tablespace

The DEFAULT TEMPORARY TABLESPACE clause of the CREATEDATABASE 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 CREATEDATABASE.

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” on page 2-17.

Specifying Oracle Managed Files at Database Creation

You can minimize the number of clauses and parameters that you specify in your CREATEDATABASE 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_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:

  1. Tablespaces and their data files
  2. Temporary tablespaces and their temp files
  3. Control files
  4. Redo log files
  5. Archived redo log files
  6. Flashback logs
  7. Block change tracking files
  8. RMAN backups

The following CREATEDATABASE 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;

  1. The SYSTEM tablespace is created as a locally managed tablespace. Without the EXTENTMANAGEMENTLOCAL clause, the SYSTEM tablespace is created as dictionary managed, which is not recommended.
  2. No DATAFILE clause is specified, so the database creates an Oracle managed data file for the SYSTEM tablespace.
  3. No LOGFILE clauses are included, so the database creates two Oracle managed redo log file groups.

  1. No SYSAUX DATAFILE is included, so the database creates an Oracle managed data file for the SYSAUX tablespace.
  2. 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.
  3. No TEMPFILE subclause is specified for the DEFAULT TEMPORARY TABLESPACEclause, so the database creates an Oracle managed temp file.
  4. If no CONTROL_FILES initialization parameter is specified in the initialization parameter file, then the database also creates an Oracle managed control file.
  5. If you are using a server parameter file (see “Managing Initialization Parameters Using a Server Parameter File” on page 2-32), then the database automatically sets the appropriate initialization parameters.

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 data files 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 CREATEDATABASE statement that let you include support for bigfile tablespaces.

Specifying the Default Tablespace Type

The SET DEFAULT…TABLESPACE clause of the CREATEDATABASE 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 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 data files completely transparent for users. SQL syntax for the ALTERTABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying data files.

The CREATEDATABASE statement shown in “Specifying Oracle Managed Files at Database Creation” on page 2-19 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’;

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 DEFAULTTEMPORARY tablespace during the CREATEDATABASE operation.

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

CREATE DATABASE mynewdb …

BIGFILE UNDO TABLESPACE undotbs

DATAFILE ‘/u01/oracle/oradata/mynewdb/undotbs01.dbf’

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

You can create a smallfile DEFAULTTEMPORARY 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 ‘/u01/oracle/oradata/mynewdb/temp01.dbf’ SIZE 20M REUSE

Specifying the Database Time Zone and Time Zone File

This section contains:

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

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:

  1. Offset from Coordinated Universal Time (UTC)
  2. Transition times for Daylight Saving Time
  3. 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.

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

SELECT * FROM V$TIMEZONE_NAMES;

Specifying the Database Time Zone File

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

Specifying FORCE LOGGING Mode

Some data definition language statements (such as CREATETABLE) 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.

Using the FORCE LOGGING Clause

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

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

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

ALTER DATABASE NO FORCE LOGGING;

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

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


Performance Considerations of FORCE LOGGING Mode

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

    1. How many media failures are likely to happen?
    2. How serious is the damage if unlogged direct writes cannot be recovered?
  1. 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.

Specifying Initialization Parameters

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 covered:

About Initialization Parameters and Initialization Parameter Files

When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAME parameter. All other parameters have default values.

The initialization parameter file can be either a read-only text file, a PFILE, or a read/write binary file.

The binary file is called a server parameter file. 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 Oracle Database. For these reasons, it is recommended that you use a server parameter file. You can create one manually from your edited text initialization file, or automatically by using Database Configuration Assistant (DBCA) to create your database.

Before you manually 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.

Default file names and locations for the text initialization parameter file are shown in the following table:

Platform Default Name Default Location
UNIX initORACLE_SID.ora For ORACLE_HOME/dbs
and example, the
Linux initialization parameter
file for the mynewdb
database is named:
initmynewdb.ora
Windows initORACLE_SID.ora ORACLE_HOME\database

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, then 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, then initialization parameter file changes made by the ALTER SYSTEM statement can persist across shutdown and startup.

Text Initialization Parameter File Format

The text initialization parameter file (PFILE) must contain name/value pairs in one of the following forms:

  1. For parameters that accept only a single value:

parameter_name=value

  1. For parameters that accept one or more values (such as the CONTROL_FILES parameter):

parameter_name=(value[,value] …)

Parameter values of type string must be enclosed in single quotes (‘). Case (upper or lower) in filenames is significant only if case is significant on the host operating system.

For parameters that accept multiple values, to enable you to easily copy and paste name/value pairs from the alert log, you can repeat a parameter on multiple lines, where each line contains a different value.

Translate »