ora-01157

[Hein van den Heuvel]

dngaya,

As Graham wrote in the files reply, you basically have some files missing.

Judging by the error message :"cannot identify data file 1 - file not found"
you are missing at least the first file.
This is probably the system tablespace so Oracle lost its mind!
It woudl not surpricse me if it can not find any file, and just reports this.

You have not given any indication whether
the files are there.

Do you know where (which mountpoint) the database file are supposed to be?
Can you 'see' them with unix (ls -l !)?

Do you know the exact name of all your DB files? You probably should. My favourite way
to gether those is: ALTER DATABASE BACKUP CONTROLFILE TO ...
This allows me to readily re-create control files if I ever have to. But I'm afraid you can not do that now.

The other reply asking for string on the control file was there as is is supposed to show a list of files. For example on a box here I get:
DGL840LG
GL840LG
GL840LG
GL840LG
/dev/vg_gl/rpssys01
/dev/vg_gl/rpsrbs01
/dev/vg_gl/rpslog01
:


Perhaps your ALERT file will give clear hints where the files are supposed to be?
Or your backup scripts/logs ?!

Good luck finding and restoreing those database files!

Regards,

Hein.

[dngaya]

how can i create a controlfile file ?.

[Jean-Luc Oudart]

You need to clone the database on itself :
1) Take a backup of current situation
2) clone the database :
cf this link
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=227394

Regards,
Jean-Luc

[Hein van den Heuvel]

Well, you 'simply' tell Oracle "CREATE CONTROLFILE..."

It's all documented in the SQL Reference Manual.

But the more important documentation is in the "User-Managed Backup and Recovery Guide" or whatever that book was called for the 7.3 release.

However... this is not trivial! And you need that list of file names.... and the file themselfes

Please explain to us (and yourself) what you have done sofar to understand what files are involved, where the are, what happended to them adn so on. Just a control file will not help if you don't have the DB files and unless I have mis-read none of your rpelies suggest you have useable datafiles from either the live systems or from a backup.

Cheers,
Hein.

[Tomek Gryszkiewicz]

If you lost your control files, you can restore it:
1. DO THE BACKUP OF THE DATABASE first (every file) - before you start any processes on this db.
2. Shutdown the database.
3. Remove the controlfiles (remember about BACKUP before!!!!!!)
4. Recreate it with:
STARTUP NOMOUNT;

CREATE CONTROLFILE SET DATABASE "{database name}" RESETLOGS {ARCHIVELOG}
MAXLOGFILES xxx
MAXLOGMEMBERS xxx
MAXDATAFILES xxx
MAXINSTANCES xxx
MAXLOGHISTORY xxx
LOGFILE
GROUP 1 (
'path to log1...',
'path to mirror 1...'
) SIZE xxxM
DATAFILE
'path to file1.dbf',
'path to file2.dbf' etc etc...
CHARACTER SET xxx;

then
alter database open resetlogs;

Be a really careful doing it...
I am not completly sure that the controlfiles are the problem

-Tomek

[dngaya]

when I launch the order alter database backup controlfile to trace, I receive the following result where there is nothing in the files log and the data files not yet, physically the files exists :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DTWPRD" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '' SIZE 1M,
GROUP 2 '' SIZE 1M,
GROUP 3 '' SIZE 1M
DATAFILE
'',
'',
'',
'',
'',
''
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

I do not know more what it is necessary to make?.

[Jean-Luc Oudart]

When you ran select * from v$datafile you did not get any row back.
That's why you have this result now.

You have to find the info elsewhere and rebuild the script from this info.

Regards,
Jean-Luc

[dngaya]

the results above show well that there are no files of log and no data file appears. indeed, I launched the order alter database at the mount step.