Home » RDBMS Server » Server Administration » ORA-01157 on database startup
ORA-01157 on database startup [message #197011] Mon, 09 October 2006 09:46 Go to next message
debarros
Messages: 6
Registered: August 2006
Junior Member
I am trying to configure an Oracle 10.2.x.x on Linux (FC4) and strated to run into the following problem when I try to startup the database.

ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1218892 bytes
Variable Size 88082100 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u03/oradata/devdata'

However, here is some additional info/history on this.
Initially when using OUI, it create a database called (ORCL), and automatically and set all the tablespaces and names.

At the end of that process, I decided that I wanted the database to be something else (DEV) and have the oracle data file in another disk. Then I dropped the ORCL database and create the DEV database using the follwing script:


CREATE DATABASE dev
USER SYS IDENTIFIED BY MYSYS
USER SYSTEM IDENTIFIED BY MYSYSTEM
LOGFILE GROUP 1 ('/u03/oradata/dev/redo01.log') SIZE 100M,
GROUP 2 ('/u03/oradata/dev/redo02.log') SIZE 100M,
GROUP 3 ('/u03/oradata/dev/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u03/oradata/dev/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u03/oradata/dev/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE dev_1
DATAFILE '/u03/oradata/dev/tbsdev01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u03/oradata/dev/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undodev
DATAFILE '/u03/oradata/dev/undotbs01.dbf'
SIZE 60M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Everyting worked fine until two days ago, I was able to connect, mount and start the DEV ($ORACLE_SID = DEV) database normally. However after a reset, I started to get this problem whenever I try to startup/open the database.

I could not find the DBWR file mentioned in the error message...
Any ideas on what I am missing here ?

Any help/info, would be highly appreciated.

Thanks,

--MD.

Re: ORA-01157 on database startup [message #197041 is a reply to message #197011] Mon, 09 October 2006 13:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

However after a reset

sorry but i am not getting above meaning.
plz. clarify more..thanx
Re: ORA-01157 on database startup [message #197054 is a reply to message #197041] Mon, 09 October 2006 15:14 Go to previous messageGo to next message
debarros
Messages: 6
Registered: August 2006
Junior Member
Sorry, I meant a restart/reboot on the server.
Re: ORA-01157 on database startup [message #197098 is a reply to message #197054] Tue, 10 October 2006 01:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

post below
select d.name, t.ts#, d.ts#
from v$datafile d, v$tablespace t
where d.ts# = t.ts#
order by t.ts#

and do below cmd
ALTER SYSTEM CHECK DATAFILES.

Re: ORA-01157 on database startup [message #197147 is a reply to message #197098] Tue, 10 October 2006 04:50 Go to previous messageGo to next message
debarros
Messages: 6
Registered: August 2006
Junior Member
For the select I got the following rows:

/opt/oracle/oradata/orcl/system01.dbf 0 0
/opt/oracle/oradata/orcl/undotbs01.dbf 1 1
/opt/oracle/oradata/orcl/sysaux01.dbf 2 2
/opt/oracle/oradata/orcl/users01.dbf 4 4
/opt/oracle/oradata/orcl/example01.dbf 6 6
/u03/oradata/devdata 7 7


6 rows selected.

And the ALTER COMMAND, ran sucessfully.

Re: ORA-01157 on database startup [message #197164 is a reply to message #197011] Tue, 10 October 2006 05:47 Go to previous messageGo to next message
debarros
Messages: 6
Registered: August 2006
Junior Member
I decided to drop and recreate the database, so the problem should go away now.

Thanks,

--MD.
Re: ORA-01157 on database startup [message #197316 is a reply to message #197147] Tue, 10 October 2006 23:46 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

/u03/oradata/devdata 7 7

Problem Explanation:
====================

Oracle tries to open all datafiles when you issue a STARTUP or ALTER DATABASE
OPEN. If a datafile cannot be found at this point, an ORA-01157 results.

There are two main possible causes for an ORA-01157 on startup:

1. The datafile does exist, but Oracle cannot find it.

1.A) It may have been renamed at the operating system level, moved to a
different directory, disk drive, etc. In this case, simply restoring the
datafile to its original name and location will solve the problem.

1.B) You intentionally moved the datafile to a different location, but forgot
to rename it at Oracle level. For example, you may have decided to move
the index tablespace datafiles to another disk to improve performance, but
forgot to rename them inside Oracle. In this case, you must rename the
datafile(s) from within Oracle.

2. The datafile has been physically removed or damaged to an extent that
Oracle cannot recognize it anymore.

In this case, that datafile is lost and the solution depends on the type of
datafile involved, i.e., on the tablespace to which the datafile belongs.


Solution Description:
=====================

There are two types of solutions for an ORA-01157:


I. THE DATAFILE IS LOST OR TOO DAMAGED
--------------------------------------

In this case, the solution depends on the tablespace to which the datafile
belongs. Look for a Solution Reference matching the type of datafile lost.


II. THE DATAFILE HAS SIMPLY BEEN MOVED OR RENAMED
-------------------------------------------------

If you originally wanted to change the name/location of the file, look for the
Solution Reference on how to rename datafiles inside Oracle. If the file has
been moved or renamed by mistake, simply restore it to its original
name/location and startup the database.

Previous Topic: objects cannot extend
Next Topic: dbms_stats.gather_schema_stats
Goto Forum:
  


Current Time: Tue Dec 06 10:02:49 CST 2016

Total time taken to generate the page: 0.11402 seconds