About Me

Wednesday 29 February 2012

ORACLE DATABASE BLOCK

Oracle database block is the smallest unit of storage in which oracle can store the data.

Different OS platforms vary in size of OS block like Linux has OS block size as 1kb and solaris has OS block size 512 bytes. But Oracle does not consider OS block size as standard Blocksize for database. Oracle takes multiples of OS block size to create one block of Oracle . for example - In oracle 11g, 8 blocks of Linux OS are used to create one block of Oracle.

Oracle creates bigger blocks than the OS so that It can perform I/O operation more efficiently. Suppose  If multi block read count is set to 8 then in one fetch Oracle retrieves 8 blocks from disk that means 64kb of data. In dataware house Generally multi block read count value is set to a high value like 32 because there most of the SQL statements are SELECT commands.


Oracle can have different block sizes in the same database as 2k,4k,8k,16k and 32k depending on requirement.

In Oracle Block we have different parts like-

1.Variable Header- This part of block contains information about block like address of the block.
2.Table Directory- This part contains information about the table whose data is stored in this block.
3.Row directory-This part contains the address of the stored row in data block.
4.Row data-Actual row are stored in this part of the block
5.Free space-This part of the block is reserved for the future updates of the exiting rows.


If PCT USED is set to 40% and PCT FREE is set to 20% then to insert a new row in block more than 60% of the block should be free.

Written by Dharmendra Singh at 07:22 2 comments

Saturday 25 February 2012

HOW TO START ORACLE INSTANCE WITHOUT SPFILE AND PFILE??

This is called a dummy instance which starts with a Memory_target value (automatic memory management). In this situation you need not to define size of any SGA components and Oracle takes care of SGA components sizes and PGA size . Generally we use this instance for restoring spfile from auto backup.

Remove all spfile and pfiles from dbs location and then you should have permission on O_H/rdbms/log directory

RMAN>startup force nomount;


DEMO

C:\Documents and Settings\amit>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Feb 25 20:13:09 2012

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


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

SYS>>startup force

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'H:\APP\ORACLE\PRODUCT\11.1.0\DATABASE\
INITIBM.ORA'

SYS>>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\amit>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 25 20:13:51 2012

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

connected to target database (not started)

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'H:\APP\ORACLE\PRODUCT\11.1.0\DATABASE\
INITIBM.ORA'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1331852 bytes
Variable Size                 67112308 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6688768 bytes

RMAN> exit


Recovery Manager complete.

C:\Documents and Settings\amit>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Feb 25 20:17:31 2012

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


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

SYS>>sho parameter spf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string


Written by Dharmendra Singh at 06:54 3 comments

Sunday 19 February 2012

RMAN "REPORT" COMMAND

RMAN REPORT command is very useful command in order to know the names of the files which need to be backed according to a specified retention policy. 
   
RMAN> report need backup ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    1     H:\FINANCE\SYSTEM01.DBF
2    0     H:\FINANCE\UNDOTBS01.DBF
3    0     H:\FINANCE\SYSAUX01.DBF
4    0     H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
5    0     H:\FINANCE\NOLOGTS.DBF

RMAN> report need backup recovery window of 2 days;

Report of files that must be backed up to satisfy 2 days recovery window
File Days  Name
---- ----- -----------------------------------------------------
2    2322  H:\FINANCE\UNDOTBS01.DBF
3    2322  H:\FINANCE\SYSAUX01.DBF
4    2322  H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP

RMAN> report need backup recovery window of 3 days;

Report of files that must be backed up to satisfy 3 days recovery window
File Days  Name
---- ----- -----------------------------------------------------
2    2322  H:\FINANCE\UNDOTBS01.DBF
3    2322  H:\FINANCE\SYSAUX01.DBF
4    2322  H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP

RMAN> report need backup redundancy 3;

Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    1     H:\FINANCE\SYSTEM01.DBF
2    0     H:\FINANCE\UNDOTBS01.DBF
3    0     H:\FINANCE\SYSAUX01.DBF
4    0     H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
5    0     H:\FINANCE\NOLOGTS.DBF

RMAN> report need backup days 3;

Report of files whose recovery needs more than 3 days of archived logs
File Days  Name
---- ----- -----------------------------------------------------
2    2322  H:\FINANCE\UNDOTBS01.DBF
3    2322  H:\FINANCE\SYSAUX01.DBF
4    2322  H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP

