We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Oracle instance terminated

rdefuria
rdefuria asked
on
Medium Priority
3,843 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)
Comment
Watch Question


NO more space on disk F:
BILL CarlisleAPEX Developer
Commented:
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 :>)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:

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

AUTOEXTEND is on.

Commented:
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.

Commented:
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.



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

Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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?

Author

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

I will try the dbverify and report back.
Commented:
could you also post more info found in the trace file f:\oracle\trace_log\bmit_dbw0_2076.trc ?

Author

Commented:
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.


Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
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?

Author

Commented:
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.
Commented:
You datafile has been corrupted.
Restoring the latest backup is only option now
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.