Installation of Oracle:

Installation of oracle 9i, 10g and 11g on Linux. Use of kernel parameters also Unix configuration.

Architecture & Configuration

• Oracle as a flexible, complex & robust RDBMS
• The evolution of hardware and the relation to Oracle
• Different DBA job roles (VP of DBA, developer DBA, production DBA)
• The changing job role of the Oracle DBA
• Environment management (network, CPU, disk and RAM)
• Instance management (managing SGA)
• Oracle table and index management

 Instance Architecture

• Instance vs. database
• Components of an instance
• Creating the OFA file structure ($DBA, bdump, udump, pfile)

• SGA vs. PGA
• Background processes
• Interfaces with server and disk I/O subsystem

 Using SQL*Plus for DBA management

• Connecting and executing SQL
• Using the “as sysdba” syntax
• Overview of SQL*Plus DBA commands

 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
• 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 log concepts for recovery
• Online redo log (log_buffer) online redo logs 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

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

Oracle database management

• Parameter files (init.ora, listener.ora, tnsnames.ora)
• Rules for sizing SGA components
• Automated Oracle memory management (AMM)

• Creating the init.ora file
• Using spfile
• Displaying init.ora values with v$parameter

• Inside the Oracle data buffers
• Using the KEEP pool
• Monitoring buffer effectiveness
• Using multiple blocksizes (multiple buffer pools)
• 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-6: Troubleshooting network connectivity

 Oracle*Net configuration

• Creating the listener.ora file
• Creating the tnsnames.ora file
• Verifying network connectivity with ping and tnsping
• Testing database links

Oracle object management

• Types of Oracle tables (regular, IOT, sorted hash clusters, nested tables)
• Oracle Views
• Oracle materialized views
• 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
• Costs & benefits of constraints
• Types of Oracle indexes constraints (check, not null, unique, PK, FK)
• Cascading constraints

 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)


 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)


Oracle DBA Utilities

• Data pump (Imp and exp utilities)
• SQL*Loader
• LogMiner
• Flashback
• Oracle DBA utilities – Oracle dbms packages (dbms_redefinition)

Monitoring Oracle

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
• 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
• Oracle automated workload tools
• Using v$bh to monitor buffer activity
• Using v$sql and v$sql_plan

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 (Ion, Excel)
• 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


STATSPACK and AWR performance management

• Installing STATSPACK
• Running STATSPACK reports
• Interpreting a STATSPACK report
• Getting time series reports with STATSPACK
• Finding performance signatures with STATSPACK


Performance Tunning

Bottleneck performance analysis

• Drill-down into AWR reports
• Top-5 timed events
• External Server Bottlenecks (Network, I/O, RAM, CPU)
• Network troubleshooting
• 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)
• 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 skewonly

•  Tunning of init parameters.

Tracing SQL Execution

• Using EXPLAIN PLAN
• Using “set autotrace”
• Interpreting EXPLAIN PLAN Output
• Using TKPROF / SQL*Trace

 Backup & Recovery

• OS-level backups
• Hardware-level backup & recovery
• Block-level change tracking
• Disk mirroring
• Backup & recovery and RAID level
• Oracle-level backups (expdp & RMAN)
• Hot vs. Cold backups

• Rman Backup with block recovery.

                • Rman backup using 3^rd party media manager like Tivoli and veritas.

 Patch Management:

          Cpu Patches

·         PSU Patches

·         Version upgradation

New Features of oracle: Like 10g & 11G