Course Environment

The system runs on centos 7 with Intel Core I3-4130 3.40 CPU and 8GB of system memory. The system will be running RAID 1.

There will be seven directories (/u01 through /u07) to store  your oracle files with a total storage size of 500G. The /home/oracle directory will hold Oracle program files or binaries. All other directories named /u02, /u03, through /u07 will hold database files. This configuration will easily accommodate a seven-disk database layout.

Each group of students will be assigned an Oracle instance in which to work. This document assumes 2 database instances and 2 students per group, but the exact numbers can be determined later. The instances will be named kud01, kud02, kud03,  … kud10. Each of the disk sets /u02 through /u07 will contain a subdirectory for each instance where the actual database files will be stored. Thus, you will create these subdirectories:

/u02/kud01

/u02/kud02

/u02/kud03

/u02/kud04

…

/u02/kud10

/u03/kud01

/u03/kud02

/u03/kud03

/u03/kud04

…

/u03/kud10

etc…

You will access these directories via links in their own directories.

Each student will receive a LINUX account for class work. The naming convention for the usernames will be the name of the instance the student will use, plus a letter of the alphabet. That is, the students in the first group will work on database instance kud01 and be assigned the usernames kud01a, kud01b, and kud01c. The next group will be kud02a, kud02b, and kud02c, and so on.

Here is an example of the structure of a student account. The arrow indicates that the directory or file is actually a link, and shows to what target the link refers.

kud01a/

.oraenv

.profile

.tcshrc

/u01

/u02/kud01

control01.con

system01.dbf

sysauxs01.dbff

user_indx01.dbf

/u03/kud01

control02.con

undotbs01.dbf

/u04/kud01

control03.con

user_data01.dbf

/u05/kud01

user_data02.dbf

indx01.kudf

temp01.kudf

temp_user01.dbf

/u06/kud01

adump/

cdump/

redo0101.log

redo0201.log

redo0301.log

udump/

/u07/kud01

arch/

redo0102.log

redo0202.log

redo0302.log

initkud01.ora  ->  /home/oracle/app/oracle/product/12.2.0/dbhome_1
/dbs/initkud01.ora

scripts/

Here are some notes on the account contents:

• The .oraenv file is a shell script that will set Oracle-related environment variables (e.g., ORACLE_HOME and ORACLE_SID) and display those values. The .profile  (depending on the shell chosen by the student) will run .oraenv at login time.
• The adump, cdump, and trace directories will hold trace files, and correspond to the background_dump_dest, core_dump_dest, parameters in the parameter file, respectively.
• The arch directory is for archiving redo logs, and corresponds to the log_archive_dest parameter.
• The parameter file (initkud##.ora) requires a number of customizations in order to handle this directory structure, as well as for running of multiple instances on this machine (e.g., appropriate sizing of the system global area, or SGA).
• The scripts directory will hold the database creation scripts, as well as any scripts provided by your coaches.
• Be aware that the /u01 through /u07 directories and the initkud##.ora file are shared. Therefore, everyone should take appropriate precautions to not overwrite changes made by other members of the group. Students are free to create subdirectories within their own accounts for class work but should not modify or create directories in the /u01 through /u07 directories.

The Basics You Need

You should already know the basics of relational database technology. Specifically, you should know how to construct and ERD, normalize the design, create objects, insert and load data, and retrieve data.

Requirements for the Course

Everyone in the class will participate as a team member.

The class will be divided into teams. Each member of the team will have DBA privileges within one instance. Team responsibilities include:

• Install Oracle instance. The instances will be named kud01, kud02, kud03, kud04, … kud10. Each team will install one of the instances.
• Set up accounts. After the installation, the KUDBAs will set up their KUDBA accounts. When the accounts have been setup, KUDBAs will turn over sys and system accounts to your coaches. The KUDBA accounts should be setup with a script, not interactively.
• Design and create objects. Each instance should have ten tables that are owned by the KUDBA team. Create an ERD with the 10 tables. One table should be sized for 50,000 rows. The other nine tables should be sized for 1,000 rows each. Each team member is responsible for sizing and creating some tables.
• Use scripts to create all objects. Create tablespaces on disks.
• Create sized tables in the tablespaces. Place objects on the specific disks. The largest table should be partitioned into two portions and stored on two different disks.
• Populate tables with test data. Create and load test data. All team members are responsible for loading data into their tables. Remember to use compute statistics after the loads.
• Backup the database. Use the export RMAN and flashback commands to backup the fully-loaded database.
• Create user accounts for end users. Create user accounts for a different team for your database. Those users should be able to select, insert, delete, and update all of your tables. They should not, however, have KUDBA privileges to objects in your instance.

Everyone in the class will participate as an individual.

Individual responsibilities include:

• Perform these maintenance tasks:

• Collect statistics on how your instance is running. Find scripts on the internet to help you.
• Manually add and delete columns from one table. Do not use the drop column or add column commands.
• Monitor space utilization.
• Monitor the alert log.
• Test the recovery capability. To do so, with a test table, export and import an object or data.
• Startup and shutdown the instance after some changes.

• Design and run a benchmark test. Design a workload that will stress your database. Set up scripts or code that can run continuously.
• Configure Oracle and tune the configuration.

Each KUDBA will be an end user.

A KUDBA as an end user permissions and abilities include:

• As an end user, request catalog information that will allow you to reverse engineer an ERD of their database. Draw an ERD of their database.
• Design a workload that will run against the other team’s database. Do not tell them what the workload will be. At intervals unknown to the KUDBA team, kick off the workload.