Oracle OCP Exam #1Z0-071 - Oracle Database 12c SQL

Introduction to Oracle and Structured Query Language (SQL)

• Introduction to Oracle Database products
• Oracle Database in the Market
• Oracle Certification Scopes and the future
• Introduction to Oracle SQL
• Relational Database Management System
• ERD and a Relational Database
• Purpose of DDL, DML
• SELECT statement to retrieve data from an Oracle Database table

Restricting and Sorting Data

• ORDER BY clause to sort SQL query results, Limit Rows
• Ampersand substitution to restrict and sort output at runtime

Single-Row Functions to Customize Output

• Various types of functions available in SQL
• Character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements

Conversion Functions and Conditional Expressions

• Conversion functions that are available in SQL
• TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• General functions and conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Group functions, Group data using GROUP BY Clause
• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Joins and their features
• SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins

Using Subqueries to Solve Queries

• Define subqueries, Correlated subqueries
• Update and delete rows using correlated subqueries
• EXISTS and NOT EXISTS operators
• WITH clause, Use single-row and multiple-row subqueries

Using the Set Operators

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

Manipulating Data

• Truncate data
• Insert, Update, Delete rows into a table
• Control transactions

Using DDL Statements to Create and Manage Tables

• Data types that are available for columns
• Create a simple table, constraints for tables
• Drop columns and set column UNUSED
• Create and use external tables

Managing Objects with Data Dictionary Views

• Query various data dictionary views

Controlling User Access

• Differentiate system privileges from object privileges
• Grant privileges on tables and on a user
• Distinguish between privileges and roles

Managing Schema Objects

• How schema objects work
• Create simple and complex views with visible/invisible columns
• Create, maintain and use sequences
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• Perform flashback operations

Manipulating Large Data Sets

• Describe the features of multitable INSERTs
• Merge rows in a table

Oracle OCP Exam #1Z0-062 - Oracle Database 12c Administration(OCA Certification)

Exploring the Oracle Database Architecture

• Architectural components of Oracle Database
• Explain the memory structures and background processes

Oracle Database Management Tools

Use database management tools

Oracle Database Instance

• Understand initialization parameter files
• Start up and shut down an Oracle database instance
• View the alert log and access dynamic performance views

Configuring the Oracle Network Environment

• Configure Oracle Net Services
• Use tools for configuring and managing the Oracle network
• Configure client-side network and communication between databases

Administering User Security

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles, profiles

Managing Database Storage Structures

• Describe the storage of table row data in blocks
• Create and manage tablespaces

Create and manage tablespaces

• Explain how Oracle database server automatically manages space
• Save space by using compression
• Use the Segment Advisor
• Reclaim wasted space from tables and indexes by using the segment shrink functionality

Managing Undo Data

• Explain DML and undo data generation
• Monitor and administer undo data
• Describe the difference between undo data and redo data
• Configure undo retention

Managing Data Concurrency

• Describe the locking mechanism and how Oracle manages data concurrency
• Monitor and resolve locking conflicts

Implementing Oracle Database Auditing

• Enable standard database auditing and unified auditing

Backup and Recovery Concepts

• Identify the importance of checkpoints, redo log files, and archive log files

Backup and Recovery Configuration

• Configure the fast recovery area
• Configure ARCHIVELOG mode

Performing Database Backups

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups

Performing Database Recovery

• Determine the need for performing recovery
• Use Recovery Manager (RMAN) and the Data Recovery Advisor to perform recovery of the control file, redo log file and data file

Moving Data

• Describe ways to move data
• Use SQL*Loader to load data from a non-Oracle database
• Use external tables to move data via platform-independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases

Performing Database Maintenance

• Use server-generated alerts

Managing Performance: SQL Tuning

• Use the SQL Tuning advisor

Automating Tasks by Using Oracle Scheduler

• Use Oracle Scheduler to simplify management tasks
• Use job chains to perform a series of related tasks
• Use Scheduler jobs on remote systems
• Use advanced Scheduler features to prioritize jobs

Creating an Oracle Database Using DBCA

• Generate database creation scripts by using DBCA
• Manage database design templates by using DBCA

Upgrading Oracle Database Software

• Describe data migration methods
• Describe the upgrade process

Introduction to DBAAS

• Recognize the difference between a DBaaS Instance and a Database Instance
• Navigate with My Services Dashboard
• User authentication and roles
• Generate a Secure Shell (SSH) Public/Private Key Pair

Oracle OCP Exam #1Z0-063 - Oracle Database 12c: Advanced Administration(OCP Certification)

Oracle Data Protection Solutions

• Explain Oracle backup and recovery solutions
• Describe types of database failures
• Describe the tools available for backup and recovery tasks
• Describe RMAN and maximum availability architecture
• Use the SYSBACK privilege
• Use RMAN stand-alone and job commands

Performing Basic Backup and Recovery

• Back up and recover a NOARCHIVELOG database
• Perform backup and recovery in NOARCHIVELOG mode
• Use SQL in RMAN

Configuring for Recoverability

