ORACLE DATABASE

TRAINING
For Enterprise Success

Paraklete Institute
Master – Certify – Practice - Lead
Oracle Certification Training Courses
Overview
The Oracle certification course is held in high regard in the global IT community. Becoming
an expert in Oracle platforms can be extremely beneficial to one’s career, and if a working
professional is just starting out, he can gain a competitive advantage over his peers in the
field. The certifications in Oracle are of different levels of expertise. There are also different
certifications available for different job roles which are based on the main pillars of
technology.

The six levels of Oracle certifications are:

1. Oracle Certified Junior Associate (OJA), for freshers and students who are learning
programming languages
2. Oracle Certified Associate (OCA) as a foundational course that equips certificate
holders with enough knowledge to support Oracle products
3. Oracle Certified Professional (OCP) is often used by IT hiring managers as a means of
evaluation for the job role of someone who has command over a specific Oracle
platform and technology
4. Oracle Certified Master (OCM) is an advanced course for becoming an expert in the
specific field of Oracle technology requiring a high level of knowledge and skills
5. Oracle Certified Expert (OCE) is directed to gain expertise in a niche or domain of a
specific Oracle platform
6. Oracle Certified Specialist (OCS) is for employees of companies who have partnered
with Oracle and is focused on specializing in a specific domain to showcase expertise
in the particular field
COURSE 01
Oracle Database 11g R2 Administration I
General Description

Administration of the Oracle database management system (DBMS) software environment and of
the server systems on which the DBMS runs is one of the primary tasks required of database
administrators. While it is true that the hardware and software platforms on which the DBMS
runs have become increasingly complex, as has the database software itself, the capabilities
provided by the Oracle database have correspondingly become more sophisticated.

Target Audience

The primary audiences for this course are:

• Database administrators
• Web server administrators
• System administrators
• Implementation specialists
• Data center support engineers
• Senior application designers and developers

Prerequisites

Many students taking this course have familiarity with system administration and administration
of other non-Oracle databases, and this is helpful though not mandatory. Specific course
prerequisites for this course are these Sideris titles:
• ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: PROGRAM WITH PL/SQL – COMPLETE LIBRARY

Suggested Next Course

This course should be considered the first part of the Sideris integrated database administrator
curriculum. Therefore, in the not-too-distant future one should proceed to the follow-up course
• ORACLE DATABASE 11G: ADMINISTRATION II

Content Summary

Pages: 1,107
Duration: 5 days
Instructor Resources

Instructor resources from the Sideris Training Portal. There is no substitute for a subject matter
expert. Sideris custom print courseware combined with our online resources make distance-
learning and virtual training more effective than ever. Download the instructor resources for this
courseware and see how your instructor presentations improve!

Objectives

The overall objective of this training course is to allow the student to gain the skills necessary for
day-to-day administration of an Oracle database. These training materials are the starting point
for Oracle database administrators and sets the foundation for advanced administrator training
and certification. Major tasks to be considered within this course are:
• Configuration of the Linux and MS Windows operating systems in anticipation of a database
installation
• Server Class installation of the Oracle 11g R2 Enterprise Edition database
• Verification of a valid database installation
• Database administrator interface options, including the use of Enterprise Manager, command-
line SQL*Plus, and terminal console SSH
• Database deinstallation
• Online software patching
• Startup and shutdown of database services and related software components
• Super administrator authentication options
• An in-depth exploration of the database architecture, including memory, process and data
structures, and the management of those structures.
• Management of database files.
• Specialized facilities intended to maximize performance of enterprise-scale transactional
applications, such as the shared SQL and PL/SQL cache, the Result Cache, the database buffer
cache, and other similar components.
• Management of security policies and procedures, including administration of user accounts,
roles, privileges and profiles.
• Utilization of advanced self-tuning and self-management capabilities, including the use of
Oracle-Managed Files, database Advisors and other components of the Management Framework.
• Performance monitoring, problem troubleshooting, and resolving lock and conflict issues.
• Using the Oracle Enterprise Manager and SQL interfaces for administration tasks.
• Control over database support services, including the Database Control, Oracle Net and others.
• A primer on backup and recovery structures and strategies.
• Operating system-specific considerations for various platforms, especially Linux and MS
Windows Server.

Contents
INSTALLATION & UPGRADE VOLUME

CONFIGURING THE SERVER FOR INSTALLATION

• IDENTIFY SYSTEM REQUIREMENTS
• OPTIMAL FLEXIBLE ARCHITECTURE (OFA)
• ORACLE_BASE
• ORACLE_HOME
• CONFIGURE LINUX INSTALLATION
• CONFIGURE MS WINDOWS INSTALLATION
• PREREQUISITE CHECKS & FIXUP UTILITY

ENTERPRISE EDITION INSTALLATION

• MORE ABOUT THE INSTALLATION
• START THE INSTALLATION
• DESKTOP CLASS INSTALLATION 11G R2
• SERVER CLASS INSTALLATION 11G R2
• POST-INSTALLATION CONFIGURATION

VERIFY THE INSTALLATION

• ACCESSING THE EM DATABASE CONTROL
• USING THE EM INTERFACE
• USING SQL*PLUS
• USING EM SQL WORKSHEET
• USING SSH

UPGRADE, DEINSTALLATION & PATCHING

• DATABASE UPGRADE
• NETCA
• DBUA
• ONLINE PATCHING
• ORACLE DEINSTALLER

VOLUME A:

MANUALLY STARTING & STOPPING DATABASE SERVICES

• STARTING & STOPPING DATABASE CONTROL
• STARTING & STOPPING THE LISTENER
• STARTING & STOPPING THE DATABASE
• SYSDBA
EXPLORING THE ORACLE DATABASE ARCHITECTURE: GENERAL
• THE ORACLE ENTERPRISE ARCHITECTURE
• GENERAL DATABASE ARCHITECTURE
• V$VERSION

EXPLORING THE ORACLE DATABASE ARCHITECTURE: INSTANCE MEMORY

• MORE ABOUT DATABASE INSTANCES
• USING V$SGA_DYNAMIC_COMPONENTS
• ABOUT THE PGA
• MANAGING MEMORY

EXPLORING THE ORACLE DATABASE ARCHITECTURE: INSTANCE PROCESSES

• INSTANCE BACKGROUND PROCESSES
• SQL EXECUTION PROCESSES
• USING V$SESSION CREATING AN ORACLE DATABASE
• CREATE A NEW DATABASE
• CONFIGURE AN EXISTING DATABASE
• DROP A DATABASE
• MANAGE DBCA TEMPLATES
• MANUALLY CREATE A DATABASE
• MORE ABOUT THE DATABASE FILES

ENABLING TRANSPARENT DATA ENCRYPTION

• TRANSPARENT DATA ENCRYPTION
• MANAGING TDE
• IMPLEMENTING COLUMN ENCRYPTION

MANAGE DATABASE STORAGE STRUCTURES: TABLESPACES

• ABOUT TABLESPACES
• TABLESPACE MANAGEMENT
• AUTOEXTEND OPTIONS
• DROP TABLESPACE
• ALTER TABLESPACE

MANAGE DATABASE STORAGE STRUCTURES: ADVANCED TABLESPACES

• MORE ABOUT TEMPORARY TABLESPACES
• USING V$SORT_SEGMENT
• ABOUT TEMPORARY TABLESPACE GROUPS
• MORE ABOUT PERMANENT TABLESPACES
• ABOUT DATABASE_PROPERTIES
• MORE ABOUT BIGFILE TABLESPACES
• MORE ABOUT THE SYSAUX TABLESPACE

MANAGE DATABASE STORAGE STRUCTURES: DATA FILES & TEMP FILES

• MANAGING DATA FILES
• MANAGING TEMP FILES
• ENABLE ORACLE-MANAGED FILES (OMF)

MANAGING UNDO DATA

• ABOUT UNDO SPACE MANAGEMENT
• CONFIGURE UNDO MANAGEMENT
• UNDO_MANAGEMENT
• UNDO_TABLESPACE
• UNDO_RETENTION
• GUARANTEE UNDO RETENTION
• MONITOR UNDO SPACE
• USE THE UNDO ADVISOR

