Wednesday, 28 December 2016

Database Magic with Oracle Database 12c

A query on the SALES table takes normally more than 2 minutes but setting the database_performance parameter to SUPER_FAST makes it … as expected super fast: less than 1 second. Setting the database_performance parameter to SUPER_SLOW makes the query hang. Again “as expected”.

Tuesday, 27 December 2016

Invisible Columns In Oracle Database 12C

In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES” statements unless specifically selected – it can be reverted back to visible by using ALTER TABLE command.

Monday, 26 December 2016

Enterprise Manager Database Express in Database 12c

  • Introduction
Key things to remember about Enterprise Manager Database Express.

EM DB Express is not Cloud Control or Grid Control!
EM DB Express is not a replacement for the DB Control from 11g, as it has significantly less functionality.
A DBA will not be able to administer the database using just EM DB Express.

Friday, 23 December 2016

Implicit Statement Results in Oracle Database 12c (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT)

Implicit statement results will simplify the migration of stored procedures from other database engines to Oracle 12c onward.
  • Background
Spend more than a few minutes with a Transact-SQL developer and you will have a conversation something like this.

Thursday, 22 December 2016

Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c

This article discusses the new functionality added to the DUPLICATE command in Oracle 12c.
  • Active Database Duplication using Backup Sets
In previous releases, active duplicates were performed using implicit image copy backups, transferred directly to the destination server. From 12.1 it is also possible to perform active duplicates using backup sets by including the USING BACKUPSET clause. Compared to image copy backups, the unused block compression associated with a backup set can greatly reduce the amount of data pulled across the network for databases containing lots of unused blocks. The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using backup sets rather than image copy backups.

Tuesday, 13 December 2016

Real-Time Database Operation Monitoring in Oracle Database 12c

Oracle 11g introduced Real-Time SQL Monitoring, allowing you to watch the progress of long running SQL, or SQL you explicitly asked to be monitored using the MONITOR hint. Oracle 12c extends this functionality, allowing you to explicitly monitor composite operations made up of a variety of SQL statements and PL/SQL calls containing SQL. The DBMS_SQL_MONITOR package provides the API for this functionality, as well as rehousing some of the subroutines previously found in the DBMS_SQLTUNE package.

Monday, 12 December 2016

Control Invoker Rights Privileges in Views in Oracle Database 12c (BEQUEATH CURRENT_USER)

Oracle 12c Release 1 introduced proper support for invoker rights function calls in views for the first time.
  • Setup
Create the following users to support the examples below.

CONN sys@pdb1 AS SYSDBA

Friday, 9 December 2016

Session-Private Statistics for Global Temporary Tables in Oracle Database 12c

Statistics gathered for global temporary tables (GTTs) were common to all sessions. If you knew the GTTs would need vastly different statistics for each session, you could avoid statistics and rely on dynamic sampling to provide the relevant information. In Oracle database 12c it is possible to have session-private statistics for global temporary tables.
  • Controlling Session-Private Statistics
Session-private statistics are controlled using the GLOBAL_TEMP_TABLE_STATS global preference in the DBMS_STATS package. By default session-private statistics are enabled, as shown below.

Thursday, 8 December 2016

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c

Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.

Wednesday, 7 December 2016

Edition-Based Redefinition Enhancements in Oracle Database 12c

  • FUNCTION
  • LIBRARY
  • PACKAGE and PACKAGE BODY
  • PROCEDURE
  • TRIGGER
  • TYPE and TYPE BODY
  • SYNONYM
  • VIEW
In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.

Tuesday, 6 December 2016

Multitenant : Flashback of a Container Database (CDB) in Oracle Database 12c Release 1

Setup

This article assumes the following things are in place for the examples to work.
  • You have a container database (CDB). You can see how to create one.
  • Your container database (CDB) has at least one pluggable database (PDB). You can see how to create one.
  • You have the flashback database feature enabled on the CDB. You can see how to do that.
  • You have backups of your CDB and PDBs. You can see how to do that.

Monday, 5 December 2016

Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB)

Overview

The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).
  • Container Database (CDB) : On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

Friday, 2 December 2016

Re-registering Databases In A Cluster With srvctl: Problems and Solutions

Recently I wanted to create a policy managed database in my 12.1.0.2 flex cluster setup, but free servers are not available. Hence I had to unregister an administrator managed RAC database (amdbh12) to make the servers available. Having completed my testing, I dropped the policy managed database and then wanted to drop the database amdbh12 using DBCA. It was then that I realized that:

Thursday, 1 December 2016

SQL/JSON Functions in Oracle Database 12c Release 2

Setup

The examples in this article use the following tables.

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

Wednesday, 30 November 2016

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 to allow you to add an extra layer of security to your PL/SQL objects.

Tuesday, 29 November 2016

Oracle Multitenant : Super-fast Provisioning of Standby Databases

I will demonstrate how to quickly create a standby database for a non-multitenant container database (NCDB) by plugging it as a pluggable database (PDB) into primary CDB (BOSTON). This method tremendously simplifies and speeds up the procedure to create a standby database without the need to set various initialization parameters or take backup or configure tnsnames.ora entries etc.

Monday, 28 November 2016

Emergency Monitoring in Oracle Enterprise Manager Cloud Control 12c

The Emergency Monitoring feature of Enterprise Manager Cloud Control 12c is an evolution of Memory Access Mode from Grid Control 11g, allowing you to access in-memory statistics. In EM12c you no longer need to explicitly enable memory access mode. Just navigate to the page and wait while it makes a diagnostic connection to the database and performs lightweight analysis. The screen contains information that will help analyze performance issues, even if the database is hung and you can't connect to the normal performance pages.

Saturday, 26 November 2016

PL/SQL Subtypes

We are always working with data and their datatypes. In the tables we constrain the data by their datatype. For instance, we create a price column of type NUMBER(4,2) telling the database to store a maximum of four (4) digits of which we want to use two (2) as the decimal digits. This is how we, in real life, use our prices. In PL/SQL we can anchor our variables to this type using the <TABLE>.%TYPE syntax. But what if there is no column to anchor to? We can also use the NUMBER(6,2) syntax to constrain the values possible for a certain variable.

