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.