Thinking Out Loud

April 7, 2010

logical corruption

Filed under: oracle — mdinh @ 4:09 am

From what I have been told, when there are data beyond the 2nd arguments in ORA-600, there is possibility for corruption.

I have later found out the above statement is incorrect.

ORA-00600: internal error code, arguments: [kcbgtcr_12], [1], [373070], [1048578]

ORA-08103: object no longer exists

The above errors were raised by SELECT COUNT(*) FROM TABLE.

analyze table <table_name> validate structure cascade
*
ERROR at line 1:
ORA-08103: object no longer exists

After excuting RMAN> backup validate check logical database;

The following errors were in the alert log

Mon Apr  5 12:56:23 2010
Error backing up file 78, block 16026: logical corruption

This can be verified by querying V$DATABASE_BLOCK_CORRUPTION and V$BACKUP_CORRUPTION

OPS$ORACLE@db08:PRIMARY> set serverout on
OPS$ORACLE@db08:PRIMARY> exec print_table(‘select * from v$backup_corruption’);
RECID                         : 1
STAMP                         : 715270236
SET_STAMP                     : 715270042
SET_COUNT                     : 13931
PIECE#                        : 1
FILE#                         : 78
BLOCK#                        : 16026
BLOCKS                        : 1
CORRUPTION_CHANGE#            : 10488120506547
MARKED_CORRUPT                : YES
CORRUPTION_TYPE               : LOGICAL
—————–
RECID                         : 2
STAMP                         : 715528352
SET_STAMP                     : 715528177
SET_COUNT                     : 13986
PIECE#                        : 1
FILE#                         : 78
BLOCK#                        : 16026
BLOCKS                        : 1
CORRUPTION_CHANGE#            : 10488120506547
MARKED_CORRUPT                : YES
CORRUPTION_TYPE               : LOGICAL
—————–

Note: Oracle support asked me to use DBVerify; however, I don’t trust it after reading the article below from some time ago.

http://wedonotuse.blogspot.com/2007/02/dbverify-i-think-not.html

Besides, doesn’t RMAN do the job?

Here are my attempts at resolving the logical corruption.

Dropping the table with the logical corruption did not help. We had this luxury, but you might not.

Oracle support suggested using blockrecover. However, there are limitations, see below.

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta010.htm

I did forget to mention that we don’t use RMAN for backup. Let’s not go there.

Also, from Tom Kyte’s post – http://bit.ly/8Ye15N

in order to perform a block level recovery YOU MUST have used rman and be using rman, otherwise this feature is not available.Reason 4,532,543 to use RMAN as part of your backup and recovery solution.

You can only do a datafile restore, rollforward as an offline operation if you are not using RMAN.

Oracle support did suggest the following – How To Format Corrupted Block Not Part Of Any Segment ORA-19566 Note: 336133.1

If you don’t have access to Metalink, the you can find the document here – http://oracle-abc.wikidot.com/how-to-format-corrupted-block-not-part-of-any-segment

It works!

No more logical corruption.

Verify by executing RMAN> backup validate check logical datafile <FILE#>

However, you would need to querry V$DATABASE_BLOCK_CORRUPTION because V$BACKUP_CORRUPTION will still show the corruptions from previous backup.

It was a learning experience for me and hope it helps you should you ever encounter logical corruption.

About these ads

1 Comment »

  1. [...] 1-How To Format Corrupted Block which is Not Part Of Any Segment? Michael T. Dinh-logical corruption [...]

    Pingback by Blogroll Report 02/04 /2010 – 09/04/2010 « Coskan’s Approach to Oracle — July 19, 2010 @ 1:41 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 272 other followers

%d bloggers like this: