Oracle Data Block Recovery Workshop

Block level corruption of an Oracle database is extremely rare. In the last 30 plus years I have needed to deal with this issue a handful of occasions and its resolution has become significantly easier since I started to use RMAN. Never the less it leaves you with a sinking feeling as it can mean data loss, embarrassment and most importantly the trust of those you work for and with.

The circumstances leading to this exercise started while I was over seas working and started to receive errors on archive only RMAN backups. They were failing and stated very clearly this was the result of block corruption.

My order of resolution was simple. I returned to Australia and sat down the weekend after arriving home to find my RMAN backups offered no joy in correcting this issue. I will however cover this recovery option during the workshops, as it is by far the simplest recovery option, but clearly not the only option as we recovered the system without issue. The objects corrupted in the system on this occasion were all indexes and materialized views. These are easily recovered, but this is not always the case.

1 Overview of this workshop

This workshop will start by creating a new tablespace and a series of objects that will be held within this tablespace and the datafiles underpin it. You will identify a number of objects and intentionally overwrite the headers of the data blocks holding these objects, thus corrupting them and rendering them unusable.

The environment will be a simple Linux 6.3 VM provisioned via Oracle Virtual Box and Oracle 12.1.0.1 database.

You will identify the corruption, the objects affected by the corruption and finally resolve the issue using a variety of techniques starting with RMAN and concluding with the moving of the object to place the corrupted block within the free block pool and finally reformatting these blocks and returning them for normal use.

Sounds simple doesn’t it?

Well, not when you are faced with the challenge and you have never dealt with it before.

The first and most important piece of advise on this subject is when it happens to you, raise an SR immediately and work with Oracle support to get you over this problem, advice I received the first time I faced this problem from Peter Corrigan who was looking over my shoulder and muttering these words of wisdom.

So on with the workshop.

2 Workshop Exercise Environment Assumptions

The workshop notes and exercises assume you have an environment similar to the one I used to assemble these notes and both corrupt and then reinstate a series of database data blocks underpinning this database.

The environment used was built on an Oracle Virtual Box installation with Oracle 6.3 x86-64bit Linux and a Oracle’s 12c Enterprise Edition database. It is assumed that a Container Database exists (CDB1) and one or many pluggable databases have been associated with it.

3 Exercise 1: Creating the Tablespace, Objects and Initial Backup Requirement Steps

Before starting to corrupt blocks take both a level zero (0) and Level 1 (Incremental) backups of the database. These backups will be used during the exercises to reinstate the database.

The database is assumed to be in archivelog mode. If the database you are using is not, shut it down and start it in mount mode and change its operation mode to archivelog mode. Use the following commends to complete this precondition.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 6 13:40:27 2014

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 254738432 bytes

Fixed Size 2287088 bytes

Variable Size 197134864 bytes

Database Buffers 50331648 bytes

Redo Buffers 4984832 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Verify the database is in archivelog by verifying the archiver is started.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 6 14:25:55 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release

12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select instance_name, version, status, archiver, instance_role

from v$instance

SQL> /

INSTANCE_NAME VERSION STATUS ARCHIVE INSTANCE_ROLE

---------------- -------------- --------- ------- --------------

CDB1 12.1.0.1.0 OPEN STARTED PRIMARY_INSTANCE

Now the database is in archivelog mode the new tablspace and objects can be created and the database backed up. During the exercises a large volume of archivelog activity will be undertaken, so it is assume ample space exists for archivelog generation.

3.1 Create the new tablespace and objects.

To isolate the exercises and the activity , create a new tablespace, table and index. Once these have been created run a script to populate the table with data and the index with leaf nodes.

create tablespace corrupt

datafile '/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf'size 2048M

SQL> /

Tablespace created

Verify the tablespaces exists under the container database.

SQL> select name from v$tablespace;

NAME

UNDOTBS1

USERS

TEMP

SYSTEM

SYSAUX

TEMP

MARTIN

