Oracle Database 12c Enterprise Edition (12.2.0.1) is a next-generation database designed for the cloud. It offers more than 500 new features including a new multi tenant architecture that simplifies consolidating databases onto the cloud and enables customers to manage many databases as one without changing their applications. Customers can choose a wide range of Oracle Database Enterprise Edition options to deliver on business users’ performance, security, big data, cloud, and availability service-level expectations.

Oracle Database In-Memory (Database In-Memory) is a suite of features, first introduced in Oracle Database 12c Release 1 (12.1.0.2), that greatly improves performance for real-time analytics and mixed workloads. The In-Memory Column Store (IM column store) is the key feature of Database In-Memory.

• Challenges for Analytic Applications
  Traditionally, obtaining good performance for analytic queries meant satisfying several requirements.
• The Single-Format Approach
  Traditionally, relational databases store data in either row or columnar formats. Memory and disk store data in the same format.
• The Oracle Database In-Memory Solution
  The Oracle Database In-Memory (Database In-Memory) feature set includes the In-Memory Column Store (IM column store), advanced query optimizations, and availability solutions.
• Prerequisites for Database In-Memory
  The Oracle Database In-Memory option is required for all Database In-Memory No special hardware is required for an IM column store.
• Principal Tasks for Database In-Memory
  For queries to benefit from the IM column store, the only requiredtasks are sizing the IM column store, and specifying objects for population. Query optimization and availability features require additional configuration.
• Tools for the IM Column Store
  No special tools or utilities are required to manage the IM column store or other Database In-Memory Administrative tools such as SQL*Plus, SQL Developer, and Oracle Enterprise Manager (Enterprise Manager) are fully supported

Challenges for Analytic Applications

Traditionally, obtaining good performance for analytic queries meant satisfying several requirements.

In a typical data warehouse or mixed-use database, requirements include the following:

• You must understand user access patterns.
• You must provide good performance, which typically requires creating indexes, materialized views, and OLAP cubes.

For example, if you create 1 to 3 indexes for a table (1 primary key and 2 foreign key indexes) to provide good performance for an OLTP application, then you may need to create additional indexes to provide good performance for analytic queries.

Description

Meeting the preceding requirements creates manageability and performance problems. Additional access structures cause performance overhead because you must create, manage, and tune them. For example, inserting a single row into a table requires an update to all indexes on this table, which increases response time.

The demand for real-time analytics means that more analytic queries are being executed in a mixed-workload database. The traditional approach is not sustainable.

 The Single-Format Approach

Traditionally, relational databases store data in either row or columnar formats. Memory and disk store data in the same format.

An Oracle database stores rows contiguously in data blocks. For example, in a table with three rows, an Oracle data block stores the first row, and then the second row, and then the third row. Each row contains all column values for the row. Data stored in row format is optimized for transaction processing. For example, updating all columns in a small number of rows may modify only a small number of blocks.

To address the problems relating to analytic queries, some database vendors have introduced a columnar format. A columnar database stores selected column—not rows—contiguously. For example, in a large sales table, the sales IDs reside in one column, and sales regions reside in a different column.

Analytical workloads access few columns while scanning, but scan the entire data set. For this reason, the columnar format is the most efficient for analytics. Because columns are stored separately, an analytical query can access only required columns, and avoid reading inessential data. For example, a report on sales totals by region can rapidly process many rows while accessing only a few columns.

Database vendors typically force customers to choose between a columnar and row-based format. For example, if the data format is columnar, then the database stores data in columnar format both in memory and on disk. Gaining the advantages of one format means losing the advantages of the alternate format. Applications either achieve rapid analytics or rapid transactions, but not both. The performance problems for mixed-use databases are not solved by storing data in a single format.

The Oracle Database In-Memory Solution

The Oracle Database In-Memory (Database In-Memory) feature set includes the In-Memory Column Store (IM column store), advanced query optimizations, and availability solutions.

The Database In-Memory optimizations enable analytic queries to run orders of magnitude faster on data warehouses and mixed-use databases.

This section contains the following topics:

• What Is Database In-Memory?
  The Database In-Memoryfeature set includes the IM column store, advanced query optimizations, and availability solutions. These features combine to accelerate analytic queries by orders of magnitude without sacrificing OLTP performance or availability.
• Improved Performance for Analytic Queries
  The compressed columnar format enables faster scans, queries, joins, and aggregates.
• Improved Performance for Mixed Workloads
  Although OLTP applications do not benefit from accessing data in the IM column store, the dual-memory format can indirectly improve OLTP performance.
• High Availability Support
  The IM column store is fully integrated into Oracle Database. All High Availability features are supported.
• Ease of Adoption
  Database In-Memoryis simple to implement, and requires no application changes.

 What Is Database In-Memory?

The Database In-Memory feature set includes the IM column store, advanced query optimizations, and availability solutions. These features combine to accelerate analytic queries by orders of magnitude without sacrificing OLTP performance or availability.

This section contains the following topics:

• IM Column Store
  The IM column storemaintains copies of tables, partitions, and individual columns in a special compressed columnar format that is optimized for rapid
• Advanced Query Optimizations
  Database In-Memory includes several performance optimizations for analytic queries.
• High Availability Support
  Availability is the degree to which an application, service, or function is accessible on deman

Prerequisites for Database In-Memory

The Oracle Database In-Memory option is required for all Database In-Memory features. No special hardware is required for an IM column store.

Prerequisites include:

• The IM column store requires a minimum of 100 MB of memory. The store size is included in MEMORY_TARGET.
• For Oracle RAC databases, the DUPLICATEand DUPLICATE ALL options require Oracle Engineered Systems.

Principal Tasks for Database In-Memory

For queries to benefit from the IM column store, the only required tasks are sizing the IM column store, and specifying objects for population. Query optimization and availability features require additional configuration.

Principal Tasks for Configuring the IM Column Store

The following table lists the principal configuration tasks.

Principal Tasks for Optimizing In-Memory Queries

In-Memory query optimizations are not required for the IM column store to function. The following optimization tasks are optional.

 Tools for the IM Column Store

No special tools or utilities are required to manage the IM column store or other Database In-Memory features. Administrative tools such as SQL*Plus, SQL Developer, and Oracle Enterprise Manager (Enterprise Manager) are fully supported.

Following are the tools that have specific Database In-Memory feature support:

• In-Memory Advisor
  The In-Memory Advisoris a downloadable PL/SQL package that analyzes the analytical processing workload in your database.
• Cloud Control Pages for the IM Column Store
  Enterprise Manager Cloud Control (Cloud Control) provides the In-Memory Column Store Central Home page. This page gives a dashboard interface to the IM column store.
• Oracle Compression Advisor
  Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS The advisor uses the DBMS_COMPRESSIONinterface.
• Oracle Data Pump and the IM Column Store
  You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:yoption of the impdp command.

 Oracle 12c Architecture:

With 12c you might have always heard of multitenant architecture and Container & pluggable Database, so we’ll start with understanding these things. Also 12c configuration has following option:-

• Multitenant configuration A CDB consists of zero, one, or more PDBs. You need to license the Oracle Multitenant option.
• Single-tenant configuration Doesn’t require the licensed Oracle Multitenant option
• Non-CDB This is the same as the pre–Oracle 12 c database  architecture.

So it doesn’t mean that 12c is only about multitenant configuration, it can be configured as the same way as your beloved 11g.

Multitenant Architecture

For those of you who have worked with SQL Server, Sybase etc this architecture won’t be new. Basically till 11g we used to have 1 instance for 1 database (excluding RAC cases for simplicity), so even you have a very small application you need to have a separate instance for that database, separate instance means memory, process and everything (But then Oracle was designed to handle large & critical databases). So with the changing requirements Oracle has changed its architecture where you can have multiple databases within a single instance. To build a little perspective on CDB (CDB$ROOT) and PDB think of this single instance as CDB and multiple databases as PDB.

Now its time to understand what is CDB & PDB, how is memory allocated to these different PDB, how does CDB maintains PDBs, where’s REDO, where’s UNDO, etc.

CDB & PDB

• The Oracle supplied container is called the root container (CDB$ROOT) and consists of just Oracle metadata (and maybe a little bit of user data) and common users. Each CDB has one root.
• The seed container is named PDB$SEED, and there is one of these per CDB. The purpose of the seed container isn’t to store user data—it’s there for you as a template to create PDBs.
• The user container , which is actually called a pluggable database (or
  PDB), consists of user metadata and user data.

—Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database.

An Oracle database server consists of a database and at least one database instance, commonly referred to as simply an instance. Because an instance and a database are so closely connected, the term Oracle database is sometimes used to refer to both instance and database. In the strictest sense the terms have the following meanings:

• Database: A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.
• Database instance

Cognosys provides hardened images of Oracle  DB on all public cloud  AWS , Azure and Google Marketplace.

Click on the respective cloud provider tab for technical information.

Oracle 12.2 Cool New Features:

Partitioning – alter table to partitioned table

In 12.2 there is a single “alter table” command to transform a regular table into a partitioned one. The “alter table” includes the partition type and key clauses, and it simply alters the table. I didn’t manage to get an answer on how it is done behind the scenes, my guess is that this command creates a new partitioned table and moves all the data into it (similar to the “alter table … move” command).

Partitioning – create table for exchange

Until now, when we wanted to create a table to perform “exchange partition” we had to create the table ourselves. Usually it worked, but sometimes it didn’t. This was caused usually because the partitioned table had unused columns or something similar. In 12.2, a new “create table” syntax was introduced to allow us to create a table based on an existing partitioned table, and Oracle will take care of the structure so we won’t have any problems with the exchange.

Standby Database – In-Memory option

12c introduced the in-memory option, however it wasn’t available in active standby. Now it is available, allowing us to configure in-memory column store on the active standby side (with same or different memory size) for reports and analytic.

In-Memory – dynamic resizing

In 12c, when Oracle introduced the In-Memory option, the size of the In-Memory column store was static. If we wanted to change its size we needed to restart the database. In 12.2 we can increase the column store size dynamically. We can’t reduce the size dynamically, but who wanted to decrease memory sizes anyway, right?

In-Memory – fast start

Another improvement of the In-Memory option is that now Oracle saves the data stored in the In-Memory column store in a lob on the disk. This allows the database to populate the In-Memory cache much quicker when the database starts. Without this feature, the database needs to work very hard to read the data from the regular tables, reconstruct it, compress and more before storing it in the column store.

Security – online encryption

Encryption is important, but until now, had to be done offline for existing data. Oracle 12.2 allows online encryption and re-key operations. The command will effect a datafile, copying it to a new encrypted one, then will change the database to start using the new encrypted file, and everything will be done online.

Security – internal tablespace encryption

Now it is possible to encrypt internal tablespaces (system, sysaux, undo, etc) as well.

Security – DB vault simulation

DB vault is a great security tool, but it is quite a big project to implement it. When implementing DB vault on an existing application, there is always the risk that we missed things and the application will break. DB vault 12.2 can be enabled in simulation mode. While in simulation mode, nothing will break, instead, DB vault will log every command that should have been blocked. That way we can make sure that everything works and fix all the problems before the actual implementation.

Dictionary – long identifiers

You’ve probably heard about this one. It is so annoying that object and column names are limited to 30 bytes, right? Well, not anymore, 128 bytes is the new limit!

Non-PDB Support

In 12.2 the old non-PDB architecture still exists but is not supported anymore. The plan is to remove this architecture completely in the next major release. Without paying the extra money for this feature, we can still use this architecture, but with a single PDB. For additional PDBs, a specific license needs to be purchased.

PDB – resource management

One of the fears when using PDBs to consolidate different databases into the same instance, is that one or a few PDBs will consume all the resources and leave none to the others. In 12.2 there is a very simple command to limit IOPS per PDB (and I think they mentioned a similar thing for CPU and memory as well). To use that, simply execute the command: “alter system set max_iops=…” when connected to a specific PDB.

PDB – hot clone

The concept of cloning a PDB is great. However, in 12c, in order to clone a PDB it should be open in read-only mode, and this prevents us from using the feature on production environments. In 12.2, the clone is hot and done when the PDB is in read-write mode. It is even possible to keep updating the cloned PDB with changes as long as the cloned PDB itself is in read-only mode.

PDB – local undo

When using the PDB architecture, the PDBs share several resources, including the undo tablespace. In 12.2 we can set the environment so every PDB will contain its own undo tablespace. This is done by the “alter database local undo on” command.

PDB – flashback

Another great feature of PDB is that we can now flashback a single PDB without affecting all other PDBs in the same instance.

PDB – character set

And last feature in the PDB architecture, we can set a different character set per PDB. There is a requirement for that though, the root has to use AL32UTF8 (which is the super-set of all others, so it kinda make sense, it is also the default characterset now).

Index Monitoring

