Oracle Backup and Recovery Solutions

When implementing a backup and recovery strategy, you have the following solutions available:

– Recovery Manager (RMAN)

This tool integrates with sessions running on an Oracle database to perform a
range of backup and recovery activities, including maintaining an RMAN
repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

– User-managed backup and recovery

In this solution, you perform backup and recovery with a mixture of host
operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery.

RMAN performs the same types of backup and recovery available through user-managed techniques more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.
Most of this manual focuses on RMAN-based backup and recovery.

User-managed backup and recovery techniques are covered in Section VIII, “Performing User-Managed Backup and Recovery.” RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

– Incremental backups

An incremental backup stores only blocks changed since a previous backup.
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery. If you enable block change tracking, then you can improve performance by avoiding full scans of every input datafile. You use the BACKUP INCREMENTAL command to perform incremental backups.

– Block media recovery

You an repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup. You use the RECOVER command to perform block media recovery.

– Unused block compression

In unused block compression, RMAN can skip data blocks that have never been used and, in some cases, used blocks that are currently unused.

– Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups.

– Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format. To create encrypted backups on disk, the database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option.
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility.

You can later use Data Pump Import to re-create data after restore and recovery. Logical backups are for the most part beyond the scope of the backup and recovery documentation.

– Oracle Flashback Technology

As explained in Oracle Database Concepts, Oracle Flashback Technology complements your physical backup and recovery strategy. This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

– Logical Flashback Features

Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

– Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

Oracle Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start and end time, operation, and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

Oracle Database Advanced Aplication Developer’s Guide explains how to use this feature.

– Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions uring a period of time. Oracle Database Advanced Application Developer’s Guide xplains how to use this feature.

– Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction, and any transactions that could be dependent on it, had never happened. Oracle
Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past
without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining
associated attributes such as current indexes, triggers, and constraints, and in this way enabling you to avoid finding and restoring database-specific properties.

– Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. “Rewinding a DROP
TABLE Operation with Flashback Drop” on page 16-7 explains how to use this
feature.

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

– Flashback Database

At the physical level, Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but is generally much faster because it does not require restoring datafiles from backup and requires less redo than media recovery.

Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a flash recovery area for a database because the flashback logs can only be stored there. Flashback logging is not enabled by default. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the flash recovery area.

Oracle Database also supports restore points in conjunction with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

– Data Recovery Advisor

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

A database failure usually manifests itself as a set of symptoms: error messages, alerts, trace files and dumps, and failed data integrity checks. Data Recovery Advisor automatically diagnoses and informs you of these failures. Within the context of Data Recovery Advisor, a failure is a persistent data corruption that can be directly mapped to a set of repair actions. Each failure has a status of open or closed. Each failure also has a priority of critical, high, or low.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it with respect to the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

RMAN BASICS

RMAN is not “too” different from WinZip. Since everybody knows WinZip, it will probably be easier to understand RMAN. Just like compressing a folder using WinZip, RMAN creates a compressed backup of the physical database files, including controlfiles, datafiles, archived logs and stores them somewhere. This somewhere can be a disk or a tape.

Therefore, the first important point to remember is:

“RMAN creates compressed backups of the physical database; if you prefer, RMAN zips the database, the way you like it”.

The usual definition for RMAN is given as,

Recovery Manager is a client/server application that uses database server sessions to perform backup and recovery. It stores metadata about its operations in the control file of the target database and, optionally, in a recovery catalog schema in an Oracle database.

Why Should we use RMAN

Ability to perform incremental backups.Ability to recover one block of a datafile.
Ability to perform the backup and restore with parallelization.
Ability to automatically delete archived redo logs after they are backed up.
Ability to automatically backup the control file and the SPFILE.
Ability to restart a failed backup without having to start from the beginning.
Ability to verify the integrity of the backup.
Ability to test the restore process without having to actually perform the restore.
Comparison of RMAN Automated and User-Managed Procedures
By using operating system commands for User-Managed Backup and Recovery , a DBA manually keeps track of all database files and backups. But RMAN performs these same tasks automatically.

Understanding the RMAN Architecture
An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side, TARGET DATABASE (This is the database which needs to be backed up) and RECOVERY CATALOG (Recovery catalog is optional otherwise backup details are stored in target database controlfile .)

About the RMAN Repository

The RMAN repository is a set of metadata that RMAN uses to store information about the target database and its backup and recovery operations. RMAN stores information about:

Backup sets and pieces
Image copies (including archived redo logs)
Proxy copies
The target database schema
Persistent configuration settings
If you start RMAN without specifying either CATALOG or NOCATALOG on the command line, then RMAN makes no connection to a repository. If you run a command that requires the repository, and if no CONNECT CATALOG command has been issued yet, then RMAN automatically connects in the default NOCATALOG mode. After that point, the CONNECT CATALOG command is not valid in the session.

Types of Database Connections

You can connect to the following types of databases.
Target database
RMAN connects you to the target database with the SYSDBA privilege. If you do not have this privilege, then the connection fails.

