Resync of Control file with Catalog taking a long time in Oracle

We can solve this kind of problem by checking the control file and executing the procedure to delete RMAN and other entries from the Control file.

Firstly we can check the following parameter setting for how many days the Control file keeps information.

show parameter control_file_record_keep_time
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed. (Default value is 7).

Check the size of the control file with different sections:

select type, record_size, records_total, records_used, round((records_used * record_size) / 1024/1024) used_mb from V_$CONTROLFILE_RECORD_SECTION;

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED    USED_MB
---------------------------- ----------- ------------- ------------ ----------
DATABASE                             316             1            1          0
CKPT PROGRESS                       8180            11            0          0
REDO THREAD                          256             8            1          0
REDO LOG                              72            40            8          0
DATAFILE                             520          1024           40          0
FILENAME                             524          4113           38          0
TABLESPACE                           180          1024           27          0
TEMPORARY FILENAME                    56          1024            5          0
RMAN CONFIGURATION                  1108            50            0          0
LOG HISTORY                           56          2336         2336          0
OFFLINE RANGE                        200          1063          578          0
ARCHIVED LOG                         584          1232         1232          1
BACKUP SET                            96          1022           87          0
BACKUP PIECE                         780          1006           87          0
BACKUP DATAFILE                      200          1063          146          0
BACKUP REDOLOG                        76          2150         1282          0
DATAFILE COPY                        736          1000            0          0
BACKUP CORRUPTION                     44          1115            0          0
COPY CORRUPTION                       40          1227            0          0
DELETED OBJECT                        20           818          760          0
PROXY COPY                           928          1004            0          0
BACKUP SPFILE                        124           131           22          0
DATABASE INCARNATION                  56           292            1          0
FLASHBACK LOG                         84          2048            0          0
RECOVERY DESTINATION                 180             1            0          0
INSTANCE SPACE RESERVATION            28          1055            1          0
REMOVABLE RECOVERY FILES              32          1000            0          0
RMAN STATUS                          116           141           91          0
THREAD INSTANCE NAME MAPPING          80             8            8          0
MTTR                                 100             8            1          0
DATAFILE HISTORY                     568            57            0          0
STANDBY DATABASE MATRIX              400           128          128          0
GUARANTEED RESTORE POINT             256          2048            0          0
RESTORE POINT                        256          2108            0          0
DATABASE BLOCK CORRUPTION             80          8384            0          0
ACM OPERATION                        104            64           11          0
FOREIGN ARCHIVED LOG                 604          1002            0          0
PDB RECORD                           780            10            5          0
AUXILIARY DATAFILE COPY              584           128            0          0
MULTI INSTANCE REDO APPLY            556             1            0          0
PDBINC RECORD                        144           113            0          0
TABLESPACE KEY HISTORY               108           151            0          0

Execute the following PL/SQL to remove all entries from V$RMAN_STATUS:

EXEC SYS.DBMS_BACKUP_RESTORE.resetCfileSection(28);

Refer: https://docs.oracle.com/cd/E25054_01/backup.1111/e10642/rcmtroub.htm

select type, record_size, records_total, records_used, round((records_used * record_size) / 1024/1024) used_mb from V_$CONTROLFILE_RECORD_SECTION where type = 'RMAN STATUS'
TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED    USED_MB
---------------------------- ----------- ------------- ------------ ----------
RMAN STATUS                          116           141            0          0

Removing entries in v$deleted_object view on RMAN catalog.

Note: If Resync with the RMAN catalog is taking the time you can use this session to delete entries from the control file so that resync is fast.

EXEC DBMS_BACKUP_RESTORE.RESETCFILESECTION(19); 

select type, record_size, records_total, records_used, round((records_used * record_size) / 1024/1024) used_mb from V_$CONTROLFILE_RECORD_SECTION where type = 'DELETED OBJECT'
TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED    USED_MB
---------------------------- ----------- ------------- ------------ ----------
DELETED OBJECT                        20           818            0          0

Removing entries in v$DATABASE_BLOCK_CORRUPTION view

EXEC SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35);

Remove entries from v$backup_spfile section using the following command on the target database:

EXEC dbms_backup_restore.resetCfileSection(21);

Remove entry from v$archived_log:

EXEC dbms_backup_restore.resetCfileSection(11);