Gimmick with offset and fetch N rows first in 12c

I was looking into some new features of 12c and I didn’t need to dig deep, in order to figure out how does the “fetch first” feature work:

QL> set autotrace on
SQL> select ename from emp offset 5 rows fetch next 5 rows only;

ENAME
———-
BLAKE
CLARK
SCOTT
KING
TURNER

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 3611411408

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (more...)

Still the same cluster limitations

I am slightly disappointed to report that the new extended data types cannot be used in the clustered tables. CLOB columns have never been allowed, and are still not allowed in 12c, but I expected the new data types to pass seamlessly. That is not the case:

SQL> create cluster testclu(intkey number(30,0));

Cluster created.

Elapsed: 00:00:00.03
SQL> create index testclu_ind on cluster testclu;

Index created.

Elapsed: 00:00:00.03

Now, let’s create a normal table (more...)

SYSBACKUP travails, part II

The SYSBACKUP connectivity problems described in the previous post are caused by unpublished bug 15828768.

There is a way to connect, using SYSBACKUP privilege:

 

RMAN> connect target ‘system@local as sysbackup’

target database Password:
connected to target database: ORA12C (DBID=214280212, not open)

Please, note the quotes around the connection string. Without the quotes, this will not work. It works with both single and double quotes:

RMAN> connect target “system@local as sysbackup”

target database Password:
connected (more...)

Quirks with the SYSBACKUP privilege in 12c

Oracle 12c has a great new privilege, tailor made for the paranoid oracle DBA guys like me. However, there are still some quirks: SYSBACKUP privilege doesn’t work over Oracle*Net. If the authentication method is SID based local authentication, all is well:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
(more...)

Missing Information

Since the version 11G it is possible to exclude committed blocks from specific tablespace from being written to flashback recovery area. FBDA process writes committed blocks to flashback area and is quite IO intensive. There are, among other things, flashback checkpoints as well. They occur when when flashback logs fill (more...)

Multi-threaded Oracle 12c architecture on Linux

Introduction

Oracle has long had so called multi-threaded architecture on Windows. On the Unix platforms, Oracle was using multi-process architecture until version 12c. The difference is that Oracle server processes in the threaded architecture become threads of a single process. The reason for such an architecture is lower overhead caused (more...)

Pidstat

Recently, I’ve been faced with a strange problem: around 10AM every morning, my database was showing signs of IO contention. Wait times for db file sequential and multipblock reads were increasing for no apparent reason and there were no signs of increased activity reported by the users. Fortunately, my system (more...)

Expert consolidation in Oracle database 12c

Today, I entered the term “Oracle Database 12c” in the books section of Amazon.com, my favourite, and was surprised by the results. There are few books announced for April 2012, some for May and June 2013, without the database even being available to the public. Expert consolidation in 12c? (more...)

The Case of Missing Utility

Recently, I’ve asked to help a client who was having doubts about their backup strategy. The client was considering image backup of database which would be constantly recovered and backed up to tape on the daily basis, by using “backup recovery area” command. However, there was a problem: there is no “restore recovery area” command. The utility to restore the recovery area seems to be missing, thus the name of this article became “The Case of Missing Utility”.

So, let’s see some definitions. This is what the client has envisioned:

RMAN> backup incremental level 1 for recover of copy database;

(more...)

ASM, block devices and SCSI emulation.

Quite recently, I learned that Oracle ASM is now supporting block
devices. The whole process is described here:

http://docs.oracle.com/cd/E11882_01/install.112/e17212/storage.htm#CDEBFDEH

This deserves few additional observations. The main engine that enables
one to add block devices to SCSI is udev. Essentially, one creates SCSI
devices by using a configuration file like this one, pasted directly
from the manual above:

# vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL==”sd?1″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id”,
RESULT==”14f70656e66696c00000000″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″
KERNEL==”sd?2″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id”,
RESULT==”14f70656e66696c00000001″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″
KERNEL==”sd?3″, BUS==”scsi”, PROGRAM==”/sbin/scsi_id”,
RESULT==”14f70656e66696c00000002″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″

What does that do? This enables udev to create “SCSI (more...)