Few New features in Oracle 12c database:

Few New features in Oracle 12c database:

Few New features in Oracle 12c database:

Oracle Database 12c Architecture

• PROCESSOR_GROUP_NAME parameter — allows to bind the instance to a Linux CGROUP, it’s NUMA aware

• THREADED_EXECUTION parameter — Enables Multithreaded Architecture (MTA), intended to speed up internal work and saves process spawning. No OS authentication allowed, need to use the password file (thus, “sqlplus / as sysdba” and “rman target /” do not work any more)

• USE_DEDICATED_BROKER parameter — use threads, not dedicated servers

• v$process STID column — holds reference to the system thread in multithreaded architecture

• DEDICATED_THROUGH_BROKER_[LISTENERNAME] listener parameter — make the listener spawn threads, not dedicated servers

• To be done: Investigate serialization waits in MTA vs. conventional process architecture

• Smart Flash Cache allows more than one device — possible to deactivate one at runtime; if two, one is used for OLTP, one for OLAP (distinction by Parallel Execution)

• SDU (Session Data Unit) of TNS allows max. 2MB — calculate 70 bytes of overhead per packet; use “trcasst -t” utility;

Generic Database Administration

• catupgrd.sql deprecated — use catctl.pl now

• DBMS_QOPATCH — query opatch information within RDBMS

• DBMS_SCHEDULER now has a RMAN job type

• option_usage.sql and used_option_details.sql scripts — from My Oracle Support (ID 1317265.1) for license-relevant infos

• STATSPACK enriched with PDB awareness — but always install it into the PDB, not into the CDB

• ADRCI improved — some menus allow interactive HOME selection (SHOW CONTROL does not)

• varchar2(32k) / nvarchar2(32k) — are just LOBs, with all limitations (no ORDER BY / GROUP BY)

Multitenant Database

• dbca for pdb’s — simple creation (of scripts)

• alter pluggable database all open [except pdb2]; — to start up SOME pdb’s

• alter system disable restricted session; — on a pdb it only works if PDB_PLUG_IN_VIOLATIONS shows NOTHING for this pdb

• Logminer shows a CON_ID — so we can find out which pdb did what

• Seed PDB can’t be changed — use an own PDB as template (maybe for test automation)

Data Management and SQL

• Automatic Data Optimization (ADO)

• ADO allows moving data to other tablespaces by analyzing the data usage profile

• Heat Maps and ADO are part of Advanced Compression Option (EE)

• ADO policies are enforced in Maintenance Window. What MMON can do, happens in 15 minute intervals.

• Datapump aligns ADO policies when using REMAP_TABLESPACE — subject to bugs, got ORA-39151

• DDL

• Online DDL — also possible in SE

• Datafile online move is possible now (EE)

• Identity Columns — finally! Based on sequence (careful with default NULL, always combine with UNIQUE constraint)

• Invisible Columns — for commissioning application changes

• Indexes set to UNUSED — analysis and firefighting

• Partitioning

• Interval Partitioning

• Reference Partitioning

• Partition Split into multiple pieces with one command / one operation

• Partial Index — Global and Partitioned Indexes over subsets of a table

• INDEXING ON / OFF per partition — global switch what part. indexes are created for when none specified

• SQL

• Row Limiting Clause — now running after ORDER BY: “FETCH FIRST n ROWS ONLY” or “OFFSET n ROWS FETCH NEXT n ROWS ONLY”

Performance, Optimizer and Statistics

• SQL Plan Management now available w/o diagnostics/tuning pack, but only for EE

• New histogram types (hybrid histogram and top frequency histogram) are created when using sampling percentage AUTO_SAMPLE_SIZE. No height balanced histograms are used any more.

• When using sampling percentage other than AUTO_SAMPLE_SIZE, no new types are created, but height balanced and frequency histograms in legacy style.

• dbms_stats.seed_col_usage and dbms_stats.report_col_usage — extended statistics can be created automatically

Adaptive Plans (AP)

• Adaptive Plans divide into: Dynamic (1) and Re-Optimizable plans (2)

• AP Fields in v$sql: is_resolved_adaptive_plan (1) and is_reoptimizable (2)

• AP ONLY works for Nested Loop vs. Hash join. No other join ops or index access vs. table access full!

• AP display: DBMS_XPLAN.display_cursor(format=>’+adaptive’)

• AP directives management with dbms_spd.*

Security

• Privilege Analysis — allows to find out who REALLY needs a privilege (requires Database Vault, EE)

• Grant INHERIT PRIVILEGES protects privilege inheritance to a PL/SQL objects defined with AUTHID CURRENT_USER

• ddl_logging — Logging DDL with ADR requires Lifecycle Managament Pack (EE)

• GRANT your_role TO your_procedure — to run SUDO (or SUID)-like with a package

Datapump (IMPDP/EXPDP)

• “LOGTIME” — logs timestamps for each operation

• It’s possible to export a view as a table (=with its data)

• “DISABLE_ARCHIVE_LOGGING” — allows NOLOGGING import, don’t forget to  backup the DB afterwards

• Full Transportable option — for PDB transport and migrations

Backup and Recovery, Migration

• RMAN can do SQL now (but only very basic formatting)

• PDBs can be backed up separately, the backup depends on the last Container Database backup. But CAREFUL! CDB backups on which a PDB backup depends on, are not marked. Thus, deleted without asking. Your PDB backup is rendered useless!

• Flagging the PDB backup as KEEP FOREVER does not change the last point.

• RMAN does all steps for Tablespace PITR [in a PDB] — including aux instance

• RMAN multi-section backup for incremental backups (EE)

• RMAN cross platform migration:”BACKUP TO PLATFORM” and “RESTORE [FOREIGN] DATABASE”

• DMU — Database Migration to Unicode utility in $ORACLE_HOME/dmu

Sql*plus know-how

• Command Line Replace “c /old/new” — to change old command before executing it with /

• SQL> show con_name — to show where we are

• SQL> alter session set container=pdb1 — to change where we are

Enterprise Manager

• Named Credentials in Cloud Control — allow users to use passwords w/o knowing them

• dbms_xdb.gethttpport/sethttpport — configuration of Enterprise Manager Database Express

Application development

• DBOP (Java) / BEGIN_OPERATION (PL/SQL) — mark operations across transaction and session borders

• FORCE_TRACKING — force all operations to be tracked in last point

High Availability

• Grid Infrastructure: Built-in NFS service for ACFS shares

11gR2 and older features rediscovered

• DEFAULT_SERVICE_ listener parameter — it is what the name tells

• RMAN ‘report schema’ — quick overview

• RMAN backup undo optimization — excludes committed undo data

• LOB: Basic File vs. SecureFile; SecureFiles are “Advanced LOBs”, optimized for concurrency (EE)

• Flashback Data Archive (EE) — but basic Flashback Data Archive available in Standard Edition!

• trcsess utility

• Remember the layers: Adaptive Cursor Sharing -> SQL Plan Management -> SQL Plan (=> ACS is unaffected by SPM)

View  and package collection

• v$pwfile_users

• PDB_PLUG_IN_VIOLATIONS

• dba_autotask_task

• dbms_spd

• dbms_xplan

• dbms_qopatch

Utility collection

• trcsess — trace helper

• trcasst -t — TNS tracer

• dmu — Unicode Migration Utility