Recovery catalog database

This database is optional: you can also use RMAN with the default NOCATALOG option.

Auxiliary database

You can connect to a standby database, duplicate database, or auxiliary instance (standby instance or tablespace point-in-time recovery instance
Note:
That a SYSDBA privilege is not required when connecting to the recovery catalog. The only requirement is that the RECOVERY_CATALOG_OWNER role be granted to the schema owner.

Using Basic RMAN Commands

After you have learned how to connect to a target database, you can immediately begin performing backup and recovery operations. Use the examples in this section to go through a basic backup and restore scenario using a test database. These examples assume the following:

The test database is in ARCHIVELOG mode.
You are running in the default NOCATALOG mode.
The RMAN executable is running on the same host as the test database.

Connecting to the Target Database

rman TARGET /

If the database is already mounted or open, then RMAN displays output similar to the following:

Recovery Manager: Release 9.2.0.0.0

connected to target database: RMAN (DBID=1237603294)

Reporting the Current Schema of the Target Database
In this example, you generate a report describing the target datafiles. Run the report schema command as follows:

RMAN> REPORT SCHEMA; (RMAN displays the datafiles currently in the target database.

Backing Up the Database

In this task, you back up the database to the default disk location. Because you do not specify the format parameter in this example, RMAN assigns the backup a unique filename.

You can make two basic types of backups: full and incremental.

Making a Full Backup

Run the backup command at the RMAN prompt as follows to make a full backup of the datafiles, control file, and current server parameter file (if the instance is started with a server parameter file) to the default device type:
RMAN> BACKUP DATABASE;

Making an Incremental Backup

Incremental backups are a convenient way to conserve storage space because they back up only database blocks that have changed. RMAN compares the current datafiles to a base backup, also called a level 0 backup, to determine which blocks to back up.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backing Up Archived Logs
Typically, database administrators back up archived logs on disk to a third-party storage medium such as tape. You can also back up archived logs to disk. In either case, you can delete the input logs automatically after the backup completes.To back up all archived logs and delete the input logs (from the primary archiving destination only), run the backup command at the RMAN prompt as follows:

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

Listing Backups and Copies

To list the backup sets and image copies that you have created, run the list command as follows:

RMAN> LIST BACKUP;

To list image copies, run the following command:

RMAN> LIST COPY;

the difference between recovery and restoring of the database

Question: What is the difference between recovery and restoring of the database?

Answer: Here is a scenario to understand Restore & Recovery

Sunday 10pm : Database is backed up. and is running fine.

Monday 11am : Went down / crashed due to some reason.

To bring up the database, we have 2 options:

1. Simple Restore : copying files from backup taken Sunday night and open the database. Here, we loose all the changes that are done since Sunday night.

2. Restore and Recovery: Copying files from backup taken Sunday night and applying all the archivelog and redo log files to bring up the database to the point of failure. Here you don’t loose the changes done until Monday 11 am.

Restore: Copying Files from the Backup Overwriting the Existing Database Files

Recovery: Applying the Changes to the Database Till Point of Failure. These Changes Are Recorded In Online Redolog And Archivelog (Which Are The Backups Of Redolog) Files.

What is difference between Flash Recovery Area and Flashback Database?

About Flash Recovery Area

The flash recovery area is a directory on the storage that provides a centralized disk location for backup and recovery files. The directory location is specified by parameter DB_RECOVERY_FILE_DEST and the size of the recovery area is defined by parameter DB_RECOVERY_FILE_DEST_SIZE. If log_archive_dest_* is set to USE_DB_RECOVERY_FILE_DEST then Oracle creates archived logs in the flash recovery area. RMAN can also store its backups in the flash recovery area ( and stores the backups in FRA by default), and it uses it when restoring files during media recovery.

All files necessary to recover the database following a media failure are located in the flash recovery area ( if defined ).

The flash recovery area also acts as a disk cache for tape. The recommended strategy is store the backups in the FRA, to have more recent backups available on disk, then the backup and restore processes will be faster.

About Flashback Database

Flashback database is a new feature introduced in 10g.

It provides a way to quickly revert entire Oracle database to the state it was in at a past point in time. Also it is possible to bring a single table back in time.

Flashback database is different from traditional point in time recovery. To Flashback a database, Oracle uses older block images to back out changes to the database, what means that the oracle blocks are being overwritten for its before images to bring the database back in time.

If flashback database feature is enabled, during normal database operation, Oracle creates the Flashback logs, where the before image of the database blocks is stored previously to applying any change in this database blocks.

The following recovery-related files are stored in the flash recovery area:

– Current control file

— Online redo logs

— Archived redo logs

— Flashback logs

— Control file autobackups

— Datafile and control file copies

— Backup pieces

— Foreign archived redo log ( An archived redo log received by a logical standby database for a LogMiner session.)

Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.

Flashback Database is not true media recovery, because it does not involve restoring physical files. However, Flashback is preferable to using the RESTORE and RECOVER commands in some cases, because it is faster and easier, and does not require restoring the whole database.

You can limit the number of flashback logs stored by defining the parameter:

– DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) which defines the upper limit in minutes on how far back one can flashback the database.

Note that, you cannot manually delete the flashback logs, you need to turn flashback database off to delete the flashback logs.

Flash Recovery Area(10g) / Fast Recovery Area(11gR2)

The Recovery Area: Why it is recommended

With 10g, the concept of the Recovery Area – associated with the parameter DB_RECOVERY_FILE_DEST – was introduced. The picture below illustrates it:

The Recovery AreaThe name Recovery Area indicates that this is the place to find everything needed to do a successful recovery in case of a damage (or a logical mistake) in the Database Area.

The Recovery Area was designed in the first place for customers who use Disk Storage as their primary backup media. Meanwhile, due to the ever decreasing price of Disk Storage, this should be the majority of customers. In spite of that, there seems to be some kind of reluctance to actually make use of the multiple benefits, the Recovery Area delivers.

This posting aims to show how deal with the Recovery Area in order to
a) Protect Controlfiles & Online Logfiles
b) Implement a robust Backup Strategy easily
c) Manage Flashback Logs

