- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: ORA-01578 : Best solution?
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
тАО05-15-2005 11:57 PM
тАО05-15-2005 11:57 PM
Re: ORA-01578 : Best solution?
"At the moment when I run dbv the pages marked as corrupt = 1. I presume once the table is deleted and re-created, the dbv should report no corrupted pages??"
Yes, I think so. But you should run it after to be realy sure.
"Is it worh doing the follow ?
1- alter database datafile ..... OFFLINE;
2 - recover automatic datafile .....;
3 - alter database datafile ... ONLINE;"
You can only do this if you have the database in archive mode and a complete and cold (executed off-line) backup: do you have does 2 conditions??
Best Regards,
Eric Antunes
Thanks
kyris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-16-2005 12:06 AM
тАО05-16-2005 12:06 AM
Re: ORA-01578 : Best solution?
No the DB is in non archive mode and they don't even have any 'proper' cold DB backup.
Therefore I can not do what I suggested on my last post?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-16-2005 12:15 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-16-2005 08:00 AM
тАО05-16-2005 08:00 AM
Re: ORA-01578 : Best solution?
first, you should check, if the bad block belongs to the table, or if the statement crashes because it is using an index!!!!
Try to use a FULL Hint, to force Oracle to use a full table scan when accessing data like this:
select /*+ FULL(table_name) */ * from table_name;
If this works, the corruption most likely is related to an index belonging to this table. Re-create the indexes in this case.
To be sure, use this, to identify where the bad block belongs to:
select
segment_name,
segment_type,
block_id,
blocks
from dba_extents
where
(
block_id and (block_id + blocks - 1))
and
file_id =
If it really belongs to the table, you are in trouble. You might be able to read more than the blocks of your where-clause if you can alter the session to ignore these errors.
You can set event 10231 for this (WARNING, not for use in application, only for partially recovery of data! Data in corrupt block will be lost !)
Option two will be to use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to tag the table to ignore the bad blocks.
Be sure to get Metalink Note 33405.1, read and understand it very carefully before you proceed.
What ever you do, do not drop the defective table at all. Do a rename instead to ensure the data is accessible later, even if you managed to read the original partly to a diffrent table name. It might be possible to dump the defective block later and conclude from a hex view which data was in it.
But again, you first approach should be to verify, that the faulty segment is not an index.
Good luck
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2005 11:15 PM
тАО05-17-2005 11:15 PM
Re: ORA-01578 : Best solution?
Please, put your production database in archivelog mode, because if not, recovery will be very difficult.
Cheerio,
Renarios
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-18-2005 01:47 AM
тАО05-18-2005 01:47 AM
Re: ORA-01578 : Best solution?
I am now trying to see if the index is at fault, as per Volker's posting. It's been running for nearly 3 hours now and still not finished..I am going home so hopefully tomorrow I will get a good surprise !!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-18-2005 10:38 PM
тАО05-18-2005 10:38 PM
Re: ORA-01578 : Best solution?
I tried to do drop index index_name but I am getting ORA-00054:resource busy and acquired with nowait specified.
I tried using drop index index_name FORCED but got ORA-00093:SQL command not properly ended;
How do I get round this problem?
thanks in advance
kyris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-18-2005 10:53 PM
тАО05-18-2005 10:53 PM
Re: ORA-01578 : Best solution?
You must kill the session that is locking the index:
select
owner,object_name,
object_type ,
os_user_name osuser,
oracle_username username,
program ,
nvl(lockwait,'ACTIVE') lockwait,
decode(locked_mode,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') lockmode,
session_id sid,
serial#
from
sys.v_$locked_object a,
sys.all_objects b,
sys.v_$session c
where
a.object_id = b.object_id and
c.sid = a.session_id and
OBJECT_NAME =
After this:
alter system kill session '
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-19-2005 12:28 AM
тАО05-19-2005 12:28 AM
Re: ORA-01578 : Best solution?
I did what u have suggested but of course the database is open so as soon as I kill a process, another takes over.
Do I need to take the database to a state other than OPEN and if so what's the best state and how to achive this.
thanks
kyris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-19-2005 01:45 AM
тАО05-19-2005 01:45 AM
Re: ORA-01578 : Best solution?
Just shutdown the database and restart it in restrict mode:
SQL/SVRMGR> connect / as sysdba;
...> shutdown immediate;
...> startup restrict;
...> drop index...
Regards,
Eric Antunes