OMF, init.ora Parameter files, Startup, Shutdown Exercises

G E N E R A L  O V E R V I E W

Scenario/Summary

Oracle provides two primary types of file management; User Managed Files (UMF) and Oracle Managed Files (OMF).  As part of this exercise, you will need to supply some information as to how you would use both of these approaches and discuss some of the advantages of each.
Along with creating a database, there are other additional files that must be created or altered.  One such file is the INIT.ORA file, or initalization file for the database.  The example code in Step 2, although not a complete init file, will need some modifications made to it.
Finally, this exercise will introduce you to the processes and steps of shutting down your database and starting it back up again using the initialization parameter file speific to your database.  You will go through this process many times during your labs, starting in Week three, so it is best to get the hang of it now, while we have some time.
For exercise part three, you only need access to your database instance.  If you have any difficulties connecting your database instance, notify your instructor as soon as possible.
Now you are ready to proceed.

I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Working with Dictionary and Oracle Managed files

As the DBA for your company, you have decided to install a new version of the Oracle Enterprise database to replace the current database version being used. The old database has become a constant headache and seems to be causing an overload on the disk drive's I/O channels.  Further analysis has also shown that two primary large tables are the main points of access.  These tables are CUST_REC and ACCT_RECV tables. You also have a new server with three large-capacity disk drives: F, G, and H.   Briefly, provide the following information:

1. Describe how you plan to rectify the I/O problem with the new Oracle10g database using the UMF method.
2. Describe how you would solve the same problem using the OMF method.

Place and save your answers in a Word document named week2_exercise.doc.

HANDS-ON #2: Configuring the INIT.ORA file

The lines of code below are from an existing init.ora file, and they contain several errors.  After reviewing the code do the following:

1. List the errors you find. 
2. Rewrite the code with corrections.  Format the code as you would expect to see it in the init.ora file with appropriate section titles in comment areas.  You can use the example in the iLab Manual as a guide.

###############
 Cache and I/O
###############
db block size=8192, db domain="detroit.usa"
remote login passwordfIle=EXCLUSIVE control_files=("D:\newlogs\contro199.ctl") maxinstances=2
compatible=101020
###############
 Database Name
###############
db name=prod901.detroit.usa
instance name=trialO2

Place and save your answers in the Word document named week2_exercise.doc that you have started for this week's exercises

HANDS-ON #3: Manual Shutdown and Startup of the Database

For this part of the exercise, you are going to shutdown your individual database instance and then open it up in stages, using the SQL*Plus editor.  The process you will use is outlined in the iLab Manual, under the section titled "Shutting Down and Opening the Database Instance".  You might want to reference the iLab Manual as a guide for this exercise.  The following steps will take you through this process.  Once you have successfully logged into Oracle through the editor, start a spool session to capture your results.  Refer to the iLab Manual for the correct way to set up a SPOOL session in the Citrix environment.  The spool file will be what you submit for a grade for this part of the exercise.  NOTE: This process is Oracle specific and not version specific.  Some of the examples may show a different release of Oracle than the one you are using.  This will not affect the outcome of this exercise.
Let's get started.

1. You will first need to log into the Oracle lab environment through Citrix.  If you have not done this yet, first follow the instructions in the iLab Manual for logging into Citrix.  Once you are into Citrix, select the Oracle folder to access the different tools available.
2. Select and open the Windows Explorer application from the available icons.  Once open then open up the O: drive.  Continue to navigate through the folders until you find the folder titled 'Pfile'.  Inside this folder is a file named INITOL###.ORA (where ### is your instance number).  You want to write down or copy the full path to this file starting with the O: drive letter.  Be sure you include the actual file name in your path.  You will need this in the steps to follow.  Once you hve done this you can close the Windows Explorer.
3. Now select the SQL*Plus icon in the Oracle folder and open the SQL Plus editor.  Once the application starts you should see a login box.
4. Log into your database instance using SYS as the User Name, ORACLE as the password, and DB###.WORLD AS SYSDBA in the Host String box (where ### is your instance number).
5. Once connected to Oracle, you should note down your screen.

Notice that the editor states that you are "Connected to:" with the specific information about the database.  Now at the SQL> prompt start your spool session by typing SPOOL V:\HANDS_ON2_3_LASTNAME.TXT (where LASTNAME is your last name).  Remember that the V: drive is the network equivalent of the C: drive on your local machine.
Next, you need to shut down the database.  Type in the command SHUTDOWN IMMEDIATE and then hit ENTER.  The database will go through MOUNT and NOMOUNT states as it shuts down and will end by stating "Oracle instance shut down".
Now you want to go through the process of opening the database into each of the various modes.  You will be starting up the database and shutting it down several times during this process.  NOTE: You are doing this exercise from a mapped network drive in Citrix so the path to your init.ora file will start with the O: drive at \oracle, as in O:\DB####\oracle\admin\pfile\initdb###.ora.  If in doubt, refer to the iLab Manual for additional help and examples.
The following will outline the steps to follow.
Start the database in NOMOUNT --

1. Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

ii. Start the database in MOUNT --

1. Use STARTUP MOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in MOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

iii. Start the database in OPEN --

1. Use STARTUP PFILE= and then place the complete path to your initol###.ora file at the end.  Remember that the OPEN key word is optional.  Your path should look similar to this: O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

iv. For this final step, you will start the database in NOMOUNT and then ALTER the database to get it to the OPEN status.

1. Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, issue an ALTER DATABASE MOUNT command.  Remember to end the command with a semicolon.
3. Once in MOUNT status (you should get a ?Database altered? reply from the system) then enter the ALTER DATABASE OPEN command.
4. Verify that you database is in OPEN status by querying the STATUS column from the V$INSTANCE data dictionary view.

v. Enter SPOOL OFF to end the spool session.  DO NOT shutdown you database instance.
You can now close the SQL Plus editor and exit out of the Desktop.  Remember to Log Off the Citrix server by selecting the Log Off button.  DO NOT just close the window as this will keep your session open.  Remember that the spool file you created is on the C: drive of your computer