CHAPTER 1 INTRODUCTION

COMPONENTS OF AN ORACLE DATABASE

DUTIES OF A DATABASE ADMINISTRATOR

DBA MANAGEMENT TOOLS

REVIEW OF THE SAMPLE SCHEMA

• The Taxes Table
• The ZipCodes Table
• The Jobs Table
• The Departments Table
• The Employees Table
• Objects In The EL Model
• Analysis
• Join Conditions

SQL*PLUS

• Starting SQL*Plus
• SQL Commands
• PL/SQL Commands
• SQL*Plus Commands
• Login.sql (login.sql In Linux / UNIX)

SQL DEVELOPER

A BRIEF OVERVIEW OF SQL

WHAT GENERATION?

WHAT SUBLANGUAGES ARE SUPPORTED?

KEY SINGLE ROW FUNCTIONS

KEY GROUP FUNCTIONS

KEY ANALYTICAL FUNCTIONS

OTHER AVAILABLE ANALYTICAL TOOLS

HELP ME!

OBJECT NAMING

WHAT ARE THE KEY DATATYPES?

WHAT CONSTRAINTS ARE AVAILABLE FOR TABLE DEFINITIONS?

HOW DO YOU SELECT FROM MULTIPLE TABLES?

WHAT IS A SELF JOIN?

WHAT ELSE CAN YOU DO WITH A SELF REFERENCE?

NEW SQL FEATURES OF NOTE IN ORACLE12C

• Top N And Bottom N Queries
• Using Extended Datatypes
• DEFAULT Values
• IDENTITY Columns
• Invisible Columns
• Temporal Validity
• Pattern Matching
• Creating Multiple Indexes On Columns
• Miscellaneous New SQL Features

A BRIEF OVERVIEW OF PL/SQL

NEW PL/SQL FEATURES OF NOTE IN ORACLE12C

• White Lists
• The WITH Clause For Functions
• PRAGMA UDF
• New Inquiry Directives
• New DBMS_SQL Functionality
• Enhanced Language Interface Features
• Miscellaneous New PL/SQL Features For Oracle12c

LAB 1: INTRODUCTION

LAB 1 SOLUTIONS: INTRODUCTION

CHAPTER 2 CREATING ORACLE DATABASES

OVERVIEW OF DATABASE CREATION

THE ORACLE UNIVERSAL INSTALLER

PREREQUISE TASKS

• Getting On The Server
• Memory Requirements
• Hardware Requirements
• Software Requirements
• Kernel Requirements
• Resource Limits
• Package Requirements
• Required Users And Groups
• Required Directories
• What About Windows?

RUNNING THE INSTALLER

POST INSTALLATION

• Environment

USING THE DATABASE CONFIGURATION ASSISTANT

DATABASE TEMPLATES

DEPRECATIONS AND DESUPPORTS FOR 18C

LAB 2: CREATING ORACLE DATABASES

LAB 2 SOLUTIONS: CREATING ORACLE DATABASES

CHAPTER 3 BASIC DATABASE ADMINISTRATION

A BRIEF ROADMAP

• Basic Database Administration
• Oracle Networking
• Instance Management
• Storage Management
• Oracle Security
• Schema Objects
• Backup And Recovery
• Oracle Advisors
• Problem Resolution
• Software Management
• Automated Storage

DATABASE MANAGEMENT USING EMDE

ENVIRONMENTAL VARIABLES

ACCESSING ENTERPRISE MANAGER DATABASE EXPRESS

• Enterprise Manager Database Express Access For Non-DBA Users

DATABASE ADMINISTRATION WITH OTHER TOOLS

LAB 3: BASIC DATABASE ADMINISTRATION

LAB 3 SOLUTIONS: BASIC DATABASE ADMINISTRATION

CHAPTER 4 ORACLE NETWORKING

ORACLE NETWORKING OVERVIEW

THE ORACLE NET LISTENER

MAKING A CONNECTION

• Using Easy Connect
• Using Local Naming

USING NET MANAGER

CONTROLLING THE LISTENER

• Starting And Stopping The Listener
• Securing The Listener

TROUBLESHOOTING NETWORKING

LAB 4: ORACLE NETWORKING

LAB 4 SOLUTIONS: ORACLE NETWORKING

CHAPTER 5 INSTANCE MANAGEMENT

MANAGING THE ORACLE INSTANCE

• Needed Vocabulary

GETTING THE DATABASE UP

CHANGING PARAMETERS

BACKGROUND PROCESSES

OTHER PROCESSES

CONTROLLING MEMORY USAGE

THE STARTUP PROCESS

STARTUP NOMOUNT

STARTUP MOUNT

STARTUP | STARTUP OPEN

STARTUP RESTRICT | STARTUP OPEN RESTRICT

STARTUP FORCE

THE SHUTDOWN PROCESS

SHUTDOWN | SHUTDOWN NORMAL

SHUTDOWN TRANSACTIONAL

SHUTDOWN IMMEDIATE

SHUTDOWN ABORT

• The Overall Shutdown Process
• Using Services For Startup / Shutdown

MEMORY MANAGEMENT

ALTERNATIVE ARCHITECTURES

• Real Application Clusters
• Pluggable Databases
• Application Containers
• Oracle Sharding
  • Sharding Benefits
  • Sharding Components
  • Application Suitability

LAB 5: INSTANCE MANAGEMENT

LAB 5 SOLUTIONS: INSTANCE MANAGEMENT

CHAPTER 6 STORAGE MANAGEMENT

THE BASIC LOGICAL STRUCTURES

• The Block
• The Extent
• The Segment
• The Tablespace
• The Database

THE PHYSICAL STRUCTURES

