Why are there multiple rows in V$SESSION_LONGOPS when running an RMAN backup?

Answer:
When performing an RMAN backup, V$SESSION_LONGOPS will be populated with two types of rows:

Detailed rows: Captures the progress of individual job steps.

Aggregated rows: Captures and summarizes the progress for the individual step of the backup job, as they complete. They are updated only after each detailed step has finished.

Example:
Below I am querying the V$SESSION_LONGOPS after an incremental backup level 0 has started, using 6 channels. Notice that there are both individual rows and an aggregated row present:

SELECT SID,SERIAL#,OPNAME,TARGET_DESC,TOTALWORK,SOFAR,UNITS,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE
FROM V$SESSION_LONGOPS 
WHERE  TOTALWORK <> 0
AND SOFAR <> TOTALWORK
ORDER BY SERIAL# ASC;

SID	SERIAL#	OPNAME	TARGET_DESC	TOTALWORK	SOFAR	UNITS	START_TIME	TIME_REMAINING	ELAPSED_SECONDS	MESSAGE
203	7409	RMAN: incremental datafile backup	Set Count	4096000	1004926	Blocks	01.08.2014 12:28:28	557	181	RMAN: incremental datafile backup: Set Count 137085: 1004926 out of 4096000 Blocks done
398	15079	RMAN: incremental datafile backup	Set Count	3018368	1122302	Blocks	01.08.2014 12:28:28	306	181	RMAN: incremental datafile backup: Set Count 137088: 1122302 out of 3018368 Blocks done
439	17451	RMAN: incremental datafile backup	Set Count	2560000	1127038	Blocks	01.08.2014 12:28:29	229	180	RMAN: incremental datafile backup: Set Count 137089: 1127038 out of 2560000 Blocks done
357	31627	RMAN: incremental datafile backup	Set Count	3044608	1115518	Blocks	01.08.2014 12:28:28	313	181	RMAN: incremental datafile backup: Set Count 137087: 1115518 out of 3044608 Blocks done
476	55467	RMAN: incremental datafile backup	Set Count	2557440	940542	Blocks	01.08.2014 12:28:29	309	180	RMAN: incremental datafile backup: Set Count 137090: 940542 out of 2557440 Blocks done
512	55527	RMAN: aggregate input	backup	76514816	4360436	Blocks	01.08.2014 12:28:28	2085	126	RMAN: aggregate input: backup 33: 4360436 out of 76514816 Blocks done
281	56085	RMAN: incremental datafile backup	Set Count	3251200	969214	Blocks	01.08.2014 12:28:28	426	181	RMAN: incremental datafile backup: Set Count 137086: 969214 out of 3251200 Blocks done

Let's join V$SESSION_LONGOPS with V$SESSION and (if desirable) V$PROCESS, to view only the detailed rows, and estimate the progress in percent. This query is very useful to quickly get an overview of your backup, whether or not it is progressing according to expected speed etc:

SELECT S.CLIENT_INFO "Client Info", SL.OPNAME "Operation" ,SL.MESSAGE, SL.SID, SL.SERIAL#, P.SPID "OS Process ID", SL.SOFAR "So Far", SL.TOTALWORK "Totalwork", ROUND(SL.SOFAR/SL.TOTALWORK*100,2) "% complete"
FROM V$SESSION_LONGOPS SL INNER JOIN V$SESSION S ON SL.SID = S.SID 
                          INNER JOIN V$PROCESS P ON P.ADDR = S.PADDR
AND OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/

Client Info	Operation	MESSAGE	SID	SERIAL#	OS Process ID	So Far	Totalwork	% complete
rman channel=tsm_channel_0	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137096: 1173118 out of 2252800 Blocks done	203	7409	42926864	1173118	2252800	52,07
rman channel=tsm_channel_1	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137095: 2029054 out of 2393600 Blocks done	281	56085	52101750	2029054	2393600	84,77
rman channel=tsm_channel_2	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137097: 293246 out of 2096640 Blocks done	357	31627	56361104	293246	2096640	13,99
rman channel=tsm_channel_3	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137099: 174206 out of 2048000 Blocks done	398	15079	52756692	174206	2048000	8,51
rman channel=tsm_channel_4	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137098: 460286 out of 2048000 Blocks done	439	17451	29032454	460286	2048000	22,47
rman channel=tsm_channel_5	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137100: 196094 out of 2048000 Blocks done	476	55467	9700724	196094	2048000	9,57

