Crossplatform transportable tablespaces – part 2

It took some time since I wrote a first post about TTS migration but I finished that project literally hours before my summer break. Now after couple of days while I enjoyed thermal waters and good wine of Hungary it's time to write next post.

As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC (more...)

Remote DML with DBMS_PARALLEL_EXECUTE

A comparison of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.

This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.

I think it’s a good example of (more...)

die Seilerwerks 2014-08-21 13:35:00

Fresh off the heels of my earlier composite partitioning post, I just ran into this confusing issue:

SQL> alter table p_objects
  2          add partition p201410
  3                  values less than (to_date('2014/11/01','yyyy/mm/dd'))
  4          (
  5                  subpartition p201410_spdts values ('DTS')
  6                 (more...)

Adding New Partitions with Custom Subpartition Definition (Range-List)

As part of a project for work I wanted to create a script that would create a new range partition but also pre-create all the list subpartitions. By default the subpartitions would be created based on the subpartition template. However for various reasons which I won't get into we don't update or use the subpartition template. I wanted to define the subpartition list as part of the ALTER TABLE ... ADD PARTITION statement. I assumed it (more...)

Plan Instability

There seems to me to be a relatively simple choice.

Either you except that the Oracle Optimizer has a wealth of complicated strategies and, in this complex effort to get the best executions it can, will inevitably either get it wrong sometimes (or take some extra executions to realise it’s wrong).

Or you stick your head in the sand and raise a bug for every unexpected poor execution or plan flip.

But let’s say that (more...)

11.2.0.4 DBUA silently changing NLS_TERRITORY init.ora Parameter during upgrade

In case you are planning to perform any upgrades with DBUA, double-check that the init.ora parameters after the upgrade. In a production upgrade at a client site, dbua silently changed init.ora parameter NLS_TERRITORY from GERMANY to AMERICA. It was only noticed when after the upgrade, decimal and grouping number characters were swapped, leading to application problem.

Support had to admit that it is unpublished bug 16538186 and that there is NO documentation of (more...)

ASM Startup Fails With ORA-04031 After Adding CPUs

A few weeks ago we upgraded one of our production server, adding another CPU tray. This brought the number of CPU cores from 80 to 160, and took us from 2Tb of RAM to 4Tb (just in time for Oracle to announce the in-memory database in 12.1.0.2!).

However when I went to start things up, ASM wasn't starting up, giving me these errors:

ORA-04031: unable to allocate 32 bytes of shared (more...)

De cómo un clon nos puede ayudar con un patch (updated to 12c)

Cada vez que vamos a aplicar un patch o un patch set update (PSU), el procedimiento tradicional implica detener todos los procesos que se estén ejecutando en el Oracle home directory involucrado, lo cual obliga a que dejemos de prestar servicio para iniciar el patching, proceso que si bien es relativamente fácil y sencillo, está sujeto a eventuales fallas que, de ocurrir, pueden llevar a una situación de crisis en la cual el (more...)

Don’t delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014

Copyright (more...)

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

Conditional uniqueness

A quick fly through the options for conditional uniqueness.

Requirement #1: I want uniqueness on a column but only under certain conditions.

For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.

Initial setup:

create table t1
(col1      number       not null
,col2      varchar2(24) not null
,is_active number(1)    not null
,constraint pk_t1 primary key (col1)
,constraint  (more...)

Audit space threat – a perfect storm

According to http://en.wikipedia.org/wiki/Perfect_storm:

A “perfect storm” is an expression that describes an event where a rare combination of circumstances will aggravate a situation drastically.

The other day we had a bit of a panic when AUD$ space grew by 45GB in 15 minutes and set off some alerts.

The audit_trail parameter was set to DB, EXTENDED.

which means:

Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text (more...)

Beware April 2014 PSU and Golden Gate Integrated Capture Users

When the latest quarterly Patch Set Update (PSU) came out from Oracle, we planned to get it in place as soon as reasonable due to our need to stay current with security patches, and my need to apply what I had assumed were well-tested bug fixes for our 11.2.0.3 installations. However we were in for an unpleasant surprise.

We first applied the April 2014 PSU on our staging & development database hosts (more...)

archive_lag_target Works in SE

TL;DR: The archive_lag_target parameter will force log archiving in Standard Edition.

Just a quick note here that I wanted to share since I didn't see anything directly confirming this when I was searching around.

I have an Oracle 11gR2 Standard Edition (SE) database that I'm also maintaining a manual standby for, since Oracle Data Guard is not available in SE. I created a metric extension in EM12c to alert me if the standby is more (more...)

Don’t Fear the EM12c Metric Extensions

A few weeks ago, our customer support team asked us to automate part of their checklist that looks at the number of active sessions in our production database. In EM12c, this seemed like a no-brainer with the Average Active Sessions metric. So I added this to my production incident ruleset and went back to another project. Over the next few days we'd get pinged by EM12c but the support folks would say it shouldn't. After (more...)

Supplemental Logging and Securefiles Causing DBWn to Block

A few weeks back, we began making changes to prepare for using Oracle Golden Gate. One of the first steps required is to enable "minimal supplemental logging" at the database level. We did this during an evening maintenance window. However by the time the morning workload picked up, we started seeing a lot of sessions blocking, and the root blocker was one of the DB Writer (DBWn) processes.

Looking at the blocked sessions, a query (more...)

Migrating (and Upgrading!) Your EM12c Repository Database

This week I migrated our EM12c repository database to a new server as part of it's promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 11.2.0.3 to 11.2.0.4. Much of this post is directly inspired by Martin Bach's post on the same subject. I ran into a few other snags that weren't mentioned so I thought it would (more...)

Analysing Parallel Execution Skew – Without Diagnostics / Tuning Pack License

This is the third part of the video tutorial "Analysing Parallel Execution Skew". In this part I show how to analyse a parallel SQL execution regarding Parallel Execution Skew.

If you don't have a Diagnostics / Tuning Pack license the options you have for doing that are quite limited, and the approach, as demonstrated in the tutorial, has several limitations and shortcomings.

Here is the video:



If you want to reproduce or play around with (more...)

Unusable unique constraint

Another annoying thing about unusable indexes

I’m surprised that I can’t remember coming across this before before.

I want to archive some data from a bunch of partitions.

Unfortunately, I can’t follow the virtuous circle of data lifecycling and partition the table in such a way that I can archive all the data from a particular partition (or subpartition) via exchange partition.

Without going into too much detail and skipping a bunch of other steps, (more...)

ORA-00600 [kkzlpllg:5] When Dropping MView Log

This week a co-worker and I have been doing some rapid-fire testing to improve fast-refresh performance on an old materialized view, which does some summary aggregations on a 1.9 billion row (and growing) master table. One of the things we tested was using the new-in-11gR2 COMMIT SCN feature. There is a great blog post describing the benefits of this feature by Alberto Dell'Era. To quickly summarize and over-simplify, it provides a much faster way (more...)