Online Tutorials & Training Materials | STechies.com
Register Login

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

|| || 3

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

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. Oracle 10g is used by individuals and corporations for the purpose of automating 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. 10 g was solely deployed by early adopters 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 10g

Oracle Database 10g incorporates several new tools that are helpful for increasing 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 dependant objects.
  3. The collect function in 10g: String aggregation can be used along with the newly introduced 10g COLLECT group function.
  4. Pl/SQL optimisation in Oracle 10g: The compiler optimisation for much quicker PL/SQL optimization in Oracle 10g comes equipped with altogether new features pertaining to optimisation 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 pertaining to 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 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 capability of viewing tables that have been added earlier on, encourages superior compression of different kinds of data, provides effective 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 11g

New features in Oracle 11g related to “Real Application Testing" or change assurance find 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 in 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 – in addition, 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 g11 version of Oracle aids 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 singular 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 subqueries/remote tables and more.
  9. The features responsible for use of simple integer, "real" native compilation, in-lining of code, PLS timer, etc. are very helpful 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 the ways in which 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 Data Recovery Advisor allow for parallel backup of 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 provides next-generation LOB tools for LOB encryption, deduplication, compression and asynchronicity.

Difference between Oracle 10g and Oracle 11g

Compared with Oracle 10g, the later version of Oracle 11g offers more simplified, automated and advanced memory management tools and features. The latter is equipped with better abilities to diagnose faults via inbuilt infrastructure. It is better designed to prevent, detect, manage, work upon, diagnose and help the resolution of critical database errors and reduced database performance issues.

Oracle 11g offers invisible indexes, table partitioning, virtual columns and the capability of redefining tables with materialized view logs even while the users are online. One important distinction between these two versions of Oracle lies in the new security features available in 11g. Oracle 11g offers much better password-specific authentication that contains mixed case passwords, enhancements pertaining to data pump encryption/compression, and encryption on the tablespace-levels. In Oracle 11g, the Standard Edition (SE), Enterprise Edition (EE), Express Edition (EX), Standard Edition One (SE1), Oracle Database Lite, etc. are available for use with mobile devices.

Overall, 11g serves as a good upgrade in comparison to10g and boasts of many positive enhancements related to evolving technology. In Oracle 11g, technical documentation is better than that available in 10g. This is significantly beneficial for DBAs who are quite dependent on the same on a daily basis.

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 Database 12c was released by Oracle 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 applications without necessitating any changes. The consolidation tools of Oracle 12c are very helpful for cloud readiness. In addition, 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 feature of pluggable databases backed by data consolidation tools. This feature has led to major changes in database architecture with the help of Container Databases that are referred to as CBDs and Pluggable Databases (PDB). The Container Database own the process and memory. The PDB take care of user data while the metadata is held by the container. 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 offset 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 so as to lead to more effective 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 functions have been greatly enhanced in Oracle 12C. Now, PL/SQL procedures and functions are capable of being added along with views in line 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 very helpful for DBAs. Just as online migration 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.

Differences between Oracle 11g and Oracle 12c

Basis of differentiation

Oracle 11G

Oracle 12C

Indexing

It is possible to create just a single index on any one table column. Additionally, the invisible index tool can be used in Oracle 11g.

 

Multiple indexes may be created on the same column. Despite the creation of more than one index, only one column is capable of being used at any given point of time.

 

New features and functions

It does not consider the performance tuning of data in the given tables. LISTAGG and Nth_value are some new features and functions in Oracle 11g.

approx_count_distinct () is a new feature in 12c; it offers an approximation of count distinct aggregation.

Caching

The In-memory parallel query statement does not work to perfection with multiple scans contended for the cache memory in Oracle 11g. Additionally, it is not possible to implement Full Database caching in this version of Oracle.

This new version of Oracle’s cache mechanism, referred to as big table cache, improves upon the performance metric for a full table scan. The automatic big table caching serves to be a new concept in Oracle 12C. Big table cache provides significant performance

benefits in terms of workloads that were earlier limited by I/O response time or throughput.

 

In-Memory table and aggregation

In-memory tables are used for full table scans and are supported by Oracle 11g. The table has to be added to the memory so that fast data can be accessed easily by the users. The In-memory aggregation concept is not supported by this version of Oracle.

 

In memory aggregation concept is supported by Oracle 12c, it is extremely useful for star queries.

Table partitions and subpartitions movement

Complex, time-consuming procedural logic has to be written by users for moving partitions/ subpartitions from one tablespace to another.

The online and offline methods of migrating table partitions and subpartitions can be easily implemented in oracle 12c.

Invisible columns

Invisible indexes, virtual columns, etc. are some effective and useful enhancements introduced in Oracle 11g as new features. The invisible column feature is not present in Oracle 11g.

Invisible columns are capable of being defined in a table in Oracle 12c. The invisible columns, after being defined, will not be available through generic query. They will be referred to through to SQL statements or as conditions in SQL statements.

Advantage of 12c over 10g and 11g Versions

Here are some important 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 the purpose of 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 pertaining to 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 & Pluggable Database: Oracle’s 12c contains a new feature that enables simpler consolidation of more than one database. It also enables 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/write activities that enable DBAs to conveniently identify the data categorized as hot/warm/cold in relation to access frequency. Additionally, smart compression, storage tiering and other related features automatically compress and arrange data on the basis of its activity and age. This is applicable 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 an 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 very easy to implement as they are woven into database kernels and are transparent to applications.

Conclusion

In case you have been thinking of 12c upgrade then it is indeed time to go through its functions, tools and features in greater details. We have tried our best to give you a closer look at why it is important 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.


Comments

  • Nitesh Singh
    07 Oct 2014 11:51 am
    • 10g and 11g is known as grid computing thats why "g" and 10 and 11 are the version.
    • Now 12c which is known to be as next level of db which is cloud computing thats why the letter "c".
    • Now rest the grid and cloud computing difference can be searched on google or wikipedia.
  • 07 Oct 2014 12:11 pm

    G grid computing already you are aware of in which ASM RAC are part of grid. 

    Now 12 c cloud you can unplug your database from on premise and plug it to cloud at anytym without any pain. you can create your 254 pluggable database under 1 container database in 12c in which it gives more sophisticated way to maintain your enterprise application DB. 

    For eg your hrms is running in peoplesoft and finance in JDE ...now you can isolate both the db in one single oracke db in a 12c.

  • 31 Jul 2017 9:56 pm

    I would add colum store in 12c and multi Databases also

    Virtuell columns in 11 und exadata in 11g

     

     

Related Articles

0.0141 seconds.