Friday, 25 November 2016

Using the Oracle Database for an Analytic Warehouse

Introduction:

Data Warehouses have evolved since their heydays in the 1980s. Many people have given up on the dream of capturing and codifying into one place all the internal data for an organization and consider the task to be too difficult. In the quest to capture everything, they have focused almost exclusively on how to get data into a data warehouse through ETL processes. Instead, we believe the value lies not in storing data, but rather in organizing it, using it to calculate new measures, and facilitating its presentation to users. By focusing on results and uses, you can achieve what organizations truly care about: fast, deep, meaningful analyses.

Wednesday, 23 November 2016

Anatomy Of A SELECT Statement – Part 2: The Hierarchical Query Clause

But first, a definition.  A hierarchical query is a type of recursive query that returns a resultset that displays the natural relationship of data.  Or, if you like pretty mental pictures, hierarchical queries return datasets in which each record is (potentially) holding hands with the record before it.

Using a vanilla select query, you can find out from the EMP table all the staff for whom King is their manager. However, you will then need to run a second select statement to find out all the staff who report to them. Using a hierarchical query, we can find out, in one move, who reports to King, who reports to them and who reports to them. King is Jones’s manager; Jones is Scott’s manager; Scott is Adams’ manager…

Monday, 21 November 2016

Anatomy of a SELECT Statement – Part 1: The WITH Clause

Come, young one.  Come sit by the open fire; hold your cold hands to its warmth. Let me tell you a story.  Back when I was young, the world was a simpler place: we roamed the plains, hunted game with our spears – and Oracle Select statements consisted of only a SELECT, a FROM and, possibly, a WHERE.

Things done changed.

Monday, 14 November 2016

Oracle University Offers New Courses Covering Oracle Database 12c Release2

September was an exciting month for Oracle, as Oracle OpenWorld was in full swing, and new and exciting cloud services were announced to the market.  Oracle announced Oracle Database 12c Release 2 to the cloud. In addition, we also unveiled the  new cloud database offering, Oracle Database Exadata Express Cloud Service.

Friday, 11 November 2016

About functions, records and result cache

Functions are the instrument in the Oracle Database to execute code and return a value. Nothing new here, everybody who develops in PL/SQL on the database knows that.

Functions returning only one value

Let’s start with the easy stuff… the following function will retrieve a record from a table and concatenate the first and last name.

Thursday, 10 November 2016

Oracle Programming Basics: Overloading

It is probably a bad idea to define a word by using a bigger one, but I really can’t introduce the concept of overloading without briefly mentioning polymorphism. Polymorphism, in computer programming, is the ability of an object to interface in different ways depending on other, external criteria. And if that isn’t exactly clear, don’t worry about it; just know that overloading is a type of polymorphism. Store that fact somewhere in the back of your brain; you may never need it again.

Overloading is the act of creating multiple subprograms –  procedures or functions – with the same name.

Collections in 12c

In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your code from scratch, it’s easy enough to make the right choice, but if you are working with existing code and you need to make adjustment and/or you are not allowed to create type objects in the database, and still want to use the SQL power the database offers, since version 12c you can now use Associative Arrays to solve your problem.

Wednesday, 9 November 2016

Collections in Oracle Part 2

Varray

The syntax for defining a varray in SQL is like this:

CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type;

The syntax for defining a varray in PL/SQL is like this:

TYPE varray_type IS VARRAY(SIZE) OF  element_type;

After it has been defined in SQL the varray can be used as a column in the table definition, just like you would use one of the simple types.

Tuesday, 8 November 2016

Collections in Oracle Part 1

Collections are single-dimensioned lists of information, very similar to 3GL arrays. Much like ‘in-memory’ tables. Maybe that’s why they started out as PL/SQL Tables. The PL/SQL only version got renamed a couple of times. When first introduced in Oracle 7 they were called PL/SQL tables. In Oracle 8i they were renamed to Index By tables. Probably because of the index by clause in the syntax. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table did, but it can now be indexed by a VARCHAR2 as well.

Monday, 7 November 2016

Oracle GoldenGate 12c New Features – Part 1

The Oracle GoldenGate software package delivers low-impact, real-time data integration and transactional data replication across heterogeneous systems for continuous availability, zero-downtime migration, and business intelligence.

Oracle GoldenGate 12c empowers organizations to capture, route, transform, and deliver transactional data between heterogeneous databases in real time with minimal overhead.  In 12c, it is now available with new features for all major operating systems and databases.

Saturday, 5 November 2016

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1

Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible.
  • Syntax Made Simple
The pattern matching syntax includes a lot of options, which make it quite daunting at first. This section describes a very simplistic view of the syntax, allowing you to take your first steps. For a detailed description of the syntax, see the documentation

Friday, 4 November 2016

Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article describes how to connect to container databases (CDB) and pluggable databases (PDB).
  • Connecting to a Container Database (CDB)
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.

$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba

Wednesday, 26 October 2016

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1

Oracle 12c introduced the LATERAL inline view syntax, as well as CROSS APPLY and OUTER APPLY joins into the SELECT syntax. There is some similarity between them, so it's easier to deal with them in a single article.
  • Setup
The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

Tuesday, 25 October 2016

Online Statistics Gathering for Bulk Loads in Oracle Database 12c Release 1

Oracle is now able to gather statistics during some bulk operations, making it unnecessary to gather statistics subsequently. This means extra table scans for statistics collection are unnecessary and there is less chance that you will forget to gather statistics after bulk loads.
  • CREATE TABLE ... AS SELECT (CTAS)
Oracle is able to gather statistics during a CREATE TABLE ... AS SELECT (CTAS) operation for most regular heap organized table, as shown below.

Monday, 24 October 2016

Multitenant : Database Triggers on Pluggable Databases (PDBs) in Oracle 12c

With the introduction of the multitenant option, database event triggers can be created in the scope of the CDB or PDB. Some extra trigger events are also available.
  • Trigger Scope
