IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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/
https://ittutorial.org/2019/03/26/oracle-rman-recovery-manager-backup-tutorials-4/
https://ittutorial.org/2019/03/26/oracle-rman-recovery-manager-backup-tutorials-5/
https://ittutorial.org/2019/03/26/oracle-rman-recovery-manager-backup-tutorials-6/
https://ittutorial.org/2019/03/26/oracle-rman-recovery-manager-backup-tutorials-7/
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>
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
Good day! Do you use Twitter? I’d like to follow you if that would be ok. I’m undoubtedly enjoying your blog and look forward to new posts.|
Hey very interesting blog!|