Incomplete database recovery

9 Oct

Scenario 1:

Lets say your entire database lost due to some nasty work,  for example, during storage activity database mount point formatted or during maintenance activity FSCK reported corrupted blocks and during repairing the blocks you lost entire database. I hope in such a scenario your database was down but while starting up, database reported missing data-files.

Here is the step how to fix this issue:

Pre-requisites to fix this issue:

1. Make sure that you have valid database backup ( Full database backup level 0 and incremental backup since last full backup and all archive logs)

2. Lets assume that you are using catalog database and rman.

if you meet the requirements above, It is possible to restore database easily but be ready for enough downtime.

Here are the steps:


1. start the database in nomount mode (startup nomount pfile=’/opt/oracle/admin/database/prod/initPROD.ora’


2. login to catalog database and check the dbid of the database PROD

              sqlplus rman/rman@catalogdb

sql>select name,dbid from rc_database where name=’PROD’;

3. login to rman and set the dbid

rman target / catalog rman/rman@catalogdb

write the command in run blocks in rman, it is advisable to create a script for your easy:

rman target / catalog rman/rman@catalogdb log /opt/oraclea/recover/rman_recvery_log_2012.log <<EOF
set dbid=2987906652
set until time “to_date(’09-OCT-2012 00:00:00′,’DD-MON-YYYY hh24:mi:ss’)”;
allocate channel t1 type ‘SBT_TAPE’
parms ‘ENV=(NSR_CLIENT=dbnode,NSR_SERVER=backup,NSR_DATA_VOLUME_POOL=DatabasePool)’;
allocate channel t2 type ‘SBT_TAPE’
parms ‘ENV=(NSR_CLIENT=dbnode,NSR_SERVER=backup,NSR_DATA_VOLUME_POOL=DatabasePool)’;
allocate channel t3 type ‘SBT_TAPE’
parms ‘ENV=(NSR_CLIENT=dbnode,NSR_SERVER=backup,NSR_DATA_VOLUME_POOL=DatabasePool)’;
allocate channel t4 type ‘SBT_TAPE’
parms ‘ENV=(NSR_CLIENT=mdmd_node,NSR_SERVER=backup,NSR_DATA_VOLUME_POOL=DatabasePool)’;
restore controlfile from autobackup;
release channel t1;
release channel t2;
release channel t3;
release channel t4;

Note: Above script will set the dbid and sets the time until your database was healthy or until you have a valid backup

After successfully recovery, rman will exits and released all the channels. You can query the v$recover_file to check if any files not recovered till the time you mentioned in the script in “until clause”

Open the database with resetlogs option

alter database open resetlogs;



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: