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

  PROACTIVE MONITORING

  • The Automatic Database Diagnostic Monitor

  MONITORING THE DATABASE WITH EMDE

  USING THE PERFORMANCE HUB

  • The Summary Tab
  • The RAC Tab
  • The Activity Tab
  • The Workload Tab
  • The Monitored SQL Tab
  • The ADDM Tab
  • The Current ADDM Findings Tab
  • The Database Time Tab
  • The Resources Tab
  • The System Statistics Tab
  • Workload Repository Reports
  • Modifying Automatic Snapshot Settings

  USING OTHER ADVISORS

  • SQL Tuning Advisor Details
  • The Automatic SQL Tuning Advisor
  • SQL Access Advisor
  • 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

  UPGRADING THE DATABASE

  EDITION-BASED REDEFINITION

  ORACLE’S NEW VERSIONING STRATEGY

  REMOVING ORACLE SOFTWARE

  CHAPTER 13 – AUTOMATED STORAGE

  AUTOMATIC STORAGE MANAGEMENT OVERVIEW

  THE ASM INSTANCE

  DISKS, DISK GROUPS AND FAILURE GROUPS

  CREATING AND DROPPING DISK GROUPS

  ADDING AND DROPPING DISKS

  ASM BACKUPS

  CHAPTER 14 – HARDENING THE DATABASE

  ELIMINATING SINGLE POINTS OF FAILURE

  HARDENING DATA FILES

  HARDENING THE CONTROL FILES

  HARDENING THE REDO LOGS

  • Adding Redo Log Groups

  HARDENING THE ARCHIVED REDO LOGS

  HARDENING THE PERIPHERAL FILES

  HARDENING THE BACKUPS

  HARDENING THE FAST RECOVERY AREA

  LAB 14: HARDENING THE DATABASE

  LAB 14 SOLUTIONS: HARDENING THE DATABASE

  CHAPTER 15 – OS BACKUP & RECOVERY

  WHY USE THE OPERATING SYSTEM?

  CLOSED BACKUPS IN NOARCHIVELOG MODE

  CLOSED RESTORE IN NOARCHIVELOG MODE

  OPEN BACKUPS IN ARCHIVELOG MODE

  RESTORING IN ARCHIVELOG MODE

  RECOVERY IN ARCHIVELOG MODE

  INCOMPLETE RECOVERY

  LAB 15: OS BACKUP & RECOVERY

  LAB6. 15 SOLUTIONS: OS BACKUP & RECOVERY

  CHAPTER 16 – ADVANCED RECOVERY MANAGER

  CONFIGURING THE RECOVERY CATALOG

  RUNNING SCRIPTS IN RMAN

  CREATING SCRIPTS IN THE CATALOG

  LAB 16: ADVANCED RECOVERY MANAGER

  LAB 16 SOLUTIONS: ADVANCED RECOVERY MANAGER

  CHAPTER 17 – DATA PUMP

  USING DATA PUMP

  • Command Line Syntax
  • Using Interactive Mode
  • Reattaching
  • Data Pump Import Options
  • EXPDP Examples
  • IMPDP Examples

  ENTERPRISE MANAGER DATABASE EXPRESS AND DATA PUMP

  THE DATA PUMP DRIVER

  DATA DICTIONARY VIEWS

  LAB 17: DATA PUMP

  LAB 17 SOLUTIONS: DATA PUMP

  CHAPTER 18 – ADVANCED SECURITY

  DBMS_RLS

  • Implementation Tasks
  • Our Example Scenario
  • The Policy Package
  • The Dynamic Predicate Package
  • Creating The Context And The Trigger
  • Using DBMS_RLS
  • Exempting Policies

  DBMS_FGA

  CONTROLLING COLUMN ACCESS WITH VIRTUAL COLUMNS

  LAB 18: ADVANCED SECURITY

  LAB 18 SOLUTIONS: ADVANCED SECURITY

  CHAPTER 19 – SHARED SERVER

  INITIALIZATION PARAMETERS

  ENABLING SHARED SERVERS

  DISPATCHER CONFIGURATION

  DATA DICTIONARY VIEWS

  CONFIGURING THE CLIENT FOR SHARED SERVER

  LAB 19: SHARED SERVER

  LAB 19 SOLUTIONS: SHARED SERVER

  CHAPTER 20 – LOGMINER

  LOGMINER BASICS

  DBMS_LOGMNR

  DBMS_LOGMNR.ADD_LOGFILE

  DBMS_LOGMNR.START_LOGMNR

  DBMS_LOGMNR. END_LOGMNR

  DBMS_LOGMNR.MINE_VALUE

  DBMS_LOGMNR.COLUMN_PRESENT

  DBMS_LOGMNR_D

  DBMS_LOGMNR_D.BUILD

  DBMS_LOGMNR_D.SET_TABLESPACE

  SUPPLEMENTAL LOGGING

  LOGMINER USE SUMMARY

  USING LOGMINER

  USING ENTERPRISE MANAGER DATABASE EXPRESS WITH LOGMINER

  LAB 20: LOGMINER

  LAB 20 SOLUTIONS: LOG