snippetMinor
How do I restore an Oracle file system backup onto a new developer machine?
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:
I have to assume that this database backup was done safely, with the database put into backup mode or st
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:
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).
The init_svp.ora file looks like this:
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:
You'll need to mount your database with these commands (substituting in your details, of course):
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 immediateAt 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> startupThe 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 existYou'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> startupCode 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 immediateSQL> 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> startupdb_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 existR:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> startupContext
StackExchange Database Administrators Q#222470, answer score: 3
Revisions (0)
No revisions yet.