DBUA displays wrong RMAN backup for restore – Oracle 12.1.0.2

Related Blog Posts:

• DBUA and Read-Only Tablespaces – Things to Know – I (Feb 3, 2016)
  
• DBUA and Read Only Tablespaces – Things to Know – II (Mar 30, 2016)
  
• DBUA displays wrong RMAN Backup for Restore (Sep 21, 2015)
  
• DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015)

If you are using the Database Upgrade Assistant (DBUA) to upgrade your database to Oracle Database 12.1.0.2 be aware when you choose to potentially restore your database from a existing backup in case of an error during the upgrade.

First of all I would always stop DBUA and try the command line upgrade after fixing the issues instead of restoring the entire database. But this is a different story.

Anyhow, the most recent available backup to be displayed is most likely your newest one as the underlying query uses a MAX function – but leading to an incorrect (or unintended) result.

Ignore the fact that the screenshot is in German – the interesting part is the displayed time stamp for the most recent available backup:

The customer who alerted me was wondering as his list of backups showed also backups from August and early September.

The query being used in DBUA to gather the most recent date;

SELECT MAX (TO_CHAR (completion_time, ‘DD-MON-YYYY HH24:MI:SS’)) AS end_time FROM (SELECT completion_time FROM v$backup_set)

may give you this result: 31-JUL-2015
23:59:52 – even if you have newer backups taken in August and September. The TO_CHAR conversion will lead to the incorrect handling of the date as the MAX function won’t deliver the most recent date but the alphabetical highest value of the conversion result.

The query should be:

SELECT MAX (completion_time) AS end_time
FROM (SELECT completion_time FROM v$backup_set)

to display the most recent full backup.

It will be fixed in the next release.
Credits go to Bernd Tuba from MM Warburg – thanks!!

–Mike