Oracle New Features - 10g/11g/12c

Oracle 10g 

• Flashback Versions Query
• Oracle Data Pump
• Automatic Workload Repository
• Automatic Storage Management
• Automatic Shared Memory Management
• ADDM and SQL Tuning Advisor
• Virtual Private Database
• Flashback Table

Oracle 11g 

Online Table Redefinition

Invisible Indexes

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

Adaptive Cursor Sharing

DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans.

This is because the optimizer peaks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration.

If we look at the V$SQL view entry for this query, we can see theIS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.

CURSOR_SHARING Initialization Parameter

The CURSOR_SHARING initialization parameter controls how the database processes statements with bind variables.In Oracle Database 12c, the parameter supports the following values:

EXACT : This is the default value. The database enables only textually identical statements to share a cursor. The database does not attempt to replace literal values with system-generated bind variables. In this case, the optimizer generates a plan for each statement based on the literal value.

FORCE : The database replaces all literals with system-generated bind variables. For statements that are identical after the bind variables replace the literals, the optimizer uses the same plan.

Active Data Guard 

Interval Partition 

RAC one node, place OCR and voting disk on ASM ,SCAN listeners

SQL plan baselines

RMAN recovery advisor

Advance Compression 

• Basic compression is a free option with the Enterprise Edition version of the database.
• Basic compression is not designed for OLTP operations. You will get no compression benefits from conventional path inserts, updates or deletes. It is designed for use with direct path loads only. If you want table compression suitable for OLTP operations you will need to use the Advanced Compression option available from 11g onwards.
• Basic compression can be used with OLTP systems that use partitioning. For example, if you have a table that is partitioned by a date column, it's possible you will have partitions with older static data, which can be compressed, leaving the newer active data in uncompressed partitions.
• Depending on the nature of the queries, basic compression may improve query performance by reducing the number of blocks read from disk. This is heavily dependent on workload.
• As mentioned previously, the level of compression you can achieve will vary depending on the nature of the data being compressed. For basic compression, the compression is performed at block level, so an individual block must contain repetitive data for compression to have any impact.

Oracle 12c 

Far sync Database

Oracle Dataguard Far Sync instance is a new feature introduced in Oracle Database 12c, which simply works like a archive/redo repeater.. Far sync instance is something different than the Oracle Database instance, as its mission is accepting redo from the primary database and then shipping that redo to other members of the Oracle Data Guard configuration..

Far Sync instance is an instance without a database.. Although; it has controlfile, and capability to read from standby redolog files and write them into its local archive log files, Far Sync instance does not map any database file, in other words, does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.. By the way; In order to use far sync feature, you need to have Oracle Active Data Guard license.

Supported Protection modes of Far Sync Instance are Maximum Availability and Maximum Performance.

In Maximum Availability mode, Sync transport method is used between primary and far sync instances.

Pluggable Databases

Redaction Policy

Restore table using RMAN

Size Limit on Varchar2, NVarchar2, Raw Data Types increased


Oracle 12.2 new features : 

Ø  Oracle Database Sharding

Sharding with Oracle Database 12c Release 2 (12.2) is architecture for suitable online transaction processing (OLTP) applications where data is horizontally partitioned across multiple discrete Oracle databases, called shards, which share no hardware or software. The collection of shards is presented to an application as a single logical Oracle database.

Ø  Application Continuity for Planned Database Maintenance

There is no reason for scheduled maintenance to be burdensome for application users. Following very simple configuration and operational practices, you can completely hide the adverse effects of scheduled maintenance from all application types. Using the correct setup, no errors are reported for either new, incoming work, or for existing work while maintenance is in progress.

For scheduled maintenance in Oracle Database 12c Release 2 (12.2), Fast Application Notification (FAN) is embedded in the Java Database Connectivity (JDBC) thin driver to support all Oracle Java solutions for planned and unplanned outages. Additionally, Transparent Application Failover (TAF) is extended with Transaction Guard to fail over more OCI-based applications transparently. As well, Oracle Data Provider for Microsoft .NET (ODP.NET) adds support for Application Continuity. This feature set provides a simple to use and fool-proof way for most applications to continue operation during scheduled maintenance. All of these features come with nil application changes required in almost all cases. A one command interface is also introduced for Server Control Utility (SRVCTL), Global Data Services Control Utility (GDSCTL), and Oracle Data Guard broker to enable database administrators to control the scheduled maintenance for all applications with a single command from the database.

For scheduled maintenance at the database server, Oracle Database 12c Release 2 (12.2) provides:

Nil errors reported to applications for new, incoming work and existing work while maintenance is in progress.

Transparent redirection of the active database sessions to another functional service. A one command orchestration focuses on the best possible application experience, as opposed to the maintenance applied, as quickly as possible, with no consideration for the applications.

Re-balancing of database sessions, as needed, during and after the maintenance completes.

This feature hides scheduled maintenance operations that are required for the underlying infrastructure (Oracle Database, Oracle Grid Infrastructure, operating system, and hardware) without placing a burden on application developers. The solution differentiates Oracle Database by enabling you to conduct scheduled maintenance without interruption to most application work.

  