RMAN> report need backup incremental 1;

Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------


RMAN> report need backup incremental 0;

Report of files that need more than 0 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------

RMAN> report need backup recovery window of 3 days database skip tablespace  nologts;

Report of files that must be backed up to satisfy 3 days recovery window
File Days  Name
---- ----- -----------------------------------------------------
2    2322  H:\FINANCE\UNDOTBS01.DBF
3    2322  H:\FINANCE\SYSAUX01.DBF
4    2322  H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP


RMAN> report need backup redundancy 3 datafile 3;

Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
3    0     H:\FINANCE\SYSAUX01.DBF

RMAN> report need backup  tablespace  nologts;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
5    0     H:\FINANCE\NOLOGTS.DBF


RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------

RMAN> report need backup incremental 1;

Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------

RMAN> report need backup device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    1     H:\FINANCE\SYSTEM01.DBF
2    0     H:\FINANCE\UNDOTBS01.DBF
3    0     H:\FINANCE\SYSAUX01.DBF
4    0     H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
5    0     H:\FINANCE\NOLOGTS.DBF

RMAN> report need backup device type sbt;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    0     H:\FINANCE\SYSTEM01.DBF
2    0     H:\FINANCE\UNDOTBS01.DBF
3    0     H:\FINANCE\SYSAUX01.DBF
4    0     H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
5    0     H:\FINANCE\NOLOGTS.DBF

RMAN> report need backup  tablespace  nologts device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
5    0     H:\FINANCE\NOLOGTS.DBF

We can also use REPORT commands to know about the obsolete backups (with respect to a retention policy) which are not relevent. Suppose  you have set retention policy to redundancy 2 and have taken 5 database backups then first 3 backups are obsolete.
 

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          1      30-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_30\O1_MF_1_8_7HVVHLTV_.ARC
Archive Log          2      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_9_7HX2XNFO_.ARC
Archive Log          3      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_10_7HY178WB_.ARC
Archive Log          4      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_11_7HYTDWTC_.ARC
Archive Log          5      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_12_7HZVFGKL_.ARC
Archive Log          6      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_13_7J02VNXS_.ARC
Archive Log          7      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_14_7J0JD298_.ARC
Archive Log          8      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_15_7J2CJVC7_.ARC
Archive Log          9      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_16_7J3HK6H4_.ARC
Archive Log          10     06-JAN-12          H:\FINANCE\ARCH\ARC00027_0770941832.001
Archive Log          11     06-JAN-12          H:\FINANCE\ARCH\ARC00028_0770941832.001
Archive Log          12     06-JAN-12          H:\FINANCE\ARCH\ARC00029_0770941832.001
Archive Log          13     07-JAN-12          H:\FINANCE\ARCH\ARC00030_0770941832.001
Datafile Copy        2      08-JAN-12          H:\RMAN2\DATA_D-FINANCE_I-2590818756_TS-SYSTEM_FNO-1_
32N08GGJ.BKP
Backup Set           98     08-JAN-12
  Backup Piece       263    08-JAN-12          H:\RMAN\3MN08P58_8_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       262    08-JAN-12          H:\RMAN\3MN08P58_7_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       261    08-JAN-12          H:\RMAN\3MN08P58_6_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       260    08-JAN-12          H:\RMAN\3MN08P58_5_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       259    08-JAN-12          H:\RMAN\3MN08P58_4_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       258    08-JAN-12          H:\RMAN\3MN08P58_3_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       257    08-JAN-12          H:\RMAN\3MN08P58_2_1.BKP
Backup Set           98     08-JAN-12
  Backup Piece       256    08-JAN-12          H:\RMAN\3MN08P58_1_1.BKP
Backup Set           97     08-JAN-12
  Backup Piece       255    08-JAN-12          H:\RMAN2\3NN08P58_1_1.BKP
Backup Set           99     08-JAN-12
  Backup Piece       264    08-JAN-12          H:\RMAN\C-2590818756-20120108-14.CTL

