Oracle Database 12c: New Features for Administrators Training

CHAPTER 1 – SQL ENHANCEMENTS

• MAJOR ENHANCEMENTS
  • Top N And Bottom N Queries
  • Using Extended Datatypes
  • Concurrent UNION / UNION ALL Execution
  • Sequences With DEFAULT Values
  • Sequences With Global Temporary Tables
  • IDENTITY Columns
  • New Sequence Functionality For Oracle Replay
  • Invisible Columns
  • Temporal Validity
  • Pattern Matching
• MISCELLANEOUS NEW SQL FEATURES
• DATA MODEL REVIEW
• LAB 1:  SQL ENHANCEMENTS
• LAB 1 SOLUTIONS: SQL ENHANCEMENTS

CHAPTER 2 – PL/SQL ENHANCEMENTS

• WHITE LISTS
• THE WITH CLAUSE FOR FUNCTIONS
• PRAGMA UDF
• NEW INQUIRY DIRECTIVES
• NEW DBMS_SQL FUNCTIONALITY
• ENHANCED LANGUAGE INTERFACE FEATURES
• MISCELLANEOUS NEW PL/SQL FEATURES FOR ORACLE12C
• LAB 2:  PL/SQL ENHANCEMENTS
• LAB 2 SOLUTIONS: PL/SQL ENHANCEMENTS

CHAPTER 3 – INSTALLATION / UPGRADING

• OVERVIEW OF DATABASE CREATION
• THE ORACLE UNIVERSAL INSTALLER
• PREREQUISE TASKS
  • Memory Requirements
  • Hardware Requirements
  • Software Requirements
  • Package Requirements
  • What About Windows?
• RUNNING THE INSTALLER
• POST INSTALLATION
• USING THE DATABASE CONFIGURATION ASSISTANT
• USING THE DATABASE CONFIGURATION ASSISTANT – SHORT FORM
• DATABASE TEMPLATES
• UPGRADING THE DATABASE
• REMOVING ORACLE SOFTWARE
• LAB 3: INSTALLATION / UPGRADING
• LAB 3 SOLUTIONS: INSTALLATION / UPGRADING

CHAPTER 4 – ENTERPRISE MANAGER ENHANCEMENTS

• DATABASE MANAGEMENT USING EMDE
• ENVIRONMENTAL VARIABLES
• ACCESSING ENTERPRISE MANAGER DATABASE EXPRESS
  • MODIFIED FUNCTIONALITY WITH EMDE Changing Parameters
  • Modification Of Physical Structures
• DELETED FUNCTIONALITY IN EMDE
• DATABASE ADMINISTRATION WITH OTHER TOOLS
• EMDE ACCESS FOR NON-DBA USERS
• EMDE ACCESS TO PLUGGABLE DATABASES
• LAB 4:  ENTERPRISE MANAGER ENHANCEMENTS
• LAB 4 SOLUTIONS: ENTERPRISE MANAGER ENHANCEMENTS

CHAPTER 5 – SQL DEVELOPER / SQL*PLUS

• SQL DEVELOPER
• MULTITENANT CONNECTION CONSIDERATIONS
• USING SQL DEVELOPER
• SQL*PLUS FOR ORACLE12C
  • Invisible Columns
  • New SHOW Commands
  • Last Login Time
  • Support For The New Administrative Privileges
  • Support For Pluggable Databases
• LAB 5:  SQL DEVELOPER / SQL*PLUS
• LAB 5 SOLUTIONS: SQL DEVELOPER / SQL*PLUS

CHAPTER 6 – MULTITENANT DATABASES

• THE IMPACT OF MULTITENANT ARCHITECTURE
• MAJOR BENEFITS OF MULTITENANT ARCHITECTURE
• THE BIRD’S EYE VIEW
• WALKING THE DIRECTORIES OF A CONTAINER DATABASE
• WHO? WHAT? WHEN? WHERE? HOW?
• IMPACTS OF MULTITENANT ARCHITECTURE
• TOOLS FOR USE WITH MULTITENANT ARCHITECTURE
  • SQL*Plus
  • DBCA
  • Oracle Enterprise Manager Cloud Control
  • SQL Developer
  • Server Control (SRVCTL)
