Wednesday, 13 December 2017

How to Turn on Instant File Initialization

Do you have instant file initialization turned on?  If you do, then allocating your database data pages will go much faster.  Not having instant file initialization turned on slows down the process of adding pages to your database.  It slows down because prior to allocating the data pages to your database, SQL Server needs to zero out the newly allocated pages.

Monday, 11 December 2017

Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 5

This article describes the installation of Oracle Database 12c Release 1 (12.1) (64-bit) on Oracle Linux 5 (64-bit). The article is based on a server installation with a minimum of 2G swap, with SELinux set to permissive and the firewall disabled. The following package groups were included for this installation.

Thursday, 7 December 2017

Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 6

This article describes the installation of Oracle Database 12c Release 1 (12.1) (64-bit) on Oracle Linux 6 (64-bit). The article is based on a server installation with a minimum of 2G swap, with SELinux set to permissive and the firewall disabled. The following package groups were included for this installation.

Wednesday, 6 December 2017

Oracle Database 12c Release 1 (12.1) RAC On Oracle Linux 7 Using VirtualBox Part-2

◉ Install the Grid Infrastructure


Make sure both virtual machines are started. Install the following package from the Oracle grid media as the "root" user.

# cd /media/sf_12.1.0.2/grid/rpm
# rpm -Uvh cvuqdisk*

Monday, 4 December 2017

Oracle Database 12c Release 1 (12.1) RAC On Oracle Linux 7 Using VirtualBox Part-1

This article describes the installation of Oracle Database 12c release 1 (12.1 64-bit) RAC on Linux (Oracle Linux 7 64-bit) using VirtualBox (5.0.4) with no additional shared disk devices.

◉ Introduction


One of the biggest obstacles preventing people from setting up test RAC environments is the requirement for shared storage. In a production environment, shared storage is often provided by a SAN or high-end NAS device, but both of these options are very expensive when all you want to do is get some experience installing and using RAC. A cheaper alternative is to use a FireWire disk enclosure to allow two machines to access the same disk(s), but that still costs money and requires two servers. A third option is to use virtualization to fake the shared storage.

Friday, 1 December 2017

Oracle Database 12c Release 1 (12.1.) RAC on Oracle Linux 7 Using NFS

This article describes the installation of Oracle Database 12c Release 2 (12.1 64-bit) RAC on Oracle Linux 7.1 64-bit using NFS to provide the shared storage.

◉ Introduction


NFS is an abbreviation of Network File System, a platform independent technology created by Sun Microsystems that allows shared access to files stored on computers via an interface called the Virtual File System (VFS) that runs on top of TCP/IP. Computers that share files are considered NFS servers, while those that access shared files are considered NFS clients. An individual computer can be either an NFS server, a NFS client or both.

Friday, 24 November 2017

WebLogic Server 12cR1 (12.1.2 and 12.1.3) : Create, Extend and Remove Domains

This article provides a quick overview of managing domains in WebLogic Server 12c. The examples all relate to WebLogic 12.1.2 and 12.1.3.

◉ Create a Domain


Start the Configuration Wizard.

Monday, 20 November 2017

Oracle Forms and Reports Services 12c Configuration Notes

This article is a collection of useful notes on a variety of topics related to configuration of Oracle Forms and Reports Services 12c.

◉ Installation


This site contains a number of articles describing the installation of WebLogic Server 11g and Oracle Forms and Reports Services 12c on Linux and Windows.

Friday, 17 November 2017

Standalone Forms Builder 12c (12.2.1) Installation on Windows

In Oracle Forms 11g a WebLogic Server installation was a prerequisite for the Forms Builder, making the footprint of a development PC quite substantial. Oracle Forms 12c allows a Standalone Forms Builder installation, which no longer needs a WebLogic Installation.

Wednesday, 15 November 2017

Forms and Reports Services 12c (12.2.1) : Create a New Domain

This article presents a brief overview of creating a new domain for a Forms and Reports Services 12c deployment.

◉ Assumptions


This article assumes the following.

Monday, 13 November 2017

Oracle WebLogic Server (WLS) 12cR2 (12.2.1) Installation on Oracle Linux 6 and 7

This article presents a brief overview of installing Oracle WebLogic Server (WLS) 12cR2 (12.2.1) on Oracle Linux 6 and 7.

◉ Assumptions


This article assumes you have an existing server (real or virtual) with either Oracle Linux 6.x or 7.x installed on it. For instruction on how to do this check out the following articles.

Friday, 10 November 2017

DEPRECATE Pragma for PL/SQL in Oracle Database 12c

As an application evolves the code gets refactored and some of the code may no longer be needed. Removing old code can be a problem as far as backwards compatibility is concerned, but you want to make sure any new development avoids referencing old APIs. Oracle database 12.2 introduced the DEPRECATE pragma to mark code as deprecated and display compiler warnings if deprecated code is referenced by any newly compiled code.

Wednesday, 8 November 2017

Create Table for Exchange With a Partitioned Table in Oracle Database 12c

Make sure a table is compatible for an EXCHANGE PARTITION operation by using CREATE TABLE ... FOR EXCHANGE WITH TABLE in Oracle Database 12c Release 2 (12.2).

Monday, 6 November 2017

Upgrade Oracle Enterprise Manager Cloud Control 12c Release 3 (12cR3) to Release 4 (12cR4)

This article describes a simple upgrade of Enterprise Manager Cloud Control 12c Release 3 (12cR3) to Release 4 (12cR4). Each upgrade potentially requires additional steps, so this is not meant as a replacement for reading the documentation.

Friday, 3 November 2017

Installing Enteprise Manager Command Line Interface (EMCLI) for Cloud Control 12c and 13c

Enterprise Manager Cloud Control 12c and 13c include a command line interface (EMCLI) that allows you to script many tasks. This article introduces the basics of the EMCLI utility.

◉ Download EMCLI


From the Cloud Control console, navigate to the "Enterprise Manager Command Line Tools Download" page using the "Setup > Command Line Interface" menu option.

Thursday, 2 November 2017

Oracle Enterprise Manager Cloud Control 12c Release 5 Installation on Oracle Linux 5.11 and 6.6 Part - 2

◉ Cloud Control 12c Installation


Make the following directories to hold the management server and agent.

$ mkdir -p /u01/app/oracle/oms12cr5
$ mkdir -p /u01/app/oracle/agent12c

Wednesday, 1 November 2017

Oracle Enterprise Manager Cloud Control 12c Release 5 Installation on Oracle Linux 5.11 and 6.6 Part-1

This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 5 on Oracle Linux 5.11 and 6.6. (x86_64).

◉ Software


Download the following software:

Tuesday, 31 October 2017

DBMS_COMPRESSION Enhancements in Oracle Database 12c

The DBMS_COMPRESSION package was introduced in Oracle 11gR2. Oracle Database 12c includes a number of enhancements to the DBMS_COMPRESSION package.

◉ Setup


The examples in this article use the table defined below.

Monday, 30 October 2017

What is difference between primary key and unique key in table - SQL database

Primary key vs unique key in SQL


The primary key and unique key are two important concepts in a relational database and used to uniquely identify a row in a table. Both primary key and unique key can identify a row uniquely but there is some subtle difference between them which we will see in this article. In fact, primary key vs unique is a popular SQL interview questions along with classics like truncate vs delete and  How to manage transaction in a database, mostly asked to fresher and 2 to 3 years experience guys in any programming language. SQL is not just limited to any DBA or PLSQL developer but its an important skill even for Java programmer and you can expect SQL interview question even in many Java interviews.

Saturday, 28 October 2017

How to connect Eclipse to Oracle database - Step by Step Guide

