Loss of "Current" Online Redo Log Group

·         Start the instance if necessary.

·         Attempt to alter the database and drop the redo log group. You will receive an ORA-00350 error stating that the redo log group needs to be archived.

·         Shutdown the instance.

·         Review the trace files.

·         Review the alert.log file.

·         Copy the datafiles and redo log files from the backup directory into their respective paths.

·         Mount the instance.

·         Query the v$log view to determine the sequence# of the current log group.

·         Run archive log list.

·         Recover the database until cancel, cancelling the recovery operation one log file sequence# before the current log group.

·         Once recovery is complete open the database with the resetlogs command.

·         Determine if a full off-line backup is required and perform one if necessary.

·         Delete the trace files in the $HOME/trace directory.

·         View the alert.log file for the recovery that was applied.

·         Remove the archive log files from the archive directory.

·         Remove the alert.log and trace files from the trace directory.

·         Ensure the instance is started and the database is open.

Oracle Database Block corruption

 What are Possible ways to detect Block corruption ?

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”--Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.

Generally block corruption occurs if write fails on the block, when the transaction is being committed

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

Whenever we encounter above error message mean we have BLOCK CORRUPTION.

Ø  Two types of block corruption can happens

- Physical corruption (media corrupt)

- Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk.

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header - footer - that is one of the checks, yes (it is looking for fractured blocks and when it hits one)

Physical corruption is "we cannot read the block from disk, something is physically preventing us from doing so”.

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. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

$ dbv FILE=/u01/test/system01.dbf FEEDBACK=100

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 (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead.

3. 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).

ANALYZE: Reporting Corruption

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You 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.

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.

For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.

RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION

V$BACKUP_CORRUPTION

V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. 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: 'C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF'

6. 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.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE

PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

How  we can find out corrupted block type

select segment_type,owner'.'segment_name

from dba_extents

where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;

Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger

Connected.

SQL> select * from test;

select * from test

*

ERROR at line 1:

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

ORA-01110: data file 5: 'C:\INDEXDATA01.DBF'

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;

Starting recover at 29-APR-15 using channel

ORA_DISK_1 starting media recoverymedia recovery complete,

elapsed time: 00:00:00

Finished recover at 29-APR-15

If you are not using rman then applying below procedure

- if it is index then drop and recreate index

- if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

Same Reference:

http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

NOTE: Find more information “Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g” Metalink Note: 28814.1

How to corrupt database block for practice purpose?

On Unix:

Use dd command

$man dd

On Windows:

Use Editor and open datafile write some junk character at middle of file and save it.

See Also

Server Maintenance Tasks for a DBA

General Tips to Prepare for an Oracle DBA Job Interview  *HOT*

 

DeadLock  in  Oracle - Practically Explained

RMAN Basics

RMAN is oracle's backup and recovery solution to protect files in the database.

RMAN parameters and descriptions:

1. Retention Policy

- Configuring a retention policy tells RMAN how long

you want to keep your backup information.

2. Backup Optimization

- If you turn on backup optimization and a backup fails half way through, RMAN picks

up where it left off when you restart the backup. If it is turned off, RMAN starts from the

beginning.

3. Default Device Type

- You can have RMAN back up files to disk or tape.

4. Control File Autobackup

- You can configure RMAN to take a backup of the control file and the spfile every time

a backup runs. Also, if the database is in archive log mode, it will take a backup of the

control file any time the database file structure changes such as renaming or adding a data

file.

5. Control File Autobackup Format

- This tells RMAN the name and location you would like control file auto backups to take

on.

6. Parallelism

- On a machine with many backup devices, such as tapes or disks, as well as multiple

CPU’s, you can set this parameter to use more resources in hopes of speeding up the

backup.

7. Data file Backup Copies

- This parameter will tell RMAN how many copies to make when backing up files. The

more you have the safer you are from losing a backup file.

8. Archive Log Backup Copies

- Similar to data file backup copies, but applies to archive logs.

9. MAXSETSIZE

- This can be used when backing up to tape to make sure that the backup files don’t span

multiple tapes. This way, losing one tape won’t nullify an entire backup. Typically, this is

left to unlimited when backing up to disk.

10. Encryption for Database

- Typically, the backup files created will contain the character strings of data that reside

in your data files. A clever hacker can extract this data and perhaps make sense out of it.

By turning this parameter on, all the data in the backup file will be garbled.

11. Encryption Algorithm

- You can choose the level of encryption in Oracle. The higher the encryption level, the

longer it can take to back up the database. Choices follow:

• AES128 AES 128-bit key

• AES192 AES 192-bit key

• AES256 AES 256-bit key

12. Snapshot Control File Name

- Tell RMAN where to put the control file and what to name it when a snapshot of the

control file is taken.