DBVERIFY Problems after Upgrade to Oracle 11.2.0.1 - SAP Community

Hello,

we have done an oracle upgrade from 10.2.0.4 to 11.2.0.1 (under Windows 2003)

After the upgrade the DBVERIFY failed with with over 1000 errors and lines like this:

Page 2617 failed with check code 6054

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

Page 2618 failed with check code 6054

csc(0x0000.006a73ec) higher than block scn(0x0000.00000001)

Page 2619 failed with check code 6054

csc(0x0000.006a73ec) higher than block scn(0x0000.00000001)

Page 2620 failed with check code 6054

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

Page 2622 failed with check code 6054

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

Page 2623 failed with check code 6054

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

Page 2624 failed with check code 6054

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

the dbverify file is over 200 MB !

Some ideas??

Gruß

Toni

SAP Managed Tags:
Oracle Database,
SQL

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

Answers (2)

Hi Toni,

this has nothing to do with the BRTOOLS version you use.

The problem here rather sounds like a problem I saw related to data files that had been created by a rather old Oracle version and some 'optimizations' in dbv.

Please run

select name from v$datafile where block1_offset <> block_size;

and report if you get any file names back.

If so, you may check whether re-creating those files via RMAN solves the issue.

BTW: BEFORE doing anything like this, you should definitively check whether these 'corruptions' are also reported by RMAN.

In general you should use the RMAN check anyhow.

regards,

Lars

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello Lars,

when i try to run

select name from v$datafile where block1_offset block_size;

i get the followig error message:

ERROR at line 1:

ORA-00920: invalid relational operator

Gruß

Toni


> Hello Lars,
> 
> when i try to run 
> 
> select name from v$datafile where block1_offset block_size;
> 
> 
> i get the followig error message:
> 
> 
> ERROR at line 1:
> ORA-00920: invalid relational operator 
> 
> Gruß
> Toni

Hi again,

oops - sorry! I always forget that the forum eats up the larger-then and smaller-then signs...

This one should work:


select name from v$datafile where block1_offset != block_size;

regards,

Lars

Hello Lars,

the sql script

select name from v$datafile where block1_offset != block_size;

give this info:

no rows selected

Gruß

Toni

Sorry for jumping in, but now i am curious. Shouldn't we be using 8192 which is the value of the blocksize parameter here?

select name from v$datafile where block1_offset != 8192;

@Toni: or could you just check if all values in block1_offset are 8192?

Kind regards, Michael

Hi,

select name from v$datafile where block1_offset != 8192;

---> same result : no rows selected

Gruß

Toni

>


> Sorry for jumping in, but now i am curious. Shouldn't we be using 8192 which is the value of the blocksize parameter here?
> 
> select name from v$datafile where block1_offset != 8192;
> 
> @Toni: or could you just check if all values in block1_offset are 8192?
> 
> Kind regards, Michael

HI there.

The problem that I remembered occurred due to a file header that had a different block size then the data file.

Since multiple block sizes are possible with Oracle databases, the blocksize parameter only determines the default block size and thus wouldn't be the right thing to use here.

regards,

Lars

Hmm.. ok.

Next steps are:

- run the datafile verification with RMAN (bbrbackup -u / -m all -w only_rmv -e <parallel degree> -c)

- open a support message

regards,

Lars

.......

Hello Lars,

sorry for delay, but database is 1.2 TB

Hmm.....

when i run the datafile verification with RMAN (brbackup -u / -m all -w only_rmv -e 4 -c)

---> all is ok, BRBACKUP completed successfully without errors !! ??

Gruß

Toni


> Hmm.....
> when i run the datafile verification with RMAN (brbackup -u / -m all -w only_rmv -e 4 -c)
> 
> ---> all is ok, BRBACKUP completed successfully without errors !! ??
> Gruß
> Toni

Ok, since DBV is the old legacy-like tool, I'd say: this really looks like a DBV bug.

If I was in your place I'd open a support message for this.

Also you can try to re-write all blocks in the data files with the current block format (which I assume would circumvent the DBV bug).

To do that, you'll use the CONVERT command of RMAN.

The commands for this could look like this:


rman target / nocatalog
startup mount
CONVERT DATAFILE
   '<full data file name incl. path>'
  ,'< next data file>', ...
