Restoring archive logs from an RMAN backup

Restoring archive logs from an RMAN backup is a common task when you need to recover database transactions to a specific point in time. The commands you've provided are examples of how to restore archive logs using RMAN (Recovery Manager). Below are explanations of each command:

1. Using RESTORE ARCHIVELOG FROM LOGSEQ ... UNTIL LOGSEQ ... THREAD

sql

RMAN> RESTORE ARCHIVELOG FROM LOGSEQ=37501 UNTIL LOGSEQ=37798 THREAD=1;

• Purpose: This command restores archive logs from the RMAN backup, specifically those logs that fall between the log sequence numbers 37501 and 37798 for the specified thread (in this case, thread 1).

• Parameters:

  • FROM LOGSEQ=37501: Specifies the starting sequence number of the archive logs you want to restore.
  • UNTIL LOGSEQ=37798: Specifies the ending sequence number of the archive logs to restore.
  • THREAD=1: Indicates the thread number (useful in RAC environments where there are multiple redo threads).

• Use Case: This approach is ideal when you know the exact range of log sequences you need to restore and want to limit the restoration to a specific thread.

2. Using RESTORE ARCHIVELOG BETWEEN SEQUENCE ... AND ...

sql

RMAN> RESTORE ARCHIVELOG BETWEEN SEQUENCE 37501 AND 37798;

• Purpose: This command restores all archive logs between the specified sequence numbers (37501 to 37798) from all threads unless a specific thread is specified elsewhere.

• Parameters:

  • BETWEEN SEQUENCE 37501 AND 37798: Specifies the range of archive log sequences you want to restore.

• Use Case: This command is useful when you want to restore a continuous range of archive logs across all threads without specifying individual thread numbers.

Key Points:

• Ensure that the archive logs you are restoring are available in your RMAN backup.
• These commands do not apply the restored archive logs; they only restore them to the specified location (usually the archive log destination).
• Check the current location and status of the logs using RMAN commands such as LIST BACKUP OF ARCHIVELOG before performing the restore operation.
• It's critical to be cautious with thread specification, especially in RAC environments, to avoid restoring unnecessary logs or missing required logs.

These commands are powerful tools for managing archive log restoration and can help ensure that you have the necessary logs for database recovery or point-in-time recovery operations.

delete noprompt obsolete archive log - RMAN

RMAN> report obsolete;

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16        

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16          
    
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc RECID=183 STAMP=903955885
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc RECID=189 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc RECID=190 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_148_896707677.arc RECID=191 STAMP=903956192

Deleted 4 objects

Restore and recover database from RMAN backup - DB in No archive log mode

Simple example for restore and recover database from RMAN backup.

Assuming that : I have a need to restore my DB as some important tables/data was dropped/removed and its really mission critical. I have already got some RMAN backups from past.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1728053248 bytes
Fixed Size                  3046512 bytes
Variable Size            1224737680 bytes
Database Buffers          486539264 bytes
Redo Buffers               13729792 bytes
Database mounted.

C:\Users\oracle>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 19 12:46:12 2016

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

connected to target database: ORCLDB (DBID=1196XXXX70)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T155541
21      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
22      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
23      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
24      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T160507


RMAN> restore database from TAG='FULL_ORCLDB';

Starting restore at 19-FEB-15
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 00003 to E:\APP\ORADATA\ORCLDB\UNDOTBS01.DBF
:
:
:
channel ORA_DISK_1: restoring datafile 00119 to E:\APP\ORADATA\TLAPP.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
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 00120 to E:\APP\ORADATA\TLLARGE.DBF
channel ORA_DISK_1: restoring datafile 00121 to E:\APP\ORADATA\TLWORK.DBF
channel ORA_DISK_1: restoring datafile 00122 to E:\APP\ORADATA\WAAPP.DBF
channel ORA_DISK_1: restoring datafile 00123 to E:\APP\ORADATA\ORCLDB\PSMATVW.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
Finished restore at 19-FEB-15

Now lets try opening DB (I know it wont work but lets try..)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\APP\ORADATA\ORCLDB\SYSTEM01.DBF'

Now lets Recover DB

RMAN> recover database;

Starting recover at 19-FEB-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3390 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO03.LOG
archived log for thread 1 with sequence 3391 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO01.LOG
archived log for thread 1 with sequence 3392 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO02.LOG
RMAN-08187: WARNING: media recovery until SCN 69107847 complete
Finished recover at 19-FEB-15



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>

We must open the database in open resetlogs mode. in order for recover to be completed. (WE CAN DO OPEN RESET LOGS FROM RMAN or SQLPLUS PROMPT)

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.

