This is the second time we have hit this error after upgrading the recovery catalog to 12.2.
The first is now documented by Oracle as Document 2291791.1, and affects 12.1 multi-tenant instances with DBBP 12.1.0.2.161018 or later applied.
If you have applied that patchset, you can see the problem by checking the contents of file $ORACLE_HOME/rdbms/admin/recover.bsq
cursor bs(low_recid number, high_recid number) is
select bs.recid, bs.stamp, bs.set_stamp, bs.set_count, bs.backup_type,
bs.incremental_level,
bs.pieces, start_time, completion_time, controlfile_included,
bs.input_file_scan_only, keep_until,
decode (bs.keep_options, 'LOGS' , KEEP_LOGS
, 'NOLOGS' , KEEP_NOLOGS
, 'BACKUP_LOGS' , KEEP_CONSIST
, 0) keep_options,
bs.block_size, bs.multi_section, bs.guid,
decode(bs.con_id, invalid_pdbid, 1, 0) dropped_pdb
from v$backup_set bs, v$containers pdb
where bs.recid between low_recid and high_recid
and (bs.stamp >= kccdivts OR bs.recid = high_recid)
and bs.stamp >= resyncstamp
and bs.for_xtts != 'YES'
order by bs.recid;
Note that the join has been missed between v$backup_set and v$containers(!). The next cursor in the source, bp, also has the same ommission.
You can work-around the issue by adding the following condition to the two cursors, but you should probably check with Oracle Support first.
and bs.con_id = pdb.con_id(+)
The latest issue is on a 11g (hence single tenant obviously) on a dataguard standby site (in this case we take backups from both primary and standby instances).
Tracing the resync of the catalog from the standby site, the issue begins when inserting to table bs (backup sets) in the recovery catalog.
The insert fails because of a violation the unique key BS_U2 on columns db_key, set_stamp and set_count.
Checking the recovery catalog there already is record for this combination of set_stamp and set_count, but from the primary instance.
My suspicion is that the unique key also needs to include site_key field, however I don’t have enough of an understanding of what the set_stamp, set_count combination represents to be sure.
The only reference to the fields I can find come quotes in the documentation such as the following:
The SET_STAMP value from V$BACKUP_SET. SET_STAMP and SET_COUNT form a concatenated key that uniquely identifies this record in the target database control file.
What does seem apparent is that the 12.2 recovery catalog is stricter in enforcing this uniqueness, thus revealing some bugs elsewhere in the codebase.
