Oracle RMAN ( Recovery Manager ) Backup Tutorials -8

Hi,

I will continue to tell you about the RMAN (Recovery Manager) tool in this eighth article of my tutorial series .

 

 

Read Older articles of my rman backup tutorial series before this article with following links.

https://ittutorial.org/2019/03/25/oracle-rman-recovery-manager-backup-tutorials-1/

https://ittutorial.org/2019/03/25/oracle-rman-recovery-manager-backup-tutorials-2/

https://ittutorial.org/2019/03/25/oracle-rman-recovery-manager-backup-tutorials-3/

 

 

With RMAN, we can query all backup informations and Reports. The query is most needed when you need to restore any Backup. Backups are taken by RMAN can be queried with different commands and different commands.

 

[oracle@MehmetSalih ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 26 02:40:08 2019
Version 19.2.0.0.0

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

connected to target database: DEVECI19 (DBID=4027228950)

RMAN> list backup;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.11G      DISK        00:00:10     25-MAR-19
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20190325T172233
        Piece Name: /oradata/install/dbs/01ttb9qq_1_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2043111    25-MAR-19              NO    /oradata/19c/DEVECI19C/system01.dbf
  3       Full 2043111    25-MAR-19              NO    /oradata/19c/DEVECI19C/sysaux01.dbf
  4       Full 2043111    25-MAR-19              NO    /oradata/19c/DEVECI19C/undotbs01.dbf
  7       Full 2043111    25-MAR-19              NO    /oradata/19c/DEVECI19C/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    10.20M     DISK        00:00:00     25-MAR-19
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20190325T172249
        Piece Name: /oradata/install/dbs/c-4027228950-20190325-00
  SPFILE Included: Modification time: 25-MAR-19
  SPFILE db_unique_name: DEVECI19C
  Control File Included: Ckp SCN: 2043124      Ckp time: 25-MAR-19

................

.................

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Full    10.20M     DISK        00:00:00     26-MAR-19
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20190326T010612
        Piece Name: /u01/backup/ControlFilec-4027228950-20190326-01.rman
  SPFILE Included: Modification time: 25-MAR-19
  SPFILE db_unique_name: DEVECI19C
  Control File Included: Ckp SCN: 2114104      Ckp time: 26-MAR-19

RMAN>



  

The summary report of the backups can be listed with the following command.

RMAN> LIST BACKUP SUMMARY;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 25-MAR-19 1 1 NO TAG20190325T172233
2 B F A DISK 25-MAR-19 1 1 NO TAG20190325T172249
3 B F A DISK 25-MAR-19 1 1 NO TAG20190325T173150
..................................
..................................
..................................
42 B A A DISK 26-MAR-19 1 1 YES TAG20190326T010611
43 B F A DISK 26-MAR-19 1 1 NO TAG20190326T010612

RMAN>

 

 

The above 2 commands report all backups, such as full,incremental,archivelog, controlfile,datafile etc. Instead, you can use following command to see only the backup of database.

RMAN> List backup of database;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.11G DISK 00:00:10 25-MAR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190325T172233
Piece Name: /oradata/install/dbs/01ttb9qq_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2043111 25-MAR-19 NO /oradata/19c/DEVECI19C/system01.dbf
3 Full 2043111 25-MAR-19 NO /oradata/19c/DEVECI19C/sysaux01.dbf
4 Full 2043111 25-MAR-19 NO /oradata/19c/DEVECI19C/undotbs01.dbf
7 Full 2043111 25-MAR-19 NO /oradata/19c/DEVECI19C/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.11G DISK 00:00:13 25-MAR-19
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20190325T173150
Piece Name: /u01/backup/FULL_DATABASE_DEVECI19_1003858311_3.rman
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2043360 25-MAR-19 NO /oradata/19c/DEVECI19C/system01.dbf
3 Full 2043360 25-MAR-19 NO /oradata/19c/DEVECI19C/sysaux01.dbf
4 Full 2043360 25-MAR-19 NO /oradata/19c/DEVECI19C/undotbs01.dbf
7 Full 2043360 25-MAR-19 NO /oradata/19c/DEVECI19C/users01.dbf

..................................
..................................

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 222.68M DISK 00:00:52 26-MAR-19
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20190326T010509
Piece Name: /oradata/install/dbs/15ttc4u5_1_1
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2114015 26-MAR-19 NO /oradata/19c/DEVECI19C/system01.dbf

RMAN>

 

 

you can use following command to see only the backup of archivelogs.

RMAN> LIST BACKUP OF ARCHIVELOG ALL;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       3.06M      DISK        00:00:00     25-MAR-19
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20190325T182613
        Piece Name: /oradata/install/dbs/07ttbdi5_1_1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    3       2041748    25-MAR-19 2044688    25-MAR-19

..................................
..................................

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
42      47.50K     DISK        00:00:00     26-MAR-19
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: TAG20190326T010611
        Piece Name: /u01/backup/FULL_DATABASE_DEVECI19_1attc503_42_20190326.bkp

  List of Archived Logs in backup set 42
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8       2114001    26-MAR-19 2114091    26-MAR-19

RMAN>

 

 

The following query can be used to query controlfile backups .

RMAN> list backup of controlfile;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.20M DISK 00:00:00 25-MAR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190325T172249
Piece Name: /oradata/install/dbs/c-4027228950-20190325-00
Control File Included: Ckp SCN: 2043124 Ckp time: 25-MAR-19

..................................
..................................

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 10.20M DISK 00:00:00 26-MAR-19
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: TAG20190326T010612
Piece Name: /u01/backup/ControlFilec-4027228950-20190326-01.rman
Control File Included: Ckp SCN: 2114104 Ckp time: 26-MAR-19

 

The following query can be used to query datafile backups .

 

 

RMAN> LIST BACKUP OF DATAFILE 1;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.11G DISK 00:00:10 25-MAR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190325T172233
Piece Name: /oradata/install/dbs/01ttb9qq_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2043111 25-MAR-19 NO /oradata/19c/DEVECI19C/system01.dbf

.............................................
.............................................
.............................................

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 222.68M DISK 00:00:52 26-MAR-19
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20190326T010509
Piece Name: /oradata/install/dbs/15ttc4u5_1_1
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2114015 26-MAR-19 NO /oradata/19c/DEVECI19C/system01.dbf

RMAN>

 

 

The following query can be used to query Image copy backups .

 

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name DEVECI19C
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    3       A 25-MAR-19
        Name: /oradata/install/dbs/arch1_3_1001759193.dbf

2       1    4       A 25-MAR-19
        Name: /oradata/install/dbs/arch1_4_1001759193.dbf

3       1    5       A 25-MAR-19
        Name: /oradata/install/dbs/arch1_5_1001759193.dbf

4       1    6       A 26-MAR-19
        Name: /oradata/install/dbs/arch1_6_1001759193.dbf

5       1    7       A 26-MAR-19
        Name: /oradata/install/dbs/arch1_7_1001759193.dbf

6       1    8       A 26-MAR-19
        Name: /oradata/install/dbs/arch1_8_1001759193.dbf


RMAN>

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

2 thoughts on “Oracle RMAN ( Recovery Manager ) Backup Tutorials -8

Leave a Reply

Your email address will not be published. Required fields are marked *