Ø  Partitioning: Auto-List Partitioning

The database automatically creates a separate (new) partition for every distinct partition key value of the table. Auto-list partitioning removes the management burden from the DBAs to manually maintain a list of partitioned tables for a large number of distinct key values that require individual partitions. It also automatically copes with the unplanned partition key values without the need of a DEFAULT partition.

Ø  Converting a Non-Partitioned Table to a Partitioned Table using single Alter table statement

Before 12.2 this was a convoluted process. You had to create a partitioned copy of the table and transfer the data over. You could use DBMS_redefinition to do this online. But it was a headache and easy to get wrong. In Oracle Database 12c Release 2 it's easy. All you need is a single alter table command.

Ø  Automatically Synchronize Password Files in Oracle Data Guard Configurations

This feature automatically synchronizes password files across Oracle Data Guard configurations. When the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.

This feature provides additional automation that further simplifies management of Oracle Data Guard configurations.

Ø  Creating Data Guard Standbys With Database Configuration Assistant

Database Configuration Assistant (DBCA) is enhanced to support creation of an Oracle Data Guard standby database from an existing primary database using the DBCA command-line interface. This ability reduces the manual steps you must execute to create a standby database outside of Oracle Enterprise Manager. In addition, DBCA allows custom scripts to be run at the end of the standby database creation. This feature enables users to script the creation of standby databases in a very simple manner from a command-line interface.

Ø  Multi-Instance Redo Apply

Releases prior to Oracle Database 12c Release 2 (12.2) limited Redo Apply (physical standby database) to a single instance on an Oracle RAC standby database. Redo Apply can now run on all or some standby instances as configured by the user. This enables Redo Apply performance to scale, if needed, by adding additional standby instances.

With this new feature, recovery time objectives can be achieved for any primary workload. This is particularly important for Oracle Exadata customers and customers with large Oracle RAC clusters. Oracle Active Data Guard users also have real-time access to current information. Scaling apply performance in this manner means that the standby database is always up-to-date even at very high volumes on the largest Oracle RAC clusters.

Ø  TDE Tablespace Live Conversion

You can now encrypt, decrypt, and rekey existing tablespaces with Transparent Data Encryption (TDE) tablespace live conversion. A TDE tablespace can be easily deployed, performing the initial encryption that migrates to an encrypted tablespace with zero downtime. This feature also enables automated deep rotation of data encryption keys used by TDE tablespace encryption in the background with zero downtime.

This feature performs initial cryptographic migration for TDE tablespace encryption on the tablespace data in the background so that the tablespace can continue servicing SQL statements that insert, delete, select, modify, and so on.

Ø  Oracle Database In-Memory Support on Oracle Active Data Guard

Oracle Active Data Guard allows a standby database to be opened in read-only mode. This capability enables enterprises to off-load production reporting workloads from their primary databases to synchronized standby databases. Thus, you can now use the in-memory column store on an Oracle Active Data Guard standby database. This enables the reporting workload that is processed on the standby database to take advantage of accessing data in a compressed columnar format, in memory. This also enables scans, joins and aggregates to perform much faster than the traditional on-disk formats performed.

It is also possible to populate a completely different set of data in the in-memory column store on the primary and standby databases, effectively doubling the size of the in-memory column store that is available to the application.

By enabling the reporting workload that is running on an Oracle Active Data Guard standby database to use the in-memory column store, you can greatly improve the execution performance of the workload. This is because the processing can take full advantage of accessing data in a compressed columnar format, in memory.

Ø  Oracle Database Can Contain Both Read/Write and Read-Only Instances

Oracle Database 12c Release 2 (12.2) provides two types of instances within the same database: read/write and read-only. The read/write instances are regular Oracle Database instances and can process updates to the data (for example, DML statements UPDATE, DELETE, INSERT, and MERGE), partition maintenance operations, and so on. You can directly connect to read/write instances.

The read-only instances can process only queries and cannot directly update data. You cannot directly connect to read-only instances. Note that there are parallel SQL statements that contain both updates and querying of the data (for example, INSERT INTO <select query>). In this case the <select query> part of the statement is processed on both read/write and read-only instances while the INSERT part is processed only on read/write instances.

To designate an instance as read-only, set the INSTANCE_MODE parameter to READ_ONLY. (The default value of the parameter is READ_WRITE.)

The introduction of read-only instances significantly improves scalability of parallel queries for data warehousing workloads and allows Oracle Database to run on hundreds of physical nodes.

Ø  Advanced Index Compression

Prior to this release, the only form of advanced index compression was low compression. Now you can also specify high compression. High compression provides even more space savings than low compression.

Indexes consume a large amount of storage within many databases. The high level of advanced index compression provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the following advantages over low compression:

Gives higher compression ratios in most cases.

Employs more complex compression algorithms than advanced low compression.

Stores data in a compression unit, which is a special on-disk format.