topic Re: ora-01157 in Operating System - HP-UX

ora-01157

dngaya — Mon, 08 Dec 2003 07:38:56 GMT

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.

Re: ora-01157

Graham Cameron_1 — Mon, 08 Dec 2003 07:52:47 GMT

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

Re: ora-01157

dngaya — Mon, 08 Dec 2003 08:26:48 GMT

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.

Re: ora-01157

Jean-Luc Oudart — Mon, 08 Dec 2003 08:38:44 GMT

If you can startup mount the database,

list yuor datafiles with :

select * from v$datafile;

Regards,
Jean-Luc

Re: ora-01157

John Palmer — Mon, 08 Dec 2003 08:40:19 GMT

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

Re: ora-01157

dngaya — Mon, 08 Dec 2003 08:44:14 GMT

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

Re: ora-01157

dngaya — Mon, 08 Dec 2003 08:55:06 GMT

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

Re: ora-01157

Jean-Luc Oudart — Mon, 08 Dec 2003 08:55:59 GMT

Well,

try your controlfiles :
select * from v$controlfile;

This should return the control files and status.

Regards,
Jean-Luc

Re: ora-01157

dngaya — Mon, 08 Dec 2003 09:00:41 GMT

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.

Re: ora-01157

Tomek Gryszkiewicz — Mon, 08 Dec 2003 09:12:38 GMT

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

Re: ora-01157

Hein van den Heuvel — Mon, 08 Dec 2003 10:03:03 GMT

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.

Re: ora-01157

dngaya — Mon, 08 Dec 2003 10:50:27 GMT

how can i create a controlfile file ?.

Re: ora-01157

Jean-Luc Oudart — Mon, 08 Dec 2003 11:02:12 GMT

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

Re: ora-01157

Hein van den Heuvel — Mon, 08 Dec 2003 11:07:46 GMT

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.

Re: ora-01157

Tomek Gryszkiewicz — Mon, 08 Dec 2003 11:14:16 GMT

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

Re: ora-01157

dngaya — Mon, 08 Dec 2003 11:19:32 GMT

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

Re: ora-01157

Jean-Luc Oudart — Mon, 08 Dec 2003 11:22:11 GMT

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

Re: ora-01157

dngaya — Mon, 08 Dec 2003 11:42:23 GMT

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.