Oracle DBA – A lifelong learning experience

Archive for the ‘RMAN’ Category

A setup to test backup and restore options

Posted by John Hallas on December 3, 2018

A couple of years my team put together some procedures and the underlying databases to allow testing of as many RMAN recovery options as we could think of. We had two databases which gave us a Dataguard capability and we secured both databases using Commvault backups so we always had a clean starting point. Each team member had a training objective to complete the course and we all thought it was an excellent refresher process.   As I recall Hamid Ansari did most of the work and I should mention him if I mention the output.


I noticed today that Francisco Munoz Alvarez has provided a script which does very much the same thing and I can only applaud the idea. I don’t think his script covers Dataguard but no doubt it could.  His web page Crash Simulator is where the script can be found. I will be looking at this myself in the next few days.


Posted in RMAN, scripts | Tagged: , , | Leave a Comment »

RMAN checksyntax function

Posted by John Hallas on December 29, 2016

I was looking at the RMAN DEBUG options and came across the CHECKSYNTAX function which I had not used before.

Firstly a quick recap on the DEBUG option.

This can be called using the following syntax

rman target / catalog rman12g1/xxxx@rmancat debug trace=rmantrace.log cmdfile=backup.rcv


rman target / catalog rman12g1/xxxx@rmancat debug trace=rmantrace.log then run RMAN> @backup.rcv (or just type in your run block of commands)

There are a number of options to DEBUG and one of the error messages lists them out quite neatly

RMAN-01009: syntax error: found "integer": expecting one of: "all, duplicate, recover, restore, resync, sql"

To be honest if I was tracing I would just stick with the DEBUG=ALL format. The DEBUG=SQL gives all the internal commands that RMAN calls and could be interesting if you were doing a deep dive into RMAN functionality

Anyway, back to the CHECKSYNTAX option.

I run it against the edited version of the command file used above

rman target / catalog rman12g1/xxxx@rmancat checksyntax cmdfile=backup.rcv 

