ORACLE 11G DBA Training

• List the features of Oracle Database 12c




• Discuss the basic design, theoretical, and physical aspects of a relational database




• Categorize the different types of SQL statements




• Describe the data set used by the course




• Log on to the database using SQL Developer environment




• Save queries to files and use script files in SQL




• Developer

• List the capabilities of SQL SELECT statements




• Generate a report of data from the output of a basic SELECT statement




• Select All Columns




• Select Specific Columns




• Use Column Heading Defaults




• Use Arithmetic Operators




• Understand Operator Precedence




• Learn the DESCRIBE command to display the table structure

• Write queries that contain a WHERE clause to limit the output retrieved




• List the comparison operators and logical operators that are used in a WHERE clause




• Describe the rules of precedence for comparison and logical operators




• Use character string literals in the WHERE clause




• Write queries that contain an ORDER BY clause to sort the output of a SELECT statement




• Sort output in descending and ascending order

• Describe the differences between single row and multiple row functions




• Manipulate strings with character function in the SELECT and WHERE clauses




• Manipulate numbers with the ROUND, TRUNC, and MOD functions




• Perform arithmetic with date data




• Manipulate dates with the DATE functions

• Describe implicit and explicit data type conversion




• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions




• Nest multiple functions




• Apply the NVL, NULLIF, and COALESCE functions to data




• Use conditional IF THEN ELSE logic in a SELECT statement

• Use the aggregation functions to produce meaningful reports




• Divide the retrieved data in groups by using the GROUP BY clause




• Exclude groups of data by using the HAVING clause

• Write SELECT statements to access data from more than one table




• View data that generally does not meet a join condition by using outer joins




• Join a table to itself by using a self-join

• Describe the types of problem that sub-queries can solve




• Define sub-queries




• List the types of sub-queries




• Write single-row and multiple-row sub-queries

• Describe the SET operators




• Use a SET operator to combine multiple queries into a single query




• Control the order of rows returned

• Describe each DML statement




• Insert rows into a table




• Change rows in a table by the UPDATE statement




• Delete rows from a table with the DELETE statement




• Save and discard changes with the COMMIT and ROLLBACK statements




• Explain read consistency

• Categorize the main database objects




• Review the table structure




• List the data types available for columns




• Create a simple table




• Decipher how constraints can be created at table creation




• Describe how schema objects work

• Create a simple and complex view




• Retrieve data from views




• Create, maintain, and use sequences




• Create and maintain indexes




• Create private and public synonyms

• Differentiate system privileges from object privileges




• Create Users




• Grant System Privileges




• Create and Grant Privileges to a Role




• Change Your Password




• Grant Object Privileges




• How to pass on privileges?




• Revoke Object Privileges

• Add, Modify, and Drop a Column




• Add, Drop, and Defer a Constraint




• How to enable and Disable a Constraint?




• Create and Remove Indexes




• Create a Function-Based Index




• Perform Flashback Operations




• Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP




• Query External Tables

• Explain the data dictionary




• Use the Dictionary Views




• USER_OBJECTS and ALL_OBJECTS Views




• Table and Column Information




• Query the dictionary views for constraint information




• Query the dictionary views for view, sequence, index and synonym information




• Add a comment to a table




• Query the dictionary views for comment information

• Use Subqueries to Manipulate Data




• Retrieve Data Using a Subquery as Source




• Insert Using a Subquery as a Target




• Usage of the WITH CHECK OPTION Keyword on DML Statements




• List the types of Multitable INSERT Statements




• Use Multitable INSERT Statements




• Merge rows in a table




• Track Changes in Data over a period of time

• Time Zones




• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP




• Compare Date and Time in a Session’s Time Zone




• DBTIMEZONE and SESSIONTIMEZONE




• Difference between DATE and TIMESTAMP




• INTERVAL Data Types




• Use EXTRACT, TZ_OFFSET and FROM_TZ




• Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

• Multiple-Column Subqueries




• Pairwise and Non-pairwise Comparison




• Scalar Subquery Expressions




• Solve problems with Correlated Subqueries




• Update and Delete Rows Using Correlated Subqueries




• The EXISTS and NOT EXISTS operators




• Invoke the WITH clause




• The Recursive WITH clause

• Use the Regular Expressions Functions and Conditions in SQL




• Use Meta Characters with Regular Expressions




• Perform a Basic Search using the REGEXP_LIKE function




• Find patterns using the REGEXP_INSTR function




• Extract Substrings using the REGEXP_SUBSTR function




• Replace Patterns Using the REGEXP_REPLACE function




• Usage of Sub-Expressions with Regular Expression Support




• Implement the REGEXP_COUNT function

• Oracle Database Architecture Overview




• Oracle ASM Architecture Overview




• Process Architecture




• Memory structures




• Logical and physical storage structures




• ASM storage components

dt_list style=”1″ bullet_position=”middle” dividers=”true”]

• Planning the Database




• Using the DBCA to Create a Database




• Password Management




• Creating a Database Design Template




• Using the DBCA to Delete a Database

• Start and stop the Oracle database and components




• Use Oracle Enterprise Manager




• Access a database with SQL*Plus




• Modify database installation parameters




• Describe the stages of database startup




• Describe database shutdown options




• View the alert log




• Access dynamic performance views

• Set up initialization parameter files for ASM instance




• Start up and shut down ASM instances




• Administer ASM disk groups

• Use Enterprise Manager to create and configure the Listener




• Enable Oracle Restart to monitor the listener




• Use tnsping to test Oracle Net connectivity




• Identify when to use shared servers and when to use dedicated servers

• Storage Structures




• How Table Data Is Stored




• Anatomy of a Database Block




• Space Management in Tablespaces




• Tablespaces in the Preconfigured Database




• Actions with Tablespaces