RMAN> report obsolete redundancy 3;

Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          1      30-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_30\O1_MF_1_8_7HVVHLTV_.ARC
Archive Log          2      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_9_7HX2XNFO_.ARC
Archive Log          3      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_10_7HY178WB_.ARC
Archive Log          4      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_11_7HYTDWTC_.ARC
Archive Log          5      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_12_7HZVFGKL_.ARC
Archive Log          6      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_13_7J02VNXS_.ARC
Archive Log          7      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_14_7J0JD298_.ARC
Archive Log          8      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_15_7J2CJVC7_.ARC
Archive Log          9      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_16_7J3HK6H4_.ARC
Archive Log          10     06-JAN-12          H:\FINANCE\ARCH\ARC00027_0770941832.001
Archive Log          11     06-JAN-12          H:\FINANCE\ARCH\ARC00028_0770941832.001
Archive Log          12     06-JAN-12          H:\FINANCE\ARCH\ARC00029_0770941832.001
Archive Log          13     07-JAN-12          H:\FINANCE\ARCH\ARC00030_0770941832.001
Datafile Copy        2      08-JAN-12          H:\RMAN2\DATA_D-FINANCE_I-2590818756_TS-SYSTEM_FNO-1_
32N08GGJ.BKP

RMAN> report obsolete redundancy 5;

no obsolete backups found

RMAN> report obsolete redundancy 4;

no obsolete backups found

RMAN> show retention policy;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

RMAN> report obsolete redundancy 3;

Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          1      30-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_30\O1_MF_1_8_7HVVHLTV_.ARC
Archive Log          2      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_9_7HX2XNFO_.ARC
Archive Log          3      31-DEC-11          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2011_12_31\O1_MF_1_10_7HY178WB_.ARC
Archive Log          4      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_11_7HYTDWTC_.ARC
Archive Log          5      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_12_7HZVFGKL_.ARC
Archive Log          6      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_13_7J02VNXS_.ARC
Archive Log          7      01-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_01\O1_MF_1_14_7J0JD298_.ARC
Archive Log          8      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_15_7J2CJVC7_.ARC
Archive Log          9      02-JAN-12          H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVE
LOG\2012_01_02\O1_MF_1_16_7J3HK6H4_.ARC
Archive Log          10     06-JAN-12          H:\FINANCE\ARCH\ARC00027_0770941832.001
Archive Log          11     06-JAN-12          H:\FINANCE\ARCH\ARC00028_0770941832.001
Archive Log          12     06-JAN-12          H:\FINANCE\ARCH\ARC00029_0770941832.001
Archive Log          13     07-JAN-12          H:\FINANCE\ARCH\ARC00030_0770941832.001
Datafile Copy        2      08-JAN-12          H:\RMAN2\DATA_D-FINANCE_I-2590818756_TS-SYSTEM_FNO-1_
32N08GGJ.BKP

RMAN> report obsolete recovery window of 3 days;

no obsolete backups found

RMAN> report obsolete recovery window of 2 days;

no obsolete backups found

RMAN> report obsolete recovery window of 1 days;

no obsolete backups found
Written by Dharmendra Singh at 04:06 1 comments

RMAN LIST COMMAND

 Some times we need to know about what backups we have taken till date in terms of datafiles, tablespaces, time and so on. Here are some usefull RMAN LIST commands .

 1. Following command will show entire database backup taken till now.

 RMAN> list backup of database;

 using target database control file instead of recovery catalog

 List of Backup Sets
===================

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
61      Full    216.98M    DISK        00:00:38     08-JAN-12
        BP Key: 196   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T103332

         Piece Name: H:\RMAN2\2GN087NS_1_1.BKP
  List of Datafiles in backup set 61
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1294477    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1294477    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62      Full    359.86M    DISK        00:01:12     08-JAN-12
  List of Datafiles in backup set 62
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1294475    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1294475    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS
_FNO-4_1IMVMG63.BKP

   Backup Set Copy #1 of backup set 62
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:12     08-JAN-12       NO         TAG20120108T103332

     List of Backup Pieces for backup set 62 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    197     1   AVAILABLE   H:\RMAN\2FN087NS_1_1.BKP
    198     2   AVAILABLE   H:\RMAN\2FN087NS_2_1.BKP
    199     3   AVAILABLE   H:\RMAN\2FN087NS_3_1.BKP
    200     4   AVAILABLE   H:\RMAN\2FN087NS_4_1.BKP
    201     5   AVAILABLE   H:\RMAN\2FN087NS_5_1.BKP
    202     6   AVAILABLE   H:\RMAN\2FN087NS_6_1.BKP
    203     7   AVAILABLE   H:\RMAN\2FN087NS_7_1.BKP
    204     8   AVAILABLE   H:\RMAN\2FN087NS_8_1.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