• Configure and manage RMAN settings
• Configure persistent settings for RMAN
• View persistent settings
• Specify a retention policy
• Configure the Fast Recovery Area
• Explain the Fast Recovery Area
• Configure the Fast Recovery Area
• Configure control files and redo log files for recoverability
• Multiplex control files
• Multiplex redo log files

Using the RMAN Recovery Catalog

• Create and use an RMAN recovery catalog
• Configure a recovery catalog
• Register target databases in a recovery catalog
• Catalog additional backup files
• Resynchronize a recovery catalog
• Use and maintain RMAN stored scripts
• Upgrade and drop a recovery catalog
• Protect the RMAN recovery catalog
• Back up the recovery catalog
• Re-create an unrecoverable recovery catalog
• Export and import the recovery catalog

Implementing Backup Strategies

• Use various RMAN backup types and strategies
• Enable ARCHIVELOG mode
• Create tape and disk based backups
• Create whole database backups
• Create consistent and inconsistent backups
• Create backup sets and image copies
• Create backups of read-only tablespaces
• Employ best practices for data warehouse backups

Performing Backups

• Perform full and incremental backups
• Create full and incremental backups
• Use the Oracle-suggested backup strategy
• Manage backups
• Configure and monitor block change tracking
• Report on backups using LIST, REPORT commands
• Manage backups using CROSSCHECK, DELETE commands

Configuring RMAN Backup Options and Creating Backup of Non-Database Files

• Use techniques to improve backups
• Create compressed backups
• Create multi-section backups of very large files
• Create proxy copies
• Create duplexed backup sets
• Create backups of backup sets
• Create archival backups
• Perform backup of non-database files
• Back up a control file to trace
• Back up archived redo log files
• Back up ASM diskgroup metadata

Using RMAN-Encrypted Backups

• Create RMAN-encrypted backups
• Use transparent-mode encryption
• Use password-mode encryption
• Use dual-mode encryption
• Restore encrypted backups

Diagnosing Failures

• Describe the Automatic Diagnostic Workflow
• Use the Automatic Diagnostic Repository
• Use ADRCI
• Find and interpret message output and error stacks
• Use the Data Recovery Advisor
• Handle block corruption
• Detect block corruption using RMAN
• Perform block recovery using RMAN

Performing Restore and Recovery Operations

• Describe and tune instance recovery
• Perform complete and incomplete recovery
• Use RMAN RESTORE and RECOVER commands
• Restore ASM disk groups
• Recover from media failures
• Perform complete and incomplete or “point-in-time” recoveries using RMAN

Recovering Files Using RMAN

• Perform recovery for spfile, control file, redo log files
• Perform table recovery from backups
• Perform recovery of index and read-only tablespaces, temp file
• Restore a database to a new host

Using Oracle Secure Backup

• Configure and use Oracle Secure Backup

Using Flashback Technologies

• Describe the Flashback technologies
• Configure a database to use Flashback technologies
• Guarantee undo retention
• Use Flashback to query data, Version Query, Flashback Transaction, Table Operations
• Restore tables from the recycle bin
• Flashback Data Archive
• Use DBMS_FLASHBACK_ARCHIVE package

Using Flashback Database

• Configure and Perform Flashback Database

Transporting Data

• Describe and use transportable tablespaces and databases
• Transport tablespaces between databases using image copies or backup sets
• Transport databases using data files or backup sets
• Transport data across platforms

Duplicating a Database

• Choose a technique for duplicating a database
• From an active database, connected to the target and auxiliary instances
• From backup, connected to the target and auxiliary instances
• From backup, connected to the auxiliary instance, not connected to the target, but with recovery catalog connection
• From backup, connected to the auxiliary instance, not connected to the target and the recovery catalog
• Duplicate a database with RMAN
• Create a backup-up based duplicate database
• Duplicate a database based on a running instance

Monitoring and Tuning of RMAN Operations

• Tune RMAN performance
• Interpret RMAN error stacks
• Diagnose performance bottlenecks
• Tune RMAN backup performance

Managing Pluggable and Container Databases

• Multitenant Container and Pluggable Database Architecture
• Describe the multitenant container database architecture
• Explain pluggable database provisioning

Creating Multitenant Container and Pluggable Databases

• Configure and create a CDB
• Create a PDB using different methods
• Unplug and drop a PDB
• Migrate a non-CDB database to PDB

Managing a CDB and PDBs

• Establish connections to CDB/PDB
• Start up and shut down a CDB and open and close PDBs
• Evaluate the impact of parameter value changes

Managing Storage in a CDB and PDBs

• Manage permanent and temporary tablespaces in CDB and PDBs

Managing Security in a CDB and PDBs

• Manage common and local users, privileges, roles
• Enable common users to access data in specific PDBs

Managing Availability

• Perform backups of a CDB and PDBs
• Recover PDB from PDB datafiles loss
• Use Data Recovery Advisor
• Duplicate PDBs using RMAN

Managing Performance

• Monitor operations and performance in a CDB and PDBs
• Manage allocation of resources between PDBs and within a PDB
• Perform Database Replay

Moving Data, Performing Security Operations and Interacting with Other Oracle Products

• Use Data Pump, SQL*Loader, Audit operations
• Use Other Products with CDB and PDBs - Database Vault, Data Guard, LogMiner