• LAB 6: MULTITENANT DATABASES
• LAB 6 SOLUTIONS: MULTITENANT DATABASES

CHAPTER 7 – MULTITENANT BASICS

• CREATING CDBS
• FEATURES UNSUPPORTED FOR MULTITENANT ARCHITECTURE
• THE CREATE DATABASE COMMAND
• Seed Data Files
• CONFIGURING ENTERPRISE MANAGER DATABASE EXPRESS FOR CDBS
• USING SQL DEVELOPER WITH CDBS AS A DBA
• LAB 7:  MULTITENANT BASICS
• LAB 7 SOLUTIONS: MULTITENANT BASICS

CHAPTER 8 – INTERMEDIATE MULTITENANT

• OPTIONS AVAILABLE
• THE CREATE PLUGGABLE DATABASE STATEMENT
  • The STORAGE Clause
  • File Locations Clause
  • Temp File Clause
• PREPARING TO CREATE PDBS
• CREATING PDBS USING THE CDB SEED
  • Specifics Using SQL*Plus
• CLONING AN EXISTING LOCAL PDB
  • Specific Cloning Steps
• CLONING AN EXISTING REMOTE PDB
  • Specific Cloning Steps
  • Effects Of Cloning A Remote PDB
• UNPLUGGING A PDB
• CREATING A PDB BY PLUGGING IN AN UNPLUGGED PDB
• CREATING A PDB FROM A NON-CDB DATABASE
  • Using DBMS_PDB
• NEW TRIGGER TYPES FOR PDBS
  • The BEFORE UNPLUG Trigger
  • The AFTER CLONE Trigger
• LAB 8:  INTERMEDIATE MULTITENANT
• LAB 8 SOLUTIONS: INTERMEDIATE MULTITENANT

CHAPTER 9 – ADVANCED MULTITENANT

• USING DATA PUMP EXPORT AND IMPORT
• USING EXP AND IMP
• DROPPING PDBS
• USING CLOUD CONTROL TO CREATE AND DROP PDBS
• ADMINISTRATION DETAILS
  • Special Roles For CDBs
  • Other Administrative Considerations For CDBs
  • Manageability Considerations
  • Objects In CDBs
  • Services
  • Sessions
  • Common Listeners
  • Connecting To PDBs In MOUNT Mode
  • Login Triggers And The SET CONTAINER Clause
  • Package States And Containers
  • EXECUTE IMMEDIATE And SET CONTAINER
• EXECUTING ACROSS ALL PLUGGABLE DATABASES USING DBMS_SQL
• LOGGING ERRORS FOR MULTIPLE CONTAINERS
• ALTER DATABASE IN A CDB
• TABLESPACES IN CDBS
• USING FORCE ON PDBS
• CLOSE VS. CLOSE IMMEDIATE
• SPECIFYING PDBS IN ALTER PLUGGABLE DATABASE
• LAB 9:  ADVANCED MULTITENANT
• LAB 9 SOLUTIONS: ADVANCED MULTITENANT

CHAPTER 10 – ULTIMATE MULTITENANT

• THE STARTUP COMMAND WITHIN PDBS
• STARTUP PLUGGABLE DATABASE
• SHUTDOWN WITHIN PDBS
• ALTER SYSTEM SET STATEMENTS IN CDBS
• THE CONTAINER CLAUSE IN DDL STATEMENTS
• RUNNING SCRIPTS IN ALL CONTAINERS
• AUTOMATING TNSNAMES.ORA MAINTENANCE
• LAB 10:  ULTIMATE MULTITENANT
• LAB 10 SOLUTIONS: ULTIMATE MULTITENANT

CHAPTER 11 – MULTITENANT ROUNDUP

• RENAMING A PLUGGABLE DATABASE
• MOVING A PDB
• BUILDING A UNIFIED PARAMETER QUERY
• LAB 11:  MULTITENANT ROUNDUP
• LAB 11 SOLUTIONS: MULTITENANT ROUNDUP

CHAPTER 12 – STORAGE AND SECURITY

