Bug 18411339 – Low performance V$ARCHIVE_GAP ( fix

Just came across bug from not fixed until 12.2 base release. Seriously Oracle?
In the test case below, it looks to have only affected – 64bit for AIX Version 7.1 since I recall this was not an issues for Linux.
select * from v$archive_gap;
Elapsed: 00:01:48.93
select * from v$archive_gap;
Elapsed: 00:00:06.60

Work (more...)

Certification : OCP 12c and OCP 12cR2 Updates

Just a couple of certification related updates.

Upgrade to oracle Database 12c (1Z0-060)

About a week ago I finally got round to doing the 12c certification. As followers of the blog/website know I’ve been writing articles about Oracle Database 12c since it was released nearly 4 years ago and for pretty much all of that time I’ve been trying to convince myself to sit the 12c OCP DBA upgrade exam. It’s not that I care about the (more...)

Setting up Oracle Database on Docker

A couple of days ago it was announced that several Oracle images were available on the Docker Store.

This is by far the easiest Oracle Database install I have every done !

You simply have no excuse now for not installing and using an Oracle Database. Just go and do it now!

The following steps outlines what I did you get an Oracle 12.1c Database.

1. Download and Install Docker

There isn't much to say (more...)

Intra-block row chaining optimization in 12.2

I’ve wrote in previous post

4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and previous version using example 4 from previous post:

Test 4

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';

Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace (more...)

Be Prepared When Using DBFS

Goldegate 12c implementation for 2-node RAC with XAG from Grid Home using DBFS.

We are facing situation where DBFS has 41G free while DBFS_TS has 1.5G free and having to resize BIGFILE tablespace.

Tablespace and DBFS size is 45G.

Basically, 45G tablespace is used to maintain 4G Goldengate files.

Based on steps followed from NOTE:1453350.1 – How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?

Next step (more...)

Goldengate 12c Troubleshooting XAGENABLE

Oracle RAC with DBFS running as oracle
Goldengate12c running as ggsuser using DBFS and XAGENABLE
Monitoring is running as monitor

ERROR: Transparent Integration with XAG is Enabled but CRS/XAG is not Available (Doc ID 2240440.1)

Modify GLOBALS per Doc ID 2240440.1


setenv (GRID_HOME='/u01/app/12.1.0/grid')

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version OGGCORE_12.
Linux, x64, 64bit  (more...)

Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 3

Normal 0 false false false EN-US X-NONE X-NONE Introduction This post covers how Oracle has improved incremental statistics for partitioned tables in Oracle Database... [Read More]

12cR2 tightens up ORA-01841 for zero year ANSI dates, but not for Oracle SQL syntax

In moving some more code from an 11gR2 database to a 12cR2 database, I found another change where a piece of code that works in 11gR2 doesn’t compile in 12cR2.

In this instance a view was being created with a projected date column which used the ANSI DATE syntax. Here is a simplified test script:

SELECT date '0000-01-01' date_col
FROM dual


The latest in Oracle Partitioning – Part 2: Multi Column List Partitioning

This is the second blog about new partitioning functionality in Oracle Database 12c Release 2, available on-premise for Linux x86-64, Solaris Sparc64, and Solaris x86-64 and for everybody else in the Oracle Cloud . This one will talk about multi column list partitioning, a new partitioning methodology in... [Read More]

Oracle Database 12c Release 2: Analytic Views & SQL Developer

One of the major new features in Oracle Database 12c Release 2 is Analytic Views. An analytic view is a type of view that can be used to easily extend the content of a star schema with aggregated data, measure calculations and descriptive metadata, and to simplify the SQL needed to access data. Analytic views layer a... [Read More]

Updated SQLcl: 2 New Commands, Bug Fixes, and 12cR2 Oh My!

You have a new version of SQLcl to download this morning. Lots and lots of bug fixes – again, thanks everyone in the community for providing feedback so we can make continuous improvements. A few things of note: Two New Commands They do pretty much what you’d expect. FIND and WHICH – hopefully will... [Read More]

Non-CDB still exists in Oracle Database 12.2.

I received a question from 3 different colleagues in the past week: "Does Oracle Database 12.2 still allow us to create non-CDB databases?" And as I get this question at every customer-facing workshop these days as well before being able to talk about Single/Multitenant - I think it is important to... [Read More]

Using HISTORY in SQL*PLUS 12.2….

One of the biggest things that we couldn’t do in the previous versions of the oracle database was to use the back arrow button-to reexecute the old commands, without typing them. Of course, a workarouns is possible in the form of rlwrap but it’s not comparable to a natively available feature in the SQL*PLUS. Well, until now because now, we have HISTORY option available in 12.2 SQL*PLUS. Let’s take a look.

SQL> /

BANNER                         (more...)

In case you miss the downloads of Oracle Database on OTN

Somebody asked me the other day why the download options for Oracle Database for Linux and Solaris  disappeared from OTN? The same will happen with the other platforms as soon as Oracle Database is available on-premises for them as well. You can always download Oracle Database [Read More]

Converting an 12.1 non-CDB and plug it into an 12.2 CDB

2 customers requested a bit more explanation on how to:Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database Cool, I like that I will do this right away and use the approach to Upgrade first, then Plugin afterwards.. Upgrading an Oracle database to Oracle ... [Read More]

3 Oracle Database 12c Courses You Need to Get Started

Three Oracle courses in particular will help you get started on Oracle Database 12c:

The Oracle Database 12c R1: New Features for Administrators training will provide you with up-front information about (more...)

Oracle Database 12c Release 2 is Now Available…and so is our New VirtualBox Appliance!

Want to get started on learning everything there is to know about the latest version of our Database? Don’t want to really have to install or create anything? Well, you’re in luck. Just go download our image. The image is up and running, complete with sample data for you to play with. In a few minutes,... [Read More]

Install components in Multitenant ALWAYS with catcon.pl

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.. How to install a component afterwards in Single-/Multitenant? I haven't checked all the MOS Notes but I recognized that some MOS Notes explaining how to... [Read More]

12.2 .. not even 24 hours available on-premise and the first “bug” .. but a good one ..

We missed to document some functionality !!! With the next milestone for Oracle Database 12c Release 2 just taking place - the availability on premise for Linux x86-64, Solaris Sparc64, and Solaris x86-64, in addition to the Oracle Cloud - I managed to use this as an excuse to play around with it for a bit .. and... [Read More]