Let’s start with
a) How does the Recovery Area help to protect Controlfiles & Online Logfiles?

It should be consensus that the Controlfiles and especially the Online Logfiles are sensitive and must be mirrored. If we use the parameter DB_CREATE_FILE_DEST (introduced in 9i already) together with DB_RECOVERY_FILE_DEST, this is done automatically during the Database creation. It is typically but not necessarily so that we use DB_CREATE_FILE_DEST together with ASM. My demo Database is without ASM here:

[oracle@uhesse ~]$ cat skripte/initprima.ora 

compatible=11.2.0.2
db_block_size=8192
db_name='prima'
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u02/fra'
db_recovery_file_dest_size=2g
memory_target=640m
undo_management=auto
undo_tablespace=undotbs1
db_domain=''
diagnostic_dest='/u01/app/oracle/diag'

That is my minimalistic init.ora. Notice the absence of the parameter CONTROL_FILES. Now look how the two parameters make it easy for me to create my Database with mirrored Control- and Online Logfiles:

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:48:08 2011

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

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/skripte/initprima.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  668082176 bytes
Fixed Size            2229480 bytes
Variable Size          398461720 bytes
Database Buffers      264241152 bytes
Redo Buffers            3149824 bytes
SQL> create database undo tablespace undotbs1
     default tablespace users
     default temporary tablespace temp
     character set al32utf8;

Database created.

That was it already and it took less than a minute. Some more minutes will it take to run catalog & catproc, though. Let’s investigate the new Database:

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name,bytes/1024/1024 as mb from v$datafile union
     select name,bytes/1024/1024 from v$tempfile;

NAME                                                                           MB
---------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf             100
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf             100
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_temp_75d3oynt_.tmp               100
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf            10
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf              100

We see above the effect of specifying the Database Area with DB_CREATE_FILE_DEST: The system created a subdirectory according to the DB_UNIQUE_NAME (derived from DB_NAME in this case) as well as another subdirectory beneath according to the file type.
Now to the mirroring of our most sensitive files:

SQL> select name,is_recovery_dest_file from v$controlfile;

NAME                                                                   IS_
---------------------------------------------------------------------- ---
/u01/app/oracle/oradata/PRIMA/controlfile/o1_mf_756xwk4p_.ctl           NO
/u02/fra/PRIMA/controlfile/o1_mf_756xwk70_.ctl                         YES

SQL> select member,is_recovery_dest_file from v$logfile;

MEMBER                                                                 IS_
---------------------------------------------------------------------- ---
/u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_1_756xwk95_.log           NO
/u02/fra/PRIMA/onlinelog/o1_mf_1_756xwkfr_.log                         YES
/u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_2_756xwkn5_.log           NO
/u02/fra/PRIMA/onlinelog/o1_mf_2_756xwl1t_.log                         YES

We see only with this approach a ‘YES’ in the IS_REVOVERY_DEST_FILE column of v$controlfile. If you specify the CONTROL_FILES parameter manually (with one file pointing to the Recovery Area) before the CREATE DATABASE command, this does not count. I admit that in earlier versions of 10g, the systems handling of the CONTROL_FILES parameter was not optimal, because it did not write the automatically created Controlfiles into the spfile. But the newer releases do:

SQL> select sid,name,value from v$spparameter where isspecified='TRUE';

SID    NAME                           VALUE
------ ------------------------------ ----------------------------------------------------------------------
*      processes                      100
*      memory_target                  671088640
*      control_files                  /u01/app/oracle/oradata/PRIMA/controlfile/o1_mf_756xwk4p_.ctl
*      control_files                  /u02/fra/PRIMA/controlfile/o1_mf_756xwk70_.ctl
*      db_block_size                  8192
*      compatible                     11.2.0.2
*      db_create_file_dest            /u01/app/oracle/oradata
*      db_recovery_file_dest          /u02/fra
*      db_recovery_file_dest_size     2147483648
*      undo_management                auto
*      undo_tablespace                undotbs1
*      db_domain
*      db_name                        prima
*      diagnostic_dest                /u01/app/oracle/diag

