Difference between Oracle 10g and 11g and 12c Database

Differences between Oracle 11g and Oracle 12c

What is Oracle 10g?

Oracle 10g is a computing product group belonging to Oracle's grid. Along with other tools and features, Oracle 10g provides an application server and a database management system (DBMS). It offers several supporting grid computing features of the likes of automatic load balancing, resource sharing, etc. Individuals and corporations use Oracle 10g to automate different types of database management tasks. Oracle 10 g uses the Real Application Cluster (RAC) tool for installing a database across multiple servers.

Oracle client 10g is a follower of the 9i platform hosted by Oracle. As per Oracle, the letter g in the name of Oracle 10g signifies the Organization’s commitment to its grid modular structure. Early adopters solely deployed 10 g for using Oracle’s automation features rather than implementing a complex grid environment. With the introduction of Oracle10g, DBAs have experienced and understood the benefits of applying new Oracle technology in the best possible way to their everyday jobs. These features are helping Oracle 10g users enjoy several time-saving and money-saving capabilities.

Features of Oracle 10g

Oracle Database 10g incorporates several new tools that help increase the efficiency of DBAs and developers. Some of the most prominent features exclusive of automation in Oracle 10g are:

1. Binding in-lists in 10g: The newly introduced MEMBER OF collection condition can be used as an alternative to IN-list binding in 10g.
2. Partition-wise dependencies possible in 10g release 2: Partitions can be modified without the requirement of invalidating dependent objects.
3. The collect function in 10g: String aggregation can be used along with the newly introduced 10g COLLECT group function.
4. Pl/SQL optimization in Oracle 10g: The compiler optimization for much quicker PL/SQL optimization in Oracle 10g comes equipped with altogether new features about optimization bugs.
5. SQL plan enhancements: New Oracle 10g features allow for enhanced SQL performance investigations that are simpler to use.
6. Dml error logging performance: The Release 2 of Oracle 10g features add-on performance characteristics of DML error logging.
7. Flashback restore points enable the capturing of a point in time for affecting flashback operations in release 2 of Oracle 10g.
8. Oracle now "fixes" DBMS_OUTPUT via new enhancements responsible for impacting dbms_output in the Oracle 10g release 2.
9. The auto-trace enhancement feature allows the use of DBMS_XPLAN for creating output for its explain plans in Oracle 10g release 2.
10. The evolution of Oracle data can now be viewed as a flashback version query in Oracle 10g.
11. Oracle 10g enables DBAs and developers to enqueue/dequeue in bulk with its new array-based advanced queuing tool in 10g.
12. External tables can be used for unloading/ write/read data in 10g.
13. Oracle offers new SQL tuning recommendations with 10g.
14. Exceptions are capable of being traced back to the source in Oracle 10g

What is Oracle 11g?

Version 11g of Oracle Database incorporates built-in testing capabilities for changes along with other useful tools and features. Oracle 11g helps DBAs and developers with the capacity of viewing tables that have been added earlier on, encourages superior compression of different kinds of data, provides useful disaster recovery functions, and a lot more. The “g” in Oracle 11g stands for "grid computing." It supports different clusters of servers that are capable of being treated as singular units.

Features of Oracle 11g

New features in Oracle 11g related to “Real Application Testing" or change assurance find a place in Oracle Database 11g. Oracle Database 11g allows the database infrastructure to become more resilient, efficient, and manageable. For instance, there are many compelling new features related to the ease of management and design of partitioned tables.

Some of the best used and popular features of Oracle 11g include the following:

1. Database Replay tool helps in the capturing of production database workload. It replays the results in a test or a database (same) for assessing the overall impact of change. The SQL statements thus captured can be replayed at will.
2. The SQL Performance Analyzer predicts the impact and performance of changes made to SQL even before the modifications take place. This feature accurately predicts the results of the actual SQL statements that are issued against a database – besides, it monitors SQL performance metrics in real-time.
3. Edition-based Redefinition, which was introduced in Release 2, enables the patching and updating process of various data objects even as the application is online.
4. The new features related to Referential, Internal, Virtual Column partitioning as well as other sub-partitioning options are useful for handling the partitioned tables in Oracle 11g with ease.
5. The revolutionary Edition-Based Redefinition feature helps in the patching/ updating of application data objects even as the application remains in a state of uninterrupted use (this relates to Release 2 only).
6. The tools and features for schema management help in the easy addition of columns containing default values. These new features of the 11g version of Oracle aid the exploration of virtual columns, invisible indexes, and read-only tables.
7. Oracle 11g presents new tools for RAC One Node, patching, and upgrades, Clusterware, etc. that enables the use of a unique name for clusters, enables HA for single-instance databases, places OCRs/voting disks on the ASM, etc.
8. The new features of Oracle 11g in the areas connected with OLAP and data warehousing include the Analytic Workspace Manager. Cube Organized MVs, Query Rewrites that are extendible to sub queries/remote tables, and more.
9. The features responsible for the use of simple integer, "real" native compilation, inlining of code, PLS timer, etc. are beneficial for the enhancement of PL/SQL Performance
10. The PL/SQL efficient coding triggers off the firing of different events. They fuel the ability of Oracle 11g to force triggers belonging to the same type to create a sequence of events.
11. The transaction management features of Oracle 11g explore the LogMiner interface of Enterprise Manager's and help in the usage of the Flashback Data Archive.
12. The new security features of Oracle 11g takes care of data masking, case-sensitive passwords, Tablespace Encryption, etc.
13. The Oracle Exadata Simulator, when used in Oracle Database 11g Release 2 EE databases, predicts how statements will react in the Oracle Exadata Database Machine while using the SQL Performance Analyzer, etc.
14. With the SQL Plan Management new feature in Oracle 11g, DBAs and developers may pick up the right plan every time. The incorporation of bind variables ensures new execution plans that serve to be less cumbersome for use by DBAs.
15. The new features of Oracle 11g include smart tools for multicolumn statistics, online patching, automatic memory management, etc.
16. Oracle 11g new features offer help via SQL Access Advisor for the actual usage of the tables along with their data.
17. The Pivot and Unpivot SQL operations give off information in the form of spreadsheet-type crosstab reports belonging to relational tables that use simple SQL as well as store data from crosstab tables to different relational tables.
18. The features of the Data Recovery Advisor allow for parallel backup of the same files, creation, and management of virtual catalogs, undropping of tablespaces, etc.
19. The Resiliency tools and features related to Oracle 11g lays down the platform for Automatic Diagnostic Repository, Automatic Health Monitoring as well as other new resiliency features.
20. The Automatic Storage Management features Oracle 11g encompasses variable extent sizes, new SYSASM roles, and many ASM improvements.
21. Oracle Database 11g supports data compression with new features of Advanced /Hybrid Columnar Compression.
22. Oracle 11g allows for brand new features related to PL/SQL Function Cache, SQL Result Cache, Database Resident Connection Pooling, and so forth.
23. New features of 11g in Oracle provide next-generation LOB tools for LOB encryption, deduplication, compression, and asynchronicity.

What is Oracle 12c?

The Oracle Database 12c is an enterprise-class database that is noted for its high-performance, result-oriented tools, and features. Oracle released Oracle Database 12c into general availability on July 1, 2013. Designed specifically for the cloud by Oracle, Oracle Database 12c has introduced 500 new features that have added to the functionality of databases, mostly pluggable databases, as well as multitenant architecture. The Oracle Database 12c In-Memory is included in the Oracle Database 12c release 12.0.1.2 that features optional add-ons with in-memory capabilities. These in-memory options allow Oracle Database 12c to be featured as the first database that offers real-time analytics.

New Features of 12c

Oracle Database 12c features make it easy for developers and DBAs to make their transition to cloud applications. For instance, its multitenant architecture has been designed for simplifying consolidation to forms without necessitating any changes. The consolidation tools of Oracle 12c are beneficial for cloud readiness. Besides, its pluggable databases are backed by rapid provisioning, portability capabilities, etc. Overall, Oracle Database 12c is very useful for self-service provisioning and database as a service. The new features of Oracle 12g include:

1. With 12C, Oracle is addressing the problems related to Multitenancy via the functionality of pluggable databases backed by data consolidation tools. This feature has led to significant changes in database architecture with the help of Container Databases that are referred to as CBDs and Pluggable Databases (PDB). The Container Database owns the process and memory. The PDB takes care of user data while the container holds the metadata. The seed PDB and up to 253 PDBs can be created using this feature. The pluggable database feature helps upgrade, patch, monitor, tune, adjust, back up and guard the data of a single instance to get separate HRs or Scott schemas for every PDB. The CPU percentage can also be allocated for each PDB.
2. Another new feature of Oracle 12C is its Redaction Policy. Data Redaction or masking of data can be set up via a Data Redaction policy that uses a package termed as DBMS_REDACT. This package extends the capability of FGAC and VPD as present in their earlier versions.
3. Oracle Database 12c 03 release introduces the features of Fetch and offsets Replacement and Top N Query to Rownum. This new SQL syntax simplifies the fetching of the first few rows. "Fetch First X Rows only" is the new syntax that can be used for this purpose.
4. The Online Stats Gathering and Adaptive Query Optimization features of the 12C version of Oracle allows the optimizer to enable runtime adjustments to different execution plans to lead to more useful stats. For IAS (Insert As Select) and CTAS (Create Table As Select) statements, the figures can be gathered online for immediate availability.
5. The new Oracle 12c restore command simplifies the task of restoring any particular table in RMAN. Users need not restore a tablespace, use the export/ import features, etc. for this purpose.
6. The limits allocated earlier on the data types NVarchar2, Varchar2, Raw Data Types, etc. have been increased from 4K to 32,767 bytes in Oracle 12C.
7. As all the functions are not existing in the database, in reality, they cannot be found by the command ALL_OBJECTS. The inline PL/SQL procedures and features have been greatly enhanced in Oracle 12C. Now, PL/SQL procedures and functions are capable of being added along with views inline constructs. The query is written in a manner that a real stored procedure is being called.
8. A col can now be 'generated as identity' to sequence replacement with the new Oracle 12c feature. This amounts to creating separate sequences and performing sequence.nextval for every row. Known as the No Sequence Auto-Increment Primary Key, this feature is helping the developer community in many more ways than one.
9. Before the introduction of the 12 version of Oracle, a column was not capable of being in multiple indexes. Now a column can be added to the B-tree index and a Bit Map index 09 at the same time even though only one index would be usable at a time.
10. The new feature of Oracle 12c related to the online migration of sub-partition or partition of tables from a tablespace to another is beneficial for DBAs. Just as online movement could be achieved for non-partitioned tables in the earlier releases, table partitions/ sub-partitions can now be moved either online or offline to other tablespaces. The ONLINE clause allows all DML operations to be performed uninterrupted to the partition or sub-partition involved in any given procedure. Do note that no DML operations are permitted in case the partition or sub-partition is taken offline.
11. With the temp undo feature in Oracle 12C, the undo records are capable of being stored in a temporary table rather than UNDO TS. This leads to the reduced tablespace and lesser redo log space being used.
12. The new database archiving feature of Oracle 12c enables the archiving of rows found in a table by stating them as inactive. The inactive rows remain in the database; they are capable of being optimized with the help of compression but aren’t visible to applications.
13. Oracle 12c allows for invisible columns in a table; they are not found in generic queries.
14. It is now possible to create limits on PGA by activating the automatic PGA management that necessitates PGA_AGGREGATE_LIMIT based parameter settings. The limits set on PGA helps in avoiding excessive usage of the same.
15. DDL statements get automatically logged in xml/log files in case ENABLE_DDL_LOGGING has been set to True.

Advantage of 12c over 10g and 11g Versions

Here are some essential benefits to upgrade to Oracle 12c sooner rather than stick onto Oracle versions 10g or 11g.

Better Upgrade Experience: The entire release upgrade process of Oracle has become better and less risky with the 1c version. Upgrade Assistants are being used to facilitate the process of upgrade ad provide more options and paths for performing the tasks related to upgrades. The same goes for data migration between the different Oracle versions if necessary. 12 c contains several online resources as well as technical seminars that are dedicated to its upgrade programs.

Value for money: The upgraded 12c features and new improvements can be paid for with the help of Support dollars, regardless of new licenses/options not being added to the package. The amount paid for 11g Support Renewal (to a large extent) went into the R&D processes for the development, innovations, and improvements about 12c. Therefore, it makes good sense to get back your money's worth by going for the upgrade to Oracle 12c.

Better Support Experience: The support for 12c seems to be better than that for older releases. Even though the Extended Support programs related to 11g provided plenty of runway for 12c upgrade, the support resources are certainly more focused on the release of this version of Oracle. As only a negligible part of annual maintenance fees directly applies to software fixes, it is a good idea to maximize the value of support dollars by upgrading to newer versions as soon as possible.

Multitenant & Plugged Database: Oracle’s 12c contains a new feature that enables a more straightforward consolidation of more than one database. It also allows the transfer of databases to the cloud to make IaaS, PaaS, DBaaS, etc. deployments more successful. This version offers reduced efforts and time, improved management, and more effectiveness for database upgrades, recovery, backup, etc. Better server resource utilization leads to enhanced levels of consolidation in 12c. The pluggable database features present in single-tenant architecture can be used without new licenses/options, thereby making the upgrade process simpler.

Automatic Data Optimization -New Heat Map features help in the monitoring of a database’s read/writes activities that enable DBAs to conveniently identify the data categorized as hot/warm/cold to access frequency. Additionally, smart compression, storage tiering, and other related features automatically compress and arrange data based on its activity and age. This applies to OLTP, Archive data, Data Warehouse, etc.

In-Memory Caching, Column Store, and Compression – These new features of 12c are not available in Oracle 11g and 10g. They allow for large quantities of memory-based data caching that goes beyond traditional buffer caches. The data thus cached can be saved in compressed columnar formats. The result lies in improved performance and better optimization of server resources. These features are elementary to implement as they are woven into database kernels and are transparent to applications.

Conclusion

In case you have been thinking of a 12c upgrade, then it is indeed time to go through its functions, tools, and features in more significant detail. We have tried our best to give you a closer look at why it is essential to prepare, upgrade, migrate, manage, and report to new 12c environments. In this article, we have thrown light on the difference between Oracle 11g and 12c, the difference between Oracle 10g vs. 11g, advantages of Oracle 12c over 11g and 10g, etc. Alongside, we have shed light upon oracle 11 g new features, Oracle 10g new features, and the new features Oracle 12 c for your benefit. In case you have any further inputs related to Oracle 12c vs. 11g, then write to us in the Comments section below. We shall wait to hear from you.