Search This Blog

Total Pageviews

Thursday 14 September 2017

Object list via File and Block ID.

Oracle Object list via File and Block ID. ..


set pause on pagesize 100 linesize 300
column segment_name format a25
column segment_type format a25
select segment_name, segment_type, block_id, blocks from dba_extents where file_id = &file_no AND ( &block_value between block_id and ( block_id + blocks ) )

  

RMAN LIST commands .

Oracle RMAN LIST commands ..

 

some usefull RMAN LIST commands .
 
RMAN> list backup summary;
RMAN> list backup of database;
RMAN> list backup;
RMAN> list backup of pluggable database anuj, anuj1;
RMAN> list copy of datafile  2, 3 ,6 ;
RMAN> list backup of datafile 4 summary;
RMAN> list archivelog all;
RMAN> list backup of archivelog all;
RMAN> list copy of database archivelog all;
RMAN> list incarnation;
RMAN> list backup of controlfile;
RMAN> list backup of spfile;
RMAN> list backup by file;
RMAN> list backup by file;
RMAN> list backup of datafile 1;
RMAN> list expired backup;
RMAN> list expired backup summary;
RMAN> list expired backup of archivelog all;
RMAN> list expired backup of datafile 10;
RMAN> list expired backup of archivelog all summary;
RMAN> list failure;
RMAN> list failure 641231 detail;
RMAN> list backup of tablespace anuj summary;
RMAN> list backup of tablespace test;
RMAN> list backup of archivelog from sequence 55;
RMAN> list backupset of datafile 1;
RMAN> list recoverable backup;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST backup tag 'ORCLEE_FULL';
RMAN> LIST BACKUPPIECE ' ';
RMAN> LIST BACKUP RECOVERABLE;
RMAN> list backup of archivelog from scn 853145 until scn 854039;
RMAN> LIST DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';
RMAN> LIST COPY OF CONTROLFILE;
RMAN> list archivelog all backed up 1 times to disk;
RMAN> list backup of archivelog from scn 901427;
RMAN> list incarnation of database anuj ;
 
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16 16:28:05
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16 18:25:29
 
===========
 
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
 
 
list backup of archivelog all
 
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 220;
 
RMAN>list backup of archivelog sequence 89961 thread 2;
RMAN> LIST BACKUP OF ARCHIVELOG ;
 
 
list backup of tablespace users ;
list backup of database device type disk;
list copy of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
list copy of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
list backup of database completed between '01-AUGUST-2017' and '08-AUGUST-2017';
 
 
xx
RMAN> list backup of datafile;
 
RMAN> list backupset tag 'T2017';
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
184     299.57M    DISK        00:00:42     17-AUG-17
        BP Key: 184   Status: AVAILABLE  Compressed: YES  Tag: TAG20170817T010006
        Piece Name: /dumps/ICRAC/20170817_icrac1_186_1_952218007
 
  List of Archived Logs in backup set 184
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    259     22824594   16-AUG-17 22847740   16-AUG-17
  1    260     22847740   16-AUG-17 22934969   17-AUG-17
  2    239     22723071   16-AUG-17 22847951   16-AUG-17
  2    240     22847951   16-AUG-17 22923793   17-AUG-17
  2    241     22923793   17-AUG-17 22935012   17-AUG-17
 
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME "SYSDATE-1";
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
184     299.57M    DISK        00:00:42     17-AUG-17
        BP Key: 184   Status: AVAILABLE  Compressed: YES  Tag: TAG20170817T010006
        Piece Name: /dumps/ICRAC/20170817_icrac1_186_1_952218007
 
  List of Archived Logs in backup set 184
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    257     22723067   16-AUG-17 22798860   16-AUG-17
  1    258     22798860   16-AUG-17 22824594   16-AUG-17
  1    259     22824594   16-AUG-17 22847740   16-AUG-17
  1    260     22847740   16-AUG-17 22934969   17-AUG-17
  2    239     22723071   16-AUG-17 22847951   16-AUG-17
  2    240     22847951   16-AUG-17 22923793   17-AUG-17
  2    241     22923793   17-AUG-17 22935012   17-AUG-17
 
 
full backup info 
 
RMAN> list backup of database summary completed after 'sysdate - 7';
 
 
List of Backups
===============
Key     TY LV S Device Type Completion Time    #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
6       B  F  A DISK        02-SEP-17 13:10:39 1       1       YES        ORCLEE_FULL
 
 
RMAN>  list backup of controlfile ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
 
 
==========
 
Recovery Manager complete.
[oracle@oraasm11g ~]$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
[oracle@oraasm11g ~]$ rman target /
 
RMAN> list incarnation of database anuj ;
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16 16:28:05
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16 18:25:29
 
 
 
RMAN> list backup summary;
 
