Few New features in Oracle 12c database:

on
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
*******************************************************************************

Comments

Post a Comment

Oracle DBA Information