Restoring with RMAN and using the Data Recovery Advisor

Restoring with RMAN and using the Data Recovery Advisor

I created a blog post focusing on Three steps to getting started with RMAN and if you have not read it yet I do recommend you take a quick look, as getting the basics right when using RMAN is crucial to making the most out of this utility.

To follow up on that post I would like to take it one step further, providing you with an example of creating a backup, but also detailing how to perform a restore when disaster strikes. I will also introduce you to the Data Recovery Advisor, which is a new feature that was introduced in Oracle 11g.

As an aside, I still find it amazing that so many DBA’s have backups but have never really tested them, and so when they do get a scenario where a restore and recovery is required, they are not comfortable performing these critical, yet often quite simple steps. It doesn’t have to be this way.

So as I always say, keep it simple. Do not make things too complicated at the start. First, get the basics working, and then once you are more familiar with the processes involved you can make things more complex, but only if it is required. I know writing complex scripts is fun, but it is not always necessary. So start simple, make sure your backup and recovery processes work and are well documented, and only then look at more complex configuration options.

For this example, my test environment consists of Oracle Linux 5 update 8 (64bit) and Oracle Database Standard Edition 11.2.0.3.0. (64bit). In this environment, I am also making use of ASM for database storage, but the steps used in the examples will be the same if you are using filesystem based storage for your database. I will be making use of filesystem-based backups, meaning I am not using a tape library or any other media management software here. I am also not using an RMAN recovery catalog in this post, but I do recommend you use one if available. For more details on the recovery catalog please see the online Oracle documentation.

In this post there are 3 areas I will cover which I consider some of the fundamentals to know about:

• Creating an RMAN backup
• Restore and recover a lost data file
• Using the Data Recovery Advisor

I will also be making use of some of the options discussed in the post mentioned above, Three steps to getting started with RMAN. These include setting my date and time format as well as updating some of the default RMAN configuration for my environment. In my environment I set the NLS_DATE_FORMAT first, before using RMAN:

NLS_DATE_FORMAT=”DD/MM/YYYY HH24:MI:SS”
export NLS_DATE_FORMAT

The next step is to set up the default configuration when connected to the target database.  The following commands were executed from the RMAN prompt after a successful connection to my target.

configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/backups/rman/testdb/%F';
configure channel device type disk format '/backups/rman/testdb/%U';
configure device type disk backup type to compressed backupset;

Once I have executed the above commands in my environment, and now have the following default RMAN configuration:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/testdb/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/testdb/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testdb.f'; # default

The next step is to create a backup, and to do this I run the following commands:

[oracle@dbvlin201 ~]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 30 17:18:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /
connected to target database: TESTDB (DBID=2569715825)
RMAN> run { 2> backup database; 3> sql 'alter system archive log current'; 4> backup archivelog all filesperset 50; 5> }

Starting backup at 30/10/2012 17:19:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/testdb/datafile/system.256.786387593
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.257.786387599
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.258.786387599
input datafile file number=00004 name=+DATA/testdb/datafile/users.259.786387601
input datafile file number=00005 name=+DATA/testdb/datafile/data.274.798052233
input datafile file number=00006 name=+DATA/testdb/datafile/data.284.798052235
channel ORA_DISK_1: starting piece 1 at 30/10/2012 17:19:40
channel ORA_DISK_1: finished piece 1 at 30/10/2012 17:20:55
piece handle=/backups/rman/testdb/14np2kdc_1_1 tag=TAG20121030T171940 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 30/10/2012 17:20:55
Starting Control File and SPFILE Autobackup at 30/10/2012 17:20:55
piece handle=/backups/rman/testdb/c-2569715825-20121030-01 comment=NONE
Finished Control File and SPFILE Autobackup at 30/10/2012 17:21:02
sql statement: alter system archive log current
Starting backup at 30/10/2012 17:21:08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1249 RECID=1248 STAMP=788356427
input archived log thread=1 sequence=1250 RECID=1249 STAMP=788356467
input archived log thread=1 sequence=6667 RECID=6666 STAMP=798049229
input archived log thread=1 sequence=6668 RECID=6667 STAMP=798052245
input archived log thread=1 sequence=6669 RECID=6668 STAMP=798052247
input archived log thread=1 sequence=6670 RECID=6670 STAMP=798052250
input archived log thread=1 sequence=6671 RECID=6669 STAMP=798052249
input archived log thread=1 sequence=6672 RECID=6671 STAMP=798052867
input archived log thread=1 sequence=6673 RECID=6672 STAMP=798052869
channel ORA_DISK_1: starting piece 1 at 30/10/2012 17:21:09
channel ORA_DISK_1: finished piece 1 at 30/10/2012 17:21:12
piece handle=/backups/rman/testdb/17np2kg5_1_1 tag=TAG20121030T172109 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30/10/2012 17:21:12
Starting Control File and SPFILE Autobackup at 30/10/2012 17:21:12
piece handle=/backups/rman/testdb/c-2569715825-20121030-02 comment=NONE
Finished Control File and SPFILE Autobackup at 30/10/2012 17:21:19
RMAN>

