176. Oracle10g Database
Administration
Rev. 4.1
This class will develop the key fundamental skills necessary to be
an Oracle DBA. Students will design and create a server using the Optimal
Flexible Architecture (OFA), configure logical and physical structures, set up
database and user security, add and administer users, and monitor and tune main
server areas. Each student will create a toolkit of administration scripts for
database management and tuning by the end of this class. This class will also
address issues for DBAs having to support Oracle8i and Oracle9i databases.
LEARNING OBJECTIVES
·
Gain a working knowledge of database
administration for Oracle 10g databases
·
Set up and configure Oracle 10g databases
·
Administer users and handle security issues
·
Monitor and tune Oracle 10g databases
Course Duration: 5 days
Audience: Database
administrators
Prerequisite: An understanding of relational database
concepts, SQL, and PL/SQL programming skills are required. A solid
understanding of Oracle schema is recommended.
1.
Course Introduction
Course Objectives
Course Overview
Using the Workbook
Suggested References
2.
Overview of Oracle Database
ORACLE_HOME and ORACLE_SID
The Oracle Database vs. The Oracle Instance
Instance Memory Structures
Background Processes
Server Processes
Datafiles
Blocks, Extents, and Segments
Control Files
Redo Logs
The Oracle Architecture
SYS and SYSTEM Users
3.
Starting and Shutting Down an Oracle Database
SYSDBA and SYSOPER System Privileges
Oracle Database Startup and Shutdown
Altering Database Availability
Suspending and Resuming a Database
Quiescing a Database
Tracking Database Activity - The Alert Log
Data Dictionary Views for Startup and Shutdown
4.
Using The Oracle Data Dictionary
Introducing the Data Dictionary
DBA, ALL, and USER Data Dictionary Views
V$ Dynamic Performance Views
Using Oracle Documentation to Locate Data Dictionary Views
Combining Data Dictionary Views
5.
Oracle Database Users and Schemas
Users and Schemas
Oracle Default Database Users
Oracle Sample Schemas
Creating, Altering, and Dropping Users
Data Dictionary Views For Users
6.
Oracle System Privileges
Overview of System Privileges
Granting and Revoking System Privileges
Data Dictionary Views for System Privileges
Roles
Creating and Removing Roles
Data Dictionary Views for Roles
Pre-defined Roles
User Group PUBLIC
7.
Parameter Files
Oracle Database Parameters
The Parameter File (PFILE) and the Server Parameter File (SPFILE)
Dynamic vs. Static Parameters
Determining Current Parameter Settings
Benefits of SPFILEs over PFILEs
Creating an SPFILE from a PFILE and Back Again
Understanding Scope
Session-Level Parameters
Data Dictionary Views for Parameters
8.
Datafiles and Tablespaces
Datafiles Overview
Tablespaces Overview
SYSTEM and SYSAUX Tablespaces
Creating Tablespaces
Dictionary- and Locally-Managed Tablespaces
Locally-Managed Tablespace Extent Allocation
Temporary Tablespaces and Tablespace Groups
Default Tablespaces
Tablespace Quotas
Dropping, Altering, and Renaming a Tablespace
Renaming or Relocating Datafiles
Bigfile Tablespaces
Data Dictionary Views for Datafiles and Tablespaces
9.
Control Files
Control Files Overview
Database Parameters for Control Files
Backing Up Control Files
Restoring Control Files from Multiplexed Copies
Restoring Control Files from Backups
Moving Control Files
Data Dictionary Views for Control Files
10. Oracle
Physical Structures - Online Redo Logs
Redo
Redo Log Files
Database Parameters for Redo
Sizing the Redo Log Files
How Many Redo Log Groups?
Creating and Removing Redo Logs
Renaming Redo Log Files
Forcing Log Switches
Archiving Redo Logs
Configuring Archive Log Mode
Database Parameters for Archiving
Data Dictionary Views for Redo Logs
11. Oracle
Physical Structures - Undo Segments
Undo Overview
Database Parameters for Undo
Creating, Altering, and Dropping Undo Tablespaces
Switching Undo Tablespaces
Undo Advisor
Data Dictionary Views for Undo
12. Tables
Tables Overview
Physical Properties of Tables
Row Chaining and Row Migration
Creating, Altering, and Dropping Tables
Table Storage and Logging
The Recycle Bin
The TRUNCATE COMMAND
Temporary, Clustered, and Indexed-Organized Tables
Creating an IOT
Data Dictionary Views for Tables
13. Indexes
B-tree Indexes
NULL Values and Indexes
NULL Values and Unique Indexes
Creating and Dropping Indexes
Monitoring Index Uage
Rebuilding and Moving Indexes
Coalescing Indexes
Bitmap Indexes
Special Index Types
Index Key Compression
Data Dictionary Views for Indexes
14. Constraints
Integrity Constraints
Constraint Names and Syntax
Constraint Checking
Managing Primary Key and NOT NULL Constraints
Managing Check and Foreign Key Constraints
Data Dictionary Views for Constraints
15. Views
Creating and Replacing Views
Data Dictionary Views for Views
Security Through Views
Altering and Dropping Views
Dependencies and Views
Updating Data Through Views
16. Object
Privileges
Granting and Revoking Object Privileges
Using WITH GRANT OPTION
Revoking and the GRANT OPTION
Data Dictionary Views for Object Privileges
17. Synonyms
Creating and Dropping Synonyms
Private and Public Synonyms
Security through Synonyms
Object Precedence
18. The
Optimizer and Statistics
Optimizer Overview
Optimizer Statistics
Data Dictionary Views and Statistics
Collecting and Removing Statistics
Automated Statistics Collection
19. Segment
Space Management
Blocks, Extents and Segments
Fragmentation
Coalescing Fragmented Space
Row Migration and Chaining
Manual Segment Space Usage
Automatic Segment Space Management
Data Dictionary Views for Physical Objects
20. Oracle
Net Services
Oracle Networking Explained
The Net Configuration Assistant
Configuring the Listener - GUI
Configuring the Listener - Manually
Configuring the Client - GUI
Configuring the Client – Manually
21. Data
Pump
Oracle Data Pump Architecture
Configuring for Data Pump
Command-Line Utilities
Exporting With expdp
Importing With impdp
Interactive Mode
Attaching to a Running Job
Parallel Data Pump
Data Dictionary Views for Data Pump
22. SQL*Loader
and External Tables
SQL*Loader Control File
SQL*Loader Features
Loading Data with SQL*Loader
External Tables Overview
Creating an External Table
Creating a Writable External Table
23. Backup
Your Database
Physical Backups vs. Logical Backups
Offline Backup Basics
Oracle Offline Backup Steps
Other Backup Considerations
Advanced Backup Concepts
RMAN
24. Recover
Your Database
Offline Recovery Basics
Restoration Using Offline Backups for a NOARCHIVE Database
Restoring Other Files
Other Recovery Considerations
Appendix A. Installing
Oracle Software
Optimal Flexible Architecture
OFA Directory Layouts
The Oracle Universal Installer
OUI Installation Modes
Preparing To Install Software
Starter Database
Installing the Oracle Software
Deinstalling Oracle Software
Appendix B. Creating a
Database Using the DBCA
The DBCA
Select a Database Template
Specify the Database Name
Set System Account Passwords
Specify File Locations
Install Sample Schemas
Specify Storage Parameters
Create Your Oracle Database
Oracle Net Configuration Assistant
Configure the Listener
Set TCP Parameters
Removing a Database with the DBCA
Appendix C. Manual Database
Creation
Manual Database Creation
Prepare the System
Creating the Parameter File
The CREATE DATABASE Statement
Manually Create the Datbase
Finalizing the Creation
SQL.BSQ File
Creating the Service on Windows
Appendix D. Auditing the
Database
Auditing Explained
Data Dictionary Views for Auditing
Audit Trail, OS, and DB
Statement Auditing
Privilege Auditing
Object Auditing
System Requirements
Course exercises require Oracle 10g running on a Microsoft Windows,
Linux, or multi-user UNIX system. See
the appropriate course Setup Guide for details.
A good minimal hardware profile for this course would have a Pentium
500-MHz or equivalent CPU, 512 MB of RAM, and at least 4 GB of free disk space
for the Oracle installation.