64      Full    359.70M    DISK        00:00:56     08-JAN-12
        BP Key: 206   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T104152

         Piece Name: H:\RMAN2\2JN0887G_1_1.BKP
  List of Datafiles in backup set 64
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1294673    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1294673    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS
_FNO-4_1IMVMG63.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65      Full    217.08M    DISK        00:00:58     08-JAN-12
  List of Datafiles in backup set 65
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1294670    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1294670    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

   Backup Set Copy #1 of backup set 65
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:58     08-JAN-12       NO         TAG20120108T104152

     List of Backup Pieces for backup set 65 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    207     1   AVAILABLE   H:\RMAN\2IN0887G_1_1.BKP
    208     2   AVAILABLE   H:\RMAN\2IN0887G_2_1.BKP
    209     3   AVAILABLE   H:\RMAN\2IN0887G_3_1.BKP
    210     4   AVAILABLE   H:\RMAN\2IN0887G_4_1.BKP
    211     5   AVAILABLE   H:\RMAN\2IN0887G_5_1.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
67      Full    216.98M    DISK        00:00:38     08-JAN-12
        BP Key: 213   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105510

         Piece Name: H:\RMAN2\2MN0890E_1_1.BKP
  List of Datafiles in backup set 67
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1295303    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1295303    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    216.98M    DISK        00:00:38     08-JAN-12
        BP Key: 214   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105639

         Piece Name: H:\RMAN2\2ON08938_1_1.BKP
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1295337    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1295337    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69      Full    359.90M    DISK        00:01:09     08-JAN-12
  List of Datafiles in backup set 69
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295335    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1295335    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS
_FNO-4_1IMVMG63.BKP

   Backup Set Copy #1 of backup set 69
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:09     08-JAN-12       NO         TAG20120108T105639

     List of Backup Pieces for backup set 69 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    215     1   AVAILABLE   H:\RMAN\2NN08937_1_1.BKP
    216     2   AVAILABLE   H:\RMAN\2NN08937_2_1.BKP
    217     3   AVAILABLE   H:\RMAN\2NN08937_3_1.BKP
    218     4   AVAILABLE   H:\RMAN\2NN08937_4_1.BKP
    219     5   AVAILABLE   H:\RMAN\2NN08937_5_1.BKP
    220     6   AVAILABLE   H:\RMAN\2NN08937_6_1.BKP
    221     7   AVAILABLE   H:\RMAN\2NN08937_7_1.BKP
    222     8   AVAILABLE   H:\RMAN\2NN08937_8_1.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    359.73M    DISK        00:00:59     08-JAN-12
        BP Key: 224   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105813

         Piece Name: H:\RMAN2\2RN08965_1_1.BKP
  List of Datafiles in backup set 71
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295384    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1295384    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS
_FNO-4_1IMVMG63.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72      Full    217.08M    DISK        00:00:59     08-JAN-12
  List of Datafiles in backup set 72
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1295381    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1295381    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

   Backup Set Copy #1 of backup set 72
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:59     08-JAN-12       NO         TAG20120108T105813

     List of Backup Pieces for backup set 72 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    225     1   AVAILABLE   H:\RMAN\2QN08965_1_1.BKP
    226     2   AVAILABLE   H:\RMAN\2QN08965_2_1.BKP
    227     3   AVAILABLE   H:\RMAN\2QN08965_3_1.BKP
    228     4   AVAILABLE   H:\RMAN\2QN08965_4_1.BKP
    229     5   AVAILABLE   H:\RMAN\2QN08965_5_1.BKP

 2. We can also search the database backup by specifying backupset number.

RMAN> list backupset 71;
List of Backup Sets
===================

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    359.73M    DISK        00:00:59     08-JAN-12
        BP Key: 224   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105813

         Piece Name: H:\RMAN2\2RN08965_1_1.BKP
  List of Datafiles in backup set 71
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295384    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1295384    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS
_FNO-4_1IMVMG63.BKP

 3. If we know the backup set tag name then following command is useful.

