In the previous article I discussed some of the interesting new features in Oracle 12c. This continues on from that post.

Information Lifecycle Management 

Information Lifecycle Management (ILM) manages how data is stored over the life of the data. Generally speaking, data is accessed more frequently shortly after it’s inserted into the database. As the data gets older, it is accessed less frequently. After 12 months it’s quite common for data not to be access at all but needs to be available for archiving purposes, or for infrequent reporting

ILM can be used to manage and move the data from various tiers of storage to ensure that data is being stored appropriately. It makes no sense to have data only accessed for archival purposes to be stored on the top tier and most expensive disk (ie Flash, SSD).  Nor would you have data that is updated every day sitting on cheaper, but slower disk (SATA, NL-SAS) if other options are available.

As an example:

• If data had not need modified for a week, ILM can move the data from a partition in a tablespace on SSD to a partition on regular SAS disk.
• If the data has not been access for 30 days, this data can then be compressed.
• If the data has not been access for 6 months this data could be moved to a partition on SATA disk and then compressed as much as possible.

ILM monitors data in a table, extent or even a row and different polices can be applied. You can even have data automatically deleted from the database after a defined period of time.

Auditing

Oracle Auditing has been tightened up a lot. Previously anyone with sysdba privileges could remove records from the aud$ tables. This is now no longer the case. The audit information is read only and cannot be removed. Using the DBMS_AUDIT_MGMT package you can purge old audit information either manually or automatically. Anytime this package is used, that data is audited so you are able to tell when this audit data is removed.

The audit tables have also been consolidated down into one view to make finding information significantly easier.

RMAN, backups and duplication changes

There has been quite a few changes to backups using RMAN 12c. There is now a sysbackup group so you can setup an account that is able to backup the database, but this is all it is able to do.

You would connect to the database instance like so:

rman target “dave@orcl as SYSBACKUP”

This saves you granting full access to the database for a service account that is only used for running backup scripts.

Another excellent additional is the ability to run any SQL command. Previously to add SQL to an RMAN script you would have to prefix it with:

RMAN> SQL “Alter tablespace TEMP …”

You no longer need to add the SQL prefix, in fact you can treat an RMAN session as basically the same as an sqlplus session.

The Duplicate command has changed significantly, particularly when using the “from active database” syntax. Previously you would connect to the production instance and push the data to the auxiliary instance. Now you can just connect to the auxiliary instance. Another significant change is that you now use an RMAN backup to source your data instead of doing an image copy. This will cut down on the amount of data that has to be copied from one server to another.

You also have the ability to compress the backup during the duplicate.

Another good feature is the NOOPEN parameter. This doesn’t open the database at the end of the duplicate process, which is very handy for setting up standby databases or you need to modify settings before opening the database.

RMAN can now take a single database file and break this up into multiple pieces. This is useful when you are dealing with large single datafiles which are more common in Data Warehouse type environments where you are dealing with Terabytes or Petabytes of data. Backing up a single datafile into a single backup piece when dealing which such large data volumes can be a problem.

RMAN can now utilize file system snapshots also.

Online operations

Most operations that used to require downtime now do not need this. For example moving a datafile from one location to a new location used to need downtime on the database. Previously, you took the database or tablespace offline, physically moved the file, ran a few ”alter database” commands to update the data dictionary, then brought the database or tablespace online.

Now you can do this all with one command and in the background Oracle takes care of it all. Essentially it stops writes to the file while the move is happening and keeps track of all the changes, then replays the change upon the completion of the move command.

The online operations can happen to almost all objects with 12c, including tables, indexes and datafiles. This has been a feature that has been lacking for a long time and I’m glad is now available.

SQL syntax changes

There has been a few changes but the key one I noticed was the row limiting clause. Previously, if you wanted to look at the top 10 records you would have to do a where clause with ROWNUM. Now you can add a ‘FETCH FIRST n Rows’ clause. This gets applied after all your order by and other selection criteria have been parsed and applied to the SQL.

If you wanted to look at the rows 10 to 20 previously that was quite tricky. Now you can use the OFFSET N ROWS FETCH NEXT N ROWS syntax.

DBconsole

Oracle’s DBConsole has been removed from 12c and replaced with Oracle Cloud Control Express. It still run as a web application on the Oracle database server.

The functionality availability in Cloud Control Express is limited. In fact it’s so limited I would not use it myself.  It looks to me that oracle now want you to use the full version of Cloud Control. As this is a non-cost option from Oracle I would consider using this as an alternative as there is significantly more functionality available, even without the management packs installed.