To create a trigger on a database event in a CDB requires a connection to the CDB as a common user with the ADMINISTER DATABASE TRIGGER system privilege.

CONN sys@cdb1 AS SYSDBA

Friday, 21 October 2016

Concurrent Statistics Collection in Oracle Database 12c Release 1

Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. This is done using a combination of the job scheduler, advanced queuing and resource manager. Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can cope with the extra workload.

  • Enabling Concurrent Statistics Collection

From a user perspective, the concurrent statistics collection functionality is very simple. You set the CONCURRENT global preference to the required value using the DBMS_STATS package and Oracle determines if concurrency is appropriate and if so, the level of concurrency to use. The CONCURRENT preference is set to OFF by default, as shown below.

Thursday, 20 October 2016

Oracle DBA in an Oracle Applications World

Transitioning from being a “normal” Oracle DBA into becoming an Oracle Applications DBA has two components. The first concerns the job or position itself, that is, how do you get a job as an Oracle Apps DBA? The second component has to do with the technology aspect of the job. In other words, what is the architecture behind the following items?
  • Oracle Applications
  • Oracle Apps
  • EBS
  • E-Business Suite
  • Oracle 11i
  • EBS 12

Wednesday, 19 October 2016

Mapping SQL Server Management Features to Oracle Database

Oracle as a Data Source, Setting up Oracle as a Data Source for SQL Server and Configuring Oracle as a Data Source for SQL Server address the use of replication within SQL Server where Oracle was used as the data source, and many of the comparisons or mappings back into Oracle are addressed therein, so I’ll pass on repeating this material and move on to the next category.

Replication

Continuing on in SQL Server Management Studio’s (SSMS) Object Explorer tree, we see that the next major category deals with replication.

Tuesday, 18 October 2016

SQL Server Management Features vs Oracle Database Management Features

Notification Services

SQL Server Notification Services, referred to as SQLNS, was meant to be an all-purpose message or messaging delivery system. Its architecture is based on a subscriber, subscriptions, one or more events, and a notification. If you have events whose occurrence you want messaged to a user, that user could receive a notification via PDA, SMS text, and other forms of delivery.

Monday, 17 October 2016

Migrate Oracle Database To ASM Using ASMCMD

Introduction


Oracle Database 10g Release 1 introduced Automatic Storage Management (ASM), a new framework for managing Oracle database files. ASM provides a foundation for highly efficient storage management with direct I/O, redundancy, striping and load balancing. Files stored on ASM are evenly distributed across disks in a disk group and mirrored according to the policies defined in the disk group. Since ASM provides an easy and highly efficient way to manage storage, it is the recommended file system for storing database files for RAC as well as single instance databases.

Saturday, 15 October 2016

Explore Oracle Database In-Memory – Part 2

Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage.

This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes to application, increase the utilization of resources, and allows for the rapid provision and cloning of databases for various purposes by lowering infrastructure and administrative costs.

The pluggable databases share the system global area (SGA) and background processes of a common container database, therefore PDBs also share a single IM column store. The size of the IM column store is controlled by the INMEMORY_SIZE parameter in the CDB.

Thursday, 6 October 2016

Temporal Validity in Oracle Database 12c

The temporal validity feature has been added in Oracle database 12c to make querying of effective date ranges simpler.
  • Setup
The following script creates three tables to store student and module records, along with the relationship between them.

CONN sys@pdb1 AS SYSDBA

GRANT EXECUTE ON dbms_flashback TO test;

CONN test/test@pdb1

Wednesday, 5 October 2016

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c

Invoker rights procedures and functions can present a security risk by allowing privilege escalation if the contents of the procedure and functions are not checked for malicious code.

  • The Problem

The following represents a scenario where a sneaky developer takes advantage of invoker rights to escalate their privileges.

Create the following users.

Tuesday, 4 October 2016

Explore Oracle Database In-Memory – Part 1

The Oracle Database In-Memory option was introduced in Oracle 12c (12.1.0.2 patch set). By using the In-Memory option, businesses can benefit from better decisions made in real time, improved productivity, increased competitiveness and lowered costs.

The Oracle In-Memory option is designed to fit both Data warehouse and mixed workload OLTP databases, and can be deployed with any existing application that supports Oracle 12c. This feature is fully integrated with Oracle Database’s scale-up and scale-out features, security technologies, storage tiering and availability.

Monday, 3 October 2016

Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1

Flashback Data Archive (FDA), also known as Flashback Archive (FBA), was introduced in Oracle 11g to provide long term storage of undo data, allowing undo-based flashback operations to be performed over an extended period of time. Oracle database 12c includes a number of changes that will allow FDA to reach a wider audience.

  • Licensing Changes (All Database Versions for Free)

In previous releases, flashback data archive used compression features from the advanced compression option, so it could only be used with enterprise edition and the advanced compression option. In Oracle 12c, the default when creating flashback data archives is no compression, so it is available for free in all editions of the database. This new default setting has also been back-ported to 11.2.0.4, making it free in all editions that can be patched to that level.

Saturday, 1 October 2016

Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

In Oracle 12c it is now possible to move table partitions and sub-partitions as online operations.

  • MOVE PARTITION ... ONLINE

The following example shows how to online move a table partition.

-- Create partitioned table.
DROP TABLE t1 PURGE;

Friday, 30 September 2016

DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1

DEFAULT Values Using Sequences

In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose.

In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL clause described in the next section.

Thursday, 29 September 2016

Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 2 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c:
  • PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online. In Oracle 12.1.0.1c, a pluggable database can be cloned only if it is read-only.
  • PDBs can be remote cloned, i.e. the source PDB may be located in a remote container. This feature was listed in Oracle Database 12c Release 1 (12.1.0.1), but didn’t work.

Wednesday, 28 September 2016

Oracle Database 12.1.0.2c : Hot Cloning of Non-Container Databases

In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods:
  • Using DBMS_PDB
  • Using datapump (expdb, impdp)
  • Using GoldenGate replication
