Re-Register ArchiveLogs and Backups

If you as the DBA or someone else as the SysAdmin has had to delete and restore or relocate ArchiveLogs or RMAN Backups to another mount point, you'd find that Oracle can no longer identify them.

This would also happen if you run a CREATE CONTROLFILE -- all information about RMAN Backups and ArchiveLogs that was formerly in the controlfile is "lost" because your database starts with a controlfile that has information only about DataFiles and Online RedoLogs.

How do you re-register ArchiveLogs and RMAN Backups ?

I begin this demo with 3 ArchiveLogs (Sequence#50 to #52) and one BackupSet created today.

SQL> l
  1  select sequence#, name from v$archived_log
  2  where dest_id=1
  3  and (completion_time > sysdate-1  OR first_time > sysdate-1)
  4* order by sequence#
SQL> /

 SEQUENCE# NAME
---------- --------------------------------------------------
        50 /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf
        51 /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf
        52 /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
144     1    50      A 31-JUL-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145     1    51      A 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146     1    52      A 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


RMAN>
RMAN> list backup completed after "sysdate-1";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Full    790.52M    DISK        00:00:23     06-AUG-20
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20200806T173252
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 5356172    06-AUG-20              NO    /opt/oracle/oradata/ORCLCDB/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    18.02M     DISK        00:00:01     06-AUG-20
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20200806T173317
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
  SPFILE Included: Modification time: 06-AUG-20
  SPFILE db_unique_name: ORCLCDB
  Control File Included: Ckp SCN: 5356230      Ckp time: 06-AUG-20

RMAN>

Sometime later, these get deleted and  I can no longer find them.

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf RECID=139 STAMP=1047211353
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf RECID=142 STAMP=1047211650
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Crosschecked 5 objects


RMAN>
RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
Crosschecked 2 objects


RMAN>

oracle19c>pwd
/opt/oracle/archivelog/ORCLCDB
oracle19c>ls -ltr |tail -2
-rw-r-----. 1 oracle oinstall     6656 Jul 31 12:02 1_48_1036108814.dbf
-rw-r-----. 1 oracle oinstall   203776 Jul 31 12:07 1_49_1036108814.dbf
oracle19c>
oracle19c>pwd
/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -ltr
total 0
oracle19c>

RMAN> list expired 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
------- ---- -- ---------- ----------- ------------ ---------------
17      Full    790.52M    DISK        00:00:23     06-AUG-20
        BP Key: 17   Status: EXPIRED  Compressed: NO  Tag: TAG20200806T173252
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 5356172    06-AUG-20              NO    /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> delete expired backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17      17      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
Deleted 1 EXPIRED objects


RMAN>

So, ArchiveLogs from Sequence#50 to Sequence#52 have been deleted and so has today's backupset (although the controlfile autobackup is still present).

I ask the SysAdmin to restore the misssing files.  He restores them to a different mountpoint  -- under /NEWFS.

I then re-register them.

oracle19c>pwd
/NEWFS/archivelog/ORCLCDB
oracle19c>ls -l
total 4432
-rw-r-----. 1 oracle oinstall 1720832 Aug  6 17:25 1_50_1036108814.dbf
-rw-r-----. 1 oracle oinstall 2808320 Aug  6 17:28 1_51_1036108814.dbf
-rw-r-----. 1 oracle oinstall    1536 Aug  6 17:28 1_52_1036108814.dbf
oracle19c>

oracle19c>pwd
/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -l
total 809504
-rw-r-----. 1 oracle oinstall 828932096 Aug  6 17:33 o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
oracle19c>

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf';

Database altered.

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
148     1    50      A 31-JUL-20
        Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

144     1    50      X 31-JUL-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

149     1    51      A 06-AUG-20
        Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

145     1    51      X 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

150     1    52      A 06-AUG-20
        Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

146     1    52      X 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

147     1    53      A 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
RMAN> crosscheck archivelog all completed after "sysdate-1";

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=148 STAMP=1047752869
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=149 STAMP=1047752894
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=150 STAMP=1047752905
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf RECID=147 STAMP=1047751145
Crosschecked 7 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
144     1    50      X 31-JUL-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145     1    51      X 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146     1    52      X 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Deleted 3 EXPIRED objects


RMAN>
RMAN> list archivelog all completed after "sysdate-1";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
148     1    50      A 31-JUL-20
        Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

149     1    51      A 06-AUG-20
        Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

150     1    52      A 06-AUG-20
        Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

147     1    53      A 06-AUG-20
        Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
--- note that ArchiveLog 53 is a new one that has been generated recently, in the default location

RMAN> catalog start with '/NEWFS/FRA';

searching for all files that match the pattern /NEWFS/FRA

List of Files Unknown to the Database
=====================================
File Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.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: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

RMAN> list backup completed after "sysdate-1";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    18.02M     DISK        00:00:01     06-AUG-20
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20200806T173317
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
  SPFILE Included: Modification time: 06-AUG-20
  SPFILE db_unique_name: ORCLCDB
  Control File Included: Ckp SCN: 5356230      Ckp time: 06-AUG-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    790.52M    DISK        00:00:23     06-AUG-20
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20200806T173252
        Piece Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
  List of Datafiles in backup set 19
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 5356172    06-AUG-20              NO    /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=19 STAMP=1047753112
Crosschecked 2 objects


RMAN>

To re-register ArchiveLogs, I used the SQL command "ALTER DATABASE REGISTER PHYSICAL LOGFILE'.  RMAN is then able to identify these ArchiveLogs as well.  I then use CROSSCHECK and DELETE EXPIRED in RMAN to delete information about the old location of the same ArchiveLogs.

To re-register RMAN Backups, I used the RMAN command "CATALOG START WITH".  And then used "CROSSCHECK" to confirm that the BackupPiece(s) is/are available.