DB_FILE_NAME_CONVERT
   '<full data file name incl. path>','<full data file name incl. path>_'
   ,'< next data file>', '< next data file>_' 
    ...
CONVERT DATAFILE
   '<full data file name incl. path>_',
DB_FILE_NAME_CONVERT
   '<full data file name incl. path>_','<full data file name incl. path>'
   ,'< next data file>_', '< next data file>'
   ,...

Basically this means: read all blocks from _ (the _ is just there to avoid overwriting the original data).

The next step is actually the same, but now the original files are overwritten.

Of course you should try this out only after you've done a complete backup!

regards,

Lars

Hello lars,

did i understand you right.

You mean also that this is a bug in the start of DBV via DB13 ?

We have opened a sap message yesterday for this problem but we got no answer since.

I will send some infos again to sap. I think we will wait, because we want to do some more upgrades to 11.2.0.1 under windows 2003 in the next days and weeks.

Gruß

Toni


> Hello lars,
> 
> did i understand you right.
> You mean also that this is a bug in the start of DBV via DB13 ?
> 
> We have opened a sap message yesterday for this problem but we got no answer since.
> 
> I will send some infos again to sap. I think we will wait, because we want to do some more upgrades to 11.2.0.1 under windows 2003 in the next days and weeks.
> 
> Gruß
> Toni

No you got me wrong.

There's a bug in DBV! Regardless how you start DBV you'll always end up with these false alarms.

BTW: 11.2.0.2 for Windows is likely to come in the very near future. I would wait for it.

regards,

Lars

Hello Lars,

i am a little bit confused.

When we start the DBV via Db13 (brbackup -u / -jid VERIF20110214092053 -c force -w only_dbv -p initw25.sap_dbv -m ALL)we got errors.

When we start the DBV via console (brbackup -u / -m all -w only_rmv -e 4 -c) all is successful.

---> so it is a bug in DBV, right?

Gruß

Toni

Hello Lars,
i am a little bit confused.
When we start the DBV via Db13 (brbackup -u / -jid VERIF20110214092053 -c force -w only_dbv -p initw25.sap_dbv -m ALL)we got errors.
When we start the DBV via console (brbackup -u / -m all -w only_rmv -e 4 -c) all is successful.
---> so it is a bug in DBV, right?

It's a bug in DBV, correct.

But you've got one thing wrong here:

When we start the DBV via console (brbackup -u / -m all -w only_rmv -e 4 -c) all is successful.

with the -w only_rmv option you DON'T start DBV, but RMAN !!

In general I'd recommend to go with RMAN because it's much faster, it put far less pressure to the system and it's findings are way easier to analyze.

Just as I wrote in note 23345...

regards,

Lars

Hello ,

thanks a lot!

I will add some more infos to the sap message and hope to get answer from sap.

Gruß

Toni

Ok, I got interested in this, so I did some research.

Apparently, these messages come up with DBV, because Oracle added a new check

Bug 7517208 - DBV enhanced to identify Logical SCN Block corruptions

into 11.2.0.1 and 10.2.0.5 but didn't add this check to RMAN yet

Bug 9669572: FIX 7517208 NEEDS TO BE ADDED INTO RMAN

The description of the new checks says


DBVerify has been enhanced to identify more logical block corruptions
in the transaction layer like SCN logical corruptions and Transaction list count:
 
a - "bad num_itls: <itlCount>" 
b - "csc(aaaa.bbbb) higher than block scn(xxxx.yyyy)
c - "itl[<itl_id>] has higher commit scn(aaaa.bbbb) than block scn (xxxx.yyyy)"
d - "Control <csc> greater than block <block scn>"
e - "Slot[<slot>] greater than block <block scn> "

but doesn't mention how to react to any of those messages and what could have caused them.

If I would have a test system with your issue present, I'd at least try out whether the RMAN CONVERT would fix this (I assume it should, but cannot test this...).

Another way of fixing those kinds of issues might be to reorganize the affected segments/tablespaces.

regards,

Lars

vvvvv--~~-careful taking off to the land of guessing, assuming and oracle internals -~~

vvvvvv---

So in this particular case we have:

csc(0x0000.006a73ed) higher than block scn(0x0000.00000001)

According to some [oak table guy's blog|http://richardfoote.wordpress.com/2010/07/28/index-block-dump-block-header-part-ii-and-read-consistency-i-cant-read/] the csc is the initial scn of the block (probably when the block was created).

So dbv is complaing that the initial block scn is higher than its scn. As we know scn's are oracle's internal timestamps, a transaction is assigned a particular scn. The weird thing is that all those reported scn's seem to be 1. Technically this would mean the block was created at "time" 6a73ed, but changed at 1. And scn 1 is the very start of the database...

And the scn number is never reset, it only increases. So after the input from Lars and if dbv is right this could be something else too like a real corruption, or an oracle bug.

-

^^^^^--~~^{- guessing ends here...}~~---^^^^-----

It would be nice if you could update the thread with the findings from support.

Cheers Michael

Hello,

in the meanwhile we have answer from SAp to our message:

Hello Antonio,

This is a bug with 11g, we have seen several Customer's reporting it.

We don't have an official fix for this issue yet, therefore it is important to still check as you did for any inconsistencies. To get

rid of the above inconsistency report you will need to reorganize with Brspace the tablespace to a newly created one.

If you get insconsistencies reported for the SYSTEM tablespace, you can the brspace's option "dbcreate" to recreate system tablespace. please refer to attached oss note #748434.

The RMAN check should be enough to validate that there isn't any issue present. If you still feel that you need further confirmationI would recommend running a consistency check as per oss note #23345 with the ANALYZE option.

Gruß

Toni

As a brief update on this.

I just mailed Oracle Dev-Support on this, and there's a sap note in preparation for this topic.

So maybe you wait a little before starting a reorganisation....

regards,

Lars

Hello all,

we did also run a complete Analysis run with brconnect the last days.

"brconnect -u / -c -f stats -v cascade -t all -e null -p 4"

the time for this run was round about 4 days!!

We got this error after the run:

BR1309E Validation of structure failed for 64/0/0 tables/indexes/clusters

We checked the log and found entries like this:

BR0301E SQL error -14508 in thread 4 at location stats_tab_validate-2, SQL statement:

'ANALYZE TABLE "SYS"."STREAMS$_APPLY_SPILL_MSGS_PART" VALIDATE STRUCTURE CASCADE ONLINE'

ORA-14508: specified VALIDATE INTO table not found

BR0893E Validating structure failed for table/cluster SYS.STREAMS$_APPLY_SPILL_MSGS_PART

BR0301E SQL error -14508 in thread 4 at location stats_tab_validate-2, SQL statement:

'ANALYZE TABLE "SYS"."WRH$_ACTIVE_SESSION_HISTORY" VALIDATE STRUCTURE CASCADE ONLINE'

ORA-14508: specified VALIDATE INTO table not found

BR0893E Validating structure failed for table/cluster SYS.WRH$_ACTIVE_SESSION_HISTORY

Gruß

Toni

These kind of errors happen, when the table INVALID_ROWS was not created. It is needed to verify partitioned tables. Check note [514178 - ORA-14508 when validating partitioned tables|https://service.sap.com/sap/support/notes/514178]

Kind regards, Michael

Hello Michael,

when i want to create the table, i get this error:

SQL> @utlvalid.sql

create table INVALID_ROWS (

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

Gruß

Toni

This means that the this user already has the table INVALID_ROWS. In that case brconnect runs under a different database user (ops$ user).

The note is kind of hard to understand and i don't like the idea of creating the table for user system. I always create the table for the user you run the verification. If you run it from DB13, then ops$sidadm has to have the table. If you start the brconnect directly as orasid, then ops$orasid has to have the table.

The DB13 example:

sidadm> sqlplus /

SQL> show user
USER is "OPS$SIDADM"

SQL> @utlvalid.sql

SQL> select owner, table_name from dba_tables where table_name = 'INVALID_ROWS';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
OPS$SIDADM                     INVALID_ROWS

Then start the verifiy from DB13.

Cheers Michael

Hello Michael,

it is a Windows System. We logon with sidadm user and run the complete Analysis run with brconnect from the "DOS Prompt"

("brconnect -u / -c -f stats -v cascade -t all -e null -p 4" ) --> the time for this run was round about 4 days!!

For the invalid rows we used:

sqlplus "/as sysdba"

@utlvalid.sql

Gruß

Toni

Then you created the table for database user SYS, but you are running brconnect with the database user OPS$SIDADM. Either create a synonym and grant permission as described in the note, or create the table for OPS$SIDADM like posted earlier.

Kind regards, Michael

Hi All,

here are some news from SAP

A note will be released by Development soon regarding the above change to dbverify. I don't have an exact release date at present.

I would advise as a workaround for now to switch to RMAN validation The attached oss note #1560512 provides steps to configure RMAN validation in DB13

Gruß

Toni

HI all,

some news from SAP again:

From 10.2.0.5 above dbverify was enhanced to check transaction area of a datablock, but if database instance is open, this part of the block is frequently changing. dbv will report that messages even you will runn it directly.

RMAN do not have (yet) this check feature. RMAN do not use dbv to perform block check but its own routines.

See also SAP note 1566406.

Infos from SAP note 1566406.

dbv report errors related to transaction layer.

Following messages can be reported by dbveryfy:

"bad num_itls: <itlCount>"

"csc(aaaa.bbbb) higher than block scn(xxxx.yyyy)

"itl[<itl_id>] has higher commit scn(aaaa.bbbb) than block scn (xxxx.yyyy)"

"Control <csc> greater than block <block scn>"

"Slot[<slot>] greater than block <block scn> "

Page <pageno> failed with check code <CheckCode>

CheckCode can be:

6051 - bad block scn

6052 - bad objd

6053 - bad segment id

6054 - bad csc

6055 - bad free list link

6056 - bad itl scn

6057 - bad # itls

Reason and Prerequisites

Starting with Oracle 10.2.0.5, DBVerify has been enhanced to identify more logical block corruptions in the transaction layer like SCN logical corruptions and Transaction list count.

Solution

As ITL layer holds the open transactions for the block and the list entries are changing permanently it is highly probably that dbverify to report above messages/check codes if database instance is open.

The blocks does not have any data corruptions, so data is still readable.

Run dbv when database instance is closed or use another checking method (eg RMAN validate).

Cheers

Toni

Check the versions, try to just call dbv as orasid:

orasid> dbv

DBVERIFY: Release 11.2.0.1.0 - Production on Mon Feb 14 13:05:30 2011

And compare with the actual version of the database:

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.1.0

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
compatible                           string      11.2.0

SQL> select comp_id, version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ ------------------
CATALOG                        11.2.0.1.0                     VALID
CATPROC                        11.2.0.1.0                     VALID

Cheers Michael

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello Michael,

here are the results of your questions

Check the versions, try to just call dbv as orasid:

orasid> dbv

DBVERIFY: Release 11.2.0.1.0 - Production on Mon Feb 14 08:18:04 2011

SQL> select version from v$instance;

VERSION

-

-

11.2.0.1.0

SQL> show parameter compatible

NAME TYPE VALUE

-

-

-

-

-

-

compatible string 11.2.0

SQL> select comp_id, version, status from dba_registry;

COMP_ID VERSION STATUS

-

-

-

-

-

-

EM 11.2.0.1.0 VALID

CATALOG 11.2.0.1.0 VALID

CATPROC 11.2.0.1.0 VALID

Gruß

Toni

Hi,

an additional info.

Before we get the first error messages in dbverify, there is this message:

BR0283W Command output buffer overflow

Gruß

Toni

What is your brtools version?

Do you have the errors too, when you start dbv on a single file directly (adapt the path to windows :-)?

 orasid> dbv file=/oracle/SID/sapdata1/system_1/system.data1

Cheers Michael

Hi,

we use brtools 7.10 (37)

Gruß

Toni

try with brtools 7.20 with latest patch level.

Hi Michael,

What is your brtools version? ---> 7.10 (37)

Do you have the errors too, when you start dbv on a single file directly (adapt the path to windows :-)?

orasid> dbv file=/oracle/SID/sapdata1/system_1/system.data1

-

-

yes the same error!!

Gruß

Toni

Ask a Question

Top Q&A Solution Author

User

Count

83

10

10

9

7

6

5

5

4

4