Dontcheff

Reading Data in Oracle Database 12c

In Cloud, Consolidation, Database options, DBA, Security and auditing, SQL on December 1, 2014 at 18:02

1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?

2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?

The aim of this blog post is to shed some light on these new concepts.

lock_read

1. New READ privilege.

Until Oracle 12.1.0.2. the SELECT object privilege allowed users to perform the following two operations in addition to just reading data from the SALES table:

LOCK TABLE sales IN EXCLUSIVE MODE;
SELECT ... FROM sales FOR UPDATE;

These 2 commands enabled the users to lock the rows of the SALES table.

The READ object privilege does not provide these additional privileges. For better security, grant users the READ object privilege if you want to restrict them to performing queries only.

In addition to the READ object privilege, you can grant users the READ ANY TABLE privilege to enable them to query any table in the database.

When a user who has been granted the READ object privilege wants to perform a query, the user still must use the SELECT statement. There is no accompanying READ SQL statement for the READ object privilege.

The GRANT ALL PRIVILEGES TO user SQL statement includes the READ ANY TABLE system privilege. The GRANT ALL PRIVILEGES ON object TO user statement includes the READ object privilege.

If you want the user only to be able to query tables, views, materialized views, or synonyms, then grant the READ object privilege. For example:

GRANT READ ON SALES TO julian;

CDB_PDB

2. Querying a table owned by a common user across all PDBs.

Consider the following scenario:

- The container database has several pluggable databases, i.e., it has a separate PDB for each different office location of the company.
– Each PDB has a SALES table that tracks the sales of the office, i.e., the SALES table in each PDB contains different sales information.
– The root container also has an empty SALES table.
– The SALES table in each container is owned by the same common user.

To run a query that returns all of the sales across the company connect to each PDB as a common user, and create a view with the following statement:

CREATE OR REPLACE VIEW sales AS SELECT * FROM sales;

The common user that owns the view must be the same common user that owns the sales table in the root. After you run this statement in each PDB, the common user has a view named sales in each PDB.

With the root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all of the sales in the sales table in all PDBs:

SELECT * FROM CONTAINERS(sales);

You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:

SELECT * FROM CONTAINERS(sales) WHERE CON_ID IN (3,4);

3. Delegate.

Something else: staring 12.1.0.2, when granting a role to a user, you can specify the WITH DELEGATE OPTION clause. Then the grantee can do the following two things:

A) Grant the role to a program unit in the grantee’s schema
B) Revoke the role from a program unit in the grantee’s schema

delegate

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

Follow

Get every new post delivered to your Inbox.

Join 297 other followers