Though, I prefer Toad or Oracle SQL Developer tool to connect Oracle database, sometimes it's useful to directly connect Eclipse to Oracle using JDBC using its Data Source Explorer view. This means you can view data, run SQL queries to the Oracle database right from your Eclipse window. This will save a lot of time wasted during switching between Toad and Eclipse or Oracle SQL Developer and Eclipse. Eclipse also allow you to view Execution plan in both text and Graphical mode, which you can use to troubleshoot performance of your SQL queries. In this article, I'll tell you steps to connect Eclipse to Oracle database. Since Eclipse uses Java to connect to Oracle database, it asks for JDBC driver information. Since connecting using thin Oracle JDBC driver is much easier, as you just need to drop a JAR file in the classpath, we will be using JDBC thin driver to connect Oracle from Eclipse. Depending upon your Oracle version e.g. Oracle 10g or Oracle 11g you need to download Oracle JDBC thin driver e.g. ojdbc14.jar file. Just download this JAR file and configure it on Eclipse IDE. Enter username, password and sid and you are ready to execute SQL query to Oracle database from Eclipse.

Friday, 27 October 2017

Oracle Enterprise Manager Cloud Control 12c Post-Installation Setup Tasks

◉ Setup Software Library


Create a directory to use as the software library.

$ mkdir -p /u01/app/oracle/oms12cr2/software_library

Navigate to the "Software Library: Administration" screen using the menu at the top-right of the screen (Setup > Provisioning and Patching > Software Library).

Thursday, 26 October 2017

Oracle Enterprise Manager Cloud Control 12c Release 4 Installation on Oracle Linux 5.9 and 6.5

Enterprise Manager Cloud Control is the new name for what was previously know as Enterprise Manager Grid Control. Depending on the source of the material, you may still see the product described as Grid Control. This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 4 on Oracle Linux 5.9 and 6.5. (x86_64).

Wednesday, 25 October 2017

Oracle Enterprise Manager Cloud Control 12c Release 3 Installation on Oracle Linux 5.9 and 6.4

Enterprise Manager Cloud Control is the new name for what was previously know as Enterprise Manager Grid Control. Depending on the source of the material, you may still see the product described as Grid Control. This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 3 on Oracle Linux 5.9 and 6.4. (x86_64). This is the first Cloud Control release capable of monitoring the Oracle 12c database.

Tuesday, 24 October 2017

Oracle Enterprise Manager Cloud Control 12c Release 2 Installation on Oracle Linux 5.8 and 6.3

Enterprise Manager Cloud Control is the new name for what was previously know as Enterprise Manager Grid Control. Depending on the source of the material, you may still see the product described as Grid Control. This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 2 on Oracle Linux 5.8 and 6.3. (x86_64).

Tuesday, 17 October 2017

Oracle Enterprise Manager Cloud Control 12c Release 1 Installation on Oracle Linux 5.7 and 6.1

Enterprise Manager Cloud Control is the new name for what was previously know as Enterprise Manager Grid Control. Depending on the source of the material, you may still see the product described as Grid Control. This article describes the installation of Oracle Enterprise Manager Cloud Control 12c Release 1 on Oracle Linux 5.7 and 6.1. (x86_64).

Monday, 16 October 2017

MySQL 8.0 RC1 Now Available

Oracle has announced the availability of the first release candidate of MySQL 8.0, which focuses on making MySQL better for modern apps.

The 8.0 also marks a departure from the 5.x numbering series, which has been used since 2005. Skipping 6.x and 7.x was intentional to avoid confusion with a previous alpha release and MySQL Cluster, and also to communicate the depth of some of the core changes, writes Tocker.

Thursday, 12 October 2017

Azure SQL Database Long-Term Backup Retention

Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure. You can take advantage of this functionality in order to perform backup, restore, and failover tasks, which help you recover from human errors, service outages, or even regional disasters. We have already described some of the corresponding features on this forum, including Point-in-Time Restore as well as Geo-Restore and Active Geo-Replication. Each one of them is an integral part of the Azure SQL Database service offering, regardless of the pricing tier. However, it is important to note that automatic backups created by Point-in-Time Restore, while convenient, have a relatively limited retention period (up to 35 days, with the Premium pricing tier). While this typically suffices to accommodate scenarios that involve restores due to an accidental data loss, it is not sufficient to satisfy archival and compliance requirements of an overwhelming majority of customers. To remediate this shortcoming, you have the option of implementing long-term backup retention of Azure SQL Database.

Wednesday, 11 October 2017

A Recent Innovation in NoSQL Data Modeling: Forward and Reverse Engineering Combined

Many organizations would like to develop and incorporate a NoSQL data model in their existing architectures, and to hire staff with Data Modeling skills. Unfortunately, there is a significant lack of data modelers with NoSQL experience, and reliable NoSQL Data Modeling tools are only just beginning to emerge into the market. The shortage of experienced NoSQL data modelers, combined with the scarcity of appropriate tools and delayed projects, can stunt a company’s growth.

Tuesday, 10 October 2017

An Introduction to Control Center Personalities in DB2 Universal Database

If you come from a Windows background, you are likely to leverage a graphical user interface (GUI) to perform most of the management tasks for your environment, including managing your IBM DB2 Universal Database (DB2 UDB) server. The Control Center is the GUI tool that provides database administrators (DBAs) with a central location from which to perform DB2-related administration tasks, and more.

Monday, 9 October 2017

Azure Azure SQL Database Long-Term Backup Retention Long-Term Backup Retention

Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure. You can take advantage of this functionality in order to perform backup, restore, and failover tasks, which help you recover from human errors, service outages, or even regional disasters. We have already described some of the corresponding features on this forum, including Point-in-Time Restore as well as Geo-Restore and Active Geo-Replication. Each one of them is an integral part of the Azure SQL Database service offering, regardless of the pricing tier. However, it is important to note that automatic backups created by Point-in-Time Restore, while convenient, have a relatively limited retention period (up to 35 days, with the Premium pricing tier). While this typically suffices to accommodate scenarios that involve restores due to an accidental data loss, it is not sufficient to satisfy archival and compliance requirements of an overwhelming majority of customers. To remediate this shortcoming, you have the option of implementing long-term backup retention of Azure SQL Database. Note that this functionality is in public preview at the time of authoring of this article.

Friday, 6 October 2017

Updating Production Environments to Feed a Big Data Application

Your big data application needs regular extracts from your production systems. While many best practices exist for big data extract, transform and load (ETL) processes, we sometimes forget that these data-intensive procedures can affect the operational environment’s performance.


Big Data Application Resource Usage


Today’s big data applications are scaling up and out. This involves adding more CPU power, more memory, and more system resources. IT staff are also upgrading the hybrid hardware and software appliances used for big data information storage and execution of business analytics.

Thursday, 5 October 2017

Oracle Database to SQL Server Comparisons

One of the initial obstacles a database administrator encounters is learning where features of his/her system live or reside on a less familiar system. Steve Callan approaches this feature comparison by taking SQL Server and mapping its features back into Oracle.

No, this isn’t one of those “why my RDBMS is better than your RDBMS” comparisons. One of the initial stumbling blocks a DBA on one system encounters is learning or trying to figure out where features of your system live or reside on the other (less familiar to you) system. Let’s approach this feature comparison by taking SQL Server and mapping its features back into Oracle. By way of time and constraints, this comparison will not be 100% inclusive of either system, but it will show (as much as possible) the SQL Server to Oracle mapping using what you can see in SQL Server Management Studio (SSMS).

Wednesday, 4 October 2017

Table types in MySQL: InnoDB tables

InnoDB is a storage engine for the database management system MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity).

Tuesday, 3 October 2017

9 New Features with SQL Server 2017

9 New Features with SQL Server 2017


Microsoft has added a ton of new features in SQL Server 2017, including expanded operating system support, Graph database capability, Python support, and more. Read on to learn about nine of these new features.

Friday, 29 September 2017

Tips for Simplifying Crosstab Query Statements

The CASE Statement


The CASE statement is a type of conditional construct much like an IF ELSE. It evaluates a search condition and executes the corresponding SQL statement list, depending on which condition evaluates to true. If no search condition matches, the statement list in the ELSE clause is executed instead. Each statement list may consist of one or more statements. Multiple statements are enclosed between the BEGIN and END delimiters.