VOLUME B:

NETWORK ENVIRONMENT: ORACLE NET SERVICES

• ORACLE NET SERVICES ARCHITECTURE
• ORACLE NET CONFIGURATION ASSISTANT
• ORACLE NET MANAGER
• CONFIGURATION USING EM
• TROUBLESHOOTING NETWORK PROBLEMS
• COUNTERING DATABASE ATTACKS

NETWORK ENVIRONMENT: ORACLE SHARED SERVERS

• SHARED SERVERS ARCHITECTURE
• CONFIGURE SHARED SERVERS
• MONITOR SHARED SERVERS
• V$SHARED_SERVER
• V$SHARED_SERVER_MONITOR
• V$SESSION • V$CIRCUIT
• V$DISPATCHER & V$DISPATCHER_CONFIG
• V$QUEUE

SECURITY: IMPLEMENTING DATABASE SECURITY

• DATABASE SYSTEM PRIVILEGES
• DATABASE OBJECT PRIVILEGES
• AUDITING OF DATABASE ACTIVITY
• AUDIT_TRAIL PARAMETER
• ENHANCED DEFAULT SECURITY SETTINGS

SECURITY: ADMINISTERING USER SECURITY

• CREATE & MANAGE DATABASE ACCOUNTS
• GRANT & REVOKE DATABASE PRIVILEGES
• RESOURCE LIMITS VIA PROFILES
• ALTER PROFILE
• DROP PROFILE
• PASSWORD MANAGEMENT VIA PROFILES
• ABOUT ROLE-BASED SECURITY
• WITH ADMIN OPTION
• WITH ADMIN OPTION & SYSTEM PRIVILEGES
• WITH GRANT OPTION & OBJECT PRIVILEGES
• ALTER USER...DEFAULT ROLE
• SET ROLE
• ENHANCED DEFAULT SECURITY SETTINGS
• SEC_CASE_SENSITIVE_LOGON SETTING

MOVING DATA: SQL LOADER

• LOADER CONCEPTS
• CONTROL FILE OPTIONS
• LOAD METHODS
• USING THE EM INTERFACE

MOVING DATA: EXPORT & IMPORT

• MANAGE DIRECTORY OBJECTS
• ABOUT THE DATA PUMP ARCHITECTURE
• DATA PUMP IMPORT
• DATA PUMP DATA DICTIONARY VIEWS
• USING THE EM INTERFACE

DATABASE MAINTENANCE & PERFORMANCE MONITORING

• MANAGEMENT & ADVISORY FRAMEWORK
• MANAGE AWR
• ADDM
• USE METRICS & ALERT THRESHOLDS
• AUTOMATIC MAINTENANCE TASKS

MANAGING CONCURRENCY & LOCKS

• ABOUT LATCHES & LOCKS
• MANAGE & MONITOR LATCHES
• V$LOCK
• V$LOCKED_OBJECT
• V$SESSION
• V$SESSION_WAIT
• DBA_BLOCKERS & DBA_WAITERS
• DBA_DML_LOCKS & DBA_DDL_LOCKS
• DML_LOCKS

BACKUP & RECOVERY CONCEPTS & CONFIGURATION

• ABOUT BACKUP & RECOVERY STRUCTURES
• MANAGING REDO DATA
• MANAGING ARCHIVAL DATA
• AUTOMATIC CRASH RECOVERY
• CREATING A BASIC BACKUP STRATEGY
COURSE 02
Oracle Database 11g R2 Administration II

General Description

This course brings database administrators beyond a basic understanding of

their duties and to transfer advanced skills needed to effectively administer an
Oracle database installation within a large-scale enterprise. Important subject
areas include advanced database configuration, performance monitoring and
tuning, configuring for recovery, troubleshooting and fault diagnosis, and use
of the extensive advisory framework. Sometimes configuration and operational
problems can exist within an Oracle database installation because important
technologies are either not fully understood or not properly implemented.

Advanced technologies which should be applied to any mission-critical

database installation. The Database Resource Manager is an essential tool for
database tuning and achieving optimum performance, and is often used in
combination with the advisors. The Database Scheduler is a sophisticated tool
which can automate many administration tasks and integrate the database into
the larger data center and systems infrastructure. All of the data within a
database installation is at risk unless one has designed, tested and
implemented a robust backup and recovery strategy.

When a major failure occurs, a variety of recovery and troubleshooting

techniques must be employed. The Oracle Recovery Manager (RMAN) tool is
discussed with additional facilities including the Data Recovery Advisor, Redo
Log File Size Advisor and MTTR Advisor. It is tempting to short-cut appropriate
backup procedures if they perform poorly, so special attention is giving to
performance optimization.

Target Audience

The primary target audiences for this course are:

• Database administrators
• Web server administrators
• System administrators
• Implementation specialists
• Data center support engineers
• Senior application designers and developers

Prerequisites
Specific prerequisites for this course are the following Sideris titles, or
equivalent experience:
• ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE
LIBRARY
• ORACLE DATABASE 11G R2: PL/SQL FUNDAMENTALS – COMPLETE
LIBRARY
• ORACLE DATABASE 11G R2: ADMINISTRATION I

Next Course

Administrators may wish to proceed onto more focused and specialized

courses with these modules:
• ORACLE DATABASE 11G R2: GRID INFRASTRUCTURE & ASM
• ORACLE DATABASE 11G R2: PERFORMANCE TUNING
• ORACLE DATABASE 11G R2: SQL TUNING
• ORACLE DATABASE 11G R2: ENCRYPTION & ADVANCED DATA
SECURITY
• ORACLE ENTERPRISE MANAGER 11G R2: GRID CONTROL

Content Summary

Pages 1330 pages

Duration: 5 days

Instructor Resources

Instructor resources from the Sideris Training Portal. There is no substitute for
a subject matter expert. Sideris custom print courseware combined with our
online resources make distance-learning and virtual training more effective
than ever. Download the instructor resources for this courseware and see how
your instructor presentations improve!

Objectives

This course brings database administrators beyond a basic understanding of

their duties and to transfer advanced skills needed to effectively administer an
Oracle database installation within a large-scale enterprise.
• Automatic database management, monitoring and tuning facilities. Included
are automatic management of storage space and memory resources within the
database and the role of the Segment Advisor and Memory Advisors.
• Automatic resumption of database operations even in the face of storage
space allocation failures and errors.
• Setting metric and alert thresholds for proactive database monitoring.
• Using deferred segment creation to improve performance and establish
efficient use of space.
• Globalization features within the database to support applications in multiple
languages, currencies, time zones and countries.
• Utilizing diagnostic sources in troubleshooting database problems and
employing the Fault Diagnosability Infrastructure, the Database Instance
Health monitor and the Support Workbench to capture and process fault data.
• Configuring the database to recover from a wide variety of failures. This
includes configuring redo log files and the Flash or Fast Recovery Area.
• Using Oracle flashback technology to recover from user errors and from
database failures, including Flashback Data Archive and Flashback Transaction
Backout as part of the Oracle Total Recall technology.
• Perform other flashback operations, including Flashback Version Query,
Flashback Transaction Query and Flashback Database.
• Controlling resource utilization on database servers whose resource demand
exceeds its capacity.
• Advanced resource management for complex configurations involving
multiple CPUs and multiple database instances.
• Automating launching routine maintenance tasks and even application
functions using the database Scheduler.
• Tune performance of database operations and SQL statement execution
using the extensive advisory framework and the Automatic Database
Diagnostic Monitoring (ADDM) facility. Included in this framework are the SQL
Tuning and SQL Access advisors.
• User-managed and automatically managed backup strategies and database
recovery operations using RMAN and other database facilities.
• Database instance recovery, tuning checkpoints, the Redo Log File Size
Advisor and the MTTR Advisor.
• User-managed recovery scenarios, including recovery from temporary, read-
only and index tablespaces.
• Automatically managed backup strategies and database recovery operations
using RMAN and other database facilities. Complete and incomplete media
recovery, including database point-in-time recovery and tablespace point-in-
time recovery.
• Build upon basic RMAN capabilities with a centralized recovery catalog.
Deploy standardized and consistent backup and recovery procedures
throughout the enterprise by means of dynamic stored scripts and variable
substitution.
• Optimizing backups for faster performance and parallelization of operations,
employing compression algorithms and other strategies for optimum efficiency.
• Data preservation through archival backups.
• Duplicate databases for regulatory compliance, Real Application Testing
database replay, test configuration and other purposes.
• Detecting and handling failures and corruption, including the use of RMAN
block recovery and the Data Recovery Advisor.