The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the CREATE PLUGGABLE DATABASE command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Thursday, 22 September 2016

JSON Support in Oracle Database 12c Release 1

This article describes the server side support for JSON in Oracle Database 12c Release 1 (12.1.0.2). There is a lot of variety in the syntax of the JSON functionality, which for simplicity is avoided here.
  • Introduction to JSON
1. JSON stands for JavaScript Object Notation
2. JSON is a lightweight data-interchange format
3. JSON is language independent *
4. JSON is "self-describing" and easy to understand

The following JSON example defines an employees object, with an array of 3 employee records:

Wednesday, 21 September 2016

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c

The PL/SQL language is a combination of two distinct parts:
  1. PL : Procedural Logic.
  2. SQL : Structured Query Language
The two are meshed together so well it is easy to forget this separation exists. One area where the separation does show is in the differing support data types. In previous database versions, binding of PL/SQL-only data types from JDBC, OCI, static and native dynamic SQL was a problem. Oracle 12c now supports the binding of additional PL/SQL-only data types to anonymous blocks, PL/SQL function calls in SQL, the TABLE operator in SQL and CALL statements.

Monday, 19 September 2016

Automatic Diagnostics Repository (ADR) Enhancements in Oracle Database 12c (ADRCI)

The basic concepts of the Automatic Diagnostics Repository (ADR) remain unchanged in Oracle Database 12c, but there have been several notable changes to the way the diagnostics information is handled.

DDL Log


Setting the ENABLE_DDL_LOGGING parameter to TRUE activates DDL logging. All DDL statements are logged into a plain text file and an XML file in the ADR location.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl_{SID}.log
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

Saturday, 17 September 2016

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1

By default, PL/SQL program units are created using definer rights and as such are executed with all the privileges granted directly to the user that created them. This can be very useful when you want low privileged users to perform tasks that require a high level of privilege. In these cases the tasks can be wrapped up in a PL/SQL program unit, with execute privilege granted on that program unit to the low privileged user. The problem with definer rights is it is very easy to accidentally expose excessive functionality to a user.

An alternative is to create the program unit with invoker rights, so it is run in the context the calling user, rather than the user that created it. The advantage of this is the program unit is only able to perform tasks that the calling user has privilege to perform, including those privileges granted via roles. Invoker rights has a number of issues including:

Friday, 16 September 2016

Oracle 12.1.0.2c : Hot Cloning of Non-Container Databases

In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods:
  • Using DBMS_PDB
  • Using datapump (expdb, impdp)
  • Using GoldenGate replication
The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the CREATE PLUGGABLE DATABASE command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Thursday, 15 September 2016

WITH Clause Enhancements in Oracle Database 12c

In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in the WITH clause.

1. Setup


The examples in this article require the following test table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;

Tuesday, 13 September 2016

This Oracle Bug Could Bite You

An interesting bug in Oracle 11.2.0.4 was reported to Oracle Support recently, a bug that can cause a DBA to scratch his or her head in either wonder or disbelief. Listed as Bug 24390197 it is fairly easy to replicate. The example starts by creating an empty tablespace of sufficient size to contain several tables, then creates the DEMO schema and adds records to consume space, leaving more than enough space to create a table of approximately 1 MB in size:

SQL> --
SQL> -- Create a blank tablespace to run
SQL> -- the example from
SQL> --
SQL> connect / as sysdba
Connected.

Monday, 12 September 2016

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1

Introduction


A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set. This concept is not a new one. In fact, Oracle already provides multiple ways to perform Top-N queries, as discussed here. These methods work fine, but they look rather complicated compared to the methods provided by other database engines. For example, MySQL uses a LIMIT clause to page through an ordered result set.

Sunday, 11 September 2016

Multitenant : Pluggable Database (PDB) Names

The CREATE PLUGGABLE DATABASE command includes a very brief, but important statement about the naming of pluggable databases (PDBs).
"Specify the name of the PDB to be created. The first character of a PDB name must be alphanumeric and the remaining characters can be alphanumeric or the underscore character (_).
The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener."
The first part of the second paragraph seems pretty obvious. Why would we expect to have two PDBs with the same name in the same container? The second part of the second paragraph is less obvious though. The PDB name must be unique amongst all the PDBs serviced by a single listener.

Friday, 9 September 2016

Managing SQL Server Backup and Restore History Information

Every time you backup or restore a database SQL Server keeps history information about the backup or restore operation.  The backup and restore information is captured and stored in the msdb database.  This backup and restore information if left unchecked can take up more and more space over time.  To minimize msdb storage space you need to consider removing the backup and restore history records when they are no longer useful.

Determining how long to keep backup history information is site specific.  Meaning how long to keep the backup and restore information is based on the requirements of an individual environment.  For most environments the value of backup and restore information diminishes over time.  For example the backup information for the current backup chain has more value than the backup information for a backup taken two months ago, or maybe even a week ago.

Thursday, 8 September 2016

SQL*Loader Enhancements in Oracle Database 12c Release 1

This article presents an overview of the enhancements to SQL*Loader in Oracle Database 12c Release 1.
  • Setup
  • Direct NFS (DNFS) Support
  • Extended Data Type Support
  • SQL*Loader Express
  • SQL*Loader Command Line Changes
  • SQL*Loader Control File Changes
  • Audit SQL*Loader Direct Path Loads

Wednesday, 7 September 2016

Collections in Oracle PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

1. Index-By Tables (Associative Arrays)


The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

Tuesday, 6 September 2016

Using the TABLE Operator with Locally Defined Types in PL/SQL

In Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types. In previous releases, the TABLE operator would only work with locally defined types if they were used within pipelined table functions. The removal of this restriction means this functionality is available for use with associative arrays, as well as nested tables and varrays in PL/SQL.
  • Setup
  • Associative Array Example
  • Nested Table Example
  • Varray Example
  • Supported Types
  • Location of Type Definition

Saturday, 3 September 2016

PL/SQL Enhancements in Oracle Database 10g

PL/SQL Native Compilation


