Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, gurupi.

Asked: July 18, 2016 - 10:32 am UTC

Last updated: July 22, 2016 - 7:25 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi Tom,

This is Gurupi from India. I am a newbie to Oracle but have fair working knowledge in Linux OS.

One of my clients need to restore their Oracle 10g databases on their old IBM server running CentOS 5. Their server drives crashed and they had to install new HDDs.

They do not seem to have any kind of conventional backup taken using Oracle utilities like EXP or RMAN. It's 10 years old data which is not used in production online now. But they need the info in the databases badly.

All they have is 3 .ctl files, 3 .log files & no. of .dbf files in various directories representing their units / branches.

All the above are in an ext. USB hard drive. They also have in the admin directory, the ?dump dirs & a pfile dir for all the DB names and also spfile<db_name>.ora in the dbs dir in the USB drive.

I have installed CentOS 5.2 and Oracle 10.2.0 on that. I am able to access the database (default - during installation) through Ent. Manager web console and through sqlplus and isqlplus too.

Here is what I did:
I created a new database in the same name reflecting the dir name or from the spfile<db_name>.
After that I copied all the datafiles from the relevant datafile dir in the USB drive to the new oradata dir (the default datafile dir) under $ORACLE_BASE. That include the users .dbf files too.

But when I restarted the system, sqlplus is unable to open the database, though it mounts. I verified the SID env variable too.
I get the following message:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/RSCLHO/rsclho/system01.dbf'

Please assist me to restore the databases. I hope I have provided enough info for you to help me.

Thanks

gurupi

and Connor said...

In the control file is contains the expected location of every single database file.

If you database is mounted, you can do

alter database backup controlfile to trace

to see where it expects each datafile to be. You must then either

a) copy that structure with your datafiles on your new machine (or use sym links to do it), or

b) recreate the controlfile (using the trace file as a starting point) but change all the file locations to where you now have them.

In the case of (b), you then do startup nomount, create the new controlfile, and the open the database.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tom's Rational methodology (aka Proof by example)

Rajeshwaran, Jeyabal, July 21, 2016 - 1:13 pm UTC

Connor,

Could you show us the above steps by a demo? that would even help us to understand the command along with your explanation.

Also if someone interested, they can even dig through the other options with-in the commands.

Connor McDonald
July 22, 2016 - 7:23 pm UTC

I can ... but that will be after my 2 week vacation :-)

gurupi, July 22, 2016 - 8:53 am UTC

I followed option (b) after creating and copying the following lines from trace file, I get the following error:

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SMUN" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/SMUN/SMUN/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/SMUN/SMUN/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/SMUN/SMUN/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/SMUN/SMUN/system01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/undotbs01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/sysaux01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/users01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/example01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_SLS_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_GEN_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_MTC_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_FAA01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_DIVN01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_PES01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_WOS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_DMR01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_PES_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_MMS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_CMS_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_STS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_MTC01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_GCMS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_FAA_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_GEN01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_STS_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_SCOTT01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_CMS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_SLS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/RBS01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/INDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_WOS_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_DMR_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_GCMS_NDX01.dbf',
'/u01/app/oracle/oradata/SMUN/SMUN/SIS_MMS_NDX01.dbf'
CHARACTER SET WE8ISO8859P1
;
ORACLE instance started.

Total System Global Area 3120562176 bytes
Fixed Size 2024008 bytes
Variable Size 637537720 bytes
Database Buffers 2466250752 bytes
Redo Buffers 14749696 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 CREATE CONTROLFILE REUSE DATABASE "SMUN" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01517: log member: '/u01/app/oracle/oradata/SMUN/SMUN/redo01.log'

Please help.
Connor McDonald
July 22, 2016 - 7:25 pm UTC

Because you are creating a brand new controlfile, you will be using "SET" to "REUSE", and RESETLOGS to create new online redo logs files.

Once your controlfile is created, you can do

alter database open resetlogs.

(Note: I'm making an assumption here that you these files are logically consistent)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.