For this post I am keeping it simple, just doing a full database backup followed by backing up all the archive logs.  In this environment I have a small database called testdb with only a few datafiles and archive logs so the backup does not take too long.

To test backups and get familiar with RMAN you do not need a large database; a small database is sufficient to get you started with the commands and processes.  One important point to make in line with this is that the steps for a small database are the same as for a large one.  Yes, you can perform a few different commands to optimize or adjust for bigger databases, but more than 90% of the time the same commands will be used on both small and large databases.  I will leave a few of the more advanced options for another post.

Remember that when you schedule backups in scripts it is always important to keep multiple log files and not overwrite the log file every day.  I tend to keep at least a week’s worth of backup logs.  But keeping longer than that is not a bad idea.  By doing this you can always go back and review backup output, and trust me, when you need to do a restore or recovery based on a backup taken a few days ago, the logs can be a useful source of information.  Also, when scheduling backups always check the output of the log file for errors.  It is good to alert whenever there are errors in backup logs. A useful query I use on a regular basis to look at rman backup job details is this:

SQL> select start_time, end_time, status, elapsed_seconds, autobackup_done, input_type from v$rman_backup_job_details;

START_TIME          END_TIME            STATUS     ELAPSED_SECONDS AUT INPUT_TYPE
------------------- ------------------- ---------- --------------- --- -------------
25/07/2012:21:28:10 25/07/2012:21:28:28 COMPLETED               18 NO  DB FULL
25/07/2012:21:28:32 25/07/2012:21:28:34 COMPLETED                2 NO  CONTROLFILE
30/10/2012:17:19:39 30/10/2012:17:21:20 COMPLETED              101 NO  DB FULL

The above query will show you backup start, end time and the status, with the last row being the backup I just ran.  Do not make this your only source for reviewing backup status, but it is a good place to start and I found that reviewing the elapsed seconds over a period of time for a specific backup job could be useful.

Now that we have a backup, let’s do some restores. 

How to restore one datafile following corruption or lost file.

The first example I would like to show you is the restore and recovery of one datafile.  Lets assume I had a maintenance window, the database was shutdown, and following hardware upgrade one data file is corrupt or was lost for some reason.  To simulate this, I shutdown the database, delete one datafile from the ASM storage.  When I startup the database I get the following error:

SQL> startup
ORACLE instance started.
Total System Global Area  521936896 bytes
Fixed Size                2229944 bytes
Variable Size           327158088 bytes
Database Buffers        188743680 bytes
Redo Buffers              3805184 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DATA/testdb/datafile/data.274.798052233'

At this stage the database is in a mounted state, and the datafile in question is lost.  The process I would normally  follow is to first get the database open – and to do this I first take this file offline and then open the database:

SQL> alter database datafile 5 offline;
Database altered.

SQL> alter database open;
Database altered.

Once this is done, I focus on the datafile in question.

SQL> select file#, status, name from v$datafile where file#=5;

FILE#      Status     Name
---------- ---------- ------------------------------------------------------------
5          OFFLINE    +DATA/testdb/datafile/data.274.798052233

SQL> select file#, name, status, error from v$datafile_header where file#=5;   

FILE#      Name                           Status     ERROR
---------- ------------------------------ ---------- ------------------------------
5                                         OFFLINE    FILE NOT FOUND

In my case I know the file is deleted, but you can easily confirm this if you look at the ASM storage using the “asmcmd” command.  If you are using a normal filesystem based storage you can just navigate to the directory and look at a listing of the files.  Now that we have identified that the file is indeed lost, the next step is to restore the file.  This process can easily be done with RMAN.  I know I have a recent backup and that all archive logs are still on disk, so I should be able to do a full recovery.  To restore the file number 5 I use the following command “restore datafile 5”, followed by “recover datafile 5”.  Below is the output when these commands are executed:

oracle@dbvlin201[/home/oracle]: rman

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 30 18:02:56 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: TESTDB (DBID=2569715825)

RMAN> restore datafile 5;
Starting restore at 30/10/2012 18:03:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATA/testdb/datafile/data.274.798052233
channel ORA_DISK_1: reading from backup piece /backups/rman/testdb/14np2kdc_1_1
channel ORA_DISK_1: piece handle=/backups/rman/testdb/14np2kdc_1_1 tag=TAG20121030T171940
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 30/10/2012 18:03:16

RMAN> recover datafile 5;
Starting recover at 30/10/2012 18:03:20
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 30/10/2012 18:03:21

RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN> exit
Recovery Manager complete.

Now if I look at the status of the datafile I can see that everything is back to normal again and the file was successfully restored and recovered.

SQL> select file#, status, name from v$datafile where file#=5;

FILE#      STATUS  NAME
---------- ------- --------------------------------------------------
5          ONLINE  +DATA/testdb/datafile/data.274.798055391

SQL> select file#, name, status, error from v$datafile_header where file#=5;

FILE#      NAME                                               STATUS  ERROR
---------- -------------------------------------------------- ------- ----------
5          +DATA/testdb/datafile/data.274.798055391           ONLINE

The Data Recovery Advisor