The process of PL/SQL native compilation has been simplified in Oracle 10g. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations. Native compliation of the package specification and body are independant of each other, meaning either one, the other or both can be natively compiled.

The PLSQL_NATIVE_LIBRARY_DIR parameter is the only one which must be set to use native compilation. All other parameters have been obsoleted. The associated compiler commands are stored in the $ORACLE_HOME/plsql/spnc_commands file which should not need to be modified.

Friday, 2 September 2016

Masking Your Data with SQL Server 2016

There are times when you want some users to see part of a column value but not the whole thing, but you want other users to see the complete column value without any masking.  For example you may want to only allow your call center staff to only see the last 4 digits for a credit card number, with the rest of the credit card number masked with 9’s.  Whereas you want your managers to see column values in an unmasked format.  Or you want the same call center employees to only see email addresses where the middle of an email address is masked with X’s, but the first character and the domain suffix contain unmasked character values.  The new SQL Server 2016 Dynamic Data Masking feature will allow you to define these different kinds of masking rules.

Wednesday, 31 August 2016

Real-time Operational Analytics in SQL Server 2016 - Part 1

Introduction


Organizations have been focusing more and investing heavily in Analytics, to gain the knowledge required to make improvements or changes to enhance business. Analytics help organizations to remain competitive, optimize cost and resources by helping them study their past data (e.g. internal operational data and external data like social media, data from research or government organizations) to identify potential trends, help them in analyzing the effects of certain decisions or events (e.g. Ad campaign, offers on their products and services), and analyzing past/current data to predict outcomes (e.g. credit scoring).

Tuesday, 30 August 2016

Online Move Datafile in Oracle Database 12c Release 1

Prior to Oracle 12c, moving datafiles has always been an offline task. There were certain techniques you could employ to minimize that downtime, but you couldn't remove it completely. Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online.
  • Basic Syntax
  • Examples
  • Pluggable Database (PDB)
  • Tempfiles

Monday, 29 August 2016

Invisible Columns in Oracle Database 12c Release 1

Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.
  • Invisible Columns
  • Invisible Columns and Column Ordering
  • Mandatory Invisible Columns

Tuesday, 23 August 2016

Extended Data Types in Oracle Database 12c Release 1

Prior to Oracle 12c, regardless of the character semantics used, the maximum size of a VARCHAR2, NVARCHAR2 and RAW columns in a database are as follows.
  • VARCHAR2 : 4000 bytes
  • NVARCHAR2 : 4000 bytes
  • RAW : 2000 bytes
With the introduction of Extended Data Types, Oracle 12c optionally increases these maximum sizes.

Monday, 22 August 2016

Database Smart Flash Cache in Oracle Database 12c Release 1

The database Smart Flash Cache was originally released as an Exadata feature. From 11.2.0.2 it was available for use with any flash disks, not just Exadata storage. In Oracle Database 12c the Smart Flash Cache can now use multiple flash disks without needing a volume manager. I'm not sure why this makes it worthy of inclusion in the 12c OCP DBA exam, since it seems like a very minor change.

Sunday, 21 August 2016

Administrative Privileges and Job Role Separation in Oracle Database 12c Release 1

Oracle 12c includes additional administrative privileges to allow a greater level of job role separation if that is necessary in your organisation.
  1. Groups
  2. Creating OS Groups and Users
  3. Using Administrative Privileges
  4. Identify Users with Administrative Privileges (V$PWFILE_USERS)

Saturday, 20 August 2016

Active Session History (ASH) Analytics in Oracle Enterprise Manager Cloud Control 12c

The Active Session History (ASH) was introduced in Oracle 10g to provide real-time diagnostics information. ASH Analytics is a feature of Enterprise Manager Cloud Control 12c, which visualises ASH information, making it even simpler to diagnose performance problems.

ASH Analytics is listed as requirement for the 1Z0-060 "Upgrade to Oracle Database 12c OCP upgrade exam, hence this short overview for those who don't use Cloud Control.

Friday, 19 August 2016

Installing node-oracledb on Microsoft Windows

I. Introduction


Node.js has revolutionized the web development industry by facilitating the use of the JavaScript programming language for server-side development. This also brings with it JavaScript’s non-blocking architecture, which permits commands to be executed in parallel in a single thread, allowing for the construction of highly concurrent applications. Before Node.js, developers were forced to use other languages such as PHP or ASP on the server-side, in addition to using JavaScript on the client-side. Having the ability for a more unified API opens the doors for increased efficiency and the potential of code sharing between the client and server.

Thursday, 18 August 2016

DB2 Data Warehouse Capacity Planning

Early data warehouse implementations began as collections of financial and customer data that were accumulated over time. This allowed for historical reporting and some trend analysis.  Modern warehouses have evolved into complex and elegant enterprise analytics platforms, hosting a broad collection of multiple data types, queried by advanced business intelligence software.

Every day more data arrives, the total amount of data grows larger, and analysts across the organization are presenting the warehouse with higher volumes of complex queries. As the warehouse environment becomes more valuable, capacity planning becomes critical.

Wednesday, 17 August 2016

Oracle's ANSI Join Syntax Can Result In Wrong Results

Ever since Oracle provided their version of ANSI syntax it's been improving with each release. Unfortunately some steps forward also take steps backwards. A case in point is the following example, brought to my attention by Jonathan Lewis. Let's look at the queries and see where Oracle has gone afoul of things.

Tuesday, 16 August 2016

Big Data Architecture

The next few years will be critical for the information technology staff, as they attempt to integrate and manage multiple, diverse hardware and software platforms. This article addresses how to meet this need, as users demand greater ability to analyze ever-growing mountains of data, and IT attempts to keep costs down.

Saturday, 13 August 2016

Index Sanity in Oracle

Unused indexes are a major topic for Oracle DBAs, since each index a table has increases the maintenance "cost" of inserts, updates and deletes. Many a blog post has been written providing ways to find such 'unused' indexes, many relying on a relatively newer Oracle feature, invisible indexes. Two other articles discuss this, one an effort to help the DBA discover unused indexes and the other discusses an issue where those invisible indexes aren't totally invisible. Neither one shows what could happen when an index, thought to be unused and dropped, really wasn't. [Invisible indexes were not used in this example, the indexes were simiply dropped based upon the execution plan generated.]

