Is this enough/proper data to restore an Oracle database?

Question

I was handed some data and was told it was a backup of an Oracle database created by RMAN and stored using Avamar. The owner would like access to the data. It is a folder named like a date, and it contains 26 files. Below is a partial list of the files. I can only assume the files are undamaged. Is this enough to restore to a new Oracle database on a new physical system?

• CONTROLFILE.EC700QSE.c-655555554-20200309-00
• EC700QSE_4cvtp99a_1_1
• EC700QSE_4dvtp9ad_1_1
• EC700QSE_4evtp9bg_1_1
• EC700QSE_4fvtp9ck_1_1
• ...25 files beginning "EC700QSE", 26 total files

I have some experience with Oracle Database, but not with RMAN or Avamar.

According to this Oracle documentation- https://docs.oracle.com/cd/E15586_01/backup.1111/e10642/rcmadvre.htm Prerequisites of Disaster Recovery (ie. restoring an RMAN backup to a new physical server)

To do this, you are required to have:

• Backups of all datafiles
• All archived redo logs generated after the creation time of the oldest backup that you intend to restore
• At least one control file autobackup
• A record of the DBID of the database

Backups of all datafiles - my guess is that's what all the files are except for the "CONTROLFILE...", so I have these

All archived redo logs generated after - I must not have these

At least one control file autobackup - From what I've read, the control file with the backup set is not the same as the control file from the autobackup. So I think that means I also don't have this. Does that sound right?

A record of the DBID of the database - From what I've read, these is a number that appears in the control file of the backup set, between the c- and the following -, so here it would be "655555554".

So it seems I am missing 2 of the 4 requirements. Can some needed things be derived?

There is also the matter of server parameters. Other documentation I've read covering restoring an RMAN backup to a different system says the server parameters, or SP file, are required. Something important in that is directory structure, though there are probably other things needed from that. Apparently directory structure has to be rebuilt on the new server just how it was on the original server?

Regarding Avamar - I understand it is a way to store a backup on a networked machine. It has a plugin for Oracle which would be running on the server housing the Oracle Database, and that would work in conjunction with RMAN to back up and restore. I am not sure where to begin with Avamar. I imagine I would have to contact Dell and license that. Does that sound correct? But is Avamar required in this situation?

From what I've read about RMAN, it is geared toward restoring to the same server that did the original backing up. The requirement for the same directory structure makes sense then. Performing a restore to a new server is considered an advanced scenario. Most terminology related to RMAN assumes the server is the same, and that makes some documentation confusing.

Also from what I've read, Oracle and RMAN can restore a database that was backed up using RMAN from an earlier version. However, if the new server has a newer version, the database will have to be migrated after it is restored, before it can actually be accessed. Does this sound correct?

What I've tried -

I've read the human-readable portions of the file CONTROLFILE.EC700QSE.c-655555554-20200309-00 with a text editor. It states compatibility with Oracle Database version 11.2.0.4.0. It seems to indicate Windows was the platform since I see file paths that include backslashes and E: for example. It also indicates "TAPE" rather than "DISK". I can also see many file paths to .DBF files within "ORADATA" and .ARC files within "FAST_RECOVERY_AREA" listed in there among the unreadable data.

"EC700QSE" seems to correspond to the db_name listed in the control file. So I assume that is the database name, and those 25 other files are the backups of the original Oracle database data files. Is this correct?

I've installed Oracle Database versions 19c (19.3) and 11g (11.2) on different machines and made RMAN restore attempts with both of those unsuccessfully.

I have attempted to mitigate the lack of autobackups. I tried using MAXDAYS in the RESTORE CONTROLFILE command. This failed due to syntax as "MAXDAYS" was not expected and isn't among the list of expected options. It seems from the message that MAXDAYS can only be used when restoring from AUTOBACKUP. I found that by attempting a restore from autobackup command which included MAXDAYS and it executed fine without complaint.

I also tried using SET UNTIL TIME before the RESTORE CONTROLFILE command. However it still failed with an error about no AUTOBACKUP found.

I've tried researching extensively, but I am frustrated that Oracle's documentation largely is blocked without a Service Identifier which requires a license. There are many articles I would like to read, but only the very beginning shows. Their support also does not help without a license.

I have communicated with Oracle quite a lot and found the high cost of their licensing precludes that option for gaining access to more support documentation and possibly support from an expert. The end goal isn't to run an Oracle database here. I'd just like to provide some data that is locked in here in any format that could be browsed by a human.

