Oracle 12c DBA- With Latest Updates like 19c, 21c etc.

Module-1 Introduction to SQL

Introduction to Oracle Database

List the features of Oracle Database 12c, Discuss the basic design of 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

Retrieve Data using the SQL SELECT Statement

List the capabilities of SQL SELECT statements, Select Table Data With All Columns, Select Table Data With Specific Columns, Use Arithmetic Operators, Use Concatenation Operators, Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

Use WHERE clause to limit the output retrieved, List the comparison operators and logical operators, 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 Data, SQL Row Limiting Clause (Fetch First, Offset and With Ties)

Usage of Single-Row Functions to Customize Output

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

Invoke Conversion Functions and Conditional Expressions

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

Aggregate Data Using the Group Functions

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

Display Data From Multiple Tables Using Joins

Write SELECT statements to access data from more than one table, View data that does not meet join condition by using outer joins, Join a table to itself by using a self-join

Use Sub-queries to Solve Queries

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

The SET Operators

Describe the SET operators, Use UNION and UNION ALL to Combine Result of Multiple Queries, Use of INTERSECT, Use of MINUS, Control the order of rows returned

Data Manipulation Statements

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

Use of DDL Statements to Create and Manage Tables

Create a simple table, Alter and Truncate Table, Drop Table and Concept of Recyclebin, Stop Entry of Invalid Data Through CONSTRAINTS, Primary Key, Foreign Key, Not Null, Unique and Check Constraints

Other Schema Objects

Create a simple and complex view, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms

Module -2 Oracle Database 12C : Administration I

Exploring the Oracle Database Architecture

Oracle Database Architecture Overview, Oracle Memory Architecture, Oracle Process Architecture, Oracle Storage Architecture, Logical and Physical Storage

In-Memory Column Store

Installing your Oracle Software

Installation: System Requirements, Oracle Universal Installer (OUI), Installing Oracle Database Software, Silent Install, Using the DBCA to Create a Database, Using the DBCA to Delete a Database

Managing the Oracle Database Instance

Start and stop the Oracle database and components, Modify database initialization parameters, Describe the stages of database startup, Describe database shutdown options, View the alert log, Administering the DDL Log File, Understanding the Debug Log File, Access dynamic performance views

Configuring the Oracle Network Environment

Oracle Net Services: Overview, Oracle Net Listener: Overview, Naming Methods, Using Oracle Net Manager, Using the Listener Control Utility, Use tnsping to test Oracle Net connectivity, Creating Database Link, Identify when to use shared servers and when to use dedicated servers

Managing Database Storage Structures

How Table Data Is Stored, Anatomy of a Database Block, Types of Tablespaces, Creating Tablespaces (Smallfile&Bigfile), Removing Tablespaces, Actions with Tablespaces, Oracle Managed Files (OMF)

Administering User Security

Database User Accounts, Predefined Administrative Accounts, Creating and Granting Privileges, Benefits of Roles, Predefined Roles, Dropping Users and Roles, Implementing Profiles for Password Management

Managing Data Concurrency

Data Concurrency, Enqueue Mechanism, Resolving Lock Conflicts, Deadlocks

Backup and Recovery Concepts

Part of Your Job, Statement Failure, User Error, Understanding Instance Recovery, Phases of Instance Recovery, Using the MTTR Advisor, Media Failure, Archive Log Files

Performing Database Backups

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

Performing Database Recovery

Loss of a Data File in NOARCHIVELOG Mode, Noncritical Recovery in ARCHIVELOG Mode, Critical Recovery in ARCHIVELOG Mode, Loss of a Control File, Loss of a Redo Log File

Moving Data

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

Module 3 Oracle Database 12C : Administration II

Introduction Of RMAN

Review of Oracle architecture, Oracle Data Protection Solutions, Advantages of Using RMAN, Connecting to RMAN and a Target Database, Connection types, Oracle Secure Backup, Oracle Data Guard: Overview, Oracle Maximum Availability Architecture

Configuring for Recoverability

Purpose of Backup and Recovery, 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)

Using the RMAN Recovery Catalog

RMAN Repository Data Storage, Storing Information in the Recovery Catalog, Reasons to Use a Recovery Catalog, Creating the Recovery Catalog, Recovery Catalog Resynchronization, Using RMAN Stored Scripts, Writing, Executing and Maintaining Stored Scripts, Creating and Managing Virtual Private Catalog

Backup Strategies and Terminology

Backup Terminology, Full and Incremental Backups, Incrementally Updated Disk Backups, Backing Up Read-Only Tablespaces, Data Warehouse Backup and Recovery, Difference Between Backupset and Image Copy

Performing and Improving Backup

