DB Creation   «Prev  Next»

Lesson 10

Database Preparation and Creation Conclusion

Oracle has literally dozens of initialization parameters that affect how your database operates. You should strive to become familiar with the parameters that are available, to enable yourself to recognize situations where you can leverage the functionality that they provide. I have two suggestions that will help you learn about the various initialization parameters. The first is this: Find one or two initialization files from some production databases, read through them, and look up each parameter setting in the Oracle Reference Manual. Make sure that you understand why each parameter is being used. If necessary, ask a senior DBA to explain things to you. My second suggestion is to sit down with the Oracle Reference Manual, and read through it. Briefly skim each parameter description. Even if you do not understand everything, this plants the material in your brain. You will be surprised at what you will recall later when you need it.

Creating a Database

This section explains how to create an Oracle database manually with the SQL*Plus CREATE DATABASE statement. These are the steps required to create a database:
  1. Set the OS variables.
  2. Configure the initialization file.
  3. Create the required directories.
  4. Create the database.
  5. Create a data dictionary.
Each of these steps is covered in the following sections.
Step 1. Set the OS Variables As mentioned previously, before you run SQL*Plus (or any other Oracle utility), you must set several OS variables. You can either manually set these variables or use a combination of files and scripts to set the variables. Here is an example of setting these variables manually: 
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
$ export ORACLE_SID=o12c
$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
$ export PATH=$ORACLE_HOME/bin:$PATH

Step 2: Configure the Initialization File
Oracle requires that you have an initialization file in place before you attempt to start the instance. The initialization file is used to configure features such as memory and to control file locations. You can use two types of initialization files:
  1. Server parameter binary file (spfile)
  2. init.ora text file

Oracle recommends that you use an spfile for reasons such as these:
  1. You can modify the contents of the spfile with the SQL ALTER SYSTEM statement.
  2. You can use remote-client SQL sessions to start the database without requiring a local (client) initialization file.

These are good reasons to use an spfile. However, some shops still use the traditional init.ora file. The init.ora file also has advantages:
  1. You can directly edit it with an OS text editor.
  2. You can place comments in it that detail a history of modifications.

When I first create a database, I find it easier to use an init.ora file. This file can be easily converted later to an spfile if required (via the CREATE SPFILE FROM PFILE statement). In this example my database name is o12c, so I place the following contents in a file named inito12c.ora and put the file in the ORACLE_HOME/dbs directory:
Table 6-10 lists best practices to consider when configuring an Oracle initialization file.
Initialization File Best Practices
Table 6-10: Initialization File Best Practices

You are almost ready to create the COIN database. Now that you have worked through this module, you should have an initCOIN.ora file that looks something like this:
View the code below to see how the initCOIN.ora file should look. 
control_files = (c:\oracle\oradata\COIN\control01.ctl,
                       c:\oracle\oradata\COIN\control02.ctl,
                       c:\oracle\oradata\COIN\control03.ctl)
background_dump_dest    = c:\oracle\admin\COIN\bdump
user_dump_dest          = c:\oracle\admin\COIN\udump
db_block_size = 4096
db_name = COIN
db_domain = courses.DistributedNetworks.com
db_files = 20
compatible = 8.1.5
global_names = TRUE
processes = 30
optimizer_mode = choose

Glossary

This module introduced you to the following terms:
  1. cache hit ratio:A ratio telling you how often Oracle goes to read a block, and finds it already in memory as opposed to having to read the block from disk.
  2. directive: With regards to an Oracle initialization parameter file, a directive is an entry in the file that has meaning to the program reading the file, rather than to the Oracle instance being started. For example, the IFILE directive is processed by Server Manager (or SQL*Plus), and is not passed on to the Oracle instance.
  3. GETHIT Ratio: The GETHIT ration tells you relatively how often an SQL statement is found already in memory, in the shared pool, and already parsed.
  4. GETMISS Ratio:Tells you relatively how often Oracle has to go to the disk for data dictionary information as opposed to finding it already in memory, in the data dictionary cache.
  5. initialization parameter:An entry in a database's initialization file, or parameter file, that controls some aspect of the way the database instance operates. For example, the SHARED_POOL parameter controls the size of the shared pool within the SGA.
  6. mount point:On a UNIX system, a mount point is the place in the directory structure where a disk is inserted. A mount point is basically a directory path, such that the resulting destination is really the top-level directory of an entirely new disk.
  7. Optimal Flexible Architecture (OFA) The Optimal Flexible Archictecture is a set of guidelines from Oracle that provides suggestions for directory structures, file placement, file naming conventions, and standard tablespaces that each database should have.
  8. redo log switch:A redo log switch refers to the process of closing one redo log file and opening the next file in the seqeuence.
  9. throughput:When used in reference to a disk drive, throughput is a measure of how much data the drive can physically pump back to the application in a given period of time. A typical SCSI drive, for example, might have a sustained throughput of 17 megabytes/second. If all your database files were on this one drive, your database throughput would be quite limited.
The next step is to write and execute a CREATE DATABASE statement. The next module will lead you through that process. Once you have done that, and taken care of a few housekeeping tasks, you will have a working database. Before you continue though, here is one final quiz for you to take on this module.

Names, Domains, Parameter File Settings - Quiz

Click the Quiz link below to test what you have learned.
Names, Domains, Parameter File Settings - Quiz

SEMrush Software