Recovery-(RMAN: how to duplicate a database on the same host)

This post explains the steps which are used for RMAN: how to duplicate a database on the same host.




We need to duplicate a database. There are many possibilities to complete this task and I choose to use RMAN and its commands: in particular, I decided (to be honest it was just a poor notebook configuration to address my choice) to duplicate the target database to the same host. Perhaps I could spend (more...)

Reocvery- (How to perform a recovery when the first block of a datafile containing the datafile header becomes corrupt)

Here some lines explain about the header of a block. Normally we know have seen block corrupt but what if when the first block of a datafile containing the datafile header becomes corrupt. The header of a block contains information like the type of segment the block belongs to and the SCN of the last transaction that modified that block. For a more advanced description. The v$ views are actually structures stored in RAM.  (more...)

Recovery – (How to recover corrupted data blocks using ‘recover datafile’ RMAN syntax on a system critical datafile)

As we know that SYSTEM – stores the data dictionary.  Being an Oracle DBA we all know the importance of SYSTEM tablespace.  In these below lines, we can have actual technical knowledge about system’s information.

SYSTEM – a tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.

  • Every Oracle database has to have a SYSTEM tablespace—it is (more...)

Recovery -(How to recover sparse corrupted data blocks using ‘recover datafile’ RMAN syntax)

Being an Oracle DBA we all are known about data block. Here this line explains the technical definition of data block A data block is the smallest unit of storage in an Oracle database. Every database has a default block size (specified when the database is created), although blocks in different tablespaces may have different block sizes. Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS.

These views show (more...)

EXADATA Essentials For Oracle DBAs: Hands-on 4-Week Saturday Workshop by Gavin Soorma (OCM)

My friend and fellow Oracle Certified Master Gavin Soorma, after his recent popular Oracle RAC workshop (over 100 registrants) is now offering a new hands-on 4-week Saturday workshop on "Exadata Essentials For Oracle DBA’s", so you can fast track your path towards becoming an Oracle Database Machine (DMA) administrator.

In this new workshop, instructions will be provided by Gavin on how to install and configure an Exadata Storage Server on your own individual VirtualBox platform.


Updating indexes with partition maintenance

An index is basically a structure that maps keys (values) in columns to the physical location of their corresponding rows in a table.  So if you move the rows (ie, change the physical location of a row) then the index entries for those rows need to be updated, or the index is no longer usable.  And as most people are aware, the latter is the default when you perform a partition maintenance operation (more...)

RMAN Backup from Standby w Recovery Catalog

Apologies as this is not a very clean post as there is too much info to disseminate.

Hopefully, the info may be useful at some point.

Try connect using connect target sys/password@tns vs connect connect target;

Otherwise, RMAN> resync catalog from db_unique_name all will fail or get the error below.

RMAN-06820: WARNING: failed to archive current log at primary database

When using RMAN recovery catalog, “from db_unique_name and for db_unique_name” is introduced.
Either specify the required (more...)

With Subquery()

Here’s a little oddity that came up recently on the OTN database forum – an example where a “with” subquery (common table expression / factored subquery) produced a different execution plan from the equivalent statement with the subquery moved to an inline view; tested in and Here are the two variations:

