cancel
Showing results for 
Search instead for 
Did you mean: 

Recovery of missing system file

Former Member
0 Kudos

all,

I just installed a ECC system recently on oracle 10g. The system installed fine and i even created a client from 001 client. unfortunately, I had to reboot the system (solaris zone) and now my system tablespace file is missing !!! This was the only file belonging to system tablespace. And i do not have a backup of it because i havent set up any backup mechanism for this system yet !!! Any ways to restore this file or restore the db somehow ?? I have all the archive logs luckily and also the control file. Do i have any option here other than installing the system again, which I would hate to do. ?? experts, please help

Edited by: rajivshekhar on May 13, 2010 5:47 PM

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi,

Is the tablespace missing just after reboot of the Solaris zone? Is the datafile for this tablespace is a different disc group? Can you check at the global zone if the data files exists? Many times if the share from global zone is not done properly we have the chance to miss out directories.

Regards

Santanu

former_member204746
Active Contributor
0 Kudos

you have no backups, even with archive logs, you are doomed.

it's time to re-install from scratch

case closed.

stefan_koehler
Active Contributor
0 Kudos

Hello Eric,

> you have no backups, even with archive logs, you are doomed.

That's a little bit too sketchy. If you are adding a data file and you have all archive logs (and online red logs) from the time of creation until now you are also still able to recover that data.

But to be honest i have never tried it with a data file that was created by a CREATE DATABASE statement - so no idea if this will work.

Regards

Stefan

volker_borowski2
Active Contributor
0 Kudos

>

> But to be honest i have never tried it with a data file that was created by a CREATE DATABASE statement - so no idea if this will work.

>

> Regards

> Stefan

Hi Stefan,

I haven't done this either, as usually the Creation SCN for datafile #1 is below 10 and you do in general

do not have all logs back to #0001 without gaps

But with TS extension datafiles it works. Been there done that !

It validates v$datafile for FILE# and CREATION_CHANGE#

you simply CREATE DATAFILE and recover, which will ask for logs since CREATION_CHANGE#.

I have a small example of about one and a half screen page how to do it,

but it garbles the Line Breaks when I clip it in.

Volker

former_member204746
Active Contributor
0 Kudos

I see wha t you guys mean.

Is the the same in his case? after all, he lost the SYSTEM tablespace in this process..

stefan_koehler
Active Contributor
0 Kudos

Hello,

i have crosschecked that stuff on my 11gR2 database theoretically.


SQL> select CREATION_CHANGE#, name from v$datafile;
CREATION_CHANGE# NAME
---------------- --------------------------------------------------
               7 /oracle/CAT/oradata/system01.dbf
            2150 /oracle/CAT/oradata/sysaux01.dbf
.....

SQL> select RECID, FIRST_CHANGE# from v$log_history;
     RECID FIRST_CHANGE#
---------- -------------
         1             1
         2         81671
         3        141178
...

So if you create the database with archive log mode initially and you still have all archive logs from change #1 it should work. But i am not quite sure how the SAPinst does create the database (with or without archive log mode).

Regards

Stefan

markus_doehr2
Active Contributor
0 Kudos

> So if you create the database with archive log mode initially and you still have all archive logs from change #1 it should work. But i am not quite sure how the SAPinst does create the database (with or without archive log mode).

Sapinst creates the database with archive log enabled and then switches to noarchivelog as soon as the database load starts. After the loading is complete it switches back and does some dipgntab and other stuff.

Markus

stefan_koehler
Active Contributor
0 Kudos

Hello Markus,

thanks for your completion (a way too long ago that i installed a fresh SAP system).

@ rajivshekhar:

So in this case you are lost ... need to install the system again.

Regards

Stefan

markus_doehr2
Active Contributor
0 Kudos

> @ rajivshekhar:

> So in this case you are lost ... need to install the system again.

If you still have your old installation directory with inifile.xml you can use a call like

${DVD_LOCATION}/installation_master/<PRODUCT>_IM_<PLATFORM>_<OS>[_<DB>]/sapinst SAPINST_PARAMETER_CONTAINER_URL=${COMMON_FOLDER}/inifile.xml

so you don't need to re-enter all the parameters again.

If you didn't use a separate installation directory but /tmp the file will be gone since /tmp is cleared after a reboot.

Markus

former_member204746
Active Contributor
0 Kudos

About /tmp being cleared after a reboot, this is the case on Solaris, but not on HP-Ux. I am unsure of other UNIXes.

markus_doehr2
Active Contributor
0 Kudos

> About /tmp being cleared after a reboot, this is the case on Solaris, but not on HP-Ux.

HP-UX can be configured to do so by changing

/etc/rc.config.d/clean_tmps

but AFAIK on 11.11 and 11.23 it's set to 0 by default. Unsure about 11.31.

Markus

volker_borowski2
Active Contributor
0 Kudos

Hi,

it would be possible to recover it if you really have

the current controlfile and ALL archivelogs since creation of the

datafile, but during the load the SAP-Installation disables DB-Logging,

so you will have gaps in archivelogs.

But if you lost the file in a reboot it may just be loacteted on a filesystem

that has not been mounted automaticly.

Otherwise I can think of no valid way to restore this situation.

Volker

Former Member
0 Kudos

As you are saying physical file is not there and missing file is system data file.....may not be possible to recover.

But you can try as below:

make a data file with same name which one is showing as missed

And delete all your controll files from respective locations

Run controlfile (make the changes which usually make during system copies)

Regards,

Nick Loy

former_member227600
Contributor
0 Kudos

Hi,

Please check the at physical location that system( System TS) file is exist or not.

1:-If file does not exist at the physical location than it's not possible to recover it without DB backup.

2:-If file exist physically & file require recovery than you can recover this file using archive.

3:-use recover database from sql prompt than you will get the archive number which you need to recover the TS. Place these file to archive destination & start recovery.

Thanks & Regards

Karan

Former Member
0 Kudos

karan,

I do not have the file anymore. thats why i mentioned in my message that the system file is missing.

can i use recover database if I do not have the physical file or its backup anymore and try to recover using the archive logs ?? if so, what would be the procedure? thanks

former_member227600
Contributor
0 Kudos

As per my knowledge it's not possible to restore any data file without backup.