SQL Oracle DBA

1.Introduction to database management systems and databases

(a) What is a database?

(b) Database management system (DBMS) software and

(c) Why Oracle DBMS.

(d) History and Current trends of DBMS and Database Administration.

(e) Oracle database market share and about its current competitive landscape.

(f) Tasks of a Database Administrator.

 2.Relational Database Model and Installing Oracle database software (11g R2)

(a) Designing a relational database using Entity-Relationship Modeling.

(b) Understand pre-requisites for a successful Oracle RDBMS software installation.

(c) Installation of Oracle 11g R2 database software.

3.Oracle Database Architecture

(a) Planning for an Oracle database creation.

(b) Creating an Oracle database using the MANUAL method.

(c) Creating an Oracle database using the Graphical Tool (Database Configuration Assistant).

(d) Startup and Shutdown of a database.

(e) Components of an Oracle database and detailed architecture.

(f) Oracle database memory and process architecture.

(g) The Oracle database job scheduler API (DBMS_SCHEDULER package)

4.Database storage

(a) Oracle tablespaces and allocation types.

(b) How to create small file (default) and big file tablespaces.

(c) How to create datafiles with fixed size and autoextend option.

(d) What are control files and how to view control file content in a trace file.

(e) How to rename datafiles in a control file.

(f) Oracle Redo log File architecture and best practices.

(g) Enabling Archive log mode for a database with automatic archiving.

(h) Temporary tablespaces and temporary table space groups (11g R2 new feature).

5.Oracle Networking

(a) Introduction to Oracle Net Services component.

(b) Oracle listener configuration (Dynamic and Static Listeners).

(c) Dynamic Service Registration and Naming Methods configuration in sqlnet.ora.

(d) Oracle database connections from other computers.

(e) Using Oracle netca (Network Configuration Assistant) for network configuration file tnsnames.ora.

(f) Oracle listener administration and TNS_ADMIN environment variable.

(g) What are database links and learn about best practices in creating them.

 6.User Administration and Security

(a) How to create database users.

(b) How to set database profile limits for a user for efficient resource management.

(c) Unlocking a user account and enabling password management.

(d) Oracle database authorization using object and system privileges

(e) Oracle database authorization using database roles

(f) How to audit Oracle database connections in XML mode and database mode (AUD$ table)

(g) How to audit Oracle DBA commands (SYS Operations).

7.User Managed (Manual) Backup and Recovery

(a) Introduction and planning for a robust Backup and Recovery Strategy.

(b) How to take an Oracle database backup in offline mode (COLD BACKUP).

(c) Hot to take an Oracle database backup in online mode (HOT BACKUP).

(d) How to clone a database using Cold Backup.

(e) How to clone a database using Hot Backup.

(f) How to recreate source database from a user-managed backup (Media failure scenario).

8.RMAN Backup and Recovery

(a) Advantages of RMAN method compared to manual method.

(b) Database backup using RMAN in normal and compressed mode.

(c) Oracle database backup metadata.

(d) Database backup using RMAN catalog.

(e) Querying the RMAN metadata.

(f) How to clone a database using RMAN duplicate (11g R2 new feature: Backup based duplication).

(g) How to restore and recover a database using its backup in the event of a media failure.

(h) Oracle database backup retention policies and backups to Tape/offsite.

(i) Fast Recovery Area for better management of backups and archive log files.

(j) How to purge old backups using RMAN.

  1. Oracle Data Movement Utilities

(a) Oracle database logical backup and restore operations (Data Pump Export and Import).

(b) Business use cases of logical backups compared to physical backups.

(c) Various Data Pump Export modes.

(d) Data Pump Import to import data from an export backup.

(e) Advanced features in Oracle Data Pump Export.

 10 .Oracle Database Performance Tuning

(a) How to handle performance issues in the real world.

(b) Dynamic views for performance monitoring and analysis.

(c) How to size the Oracle database memory structures for optimal performance.

(d) Oracle database wait events.

(e) Explain Oracle UNDO management internals and sizing.

(f) Explain Oracle TEMP space internals and sizing.

(g) How to trace database sessions using DBMS_MONITOR and DBMS_SESSION packages.

(h) How to format SQL trace files using the tkprof utility.

(i) Database wide tracing for Oracle Errors (Event Tracing).

(j) Index Monitoring and internals of index maintenance by Oracle.

(k) Statistics data collection for Oracle database optimizer.

(l) What is Oracle automatic workload repository (AWR) and how to obtain AWR reports.

 11.Automatic Storage Management (ASM)

(a) Introduction and benefits of Oracle ASM.

(b) Oracle ASM 11g software installation and configuration (Grid Infrastructure)

(c) Partitioning Disks in LINUX.

(d) Oracle ASM Lib Utility for managing ASM Disk Storage.

(e) Oracle 11g ASM Configuration Assistant.

(f) ASM Parameter file, ASM Instance and working with DISKGROUPS.

(g) Querying ASM Information.

