CIS 421b: Database Administration I

Chapter 3: Creating an Oracle Instance


This chapter introduces major components of Oracle9i. The objectives important to this chapter are:

  1. Creating a database
  2. Requirements for creating a database
  3. Initial configuration of a database
  4. Creating, starting, and stopping an instance

The text offers a list of steps for creating an Oracle database from scratch. Each step has several parts:

  1. Install software
  2. Create a privileged user. The text states that this is only needed in UNIX. We have found that the equivalent granting of rights is needed in our lab.
  3. Confirm adequate memory and hard drive storage. (If this is a problem, you would save you a lot of time by doing it first.)
  4. Choose file management method, and the initial configuration settings.
  5. Choose the type of database to make.
  6. Create a database, either manually, or through Database Configuration Assistant
  7. Test the database

Requirements to perform a database creation:

  • Oracle software must be installed
  • Your user ID must have sufficient rights to use the software and create the database
  • Memory and disk space requirements must be met. Some minimum specifications are given for a Windows 2000 system:
    • 128 MB of RAM (This is, of course, a bare bones estimate. It will not be nearly enough to actually use the system.)
    • 200 MB of virtual memory (swap file space), which can grow to a maximum of 400 MB.
    • 400 MB of space for temporary files
    • Adequate storage space on the drive used for ORACLE_HOME. At least 4.5 GB if using FAT storage; at least 2.75 GB if using NTFS.
    • Another 140 MB of space on the system drive

    The references to various drives comes from the possibility that you are running the system on a network, instead of on one machine as in our lab.

The initial settings for your database are broken into three groups: DBA authentication, file management, and initial parameters.

Database Administrator (DBA) Authentication refers to two methods that can prove to the system that a user has the rights needed to manage the database. The text refers to two roles that have theses rights: SYSDBA and SYSOPER. Both roles are defined on the system when it is installed. Both may be used to start and stop the database, back it up, and modify components. The SYSDBA role also has the rights needed to create an instance, recover an instance, and create or maintain tables and data. Typically, the SYSDBA role is granted the ADMIN option, giving it the right to grant any of its rights to other users.

The methods of actually authenticating to the system are Operating System (OS) authentication and password authentication.

You can set up groups in your operating system that are granted rights to the database, which allows your users to log on to the operating system of the server, instead of logging on to the database. Separate groups are used to represent the SYSDBA role (OSDBA group in UNIX, ORA_DBA group in Windows) and the SYSOPER role (OSOPER group, must be manually set up in UNIX or Windows).

The text recommends using a password file when your admins need to access the system over web links or other non-secure channels. A password file of this sort is just for the admin users. It will be named pwdora92.ora if you are using Oracle 9i revision 2. To use this option, set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE and create a password file using the orapwd utility. The syntax to create this file from the command line requires you to know the path to where it should be stored (in your database directory), the password you want to use for your admin users, and the maximum number of admin users you will allow to log on simultaneously with this file. Use the command like this:

orapwd file=drive_letter:path_to_file\filename password=admin_password entries=number_of_admins

The text gives some general advice and some specific choices about file management. Generally, it recommends that you multiplex control files and redo log files, because of their importance to the functions of your system. Multiplexing means to store multiple copies of these files on separate hard drives, and to update all changes to those files in real time.

Without coming out and saying so, the text informs us that the DBA will do a lot of work if he decides to implement User Managed Files, as opposed to Oracle Managed Files (the default). One reason is that Oracle will require that space be allocated for the files, and that space must change as data files outgrow their current space allowances. Another reason is that the Oracle Managed files option will allow Oracle to create new files, including related control files, on the fly as needed. The recommendation is to use Oracle Managed files.

Initialization parameters are settings that affect how the system runs. They are stored in a file whose name starts with init, ends with .ora, and has your system identifier in between. There are over 200 initialization parameters. If you don't set one in your initialization file, it is set to its default value by Oracle. The parameters are classified as:

  • Static (permanent) - must be set prior to creating the database, cannot be changed afterward
  • Static (adjustable) - can be changed, but the system must be restarted to use them
  • Dynamic (System only) - can be changed while the system runs, but those settings go away when it is restarted
  • Dynamic (System and Session) - can be changed for the current session, or for all sessions on the database
  • Derived - set by the system, based on the value of other parameters

Two parameters are listed that are Static (permanent):

  • CPU_COUNT - This is just the number of CPUs in your server. Although it is listed here as unchangeable, the text states that Oracle will change it if the number of processors in your computer changes.
  • DB_BLOCK_SIZE - This may be the size of the data blocks on your hard drive. It is the size that Oracle will use for data blocks. The text cautions us not to change this value after it is set.

Three other parameters are listed as examples whose values should not be changed after setting:

  • DB_DOMAIN - This is similar to a URL. It specifies the name of your database in your IP domain.
  • DB_NAME - The name of the database; limited to 8 characters.
  • COMPATIBLE - This will be the release number of Oracle that you want your database to be compatible with. You might use the current release number, or an older one.

When creating a database, you have two choices: the CREATE DATABASE command, or the Database Configuration Assistant. As stated in the text, the CREATE DATABASE command is more flexible, but the Database Configuration Assistant will provide an interface that prevents leaving out any steps. On the other hand, when you use the manual method you can allow larger tablespaces and larger files than the choice offered by Database Configuration Assistant. A disadvantage to this method on a Windows system is you have to modify the Windows Registry by hand to make the database available.

After creating a database, it will be necessary to create a Net Service that will use it. This includes creating a Database Service Name, and changing port settings if necessary. After making this connection, you can use Instance Manager to start, stop, and restart the database.

You can also perform a manual start or stop for a database. Neither procedure is very intuitive.

Stopping a database manually:

  1. Open a command window in Windows, or get to a $ prompt in UNIX.
  2. Start SQL*Plus with the command sqlplus /nolog
  3. Log on as the SYS user, with SYSDBA rights. The command is connect sys/password as sysdba
  4. Stop the database with the command shutdown immediate
    The shutdown command can be modified by the words immediate, abort, or normal. Normal waits for users to log off, immediate waites for transactions to complete and for users to log off, and abort logs off the users and waits for nothing.

Starting a database manually:

  1. Open a command window in Windows, or get to a $ prompt in UNIX.
  2. Start SQL*Plus with the command sqlplus /nolog
  3. Log on as the SYS user, with SYSDBA rights. The command is connect sys/password as sysdba
  4. Start a new instance of the database, by using the startup command along with the name of the parameter file for that database.
    startup pfile=drive_letter:path_to_.ora_file\parameter_file