Certification

This course covers information relevant for the certification test EXAM #1Z1-
053 ORACLE DATABASE 11G R2: ADMINISTRATION II.

ILT / Virtual Training

Instructor-Led Training (ILT) / Live Virtual Training Schedule

This textbook may be used as one module within a Sideris courseware kit
entitled ORACLE DATABASE 11G R2: ADMINISTRATION II. When delivered
in this format as an instructor-led training (ILT) or live virtual training session,
the suggested presentation length of all modules included in this kit is 5 days.
The sequence in which the modules should be considered is:
• ORACLE DATABASE 11G R2: ADVANCED DATABASE ADMINISTRATION
• ORACLE DATABASE 11G R2: RESOURCE MANAGER & SCHEDULER
• ORACLE DATABASE 11G R2: RMAN BACKUP & RECOVERY

Contents

Advanced Database Administration

MANAGING MEMORY
• ABOUT ADVANCED ADMINISTRATION
• ABOUT MANUAL MEMORY MANAGEMENT
• AUTOMATIC MEMORY MANAGEMENT
• MEMORY_TARGET
• MEMORY_MAX_TARGET
• SGA_TARGET
• SGA_MAX_SIZE
• WORKAREA_SIZE_POLICY & PGA_AGGREGATE_TARGET
• USING EM & THE MEMORY ADVISORS
• MONITOR IN MEMORY ACCESS MODE

MANAGING STORAGE & THE SEGMENT ADVISOR

• MONITOR TABLESPACES
• ABOUT RESUMABLE SPACE ALLOCATION
• TABLE STORAGE MANAGEMENT
• USE THE SEGMENT ADVISOR

GLOBALIZATION
• GLOBALIZATION SETTINGS
• NLS_LANG
• NLS_LANGUAGE & NLS_TERRITORY
• DATE & TIME LOCALIZATION
• LINGUISTIC INDEXES
• ABOUT LOCALE BUILDER

DIAGNOSING DATABASE PROBLEMS

• FAULT DIAGNOSABILITY INFRASTRUCTURE
• USING THE ALERT LOG
• ABOUT THE TRACE FILES
• DATABASE INSTANCE HEALTH SNAPSHOT
• USING THE SUPPORT WORKBENCH
• ENABLE ORACLE CONFIGURATION MANAGE

RECOVERY CONCEPTS
• THE BACKUP & RECOVERY STRUCTURES
• ORACLE SECURE BACKUP
• MANAGING REDO DATA
• MANAGING ARCHIVED REDO DATA
• CHECKLIST FOR SOUND RECOVERY

ORACLE FLASHBACK TECHNOLOGY

• ABOUT FLASHBACK TECHNOLOGY
• SELECT...AS OF TIMESTAMP
• SELECT...AS OF SCN
• UNDO MANAGEMENT FOR FLASHBACK
• UNDO_RETENTION
• FLASHBACK TABLE
• FLASHBACK DROP
• FLASHBACK VERSIONS QUERY
• FLASHBACK TRANSACTION QUERY
• FLASHBACK TRANSACTION BACKOUT

FLASHBACK DATABASE & FLASHBACK DATA ARCHIVE

• ABOUT FLASHBACK DATABASE
• CONFIGURING THE FLASH RECOVERY AREA
• PERFORMING DATABASE FLASHBACK
• MONITORING FLASHBACK PERFORMANCE
• FLASHBACK DATABASE CONSIDERATIONS
• CONFIGURE FLASHBACK DATA ARCHIVE

Resource Manager & Scheduler

MANAGING RESOURCES
• RESOURCE MANAGER ARCHITECTURE
• CONSUMER GROUPS
• RESOURCE PLANS
• AUTOMATIC & ADAPTIVE MAPPING
• ACTIVATING PLANS
• MONITORING RESOURCE ALLOCATION
• EXPLICIT CONSUMER GROUP SWITCHING
• INSTANCE CAGING

AUTOMATING TASKS WITH THE SCHEDULER (BASIC)

• SCHEDULER ARCHITECTURE
• MANAGE PROGRAMS
• MANAGE SCHEDULES
• MANAGE JOBS

AUTOMATING TASKS WITH THE SCHEDULER (ADVANCED)

• MANAGE JOB CLASSES
• MANAGE WINDOWS & WINDOW GROUPS
• MANAGE CHAINS
• ABOUT EVENTS & THE SCHEDULER

MANAGING DATABASE PERFORMANCE

• MANAGEMENT & ADVISORY FRAMEWORK
• MANAGE AWR
• ADDM
• SQL TUNING ADVISOR
• SQL ACCESS ADVISOR

RMAN Backup & Recovery

INSTANCE RECOVERY
• ABOUT INSTANCE RECOVERY
• INSTANCE RECOVERY PARALLELISM
• Changing RECOVERY_PARALLELISM
• MTTR ADVISOR & TUNING CHECKPOINTS
• LOG_CHECKPOINTS_TO_ALERT
• REDO LOGFILE SIZE ADVISOR
• FAST-START ON-DEMAND PARALLELISM

CONFIGURING RMAN
• RMAN ARCHITECTURE
• LAUNCH & USE RMAN
• CONFIGURE RMAN SETTINGS
• ALLOCATE RMAN CHANNELS

BACKUP WITH RMAN

• ABOUT BACKUP FILE TYPES
• PERFORMING FULL BACKUPS
• PERFORMING INCREMENTAL BACKUPS
• ESTABLISHING BACKUP RETENTION POLICY
• GENERATE REPORTS
• REPORT UNRECOVERABLE
• REPORT NEED BACKUP
• LIST INCARNATION

RMAN MANAGEMENT WITH EM

• MONITORING THE FLASH RECOVERY AREA
• THE EM INTERFACE TO RMAN
• SCHEDULING RMAN BACKUPS
• ORACLE-SUGGESTED BACKUP STRATEGY
• MANAGE CURRENT BACKUPS
• BACKUP REPORTS
• MANAGE RESTORE POINTS

USER-MANAGED RECOVERY OPERATIONS

• RECOVERY CONCEPTS
• RECOVER TEMPORARY TABLESPACES
• RECOVER READ-ONLY TABLESPACES
• RECOVER INDEX TABLESPACES
• RECOVER REDO LOG GROUP MEMBER
• RECREATE THE PASSWORD FILE

RECOVERY WITH RMAN

• COMPLETE MEDIA RECOVERY
• INCOMPLETE MEDIA RECOVERY
• RECOVERY USING EM

USING THE RMAN RECOVERY CATALOG

• UNDERSTANDING CATALOG CONCEPTS
• CREATE THE RECOVERY CATALOG
• MANAGE VIRTUAL PRIVATE CATALOGS
• PROTECTION OF THE RECOVERY CATALOG
• USING RMAN SCRIPTS

DATABASE DUPLICATION & CLONING

• WHY PERFORM DATABASE DUPLICATION?
• RMAN DUPLICATE DATABASE
• RMAN DUPLICATE
• DATABASE CLONING

ADVANCED RMAN CAPABILITIES

• BACKUP OPTIMIZATION
• ENHANCING PARALLELISM, SECTION SIZES
• BACKUP PERFORMANCE & CONTROL
• MINIMIZE TIME vs. MINIMIZE LOAD
• ARCHIVAL BACKUPS
• COPYING FILES BETWEEN DATABASES

TRANSPORTING TABLESPACES
• ABOUT TABLESPACE TRANSPORTATION
• READ-ONLY TABLESPACE TRANSPORTATION
• READ-WRITE TABLESPACE TRANSPORT
• TRANSPORT TABLESPACES USING EM

RECOVERING FROM CORRUPTION WITH DATA RECOVERY ADVISOR