C:\Users\oracle>

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

RMAN archive log backup at the standby site is throws the following errors

********************************************************************************
RMAN-06820: WARNING: failed to archive current log at primary database ORACLE error from target database: ORA-17629: Cannot connect to the remote database server
********************************************************************************


RMAN> backup database plus archivelog tag 'FULL_AL_BKP';


Starting backup at 29-FEB-16
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
skipping archived logs of thread 1 from sequence 192 to 201; already backed up
Finished backup at 29-FEB-16

Starting backup at 29-FEB-16
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/ORCLSTB1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCLSTB1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCLSTB1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLSTB1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLSTB1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-FEB-16
channel ORA_DISK_1: finished piece 1 at 29-FEB-16
piece handle=/u01/app/oracle/backup/ORCLSTB1/ORCL_20160229_82_1_1.bak tag=TAG20160229T114824 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 29-FEB-16

Starting backup at 29-FEB-16
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 29-FEB-16

Starting Control File and SPFILE Autobackup at 29-FEB-16
piece handle=/u01/app/oracle/backup/ORCLSTB1/c-1424488411-20160229-05 comment=NONE
Finished Control File and SPFILE Autobackup at 29-FEB-16


But rest of the backup actually completed successfully.

As per the Source : http://oraclesivaram.blogspot.com/2015/11/rman06820-warning-failed-to-archive.html

****************
CAUSE:
****************

11.2.0.4 onward as per 'unpublished' Bug 8740124, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site. This is achieved by forcing a log switch at the primary site. However, the connection to the primary failed when attempting to do so.This is due to this bug:
Bug 17580082 ACTIVE STANDBY RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY

****************
Workaround:
****************
Do not use operating system authentication to login with RMAN. Use a username and password.

That is, do not use just the "/" (operating system authentication) connect to the standby database:
$ rman target /
Connecting as 'rman target /'

# it gets the sys user but not the password and so, it does NOT mean it is being explicitly specified to connect as sysdba.
Instead put in the username and password for the SYSDBA user:

$ rman target sys/password@stby
Connecting as 'rman target sysdba_user/password@stby'

Note: Also make sure password, within the (ORAPWD) password file, in primary and standby should be identical.

For more details please look at the Doc ID: 1616074.1

restore archive logs from backup

Restoring a archive log (LOG SEQ) from backups.

[oracle@Linux02 backups]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 16 10:47:13 2016

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

connected to target database: ORCL (DBID=1424488411, not open)

RMAN> restore archivelog logseq 140;

Starting restore at 16-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=140
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1 tag=LEVEL0BACKUP_FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-FEB-16

Restoring archive from backups from SEQ. 

This will restore all archive logs from SEQ till date

RMAN> restore archivelog from logseq 140;   

Restoring archive from backups from SEQ until SEQ .

RMAN> restore archivelog from logseq 140 until logseq 150;

Starting restore at 16-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

archived log for thread 1 with sequence 140 is already on disk as file /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=141
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=142
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=143
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=144
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=145
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=146
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=147
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=148
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=149
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=150
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1 tag=LEVEL0BACKUP_FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-FEB-16

Rman Recovery comands....

Rman Time-based PITR