Wednesday, 27 September 2017

Oracle's Newest PL/SQL Feature 'ACCESSIBLE BY'

Oracle has offered packages for a while now, but until recently it's been difficult, if not impossible, to prevent 'out of context' execution of functions and procedures; in many cases package code is designed to be used within the context of the package, not as stand-alone procedures and functions. In release 12.2.0.1, Oracle has addressed this issue with the ACCESSIBLE BY clause. This clause allows developers to refine the scope of a procedure or function call and generate an error message when that procedure or function is called outside of the package context. Let's build an example to illustrate how this is written and what results are obtained when the context-restricted code is executed in context and out.

Tuesday, 26 September 2017

Overview of Azure Data Lake

Introduction


Data with 4 Vs – variety, volume, velocity and veracity is known as Big Data. As we know, there are lots of tools and techniques available to handle big data. Big data is shifting the data paradigm and increasing the expectations with data analytics. We should consider how to manage and store this massive data from its source and apply data analytic techniques to meet expectations. On the cloud, there are various ways to store the data. Microsoft Azure supports various types of data storage like Blob storage, File storage, Queue storage, Table storage and Azure Data Lake. Although, the use cases are relatively different to use any of these storages. However, Azure Data Lake is widely accepted to store massive size i.e. petabytes and zettabytes of data.

Monday, 25 September 2017

Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION in Oracle Database 12c

The TRUNCATE [SUB]PARTITION and EXCHANGE [SUB]PARTITION commands can now include a CASCADE clause, allowing the actions to cascade down the hierarchy of reference partitioned tables. For this to work, the referenced foreign keys must include the ON DELETE CASCADE clause.

Thursday, 21 September 2017

Where is my SQL Server Configuration Manager?

If you are running Windows 8 and above, you might have found out that you can’t find SQL Server Configuration Manager in the list of installed applications.   This is because SQL Server Configuration Manager is not a stand-alone program, and therefore doesn’t appear as an application in newer versions of Windows.   The SQL Server Configuration Manager is a Microsoft Management Console snap-in.

Wednesday, 20 September 2017

Machine Learning in Oracle Database – Classification of Conference Abstracts based on Text Analysis

Machine Learning is hot. The ability to have an automated system predict, classify, recommend and even decide based on models derived from past experience is quite attractive. And with the number of obvious applications of machine learning – Netflix and Amazon recommendations, intelligent chat bots, license plate recognition in parking garages, spam filters in email servers – the interest further grows. Who does not want to apply machine learning?

DB2 V12 Features Supporting Large Databases

Big data applications were once limited to hybrid hardware/software platforms. Now, recent advances are allowing applications like these to be integrated with and federated into operational systems. In particular, IBM's DB2 for z/OS Version 12 (DB2 V12) delivers new features and functions that allow the DBAs to design, define and implement very large databases and business intelligence query platforms that fulfill some big data expectations. Such applications can then provide value to the enterprise without expending money and time on specialized infrastructure, and can also be the first step towards building out one or more true big data applications.

Tuesday, 19 September 2017

Database as a Service: Defining Quality

Enterprises are delegating various aspects of application development and support to outside firms in order to shorten implementation schedules. The sooner you implement, the thought goes, the sooner you are in front of customers and making profits. Another advantage is getting your application running before your competition implements their own. This delegation comes at a cost. Application and database definitions are now spread among multiple firms across multiple platforms. Business rules are implemented in (potentially) many places, and many support staff skills are not under your control. This raises the complexity of the application and increases the risks of failure during application changes, maintenance or upgrades.

Monday, 18 September 2017

Introduction to the Azure SQL Database Advisor

One of the challenges associated with running your workloads in Azure SQL Database is the limited level of management oversight (when compared with databases hosted on full-fledged SQL Server instances) over its resources. While you can easily scale it vertically by changing the associated service tier and performance level, you do not have, for example, the option of running SQL Server Profiler or Index Tuning Wizard, commonly used to evaluate, troubleshoot, and optimize database performance. Fortunately, there is an alternative approach that leverages the functionality incorporated into the recently introduced Azure SQL Database Advisor component of Azure SQL Database. In this article, we will present its basic characteristics.

Thursday, 14 September 2017

Database as a Service: Choosing a Provider

Rolling out a new customer-facing application can be quite a challenge, considering the time it takes to develop and test the code. In addition, much time is taken on data modeling and database design, usually with multiple levels of operating systems, network and database professionals. Providers of database as a service (DBaaS) promise to handle all these database tasks, including hardware acquisition, database management software installation and configuration, database definition, performance tuning, and backup and recovery.

Tuesday, 12 September 2017

DBMS_PIPE - For Inter-Session Communication

The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.

Monday, 11 September 2017

Introduction to SAP HANA on Azure

Microsoft and SAP share a long history of working together on a variety of interoperability solutions, including, for example, SAP Fiori and Office 365. They have been technology partners for over 20 years, with development teams co-located in their respective corporate headquarters in Redmond and Waldorf, Germany. In recent years, this trend has been exemplified by advancements in support for SAP-based solutions on Microsoft Azure. The purpose of our article is to provide their overview, focusing in particular on support for SAP HANA on Azure.

Saturday, 9 September 2017

Managing Azure SQL Database Recovery

Azure SQL Database offers a number of benefits, built into the underlying cloud infrastructure, that leverage resiliency and redundancy. You can take advantage of this functionality in order to facilitate failover and failback in response to events that affect availability of an Azure region. In this article, we will step through a procedure illustrating this capability.

Friday, 8 September 2017

Network Configuration of SQL Server Always On Availability Groups in Azure

As we pointed out in our article recently published on this forum, implementing SQL Server Failover Clustering in Azure virtual machines differs in several aspects from its on-premises implementations. These differences reflect some of the unique characteristics of the storage and network infrastructure services in the Microsoft cloud environment. In this article, we will focus on the networking aspects of clustered deployments of SQL Server 2016 in Azure. For more information regarding storage-related considerations, refer to the aforementioned article.

Wednesday, 6 September 2017

Understanding Azure Data Factory – A Cloud Based Integration Service

Azure Data Factory is a cloud based integration service that allows you to transfer data from disparate cloud and on premise data sources to various data storage destinations. This is similar to another on premise ETL tool, SQL Server Integration Service (SSIS), provided by Microsoft.

Monday, 28 August 2017

Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 7 Using VirtualBox Part-2

  • Install Guest Additions

Click on the "Devices > Install Guest Additions" menu option at the top of the VM screen. If you get the option to auto-run take it. If not, then run the following commands.

cd /media/VBOXADDITIONS*
sh ./VBoxLinuxAdditions.run

Friday, 25 August 2017

Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 7 Using VirtualBox Part-1

This article describes the installation of Oracle Database 12c Release 2 (12.2 64-bit) RAC on Linux (Oracle Linux 7 64-bit) using VirtualBox (5.1) with no additional shared disk devices.

Thursday, 24 August 2017

Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 6 Using VirtualBox Part-2

  • Install Guest Additions

Click on the "Devices > Install Guest Additions" menu option at the top of the VM screen. If you get the option to auto-run take it. If not, then run the following commands.

cd /media/VBOXADDITIONS*
sh ./VBoxLinuxAdditions.run

Wednesday, 23 August 2017

Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 6 Using VirtualBox Part-1

This article describes the installation of Oracle Database 12c Release 2 (12.2 64-bit) RAC on Linux (Oracle Linux 6 64-bit) using VirtualBox (5.1) with no additional shared disk devices.

  • Introduction


One of the biggest obstacles preventing people from setting up test RAC environments is the requirement for shared storage. In a production environment, shared storage is often provided by a SAN or high-end NAS device, but both of these options are very expensive when all you want to do is get some experience installing and using RAC. A cheaper alternative is to use virtualization to fake the shared storage.

Tuesday, 22 August 2017

Azure SQL Database - Dynamic Data Masking