Any help is appreciated! Let me know if I have some misconceptions in there. Do you see some showstoppers? What do you think of the prerequisites from that Oracle RMAN page on advanced scenarios? Are there any additional routes you are aware of for getting help from Oracle in a situation like this? Do you know of other good publicly-accessible documentation URLs for this procedure? If you would like more information, please ask, and I'll try to provide it. Thank you.

Answer 1

• Backups of all datafiles - it seems you have them
• All archived redo logs generated after the creation time of the oldest backup that you intend to restore - not needed for restoring an offline consistent backup, but this may be an inconsistent online backup, for which they may be included in your "EC700QSE_*" files
• At least one control file autobackup - it seems you have it
• A record of the DBID of the database - you guessed it right, it is included in the name of controlfile autobackup: 655555554. However, you may not need it at all.

This is way behond the scope of a forum answer, and no single documentation or blog post or anything similar will give you all the necessary sequence of steps with details.

I believe all of the below can be found in the public documentation, but the amount and the detail there can be overwhelming.

The very basic steps for this:

• Install 11.2.0.4 (you have already identified the version) database software on a Windows host (you have already identified the platform). If you use a newer version, you will be able to open the database only in UPGRADE mode and you will have to upgrade it before you can use it.

• Create a dummy instance with the name (ORCL for simplicity) of your database:

set ORACLE_HOME=C:\path\to\installed\11.2.0.4\dbhome
set PATH=%ORACLE_HOME%\bin;%PATH%
oradim -new -sid ORCL

• Start the instance without an spfile and restore the spfile from the autobackup:

set ORACLE_SID=ORCL
rman target /
RMAN> startup nomount
RMAN> restore spfile from 'C:\path\to\CONTROLFILE.EC700QSE.c-655555554-20200309-00';
RMAN> exit

• At this point you may need to edit the values in the server parameter file, and you may need to do it offline by creating the nonbinary pfile version of it, so it can be edited then converted back to the binary spfile format. Or you may skip this step if you have the same environment with the same hardware resources and storage locations.

sqlplus / as sysdba
SQL> shutdown abort
SQL> create pfile from spfile;
-- edit values in %ORACLE_HOME%/dbs/initORCL.ora, memory size, directory paths, etc.
SQL> create spfile from pfile;
SQL> startup nomount;
SQL> exit

• Restore controlfile from the autobackup:

rman target /
RMAN> restore controlfile from 'C:\path\to\CONTROLFILE.EC700QSE.c-655555554-20200309-00';

• Mount the database, and make it aware of the location of the backups:

RMAN> alter database mount;
RMAN> catalog start with 'C:\path\to\backups\';

• List datafiles and their original path:

RMAN> report schema;

• Restore the database, with updated paths if needed:

run
{
  set newname for datafile 1 to 'D:\ORCL\system01.dbf';
  set newname for datafile 2 to 'D:\ORCL\sysaux01.dbf';
  ...
  set newname for tempfile 1 to 'D:\ORCL\temp01.dbf';
  ...
  restore database;
}

• After the restore, switch datafile entries to the freshly restored files:

RMAN> switch database to copy;

• Recover database if needed:

RMAN> recover database;

• Use UNTIL TIME with the restore and recover commands if needed.
• Fix the path of redo logs if needed, query original paths from v$logfile:

SQL> alter database rename file 'E:\ORCL\redo01.log' to 'D:\ORCL\redo01.log'; 
SQL> alter database rename file 'E:\ORCL\redo02.log' to 'D:\ORCL\redo02.log';

...

Finally open the database:

SQL> alter database open resetlogs;

If you used a newer version, you can open the database as below, after which you have to upgrade the database to the new version:

SQL> alter database open resetlogs upgrade;

You may encounter various issues, for example:

• Spfile was not included in the backup and you need to create a pfile manually then convert it to an spfile.
• You have "TAPE" device backup pieces, but you have them on disk. If I remember correct, "TAPE" backups and "DISK" backups had different headers and could not be used interchangeably, "TAPE" backups could not be used with a "DISK" channel in old versions, even if they were placed in the locally accessible filesystem. You may overcome this by accessing the "TAPE" backups on disk with a simulated "TAPE" channel with the "oracle.disksbt" library.
• Your backup is incomplete and the database can not be recovered to a consistent state.
• etc.