Handling Corrupt Datafile Blocks in RMAN Backup

We have two different kinds of block corruption:

Physical corruption (media corrupt) : Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk.

Logical corruption (soft corrupt) : Logical corrution can among other reasons be caused by an attempt to recover through a NOLOGGING action.

When RMAN encounters a corrupt datafile block during a backup, the behavior depends upon whether RMAN has encountered this corrupt block during a previous backup. If the block is already identified as corrupt, then it is included in the backup. If the block is not previously identified as corrupt, then RMAN's default behavior is to stop the backup. We can override this behavior using the SET MAXCORRUPT command with BACKUP in a RUN block. Setting MAXCORRUPT allows a specified number of previously  undetected block corruptions in datafiles during the execution of an RMAN BACKUP command. Here is the example of set maxcorrupt example.

Syntax  :  set maxcorrupt  for datafile <dataFileSpec>  TO  <integer>

Example :
i.) RMAN>run  {
                             set maxcorrupt for datafile 3,4,5,6  to 1 ; 
                              backup check logical database ; 
                           }

In the above example datafile 3,4,5,6 may not more than 1 corruption datafile block otherwise backup will fail.

ii.)RMAN> run {
                                set maxcorrupt for datafile 1 to 10;
                                backup database;
                                skip inaccessible;
                                skip readonly
                          }

If RMAN detects more than this number of new corrupt blocks while taking the backup, then the backup job aborts, and no backup is created. As RMAN finds corrupt blocks during the backup process, it writes the corrupt blocks to the backup with a special header indicating that the block has media corruption. If the backup completes without exceeding the specified MAXCORRUPT limit, then the database records the address of the corrupt blocks and the type of corruption found (logical or physical) in the control file. We can access these records through the V$DATABASE_BLOCK_CORRUPTION view.

Detecting Physical Block Corruption With RMAN BACKUP : RMAN checks only for physically corrupt blocks with every backup it takes and every image copy it makes. RMAN depends upon database server sessions to perform backups, and the database server can detect many types of physically corrupt blocks during the backup process. Each new corrupt block not previously encountered in a backup is recorded in the control file and in the alert.log. By default, error checking for physical corruption is enabled. At the end of a backup, RMAN stores the corruption information in the recovery catalog and control file.

How to detect block corruption ? 

1.)  DBVERIFY utility   :  DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. we use DBVERIFY primarily when we need to ensure that a backup database (or datafile) is valid before it is restored

2.) Block checking parameters  : There are two initialization parameters for dealing with block corruption :                            

• DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time) causes 1-2% performance overhead .
• DB_BLOCK_CHECKING (server process checks block for internal consistency after every DML) causes 1-10% performance overhead .

For more about db_block_checking parameter click here

3.) ANALYZE TABLE  :  ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL  statement  Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF) . More about Analyze:Report Corruption click here

4.) RMAN BACKUP command with  VALIDATE option   :   We can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

To check for logical corruptions in addition to physical corruptions, run the following variation of the preceding command:

RMAN> BACKUP VALIDATE 
              CHECK LOGICAL  DATABASE 
              ARCHIVELOG ALL;

Detection of Logical Block Corruption :  Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used,the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.

1.) RMAN found any block corruption in database then following Data Dictionary view populated.
V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

2.)  EXPORT/IMPORT command line utility
Full database EXPORT/IMPORT show=y is another method.
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'D:\app\Neerajs\oradata\orcl\USERS01.DBF'

3.) DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.For detail about dbms_repair Package click here .

Enjoy     :-)

Rman Change Command in Oracle

Rman Change Comamnd : Change command update the status of a backup or copy in the RMAN repository.The purpose of the Change command is to change the status of backups, copies, and archived logs in the repository to AVAILABLE or UNAVAILABLE. This feature is useful when a previously unavailable file is made available again, or we do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.

The CHANGE command can alter the repository status of usable backups and copies from prior incarnations.It  removes the catalog records for backups and copies, and update the corresponding records in the target control file to status DELETED. This feature is useful when we remove a file by using an operating system command rather than the RMAN CHANGE command, and want to remove its repository record as well.

Restrictions and Usage Notes :

• The target instance must be started.
• The KEEP FOREVER clause requires use of a recovery catalog.
• we cannot use CHANGE... UNAVAILABLE or KEEP attributes for files stored in the flash recovery area.