• HOW DOES CORRUPTION OCCUR?
• DETECTING CORRUPTION
• ANALYZE
• V$DATABASE_BLOCK_CORRUPTION View
• DB_BLOCK_CHECKING
• DB_BLOCK_CHECKSUM
• DB_LOST_WRITE_PROTECT
• DB_ULTRA_SAFE
• USING THE DATA RECOVERY ADVISOR
• BLOCK MEDIA RECOVERY
• DBMS_REPAIR() ISOLATION
COURSE 03
Oracle Database 12c: New Features For Administrators

General Description

This course brings database administrators beyond a basic understanding of their duties and to
transfer advanced skills needed to effectively administer an Oracle database installation within a
large-scale enterprise. Important subject areas include advanced database configuration,
performance monitoring and tuning, configuring for recovery, troubleshooting and fault diagnosis,
and use of the extensive advisory framework. Sometimes configuration and operational problems
can exist within an Oracle database installation because important technologies are either not
fully understood or not properly implemented.

Advanced technologies which should be applied to any mission-critical database installation. The
Database Resource Manager is an essential tool for database tuning and achieving optimum
performance, and is often used in combination with the advisors. The Database Scheduler is a
sophisticated tool which can automate many administration tasks and integrate the database into
the larger data center and systems infrastructure. All of the data within a database installation is
at risk unless one has designed, tested and implemented a robust backup and recovery strategy.

When a major failure occurs, a variety of recovery and troubleshooting techniques must be
employed. The Oracle Recovery Manager (RMAN) tool is discussed with additional facilities
including the Data Recovery Advisor, Redo Log File Size Advisor and MTTR Advisor. It is tempting
to short-cut appropriate backup procedures if they perform poorly, so special attention is giving
to performance optimization.

Target Audience

The primary target audiences for this course are:

• Database administrators
• Web server administrators
• System administrators
• Implementation specialists
• Data center support engineers
• Senior application designers and developers

Prerequisites
Specific prerequisites for this course are the following Sideris titles, or equivalent experience:
• ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: PL/SQL FUNDAMENTALS – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: ADMINISTRATION I

Next Course

Administrators may wish to proceed onto more focused and specialized courses with these
modules:
• ORACLE DATABASE 11G R2: GRID INFRASTRUCTURE & ASM
• ORACLE DATABASE 11G R2: PERFORMANCE TUNING
• ORACLE DATABASE 11G R2: SQL TUNING
• ORACLE DATABASE 11G R2: ENCRYPTION & ADVANCED DATA SECURITY
• ORACLE ENTERPRISE MANAGER 11G R2: GRID CONTROL

Content Summary

Duration: 5 days

Objectives

This course brings database administrators beyond a basic understanding of their duties and to
transfer advanced skills needed to effectively administer an Oracle database installation within a
large-scale enterprise.
• Automatic database management, monitoring and tuning facilities. Included are automatic
management of storage space and memory resources within the database and the role of the
Segment Advisor and Memory Advisors.
• Automatic resumption of database operations even in the face of storage space allocation
failures and errors.
• Setting metric and alert thresholds for proactive database monitoring.
• Using deferred segment creation to improve performance and establish efficient use of space.
• Globalization features within the database to support applications in multiple languages,
currencies, time zones and countries.
• Utilizing diagnostic sources in troubleshooting database problems and employing the Fault
Diagnosability Infrastructure, the Database Instance Health monitor and the Support Workbench
to capture and process fault data.
• Configuring the database to recover from a wide variety of failures. This includes configuring
redo log files and the Flash or Fast Recovery Area.
• Using Oracle flashback technology to recover from user errors and from database failures,
including Flashback Data Archive and Flashback Transaction Backout as part of the Oracle Total
Recall technology.
• Perform other flashback operations, including Flashback Version Query, Flashback Transaction
Query and Flashback Database.
• Controlling resource utilization on database servers whose resource demand exceeds its
capacity.
• Advanced resource management for complex configurations involving multiple CPUs and
multiple database instances.
• Automating launching routine maintenance tasks and even application functions using the
database Scheduler.
• Tune performance of database operations and SQL statement execution using the extensive
advisory framework and the Automatic Database Diagnostic Monitoring (ADDM) facility. Included
in this framework are the SQL Tuning and SQL Access advisors.
• User-managed and automatically managed backup strategies and database recovery operations
using RMAN and other database facilities.
• Database instance recovery, tuning checkpoints, the Redo Log File Size Advisor and the MTTR
Advisor.
• User-managed recovery scenarios, including recovery from temporary, read-only and index
tablespaces.
• Automatically managed backup strategies and database recovery operations using RMAN and
other database facilities. Complete and incomplete media recovery, including database point-in-
time recovery and tablespace point-in-time recovery.
• Build upon basic RMAN capabilities with a centralized recovery catalog. Deploy standardized
and consistent backup and recovery procedures throughout the enterprise by means of dynamic
stored scripts and variable substitution.
• Optimizing backups for faster performance and parallelization of operations, employing
compression algorithms and other strategies for optimum efficiency.
• Data preservation through archival backups.
• Duplicate databases for regulatory compliance, Real Application Testing database replay, test
configuration and other purposes.
• Detecting and handling failures and corruption, including the use of RMAN block recovery and
the Data Recovery Advisor.

Certification

This course covers information relevant for the certification test EXAM #1Z1-053 ORACLE
DATABASE 11G R2: ADMINISTRATION II.

ILT / Virtual Training or ONSITE CLASSROOM Based Training

Instructor-Led Training (ILT) / Live Virtual Training Schedule

This courseware may be used as one module within a single courseware kit entitled ORACLE
DATABASE 11G R2: ADMINISTRATION II. When delivered in this format as an instructor-led
training (ILT) or live virtual training session or Face-to-Face Classroom, the suggested
presentation length of all modules included in this kit is 5 days. The sequence in which the
modules should be considered is:
• ORACLE DATABASE 11G R2: ADVANCED DATABASE ADMINISTRATION
• ORACLE DATABASE 11G R2: RESOURCE MANAGER & SCHEDULER
• ORACLE DATABASE 11G R2: RMAN BACKUP & RECOVERY

Contents

Advanced Database Administration

MANAGING MEMORY
• ABOUT ADVANCED ADMINISTRATION
• ABOUT MANUAL MEMORY MANAGEMENT
• AUTOMATIC MEMORY MANAGEMENT
• MEMORY_TARGET
• MEMORY_MAX_TARGET
• SGA_TARGET
• SGA_MAX_SIZE
• WORKAREA_SIZE_POLICY & PGA_AGGREGATE_TARGET
• USING EM & THE MEMORY ADVISORS
• MONITOR IN MEMORY ACCESS MODE

MANAGING STORAGE & THE SEGMENT ADVISOR

• MONITOR TABLESPACES
• ABOUT RESUMABLE SPACE ALLOCATION
• TABLE STORAGE MANAGEMENT
• USE THE SEGMENT ADVISOR

GLOBALIZATION
• GLOBALIZATION SETTINGS
• NLS_LANG
• NLS_LANGUAGE & NLS_TERRITORY
• DATE & TIME LOCALIZATION
• LINGUISTIC INDEXES
• ABOUT LOCALE BUILDER
DIAGNOSING DATABASE PROBLEMS
• FAULT DIAGNOSABILITY INFRASTRUCTURE
• USING THE ALERT LOG
• ABOUT THE TRACE FILES
• DATABASE INSTANCE HEALTH SNAPSHOT
• USING THE SUPPORT WORKBENCH
• ENABLE ORACLE CONFIGURATION MANAGE

RECOVERY CONCEPTS
• THE BACKUP & RECOVERY STRUCTURES
• ORACLE SECURE BACKUP
• MANAGING REDO DATA
• MANAGING ARCHIVED REDO DATA
• CHECKLIST FOR SOUND RECOVERY

ORACLE FLASHBACK TECHNOLOGY

• ABOUT FLASHBACK TECHNOLOGY
• SELECT...AS OF TIMESTAMP
• SELECT...AS OF SCN
• UNDO MANAGEMENT FOR FLASHBACK
• UNDO_RETENTION
• FLASHBACK TABLE
• FLASHBACK DROP
• FLASHBACK VERSIONS QUERY
• FLASHBACK TRANSACTION QUERY
• FLASHBACK TRANSACTION BACKOUT