• More About Control Files
• More About Online Redo Logs
• More About Archived Redo Logs
• More About Rollback Segments
• What Types Of Tablespaces Are There?
• Tablespace Status
• Default Tablespace Type
• Changing Tablespace Size
• Tablespace Properties
• Encrypted Tablespaces
• Why Add Tablespaces?

USING EMDE FOR STORAGE MANAGEMENT

CHANGING TABLESPACE AND OTHER ALERTS

MANAGING SPACE WITHIN TABLES AND INDEXES

SWITCHING LOG FILES

UNDO RETENTION

REVIEWING UNDO INFORMATION

THE UNDO ADVISOR

CHANGING UNDO TABLESPACE SIZE

LAB 6: STORAGE MANAGEMENT

LAB 6 SOLUTIONS: STORAGE MANAGEMENT

CHAPTER 7 ORACLE SECURITY

CREATING USER ACCOUNTS

• Application Schemas
• Automatically Created Accounts

TYPES OF PRIVILEGES

ABOUT ROLES

PREDEFINED ROLES

VIEWING ROLES IN ENTERPRISE MANAGER DATABASE EXPRESS

CREATING ROLES IN ENTERPRISE MANAGER DATABASE EXPRESS

DROPPING A ROLE

ADMIN AND GRANT OPTIONS

SYSDBA / SYSOPER DETAILS

USER ACCOUNT ADMINISTRATION

CONSIDERATIONS REGARDING PASSWORD CASE SENSITIVITY

PROFILES AND PASSWORD POLICIES

OTHER SECURITY FEATURES

• Auditing
• Fine Grained Auditing
• The Virtual Private Database
• Oracle Label Security
• Oracle Database Vault
• Oracle Data Redaction

LAB 7: ORACLE SECURITY

LAB 7 SOLUTIONS: ORACLE SECURITY

CHAPTER 8 SCHEMA OBJECTS

SCHEMA OBJECTS

• Object Naming
• Fully Qualified Naming
• Aliases
• Object Control
• Table Management
• Column Attributes
• Available Datatypes
• Large Object Enhancements
• Column Constraints
• Partitioning
• Table Storage Attributes
• Table Compression
• Accessing Table Information
• Creating A Table
• Adding Columns
• Dropping Columns
• Adding Constraints
• Disabling Constraints

LOADING DATA

• Field Delimited Data
• Comma Delimited Data
• SQL Developer Import
• SQL Developer Export

ABOUT INDEXES

• Index Creation

VIEWS

• Permission Control Within Views

STORED PROCEDURES

OTHER OBJECTS

• Materialized Views
• Synonyms
• Sequences
• Database Links

LAB 8: SCHEMA OBJECTS

LAB 8 SOLUTIONS: SCHEMA OBJECTS

CHAPTER 9 BACKUP AND RECOVERY

OVERVIEW / KEY CONCEPTS

USERS FOR BACKUP

THE FAST RECOVERY AREA

THE RMAN REPOSITORY

DATABASE CONFIGURATION

RMAN CONFIGURATION

BLOCK CHANGE TRACKING

ORACLE SECURE BACKUPS

ORACLE SUGGESTED BACKUP STRATEGY

SCHEDULING BACKUPS

BACKUP VALIDATION AND OTHER RMAN MAINTENANCE

RESTORE VALIDATION

THE REPORT COMMAND

THE LIST COMMAND

ORACLE ADVISED RECOVERY

USER-DIRECTED RECOVERY

FLASHBACK TABLE

FLASHBACK TO BEFORE DROP

FLASHBACK DATABASE

MANUAL RESTORE AND RECOVER

THE RECOVERY CATALOG

BACKUP MANAGEMENT

MONITORING FRA SPACE USAGE

ORACLE ADVISED RECOVERY

LAB 9: BACKUP AND RECOVERY

LAB 9 SOLUTIONS: BACKUP AND RECOVERY

CHAPTER 10 ORACLE ADVISORS

OVERVIEW

PROACTIVE MONITORING

• The Automatic Database Diagnostic Monitor

THE PERFORMANCE PAGE

ALERT MANAGEMENT

• Metric Thresholds
• Actions Needed When Alerts Are Issued
• Alert Notification Using Email

PROBLEM DIAGNOSIS

MODIFYING AUTOMATIC SNAPSHOT SETTINGS

USING OTHER ADVISORS

• SQL Tuning Advisor Details
• SQL Access Advisor Details
• Running Memory Advisors

REAL APPLICATION TESTING

• Database Replay
• SQL Performance Analyzer (SPA)

LAB 10: ORACLE ADVISORS

LAB 10 SOLUTIONS: ORACLE ADVISORS

CHAPTER 11 PROBLEM RESOLUTION

OVERVIEW

THE RESOLUTION PROCESS

LAB 11: PROBLEM RESOLUTION

LAB 11 SOLUTIONS: PROBLEM RESOLUTION

CHAPTER 12 ? SOFTWARE MANAGEMENT

PATCH MANAGEMENT

MY ORACLE SUPPORT CREDENTIALS

SOFTWARE LIBRARY CONFIGURATION

PATCH STAGING AND APPLICATION

CRITICAL PATCH ADVISORIES

DATABASE UPGRADES

SILENT MODE

RAC CONSIDERATIONS

AUTOMATIC STORAGE MANAGEMENT CONSIDERATIONS

EDITION-BASED REDEFINITION

CHAPTER 13 AUTOMATED STORAGE

AUTOMATIC STORAGE MANAGEMENT OVERVIEW

THE ASM INSTANCE

DISKS, DISK GROUPS AND FAILURE GROUPS

DISK GROUP SPACE USAGE

CREATING AND DROPPING DISK GROUPS

ADDING AND DROPPING DISKS

ASM BACKUPS