Index monitoring before 12.2 was quite simple and had many problems. In 12.2, Oracle improved that a lot (finally!). Index monitoring is now enabled by default, tracks the usage at execution level (as opposed to parse level) and keeps a lot of information, such as number of times the index was used, last usage time and much more. All the information is recorded in the DBA_INDEX_USAGE data dictionary view.

Materialized Views – real time refresh

Mv real time refresh allows queries against the base table to be rewritten and use the MV, even if it is not up-to-date, while retrieving the up-to-date data. Oracle goes to the MV log, gets the data from it and merges it into the MV to return the correct results. This is done at the statement level, so it does not update the MV itself. If we query the MV directly, we can also use the FRESH_MV hint to do the same and get the up-to-date data.

External Tables – partitions

External tables are now can be partitioned by using a set of files (instead of the single one), each representing a table partition.

Features new to Oracle Database 12c Release 2 (12.2).

• Application Development
• Availability
• Big Data and Data Warehousing
• Compression and Archiving
• Database Lifecycle Management
• Database Overall
• Diagnosability
• Exadata
• Manageability
• Miscellaneous
• Performance
• Public Cloud
• RAC and Grid
• Security
• Spatial and Graph
• Windows

 Availability

• Data Guard
• End-to-End Application Availability
• General
• Logical Replication
• Online Operations
• Recovery Server and RMAN Improvements
• Sharding
• Simplifying Upgrades

 Application Development

• Database Development Productivity Tools Enhancements
• General
• Globalization Support Enhancements
• JSON Support
• Oracle SQL and PL/SQL Improvements
• Reducing Costs and Complexities of Migration to Oracle Database
• Support for the .NET and MS Development Community
• Support for the Java Development Community
• XML

Database Development Productivity Tools Enhancements

• Application Express 5.0: Packaged Applications
• Application Express 5.0: Page Designer
• Application Express 5.0: Universal Theme
• Application Express 5.0: User Interface Enhancements
• Application Express 5.0: Improved Application Builder Design
• Application Express 5.0: Interactive Reporting
• Application Express 5.0: Mobile Enhancements
• Application Express 5.0: Modal Dialog Pages
• Application Express 5.0: All New Calendar

Application Express 5.0: Packaged Applications

Oracle Application Express release 5.0 includes a broad collection of point solutions called packaged applications. These are Application Express applications that you can use out-of-the-box and that are supported by Oracle Database. Examples include Project Tracking, Survey Builder, Meeting Minutes, and Group Calendar. There are 19 productivity applications in all. Additionally, there are 16 sample applications that are used to showcase the features of Oracle Application Express, from Sample Charts to Sample Data Loading. Release 5.0 even includes a sample application that demonstrates the powerful spatial capabilities that are present in every Oracle Database. Oracle Application Express release 5.0 packaged applications are very popular as point solutions and learning examples.

 Application Express 5.0: Page Designer

Page Designer is a modern, intuitive, and exceedingly powerful browser-based Integrated Development Environment (IDE). As a completely new IDE that is designed to greatly improve developer productivity, Page Designer enables you to very quickly develop and maintain your Application Express applications. Page Designer features a better visual representation of your application pages and provides an entirely new way of quickly developing pages by using intuitive drag and drop. The enhanced code editor provides SQL and PL/SQL validation with inline errors, auto completion, syntax highlighting, search and replace with regex support, complete with undo and redo support. Oracle Application Express Page Designer greatly improves developer productivity, provides a cohesive user experience, better visual representation, intuitive drag and drop, and an enhanced code editor.

Application Express 5.0: Universal Theme

Universal Theme is a new user interface for your applications for Oracle Application Express release 5.0. It is a simpler, yet more capable theme that eliminates excessive templates and enables customization using the built-in Theme Roller and Template Options. The Universal Theme enables developers to build modern, responsive, and sophisticated applications without requiring expert knowledge of HTML, Cascading Style Sheet (CSS), or JavaScript.Oracle Application Express Universal Theme provides a number of new capabilities including Theme Roller, Template Options, responsive design, and accessibility.

Application Express 5.0: User Interface Enhancements

With the development of Universal Theme, there are also several enhancements to the handling of themes and templates. Oracle Application Express release 5.0 includes features such as Theme Subscriptions, Template Options, and Theme Styles. These features give you more granular control over your templates and the HTML that the Application Express engine produces. It is easier to fully control your application user interface. Oracle Application Express release 5.0 enables developers to easily build modern, responsive, and beautiful applications out-of-the-box.

