Oracle Database New Features

In Database options, DBA, Oracle database on July 23, 2014 at 14:25

Oracle Database was released yesterday, July 22nd 2014.

I found no issues whatsoever with downloading, installing the software and creating a container database plus enabling the in-memory option.


Here are few useful links:

1. Oracle Database Software Download
2. Oracle 12cR1 Documentation
3. MOS 1905806.1 about
4. Oracle Database Blog: is available!!!

When you setup the IM option, note the inmemory area specified in the output below:

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  511707888 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5455872 bytes
In-Memory Area		  268435456 bytes
Database mounted.
Database opened.
SQL> show parameter inmemory

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default 	     string
inmemory_force			     string			       DEFAULT
inmemory_max_populate_servers	     integer			       1
inmemory_query			     string			       ENABLE
inmemory_size			     big integer		       256M
inmemory_trickle_repopulate_servers_ integer			       1
optimizer_inmemory_aware	     boolean			       TRUE

Let me show you how one can see the incredible speed of the inmemory option:

SQL> alter table SALES inmemory;

Table altered.

SQL> select max(price) most_expensive_order from sales;


Elapsed: 00:00:02.50

SQL> alter session set inmemory_query="DISABLE";

Session altered.

Elapsed: 00:00:00.03

SQL> select max(price) most_expensive_order from sales;


Elapsed: 00:01:25.51

Check the following new commands and views related to the in-memory option:

SQL> alter table SALES inmemory memcompress for capacity high;

Table altered.

SQL> alter table SALES no inmemory (client);

Table altered.

SQL> select segment_name, inmemory_size, inmemory_compression, bytes/inmemory_size comp_ratio from v$im_segments;

-------------------- ------------- ------------------------------ ----------
SALES			  24969216 FOR CAPACITY HIGH		  11.6325459

SQL> select table_name, cache, inmemory_compression comp, inmemory_priority priority, inmemory_distribute RAC from dba_tables where table_name = 'SALES';

------------ ---------- -------------------- ---------- ----------

SQL> select view_name from dba_views where view_name like 'V_$IM%';


10 rows selected.

Some of the most interesting new features are:

Oracle Database In-Memory
In-Memory Aggregation and In-Memory Column Store
Oracle Big Data SQL
Advanced Index Compression
Automatic Big Table Caching
Zone Maps for full table access
New and optimized SQL function, APPROX_COUNT_DISTINCT()
Full Database Caching
Rapid Home Provisioning based on gold images stored in a catalog of pre-created homes
New database parameter: DBFIPS_140


ORAchk replaces EXAchks and RACcheck

In Database tuning, DBA, Oracle utilities on June 21, 2014 at 12:48

The new tool from Oracle ORAchk includes EXAchks and RACcheck functionality and replaces the older tools. It is no longer just a database tool but also runs health check on E-Business Suite Financials, Enterprise Manager Cloud Control repository, GoldenGate and Sun Systems.


Going memory lane, have a look at how Statspack compares with BSTAT/ESTAT. Well, those days are long over. DBAs used to have their own scripts, ran their own healthchecks but some special scripts can still shed some more light on the system health.

One such example is Tanel Poder’s Exadata Snapper.

For details on the ORAchk tool, how to download it, what’s new in ORAchk, read MOS Note 1268927.2. If you do not have access to MOS, here are 2 blog posts that describe the tool quite well:

1. ORAchk Health Checks for the Oracle Stack (including Solaris) by Gerry Haskins

2. ORAchk Health Checks For The Oracle Stack by Levi Pereira

Here is how System Health Score is calculated:


I would recommend glancing first at the ORAchk Known Issues before starting using the tool: MOS 1509746.1

Note that ORAchk’s requirements are as follows:

Supported Platforms:
Linux x86-64* (Enterprise Linux, RedHat and SuSE 9, SuSE 10 and SuSE 11)
Oracle Solaris SPARC (Solaris 10 and 11)
Oracle Solaris x86-64 (Solaris 10 and 11)
AIX **

* 32-bit platforms not supported, no planned support for Linux Itanium
**Requires BASH Shell 3.2 or higher to be installed

