HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How do I restore an Oracle file system backup onto a new developer machine?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
filenewsystemontohowmachinedeveloperoraclerestorebackup

Problem

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 st

Solution

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

Code Snippets

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
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
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'
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
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

Context

StackExchange Database Administrators Q#222470, answer score: 3

Revisions (0)

No revisions yet.