11/22/13
Sachi n' s D BA Bl og : New featur es i n Or acl e Database 12c
New features in Oracle Database 12c Oracle Database 12c, Database 12c, c for cloud, a multi-tenant database management system, with nearly 500 new features. Released on June 26, 2013 New Features
Oracle Database
Duplicate
Weight loss patch reviews
Weight loss plan
SQL: Increased Increa sed size limit limit for VARCHAR2, VARCHAR2, NVARCHAR NVARCHAR2, 2, and RAW datat ypes t o 32K (from 4K). 4K). We can make a column invisible. SQL> create table t table t est (colum (c olumn-name n-name c olumn-t ype invisible); invisible); SQL> alter table table-name modify column-name invisible; SQL> alter table table-name modify column-name visible; Oracle Database 12c has new feature c feature c alled alled "Identity "Identity Columns" whic whic h are aut o-inc remented remented at the time time of insertion (like in MySQL). SQL> create table dept (dept_id number generated as identity, dept_name varchar); SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name dept_name varchar); Temporary undo (for global temporary tables) will not generate undo. No need to shutdown database for database for changing archive log mode. Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, Duplicate Indexes 11.2, if we try to create an index using the same columns, in the same order, as an existing existing index, we'll get an error. In some cases, we might want two different types of index on index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index). PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function) The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies applies also also to sequences, sequences, identity identity types et c... Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records. select ... fetc h first first n rows only; only; select ... offset m rows fetch next n rows only; select ... fetc h first first n percent rows only; select ... fetch first n percent rows with ties; Moving and Renaming datafile is now ONLINE, no need to put datafile in offline. The TRUNCATE command has been enhanced with a CASCADE option which follows child records. Reduces contents of regular UNDO, allowing better flashback operations.
PL/SQL: PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code. SQL WITH Clause Enhancement - In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement. Implicit Result Sets - create a procedure, open a ref cursor, return the results results.. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases). MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database. We can use Booleans values in dynamic PL/SQL. Still no Booleans as database types.
Database: New bac kground kground proce processes sses - LREG LREG (Liste (Listener ner Registration), Registration), SA (SGA (SGA Allocat or), RM Oracle Database 12c Data Pump will Pump will allow turning off redo for the import operation (only). Enhanced statistics statistics (Hybrid (Hybrid histograms for more than 254 distinct values, dynamic sampling up to eleven, and stats automatically gathered during load). satya- dba.bl og spot.i n/2012/10/new- featur es- i n- or acl e- database- 12c.html
1/3
11/22/13
Sachin's DBA Blog: New features in Oracle Database 12c
Row pat tern matching - "MATCH_RECOGNIZATION" (identificat ion of pat terns within a t able ordered/sorted by the SQL statement). Adaptive execution plans (change of the plan at runtime). Oracle 12c includes database level redaction, allowing granular control of access to sensitive data. Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit. UNDO for temporary t ables c an now be managed in TEMP, rather than the regular UNDO tablespace. Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c v ersion), replaces the Oracle Databaseconsole and, is installed automatically. Reduces the size of redo associated with recovering the regular UNDO tablespace.
ASM: (Oracle Grid Infrastructure new features) Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful. Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.
RMAN: RMAN TABLE Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required). Running SQL commands in RMAN without SQL keyword. Recover or copy files from Standby databases. Refresh a single datafile on the primary from the standby (or standby from primary). Table level restoration i.e object level. Incremental rec overy more fast er, many of the tasks removed. Rolling forward/Synchronizing a standby database.
Partitioning: Partitioning enhancements (partition truncate, cascading, global index c leanup, online moving of a partition, ...) Multiple partition operations in a single DDL. Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type. Cascade for TRUNCATE and EXCHANGE partition. Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later. Online move of a partition(without DBMS_REDEFINTIION).
Patching: Centralised patching. We c an test patc hes on database c opies, rolling patches out centrally once test ing is complete.
Compression: Automated compression with heat map. Optimisation c an be run on live dat abases with no disruption. Data optimization will monitor the data usage and w ith policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs. Advanced Row compression (for Hot Data). Columnar Query compression (for Warm Data). Columnar Archive compression (for Archive Data).
Data Guard: Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport. Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the satya-dba.blogspot.in/2012/10/new-features-in-oracle-database-12c.html
2/3
11/22/13
Sachin's DBA Blog: New features in Oracle Database 12c
target. Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.
Dataguard Broker now supports cascaded standby. Global Temporary Tables can now be used on an Active Guard standby database.
Pluggable Databases: In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where eac h database has its own background processes and shared memory. T his helps in database consolidation and reduces the overhead of managing multiple desperate databases. Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation. Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB. A new admin role "CDB Administrator" has been introduced in Oracle 12.1 release databases. Multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.
All Oracle database options/features are available on the PDB level. RMAN backup at CDB level. We can unplug a PDB from a CDB to another CDB. PDB's can be cloned inside the CDB. Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs. Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”. Database pat ch/upgrade management very quick as CDB is a single point of installation. Each PDB has its own data dictionary. Data Guard configuration on CDB as whole. RMAN point-in-time recovery at PDB level (while other PDB's remains open). Resource Manager is extended for c reating, unplugging, plugging in, and c loning, dropping or even s ett ing up for the open mode of the PDB. Flashback of a PDB should be available for Oracle 12c Release 2. Entire containers can be backed up in single run, regardless of how many databases they contain. Upgrade one container database and all pluggable databases are upgraded.
New Commands create pluggable database ... alter pluggable database ... drop pluggable dat abase ...
New Views/Packages in Oracle 12c Release1 dba_pdbs v$pdbs cdb_data_files dbms_pdb dbms_qopatch
satya-dba.blogspot.in/2012/10/new-features-in-oracle-database-12c.html
3/3
