Home » RDBMS Server » Server Administration » ORA-01115:ORA-27070:
ORA-01115:ORA-27070: [message #574050] Sun, 06 January 2013 04:46 Go to next message
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).
Re: ORA-01115:ORA-27070: [message #574051 is a reply to message #574050] Sun, 06 January 2013 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01115:ORA-27070: [message #574052 is a reply to message #574051] Sun, 06 January 2013 04:57 Go to previous messageGo to next message
maninderkrb
Messages: 41
Registered: August 2012
Location: India
Member
the command returns

no rows selected
Re: ORA-01115:ORA-27070: [message #574053 is a reply to message #574052] Sun, 06 January 2013 05:03 Go to previous messageGo to next message
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>
Re: ORA-01115:ORA-27070: [message #574054 is a reply to message #574053] Sun, 06 January 2013 05:04 Go to previous messageGo to next message
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;
Re: ORA-01115:ORA-27070: [message #574055 is a reply to message #574054] Sun, 06 January 2013 05:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And if you query dba_temp_files as well as dba_data_files?
Re: ORA-01115:ORA-27070: [message #574056 is a reply to message #574055] Sun, 06 January 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01115:ORA-27070: [message #574064 is a reply to message #574056] Sun, 06 January 2013 08:40 Go to previous messageGo to next message
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
Re: ORA-01115:ORA-27070: [message #574066 is a reply to message #574064] Sun, 06 January 2013 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clue:

Quote:
I bet your db_files parameter is set to 200.


Regards
Michel
Re: ORA-01115:ORA-27070: [message #574081 is a reply to message #574066] Sun, 06 January 2013 22:58 Go to previous messageGo to next message
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?
Re: ORA-01115:ORA-27070: [message #574096 is a reply to message #574081] Mon, 07 January 2013 01:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is tempfile 1 = 201-200.

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


Current Time: Thu Mar 28 18:02:31 CDT 2024