FLASHBACK DATABASE & FLASHBACK DATA ARCHIVE

• ABOUT FLASHBACK DATABASE
• CONFIGURING THE FLASH RECOVERY AREA
• PERFORMING DATABASE FLASHBACK
• MONITORING FLASHBACK PERFORMANCE
• FLASHBACK DATABASE CONSIDERATIONS
• CONFIGURE FLASHBACK DATA ARCHIVE

Resource Manager & Scheduler

MANAGING RESOURCES
• RESOURCE MANAGER ARCHITECTURE
• CONSUMER GROUPS
• RESOURCE PLANS
• AUTOMATIC & ADAPTIVE MAPPING
• ACTIVATING PLANS
• MONITORING RESOURCE ALLOCATION
• EXPLICIT CONSUMER GROUP SWITCHING
• INSTANCE CAGING

AUTOMATING TASKS WITH THE SCHEDULER (BASIC)

• SCHEDULER ARCHITECTURE
• MANAGE PROGRAMS
• MANAGE SCHEDULES
• MANAGE JOBS

AUTOMATING TASKS WITH THE SCHEDULER (ADVANCED)

• MANAGE JOB CLASSES
• MANAGE WINDOWS & WINDOW GROUPS
• MANAGE CHAINS
• ABOUT EVENTS & THE SCHEDULER

MANAGING DATABASE PERFORMANCE

• MANAGEMENT & ADVISORY FRAMEWORK
• MANAGE AWR
• ADDM
• SQL TUNING ADVISOR
• SQL ACCESS ADVISOR

RMAN Backup & Recovery

INSTANCE RECOVERY
• ABOUT INSTANCE RECOVERY
• INSTANCE RECOVERY PARALLELISM
• Changing RECOVERY_PARALLELISM
• MTTR ADVISOR & TUNING CHECKPOINTS
• LOG_CHECKPOINTS_TO_ALERT
• REDO LOGFILE SIZE ADVISOR
• FAST-START ON-DEMAND PARALLELISM

CONFIGURING RMAN
• RMAN ARCHITECTURE
• LAUNCH & USE RMAN
• CONFIGURE RMAN SETTINGS
• ALLOCATE RMAN CHANNELS
BACKUP WITH RMAN
• ABOUT BACKUP FILE TYPES
• PERFORMING FULL BACKUPS
• PERFORMING INCREMENTAL BACKUPS
• ESTABLISHING BACKUP RETENTION POLICY
• GENERATE REPORTS
• REPORT UNRECOVERABLE
• REPORT NEED BACKUP
• LIST INCARNATION

RMAN MANAGEMENT WITH EM

• MONITORING THE FLASH RECOVERY AREA
• THE EM INTERFACE TO RMAN
• SCHEDULING RMAN BACKUPS
• ORACLE-SUGGESTED BACKUP STRATEGY
• MANAGE CURRENT BACKUPS
• BACKUP REPORTS
• MANAGE RESTORE POINTS

USER-MANAGED RECOVERY OPERATIONS

• RECOVERY CONCEPTS
• RECOVER TEMPORARY TABLESPACES
• RECOVER READ-ONLY TABLESPACES
• RECOVER INDEX TABLESPACES
• RECOVER REDO LOG GROUP MEMBER
• RECREATE THE PASSWORD FILE

RECOVERY WITH RMAN

• COMPLETE MEDIA RECOVERY
• INCOMPLETE MEDIA RECOVERY
• RECOVERY USING EM

USING THE RMAN RECOVERY CATALOG

• UNDERSTANDING CATALOG CONCEPTS
• CREATE THE RECOVERY CATALOG
• MANAGE VIRTUAL PRIVATE CATALOGS
• PROTECTION OF THE RECOVERY CATALOG
• USING RMAN SCRIPTS

DATABASE DUPLICATION & CLONING

• WHY PERFORM DATABASE DUPLICATION?
• RMAN DUPLICATE DATABASE
• RMAN DUPLICATE
• DATABASE CLONING

ADVANCED RMAN CAPABILITIES

• BACKUP OPTIMIZATION
• ENHANCING PARALLELISM, SECTION SIZES
• BACKUP PERFORMANCE & CONTROL
• MINIMIZE TIME vs. MINIMIZE LOAD
• ARCHIVAL BACKUPS
• COPYING FILES BETWEEN DATABASES

TRANSPORTING TABLESPACES
• ABOUT TABLESPACE TRANSPORTATION
• READ-ONLY TABLESPACE TRANSPORTATION
• READ-WRITE TABLESPACE TRANSPORT
• TRANSPORT TABLESPACES USING EM

RECOVERING FROM CORRUPTION WITH DATA RECOVERY ADVISOR

• HOW DOES CORRUPTION OCCUR?
• DETECTING CORRUPTION
• ANALYZE
• V$DATABASE_BLOCK_CORRUPTION View
• DB_BLOCK_CHECKING
• DB_BLOCK_CHECKSUM
• DB_LOST_WRITE_PROTECT
• DB_ULTRA_SAFE
• USING THE DATA RECOVERY ADVISOR
• BLOCK MEDIA RECOVERY
• DBMS_REPAIR() ISOLATION
COURSE 05
Oracle Database 12c: SQL III- Advanced
General Description
There is a difference between using the SQL language in the classroom or in a prototype
environment and successfully deploying it as part of a production application. This textbook goes
beyond the basics of the SQL language and considers topics that must be addressed in a real-world
environment.

Target Audience

The target audience for this textbook is all Oracle professionals, both business and systems
professionals. Among the specific groups for whom this textbook will be helpful are:

• Application designers and database developers

• ETL, BI and data analytics developers

• Database and data warehouse administrators

• Web server administrators

Oracle Database 12c: Administration
Workshop II
General Description

This training textbook considers advanced features for Oracle 12c database administrators. Like
any
major software platform, the management of an Oracle database in a production environment can
present challenges. Responding to those challenges requires more than just a basic understanding
of
database administration.

Target Audience

The primary target audiences for this textbook are:

• Database administrators
• Storage administrators
• System administrators
• Implementation specialists
• Data center support engineers

Prerequisites

The entire Oracle database administrator curriculum, including this textbook that you are now
reading, has these prerequisites from the Sideris Textbook Series For Oracle Database
Developers.
Equivalent experience is also acceptable.
• ORACLE DATABASE 12C: SQL FUNDAMENTALS (LEVELS I & II)
• ORACLE DATABASE 12C: PL/SQL FUNDAMENTALS (LEVELS I & II)

With regard to this text and its position within the Sideris Textbook Series For Oracle
Administrators these are the required prerequisites, or equivalent experience:
• ORACLE DATABASE 12C: INSTALL & UPGRADE WORKSHOP
• ORACLE DATABASE 12C: ARCHITECTURE & INTERNALS
• ORACLE DATABASE 12C: ADMINISTRATION WORKSHOP

Certification

This textbook considers subjects helpful for certification as an Oracle Database 12c Administrator
Certified Professional (OCP) for "Exam: 1Z0-063: Oracle Database 12c: Advanced
Administration".

Content Summary

Volumes: 2
Pages: 906
Hands-on Workshops: 13
Exercises: 109

Suggested Next Courses

Readers may next wish to proceed onto these textbooks from this textbook series:
• ORACLE DATABASE 12C: BACKUP & RECOVERY WORKSHOP
• ORACLE DATABASE 12C: RESOURCE MANAGER & SCHEDULER
• ORACLE DATABASE 12C: MANAGE THE MULTITENANT ARCHITECTURE

Objectives

Within this textbook we will explore these topics:

