CONTENUTI

1: Introduction to Oracle Database

• Objectives 1-2
• Oracle Database Server Architecture: Overview 1-3
• Oracle Database Instance Configurations 1-4
• Oracle Multitenant Container Database: Introduction 1-5
• Oracle Multitenant Container Database: Architecture 1-7
• Oracle Database Memory Structures 1-8
• Shared Pool 1-10
• Database Buffer Cache 1-12
• Redo Log Buffer 1-13
• Large Pool 1-14
• Java Pool and Streams Pool 1-15
• Program Global Area (PGA) 1-16
• Process Architecture 1-17
• Process Structures 1-18
• Database Writer Process (DBWn) 1-20
• Log Writer Process (LGWR) 1-22
• Checkpoint Process (CKPT) 1-24
• System Monitor Process (SMON) 1-25
• Process Monitor Process (PMON) 1-26
• Recoverer Process 1-27
• Archiver Processes (ARCn) 1-28
• Database Sharding: Introduction 1-29
• Oracle Database Server: Interactive Architecture Diagram 1-30
• Summary 1-31

 

2: Accessing an Oracle Database

• Objectives 2-2
• Connecting to an Oracle Database Instance 2-3
• Oracle Database Tools 2-5
• Database Tool Choices 2-7
• SQL*Plus 2-8
• Oracle SQL Developer 2-10
• Oracle SQL Developer: Connections 2-11
• Oracle SQL Developer: DBA Actions 2-12
• Database Configuration Assistant (DBCA) 2-13
• Oracle Enterprise Manager Database Express 2-14
• Enterprise Manager Cloud Control 13c Features 2-16
• Oracle Enterprise Manager Component Overview 2-18
• Single Pane of Glass for Enterprise Management 2-19
• Oracle Enterprise Manager Database Management 2-20
• Summary 2-22

 

3: Creating an Oracle Database by Using DBCA

• Objectives 3-2
• Planning the Database 3-3
• Choosing a Database Template 3-4
• Choosing the Appropriate Character Set 3-5
• How are character sets used? 3-7
• Setting NLS_LANG Correctly on the Client 3-8
• Using the Database Configuration Assistant 3-9
• Using DBCA in Silent Mode 3-10
• Summary 3-11

 

4: Creating an Oracle Database by Using a SQL Command

• Objectives 4-2
• Creating a Container Database (CDB) 4-3
• Creating a CDB by Using a SQL Command: Example 4-4
• Using the SEED FILE_NAME_CONVERT Clause 4-5
• Using the ENABLE PLUGGABLE DATABASE Clause 4-6
• Summary 4-7

 

5: Starting Up and Shutting Down a Database Instance

• Objectives 5-2
• Starting the Oracle Database Instance 5-3
• Shutting Down an Oracle Database Instance 5-4
• Comparing SHUTDOWN Modes 5-6
• Opening and Closing PDBs 5-8
• Configuring PDBs to Automatically Open 5-9
• Summary 5-10

 

6: Managing Database Instances

• Objectives 6-2
• Working with Initialization Parameters 6-3
• Initialization Parameters 6-5
• Modifying Initialization Parameters 6-7
• Viewing Initialization Parameters 6-10
• Working with the Automatic Diagnostic Repository 6-12
• Automatic Diagnostic Repository 6-13
• Viewing the Alert Log 6-14
• Using Trace Files 6-16
• Administering the DDL Log File 6-18
• Querying Dynamic Performance Views 6-20
• Considerations for Dynamic Performance Views 6-22
• Data Dictionary: Overview 6-23
• Querying the Oracle Data Dictionary 6-24
• Summary 6-26

 

7: Oracle Net Services: Overview

• Objectives 7-2
• Connecting to the Database Instance 7-3
• Oracle Net Services: Overview 7-4
• Defining Oracle Net Services Components 7-5
• Tools for Configuring and Managing Oracle Net Services 7-6
• Oracle Net Listener: Overview 7-7
• The Default Listener 7-8
• Comparing Dedicated and Shared Server Architecture 7-9
• Summary 7-10

 

8: Configuring Naming Methods

• Objectives 8-2
• Establishing Oracle Network Connections 8-3
• Connecting to an Oracle Database Instance 8-4
• Name Resolution 8-5
• Establishing a Connection 8-6
• User Sessions 8-7
• Naming Methods 8-8
• Easy Connect 8-9
• Local Naming 8-10
• Directory Naming 8-11
• Using Database Services to Manage Workloads 8-12
• Creating Database Services 8-13
• Summary 8-14

 