I would like to introduce you to a feature that was made available in Oracle 11g called the Data Recovery Advisor.  The Data Recovery Advisor tool will automatically diagnose data failures, such as corruption or loss of persistent data on disk, and will attempt to identify and present you with repair options, which you the DBA can then review and execute.  That is the quick summary of it, and it is a great little feature that can provide you with extra options to perform quick and easy failure detection, guiding you through the options available to repair and resolve the failure.  What I do like about this tool is there are only a few commands you need to know in RMAN to make use of it:

• list failure
• advise failure
• repair failure

These options are also available in Enterprise Manager and Database Control for those of you that prefer to use the GUI.

In the previous example, I had a lost datafile and showed how you can manually restore and recover it. But what I would like to do now is show you how you can use the Data Recovery Advisor to help you through this process.  So first what I do is to delete one of the datafiles again, simulating a similar issue as in the previous example.  But this time instead of executing the RMAN commands manually to perform the restore and recovery I will use the Data Recovery Advisor.  Below are the steps that I followed to restore datafile 6 this time.

SQL> startup
ORACLE instance started.
Total System Global Area  521936896 bytes
Fixed Size                2229944 bytes
Variable Size           327158088 bytes
Database Buffers        188743680 bytes
Redo Buffers              3805184 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DATA/testdb/datafile/data.284.798052235'

As you can see file 6 is now missing. I login to RMAN and execute the “list failure” command to show me what the Data Recovery Advisor has picked up:

oracle@dbvlin201[/usr/local/dbvisit/standby]: rman

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 31 10:57:32 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: TESTDB (DBID=2569715825, not open)

RMAN> list failure;
using target database control file instead of recovery catalog

List of Database Failures
=========================
Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
1062       HIGH     OPEN      31/10/2012 10:32:05 One or more non-system datafiles are missing

The next step was to execute the “advise failure” command to see what the suggested course of action would be to resolve this failure.

RMAN> advise failure;

List of Database Failures
=========================
Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
1062       HIGH     OPEN      31/10/2012 10:32:05 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file +DATA/testdb/datafile/data.284.798052235 was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 6

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/testdb/testdb/hm/reco_66710732.hm

As you can see above there are two options provided, the Manual and Automated options. We are going to use the Automated option, which will execute the repair script listed above. The next step then is to execute the repair command to automatically fix the issue for me. To do this I execute the “repair failure” command, and with this the restore and recovery process is started for you.

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/testdb/testdb/hm/reco_66710732.hm
contents of repair script:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 31/10/2012 10:58:16
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to +DATA/testdb/datafile/data.284.798052235
channel ORA_DISK_1: reading from backup piece /backups/rman/testdb/14np2kdc_1_1
channel ORA_DISK_1: piece handle=/backups/rman/testdb/14np2kdc_1_1 tag=TAG20121030T171940
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 31/10/2012 10:58:19

Starting recover at 31/10/2012 10:58:19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 31/10/2012 10:58:20

sql statement: alter database datafile 6 online
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

From the above output you can see that the “repair failure” command started the restore and recovery process after confirming with me that this is what I wanted to do. It also picked up the database was still mounted and asked if it can open the database, to which I also responded with YES, and within a few minutes my database was back up and running. This just makes backup and recovery so much easier. The extract below shows you that all my datafiles are now looking good following the restore and recovery:

SQL> select file#, status, name from v$datafile;

FILE#      STATUS  NAME
---------- ------- --------------------------------------------------
1          SYSTEM  +DATA/testdb/datafile/system.256.786387593
2          ONLINE  +DATA/testdb/datafile/sysaux.257.786387599
3          ONLINE  +DATA/testdb/datafile/undotbs1.258.786387599
4          ONLINE  +DATA/testdb/datafile/users.259.786387601
5          ONLINE  +DATA/testdb/datafile/data.274.798055391
6          ONLINE  +DATA/testdb/datafile/data.284.798116297
6 rows selected.

SQL> select file#, name, status, error from v$datafile_header;

FILE#      NAME                                               STATUS  ERROR
---------- -------------------------------------------------- ------- ----------
1          +DATA/testdb/datafile/system.256.786387593         ONLINE
2          +DATA/testdb/datafile/sysaux.257.786387599         ONLINE
3          +DATA/testdb/datafile/undotbs1.258.786387599       ONLINE
4          +DATA/testdb/datafile/users.259.786387601          ONLINE
5          +DATA/testdb/datafile/data.274.798055391           ONLINE
6          +DATA/testdb/datafile/data.284.798116297           ONLINE
6 rows selected.

Once done you can use the “list failure closed” command to show you a listing of closed (resolved) failures. I have not gone into detail relating to the Data Recovery Advisor, but wanted to highlight this feature for you. My suggestion is that you have a look at this feature and test it yourself. There are more detailed explanations in the Oracle Database Backup and Recovery User’s Guide regarding the use of this utility. It may well come in handy one day in helping you quickly resolve an issue.

Note: Data Recovery Advisor option is not available in RAC If you haven't already been sure to check out the post that preceded this one, Three Steps to Getting Started with RMAN.