• Setting metric and alert thresholds for proactive database monitoring by the server alert
framework.
• Globalization features within the database to support applications in multiple languages,
currencies, time zones and countries.
• Database connection and session options that support thousands of concurrent users, including
a shared servers configuration.
• Automatic database management, monitoring and tuning facilities. Included are automatic
management of storage space and memory resources within the database and the role of the
Segment Advisor and Memory Advisors.
• Automatic resumption of database operations even in the face of storage space allocation
failures and errors.
• Protecting data-at-rest in the database using the advanced data security feature Transparent
Data Encryption, as well as a solid overview of underlying encryption technologies, protocols and
algorithms.
• Protecting data en route with Oracle Net Services encryption.
• An introduction to advanced space management topics, including custom space management
attributes, resumable space allocation and other subjects.
• Advanced compression techniques for tables and indexes.
• Columnar vs. row-oriented database storage and the use of hybrid columnar compression.
• Heat map tracking of segment access to measure hot, warm and cold areas of the database that
could be targeted for optimization.
• ILM data management and support for that framework with available features of the Oracle
database.
• Automatic data optimization and policies that automatically implement the ILM concepts of
row-level compression tiering, segment-level compression tiering and segment-level storage
tiering.
• Implementing custom ILM storage management policies.
• In-memory storage of database objects, including big table caching, full database caching and in-
memory column stores.
• Archiving inactive data inside the database while not degrading performance while limiting
added storage costs.
• Introduce temporal validity into a table and perform flashback query operations using temporal
validity criteria.
• Data redaction that allows data privacy policies to be dynamically enforced during queries.

Contents

Advanced Administration: About Advanced Database Administration

• Database Feature Usage
• Server Alert Framework
• Managing The Server Alerts Using EM CC
• pDB Level Alerts

Advanced Administration: Globalization

• Globalization Settings
• Managing NLS Settings
• Date & Time Localization
• Linguistic Sorting & Matching
• Linguistic Indexes
• About Locale Builder

Advanced Administration: Database Connections & Shared Servers

• Connection & Session Options
• Shared Servers Architecture
• Comparing Shared Servers & Dedicated Servers
• Configure Shared Servers
• Use Shared Servers
• Monitor Shared Servers

Advanced Security: Database Security Threats

• Potential Threats
• Potential Attackers
• The Threat Matrix
• Database Security Checklist

Advanced Security: Encryption Concepts

• About Encryption
• Data-At-Rest Encryption
• Data-In-Transit Encryption
• Data-En-Route Encryption
• Encryption Limitations
• Encryption Algorithms & Potential Weaknesses
• Managing Encryption Keys

Advanced Security: TDE Keystore Management

• About Transparent Data Encryption
• The SYSKM Administrator
• TDE Encryption Algorithms
• Understanding How Column Encryption Works
• Understanding How Tablespace Encryption Works
• Is TDE Unbreakable?
• Managing TDE
• Managing The Keystore
• Managing A Wallet Keystore Using SQL
• Managing A Wallet Keystore Using EM CC
• Managing A Wallet Keystore Using OWM
• Advanced Management Options
• Keystore Backups
• Migrate To HSM Keystore
• Wallet Open Options
• Regenerate Master Encryption Key

Advanced Security: TDE Data Encryption

• Implement Tablespace Encryption
• Implement Column Encryption
• Assessing Encryption Usage

Advanced Security: Oracle Net Services Encryption & Security

• Network-Launched Attacks
• Limiting Database Attacks
• Preventing Denial-of-service Attacks
• Avoiding Disclosure Of Vulnerabilities
• Oracle Net Services User Notifications
• Oracle Net Native Encryption
• Implementation With Oracle Net Manager
• Implementation With sqlnet.ora

Advanced Storage: Space Management

• Space Management Best Practices
• Segment Storage Specifications
• Estimate & Track Space Usage
• Resumable Space Allocation
• Enable A Resumable Session
• Detect A Suspended Session
• Resuming A Session
• Using DBMS_RESUMABLE()
• Deferred Segment Creation

Advanced Storage: The Segment Advisor

• About The Segment Advisor
• Using The SHRINK SPACE Option
• Segment Advisor Within EM CC
• The Automatic Segment Advisor
• Programmatic Segment Advisor

Advanced Compression
• About Compression
• Table Compression
• Basic Table Compression
• Advanced Row Compression
• Additional Table Compression Considerations
• Index Compression
• Key Compression
• Advanced Index Compression
• Hybrid Columnar Compression
• Compression Analysis
• Using SQL Metadata Queries
• Using DBMS_COMPRESSION()
• Using EM CC

Heat Map Tracking & Automatic Data Optimization (ADO)

• About ILM
• The Oracle Database & ILM Support
• Heat Map Tracking
• Automatic Data Optimization (ADO)
• Adding ADO Policies Using SQL
• Managing ADO Policies Using SQL
• Managing ADO Policy Evaluation
• Managing ADO Policies Using EM CC

In-Memory Storage
• About Traditional Table Caching
• Big Table Caching
• Full Database Caching
• In-Memory Column Store
• IMCS Internals
• Configuring The IMCS
• Assigning Objects To The IMCS
• Assigning Columns To The IMCS
• Monitoring IMCS Using EM CC

In-Database Archiving
• About In-Database Archiving
• In-Database Archival Tables
• In-Database Archiving Of Rows
• Temporal Validity

Data Redaction
• About Data Redaction
• The Redaction Functions
• Using Function Type dbms_redact.random
• Using Function Type dbms_redact.partial
• Function Type dbms_redact.partial Shortcuts
• Using Function Type dbms_redact.regexp
• Function Type dbms_redact.regexp Shortcuts
• Defining Redaction Policies
• Managing Redaction Policies
• Customizing Full Redaction
• Viewing Redaction Metadata
• Using EM Cloud Control
• Inference Attacks
SPECIALIZATION COURSES
Description

This Oracle 11g textbook will give attention to three fundamental pillars of effective
implementation of PL/SQL applications. The PL/SQL programming language is at the core of most
Oracle database applications. First, we will explore the advanced features of the language which
allow powerful and effective database applications to be built. Next, we will discuss performance
tuning techniques which allows these applications to run efficiently. Finally, we will consider
critical security measures which should be implemented to counter hacker attacks and other
security threats. In addition to receiving the print copy of this textbook, all students may receive
e-Learning modules.

This textbook can be used for advanced self-study, or for instructor-led training in both in-class or
virtual-class environments. Textbooks can also be used for ongoing reference long after
the course is completed.

Print Textbook Available

See the list of related textbook items for a print textbook version of this eTextbook.

Audience

The target audience for this course is senior application developers. Developers who will be
building, debugging and tuning PL/SQL program units will benefit from this course.

Prerequisites

The following Sideris courses are suggested prerequisites:

• ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: PROGRAM WITH PL/SQL – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: NEW & ADVANCED FEATURES FOR DEVELOPERS

Content Summary

• Volumes: 2
• Pages: 617
• Hands-on Workshops: 16
• Exercises: 98

Objectives
The PL/SQL programming language is at the core of most Oracle database applications. This
training course will give attention to three fundamental pillars of effective implementation of
PL/SQL applications. First, we will explore the advanced features of the language which allow
powerful and adaptable database applications to be built. Next, we will discuss performance
tuning techniques which allows these applications to run efficiently. Finally, we will consider
critical security measures which should be implemented to counter hacker attacks and other
security threats.
Among the specific topics contained within these training materials are:
• Invoking external procedures and integrating these into PL/SQL applications. These include
external Java classes using the JDBC interface and external C programs contained within DLL
libraries.
• Using dynamic SQL to extend the functionality and flexibility of database programs, including
the DBMS_SQL() system-supplied package for maximum flexibility.
• Identifying SQL injection attack vulnerabilities within an application and applying
countermeasures to address security risks and protect against hacking.
• Incorporating collections and other advanced types into application logic to increase efficiency
and execution speed.
• Working with LOBs, including piece-wise data manipulation and dynamic modification of
SecureFile storage options.
• Expanding functionality with system-supplied database utility packages.
• Tuning with the DBMS_PROFILER() system-supplied package and debugging with the
DBMS_TRACE() system-supplied package.
• Writing efficient PL/SQL code and avoiding common coding mistakes.
• Enabling native compilation and execution of all database-resident program units.
• Controlling and managing PL/SQL compilation for high-efficiency execution.
• Analyze PL/SQL code structure by means of the PL/Scope facility.
• Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarachical
Profiler.
• Recognizing the dangers of SQL injection attacks against PL/SQL applications and applying
countermeasures to this potential security threat from hackers.
• Implementing fine-grained security mechanisms as part of an advanced security model using
application contexts and the Oracle virtual private database (VPD).
• Dynamic partitioning and DML parallelization using the system-supplied package
DBMS_PARALLEL_EXECUTE().