Notice how the V$RMAN_STATUS only hold information on aggregated level (2 rows, one for each operation although the first spawns the other):

SELECT SID,OPERATION,STATUS,MBYTES_PROCESSED, START_TIME, END_TIME, OBJECT_TYPE, OUTPUT_DEVICE_TYPE 
FROM V$RMAN_STATUS WHERE STATUS = 'RUNNING';

SID	OPERATION	STATUS	MBYTES_PROCESSED	START_TIME	OBJECT_TYPE	OUTPUT_DEVICE_TYPE
512	BACKUP	RUNNING	226641,953125	01.08.2014 12:28:28	DB INCR	SBT_TAPE
512	RMAN	RUNNING	0	01.08.2014 12:28:28

Workaround for RMAN-11003 and ORA-32010 during cloning

At the end of the duplication procedure, my script threw the following error:

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
 RMAN-08031: released channel: c1
 RMAN-08031: released channel: c2
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of Duplicate Db command at 06/12/2014 17:40:14
 RMAN-05501: aborting duplication of target database
 RMAN-03015: error occurred in stored script Memory Script
 RMAN-03009: failure of sql command on clone_default channel at 06/12/2014 17:40:14
 RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile
 ORA-32010: ORA-32010: cannot find entry to delete in SPFILE 

 Recovery Manager complete.

After a brief search I found that my init.ora was the culprit: I had accidently left out single quotes in the db_file_name_convert parameter:

*.db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01','/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

Oracle Support Dok ID 1384262.1 "ORA-32010: cannot find entry to delete in SPFILE" points this out.

The spfile was not in $OH/dbs physically, although Oracle believes it does:

SQL> show parameter spfile

NAME        TYPE        VALUE
 ---------- ----------- ---------------------------------------------
 spfile     string      /u01/oracle/product/11204/dbs/spfiletestdb01.ora

To prove it, I attempted to create an spfile:

SQL> create spfile from pfile;
 create spfile from pfile
 *
 ERROR at line 1:
 ORA-32002: cannot create SPFILE already being used by the instance 

When attempting to mount:

SQL> alter database mount;
 alter database mount
 *
 ERROR at line 1:
 ORA-01103: database name PRODDB01 in control file is not TESTDB01

So the control files are incorrect, written to during the duplication, and RMAN was unfortunately interrupted before the duplication procedure completed.

Solution:
Recreate the controlfile from the Source database, and open the auxiliary database with this new controlfile instead of the one that was generated during duplication.


1. Log onto the source server on which the PRODDB01 database is hosted, and execute

SQL> alter database backup controlfile to trace as '/tmp/proddb01_cntrfile.sql';

2. Transfer the file /tmp/proddb01_cntrfile.sql to the testserver
3. Shutdown the auxiliary database
4. Edit the script:
* Go to the section that says "Set #2. RESETLOGS case". You can strip away anything above this section, as you won't be needing it.
* Use the physical paths valid on the auxiliary server
* Exchange "REUSE DATABASE" with "SET DATABASE"
From Source database:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "PRODDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/proddb01/redo1.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/proddb01/redo2.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/proddb01/redo3.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/proddb01/redo4.log'  SIZE 1000M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
   '/u02/oradata/proddb01/datafile/system.428.688578763',
   '/u02/oradata/proddb01/datafile/undotbs1.489.688574787',
   '/u02/oradata/proddb01/datafile/sysaux.436.688579377'
.
.
.
CHARACTER SET AL32UTF8
;

