1. BACKUP DATABASE PLUS ARCHIVELOG
or
2. ALTER SYSTEM ARCHIVE LOG CURRENT ; followed by BACKUP ARCHIVELOG
But if you receive a Backup from another DBA, can you validate that you have all the ArchiveLogs required to RECOVER DATABASE upto a consistent point (SEQUENCE# or TIME ?) ?
If you use an RMAN Catalog schema, you can query that RMAN Catalog schema for information.
But if there is no RMAN Catalog schema, all the information you need is in the Controlfile backup
One technique that can be used is
1. Create a dummy parameter file with
a. DB_NAME the same as the source database
b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.
At the end of the exercise, I can discard the "temporary" parameter file that I used and also remove the Contolfile that I have restored.
If I find that the Backup is Good (i.e. ArchiveLogs contain enough Redo (SCNs) to RECOVER the datafiles, I can do a proper RESTORE DATABASE and RECOVER DATABASE or DUPLICATE DATABASE from the Backup.
Let's say that I receive Backup Pieces, organised as would be an FRA :
$pwd /u01/app/Backup_from_Source/ORCL12C $ls -l total 32 drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFE9E2D73E2038E0530100007F846C drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFF8A6BB912582E0530100007F8BE4 drwxr-x--- 3 oracle oinstall 4096 Jun 5 2017 4F793A6D323D1344E0530100007FABC7 drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 53F8012866211264E0530100007FD493 drwxr-x--- 3 oracle oinstall 4096 Jan 13 2018 5C9E4689632518EBE0530100007F03C5 drwxr-x--- 3 oracle oinstall 4096 Jun 17 22:36 A84987FDF4C51164E0530100007FEB9C drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 autobackup drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 backupset $
I first create a parameter file as :
$cat initRTST.ora db_name = 'ORCL12C' db_unique_name = 'RTST' control_files='/tmp/RTST_control.ctl' enable_pluggable_database=true $
Then, with ORACLE_SID set to RTST, I restore and mount the Controlfile
$ORACLE_SID=RTST;export ORACLE_SID $ls -l /tmp/RT* ls: cannot access /tmp/RT*: No such file or directory $sqlplus '/ as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:03:06 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/12.2/db_1/dbs/initRTST.ora'; ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 8792152 bytes Variable Size 251660200 bytes Database Buffers 50331648 bytes Redo Buffers 7983104 bytes SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production $rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Fri Sep 25 23:03:50 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL12C (not mounted) RMAN> restore controlfile from '/u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp'; Starting restore at 25-SEP-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=179 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/tmp/RTST_control.ctl Finished restore at 25-SEP-20 RMAN> RMAN> alter database mount; Statement processed released channel: ORA_DISK_1 RMAN>
Next, I "clear" information about all other backups from the controlfile.
RMAN> delete noprompt backup; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=182 device type=DISK List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 54 54 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp 55 55 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp 56 56 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp 57 57 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp 58 58 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp 59 59 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp 60 60 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp 61 61 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp 62 62 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp 63 63 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp 64 64 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp 65 65 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp 66 66 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp 67 67 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp 68 68 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp RECID=63 STAMP=1051983568 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp RECID=64 STAMP=1051983703 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp RECID=65 STAMP=1051983676 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp RECID=66 STAMP=1051983711 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp RECID=67 STAMP=1051983712 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp RECID=68 STAMP=1051983737 Deleted 6 objects RMAN-06207: warning: 9 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RMAN> RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432 Crosschecked 9 objects RMAN> delete noprompt expired backup; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 54 54 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp 55 55 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp 56 56 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp 57 57 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp 58 58 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp 59 59 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp 60 60 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp 61 61 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp 62 62 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431 deleted backup piece backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432 Deleted 9 EXPIRED objects RMAN> list backup; specification does not match any backup in the repository RMAN>
Now I am ready the catalog the Backup Pieces that I have received
RMAN> catalog start with '/u01/app/Backup_from_Source/ORCL12C'; searching for all files that match the pattern /u01/app/Backup_from_Source/ORCL12C List of Files Unknown to the Database ===================================== File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp RMAN>
Note how the CATALOG command found 3 PDBs in the Backup.
I can now query from RMAN to get information
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 163.55M DISK 00:01:06 24-SEP-20
BP Key: 69 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
List of Datafiles in backup set 69
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 503.88M DISK 00:01:46 24-SEP-20
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
List of Datafiles in backup set 70
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 18.22M DISK 00:00:01 24-SEP-20
BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174150
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286161 Ckp time: 24-SEP-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 18.22M DISK 00:00:00 24-SEP-20
BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174333
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286305 Ckp time: 24-SEP-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73 Full 161.83M DISK 00:00:19 24-SEP-20
BP Key: 73 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
List of Datafiles in backup set 73
Container ID: 4, PDB Name: NEWPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
41 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf
42 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf
43 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf
44 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgnbjwg7_.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74 23.55M DISK 00:00:03 24-SEP-20
BP Key: 74 Status: AVAILABLE Compressed: YES Tag: TAG20200924T174142
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
List of Archived Logs in backup set 74
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 73 3030419 17-JUN-20 3033316 17-JUN-20
1 74 3033316 17-JUN-20 3033319 17-JUN-20
1 75 3033319 17-JUN-20 3033326 17-JUN-20
1 76 3033326 17-JUN-20 3033329 17-JUN-20
1 77 3033329 17-JUN-20 3033340 17-JUN-20
1 78 3033340 17-JUN-20 3033343 17-JUN-20
1 79 3033343 17-JUN-20 3033358 17-JUN-20
1 80 3033358 17-JUN-20 3035646 17-JUN-20
1 81 3035646 17-JUN-20 3035675 17-JUN-20
1 82 3035675 17-JUN-20 3036658 17-JUN-20
1 83 3036658 17-JUN-20 3038913 09-JUL-20
1 84 3038913 09-JUL-20 3057240 13-JUL-20
1 85 3057240 13-JUL-20 3163574 23-SEP-20
1 86 3163574 23-SEP-20 3165215 23-SEP-20
1 87 3165215 23-SEP-20 3165221 23-SEP-20
1 88 3165221 23-SEP-20 3165687 23-SEP-20
1 89 3165687 23-SEP-20 3165755 23-SEP-20
1 90 3165755 23-SEP-20 3165858 23-SEP-20
1 91 3165858 23-SEP-20 3167178 23-SEP-20
1 92 3167178 23-SEP-20 3168603 23-SEP-20
1 93 3168603 23-SEP-20 3284332 24-SEP-20
1 94 3284332 24-SEP-20 3285739 24-SEP-20
1 95 3285739 24-SEP-20 3285960 24-SEP-20
1 96 3285960 24-SEP-20 3286131 24-SEP-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 327.08M DISK 00:00:31 24-SEP-20
BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
List of Datafiles in backup set 75
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
RMAN>
From "eyeballing" the output, I can see that :
a. the highest Checkpoint SCN for datafiles is 3286164 (for PDB "NEWPDB")
but
b. the highest ArchiveLog SCN is 3286130 (3286131-1) from Sequence#96.
Quite obviously, I do not have enough Redo Information in the ArchiveLogs to be able to RECOVER to a consistent SCN.
Of course, the RMAN LIST BACKUP listing is quite short here. What if it was very long ? How would I "query" ? Using SQL, of course.
The information I get is that CON_ID=4 (which is NEWPDB) has at least one datafile at a higher Checkpoint SCN and Time then the last ArchiveLog in the backup.
$sqlplus '/ as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:18:31 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI';
Session altered.
SQL>
SQL> select df.con_id, max(df.checkpoint_change#)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /
CON_ID MAX(DF.CHECKPOINT_CHANGE#)
---------- --------------------------
1 3286305
2 1443131
3 3285704
4 3286164
SQL>
SQL> select df.con_id, max(df.checkpoint_time)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /
CON_ID MAX(DF.CHECKPOI
---------- ---------------
1 24-SEP-20 17:43
2 02-MAR-17 07:57
3 24-SEP-20 17:39
4 24-SEP-20 17:41
SQL>
SQL> select arc.thread#, max(arc.next_change#)-1
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /
THREAD# MAX(ARC.NEXT_CHANGE#)-1
---------- -----------------------
1 3286130
SQL>
SQL> select arc.thread#, max(arc.next_time)-1/1440
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /
THREAD# MAX(ARC.NEXT_TI
---------- ---------------
1 24-SEP-20 17:40
SQL>
SQL> select arc.thread#, max(arc.sequence#)
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /
THREAD# MAX(ARC.SEQUENCE#)
---------- ------------------
1 96
SQL>
The information I get is that CON_ID=4 (which is NEWPDB) has at least one datafile at a higher Checkpoint SCN and Time then the last ArchiveLog in the backup.
Therefore, I would not be able to do an OPEN RESETLOGS after a full RESTORE + RECOVER because Oracle will expect some more Redo to be applied (from at least Sequence #97).
Why do I query for CON_ID > 0 ? Because CON_ID=0 is for the CDB, not the actual Root (which is CON_ID=1)
Why I do filter for RESETLOGS_CHANGE#? Because I want to query for the current Incarnation of the database, as reflected in the Controlfile.
Such a method is also useful to determine what point in time you an do the Incomplete Recovery till. For example, if Sequence#97 had been included in the Backup, I could have been able to write my RECOVER DATABASE command with UNTIL SEQUENCE 98. (97+1) The timestamps are also available, and I could do UNTIL TIME 24-Sep-20 17:41
No comments:
Post a Comment