run
{
allocate channel dev1 type disk;
set until time "to_date('2014-03-24:00:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database; }

or

run
{
allocate channel dev1 type disk;
set until time "to_date('2014-03-25:00:00', 'yyyy-mm-dd:hh24:mi')";
restore database;
recover database; }

Rman Time-based Controlfile PITR

run
{
SET UNTIL TIME "TO_DATE('20140323:12:01','YYYYMMDD:HH24:MI')";
RESTORE CONTROLFILE FROM AUTOBACKUP preview;
}

Rman System datafile recovery scenerio

System datafile recovery:

Lets create a scenario where we will drop the system datafile, and we will recover using the rman restore and recover process.Since system datafile is key for database functioning.

Before we start. Lets verify that database is functioning without any problems.

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:47:23 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


Lets drop system datafile now.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

[oracle@linux1 orcl]$ rm system*

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  temp01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

After dropping system datafile. lets query some data from database. We will now see that database is complaining that it can't access system01.dbf

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 21:18:06 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username from dba_users;
select username from dba_users
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now lets start the recover process.Since this is system datafile it is mandatory that you restart the server in mount state. Even if you try to start the
database using startup, it will just mount the database and fail asking for system.dbf file.

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.


Now login into rman session and restore the system datafile.

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 21:19:20 2014

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

connected to target database: ORCL (DBID=1378116623, not open)


RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 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 00001 to /u01/app/oracle/datafiles/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/oraarch/orcl_1_65_850335889.arc
archived log for thread 1 with sequence 66 is already on disk as file /u01/app/oracle/oraarch/orcl_1_66_850335889.arc
archived log for thread 1 with sequence 67 is already on disk as file /u01/app/oracle/oraarch/orcl_1_67_850335889.arc
archived log file name=/u01/app/oracle/oraarch/orcl_1_65_850335889.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:05
Finished recover at 07-JUL-14

Note: In case you don't know the datafile number to recover you can use below query.
Most of the time system datafile is the #1. You can run this query  when DB is in mount state.

SQL> select file#,Name, status from v$datafile where name='SYSTEM';

     FILE# Name STATUS
---------- ----------------------------------- -------
         1 /u01/app/oracle/datafiles/orcl/system01.dbf SYSTEM
   


verify that system datafile has been restored and recovered.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf


Now open the database and query some random data.

SQL> alter database open;

Database altered.

SQL> select count(username) from dba_users;

COUNT(USERNAME)
---------------
             36

RMAN FULL database recovery scenerio

Full database recovery :

We will create a scenerio where we will drop all the datafiles and do a full database recovery.

• Database name : ORCL
• Assuming that you have already taken the full database backup (if not please take the full backup before your perform this test/task).
• Database is running in archive-log mode, if not to have a consistent backup you need to shutdown the database open it in Mount mode and take a full backup.

-----------      If you haven't taken backup do so (please see below)   ------

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 20:20:55 2014

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

connected to target database: ORCL (DBID=1378116623)

RMAN>  backup database tag"full_backup";

Starting backup at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 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/datafiles/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/datafiles/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/datafiles/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/datafiles/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:51
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_ncsnf_FULL_BACKUP_9vpkxymd_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-14

----  Oracle DB restore process -------

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:18:05 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes

lets check name of database:

SQL> select name from v$database;

NAME
---------
ORCL

Check if the database is in archivelog mode;

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oraarch/
Oldest online log sequence     62
Next log sequence to archive   64
Current log sequence           64


list out all the data-files:

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf

Now lets drop all the dbf files:

[oracle@linux1 orcl]$ rm *.dbf

lets verify that all files have been dropped :

[oracle@linux1 orcl]$ ls -al
total 368564
drwxr-x--- 2 oracle oinstall     4096 Jul  7 20:29 .
drwxr-xr-x 3 oracle oinstall     4096 Jun 15 20:22 ..
-rw-r----- 1 oracle oinstall  9912320 Jul  7 20:29 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:29 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:20 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo04.log

Most times you will see that server is still open, even tough you see that database has crashed and you can't access datafile.
we will now do a full database recovery. since all the datafiles have been lost.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'



RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 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 00001 to /u01/app/oracle/datafiles/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/datafiles/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/datafiles/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/datafiles/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 07-JUL-14

Since the full database recovery has been completed successfully. we need to open the database now

SQL> alter database open;

Database altered.

verify that you can access the database without any errors.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


14 rows selected.

RMAN Backup Optimization

From 11G R1 onwards only undo data blocks containing undo data for not yet committed --- or rolled back --- transactions are backed up. Undo data in the undo tablespace still required to satisfy the undo retention but only containing undo records for already committed transactions are not backed up anymore. Nice if you have undo tablespaces of multiple Gb !!!

If backup optimization is configured ( see below ) then
1. Data files belonging to read only tablespaces are not backed up all the time. It is not needed anymore to use the SKIP READONLY clause. ( Note that the SKIP READONLY could not be used for RMAN backups of standby databases. )
2. Data files belonging to tablespaces which were offlined normal are not backed up all the time. It is not needed anymore to use the SKIP TABLESPACE clause.
3. The archivelog sequence is not backed up all the time. This is nice if you keep a large amount of archived redo logs on disk for whatever reason ( Logminer, Streams, Data Guard )

How to configure backup optimization ?

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

What is the RMAN output when files are skipped during backup ?

RMAN> backup as compressed backupset database;

Starting backup at 23-JUN-09
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 4; already backed up 2 time(s)
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_SYSAUX_0MK30K1T_.DBF
input datafile file number=00003 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_UNDOTBS1_0NK30K84_.DBF
input datafile file number=00006 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_ARCHIVE_02_0PK30K9S_.DBF
input datafile file number=00009 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM2_4ZM8LTBW_.DBF
input datafile file number=00011 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_CATALOG_53ZL1X82_.DBF