Crash recovery of Oracle databases using Commvault crash rec01

Crash recovery of Oracle databases using Commvault

Some time ago I published an article about creating a RMAN backup using Commvault.

See: https://technology.amis.nl/2018/03/01/creating-rman-backups-using-commvault/ .

In this article I describe how to execute a crash recovery using Commvault. You need to do a crash recovery when you lost all your datafiles, redolog files, control files and spfile. For example when all the harddisks on which the oracle database files have been located are lost you need to perform a crash recovery in order to get a working database. Also when the server has been crashed and all the oracle database files have been lost, you need to do a crash recovery.

When you use a block change tracking file and you also have lost your block change tracking file, you should perform your crash recovery in two steps: First you should restore your spfile and/or controlfiles and then disable the block change tracking file. And in step 2 you should restore your datafiles and execute the recovery. If you don’t do the recovery in these two steps, you will get an error message during recovery that there is no block change tracking file. If you don’t use a block change tracking file or if your block change tracking file is still available you can do the restore in one step.

In our example we use a block change tracking file and therefore we will execute the recovery in two steps.

 

Step 1: restore of spfile and/or controlfiles

If you are running your database on a Windows server the first step is: check if your Windows Oracle Database Service is started. This service should be running in order to perform recovery. When the service is started, check if your database is in nomount status.

In case your OS server has been lost you should create a new Windows Oracle Database service with the oradim utility. If you don’t have a spfile or pfile anymore, you could use the standard init.ora file which you can find in the %ORACLE_HOME%\database directory. In that case you should do the following steps:

  1. Copy the init.ora file to %ORACLE_HOME%\dbs
  2. Rename the init.ora to init[ORACLE_SID].ora
  3. Edit the new init[ORACLE_SID].ora : change the db_name parameter and also change the parameters in which you find the oracle_base value. Enter the correct values for these parameters.
  4. Start the database in nomount modus using “startup nomount”

Start the Commcell Console and login.

Select and open “Client Computer Groups”:

Crash recovery of Oracle databases using Commvault crash rec01

 

Select and open  Oracle:

Crash recovery of Oracle databases using Commvault crash rec02

 

A list of servernames will be appearing:

Crash recovery of Oracle databases using Commvault crash rec03

 

Select the requested servername:

Crash recovery of Oracle databases using Commvault crash rec04

 

Open Oracle:

Crash recovery of Oracle databases using Commvault crash rec05

 

Click with the right mouse button on the database name, in this case PRIMRP1A and then click with the left mouse button on “All Taks” and then select “Browse and Restore”:

Crash recovery of Oracle databases using Commvault crash rec06

 

The following window is shown:

Crash recovery of Oracle databases using Commvault crash rec07

Click on “View Content.

 

In the following window click in the left pane on PRIMRP1A. All the tablespaces will be selected:

Crash recovery of Oracle databases using Commvault crash rec08

 

Click underneath on this screen on “Recover All Selected”:

Crash recovery of Oracle databases using Commvault crash rec09

In the next screen select and deselect the following:

select "Restore SP file"
select "Restore Control File"
deselect "Restore Data"
deselect "Recover"

Crash recovery of Oracle databases using Commvault crash rec10

If you don’t use a block change tracking file or if you still have your block change tracking file you can also select “Restore Data” and “Recover” and perform the recovery in one step. But in this case as has been said already we lost the block change tracking file and therefore have to perform the recovery in two steps.

Click on Script Preview and check the rman script which will be executed. You will see something like:

#
#******CONTROL FILE/SPFILE RESTORE SCRIPT******#
SET DBID 910255345;
run {
allocate channel ch1 type 'sbt_tape'
PARMS=" BLKSIZE=1048576 SBT_UNSETENV=0,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)"
TRACE 0;
## send " -jm 16908298 -a 2:-1 -cl 883 -ins 202 -at 22 -j 517590 -rcp 0 -ms 1 -p 1 -df -PREVIEW";
 restore spfile  from autobackup ;
 startup force nomount;
}
exit;
SET DBID 910255345;
 run {
allocate channel ch1 type 'sbt_tape'
PARMS=" BLKSIZE=1048576 SBT_UNSETENV=0,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)"
TRACE 0;
## send " -jm 16908298 -a 2:-1 -cl 883 -ins 202 -at 22 -j 517590 -rcp 0 -ms 1 -p 1 -df -PREVIEW";
 restore controlfile  from autobackup ;
}
exit;

 