The new edited version:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE
 SET DATABASE "TESTDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/testdb01/redo1.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/testdb01/redo2.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/testdb01/redo3.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/testdb01/redo4.log'  SIZE 1024M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
  '/u02/oradata/testdb01/system.428.688578763',
   '/u02/oradata/testdb01/undotbs1.489.688574787',
   '/u02/oradata/testdb01/sysaux.436.688579377',
   '/u02/oradata/testdb01/users.476.688577593',
   '/u02/oradata/testdb01/undotbs1.510.688564845',
   '/u02/oradata/testdb01/sl_part_data_2006.473.688577939',
.
.
.
CHARACTER SET AL32UTF8
;

5. Run the script on the newly cloned database:

SQL> sqlplus / as sysdba @proddb01_cntrfile.sql
Oracle instance started

 Total System Global Area    5344731136 bytes

 Fixed Size                     2255784 bytes
 Variable Size               1459618904 bytes
 Database Buffers            3875536896 bytes
 Redo Buffers                   7319552 bytes

Control file created.

6. Mount the database

SQL> alter database mount;

Database mounted.

7. Open the database

SQL> alter database open resetlogs;

Database opened.

Finally I had to handle TEMP tablespaces, as the alert log would tell you:

Dictionary check beginning
 Tablespace 'TEMP' #3 found in data dictionary,
 but not in the controlfile. Adding to controlfile.
 *********************************************************************
 WARNING: The following temporary tablespaces contain no files.
          This condition can occur when a backup controlfile has
          been restored.  It may be necessary to add files to these
          tablespaces.  That can be done using the SQL statement:

          ALTER TABLESPACE  ADD TEMPFILE

          Alternatively, if these temporary tablespaces are no longer
          needed, then they can be dropped.
            Empty temporary tablespace: TEMP

Add a file to the temporary tablespace using the REUSE clause:

SQL> alter tablespace temp add tempfile '/u02/oradata/testdb01/temp01.dbf' size 1024M reuse autoextend on next 32M maxsize unlimited;

How to solve RMAN-06217 during cloning from active database

RMAN-03002: failure of Duplicate Db command at 06/12/2014 12:28:00
 RMAN-05501: aborting duplication of target database
 RMAN-06217: not connected to auxiliary database with a net service name

Workaround is to connect to the auxiliary database using username and password, followed by the TNS alias.
So instead of

 connect target sys/<password>@PRODDB01
 connect rmanuser/<password>@RMANCAT
 connect auxiliary /

use

 connect target sys/<password>@PRODDB01
 connect rmanuser/<password>@RMANCAT
 connect auxiliary sys/<password>@AUX01

Examples of RMAN duplicate database scripts

I prefer to set the parameters db_file_name_convert and log_file_name_convert whenever possible, to simplify the redirection of restored files.

Here is an example when parameter log_file_name_convert is not set. We are also specifying the auxiliary channels with vendor specific tape settings:

run { 
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64; 
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=myschedule'; 
set until scn 5886541624244; 
duplicate target database to AUXDB1 
logfile group 1 ('/data/oracle/u01/AUXDB1/redo01.log') size 128M, 
        group 2 ('/data/oracle/u02/AUXDB1/redo02.log') size 128M, 
        group 3 ('/data/oracle/u03/AUXDB1/redo03.log') size 128M;
} 

Another example, when both db_file_name_convert and log_file_name_convert are set.
In smaller environments, you may not need to bother specifying different backup polices etc, you simply specify 'SBT_TAPE' and the

run {
allocate auxiliary channel c1 type 'SBT_TAPE';
allocate auxiliary channel c2 type 'SBT_TAPE';
set until time "to_date('01.05.2014 18:00:00','DD.MM.YYYY HH24:MI:SS')";
duplicate target database to AUXDB1; 
}

How to backup and delete a specific range of archivelogs

Use the "BACKKUP ARCHIVELOG FROM LOGSEQ .. UNTIL LOGSEQ" syntax:

RMAN> backup archivelog from logseq 3 until logseq 4 delete input;

