Oracle DBA – A lifelong learning experience

Posts Tagged ‘RMAN’

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 »

Controlling RMAN channels on RAC

Posted by John Hallas on December 3, 2014

This was sent to me for posting  by my friend and ex-colleague Vitaly Kaminsky ….

I have recently worked with the customer where standard RMAN backups of production 2-node cluster ( were getting too big and taking longer than 24 hours to run.

The problem with this particular cluster was the fact that ALLOCATION of RMAN connections to the instances of the cluster was controlled by SCAN and driven by the load-balancing algorithm of SCAN.

The result was rather undesirable allocation of all channels to a single instance and the only way to control it was to reduce the workload on all nodes at the time when backup is started which was unacceptable on this 24/7 constant workload system.

This single-node allocation caused the network connections (10GB network) to become saturated, throttling the backup rates and degrading the “user experience” for user sessions on the same node.

After considerable search, I found a way of controlling this allocation I did not know about before (just showing my lack of attention when reading low-level manuals).

The way RMAN is using parameters is as follows:

  1. Use the instructions from the script
  2. If there are no script instructions, use the defaults.

How to force RMAN to “distribute” the channels across instances:

  1. Set required degree of parallelism in default parameters:



RMAN> show all

  1. “Hard-code” the channel allocation in the defaults:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';
  1. DO NOT allocate channels in the script:
RMAN> run {

2> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

3> }
  1. Check how channels are allocated:
RMAN> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

Starting backup at 24-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=871 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=970 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1453 instance=racservice2 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=399 instance=racservice2 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set


There are 2 issues I can think of with this approach:

I am not keen on passing the passwords to any services, but RMAN is smart enough to cater for this:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name RACSERVICE are:





  1. Correcting mistakes.

While typing, I made some mistakes and ended up with the wrong CONFIGURE CHANNEL lines.

How do you delete those lines?  Simples:


The result of the above?

We now have even distribution of RMAN workload and much higher utilisation of available network bandwidth on both nodes!


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

Recover table from RMAN in 12c

Posted by John Hallas on October 21, 2013

When  I read the list of 12c new features the one that interested me immediately was the ability to be able to recover a table from an RMAN backup. This seemed to be quite challenging as RMAN is an image copy of blocks and a table is normally copied using a logical Datapump export.

What Oracle have delivered is a packaged technique which recovers only the necessary tables so as to get the data dictionary and any undo/redo segments necessary to get all the data back to a  specific point in time whether that be a SCN or a timestamp.

The whole process is very neat and it creates a new database, recovers the necessary datafiles and then creates a dumpfile of the necessary table, puts the data back to wherever you want and then deletes all evidence and traces of what it has done.

I have provided a full example of such a recovery below and it is well worth looking at to see how it all works.

A few things seem worthy of note and even further  investigation. Read the rest of this entry »

Posted in 12c new features, Oracle | Tagged: , , , | 2 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 »

UKOUG Part Deux

Posted by John Hallas on December 3, 2008

Yesterday I finished my blog with a mention that I was going to see Alex Gorbachev talk. Well I did and I didn’t. I saw the clusterware presentation he had written, he was on the podium but he did not do the presentation. It had looked like he might not get to Birmingham on time so he had called a fellow Pythian in at short notice.  The stand-in did a good job but it was quite obvious he did not know the material or presentation and the comments I heard later all aligned with mine. Alex should have taken over. Saying that there were at least 2 slides that were very useful. One on the names and location and order to study event logs if problems occured and the other relating to which processes call other processes.

Wednesday morning started with Understanding Lobs by Julian Dyke. I was looking forward to this and whilst it was detailed and pretty complex with lots of block dumps I am not sure it answered the question that Julian posed early on. He was saying that he had become aware that a lot of sites were making basic mistakes with their use of lobs. I was hoping he might provide a summary of what those mistakes were (and the fixes) but whilst he might have alluded to the problems I don’t think he truly covered them all.  However I now  know much more about lobs and sizing chunks and parts of the talk about compression factors is one that I have marked down to study further.

Chris Dunscombe did a talk on SQL Tuning which I enjoyed and was very informative. On the way out I heard a couple of people saying that they could have provided a few examples of badly written SQL and how they had fixed them. Yes, very, true ….but they didn’t did they.

Dan Morgan (of PSOUG fame) did a talk about how to manhandle Oracle into hanging, crashing, corruptions, spins and anything else bad you can think of. The aim was to learn how to handle the resultant issues. I think most of us had tried a block recovery using RMAN but probably not that many of the other tests.

Niall Litchfield provided the next seminar which was a sort of travel back in time through Oracle history showing how you should use the tools on offer at the time and free yourself up to do more specific or targeted work. Forty five minutes of easy listening to a well-written presentation that was not too challenging.

Stephen Haisley from Oracle talked about Optimising Oracle Backup and Recovery Operations and I picked a few tips up from that. My final session of the day was by Steve Shaw (I think he might have written HammerOra, or at least had a lot to do with it). He was demonstrating its use for large scale parallel query testing. He presented very well, knew his subject and provided lots of good slides.

So that was the end of my three days at UKOUG. I enjoyed it very much. I am not sure I learnt vast amounts but I have got a lot of notes about things to check out and from there I will undoubtedly get more knowledge, which is the way it should be.

The organisation was superb. Every presentation I saw had been well written and prepared and the speakers were universally good. I might have been slightly negative with some comments above but there is no point me stating that everything was 100% perfect and after all it is only my view.

I think the main thing that struck me is those who are well known (and therefore experienced) on the speaking circuit managed to talk with an easy familiarity that seemed to make the whole session seem so much more enjoyable.

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

Script to tidy archivelogs from ASM and RMAN

Posted by John Hallas on July 17, 2008

We have a pre-production RAC cluster that is kept in archivelog mode to allow true performance monitoring (and to be used for Streams and DataGuard testing). However we do not need the archivelogs for recovery purposes and as we perform high-volume testing the +FRA diskgroup (on solid-state disk) gets full very quickly.

I wrote a script that can be run to quickly free up space. It connects to the ASM instance and removes the logfiles.

It sets the SID and ORACLE_HOME to that of the RAC instance then runs RMAN to perform a crosscheck and delete of the archivelogs.

Not particularly complex but efficient.

export ORACLE_HOME=/u00/app/asm/product/11.1.0/db_1

asmcmd -p << EOF


export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

rman target / catalog username/password@catdb << EOF1


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


Posted by John Hallas on July 17, 2008

I tend to write on subjects that I have had problems with or on commands and syntax that are new to me or hard to find good working examples of.
I was trying to find out how to answer a prompt after an RMAN command within a script and I came across the CHECKSYNTAX command to RMAN. This came in with 10g
Very simple to use but also very helpful when putting an RMAN script together.

The documentation example is as good as any to try out

Create an flat file badcommand with the following contents :-

#command file with bad syntax commands
restore database
recover database

Unix_prompt> RMAN CHECKSYNTAX @badcommand

Recovery Manager: Release – Production on Thu Jul 17 09:07:08 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> #command file with bad syntax commands
2> restore database
3> recover
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “recover”: expecting one of: “archivelog, channel, check, controlfile, clone, database, datafile, device, from, force, high, (, preview, primary, ;, skip, spfile, skip readonly, standby, tablespace, to restore point, until, until restore point, validate”
RMAN-01007: at line 3 column 1 file: badcmdfile
Recovery Manager complete.

Edit the badcommand file and add semi-colons at the end of each line

Unix_prompt> RMAN CHECKSYNTAX @badcommand

Recovery Manager: Release – Production on Thu Jul 17 09:07:33 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> #command file with bad syntax commands
2> restore database;
3> recover database;
The cmdfile has no syntax errors

Recovery Manager complete.

Overall nice and simple and well worth using when writing RMAN scripts

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

RMAN backup script – example – logging output

Posted by John Hallas on March 20, 2008

To answer a question about writing the output from RMAN commands to a logfile I posted a copy of a shell script I use to the Oracle-L newsgroup. It is a script I use for testing which does explain the multiple RMAN commands, much of which are commented out.



export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2




export DATE=$(date +%Y-%m-%d)

rman target backup_admin/xxxxxxx catalog rman/xxxxxxx@server msglog /export/backups/rman/11g/logs/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

#backup AS COMPRESSED BACKUPSET database ;

#backup AS COMPRESSED BACKUPSET archivelog all delete input ;

backup AS COMPRESSED BACKUPSET database plus archivelog delete input;


#backup database;

#backup archivelog all delete input;


#delete noprompt force obsolete;

#Change archivelog all validate





From the Oracle documentation I also found the following notes

Using RMAN with Command Files
A command file is a text file which contains RMAN commands as you would enter them at the command line. You can run the a command file by specifying its name on the command line. The contents of the command file will be interpreted as if entered at the command line. If the LOG command line argument is specified, RMAN directs output to the named log file. Command files are one way to automate scheduled backups through an operating system job control facility.

In this example, a sample RMAN script is placed into a command file called commandfile.rcv. You can run this file from the operating system command line and write the output into the log file outfile.txt as follows:

% rman TARGET / CATALOG rman/cat@catdb CMDFILE commandfile.rcv LOG outfile.txt
Directing RMAN Output to a Log File
When you run RMAN in command line mode, it sends the output to the terminal. If you specify the LOG option, then RMAN writes the output to a specified log file instead.

Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view, which reads only from memory (that is, the information is not stored in the control file). The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs. The metadata for completed jobs is stored in the control file.

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

ORA-27054 when using RMAN to NAS/NFS disks

Posted by John Hallas on March 4, 2008


When an NFS disk is mounted there are a set of requested mount attributes. It is perfectly possible for an NFS disk to mount with the correct actual attributes (rsize/wsize etc) even if these attributes are not explicitly listed in the original mount request. However, on some platforms Oracle does not check the “actual” attributes and can think, incorrectly, that the disk is not mounted properly. Some platforms include an auto-mount feature which auto mounts the NFS disks, again with correct attributes, but Oracle cannot determine the actual mount point and attributes of the auto-mounted disk/s. As the files look like NFS type then again ORA-27054 is thrown even though the disks are mounted correctly as Oracle cannot confirm the mount attributes.


Use correct mount options.


default rsize and wsize for NFS is 4096 Blocks

Do not use auto-mount. Touching a file on the auto-mounted fs from your RMAN script does not wor.

set event=”10298 trace name context forever, level 32″ in the spfile and restart the instance. The event will disable the NFS checks in 10gR2 but it will also have some side effects:

– It will generate some trace files which you can ignore Bug:4998064 ‘Ksfq 10298 Trace Enabled For Levels Other Than Bit Level 0x2’ (unpublished)
– It can have a side effect of disabling direct IO on RAC using regular files on NFS mounts

This information comes from Metalink note 356199.1. I thought it worth commenting on as we have been testing RMAN network backups recently and I kept hitting the same problems as we have a sys admin who likes to use automount.

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