RMAN> list backupset tag 'TAG20120108T105639';
List of Backup Sets
===================

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    216.98M    DISK        00:00:38     08-JAN-12
        BP Key: 214   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105639
        Piece Name: H:\RMAN2\2ON08938_1_1.BKP
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1295337    08-JAN-12 H:\FINANCE\UNDOTBS01.DBF
  3       Full 1295337    08-JAN-12 H:\FINANCE\SYSAUX01.DBF

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69      Full    359.90M    DISK        00:01:09     08-JAN-12
  List of Datafiles in backup set 69
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295335    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
  4       Full 1295335    08-JAN-12 H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP

   Backup Set Copy #1 of backup set 69
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:09     08-JAN-12       NO         TAG20120108T105639

     List of Backup Pieces for backup set 69 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    215     1   AVAILABLE   H:\RMAN\2NN08937_1_1.BKP
    216     2   AVAILABLE   H:\RMAN\2NN08937_2_1.BKP
    217     3   AVAILABLE   H:\RMAN\2NN08937_3_1.BKP
    218     4   AVAILABLE   H:\RMAN\2NN08937_4_1.BKP
    219     5   AVAILABLE   H:\RMAN\2NN08937_5_1.BKP
    220     6   AVAILABLE   H:\RMAN\2NN08937_6_1.BKP
    221     7   AVAILABLE   H:\RMAN\2NN08937_7_1.BKP
    222     8   AVAILABLE   H:\RMAN\2NN08937_8_1.BKP

 4. We can search particular datafile in backup copy set using follwing command.
RMAN> list copy of datafile 'h:\finance\system01.dbf';
List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
2       1    A 08-JAN-12       1298637    08-JAN-12       H:\RMAN2\DATA_D-FINANCE_I-2590818756_TS-SY
STEM_FNO-1_32N08GGJ.BKP

 5. Following command will list backup of datafile 1 if it has been backed up atleast once in between to specified dates.

 RMAN> list backup of datafile 1 completed between '07-jan-2012' and 'sysdate';
List of Backup Sets
===================

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62      Full    359.86M    DISK        00:01:12     08-JAN-12
  List of Datafiles in backup set 62
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1294475    08-JAN-12 H:\FINANCE\SYSTEM01.DBF

   Backup Set Copy #1 of backup set 62
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:12     08-JAN-12       NO         TAG20120108T103332

     List of Backup Pieces for backup set 62 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    197     1   AVAILABLE   H:\RMAN\2FN087NS_1_1.BKP
    198     2   AVAILABLE   H:\RMAN\2FN087NS_2_1.BKP
    199     3   AVAILABLE   H:\RMAN\2FN087NS_3_1.BKP
    200     4   AVAILABLE   H:\RMAN\2FN087NS_4_1.BKP
    201     5   AVAILABLE   H:\RMAN\2FN087NS_5_1.BKP
    202     6   AVAILABLE   H:\RMAN\2FN087NS_6_1.BKP
    203     7   AVAILABLE   H:\RMAN\2FN087NS_7_1.BKP
    204     8   AVAILABLE   H:\RMAN\2FN087NS_8_1.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
64      Full    359.70M    DISK        00:00:56     08-JAN-12
        BP Key: 206   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T104152
        Piece Name: H:\RMAN2\2JN0887G_1_1.BKP
  List of Datafiles in backup set 64
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1294673    08-JAN-12 H:\FINANCE\SYSTEM01.DBF

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69      Full    359.90M    DISK        00:01:09     08-JAN-12
  List of Datafiles in backup set 69
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295335    08-JAN-12 H:\FINANCE\SYSTEM01.DBF

   Backup Set Copy #1 of backup set 69
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:09     08-JAN-12       NO         TAG20120108T105639

     List of Backup Pieces for backup set 69 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    215     1   AVAILABLE   H:\RMAN\2NN08937_1_1.BKP
    216     2   AVAILABLE   H:\RMAN\2NN08937_2_1.BKP
    217     3   AVAILABLE   H:\RMAN\2NN08937_3_1.BKP
    218     4   AVAILABLE   H:\RMAN\2NN08937_4_1.BKP
    219     5   AVAILABLE   H:\RMAN\2NN08937_5_1.BKP
    220     6   AVAILABLE   H:\RMAN\2NN08937_6_1.BKP
    221     7   AVAILABLE   H:\RMAN\2NN08937_7_1.BKP
    222     8   AVAILABLE   H:\RMAN\2NN08937_8_1.BKP

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    359.73M    DISK        00:00:59     08-JAN-12
        BP Key: 224   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T105813
        Piece Name: H:\RMAN2\2RN08965_1_1.BKP
  List of Datafiles in backup set 71
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1295384    08-JAN-12 H:\FINANCE\SYSTEM01.DBF
6. Using following command we can see the list of archivelog files backed up atleast once to disk.

