Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
This section takes you through the planning stage and the actual creation of the database.
Database creation prepares several operating system files to work together as an Oracle Database. You need only create a database once, regardless of how many datafiles it has or how many instances access it. You can create a database to erase information in an existing database and create a new database with the same name and physical structure.
The following topics can help prepare you for database creation.
Prepare to create the database by research and careful planning. Table 2-1 lists some recommended actions:
Table 2-1 Database Planning Tasks
Action | Additional Information |
---|---|
Plan the database tables and indexes and estimate the amount of space they will require. |
Part II, "Oracle Database Structure and Storage" |
Plan the layout of the underlying operating system files your database will comprise. Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. You can distribute I/O in several ways when you install Oracle software and create your database. For example, you can place redo log files on separate disks or use striping. You can situate datafiles to reduce contention. And you can control data density (number of rows to a data block). |
Oracle Database Performance Tuning Guide Your Oracle operating system specific documentation |
Consider using Oracle-managed files and Automatic Storage Management to create and manage the operating system files that make up your database storage. |
Chapter 15, "Using Oracle-Managed Files" Oracle Database Storage Administrator's Guide |
Select the global database name, which is the name and location of the database within the network structure. Create the global database name by setting both the |
"Determining the Global Database Name" |
Familiarize yourself with the initialization parameters contained in the initialization parameter file. Become familiar with the concept and operation of a server parameter file. A server parameter file lets you store and manage your initialization parameters persistently in a server-side disk file. |
"Understanding Initialization Parameters" "What Is a Server Parameter File?" |
Select the database character set. All character data, including data in the data dictionary, is stored in the database character set. You must specify the database character set when you create the database. If clients using different character sets will access the database, then choose a superset that includes all client character sets. Otherwise, character conversions may be necessary at the cost of increased overhead and potential data loss. You can also specify an alternate character set. Caution: Do not confuse Oracle Database database character set Using database character set |
Oracle Database Globalization Support Guide |
Consider what time zones your database must support. Oracle Database uses one of two time zone files as the source of valid time zones. The default time zone file is |
"Specifying the Database Time Zone File" |
Select the standard database block size. This is specified at database creation by the The |
"Specifying Database Block Sizes" |
Determine the appropriate initial sizing for the |
"Creating the SYSAUX Tablespace" |
Plan to use a default tablespace for non- |
"Creating a Default Permanent Tablespace" |
Plan to use an undo tablespace to manage your undo data. |
|
Develop a backup and recovery strategy to protect the database from failure. It is important to protect the control file by multiplexing, to choose the appropriate backup mode, and to manage the online and archived redo logs. |
Chapter 10, "Managing the Redo Log" Chapter 11, "Managing Archived Redo Logs" Chapter 9, "Managing Control Files" Oracle Database Backup and Recovery User's Guide |
Familiarize yourself with the principles and options of starting up and shutting down an instance and mounting and opening a database. |
Chapter 3, "Starting Up and Shutting Down" |
Before you can create a new database, the following prerequisites must be met:
The desired Oracle software must be installed. This includes setting various environment variables unique to your operating system and establishing the directory structure for software and database files.
You must have the operating system privileges associated with a fully operational database administrator. You must be specially authenticated by your operating system or through a password file, allowing you to start up and shut down an instance before the database is created or opened. This authentication is discussed in "Database Administrator Authentication".
Sufficient memory must be available to start the Oracle Database instance.
Sufficient disk storage space must be available for the planned database on the computer that runs Oracle Database.
All of these are discussed in the Oracle Database Installation Guide specific to your operating system. If you use the Oracle Universal Installer, it will guide you through your installation and provide help in setting environment variables and establishing directory structure and authorizations.
This section presents the steps involved when you create a database manually. These steps should be followed in the order presented. The prerequisites described in the preceding section must already have been completed. That is, you have established the environment for creating your Oracle Database, including most operating system dependent environmental variables, as part of the Oracle software installation process.
Step 1: Decide on Your Instance Identifier (SID)
Step 2: Establish the Database Administrator Authentication Method
Step 3: Create the Initialization Parameter File
Step 4: Connect to the Instance
Step 5: Create a Server Parameter File (Recommended)
Step 7: Issue the CREATE DATABASE Statement
Step 8: Create Additional Tablespaces
Step 9: Run Scripts to Build Data Dictionary Views
Step 10: Run Scripts to Install Additional Options (Optional)
Step 11: Back Up the Database.
The examples shown in these steps create an example database mynewdb
.
Notes:
The steps in this section contain cross-references to other parts of this book and to other books. These cross-references take you to material that will help you to learn about and understand the initialization parameters and database structures with which you are not yet familiar.
If you are using Oracle Automatic Storage Management to manage your disk storage, you must start the ASM instance and configure your disk groups before performing the following steps. For information about Automatic Storage Management, see Oracle Database Storage Administrator's Guide.
An instance is made up of the system global area (SGA) and the background processes of an Oracle Database. Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID
environment variable accordingly. This identifier is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on your system.
The following example for UNIX operating systems sets the SID for the instance that you will connect to in Step 4: Connect to the Instance:
% setenv ORACLE_SID mynewdb
You must be authenticated and granted appropriate system privileges in order to create a database. You can use the password file or operating system authentication method. Database administrator authentication and authorization is discussed in the following sections of this book:
When an Oracle instance starts, it reads an initialization parameter file. This file can be a read-only text file, which must be modified with a text editor, or a read/write binary file, which can be modified dynamically by the database (for tuning) or with SQL commands that you submit. The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you can optionally create a server parameter file from the text file.
One way to create the text initialization parameter file is to edit a copy of the sample initialization parameter file that Oracle provides on the distribution media, or the sample presented in this book.
Note:
On Unix operating systems, the Oracle Universal Installer installs a sample text initialization parameter file in the following location:$ORACLE_HOME/dbs/init.ora
For convenience, store your initialization parameter file in the Oracle Database default location, using the default name. Then when you start your database, it will not be necessary to specify the PFILE
clause of the STARTUP
command, because Oracle Database automatically looks in the default location for the initialization parameter file.
For name, location, and sample content for the initialization parameter file, and for a discussion of how to set initialization parameters, see "Understanding Initialization Parameters".
Start SQL*Plus and connect to your Oracle Database instance AS SYSDBA
.
$ SQLPLUS /nolog
CONNECT SYS/password AS SYSDBA
Oracle recommends that you create a server parameter file. The server parameter file enables you to change initialization parameters with database commands and persist the changes across a shutdown and startup. You create the server parameter file from your edited text initialization file. For more information, see "Managing Initialization Parameters Using a Server Parameter File".
The following script creates a server parameter file from the text initialization parameter file and writes it to the default location. The script can be executed before or after instance startup, but after you connect as SYSDBA
. The database must be restarted before the server parameter file takes effect.
-- create the server parameter file CREATE SPFILE='/u01/oracle/dbs/spfilemynewdb.ora' FROM PFILE='/u01/oracle/admin/initmynewdb/scripts/init.ora'; SHUTDOWN -- the next startup will use the server parameter file EXIT
Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database. Use the STARTUP
command with the NOMOUNT
clause. In this example, because the server parameter file is stored in the default location, you are not required to specify the PFILE
clause:
STARTUP NOMOUNT
At this point, the SGA is created and background processes are started in preparation for the creation of a new database. The database itself does not yet exist.
See Also:
"Managing Initialization Parameters Using a Server Parameter File"
Chapter 3, "Starting Up and Shutting Down", to learn how to use the STARTUP
command
To create the new database, use the CREATE
DATABASE
statement. The following statement creates database mynewdb
:
CREATE DATABASE mynewdb USER SYS IDENTIFIED BY pz6r58 USER SYSTEM IDENTIFIED BY y1tz5p LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M, GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M, GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE tbs_1 DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
A database is created with the following characteristics:
The database is named mynewdb
. Its global database name is mynewdb.us.oracle.com
. See "DB_NAME Initialization Parameter" and "DB_DOMAIN Initialization Parameter".
Three control files are created as specified by the CONTROL_FILES
initialization parameter, which was set before database creation in the initialization parameter file. See "Sample Initialization Parameter File" and "Specifying Control Files".
The password for user SYS
is pz6r58
and the password for SYSTEM
is y1tz5p
. The two clauses that specify the passwords for SYS
and SYSTEM
are not mandatory in this release of Oracle Database. However, if you specify either clause, you must specify both clauses. For further information about the use of these clauses, see "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM".
The new database has three redo log files as specified in the LOGFILE
clause. MAXLOGFILES
, MAXLOGMEMBERS
, and MAXLOGHISTORY
define limits for the redo log. See Chapter 10, "Managing the Redo Log".
MAXDATAFILES
specifies the maximum number of datafiles that can be open in the database. This number affects the initial sizing of the control file.
Note:
You can set several limits during database creation. Some of these limits are limited by and affected by operating system limits. For example, if you setMAXDATAFILES
, Oracle Database allocates enough space in the control file to store MAXDATAFILES
filenames, even if the database has only one datafile initially. However, because the maximum control file size is limited and operating system dependent, you might not be able to set all CREATE DATABASE
parameters at their theoretical maximums.
For more information about setting limits during database creation, see the Oracle Database SQL Language Reference and your operating system specific Oracle documentation.
MAXINSTANCES
specifies that only one instance can have this database mounted and open.
The US7ASCII
character set is used to store data in this database.
The AL16UTF16
character set is specified as the NATIONAL CHARACTER SET,
used to store data in columns specifically defined as NCHAR
, NCLOB
, or NVARCHAR2
.
The SYSTEM
tablespace, consisting of the operating system file /u01/oracle/oradata/mynewdb/system01.dbf
is created as specified by the DATAFILE
clause. If a file with that name already exists, it is overwritten.
The SYSTEM
tablespace is a locally managed tablespace. See "Creating a Locally Managed SYSTEM Tablespace".
A SYSAUX
tablespace is created, consisting of the operating system file /u01/oracle/oradata/mynewdb/sysaux01.dbf
as specified in the SYSAUX DATAFILE
clause. See "Creating the SYSAUX Tablespace".
The DEFAULT
TABLESPACE
clause creates and names a default permanent tablespace for this database.
The DEFAULT TEMPORARY TABLESPACE
clause creates and names a default temporary tablespace for this database. See "Creating a Default Temporary Tablespace".
The UNDO TABLESPACE
clause creates and names an undo tablespace that is used to store undo data for this database if you have specified UNDO_MANAGEMENT=AUTO
in the initialization parameter file. See "Using Automatic Undo Management: Creating an Undo Tablespace".
Redo log files will not initially be archived, because the ARCHIVELOG
clause is not specified in this CREATE
DATABASE
statement. This is customary during database creation. You can later use an ALTER DATABASE
statement to switch to ARCHIVELOG
mode. The initialization parameters in the initialization parameter file for mynewdb
relating to archiving are LOG_ARCHIVE_DEST_1
and LOG_ARCHIVE_FORMAT
. See Chapter 11, "Managing Archived Redo Logs".
See Also:
Oracle Database SQL Language Reference for more information about specifying the clauses and parameter values for the CREATE DATABASE
statement
To make the database functional, you need to create additional files and tablespaces for users. The following sample script creates some additional tablespaces:
CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
DATAFILE '/u01/oracle/oradata/mynewdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/u01/oracle/oradata/mynewdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
For information about creating tablespaces, see Chapter 12, "Managing Tablespaces".
Run the scripts necessary to build views, synonyms, and PL/SQL packages:
CONNECT SYS/password AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql
EXIT
The following table contains descriptions of the scripts:
Script | Description |
---|---|
CATALOG.SQL |
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms. |
CATPROC.SQL |
Runs all scripts required for or used with PL/SQL. |
You may want to run other scripts. The scripts that you run are determined by the features and options you choose to use or install. Many of the scripts available to you are described in the Oracle Database Reference.
If you plan to install other Oracle products to work with this database, see the installation instructions for those products. Some products require you to create additional data dictionary tables. Usually, command files are provided to create and load these tables into the database data dictionary.
See your Oracle documentation for the specific products that you plan to install for installation and administration instructions.
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.