Creating Oracle Databases

This chapter is from the book

Oracle 9i Fundamentals I Exam Cram 2

Learn More Buy

This chapter is from the book

This chapter is from the book 

Oracle 9i Fundamentals I Exam Cram 2

Learn More Buy

Create a Database Manually

Even if your company standardizes on using the GUI tools, such as DBCA, it would be a good idea to create a database manually at least once, not only to help you with this test (although it will help you to pass this portion of the exam), but so that you have an idea of what the DBCA is doing behind the scenes. This knowledge will help you to make more informed decisions in creating your databases, going forward, regardless of how you create them.

Step 1: Decide on Your Instance's System Identifier (SID)

The first step in creating the database is to determine the System Identifier (SID). This is what the environment will be set to when your database starts up and shuts down, and what the instance processes will have in their names as unique identifiers.

Step 2: Create the Initialization Parameter File

In the ORACLE_HOME/dbs directory, a set of generic initialization files are available for you to copy and customize to your situation. There is one for a regular OLTP database, one for a data warehouse database, and one for a combination database. Find the one that most closely matches what you intend your database to be used for, copy it to another name (saving the template for reuse later), and make your specific alterations to the copy.

Name the copied init.ora file init<SID>.ora because this is the default naming convention that the Oracle server looks for when it tries to start up the instance.

Edit this file's parameters so that your database and your instance will run effectively on your system.

Let's address the parameters in the initialization file in greater depth:

• DB_NAME Must be set to a text string of eight characters or less. During database creation, the value assigned to DB_NAME is recorded in the database's datafiles, redo log files, and control files. If, when you attempt to start the database instance, the value of DB_NAME as recorded in the initialization parameter file and the database name in the control files are not the same, the database will not start.

• DB_DOMAIN A text string that specifies the network domain where the database is created. This is often the name of the organization that owns the database. If the new database will be a part of a distributed database system, special attention needs to be paid to this parameter before database creation.

• CONTROL_FILES Is assigned the comma-separated list of control file names to use for the new database. When you execute the CREATE DATABASE statement, the control files listed as values assigned to this parameter are created. If you fail to specify a filename for the CONTROL_FILES parameter, Oracle creates a file with a default operating system dependant filename.

• DB_BLOCK_SIZE Specifies the standard block size of the database. This size is used in the creation of the system tablespace and by default in any other tablespaces created. Take care when determining what this value should be because after the database is created, the value cannot be changed.

• UNDO_MANAGEMENT Determines whether the database starts in automatic undo management mode. A value of AUTO enables automatic undo management; MANUAL enables manual undo management mode. In 9i, AUTO is suggested, but MANUAL is default.

• UNDO_TABLESPACE When the instance starts in automatic undo management mode, it needs to have a tablespace in which to store its undo information. The default is SYS_UNDOTBS and is automatically created if you execute a CREATE DATABASE statement with the UNDO_MANAGEMENT initialization parameter set to AUTO and no UNDO TABLESPACE statement in the CREATE DATABASE statement.

Step 3: Connect to and Start the Instance

Connect to the instance as SYSDBA either using operating system authentication or the password file method of authentication and start up the instance using the STARTUP command.

>sqlplus '/ as sysdba'

Because you do not yet have a database attached to the instance, you need to start up the instance in NOMOUNT state:

SQL> startup nomount;

The instance is now started and ready for the CREATE DATABASE command.

There are several options for starting up the database. The following are the ways that you can start up the instance and the database, and what each means:

• STARTUP NOMOUNT Starts the instance but does not mount the database.

• STARTUP MOUNT Starts the instance and mounts the database but does not open the database.

• STARTUP OPEN Starts the instance and mounts and opens the database.

• STARTUP RESTRICT Starts the instance, mounts and opens the database; however, access is restricted to users with restricted session privileges.

• STARTUP RECOVER Starts the instance but leaves the database closed and begins recovery for whatever failure scenario occurred.

• STARTUP FORCE Forces the instance to shutdown abort and immediately startup open. This option should only be used for instances having problems either starting or stopping.

Step 4: Issue the CREATE DATABASE Statement

To create the database, issue the CREATE DATABASE command. The CREATE DATABASE command creates data files, control files, redo log files, the system tablespace along with the data file associated with it, and a system rollback segment. It creates the tables that underlie the data dictionary, assigns the character set to the database, optionally sets the database time zone, and mounts and opens the database for use.

The general format follows:

CREATE DATABASE [database name]
[CONTROLFILE REUSE]
[LOGFILE [GROUP integer] file specification]
[MAXLOGFILES integer]
[MAXLOGMEMBERS integer]
[MAXLOGHISTORY integer]
[MAXDATAFILES integer]
[MAXINSTANCES integer]
[ARCHIVELOG|NOARCHIVELOG]
[CHARACTER SET charset]
[NATIONAL CHARACTER SET charset]
[DATAFILE filespec [autoextend]]
[DEFAULT TEMPORARY TABLESPACE tablespace filespec]
[UNDO TABLESPACE tablespace DATAFILE filespec]
[SET TIME_ZONE [time_zone_region]];

Database is the name of the database that you are creating. If the name of the database is omitted, the initialization parameter DB_NAME is used. The database name should be the same as the DB_NAME parameter.

