This five day instructor led class provides a complete, hands-on introduction to Oracle Database Administration including the use of Enterprise Manager Database Express (EMDE), SQL Developer and SQL*Plus.
Target Audience
This course is appropriate for anyone needing to understand and manage an Oracle 12c database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers, database administrators and IT management.
Prerequisites
Oracle12c SQL is required.
Oracle12c PL/SQL is highly desirable.
Course Content
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
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
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
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: LOGMINER
To Hire a proven Oracle SQL 12C Subject Matter Expert Consultant / Course Author and Instructor who teaches this class, call 800-798-3901 today!