OraFAQ Forum: Server Administration » ORA-01115:ORA-27070:

Home » RDBMS Server » Server Administration » ORA-01115:ORA-27070:

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01115:ORA-27070: [message #574050]

Sun, 06 January 2013 04:46

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

i am not able to create the index as foloows...
i have tried to create the index in diff tbs also but same error is there

SQL> create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index;
create index inx_tbl_voicechat_unsub_ani on tbl_voicechat_unsub (ani) tablespace ideadb_index
*

ERROR at line 1:
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).
ORA-01115: IO error reading block from file 201 (block # 144265)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check).

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574051 is a reply to message #574050]

Sun, 06 January 2013 04:51

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have a disk block corruption, file 201, block 144265.
1/ Run dbverify on the file
2/ Check which object belongs to this block:

select owner, segment_name, partition_name, segment_type
from dba_extents
where file_id = 201
  and 144265 between block_id and block_id+blocks-1
/

Regards
Michel

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574052 is a reply to message #574051]

Sun, 06 January 2013 04:57

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

the command returns

no rows selected

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574053 is a reply to message #574052]

Sun, 06 January 2013 05:03

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

SQL> select file_id

FILE_ID
----------
8
9
10
4
3
2
1
5
6
7
11

FILE_ID
----------
12

12 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574054 is a reply to message #574053]

Sun, 06 January 2013 05:04

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

sir why is the file _id 201 is not showing ???
when i run select file_id from dba_dat_files;

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574055 is a reply to message #574054]

Sun, 06 January 2013 05:35

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

And if you query dba_temp_files as well as dba_data_files?

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574056 is a reply to message #574055]

Sun, 06 January 2013 06:04

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, John is right it is a tempfile and I bet your db_files parameter is set to 200.
In this case, just drop the temp tablespace (with "including contents and datafiles") and recreate it; do it when no one is using the tablespace, the best way is to shut down and restart the database in restricted mode to do it and then enable the database from everyone.

Regards
Michel

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574064 is a reply to message #574056]

Sun, 06 January 2013 08:40

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

i check dba_temp_files also it give file_id 1 Sad

.....

realy bemused wat is this 201 file

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574066 is a reply to message #574064]

Sun, 06 January 2013 10:31

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Clue:

Quote:

I bet your db_files parameter is set to 200.

Regards
Michel

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574081 is a reply to message #574066]

Sun, 06 January 2013 22:58

maninderkrb
Messages: 41
Registered: August 2012
Location: India

Member

yes you are right sir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams049.htm

DB_FILES specifies the maximum number of database files that can be opened for this database. The maximum valid value is the maximum number of files, subject to operating system constraint, that will ever be specified for the database, including files to be added by ADD DATAFILE statements.

but we are hardly having 10 datafile.

how to identify 201 datafile now?

Report message to a moderator

Re: ORA-01115:ORA-27070: [message #574096 is a reply to message #574081]

Mon, 07 January 2013 01:35

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is tempfile 1 = 201-200.

Regards
Michel

Report message to a moderator

Previous Topic:	TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
Next Topic:	high REDO generation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 23:35:49 CST 2025