Dynamic Data Masking offers the ability to limit visibility of sensitive data stored in relational database tables accessible to non-privileged users. This functionality, supported starting with SQL Server 2016 and present in the current version of Azure SQL Database relies on a custom policy defined on the database level. The policy specifies one or more filters that should be applied to the result set returned in response to user or application initiated queries. This capability facilitates scenarios where database-resident content should not be fully exposed to database users. This is commonly required when referencing data containing Personally Identifiable Information (PII), such as Social Security Numbers (SSNs), credit card numbers, or email addresses. By applying Dynamic Data Masking, you can ensure that only a portion of the relevant column (for example, the last four digits of a SSN) is present in its original form in a result set.

Monday, 21 August 2017

Oracle Database 12c Release 2 (12.2) Installation On Fedora 26 (F26)

This article describes the installation of Oracle Database 12c Release 2 (12.2) 64-bit on Fedora 26 (F26) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive. An example of this type of Linux installation can be seen here.

Saturday, 19 August 2017

Oracle Database 12c Release 2 (12.2) Installation On Fedora 25 (F25)

This article describes the installation of Oracle Database 12c Release 2 (12.2) 64-bit on Fedora 25 (F25) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

Friday, 18 August 2017

Oracle Database 12c Release 2 (12.2) Installation On Oracle Linux 6 (OL6) and 7 (OL7)

This article describes the installation of Oracle Database 12c Release 2 (12.2) 64-bit on Oracle Linux 6 (OL6) and 7 (OL7) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

Friday, 11 August 2017

Implementing SQL Server Failover Clustering in Azure

Deploying Infrastructure as a Service (IaaS) solutions in Microsoft Azure offers a number of benefits that leverage agility, resiliency, and scalability built into the underlying platform. However, when dealing with business-critical workloads, customers typically want to also provide high-availability and disaster recovery capabilities in a manner that they can control. Trying to implement this approach in the cloud by following the procedures applicable in on-premises datacenters frequently presents challenges that results from differences between the two environments. In this article, we will focus on these differences in the context of deployment of SQL Server Failover Clustering in Azure.

Thursday, 10 August 2017

Full Database Caching Mode in Oracle Database 12cR1

Under normal running the Oracle database decides what data to cache in the buffer cache. If there is not enough room, data can be aged out of the cache. In addition, Oracle may choose to bypass the buffer cache for some operations to prevent useful information being artificially aged out by a large read operation.

Wednesday, 9 August 2017

Dynamic Statistics in Oracle Database 12c Release 1

Oracle Database 10g introduced Dynamic Sampling to allow the optimizer to gather additional information at parse time if database statistics were missing, stale or insufficient to produce a good execution plan. Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter at instance or session level, or for individual queries using the DYNAMIC_SAMPLING hint, with available values ranging between "0" (off) to "10" (aggressive sampling) with a default value of "2".

Tuesday, 8 August 2017

Enterprise Manager Database Express in Database 12c Release 1

  • Introduction

Key things to remember about Enterprise Manager Database Express.
  1. EM DB Express is not Cloud Control or Grid Control!
  2. EM DB Express is not a replacement for the DB Control from 11g, as it has significantly less functionality.
  3. A DBA will not be able to administer the database using just EM DB Express.

Friday, 4 August 2017

Multitenant : Backup and Recovery of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c Release 1

  • RMAN Connections


Unless stated otherwise, this article assumes connections to RMAN are using OS authentication. This means you are connecting to the root container in the CDB with "AS SYSDBA" privilege.

$ export ORAENV_ASK=NO
$ export ORACLE_SID=cdb1
$ . oraenv

Thursday, 3 August 2017

Loading LOB Data Using SQL*Loader

This articles shows how SQL*Loader is used to load CLOB and BLOB data, enabling parallel load operations of large quanities of data. There are several methods for SQL*Loader to load LOBs, but here we will focus on the LOBFILE method, using one LOB per file.

Download the following documents and place them on the server filesystem. In this example we will use the "/tmp" directory for all files.

Wednesday, 2 August 2017

Multithreaded Model using THREADED_EXECUTION in Oracle Database 12c

Background


On UNIX/Linux systems the Oracle database uses a multiprocess model. For example, a basic installation of the database on a Linux system will have a process list something like this.

Tuesday, 1 August 2017

In-Database Archiving in Oracle Database 12c Release 1

Rather than deleting data, some applications have a concept of "mark for delete", so the data remains present in the table, but is not visible to the application. This is usually achieved by doing the following.
  • Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
  • Add an extra predicate to every statement that checks the deleted status, like "WHERE deleted = 'N'", to exclude the deleted rows from the SQL. The predicate can be hard coded into the SQL, or applied dynamically using a security policy.

Monday, 31 July 2017

Introduction to Azure Cosmos DB

Azure Cosmos DB is one of the latest cloud based service offerings from Microsoft. Azure Cosmos DB is a superset data service of DocumentDB. Earlier, Microsoft offered DocumentDB as Data as a Service (DaaS), which supported a limited set of features and functionalities. Microsoft’s engineers shared their challenges, running the company’s cloud-based services, such as Bing, Azure and Office 365 using DocumentDB. Microsoft understood their engineer’s challenges and marketed the opportunity to take DocumentDB to the next level. As a result, Microsoft come up with Azure Cosmos DB - A globally distributed, multi-model database.

Saturday, 29 July 2017

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.

Friday, 28 July 2017

Backing Up SQL Server Databases Hosted on Azure VMs

In this article, we will provide a more comprehensive overview of the different backup options applicable to Azure IaaS SQL Server workloads.

In general, you have the following options for backing up databases residing on SQL Server instances that are hosted by Azure virtual machines:

Thursday, 27 July 2017

Database Performance Advice for DBAs

As DBAs, we can get mired in the depths of performance tuning parameters and scripts, sometimes getting lost in all the details. It is a good idea to always have a set of goals and philosophies that you can lean on to keep you focused and working on the appropriate things. That is what I want to talk about in this month’s DBA Corner column. Here are six high-level rules of thumb for achieving your DBMS-related performance goals and maintaining your sanity.

Wednesday, 26 July 2017

Planning Database Recovery Options

When a failure occurs, the DBA must ascertain whether recovery is required. It is possible, though not very likely for active databases, that a failure does not impact the integrity of your data. Assuming that recovery is required, the DBA will need to determine what resources (backup copies and log files) are available and how best to perform the needed database recovery. Several questions must be answered to figure out the type and extent of failure. The answers will dictate the steps you take to recover the system.

Tuesday, 25 July 2017

Enhance Your MySQL XML Import Procedures using Prepared Statements

In my Importing XML Data into MySQL Tables Using a Stored Procedure article, I outlined some ways to work around MySQL's restrictions on stored procedures to import XML data into your MySQL database tables. That article covered the basics of formatting your XML files as well as writing your proc to process the data. In today's article, we'll be enhancing our proc to use a Prepared Statement, include error handling and validation, as well as handling additional XML formats.

Monday, 24 July 2017

Importing XML Data into MySQL Tables Using a Stored Procedure

In theory, it would seem that a stored procedure would be the perfect vehicle for importing XML data from a file. In practice, there are several mitigating factors to doing so.  For starters, the LOAD XML INFILE and LOAD DATA INFILE statements have not been permitted to run within a Stored Procedure since MySQL v5.0.7.  On top of that, there are serious limitations in how dynamic you can make your procs; too many to make them as flexible that you'd need them to be in order to handle a variety of input files.  Finally, right now there are no means for mapping XML data to table structures, so you really have to know what's coming in.  But it's not all bad news. If you're willing to target an XML file with a rigid and known structure per proc, you can still get a lot of use out of one call.

Saturday, 22 July 2017

Automating Data Warehouse Support