(h) Creating Database Files using ASM.

 12.Automatic Storage Management (ASM): Mirroring Disks

(a) Understanding ASM Disk Redundancy (Mirroring)

(b) Learn about the three levels of ASM redundancy

(c) How Oracle manages ASM Disk failures

(d) Replacing a failed disk with a new disk

(e) ASM Disk Rebalancing operations

13 .Oracle 11g Real Application Clusters (RAC)

(a) Components of a RAC environment.

(b) Storage, network and hardware requirements.

(c) Pre-requisites for RAC installations.

(d) Installation and configuration of clusterware.

(e) How to validate the configuration and install.

(f) The Grid and RAC installation.

  1. RAC Databases

(a) Creation of RAC database and instances.

(b) Differences of RAC database versus single instance.

(c) Database and instance parameters.

(d) How to change parameters.

(e) Connection information for high availability (TAF, FAN and FCF).

(f) Single client access name (SCAN).

  1. Managing RAC Databases

(a) Database services and SRVCTL.

(b) Using restart utility.

(c) Adding and removing a node to the RAC environment.

(d) Backup of databases and parameter files.

(e) Rolling Patches of RAC environment grid and database.

  1. Testing and Monitoring RAC

(a) Clusterware testing.

(b) Failover testing and validations.

(c) Troubleshooting configuration and failures.

(d) Monitoring interconnect and performance.

  1. Oracle Data Guard

(a) Using standby databases in HA environments.

(b) Active Standby features.

(c) Decisions for syncing standby databases.

(d) Requirements for different sync options.

(e) Getting the requirements and parameters set for primary and standby databases.

(f) Create standby database.

  1. Managing Data Guard Environment

(a) Configuring Data Guard broker (DGMGRL).

(b) Validating the configuration.

(c) Commands for managing Data Guard environment.

(d) Failing over to standby.

(e) Recreating Primary.

(f) Monitoring syncing the standby

 

SYLLABUS CONTENTS CAN BE MODIFIED BASED ON STUDENTS REQUIREMENTS

Informatica Power Center – V 9.5.1

  • One – to – One Mapping
  • Customizing the Source Qualifier
  • Concatenating First_Name, Last_Name and other Row Level Functions
  • Implementing if – else statement using Expression Transformation
  • How to filter the data using Filter Transformation
  • Multiple Filter Conditions using Filter Transformation
  • Need of Router of Transformation
  • Joiner Transformation
  • Transposing Columns into Rows using Normalizer Transformation
  • Transposing rows into columns using variable port and aggregator
  • transformation.
  • Combining Multiple Pipelines – using Union Transformation
  • Usage of Lookup Transformation
  • Unconnected Lookup
  • Update Else Insert at Session Level
  • Department wise MaxSalary – using Aggregator Transformation
  • Update Strategy Transformation
  • Update Else Insert at Mapping level SCD Type – 1
  • Sorter Transformation
  • Fixed Width and Delimeter Flat File
  • Populating Surrogate Key – using Sequence Generator
  • Source First Record into First Target and Second Record into Second Target
  • Multiple Flat Files into the target – Indirect File Type
  • Populating Source FlatFile Name in the target table
  • Effective Date – SCD– Type 2
  • Preventing source duplicate records using Lookup Transformation
  • Cumulative Salary
  • Variables – Incremental Logic (CDC)
  • Control Table Logic
  • Committing at Mapping Level
  • Dynamic File Generation
  • Generating sequence numbers without using Sequence Generator  Transformations
  • Assignment Task
  • Event Wait – File Watch Mechanism
  • Worklet
  • Command Task
  • Email Task
  • Stored Procedure
  • Differences between Informatica 7 / 8 and 9 versions.
  • Incremental Logic Using Parameter File
  • Complex Mapping
  • Assigning a value to workflow level variable – using Assignment task.
  • Handling Error Data
  • Loading Unique Records into one table and duplicate records into another
  • How to eliminate or avoid duplicate record from flat file to relational table.
  • Generating multiple records in target, based on source column
  • updating the target table, if target doesn’t have Primary key
  • Performance tuning in Informatica
  • How to Load half records into one Target another half records into one target
  • Ways of creating Parameter file (Types of parameter files)
  • Development Code Standards
  • How to skip header and footer records while processing Source Files.
  • Handling Surrogate Key, if same target table populated by two mappings and
  • corresponding sessions execute parallelly.
  • Functioning the loops in Informatica while processing from source to target
  • Skipping or processing last nth records from Source Flat File
  • Unit Test Plan
  • Difference between Source Qualifier, Joiner and Lookup transformation.
  • Difference between Dynamic Lookup Cache and Static Lookup Cache.
  • Difference between Connected Lookup and Unconnected Lookup
  • Development Lifecycle
  • Differences between Waterfall and Agile Methodology
  • How to process multiple files without using indirect method
  • How to transpose rows into columns

 

SYLLABUS CONTENTS CAN BE MODIFIED BASED ON STUDENTS REQUIREMENTS