Click on OK to return to the last screen.

On this screen click OK to start the restore.

To monitor the status of the restore job click on the Job Controller:

Crash recovery of Oracle databases using Commvault crash rec11

You will see the something like the next list of running jobs in Commvault:

Crash recovery of Oracle databases using Commvault crash rec12

You can click on the job to check the progress of this job.

Wait until the job gets status Completed.

If it gets status Completed check if the spfile and controlfile are properly restored.

 

The next step is: disable block change tracking. You should do this because you lost your block change tracking file and therefore can not use the block change tracking file. You can do this as follows:

login as sysdba using sqlplus:

sqlplus / as sysdba

In sqlplus:

alter database mount;
alter database disable block change tracking;

 

Step 2: restore datafiles and execute the recovery

Go back to the commvault console. Again select in the “Client Computer Groups” tree the database name and then with the right mouse button select the “All Tasks” option. And then again select “Browse and Restore”. Click on “View Content”. Click in the left pane on the database name. In that way you select all tablespaces. Click on “Recover All Selected”.

Select and deselect the following:

select "Restore Data
select "Recover"
deselect the options "Restore SP File" 
deselect "Restore Control File".

Remember: you have already restored the spfile and controlfile in step 1. So therefore you deselect Restore SP File and Restore Control File.

 

Next click on the Advanced button. On the tabs Restore and Recover you can choose restore and recovery to Current Time or choose point in time recovery. In this example we will choose restore to Current Time.

On tab “Restore” choose: From the Latest Backup. Also select Restore Archive Log and then option “All”:

Crash recovery of Oracle databases using Commvault crash rec13

 

Go to tab Recover: Choose “Recover until Current Time”:

Crash recovery of Oracle databases using Commvault crash rec14

Click OK.

Click on Script Preview and check if the generated RMAN script is indeed what you want.

In this example this is the generated RMAN script:

#******DATA RESTORE SCRIPT******#
run {
allocate channel ch1 type 'sbt_tape'
PARMS=" BLKSIZE=1048576 SBT_UNSETENV=0,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)"
TRACE 0;
## send " -jm 17039374 -a 2:-1 -cl 883 -ins 202 -at 22 -j 525310 -rcp 0 -ms 1 -p 2 -df -PREVIEW";
 restore database ;
}
exit;
run {
allocate channel ch1 type 'sbt_tape'
PARMS=" BLKSIZE=1048576 SBT_UNSETENV=0,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)"
TRACE 0;
## send " -jm 17039374 -a 2:-1 -cl 883 -ins 202 -at 22 -j 525310 -rcp 0 -ms 1 -p 2 -df -PREVIEW";
 recover database;
alter database open resetlogs;
}
exit;

 

Go back to the last screen by clicking OK.

Click OK to start the restore and recovery.

To monitor click on Job Controller:

Crash recovery of Oracle databases using Commvault crash rec15

Find the restore job in the list of running jobs. Click on this job to get the details of this running job:

Crash recovery of Oracle databases using Commvault crash rec16

 

Click on “View RMAN log” to monitor the restore realtime:

Crash recovery of Oracle databases using Commvault crash rec17

On the end of the job you will probably get something like the following error message:

archived log file name=F:\ORACLE\FRA\PRIMRP1A\ARCHIVELOG\2017_11_02\O1_MF_1_104972_DZP0DCDN_.ARC RECID=105009 STAMP=958998555
unable to find archived log
archived log thread=1 sequence=104973
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/02/2017 12:29:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 104973 and starting SCN of 5289832268
RMAN>
Recovery Manager complete.
]

 

This error message is expected because you are executing a crash recovery and therefore you probably have lost some archivelogfiles and also the redo logfiles.

You should open the database using the resetlogs option. Login as sys in sqlplus and execute the following SQL command:

alter database open resetlogs;

 

The last step you should perform is: enable block change tracking again. Execute the following SQL command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘F:\oracle\FRA\PRIMRP1A\ BCT_PRIMRP1A.BCT’;

 

Now you are done! You just performed a crash recovery with Commvault.