9: Configuring and Administering the Listener

• Objectives 9-2
• Review: Oracle Net Services Overview 9-3
• Oracle Net Listener: Overview 9-4
• The Default Listener 9-5
• Configuring Dynamic Service Registration 9-6
• Configuring Static Service Registration 9-8
• Summary 9-10

 

10: Configuring a Shared Server Architecture

• Objectives 10-2
• Shared Server Architecture: Overview 10-3
• Comparing Dedicated and Shared Server Architecture: Review 10-4
• Enabling Shared Server 10-5
• Controlling Shared Server Operations 10-6
• SGA and PGA Usage 10-7
• Shared Server Configuration Considerations 10-8
• Summary 10-9
• Practice Overview 10-10

 

11: Creating PDBs from Seed

• Objectives 11-2
• Provisioning New Pluggable Databases 11-3
• Tools 11-4
• Creating a New PDB from PDB$SEED 11-5
• Using the FILE_NAME_CONVERT Clause 11-6
• Using OMF or the PDB_FILE_NAME_CONVERT Parameter 11-7
• Summary 11-8

 

12: Using Other Techniques to Create PDBs

• Objectives 12-2
• Cloning Regular PDBs 12-3
• Migrating Data from a Non-CDB into a CDB 12-4
• Plugging a Non-CDB into CDB Using DBMS_PDB 12-5
• Replicating a Non-CDB into a CDB by Using GoldenGate 12-6
• Cloning a Non-CDB or Remote PDB 12-7
• Using DBCA to Clone a Remote PDB 12-8
• Plugging an Unplugged Regular PDB into CDB 12-9
• Plugging in a PDB Using an Archive File 12-10
• Cloning Remote PDBs in Hot Mode 12-11
• Near-Zero Downtime PDB Relocation 12-12
• Using DBCA to Relocate a Remote PDB 12-14
• Proxy PDB: Query Across CDBs Proxying Root Replica 12-15
• Creating a Proxy PDB 12-16
• Summary 12-17

 

13: Managing PDBs

• Objectives 13-2
• Changing the PDB Mode 13-3
• Modifying PDB Settings 13-4
• Impact of Changing Initialization Parameters 13-5
• Changing Initialization Parameters: Example 13-6
• Using the ALTER SYSTEM Command in a PDB 13-7
• Configuring Host Name and Port Number per PDB 13-8
• Dropping PDBs 13-9
• Summary 13-10

 

14: Database Storage Overview

• Objectives 14-2
• Database Storage Architecture 14-3
• Logical and Physical Database Structures 14-5
• Segments, Extents, and Blocks 14-7
• Tablespaces and Data Files 14-8
• Default Tablespaces in a Multitenant Container Database 14-9
• SYSTEM and SYSAUX Tablespaces 14-10
• Types of Segments 14-11
• How Table Data Is Stored 14-12
• Database Block Content 14-13
• Understanding Deferred Segment Creation 14-14
• Controlling Deferred Segment Creation 14-15
• Monitoring Tablespace Space Usage 14-16
• Summary 14-17

 

15: Creating and Managing Tablespaces

• Objectives 15-2
• Creating Tablespaces 15-3
• Creating a Tablespace: Clauses 15-4
• Creating Permanent Tablespaces in a CDB 15-7
• Defining Default Permanent Tablespaces 15-8
• Temporary Tablespaces 15-9
• Altering and Dropping Tablespaces 15-10
• Viewing Tablespace Information 15-12
• Implementing Oracle Managed Files (OMF) 15-13
• Enlarging the Database 15-15
• Moving or Renaming Online Data Files 15-16
• Examples: Moving and Renaming Online Data Files 15-17
• Summary 15-18

 

16: Improving Space Usage

• Objectives 16-2
• Space Management Features 16-3
• Block Space Management 16-4
• Row Chaining and Migration 16-5
• Free Space Management Within Segments 16-6
• Allocating Extents 16-7
• Using Unusable Indexes 16-8
• Using Temporary Tables 16-9
• Creating Global Temporary Tables 16-10
• Creating Private Temporary Tables 16-11
• Table Compression: Overview 16-12
• Table Compression: Concepts 16-13
• Compression for Direct-Path Insert Operations 16-14
• Advanced Row Compression for DML Operations 16-15
• Specifying Table Compression 16-16
• Using the Compression Advisor 16-17
• Resolving Space Usage Issues 16-18
• Reclaiming Space by Shrinking Segments 16-19
• Shrinking Segments 16-20
• Results of a Shrink Operation 16-21
• Managing Resumable Space Allocation 16-22
• Using Resumable Space Allocation 16-23
• Resuming Suspended Statements 16-25
• What operations are resumable? 16-27
• Summary 16-28

 

