Dontcheff

Strong Oracle Database 12c Wiping Away the Competition

In DBA, Oracle database, SAP HANA on November 23, 2014 at 12:24

49% of Oracle’s total software revenues in FY14 came from database software. As Forbes mentioned in their article Strong Database And Application Software Sales Could Lift Oracle Higher, the company commands a market share of nearly 48% in the well established and mature Relational Database Management System market.

Forbes_Strong_Databases

According to Forbes “Oracle has been aggressive in marketing its newest database offering, Oracle 12c, against strong competition from alternative RDBMS providers, as well as HANA from its rival SAP. In-memory platforms are increasingly being deployed to facilitate faster transactions for applications where the cost of the extra memory can be justified, including the rapidly expanding online commerce market across the globe.”

Although SAP HANA belongs to the 14% other segment, it should not be at all ignored or underestimated. But curb your enthusiasm.

RDBMS_market

In HANA there are 2 types of tables. Row based and Column based. It is defined upon table creation and can be changed vice-versa afterwards with the ALTER TABLE SALES ALTER TYPE ROW; command. Because computer memory is structured linearly, there are two options for the sequences of cell values stored in contiguous memory locations:

Row Storage – It stores table records in a sequence of rows.
Column Storage – It stores table records in a sequence of columns i.e. the entries of a column is stored in contiguous memory locations.

An Oracle DBA might be slightly amused or bewildered when going though the instruction on how to Create Table in SAP HANA. Copy the SQL statement below in SQL editor, Press F8, Right click on the table…

HANA_table

SAP HANA tracks memory from the perspective of the host. The most important concepts are as follows:

- Physical memory: The amount of (system) physical memory available on the host.
– SAP HANA Allocated memory: The memory pool reserved by SAP HANA from the operating system.
– SAP HANA Used memory: The amount of memory from this pool that is actually used by the SAP HANA database.

On most SAP HANA hosts, the DRAM ranges from 256 gigabytes to 2 terabytes. To find the global allocation limit of the database, run below SQL query:

select HOST, round(ALLOCATION_LIMIT/1024/1024/1024,2) 
as "Allocation Limit GB" from PUBLIC.M_HOST_RESOURCE_UTILIZATION

In order to understand how to find out different memory consumption in SAP HANA, check SAP HANA Memory Usage Explained.

Oracle Database 12c In-Memory (12.1.0.2) is more complex and sophisticated:

create_table_in_memory

The Oracle DBA can:

- Define if the table is row format only or both: then the data is kept synchronized on disk and in RAM
– Exclude certain partitions from the table: a multi-million row table with old unused data might be a memory consumption killer
– Exclude certain columns from the table and only store in memory the analytical columns: look at the NO INMEMORY syntax above
– Define what type of memory compression is to be used for the columns
– Specify if the compression to be more suited for DML or for SELECT
– Define the priority of how fast to load the memory area: imagine thousands of SAP tables being loaded into memory after instance startup
– Define how to distribute and replicate the data in RAC: do you really want same data replicated in all RAC instances?
– Create an In-Memory tablespace so afterwards all tables created in that tablespace will be IM tables
– Specify how many copies of each In-Memory Compression Unit (IMCU) of the tables in the IM column store will be spread across all the Oracle RAC instances

All of the above is far from reality in SAP HANA. Did you know that in SAP HANA, a non-partitioned table cannot store more than 2 billion rows? Check these SAP HANA Restrictions.

Every 6 months there is a major release of HANA, called a Service Pack. Service Pack 9, or SPS09, has several new innovations. There are SAP HANA user defined functions (UDFs) for Hadoop which enable SAP HANA to access the map reduce jobs within Hadoop directly.

For the Exadata and SuperCluster DBAs: the duplicate-clause is only applicable if you are using Oracle Real Application Clusters on an Engineered System. Otherwise, the duplicate-clause is ignored and there is only one copy of each IMCU in memory.

CatchMeIfYouCan

Oracle Zero Data Loss Recovery Appliance

In DBA, Oracle database, Oracle Engineered Systems, RMAN on October 30, 2014 at 17:57

During the early versions of Oracle, Larry Ellison was asked once if anyone ever asked for their money back. His answer was: “No, but they used to ask us for their DATA back.”

0DLRA

Backup and Recovery go hand-in-hand, right? It is just that before recovery, we need to restore the data under the solid assumption that there is something to restore.

From the 10 member family of Oracle Engineered Systems, the ZDLRA is in my opinion the most critical. Let me explain why including some useful information for system DBAs.

OES10members