Big data is here, and fits nicely into an organization's data warehouse infrastructure. Most large IT enterprises have installed one or more big data applications. These applications provide fast access to large stores of data, usually customer or sales data. Your technical staff that supports these applications and the systems that analyze and consume the data didn’t exist ten years ago. In addition, new technologies and new business models using advanced analytics require installation, configuration, and performance tuning.

Friday, 21 July 2017

Speeding up MySQL Data Imports

A Two-pronged Approach


There are two parts to importing data:
  1. Reading the data
  2. Inserting it into tables
We’ll address each of these separately as they are quite distinct from one another.

Thursday, 20 July 2017

Database as a Service: Performance Tuning

Many businesses want to get their latest applications in front of customers as quickly as possible. In order to shorten total development time, they have turned to providers of database as a service (DBaaS) to manage database creation and maintenance. Delegating these resource-intensive tasks to outside experts relieves the business from hardware acquisition and installation, software selection and licensing costs, and staffing for all these functions; however, after the application is up and running what happens as the database gets larger, the number of customers grows, and the transaction load on the system begins to strain resources? Who is responsible for performance tuning, and what pieces should be managed first?

Wednesday, 19 July 2017

The Rise of the Renaissance Database Administrator

Introduction


What do these market-defining trends have in common?

· Analytics for all
· Analytics as competitive differentiator
· Internet of Things
· Artificial intelligence/Machine learning/Cognitive computing
· Real-time analytics/event management

Monday, 17 July 2017

Querying Multiple MySQL Tables

It’s been said that one of the drawbacks to normalization to the third form (3NF) is more cumbersome data extraction due to the greater number of tables.  These require careful linking via JOIN clauses.  Improper table joining can easily result in erroneous results or even in the dreaded Cartesian Product. In today’s article, we’ll explore how table joins are achieved in MySQL.

Saturday, 15 July 2017

Big Data Quality Assurance

With promises of incredibly fast queries, many IT shops implemented one or more big data applications in combination with high-performance hardware and software suites. Results have been generally good: many installations report incredible decreases in query elapsed times, sometimes by factors of 100 or more. Such improvements translated to faster results from analytical queries as well as a better understanding of corporate data.

Friday, 14 July 2017

The Big Data DBA

Many IT enterprises are starting pilot projects to implement big data solutions. As a DBA, are you ready to support these efforts, and integrate them into your current architecture, processes, and standards?

Big Data, DBA

Thursday, 13 July 2017

Top 6 MySQL DBA Mistakes

To err is human, or so the saying goes.  Unfortunately, in the world of IT, innocuous mistakes made early on can lead to really daunting problems down the road. While you can never eliminate human error or bad decisions, there are steps that you can take to minimize the likelihood of finding yourself in a pickle due to a hasty decision arrived at in the spur of the moment or a mistake made early on in design.  In today’s article, we’ll go over a few of the most common DBA mistakes to avoid.  Some of these relate specifically to MySQL, while others may be applicable to any RDBMS.

Wednesday, 12 July 2017

Transactions in MySQL

What is a transaction?


If you are asking this question, you are probably used to website databases, where most often it does not matter in which order you run transactions, and if one query fails, it has no impact on others. If you are updating some sort of content, often you will not care when the update is performed, as long as the reads are being taken care of quickly. Similarly, if an update fails, the reads can still carry on reading the old data in the meantime. However, there are times when it is vitally important in which order queries run, and that all queries in a group run, or none at all. The classic example is from the banking environment. An amount of money is taken from one person's account, and put into another, for example as follows, a 500-unit transaction:

Tuesday, 11 July 2017

Basic DB2 System Tuning Strategies

While the performance of a mission-critical application sometimes takes center stage, IT staff are always aware that the performance of the overall system affects all applications.  Database management systems, operating system software, physical data storage and retrieval, and backup and recovery operations all take part in providing a solid infrastructure for applications. For many customer facing applications such as on-line transaction processing, the database management system is the key component.

Monday, 10 July 2017

DB2 and BigData 2.0

Big data applications start big and keep growing. As the masses of big data being analyzed grow both in size and complexity (think XML data, binary large objects or BLOBs, and URLs of internet page visits), the hardware and software communities have responded with huge storage offerings and massively parallel storage and retrieval mechanisms. The logical next step is for the IT enterprise to take advantage of these technological innovations for things other than classical big data processing.

Saturday, 8 July 2017

Database as a Service: Outlook Cloudy

For most companies, IT-related hardware and software maintenance is costly, time-consuming and requires hiring and retaining a support staff of operating system and database management system specialists. Delegating these responsibilities to an outside firm allows a new application to be developed and implemented more quickly. Enterprises also consider this possibility as a way to purchase only the resources that they need at initial implementation. As the application data and number of users grows, additional IT resources can be purchased as needed.

Friday, 7 July 2017

Database as a Service: Your First Application

For the entrepreneur who wants to implement a significant digitally-based service or sell products to customers on-line, the need to get the application written and implemented quickly is a strong one. The thought of creating and maintaining an information technology infrastructure is daunting. Purchasing hardware, finding a space with appropriate power requirements, installing software, managing leases and licensing agreements, backup and recovery ... the list is long, and getting it all done will be time-consuming.

Thursday, 6 July 2017

How to Find Out Which Database Object Got Deleted

In my last tip, I showed you how to use a transaction log backup file to identify who deleted an object from a database.   In this tip, I will show you how to find out the actual table name for the object deleted.  

I ran the following code to identify who delete tables from my database in my last tip:

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser

Wednesday, 5 July 2017

How to Read a Transaction Log Backup

The transaction log backup is stored in SQL Server proprietary format, just like the transaction log itself. Even though the transaction log backup is in a special format, Microsoft has provided us with the fn_dump_dblog() function to read transaction log backups.   This function is undocumented, so you should use care when using this function in a production environment.

Monday, 3 July 2017

An Introduction to JSON Support in the Oracle Database

This article provides an overview of the JSON functionality available when using an Oracle database, along with links to relevant articles.

  • What's the point?

In today's development world relational databases are considered a legacy technology by many. PL/SQL is also considered a legacy language. With that in mind, how do we as Oracle DBAs and PL/SQL developers stay relevant? One way is to make sure everything we do is easily accessible.

Sunday, 2 July 2017

Getting Started with Azure SQL Data Warehouse - Part 6

Introduction


Azure SQL Data Warehouse is Microsoft’s new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes.  In my last article of the series, I discussed the importance of statistics, how to created them for better performance, workload management and performance optimization guidelines in SQL Data Warehouse. This article will cover the different ways to loading data into SQL Data Warehouse and how they work and perform.

Friday, 30 June 2017

Getting Started with Azure SQL Data Warehouse - Part 5

Azure SQL Data Warehouse is Microsoft’s new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes. In my last article of the series, I discussed round-robin and the distributed table, how partitioning works in SQL Data Warehouse and looked at the impact of choosing the right distribution key. I also talked about PolyBase and how you can leverage it to import data-in or export data-out from SQL Data Warehouse very easily and quickly. In this article, I am going to cover performance optimization in SQL Data Warehouse and the different aspects that impact performance.

Thursday, 29 June 2017

Multitenant : Configure Instance Parameters and Modify Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article shows how to configure instance parameters and modify container databases (CDB) and pluggable databases (PDB).

Wednesday, 28 June 2017

Multitenant : Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article shows how to manage users and privileges for container databases (CDB) and pluggable databases (PDB).

Tuesday, 27 June 2017

Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article demonstrates how to manage tablespaces in a container database (CDB) and pluggable database (PDB).

Thursday, 22 June 2017

Multitenant : Prevent Accidental Creation of a Pluggable Database (PDB) - Lone-PDB

Oracle 12.1 allowed 252 user-defined pluggable databases. Oracle 12.2 allows 4096 user-defined pluggable databases, including application root and application containers. From Oracle 12.1.0.2 onward the non-CDB architecture is deprecated. As a result you may decide to use the Multitenant architecture, but stick with a single user-defined pluggable database (PDB), also known as single-tenant or lone-PDB, so you don't have to pay for the Multitenant option. In Standard Edition you can't accidentally create additional PDBs, but in Enterprise Edition you are potentially one command away from having to buy some extra licenses. This article gives an example of a way to save yourself from the costly mistake of creating more than one user-defined PDB in a Lone-PDB instance.

