Query to understand the root cause of the issue , you can take the query as given format as below ....
***************************************************************************
ACTION PLAN
===========
1) Please provide a DBV on datafile 208.
2) Please provide:
RMAN> backup validate check logical datafile 208;
when this completes provide:
SQL> select * from v$database_block_corruption;
3) Please also provide:
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
archive log list;
spool off
exit
4) Upload alert.log
*****************************************************
1>>>
ORA -01114 ORA-27069 Errors In Alert Log [ID 433499.1]
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process,
and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.4.0
This problem can occur on any platform.
Symptoms
Following errors are seen when using RAC in the alert log
ORA-01114: IO error writing block to file 82 (block # 526666)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 526666
Additional information: 1
Additional information: 526657
Mon Apr 23 17:00:01 2007
Trace dumping is performing id=[cdmp_20070423170001]
Mon Apr 23 17:00:02 2007
Warning: The file system may not be configured correctly.
The file size returned by file system may be obsolete after a file has been
resized [obsolete size: 526656] [up-to-date size: 526672].
Mon Apr 23 17:16:14 2007
Private_strands 0 at log switch
Thread 1 advanced to log sequence 8483
Current log# 4 seq# 8483 mem# 0: /db/S0LLP0/redoa/s0llp0/log4a.log
Cause
The warning itself reveals the error. Error ORA - 1114 shows that the we are trying to write to a block ( 526666 ) after where
the file system thinks the file ends ( 526656 ). However , Oracle thinks the file should be 526672 bytes.
Looking at the code this suggests that the file may have changed size in another instance, hence on another node,
but this change is not propagated to the node on where the error is reported. The file information is in effect, stale.
Solution
These errors appear transitory and look like a configuration issue in the storage. Check with your
Storage Admin/Vendor to find the cause for this and make appropriate changes
==============>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<============
2>>>
SELECT or ANALYZE returns ORA-1114, ORA-27072, "No space left on device" [ID 121072.1]
Modified 29-AUG-2002 Type PROBLEM Status PUBLISHED
Problem Description
-------------------
You have created a tablespace of type TEMPORARY. The tablespace was created
successfully.
You run a SELECT statement or ANALYZE TABLE statement. You are getting
the following errors:
ORA-1114: IO error writing block to file 102(block # 57333)
ORA-27072:skgfdisp: I/O error
SVR4 Error: 28: No space left on device
(The exact errors may vary depending on the platform / operating system.)
Solution Description
--------------------
Check the space left on the device. Re-create the temporary tablespace with
an appropriate file size.
Explanation
-----------
Your SELECT or ANALYZE statement requires a lot of room for sorting in the
temporary tablespace.
The temporary tablespace was created successfully. You can see the file at
the operating system level. The operating system command (for example Unix
command ls -l) shows that the datafile exists and has the appropriate size.
However, the space for that file has not been really allocated by the
operating system yet (this can be confirmed by another Unix command
df -k
Only when the temporary tablespace is first used, Oracle will attempt to take
the space required by the temporary datafile. If there is insufficient space
on the device, Oracle will return the errors.
===========>>>>>>>>>>>>>>>>><=======================
3>>>
Avoiding ORA-01114 after using transportable tablespace feature. [ID 69373.1]
Modified 07-AUG-2003 Type BULLETIN Status PUBLISHED
PURPOSE
-------
When using the transportable tablespace feature of Oracle8i, if the file
permissions are not set correctly then it is possible to encounter ORA-1114,
ORA-1110, ORA-27091 when attempting to set the tablespace to READ WRITE.
SCOPE
-----
The article is intended to assist analysts and customers who might encounter
the errors.
The scenario is described below:
Source site has Unix user "oracle8" belonging to the dba group.
Target site has Unix users "oracle8" and "oracle8i" as members of the
"dba" group. However, "oracle8i" owns the ORACLE_HOME and the oracle
executables.
You have done the following steps:
1. Logged on to Unix as an user belonging to the dba group (say oracle8).
2. Created a tablespace TEST.
3. Alter tablespace TEST read only.
4. Exported the metadata pertaining to the tablespace.
5. Physically copied the datafile across to the target location using ftp as
user "oracle8".
6. Imported the metadata into the target location.
In the target location when you try to change the tablespace status, you
get errors similar to the following :
SQL> alter tablespace test read write;
alter tablespace test read write
*
ERROR at line 1:
ORA-01114: IO error writing block to file 8 (block # 1)
ORA-01110: data file 8: '/u02/app/oracle/product/8.1.5/oradata/V815/test.dbf'
ORA-27091: skgfqio: unable to queue I/O
SVR4 Error: 9: Bad file number
Additional information: 1
Explanation:
Apparently this might look to be something wrong with the Input/Output
operations of the Operating System. Though it is not the case. When the file
was copied across from the source to the destination, it typically preserves
the ownership of the creator. In the above example, if the ftp from the source
to the destination is done as the "oracle8" Unix user the file permissions
would look like :
-rw-r----- 1 oracle8 dba 2099200 Mar 31 04:39 test.dbf
ie.the mode is 640.
So when the server process (which is not owned by "oracle8") tries to put it
back to READ WRITE mode, it generates the error.
*Important Note* We would expect the operating system to generate EPERM (Error
number 13) instead of EBADF(Error number 9). But this is because there was no
error reported by the open( ) system call. The process has already opened the
file in read only mode (O_RDONLY flag)and hence an error has been reported by
the write( ) system call.
The solution could be:
Either change the permissions of the file in the target location to have
write permission for the "dba" group or ftp the file as the user who owns
the ORACLE_HOME and the executables, in this example "oracle8i".
KEYWORDS
--------
transportable tablespace ORA-27091 ORA-01114
******************************************
4>>>>
Ensure that other tools/products are not running against the Oracle
database files when an Instance startup is being performed.
ORA-1114 and/or ORA-27091 during Instance Startup on OpenVMS [ID 260868.1]
Modified 26-MAR-2010 Type BULLETIN Status PUBLISHED
Checked for relevance on 25-Mar-2010.
PURPOSE
-------
To discuss one possible cause of the following errors during
Instance startup :-
ORA-01114: IO error writing block to file n (block # n)
ORA-27091: skgfqio: unable to queue I/O
SCOPE & APPLICATION
-------------------
This note will be of interest to OpenVMS customers and Oracle Support
Analysts.
ORA-1114 and/or ORA-27091 during Instance Startup on OpenVMS
------------------------------------------------------------
During instance startup, the following errors may be reported in the
ALERT log by the LGWR process.
ORA-01114: IO error writing block to file x (block # y)
ORA-01110: data file x: ''
ORA-27091: skgfqio: unable to queue I/O
ORA-27041: unable to open file
%RMS-E-FLK, file currently locked by another user
LGWR: terminating instance due to error 1114
Or...
ORA-01110: data file x: ''
ORA-01114: IO error writing block to file x (block # y)
ORA-27070: skgfdisp: async read/write failed
%SYSTEM-F-NOPRIV, insufficient privilege or object protection violation -
%SYSTEM-S-NORMAL, normal successful completion
These errors can be reported if something external to Oracle has
taken a VMS lock out on one or more of the database files.
Please check to see if any third party products were being run
that could take out locks on the Oracle database files.
($ SHOW DEV/FILES may help to view open files on each disk)
For example, were VMS backups active at the time of the instance
startup attempt. If so, could the backup have been dealing with the
database files.
Solution:
Ensure that other tools/products are not running against the Oracle
database files when an Instance startup is being performed.
Show Related Information Related
Products
* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Errors
ORA-1114; ORA-27070; ORA-27091
*****************************************************
5 >>>>
ORA-01114 ORA-27063 SVR4 Error: 28: No space left on device,temporary tablespace [ID 121732.1]
Modified 25-JUL-2010 Type PROBLEM Status PUBLISHED
***Checked for relevance on 25-Jul-2010***
Problem Description
-------------------
You have a temporary tablespace of type TEMPORARY.
You are running a SQL statement using temporary storage (for example, a sort
segment to process a GROUP BY or ORDER BY ), and get these errors :
ORA-01114: IO error writing block to file 5015 (block # 199528)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 49152
Solution Description
--------------------
The files of the temporary tablespace of type TEMPORARY are in a full file
system (no free space available).
Make free space in this file system.
Explanation
-----------
On certain file systems, creation of tempfiles does not guarantee the allocation
of the actual disk space of the file size specified.
The disk space is allocated later when the tempfile blocks are accessed.
This has the advantage of fast tempfile creation (as compared to datafile
creation). However, the disk could run out of space when tempfiles are accessed
later. The same thing happens when tempfiles are resized (to a larger size:
either by ALTER DATABASE ... TEMPFILE ... RESIZE command or by autoextend) i.e.
resize does not guarantee the actual disk space allocation on certain file
systems.
Tempfiles are currently not initialized, and (at least on Unix) this means they
are not pre-allocated. So even if a file system has enough free space at the
time the tempfile is created, later on the user may get write errors due to the
file system being full while writing to the tempfile.
DATAFILE FOR TEMPORARY TABLESPACE IS CREATED AS A SPARSE FILE
References
----------
Note:6224.1 : ALERT: Sparse Files and Oracle
Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?
**********************************************
6>>>>
ORA-1114 error accessing Temporary Tablespace on AIX [ID 1083829.6]
Modified 27-NOV-2002 Type PROBLEM Status PUBLISHED
Problem Summary
===============
ORA-1114 Accessing Temporary Tablespace on AIX 4.3.2
RDBMS: 8.1.5 Not Using Transportable Tablespaces
Problem Description
===================
Using Tuxedo and clients are receiving ORA-1114 accessing the temporary
tablespace and receive the following error messages:
ORA-1114: IO error writing block to file #3 block#497707
IBM AIX RISC system/6000: error 27: File too large.
Problem Explanation
===================
You are either on a platform and database version combination that requires
an Oracle patch to access datafiles over 2Gb. With 8.1.5 however, there
are no Oracle patches that are needed to access or create datafiles over 2Gb
AIX 4.2.1 and 4.3 allows datafiles up to 32Gb.
Release AIX Platform Required Patch FS/RAW AIO>2 GB
====================================================================
8.1.3 + >=4.2.1 RS/6000 & SP none both yes
Problem References
==================
But you are still getting that error messages and don't know why?
When you select using SQL*Plus you do not receive the error messages.
Generally when you see this error message it is related to something in
the OS such as large datafiles not being supported.
Solution Summary
================
Check to see if there is enough space in /VAR to even VI a log file.
Solution Description
====================
If you are unable to VI a log file and also receive the ORA-1114 error it
is not related to the datafile size but is most likely related to space
issue in /VAR.
If you clean out /VAR and still receive the error message you might also
need to alter the tablespace to 1Gb and then back to 2Gb or whatever the
size of the file is.
Solution Explanation
====================
If resolve space problems in /VAR allowing the temp tablespace to do
its sorting.
***************************************
7>>>>
Sporadic Export Failure with Exp-00008: Oracle Error 1114 Encountered, ORA-1114 [ID 390605.1]
Modified 07-SEP-2006 Type PROBLEM Status MODERATED
In this Document
Symptoms
Cause
Solution
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6.0
This problem can occur on any platform.
Symptoms
Sporadic export failures with the following errors, yet all datafiles are online.
. exporting synonyms
Export fails with:
EXP-00008: ORACLE error 1114 encountered
ORA-01114: IO error writing block to file %s (block # %s)
EXP-00000: Export terminated unsuccessfully
Cause :
The filesystem where the tempfiles (used by the temp tablespace) are located is currently 100%
utilized.
Checking filesystem at OS level verifies this fact. The location of tempfiles can be found by
selecting from the v$tempfile view. ( i.e. select * from v$tempfile; )
Solution :
1. Add more free space to filesystem.
2. Rerun the export.
Show Related Information Related
Products
* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Errors
EXP-0; EXP-8; ORA-1114
No comments:
Post a Comment