Saturday, 6 August 2016

Temporal Data Part 3 – Reporting Out Current and Historical Information

As you start creating temporal tables to track history information you might find it necessary to join the history records with the current records into a single set of records.  With the introduction of temporal table support in SQL Server 2016 Microsoft also added some additional functionality that makes it easy for you to join the current and history records of a system-versioned table. In this article I will show you some of the different ways to do analysis of your system-versioned records over time.

Friday, 5 August 2016

Introduction to SQL Server Stretch Database

In many cases Azure SQL Database offers an economically and functionally viable alternative to SQL Server deployments. However, there are also scenarios where we might discover that rather than serving as a replacement, it provides synergy, working side by side with your on-premises databases. One of technologies that illustrate this paradigm is Stretch Database, introduced in SQL Server 2016. We will describe its basic characteristics and review its implementation steps in this article.

Thursday, 4 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 5

Tips & Tricks

Auto Layout


The Auto Layout button - found on the main toolbar - automatically arranges objects on the canvas.  This is often faster and easier than trying to arrange every diagram component yourself.

Here is a Conceptual Diagram of a Travel Bookings before and after executing the Auto Layout command:

Wednesday, 3 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4

Creating Database Structures from a Model


Once the Physical Model has been finalized, it acts as a template for the real database structure.  Building the database schema from a model typically involves consulting the Physical Model for each step of database construction, much like blueprints in the construction of buildings. Some of the downsides to this approach is that it is time consuming and prone to errors.

Tuesday, 2 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 3

Creating a Physical Model


The Model Conversion command can also be employed to create a Physical Model from the Logical one.
  • On the Convert to New Model dialog, select MySQL 5.6 as the target database.

Monday, 1 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2

Converting a Model into another Type

All the work that you put into the Conceptual model need not go to waste.  We can utilize it as the basis for the Logical model.
  • Select File > Model Conversion... from the main menu to open the Convert to New Model dialog.
  • Choose Logical as the Model Type and click OK:

Friday, 29 July 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool

A proper database design is essential to achieving your goals in working with a database, whether they prioritize rapid information retrieval, efficient storage, or some combination of both.  Therefore, investing the time to apply the principles of good design should be a high priority.  Once completed, your database will better meet your needs and can easily accommodate future changes.

As in all aspects of database development and management, specialized software has emerged to make the process much easier to accomplish.  In today’s tutorial, we’ll learn how to design, implement, maintain, and synchronize MySQL database schemas using just such an application - the Navicat Data Modeler.

Thursday, 28 July 2016

Is Your Database Healthy?

Despite the sophistication of the latest DB2 software versions and the power of current IBM z/server technology, it is still possible for performance and data availability to deteriorate due to a variety of things, including increased dataset extents, loss of clustering, index page splits, and other factors.

This article presents simple SQL statements*  that the database administrator (DBA) can execute against the DB2 catalog to determine if one or more application databases suffer from common maladies, and what the DBA can do to fix or mitigate potential problems.

Wednesday, 27 July 2016

Is Your DB2 Subsystem Healthy?

Each new release of DB2 for z/OS contains "autonomic" features that allow the subsystem to diagnose potential issues in real-time. These include the gathering of data distribution statistics in real-time, the ability to reorg databases based on performance criteria, and other functions included in the new DB2 Autonomics Director.

However, it is still possible for performance and data availability to deteriorate due to a variety of things, including lack of proper referential integrity support, excessive package versions and potential security holes.

Monday, 25 July 2016

Getting started with Query Store feature in SQL Server 2016 – Part 3

Introduction


Query Store is a new feature in SQL Server 2016, which once enabled, automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after server restart or upgrade.

Friday, 22 July 2016

Oracle Database 12c – RMAN New Features: Part 3

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how they help the Oracle community.

See new features around SYSBACKUP, pluggable databases, and the SQL interface covered in Part 1 – Oracle Database 12c – RMAN New Features: Part 1

See new features around backups, file recovery, snapshots, and duplication covered in Part 2 – Oracle Database 12c – RMAN New Features: Part 2

Thursday, 21 July 2016

Oracle Database 12c – RMAN New Features: Part 2

Oracle Database 12c has new enhancements and additions to Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help the Oracle community.

See new features covered in Part 1 – Oracle Database 12c – RMAN New Features: Part 1

In this article I will cover:
  • Multisection Backup Improvements
  • Restoring and Recovering Files Over Network
  • Storage Snapshot Optimization
  • Active Database Duplication Improvements

Wednesday, 20 July 2016

Oracle Database 12c – RMAN New Features: Part1

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help Oracle community.

In this article I will cover:
  • SQL Interface Improvements
  • SYSBACKUP Privilege
  • Support for multitenant container and pluggable databases
  • DUPLICATE enhancements

Monday, 18 July 2016

When is an index access path better than a full table scan?

The cost based optimizer makes decisions that can be hard to understand. One of the hardest may be why it chooses indexed or scan access paths: a burning question for many DBAs.

You will often hear a rule-of-thumb regarding what percentage of a table needs to be selected before a full table scan is more efficient thatn an index range scan. 2% is frequently suggested. However, you cannot place any reliance on such a figure. The cross over point will be dependent on many factors, including (but not limited to):

Thursday, 14 July 2016

Inheritance in the Database

In martial arts, fundamentals are everything. The basic kicks, rolls and jumps are far more important that any fancy technique you will learn during the years of practice. When you improve your basic rolls even a little bit, all the body throws that depend on your ability to roll on the floor will improve tenfold.

Software engineering is almost like training martial arts. We should practice every day, and focus on improving our fundamentals instead of running for the next big shinny thing that we read on Hacker News. Of course, this is easier said that done. It is always easier to learn the basics of a new programming language, than to get better at designing classes and polymorphism.

Tuesday, 12 July 2016