Output will be similar to:

Starting backup at 27-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=3 STAMP=848499170
input archived log thread=1 sequence=4 RECID=4 STAMP=848499311
channel ORA_DISK_1: starting piece 1 at 27-MAY-14
channel ORA_DISK_1: finished piece 1 at 27-MAY-14
piece handle=/u04/archive/PRODDB01/backupset/2014_05_27/o1_mf_annnn_TAG20140527T115156_9r8r1wt7_.bkp tag=TAG20140527T115156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_3_9r3qksmy_.arc RECID=3 STAMP=848499170
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_4_9r3qp6mt_.arc RECID=4 STAMP=848499311

A check in the V$ARCHIVED_LOG reveals what Oracle has done:

SQL>  select name,sequence#,archived,applied,deleted,status,IS_RECOVERY_DEST_FILE,compressed from v$archived_log;

NAME                                                                      SEQUENCE# ARC APPLIED   DEL S IS_ COM
------------------------------------------------------------------------ ---------- ---------- --- --------- --- - --- ---
                                                                                   1 YES NO        YES D YES NO
                                                                                   2 YES NO        YES D YES NO
                                                                                   3 YES NO        YES D YES NO
                                                                                   4 YES NO        YES D YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_5_9r3r0fo6_.arc                5 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_6_9r3rc6pf_.arc                6 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_7_9r3rmkpr_.arc                7 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_8_9r3s0vs3_.arc                8 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_9_9r3sbst4_.arc                9 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_10_9r3so0v9_.arc              10 YES NO        NO  A YES NO

How to perform a clone from active database

With Oracle 11g, a new method to clone databases was introduced: "Clone from Active Database".
This method allows for cloning of a database using RMAN's DUPLICATE command, but without being dependent on any previous backups being available.

The following are my notes on how to use this feature to produce a clone from production database PRODDB01 to test database TESTDB01.

In file run_duplication.cmd:

connect catalog cataloguser/password@RMANCAT
connect target sys/clonedb@PRODDB01
connect auxiliary sys/clonedb@TESTDB01;
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
run {
debug io;
DUPLICATE TARGET DATABASE TO TESTDB01
FROM ACTIVE DATABASE
SKIP TABLESPACE TOOLS;
debug off;
}
exit

to execute:

rman cmdfile='run_duplication.cmd' debug trace='run_duplication.trc' log='run_duplication.log'


For this to work smoothly I followed the principles below:

• Script executed from the target server
• Auxiliary database must be in NOMOUNT mode
• Sys passwords must be identical on both source and target.
• Use the notation uid/pwd@<ORACLE_SID> when you Connect to both target and auxiliary. Do not use operating system authenticaion ( connect / ) - it will throw an error
• The db_block_size parameter must be identical on both source and target
• TNS connection must exist on both sides e.g. communication must be able to go from and from the source and the target. If not you will see the error

RMAN-03002: failure of Duplicate Db command at 07/21/2015 10:06:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c1 channel at 07/21/2015 10:06:15
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

• I set the db_file_name_convert and the log_file_name_convert parameter in the auxiliary database. Make sure you check the location of the tempfiles, too, and include this in the db_file_name_convert value.
• I had no success using the TABLESPACE clause (which means an implicit EXCLUDE of all other tablespaces except those named explicitly)
• Using SKIP TABLESPACE ,, etc works, if the tablespaces are self-contained, meaning there are no referential constraints from object in one tablespace to another (even if both are skipped!)

Remember to reregister the duplicated database if it's backed up using a catalog database:

RMAN> connect target /
RMAN> list incarnation of database;
RMAN> register database;
RMAN> list incarnation of database;


The prerequisites for ACTIVE DATABASE cloning as laid out in the Oracle documentation are:

• At least one normal target channel and at least one AUXILIARY channel are required.
• When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication.
• The source database must be mounted or open. If the source database is open, then archiving must be enabled.
• If the source database is not open, then it must have been shut down consistently.
• When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.
• The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files.
• You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

