CONTENTS
Chapter 1 Installing Oracle Database Software
Plan for an Oracle Database Software Installation
Obtain the Oracle Database Software
Install the Oracle Database Software
The OUI Dialog: Interactive Install
Silent Install and Response Files
Plan for an Oracle Database Software Installation
Install the Oracle Database Software
Chapter 2 Creating an Oracle Database Using DBCA
Create a Database by Using the Database Configuration Assistant
The Instance, the Database, and the Data Dictionary
Using the Database Configuration Assistant to Create a Database
Generate Database Creation Scripts by Using DBCA
The Initialization Parameter File
Manage Database Design Templates by Using DBCA
Configure Database Options by Using DBCA
Create a Database by Using the Database Configuration Assistant
Generate Database Creation Scripts by Using DBCA
Manage Database Design Templates by Using DBCA
Configure Database Options by Using DBCA
Chapter 3 Installing Oracle Grid Infrastructure for a Stand-Alone Server
Configure Storage for Oracle Automatic Storage Management
Install Oracle Grid Infrastructure for a Stand-Alone Server
Configure Storage for Oracle Automatic Storage Management
Install Oracle Grid Infrastructure for a Stand-Alone Server
Chapter 4 Using Oracle Restart
Use Oracle Restart to Manage Components
Administering the Oracle Restart Processes
Administering Registered Resources
Use Oracle Restart to Manage Components
Chapter 5 DDL and Schema Objects
Categorize the Main Database Objects
List the Data Types that Are Available for Columns
Creating Tables with Column Specifications
Creating Tables from Subqueries
Altering Table Definitions After Creation
Dropping and Truncating Tables
Create and Use Temporary Tables
Modifying and Dropping Indexes
CREATE VIEW, ALTER VIEW, and DROP VIEW
Categorize the Main Database Objects
List the Data Types that Are Available for Columns
Create and Use Temporary Tables
Describe Each Data Manipulation Language Statement
Transaction Control: COMMIT, ROLLBACK, SAVEPOINT, SELECT FOR UPDATE
Explain DML and Undo Data Generation
Monitor and Administer Undo Data
Error Conditions Related to Undo
Parameters for Undo Management
Sizing and Monitoring the Undo Tablespace
Creating and Managing Undo Tablespaces
Describe the Difference Between Undo Data and Redo Data
Configuring Undo Retention to Support Long-Running Queries
Flashback Query and Undo Retention
Describe the Locking Mechanism and Data Concurrency Management
Monitor and Resolve Locking Conflicts
Describe Each Data Manipulation Language Statement
Explain DML and Undo Data Generation
Monitor and Administer Undo Data
Describe the Difference Between Undo Data and Redo Data
Describe the Locking Mechanism and Data Concurrency Management
Monitor and Resolve Locking Conflicts
Chapter 7 Retrieving, Restricting, and Sorting Data Using SQL
Explain the Capabilities of SQL SELECT Statements
Introducing the SQL SELECT Statement
Capabilities of the SELECT Statement
Create the Demonstration Schemas
Execute a Basic SELECT Statement
Syntax of the Primitive SELECT Statement
Rules Are Meant to Be Followed
Limit the Rows Retrieved by a Query
Sort the Rows Retrieved by a Query
Explain the Capabilities of SQL SELECT Statements
Execute a Basic SELECT Statement
Limit the Rows Retrieved by a Query
Sort the Rows Retrieved by a Query
Chapter 8 Single-Row and Conversion Functions
Describe and Use Character, Number, and Date Functions in SQL
Using Case Conversion Functions
Using Character Manipulation Functions
Describe Various Types of Conversion Functions Available in SQL
Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
Using the Conversion Functions
Apply Conditional Expressions in a SELECT Statement
Describe Various Types of Conversion Functions Available in SQL
Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
Apply Conditional Expressions in a SELECT Statement
Group Data Using the GROUP BY Clause
Include or Exclude Grouped Rows Using the HAVING Clause
Group Data Using the GROUP BY Clause
Include or Exclude Grouped Rows Using the HAVING Clause
Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins
Joining Tables Using ANSI SQL Syntax
Qualifying Ambiguous Column Names
N-Way Joins and Additional Join Conditions
Join a Table to Itself Using a Self-Join
Joining a Table to Itself Using the JOIN . . . ON Clause
View Data That Does Not Meet a Join Condition by Using Outer Joins
Generate a Cartesian Product of Two or More Tables
Creating Cartesian Products Using Cross Joins
Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins
Join a Table to Itself Using a Self-Join
View Data that Does Not Meet a Join Condition Using Outer Joins
Generate a Cartesian Product of Two or More Tables
Chapter 11 Subqueries and Set Operators
Describe the Types of Problems that the Subqueries Can Solve
Use of a Subquery Result Set for Comparison Purposes
Generate a Table from Which to SELECT
Generate Values for Projection
Generate Rows to Be Passed to a DML Statement
Describe the Types of Subqueries
Single- and Multiple-Row Subqueries
Write Single-Row and Multiple-Row Subqueries
Set Operator General Principles
Use a Set Operator to Combine Multiple Queries into a Single Query
Control the Order of Rows Returned
Describe the Types of Problems that the Subqueries Can Solve
Describe the Types of Subqueries
Write Single-Row and Multiple-Row Subqueries
Use a Set Operator to Combine Multiple Queries into a Single Query
Control the Order of Rows Returned
Chapter 12 Architectural Overview of Oracle Database 12c
List the Architectural Components of an Oracle Database
Single-Instance Database Architecture
Distributed Systems Architectures
Describe the Background Processes
MMON, the Manageability Monitor
MMNL, the Manageability Monitor Light
LREG, the Listener Registration Process
Some Other Background Processes
Explain the Relationship Between Logical and Physical Storage Structures
The Physical Database Structures
The Logical Database Structures
List the Architectural Components of an Oracle Database
Describe the Background Processes
Explain the Relationship Between Logical and Physical Storage Structures
Chapter 13 Instance Management
Log On as a Normal or Privileged User
Understand Initialization Parameter Files
Static and Dynamic Parameter Files
Static and Dynamic Parameters and the Initialization Parameter File
Start Up and Shut Down an Oracle Database Instance
Starting the Database Listener
View the Alert Log and Access Dynamic Performance Views
Understand Initialization Parameter Files
Start Up and Shut Down an Oracle Database Instance
View the Alert Log and Access Dynamic Performance Views
Oracle Net and the Client-Server Paradigm
Use Tools for Configuring and Managing the Oracle Network
The Net Configuration Assistant
The TNS_ADMIN Environment Variable
Directory Naming and External Naming
Testing Oracle Net Connectivity
Understand Database Resident Connection Pooling
Configure Communication Between Databases
Use Tools for Configuring and Managing the Oracle Network
Understand Database Resident Connection Pooling
Configure Communication Between Databases
Describe the Storage of Table Row Data in Blocks
Segments, Extents, Blocks, and Rows
Explain How Oracle Database Server Automatically Manages Space
Automatic Segment Space Management
Save Space by Using Compression
Proactively Monitor and Manage Tablespace Space Usage
Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality
Manage Resumable Space Allocation
Describe the Storage of Table Row Data in Blocks
Explain How Oracle Database Server Automatically Manages Space
Save Space by Using Compression
Proactively Monitor and Manage Tablespace Space Usage
Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality
Manage Resumable Space Allocation
Create and Manage Database User Accounts
Creating and Assigning Profiles
Explain DBA Responsibilities for Security and Auditing
Enable Standard Database Auditing and Unified Auditing
Create and Manage Database User Accounts
Explain DBA Responsibilities for Security and Auditing
Enable Standard Database Auditing and Unified Auditing
Chapter 17 Moving and Re-organizing Data
Create and Use Directory Objects
Use SQL*Loader to Load Data from a Non-Oracle Database
Use External Tables to Move Data via Platform-Independent Files
Explain the General Architecture of Oracle Data Pump
Use Data Pump Export and Import to Move Data Between Oracle Databases
Using Data Pump with the Command-Line Utilities
Use Data Pump in a Multitenant Environment
Export from Non-CDB and Import into PDB
Export and Import Between PDBs
Export from PDB and Import into Non-CDB
Full Transportable Export and Import
Transporting a Database Over the Network
Use SQL*Loader in a Multitenant Environment
Create and Use Directory Objects
Use SQL*Loader to Load Data from a Non-Oracle Database
Use External Tables to Move Data via Platform-Independent Files
Explain the General Architecture of Oracle Data Pump
Use Data Pump Export and Import to Move Data Between Oracle Databases
Use Data Pump in a Multitenant Environment
Use SQL*Loader in a Multitenant Environment
Chapter 18 The AWR and the Alert System
Manage the Automatic Workload Repository
Use the Automatic Database Diagnostic Monitor
Describe and Use the Advisory Framework
The Mean Time to Recover Advisor
Alert Condition Monitoring and Notifications
Manage the Automatic Workload Repository
Use the Automatic Database Diagnostic Monitor
Describe and Use the Advisory Framework
Use Enterprise Manager to Monitor Performance
A Performance Tuning Methodology
The Database Express Performance Pages
Use Automatic Memory Management
Use the Memory Advisor to Size Memory Buffers
How the Optimizer Uses Statistics
The Capabilities of the SQL Tuning Advisor
The SQL Tuning Advisor API: The DBMS_SQLTUNE Package
Use the SQL Access Advisor to Tune Workload
The Capabilities of the SQL Access Advisor
Using the SQL Access Advisor with DBMS_ADVISOR
Use Enterprise Manager to Monitor Performance
Use Automatic Memory Management
Use the Memory Advisor to Size Memory Buffers
Use the SQL Access Advisor to Tune Workload
Chapter 20 The Resource Manager
Configure the Database Resource Manager
The Need for Resource Management
The Resource Manager Architecture
Resource Manager Configuration
Access and Create Resource Plans
The Active Session Pool Method
Limiting the Degree of Parallelism
Controlling Jobs by Execution Time
Terminating Sessions by Idle Time
Restricting Generation of Undo Data
Configure the Database Resource Manager
Access and Create Resource Plans
Use Oracle Scheduler to Simplify Management Tasks
Use Job Chains to Perform a Series of Related Tasks
Use Scheduler Jobs on Remote Systems
Use Advanced Scheduler Features to Prioritize Jobs
Use Oracle Scheduler to Simplify Management Tasks
Use Job Chains to Perform a Series of Related Tasks
Use Scheduler Jobs on Remote Systems
Use Advanced Scheduler Features to Prioritize Jobs
Chapter 22 Configuring the Database for Backup and Recovery
Possible Failures and Their Resolution
Oracle Backup and Recovery Solutions
Instance Recovery and the Impossibility of Database Corruption
Protecting the Online Redo Log Files
Archivelog Mode and the Archiver Processes
Backup and Recovery: Configuration
Configure the Fast Recovery Area
Possible Failures and Their Resolution
Configure the Fast Recovery Area
Configure Control Files and Redo Log Files for Recoverability
Backup Theory and Basic Syntax
Some Advanced RMAN Capabilities
Create and Use RMAN Stored Scripts
Create Consistent Database Backups
Back Up Your Database Without Shutting It Down
Manage Backups and the RMAN Repository
Use Various RMAN Backup Types and Strategies
Perform Full and Incremental Backups
Use Techniques to Improve Backups
Chapter 24 Restore and Recover with RMAN
Perform Complete and Incomplete Recovery
Restore and Recovery in One Page
Using the RMAN RESTORE and RECOVER Commands
Performing Complete Recovery of a Noncritical Datafile
Performing Complete Recovery of a Critical Datafile
Performing Incomplete Recovery Using RMAN
Recovering Using Incrementally Updated Backups
Switching to Image Copies for Fast Recovery
Perform Recovery for Spfiles, Controlfiles, and Online Redo Log Files
Restoring the Spfile from the Autobackup
Recovering from a Lost Redo Log Group
Backing Up and Restoring the Password File
Recovering from a Lost Tempfile
Perform Complete and Incomplete Recovery
Perform Recovery for Spfiles, Controlfiles, and Online Redo Log Files
Chapter 25 Diagnosing Failures
Describe the Automatic Diagnostic Workflow
Understanding Alerts, Problems, and Incidents
Using the Data Recovery Advisor
Describe the Automatic Diagnostic Workflow
Describe the Flashback Technologies
Flashback Query, Transaction, and Table
When to Use Flashback Technology
Perform Flashback Table Drop Operations
The Implementation of Flashback Drop
Perform Table Recovery from Backups
Describe and Use Flashback Data Archive
Flashback Database Architecture
Configuring Flashback Database
Describe the Flashback Technologies
Perform Flashback Table Operations
Perform Table Recovery from Backups
Describe and Use Flashback Data Archive
Chapter 27 Duplicating a Database
Describe and Use Transportable Tablespaces and Databases
Configuring Transportable Tablespaces
Duplicate from the Active Database
Describe and Use Transportable Tablespaces and Databases
Choose a Technique for Duplicating a Database
Create a Backup-Based Duplicate Database
Chapter 28 Encrypting, Securing, Monitoring, and Tuning RMAN Backups
Configuring and Using Transparent Encryption
Configure and Use Oracle Secure Backup
Installing and Configuring Oracle Secure Backup
Using RMAN with Oracle Secure Backup
Monitor and Tune RMAN Performance
Monitoring RMAN Sessions and Jobs
Identifying Backup and Restore Steps
Understanding RMAN Multiplexing
Configure and Use Oracle Secure Backup
Part V Upgrades and Multitenant
Chapter 29 Upgrading Oracle Database Software and Migrating Data
Database Upgrade: DBUA or Manual
Describe Data Migration Methods
Migrate Data by Using Oracle Data Pump
Describe Data Migration Methods
Migrate Data by Using Oracle Data Pump
Chapter 30 Upgrading an Oracle Database
Describe Upgrade Requirements when Certain Features or Options Are Used in Oracle Database
Oracle Label Security and Oracle Data Vault
Use the Pre-Upgrade Information Tool Before Performing an Upgrade
Prepare the New Oracle Home Prior to Performing an Upgrade
Upgrade the Database to Oracle Database 12c by Using the Database Upgrade Assistant
Perform a Manual Upgrade to Oracle Database 12c by Using Scripts and Tools
Describe Upgrade Requirements when Certain Features or Options Are Used in Oracle Database
Use the Pre-Upgrade Information Tool Before Performing an Upgrade
Prepare the New Oracle Home Prior to Performing an Upgrade
Upgrade the Database to Oracle Database 12c by Using the Database Upgrade Assistant
Perform a Manual Upgrade to Oracle Database 12c by Using Scripts and Tools
Chapter 31 Multitenant Container and Pluggable Database Architecture
Describe the Multitenant Container Database Architecture
The CDB and the Root Container
Explain Pluggable Database Provisioning
Techniques for Provisioning Pluggable Containers
Communications Between Containers
Describe the Multitenant Container Database Architecture
Explain Pluggable Database Provisioning
Chapter 32 Creating and Managing Multitenant Container and Pluggable Databases
Using SQL*Plus to Create a CDB
Using PDB$SEED to Create a New PDB
Cloning a PDB to Create a New PDB
Plug an Unplugged PDB into a CDB
Migrate a Pre-12.1 Non-CDB Database to CDB
Establish Connections to CDBs and PDBs
Understanding CDB and PDB Service Names
Creating Services for CDBs or PDBs
Switching Connections Within a CDB
Start Up and Shut Down a CDB and Open and Close PDBs
Evaluate the Impact of Parameter Value Changes
Understanding the Scope of Parameter Changes
Using ALTER SYSTEM in a Multitenant Environment
Migrate a Pre-12.1 Non-CDB Database to CDB
Establish Connections to CDBs and PDBs
Start Up and Shut Down a CDB and Open and Close PDBs
Evaluate the Impact of Parameter Value Changes
Chapter 33 Managing Storage, Security, Availability, and Performance in a CDB and PDBs
Space Management in a Multitenant Environment
Default Permanent and Temporary Tablespaces in a PDB
Manage Common and Local Users, Roles, and Privileges
Grant Privileges Commonly and Locally
Enable Common Users to Access Data in Specific PDBs
Audit in the Multitenant Environment
Multitenant Backup and Recovery
Back Up a CDB and Individual PDBs
Mulitenant Performance Monitoring and Tuning
Manage Allocation of Resources Between PDBs and Within a PDB
Using Shares to Manage Inter-PDB Resources
Creating and Modifying Resource Manager Plans
Capture Source Database Workloads
Process Workloads on the Target System
Replay Workloads on Target CDB: Consolidated Replay
Manage Permanent and Temporary Tablespaces in CDB and PDBs
Manage Common and Local Privileges
Enable Common Users to Access Data in Specific PDBs
Perform Backups of a CDB and PDBs
Recover PDB from PDB Datafiles Loss
Monitor Operations and Performance in a CDB and PDBs
Manage Allocation of Resources Between PDBs and Within a PDB