FullOffline Backup ->ORA-19566: exceeded limit of 0 corrupt blocks for file

[Former Member]

Dear SAP gurus,

I am getting an error from the DBA Planning Calendar every time the job for "Full Offline backup" is run. And it is always as you can see from the log on the same file "oracle/SHD/sapdata4/sr3_16/sr3.data16".

The oracle error is the following:

ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/SHD/sapdata4/sr3_16/sr3.data16

I found the SAP Note 969192 - RMAN Backup of SYSTEM tablespace terminates with ORA-19566

but it does no apply because this is for the tablespace SYSTEM and not PSAPSR3.

Please find below the log:

BR0051I BRBACKUP 7.00 (46)

BR0055I Start of database backup: begomwsv.ffd 2011-08-17 10.01.37

BR0484I BRBACKUP log file: /oracle/SHD/sapbackup/begomwsv.ffd

BR0477I Oracle pfile /oracle/SHD/102_64/dbs/initSHD.ora created from spfile /oracle/SHD/102_64/dbs/spfileSHD.ora

BR0101I Parameters

Name Value

oracle_sid SHD

oracle_home /oracle/SHD/102_64

oracle_profile /oracle/SHD/102_64/dbs/initSHD.ora

sapdata_home /oracle/SHD

sap_profile /oracle/SHD/102_64/dbs/initSHD.sap

backup_mode FULL

backup_type offline_force

backup_dev_type disk

backup_root_dir /mnt/backup/oracle/SHD

compress no

disk_copy_cmd rman

cpio_disk_flags -pdcu

exec_parallel 0

rman_compress no

system_info shdadm/orashd eccdev01 Linux 2.6.16.60-0.87.1-smp #1 SMP Wed May 11 11:48:12 UTC 2011 x86_64

oracle_info SHD 10.2.0.4.0 8192 17654 1114483454 eccdev01 UTF8 UTF8

sap_info 700 SAPSR3 0002LK0003SHD0011Y01548735220015Maintenance_ORA

make_info linuxx86_64 OCI_102 Jan 29 2010

command_line brbackup -u / -jid FLLOF20110817100136 -c force -t offline_force -m full -p initSHD.sap

BR0116I ARCHIVE LOG LIST before backup for database instance SHD

Parameter Value

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /oracle/SHD/oraarch/SHDarch

Archive format %t_%s_%r.dbf

Oldest online log sequence 17651

Next log sequence to archive 17654

Current log sequence 17654 SCN: 1114483454

Database block size 8192 Thread: 1

Current system change number 1114501246 ResetId: 664011854

BR0118I Tablespaces and data files

...

BR0202I Saving /oracle/SHD/sapdata3/sr3_15/sr3.data15

BR0203I to /mnt/backup/oracle/SHD/begomwsv/sr3.data15 ...

#FILE..... /oracle/SHD/sapdata3/sr3_15/sr3.data15

#SAVED.... /mnt/backup/oracle/SHD/begomwsv/sr3.data15 #1/15

BR0280I BRBACKUP time stamp: 2011-08-17 10.28.42

BR0063I 15 of 48 files processed - 44100.117 of 121180.346 MB done

BR0204I Percentage done: 36.39%, estimated end time: 11:15

BR0001I ******************________________________________

BR0202I Saving /oracle/SHD/sapdata4/sr3_16/sr3.data16

BR0203I to /mnt/backup/oracle/SHD/begomwsv/sr3.data16 ...

BR0278E Command output of 'SHELL=/bin/sh /oracle/SHD/102_64/bin/rman nocatalog':

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 17 10:28:42 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN>

RMAN> connect target *

connected to target database: SHD (DBID=1683093070, not open)

using target database control file instead of recovery catalog

RMAN> *end-of-file*

RMAN>

host command complete

RMAN> 2> 3> 4> 5> 6>

allocated channel: dsk

channel dsk: sid=223 devtype=DISK

executing command: SET NOCFAU

Starting backup at 17-AUG-11

channel dsk: starting datafile copy

input datafile fno=00019 name=/oracle/SHD/sapdata4/sr3_16/sr3.data16

released channel: dsk

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on dsk channel at 08/17/2011 10:30:30

ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/SHD/sapdata4/sr3_16/sr3.data16

RMAN>

Recovery Manager complete.

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.30

BR0279E Return code from 'SHELL=/bin/sh /oracle/SHD/102_64/bin/rman nocatalog': 1

BR0536E RMAN call for database instance SHD failed

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.30

BR0506E Full database backup (level 0) using RMAN failed

BR0222E Copying /oracle/SHD/sapdata4/sr3_16/sr3.data16 to/from /mnt/backup/oracle/SHD/begomwsv failed due to previous errors

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.34

BR0307I Shutting down database instance SHD ...

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.34

BR0308I Shutdown of database instance SHD successful

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.34