CORRUPT

8 rows selected.

Create the table (test_dummy) and index (index_dummy) under the system schema.

create table system.test_dummy

(

rownumber number,

dummy_text varchar2(50)

)

tablespace corrupt

/

Table created.

create index system.index_dummy on system.test_dummy(rownumber)

tablespace corrupt

/

Index created.

Populate the table with some data.

declare

i number :=0;

begin

for i in 1 .. 10000 loop

insert /*+ APPEND */ into system.test_dummy select i, 'test data' from dual;

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

This completes the tablespace and object creation. Now move to backing up the database for recovery with RMAN.

3.2 RMAN backup and recovery

Connect to RMAN and execute a full level zero backup uncluding archivelogs

[oracle@localhost ~]$ rman target /

RMAN> run

2> {

3> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

4> }

Starting backup at 28-JUN-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=60 RECID=12 STAMP=851422739

channel ORA_DISK_1: starting piece 1 at 28-JUN-14

channel ORA_DISK_1: finished piece 1 at 28-JUN-14 piece handle=/u01/database/fast_recovery_area/CDB1/backupset/2014_06_28/o1_mf_annnn_TAG20140628T101859_9tw2hmhx_.bkp tag=TAG20140628T101859 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-JUN-14

Starting backup at 28-JUN-14 using

channel ORA_DISK_1

ETC………

Once this full backup is complete, take an archivelog only backup. Initially however, issue a number of log switches to create redo and subsequent archivelogs.

Issues logfile swithes and then a level 1 backup

RMAN> sql alter system switch logfile;

Statement processed

RMAN> sql alter system switch logfile;

Statement processed

RMAN> run

2> {

3> backup archivelog all delete input;

4> }

Starting backup at 28-JUN-14

current log archived using

channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=63 RECID=15 STAMP=851424075

input archived log thread=1 sequence=64 RECID=16 STAMP=851424090

input archived log thread=1 sequence=65 RECID=17 STAMP=851424116

channel ORA_DISK_1: starting piece 1 at 28-JUN-14

ETC……

Verify the backups by listing the backup history.

Whilst still in RMAN execute the following to verify the existence of valid backups

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

1 B A X DISK 27-JUN-14 1 1 NO TAG20140627T151927

2 B F X DISK 27-JUN-14 1 1 NO TAG20140627T151929

3 B F A DISK 27-JUN-14 1 1 NO TAG20140627T151929

4 B F A DISK 27-JUN-14 1 1 NO TAG20140627T151929

5 B F A DISK 27-JUN-14 1 1 NO TAG20140627T151929

7 B A X DISK 27-JUN-14 1 1 NO TAG20140627T152526

8 B A X DISK 27-JUN-14 1 1 NO TAG20140627T215153

9 B A X DISK 28-JUN-14 1 1 NO TAG20140628T094646

10 B F X DISK 28-JUN-14 1 1 NO TAG20140628T094648

11 B F A DISK 28-JUN-14 1 1 NO TAG20140628T094648

12 B F A DISK 28-JUN-14 1 1 NO TAG20140628T094648

13 B F A DISK 28-JUN-14 1 1 NO TAG20140628T094648

14 B A X DISK 28-JUN-14 1 1 YES TAG20140628T094922

15 B F A DISK 28-JUN-14 1 1 YES TAG20140628T094923

16 B A X DISK 28-JUN-14 1 1 NO TAG20140628T100623

17 B A A DISK 28-JUN-14 1 1 YES TAG20140628T101859

18 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

19 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

20 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

21 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

22 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

23 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

24 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

25 B F A DISK 28-JUN-14 1 1 YES TAG20140628T101900

26 B A A DISK 28-JUN-14 1 1 YES TAG20140628T102242

27 B F A DISK 28-JUN-14 1 1 NO TAG20140628T102243

28 B A A DISK 28-JUN-14 1 1 NO TAG20140628T104036

29 B F A DISK 28-JUN-14 1 1 NO TAG20140628T104038

30 B A A DISK 28-JUN-14 1 1 NO TAG20140628T104156

31 B F A DISK 28-JUN-14 1 1 NO TAG20140628T104157

RMAN>

Now that the tablespace, objects and backups are complete, move to identifying the blocks associated with the index and table that you have just created

3.3 Identify blocks to corrupt

Connect to SQL-Plus and execute the following

SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' || to_number(S.HEADER_BLOCK + 1) || ' << EOF',

'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',

'EOF'

FROM DBA_SEGMENTS s, dba_data_files f

WHERE f.tablespace_name = 'CORRUPT'

AND s.owner = 'SYSTEM'

AND SEGMENT_NAME in ('TEST_DUMMY','INDEX_DUMMY')

/

The Output, formatted without headings.

dd of=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf bs=8192 conv=notrunc seek=131 << EOF CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt EOF

dd of=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf bs=8192 conv=notrunc seek=139 << EOF CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt EOF

Two blocks have been identified, one for the index and the other the table.

To calrify which is owned by the index, run the following

SELECT s.segment_name,

s.segment_type,

f.file_name, to_number(S.HEADER_BLOCK + 1)

FROM DBA_SEGMENTS s, dba_data_files f

WHERE f.tablespace_name = 'CORRUPT'

AND s.owner = 'SYSTEM'

AND SEGMENT_NAME in ('TEST_DUMMY','INDEX_DUMMY')

SQL> /

SEGMENT_NA SEGMENT_ FILE_NAME BLOCK

---------- -------- ------------------------------------------- -----

TEST_DUMMY TABLE /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf 131

INDEX_DUMMY INDEX /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf 139

Now run the dd command on the command prompt to corrupt the two blocks, 131 and 139

[oracle@localhost ~]$ dd of=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf bs=8192 conv=notrunc seek=131

<< EOF

CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt

EOF

(Press CTRL d to execute)

0+4 records in

0+4 records out

245 bytes (245 B) copied, 74.8723 s, 0.0 kB/s

[oracle@localhost ~]$ dd of=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf bs=8192 conv=notrunc seek=139

<< EOF

CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt

EOF

(Press CTRL d to execute)

0+3 records in

0+3 records out

123 bytes (123 B) copied, 38.7462 s, 0.0 kB/s

Verify that the blocks are corrupt using the dbv utility.

[oracle@localhost ~]$ dbv file=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf blocksize=8192

DBVERIFY: Release 12.1.0.1.0 - Production on Sun Jul 6 16:03:58 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x08c00083 (file 35, block 131)

Bad header found during dbv:

Data in bad block:

type: 60

format: 4

rdba: 0x430d464f

last change scn: 0x5450.5552524f

seq: 0x20 flg: 0x63

spare1: 0x20 spare2: 0x45 spare3: 0x7572

consistency value in tail: 0x95210602

check value in block header: 0x726f

block checksum disabled

Page 139 is marked corrupt

Corrupt block relative

dba: 0x08c0008b (file 35, block 139)

Bad header found during dbv:

Data in bad block: type: 60

format: 4

rdba: 0x430a464f

last change scn: 0x5450.5552524f

seq: 0x20 flg: 0x63

spare1: 0x20 spare2: 0x45 spare3: 0x7572

consistency value in tail: 0x76f30601

check value in block header: 0x726f

block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined : 262144

Total Pages Processed (Data) : 19999

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 57

Total Pages Failing (Index): 0

Total Pages Processed (Other): 316

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 241770

Total Pages Marked Corrupt : 2

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 3439097 (0.3439097)

Identify the rfile# from the v$datafile view for the data file underpinning the corrupt tablespace where the table and index are located. Note: the number can be confirmed in the dbv output as well.

select rfile#, file#, name

from v$datafile

/

RFILE# FILE# NAME

------ ----- ------------------------------------------------

35 35 /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf

Run RMAN to update the database about the corrupt block details.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jul 6 16:19:59 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDB1 (DBID=795429890)

RMAN> backup validate check logical datafile 35;

Starting backup at 06-JUL-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=254 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=00035 name=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

35 FAILED 0 241770 262144 3439097

File Name: /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 19999

Index 0 57

Other 2 318

validate found one or more corrupt blocks See trace file /u01/database/diag/rdbms/cdb1/CDB1/trace/CDB1_ora_4308.trc for details

Finished backup at 06-JUL-14

Verify the database has been updated with the details of the two corrupted blocks.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 6 16:24:57 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID

---------- ------ --------- ------------------ --------- ----------

35 131 1 0 CORRUPT 0

35 139 1 0 CORRUPT 0

3.4 RMAN Block Corruption Recovery

Use RMAN to recover the corrupted blocks.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jul 6 16:30:30 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.

All rights reserved.

connected to target database: CDB1 (DBID=795429890)

RMAN> recover corruption list;

Starting recover at 06-JUL-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00035

channel ORA_DISK_1: reading from backup piece /u01/database/fast_recovery_area/CDB1/backupset/2014_07_06/o1_mf_nnndf_TAG20140706T144549_9vko3xpd_.bkp

channel ORA_DISK_1: piece handle=/u01/database/fast_recovery_area/CDB1/backupset/2014_07_06/o1_mf_nnndf_TAG20140706T144549_9vko3xpd_.bkp tag=TAG20140706T144549

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:15

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=133

channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=134

channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=135

channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=136

channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=137

channel ORA_DISK_1: reading from backup piece /u01/database/fast_recovery_area/CDB1/backupset/2014_07_06/o1_mf_annnn_TAG20140706T145125_9vkogfpn_.bkp

channel ORA_DISK_1: piece handle=/u01/database/fast_recovery_area/CDB1/backupset/2014_07_06/o1_mf_annnn_TAG20140706T145125_9vkogfpn_.bkp tag=TAG20140706T145125

channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/database/fast_recovery_area/CDB1/archivelog/2014_07_06/o1_mf_1_133_9vkv90tz_.arc RECID=76 STAMP=852222656

channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/database/fast_recovery_area/CDB1/archivelog/2014_07_06/o1_mf_1_134_9vkv90vy_.arc RECID=77 STAMP=852222656

channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/database/fast_recovery_area/CDB1/archivelog/2014_07_06/o1_mf_1_135_9vkv90y3_.arc RECID=79 STAMP=852222656

channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/database/fast_recovery_area/CDB1/archivelog/2014_07_06/o1_mf_1_136_9vkv9101_.arc RECID=80 STAMP=852222657

channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/database/fast_recovery_area/CDB1/archivelog/2014_07_06/o1_mf_1_137_9vkv90x0_.arc RECID=78 STAMP=852222656

media recovery complete, elapsed time: 00:00:03

Finished recover at 06-JUL-14

RMAN>

Verify the blocks have been reinstated from backups and there are no longer any corrupt blocks in the database.

RMAN> validate tablespace corrupt;

Starting validate at 06-JUL-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00035 name=/u01/oradata/CDB1/CDB1/datafile/corrupt.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:07

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

35 OK 0 241770 262144 3439097

File Name: /u01/oradata/CDB1/CDB1/datafile/corrupt.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 20000

Index 0 58

Other 0 316

Finished validate at 06-JUL-14

Summary.

This exercise shows you how to simulate Oracle block corruption, identify the corrupt blocks and the objects affected by the corruption as well as how to use RMAN to recover from the corruption.

This post is a section for a 1/2 day session I run on recovering from block level corruption. The section section of this training shows how to recover when RMAN cannot be used. If you find yourself in this situation, please contact me on martin.power@parishcrest.com.au and I will provide a copy of the entire training exercises notes. Alternatively I or one of my team can fix the problem for you.

Regards

Martin Power