Author: Luke Davies

Shutdown while upgrading to 18c

My colleague and friend Gleb Otochkin has already blogged about installing 18c – Installing Oracle 18c using command line – but I thought that I would share my experience. My plan was to upgrade my 12.2 pluggable database to 18c…

Read More >

Mysterious rollback and replay with partitions

Recently I had a situation where I was loading a partitioned table and during the load, which was going to take hours, I realised that I wanted to add DEFAULT and NULL partitions to help with the robustness of future…

Read More >

Online resizing of ASM disks

  The SAN administrator has informed us that they have extended the disks. This is the information I had from our client. The disks were labelled: mpath_compellent_oraarch mpath_compellent_oraarch02 mpath_compellent_oraarch03   The original size of the disks were 300GB and they…

Read More >

Read GoldenGate Discard Files

GoldenGate discard files can get big and cumbersome. To address this, I have build a PL/SQL function to read the discard files thereby reducing the output to one line per error and it also means that I can apply SQL logic to filter out the unwanted entries. Here’s how to do it.

Read More >

AQ Notifications in Oracle 11gR1

The AQ notification process has changed from release 10gR2 to 11gR1. The most notable change is the switch from using DBMS_JOB jobs to DBMS_SCHEDULER jobs. The number of available jobs to run the notifications is limited to the number of JOB_QUEUE_PROCESSES that have been set up for that instance, and each job dequeues one message. In 11gR1 the notification process is very similar but instead of creating a DBMS_JOB job it creates a DBMS_SCHEDULER job. This, in itself, is not very different but here’s the big difference:

Read More >

Over 4 Billion Buffer Gets?

Recently I was looking into a long-running statement and noticed a curious thing. One moment, I had just over 4 billion buffer gets, and the next I had around 2 million. Beware that if you have had a statement running for some time, you cannot necessarily rely on the buffer_gets column in v$sql—it may be that it has run over the limit, been recycled, and is counting from zero again.

Read More >

SQL BackTrack and Flash Recovery Area

There is a bug in SQL BackTrack (at least in version 6.8) that prevents your backing up of the archive logs from the flash recovery area. This will result in the inability to recover from any online backups that you have taken if you somehow lose those archive logs. There are 2 workarounds to this issue that I have tested, have a look.

Read More >

Undocumented parameter _fix_control: How to break your database

Beware this parameter can prevent your database from starting. Indeed it can prevent your instance from starting! There are two dynamic views v$system_fix_control and v$session_fix_control which were introduced in 10.2 and control whether fixes for bugs in the optimizer can be turned on or off. This can also be controlled using the _fix_control initialization parameter. Also beware if setting this parameter in a running database. You can prevent further logins if you get it wrong or indeed try to unset it. Do not do. The moral of the story is be very careful with undocumented parameters when upgrading!

Read More >