with  tbl as (
          select 1 col1, 'a'  col2 from dual
union all select 2 ,  (more...)

Did You Know #21 – Adaptive Features

When Oracle are adding a new feature to the database, they usually add a parameter to control it. Sometimes, after adding feature and the parameter, they realize that the parameter they chose for controlling the feature is not suitable. It might be confusing, or too general or something else. In these cases they change the … Continue reading Did You Know #21 – Adaptive Features

Idempotent and Nullipotent in Cloud

I was going through the documentation of Oracle Cloud IaaS, when I came across the vaguely familiar term Idempotent.

One great thing which I have felt very strongly with all this Cloud-mania is the recall of various theoretical computing concepts which we learned/read in university courses way back. From networking through web concepts to operating system; there are plethora of concepts which are coming back to be in practice very actively in everyday life (more...)

Thoughts on RMAN Backup Strategy Part 3

Thoughts on RMAN Backup Strategy Part 2

Thoughts on RMAN Backup Strategy

Linux Locking using flock

From cron:

00 03 * * 0 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5 1     > /tmp/rman_l1.log 2>&1
30 * * * *   > /tmp/rman_arch.log 2>1

Notice from cron, DB backup starts at 3am and achivelog backup starts at 30m after the hour.

What happens when DB performs (more...)

SRVCTL Status Doesn’t Show RAC instances Running Unlike SQLPLUS

Yesterday, I converted a single instance physical standby database to a cluster database with 2 nodes.

After converting that to RAC database, I brought both instances up in mount state on both nodes and they came up fine and I started managed recovery on one node and it started working perfectly fine and got in sync with the primary.

Then I added them as a cluster resource by srvctl like (more...)

Added save and restore data function to PythonDBAGraphs

I pushed out a quick change to PythonDBAGraphs to automatically save the data for any graph that you make so that you can redraw the graph later. This is better than saving an image file because the redrawn graph lets you see details about the points on the graph when you hover the mouse over the points.

Now when you generate a graph you get a line like this:

Saving data in C:\temp\ASH active session  (more...)

“Oracle Indexing Internals & Best Practices” Seminar: Australia 2017 Tour (Stage)

I’ll be running a fully revised and updated version of my acclaimed “Oracle Indexing Internals and Best Practices” seminar throughout Australia in Oct/Nov 2017. Previous versions of this seminar have been hugely popular and run in some 18 countries. This updated version will feature lots of new material including 12c related indexing capabilities and recommendations. […]

Log Buffer #522: A Carnival of the Vanities for DBAs

This edition of Log Buffer covers Cloud, Oracle, SQL Server and MySQL and much more.


Introducing managed SSL for Google App Engine

Using Cloud Foundry CUPS to inject Spring Security credentials into a Spring Boot Application

ClusterControl in the Cloud – All Our Resources

Monitoring Amazon Aurora Audit Events with Amazon CloudWatch

Integrating Teradata with Amazon Redshift Using the AWS Schema Conversion Tool


Demo App for REST enabled SQL

The Insurance of Oracle (more...)

Recovery ( How to recover contiguous corrupted data blocks using ‘recover datafile’ RMAN syntax)

Being a DBA we all have good knowledge on feature of its database.   As we know that Oracle has introduced a new feature in 11g which can be used to get assistance in case of loss or corruption of datafiles, redo log files or controlfiles. This tool is called data recovery advisor and is accessible from the command line as well as from the GUI. Today we are going to have look on those (more...)

LOBs and tiny typos

This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created.  Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a securefile.

SQL> create table t1 ( b blob ) lob ( b ) store as securfile;

Table created.

SQL> select securefile
  2  from   user_lobs
  3  where  table_name = 'T1';


And the idiocy continues…

Been a while since my last post.  Then again, Oracle administration has become such a small part of my work that I hardly find the motivation... But the latest idiocy from the Larry Ellison camp is just too much! Yeah sure: another iteration of the "let's get rid of all those DBAs" nonsense that has essentially killed the company here in NSW Australia, since the days of release9! This one is

Changes to the Oracle preinstall RPMs in OL 7.3 and OL 7.4

For quite some time now Oracle has documented the use of the so-called preinstall RPMs to prepare Oracle Linux for the installation of the Oracle database software. I think that’s a great idea if the settings applied by the RPM fit your environment. If I find the time, I’ll write a blog post about what it does specifically in a little while. It definitely fits my lab environment, and I regularly kickstart my OL 7 (more...)

Partitioning Guide updated


Just a quick note to say I’ve updated our Getting Started With Partitioning Guide! 9 easy to follow demonstrations to get developers up to speed with handling large databases. Check it out here