International Certification Program Oracle 19C DBA OCP

• Course objectives, roadmap, and appendixes used in the course
• Overview of Oracle Database 19c and related products
• Overview of relational database management concepts and terminologies
• Human Resource (HR) Schema and the tables used in the course
• Introduction to SQL and its development environments
• Oracle Database 19c SQL Documentation and Additional Resources

• Capabilities of SQL SELECT statements
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of the concatenation operator, literal character strings, the alternative quote operator, and the DISTINCT keyword
• DESCRIBE command

• Limiting rows with
• The WHERE clause
• The comparison operators using
• Logical conditions using AND, OR, and NOT operators
• Rules of precedence for operators in an expression
• Sorting rows using the ORDER BY clause
• SQL row limiting clause in a query
• Substitution variables in Oracle
• Assigning values to variables

• Single-row SQL functions
• Character functions
• Nesting functions
• Number functions
• Working with dates in Oracle Databases
• Working with dates in MySQL Databases
• Date functions

• Implicit and explicit data type conversion
• TO_CHAR, TO_DATE, TO_NUMBER functions in Oracle
• Using the CAST() function in MySQL
• General functions (NVL, NVL2, NULLIF, COALESCE)
• Conditional expressions (CASE, Searched CASE, DECODE)
• JSON functions: (JSON_QUERY, JSON_TABLE, JSON_VALUE)

• Group functions
• Types and syntax
• Use AVG, SUM, MIN, MAX, COUNT
• Use the DISTINCT keyword within group functions
• NULL values in a group function
• Grouping rows: (GROUP BY clause, Having clause)

• Displaying Data from Multiple Tables Using Joins
• Types of JOINS and their syntax
• Natural join
• Join with the USING clause
• Join with the ON clause
• Self-join
• Nonequijoins
• OUTER join ( LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN )
• Cartesian product (Cross join)

• Subquery: Types, syntax, and guidelines
• Single-row subqueries
• Group functions in a subquery
• HAVING clause with subqueries
• Multiple-row subqueries
• Using All or Any Operator
• Multiple-column subqueries
• Null values in a subquery

• Set operators: Types and guidelines
• Tables used in this lesson
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching SELECT statements
• Using the ORDER BY clause in set operations

• Adding new rows in a table ( INSERT statement )
• Changing data in a table ( UPDATE statement)
• Removing rows from a table: ( DELETE and TRUNCATE statement)
• Database transaction control using COMMIT, ROLLBACK, and SAVEPOINT
• Read consistency - Manual Data Locking

• Adding new rows in a table ( INSERT statement )
• Changing data in a table ( UPDATE statement)
• Removing rows from a table: ( DELETE and TRUNCATE statement)
• Database transaction control using COMMIT, ROLLBACK, and SAVEPOINT
• Read consistency
• Manual Data Locking

• Database objects ( Naming rules )
• CREATE TABLE statement
• Data types
• Overview of constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK constraints
• Creating a table using a subquery
• ALTER TABLE statement
• DROP TABLE statement

• Database objects ( Naming rules )
• CREATE TABLE statement
• Data types
• Indexes, keys, and constraints
• Column options
• Creating a table using a subquery
• ALTER TABLE statement
• DROP TABLE statement

• Introduction to data dictionary
• Querying the dictionary views for the following
• Table information
• Column information
• Constraint information
• Adding a comment to a table and querying the dictionary views for comment information

• Overview of sequences
• Overview of synonyms
• Overview of indexes

• Overview of views
• Creating, modifying, and retrieving data from a view
• Data manipulation language (DML) operations on a view
• Dropping a view

• Managing constraints
• Creating and using temporary tables
• Creating and using external tables

• Retrieving data by using a subquery as a source
• Writing a multiple-column subquery
• Using scalar subqueries in SQL
• Solving problems with correlated subqueries
• Using the EXISTS and NOT EXISTS operators
• Using the WITH clause

• Using subqueries to manipulate data
• Inserting values by using a subquery as a target
• Using the WITH CHECK OPTION keyword on DML statements
• Using correlated subqueries to update and delete rows

• System privileges
• Creating a role
• Object privileges
• Revoking object privileges

• Specifying explicit default values in the INSERT and UPDATE statements
• Using the following types of multitable INSERTs
• Unconditional INSERT
• Conditional INSERT ALL
• Conditional INSERT FIRST
• Pivoting INSERT
• Merging rows in a table
• Performing flashback operations
• Tracking the changes in data over a period of time

• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• INTERVAL data types
• Using the following functions
• EXTRACT
• TZ_OFFSET
• FROM_TZ
• TO_TIMESTAMP
• TO_YMINTERVAL
• TO_DSINTERVAL

• Database Server Architecture

• Creating a Database Using DBCA

• Creating a Database Using a SQL Command

• Opening and Closing PDBs

• Managing the Database

Configuring and Administering Oracle Net Services

• Oracle Net Components
• Naming Method

Connection Manager

• Provisioning New Pluggable Databases

• Method of creating and migrating PDBs

• Purpose of the Default Tablespaces
• Storage of Data in blocks
• Advantage of Deferred Segment Creation

• Viewing Tablespace information

• Space Management Features
• Creating Global Temporary Tables

• Enabling temporary Undo

• Configuring Auditing
• Viewing Audit Policy Information

• Using External Tables to Load and Transport Data

• Automated Maintenance Tasks: Components

• Configuring Automated Maintenance Tasks

• Understanding Monitoring Database Performance
• Database Processes
• Managing Memory

• Understanding Instance Recovery

• Configuration Demo
• Control File Demo
• Adding a Redo file to Redo Group - Demo

• Connecting RMAN to a Target Database Demo
• Using SQL and RMAN Demo
• Managing RMAN Settings Demo

• Fast Incremental Backup
• Syntax and Clause in RMAN Use for Backups Demo
• Creating Image Copies for Backup Demo
• Creating a Whole Backups Container or Noncontainer Database System Demo
• Creating a Partial PDP Backups Demo
• Maintaining the Block Change Tracking File Demo
• Backing up to Control Files to Trace File Demo

• Compres Backup Demo
• Creating Duplex Backups Set by Backups Copies Demo
• Creating Backups of Backups Set Demo
• Creating archival Backups with RMAN Demo
• Backups ups Recovery File Demo

• Asynchronous I/O Bottlenecks

• Configure a Recovery Catalog Database Demo
• Creating a Recovery Catalog Owner Demo
• Creating a Recovery Catalog Demo

• Register a Database in Recovery Catalog Demo
• Manually Resynchronizing Recovery Catalog Demo

• Instance Failure

• Data Recovery Advisor
• Diagnosing Failure Using Oracle Database Recovery Advisor Demo

• Performing Complete Recovery Using RMAN Demo

• Architecture Tablespace Point-in-Time Recovery
• Performing incomplete Recovery Using Data Recovery Advisor RMAN Demo

• Performing Block Media Recovery

• Flashback Drop and the Recycle Bin
• Prepare to Using Flashback Technology Demo

• Enabling Oracle Flashback Database Demo

• Allocating Auxiliary Channels

• Describe the multitenant architecture
• Describe the CDB root and pluggable database containers
• Differentiate the CDB root from a pluggable database
• Understand the terminology of commonality
• List impacts in various areas

• Configure and create a CDB
• Provision new PDBs
• Create a new PDB from the CDB seed
• Explore the instance
• Explore the structure of PDBs
• Explore the Automatic Diagnostic Repository (ADR)

• Describe application containers in CDBs
• Explain the purpose of application root and application seed
• Define application PDBs
• Create application PDBs
• Explain application installation on top of application containers
• Install, upgrade and patch an application
• Describe the commonality concept in application contexts
• Use a dynamic container map

• Clone a regular PDB
• Clone an application PDB
• Unplug and plug a non-CDB
• Unplug and plug a regular PDB
• Unplug and plug an application container
• Convert regular PDBs to application PDBs
• Configure and use the local UNDO mode
• Perform hot cloning and relocation

• Establish connections to CDB and PDB
• Start up and shut down a CDB
• Open and close PDBs
• Avoid service name conflicts
• Start PDB service
• Change the different modes and settings of PDBs
• Evaluate the impact of parameter value changes
• Configure host name and port number per PDB

• Manage permanent tablespaces in CDB and PDBs
• Manage temporary tablespaces in CDB and PDBs
• Manage the UNDO tablespaces in CDB root and PDB

• Manage common and local users, roles, privileges and profiles in PDBs
• Manage common and local objects in application containers
• Enable common users to access data in PDBs
• Manage PDB lockdown profiles
• Audit users in CDB and PDBs
• Manage other types of policies in application containers
• Protect data with Database Vault policies in CDB and PDBs
• Encrypt data in PDBs

• Backup CDB and PDBs
• Use RMAN backups to plug unplugged PDBs
• Duplicate PDBs
• Duplicate an active PDB into an existing CDB
• Duplicate a CDB as encrypted
• Validate CDBs and PDBs

• Recover a PDB from essential file damage
• Recover a PDB from non-essential file damage
• Reuse preplugin backups after conversion of a non-CDB to a PDB
• Reuse preplugin backups after plugging/relocating a PDB into another CDB
• Perform CDB flashback
• Perform PDB flashback
• Use clean restore points to complete PDB flashback
• Manage PDB snapshots

• Monitor operations in a CDB and PDBs
• Monitor performance in a CDB and PDBs
• Manage SGA and PGA limits at the PDB level
• Manage AWR snapshots at the CDB and PDB levels
• Run ADDM tasks for CDB and PDB recommendations
• Manage application shared object statistics
• Control query DOP involving the containers() construct
• Control PDB IO rate limits

• Manage resource allocation between PDBs and within a PDB
• Enable parallel statement queuing at PDB level
• Manage PDB performance profiles
• Avoid excessive session PGA

• Export from a non-CDB and import into a PDB
• Export from a PDB and import into a PDB
• Export from a PDB and import into a non-CDB
• Use SQL*Loader to load data into a PDB

• Upgrade 12.2 CDB to 18c
• Upgrade 12.2 PDBs to PDBs in 18c
• Plug in a remote PDB through XTTS into a target CDB