Application Express 5.0: Improved Application Builder Design

Oracle Application Express release 5.0 introduces a new interface that focuses on improving the user experience through simplicity and the removal of clutter. The new design uses a new color palette, carefully crafted icons, and improved menus and navigation. The new design also provides improved accessibility and keyboard support, more intuitive page layouts, and many other enhancements. Oracle Application Express release 5.0 Application Builder is more intuitive and productive for developers.

Application Express 5.0: Interactive Reporting

Interactive reports are completely rebuilt in Oracle Application Express release 5.0 to enhance both developer and end-user capabilities. New capabilities include the ability to define multiple reports on a single page, column pivot, fixed headers, and modernized actions. You can also restyle interactive report regions using Cascading Style Sheets (CSS) in a similar manner to other regions within Oracle Application Express.Oracle Application Express release 5.0 interactive reports enhances both developer and end-user capabilities.

Application Express 5.0: Mobile Enhancements

You can now build reports that display all of your data on any mobile device by using reflow table or column toggle. Reflow table wraps each column or changes the display to allow multiple lines on very small screens. Column toggle enables you to specify the most important columns to view and those columns that should be hidden, as necessary, on smaller screens. Panels are now incorporated into mobile applications and are used to display navigation menus. Oracle Application Express release 5.0 mobile improvements enable the development of improved mobile-first applications.

Application Express 5.0: Modal Dialog Pages

Now you can easily define modal and non-modal pages, complete with the ability to use standard page processes. You no longer need to manually edit a page using JavaScript. Instead, set the display type and the appropriate template and let Oracle Application Express take care of the rest. Oracle Application Express release 5.0 modal dialog pages make it easy for developers to display pages modally, as opposed to writing significant amounts of custom code.

Major Features of Oracle DB

• Oracle Move is cost effective. The same flexibility that lets you directly migrate your Oracle Database to the Oracle Cloud is applied to finding the most cost effective solution for the purpose and duration of the migration.
• Oracle Move is highly available and scalable. The tight integration of all migration tools with the Oracle Database lets you maintain control and gain better efficiency when moving your databases into the Oracle Cloud.
• Oracle Move offers simplicity and efficiency. Oracle automated tools make it seamless to move your database to the Oracle Cloud with virtually no downtime. Using the same technology and standards on-premises and in the Oracle Cloud, you can facilitate the same products and skills to manage your cloud-based Oracle Databases as you would on any other platform.
• Oracle Move is flexible. You can directly migrate your Oracle Database to the Oracle Cloud from various source databases into different target cloud deployments depending on your requirements and business needs. Oracle Move provides a well-defined set of tools, giving you the flexibility to choose the method that best applies to your needs

Installation Instructions For Linux

Installation Instructions 

Note: How to find PublicDNS in AWS

Step 1) SSH Connection: To connect to the deployed instance, Please follow Instructions to Connect to Linux instance on AWS Cloud

1) Download Putty.

2) Connect to the virtual machine using SSH key Refer this link:

• Hostname: PublicDNS  / IP of machine
• Port : 22

Step 2) Database Details:

Sample Database: Database cdb1 and pdb pdb1 has been created.
Credentials: The below passwords for oracle access have been set to Passw@rd123

1. sysPassword
2. systemPassword
3. pdbAdminPassword
   Note : Please change the password after first login.

To use the oracle system user

1. SSH to the instance please
2. Switch context to root with “sudo -i”
3. Then do su – oracle

Please set oracle user password as required.

Login with the command “sqlplus / as sysdba” as oracle user.

Step 3) Other Information:

Default ports: For Linux SSH Port – 22

Configure custom inbound and outbound rules using this link

Remote Connections to Oracle EC2 instance:

1. Configure the firewall – Either allow 1521 port in iptables or disable the firewall
   • To allow use the command iptables -I INPUT -s 192.168.1.0/24 -p tcp –dport 1521 -j ACCEPT (192.168.1.0/24 will be your VPC ip ranges)
   • To disable use service iptables status
     service iptables stop
     chkconfig iptables off
     service iptables status

     With either of the above methods access to the 1521 port from VLAN / same vpc subnet is allowed unless any other security group is blocking the access

2. For allowing public/remote internet access you need to create entry in listener.ora file. When opening 1521 port on on inbound rule for security please ensure that the port is allowed only to restricted IP ranges.
3. An example is as given below

Oracle DataBase