RMAN> list archivelog all backed up 1 times to disk;
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
1       1    8       A 29-DEC-11 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2011_12_30
\O1_MF_1_8_7HVVHLTV_.ARC
2       1    9       A 30-DEC-11 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2011_12_31
\O1_MF_1_9_7HX2XNFO_.ARC
3       1    10      A 31-DEC-11 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2011_12_31
\O1_MF_1_10_7HY178WB_.ARC
4       1    11      A 31-DEC-11 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_01
\O1_MF_1_11_7HYTDWTC_.ARC
5       1    12      A 01-JAN-12 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_01
\O1_MF_1_12_7HZVFGKL_.ARC
6       1    13      A 01-JAN-12 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_01
\O1_MF_1_13_7J02VNXS_.ARC
7       1    14      A 01-JAN-12 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_01
\O1_MF_1_14_7J0JD298_.ARC
8       1    15      A 01-JAN-12 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_02
\O1_MF_1_15_7J2CJVC7_.ARC
9       1    16      A 02-JAN-12 H:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\FINANCE\ARCHIVELOG\2012_01_02
\O1_MF_1_16_7J3HK6H4_.ARC
10      1    27      A 06-JAN-12 H:\FINANCE\ARCH\ARC00027_0770941832.001
11      1    28      A 06-JAN-12 H:\FINANCE\ARCH\ARC00028_0770941832.001
12      1    29      A 06-JAN-12 H:\FINANCE\ARCH\ARC00029_0770941832.001
13      1    30      A 06-JAN-12 H:\FINANCE\ARCH\ARC00030_0770941832.001
14      1    31      A 07-JAN-12 H:\FINANCE\ARCH\ARC00031_0770941832.001
15      1    32      A 07-JAN-12 H:\FINANCE\ARCH\ARC00032_0770941832.001
16      1    33      A 08-JAN-12 H:\FINANCE\ARCH\ARC00033_0770941832.001
17      1    34      A 08-JAN-12 H:\FINANCE\ARCH\ARC00034_0770941832.001

 7. Following command is going to show you backup of archivelog files taken anytime.

 RMAN> list backup of archivelog all;
List of Backup Sets
===================

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74      54.32M     DISK        00:00:11     08-JAN-12

   List of Archived Logs in backup set 74
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    16      934669     02-JAN-12 957090     02-JAN-12
  1    27      1195959    06-JAN-12 1207064    06-JAN-12
  1    28      1207064    06-JAN-12 1207066    06-JAN-12
  1    29      1207066    06-JAN-12 1207151    06-JAN-12
  1    30      1207151    06-JAN-12 1235904    07-JAN-12
  1    31      1235904    07-JAN-12 1260227    07-JAN-12
  1    32      1260227    07-JAN-12 1292271    08-JAN-12

   Backup Set Copy #1 of backup set 74
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:11     08-JAN-12       NO         TAG20120108T115845

     List of Backup Pieces for backup set 74 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    231     1   AVAILABLE   H:\RMAN\2TN08CNM_1_1.BKP
    232     2   AVAILABLE   H:\RMAN\2TN08CNM_2_1.BKP

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
75      39.26M     DISK        00:00:13     08-JAN-12
        BP Key: 233   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T115845
        Piece Name: H:\RMAN2\2UN08CNM_1_1.BKP

   List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8       723728     29-DEC-11 750991     30-DEC-11
  1    9       750991     30-DEC-11 778009     31-DEC-11
  1    10      778009     31-DEC-11 801454     31-DEC-11
  1    11      801454     31-DEC-11 833254     01-JAN-12
  1    12      833254     01-JAN-12 855003     01-JAN-12
  1    13      855003     01-JAN-12 878802     01-JAN-12
  1    14      878802     01-JAN-12 901427     01-JAN-12
  1    15      901427     01-JAN-12 934669     02-JAN-12

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
76      3.02M      DISK        00:00:00     08-JAN-12
        BP Key: 234   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T115845
        Piece Name: H:\RMAN\2VN08CO6_1_1.BKP

   List of Archived Logs in backup set 76
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    33      1292271    08-JAN-12 1296996    08-JAN-12

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
79      685.50K    DISK        00:00:01     08-JAN-12
        BP Key: 237   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T131318
        Piece Name: H:\RMAN2\34N08H3G_1_1.BKP

   List of Archived Logs in backup set 79
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    34      1296996    08-JAN-12 1298930    08-JAN-12
8. Following command will search in the backup for archivelogs which were generated after a particular SCN.   