The only CHANGE command that requires either a manual or automatic maintenance channel is the CHANGE ... AVAILABLE command. However, a maintenance channel is not required when CHANGE ... AVAILABLE is used with a file that is disk only (that is, an archivelog, datafile copy, or controlfilecopy). Here are the option used with the Change Command :

AVAILABLE  :  Changes the status of a backup or copy to AVAILABLE in the repository. View the status in the LIST output or recovery catalog views.

keepOption   :  Changes the exemption status of a backup or copy in relation to the configured retention policy. For example, specify CHANGE ... NOKEEP to make a backup that is currently exempt from the retention policy eligible for OBSOLETE status.

Note: we cannot use this option with flash recovery area files.

UNAVAILABLE  :   Changes the status of a backup or copy to UNAVAILABLE in the repository. View the status in the LIST output or recovery catalog views. This option is provided for cases when the file cannot be found or has migrated offsite. RMAN does not use a file that is marked UNAVAILABLE in a RESTORE or RECOVER command. If the file is later found or returns to the main site, then use the AVAILABLE option to update its status.

UNCATALOG  :    Removes references to a datafile copy, backup piece, or archived redo log from the recovery catalog, and updates records in the target control file to status DELETED. The CHANGE ... UNCATALOG command does not touch physical backups and copies. Use this command to notify RMAN when a file is deleted by some means other than a DELETE command.

Caution: If we resynchronize from a backup control file, or upgrade the recovery catalog, then uncataloged records can sometimes reappear in the catalog metadata.

Here are few Examples : 

1.) UNAVAILABLE option   :  This example changes the status of backup set 100 as well as all backups of server parameter files created more than a day ago to UNAVAILABLE:

RMAN > CHANGE BACKUPSET 100 UNAVAILABLE;

RMAN > CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-1' UNAVAILABLE;

2.) Uncataloging and Cataloging Archived Logs  :  In this example, we move all archived logs to a new directory, uncatalog them, and then recatalog them in the new location:

RMAN > HOST move 'D:\archive\'  'D:\stand_arch\' ;

RMAN > CHANGE ARCHIVELOG ALL UNCATALOG;

RMAN > CATALOG START WITH 'D:\stand_arch\';

3.) Changing the Retention Status of a Backupset : This example, which requires a recovery catalog, changes an ordinary backup into a long-term backup:

RMAN > CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER NOLOGS;

Enjoy      :-)

ORA-04043 , ORA-00942 : object does not exist

Today, i have face very usual error. I have imported a table from ms-excess into "scott" schemas. when i check the table in scott schemas,i found it was there.And when try to access the table it throws as error "ORA-00942 " . I get puzzled.

SQL> select * from tab;
TNAME                          TABTYPE              CLUSTERID
-------------------------      --------------                ----------
BONUS                          TABLE
DEPT                             TABLE
EMP                               TABLE
SALGRADE                   TABLE
Table11                          TABLE

SQL> select * from table11;
select * from table11
              *
ERROR at line 1:
ORA-00942: table or view does not exist

then, i have decided to describe the table, and got the error "ORA-04043"  .

SQL> desc Table11
ERROR:
ORA-04043: object Table11 does not exist

SQL> desc dept
 Name                                      Null?                           Type
 --------------------------            -------------             ---------------------
 DEPTNO                               NOT NULL           NUMBER(2)
 DNAME                                                               VARCHAR2(14)
 LOC                                                                    VARCHAR2(13)

While in case of table "dept"  it is working fine,then i have decide to rename the table, so that it may solved.

SQL> rename Table11 to emp1;
rename Table11 to emp1
*
ERROR at line 1:
ORA-04043: object TABLE11 does not exist

After some analysis i come to conclusion that the table are export from the ms-excess and ms-excess support the char datatype i.e, it is right padded . So to solve this issue, i  put the table in double quotes to excess the table. For detail click here 

SQL> desc "Table11"
 Name                                         Null?                           Type
 -----------------------------               --------             ------------------------
 ID                                                                          VARCHAR2(20)
 ACCOUNTNO                                                      BINARY_DOUBLE
 TEMPLATENO                                                     BINARY_DOUBLE
 DEFAULTTEMPLATE                                          BINARY_DOUBLE

ORA_04043 is an special error and cause due to various reason. Few Possible causes are :
- An attempt was made to rename an index or a cluster, or some other object that cannot be renamed.
- An invalid name for a table, view, sequence, procedure, function, package, or package body was entered.


Enjoy     :-)