Granted we do not want to find solutions to problems we don’t have, let us look at what DBAs’ reality is today. Most problems listed below are not longer just DBA’s weekly burdens, they affect global business directly:

1. Data growth and long backup windows: system is slow and virtually unusable because of a long running backup, what are the options now: offload backups to an ADG site, use BCV splits, do not take backups at all?

2. Infinite availability: looks like people do not talk any more of four 9s or five 9s, more and more often I hear words like zero down time, infinite availability, continuous availability. An ex-colleague and friend of mine used to say: “Zero downtime exists only in power point presentations”.

3. Lack of backup validation and end-to-end visibility: according to the Oracle documentation, the main purpose of RMAN validation is to check for corrupt blocks and missing files. You can also use RMAN to determine whether backups can be restored. How often do we do that? Really!

4. Data loss and data corruption: one can write a book on this subject. I still sadly witness databases being backed up using storage replication. That is indeed a very fast way to backup corrupted data blocks!

Recovery Appliance provides the following benefits:

1. Elimination of Data Loss
2. Minimal Backup Overhead
3. Improved End-to-End Data Protection Visibility
4. Cloud-Scale Protection

Here is a sample picture of the Recovery Appliance Architecture (it is worth reading the details behind the link):

ZDLRA_ARCH

Core DBAs might be interested in the new DBMS_RA package. A DBA can use the DBMS_RA subprograms to perform all Recovery Appliance administration functions. Check the DBMS_RA Package Reference.

There 27 new views related to the ZDLRA. Check the Recovery Appliance View Reference for more details.

The database account RASYS owns the Recovery Appliance schema, which includes the RMAN recovery catalog and the DBMS_RA PL/SQL package. The RASYS user name is fixed and cannot be changed. RASYS does not have the privileges required to create database user accounts.

DBAs should know that the Zero Data Loss Recovery Appliance Backup Module is an Oracle-supplied SBT library that RMAN uses to transfer backup data over the network to the Recovery Appliance. An SBT library transfers data to and from a backup device type, either a tape device or Recovery Appliance. RMAN performs all backups to the Recovery Appliance, and all restores of complete backup sets, by means of this module.

The Recovery Appliance Backup Module must be installed in the following two locations: (1) in the ORACLE_HOME of every protected database that sends backups to a Recovery Appliance for Recovery Appliance replication environments, and (2) on every upstream Recovery Appliance that sends backups to downstream Recovery Appliances.

Another important new concept for DBAs is the protection policy one: it is a named collection of properties that you can assign to multiple protected databases. A default installation of Recovery Appliance has these 4 protection policies.

Finally, an important questions: which databases are supported? The following Oracle Database releases are:

    – Oracle Database releases 10.2 through 11.2.0.3 require manual HTTPS configuration.
    – Oracle Database releases 11.2.0.4 and 12.x are fully supported.

Four good links to start from:

1. Zero Data Loss Recovery Appliance Administrator’s Guide Release 12.1
2. Reinventing Database Protection
3. Data Sheet – Oracle Zero Data Loss Recovery Appliance
4. A Technical Guide to Oracle’s Recovery Appliance

ZDLRA_CNN

Oracle Database 12.1.0.2 New Features

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

Oracle Database 12.1.0.2 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.

OEMDBEXpress12102b

Here are few useful links:

1. Oracle Database 12.1.0.2 Software Download
2. Oracle 12cR1 Documentation
3. MOS 1905806.1 about 12.1.0.2
4. Oracle Database Blog: 12.1.0.2 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
percent
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;

MOST_EXPENSIVE_ORDER
--------------------
	       91978

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;

MOST_EXPENSIVE_ORDER
--------------------
	       91978

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;

SEGMENT_NAME	     INMEMORY_SIZE INMEMORY_COMPRESSION 	  COMP_RATIO
-------------------- ------------- ------------------------------ ----------
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';

TABLE_NAME   CACHE	COMP		     PRIORITY	RAC
------------ ---------- -------------------- ---------- ----------
SALES		 N	FOR CAPACITY HIGH    NONE	AUTO

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

VIEW_NAME
----------------------------------------
V_$IM_SEGMENTS_DETAIL
V_$IM_SEGMENTS
V_$IM_USER_SEGMENTS
V_$IM_TBS_EXT_MAP
V_$IM_SEG_EXT_MAP
V_$IM_HEADER
V_$IM_COL_CU
V_$IM_SMU_HEAD
V_$IM_SMU_CHUNK
V_$IM_COLUMN_LEVEL

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

OEMDBEXpress12102c

Follow

Get every new post delivered to your Inbox.

Join 288 other followers