RMAN Block Recovery ,Skip Corrupted Block

 

Create table under tablespace test2 and check the block# for the first few rows.

SQL> create table oratab.objects2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from oratab.objects2) where rownum < 7;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
131
132
133
134
135

SQL> select count(*) from oratab.objects2;

COUNT(*)
———-
18520

No corrupted blocks in the tablespace.

SQL> select * from v$database_block_corruption;

no rows selected

Manually corrupt one of the block containing data.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.0526e-05 s, 162 MB/s

Flush buffer cache as it may have all blocks currently in memory.

SQL> alter system flush buffer_cache;

System altered.

Query the table, it will through error message with the block# of corrupted block.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

The corrupted block details will be reflected in v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 133 1 0 ALL ZERO

Recover block using RMAN

RMAN> blockrecover datafile 7 block 133;

Starting recover at 21-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Corrupt multiple blocks now.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=134 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6443e-05 s, 145 MB/s
$ dd of=/u02/oradata/test201.dbf bs=8192 seek=135 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2078e-05 s, 157 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 134)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

Use RMAN validate to check the corrupted block list.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE TEST2;

Starting validate at 21-APR-16
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=00007 name=/u02/oradata/test201.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 FAILED 0 12410 12800 30691648
File Name: /u02/oradata/test201.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 247
Index 0 0
Other 2 143

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 134 2 0 ALL ZERO
When there are multiple corrupted blocks, use blockrecover corruption list to recover them.

RMAN> blockrecover corruption list;

Starting recover at 21-APR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Skipping Corrupted Block using event ‘10231’  or DBMS_REPAIR if it can’t be recovered.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9682e-05 s, 165 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 131 1 0 ALL ZERO

SQL> ALTER SESSION SET EVENTS
‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’; 2

Session altered.

SQL> create table oratab.objects2_recover tablespace test2 as select * from oratab.objects;

Table created.

SQL> SQL> select count(*) from oratab.objects2_recover;

COUNT(*)
———-
18518 — 2 rows decreased.

Corrupted block can be skipped using DBMS_REPAIR package as well

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME SKIP_CORRUPT
—————————— ——–
OBJECTS2 ENABLED

Skip corrupt flag can be cleared after exporting the table or using CTAS

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,SKIP_CORRUPT from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME                               SKIP_CORRUPT
—————————— ——–
OBJECTS2                                             DISABLED

 

ref:Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 (Doc ID 33405.1)

 

RMAN Convert database from Linux to Windows

Check Endian format for the source and target OS versions:

SQL> select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ————————————————–
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
5 HP Tru64 UNIX Little
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
13 Linux x86 64-bit Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little

12 rows selected.

SQL> select platform_name from v$database;

PLATFORM_NAME
————————————————————-
Linux x86 64-bit

Preparing to Convert database:

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
begin
3 4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and
retry.

PL/SQL procedure successfully completed.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> set serveroutput on
SQL> declare
db_ready boolean;
2 3 begin
/* db_ready is ignored, but with SERVEROUTPUT set to ON any
4 5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /

PL/SQL procedure successfully completed.
Using DBMS_TDB .CHECK_EXTERNAL to Identify External Objects:
SQL> declare
external boolean;
begin
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := dbms_tdb.check_external;
end; 2 3 4 5 6 7 8
9 /
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.EXPDP

PL/SQL procedure successfully completed.

RMAN convert from Source platform:

RMAN> CONVERT DATABASE NEW DATABASE ‘PDMTMP2’
2> transport script ‘/ofaroot/MISC/transportscript’
3> to platform ‘Microsoft Windows IA (32-bit)’
db_file_name_convert ‘/oradata/’ ‘/ofaroot/MISC/’
;4> 5>

Starting conversion at source at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/oradata/users01.dbf
converted datafile=/ofaroot/MISC/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/oradata/system.dbf
converted datafile=/ofaroot/MISC/system.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oradata/sysaux.dbf
converted datafile=/ofaroot/MISC/sysaux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/oradata/undotbs1.dbf
converted datafile=/ofaroot/MISC/undotbs1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Edit init.ora file /apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /ofaroot/MISC/transportscript on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
Transport script output:

[oracle@VM1]$ cat transportscript
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
CREATE CONTROLFILE REUSE SET DATABASE “PDMTMP2” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-778_T-1_A-847714242_03qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-779_T-1_A-847714242_04qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-777_T-1_A-847714242_05qpgua4’ SIZE 100M BLOCKSIZE 512
DATAFILE
‘/ofaroot/MISC/system.dbf’,
‘/ofaroot/MISC/sysaux.dbf’,
‘/ofaroot/MISC/undotbs1.dbf’,
‘/ofaroot/MISC/users01.dbf’
CHARACTER SET AL32UTF8
;

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/apps/oracle/product/11.2.0.3/dbs/data_D-PDMTMP2_I-2935319874_TS-TEMP_FNO-1_06qpgua4′
SIZE 524288000 AUTOEXTEND OFF;
— End of tempfile additions.

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
— The following step will recompile all PL/SQL modules.
— It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
RMAN convert on TARGET Platform:

RMAN> CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT ‘/ofaroot/MISC/convertscript.rman’
2> 3> TRANSPORT SCRIPT ‘/ofaroot/MISC/transportscripttarget.sql’
4> new database ‘PDMTMP1’
5> FORMAT ‘/ofaroot/MISC/%U’;

Starting conversion at source at 23-DEC-15
using channel ORA_DISK_1

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00004 name=/oradata/users01.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00001 name=/oradata/system.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00002 name=/oradata/sysaux.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00003 name=/oradata/undotbs1.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
Edit init.ora file /ofaroot/MISC/init_00qpgv1f_1_0.ora. This PFILE will be used to create the database on the target platform
Run RMAN script /ofaroot/MISC/convertscript.rman on target platform to convert datafiles
Run SQL script /ofaroot/MISC/transportscripttarget.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
[oracle@VM1]$ cat convertscript.rman
STARTUP NOMOUNT PFILE = ‘/ofaroot/MISC/init_00qpgv1f_1_0.ora’;
RUN {
CONVERT
FROM PLATFORM ‘Linux x86 64-bit’
PARALLELISM 1
DATAFILE ‘/oradata/users01.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-USERS_FNO-4_05qpgv1f’
DATAFILE ‘/oradata/system.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSTEM_FNO-1_06qpgv1f’
DATAFILE ‘/oradata/sysaux.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSAUX_FNO-2_07qpgv1f’
DATAFILE ‘/oradata/undotbs1.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-UNDOTBS1_FNO-3_08qpgv1f’
; }

Ref: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm#CHDEICED