ora-01157

[dngaya]

hello,
I have a base of data 7.3.4 installed on HP 10, when I have to stop the database and by wanting the restart, I receive the error message according to:

01157, 00000, "cannot identify data file %s - file not found"
// *Cause: The background process was not able to find one of the data files.
// The database will prohibit access to this file but other files will
// be unaffected. However the first instance to open the database will
// need to access all online data files. Accompanying error from the
// operating system describes why file was not found.
// *Action: Have operating system make file available to database. Then either
// open the database or do ALTER SYSTEM CHECK DATAFILES.

how to make for to resolve it?.
thank you for your assistance.

[Graham Cameron_1]

The answer is in the error message. One of the data files is missing, or the permissions don't allow oracle to read/write it.

You need to restore the missing data file from backup. If you don't have a backup you are in trouble, this is a very old version of Oracle and you won't get very good support.

I think you can see the data files by running the following query from svrmgrl (connect internal)

SELECT FILE_NAME, STATUS
FROM DBA_DATA_FILES ;

Check each one is there and with correct permissions.

-- Graham

[dngaya]

hello,
i cannot to restart my database and i cannot to do this command:
SELECT FILE_NAME, STATUS
FROM DBA_DATA_FILES ;
but i can to start the database until the mount step and when i do the command alter database oppen; i receive the oracle error follow:

ORA-01157: cannot identify data file 1 - file not found
ORA-01110: data file 1: ''
thank for your assistance.

[Jean-Luc Oudart]

If you can startup mount the database,

list yuor datafiles with :

select * from v$datafile;

Regards,
Jean-Luc

[John Palmer]

Have you possibly got a filesystem that has been unmounted or failed to mount?

If you can't start the database, you can extract filenames from one of the Oracle controlfiles. Try the following:-

First find the name of a controlfile...
cd $ORACLE_HOME/dbs
more init.ora

Look for a line like
control_files = (/xxx/yyy/zzzz...

Pick one of the controlfiles and do:

strings | more

You should be able to identify the names of your datafiles.

You then need to find out why they aren't available and either make them so or recover from a backup.

Regards,
John

[dngaya]

hello,
when i do the command select * from v$datafile in mount step, i receive o rows.

[dngaya]

hello john palmer,
when i try to do your command follow:
strings '/data/ora_bd.DTWPRD/CTL/dtw_ctl_001.dbf'|more
i receive the result follow:
DTWPRD:/home/dbaora/app/oracle/product/7.3.4
cDTWPRD
cDTWPRD
DTWPRD
DTWPRD
what is mean?.

[Jean-Luc Oudart]

Well,

try your controlfiles :
select * from v$controlfile;

This should return the control files and status.

Regards,
Jean-Luc

[dngaya]

hello,
when i do the command line follow:
select * from v$controlfile;
i received the result abov:
STATUS NAME
------- --------------------------------------------------------------------------------
/data/ora_bd.DTWPRD/CTL/dtw_ctl_001.dbf
/data/ora_bd.DTWPRD/CTL/dtw_ctl_002.dbf
2 rows selected.

[Tomek Gryszkiewicz]

I am afraid something is wrong with you control files: you can try to recreate it, but you have to know the location of every single datafile in the database.

-Tomek

[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.