Wednesday, 21 June 2017

Dynamic Statistics in Oracle Database 12c

Oracle Database 10g introduced Dynamic Sampling to allow the optimizer to gather additional information at parse time if database statistics were missing, stale or insufficient to produce a good execution plan. Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter at instance or session level, or for individual queries using the DYNAMIC_SAMPLING hint, with available values ranging between "0" (off) to "10" (aggressive sampling) with a default value of "2".

Monday, 19 June 2017

Creating a Container Database in 12c

Creating a CDB:


To use the pluggable database feature, we have to specifically create a pluggable enabled CDB.
There are a several different techniques for creating a CDB:

• Manually issuing the SQL CREATE DATABASE command

• Using DBCA

• Generating the required scripts with the DBCA, and then manually running the scripts to create a CDB.

How to Read the Transaction Log File

Have you ever wanted to be able to see the actual transactions that are contained in the transaction log file?  Well there is a way to read the transaction log file using an undocumented function.   In this article, I will show you how to browse the transaction log using this undocumented function.

Thursday, 15 June 2017

Multitenant : PDB Subset Cloning in Oracle Database 12c

The 12.1.0.2 patchset introduced the concept of PDB subset cloning, which allows a subset of all the tablespaces in a PDB to be cloned. Excluding tablespaces can be useful when you want to build a PDB to test a specific piece of functionality, which doesn't require the whole database. It is also useful when splitting instances that were used for consolidation into their individual functional areas.

Wednesday, 14 June 2017

LISTAGG Function Enhancements in Oracle Database 12c Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to perform string aggregations. The LISTAGG function has been enhanced in Oracle Database Release 2 (12.2), allowing it to handle overflow errors gracefully.

Tuesday, 13 June 2017

Logic/Branch Ordering in PL/SQL

PL/SQL evaluates CASE and ELSIF statements from the top down. As a result, placing the most likely outcomes at the top reduces the amount of evaluations that must be done, improving the performance of your code. This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

Friday, 9 June 2017

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."

Thursday, 8 June 2017

Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c

Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI). For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.

Wednesday, 7 June 2017

Multitenant : Metadata Only PDB Clones in Oracle Database 12c

The 12.1.0.2 patchset introduced the ability to do a metadata-only clone. Adding the NO DATA clause when cloning a PDB signifies that only the metadata for the user-created objects should be cloned, not the data in the tables and indexes.

  • Setup


Create a clean PDB, then add a new user and a test table with some data.

CONN / AS SYSDBA

Tuesday, 6 June 2017

Resource Manager : Per-Process PGA Limits in Oracle Database 12c Release 2

Oracle has a long history of improving the management of the Process Global Area (PGA). Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to automate the management of the PGA and set a soft limit for its size. Oracle 11g introduced Automatic Memory Management (AMM), which you should probably avoid. Oracle 12c Release 1 introduced the PGA_AGGREGATE_LIMIT parameter to define a hard limit for PGA size.

Monday, 5 June 2017

Relocate a PDB in Oracle Database 12c Release 2

From Oracle 12.2 onward you can relocate a PDB by moving it between two root containers with near zero-downtime.

Oracle Database Tutorials and Materials

The process is similar to performing a remote clone of a PDB.

Friday, 2 June 2017

Multitenant : PDB Refresh in Oracle Database 12c Release 2

From Oracle Database 12.2 onward it is possible to refresh the contents of a remotely hot cloned PDB provided it is created as a refreshable PDB and has only ever been opened in read only mode. The read-only PDB can be used for reporting purposes, or as the source for other clones, to minimise the impact on a production system when multiple up-to-date clones are required on a regular basis.

Multitenant, Oracle PDB, Oracle Database 12c

Thursday, 1 June 2017

Multitenant : PDBs With Different Character Sets to the CDB in Oracle Database 12c Release 2

In the previous release the character set for the root container and all pluggable databases associated with it had to be the same. This could limit the movement of PDBs and make consolidation difficult where a non-standard character set was required.

In Oracle Database 12c Release 2 (12.2) a PDB can use a different character set to the CDB, provided the character set of the CDB is AL32UTF8, which is now the default character set when using the Database Configuration Assistant (DBCA).

Wednesday, 31 May 2017

Multitenant : Pluggable Database (PDB) Operating System (OS) Credentials in Oracle Database 12c Release 2

There are a number of database features that require access to the operating system, for example external jobs without explicit credentials, PL/SQL library executions and preprocessor executions for external tables. By default these run using the Oracle software owner on the operating system, which is a highly privileged user and represents a security risk if you are trying to consolidate multiple systems into a single container.

Monday, 29 May 2017

Multitenant : PDB Lockdown Profiles in Oracle Database 12c Release 2

A PDB lockdown profile allows you to restrict the operations and functionality available from within a PDB. This can be very useful from a security perspective, giving the PDBs a greater degree of separation and allowing different people to manage each PDB, without compromising the security of other PDBs with the same instance.

Friday, 26 May 2017

Multitenant : PDB Archive Files for Unplug and Plugin in Oracle Database 12c Release 2

In Oracle 12.1 a pluggable database could be unplugged to a ".xml" file, which describes the contents of the pluggable database. To move the PDB, you needed to manually move the ".xml" file and all the relevant database files. In addition to this functionality, Oracle 12.2 allows a PDB to be unplugged to a ".pdb" archive file. The resulting archive file contains the ".xml" file describing the PDB as well as all the datafiles associated with the PDB. This can simplify the transfer of the files between servers and reduce the chances of human error.

Thursday, 25 May 2017

Multitenant : Memory Resource Management for PDBs in Oracle Database 12c Release 2

In the previous release there was no way to control the amount of memory used by an individual PDB. As a result a "noisy neighbour" could use up lots of memory and impact the performance of other PDBs in the same instance. Oracle Database 12c Release 2 (12.2) allows you to control the amount of memory used by a PDB, making consolidation more reliable.

  • PDB Memory Parameters


The following parameters can be set at the PDB level.

Friday, 19 May 2017

Multitenant : Local Undo Mode in Oracle Database 12c Release 2

In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace. In Oracle 12c Release 2 each container in an instance can use its own undo tablespace. This new undo management mechanism is called local undo mode, whilst that of previous releases is now known as shared undo mode. Local undo mode is the default mode in newly created databases, so you will probably only need to consider switching undo modes for upgraded instances.

Thursday, 18 May 2017

Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2

In the initial release of Oracle Database 12c Release 1 (12.1.0.1) remote cloning of PDBs was listed as a feature, but it didn't work. The 12.1.0.2 patch fixed that, but also added the ability to create a PDB as a clone of a remote non-CDB database. The biggest problem with remote cloning was the prerequisite of placing the source PDB or non-CDB into read-only mode before initiating the cloning process. This made this feature useless for cloning production systems, as that level of down-time is typically unacceptable. Oracle Database 12c Release 2 (12.2) removes this prerequisite, which enables hot cloning of PDBs and non-CDBs for the first time.

Wednesday, 17 May 2017

Multitenant : Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2

In Oracle Database 12.1 flashback database operations were limited to the root container, and therefore affected all pluggable databases (PDBs) associated with the root container. Oracle Database 12.2 now supports flashback of a pluggable database, making flashback database relevant in the multitenant architecture again.

  • Enable/Disable Flashback Database


Before we can enable flashback database we need to make sure the database is in archivelog mode. You must do this from the root container.

Tuesday, 16 May 2017

Multitenant : Disk I/O (IOPS, MBPS) Resource Management for PDBs in Oracle Database 12c Release 2

