- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- datafile of index tablespace corrupt
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2003 10:25 PM
тАО08-29-2003 10:25 PM
I have checked the corrupted datafile have 20 very large index table on it.
Because this is a production database, and do not allow long time shudown.
I think the best way is move the data on corrupted datafile to another good datafile online. But I don't know how to do it or does oracle support it.
because OS command will copy corrupt data to new datafile, I can't use it.
Can some one tell me a good way to resolve this problem?
I only know 2 way:
using datacopy function of raman. it will need offline the full tablespace.
export tablespace or related index table, it will lock the data for a long time.
rebuild related index on other tablespace. It will cost a long time and maybe affect user's current operation.
Other question, Because oracle looks Ok, If I don't do any action, how much dangerous I will face?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-30-2003 04:29 AM
тАО08-30-2003 04:29 AM
Re: datafile of index tablespace corrupt
If you suspect an index is corrupt then it can be checked against the table using the following command:
ANALYZE TABLE
This will cross check the table with all of its indexes. This can take a while and will block DML while it runs. It will report an ORA-1499 error if there is a mismatch between the table and one of its indexes.
But this will not detect IOT data loss.
An alternative but less rigorous check is to use statements of the form:
rem Make the queries consistent with respect to each other
SET TRANSACTION READ ONLY;
rem Find the total number of entries in the table
SELECT /*+ FULL(tabname) */ count(*) FROM tabname;
rem Find the total number of entries in the index
SELECT /*+ INDEX_FFS(tabname index_name) */ count(*) FROM tabname;
This technique is much faster and does not block any DML. It can however:
a. Only show a mismatch in row counts between index and table
b. Only be used if the index is on a NOT NULL column otherwise the INDEX_FFS hint will be ignored. If there is a mismatch then the ANALYZE TABLE command can be used to check the problem in more detail.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-30-2003 06:22 AM
тАО08-30-2003 06:22 AM
Solutionmy suggestion is a little hard, but maybe with a lesser impact on production.
Create a new tablespace for the indexes,
and, 1 by 1, drop the index from the corrupted tablespace and recreate them in the new tablespace.
No downtime requested, there are some obvious drawback: lesser performance due to the index rebuild, need for space in temp tablespace.
pro: new index, no downtime, index are reorganized (and this is really good).
It's week-end, so i imagine less load on the system.
You said that his operation will request long time... use parallel index creation, if you are on oracle 817 it can save you much time.
I don't think that using rman datacopy function can help, if data is corrupted, even the copy will be corrupted.
Massimo
BTW, oracle is running fine because if it found an index unusuable, it will bypass it and read the data from the table directly. Just a smaller performance.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-30-2003 10:09 PM
тАО08-30-2003 10:09 PM
Re: datafile of index tablespace corrupt
I have a question again.
The problem of datafile like some data block(disk section) was corrupted. If any process want to read data from these data block, it will hang.
Because the oracle have not read these data block, oracle is OK now.
My question is:
if oracle read dat from these bad data block later, what action will oracle take?
disable the affected index table or offline the whole index tablespace or crash oracle abnormally?
If it crash oracle or offline the whole tablespace, can I online it again?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2003 08:31 AM
тАО08-31-2003 08:31 AM
Re: datafile of index tablespace corrupt
And the trace file will contain something helpful in identifying the possibly corrupted data object or corrupted indexes.
In your case, if you find index corruption, you can re-create the corrupted index.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2003 06:30 PM
тАО08-31-2003 06:30 PM
Re: datafile of index tablespace corrupt
Is this a critical system?
It will be better to use a RAID5 system to prevent this sort of problem.
You are quite lucky that the disk that failed contain only the indexes and not the datafiles...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-31-2003 07:35 PM
тАО08-31-2003 07:35 PM
Re: datafile of index tablespace corrupt
yes check the alert log and udump directory for error logs.
Before you rebuild / re-create indexes check to see if there are indexes in invalid status.
then try to validate the structure by analyze table
If the index is corrupted then do as Massimo suggested. Create a new tablespace in a new datafile. Then give quota to this user on the new tablespace. Now as this user drop the index thsat was reported a corrupted from the analyze statement, and re-create the index in the new tablespace. This will not have any down time but poor performance until the index has been recreated.
if you have very few indexes to be re-created then re-create them.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-01-2003 01:19 AM
тАО09-01-2003 01:19 AM
Re: datafile of index tablespace corrupt
The correct way is to restore the datafile to other good disk and do your recovery.
Or, you will have to rebuild all your indexes in that tablespace to new tablespace and drop the old tablespace.
Single datafile cannot be dropped from the tablespace.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-01-2003 06:30 AM
тАО09-01-2003 06:30 AM
Re: datafile of index tablespace corrupt
>if oracle read dat from these bad data block >later, what action will oracle take?
>disable the affected index table or offline the >whole index tablespace or crash oracle >abnormally?
>If it crash oracle or offline the whole >tablespace, can I online it again?
Oracle will offline the datafile, and probably also the tablespace. Since there are indexes, the db should not crash, but i admit that i cannot put my hands over ( and not on :) ) the fire.
For sure you will have messages stating that blocks cannot be read, ORA-600 and so on, and the index will be put offline.
You can check it now, querying the dba_indexes and see the column "status". If it is <> valid, the index is flagged as unusable.
Massimo