RMAN Backup Types, Incrementally Updated Backups, Maintaining Block Change Tracking File, Oracle-Suggested Backup, Managing Backups: Cross-Checking and Deleting, Using RMAN Backup Compression, Configuring Backup and Restore for Very Large Files, Creating Backups of Backup Sets, Backing up Controlfile, Archivefile and Spfile, Cataloging Additional Backup Files

Using RMAN-Encrypted Backups

Comparing OSB and RMAN Encryption, What Is TDE?, Configuring RMAN Encryption, Creating RMAN-Encrypted Backups, Restoring Encrypted Backups

Restore and Recovery Task

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

Using RMAN to Perform Recovery

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 and Tuning RMAN

Monitoring RMAN Jobs, Using the DEBUG Option, Balance Between Speed of Backup Versus Speed of Recovery, RMAN Multiplexing, Setting LARGE_POOL_SIZE, Synchronous and Asynchronous I/O, Explaining Performance Impact of MAXPIECESIZE, FILESPERSET, MAXOPENFILES and BACKUP DURATION

Diagnosing the Database

Data Recovery Advisor (DRA), Block Corruption, Automatic Diagnostic Repository (ADR), Health Monitor, The ADR Command-Line Tool, ADRCI

Using Flashback Technology I

Flashback Technology: Overview and Setup, Using Flashback Technology to Query Data, Flashback Version Query, Flashback Table, Flashback Transaction Query, Flashback Drop

Performing Flashback Database

Configuring Flashback Database, Performing Flashback Database Operations, Monitoring Flashback Database, Performing Flashback Transaction Backout, Performing Flashback Data Archive

Transporting Data

Transporting Data Across Platforms, Transporting a Tablespace with Image Copies, Using the RMAN CONVERT Command, Database Transportation Procedure, Database Transport with Backup Sets, Transporting Inconsistent Tablespaces

Performing Point-in-Time Recovery

When to Use TSPITR, PITR Terminology, Tablespace Point-in-Time Recovery: Architecture, Performing RMAN TSPITR, Performing Fully Automated TSPITR, Troubleshooting RMAN TSPITR

Duplicating a Database

Choosing Database Duplication Techniques, “Push” Versus “Pull” Methods of Duplication, Duplicating a Database with a Target Connection, Duplicating a Database with Recovery Catalog Without Target Connection, Duplicating a Database Without Recovery Catalog or Target Connection, Creating a Backup-Based Duplicate Database, Understanding the RMAN Duplication Operation, Using Additional DUPLICATE Command Options

Basics of Multitenant Container Database and Pluggable Databases

New Multitenant Architecture: Benefits, Oracle Container Database, Separating SYSTEM and User Data, Provisioning a Pluggable Database, Multitenant Container Database Architecture, Common and Local Users, Common and Local Privileges and Roles, Shared and Non-shared Objects

Creating a Multitenant CDB and PDB

Steps to Create a Multitenant Container Database, Data Dictionary Views: DBA_ xxx, Data Dictionary Views: CDB_ xxx, EM Cloud Control: Summary, Creating A Pluggable Database, Plug a Non-CDB into CDB, Clone PDBs: Using SQL Developer, Plug Unplugged PDB into CDB

Managing a Multitenant CDB and PDB

Connection with SQL*Developer, Creating Services, Mounting and Opening CDB, Opening and Closing PDB, Shutting Down a CDB Instance, Modifying a PDB Settings, Using ALTER SYSTEM Statement on PDB

Managing Tablespace in CDB and PDB

Tablespaces in PDBs, Creating Permanent Tablespaces in a CDB, Assigning Default Tablespaces, Creating Local Temporary Tablespaces, Assigning Default Temporary Tablespaces

Managing Security in CDB and PDBs

Users, Roles, and Privileges, Local User and Common User, Local and Common Schema, Granting Common and Local Roles to Users, Enabling Common Users to Access Data in Specific PDBs, Creating Common and Local Profiles

Backup, Recovery, Flashback CDB and PDBs

CDB Backup: Whole or Partial CDB Backup, PDB Backup: Whole or Partial PDB Backup, Media Failure: Datafile, Controlfile and Redolog Files, Media Failure: PITR, Flashback CDB, Duplicating Pluggable Databases, New Data Dictionary View and Column

Performance

Sizing the CDB, Testing the Estimates, Allocating Resources in the CDB, Tuning CDB Memory, Limiting PGA Usage, AWR SQL Reports, Use of Other Advisors

Miscellaneous

Using Oracle Data Pump with PDBs, Full Transportable Export/Import, Transporting a Database Over the Network, Using SQL*Loader with PDBs, Auditing Actions in a CDB and PDBs, Securing Data with Transparent Data Encryption, Using XStreams with a CDB and PDB, Scheduling Operations in a PDB