Oracle 11g and 10g Database Administration – ORA511

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

Starting and Shutting Down an Oracle Database

• SYSDBA and SYSOPER System Privileges
• Oracle 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 Dictionary
• DBA, 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

Oracle Database Users and Schemas

• Users and Schemas
• Oracle Default Database Users
• Oracle Sample Schemas
• Creating Users
• Altering and Dropping Users
• Data Dictionary Views For Users

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

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

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
• 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 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

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 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 Overview
• Database 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 Segments
• Fragmentation
• Coalescing Fragmented Space
• Row Migration and Chaining
• Manual Segment Space Usage
• Automatic Segment Space Management
• Data Dictionary Views for Physical Objects

Tables

• Tables Overview
• Physical 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

• Indexes
• B-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 Constraints
• Constraint 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

• Views
• Creating 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 Privileges
• Granting and Revoking Object Privileges
• Using WITH GRANT OPTION
• Revoking and the GRANT OPTION
• Data Dictionary Views for Object Privileges

Synonyms

• Synonyms
• Private and Public Synonyms
• Creating and Dropping Synonyms
• Security and Synonyms
• Object Precedence

The Optimizer and Statistics

• Optimizer Overview
• Optimizer Statistics
• Data Dictionary Views and Statistics
• Collecting Statistics
• Removing Statistics
• Automated Statistics Collection

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

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

SQL*Loader and External Tables

• SQL*Loader
• SQL*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 Backups
• Offline Backup Basics
• Oracle Offline Backup Steps
• Other Backup Considerations
• Advanced Backup Concepts
• RMAN

Recover Your Database

• Offline Recovery Basics
• Restoration Using Offline Backups for a NOARCHIVE Database
• Restoring Other Files
• Other Recovery Considerations

Oracle Enterprise Manager

• Introducing Oracle Enterprise Manager
• OEM 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