14 rows selected.

A valid reason not to go with this method (mirroring Controlfiles & Online Logfiles into the Recovery Area) would be if the devices there a significantly slower than the ones that make up the Database Area. We may still use the Recovery Area to keep our Backups, Archivelogs & Flashback Logs there, though. Because the devices underneath /u01 and /u02 have similar performance characteristics, it is perfectly OK as it is now on my demo system. Apart from performance aspects, it is of course of key importance to have the Recovery Area placed on different physical devices than the Database Area.

We have two V$-Views that reflect the usage of the Recovery Area:

SQL> select * from V$RECOVERY_FILE_DEST;

NAME                           SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------ ----------- ---------- ----------------- ---------------
/u02/fra                        2147483648  218316800                 0               3

The SPACE* columns have the unit bytes, unfortunately. SPACE_LIMIT is derived from the dynamic parameter DB_RECOVERY_FILE_DEST_SIZE that was  set here to the (very small) value 2G. SPACE_RECLAIMABLE is derived from the size of the Backups/Archivelogs, being obsolete according to our RMAN retention policy. The next view is newer and shows also the file types, consuming space in the Recover Area:

SQL> select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .36                         0               1
REDO LOG                           9.77                         0               2
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

Because the Database is neither in Archivelog mode nor in Flashback mode yet and we did not take backups, the only files in the Recovery Area are the mirrors of the Online Logs and the Controlfile. We wil change that with the next step.

b) How does the Recovery Area help to implement a robust Backup Strategy?

We will put the Database in Archivelog mode and see that the Archivelogs are being created automatically into the Recovery Area:

SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
Total System Global Area  668082176 bytes
 Fixed Size                  2229480 bytes
 Variable Size             444599064 bytes
 Database Buffers          218103808 bytes
 Redo Buffers                3149824 bytes
 Database mounted.
 SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$archived_log;
NAME
 ----------------------------------------------------------------------
 /u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_7_75d56p79_.arc
1 row selected.
SQL> select * from v$recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
 -------------------- ------------------ ------------------------- ---------------
 CONTROL FILE                        .36                         0               1
 REDO LOG                           9.77                         0               2
 ARCHIVED LOG                       1.46                         0               1
 BACKUP PIECE                          0                         0               0
 IMAGE COPY                            0                         0               0
 FLASHBACK LOG                         0                         0               0
 FOREIGN ARCHIVED LOG                  0                         0               0
7 rows selected.

Notice that I did not need to specify a single initialization parameter of the LOG_ARCHIVE* range for this; easy, isn’t it? Now we come to the almost only remaining difficulty: Specifying an appropriate value for DB_RECOVERY_FILE_DEST_SIZE. That parameter sets a logical space limit, our Database is allowed to consume inside of the Recovery Area. We must set it large enough for our Backups & Archivelogs to fit in, according to our retention policy. Let’s have a look how large our backups get for this Database:

SQL> select name,bytes/1024/1024 as mb from v$datafile union
     select name,bytes/1024/1024 from v$tempfile;    

NAME                                                                           MB
---------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf             100
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf             200
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_temp_75d3oynt_.tmp               100
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf           180
/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf              100

5 rows selected.

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

        MB
----------
       580

1 row selected.