Control file reuse specifies that an existing control file identified by the control file parameter in the initialization file should be reused.

Log file group identifies the names of the log files to be used and the group to which they belong.

MAXLOGFILES specifies the maximum number of redo log files that can ever be created in the database.

MAXLOGMEMBERS specifies the maximum number of redo log file members that any given log file group can have in the database.

MAXLOGHISTORY specifies the maximum number of archive redo logs for automatic media recovery.

AUTOEXTEND enables or disables the automatic extension of SYSTEM tablespace's data files in the database.

MAXDATAFILES controls the initial sizing of the data file section of the control file at the time when the CREATE DATABASE or CREATE CONTROLFILE commands are issued. If an attempt is made to add a new file with a number greater than the value set to the MAXDATAFILES parameter, but less than the DB_FILES parameter, it causes the control file to expand automatically so that the data files section can accommodate the new files.

The MAXINSTANCES parameter is optional and is primarily used for Real Application Clusters (RAC) environments. The default, if left unspecified, is 1.

The optional ARCHIVELOG|NOARCHIVELOG statement provides the option for the database's redo log files to automatically initially be archived. This is an optional statement at database creation time. NOARCHIVELOG is the default if ARCHIVELOG is not specified. If, after database creation, you choose to change the archive log mode of the database, you can use the ALTER DATABASE command to change between the two. ARCHIVELOG establishes that the redo logs can be reused, but only after they have been archived. NOARCHIVELOG establishes that the redo logs can be reused without archiving their contents.

CHARACTER SET is the character set that the database uses to store the data.

The NATIONAL CHARACTER SET parameter is optional and is used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.

DATAFILE, when connected directly to the CREATE DATABASE statement, is the file specification used to specify the data files to be used for the system tablespace.

DEFAULT TEMPORARY TABLESPACE instructs the database to assign this tablespace to all users created without having a temporary tablespace specified.

UNDO TABLESPACE creates and names the undo tablespace and the associated data files that should be used to store undo records for the database if you have specified (as Oracle suggests) UNDO_MANAGEMENT=AUTO in the initialization parameter file.

A working example of a CREATE DATABASE script follows:

create database mydb1 controlfile reuse
Logfile 
Group 1 ('/mydatabases/mydb1/log01a.log', 
'/mydatabase02/mydb1/log01b.log') size 50M,
Group 2 ('/mydatabases/mydb1/log02a.log',
'/mydatabase02/mydb1/log02b.log') size 50M,
Group 3 ('/mydatabases/mydb1/log03a.log',
'/mydatabase02/mydb1/log03b.log') size 50M,
Group 4 ('/mydatabases/mydb1/log04a.log',
'/mydatabase02/mydb1/log04b.log') size 50M
Datafile '/mydatabases/mydb1/mydb1_system.dbf' 250M 
AUTOEXTEND ON MAXSIZE 500M
Undo tablespace mydb1_undo1
Datafile '/mydatabases/mydb1/mydb1_undo1.dbf' 50M
Default temporary tablespace mydb1temp tempfile
'/mydatabases/mydb1/mydb1temp1.dbf' size 75M
Extent management local 
Character set US7ASCII
MAXLOGFILES 10
MAXLOGMEMBERS 10
MAXLOGHISTORY 1
MAXDATAFILES 500; 

If you are using Oracle Managed File (OMF) systems and you have specified the appropriate OMF initialization parameters in the parameter file (DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n), the following statement creates the database:

Startup nomount
Create database
Default temporary tablespace mydb1temp;

At this point, your database now contains data files (at least a limited number of them), control files, redo log files, the SYS and SYSTEM users, and all the internal data dictionary tables, but no data dictionary views. You can see the dynamic performance views, such as V$LOGFIEL, V$CONTROLFILE, and V$DATAFILE. If you have not yet changed the default passwords for SYS (change_on_install) and SYSTEM (manager), now is a good time to do it.

Step 5: Run Scripts to Build Data Dictionary Views

Now that you have a database, you need to complete the data dictionary views and procedures. In the ORACLE_HOME/rdbms/admin directory, a pair of scripts need to be run to complete the database's data dictionary creation. CATALOG.sql creates the data dictionary views from which you can gain insight into the contents of the database. CATPROC.sql creates the objects required to use PL/SQL.

Step 6: Run Scripts to Install Additional Options

This step is optional. However, if you need to add any of the optional products, such as Spatial or InterMedia, you can now run the scripts to add these products to your database. You can locate the scripts and the following procedures in the Oracle documentation.

Step 7: Create a Server Parameter File

This step is optional, although highly recommended by Oracle. Your Oracle database was created by starting the instance with a parameter file, or PFILE. Because it is editable, you can migrate, easily, your new database to using a server parameter file. Creation of the server parameter file (or spfile) is accomplished by issuing the following statement:

Create spfile from pfile;

This command looks in the default location (ORACLE_HOME/dbs) for an initialization parameter file (or PFILE) with the default name. If your database is named mydb1, the default name would be initmydb1.ora. The command creates an spfile with the default name in the same default location unless otherwise specified.

Step 8: Back Up the Database

Now that your database is created, take this chance to shut down the database and take a cold backup of the new database.

Step 9: Create Additional Tablespaces

After the backups are finished, you can restart the database and create new tablespaces, tables, and users; add data; and in general, open the new database for business.