Feeds:
Posts
Comments

Archive for the ‘Block recovery’ Category

Today Hemant Sir have posted a good link that represent some basic for oracle HIGH AVAILABILITY with demonstration.

 

Please click the link…

 

oracle High availability demos…

 

Hope this helps…

Read Full Post »

Some issues which I have faced at past are some of my blocks got corrupted on the pre-prod, I can’t  give you the exact scenario but I will try to make that scenario again.

So, if some of our data block got corrupted, How we can restore them?

Answer is simple Oracle Block Media Recovery.

When some of the data blocks in the data files got physically corrupted and if you don’t have RMAN backups then you will have to restore the full data file from backup to restore and recover those few blocks (like expdp/impdp, cold, hot backup), which can be quite a hectic job. But if you use rman backups then you can take benefit of this very powerful feature called BMR (BLOCK MEDIA RECOVERY). Using Block Media Recovery only those blocks which got corrupted can be recovered from a backup instead of recovering whole file.

The steps to resolve block corruption are simple…..

  • Start SQL*Plus and connect to the target database.
  • Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist. For example, execute the following statement:
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
  • (if exist) Start RMAN and connect to the target database.
  • Recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION.The following command repairs all physically corrupted blocks recorded in the view:
  • RMAN> RECOVER CORRUPTION LIST;

Lets create the scenario.

For this we need to have a test tablespace with a different data-file where we can corrupt our data block. So lets create the tablespace.

Login to sqlplus.

$ sqlplus / as sysdba

create tablespace test datafile ‘/u01/apps/oradata/test/test01.dbf’ size 1G autoextend on next 10M

extent management dictionary segment space management auto

/

Tablespace created.

create user nimai identified by password default tablespace test

quota unlimited on test

/

User created.

grant connect , resource,dba to nimai;

Grant succeeded.

conn nimai/password;

Connected.

create table testnimai as select * from all_objects;

Table created.

SQL> exit

Ok, now we have a tablespace named as test, which has a table named testnimai owned by the user nimai.

Lets take a backup of this tablespace using RMAN.

$ rman target /

RMAN>run

{

allocate channel no1 type disk;

sql ‘alter system switch log’;

backup datafile 5 format /u02/rman_backup/backup/nimai_%d_%s_%p_%t’;

release channel no1;

}

Starting backup at 14-NOV-13

using channel no1

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

comment=NONE

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

Finished backup at 14-NOV-13

Check that your backup piece exists

RMAN> list backup;

RMAN> exit

Recovery Manager complete.

Now we have a backup of data file “/u01/apps/oradata/test/test01.dbf”.

let check out the status of the table.

$ sqlplus / as sysdba

select segment_name,header_file,header_block from dba_segments where segment_name = ‘TESTNIMAI’

and owner = ‘NIMAI’;

SEGMENT_NAME                 HEADER_FILE HEADER_BLOCK

—————————- ———– ————

TESTNIMAI                              5           10

SQL> exit

The header of the table is in block 11, so if we are able to corrupt the next block we can create a scenario for test. Lets corrupt the next block which is 11 using the “dd” command in Linux (Note: Please use the dd command carefully in you environment as its can remove some important blocks from your environment).

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

Ok, now we have executed the command and the block 11 got corrupted as well in the data file “/apps/oradata/test/test01.dbf”.

$ sqlplus / as sysdba

SQL> alter system flush buffer_cache;

System altered.

We need to flush the buffer_cache because if the block 11 is in the buffer_cache then we can’t read that block from the data file.

We will get the block corruption error

  1.  when we will query the v$database_block_corruption.
  2.  in the alert log file.
  3. when we use dbverify (dbv) utility for that file.
  4. when we will try to query the table.

lets check here simply as users are.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

As our scenario is created and now we can proceed further to recover the block 11 of data file 5.

$ rman target /

RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 11;

Starting blockrecover at 14-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00005

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

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

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

starting media recovery

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

Finished blockrecover at 14-NOV-13

RMAN> exit

Recovery Manager complete.

BLOCK MEDIA RECOVERY Complete.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

Lets now corrupt more then one blocks in the file and do a test for Block Media Recovery.

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=12 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=13 << EOF

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

So,why are we not getting the error, remember as I told you earlier we need to flush the buffer_cache because if the block is in the buffer_cache then we can’t read that block from the data file.So, here we have queried the blocks from buffer_cache.

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

Now query again.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

Now say that you have three blocks corrupt in your data file, your automated backup script started in the night somewhere and took a backup of the file marking the blocks as corrupt.  When RMAN finds corrupt blocks in the data file it reports them in v$backup_corruption.

$ rman target /

after getting the corrupted block count from v$backup_corruption we can set in the backup script to not backup those 3 block  (as we have corrupted three blocks e.g. 11,12,13)

We will use set maxcorrupt command in RMAN which will ignore 3 corrupted blocks in the file 5 and mark the blocks as corrupt and mark those 3 blocks in v$backup_corruption.

RMAN> run {

allocate channel no1 type disk;

set maxcorrupt for datafile 5 to 3;

backup datafile 5;

release channel no1;

}

executing command: SET MAX CORRUPT

using target database control file instead of recovery catalog

Starting backup at 14-NOV-13

allocated channel: no1

channel no1: sid=158 devtype=DISK

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005

name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=

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

Finished backup at 14-NOV-13

RMAN> exit

Recovery Manager complete.

The backup is complete now lets query the v$backup_corruption to see our corrupted blocks count.

$ sqlplus / as sysdba

select piece#, file#, block# , blocks , marked_corrupt from v$backup_corruption;

PIECE#      FILE#     BLOCK#     BLOCKS MAR

———- ———- ———- ———- —

1          5         11          3 YES

SQL> exit

It tells us there are three blocks corrupted in data file 5. Now we can simply go to RMAN use the BLOCK RECOVER command to recover all these blocks from the backup we took earlier.

$ rman target /

RMAN> list backup;

RMAN> BLOCKRECOVER CORRUPTION LIST from tag=tag_name;

CORRUPTION LIST means all the blocks reported corrupt in v$backup_corruption.

OR

RMAN> RECOVER CORRUPTION LIST;

RMAN> exit

Recovery Manager complete.

Now lets query again and check that do we have our corrupted data blocks with us in ok condition.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

All blocks are recovered successfully. Always off and on do take rman backup cause you never know when you can face such type of scenario in your prod environment.

Hope it’s help.

Thanks & Regards

Nimai Karmakar

$

Read Full Post »

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog

The Ivica Arsov Blog

Database Management & Performance

gumpx

DBA's online diary