Course Outline: Oracle 11g & 12c OCP & DBA Training
See the latest outline of our Course here: Course Outline: Oracle 11g & 12c OCP & DBA Training
Oracle Database Design, Creation, Management & Administration
(18 Weeks /14 hrs a week Course Outline)
Intended Audience:
This Track is intended for anyone who is absolutely interested in becoming an Oracle Database Administrator and is willing and available to hang in and push on with dedicated working Oracle Professionals (DBAs, Developers, and Programmers) from whom they can benefit from some real life work experiences. The candidates will be directed on how and where to acquire information as professional DBAs do to survive as leaders in the work environment. At least 50% of the class time shall be devoted to hands on exercises. A desirable prerequisite for this class shall be the completion of a course in SQL or PL/SQL since the Track shall be primarily focused just on preparing the audience to the administration of the Oracle database.
Participants will understand and master the full concept and steps or process of working as a DBA from this succinct and unequivocal Oracle training course. They will practically Create a Class database: setting the environment, starting an Oracle Instance, creating a Database, tablespaces, Rollback segments, tables, users, data dictionary, and other objects, based on data design models, and the commands to maintain and modify these data structures. Participants will learn how to insert, update and delete data using SQL’s Data Manipulation Language, (DML). In addition, they will be able to write database transactions that perform the insertion, modification, and deletion of data. Finally, this Track will demonstrate how to control both user and object level security in an Oracle database.
Recommended Stationery & Books:
- Buy a 2” folder for DBA Notes from the class.
- 3-4 Subject Note book – 9-1/2” x 6” x 15.2cm
- 1 Dozen Assorted Color Sharpie Permanent Markers, Pens & Colors
- White Poster boards – 22” x 28”– pack of 10
- OCA/OCP Oracle Database 11g “All-In-One Exam Guide” – By John Watson, Roopesh Ramklass, Bob Bryla
- OCA/OCP Oracle Database 12c “All-In-One Exam Guide” – By John Watson, Roopesh Ramklass, Bob Bryla
- High powered intel i7 Laptop with minimum 12 GB RAM, 1 TB hard drive – preferably and HP
(Advanced DBAs Class)
- Expert Oracle RAC 12c (The Expert’s Voice) by Syed Jaffar Hussain
- Oracle Enterprise Manager Cloud Control 12c Deep Dive Michael New (Author), Edward Whalen (Author), Matthew Burke (Author)
Books Online: Print out notes from these sites.
- http://www.intelligentedu.com/free_it_certification_study_guides.html#Oracle
Course Summary
You shall be taught and mentored amongst many things, the following:
1) General Computers & Operating Systems: Windows, Unix / Linux
2) Server Configuration: Linux Server & Windows Server
3) Linux (Unix) Server commands & Configuration (Main Operating System)
4) Scripting: SQL & Shell scripts
5) Cloud Computing & Virtualization: VMware & Virtual box
6) Oracle Database Administration (Main Course) – DB configuration, Backup & Recovery, DB Security, Performance Monitoring, Diagnosis & Tuning; etc.
7) Database Architecture – SQL Developer, Toad & Erwin – Forward & Reverse Engineering
8) Microsoft SQL Server Database Administration
9) Documentation and Resume compilation
10) Oracle DBA Certification
11) Technical Resume writing
12) Interview Preparation
13) Job Search Skills & Assistance
14) Etc.
DBA Course Outline
MONTH 1 – Architecture, DB Configuration & Unix
1-1: Overview of Oracle DBA tasks
• The evolution of hardware and the relation to Oracle
- Different DBA job roles (VP of DBA, developer DBA, production DBA, database Babysitter)
Responsibilities of a DBA
- What do DBAs do
- What must DBAs know
- What could they be asked at Interviews
The Oracle General Architecture
- Environment management (network, CPU, disk and RAM)
- Physical, Memory, & Process Structures
* SGA = Database Buffer Cache + Redo Log Buffer + Shared Pool + Cursors + etc
1-2: Virtualization of Machines and Instances
Virtualization of O/S & Instance Architecture
- What is Virtualization
- Install Oracle Virtual box & Creating Virtual Machines
- Starting and Stopping Virtual Machines
* PGA / UGA
* Processes: – User processes; Server processes, Background processes: dbwr, lgwr, ckpt, smon, pmon, arch, reco, dnnn, lckn
* Physical Files : data files, redo log files, control files, archive logs, flashback logs, pfile (init.ora), spfle, pswd file
-
- Instance -> SGA + Background Processes.
- Database -> Instance + Data Files
- Oracle as a flexible, complex & robust RDBMS
- Instance management (managing SGA regions)
- Oracle table and index management
1-3: The Oracle Data Block
- Header, Free space, Data space
- Review of the SGA:
Required SGA Components:
- Shared Pool: Caches the most recently used SQL statements that have been issued by database users.
- Database Buffer Cache: Caches the data that has been most recently accessed by database users.
- Redo Log Buffer: Stores transaction information for recovery purposes
1-4: Logical & Physical Structures
- Tablespaces –> Segment -> Extents -> Data block
- Segments: data segments, undo/rollback segments, temporary segments, index segments, etc
- Files: data files, redo log files, control files, pfile (initSID.ora), spfile, pswd file
- Types of Buffers: free, dirty & pinned buffers, Dirty List, LRU & MRU
1-6: The OFA = Optimal Flexible Architecture
- Requirements & Benefits of OFA
- $ORACLE_BASE & $ORACLE_HOME
1-4: Oracle Installation on Windows Server
- Configure Windows Server
- Create Account at OTN
- Download Oracle software & Install following OFA
- Create a Database ( CLASSD) using DB Assistant dbca and as per OFA
1-7: Oracle*Net configuration – nca
Architecture and Basic Configuration – using Assistants & Manually: Net8: Sqlnet.ora, listener.ora & tnsnames.ora
- Creating the listener.ora file
- Creating the tnsnames.ora file
- The sqlnet.ora file
- Troubleshooting network connectivity
• Verifying network connectivity: ping & tnsping
• Testing database links
Quiz #2. (Diagram of the Oracle Architecture and Multiple choice)
1-8: Connecting to the Database
- Using db Control – url
- Using SQL*Plus for DBA management
Connecting and executing SQL
Using the “as sysdba” syntax - Overview of SQL*Plus DBA commands (startup , etc.)
1-8,9,10: SQL – DB Languages – SQL =: DDL, DML, DCL, TCL, DR & Using the Scott Schema
Prep for OCA
- Unlock Scott schema
- Exam samples questions
- DML – Select – Types of Joins etc
- Unix Operating System
- Differences between Unix and Windows @ GUI & Command line
- Unix Commands for a DBA
- vi editor
1-11: Creating Database Objects
- Creating tablespaces, users, tables, indexes, sequences, synonyms, database links, etc
- Managing Storage Allocation, Undo/Rollback Segments (ora-1555), Table and Index Segments
- Manage Cluster Segments, Constraints, Roles, Resource Usage, Audit Database, Users: DB Access
1-12 Unix Commands common to DBAs
- Telnet and FTP
- Copy and moving files
- Creating Users and Permissions
MONTH 2 – Unix, Management, Backup & Recovery, Migration
2-1: Upgrade of 10g to 11g rdbms
- Backup current Windows Databases
- Download & Install Oracle 11g in Windows
- Apply necessary patches
- Upgrade using dbuca; drop database after testing
- Repeat Upgrade using Oracle datapump
- Configure DB for production with all scripts – backup etc .
2-3: The Oracle Architecture con’t
Optional SGA Components:
-
- Java Pool: Caches the most recently used Java objects and application code when Oracle’s JVM option is used.
- Large Pool: Caches data for large operations such as Recovery Manager (RMAN) backup and restore activities and Shared Server components
- Streams Pool: Caches the data associated with queued message requests when Oracle’s Advanced Queuing option is used
2-4 Oracle in Unix/Linux
- Download & Install Oracle Oracle 11g in Linux
- Create a Database CLASST in Linux
- Configure Sqlnet and connect via db_control
- Download & Install Oracle Oracle 12c in Linux
- Initialization file management – The INIT.ORA
• Creating the spfile.ora from the init.ora file
• Using spfile
• Displaying init.ora values with v$parameter
Oracle database management
2-5: Control file, UNDO and REDO management
• Explaining the use of control files
• Listing the Contents of the control File
• File locations for control Files
• Obtaining Control File Information
• Listing control file contents
UNDO management
• Displaying and Creating Undo segments
• Altering Undo Segments
• Determining the Number and Size of Undo segments
• Understanding flashback technology
• Troubleshooting Undo – snapshot too old
REDO management
• Redo log concepts for recovery
• Online redo log (log_buffer) and archived redo logs
• Oracle ARCH and LGWR background processes
• Redo log dictionary queries
• Redo log switch frequency and performance
• Multiplexing the Online Redo Log Files
• Archiving the Oracle Redo Logs
• Recovery using the redo log files
2-6: User and privilege management
• The three security methods (VPD, Grant security/role-based security, grant execute)
• Creating New Database Users
• Using pre-spawned Oracle connections
• Auditing User activity
• Identifying System and Object Privileges
• Granting and Revoking Privileges
• Creating and Modifying Roles
• Displaying user security Information from the Data Dictionary
2-7: Data Dictionary
- Data dictionary Tables & Views essential for Administration Tuning & Backup
- Dynamic Performance Tables ( V$Tables), DBA Views, USER Views, All Views.
2-8: Overview of instance management
- Parameter files: init.ora, spfile.ora, listener.ora, tnsnames.ora
• Rules for sizing SGA components
• Automated Oracle memory management (AMM)Data buffer configuration & sizing
• Inside the Oracle data buffers
• Using the KEEP pool
• Monitoring buffer effectiveness
• Using multiple blocksizes (multiple buffer pools)
Shared pool and PGA configuration & Sizing
• Shared pool concepts and components
• Understanding the library cache
• Relieving shared pool contention
• Overview of PGA for sorting and hash joins
• Using sort_area_size, hash_area_size and pga_aggregate_target
2-9: Oracle tables, views and materialized views
• Types of Oracle tables (regular, IOT, sorted hash clusters, nested tables)
• Oracle Views
materialized views
• Uses of materialized views
- Creating, updating and managing materialized views
Oracle indexes
• Types of Oracle indexes (b-tree, bitmap, bitmap join index)
• Creating B*-Tree, bitmap and function-based Indexes
• Function-based indexes
• Finding indexing opportunities
• Index maintenance
Oracle constraints
• Costs & benefits of constraints
• Types of indexes & constraints (check, not null, unique, PK, FK)
• Cascading constraints
2-10: Schema, File & tablespace management
• Describing the relationship between data files, tablespaces, and table
• Understanding Oracle segments
• Creating Tablespaces – using the autoextend option
• Changing the Size of Tablespaces – alter database datafile command
• Defining a TEMP tablespace
• Changing the default storage Settings for a tablespace
• Review of the storage parameters in DBA views (ASM, ASSM, pctfree, pctused and freelists).
• Monitoring Chained rows (fetch continued rows)
• Monitoring Insert and Update performance (pctused, APPEND)
2:11 Backup, Failures, Recovery & Restoration
- Review of Oracle Architecture
- Logical & Physical Backups
- Backup Methods: Hot (open) & cold (close) Backups,
- RMAN & Export Datadump Configurations
- BCT – block change tracking
- Failure Scenarios
- Recovery without Archiving; Enable Archiving
- Complete & Incomplete Recovery with Archiving
- Standby Database
- Backup Strategizing
2:12 Backup
- Flashback – Types of flashbacks
- A Step-by-Step Guide to Disaster Recovery Planning
- Scripting & Scheduling backups – cron, AT, task Scheduler
MONTH 3 Migration, Patching, Monitoring & Tuning
3-8: MIGRATION: Loading/Populating a Database & Database Restructuring
- Import / Export -> ExpDp / ImpDp
- Database Migration Assistant
- SQL Loader (Flat files / non-oracle data)
- Create table AS Select * from — & Insert select * from —
- Restructure the ORCL database using ‘backup control file to trace’
3-9: Patching the Oracle Software
- Types of Oracle Patches: Patchset, CPU, SPU, Interim patch, hotpatch, rolling patch
- What is opatch, OUI?
- Installing & Using Opatch & OUI (Windows & Unix)
- CPU = Critical Patch Update – Best Practices – Optimizing CPU Implementation.
DB Documentation: using Scripts & RDA
Using Remote Diagnostic Agent a.k.a. RDA
- Download, Installation, and Usage of rda
3-10: Oracle DBA Utilities
• Data pump (Imp and exp utilities)
• SQL*Loader – do Example with Class Schema.
• LogMiner • oerr
• Flashback
• DataGuard
• Oracle DBA utilities – Oracle dbms packages (dbms_redefinition)
• Replication (Streams, multimaster, materialized views)
Database Development & ER Diagrams
- Reverse and Forward Engineering using Erwin
- Design Class Schema using Erwin & Oracle Data modeler.
3-11: Oracle 12c – Cloud Computing Installing
- Types of clouds: Private, Public & Hybrid clouds
- Oracle Cloud Platform & Cloud Management Virtualization
- oracle cloud services
- Installing Oracle 12c & Creating CDB & PDB
- Differences between 11g & 12c
• Backup & Recovery - Monitoring & Tuning
MONTH 4: DataGuard, RAC, Ms SQL Server
4-1: Dictionary and v$ views
• The dba_, all_ and user_ structures
• Querying the tables, indexes, and segments views
• Querying the AWR (STATSPACK) tables
Table & index monitoring
• Monitoring table extents and fragmentation
• Using the dba_tables and dba_segments views
• Monitoring table CBO statistics
• Monitoring table extents and fragmentation
• Locating chained rows – Oracle block
• Monitoring table & index growth
• Monitoring index usage
• Monitoring index fragmentation
• Locating un-used indexes
• Identifying IOT candidates
• Reorganizing Indexes with alter index rebuild
• Dropping Indexes
• Getting Index Information from the Data Dictionary
workload & trend monitoring
• Oracle automated workload tools
• Using v$bh to monitor buffer activity
• Using v$sql and v$sql_plan
4-2: Instance monitoring
• Monitoring with the AWR and STATSPACK
• Creating a time-series performance report
• Using www.statspackanalyzer.com
• Scripts for AWR and STATSPACK
• Plotting performance data (WISE, Excel, etc)
• Finding performance trends and signatures
Oracle environment monitoring
• Displaying and managing Oracle sessions (v$session, v$process)
• Using AWR to monitor disk, network and CPU consumption
• Monitoring the alert log
• Oracle trace/dump files
Viewing Information About the SGA – The V$ views involved.
4-3: Database Maintenance
• Reason for reorgs – chained rows, imbalanced freelists
• Reorganizing Tables using Export and Import
• Using CTAS to reorganize data
• Index rebuilding
• Backup & Recovery overview (hot & cold Backups, RMAN, block change tracking)
AWR & ADDM – performance management
This section explores the methods used for performance management in Oracle and shows tips and scripts for monitoring all components of any Oracle database. You will also learn the proper action to take when any area of Oracle becomes a bottleneck.
Bottleneck performance analysis
• Drill-down into AWR reports
• Top-5 timed events
• External Server Bottlenecks (Network, I/O, RAM, CPU)
• Network troubleshooting
Instance Tuning
• Changing init.ora optimizer parameters (index_optimizer_cost_adj, optimizer_mode)
• Managing region parameters (shared_pool_size, db_cache_size)
• Understanding instance contention (e.g. Buffer busy waits, library cache contention)
SQL and CBO behavior
• Introduction to cost-based optimization
• Changing the default optimizer modes
• Optimizer parameters
• Dynamic sampling
• Collecting table and index statistics (dbms_stats)
• Using column histograms and skew only
4-4: Oracle Data Replication
- Oracle Streams
- Streams Configuration
- Oracle GoldenGate
DataGuard (STANDBY DATABASES)
Ensuring Disaster Recovery for the Enterprise
- Introduction to STANDBY
- Standby Configuration – see notes
- Step-by-Step Instructions for Creating a Physical Standby DB
- Protection Modes
- DataGuard Physical vs Logical Standby
- Recovering Logical Standby
4-5: RAC & RAC One Node
- Introduction to RAC: TAF, CRS, ocr, srvctl,
- Oracle RAC One Node – uses “instance relocation”
- RAC Configuration – see notes
- Startup / Shutdown of RAC
- Sample Interview Questions.
4-6: MS SQl Server
- Installation of SQL Server
- Create Database, Users, objects
- Backup, Recover, Restore
- Monitor & Tune SQL Server DB.
Database link from Oracle to SQL Server
Configure odbc connection
Modify Listener.ora and tnsnames.ora files
Create Database link
See example: http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
4-7: Big Data – Hadoop & Oracle NoSQL Database
- Creating and accessing Oracle NoSQL Database
- Administration of Oracle EBS – Enterprise Busines Suite (aka E-Business Suite (EBS/ e-BS) Release 12 (R12)) (Intro)
- Administration of OBIEE – Oracle Business Intelligence Enterprise Edition – (Intro)
Concepts and Architecture Installation (Oracle 11g on Linux)
Configuring the Manager process performing initial data load
configuring online change synchronization,
Data Pump process, DDL, configuring DDL synchronization
Filtering and Mapping data Monitoring GoldenGate
performing a zero downtime cross platform migration and 11g database upgrade
http://gavinsoorma.com/2010/03/oracle-goldengate-tutorials-now-available/
Extras
Miscellaneous
4-7
Quiz #9. (Test covering Entire course)
OCA or OCP Preparation & Interview Questions – Rehearsals
- Interview Practical and Strategic Planning
- Resume Techniques & Preparation
Live Referencing Project
Purpose:
Every Student of the class is considered as worker as will and has the responsibility of engaging in this real life project and meeting all the date lines as shall be stipulated in class by the coordinator. Punctuality, dedication, attention to details and meeting date lines is the most important priorities for the project and its successful completion. Assistance from senior DBAs is available upon request during and after classes. Work references shall be provided only to those who successfully complete the project within the given the given time completing the course without this LR Project shall not warrant a reference from the institution. Our goal is to train practical DBAs who can handle typical DBA responsibilities comfortably upon completion of the course and this project. All work must be neatly presented in a word document (LR_Project) by all DBA candidates. Expect this LR_Project.doc to be at least 25 pages long.
See Details on Project Handout
Quiz #5. (Diagram of the Oracle Architecture including all background processes and V$tables)
Performance Monitoring, Trouble Shooting & Tuning
- Monitoring tools/ Third party tools: DBArtisan, PSL, OPM, Toadsoft, Q-savant,
- Diagnosing Problems;
- Architecture & Tuning: Diagrammatic relationships between Processes, V$tables & Files.
- Views, Scripts, OEM Diagnosing & Tuning Packs
- Tuning Memory: Shared Pool, Buffer Cache, Redo Buffer cache
- Tuning Redo Mechanisms, Tuning Sorts, Tuning Rollback Segments
- Applications Tuning: SQL_Trace, Oracle_trace, Tkprof,
- Explain plan: Plan table
- Optimizing Load; I/O and Contention
- Tuning Advisors
Quiz #6. (Diagram of the Oracle Architecture)
Managing Space Usage (Capacity planning)
- Planning for Fault Tolerance: – Mirrored Control Files & Mirrored Redo Log Files
- Tablespaces & Data Files Management (tablespace creation)
- Database Block Size Management: – Pctfree & Pctused
- Controlling Extent Allocation: – Initial – Next – pctincrease – minextents – maxextents
Capacity Planning and ER Diagrams
- Calculating table, tablespace, Database sizes; Spreadsheet calculations.
- Block size determination
Internet & the New DBA; Requested discussion Topics
- Job Hunting
Web sites for the DBAs
Getting your resume on the NET
- Technical Help: Sites needed by the DBA; CSI
Day 48.
4-8: A picture is worth a thousand words – Architecture Drawing
See Sample Oracle Architecture Drawing below – All should be able to draw this sample in about 30 minutes.
- Final Oracle Architecture Drawing – comparing drawings
- General Revision.
Hebrews 11:27
By faith he left Egypt, not fearing the king’s anger; he persevered because he saw him who is invisible.
James 1:12
Blessed is the man who perseveres under trial, because when he has stood the test, he will receive the crown of life that God has promised to those who love him..
Proverbs 27:17 As iron sharpens iron, so one man sharpens another.
We are called the Bereans at “Bereans Technology” for our eagerness, persistence and resolve in getting facts on the things we do as per
Acts 17:10-12 – ”Now the Bereans were of more noble character than the Thessalonians, for they received the message with great eagerness and examined the Scriptures every day to see if what Paul said was true.”
Amen.
Cho