RMAN> list backup of archivelog from scn 901427;
List of Backup Sets
===================

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74      54.32M     DISK        00:00:11     08-JAN-12

   List of Archived Logs in backup set 74
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    16      934669     02-JAN-12 957090     02-JAN-12
  1    27      1195959    06-JAN-12 1207064    06-JAN-12
  1    28      1207064    06-JAN-12 1207066    06-JAN-12
  1    29      1207066    06-JAN-12 1207151    06-JAN-12
  1    30      1207151    06-JAN-12 1235904    07-JAN-12
  1    31      1235904    07-JAN-12 1260227    07-JAN-12
  1    32      1260227    07-JAN-12 1292271    08-JAN-12

   Backup Set Copy #1 of backup set 74
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:11     08-JAN-12       NO         TAG20120108T115845

     List of Backup Pieces for backup set 74 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    231     1   AVAILABLE   H:\RMAN\2TN08CNM_1_1.BKP
    232     2   AVAILABLE   H:\RMAN\2TN08CNM_2_1.BKP

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
75      39.26M     DISK        00:00:13     08-JAN-12
        BP Key: 233   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T115845
        Piece Name: H:\RMAN2\2UN08CNM_1_1.BKP

   List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15      901427     01-JAN-12 934669     02-JAN-12

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
76      3.02M      DISK        00:00:00     08-JAN-12
        BP Key: 234   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T115845
        Piece Name: H:\RMAN\2VN08CO6_1_1.BKP

   List of Archived Logs in backup set 76
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    33      1292271    08-JAN-12 1296996    08-JAN-12

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
79      685.50K    DISK        00:00:01     08-JAN-12
        BP Key: 237   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T131318
        Piece Name: H:\RMAN2\34N08H3G_1_1.BKP

   List of Archived Logs in backup set 79
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    34      1296996    08-JAN-12 1298930    08-JAN-12

9. Following command will search in the backup for archivelogs which were generated between two particular SCN.   

RMAN> list backup of archivelog from scn 901427 until scn 901456;
List of Backup Sets
===================

 BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
75      39.26M     DISK        00:00:13     08-JAN-12
        BP Key: 233   Status: AVAILABLE  Compressed: NO  Tag: TAG20120108T115845
        Piece Name: H:\RMAN2\2UN08CNM_1_1.BKP

   List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15      901427     01-JAN-12 934669     02-JAN-12

 10. Following command will show current incarnation as well past incarnation numbers of database.

 RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       FINANCE  2590818756       PARENT  1          30-AUG-05
2       2       FINANCE  2590818756       CURRENT 534907     26-DEC-11
RMAN> list incarnation of database finance;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       FINANCE  2590818756       PARENT  1          30-AUG-05
2       2       FINANCE  2590818756       CURRENT 534907     26-DEC-11


Written by Dharmendra Singh at 02:41 3 comments

RMAN USEFUL COMMANDS


 Here I am introducing some very useful RMAN commands related to backup .

1. If you want to take backup of archivelogs and dont want backup set to be more than 50mb in size then.. It will create backup sets of size 50mb each. Whenever backupset size will go beyond 50mb then a new backupset will be created. Hence each backupset will have size 50mb only.

RMAN> backup maxsetsize 50m archivelog all;

Starting backup at 08-JAN-12
current log archived
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=1 stamp=771287373
input archive log thread=1 sequence=9 recid=2 stamp=771327757
input archive log thread=1 sequence=10 recid=3 stamp=771358788
input archive log thread=1 sequence=11 recid=4 stamp=771384567
input archive log thread=1 sequence=12 recid=5 stamp=771418375
input archive log thread=1 sequence=13 recid=6 stamp=771425997
input archive log thread=1 sequence=14 recid=7 stamp=771439835
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3EN08JID_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3FN08JIM_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
input archive log thread=1 sequence=34 recid=17 stamp=772031598
input archive log thread=1 sequence=35 recid=18 stamp=772033405
input archive log thread=1 sequence=36 recid=19 stamp=772034021
input archive log thread=1 sequence=37 recid=20 stamp=772034088
input archive log thread=1 sequence=38 recid=21 stamp=772034104
input archive log thread=1 sequence=39 recid=22 stamp=772034112
input archive log thread=1 sequence=40 recid=23 stamp=772034124
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3GN08JJ0_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:05
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-10.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

 2. If you want to skip some inaccessible ,offline or read only files from full database backup then here is the command.It will ignore inaccessible ,offline or read only files.