• NEW SUPER USERS
• ENHANCED SECURITY FOR CDBS
• TEMPORARY TABLESPACE CONSIDERATIONS FOR CDBS
• HARDENING A CDB
• STORAGE CLAUSES RELEVANT TO PDBS
• ALTER SYSTEM IN A PDB
• SCOPE = SPFILE IN PDBs
• SERVICES AND PDBs
• VIEWS IN CDBs
• CON_ID DETAILS
• OTHER INTERESTING CDB VIEWS
• CDB VIEW EXAMPLES
• NEW FUNCTIONS FOR CONTAINERS
• TRIGGERS IN A CDB
• MOVING DATAFILES ONLINE
• RAW PARTITIONS
• DATABASE SMART FLASH CACHE SUPPORT
• LAB 12:  STORAGE AND SECURITY
• LAB 12 SOLUTIONS: STORAGE AND SECURITY

CHAPTER 13 – BACKUP AND RECOVERY FOR CDBS

• CONTAINER DATABASE CONSIDERATIONS
• BACKUP SETUP
• BACKING UP THE ENTIRE CDB
• BACKING UP JUST THE ROOT CONTAINER
• BACKING UP PDBS
• BACKING UP TABLESPACES AND DATAFILES FROM WITHIN A PDB
• EXECUTING RMAN ACROSS PDBS
• REDO LOG, ARCHIVE LOG AND CONTROLFILE BACKUPS IN CDBS
• COMPLETE RECOVERY OF A CDB
• COMPLETE RECOVERY OF A PDB
• COMPLETE RECOVERY OF THE ROOT CONTAINER
• INCOMPLETE RECOVERY OF THE ENTIRE CDB
• INCOMPLETE RECOVERY OF PDBS
• LOSS OF SYSTEM TABLESPACE IN A PDB
• RMAN RESTRICTIONS IN PDBS
• PRIOR BACKUPS AND ARCHIVELOGS FOR CONVERTED NON-CDBS
• FLASHBACK AND CDBS
• DATA PUMP AND CDBS
• LAB 13:  BACKUP AND RECOVERY FOR CDBS
• LAB 13 SOLUTIONS: BACKUP AND RECOVERY FOR CDBS

CHAPTER 14 – AUTOMATIC DATA OPTIMIZATION

• STORAGE AND COMPRESSION TIERING
• THE HEAT MAP
• AUTOMATIC DATA OPTIMIZATION
• COMPRESSION LEVELS

CHAPTER 15 – IN-DATABASE ARCHIVING

• TO KEEP OR NOT TO KEEP
• THE ROW ARCHIVAL CLAUSE
• ORA_ARCHIVE_STATE
• ROW ARCHIVAL VISIBILITY
• LAB 15:  IN-DATABASE ARCHIVING
• LAB 15 SOLUTIONS: IN-DATABASE ARCHIVING

CHAPTER 16 – AUDITING ENHANCEMENTS

• UNIFIED AUDITING
• ENABLING UNIFIED AUDITING
• DISABLING UNIFIED AUDITING
• HANDLING EARLIER AUDIT RECORDS
• CREATING A SEC_ADMIN ACCOUNT
• CREATING A UNIFIED AUDIT POLICY
• AUTOMATIC AUDITS WITH UNIFIED AUDITING
• DISABLING / DROPPING AUDIT POLICIES
• IMMEDIATE-WRITE VS. QUEUED-WRITE MODE
• AUDIT DATA CLEANUP
• LAB 16:  AUDITING ENHANCEMENTS
• LAB 17 SOLUTIONS: AUDITING ENHANCEMENTS

CHAPTER 17 – PRIVILEGE ENHANCEMENTS

• CONSIDERATIONS REGARDING PASSWORD CASE SENSITIVITY
• PROFILES AND PASSWORD POLICIES
• ROLES IN ORACLE12C
• SEPARATION OF DUTY
• INHERIT [ ANY ] PRIVILEGES
• PERMISSION CONTROL WITHIN VIEWS
• DATABASE PRIVILEGE ANALYSIS
  • Restrictions On Privilege Analysis
  • Potential Benefits Of Privilege Analysis
  • Privilege Analysis In Container Databases
  • Privilege Analysis Policies
  • Disabling A Privilege Analysis Policy
  • Generating A Privilege Analysis Report
  • Dropping A Privilege Analysis Policy
