CTP 245 - Database Administration 1

3 credit hours - Two hours of lecture and two hours of laboratory weekly; one term.
Formerly CSI 224 - Database Administration 1

Learn how to install and maintain an Oracle database. This course includes the architecture of a Database Management System using Oracle as the example. Investigate issues related to database security, user management, performance monitoring, and data concurrency with structured hands-on exercises. This course is the third in the series of the Oracle administration courses. This course addresses the core material of the Oracle Certified Associate exam objective.

Prerequisite(s): CTP 145 with a grade of C or better or permission of the CTP department chair.

Note: Typically offered OL; fall and spring terms.

Course Outcomes:

Install the Oracle database software
- Distinguish between installation methods and delivery platforms
- Distinguish between installation methods and delivery platforms
- Install software using the OUI
- Create a database using the DBCA
- Generate database creation SCRIPTS with the DBCA
Manage an Oracle DB Instance
- Start and Stop the database components
- Access a database with SQL*Plus
- Access a database with Interactive-SQL*Plus (iSQL)
- Modify database initialization parameters
- View the Alert Log
- Use the Data Dictionary to view database elements
Manage Database Storage Structures
- Describe the record layout of the table storage (blocks)
- Define the purpose of tablespaces
- Define the purpose of tablespaces
- Create a tablespace
- Obtain information about an existing tablespace
- Describe the functionality of the ASM (Automatic Storage Manager)
Administer User Security
- Create database user accounts
- Assign default storage areas to users
- Grant privileges
- Revoke privileges
- Manage roles
- Manage profiles
- Implement standard password security
Manage Schema Objects
- Implement standard password security
- Create tables
- Define constraints
- View the columns of a table
- View the contents of a table
- Explain when indexes should be placed on tables
- Create indexes on tables
- Choose when to use temporary tables
- Manage Data Concurrency
- Manage Data Concurrency
Implement Database security
- Describe the responsibilities of a DBA concerning securing the database
- Apply the principle of “least privilege
- Enable auditing on a database
- Specify audit options
- Maintain an audit trail on a database
Configure the Network Environment
- Use the Enterprise Manager for configuring a network
- Create additional network LISTENER instances
- Configure connect-time failover
- Test connectivity to a database over a network
- Analyze statistics to determine if proactive maintenance is necessary
- Set ALERT thresholds
Manage the performance of a database
- Use the Enterprise Manager to monitor performance
- Use the SQL Tuning Advisor to determine if there are performance problems
- Use the Memory Advisor to size memory buffers
- Troubleshoot invalid or unusable objects
Perform the Backup of a data base
- Identify the types of failure that might occur in a database
- Identify the roles of checkpoints, redo log files, and archived log files
- Configure ARCHIVELOG modes
- Create a consistent database backup
- Backup a database without shutting it down
- Create an incremental backup
Perform the Recovery of a data base
- Perform recovery from the loss of a control file
- Perform recovery from the loss of a redo log file
- Perform recovery from the loss of a data file
- Restore the contents of a table to a specific point in time with the FLASHBACK Table
- View the transaction history of a row of user data
Move data objects
- Describe alternative ways to move data
- Create directory objects
- Use directory objects
- Use SQL*Loader to import data from a non-Oracle database
- Use the Data Pump export to move data between Oracle databases
- Use external tables to move data via platform independent file