3

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.

  • Is your OS exactly the same as theirs? – Gerard H. Pille Nov 13 '18 at 21:42
  • No, I have Windows 10 Pro, and they have (had) a Windows server edition. Not sure which one. – Phil Cairns Nov 13 '18 at 21:53
  • Maybe this sort of thing: oracledistilled.com/oracle-database/administration/… ? – Phil Cairns Nov 13 '18 at 22:52
  • looks fine except that i am missing a pfile/ini.ora-file – miracle173 Nov 13 '18 at 23:41
  • 1
    You don't want to create a database, you want to make Oracle think that it already exists by supplying the existing control file to it. If the "not yours" company IT support made file system backups, the corresponding .ini file is bound to be present somewhere. – mustaccio Nov 14 '18 at 0:00
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.