RMAN: Manually cataloging a file from OCI object storage

27. Februar 2020

Johannes Michler PROMATIS Horus Oracle


Senior Vice President – Head of Platforms & Development

Recover database fails

Recently I ran into a problem when trying to restore a database backup (created with backup database plus archivelog;) that was created onto OCI object storage. Somehow one of the archive logs required to recover the database was missing. We got the following error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/install/APPS/data/QAT/QAT_data_D-PROD_TS-SYSTEM_FNO-1'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2020 12:50:34
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 88897 and starting SCN of 6215220161483 found to restore

We're still investigating with Oracle on how this can have happened.

However at least we found a workaround to fix the backup in OCI and be able to restore it without recreating it:

Add the archive log

Since I didn't want to "manually copy" the archive log over to the target (and do a "catalog start with" there) I've decided to put the archivelog into my existing object storage bucket.

On the source side I started a new rman session and ran the following commands:

CONFIGURE COMPRESSION ALGORITHM 'BASIC';
SET ENCRYPTION ON IDENTIFIED BY 'XXXX' ONLY;
run {
allocate channel CH1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/staging/oci/rman/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/staging/oci/rman/dbs/PROD.ora)';
BACKUP archivelog sequence 88897;
release channel CH1;
}

Afterwards list backup gives us:

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
24983 1.35G SBT_TAPE 00:02:36 14-FEB-20
BP Key: 25991 Status: AVAILABLE Compressed: NO Tag: TAG20200214T202517
Handle: vtuoho9d_1_1 Media: objectstorage.eu-frankf..cloud.com/n/XXXXXXX/PROD20200212DB

List of Archived Logs in backup set 24983
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 88897 6215220161483 13-FEB-20 6215220873083 13-FEB-20

The backup is also visible in OCI Object Storage management:

Catalog on the target side

On the target side I was able to add this backup to the catalog (which was part of the control file I've restored first):

set decryption identified by 'XXXX';
configure channel device type 'SBT_TAPE' PARMS='SBT_LIBRARY=/u01/install/APPS/scripts/restore/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/u01/install/APPS/scripts/restore/conf/opcQAT.ora)'
catalog DEVICE TYPE 'SBT_TAPE' backuppiece 'vtuoho9d_1_1';

Finally

After doing this a second attempt to restore and recover was succesful:

RESTORE DATABASE;
switch datafile all;
switch tempfile all;
RECOVER DATABASE;

was working as expected and led to a restored and recovered database instance. Since this was in the progress of an "automated EBS@OCI" backup restore process we've added the "catalog backuppiece command into the restore_db-Script executed by E-Business Suite Cloud Manager and in that way we got a backup, that could then be used "as usual" to provision one or multiple EBS@OCI environments.