Home » Server Options » Data Guard » Datafile Fractured (oracle 9i windows)
Datafile Fractured [message #622298] Mon, 25 August 2014 00:44 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

I am in trouble, kindly help me out.

One of my DB's 2 Datafile are fractured (one Table and 2 Indexes).

Having no backups, and the DB version is 9i.

But having a standby DB.

Kindly help me out.

Is is possible that opening the Standby DB in read only mode then convert them as standby DB without recreating the standby DB?

Thanks
Muktha
Re: Datafile Fractured [message #622303 is a reply to message #622298] Mon, 25 August 2014 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is is possible that opening the Standby DB in read only mode then convert them as standby DB without recreating the standby DB?


Your sentence is not clear.
"Is is possible that opening the Standby DB in read only mode then convert them as standby DB "
What is "them"?
what do you mean convert a standby as a standby?

"without recreating the standby DB?"
Why do you want to recreate something that already exists?

I assume the "fractured file" (whatever this means) is on the primary.
You can open the standby as read-only to work with, take a backup of it and rebuild the primary with this backup.
But the main purpose of a standby is to serve as a primary in case of this later one is down.
So switchover/failover to the standby and make it the primary when you rebuild the (previous) primary using a backup you will take from the standby. Then switchover back.


Re: Datafile Fractured [message #622339 is a reply to message #622303] Mon, 25 August 2014 05:20 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Michel,

My question was.

If we open the standby DB as read only, it will not be any more a Standby, right? So at the point can we make that server back as standby without any cold backup?
If is is possible, then I can open the standby in read only then take a export backup of that particular table and import them in primary Db at a seperate Tablespace and then drop the corrupted Table.

In your post, that saying to down the server for cold backup to create the primary. which is tough as it is a crucial db can down.


Also is it advisable for making a snapshot standby from physical standby in oracle 9i version? it is licensed?

Thanks and Regards
Muktha
Re: Datafile Fractured [message #622340 is a reply to message #622339] Mon, 25 August 2014 05:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Is your problem with a file or a table? In your first post,
Quote:
2 Datafile are fractured
and in your second
Quote:
corrupted Table


What is "fractured"?
Re: Datafile Fractured [message #622341 is a reply to message #622339] Mon, 25 August 2014 05:26 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi,

And one more thing,

I can select the rows from that fractured table.
But can make any duplicate table through CTAS format.


So is there any possible way to create a duplicate table excluding that particular corrupted rows?

Muktha
Re: Datafile Fractured [message #622343 is a reply to message #622341] Mon, 25 August 2014 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to first CLEARLY specify what you have.
So restart from the beginning.
What is wrong?
Don't just say it, post evidences, copy and paste what you have done to diagnose, show us.

Re: Datafile Fractured [message #622405 is a reply to message #622343] Tue, 26 August 2014 02:57 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,


1) Actually, Datablock (Of course Datafile) and Tables / indexes inside that Datablock was fractured.

2) We dont have any RMAN Backup. But having Archive log files (which is the purpose for replicating in physical standby DB).

3) When I count the corrupted table or select all the rows from that table, there is no issue. But if I create a another table based on this with the method of CTAS, not allowing.

Thanks and Regards
Muktha
Re: Datafile Fractured [message #622406 is a reply to message #622405] Tue, 26 August 2014 03:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is like trying to get blood out of a stone! This "fractured". How did you diagnose it? For example, did you run a query? Or did you use the dbv utility??
"Not allowing" is not an Oracle error message. What did you do? What was the result?

--update: typos.

[Updated on: Tue, 26 August 2014 03:05]

Report message to a moderator

Re: Datafile Fractured [message #622424 is a reply to message #622406] Tue, 26 August 2014 05:14 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi John,

I used the query "v$database_block_corruption" to find out the Datablock corruption.

And used the below query for finding object level corruption.
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;


Not Allowing:
Which means, it clearly showing the error of datablock and its number, hence I couldn't.


Thanks and Regards
Muktha
Re: Datafile Fractured [message #622426 is a reply to message #622424] Tue, 26 August 2014 05:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What was the result of running that query?

And as for this,
Quote:
Not Allowing:
Which means, it clearly showing the error of datablock and its number, hence I couldn't.
It may be showing clearly to you, you might want to show it here as well.

I am still trying to squeeze the blood out of the stone. But I shall give up soon.
Re: Datafile Fractured [message #622428 is a reply to message #622426] Tue, 26 August 2014 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I shall give up soon.


+1

[Updated on: Tue, 26 August 2014 05:25]

Report message to a moderator

Re: Datafile Fractured [message #622461 is a reply to message #622405] Tue, 26 August 2014 12:59 Go to previous messageGo to next message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
Let us see if we can give you a push in the right direction here.

It would be helpful us to know how you detected the corruption. If you would give us the command you executed, the results showing the corruption, and the matching words in the alert log it would be of great information for us to have to help you.

Can you put the standby database in read-only mode and retrieve the information? Well that is going to depend on if this is a physical corruption or a logical corruption. If it is a logical corruption then the corruption is probably going to be on both they primary database and the standby database. If it is a physical corruption then yes it probably would work...but I don't have an 9i database to test it out on. I would hope that you have a test environment that you could do the test in. The standby database can be used in READ-ONLY mode and then back again as a standby database. That is a good aspect of a standby database.

Why don't you have a backup? ...I think this was a mistake that should be fixed in the future.

Quote:
3) When I count the corrupted table or select all the rows from that table, there is no issue. But if I create a another table based on this with the method of CTAS, not allowing.


I would love to see the results of both of those attempts.

Quote:
Also is it advisable for making a snapshot standby from physical standby in oracle 9i version? it is licensed?

It is not available in 9i

If you are licensed then you can check out support.oracle.com for information on handling block corruptions.

FAQ: Physical Corruption (Doc ID 403747.1)
Note:33405.1 Extracting Data from a Corrupt Table using SKIP_CORRUPT_BLOCKS or Event 10231
Note:28814.1 Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Physical and Logical Block Corruptions. All you wanted to know about it. (Doc ID 840978.1) with lots of other reference articles at the end of it.

Re: Datafile Fractured [message #622869 is a reply to message #622461] Mon, 01 September 2014 02:35 Go to previous message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

Sorry again.

1) The problem is solved by, taking a full cold backup of the standby DB and open that in read write mode.
2) Importing them into the primary DB (After dropping the corrupted objects).
3) Then replace the cold backup of the standby db and open for normal log replication.

We dont have any test environment, so I cant re execute those steps to show off.

Anyway thanks a lot for support.

Muktha
Previous Topic: Wrong datafiles and logfiles address in standby Controlfile
Next Topic: Switchover_status is not allowed in standby
Goto Forum:
  


Current Time: Thu Mar 28 06:14:42 CDT 2024