I posted a question about this and didn’t find an answer. I searched both Oracle’s support site and the internet in general. I ran the DBVERIFY utility dbv against a bigfile datafile that had corrupt blocks and wanted to relate the blocks back to a table using the DBA_EXTENTS view. For smallfile datafiles I could have used dbms_utility.data_block_address_block. But the manual says that it does not work with bigfiles. I did a small test and found that with bigfiles the address output by dbv is just the block_id within the data file. With a smallfile tablespace it was some combination of block_id and file_id. Really, it is more helpful for dbv to spit out the block_id if you are running it against a datafile because you already know which datafile you have. I will include some of the output of the test below.
Steps of my test:
- create a small bigfile tablespace
- create empty table nologging
- take a rman backup
- do a nologging update
- delete tablespace/datafile
- restore and recover it
- verify corruption exists
- run dbv to get DBA – block address
- run rman backup validate to get file id and block id
- select from dba_extents to get block locations for table
1 - create a small bigfile tablespace create bigfile tablespace big datafile '/home/oracle/product/oradata/ORCL/big.dbf' size 10M; [oracle@ora19 bigfiletests]$ sqlplus / as sysdba SQL*Plus: Release 188.8.131.52.0 - Production on Wed Mar 3 07:46:15 2021 Version 184.108.40.206.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.0 SQL> create bigfile tablespace big datafile '/home/oracle/product/oradata/ORCL/big.dbf' size 10M; Tablespace created.
This was on a small 19c test database on a Linux VM.
2 - create empty table - maybe ctas and truncate select * from dba_tables; ORCL:SYSTEM>create table bigfiletest tablespace big nologging as select * from dba_tables where 1=2; Table created.
Had to create table with NOLOGGING to make the insert append below unrecoverable.
3 - take a rman backup [oracle@ora19 ORCL]$ rman target / Recovery Manager: Release 22.214.171.124.0 - Production on Wed Mar 3 08:11:29 2021 Version 126.96.36.199.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1534990846) RMAN> backup database; Starting backup at 03-MAR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/home/oracle/product/oradata/ORCL/system01.dbf input datafile file number=00003 name=/home/oracle/product/oradata/ORCL/sysaux01.dbf input datafile file number=00004 name=/home/oracle/product/oradata/ORCL/undotbs01.dbf input datafile file number=00005 name=/home/oracle/product/oradata/ORCL/big.dbf input datafile file number=00007 name=/home/oracle/product/oradata/ORCL/users01.dbf channel ORA_DISK_1: starting piece 1 at 03-MAR-21 channel ORA_DISK_1: finished piece 1 at 03-MAR-21 piece handle=/home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1 tag=TAG20210303T081136 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05 Finished backup at 03-MAR-21 Starting Control File and SPFILE Autobackup at 03-MAR-21 piece handle=/home/oracle/product/db/19.0.0/dbs/c-1534990846-20210303-02 comment=NONE Finished Control File and SPFILE Autobackup at 03-MAR-21
Just a regular backup before doing the unrecoverable insert append into nologging table.
4 - do a nologging update - insert append select * from dba_tables commit ORCL:SYSTEM>insert /*+ append */ into bigfiletest 2 select * from dba_tables where rownum < 2; 1 row created. ORCL:SYSTEM> ORCL:SYSTEM>commit; Commit complete.
Just one row – should be one corrupt block.
5 - delete tablespace/datafile [oracle@ora19 ORCL]$ cd /home/oracle/product/oradata/ORCL [oracle@ora19 ORCL]$ ls -altr total 2813440 drwxr-x---. 3 oracle oinstall 17 Jul 30 2019 .. -rw-r-----. 1 oracle oinstall 209715712 Mar 3 07:50 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Mar 3 07:50 redo03.log -rw-r-----. 1 oracle oinstall 57679872 Mar 3 07:51 temp01.dbf drwxr-x---. 2 oracle oinstall 4096 Mar 3 08:02 . -rw-r-----. 1 oracle oinstall 1142956032 Mar 3 08:11 system01.dbf -rw-r-----. 1 oracle oinstall 692068352 Mar 3 08:11 sysaux01.dbf -rw-r-----. 1 oracle oinstall 356524032 Mar 3 08:11 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Mar 3 08:11 users01.dbf -rw-r-----. 1 oracle oinstall 10493952 Mar 3 08:14 big.dbf -rw-r-----. 1 oracle oinstall 209715712 Mar 3 08:15 redo01.log -rw-r-----. 1 oracle oinstall 10600448 Mar 3 08:15 control01.ctl -rw-r-----. 1 oracle oinstall 10600448 Mar 3 08:15 control02.ctl [oracle@ora19 ORCL]$ rm big.dbf
Now the unrecoverable nologging insert append change is lost. It is not on the backup and not on the redo or archived redo logs.
6 - restore and recover it [oracle@ora19 ORCL]$ rman target / Recovery Manager: Release 188.8.131.52.0 - Production on Wed Mar 3 08:16:07 2021 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1534990846) RMAN> alter tablespace big offline immediate; using target database control file instead of recovery catalog Statement processed RMAN> restore tablespace big; Starting restore at 03-MAR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/product/oradata/ORCL/big.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1 channel ORA_DISK_1: piece handle=/home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1 tag=TAG20210303T081136 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 03-MAR-21 RMAN> recover tablespace big; Starting recover at 03-MAR-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 03-MAR-21 RMAN> alter tablespace big online; Statement processed
Simple tablespace restore and recovery. Had to alter tablespace offline immediate because the file was not there.
7 - verify corruption exists ORCL:SYSTEM>select * from bigfiletest; select * from bigfiletest * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 787) ORA-01110: data file 5: '/home/oracle/product/oradata/ORCL/big.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
This just shows that the block is corrupt. It also gives us the file number (which we already knew) and the block id which would relate back to DBA_EXTENTS.
8 - run dbv to get DBAs - block addresses dbv file=/home/oracle/product/oradata/ORCL/big.dbf blocksize=8192 [oracle@ora19 ORCL]$ dbv file=/home/oracle/product/oradata/ORCL/big.dbf blocksize=8192 DBVERIFY: Release 220.127.116.11.0 - Production on Wed Mar 3 08:21:45 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /home/oracle/product/oradata/ORCL/big.dbf DBV-00201: Block, DBA 787, marked corrupt for invalid redo application
This was kind of the moment of truth. The DBA from the DBVERIFY utility dbv was 787 which is the same as the block number in the error from the select.
RMAN VALIDATE has the same block number – 787.
Seems to be the forth block. The extent starts at block 784 but block 787 is corrupt.
I had a larger test database with many corrupt blocks due to the way we had populated it with an RMAN restore and recover. I knew which table was corrupt because I ran select count(*) queries against every table on the database and only found one corrupt. Using the DBA value from dbv against the DBA_EXTENTS view for over 300 sample corrupt blocks that all pointed back to the table I knew was corrupt. I queried it like this:
SELECT tablespace_name, segment_type, owner, segment_name FROM my_extents WHERE file_id = 29 and 15340893 between block_id AND block_id + blocks - 1;
I created the my_extents table from dba_extents to speed up these queries:
create table my_extents as select * from dba_extents; create index my_extents_i1 on my_extents(block_id); execute dbms_stats.gather_table_stats('MYOWNER','MY_EXTENTS');
Anyway, I do not know if this holds true in every situation, but it appears that the DBA values from dbv for bigfiles correspond to the block_id values in DBA_EXTENTS.