• Oracle Managed Files (OMF)

• Database User Accounts




• Predefined Administrative Accounts




• Benefits of Roles




• Predefined Roles




• Implementing Profiles

• Data Concurrency




• Enqueue Mechanism




• Resolving Lock Conflicts




• Deadlocks

• Data Manipulation




• Transactions and Undo Data




• Undo Data Versus Redo Data




• Configuring Undo Retention

• Describe DBA responsibilities for security




• Enable standard database auditing




• Specify audit options




• Review audit information




• Maintain the audit trail

• Manage optimizer statistics




• Manage the Automatic Workload Repository (AWR)




• Use the Automatic Database Diagnostic Monitor (ADDM)




• Describe and use the advisory framework




• Set alert thresholds




• Use server-generated alerts




• Use automated tasks

• Performance Monitoring




• Managing Memory Components




• Enabling Automatic Memory Management (AMM)




• Automatic Shared Memory Advisor




• Using Memory Advisors




• Dynamic Performance Statistics




• Troubleshooting and Tuning Views




• Invalid and Unusable Objects

• Part of Your Job




• Statement Failure




• User Error




• Understanding Instance Recovery




• Phases of Instance Recovery




• Using the MTTR Advisor




• Media Failure




• Archive Log Files

• Backup Solutions: Overview




• Oracle Secure Backup




• User-Managed Backup




• Terminology




• Recovery Manager (RMAN)




• Configuring Backup Settings




• Backing Up the Control File to a Trace File




• Monitoring the Flash Recovery Area

• Describe ways to move data




• Create and use directory objects




• Use SQL*Loader to move data




• Use external tables to move data




• General architecture of Oracle Data Pump




• Use Data Pump export and import to move data

• Use the Enterprise Manager Support Workbench




• Work with Oracle Support\




• Log service requests (SR)




• Manage patches




• Use the Enterprise Manager Support Workbench




• Work with Oracle Support\




• Log service requests (SR)




• Manage patches

• The Oracle Database Architecture: Overview




• ASM Storage Concepts




• Connecting to the Database and the ASM Instance




• DBA Tools Overview

• Purpose of Backup and Recovery (B&R), Typical Tasks and Terminology




• Using the Recovery Manager (RMAN)




• Configuring your Database for B&R Operations




• Configuring Archivelog Mode




• Configuring Backup Retention




• Configuring and Using a Flash Recovery Area (FRA)

• Tracking and Storing Backup Information




• Setting up a Recovery Catalog




• Recording Backups




• Using RMAN Stored Scripts




• Managing the Recovery Catalog (Backup, Export, Import, Upgrade, Drop and Virtual Private Catalog)

• Configuring and Managing Persistent Settings for RMAN




• Configuring Autobackup of Control File




• Backup optimization




• Advanced Configuration Settings: Compressing Backups




• Configuring Backup and Restore for Very Large Files (Multisection)

• RMAN backup types

• Restoring and Recovering




• Causes of File Loss




• Automatic Tempfile Recovery




• Recovering from the Loss of a Redo Log Group




• Recovering from a Lost Index Tablespace




• Re-creating a Password Authentication File




• Complete and Incomplete Recovery




• Other Recovery Operations

• Complete Recovery after Loss of a Critical or Noncritical Data File




• Recovering Image Copies and Switching Files




• Restore and Recovery of a Database in NOARCHIVELOG Mode




• Incomplete Recovery




• Performing Recovery with a Backup Control File




• Restoring from Autobackup: Server Parameter File and Control File




• Restoring and Recovering the Database on a New Host

• Monitoring RMAN Jobs




• Balance Between Speed of Backup Versus Speed of Recovery




• RMAN Multiplexing




• Synchronous and Asynchronous I/O




• Explaining Performance Impact of MAXPIECESIZE, FILESPERSET, MAXOPENFILES and BACKUP DURATION

• Data Recovery Advisor (DRA)




• Block Corruption




• Automatic Diagnostic Repository (ADR)




• Health Monitor




• The ADR Command-Line Tool, ADRCI

• Flashback Technology: Overview and Setup




• Using Flashback Technology to Query Data




• Flashback Table




• Flashback Transaction Query




• Performing Flashback Transaction Backout

• Oracle Total Recall




• Flashback Drop and the Recycle Bin

• Configuring Flashback Database




• Performing Flashback Database Operations




• Monitoring Flashback Database

• Oracle Memory Structures




• Oracle Database Memory Parameters




• Using Automatic Memory Management




• Automatic Shared Memory Management




• Using Memory Advisors




• Using Data Dictionary Views

• Tuning Activities




• Using Statistic Preferences




• Optimizer Statistics Collection




• Monitor the Performance of Sessions and Services




• Automatic Workload Repository (AWR)




• Describing the Benefits of Database Replay

• SQL Tuning and SQL Advisors




• Using SQL Tuning Advisor




• SQL Access Advisor




• SQL Performance Analyzer Overview

• Database Resource Manager: Overview and Concepts




• DAccessing and Creating Resource Plans




• DCreating Consumer Group




• DSpecifying Resource Plan Directives, including

• Simplifying Management Tasks




• Creating a Job, Program, and Schedule




• Using Time-Based, Event-Based, and Complex Schedules




• Describing the Use of Windows, Window Groups, Job Classes, and Consumer Groups




• Multi-Destination Jobs

• Free Space Management




• Monitoring Space




• Compressing Data

• Segment Creation on Demand




• Additional Automatic Space-Saving Functionality




• Shrinking Segments




• Segment Advisor




• Managing Resumable Space Allocation

• Purpose and Methods of Cloning a Database




• Using RMAN to Create a Duplicate Database




• Cloning a Database from a Backup




• Duplicate a Database Based on a Running Instance




• Targetless Duplicating a Database