Contents

Course Outline - Volume A

DYNAMIC SQL
• ADVANTAGES & DISADVANTAGES
• NATIVE DYNAMIC SQL
• DYNAMIC UPDATE...RETURNING
• DYNAMIC SQL USING DBMS_SQL()

USING COLLECTIONS
• ABOUT COLLECTIONS
• BULK BIND USING COLLECTIONS
• COLLECTION METHODS
• MORE ABOUT THE RETURNING CLAUSE
• ADVANCED COLLECTION FEATURES
• Collection MULTISET Operations
• IN INDICES OF CLAUSE
• IN VALUES OF CLAUSE

SYSTEM-SUPPLIED PACKAGES: DBMS_METADATA() – PART I

• WHY RETRIEVE OBJECT DEFINITIONS?
• RETRIEVING DEFAULT METADATA
• RETRIEVING CUSTOMIZED METADATA
• Using OPEN() & CLOSE()
• Using SET_FILTER()
• Using SET_COUNT()
• Using ADD_TRANSFORM()
• Using FETCH DDL()
• About SYS.KU$_DDL
• About SYS.KU$_DDLS
• Calling FETCH_DDL()

SYSTEM-SUPPLIED PACKAGES: DBMS_METADATA() – PART II

• SET_TRANSFORM_PARAM()
• GET_QUERY()

SYSTEM-SUPPLIED PACKAGES: DBMS_METADATA() – PART III

• FETCH CLOB()
• SET_FILTER() FOR DEPENDENT OBJECTS
• SET_PARSE_ITEM()
• PRIMARY & DEPENDENT OBJECT DDL

SYSTEM-SUPPLIED PACKAGES: DBMS_REDEFINITION()

• ABOUT TABLE REDEFINITION
• USING DBMS_REDEFINITION()
• DBA_REDEFINITION_ERRORS
• CAN_REDEF_TABLE()
• START_REDEF_TABLE()
• FINISH_REDEF_TABLE()
• ABORT_REDEF_TABLE()
• COPY_TABLE_DEPENDENTS()
• SYNC_INTERIM_TABLE()

SYSTEM-SUPPLIED PACKAGES: DBMS_LOB()

• WORKING WITH EXTERNAL BFILES
• WORKING WITH INTERNAL LOBS
• LoadBLOBFromFile(), LoadCLOBFromFile()
• Compare()
• GetLength()
• Append()
• Copy()
• Erase()
• Trim()
• Read()
• SUBSTR()
• INSTR()
• Write()
• DYNAMIC SECUREFILE OPTIONS
• GetOptions()
• SetOptions()
SYSTEM-SUPPLIED PACKAGES: OTHERS
• COMPRESSION WITH UTL_COMPRESS()
• LZ_COMPRESS()
• LZ_UNCOMPRESS()
• DBMS_DESCRIBE()
• UTL_MAIL()
• Set SMTP_OUT_SERVER
• Calling The Send() Procedure
• Calling The Send_Attach_xxx() Procedures
• DBMS_UTILITY()
• COMPILE_SCHEMA()
• DB_VERSION()
• WAIT_ON_PENDING_DML()

ADVANCED INTERFACE METHODS

• ABOUT EXTERNAL PROCEDURES
• CALLING JAVA CLASSES
• CALLING C PROGRAMS

PL/SQL ADVANCED PROGRAMMING & CODING TECHNIQUES

• AUTONOMOUS TRANSACTIONS
• USING NOCOPY FOR PARAMETERS
• CHOOSING THE OPTIMUM DATA TYPE
• Avoiding Implicit Data type Conversion
• Choosing Between NUMBER And PLS_INTEGER
• ABOUT PLS_INTEGER
• USING SIMPLE_INTEGER
• CHAR Variables Of Different Lengths
• VARCHAR2 Variables Of Different Lengths
• CHAR VS. VARCHAR2
• CHAR VS. VARCHAR2 With An Equality Comparison
• USEFUL PL/SQL CODING TECHNIQUES
• HANDLING STRING LITERALS

INFLUENCING ORACLE PL/SQL COMPILATION

• PL/SQL COMPILER OPTIMIZATION
• PLSQL_OPTIMIZE_LEVEL
• CONTROLLING COMPILATION MESSAGES
• PL/SQL NATIVE EXECUTION

DYNAMIC PARTITIONING & PARALLELIZATION

• ABOUT DYNAMIC PARTITIONING (CHUNKS)
• CREATING & PROCESSING CHUNKS
• CREATE_TASK()
• CREATE_CHUNKS_BY_ROWID()
• CREATE_CHUNKS_BY_NUMBER_COL()
• EXECUTE_RUN_TASK()
• TASK_STATUS()
• DROP_TASK()
• MONITORING CHUNK PROCESSING

Course Outline - Volume B

APPLICATION TUNING WITH THE PL/SQL HIERARCHICAL PROFILER

• WHAT IS THE HIERARCHICAL PROFILER?
• CONFIGURING THE PROFILER
• MANAGING PROFILER RUNS
• ANALYZING PROFILER DATA
• INTERPRETING THE RESULTS
• DBMSHP_RUNS
• DBMSHP_FUNCTION_INFO
• DBMSHP_PARENT_CHILD_INFO

PL/SQL DEBUGGING WITH DBMS_TRACE()

• USING THE TRACE FACILITY
• DBMS_TRACE() TO MANAGE RUNS
• EXAMINING THE TRACE DATA
• EVENT_KIND VALUES

PROTECTING AGAINST SQL INJECTION ATTACKS

• UNDERSTANDING THE THREAT
• APPLYING COUNTERMEASURES

IMPLEMENTING VIRTUAL PRIVATE DATABASES

• UNDERSTANDING VPDS
• PREPARING FOR A VPD
• CONFIGURING A VPD
• MANAGING APPLICATION CONTEXTS
• USING SYS_CONTEXT()
• MANAGING POLICIES & SECURITY RULES

Oracle Database 12c: PL/SQL III - Advanced Programming & Tuning

Description

The PL/SQL programming language is at the core of most Oracle database applications. This
training textbook will give attention to three fundamental pillars of effective implementation of
PL/SQL applications. First, we will explore the advanced features of the language that allow
powerful and adaptable database applications to be built. Next, we will discuss performance
tuning techniques that allow these applications to run efficiently. Finally, we will consider critical
security measures which should be implemented to counter hacker attacks and other security
threats.

Print Textbook Available

See the list of related textbook items for a print textbook version of this eTextbook.

Audience

The target audience for thiseTextbook is senior application developers. Developers who will be
building, debugging and tuning PL/SQL program units will benefit from this textbook.
Prerequisites

Specific prerequisites for this textbook are the following Sideris titles, or equivalent experience:

• ORACLE DATABASE 12C: SQL FUNDAMENTALS (LEVELS I & II)

• ORACLE DATABASE 12C: PL/SQL FUNDAMENTALS (LEVELS I & II)

You can see where thiseTextbook and its prerequisites fit within the Sideris Oracle Database 12c
Application Developers curriculum.

About This Series

This textbook is part of the Sideris Oracle SQL Expert series, which in turn is one of the learning
paths from the Sideris Oracle Database 12c: SQL & PL/SQL Programming curriculum.

Content

• Volumes: 2
• Pages: 838
• Hands-on Workshops: 16
• Exercises: 99

Certification

ThiseTextbook considers subjects applicable to certification as an Oracle Advanced PL/SQL

Developer Certified Professional (OCP). The topics considered are included within "Exam 1Z0-
146: Oracle Database Advanced PL/SQL".

Objectives

The objectives for this course are as follows:

• Among the specific topics contained within these training materials are:
• Invoking external procedures and integrating these into PL/SQL applications. These include
external Java classes using the JDBC interface and external C programs contained within DLL
libraries.
• Using dynamic SQL to extend the functionality and flexibility of database programs, including
the DBMS_SQL() system-supplied package for maximum flexibility.
• Identifying SQL injection attack vulnerabilities within an application and applying
countermeasures to address security risks and protect against hacking.
• Incorporating collections and other advanced types into application logic to increase efficiency
and execution speed.
• Working with LOBs, including piece-wise data manipulation and dynamic modification of
SecureFiles storage options.
• Expanding database application functionality with advanced system-supplied database utility
packages, integrating ones applications with external mail systems, database internals and other
facilities.
• Tuning with the DBMS_PROFILER() system-supplied package and debugging with the
DBMS_TRACE() system-supplied package.
• Writing efficient PL/SQL code and avoiding common coding mistakes.
• Enabling native compilation and execution of all database-resident program units.
• Controlling and managing PL/SQL compilation for high-efficiency execution.
• Analyze PL/SQL code structure by means of the PL/Scope facility.
• Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical
Profiler.
• Implementing fine-grained security mechanisms as part of an advanced security model using
application contexts and the Oracle virtual private database (VPD).
• Dynamic partitioning and DML parallelization using the system-supplied package
DBMS_PARALLEL_EXECUTE().
• Using the wrap utility to hide the source code of database-resident programs, even from the
owner or authorized users of the programs.

Content

• Volumes: 2
• Pages: 838
• Hands-on Workshops: 16
• Exercises: 99

Certification

This textbook considers subjectsapplicable to certification as an Oracle Advanced PL/SQL

Developer Certified Professional (OCP). The topics considered are included within "Exam 1Z0-
146: Oracle Database Advanced PL/SQL".

Objectives

The objectives for this course are as follows:

• Among the specific topics contained within these training materials are:
• Invoking external procedures and integrating these into PL/SQL applications. These include
external Java classes using the JDBC interface and external C programs contained within DLL
libraries.
• Using dynamic SQL to extend the functionality and flexibility of database programs, including
the DBMS_SQL() system-supplied package for maximum flexibility.
• Identifying SQL injection attack vulnerabilities within an application and applying
countermeasures to address security risks and protect against hacking.
• Incorporating collections and other advanced types into application logic to increase efficiency
and execution speed.
• Working with LOBs, including piece-wise data manipulation and dynamic modification of
SecureFiles storage options.
• Expanding database application functionality with advanced system-supplied database utility
packages, integrating ones applications with external mail systems, database internals and other
facilities.
• Tuning with the DBMS_PROFILER() system-supplied package and debugging with the
DBMS_TRACE() system-supplied package.
• Writing efficient PL/SQL code and avoiding common coding mistakes.
• Enabling native compilation and execution of all database-resident program units.
• Controlling and managing PL/SQL compilation for high-efficiency execution.
• Analyze PL/SQL code structure by means of the PL/Scope facility.
• Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical
Profiler.
• Implementing fine-grained security mechanisms as part of an advanced security model using
application contexts and the Oracle virtual private database (VPD).
• Dynamic partitioning and DML parallelization using the system-supplied package
DBMS_PARALLEL_EXECUTE().
• Using the wrap utility to hide the source code of database-resident programs, even from the
owner or authorized users of the programs.

Contents

Advanced Programming: Why Needed & PL/SQL Execution Internals

• Why Advanced Programming?
• SQL & PL/SQL Execution Internals
• SQL & PL/SQL PGA Internals

Advanced Programming: Dynamic SQL

• Advantages Of Dynamic SQL
• Native Dynamic SQL
• Dynamic SQL Using DBMS_SQL()

Advanced Programming: Using Collections

• About Collections
• Bulk Bind Using Collections
• About SQL%BULK_ROWCOUNT()
• About SQL%BULK_EXCEPTIONS()
• Collection Methods
• More About RETURNING Clause
• Advanced Collection Features
• IN INDICES OF Clause
• IN VALUES OF Clause

Advanced Programming: Java & C Interface Methods

• Advanced Program Interfaces
• Calling Java Classes
• Calling C Programs
System-Supplied Packages: DBMS_METADATA() – Part I
• Why Retrieve Object Definitions?
• Retrieving Default Metadata
• Retrieving Customized Metadata
• Using SET_COUNT()
• Using ADD_TRANSFORM()
• Using FETCH DDL()
• Calling FETCH_DDL()

System-Supplied Packages: DBMS_METADATA() – Part II

• SET_TRANSFORM_PARAM()
• GET_QUERY()

System-Supplied Packages: DBMS_METADATA() – Part III

• FETCH CLOB()
• SET_FILTER() Dependent Objects
• SET_PARSE_ITEM()
• Primary & Dependent Object DDL

System-Supplied Packages: DBMS_REDEFINITION()

• About Table Redefinition
• Using DBMS_REDEFINITION()
• DBA_REDEFINITION_ERRORS
• CAN_REDEF_TABLE()
• START_REDEF_TABLE()
• FINISH_REDEF_TABLE()
• ABORT_REDEF_TABLE()
• COPY_TABLE_DEPENDENTS()
• SYNC_INTERIM_TABLE()

System-Supplied Packages: DBMS_LOB()

• Working With External BFILES
• Working With Internal LOBS
• SUBSTR()
• INSTR()
• Dynamic SECUREFILE Options

High-Performance: Advanced System-Supplied Packages

• COMPRESSION & UTL_COMPRESS()
• LZ_COMPRESS()
• LZ_UNCOMPRESS()
• DBMS_DESCRIBE()
• UTL_MAIL()
• DBMS_UTILITY()
• COMPILE_SCHEMA()
• DB_VERSION()
• GET_PARAMETER_VALUE()
• WAIT_ON_PENDING_DML()
• GET_TIME()
• GET_ENDIANNESS()
• DBMS_FILE_TRANSFER()

High Performance: Programming & Coding Techniques

• Autonomous Transactions
• Using NOCOPY FOR Parameters
• Choosing The Optimum Data Type
• About NOT NULL
• Useful PL/SQL Coding Techniques
• Handling String Literals
• User-Defined SQL Functions

High Performance: Influencing Oracle Pl/Sql Compilation

• PL/SQL Compiler Optimization
• PLSQL_OPTIMIZE_LEVEL
• Controlling Compilation Messages
• PL/SQL Native Execution
• Wrapping Source Code

High Performance: Dynamic Partitioning & Parallelization

• Dynamic Partitioning (Chunks)
• Creating & Processing Chunks
• CREATE_TASK()
• CREATE_CHUNKS_BY_ROWID()
• CREATE_CHUNKS_BY_NUMBER_COL()
• EXECUTE_RUN_TASK()
• TASK_STATUS()
• DROP_TASK()
• Monitoring Chunk Processing

High Performance: Using PL/SCOPE For Code Analysis

• Configuring PL/SCOPE
• PLSCOPE_SETTINGS
• Using PL/SCOPE Data

High Performance: Tuning With The Hierarchical Profiler

• What Is The Hierarchical Profiler?
• Configuring The Profiler
• Managing Profiler Runs
• Analyzing Profiler Data
• Interpreting The Results
• DBMSHP_RUNS
• DBMSHP_FUNCTION_INFO
• DBMSHP_PARENT_CHILD_INFO

High Performance: Debugging With DBMS_TRACE()

• Using The Trace Facility
• DBMS_TRACE() To Manage Runs
• Examining The Trace Data
• EVENT_KIND Values

Application Security: SQL Injection Attacks

• Understanding The Threat
• Applying Countermeasures

Application Security: Virtual Private Databases

• Understanding VPDS
• Preparing For A VPD
• Configuring A VPD
• Managing Application Contexts
• Managing Policies & Security Rules

Course Element ILT or Live Virtual Training ONSITE Classroom Training

Registration + Courseware 195,000 xaf 195,000 xaf

Training Fees 1,850,000 xaf 2,650,000 xaf
Certification Fees 585,000 xaf 585,000 xaf
 OUR CENTERS
Douala
Feux Rouge Bessengue, Imm. Residence Blue Sky, 5eme etage.
B.P:4605 Douala

Yaoundé
Imm. Standard Chartered Bank
Face Ministère de la Communication,
Avenue L’Indépendance

Buea
AnB Accountancy Building Molyko,
Contact:
+237 243 247 770;
691 246 428
651 823 228

Website: www.paraklete.global
Email: [email protected]