In the previous release there was no easy way to control the amount of disk I/O used by an individual PDB. As a result a "noisy neighbour" could use up lots of disk I/O and impact the performance of other PDBs in the same instance. Oracle Database 12c Release 2 (12.2) allows you to control the amount of disk I/O used by a PDB, making consolidation more reliable.

  • I/O Parameters (MAX_IOPS, MAX_MBPS)


The following parameters can be set at the CDB or PDB level to throttle I/O at the PDB level.
  • MAX_IOPS : The maximum I/O operations per second for the PDB. Default "0". Values less that 100 IOPS are not recommended.

Monday, 15 May 2017

Multitenant : Default Tablespace Clause During PDB Creation in Oracle Database 12c Release 2

This article describes the change in behaviour of the DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE command between Oracle database 12.1 and 12.2.

  • Default Tablespace Clause in 12.1

In both Oracle database 12.1 and 12.2 the DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE command can be used to create a new default tablespace for a pluggable database created from the seed.

Friday, 12 May 2017

Multitenant : Application Containers in Oracle Database 12c Release 2

Application containers are a new feature in Oracle Database 12c Release 2 (12.2) that allow you to define an application root, like a mini CDB root container, along with dependent application container PDBs. An application root can house one or more applications, each made up of shared configuration, metadata and objects that are used by the pluggable databases associated with the application root.

Thursday, 11 May 2017

Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2) Part- 2

  • Manual (SQL*Plus)


There are lots of variations on the CREATE PLUGGABLE DATABASE and ALTER PLUGGABLE DATABASE commands, so we will keep things simple here and only focus on those that mimic what is possible in the DBCA.

For all the operations listed here you must be connected to the CDB with the container set to root (the default). Typically you will be connected to a common user with SYSDBA or SYSOPER privilege. When creating a new pluggable database, the user must have the CREATE PLUGGABLE DATABASE system privilege.

Wednesday, 10 May 2017

Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2) Part- 1

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article covers the options available to create a new pluggable database in an existing container database.

  • Oracle Universal Installer (OUI)

In a previous article we discussed the creation of a pluggable database (PDB) during the creation of the container database (CDB) during the installation of the Oracle software using the Oracle Universal Installer (OUI).

Thursday, 4 May 2017

Database Migration Assistant for Unicode (DMU) in Oracle Database 12c

The Database Migration Assistant for Unicode (DMU) is listed as a 12c new feature, but it is actually a separate tool that supports a number of different database versions.

  • Download


Version 1.2 of the DMU is shipped with the 12c database. You can see it by running the following commands.

$ cd $ORACLE_HOME/dmu
$ sh dmu.sh
Oracle recommend you always download the latest release before starting a migration. At the time of writing, version 2.0 is available at OTN.

Monday, 1 May 2017