17: Managing Undo Data

• Objectives 17-2
• Undo Data: Overview 17-3
• Transactions and Undo Data 17-5
• Storing Undo Information 17-6
• Comparing Undo Data and Redo Data 17-7
• Managing Undo 17-8
• Comparing SHARED Undo Mode and LOCAL Undo Mode 17-9
• Configuring Undo Retention 17-10
• Categories of Undo 17-11
• Guaranteeing Undo Retention 17-12
• Changing an Undo Tablespace to a Fixed Size 17-13
• Temporary Undo: Overview 17-14
• Temporary Undo Benefits 17-15
• Enabling Temporary Undo 17-16
• Monitoring Temporary Undo 17-17
• Summary 17-18

 

18: Creating and Managing User Accounts

• Objectives 18-2
• Database User Accounts 18-3
• Oracle-Supplied Administrator Accounts 18-5
• Creating Oracle Database Users in a Multitenant Environment 18-6
• Creating Common Users in the CDB and PDBs 18-7
• Creating Schema-Only Accounts 18-8
• Authenticating Users 18-9
• Using Password Authentication 18-11
• Using Password File Authentication 18-13
• Using OS Authentication 18-14
• OS Authentication for Privileged Users 18-16
• Assigning Quotas 18-17
• Summary 18-20

 

19: Configuring Privilege and Role Authorization

• Objectives 19-2
• Privileges 19-3
• System Privileges 19-4
• System Privileges for Administrators 19-6
• Schema-Level Privileges 19-7
• New Developer Role and Simplified Schema Privileges 19-13
• Object Privileges 19-14
• Granting Privileges in a Multitenant Environment 19-15
• Granting Privileges: Example 19-16
• Using Roles to Manage Privileges 19-17
• Assigning Privileges to Roles and Assigning Roles to Users 19-18
• Oracle-Supplied Roles 19-19
• Granting Roles in a Multitenant Environment 19-20
• Granting Roles: Example 19-21
• Making Roles More Secure 19-22
• Revoking Roles and Privileges 19-23
• Granting and Revoking System Privileges 19-24
• Granting and Revoking Object Privileges 19-25
• Summary 19-26

 

20: Configuring User Resource Limits

• Objectives 20-2
• Profiles and Users 20-3
• Creating Profiles in a Multitenant Architecture 20-4
• Creating Profiles: Example 20-5
• Profile Parameters: Resources 20-6
• Profile Parameters: Locking and Passwords 20-9
• Oracle-Supplied Password Verification Functions 20-12
• Assigning Profiles in a Multitenant Architecture 20-13
• Summary 20-14

 

21: Implementing Oracle Database Auditing

• Objectives 21-2
• Database Security 21-3
• Monitoring for Compliance 21-5
• Types of Activities to be Audited 21-6
• Mandatorily Audited Activities 21-7
• Understanding Auditing Implementation 21-8
• Administering the Roles Required for Auditing 21-9
• Database Auditing: Overview 21-10
• Configuring Auditing 21-11
• Creating a Unified Audit Policy 21-12
• Creating an Audit Policy: Systemwide Audit Options 21-13
• Creating an Audit Policy: Object-Specific Actions 21-14
• Creating an Audit Policy: Specifying Conditions 21-15
• Enabling and Disabling Audit Policies 21-16
• Auditing Actions in the CDB and PDBs 21-17
• Modifying a Unified Audit Policy 21-19
• Auditing Top-Level Statements Only 21-20
• Viewing Audit Policy Information 21-21
• Value-Based Auditing 21-22
• Fine-Grained Auditing 21-24
• FGA Policy 21-25
• Audited DML Statements: Considerations 21-27
• FGA Guidelines 21-28
• Archiving and Purging the Audit Trail 21-29
• Purging Audit Trail Records 21-30
• Summary 21-31

 

22: Introduction to Loading and Transporting Data

