A new product …
Last week I spent some days of intense investigation and testing the new database 12c. I found lots of great stuff (even about 11g 🙂 ), and some for sure will make it into a separate blog post. So these are just my notes of a very interesting couple of days, and maybe it can be a guide for you when starting to dive deeper into the newest RDBMS coming from Redwood Shores. Just let me know your opinion, and maybe submit a comment.
Unfortunately, many of the new features are only available in Enterprise Edition or EE plus some options or additional products. As far as I knew the need, I marked my points accordingly. But don’t rely on my information, have a look at the most recent licensing guide!
One word before we start: Do nothing before you know WHAT you do. And with a new Oracle product, you CAN’T know what you do yet. So play happily, but don’t use it in production until second PSU or first patch set and its first PSU.
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
— own blog post to come early October —
- 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
Be careful, but I hope you can use some new features soon – learning by doing is what I suggest.
Usn