• SIGNIFICANT CHANGES TO ROLES AND PRIVILEGES
• LAB 17:  PRIVILEGE ENHANCEMENTS
• LAB 17 SOLUTIONS: PRIVILEGE ENHANCEMENTS

CHAPTER 18 – DATA REDACTION

• OVERVIEW OF DATA REDACTION
• EXEMPTION FROM REDACTION POLICIES
• HOW AND WHEN REDACTION OCCURS
• REDACTION EXAMPLES
• FUNCTION_PARAMETERS FOR PARTIAL REDACTION
• USING REGULAR EXPRESSIONS
• USING RANDOM VALUES IN REDACTION
• THE EXPRESSION PARAMETER
• ENABLE / DISABLE / DROP REDACTION POLICIES
• USING RES FOR PARTIAL VARCHAR2 REDACTIONS
• REDACTION_POLICIES / REDACTION_COLUMNS
• LAB 18:  DATA REDACTION
• LAB 18 SOLUTIONS: DATA REDACTION

CHAPTER 19 – RMAN / FDA ENHANCEMENTS

• NEW BACKUP SUPER USER
• RMAN CONNECTIONS
• SELECT AND DESCRIBE FROM WITHIN RMAN
• SIMPLER SQL COMMANDS
• DUPLICATE WITH NOOPEN
• CROSS-PLATFORM BACKUP AND RESTORE
• RECOVERY OVER A NETWORK
• STORAGE SNAPSHOT TECHNOLOGY
• TABLE AND PARTITION RECOVERY
• MULTISECTION BACKUPS ON VERY LARGE FILES
• ACTIVE DATABASE DUPLICATION ENHANCEMENTS
• TABLESPACE TRANSPORTS ACROSS PLATFORMS AND ENDIANNESS
  • Convert At Source
  • Restore From Converted At Source
  • Convert At Target
  • Restore From Converted At Target
• STANDBY DATABASE UPDATING
• DATA GUARD ENHANCEMENTS
• ORACLE SCHEDULER SCRIPT JOBS
• LAB 19:  RMAN / FDA ENHANCEMENTS
• LAB 19 SOLUTIONS: RMAN / FDA ENHANCEMENTS

CHAPTER 20 – DB OPERATIONS

• DATABASE OPERATIONS
• DO MONITORING USE CASES
• ENABLING DATABASE OPERATIONS MONITORING
• DB OPERATIONS MONITORING ARCHITECTURE OVERVIEW
• DO ATTRIBUTES
• USING DBMS_SQL_MONITOR
  • BEGIN_OPERATION Function
  • END_OPERATION Procedure
  • REPORT_SQL_MONITOR Function
  • SIMPLE DATABASE OPERATIONS MONITORING EXAMPLE
  • THE ACTIVE REPORT TYPE
• NEW OPTIMIZER HINTS
• LAB 20:  DB OPERATIONS
• LAB 20 SOLUTIONS: DB OPERATIONS

CHAPTER 21 – SCHEMA AND DATA CHANGES

• DATABASE CHANGES
• CHANGE MANAGEMENT CAPABILITIES
• THE DBMS_COMPARISON PACKAGE
• THE CREATE COMPARISON PROCEDURE
• THE COMPARE FUNCTION
• DISCOVERING DIFFERENCES
• THE CONVERGE PROCEDURE
• THE RECHECK FUNCTION
• THE DBA_COMPARISON_COLUMNS VIEW
• LAB 21:  SCHEMA AND DATA CHANGES
• LAB 21 SOLUTIONS: SCHEMA AND DATA CHANGES

CHAPTER 22 – SQL TUNING

• ADAPTIVE EXECUTION PLANS
• DBMS_XPLAN AND ADAPTIVE EXECUTION PLANS
• SQL PLAN DIRECTIVES / EXTENDED STATISTICS
• ENHANCED STATISTICS GATHERING
• HISTOGRAM ENHANCEMENTS
• LAB 22:  SQL TUNING
• LAB 22 SOLUTIONS: SQL TUNING

CHAPTER 23 – REAL-TIME ADDM / PERFORMANCE

• THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR
• REAL-TIME ADDM
• EMERGENCY MONITORING / CURRENT ADDM FINDINGS
• MONITORING THE DATABASE WITH EMDE
• USING THE PERFORMANCE HUB
  • The Summary Tab
  • The RAC Tab
  • The Activity Tab
  • The Workload Tab
  • The Monitored SQL Tab
  • The ADDM Tab
  • The Current ADDM Findings Tab
  • The Database Time Tab
  • The Resources Tab
  • The System Statistics Tab
  • Workload Repository Reports
  • Modifying Automatic Snapshot Settings
• USING OTHER ADVISORS
  • SQL Tuning Advisor Details
  • The Automatic SQL Tuning Advisor
  • SQL Access Advisor
• DATABASE REPLAY
• WORKLOAD INTELLIGENCE OVERVIEW
• WORKLOAD DIMENSIONS
• REPORTS
• SECUREFILES
• PGA_AGGREGATE_LIMIT
• REAL APPLICATION TESTING
• PATCH INVENTORY PACKAGE
• STATISTICS ENHANCEMENTS
  • Automatic Column Group Detection
  • Concurrent Statistics Gathering
  • Incremental Statistics Enhancements
  • Global Temporary Tables Session-Private Statistics
  • Reporting Mode
  • Past Statistics Collection Reports
• LAB 23:  REAL-TIME ADDM / PERFORMANCE
• LAB 23 SOLUTIONS: REAL-TIME ADDM / PERFORMANCE

CHAPTER 24 – RESOURCE MANAGER FOR CDBS

• RESOURCE MANAGER AND CDBS
  • CDB Resource Plans
  • The Initial Default Directive Attributes For PDBs
  • PDB Resource Plans
  • Background And Administrative Tasks
  • Creating The Resource Plan For The CDB
  • Updating The CDB Resource Plan
  • Adding A New Plan Directive For A PDB
  • Deleting A PDB’s Plan Directive
  • Enabling The CDB Resource Plan
  • Disabling The CDB Resource Plan
  • Deleting The Entire CDB Resource Plan
  • Creating / Modifying PDB Resource Plans
  • Enabling / Disabling PDB Resource Plans
  • Resource Manager Views
  • Enabling Resource Manager In PDBs
  • Using Cloud Control With Resource Manager
• RUNAWAY QUERY MANAGEMENT
• DBMS_SCHEDULER WITH A CDB
• LAB 24:  RESOURCE MANAGER FOR CDBS
• LAB 24 SOLUTIONS: RESOURCE MANAGER / PERFORMANCE

CHAPTER 25 – TABLE / INDEX ENHANCEMENTS

• CREATING MULTIPLE INDEXES ON COLUMNS
• ADVANCED ROW COMPRESSION
• ONLINE REDEFINITION ENHANCEMENTS
• DML_LOCK_TIMEOUT
• EDITIONING ENHANCEMENTS

CHAPTER 26 – ADR / NETWORK ENHANCEMENTS

• ADR DIRECTORY STRUCTURE
• ADR COMMAND INTERPRETER ENHANCEMENTS
• ADVANCED NETWORK COMPRESSION
• MULTIPROCESS / MULTITHREADED ARCHITECTURE
• VERY LARGE NETWORK BUFFERS

CHAPTER 27 – DATA LOADING ENHANCEMENTS

• DATA PUMP ENHANCEMENTS
  • Data Pump VIEWS_AS_TABLES
  • Transform Clause Enhancements
  • New Parameters
• LOG MINER ENHANCEMENTS
• FULL TRANSPORTABLE EXPORT / IMPORT
• SQL*LOADER ENHANCEMENTS
  • SQL*Loader Express Mode
  • SQL*Loader Enhancements
  • SQL*Loader / External Table Common Enhancements

CHAPTER 28 – PARTITIONING ENHANCEMENTS

• MOVING PARTITIONS ONLINE
• INTERVAL-REFERENCE PARTITIONING
• ENHANCED CASCADE FUNCTIONALITY
• OPERATING ON MULTIPLE PARTITIONS
• PARTIAL INDEXES
• GLOBAL INDEX MAINTENANCE
• CONTROLLING STATISTICS GATHERING FOR PARTITIONS