• Objectives 22-2
• Moving Data: General Architecture 22-3
• Oracle Data Pump: Overview 22-4
• Oracle Data Pump: Benefits 22-5
• SQL Loader: Overview 22-7
• Summary 22-9

 

23: Loading Data

• Objectives 23-2
• SQL Loader: Review 23-3
• Creating the SQL*Loader Control File 23-4
• SQL*Loader Loading Methods 23-6
• Protecting Against Data Loss 23-7
• SQL*Loader Express Mode 23-8
• Using SQL*Loader to Load a Table in a PDB 23-9
• Summary 23-10

 

24: Transporting Data

• Objectives 24-2
• Data Pump Export and Import Clients 24-3
• Data Pump Interfaces and Modes 24-4
• Data Pump Import Transformations 24-6
• Using Oracle Data Pump with PDBs 24-7
• Exporting from a Non-CDB and Importing into a PDB 24-8
• Exporting and Importing Between PDBs 24-9
• Full Transportable Export/Import 24-10
• Full Transportable Export/Import: Example 24-12
• Transporting a Database Over the Network: Example 24-13
• Using RMAN to Transport Data Across Platforms 24-14
• RMAN CONVERT Command 24-15
• Transporting Data with Minimum Down Time 24-16
• Transporting a Tablespace by Using Image Copies 24-17
• Determining the Endian Format of a Platform 24-18
• Transporting Data with Backup Sets 24-19
• Transporting a Tablespace 24-20
• Transporting Inconsistent Tablespaces 24-22
• Summary 24-23

 

25: Using External Tables to Load and Transport Data

• Objectives 25-2
• External Tables 25-3
• External Tables: Benefits 25-4
• ORACLE_LOADER Access Driver 25-5
• ORACLE_DATAPUMP Access Driver 25-6
• External Tables 25-7
• Viewing Information About External Tables 25-8
• Summary 25-9
• Practice Overview 25-10

 

26: Automated Maintenance Tasks: Overview

• Objectives 26-2
• Proactive Database Maintenance Infrastructure 26-3
• Automated Maintenance Tasks: Components 26-4
• Predefined Automated Maintenance Tasks 26-6
• Maintenance Windows 26-8
• Predefined Maintenance Windows 26-9
• Automated Maintenance Tasks 26-10
• Summary 26-11

 

27: Automated Maintenance Tasks: Managing Tasks and Windows

• Objectives 27-2
• Configuring Automated Maintenance Tasks 27-3
• Enabling and Disabling Maintenance Tasks 27-4
• Creating and Managing Maintenance Windows 27-5
• Resource Allocations for Automated Maintenance Tasks 27-6
• Changing Resource Allocations for Maintenance Tasks 27-7
• Summary 27-8
• Practice Overview 27-9

 

28: Database Monitoring and Tuning Performance Overview

• Objectives 28-2
• Performance Management Activities 28-3
• Performance Planning Considerations 28-4
• Database Maintenance 28-6
• Automatic Workload Repository (AWR) 28-7
• Automatic Database Diagnostic Monitor (ADDM) 28-8
• Configuring Automatic ADDM Analysis at the PDB Level 28-9
• Advisory Framework 28-10
• Performance Tuning Methodology 28-12
• Summary 28-13

 

29: Monitoring Database Performance

• Objectives 29-2
• Server-Generated Alerts 29-3
• Setting Metric Thresholds 29-4
• Reacting to Alerts 29-5
• Alert Types and Clearing Alerts 29-6
• Database Server Statistics and Metrics 29-7
• Performance Monitoring 29-8
• Viewing Statistics Information 29-9
• Monitoring Wait Events 29-11
• Monitoring Sessions 29-12
• Monitoring Services 29-13
• Summary 29-14

 

30: Analyzing SQL and Optimizing Access Paths

• Objectives 30-2
• SQL Tuning Process 30-3
• Oracle Optimizer 30-4
• Optimizer Statistics 30-5
• Optimizer Statistics Collection 30-6
• Setting Optimizer Statistics Preferences 30-8
• Optimizer Statistics Advisor 30-10
• Optimizer Statistics Advisor Report 30-11
• Executing Optimizer Statistics Advisor Tasks 30-12
• SQL Plan Directives 30-13
• Adaptive Execution Plans 30-14
• SQL Tuning Advisor: Overview 30-16
• SQL Access Advisor: Overview 30-18
• SQL Performance Analyzer: Overview 30-19
• Managing Automated Tuning Tasks 30-21
• Summary 30-22