Preparing the Operating System - Enterprise DBA Part 1A: Architecture and Administration

Considerations Before Creating a Database

You should be fully privileged on the operating system or should use the password file authentication(see the lesson “Managing an Oracle Instance”).

Before you create the database, make sure that the memory for the SGA, the Oracle executable, and the processes is sufficient. Refer to your operating system installation and administration guides.

Calculate the necessary disk space for the database, including online redo log files, control files, and data files.

Creation Prerequisites

• A privileged account authenticated in one of the following ways:

– By the operating system – Using a password file • Memory to start the instance

...Preparing the Operating System

Planning File Location

Plan how to protect the database, including the online redo log files, control files, data files, and archived redo log files, and provide a backup strategy.

Control Files For the sake of safety, you should create at least two control files on two different disks (see the lesson “Maintaining the Control File”). Because control file copies must always be placed on different disks, they can have identical names, such as control01.ctl on UNIX or control.ora on Windows NT.

Online Redo Log Files The online redo log files of a database should consist of multiplexed groups of online redo log files. A group of log files consists of identical copies, which should be located on different disks (see the lesson “Maintaining Redo Log Files”).

To distinguish between groups and their members, use a name such as log0101.rdo

or log01a.rdo.

Planning Database File Locations

• Keep at least two active copies of a database control file on at least two different devices. • Multiplex the redo log files and put group

members on different disks. • Separate data files whose data:

– Will participate in disk resource contention across different physical disk resources – Have different life spans

Planning File Location (continued)

Data Files Name data files by relating to the contents as the root of the name—for example, data files such as system01.dbf, temp01.dbf, andusers01.dbf on UNIX

and system01.ora and temp01.ora on Windows NT.

Consider the characteristics of the data to be stored before determining the structure appropriate for your database, in order to:

• Minimize fragmentation • Minimize disk contention • Separate objects

To minimize fragmentation of the database, you should separate database objects with different life spans, such as application data and temporary data, into different

tablespaces.

To ensure well-balanced I/O loads, you should separate objects with competing I/O requirements, such as tables and indexes, into different tablespaces.

Note: These subjects are covered in detail in the lessons “Maintaining Tablespaces and

...Preparing the Operating System

Oracle Software Locations

/oracle_base /product /admin /local /release_number /8.1.5 /bin /dbs /orainst /sqlplus ... On UNIX \admin\db_name \admin\v815 \oradata\db_name … \bin \database \oracle_base \oracle_home On Windows NT

Oracle Database Files

/u02/ oradata/ db01/ system01.dbf control01.ctl redo0101.rdo ... db02/ system01.dbf control01.ctl redo0101.rdo ... /u03/ oradata/ db01/ db02/ tools01.dbf control02.ctl redo0102.rdo ... users01.dbf control02.ctl redo0102.rdo ...

...Preparing the Operating System Optimal Flexible Architecture

Another important issue during installation and creation of a database is organizing the file system so that it is easy to administer growth by adding data into an existing database, adding users, creating new databases, and adding hardware and distributing I/O load across sufficiently many drives.

The Optimal Flexible Architecture (OFA) standard, which provides one solution to these issues, was written by an Oracle team responsible for installing, tuning, and upgrading UNIX systems. OFA facilitates configuration of complex Oracle systems with low maintenance. During an Oracle installation, the OFA standard configuration is used automatically.

The OFA structure:

• ORACLE_BASE is the root of the directory tree.

• ORACLE_HOME is the subdirectory that contains the Oracle software and data. Each

version has a separate home.

• Distinguish between product files, which consist of Oracle server software and tools; administrative files such as database creation scripts; initialization scripts; and local software that is used with the Oracle server. In the UNIX example, the directories product, admin, and local satisfy this requirement.

• Make a directory explicitly for storing Oracle server data at the same level of each of the devices, such as ORACLE_HOME/oradata.

• Make a directory beneath the Oracle directory for each of the databases on the system. In the example, the databases are named dba01 and db01.

Note: The OFA directory structure for NT is not identical to that for UNIX because of

the naming conventions and the lack of symbolic links. To circumvent this problem, the creation of hard directories instead of symbolic links such as u01 and u02 is necessary.

For example, instead of creating /u01/oradata/db01/, where u01 represents a mount point on UNIX, you would create a directory DISK_3:\ORADATA\DB01\ on

Windows NT.

Creating a Database

Methods for Creating a Database

Creating a database can be done either by using the Database Configuration Assistant or by creating a SQL script using the CREATE DATABASE command. The Database Configuration assistant is Java-based and can be launched from any platform with a Java engine.

During the installation you are prompted to create a database with the Database Configuration Assistant. This utility can also be used after installation to create or to delete a database.

Technical Note

Before release 8.1, the Database Configuration Assistant was available only on Windows NT. To create a database on UNIX, the installer would prompt you to enter the number and the location of the mount points, the character set, the national character set, passwords for user SYS and SYSTEM, and the UNIX group password to

enable operating system authentications.

Creating a Database

• Created using the Database Configuration Assistant

• Created manually using the CREATE DATABASE command

In document Enterprise DBA Part 1A: Architecture and Administration (Page 136-143)