BR0304I Starting and opening database instance SHD ...

BR0280I BRBACKUP time stamp: 2011-08-17 10.30.47

BR0305I Start and open of database instance SHD successful

Do you guys have any idea on how to solve this issue??

Thanks in advance, Marc

[former_member213250 · Accepted solution]

Hello Marc

Regarding your issue would you try to analyze the table as per note 23345, to check for any inconsistencies.

If you find the corrupted blocks and all of them are empty blocks so I would say reorganization for this tablespace to see if this can make corruption gone.

Also refer tp SAP Note 365481 cna be helpful

Thanks

BR

Venkat

[Former Member · Accepted solution]

Thanks Venkat,

I have discovered with the database check (checkdb) that the following block is corrupted:

Corrupt block relative dba: 0x04c5d70b (file 19, block 382731)

How can I see if the block is empty or not?

Regards, Marc

[Former Member · Accepted solution]

I executed the following command:

SQL> alter system dump datafile 19 block 382731;

:orashd 78> more shd_ora_10391.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/SHD/102_64

System name: Linux

Node name: <server name>

Release: 2.6.16.60-0.87.1-smp

Version: #1 SMP Wed May 11 11:48:12 UTC 2011

Machine: x86_64

Instance name: SHD

Redo thread mounted by this instance: 1

Oracle process number: 20

Unix process pid: 10391, image:

• 2011-08-18 09:07:47.386

• ACTION NAME:() 2011-08-18 09:07:47.386

• MODULE NAME:(sqlplus@<server name> (TNS V1-V3)) 2011-08-18 09:07:47.386

• SERVICE NAME:(SYS$USERS) 2011-08-18 09:07:47.386

• SESSION ID:(191.2153) 2011-08-18 09:07:47.386

Start dump data blocks tsn: 4 file#: 19 minblk 382731 maxblk 382731

buffer tsn: 4 rdba: 0x00000000 (0/0)

scn: 0x0000.00000000 seq: 0x00 flg: 0x00 tail: 0x00000000

frmt: 0x00 chkval: 0x0000 type: 0x00=unknown

Hex dump of corrupt header 4 = CORRUPT

Dump of memory from 0x0000000006238200 to 0x0000000006238214

006238200 00000000 00000000 00000000 00000000 [................]

006238210 00000000 [....]

Hex dump of block: st=4, typ_found=0

Dump of memory from 0x0000000006238200 to 0x000000000623A200

006238200 00000000 00000000 00000000 00000000 [................]

Repeat 511 times

End dump data blocks tsn: 4 file#: 19 minblk 382731 maxblk 382731

How can I know what is affected from this log?and how to fixed it?

Regards, Marc

[Former Member · Accepted solution]

As suggested by Venkat, follow SAP note 365481.

You already have got file_id and block_id.

Now you may go for the object name.

hope this helps

[former_member213250 · Accepted solution]

Hello Marc.

In the mentioned SAP note there are couple of sql statement, by executing with the known file and block number you will be able to find which object does this corruption belongs to.

You need to perform few more steps to eliminate the corruption.

If it is on empty block happy days easy to eliminate or can ignore it .

On the other hand if the corruption is on any data block there are few scenarios mentioned in note and you need to check step by step of the process to eliminate this.

Hope it helps.

Thanks

BR

Venkat

[volker_borowski2]

Hi,

I am getting an error from the DBA Planning Calendar every time the job ...

So when was your last successfull backup of this datafile. Check if still available.

If this is some time ago, and may be you are currently without any backup, try to backup without rman at once,

to have at least something to work with in case you get additional errors right now.

Then you need to find out what object is affected. You are on the right way already. You need the statement,

that goes to dba_extents to check what object the block belongs to.

Has the DB been recovered recently, so the block might possibly belong to an index created with nologging ?

(this could be the case on BW systems).

If the last good backup of that file is still available and the redologs belonging to this backup up to current time are as well, you could try to recover that file. But I'd do this only after a good backup without rman and by not destroying the original file.

If the last good backup was an rman backup, you can do a verify restore of that datafile in advance, to check if the corruption is really not inside the file to be restored.

Check out the -w (verify) option of brrestore first, to understand how it works.

(I am not sure it this is already available in version 7.00, may be you need to switch to 7.10 or 7.20)

brrestore -c -m /oracle/SHD/sapdata4/sr3_16/sr3.data16 -b xxxxxxxx.ffr -w only_rmv

You should do a dbv check of that file as well, to check if it gets more information. I.E if more blocks are

affected. rman stops right after the first corruption, but usually you have a couple of those in line, esp. if these are

zeroed ones. (This one would also work with version 7.00 brtools)

brbackup -c -u / -t online -m /oracle/SHD/sapdata4/sr3_16/sr3.data16 -w only_dbv

Good luck.

Volker