OVERVIEW
This course will combine lecture with hands-on programming to facilitate the student’s understanding of the duties of a new Oracle database administrator for Oracle 10g. Hands-on labs reinforce the learning process by allowing students to create and manage their own Oracle servers. This course provides the foundation of knowledge for but does not cover database tuning.
DURATION: 5 - 8 Days
This training course contains a lot of information, sometimes too much for 5 days. The pre-existing knowledge of the student(s) will dictate how many days of in-class instructor-led training are required. For example, if the students have no prior administration experience with a relational database, we recommend either allocating 8 days, or deleting some lessons from the lesson plan. However, if the students are veteran DB2, or Sybase administrator's, it is quite possible that all topics can be covered in 5 days.
OBJECTIVES
- Describe the major architectural components of Oracle10g, 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.
- Use data dictionary for gaining a solid understanding of the purpose and content of many V$ and DBA-level views.
- Create administrative users, including granting and using the SYSDBA privilege, and create constraints on tables.
- Connect to the Oracle database as SYSDBA, both from a host server or - via the use of password files - over a network.
- Start and stop a database, including the use of various shutdown modes such as IMMEDIATE, TRANSACTIONAL and ABORT.
- View and alter initialization parameter values, including the use of the 10g Server Parameter File and the use of the 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 including the use of triggers.
- Create and manage locally managed tablespaces including adding free space, monitoring free/used space and using the 10g OMF feature to locate and name the underlying data files..
- Create heap tables including various data types 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 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 Oracle10g 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 data file.
- Use the 10g Data Pump utility to perform logical backups or move or copy objects.
- Use the SQL*Loader utility to load data into the database.
- Create a new database with the Database Configuration Assistant.
- Optional lessons include: Introduction to Partitioning, Other Table Types and Original Import / Export Utility
INTENDED AUDIENCE: This course is for Oracle database administrators.
PREREQUISITES
Before beginning this course, students should either have a working knowledge of both SQL and Oracle's PL/SQL.
FORMAT/MATERIALS
This course includes a combination of lecture, discussion and numerous computer workshops. Students will receive an extensive course manual covering Oracle DBA duties, which will be a valuable reference after class.
COURSE CONTENT
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
Oracle10g Architecture Part 1: Files
- Database and Instance
- The Big Picture
- Data Files
- Segments and Extents
- Query DBA_DATA_FILES
- Query DBA_SEGMENTS
- Redo Log Files
- Control Files
- Temp Files
- Parameter Files
- Password Files
- Architecture - Files Workshop
Oracle10g Architecture Part 2: Processes
- The Processes
- Background Processes Query
- V$BGPROCESS
- Database Block Writer
- Log Writer - LGWR
- System Monitor - SMON
- Process Monitor - PMON
- Checkpoint - CKPT
- Archiver - ARCn
- Miscellaneous Processes (10g Enhancements)
- Architecture - Processes Workshop
Oracle10g Architecture Part 3: Memory
- The Big Picture
- PGA and UGA
- The Oracle10g SGA
- SGA Areas
- Shared Pool
- Library Cache
- SQL Statement Processing
- Data Dictionary Cache
- Buffer Cache
- Database Block Size
- Non standard 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
- 10g Connect String Enhancements (10g)
- Startup/Shutdown Basics
- Startup Basics Workshop
- Stages Of STARTUP
- STARTUP Command
- STARTUP Examples
- 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
- Parameter Overrides
- Am I Using SPFILE?
- Helpful Dictionary Views
- Export & Backup
- Modifying Parameters
- SCOPE Clause
- Dynamic Parameters
- Server Parameter Files Workshop
Creating and Managing Users
- Creating New Users
- Intro to User Privileges
- Default and Temporary Parameters
- Default Permanent Tablespace (10g)
- 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
- Auditing SYSDBA
- Auditing SYSDBA Tips
- AUDIT Statement
- Auditing Logons
- Monitoring Logins
- Audit GRANT/REVOKE
- Object-Level Audits
- Monitoring Object Activity
- 10g Enhancements - Additional Data Capture (10g)
- Displaying Object Audit Activity
- The SYSAUD$ Table
- Auditing with Triggers
- Fine Grained Auditing
- Create FGA Policy
- Display FGA Activity
- Fine Grained Auditing of DML (10g)
- Auditing Summary
- Auditing Workshop
Managing Security
- Security Basics
- Privilege Types
- System Privileges
- ANY-Level Privileges
- The PUBLIC Keyword
- Revokking System Privileges (admin goes above)
- SESSION_PRIVS
- DBA_SYS_PRIVS
- DBA_TAB_PRIVS
- System Privileges Workshop
- Object Privileges
- GRANT Object-level Privileges
- INSERT Privilege
- UPDATE Privilege
- The ALL Option
- WITH GRANT OPTION
- 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
- PL/SQL and Roles
- Password Protected Roles
- Dropping A Role
- Revoking Roles
Summary
Roles Workshop
Creating/Managing Oracle 10g Tablespaces
- What is a Tablespace?
- Data Dictionary
- Why Tablespaces?
- Locally Managed Tablespaces
- Tablespace Basics Workshop
- Tablespace Parameter
- System-Managed LMT
- Uniform LMT's
- Bigfile Tablespaces (10g)
- Automatic Segment Space Management
- Oracle Managed Files
- OMF Key Points
- Configuration and File Location
- Striping Overview
- Non-Standard Blocksize
- Benefits of Non-Standard Blocksize
- Monitoring Free Space
- Adding Space
- Offline Tablespace
- Read-Only Tablespace
- Dropping Tablespaces
- Managing the SYSTEM Tablespace
- Managing the SYSAUX Tablespace (10g)
- Temporary Tablespace and Temporary Tablespace Groups (10g)
- Summary
- Tablespaces Workshop
Proactive Maintenance with Server-Generated Alerts (10g)
- Concepts and Alert Architecture
- Threshold-Based Alerts
- Default Thresholds
- Setting Custom Thresholds
- Enterprise Manager Notifications
- Display Alert Data in the Data Dictionary
- Summary
WorkshopManaging Oracle 10g Tables
- Types of Tables
- CREATE TABLE Statement
- Column Definition
- Character and Numeric Data types
- Date and Time Data types
- Binary Data types
- ANSI and Miscellaneous Data types
- Table Create Workshop
- Create Table As Subquery
- CTAS Options
- CTAS Workshop
- Collecting Statistics
- Oracle10g Automatic Statistics Collection (10g)
- GATHER_SCHEMA_STATS
- Gather Database Stats
- Table MONITORING
- Additional STATS Features
- DBMS_STATS Workshop
- Managing Table Storage
- INITRANS & MAXTRANS
- NOLOGGING
- Mini-Workshop
- Table Compression
- Table Compression Workshop
- ALTER TABLE MOVE
- CTAS Nologging
- DROP Column
- Renaming Tables
- Renaming Columns
- Summary
- Table Workshop
B-Tree Indexes
- Indexes: What and Why
- Index Structures
- B-Tree Organization
- Unique Index Scan
- AUTOTRACE
- Important Points
- CREATE INDEX
- NOLOGGING
- Statistics Collection
- Indexes WorkshopComposite Indexes
- Which Column First?
- Index Key Compression
- Descending Keys
- Index Coalesce
- Choosing Index Columns
- Calculating Selectivity
- Where to Look Next
- Summary Composite Index Workshop
Creating and Managing Constraints
- Intro to Constraints
- Inline Syntax
- Out-of-Line Syntax
- Constraint Name
- Referential Integrity
- Alter to Add Constraint
- Identifying Exceptions
- Constraint Exceptions Workshop
- NOVALIDATE Option
- Primary Key Index
- USING INDEX Option
- Modify State
- 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
- Using the 10g Redo Log Advisor (10g)
- Resources
- 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
- Change tracking (10g)
- 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?
- RMAN Workshop
Importing and Exporting with DataPump (10g)
- Data Pump Concepts
- Data Pump Modes
- Data Pump Access Methods
- Parallel Processing
- Export Example
- Import Example
- Data Pump Features
- Network Export / Import
- Network Import Example
- DBMS_DATAPUMP
- Data Pump Dictionary
- Datapump Resources
- Affect on External Tables
- Lesson Summary
- 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 DB Workshop
Optional: Automatic Storage Management (10g)
- Introducing ASM
- The ASM Instance
- Starting the ASM Instance
- ASM Disks
- ASM Diskgroups
- Creating Diskgroup
- Altering Diskgroup
- Working in the Database Instance
- Tablespace Management in ASM
- Summary ASM Benefits
- Workshop
Optional: Introduction to Oracle Partitioning
- Partitioning History
- Range Partitioning
- MAXVALUE and NULLs
- Partition Storage Attributes
- Multiple-Column Partitioning
- Hash Partitioning
- Composite Partitioning
- List Partitioning
- Composite Range-List Partitioning
- Subpartition Template
- IOT and Hash Partitioning
- Global Indexes and Partitioning Maintenance
- Row Movement
- Maintenance: Rolling In
- Maintenance: Rolling Out
- Merge Partitions
- Other Maintenance Operations
- Data Dictionary Implications
- Finding the Partitioning Key
- Finding the Partitioning Bounds
- Explicit Referencing
- Table Partitioning Restrictions
- Summary of Benefits
Optional: Introduction to Other Table Types
- Index Organized Tables
- Temporary Tables
Concepts
- Temporary Tables Management
- Creating Temporary Tables
- Index Clustered Tables
- Hash Clustered Tables
- Sorted Hash Clusters (10g)
- Nested Tables
- Nested Table Examples
- Object Tables
Optional : Logical Backups with Original Export/Import Utilities
- Introduction
- Export Modes
- User-Mode Export
- Export Features
- Import
- Using Import
- Import Tips
- Resources
- Logical Backups Workshop