Oracle Training Overview
This course provides you with the foundation skills necessary to create, maintain and manage an Oracle9i database. In this class we will focus on the techniques and skills necessary for day-to-day database administration tasks such as security, creating users, creating object (tables, tablespaces) and backup and recovery. An in-depth discussion of database architecture is provided.
This course has been completely updated for Oracle9i. Working examples are included throughout the course book and students have access to electronic versions of all examples and lab solutions. Approximately 50% of class time is spent on hands-on exercises.
This class will help prepare you for Oracle Certified Professional exam “Oracle9i Database: Fundamentals I Exam. It also covers some of the material required for the next test, “Oracle9i Database: Fundamentals II Exam.
Oracle Training Prerequisites
Oracle SQL, SQL*Plus and PL/SQL experience. This can be obtained in Introduction to Oracle9i for Developers class.
Oracle Training Audience
Database and System Administrators
Oracle Training Course duration
5 - 8 Days
Oracle Training Objectives
After successfully completing this course, you will be able to:
- Describe the major architectural components of Oracle9i, including the
SGA, background processes and files that make up the physical database.
- Query the data dictionary (e.g. DBA_TABLES, DBA_TABLESPACES) to determine
the structure of the database.
- Create administrative users, including granting and using the SYSDBA privilege
- Start and stop a database, including the use of various shutdown modes
such as IMMEDIATE, TRANSACTIONAL and ABORT.
- Alter initialization parameter values, including the use of the 9i Server
Parameter File.
- Create new users, provide tablespace quota and grant them the system or
object privileges necessary to perform their work, including the use of roles.
- Utilize the user password management features such as password expiration
and account locking.
- Audit activity on a database such as logon attempts.
- Create and manage locally managed tablespaces including adding free space,
monitoring free/used space and using the 9i OMF feature to locate and name
the underlying datafiles.
- Create heap tables including various datatypes such as VARCHAR2, DATE and
NUMBER. Also use the table compression feature. Use DBMS_STATS to gather optimizer
statistics on the tables you've created.
- Create constraints on tables.
- Create simple B-Tree indexes on tables. This includes understanding the
fundamental rules for when and where to create and when to avoid using indexes.
- Manage storage for objects (e.g. tables and indexes) with the PCTFREE and
PCTUSED parameters. Also control concurrency with the INITTRANS and MAXTRANS
parameters.
- Work with the Oracle9i Automatic Undo Management feature including creating
and enabling new undo tablespaces and monitoring for space-related problems.
- Ensure that the database is recoverable by checking the archive log status
of the database.
- Understand the role and importance of control files and add additional
control files.
- Use the basic features of Recovery Manager to perform simple backup and
recovery operations in the event of a lost or corrupted datafile.
Oracle Training Course outline
- A DBAs Introduction to the Oracle Data Dictionary
- Intro to Data Dictionary
- Static Views
- Common Static DBA Views
- Example: DBA_USERS
- Example: DBA_SYS_PRIVS
- Dynamic Performance Views
- Example: V$PARAMETER
- Miscellaneous Views
- DICTIONARY View…
- Data Dictionary Workshop
- Oracle9i Architecture Part 1: Files
- Database and Instance
- The Big Picture
- Data Files
- Redo Log Files
- Control Files
- Temp Files
- Parameter Files
- Password Files
- Architecture - Files Workshop
- Oracle9i Architecture Part 2: Processes
- The Big Picture
- The Processes
- Background Processes
- Database Block Writer
- Log Writer - LGWR
- System Monitor - SMON
- Process Monitor - PMON
- Checkpoint - CKPT
- Archiver - ARCn…
- Miscellaneous Processes
- Architecture - Processes Workshop
- Oracle9i Architecture Part 3: Memory
- The Big Picture
- PGA and UGA
- The Oracle9i SGA
- SGA Areas
- Shared Pool
- Library Cache
- SQL Statement Processing
- Data Dictionary Cache
- Buffer Cache
- Database Block Size
- Keep and Recycle Caches
- Redo Log Buffer
- Large Pool / Java Pool
- Summary
- Architecture - Memory Workshop
- Creating Administrative Users
- The SYS User
- The SYSTEM User
- SYSDBA
- DBA Role
- Local Connections
- Remote Connections
- Creating a Password File
- Password File Entries
- Create New Administrators
- Administrative Users Workshop
- Starting and Stopping an Oracle Instance
- STARTUP Procedures
- SQL*Plus for Startup
- Connecting for STARTUP
- Startup/Shutdown Basics
- Startup Basics Workshop
- Stages Of STARTUP
- STARTUP Command
- STARTUP Examples
- ALTER DATABASE
- Startup MOUNT Workshop
- Restricted Access
- Shutting Down
- Shutdown Workshop
- Initialization Parameter Files
- Review: Parameter Files
- Text Parameter File
- Introduction to SPFILE
- Benefits of SPFILE
- Creating an SPFILE
- Using SPFILE
- Monitoring Parameter Values
- V$PARAMETER View
- SPFILE - Exporting
- Persistent Parameters
- Summary
- Server Parameter Files Workshop
- Creating and Managing Users
- Creating New Users
- Intro to User Privileges
- Default and Temporary Parameters
- Querying the Data Dictionary
- Altering a User
- Dropping a User
- Killing a User Session
- User Basics Workshop
- Quota Concepts
- Providing Quota
- Unlimited and Zero Quota
- Query Existing Quotas
- User Space Used
- User Quota Workshop
- Installation Users
- Secure Installation Users
- User Account Status Workshop
- Password Management
- Password Aging
- Manual Password Expiration
- Account Locking
- Password History
- Password Complexity
- User Passwords Workshop
- Introduction to Auditing
- Auditing Concepts
- Enabling Auditing
- Monitoring Logins
- Audit GRANT/REVOKE
- Object-Level Audits
- Displaying Object Audit Activity
- The SYSAUD$ Table
- Auditing with Triggers
- Auditing Summary
- Auditing Workshop
- Managing Security
- Security Basics
- Privilege Types
- System Privileges
- ANY-Level Privileges
- The PUBLIC Keyword
- ADMIN Option
- Revoking System Privileges
- Data Dictionary
- System Privileges Workshop
- Object Privileges
- GRANT Object-level Privileges
- INSERT Privilege
- UPDATE Privilege
- The ALL Option
- WITH GRANT OPTION
- Data Dictionary
- Object Privileges Workshop
- Role Concepts
- Predefined Roles
- Giving System And Object Privileges To Roles
- Granting Roles
- Enabling and Disabling
- SET ROLE Command
- SET ROLE Options
- The ADMIN OPTION
- Granting Roles to Roles
- Password Protected Roles
- Dropping A Role
- Revoking Roles
- Roles Workshop
- Creating and Managing Tablespaces
- Review: Tablespace Concepts
- Storage Fundamentals
- Creating Tablespaces
- Creating OMF Tablespaces
- OMF Key Points
- Configuration and File Location
- Multiple Blocksize Support
- Why Non-Standard Block Size?
- Free Space
- Adding Space
- Online/Offline Tablespace
- Read/Write Tablespace
- Dropping Tablespaces
- Managing SYSTEM TS
- Tablespaces Workshop
- Managing Tables
- Types of Tables
- CREATE TABLE Statement
- Character and Numeric Datatypes
- Date and Time Datatypes
- Binary Datatypes
- Other Datatypes
- Table Create Workshop
- NOLOGGING Parameter
- Table Compression
- Table Compression Workshop
- Create Table as Subquery
- CTAS Options
- Moving a Table
- MOVE Table
- ONLINE Option
- DROP Column
- DROP Column Syntax
- Immediate DROP Column
- Deferred DROP Column
- DROP Restrictions
- Renaming Tables and Columns
- Statistics Collection and Management
- DBMS_STATS
- DBMS_STATS Workshop
- Summary
- Creating and Managing Constraints
- Intro to Constraints
- Violating a Constraint
- Constraint States
- Identifying Exceptions
- Deferred Constraint Checking
- Dropping a Constraint
- Renaming a Constraint
- Data Dictionary and Constraints
- Summary
- Constraints Workshop
- Creating Indexes
- Index Concepts
- B-Tree Concepts
- B-Tree Effect on Query Performance
- Creating an Index
- Nologging Option
- Statistics Collection
- Choosing Index Columns
- Calculating Selectivity
- When to Avoid Indexing
- Multi-Column Indexes
- Which Column First?
- Index Monitoring
- When to Create
- Where to Create
- Indexes Workshop
- Managing Storage
- Managing Storage
- PCTFREE & PCTUSED
- FREELISTS & PCTUSED
- PCTFREE/PCTUSED Scenarios
- PCTFREE/PCTUSED Summary
- INITRANS & MAXTRANS
- INITRANS/MAXTRANS Example
- Storage Workshop
- Managing Undo
- What is Undo?
- Where is Undo Kept?
- Why Not Rollback Segments?
- Why AUM?
- Implementing AUM
- Find Undo Tablespace
- Create Undo Tablespace
- Initialization Parameters
- Switching Undo Tablespace
- Dropping Undo Tablespace
- Data Dictionary Views
- Tuning AUM
- Adding Space
- Summary
- Automatic Undo Management Workshop
- Managing Redo Logs
- Review: Concepts
- DBA's Responsibilities
- Archiving Status
- Enabling Archiving
- Archiving Workshop
- Multiplexing Archive Logs
- Multiplex Archive Logs Workshop
- Log Switch Delays
- Adding Log Files
- Introduction to OMF
- Adding Log Files - OMF
- Multiplexing Online Logs
- Add Redo Log Workshop
- Managing Control Files
- Control File Review
- DBA Responsibilities
- Adding Control Files
- Add Control File Workshop
- Backing Up Control File
- Recreating Control File
- Optional Create Control File Workshop
- Introduction to RMAN
- What is RMAN?
- RMAN Components
- Starting and Connecting
- Common RMAN Commands
- Backup Concepts
- Full Backup
- Full Tablespace Backup
- Datafile Backup
- Incremental Backup
- Backups - Where to go from here
- RESTORE Command
- RECOVER Command
- Complete Recovery - Database
- Recover Tablespace
- Summary: Restore and Recovery
- Reporting
- Report Need Backup
- Show Stored Settings
- What Else is There?
- Getting More Information
- RMAN Workshop
- Logical Backups
- Introduction
- Export Modes
- User-Mode Export
- Export Features
- Import
- Using Import
- Import Tips
- Resources
- Logical Backups Workshop
- SQL*Loader
- Introduction
- Executing SQL*Loader
- The Control File
- Variable Format Data
- Fixed Format Data
- LOG File
- Conditional Loads
- SQL*Loader Workshop
- Introduction to Oracle Networking
- Introduction to Oracle Net
- Configuration Files
- tnsnames.ora
- Introduction to the Listener
- Listener Control Utility
- Listener Started?
- Stopping the Listener
- Listener Password
- listener.ora
- Resources
- Oracle Net Workshop
- Create the DataBase
- Major Steps
- What We Need to KnoW
- The Oracle SID
- Windows Services
- Seeing the Active Threads
- Listing Active Instances
- Stopping Active Instances
- Database Configuration Assistant
- Select Operation
- Select Template
- Database ID
- Database Features
- Database Connection Options
- Init Parameters
- Database Storage
- Create Options
- The .BAT File
- ORADIM
- CreateDB Script
- Create DataBase Workshop
- Online topic - User managed Backup & Recovery
- Online topic - Implementing Partitioning
- Online topic - Other Table Types
Hardware/Software Requirements
- Hardware Requirements
- Minimal Processor: Pentium 166 or Pentium 200
- Recommended Processor: Pentium 233 or Pentium 266
- RAM: Minimum 128 MB (256 MB recommended)
- Oracle Corporation also recommends increasing virtual memory to:
- Minimum 200 MB, Maximum 400MB
- Modify it in the Performance tab of System Properties in the Control
Panel
- Available Disk Space (FAT or HTFS):
- FAT 4.5 GB for HOME Directory, 140MB for System Drive
- NTFS 2.75 GB for HOME Directory, 140MB for System Drive
- Temp Space - 400 MB
- Video: 256 color
- A connection to the Internet is desirable (but not absolutely necessary)
to show students various Oracle-related sites.
- Software Requirements
- Oracle9i - 9i Release 2 (provided by customer) is recommended. A new
install is strongly recommended. Each student should have his or her own
instance.
- Any compatible operating system for the version of Oracle provided.
- Microsoft Internet Explorer 5 or later OR Netscape Navigator 4.5 or
later
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)
|