Getting Started with JSON Support in SQL Server 2016 – Part 2

Introduction


SQL Server 2005 introduced native support for storing, managing and processing XML data when XML had become de-facto standard for data interchange. This feature still exists in SQL Server as XML has some inherent advantages and is still widely used but because many new applications have started using JSON for a light-weight data interchange mechanism, SQL Server 2016 introduces built-in support for storing, managing and processing JSON data. In my earlier article, I talked about JSON support in SQL Server 2016, how to format or convert tabular data to JSON format using the FOR JSON clause. We also looked at different variants of FOR JSON, to use AUTO to automatically get the structure of the JSON data based on the order of source tables and columns in the query or by using PATH to have full control in specifying the output structure of the JSON data.

Monday, 11 July 2016

Getting Started with JSON Support in SQL Server 2016 – Part 1

Introduction


SQL Server 2005 introduced native support for storing, managing and processing XML data when XML had become the de-facto standard for data interchange. This feature still exists in SQL Server as XML has some inherent advantages and is still used widely, but because many new applications have started using JSON for a light-weight data interchange mechanism, SQL Server 2016 introduces built-in support for storing, managing and processing JSON data. In this article series, I will explain this new feature and demonstrate how to use it in your applications in different scenarios.

Thursday, 7 July 2016

Creating and Deleting System-Versioned Tables

In my last article Altering an Existing Table to Support Temporal Data. I showed you how to modify an existing table to take advantage of the new temporal data feature in SQL Server 2016. By having a temporal data table (history table) I was able to show the changes that have taken place after temporal data support for an existing table was enabled.  In this article I plan to expand the topic to show you how to create and deleted system-versioned tables and the associated history temporal table.

Business Situation


Now that SQL Server 2016 has finally been released management is keen on using this feature as part of a new Employee Management portal application we are building.  One of the first tables they want to create that will track historical data is a table named dbo.EmpSalary.  This table will contain the salaries of current employees, and the historical records associated with this table.  By using the historical temporal data support in SQL Server 2016, management will be able to track salary changes over time.  By creating this system-versioned table using the new temporal data table the new Employee Management portal application will be able to track employees’ salaries over time.

Monday, 20 June 2016

Getting started with Query Store feature in SQL Server 2016 – Part 2

Introduction


Query Store is a new feature in SQL Server 2016, which once enabled, automatically captures and retains a history of queries, execution plans, and runtime statistics, for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after a server restart or upgrade.

In the last article of the series, I discussed the advantages of this new feature, and explained some of the scenarios where you can use it. Finally, I talked about data capture processing, including what’s captured, and how it is retrieved when you enable the Query Store feature. In this article I am going to explain query execution flow when using Query Store and how it differs from regular query execution flow, its architecture and how to get started with it.

Monday, 6 June 2016

Altering an Existing Table to Support Temporal Data

What Is a Temporal Table?

A temporal table is just another SQL Server table that contains the old rows for a corresponding SQL Server table.  It is basically just a history table of old rows.  Every time an existing record is updated, the old row is placed in the associated temporal table automatically. A temporal table can also be called a history table.   Using this new feature in SQL Server 2016 means you can now track changes to a table overtime without having to write any application logic.  SQL Server will place the older rows in the temporal tables automatically.

Wednesday, 1 June 2016

Improved Defaults in Oracle Database 12c

The new DEFAULT clause provides better values for getting started and better performance.

Gordon has just completed some self-study Oracle Application Express training and is eager to start building applications to improve his skills and, more importantly, to rapidly deliver value to the business stakeholders in the company where he works.

He knows that Oracle Application Express is a database-centric development tool, so he wants to make sure he takes as much advantage as possible of the features available to him within the database so that the applications he builds will be more robust; will require less coding; and could even be ported to other front-end technologies, should the need arise. The applications will be simply a window into his data, but the data relationships, integrity, and controls will be within the database, alongside the data.

Tuesday, 31 May 2016

Mutating Table Error In Oracle: Why It Happens And What You Can Do About It

The mutating table error in Oracle can be a familiar sight throughout a DBA's career, along with the usual question of 'Why?'. The error itself is not difficult to understand nor is it hazardous to the table data. Let's look at why the error is thrown, why it's not going to create data problems and how to possibly fix it.

The first thing to realize is the table isn't actually mutating; the error is thrown because of the read consistency mechanism Oracle employs to ensure against 'dirty reads', which are reads of uncommitted changes. For all but the session performing the data modifications Oracle will use redo blocks to reconstruct the data image as of the starting time of the query.

Friday, 27 May 2016

Getting Started with the Query Store Feature in SQL Server 2016 – Part 1

Introduction


While doing performance troubleshooting, have you ever wished for a process that was easier and faster to follow, especially when your system had slowed down and business is being impacted? Did you wish you could compare the execution plans for a query from a different time frame easily and quickly? Did you wish you could regress the query plan easily and quickly instead of going through a lengthy process of using a plan guide to do that? Did you wish execution plans for the queries didn’t get lost even after a server restart or upgrade? If your answers to the above questions are yes, you will be happy to know that the SQL Server 2016 Query Store feature greatly simplifies performance troubleshooting, helps you in the scenarios mentioned above and many more. In this article series, I am going to talk about this new feature and how it greatly simplifies the performance troubleshooting process.

Monday, 23 May 2016

Oracle System Change Number: An Introduction

For many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly – but despite that it’s a fascinating topic, it’s confusing as well. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works.

It’s worth mentioning that this is not and cannot be complete coverage of every detail about SCN. With that said, let’s start.

SCN- Why do we need it?

Assume it’s the end of the month and its payday. You are in the Payroll group and, with appropriate authority, have issued a query to pull the currently salary calculation of all the employees. Numbers are flowing across the screen, everything is going great.

Friday, 13 May 2016

Automatic Big Table Caching in RAC

