Curriculum
1Z0-071: Oracle Database 12c SQL
Oracle and Structured Query Language (SQL)
- Identify the connection between an ERD and a Relational Database
- Explain the relationship between a database and SQL
- Describe the purpose of DDL
- Describe the purpose of DML
- Build a SELECT statement to retrieve data from an Oracle Database table
Restricting and Sorting Data
- Use the ORDER BY clause to sort SQL query results
- Limit the rows that are retrieved by a query
- Use ampersand substitution to restrict and sort output at runtime
- Use SQL row limiting clause
Using Single-Row Functions to Customise Output
- Use various types of functions available in SQL
- Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements
Using Conversion Functions and Conditional Expressions
- Describe various types of conversion functions that are available in SQL
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Apply general functions and conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions
- Describe the use of group functions
- Group data by using the GROUP BY clause
- Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple Tables
- Describe the different types of joins and their features
- Use 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
- Describe the types of problems subqueries can solve
- Describe the types of subqueries
- Query data using correlated subqueries
- Update and delete rows using correlated subqueries
- Use the EXISTS and NOT EXISTS operators
- Use the 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 rows into a table
- Update rows in a table
- Delete rows from a table
- Control transactions
Using DDL Statements to Create and Manage Tables
- Describe data types that are available for columns
- Create a simple table
- Create 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
- Describe 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
1Z0-072: Oracle Database 12c Administration
Exploring the Oracle Database Architecture
- List the architectural components of Oracle Database
- Explain the memory structures
- Describe the 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
- Configure communication between databases
Administering User Security
- Create and manage database user accounts
- Grant and revoke privileges
- Create and manage roles
- Create and manage 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
- Manage 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
1Z0-063: Oracle Database 12c Advanced Administration
Backup and Recovery
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
- Resynchronise 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
- Use Flashback Query
- Use Flashback Version Query
- Use Flashback Transaction Query
- Flash back a transaction
- Perform Flashback Table operations
- Perform Flashback Table
- Restore tables from the recycle bin
- Describe and use Flashback Data Archive
- Use Flashback Data Archive
- Use DBMS_FLASHBACK_ARCHIVE package
Using Flashback Database
- Perform Flashback Database
- Configure Flashback Database
- 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
Introduction
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
- Manage common and local privileges
- Manage common and local 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
- Use SQL*Loader
- Audit operations
- Use Other Products with CDB and PDBs - Database Vault, Data Guard, LogMiner