Online Redo Log Switch Frequency Map

Uncategorized
| May 31, 2019

A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency.

Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints.  The (more...)

RMAN Incremental Updating Backup & Demo Part 1

Uncategorized
| May 30, 2019

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Updating Backup

An incremental updating backup is a level 0 incremental backup which is recovered using level 1 incremental backups.  This avoids the overhead of taking level 0 incremental backups, for example in a backup strategy where a level 0 is taken every Sunday and all other days level 1.

An simple example would be, an (more...)

RMAN Incremental Differential vs Cumulative & Demo

Uncategorized
| May 29, 2019

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Differential Incremental Backups (default)

A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.

RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the (more...)

RMAN Incremental with Block Change Tracking & Demo

Uncategorized
| May 29, 2019

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Block Change Tracking

Block change tracking (BCT) improves incremental backup performance by recording the changed blocks in each datafile in the block change tracking file, thus avoiding the need to scan every block in the datafile for changes.  Only applicable for level 1 backups.  Block change tracking file is 1/30,000 the size of the data blocks being tracked, (more...)

RMAN Incremental & Demo Part 2 (Level 1)

Uncategorized
| May 28, 2019

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included (more...)

RMAN Incremental & Demo Part 1 (Level 0)

Uncategorized
| May 27, 2019

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included (more...)

JSON, BLOB column and Check Constraint

Uncategorized
| Sep 11, 2018

Last week I attended Neil Chandler's session on JSON during the POUG conference in beautiful Sopot, Poland.
The JSON Developer's Guide recommends using BLOB for storing data, and this is what Neil also recommended.
I was under the (FALSE!) impression that it was not possible to put an IS JSON check constraint on a BLOB column, simply because I tried once and got an exception. After the session I asked Neil if this was (more...)

Video: Oracle X$TRACE, Wait Event Internals and Background Process Communication

Uncategorized
| Jan 24, 2018

I have uploaded the the video of my Secret Hacking Session: Oracle X$TRACE, Wait Event Internals and Background Process Communication to my Oracle performance & troubleshooting Youtube channel.

The slides are in Slideshare.

Enjoy!

 

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

ORA-00240: control file enqueue held for more than 120 seconds 12c

Uncategorized
| Dec 11, 2017
Occasionally, In Oracle 12c database, you may get ORA-00240: control file enqueue held for more than 120 seconds  error in the alert log file.


Well, as this error contains the word control file so it looks scary but if you are getting it infrequently and the instance stays up and running then there is no need to worry and this can be ignored as a fleeting glitch.

But if it starts happening too often and worst (more...)

Advanced Oracle Troubleshooting seminar in 2018!

Uncategorized
| Nov 29, 2017

A lot of people have asked me to do another run of my Advanced Oracle Troubleshooting training or at least get access to previous recordings – so I decided to geek out over the holiday period, update the material with latest stuff and run one more AOT class in 2018!

The online training will take place on 29 January – 2 February 2018 (Part 1) & 26 February – 2 March 2018 (Part 2).

The (more...)

Oracle Installer fails with [EXCEPTION]:java.lang.reflect.InvocationTargetException under RedHat 7.4

Uncategorized
| Sep 19, 2017
I recently had a customer with a fresh installed RedHat 7.4 system on which we wanted to install different Oracle Software in graphical mode, like Oracle Database 12.1.0.2.0, Oracle WebLogic Server 12.2.13.0 and Oracle Data Integrator 12.2.1.3.0.

Following error occured when we were starting the Oracle Installers, either runInstaller or java -jar fmw_12.2.1.3.0_odi.jar:

export JAVA_HOME=/u00/app/oracle/product/jdk1.8.0_131
export (more...)

Good old BIN_TO_NUM to check the overall status

Uncategorized
| Mar 28, 2017

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

(more...)

Book on Oracle 12c New features

Uncategorized
| Dec 8, 2016

A Book on Oracle 12c New features for administrators

Hi All,

It’s our pleasure to announce the availability of our first book – OCP 12c upgrade 1Z0-060 Exam Guide

 

51xr2hzj5ql

 

 

 

 

 

 

 

 

 

 

The book covers the new features of the Oracle Database 12c for the DBAs from the OCP 12c upgrade exam standpoint. Book is available WW in kindle format as well as paperback.

Link to Amazon.com (more...)

RegExp: Constraint to prevent spaces at the beginning or end.

Uncategorized
| Aug 31, 2016

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Oracle 12c Learning Series: Automatic Table Recovery Using RMAN

Uncategorized
| Aug 10, 2016

Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options

  • Database point in time recovery (DBPITR)
  • Tablespace point in time recovery (TSPITR)
  • Flashback technology

In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)

Oracle 12c Learning Series: In-database Archiving and Temporal Validity

Uncategorized
| Jul 29, 2016

One of the major challenge faced by an Oracle DBA is – how to effectively deal with historical data? Today, if we consider database tables for an enterprise, data in the table goes back several years and most of the data in the table is inactive. Challenge remains as to how to archive this data and make our query run efficiently.

If we choose to archive old data outside of database on a tape, cost (more...)

Oracle 12c Learning Series: Automatic Data Optimization – ADO

Uncategorized
| Jul 15, 2016

I am starting Oracle 12c learning series, where I am planning to publish multiple articles on Oracle 12c new features. I hope these articles will be helpful to DBAs aspiring for OCP 12c certification and also others who are planning to implement these features in there databases.

This is a very long post about ADO – Automatic Data Optimization, which is one of the ILM strategy to manage aging data. So please be patient while reading this article. This article provides end-to-end details (more...)

Top N- queries: using the 12c syntax.

Uncategorized
| May 31, 2016

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Rounding amounts, divide cents over multiple lines

Uncategorized
| May 18, 2016
In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Temporal validity, multiple end dates

Uncategorized
| Mar 18, 2016

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)