I discussed Automatic big table cache (ABTC) which is a new feature introduced in Oracle Database 12.1.0.2c. Automatic big table cache is an optional section of the buffer cache reserved for storing large objects. It tracks only big tables and automatically caches scanned big objects in big table cache thereby avoiding direct path reads. It caches partial objects when objects cannot be fully cached. It is primarily designed to enhance performance for data warehouse workloads, but also improves performance in mixed workloads. Though it can be used for Serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.

In this article, I will demonstrate ABTC for parallel scans in a 12.1.0.2c RAC database.

Current scenario:
  • Name of cluster: Cluster01
  • Number of nodes : 3
  • Name of RAC database (Non-CDB): orcl
  • Number of RAC database instances : 3

Friday, 1 April 2016

Oracle Database Solutions: Proven Market Leadership

What is cloning?

Cloning is a process of copying an existing installation to a different server or location. Cloning is similar to an Oracle installation except Oracle universal installation performs the actions in a special mode called “clone mode”.

Starting from 10g onwards, Oracle supports cloning and users can easily clone existing Oracle installations. The source and destination servers should have same configuration and packages installed in order Oracle cloning to work.  The cloning process works by copying all files from the source Oracle home to the destination Oracle home, and files which are not part of the source instance will not be copied to the destination location.

Thursday, 31 March 2016

Oracle Database 12.1.0.2c: Automatic Big Table Cache

Serial direct path reads were first introduced in Oracle 11g to prevent wiping out a large number of buffers from the buffer cache by a serial large table scan. As a result, big tables accessed via serial Full Table Scans bypass the Buffer Cache and read data from the Data Files into the users’ Program Global Area (PGA). This prevents flooding of the Buffer Cache, but subsequent SELECTs on big table always access the disk thereby degrading the performance.

If a big table is accessed repeatedly using serial FTS, and it is desirable to avoid repeated disk reads during subsequent serial full table scans, there are a few options according the version of your Oracle instance. Prior to 12.1.0.2c, the data in big tables can be cached in buffer cache using two different methods:

Saturday, 27 February 2016

Oracle Database Cloud Service vs Amazon Relational Database Service

How to compare Oracle’s Database Public Cloud with Amazon’s Relational Database Service (RDS) for enterprise usage? Let us have a look.

Oracle’s Database has 4 editions: Personal Edition, Express Edition (XE): free of charge and used by very small businesses and students, Standard Edition (SE): light version of Enterprise Edition and purpose designed to lack most features needed for running production grade workloads and Enterprise Edition (EE): provides the performance, availability, scalability, and security required for mission-critical applications.

In the comparison in this post, we will evaluate Oracle and Amazon in relation to the Enterprise Edition of Oracle’s database.

Oracle Database Cloud Service vs Amazon Relational Database Service

Oracle Public Database Cloud consists of 4 DB Cloud offerings: DBaaS, Virtual Image, Schema Service and Exadata Service. Here are few characterizations:

– Oracle supports Exadata, RAC & all DB options
– Simple pricing structure with published costs representing actual costs (unlimited I/Os, etc.)
– Hourly, Monthly & Annual pricing options
– Lowest cloud storage pricing across all major IaaS vendors

Amazon RDS for Oracle Database supports two different licensing models – “License Included” and “Bring-Your-Own-License (BYOL)”. In the “License Included” service model, you do not need separately purchased Oracle licenses. Here are few characterizations:

– Enterprise Edition supports only db.r3.large and larger instance classes, up to db.r3.8xlarge
– Need to choose between Single-AZ (= Availability Zone) Deployment and Multi-AZ Deployment
– For Multi-AZ Deployment, Amazon RDS will automatically provision and manage a “standby” replica in a different Availability Zone (prior to failover you cannot directly access the standby, and it cannot be used to serve read traffic)
– Only 2 instance types support 10 Gigabit network: db.m4.10xlarge and db.r3.8xlarge
– Amazon RDS for Oracle is an exciting option for small to medium-sized clients and includes Oracle Database Standard Edition in it’s pricing
– Several application with limited requirements might find Amazon RDS to be a suitable platform for hosting a database
– As the enterprise requirements and resulting degree of complexity of the database solution increase, RDS is gradually ruled out as an option

So, here is high level comparison:

Oracle Database Cloud Service vs Amazon Relational Database Service


Notes:

– Oracle’s price includes the EE license with all options
– Amazon AWS is BYOL for EE
– Prices above are based on the EU (Frankfurt) region
– Amazon’s Oracle database hour prices vary from $0.290 to $4.555 for Single AZ Deplyoments and from $0.575 to $9.105 for Multi-AZ Deployments
– Oracle’s database hour prices vary from $0.672 to $8.569

So, Amazon RDS is not an option if you need any of the following: Real Application Clusters (RAC), Real Application Testing, Data Guard / Active Data Guard, Oracle Enterprise Manager Grid Control, Automated Storage Management, Database Vault, Streams, Java Support, Locator, Oracle Label Security, Spatial, Oracle XML DB Protocol Server or Network access utilities such as utl_http, utl_tcp, utl_smtp, and utl_mail.

Tuesday, 16 February 2016

ASM Proxy: New Instance Type in Oracle 12c

Prior to Oracle Database 12c, an ASM instance ran on every node in the cluster and ASM Cluster File System (ACFS) Service on a node connected to the local ASM instance running on the same host to fetch the required metadata. If the ASM instance on a node were to fail, then ACFS file systems could no longer be accessed on that node.

With introduction of Flex ASM in Oracle 12c, hard dependency between ASM and its clients has been relaxed and only a smaller number of ASM instances need run on a subset of servers in a cluster. In such a scenario, in order to make ACFS services available on nodes without an ASM instance, a new instance type has been introduced by Flex ASM: the ASM-proxy instance which works on behalf of a real ASM instance. ASM Proxy instance fetches the metadata about ACFS volumes and file systems from an ASM instance and caches it. If ASM instance is not available locally, ASM proxy instance connects to other ASM instances over the network to fetch the metadata. Additionally, if the local ASM instance fails, then ASM proxy instance can failover to another surviving ASM instance on a different server resulting in uninterrupted availability of shared storage and ACFS file systems.