Multitenant : Create and Configure a Container Database (CDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article covers the options available to create a new container database.

  • Oracle Universal Installer (OUI)

The Oracle Universal Installer (OUI) allows you to create a container database (CDB) during the software installation. The "Typical Install Configuration" screen has a checkbox to indicate the database is a container database. You can optionally create a single pluggable database (PDB) in this screen also.

Sunday, 30 April 2017

Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article demonstrates how to startup and shutdown container databases (CDB) and pluggable databases (PDB).

  • Container Database (CDB)


Startup and shutdown of the container database is the same as it has always been for regular instances. The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user. Some typical values are shown below.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

Friday, 28 April 2017

What is Multitenant Architecture and Its Benefits Pluggable and Container Databases

What is Multitenant Architecture?  


This feature allows you to create and maintain many pluggable databases within an multitenant container database.

Multiple tenants share same resources on a mutual benefit for different purposes at a very broad level. The same applies to Oracle Database where Multiple Databases share a single instance of resources aiming for different purposes on the same Server. This Oracle Database which is built on Multitenant foundation is called Container Database(CDB), and each container(tenant) residing inside is called Pluggable Database(PDB, Container).

Monday, 24 April 2017

Getting Started with Azure SQL Data Warehouse - Part 4

Introduction


Microsoft introduced Azure SQL Data Warehouse, a new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes. In my last article of the series, I talked about the different types of tables we can create in SQL Data Warehouse and how it impacts performance and best practices around them. I also demonstrated how to get started with creating a SQL Data Warehouse database and to connect to it from SQL Server Management Studio (SSMS). In this article, I am going to talk in detail about round-robin and distributed tables, and how to create them. I will also discuss how partitioning works in SQL Data Warehouse and look at the impact of choosing the right distribution key. I will also cover PolyBase in detail and how you can leverage it to easily and quickly import or export data from SQL Data Warehouse.

Thursday, 20 April 2017

Database as a Service: A Practicum

Vendors now offer Database as a Service (DBaaS) as part of a bundled solution of managed IT services. The selling point is that businesses will not need to purchase and install database software, create and maintain databases, hire database administrators, or worry about upgrades and scaling up when their business expands. Delegating database management services to an external provider may speed up development time, and allows a company to pay only for the data they store and the database services they consume.

However, delegating database administration services has many hidden dangers. In this article, we focus on how delegating data modeling to an outside service can cause problems when making application updates, changing business rules or doing performance tuning.

Thursday, 13 April 2017

Big Data DBA Support Strategies

The Database Administrator (DBA) is usually a technical professional who supports one or more hardware and software platforms that provide application solutions. However, technical details such as SQL tuning, hardware and software upgrades, and database designs tend to be tactical in nature.  It is essential that the DBA also maintain a strategic outlook in order to get ahead of potential problems. This article addresses two of these strategies: knowing application breaking points and preparing for future enhancements to big data applications.

Wednesday, 12 April 2017

The Problem with SQL Calling PL/SQL Calling SQL

The reduction in query execution time was ten seconds. The observant among you will have noticed that the statement processed over 60,000 rows. That's a saving of less than 200 microseconds per row.

Oracle Database PL/SQL, SQL, Oracle Database Certifications

Tuesday, 11 April 2017

Oracle Data Warehouse Solutions: Delivering Business Value From The Datacenter To The Cloud

Leading-edge organizations differentiate themselves through analytics, and are further expanding their competitive advantage with new data sources.  This initiative does include several obstacles and challenges.  Today’s digital world is already creating data at an explosive rate, but the next wave is on the horizon, and this momentum is driven by the emergence of IoT data sources.  These sources include connected cars, smart-appliances, wearable technology and industrial sensor data.  As a result, IT leadership is faced with two diametrically opposed forces.  On one hand, IT resources are consumed dealing with the operational challenges associated with the constant acquiring, deploying and maintaining of necessary infrastructure to support data consumption.  On the other hand, IT leadership is pulled in the opposite direction to support evolving requirements of line-of-business owners.  These polarizing pressures create a bitter dichotomy where IT attempts to address both demands, but becomes incapable of doing so to the satisfaction of all stakeholders.

Monday, 10 April 2017

PL/SQL Object Types for JSON in Oracle Database 12c

  • Introduction

In Oracle Database 12c Release 1 the JSON functionality was focused on consuming JSON data and converting it into relation data. Generation of JSON relied on string handling or packages such as the APEX_JSON package. Oracle Database 12c Release 2  includes new JSON object types to support the in-memory parsing, generation and update of JSON data directly from PL/SQL.
  1. JSON_ELEMENT_T : The supertype some of the other object types extend. You will not often use this type directly. You can cast a JSON_ELEMENT_T to a subtype using TREAT AS. For example, "l_obj := TREAT (l_elem AS JSON_OBJECT_T);"
  2. JSON_OBJECT_T : An object representing a JSON object.

Wednesday, 5 April 2017

Node.js and Oracle NoSQL Database

Oracle NoSQL Database is an interesting option to consider when you want a schemaless, fast, scale-able database which can provide relaxed (eventual) consistency. Oracle provides a Node.js driver for this database. In this blog I’ll describe how to install Oracle NoSQL database and how to connect to it from a Node.js application.

The Node.js driver provided by Oracle is currently in preview version 3.3.7. It uses NoSQL client version 12.1.3.3.4 which does not work with 4.x versions of NoSQL database, so I downloaded Oracle NoSQL Database, Enterprise Edition 12cR1 (12.1.3.3.5) from here (the version number was closest to the version number of the client software).

Tuesday, 4 April 2017

How to Move a Table to a Different File Group

There may be a time when you want to move a table from one file group to another.  There could be lots of different reasons for wanting to move your data file.  One of the reasons why you might want to do this is to improve performance.  It is easy to move a table from one filegroup to another if it contains a clustered index.

To show you how to move a table from one file group to another let me create an example.

First let me create a sample database using the following code:

Saturday, 1 April 2017

Oracle for Absolute Beginners: Data Types

All databases stand on a tripod of datatypes: strings, numbers and dates. And so I’d imagine that by Day 2 or so of dabbling with Oracle you’d be starting to get reasonably comfortable with the VARCHAR2, NUMBER and TIMESTAMP data types. And that’s a good thing, familiarity with those three data types will take you a long way into a career of working with the Oracle database.

However, there are a legion of ‘second string’ data types that you’ll probably want to be passingly familiar with too. It’s kinda like when you meet someone, and fall in love. Sure you want to know every single thing about the gorgeous person you’re in love with – but to really know them, don’t you need to know a bit about the family they come from too?

Tuesday, 21 March 2017

Getting Started with Azure SQL Data Warehouse - Part 3

Introduction


Microsoft introduced Azure SQL Data Warehouse, a new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes. In my first article of the series, I talked about traditional ways of creating data a warehouse and the challenges with it in the current scenario, then discussed Azure SQL Data Warehouse, and how it helps in meeting the changing demands for the current business. In my second article of the series, I covered the architecture of Azure SQL Data Warehouse in detail and how you can scale up0 or down based on your need. In this article I am going to talk about the different types of tables we can create in SQL Data Warehouse, how they impact the performance and best practices around them. We will also get started with creating our first SQL Data Warehouse database, etc.

Wednesday, 15 March 2017

Getting Started with Azure SQL Data Warehouse - Part 2

Introduction


Microsoft introduced Azure SQL Data Warehouse, a new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes. In my last article of the series, I discussed the traditional way of creating a data warehouse and the challenges associated with it in the current scenario. Then we talked about Azure SQL Data Warehouse, and how it helps meet the changing demands for current business. In this article, I will cover the architecture of Azure SQL Data Warehouse and how you can scale up or down, based on your need.

Wednesday, 8 March 2017

Getting Started with Azure SQL Data Warehouse - Part 1

Introduction


As the demand for data analytics grows so does the need for a technology or platform to process large amounts of different types of data in timely manner. A platform that can be easily scaled up or down, based on increased or decreased demand, has become an imminent need for all those organizations that want to do data analytics on a large scale of varied data.

Microsoft introduced Azure SQL Data Warehouse, a new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just a few minutes. In this article series, we will dive deep into this data warehouse-as-a-service offering from Microsoft.

Friday, 3 March 2017

Oracle for Absolute Beginners: Users, Synonyms, Privileges, & Roles

I accidentally locked myself out of my house this evening, and so I had to climb up the wall like a burglar and clamber in through a bathroom window, while the neighbourhood kids all watched and giggled, probably taking secret photos to share on Snapchat or something.

Embarrassing, yes, but what’s it got to do with Oracle? Well, the point is this: no one called the police.

And that’s because it’s my house. And while having gifs on Instagram of my bum squeezing through a small window might be embarrassing, we all understand that I can do whatever I please with my property.

Thursday, 23 February 2017

Getting started with Oracle Database 12c Multitenant Architecture

Oracle database, since its inception, has always used a very specific and unique architecture which served it very well. In earlier forms of this architecture, all components of the database were meant to work in a one-to-one mapping with each other. But from version 12c onwards, a completely new architecture has been introduced – Oracle Multitenant. In other words, from 12c onwards, there are two ways to create a database, as a multitenant database or a pre-12c non-multitenant database. In this series, we will learn how this new architecture works, build components within the new architecture, and learn how to manage different aspects of it, such as backup and security. In this first article, we’ll learn about the necessity of this new architecture and about its core components.

Wednesday, 22 February 2017

Policy-Managed Oracle RAC One Node Databases

Oracle RAC One Node, introduced in Oracle Database 11g Release 2 (11.2.0.2), is a single instance of an Oracle RAC-enabled database running on one node in a cluster. It lets you consolidate multiple databases onto a single cluster while providing high availability benefits of failover protection to the single instance databases.

Oracle RAC One Node databases can be configured to be administrator-managed or policy-managed.

Administrator-managed Oracle RAC One Node Database: The database instance executes on one of the servers from the specified list of candidate nodes. Candidate servers reside in the Generic server pool and as long as at least one server from the candidate list is available, database availability is ensured.

Tuesday, 21 February 2017

Oracle for Absolute Beginners: Date, Timestamp and Interval

All databases stand on a tripod of datatypes: strings, numbers and dates. And though they might dress them in fancy clothing – varchar2, clob, float, integer – strings are really just strings, and numbers are really just numbers. But dates — dates are interesting.

In this article I’ll talk to you about dates, about time, and about how both are captured and calculated in an Oracle database.

The DATE Datatype

DATE is the main – or rather, original – datatype used in Oracle for holding dates. Beneath the plainness of its name, it hides a little depth.  Firstly, for example, it doesn’t really hold a date, instead it records a datetime. It’s a seven byte store of century, year, month, day and hour, minute and second.

Wednesday, 11 January 2017

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c

The PDB CONTAINERS clause allows data to be queried across multiple PDBs.

Setup

We need to create 3 PDBs to test the CONTAINERS clause. The setup code below does the following.
  • Creates a pluggable database called PDB1.
  • Creates a PDB1 with a local user called LOCAL_USER that owns a populated table called LOCAL_USER_TAB.
  • Creates two clones of PDB1 called PDB2 and PDB3.

Tuesday, 10 January 2017

Oracle Data Type Implicit Conversion Hierarchy

In Oracle, under certain circumstances, an implicit data type conversion precludes the use of indexes. Perhaps you have a vague idea of what an implicit data type conversion hierarchy is, and you might even ignore the subtlety of the implicit data type conversion direction within a query predicate expression. Hopefully this article will shed some light on this concept.

Monday, 9 January 2017

Interval-Reference Partitioning in Oracle Database 12c

Oracle 12c lifts that restriction, so you can now use interval-reference partitioning.

Interval-Reference Partitioning

The following code creates an interval partitioned table (T1) with yearly partitions and an interval-reference partitioned table (T2).

DROP TABLE t2 PURGE;
DROP TABLE t1 PURGE;

Friday, 6 January 2017

Asynchronous (Delayed) Global Index Maintenance for DROP and TRUNCATE Partition in Oracle Database 12c

Oracle 12c can optimize the performance of some DROP PARTITION and TRUNCATE PARTITION commands by deferring the associated index maintenance, while leaving the global indexes in a valid state.

1. Setup

The following code creates and populates a partitioned table with global indexes.

-- Create a partitioned table with some global indexes.
DROP TABLE t1 PURGE;

Thursday, 5 January 2017

Edition-Based Redefinition Enhancements in Oracle Database 12c Release 1

Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time. Editionable object types include:
  • FUNCTION
  • LIBRARY
  • PACKAGE and PACKAGE BODY
  • PROCEDURE
  • TRIGGER
  • TYPE and TYPE BODY
  • SYNONYM
  • VIEW

Wednesday, 4 January 2017

Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 7 (OL7)

Oracle Linux 7 is a production release, but Oracle Database 12c is only supported on it from (12.1.0.2) onward. This installation should not be used for a real system when using 12c database versions prior to 12.1.0.2.

This article describes the installation of Oracle Database 12c Release 1 (12.1) 64-bit on Oracle Linux 7 (OL7) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

Tuesday, 3 January 2017

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT.

Monday, 2 January 2017

Reading Data in Oracle Database 12c

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?