Recovery Manager: Release - Production on Wed Dec 28 10:22:20 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: T12TEST (DBID=1543168240)
connected to recovery catalog database
RMAN> run {
2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
3> allocate channel c1 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
4> allocate channel c2 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "controlfile"
RMAN-01008: the bad identifier was: CONTROLFILEs
RMAN-01007: at line 6 column 33 file: backup.rcv

Note the file backup.rcv has a blank line as the first line which confuses the line numbering

Pretty neat. I edit the file and put a different error in, a much more common missing semi-colon

RMAN> run {
2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
3> allocate channel c1 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
4> allocate channel c2 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t'
5> backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "backup": expecting one of: "auxiliary, connect, format, maxopenfiles, maxpiecesize, parms, rate, send, to, comma, ;"
RMAN-01007: at line 6 column 1 file: backup.rcv

Overall not a mind-shatteringly exciting find but something that might be of use one day

Posted in Oracle, RMAN | Tagged: , | 6 Comments »

UKOUG Tech13 day 1 review

Posted by John Hallas on December 3, 2013

UKOUG conference day 1 and to my eye there does not seem to be that many differences between Manchester Central and Birmingham ICC.  Lots of smaller halls and a large main hall,  a similar lunch available, the same lack of seating. I understand that attendance is seen comparable as well. Overall I see it as a refreshing change which will suit some and not others.  Make or break is the quality of the drinks do later on – that will be reported on tomorrow.

An early start for a standing room only talk by Carl Dudley on Flashback in 11g and 12c. A really good handout was provided, which negated the need to attend the presentation. Nice factual presentation of the capabilities and options of recovering data. Would have been nice to have seen a bit more real-world ideas and examples but a session that I would recommend downloading the paper later.

Andy Colvin of Enkitec talked about 12C RMAN new features. He covered recovery of a table from an RMAN backup and I proposed my unproven theory that it might be possible to use the functionality in 11GR2 (using the 12c binaries) and he suggested trying it in – the story continues. He also mentioned the role SYSBACKUP which takes away the need for the RMAN user to have select any table privilege. Someone in the audience stated that you might not have permission to read a table before you do a restore but you will afterwards which proved to be true. Looks like an Oracle bug. I think Andy learnt as much as the audience did. An hour well spent.

I did attend a presentation which I walked out of. I felt that the presenter was divulging too much about poor practises / long turnarounds at companies which he named. He then went into blatant advertising mode. Out I walked.

Then the CERN people were talking about latency. Lots of examples which were very good and some demos of a few tools they have created and seem to be publicly available. Well worth searching out. However I do not understand the concept of people sharing a presentation. Quite frankly one of the presenters added little value, perhaps it was a way of getting more speaker badges.

I also went to another CERN talk by David Wojcik on Cardinality feedback in 12C. This had the potential of being interesting but it was much too rushed and I doubt many in the audience came away much better informed. It was a pity because I think the slides are worthy of download and review.

My last one of the day was Martin Bach doing a non-technical presentation  – shock and horror. This was about how to manage engineered systems (or Exadata if you are in the Oracle world). An interesting and challenging talk and one that I really enjoyed. I did take issue with him re one of his recommendations and that started off what was quite a lively chat afterwards with most of the audience remaining.

Oh and the Manchester climate was quite mild, compared with ice and snow that I have suffered in Birmingham in recent years.

My final thought. Walking back to my hotel I saw a pub called The Ape and Apple. That must be worthy of some research as to what the name signifies and how it came about.

Posted in Oracle, RMAN, UKOUG | Tagged: , , | Leave a Comment »

Flashback logging at tablespace level

Posted by John Hallas on June 16, 2010

Our site policy is to enable flashback logging on our production databases unless there is a good reason not to, two examples are the data warehouse where volume is prohibitive and the other is a highly active Peoplesoft database where performance is a primary consideration and we suffer from “flashback buf free by RVWR" wait event as it cannot write the flashback logs quickly enough. 

On the same system we had experimented with disabling flashback against two tablespaces which contained objects of a transient nature and that could be rebuilt if necessary. We finally agreed that the overhead of flashback was too heavy balanced aganst the likelyhood of us ever using it and so we disabled it at the database level.

However, when we are applying application code changes during an agreed outage we bounce the database and restart it with flashback enabled. Therefore when the changes have been applied and basic testing has taken place we can quickly flashback the database to a clean point with a minimum of fuss in the event that the changes have not produced the desired results. If the bundle is OK we disable flashback logging dynamically without a further database bounce. We find this an excellent belt and braces method which has little overhead or manageability cost.

During a recent bundle release we did want to flashback but had some problems Read the rest of this entry »

Posted in 11g new features, Oracle, RMAN | Tagged: , , , | Leave a Comment »

Cataloging RMAN backups

Posted by John Hallas on April 15, 2010

We had a situation where we had backed up several databases and then would restore them as part of performance testing work. The catalog entries in the controlfile were lost either because they had become obsolete or we had restored to another point prior to the backup being taken.

 The backups were on still on disk and therefore they could be re-introduced to the controlfile. 

Read the rest of this entry »

Posted in Oracle, RMAN | Tagged: , , , , | Leave a Comment »

Identifying corrupt blocks

Posted by John Hallas on April 14, 2010

Recently we had a datafile with a number of corrupt blocks and once it was fixed I resolved to try out all the methods available of identifying block corruption. Searching around I came across an excellent blog by   Asif Momen – Practising block recovery which provided me pretty much what I wanted. However I did think it worth adding a blog entry as I wanted to document all the various methods available to identify corruption.  I also wanted to cover how to test causing a corruption when using ASM datafiles.

Create a datafile, add data and find out which block that data is in

 create smallfile tablespace bad_data datafile '+DATA' size 10M;

Create table test (username varchar2(9), password varchar2(6)) tablespace bad_data;


u  VARCHAR2(9);

p  VARCHAR2(6);


FOR jump  IN 1 ..10000  LOOP

u := 'TEST'||jump;

p := 'P'||jump;

insert into test values (u,p);





PL/SQL procedure successfully completed.


rowid ,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot

from test where password='P7777'


—————— ———- ———- ———- ———-

AAAXaiAAHAAAACdAAr      95906          7        156         43 Read the rest of this entry »

Posted in Oracle, RMAN | Tagged: , , , , | 4 Comments »

Reducing RMAN backup time for unevenly sized tablespaces

Posted by John Hallas on April 6, 2010

The following note was written by Kevin Cardew after we had discussed how to reduce the backup time of some of our large databases, especially when they constrained by most of the data being stored in a single bigfile tablespace .

Our build standards require that 11g ( databases are built using BIGFILE tablespaces, however this has led to one or two problems with RMAN backups. In several of the databases the deployed application, third party supplied, has all the objects deployed to a single tablespace. The result of this is that an increasing amount of the database size resides in a single data file. During the backup RMAN balances the load by attempting to divide the backup volume (data files) over the available channels to maximise backup speed. Given the unbalanced nature of the data files, this results in a single channel being allocated to the largest data file and the rest load balanced across the remaining channels. Therefore the overall backup time is determined by the time taken to backup a single data file. Read the rest of this entry »

Posted in Oracle, RMAN | Tagged: , , , , , , | 4 Comments »

Recovering from a cold backup using RMAN

Posted by John Hallas on March 22, 2010

These are  basic notes that I am using when taking copies of databases that are being used for performance testing work and on which we will be performing regular regressions. I prefer writing  to disk if we have sufficient space as no time is lost in waiting for tape drive availability and  also know that I don’t have to worry about being past the retention data and the backup being deleted. There are 4 databases involved in this test and I prefer to close them all down, startup mount, backup to disk and then make them all available again.

It is a good habit to create the recovery script at the same time as the backup is created and leave a copy in the same directory as the RMAN backup pieces as it aids recovery. Both the channel command and the use of the TAG command both help in this.

Technically this is not a cold backup as the database is open, although not in use, which is why a resetlogs is required at the end. A proper cold backup is to shut the database down and take image copies of the database files and replace them as part of a restore. Read the rest of this entry »

Posted in Oracle, RMAN | Tagged: , , , | 8 Comments »

Recovering old spfile from autobackup

Posted by John Hallas on January 18, 2010

This post is about a problem I had today when trying to restore a spfile from a previous autobackup.  I did not want the latest one but one from earlier in the week. The database was a RAC one but I don’t think that is relevant to the problems I had.

Recovering a spfile from autobackup is relatively easy – startup mount and recover spfile from autobackup.  I set my date format and started the database up in mount mode and then tried to recover the spfile. Read the rest of this entry »

Posted in ASM, Oracle, RMAN | Tagged: , , , , , , , | 3 Comments »

Cloning a database (ASM to ASM)

Posted by John Hallas on April 2, 2009

Cloning production ASM (RAC)  to ASM (non RAC)

In this document I will be cloning database SOURCEDB ( RAC) to TARGETDB ( single instance). There will be no downtime on the source database and minimal impact.

Source server source_server  150Gb data ASM storage used

Target server target_server

Pre-requirements -ensure that you have backed up the source database and are comfortable that you are covered in case anything goes wrong

200Gb of disk space

NFS mount between the 2 servers. In this case I created a directory /app/oradata/rman_backup_dir and arranged for a NFS mount to the same filesystem on target_server


403701760 42657229 338479316   11% /app/oradata/rman_backup_dir

The steps we will follow will be :-

  • Prepare database on target server
  • Backup source database
  • Run duplicate database scripts

Note 1

RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database. In this document I am using the terms source and target for the two databases. However the RMAN script will use target and auxiliary instead out of necessity.

Note 2

Initially I wanted to use active duplicate database however whilst it did work once it failed many more times and there is an Oracle bug Bug 6603587 – CREATION OF SECOND STANDBY USING RMAN DUPLICATE FAILS WITH RMAN-20242

I then tried using non-active database and with a database backup. This time I hit Bug.5653255: PHSB: RMAN CAN\’T RESTORE DATAFILES CORRECTLY ON ASM USING IMAGE COPIES. The workaround to this was to use a backupset..

Prepare target database

I am assuming that we have an existing database built on the target server and we are just performing a refresh from production. However for completeness this section shows how to prepare the spfile and startup nomount the target database.

If this process is a refresh rather than new build then go to here

Create a tnsnames.ora entry on the source server

Note that we are using port 1524 whereas the source database uses port 1522




(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))







Create a listener on the target server

Note that we are using port 1524 whereas the source database uses port 1522




(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))







(ORACLE_HOME = /app/oracle/product/11.1.0/db_1/)




Start the listener

lsnrctl start listenerclone

On source_server

create pfile=\’/home/oracle/initTARGETDB.ora\’ from spfile;

Create pfile – target_server. Edit to remove references to RAC (threads etc). As I am moving from DATA/FRA diskgroup to DATA/FRA diskgroup I have no need to use any covert file_name parameters. Edit the spfile to replace the source SID name with the new SID

Edit /etc/oratab to add database entry

Copy the pfile from source_server


scp oracle@source_server:/home/oracle/initTARGETDB.ora .

Create audit dest on targetserver

mkdir -p /app/oracle/admin/TARGETDB/adump

Copy passwordfile from source_server

scp oracle@source_server:/app/oracle/product/11.1.0/db_1/orapwSOURCEDB /app/oracle/product/11.1.0/db_1/orapwTARGETDB

Startup standby database from targetserver

sqlplus  / as sysdba

create spfile from pfile;

startup nomount


Start the clone listener on target_server.

This is required as you cannot connect to a database that is unavailable with the asm listener

lsnrctl start listenerclone

From the primary database server source_server test the connection to the target database using the command:

sqlplus sys/password@TARGETDB_CLONE as sysdba

Prepare refreshed database

sqlplus  / as sysdba

shutdown immediate;

startup nomount;


Backup source database

rm /app/oradata/rman_backup_dir/*


rman target /



allocate channel d1 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d2 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d3 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d4 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d5 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

backup as backupset database plus archivelog;


This takes around 10 minutes. Make a note of the finish time (which is why the NLS_DATE_FORMAT variable was set)

Duplicate database

2 scripts have been created in /home/oracle on pebor02 which are pasted here


# Set the Oracle Environment


export ORACLE_HOME= /app/oracle/product/11.1.0/db_1/



export NLS_LANG=american





# Run the RMAN Command


nohup  $ORACLE_HOME/bin/rman target sys/password nocatalog auxiliary sys/password@TARGETDB_clone @rman_duplicate_from_backup.cmd log=/home/oracle/rman_duplicate_from_backup.log  &




set until scn 9660022443946;

allocate auxiliary channel d1 device type disk;

allocate auxiliary channel d2 device type disk;

allocate auxiliary channel d3 device type disk;

allocate auxiliary channel d4 device type disk;

allocate auxiliary channel d5 device type disk;

duplicate target database to TARGETDB;


Run the shell script  ./ then it will fail with an invalid SCN, however  the output will provide the latest SCN that can be recovered until. Edit the rman_duplicate_from_backup.cmd file and replace the SCN and rerun.

You can get the SCN from the timestamp by this way you are letting Oracle do the work for you..

Restart the shell script and monitor the log file specified in the shell script.

Tidy up

Stop the listener_clone and start a listener for the newly refreshed database.

Remove the files from /app/oradata/rman_backup_dir

Notify the Apps team that the database has been refreshed and the time that the backup finished.

Posted in ASM, Oracle, RMAN | Tagged: , , , , , , | 6 Comments »