Oracle Database Creation Exercises

L A B   O V E R V I E W

Scenario/Summary

Although Oracle provides a very nice graphical interface tool call "Enterprise Manager" to aid the DBA in working with the database, it is not always the tool of choice.  Many DBAs find it preferable to use command-line execution of scripts and procedures to do administrative work.  One such case is in the creation of a new database within the management system.
Although creation of new database requires the editing of several support files for the database (the listener.ora and tnsnames.ora files in particular), the CREATE DATABASE script is the essential file that would need to be run to accomplish this process.  In this lab you will be responsible for writing a CREATE DATABASE script that would create a new database named LABII, based on a specific set of requirements that will be listed later.  The basic configuration for the server the database instance will use is:

1. Disk Drives: There are three disk drives you will be using – C:\ - E:\ - F:\
2. OFA directory paths:  The OFA path for each drive that you will use to place the described files in is:

 \ORACLE\ORADATA\ORACLE

VERY IMPORTANT – DO NOT ATTEMPT TO RUN YOUR SCRIPT FILE OR TEST IT!!  Doing so could cause unforeseen problems and that is not the objective of the lab.  You will be furnished with a solution later that you can compare your example against.

L A B   S T E P S

Step 1: The CREATE DATABASE SQL

You are being asked to create the CREATE DATABASE script for a new database instance.  The name of the new database should be LABII.  Create a script file using Notepad that would create this database containing:

• The following parameters need to be defined and settings assigned: 
  
  
  
  • maxlogfiles to allow up to 5 groups
  • maxlogmembers to allow up to 4
  • maxdatafiles to allow up to 100
  • maxinstances to allow only 2
  A system datafile should be based on the following guidelines:
  
  
  
  • Named SYSTEM01.DBF Located on the C: with the directory path noted above.  Have a storage clause set to AUTOEXTEND ON NEXT 10240K and should have a MAXSIZE of UNLIMITED. 
  • Have a file size of 100M.
  
  
  
  
• A system auxilary datafile should be based on the following guidelines:
  
  
  
  • Named SYSAUX01.DBF Located on the C: with the directory path noted above.  Have a storage clause set to AUTOEXTEND ON NEXT 10240K and should have a MAXSIZE of UNLIMITED. 
  • Have a file size of 100M.
  
  
  
  
• Two logfile groups should be created based on the following guidelines:
  
  
  
  • Each group should have two 4M logfiles.  Log group one will have file REDO01A.LOG located on the C: drive and REDO01B.LOG located on the E: drive.  Each drive should use the directory path noted above in the opening scenario/summary section.
  • Log group two will have file REDO02A.LOG located on the E: drive and  REDO02B.LOG located on the F: drive.  Each drive should use the directory path noted above in the opening scenario/summary section.
  
  
  
  

You can include settings for character sets if you wish, however, since this database resides in an already defined DBMS, the default character sets defined with Oracle was installed would be used.Be sure to include a semicolon at the end of the script to indicate the end of the script.
REMEMBER-–You are not required, nor should you try, to execute this script.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle