Go to main content
1/19
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documentation
Conventions
Changes in This Release for Oracle Database 2 Day DBA
Changes in Oracle Database 12
c
Release 2 (12.2.0.1)
New Features
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
New Features
1
Introduction
1.1
About This Guide
1.1.1
What This Guide Is Not
1.1.2
How to Use This Guide with Related Material
1.2
About Oracle Database
1.3
Common Oracle DBA Tasks
1.4
Tools for Administering the Database
2
Installing Oracle Database and Creating a Database
2.1
Overview of Installing Oracle Database Software and Creating a Database
2.1.1
Checking Oracle Database Installation Prerequisites
2.1.2
Deciding on Oracle Database Installation Choices
2.1.2.1
Install Option for Oracle Database
2.1.2.2
Installation Class for Oracle Database
2.1.2.3
Installation Edition for Oracle Database
2.1.2.4
Software Installation Directories for Oracle Database
2.1.2.5
Database File Location for Oracle Database
2.1.2.6
Database Identifiers for Oracle Database
2.1.2.7
About Advanced Installation for Oracle Database
2.2
Installing Oracle Database Software
2.3
Creating and Managing a Database with DBCA
2.3.1
Starting DBCA
2.3.2
Creating a Database Using DBCA
2.3.2.1
DBCA Creation Mode Window
2.3.2.2
DBCA Deployment Type Window
2.3.2.3
DBCA Database Identification Window
2.3.2.4
DBCA Storage Option Window
2.3.2.5
DBCA Fast Recovery Option Window
2.3.2.6
DBCA Network Configuration Window
2.3.2.7
DBCA Database Vault Option Window
2.3.2.8
DBCA Configuration Options Window
2.3.2.8.1
Memory
2.3.2.8.2
Sizing
2.3.2.8.3
Character Sets
2.3.2.8.4
Connection mode
2.3.2.8.5
Sample schema
2.3.2.9
DBCA Management Options Window
2.3.2.10
DBCA User Credentials Window
2.3.2.11
DBCA Creation Option Window
2.3.2.12
DBCA Summary Window
2.3.2.13
DBCA Progress Window
2.3.2.14
DBCA Finish Window
2.3.3
Changing the Configuration of a Database Using DBCA
2.3.4
Deleting a Database Using DBCA
2.3.5
Managing Templates with DBCA
2.3.5.1
Advantages of Using DBCA Templates
2.3.5.2
Types of DBCA Templates
2.3.5.3
DBCA Templates Provided by Oracle
2.3.5.4
Creating Templates Using DBCA
2.3.5.5
Deleting DBCA Templates
2.3.6
Using DBCA to Manage PDBs
2.3.6.1
Managing PDBs in a CDB using DBCA
2.4
Manually Installing the Database Sample Schemas Post-Installation
2.5
Installation: Oracle By Example Series
3
Getting Started with Database Administration
3.1
Managing Your Database: An Overview
3.2
Configuring the Operating System Environment Variables
3.3
Introduction to Oracle Enterprise Manager Database Express
3.4
Starting EM Express
3.4.1
Starting EM Express for a Non-CDB
3.4.2
Starting EM Express for a CDB
3.4.3
Starting EM Express for a PDB
3.5
Configuring the HTTPS Port for EM Express
3.6
Accessing the Database Home Page
3.7
Granting Access to EM Express for Nonadministrative Users
3.8
Administering the Database with SQL-Based Management Tools
3.8.1
About SQL
3.8.2
About SQL*Plus
3.8.3
Starting SQL*Plus and Connecting to the Database
3.8.4
About SQL Developer
3.9
Getting Started with Database Administration: Oracle by Example Series
4
Configuring the Network Environment
4.1
Understanding Network Configuration
4.1.1
Oracle Net Listener Configuration
4.1.2
Client Connections
4.1.3
Tools for Network Configuration
4.2
Viewing Listener Configuration
4.3
Starting and Stopping the Listener
4.4
Connecting to an Oracle Database from a Client Computer
4.5
Configuring the Network Environment: Oracle by Example Series
5
Managing the Oracle Instance
5.1
Overview of the Oracle Instance and Instance Management
5.1.1
About Initialization Parameters
5.1.2
About Background Processes
5.1.3
About Server and Client Processes
5.1.4
About Instance Memory Structures
5.1.4.1
System Global Area
5.1.4.2
Program Global Area
5.1.5
About Instance Startup and Shutdown
5.1.5.1
About Administration Privileges for Startup and Shutdown
5.1.5.2
About Instance Startup
5.1.5.3
About Instance Shutdown
5.2
Shutting Down and Starting Up the Oracle Instance
5.2.1
Shutting Down and Starting Up Using SQL*Plus
5.2.2
Shutting Down and Starting Up Using the Windows Services Program
5.3
Viewing and Modifying Initialization Parameters
5.4
Managing Memory
5.4.1
About Memory Management
5.4.2
Enabling Automatic Memory Management
5.4.3
Modifying Memory Settings – Automatic Memory Management
5.4.4
Enabling Automatic Shared Memory Management
5.4.5
Modifying Memory Settings – Automatic Shared Memory Management
5.4.6
Enabling Manual Shared Memory Management
5.4.7
Modifying Memory Settings - Manual Shared Memory Management
5.4.8
Modifying Memory Settings – Automatic PGA Memory Management
5.5
Managing the Oracle Instance: Oracle By Example Series
6
Managing Database Storage Structures
6.1
About Database Storage Structures
6.1.1
About Control Files
6.1.2
About Online Redo Log Files
6.1.3
About Archived Redo Log Files
6.1.4
About Rollback Segments
6.1.5
About Data Files
6.1.6
About Tablespaces
6.1.6.1
Locally Managed Tablespaces
6.1.6.2
Tablespace Types
6.1.6.3
Tablespace Status
6.1.6.4
Autoextend Tablespace
6.1.6.5
Encrypted Tablespaces
6.1.7
About Other Storage Structures
6.2
Viewing Database Storage Structure Information
6.2.1
Viewing Control File Information
6.2.2
Viewing Online Redo Log File Information
6.2.3
Viewing Archived Redo Log File Information
6.2.4
Viewing Tablespace and Data File Information
6.3
Performing Common Database Storage Tasks
6.3.1
Creating a Tablespace
6.3.2
Modifying a Tablespace
6.3.2.1
Setting a Tablespace to Automatically Extend
6.3.2.2
Setting the Datafile for a Smallfile Tablespace to Automatically Extend
6.3.2.3
Taking a Tablespace Offline
6.3.3
Dropping a Tablespace
6.4
Managing the Online Redo Log
6.4.1
Multiplexing the Online Redo Log
6.4.2
Switching a Log File
6.5
Managing Undo Data
6.5.1
About Undo Data
6.5.2
About Managing Undo Data
6.5.3
Viewing Undo Information
6.5.4
Computing the Minimum Undo Tablespace Size Using the Undo Advisor
6.5.5
Changing the Undo Tablespace to a Fixed Size
6.5.6
Changing the Datafiles for an Undo Tablespace to a Fixed Size
6.5.7
Changing Undo Management Analysis Parameters
6.5.8
Switching Undo Tablespaces
6.6
Managing Database Storage Structures: Oracle by Example Series
7
Administering User Accounts and Security
7.1
About User Accounts
7.1.1
About Commonality in a CDB
7.1.1.1
Common Users in a CDB
7.1.1.2
Local Users in a CDB
7.2
About User Privileges and Roles
7.2.1
About Common and Local Roles in a CDB
7.2.1.1
Common Roles in a CDB
7.2.1.2
Local Roles in a PDB
7.2.2
About Privilege and Role Grants in a CDB
7.2.2.1
Privileges and Roles Granted Commonly in a CDB
7.2.2.2
Privileges and Roles Granted Locally in a CDB
7.3
About Administrative Accounts and Privileges
7.3.1
SYS and SYSTEM Users
7.3.2
SYSDBA and SYSOPER System Privileges
7.4
Administering Roles
7.4.1
Viewing Roles
7.4.2
Example: Creating a Role
7.4.3
Example: Modifying a Role
7.4.4
Deleting a Role
7.5
Administering Database User Accounts
7.5.1
Viewing User Accounts
7.5.2
Example: Creating a User Account
7.5.3
Creating a New User Account by Duplicating an Existing User Account
7.5.4
Example: Granting Privileges and Roles to a User Account
7.5.5
Example: Assigning a Tablespace Quota to a User Account
7.5.6
Example: Modifying a User Account
7.5.7
Locking and Unlocking User Accounts
7.5.8
Expiring a User Password
7.5.9
Example: Deleting a User Account
7.6
Setting the Database Password Policy
7.6.1
About Password Policies
7.6.2
Modifying the Default Password Policy
7.7
Administering User Accounts and Security: Oracle by Example Series
8
Managing Schema Objects
8.1
About Schema Objects
8.2
About Schema Object Management Privileges
8.3
About SQL Developer
8.3.1
Installing and Starting SQL Developer
8.3.2
Understanding the SQL Developer User Interface
8.3.3
Creating a Database Connection Using SQL Developer
8.4
Managing Tables
8.4.1
About Tables
8.4.1.1
About Table Types
8.4.1.2
About Table Column Attributes
8.4.1.2.1
Data Type
8.4.1.2.2
NOT NULL Column Constraint
8.4.1.2.3
Default Value
8.4.1.2.4
Encryption
8.4.1.3
About Table-Level Constraints
8.4.1.4
Other Table Creation Considerations
8.4.1.4.1
User-Defined Types and Large Objects (LOBs)
8.4.1.4.2
Partitioned Tables and Indexes
8.4.1.4.3
Physical Storage Attributes
8.4.1.4.4
Compressed Tables
8.4.2
Viewing Tables
8.4.3
Viewing Table Data
8.4.4
Example: Creating a Table
8.4.5
Modifying Table Attributes
8.4.5.1
Example: Adding Table Columns
8.4.5.2
Example: Deleting a Table Column
8.4.5.3
Example: Adding a New Table Constraint
8.4.5.4
Example: Modifying an Existing Table Constraint
8.4.5.5
Example: Deleting a Table Constraint
8.4.6
Example: Loading Data into a Table
8.4.7
Deleting a Table
8.5
Managing Indexes
8.5.1
About Indexes
8.5.1.1
Indexes and Performance
8.5.1.2
Index Attributes
8.5.2
Viewing Indexes
8.5.3
Example: Creating an Index
8.5.4
Example: Deleting an Index
8.6
Managing Views
8.6.1
About Views
8.6.2
Displaying Views
8.6.3
Example: Creating a View
8.6.4
Example: Deleting a View
8.7
Managing Program Code Stored in the Database
8.7.1
About Program Code Stored in the Database
8.7.2
Validating (Compiling) Invalid Schema Objects
8.8
Working with Other Schema Objects
8.9
Managing Schema Objects: Oracle by Example Series
9
Performing Backup and Recovery
9.1
Overview of Database Backup and Recovery
9.1.1
Overview of Backing Up and Recovering CDBs and PDBs
9.1.1.1
Backup and Complete Recovery of CDBs
9.1.1.2
Backup and Complete Recovery of PDBs
9.1.1.3
Point-in-Time Recovery of CDBs and PDBs
9.1.1.4
Flashback of CDBs and PDBs
9.2
Database Backup and Recovery Concepts
9.2.1
ARCHIVELOG and NOARCHIVELOG Mode
9.2.2
RMAN Repository
9.2.3
Image Copies and Backup Sets
9.2.4
Full Backups and Incremental Backups
9.2.5
Consistent and Inconsistent Backups
9.2.6
Media Recovery
9.2.7
Fast Recovery Area
9.3
Configuring Your Database for Basic Backup and Recovery
9.3.1
Planning Space Usage and Location for the Fast Recovery Area
9.3.1.1
About the Backup Retention Policy and the Fast Recovery Area
9.3.1.2
About the Fast Recovery Area Size
9.3.2
Configuring Users to Perform Backup and Recovery
9.3.2.1
Credentials Required to Perform Backup and Recovery
9.3.2.2
Granting the SYSBACKUP Privilege
9.3.3
Connecting to the Target Database Using RMAN
9.3.4
Configuring Recovery Settings
9.3.4.1
Configuring the Fast Recovery Area
9.3.4.2
Enabling Archiving of Redo Log Files
9.3.4.3
Enabling Flashback Database
9.3.5
Configuring Backup Settings
9.3.5.1
Configuring Backup Device Settings
9.3.5.2
Configuring Backup Policy Settings
9.3.5.3
Configuring Automatic Backups for the Control File and Server Parameter File
9.3.5.4
Enabling Block Change Tracking
9.4
Backing Up Your Database
9.4.1
Additional Backup Concepts
9.4.1.1
Incrementally Updated Backups: Rolling Forward Image Copies of Data Files
9.4.1.2
Backup Tags
9.4.2
Performing and Scheduling Backups Using RMAN
9.4.2.1
Performing a Whole Database Backup
9.4.2.2
Using the Oracle Suggested Backup Strategy
9.4.2.2.1
About the Oracle Suggested Backup Strategy
9.4.2.2.2
Task 1 - Preparing to Use the Oracle Suggested Backup Strategy
9.4.2.2.3
Task 2 - Creating the Backup Script—UNIX and Linux
9.4.2.2.4
Task 3 - Testing the Backup Script
9.4.2.2.5
Task 4 - Scheduling the Daily Backup—UNIX and Linux
9.4.2.3
About the Oracle Suggested Backup Strategy and Retention
9.4.2.4
Scheduling Miscellaneous Backup Tasks
9.4.3
Displaying Backups Stored in the RMAN Repository
9.4.4
Validating Backups and Testing Your Backup Strategy
9.4.4.1
Validating Selected Backups
9.4.4.2
Validating Backups for Restore Operations
9.5
Displaying Backup Reports
9.6
Managing Backups
9.6.1
About Backup Management
9.6.2
Cross-Checking Backups
9.6.3
Deleting Expired Backups
9.6.4
Marking Backups as Available or Unavailable
9.6.5
Deleting Obsolete Backups
9.6.6
Monitoring Fast Recovery Area Space Usage
9.7
Performing Oracle Advised Recovery
9.7.1
About Data Recovery Advisor
9.7.2
Using Data Recovery Advisor
9.8
Performing User-Directed Recovery
9.8.1
Rewinding a Table Using Oracle Flashback Table
9.8.1.1
Enabling Row Movement on a Table
9.8.1.2
Performing a Flashback Table Operation
9.8.2
Recovering a Dropped Table Using Oracle Flashback Drop
9.8.2.1
Dropping a Table
9.8.2.2
Retrieving a Dropped Table
9.8.3
Rewinding a Database Using Oracle Flashback Database
9.8.4
Restoring and Recovering the Database
9.9
Performing Backup and Recovery: Oracle By Example Series
10
Monitoring and Tuning the Database
10.1
Proactive Database Monitoring
10.1.1
Monitoring General Database State and Workload
10.1.2
Monitoring Performance Using the Performance Hub
10.1.2.1
Specifying the Time Period for Which to Display Statistics
10.1.3
About Viewing Performance Statistics on a Standby Database
10.1.4
Performance Self-Diagnostics: Automatic Database Diagnostic Monitor
10.2
Diagnosing Performance Problems Using ADDM
10.2.1
Viewing a Summary of ADDM Performance Findings
10.2.2
Responding to ADDM Performance Findings
10.2.3
Viewing a Summary of Real-Time ADDM Findings
10.2.4
Responding to Real-Time ADDM Findings
10.3
Using Advisors to Optimize Database Performance
10.3.1
About Advisors
10.3.2
About the SQL Tuning Advisor
10.3.3
About the Automatic SQL Tuning Advisor
10.3.4
Configuring the Automatic SQL Tuning Advisor
10.3.5
Viewing Automatic SQL Tuning Results
10.3.6
Running the SQL Tuning Advisor
10.3.7
About Tuning SQL Statements on a Standby Database
10.3.7.1
Tuning SQL Statements on a Standby Database
10.3.8
Optimizing Memory Usage with the Memory Advisors
10.3.8.1
About the Memory Advisors
10.4
Monitoring and Tuning the Database: Oracle by Example Series
11
Analyzing the Impact of Database Changes on SQL Performance
11.1
About SPA Quick Check
11.2
About Configuring SPA Quick Check
11.2.1
About SQL Tuning Sets
11.2.2
Specifying Default Values for SPA Quick Check
11.3
About Validating the Impact of an Initialization Parameter Change
11.3.1
Validating the Impact of an Initialization Parameter Change
11.4
About Validating the Impact of Implementing a SQL Profile
11.4.1
Validating the Impact of Implementing a SQL Profile
11.4.2
About Validating the Impact of Implementing Multiple SQL Profiles
11.4.3
Validating the Impact of Implementing Multiple SQL Profiles
11.5
About SQL Performance Analyzer
11.6
Specifying Default Settings for SQL Performance Analyzer
11.6.1
Creating a SQL Performance Analyzer Task
11.6.2
Creating a Pre-Change SQL Trial
11.6.3
Creating a Post-Change SQL Trial
11.6.4
Comparing Two SQL Trials
11.7
Analyzing the Impact of Database Changes on SQL Performance: Oracle by Example Series
12
Managing Database Resources
12.1
About Resource Management
12.1.1
About Resource Manager Solutions for a Non-CDB
12.1.2
About Resource Manager Solutions for a CDB
12.1.3
About Consumer Groups
12.1.4
About Plans
12.1.5
About Directives
12.1.6
About Non-CDB Plans
12.1.7
About Managing CDB and PDB Workloads
12.1.7.1
About CDB Plans
12.1.7.1.1
About Shares for Allocating Resources to PDBs
12.1.7.1.2
About Utilization Limits for PDBs
12.1.7.1.3
About the Default Directive for PDBs
12.1.7.2
About PDB Plans
12.1.8
Accessing the Resource Management Page
12.2
Managing Plans
12.2.1
Creating Plans
12.2.1.1
Creating a New Plan using Quick Setup
12.2.1.2
Creating a New Plan
12.2.2
Viewing Plans
12.2.2.1
Viewing All the Plans
12.2.2.2
Viewing a Plan
12.2.3
Modifying a Plan
12.2.3.1
Adding a New Consumer Group to a Plan
12.2.3.2
Adding an Existing Consumer Group to a Plan
12.2.3.3
Removing a Consumer Group from a Plan
12.2.3.4
Setting a Comment for a Consumer Group in a Plan
12.2.3.5
Setting Mapping Rules for a Consumer Group
12.2.3.6
Setting Mapping Rule Priorities for Consumer Groups
12.2.3.7
Setting CPU Directives for a Consumer Group in a Plan
12.2.3.8
Setting Parallel Server Directives for a Consumer Group in a Plan
12.2.3.9
Setting Runaway Query Directives for a Consumer Group in a Plan
12.2.3.10
Setting Session Directives for a Consumer Group in a Plan
12.2.3.11
Setting a Plan as the Currently Active Plan
12.2.3.12
Changing the Default PDB Directive for a CDB Plan
12.2.3.13
Changing Directives for a PDB in a CDB Plan
12.2.3.14
Setting a Comment for a PDB Directive in a CDB Plan
12.2.3.15
Deleting a Plan
12.3
Managing Multiple Database Instances on a Single Server
12.3.1
About Instance Caging
12.3.2
Setting Instance Caging
12.4
Managing Database Resources: Oracle by Example Series
13
Managing PDBs with EM Express
13.1
Getting Started With Managing PDBs Using EM Express
13.2
Overview of CDBs and PDBs
13.3
About PDBs That Can be Managed in EM Express
13.4
Accessing the Containers Page for a CDB
13.5
Accessing the Containers Page for an Application Root
13.6
Setting Storage Limits for a PDB Using EM Express
13.7
Configuring Oracle Managed Files for a CDB Using EM Express
13.8
Provisioning a PDB Using EM Express
13.8.1
Creating a New PDB from the Seed Using EM Express
13.8.2
Creating a PDB by Cloning a PDB in the Same CDB Using EM Express
13.8.3
Plugging in an Unplugged PDB Using EM Express
13.8.4
About Remote PDB Cloning, Relocating PDBs, and Refreshing PDBs
13.8.4.1
Creating a PDB by Cloning a PDB from a Remote CDB Using EM Express
13.8.4.2
Relocating a Remote PDB
13.8.4.3
Refreshing a PDB on Demand
13.9
Removing PDBs Using EM Express
13.9.1
Unplugging a PDB Using EM Express
13.9.2
Dropping a PDB Using EM Express
13.10
Opening PDBs Using EM Express
13.10.1
Opening a PDB Using EM Express
13.10.2
Opening All the PDBs in a CDB Using EM Express
13.11
Closing PDBs Using EM Express
13.11.1
Closing a PDB Using EM Express
13.11.2
Closing All the PDBs in a CDB Using EM Express
13.12
Managing PDBs with EM Express: Oracle by Example Series
14
Managing Oracle Database Software
14.1
About Software Management and Patch Releases
14.2
Upgrading a Database
14.3
Removing Oracle Database Software
14.4
Managing Oracle Database Software: Oracle by Example Series
Index
Scripting on this page enhances content navigation, but does not change the content in any way.