SQL> select file_name,file_id,online_status from dba_data_files;


FILE_NAME                         FILE_ID ONLINE_
------------------------------ ---------- -------
H:\RMAN\DATA_D-FINANCE_I-25908          4 ONLINE
18756_TS-USERS_FNO-4_1IMVMG63.
BKP

H:\FINANCE\SYSAUX01.DBF                 3 ONLINE
H:\FINANCE\UNDOTBS01.DBF                2 RECOVER
H:\FINANCE\SYSTEM01.DBF                 1 SYSTEM

RMAN> backup database
2> skip inaccessible
3> skip offline
4> skip readonly;

Starting backup at 08-JAN-12
using channel ORA_DISK_2
skipping offline file 2
RMAN-06060: WARNING: skipping datafile compromises tablespace UNDOTBS1 recoverability
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3IN08K0L_1_1.BKP tag=TAG20120108T140301 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:56
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-11.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

3. If you want to start a RMAN backup job which should complete in specified time then you can use following command. Here i am specifying  1 minute for database backup job to complete. If job is not complete in 1 minute then it will be aborted.

RMAN> backup duration 00:01 database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_1_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_2_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_3_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_4_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2JN0887G_1_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_5_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:58
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-06.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12


4. In above command the oracle is putting performance impact  on database in order to complete the backup job in given time. In following command Oracle is trying to complete the job in given time without affecting the database performance . Since Performance of database is prime here hence 1 min time is note enough to complete the job and aborted.


RMAN> backup duration 00:01 minimize time database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_1_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_2_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_3_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2MN0890E_1_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:39
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_4_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_5_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 6 at 08-JAN-12
channel ORA_DISK_1: finished piece 6 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_6_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 7 at 08-JAN-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/08/2012 10:56:
11
ORA-19591: backup aborted because job time exceeded duration time


 4. If we increase the time then Backup job is successful. 

RMAN> backup duration 00:02 minimize time database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_1_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_2_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_3_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2ON08938_1_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:38
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_4_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_5_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 6 at 08-JAN-12
channel ORA_DISK_1: finished piece 6 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_6_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 7 at 08-JAN-12
channel ORA_DISK_1: finished piece 7 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_7_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 8 at 08-JAN-12
channel ORA_DISK_1: finished piece 8 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_8_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:11
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-07.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12


5. In following command Oracle will try to adjust database time taken to complete backup job according to rate of taking backup. If backup is going to take less time than specified then Oracle will automatically slowdown the backup speed in order to maximize performance.


RMAN> backup duration 00:02 minimize time minimize load database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_1_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_2_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_3_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_4_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_5_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2RN08965_1_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:02
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-08.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12


6. Following command will take backup of archivelogs generated in between two scn numbers. 


RMAN> backup archivelog from scn 901427 until scn 1296996;

Starting backup at 08-JAN-12
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\36N08IF2_1_1.BKP tag=TAG20120108T133633 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-0C.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

7. Following command will take backup of archivelogs generated between 1st january 2012 and current date.

RMAN> backup archivelog from time '01-jan-2012' until time 'sysdate';

Starting backup at 08-JAN-12
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=11 recid=4 stamp=771384567
input archive log thread=1 sequence=12 recid=5 stamp=771418375
input archive log thread=1 sequence=13 recid=6 stamp=771425997
input archive log thread=1 sequence=14 recid=7 stamp=771439835
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
input archive log thread=1 sequence=34 recid=17 stamp=772031598
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\38N08IO1_1_1.BKP tag=TAG20120108T134120 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:01
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-0D.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

 8. Following command will take backup of archivelogs generated after 05 jan 2012 to current date.

RMAN> backup archivelog from time '05-jan-2012';

Starting backup at 08-JAN-12
current log archived
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
input archive log thread=1 sequence=34 recid=17 stamp=772031598
input archive log thread=1 sequence=35 recid=18 stamp=772033405
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3AN08IRU_1_1.BKP tag=TAG20120108T134325 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-0E.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12




Written by Dharmendra Singh at 01:55 3 comments
Subscribe to: Posts (Atom)