Oracle Interview Preparation Notes Part 13

· If an incomplete recovery is done on a database in a distributed environment, a coordinated time or change based recovery should be performed on all dependent databases to ensure global read consistency.

1. Use time based recovery on the failed database.

2. Open RESETLOGS.

3. Check alert file for the message “RESETLOGS after incomplete recovery UNTIL CHANGE scn.

4. Perform incomplete recoveries on dependent databases to the new SCN.

Clearing a corrupted redo log file (when dropping is not possible and database must be kept open):

· ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

· Perform a complete backup after this operation.

*** Recover from failure after RESETLOGS (before new backup)

1) SHUTDOWN IMMEDIATE;

2) Copy current control files to a save location.

3) Restore files and control files from a time before resetlogs (last good backup).

4) STARTUP MOUNT;

5) Get SCN applied at last recovery from the alert log.

6) RECOVER DATABASE UNTIL CHANGE scn USING BACKUP CONTROLFILE;

7) SHUTDOWN NORMAL;

8) Copy current save control files back to original location.

9) STARTUP MOUNT;

10) RECOVER DATABASE (applies all archives from resetlogs to current time);

11) ALTER DATABASE OPEN;

Tablespace Point in Time Recovery:

1) Clone the database

2) Restore backup on clone and perform incomplete recovery to time desired.

3) Export tablespace tables from clone, Import tablespace tables (or use transportable tablespace).

Troubleshooting:

DB_BLOCK_CHECKSUM = TRUE

· A checksum for every block is computed and stored in the block header.

· Verified when the block is read.

· Every log block is also given a checksum before it is written to the redo log.

· Heavy performance overhead.

DB_BLOCK_CHECKING = TRUE

· Check data and index blocks whenever they are changed.

· Minimal performance impact (does not check blocks while reading).

LOG_BLOCK_CHECKSUM = TRUE

· If set to TRUE, Oracle will check log file blocks for corruption at archive time (each log switch).

· Oracle will substitute good blocks from another member for the corrupted blocks.

· If all members are corrupt, archiving will not occur (must be cleared with the ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n command and back up the database).

DBVERIFY [ FILE START END BLOCKSIZE LOGFILE FEEDBACK HELP PARFILE ]

· OS utility to verify the integrity of an online or backed up datafile.

· Blocksize default is 2k and must be specified if block size is not 2k.

· Example: $ dbv file=system01.dbf blocksize=8192 logfile=system01.log feedback=100

DBMS_REPAIR

· PL/SQL package to detect and mark block corruptions.

· Created by the dbmsrpr.sql and prctrpr.sql scripts.

LOGMINER

· Use to track changes to database to pinpoint time or SCN to be used for imcomplete recovery.

· Useful in determining logical corruption times.

· Can be used for granular logical recovery by undoing specific changes.