After catalog & catproc did run, the files grew little but still this is a very tiny Database. With Image Copies, my full Backup would take 580 MB in size, while Backup Sets would be smaller, depending on the amount of empty space inside of the datafiles that Backup Sets would not contain. We are going to take an ordinary default online backup:

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
[oracle@uhesse ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 25 11:40:26 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIMA (DBID=1992392973)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIMA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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.2/db_1/dbs/snapcf_prima.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup database;

Starting backup at 25-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-11
channel ORA_DISK_1: finished piece 1 at 25-AUG-11
piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T114118_75d65z8m_.bkp tag=TAG20110825T114118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-AUG-11

Starting Control File and SPFILE Autobackup at 25-AUG-11
piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760102882_75d6633f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-11

We could see that the Backup Set as well as the Controlfile Autobackup (a must to switch on!) were taken into the Recovery Area. Now to our calculation about the Recovery Area sizing:

SQL> select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .36                         0               1
REDO LOG                           9.77                         0               2
ARCHIVED LOG                       1.46                      1.46               1
BACKUP PIECE                      19.07                         0               2
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1g;

System altered.

SQL> select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .73                         0               1
REDO LOG                          19.53                         0               2
ARCHIVED LOG                       2.93                      2.93               1
BACKUP PIECE                      38.14                         0               2
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

With this (very small) value of DB_RECOVERY_FILE_DEST_SIZE, it should still be possible for RMAN to take another full backup into the Recovery Area – because only about 50 % of space is consumed by not obsolete files – after which the previous backup becomes obsolete, together with the Archivelogs, belonging to that backup. Our goal is to just take backups (every night, for example) and let the obsolete backups & archivelogs get deleted automatically. We must keep in mind that it is of key importance that archive logs can be created always to avoid archiver stuck problems, though. Let’s look at the desired behavior:

RMAN> backup database;

Starting backup at 25-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-11
channel ORA_DISK_1: finished piece 1 at 25-AUG-11
piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T115312_75d6w8bd_.bkp tag=TAG20110825T115312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-AUG-11

Starting Control File and SPFILE Autobackup at 25-AUG-11
piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760103595_75d6wcng_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-11

RMAN> exit

Recovery Manager complete.
[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 25 11:53:23 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SQL> set lines 200
SQL> select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .73                         0               1
REDO LOG                          19.53                         0               2
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      38.89                       .74               3
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

We took another backup and the previous backup got obsolete. Automatically, obsolete files got deleted because of space pressure inside of the Recovery Area:

[oracle@uhesse ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Thu Aug 25 11:56:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle/diag"
adrci> show alert -tail
2011-08-25 11:24:26.308000 +02:00
Starting background process CJQ0
CJQ0 started with pid=25, OS id=2495
2011-08-25 11:24:38.156000 +02:00
Thread 1 advanced to log sequence 8 (LGWR switch)
  Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_2_75d3og50_.log
  Current log# 2 seq# 8 mem# 1: /u02/fra/PRIMA/onlinelog/o1_mf_2_75d3ogms_.log
Archived Log entry 1 added for thread 1 sequence 7 ID 0x76c1760d dest 1:
2011-08-25 11:34:24.262000 +02:00
Starting background process SMCO
SMCO started with pid=23, OS id=2529
2011-08-25 11:39:10.821000 +02:00
Stopping background process CJQ0
2011-08-25 11:46:00.516000 +02:00
ALTER SYSTEM SET db_recovery_file_dest_size='1G' SCOPE=BOTH;
2011-08-25 11:53:14.365000 +02:00
Deleted Oracle managed file /u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_7_75d56p79_.arc
2011-08-25 11:53:15.647000 +02:00
Deleted Oracle managed file /u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T114118_75d65z8m_.bkp

Although it worked well in this scenario, the logical limit is set a little too optimistic here; a peak in Archivelog creation could easily lead to Archiver Stuck problems. Therefore, we increase the Recovery Area to a safer size and look at the consequences of that:

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

RMAN> backup database;

Starting backup at 25-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-11
channel ORA_DISK_1: finished piece 1 at 25-AUG-11
piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T120213_75d7f5wk_.bkp tag=TAG20110825T120213 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-AUG-11

Starting Control File and SPFILE Autobackup at 25-AUG-11
piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760104136_75d7f8wb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-11

RMAN> backup database;

Starting backup at 25-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-11
channel ORA_DISK_1: finished piece 1 at 25-AUG-11
piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T120231_75d7fqcq_.bkp tag=TAG20110825T120231 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-AUG-11

Starting Control File and SPFILE Autobackup at 25-AUG-11
piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760104154_75d7ftjw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-11

SQL> select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .36                         0               1
REDO LOG                           9.77                         0               2
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      58.05                     38.75               7
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

After increasing the logical limit, we took two new backups. Although our retention policy is still redundancy 1, the accordingly obsolete backups did not get deleted yet, because there is no space pressure. Keep in mind that the system will consume almost the complete space that we allow with DB_RECOVERY_FILE_DEST_SIZE, therefore! The background of that is that we may make use of the old obsolete backups for a conventional Database Point In Time Recovery (DBPITR) without Flashback Logs. From now on, we will just take our (nightly) backups and let the system take care about obsolete backups & archivelogs. We should monitor V$RECOERY_AREA_USAGE or rely on an Enterprise Manager Alert that raises automatically if space in the Recovery Area gets scarce.

In other words: We just need to figure out once an appropriate logical limit and the system takes care of itself subsequently, while we just monitor it. This approach is much easier than other methods used before the introduction of the Recovery Area.

c) How to manage Flashback Logs together with the Recovery Area?

If we want to be able to do Flashback Database, we must have a Recovery Area to place the Flashback logs into:

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,open_mode from v$database;

FLASHBACK_ON       OPEN_MODE
------------------ --------------------
YES                READ WRITE

SQL> show parameter flashback_retention_target

NAME                                 TYPE         VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target         integer     1440

From now on, we will be able to do a very fast DBPITR with Flashback Database, by default one day (1440 minutes) back into the past. Notice that this target is not mandatory and will not lead to a “Flashback Stuck” if space is consumed already by non obsolete backups & archivelogs. In order to show that, I will again reduce the logical limit to create a space pressure:

SQL> alter system set db_recovery_file_dest_size=700m;

System altered.

SQL> select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                       1.07                         0               1
REDO LOG                          28.57                         0               2
ARCHIVED LOG                      13.72                         0               1
BACKUP PIECE                      58.63                      1.08               4
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      3.35                         0               3
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL> insert /*+ append */ into sales select * from sales;

3200000 rows created.

SQL> commit;

Commit complete.

The above insert would normally create Flashback Logs but it runs in spite of the full Recovery Area. There is no such thing as “Flashback Stuck” (unless you create a guaranteed restore point, that is). That scenario is somewhat artificial because the command above creates almost no Redo Protocol (the table sales is on NOLOGGING) and also this is the only activity on the system. With the Recovery Area 100% full, the Database would normally get to a hold immediately – not because of the impossibility to create Flashback Logs, but because of Archiver Stuck:

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

Waits ... Second terminal:

[oracle@uhesse ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Thu Aug 25 12:36:18 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle/diag"
adrci> show alert -tail
2011-08-25 12:36:24.236000 +02:00
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARC1: Error 19809 Creating archive log file to '/u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_9_%u_.arc'

Exactly. LGWR cannot write into the Online Logs anymore because no more Archivelogs can be stored in the Recovery Area. Remedy:

adrci> exit
[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 25 12:39:53 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

That resolved the Archiver Stuck problem immediately.

In short: Although it is desireable to have enough space to store flashback logs, it is not mandatory. That is extremely different to archive logs. We should try to make our Recovery Area large enough to store our backups & archive logs according to our Backup Retention, and our Flashback Logs according to our Flashback Retention Target. The space required is partly depending on the Database Load. Therefore, it would be a good idea to start with a somewhat gracefully large DB_RECOVERY_FILE_DEST_SIZE and a relatively short DB_FLASHBACK_RETENTION_TARGET and then monitor V$RECOVERY_AREA_USAGE to come up with an appropriate value for the two parameters.

Coming to the end of this posting, let me try to anticipate the most common objectives against the usage of the Recovery Area:

1. Our Database is too large to go with Disk Storage as the Primary Backup Location!
Agreed. You will only take backups to tape. In spite of that, you may consider to use the Recovery Area to put mirrors of your Controlfiles and Online Logfiles on and to create your Archivelogs into. You must use it if you want to be able to do Flashback Database anyway.

2. We don’t use RMAN!
Agreed. The Recovery Area is not for you. You should have a very good reason not to use RMAN, though.

3. This is too good to be true! We have never done it like that!
Believe it or not, but that is (in slightly other wording, of course) the most common objection that I hear, typically from seasoned DBAs who have crafted their own scripts & methods in 8i already and now hesitate to change that. I have much understanding for the “never touch a running system” axiom. It should not mislead us to discredit new concepts, just because they are different from what we are used to, though. Keep an open mind for (not so new, meanwhile) New Features that are designed to help you to do your job and consider using it at least for the next Database that you create 🙂

RECOVERY: Complete loss of all database

Database Name=ORCL3

Oracle 9i Windows 2003 Env.

DBID=691421794

Backup Available: Full RMAN Online Backup dated: 15/03/2012

: Cumulative and Incremental dated: 18/03/2012

: Recovery dated: 19/03/2012.

Note: You can find the DBID from alert.log with the Controlfile Backupset. For precaution you must keep record of important information of your database.

—————————————————————————————————————————–

For Test Environment:

Shutdown your database and delete all the datafiles and controlfile along with spfile.

C:ORACLE1ORADATADEL *.DBF

C:ORACLE1DATABASEDEL SPFILEORCL3.ORA

Step 1: Create pfile and spfile or restore your old spfile

If you have pfile (you can create from alert. log) then you can startup the database in nomount phase using the pfile and then create spfile from this pfile.

C:SQLplus /nolog

SQL> startup nomount pfile=’Location of pfile’;

SQL> create spfile from pfile = ’Location of pfile’;

Now connect the target database through RMAN and restore controlfile.

Otherwise you can try to restore the spfile directly through RMAN

Create orcl3_spfile.rcv as:

set dbid= 691421794

run {

startup nomount force  ;

};

C:rman target sys/oracle@orcl3 catalog rman/rman@shaan cmdfile=orcl3_spfile.rcv

Recovery Manager: Release 9.2.0.1.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database (not started)

connected to recovery catalog database

RMAN>

executing command: SET DBID

database name is “ORCL3” and DBID is 691421794

Oracle instance started

Total System Global Area     135338868 bytes

Fixed Size                      453492 bytes

Variable Size                109051904 bytes

Database Buffers              25165824 bytes

Redo Buffers                    667648 bytes

RMAN>set dbid=691421794

RMAN>restore spfile ;

Step 2: Restore Controlfile

Same Steps as spfile with the restore command changed. So the new script is

RMAN>set dbid=691421794

RMAN>restore controlfile ;

Step 3: Restore and Recover the database

Since you have the controlfiles now mount the database

SQL> connect sys/oracle@orcl3 as sysdba

Connected.

SQL> alter database mount;

Database altered.

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’ORCL3’;

RMAN> restore database ;

Starting restore at 19-MAR-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=11 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00005 to C:ORACLE1ORADATAORCL3EXAMPLE01.DBF

restoring datafile 00010 to C:ORACLE1ORADATAORCL3XDB01.DBF

restoring datafile 00012 to C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_20120315_L0_ORCL3-1186_1.DB tag=WEEKLY_FULL_DATABASE params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00006 to C:ORACLE1ORADATAORCL3INDX01.DBF

restoring datafile 00007 to C:ORACLE1ORADATAORCL3ODM01.DBF

restoring datafile 00008 to C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_20120315_L0_ORCL3-1189_1.DB tag=WEEKLY_FULL_DATABASE params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to C:ORACLE1ORADATAORCL3UNDOTBS01.DBF

restoring datafile 00009 to C:ORACLE1ORADATAORCL3USERS01.DBF

restoring datafile 00013 to C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_20120315_L0_ORCL3-1187_1.DB tag=WEEKLY_FULL_DATABASE params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to C:ORACLE1ORADATAORCL3SYSTEM01.DBF

restoring datafile 00003 to C:ORACLE1ORADATAORCL3CWMLITE01.DBF

restoring datafile 00004 to C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_20120315_L0_ORCL3-1188_1.DB tag=WEEKLY_FULL_DATABASE params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00011 to C:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_20120315_L0_ORCL3-1190_1.DB tag=WEEKLY_FULL_DATABASE params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 19-MAR-12

RMAN> recover database;

Starting recover at 19-MAR-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00005: C:ORACLE1ORADATAORCL3EXAMPLE01.DBF

destination for restore of datafile 00010: C:ORACLE1ORADATAORCL3XDB01.DBF

destination for restore of datafile 00012: C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_CUMUL_ORCL3-1212_1.DB tag=DAILY_CUMULATIVE_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00002: C:ORACLE1ORADATAORCL3UNDOTBS01.DBF

destination for restore of datafile 00009: C:ORACLE1ORADATAORCL3USERS01.DBF

destination for restore of datafile 00013: C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_CUMUL_ORCL3-1213_1.DB tag=DAILY_CUMULATIVE_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: C:ORACLE1ORADATAORCL3SYSTEM01.DBF

destination for restore of datafile 00003: C:ORACLE1ORADATAORCL3CWMLITE01.DBF

destination for restore of datafile 00004: C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_CUMUL_ORCL3-1214_1.DB tag=DAILY_CUMULATIVE_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00006: C:ORACLE1ORADATAORCL3INDX01.DBF

destination for restore of datafile 00007: C:ORACLE1ORADATAORCL3ODM01.DBF

destination for restore of datafile 00008: C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_CUMUL_ORCL3-1215_1.DB tag=DAILY_CUMULATIVE_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00011: C:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_CUMUL_ORCL3-1216_1.DB tag=DAILY_CUMULATIVE_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00005: C:ORACLE1ORADATAORCL3EXAMPLE01.DBF

destination for restore of datafile 00010: C:ORACLE1ORADATAORCL3XDB01.DBF

destination for restore of datafile 00012: C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_INCR_ORCL3-1220_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00002: C:ORACLE1ORADATAORCL3UNDOTBS01.DBF

destination for restore of datafile 00009: C:ORACLE1ORADATAORCL3USERS01.DBF

destination for restore of datafile 00013: C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_INCR_ORCL3-1221_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: C:ORACLE1ORADATAORCL3SYSTEM01.DBF

destination for restore of datafile 00003: C:ORACLE1ORADATAORCL3CWMLITE01.DBF

destination for restore of datafile 00004: C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_INCR_ORCL3-1222_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00006: C:ORACLE1ORADATAORCL3INDX01.DBF

destination for restore of datafile 00007: C:ORACLE1ORADATAORCL3ODM01.DBF

destination for restore of datafile 00008: C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_INCR_ORCL3-1223_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00011: C:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_20120318_INCR_ORCL3-1224_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL

channel ORA_DISK_1: restore complete

starting media recovery

archive log thread 1 sequence 148 is already on disk as file C:ORACLE1ORADATAORCL3ARCHIVE148.ARC

archive log thread 1 sequence 149 is already on disk as file C:ORACLE1ORADATAORCL3ARCHIVE149.ARC

archive log thread 1 sequence 150 is already on disk as file C:ORACLE1ORADATAORCL3ARCHIVE150.ARC

archive log filename=C:ORACLE1ORADATAORCL3ARCHIVE148.ARC thread=1 sequence=148

media recovery complete

Finished recover at 19-MAR-12

Step 4: Open the database

RMAN> Alter database open;

database opened

NOTE: If your doing incomplete recovery (Recovery by log sequence and by point of time) then you must use resetlogs option to open the database.

Restoring Controlfiles and Spfile

When restoring the control file, the default destination is all of the locations defined in the CONTROL_FILES initialization parameter. If you do not set the CONTROL_FILES initialization parameter, the database uses the same rules to determine the destination for the restored control file as it uses when creating a control file if the CONTROL_FILES parameter is not set.

When you are restoring your control file to a location not listed in the CONTROL_FILES initialization parameter. In that case, you create a copy of your control file in the specified location without touching your running database.

Note: After restoring the control files of your database from backup, you must perform complete media recovery of the database, and then open your database with the RESETLOGS option.

Restore of the Control File from Control File Autobackup

If you are not using a recovery catalog, you must restore your control file from an autobackup. If you want to restore the control file from autobackup, the database must be in a NOMOUNT state.

RMAN> SET DBID 691421794 RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; If your database is still running then use: RMAN> restore until time ‘sysdate-1’ CONTROLFILE to ‘D:controlfile4042012_control01.ctl’ from autobackup; Note: The until time clause set the time validity for controlfile. By default RMAN looks for autobackup for 7 days. If you want to override this then simply use this: RMAN> restore until time ‘sysdate-1 ‘CONTROLFILE to ‘D:controlfile4042012_control01.ctl’ from autobackup maxdays 100; It will force RMAN to look for copies more days.

Restoring a Control File When Using a Recovery Catalog

Restoring a lost control file from autobackup is easier when using a recovery catalog than when using only the control file to store the RMAN repository. The recovery catalog contains a complete record of your backups, including backups of the control file. Therefore, you do not have to specify your DBID or control file autobackup format.

C:RMAN TARGET SYS/ORACLE@ORCL3 CATALOG RMAN/RMAN@SHAAN RMAN> RESTORE CONTROLFILE;

The restored control file is written to all locations listed in the CONTROL_FILES initialization parameter.

Restore of the Control File from a Known (Default) Location

RMAN> RESTORE CONTROLFILE from ‘C:oracle1oradataorcl3CONTROL01.CTL’;

Restore of the Control File to a New Location

RESTORE CONTROLFILE TO ‘D:CONTROLFILE_BACKUP4042012_CONTROL01.CTL’;

You can perform this operation with the database in NOMOUNT, MOUNT or OPEN states, because you are not overwriting any of the control files currently in use. Any existing file named ‘filename’ is overwritten. After restoring the control file to a new location, you can then update the CONTROL_FILES initialization parameter to include the new location.

NOTE: After you restore your database using a backup control file, you must run RECOVER DATABASE and perform an OPEN RESETLOGS on the database

Restoring the Server Parameter File (SPFILE) from Backup

If you lose your server parameter file (SPFILE), RMAN can restore it to its default location or to a location of your choice.

Unlike the loss of the control file, the loss of your SPFILE does not cause your instance to immediately stop. Your instance may continue operating, although you will have to shut it down and restart it after restoring the SPFILE.

If the database is up at the time of the loss of the SPFILE, connect to the target database.

C:rman TARGET /

If the database is not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database

Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file. For example:

RMAN> STARTUP FORCE NOMOUNT;

Restore the server parameter file. If restoring to the default location

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

If Restoring to a nondefault location…..

RMAN> RESTORE SPFILE TO ‘D:backupspfileTEMP.ora’ FROM AUTOBACKUP;

Restart the instance with the restored file. If restarting with a server parameter file in a nondefault location, then create a new pfile.

CREATE PFILE = ‘C:ORACLE1DBSPFILE_TEMP; FROM SPFILE= ‘D:backupspfileTEMP.ora’; RMAN> STARTUP FORCE PFILE‘C:ORACLE1DBSPFILE_TEMP’;

Restore of the SPFILE from the Control File Autobackup

If you have configured control file autobackups, the SPFILE is backed up with the control file whenever an autobackup is taken.

RMAN> SET DBID 691421794; RMAN> RESTORE SPFILE FROM AUTOBACKUP; RMAN> restore until time ‘sysdate-1’ SPFILE to ‘C:tempSPFILE’ from autobackup maxdays 100; After restoring SPFILE You can convert it to PFILE so you can easily see the setting. Complex scenario You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don’t it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it’is part of the name of autobackup file. Details in my previous postRecovery:Complete loss of database”. RMAN target=/ At the beginning set Database ID: RMAN> SET DBID 691421794; Then startup instance: RMAN> startup force nomount; RMAN> RUN { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO D:BACKUPRMANHOTBACKUP%F’; RESTORE SPFILE FROM AUTOBACKUP; } I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them. RMAN> shutdown; RMAN> startup force nomount; RMAN> RUN         { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO D:BACKUPRMANHOTBACKUP%F’; RESTORE CONTROLFILE FROM AUTOBACKUP;         } So you have controlfile take the database in mount phase and perform Media recovery. For that check the earlier post:

Ref:http://shahiddba.blogspot.in/2012/03/restore-and-recovery-database-complete.html