How to deal with RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous

It turned out that the my RMAN session didn't know which DBID to perform the requested command against.

I checked my incarnation list, and observed the following:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       198     PTADB01  2645386576       PARENT  6275306386559 10.04.11
1       2       PTADB01  2645386576       CURRENT 6278643127311 20.04.11
681     923     PTADB01  2663743751       PARENT  6275306386559 10.04.11
681     682     PTADB01  2663743751       CURRENT 6435835146938 16.11.11

Note how two of my incarnations are marked as being PARENT. Never saw this before.

A quick search on the internet pointed me to setting the DBID explicitly, so I vierfied that the DBID listed in the incarnation list above was indeed correct, by checking the backup logs files, and set it in the RMAN session:

RMAN> set DBID=2663743751;

So when the database name is not unique in the recovery catalog, you need to point out which one to use before RMAN can work.
The restore now worked, and the database could be mounted.

How to enable block change tracking for faster incremental backups:

From Oracle 10g and onwards, you can enable block change tracking to speed up incremental backups. In short,

"if change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile"

Syntax:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/path/PRODDB_rman_change_track.dbf' REUSE;

Database altered.

More info here

How to deregister a database from a Recovery Catalog

Log on to the target database:

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:00:50 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
 305453026 PRODDB01

connect to the recovery catalog through sqlplus:

sqlplus uid/pwd@RMANCAT

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:01:33 2014

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

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

SQL> select db_key,dbid,name from rc_database
  2  where dbid=305453026;

    DB_KEY       DBID NAME
---------- ---------- --------
         1  305453026 PRODDB01

Finally, execute the procedure dbms_rcvcat.unregisterdatabase, passing in the parameters db_key and dbid:

SQL> execute dbms_rcvcat.unregisterdatabase(1,305453026);

PL/SQL procedure successfully completed.

How to restore a sequence of archivelogs back to their original location

