How to restore an Oracle file system backup onto a new developer machine

installationoracleoracle-11g-r2restore

A server (not mine) has died due motherboard temperature problems and disk controller problems. Given that the company's IT support should have known better, and so on, the company does have a file system backup of a directory structure that looks like this:

Oracle
├───adminscripts
│       (this looks like old custom scripts)
├───EM
│   └───DBNAME
│           emkey.ora
├───fast_recovery_area
│   └───DBNAME
│       └───ARCHIVELOG
│           ├───2015_06_01
│           ├───2015_06_02
│           ├───.... 
│           ├───2018_11_03
│           └───2018_11_04
│                   O1_MF_1_5091_FXVO24XT_.ARC (~80MB)
│                   O1_MF_1_5092_FXWPML1J_.ARC (~85MB)
│                   O1_MF_1_5093_FXXTSO2W_.ARC (~40MB)
├───oradata
│   ├───orcl
│   │       CONTROL01.CTL (~10MB)
│   │       REDO01.LOG    (~50MB)
│   │       REDO02.LOG    (~50MB)
│   │       REDO03.LOG    (~50MB)
│   │       SYSAUX01.DBF  (~520MB)
│   │       SYSTEM01.DBF  (~750MB)
│   │       TEMP01.DBF    (~20MB)
│   │       UNDOTBS01.DBF (~46MB)
│   │       USERS01.DBF   (~5MB)
│   └───DBNAME
│       ├───CONTROLFILE
│       │       O1_MF_9T04M5YO_.CTL (~10MB)
│       ├───DATAFILE
│       │       O1_MF_SVO_DATA_9T06KN5J_.DBF (~9GB)
│       │       O1_MF_SVO_DATA_9T06LGRN_.DBF (~1.5GB)
│       │       O1_MF_SVO_DATA_9T06LO63_.DBF (~30MB)
│       │       O1_MF_SYSAUX_9T04J8M6_.DBF   (~1.8GB)
│       │       O1_MF_SYSTEM_9T04J8JR_.DBF   (~1.5GB)
│       │       O1_MF_TEMP_9T04MLQV_.TMP     (~600MB)
│       │       O1_MF_TOOLS_9T06LODY_.DBF    (~100MB)
│       │       O1_MF_UNDOTBS1_9T04J8N5_.DBF (~500MB)
│       │       O1_MF_USERS_9T04J8O5_.DBF    (~5MB)
│       └───ONLINELOG
│               O1_MF_1_9T04MB6F_.LOG        (~100MB)
│               O1_MF_2_9T04MC8Y_.LOG        (~100MB)
│               O1_MF_3_9T04MDHC_.LOG        (~100MB)
└───scripts
        (this looks like old custom scripts)

I have to assume that this database backup was done safely, with the database put into backup mode or stopped before the backup was done. The backup is a disk image of a data disk, and doesn't contain other configuration information.

So I need to be able to extract the data from this. How do I do it? I assume that I have to set up an Oracle instance on my computer (64-bit Windows 10 Pro), and then somehow move this data somewhere into the installation and hook it up. I've downloaded the Oracle installer, but is there a particular kind of setup that's going to be best for this?

I know how I'd do it with MySQL, but this is new to me. I've searched for backup / restore procedures for Oracle, but they tend to involve rman.

Best Answer

Here's what I did ... this required a lot of trial and error, but I've got access to the database.

Installed Oracle with it's home on an R: drive. Then opened an administrative command prompt, then:

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>oradim -new -sid SVP -startmode manual -pfile r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> create spfile='r:\oracle\product\11.2.0\dbhome_1\database\SPFILESVP.ORA' from pfile='r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora';
SQL> startup nomount
SQL> CREATE DATABASE SVP
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   EXTENT MANAGEMENT LOCAL
   DEFAULT TEMPORARY TABLESPACE temp
   UNDO TABLESPACE undotbs1;
SQL> shutdown immediate

At this point, I moved the new database's CONTROLFILE, DATAFILE and ONLINELOG folders away from R:\oracle\oradata\SVP and replaced them with the ones from the backup oradata\DBNAME folder (from the question).

SQL> startup nomount -- (because we'd get an error here about the control file)
SQL> alter system set control_files='R:\oracle\oradata\SVP\CONTROLFILE\O1_MF_9T04M5YO_.CTL' scope=SPFILE;
SQL> alter system set compatible='11.2.0.4.0' scope=SPFILE;
SQL> shutdown immediate
SQL> startup

The init_svp.ora file looks like this:

db_name='SVP'
memory_target=1G
processes = 150
audit_file_dest='r:\oracle\admin\SVP\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='r:\oracle\oradata'
db_recovery_file_dest='r:\oracle\fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='r:\oracle'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'

I don't know if I've got anything I don't need, and I don't particularly care. I've got access to the data, so I'm all good.

Thanks to those who posted ... mustaccio's comment about making Oracle think it already existed by supplying the control file was helpful, as were several web pages that got me through the "control_files" and "compatible" parameter issues.

EDIT: After you reboot your computer, you may get an Oracle error saying something like:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

You'll need to mount your database with these commands (substituting in your details, of course):

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
Related Question