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
• 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

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

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

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

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

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

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

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

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

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

OVERVIEW

THE RESOLUTION PROCESS

LAB 11:  PROBLEM RESOLUTION

LAB 11 SOLUTIONS:  PROBLEM RESOLUTION

PATCH MANAGEMENT

UPGRADING THE DATABASE

EDITION-BASED REDEFINITION

ORACLE’S  NEW VERSIONING STRATEGY

REMOVING ORACLE SOFTWARE

AUTOMATIC STORAGE MANAGEMENT OVERVIEW

THE ASM INSTANCE

DISKS, DISK GROUPS AND FAILURE GROUPS

CREATING AND DROPPING DISK GROUPS

ADDING AND DROPPING DISKS

ASM BACKUPS

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

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

CONFIGURING THE RECOVERY CATALOG

RUNNING SCRIPTS IN RMAN

CREATING SCRIPTS IN THE CATALOG

LAB 16:  ADVANCED RECOVERY MANAGER

LAB 16 SOLUTIONS: ADVANCED RECOVERY MANAGER

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

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

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

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: LOGMINER