connect catalog catowner/*********@RMANCAT
connect target
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
restore archivelog from logseq 412594 until logseq 412656;
release channel t1;
release channel t2;
}

How to use the DUPLICATE ... FOR STANDBY command to create a physical standby database

run {
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=my_schedule';
allocate auxiliary channel aux1 device type disk;
set until logseq=l2345 thread=1;
duplicate target database for standby dorecover;
release channel t1;
release channel t2;
}

Note that the parameters db_file_name_convert and log_file_name_convert must be set in order to use this simplified syntax.
If not, you must specify file location for all the involved files and redo log members.

How to use RMANs RESTORE...VALIDATE command

The RESTORE ... VALIDATE and VALIDATE BACKUPSET commands test whether you can restore from your backups:

RESTORE CONTROLFILE VALIDATE;
RESTORE TABLESPACE SYSTEM VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

This example validates the restore of backup sets 56 and 57:

VALIDATE BACKUPSET 56,57;

Same thing, but for individual datafiles:

RESTORE DATAFILE 4,5,6 VALIDATE;

In addition to listing the files needed for restore and recovery, the RESTORE ... VALIDATE HEADER command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository:

RESTORE DATABASE VALIDATE HEADER;

How to use RMANs RESTORE PREVIEW command

The RESTORE PRIVIEWS: identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry
out a given restore operation, based on the information in the RMAN repository.

These commands are brilliant for planning restore and recovery operations, for example

ensuring that all required backups are available

identifying RMAN backups you'd like to avoid or use

RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
RESTORE DATABASE UNTIL TIME "TO_DATE('18.11.2014 18:00:00','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

Use the RESTORE... PREVIEW SUMMARY option to suppress much of the detail about specific files used and
affected by the restore process:

RESTORE DATABASE PREVIEW SUMMARY;
RESTORE DATABASE UNTIL TIME "TO_DATE('31-08-2010 18:00:00', 'DD-MM-YYYY HH24:MI:SS')" PREVIEW;
RESTORE DATABASE UNTIL TIME 'sysdate-3' PREVIEW SUMMARY;
RESTORE DATABASE UNTIL TIME 'sysdate-1/24' preview summary; -->; one hour ago
RESTORE TABLESPACE users PREVIEW SUMMARY;
RESTORE DATAFILE 3 PREVIEW SUMMARY;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW SUMMARY;
-- For pluggable databases, add the keyword "pluggable" to the very same statement
RESTORE PLUGGABLE DATABASE PDB2 until time "TO_DATE('31-08-2010 18:00:00', 'DD-MM-YYYY HH24:MI:SS')" PREVIEW;

To avoid the following error stack:

Starting restore at 02.12.2014 16:51:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/02/2014 16:51:25
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile n

you need to allocate the appropriate channels for maintenance first, such as

allocate channel for maintenance type 'sbt_tape';

Output example from a preview started 06.05.2015 14:18:12, checking to see what would be needed if I took the database back approximately 1,5 hours:

RMAN> restore database until time "TO_DATE('06.05.2015 12:52:00','DD.MM.YYYY HH24:MI:SS')" preview summary;

Starting restore at 06.05.2015 14:18:12

using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
using channel ORA_DISK_2


List of Backups
===============

Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---

373688959 B  0  A SBT_TAPE    30.04.2015 17:42:55 1       1       NO         20150430173000.SOLP2
373688963 B  0  A SBT_TAPE    30.04.2015 17:43:06 1       1       NO         20150430173000.SOLP2
373688965 B  0  A SBT_TAPE    30.04.2015 17:43:21 1       1       NO         20150430173000.SOLP2
373688962 B  0  A SBT_TAPE    30.04.2015 17:43:03 1       1       NO         20150430173000.SOLP2
373688958 B  0  A SBT_TAPE    30.04.2015 17:42:26 1       1       NO         20150430173000.SOLP2
373688961 B  0  A SBT_TAPE    30.04.2015 17:43:02 1       1       NO         20150430173000.SOLP2
373688956 B  0  A SBT_TAPE    30.04.2015 17:42:22 1       1       NO         20150430173000.SOLP2
373688957 B  0  A SBT_TAPE    30.04.2015 17:42:24 1       1       NO         20150430173000.SOLP2
373688960 B  0  A SBT_TAPE    30.04.2015 17:42:58 1       1       NO         20150430173000.SOLP2
373927006 B  0  A SBT_TAPE    06.05.2015 12:49:58 1       1       NO         20150506123352.SOLP2
373688966 B  0  A SBT_TAPE    30.04.2015 17:45:12 1       1       NO         20150430173000.SOLP2
373688964 B  0  A SBT_TAPE    30.04.2015 17:43:12 1       1       NO         20150430173000.SOLP2

List of Backups

===============

Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---

373689024 B  A  A SBT_TAPE    30.04.2015 17:45:28 1       1       NO         TAG20150430T174527
373701547 B  A  A SBT_TAPE    30.04.2015 21:40:29 1       1       NO         TAG20150430T214020
373708776 B  A  A SBT_TAPE    30.04.2015 23:40:24 1       1       NO         TAG20150430T234018
373715185 B  A  A SBT_TAPE    01.05.2015 03:40:28 1       1       NO         TAG20150501T034020
373718630 B  A  A SBT_TAPE    01.05.2015 06:40:49 1       1       NO         TAG20150501T064020
373747390 B  A  A SBT_TAPE    01.05.2015 23:40:33 1       1       NO         TAG20150501T234022
373755172 B  A  A SBT_TAPE    02.05.2015 03:40:31 1       1       NO         TAG20150502T034022
373758977 B  A  A SBT_TAPE    02.05.2015 06:40:41 1       1       NO         TAG20150502T064016
373763033 B  A  A SBT_TAPE    02.05.2015 09:40:27 1       1       NO         TAG20150502T094020
373772933 B  A  A SBT_TAPE    02.05.2015 12:40:20 1       1       NO         TAG20150502T124018
373775169 B  A  A SBT_TAPE    02.05.2015 14:40:17 1       1       NO         TAG20150502T144016
373818387 B  A  A SBT_TAPE    04.05.2015 01:01:30 1       1       NO         TAG20150502T164016
373826167 B  A  A SBT_TAPE    04.05.2015 03:41:13 1       1       NO         TAG20150504T034021
373826168 B  A  A SBT_TAPE    04.05.2015 03:41:33 1       1       NO         TAG20150504T034021
373830219 B  A  A SBT_TAPE    04.05.2015 06:41:12 1       1       NO         TAG20150504T064022
373835638 B  A  A SBT_TAPE    04.05.2015 09:40:22 1       1       NO         TAG20150504T094018
373847467 B  A  A SBT_TAPE    04.05.2015 12:40:35 1       1       NO         TAG20150504T124024
373889376 B  A  A SBT_TAPE    05.05.2015 14:40:43 1       1       NO         TAG20150505T144022
373889377 B  A  A SBT_TAPE    05.05.2015 14:40:58 1       1       NO         TAG20150505T144022
373889378 B  A  A SBT_TAPE    05.05.2015 14:41:26 1       1       NO         TAG20150505T144022
373889379 B  A  A SBT_TAPE    05.05.2015 14:42:03 1       1       NO         TAG20150505T144022
373889380 B  A  A SBT_TAPE    05.05.2015 14:42:20 1       1       NO         TAG20150505T144022
373889381 B  A  A SBT_TAPE    05.05.2015 14:42:37 1       1       NO         TAG20150505T144022
373924623 B  A  A SBT_TAPE    06.05.2015 12:32:13 1       1       NO         TAG20150506T123133
373924624 B  A  A SBT_TAPE    06.05.2015 12:32:45 1       1       NO         TAG20150506T123133
373924625 B  A  A SBT_TAPE    06.05.2015 12:33:02 1       1       NO         TAG20150506T123133
373927099 B  A  A SBT_TAPE    06.05.2015 13:01:48 1       1       NO         TAG20150506T130147
373927100 B  A  A SBT_TAPE    06.05.2015 13:01:49 1       1       NO         TAG20150506T130147

validation succeeded for backup piece
Media recovery start SCN is 169070460360
Recovery must be done beyond SCN 169092024179 to clear datafile fuzziness
validation succeeded for backup piece
Finished restore at 06.05.2015 14:18:16

How to use RMANs LIST command to find individual archivelogs or a sequence of archivelogs

To list an individual backup, any of the three alternatives bellow would be valid query using rman:

RMAN> list backup of archivelog logseq=120316;
RMAN> list backup of archivelog logseq 120316;
RMAN> list backup of archivelog sequence 120316;

To view backups of archivelog between two sequences:

RMAN> list backup of archivelog sequence between 120316 and 120317;
RMAN> list backup of archivelog from logseq 412593 until logseq 412656;

Use the SUMMARY directive to view only the backupsets affected:

RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary;
RMAN> list backup of archivelog sequence between 120316 and 120317 summary;

To view backups completed before a specific date:

RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list archivelog all completed before 'sysdate-2';:

To list archivelogs recognized by the controlfile between two sequnce numbers:

RMAN> list archivelog sequence between 110880 and 110881;

To list archivelogs recognized by the controlfile up until yesterday:

RMAN> list archivelog until time 'sysdate-1';

To list archivelogs recognized by the controlfile from two days ago, up until yesterday:

RMAN> list archivelog from time 'sysdate-2' until time 'sysdate-1';

To limit the list of archivelogs to the ones taken on a specific date, in this case, the last of January 2022:

RMAN> list archivelog from time '31.01.2022' until time '01.02.2022';

If you remove the "until time" clause, RMAN will list all the archivelogs from 31.01.2022 up until the latest ones:

RMAN> list archivelog from time '31.01.2022';

Remember, the value of sysdate is the time right now, sysdate-1 is 24hrs ago, sysdate-2 is 48 hours ago, etc.

 select sysdate, sysdate-1, sysdate-2 from dual;

SYSDATE             SYSDATE-1           SYSDATE-2
------------------- ------------------- -------------------
03.02.2022 12:24:12 02.02.2022 12:24:12 01.02.2022 12:24:12

Template for RMAN script, including Netbackup directives

Executed as user oracle directly on the server:

connect target /
connect catalog rmancat/password@RMANCAT
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=my_policy_name,NB_ORA_CLIENT=my_client_name-bkp.mydomain.com,NB_ORA_SCHED=my_schedule_name';

rman commands here


release channel t1;
release channel t2;
}

to execute:

$ rman cmdfile='restore_archlog.cmd' log='restore_archlog.log'

How the Archived Redo Log Deletion Policy works

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Note that *only* logs in the flash recovery area can be deleted automatically by the database.

You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.

When the Archived Redo Log Deletion Policy Is ENABLED

You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.

For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.

You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

When the Archived Redo Log Deletion Policy Is DISABLED

In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.

The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.


Source: Oracle Documentation

How to restore archive logs to a new destination

This example restores all archived redo logs to the /oracle/temp_restore directory:

RMAN> RUN
{ 
  SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore/'; <-- note the last /
  RESTORE ARCHIVELOG ALL;
}

If the file is already on disk you will get an error message from RMAN.

To override this, use the force option:

RMAN> run {
2> allocate channel t1 device type 'sbt';
3> set archivelog destination to '/oracle/temp_restore/';
4> restore archivelog logseq 15572 force;
5> }

Possible reason for RMAN-06059: "expected archived log not found, lost of archived log compromises recoverability"

After having changed the archive log destination, I received the following when performing ARCHIVELOG backups:

Starting backup at 25-NOV-2008
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/25/2008 10:57:56
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /app/oracle/admin/PRODDP/archive670098694_1_1.arc
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

I needed to run a crosscheck after having pointed out the new archive log destination to the recovery catalog:

allocate channel for maintenance type disk; 
run {
set archivelog destination to '/app/oracle/admin/PRODDB/archive';
change archivelog all crosscheck;
}

After this has been run, I could backup up the ARCHIVELOGS successfully using tape (or disk) backups:

run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=O_SYR_loc1,NB_ORA_CLIENT=prodserver1-bkp.mydomain.net,NB_ORA_SCHED=O_SYR_loc1_user';
backup archivelog all; 
release channel t1;
}

How to map the operating system sessions with RMAN Channels

When only one RMAN session is active, the easiest method for determining the server session ID for an RMAN channel is to execute the following query on the target database while the RMAN job is executing:

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
;

If you do not run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:

rman channel=channel_id

For example, in my case:

SID SPID         CLIENT_INFO
---- ------------ ------------------------------
  14 8374         rman channel=ORA_SBT_TAPE_1

As pointed out by the oracle documentation, you can monitor the sbt events:

COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, 
       sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 's%bt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
;

In an example, the output from the above could look as below:

SPID EVENT             SEC_WAIT   STATE                CLIENT_INFO
---- ----------------- ---------- -------------------- ------------------------------
8642 Backup: sbtbackup 600        WAITING              rman channel=ORA_SBT_TAPE_1

indicating that RMAN has been waiting for the sbtbackup function to return for ten minutes.

What is an RMAN CROSSCHECK?

Purpose: To ensure that data about backups in the recovery catalog or control file is synchronized with actual files on disk or in the media management catalog.

If the backup is on disk, then the CROSSCHECK command checks whether the header of the file is valid.
If the backup is on tape, then the crosscheck command checks that the backups exist in the media management software's catalog.

Backup pieces and image copies can have the status AVAILABLE, EXPIRED, or UNAVAILABLE.

NOTE:
The CROSSCHECK command *does not* delete operating system files or remove repository records.
For such operations, you must use the DELETE command.

In short, in RMAN terms, obsolete means "file is not needed", whereas expired means it "file not found".