Great Tips for Successful Oracle Block Recovery!

 Author: Asif Momen

Page: 1 2 3

Most of the database administrators find it hard to perform block recovery during backup and recovery as it is difficult to corrupt an Oracle block. In this article I will discuss some great tips that will ease your task of block recovery during backup and recovery practice session. If you encounter a block corruption in a production environment then you will be able to set right it yourself instead of looking here and there for help.

I strongly recommend that NEVER PERFORM THIS TEST ON YOUR PRODUCTION, DEVELOPMENT OR TESTING DATABASE. Instead you should create a new database and perform tests on it. Block Recovery Test scenario discussed in this article is based on Oracle 10g R2 (10.2.0.1) on Windows XP.

Create Tablespace:

First of all we will create a separate tablespace.

SQL> create tablespace TABLSPACE_TO_CORRUPT datafile 'c:\mydb\data\DATABLOCK_RECOVERY_TEST.DBF' size 10m;

Tablespace created.

Create User:

Now create the user and grant privileges.

SQL> create user test_user identified by test default tablespace TABLSPACE_TO_CORRUPT;

User created.

SQL> grant connect, resource to test_user;

Grant succeeded.

Create Table:

Now create a test table and insert dummy data into it.

SQL> conn test_user/test
Connected.
SQL>
SQL> create table MYTABLE as select rownum rno, object_name from all_objects
2 where object_name like 'AQ%';

Table created.

SQL> select count(*) from MYTABLE;

COUNT(*)
----------
42

Now we will insert a record into table that we will corrupt.

SQL> insert into MYTABLE values (55, ‘TEST DATA TO BE CORRUPTED');

1 row created.

SQL> commit;

Commit complete. Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips for Test Data Generation in Oracle!!
   DBA Tips for Oracle Failure to Spawn!!
   The Power of Oracle Standby Database with a Time Lag!!
   Beware of Oracle Outage with database growth!!