Supported Oracle Releases:

Still, no Windows…

And, do not aim at a 100% score, you will not get it easily. Points get deducted if you have more than 1 instance on the server:

WARNING OS: Multiple RDBMS instances discovered, observe database consolidation best practices

According to the tool, there is a “risk for shared resource contention leading for instability, performance degradation or scalability problems”. Then it says: “if the database consolidation best practices have already been reviewed and observed then this message can be ignored”.

Not to mention that you might run the report twice in a row and get a different score.

But there is more: ORAchk Collection Manager is a companion application to ORAchk, RACcheck and Exachk. When having lots of systems, auditing them with ORAchk might be a tedious piece of work. ORAchk has long had the ability to upload the results of its audit checks into a database automatically at run time.

However, it was up to us to create a custom front-end to that data for reporting purposes and trend analysis. Now, with ORAchk Collection Manager, Oracle provides this Application Express application to be used as a dashboard in which they can track their ORAchk, RACcheck and Exachk collection data in one easy to use interface.

Download the ORAchk Collection Manager and go through the ORAchk Collection Manager Users Guide for more details.

Cross-Platform Transportable Database and Oracle Engineered Systems

In Consolidation, DBA, Exadata, Oracle database, RMAN, SuperCluster on May 25, 2014 at 15:25

Cross-platform transportable database is not the same thing as transportable tablespace. When performing x-platform transportable database we copy the entire database, including the SYSTEM and SYSAUX tablespaces from one platform to another. The usual containment checks are no longer needed and because the SYSTEM tablespace is also being copied, no metadata datapump export/import step is required. But cross-platform transportable database can only be performed between platforms that have the same endian format.


When consolidating a large number of databases onto Exadata or SuperCluster, the work has to be automated as much as possible. When the source and the target platform share the same endian (see the 2 endian group below), then the best option is to use the transportable database method.

Otherwise, there are 3 options:

1. Create a new database on the EXA platform manually, and transport the needed tablespaces from the source database using the Cross Platform Transportable Tablespace method.
2. If downtime during the upgrade is an issue, Cross Platform Incremental Backup is an extremely good option. You can Refresh Standby Databases using the method.
3. Use the new Full Transportable Export/Import method. Note that full transportable export is available starting while full transportable import is available starting


DBAs should consider the following points before the migrations:

- Some parts of the database cannot be transported directly: redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process (alter database backup controlfile to trace resetlogs;), and an OPEN RESETLOGS is performed once the new database is created.

- BFILEs are not transported. RMAN provides a list of objects using the BFILE datatype in the output for the CONVERT DATABASE command, but users must copy the BFILEs themselves and fix their locations on the destination database. Execute DBMS_TDB.CHECK_EXTERNAL in order to identify any external tables, directories or BFILEs.

- Tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run. After opening with resetlogs, run alter tablespace TEMP add tempfile…

- External tables and directories are not transported. RMAN provides a list of affected objects as part of the output of the CONVERT DATABASE command, but users must redefine these on the destination platform. Run select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES and ensure that the same paths are available on the target system.

- Password files are not transported. If a password file was used with the source database, the output of CONVERT DATABASE includes a list of all usernames and their associated privileges. Create a new password file on the destination database using this information.

- When all is done, run UTLIRP and UTLRP.

For SAP users, check Note 105047 – Support for Oracle functions in the SAP environment:

86. Transportable Tablespaces: the implicit use is supported in the BRSPACE function “-f dbcreate” (Note748434) and the “Tablespace Point in Time Recovery” function of BRRECOVER. Explicit use as part of system copying is tolerated.

87. Transportable database: Can be used (Note 1367451).

And finally, here is some more information on how to migrate your databases to Oracle Engineered Systems:

1. Exadata MAA Best Practices Series Session 6: Migrating to Exadata

2. Exadata MAA Best Practices Migrating Oracle Databases

3. Oracle’s SPARC M5-32 and SPARC M6-32 Servers: Domaining Best Practices



Get every new post delivered to your Inbox.

Join 279 other followers