Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to (more...)

12cR1 RAC Installation on OEL7

To build Oracle Clusterware Database at Home, I believe , RAC ATTACK is the best place to learn. Its is a free curriculum and platform for hands-on learning labs related to Oracle RAC. While reviewing the article, I thought to perform 12cR1 RAC installation on OEL 7.2.

Attached is the document :- 12c_RAC_on_OEL7

The attached article is inspired by

RAC ATTACK :- https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_12c

Tim Hall’s article :- https://oracle-base.com/articles/12c/oracle-db-12cr1-rac-installation-on-oracle-linux-7-using-virtualbox 

Deploying Oracle RAC (more...)

Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Dead Connection Detection in Oracle Database 12c

In my earlier post I discussed what Dead Connection Detection is and why you should use it – read more here Oracle TNS-12535 and Dead Connection Detection

The pre-12c implementation of DCD used TNS packages to “ping” the client and relied on the underlying TCP stack which sometimes may take longer. Now in 12c this has changed and DCD probes are implemented by TCP Stack. The DCD probes will now use the TCP KEEPALIVE socket (more...)

Hello Noida-Are You Attending Oracle Cloud Day….

Are you living and/or working in Noida or any place near to it? Do you happen to work with Oracle technologies especially database and it’s related ones? Are you curious to understand technically about Oracle’s Cloud Computing services? Well, then you must attend,

Oracle Cloud Day 26th March 2016

In a first of it’s kind event happening at Oracle’s Noida office, would be bringing you the information about three different aspects of Oracle’s Cloud services-Oracle (more...)

Hello Noida-Are You Attending Oracle Cloud Day….

Are you living and/or working in Noida or any place near to it? Do you happen to work with Oracle technologies especially database and it’s related ones? Are you curious to understand technically about Oracle’s Cloud Computing services? Well, then you must attend,

Oracle Cloud Day 26th March 2016

In a first of it’s kind event happening at Oracle’s Noida office, would be bringing you the information about three different aspects of Oracle’s Cloud services-Oracle (more...)

Client support for WITH using PL/SQL

My employer has been using 12c for about a year now, migrating away from 11gR2. It's fun working out the new functionality, including wider use of PL/SQL.

In the 'old' world, you had SQL statements that had to include PL/SQL, such as CREATE TRIGGER, PROCEDURE etc). And you had statements that could never include PL/SQL, such as CREATE SYNONYM, CREATE SEQUENCE. DML (SELECT, INSERT, UPDATE, DELETE and MERGE) were in the latter category.

One of (more...)

AWR Warehouse – security issue

During implementation of AWR Warehouse, I discovered that AWR warehouse is using temporary staging schemas in the AWR warehouse repository database. These schemas life approximately for the duration of a datapump import job and are then dropped again. Due to the fact that the used password is not compliant with customers password verification function, the jobs failed.

v_sql := ‘ CREATE USER ‘ || STAGING_SCHEMA || ‘ IDENTIFIED BY SYS_GUID ‘ ||
‘ DEFAULT TABLESPACE (more...)

Issues with Oracle Direct NFS

This is a quick post to highlight two issues I had with Oracle dNFS. Both relate to wrong entries in the oranfstab file.

One might encounter ORA-00600 during database creation:

DBCA_PROGRESS : 7%
DBCA_PROGRESS : 8%
ORA-01501: CREATE DATABASE failed
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], [], [], [], [], []
ORA-17502: ksfdcre:3 Failed to create file /oracle/ORCL/database/ORCL/controlfile/o1_mf_%u_.ctl

This was caused by having a wrong entry in oranfstab (more...)

DOAG 2015: Best of Oracle Security 2015

Yesterday I gave my yearly presentation “Best of Oracle Security 2015” at the DOAG 2015 conference in Nürnberg. In this presentation I showed different Oracle exploits I found/modified released in 2015 in various sources.

One of the most interesting Oracle bugs in 2015 was CVE-2014-6577 (found by Trustwave, affecting 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.02, fixed in April 2015 CPU). This bug can be used as helper (more...)

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on 11.2.0.4

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

Introducing Oracle ASM Filter Driver

The Oracle ASMFD (Filter Driver) was introduced in Oracle Database 12.1.0.2 and as of the moment it is available on Linux systems only.

Oracle ASM Filter Driver is a kernel module very much like the ASMLIB that resides in the I/O path of the Oracle ASM disks. It provides an interface between the Oracle binaries and the underlying operating environment.

Here are some of the features of ASMFD:

  • Reject non-Oracle I/O

The (more...)

What AWR isn’t telling you

It is well-known that AWR, and Statspack before, take snapshots of V$ views (or rather the underlying objects) to produce the data in AWR.

It is also well-known that, when considering sql and its statistics in the shared pool, if something big hitting happens but the big-hitter is no longer in the shared pool by the time of the snapshot, then it can’t be recorded in your AWR picture of activity.

But like many things (more...)

Quick Cross-Period AWR Time Model Comparison

Here’s a query which I find useful in order to have a very quick comparison across AWR snapshots of the high level time model statistics.
The numbers should match those in the associated section in the AWR report.

If you feel compulsed, obsessively, with tuning then you may see some blips here and there which then encourage you to dive into the AWR detail for that snapshot.

Or quite often I get in in the (more...)

Online Partition Move fails with ORA-00932 – 12.1.0.2

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from 11.2.0.4 to 12.1.0.2 and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition  (more...)

PDB Saved state – 12.1.0.2

Prior to 12.1.0.2 version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from 12.1.0.2, this can be done with PDB save state feature

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			     READ ONLY  NO
	 3 ALPDB			     READ WRITE NO

Lets create a new PDB

 (more...)

SQL Plan Management Choices

My thoughts on SQL plan management decision points:

SPM

SQL Patches are also available and not covered in the above flowchart.


Upgrade to Oracle 12c, Get the Huge Trace Files for Free!

Last week we began testing a copy of our production database on Oracle 12c (12.1.0.2). This past weekend we were alerted that the disk holding our ADR diagnostic directory was near full. We noticed some pretty big (and recent) trace files there. Then it happened twice more. This last time filled the disk before we could get to it (thankfully only dev, and during the evening), meaning it filled up fast. The (more...)

12.1.0.2 PDB fails to come out of restricted mode

This one is a nasty bug I was trying to setup Oracle PDB in a test environment for the first time and got stuck with ORA-01035 error [oracle@oracle11g ~]$ sqlplus hr/hr@//oracle11g:1522/engg SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 3 07:34:58 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01035: ORACLE only available to users with…