using target database control file instead of recovery catalog
 
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6       B  F  A DISK        02-SEP-17       1       1       YES        ORCLEE_FULL
7       B  F  A DISK        02-SEP-17       1       1       NO         TAG20170902T131049
8       B  A  A DISK        02-SEP-17       1       1       NO         ORCLEE_ARCHIVE
 
RMAN> list backup ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list backup of database;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
RMAN> list backup;
 
List of Backup Sets
===================
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list archivelog all;
 
specification does not match any archived log in the repository
 
RMAN> list backup of archivelog all;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       291.50K    DISK        00:00:00     02-SEP-17
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       853145     02-SEP-17 853302     02-SEP-17
  1    6       853302     02-SEP-17 853310     02-SEP-17
  1    7       853310     02-SEP-17 854039     02-SEP-17
  1    8       854039     02-SEP-17 854047     02-SEP-17
 
 
RMAN> list backup of archivelog from sequence 4;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list incarnation;
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ANUJ     3057090325       PARENT  1          25-DEC-16
2       2       ANUJ     3057090325       CURRENT 825576     25-DEC-16
 
RMAN> list backup of datafile 1;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854000     02-SEP-17 +DATA/anuj/datafile/system.268.931544353
 
 
RMAN> list backup of controlfile ;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
 
RMAN> list backup of spfile;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17 12:53:04
  SPFILE db_unique_name: ANUJ
 
 
 
RMAN> list backup by file;
 
 
List of Datafile Backups
========================
 
File Key     TY LV S Ckp SCN    Ckp Time           #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- ------------------ ------- ------- ---------- ---
1    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
2    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
3    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
4    6       B  F  A 854000     02-SEP-17 13:09:54 1       1       YES        ORCLEE_FULL
 
List of Archived Log Backups
============================
 
Thrd Seq     Low SCN    Low Time           BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- ------------------ ------- - ------- ------- ---------- ---
1    5       853145     02-SEP-17 12:54:24 8       A 1       1       NO         ORCLEE_ARCHIVE
1    6       853302     02-SEP-17 12:58:27 8       A 1       1       NO         ORCLEE_ARCHIVE
1    7       853310     02-SEP-17 12:58:27 8       A 1       1       NO         ORCLEE_ARCHIVE
1    8       854039     02-SEP-17 13:10:52 8       A 1       1       NO         ORCLEE_ARCHIVE
 
List of Control File Backups
============================
 
CF Ckp SCN Ckp Time           BS Key  S #Pieces #Copies Compressed Tag
---------- ------------------ ------- - ------- ------- ---------- ---
854026     02-SEP-17 13:10:49 7       A 1       1       NO         TAG20170902T131049
List of SPFILE Backups
======================
 
Modification Time  BS Key  S #Pieces #Copies Compressed Tag
------------------ ------- - ------- ------- ---------- ---
02-SEP-17 12:53:04 7       A 1       1       NO         TAG20170902T131049
 
 
 
RMAN> LIST backup tag 'ORCLEE_FULL';
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/users.272.931544539
 
 
RMAN> list recoverable backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
  2       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/sysaux.270.931544485
  3       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/undotbs1.269.931544399
  4       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/users.272.931544539
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
7       Full    9.36M      DISK        00:00:01     02-SEP-17 13:10:50
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20170902T131049
        Piece Name: /u01/app/RmanBackup/c-3057090325-20170902-02
  SPFILE Included: Modification time: 02-SEP-17 12:53:04
  SPFILE db_unique_name: ANUJ
  Control File Included: Ckp SCN: 854026       Ckp time: 02-SEP-17 13:10:49
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list backup of archivelog from scn 853145 until scn 854039;
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
 
RMAN>  list backupset 8;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
8       291.50K    DISK        00:00:00     02-SEP-17 13:10:54
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: ORCLEE_ARCHIVE
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_13_1_ARCHIVE
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time           Next SCN   Next Time
  ---- ------- ---------- ------------------ ---------- ---------
  1    5       853145     02-SEP-17 12:54:24 853302     02-SEP-17 12:58:27
  1    6       853302     02-SEP-17 12:58:27 853310     02-SEP-17 12:58:27
  1    7       853310     02-SEP-17 12:58:27 854039     02-SEP-17 13:10:52
  1    8       854039     02-SEP-17 13:10:52 854047     02-SEP-17 13:10:54
 
 
RMAN> list backup of datafile 1 completed between '01-sep-2017' and 'sysdate';
RMAN> list backup of datafile 1 completed between '01-sep-2017' and 'sysdate';
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
6       Full    223.16M    DISK        00:00:45     02-SEP-17 13:10:39
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: ORCLEE_FULL
        Piece Name: /u01/app/RmanBackup/ANUJ_20170902_11_1_FULL
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 854000     02-SEP-17 13:09:54 +DATA/anuj/datafile/system.268.931544353
 
 
w
List Command with Date ( All are tested ) 
 
RMAN> list backup of datafile 1 completed between '07-jan-2012' and 'sysdate';
RMAN> list backup of archivelog  from time "to_date('01/08/2017 22:00:00', 'dd/mm/yyyy hh24:mi:ss')"   until time "to_date('08/08/2017 04:00:00', 'dd/mm/yyyy hh24:mi:ss')";
RMAN> list backup of archivelog time between "to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of archivelog time between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/08/2017','dd/mm/yyyy')";
RMAN> list backup of database completed between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('08/08/2017','dd/mm/yyyy')";
RMAN> list backup of archivelog from time "to_date('01-08-2017','mm-dd-yyyy')";
RMAN> list backup of database completed between 'sysdate -7' and 'sysdate';
RMAN> list backup completed between 'sysdate -14' and 'sysdate';
RMAN> list backup completed between 'sysdate -14' and 'sysdate -7';
RMAN> list backup of spfile time between "to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of database completed between "to_date('06/17/2010','mm/dd/yyyy')" and "to_date('06/19/2010','mm/dd/yyyy')";
RMAN> list backup of database completed between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/08/2017','dd/mm/yyyy')";
RMAN> list backup of archivelog time between "to_date('20-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('25-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')";
RMAN> list backup of archivelog time between "to_date('01/08/2017','dd/mm/yyyy')" and "to_date('09/09/2017','dd/mm/yyyy')";
RMAN> LIST backup of database completed after "to_date('01/08/2017','mm/dd/yyyy')";
RMAN> list backup of archivelog  from time "to_date('01/08/2017 22:00:00', 'dd/mm/yyyy hh24:mi:ss')"   until time "to_date('08/08/2017 04:00:00', 'dd/mm/yyyy hh24:mi:ss')";
RMAN> LIST BACKUP OF ARCHIVELOG  FROM TIME "TO_DATE('08/16/2017 22:00:00', 'MM/DD/YYYY hh24:mi:ss')"   UNTIL TIME "TO_DATE('08/17/2017 03:00:00', 'MM/DD/YYYY hh24:mi:ss')";



 export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
LIST BACKUP OF ARCHIVELOG  FROM TIME "TO_DATE('06/17/2023 22:00:00', 'MM/DD/YYYY hh24:mi:ss')"   UNTIL TIME "TO_DATE('06/18/2023 03:00:00', 'MM/DD/YYYY hh24:mi:ss')";

for SUMMARY
RMAN> LIST BACKUP SUMMARY COMPLETED BETWEEN "to_date('10-JAN-2022 05:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('14-JAN-2022 07:00:00','DD-MON-YYYY HH24:MI:SS')";

RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1' UNTIL TIME 'SYSDATE';
========================
 
RMAN>report need backup days=2;
RMAN> report unrecoverable;
 
REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.
 
REPORT NEED BACKUP REDUNDANCY n
Displays objects requiring backup to satisfy a redundancy-based retention policy.
 
REPORT NEED BACKUP DAYS n
Displays files that require more than n days' worth of archived redo log files for recovery.
 
REPORT NEED BACKUP INCREMENTAL n
Displays files that require application of more than n incremental backups for recovery.
 
 
RMAN> report schema ;
 
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ANUJ
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/anuj/datafile/system.268.931544353
2    0        SYSAUX               ***     +DATA/anuj/datafile/sysaux.270.931544485
3    0        UNDOTBS1             ***     +DATA/anuj/datafile/undotbs1.269.931544399
4    0        USERS                ***     +DATA/anuj/datafile/users.272.931544539
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    256      TEMP2                256         +DATA/anuj/tempfile/temp2.277.931545697


=========


RMAN>  list backup of pluggable database 'PDB$SEED' summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1967    B  0  A DISK        03-04-2022 02:08:44 1       1       YES        TAG20220403T020100
1999    B  0  A DISK        10-04-2022 02:08:41 1       1       YES        TAG20220410T020100




export NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
LIST BACKUP OF ARCHIVELOG TIME BETWEEN "to_date('27-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('28-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')";
LIST BACKUP SUMMARY COMPLETED BETWEEN "to_date('27-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')"  and "to_date('28-MAR-2023 09:00:00','DD-MON-YYYY HH24:MI:SS')";



===

export NLS_DATE_FORMAT='DD/MM/YYYY hh24:mi:ss'


rman target / catalog prod_23/xxxx@rmancatalog


list backup of database completed between "to_date('01/04/2023','DD/MM/YYYY')" and "to_date('22/04/2023','DD/MM/YYYY')";
LIST backup of database completed after "to_date('01/04/2023','DD/MM/YYYY')";
list backup of datafile 1 completed after "to_date('01/04/2023','DD/MM/YYYY')";
list until time "to_date('01/04/2023','DD/MM/YYYY')" backupset of tablespace system;
list backup of tablespace system completed after "to_date('01/04/2023','DD/MM/YYYY')";


Oracle DBA

anuj blog Archive