Oracle 11G Database Administration
Course Description
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.
5 Days
Contact us for pricing
Prerequisites
Introduction to Oracle 11g, or at least six months working in an Oracle technical environment. An understanding of relational database concepts, SQL, and PL/SQL programming skills are required. A solid understanding of Oracle schema is recommended.Overview of Oracle Database
ORACLE_HOME and ORACLE_SIDThe 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
Starting and Shutting Down an Oracle Database
SYSDBA and SYSOPER System PrivilegesOracle Database Startup
Oracle Database 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
Using The Oracle Data Dictionary
Introducing the Data DictionaryDBA, ALL, and USER Data Dictionary Views
V$ Dynamic Performance Views
Using Oracle Documentation to Locate Data Dictionary Views
Combining Data Dictionary Views
Oracle Database Users and Schemas
Users and SchemasOracle Default Database Users
Oracle Sample Schemas
Creating Users
Altering and Dropping Users
Data Dictionary Views For Users
Oracle System Privileges
Overview of System PrivilegesGranting and Revoking System Privileges
Data Dictionary Views for System Privileges
Roles
Creating and Removing Roles
Data Dictionary Views for Roles
Predefined Roles
User Group PUBLIC
Parameter Files
Oracle Database ParametersThe 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
Datafiles and Tablespaces
Datafiles OverviewTablespaces Overview
SYSTEM and SYSAUX Tablespaces
Creating Tablespaces
Dictionary- and Locally Managed Tablespaces
Locally Managed Tablespace Extent Allocation
Temporary Tablespaces
Temporary Tablespace Groups
Default Tablespaces
Tablespace Quotas
Dropping and Altering a Tablespace
Renaming a Tablespace
Renaming or Relocating Datafiles
Bigfile Tablespaces
Data Dictionary Views for Datafiles and Tablespaces
Control Files
Control Files OverviewDatabase 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
Oracle Physical Structures — Online Redo Logs
RedoRedo Log Files
Database Parameters for Redo
Sizing the Redo Log Files
How Many Redo Log Groups?
Creating Redo Logs
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
Oracle Physical Structures — Undo Segments
Undo OverviewDatabase Parameters for Undo
Creating Undo Tablespaces
Altering and Dropping Undo Tablespaces
Switching Undo Tablespaces
Undo Advisor
Data Dictionary Views for Undo
Segment Space Management
Blocks, Extents and SegmentsSegment Space Management
Fragmentation
Coalescing Fragmented Space
Row Migration and Chaining
Manual Segment Space Usage
Automatic Segment Space Management
Data Dictionary Views for Physical Objects
Tables
Tables OverviewPhysical Properties of Tables
Creating Tables
Table Storage and Logging
Altering Tables
Dropping Tables
The Recycle Bin
The TRUNCATE COMMAND
Temporary Tables
Clustered Tables
Indexed-Organized Tables
Creating an IOT
Data Dictionary Views for Tables
Indexes
IndexesB-tree Indexes
NULL Values and Indexes
NULL Values and Unique Indexes
Creating Indexes
Monitoring Index Usage
Dropping Indexes
Rebuilding and Moving Indexes
Coalescing Indexes
Bitmap Indexes
Special Index Types
Index Key Compression
Data Dictionary Views for Indexes
Constraints
Integrity ConstraintsConstraint Names and Syntax
Constraint Checking
Managing Primary Key Constraints
Managing NOT NULL Constraints
Managing Check Constraints
Managing Foreign Key Constraints
Data Dictionary Views for Constraints
Views
ViewsCreating and Replacing Views
Data Dictionary Views for Views
Security Through Views
Altering and Dropping Views
Dependencies and Views
Updating Data Through Views
Object Privileges
Object PrivilegesGranting and Revoking Object Privileges
Using WITH GRANT OPTION
Revoking and the GRANT OPTION
Data Dictionary Views for Object Privileges
Synonyms
SynonymsPrivate and Public Synonyms
Creating and Dropping Synonyms
Security and Synonyms
Object Precedence
The Optimizer and Statistics
Optimizer OverviewOptimizer Statistics
Data Dictionary Views and Statistics
Collecting Statistics
Removing Statistics
Automated Statistics Collection
Oracle Net Services
Oracle Networking ExplainedThe Net Configuration Assistant
Configuring the Listener — GUI
Configuring the Listener — Manually
Configuring the Client — GUI
Configuring the Client — Manually
Data Pump
Oracle Data Pump ArchitectureConfiguring 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
SQL*Loader and External Tables
SQL*LoaderSQL*Loader Control File
Loading Data with SQL*Loader
Conventional vs. Direct Path Loads
External Tables Overview
Creating an External Table
Creating a Writable External Table
Back Up Your Database
Physical Backups vs. Logical BackupsOffline Backup Basics
Oracle Offline Backup Steps
Other Backup Considerations
Advanced Backup Concepts
RMAN
Recover Your Database
Offline Recovery BasicsRestoration Using Offline Backups for a NOARCHIVE Database
Restoring Other Files
Other Recovery Considerations
Oracle Enterprise Manager
Introducing Oracle Enterprise ManagerOEM Architecture and Physical Structure
Installing OEM When Using DBCA
Using EMCA to Install OEM
Managing OEM
Removing OEM
Troubleshooting OEM
Securing OEM
Starting OEM
Introducing the OEM Home Pages
Using OEM — Managing the Job Scheduler
Using OEM — Using Metrics, Alerts, and Thresholds
Automatic Workload Repository
The Automatic Database Diagnostic Monitor — ADDM
OEM and Advisors
The SQL Access Advisor
Appendix A: Installing Oracle Software
Optimal Flexible ArchitectureOFA 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 DBCASelect a Database Template
Specify the Database Name
Set System Account Passwords
Specify File Locations
Install Sample Schemas
Security Settings
Automatic Maintenance Tasks
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 CreationPrepare the System
Creating the Parameter File
The CREATE DATABASE Statement
Manually Create the Database
Finalizing the Creation
SQL.BSQ File
Creating the Service on Windows
Appendix D: Auditing the Database
Auditing ExplainedData Dictionary Views for Auditing
Audit Trail, OS, and DB
Statement Auditing
Privilege Auditing
Object Auditing
DBAOracleOracle 11gOracle Database 11gOracle Database Administration