Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle instance terminated

Posted on 2006-03-23
14
Medium Priority
?
3,442 Views
Last Modified: 2012-05-05

My oracle instance keeps terminating.

I have Ora92 running on Windows 2000 Server Service Pack 4.

alert.log and associated files are below.

Thoughts?

-RD


alert.log:
Thu Mar 23 15:48:01 2006
KCF: write/open error block=0x2ffff9 online=1
     file=13 F:\ORACLE\TABLESPACES\BM_TAB.ORA
     error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 3145721)'
Thu Mar 23 15:48:01 2006
Errors in file f:\oracle\trace_log\bmit_dbw0_2076.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 13 (block # 3145721)
ORA-01110: data file 13: 'F:\ORACLE\TABLESPACES\BM_TAB.ORA'
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 3145721)

DBW0: terminating instance due to error 1242
Thu Mar 23 15:48:01 2006
Errors in file f:\oracle\trace_log\bmit_pmon_1780.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

Instance terminated by DBW0, pid = 2076




bmit_pmon_1780.trc
*** 2006-03-23 15:48:01.000
*** SESSION ID:(1.1) 2006-03-23 15:48:01.000
error 1242 detected in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode



bmit_dbw0_2076.trc:
*** 2006-03-23 15:48:01.000
*** SESSION ID:(2.1) 2006-03-23 15:48:01.000
KCF: write/open error block=0x2ffff9 online=1
     file=13 F:\ORACLE\TABLESPACES\BM_TAB.ORA
     error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 3145721)'
error 1242 detected in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 13 (block # 3145721)
ORA-01110: data file 13: 'F:\ORACLE\TABLESPACES\BM_TAB.ORA'
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 3145721)
0
Comment
Question by:rdefuria
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16275001

NO more space on disk F:
0
 
LVL 2

Assisted Solution

by:bcarlis
bcarlis earned 200 total points
ID: 16277429
I agree with MikeOM...

ORA-01114: IO error writing block to file string (block # string)

Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

Action: Restore access to the device or remove unnecessary files to free up space.

Also: ?? tablespace is corrupt. Can you export it to see if it can read all data.??
http://forums.databasejournal.com/archive/index.php/t-35159.html

Bill :>)
0
 

Author Comment

by:rdefuria
ID: 16279000

Plenty of disk space on drive F (22 GB).

AUTOEXTEND is on.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:marper
ID: 16281027
You either have an issue with the memory on your server or the file 'F:\ORACLE\TABLESPACES\BM_TAB.ORA' is corrupted. Most likely the 2nd one

see more info below on the erros you are getting:

Error:      OSD 4026
Text:      Invalid parameter passed.
---------------------------------------------------------------------------
Cause:      Internal Error
Action:      Contact your Oracle Customer Support Representative Memory errors:
      OSD-4100 to OSD-4199

Error:  ORA 1242
Text:   "data file suffered media failure: database in NOARCHIVELOG mode"
-------------------------------------------------------------------------------
Cause:  The database is in NOARCHIVELOG mode and a database file was
          detected as inaccessible due to media failure.
Action: Restore accessibility to the file mentioned in the error stack
          and restart the instance.



I would restore the file from back up, restart DB and you should be fine.

0
 
LVL 4

Expert Comment

by:marper
ID: 16281064
Recover the database until point in time before you attempted to resize the
datafile.
 
Steps to follow:

Copy all datafiles, controlfiles and redo logs from cold backup.
Make sure all archived redo logs are in correct location.

svrmgrl> STARTUP MOUNT
svrmgrl> RECOVER DATABASE UNTIL TIME '1999-02-19:14:30:00';

Where time format is YYYY-MM-DD:HH24:MI:SS

svrmgrl> ALTER DATABASE OPEN RESETLOGS;

Take a cold backup because resetlogs option was used.

 

Solution Explanation:
=====================
In case of KCF: write/open errors, error code and additional information is the
most important part. In your case it is not a 2 GIG problem (ulimit on HP-UX
can not be changed), neither do you run with multiple dbwr.

The datafile has already become unusable and it either needs to be recovered or
dropped.

If the datafile is dropped all data in it will be lost which is unacceptable.
Therefore the only solution left is to recover in point in time before the
problem occured.

To solve original datafile problem the whole tablespace with new datafile sizes
needs to be recreated: run tables export, drop and recreate the tablespace. Run
import to get data back.


0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
ID: 16281525

@marper: Don't think he can recover because:
"ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode"

Perhaps you can use dbverify to find corrupted blocks exist and use DBMS_REPAIR to fix them.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16282487
What is the size of that datafile?  Is it near or above 2GB?  I'm not sure that file sizes larger than 2GB are supported on Windows2000 / Oracle9.  If it is near or over 2GB, that may be the problem.

Also, running an Oracle database in NOARCHIVELOG mode greatly reduces your recovery options, and means that you most likely will lose (or have already lost) data when you get an error like this.

How recent was your latest cold backup?  Do you have a recent full export?
0
 

Author Comment

by:rdefuria
ID: 16282816
The tablespace file is 24+GB; file sizes on win2k/ora92 are not limited to 2GB.

I will try the dbverify and report back.
0
 
LVL 4

Accepted Solution

by:
marper earned 200 total points
ID: 16282847
could you also post more info found in the trace file f:\oracle\trace_log\bmit_dbw0_2076.trc ?
0
 

Author Comment

by:rdefuria
ID: 16299853
Here's some more info:

although AUTOEXTEND was set, and the increment was set to 1GB (recall: lots of free disk space on the F: drive), Oracle was not extending that tablespace when the "Used %" got to be 99+.

Out of curiousity, in Oracle Enterprise Manager, I manually increased the size of that tablespace, then everything started working just fine again.

Not sure why this was not getting autoextend-ed.


0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 16302758
I'm curious about the NOARCHIVELOG mode.  Are you prepared for a disk failure or file corruption in this data file?  Or, can you rebuild/recover the data in this database from exterior sources if you need to?
0
 

Author Comment

by:rdefuria
ID: 16302842
Your latter assumption is correct -- I can easily rebuild the database if need be from exterior data sources.

Further, this db is development-only, not production.
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 200 total points
ID: 16318572
You datafile has been corrupted.
Restoring the latest backup is only option now
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question