RMAN Recovery | Restore Controlfile

In this post, you will learn how to recover a database using RMAN. To recover database, an RMAN backup is required. In last article, it was explained that how we can take backup using RMAN. In following example, backup taken in last article will be used to perform full database recovery. Visit following link to view last article.

http://exploreoracle.com/2009/09/06/rman-script-to-take-full-database-backup/

Now we have scenario in which all data files have lost, we have also lost controlfiles.But some how, we haven’t lost archivelog files since last backup. In this case, we can recover our database using RMAN backup and archivelog files. We shall only lose data which was in current online redo log file.Remember, at startup our database in not opened.

Invoke RMAN and connect to database using following command.

RMAN> connect target /

Now set DBID with following command, it should be the Id of your database.

RMAN> set dbid=1216018526

Now start database in nomount stage.

RMAN> startup nomount;

Now configure controlfile autoback location.

RMAN> set controlfile autobackup format for device type disk to 'd:/rmanbackup/%F’;

Use following command to restore controlfiles.

RMAN> restore controlfile from autobackup;

Using this way all three control files will be restored to their default locations. However, there is another way to restore controlfile if you do not want to restore controlfile using “from autobackup”. In this method, first connect to database, and then start it in nomount stage. Now locate the file name which contains controlfile backup in d:\rmanbackup, copy its name. Now issues following command to restore control file

RMAN> restore controlfile to ‘c:/CONTROL01.CTL’ from ‘d:/rmanbackup/C-1216018526-20090905-00’;

In this command ‘C-1216018526-20090905-00′ is name of backup file which contains controlfile backup. This file can be from autobackup or from other backup. Other back means controlfile backup taken using “backup format” method in RMAN.

Above restore controlfile command will create only one controlfile at ‘c:/CONTROL01.CTL’. Now copy this first control file at its location and save as it two times to create CONTROL02.CTL, and CONTROL03.CTL files. using this method you do not need to set DBID, and autobackup control file location.

 

Restoration of control file is key point in database recovery. As control file has the information about all datafiles. Once control file is restored, database can be mounted. Mounting of database is necessary. All recovery operation can only be performed in mount stage. You can mount your database using following command.

RMAN> mount database;

After mounting database, this is time to restore all datafilese. To restore database, use following command.

RMAN> restore database from tag full_db;

Media Recovery should be performed on database before opening. In this step archivelogs are applied to recover data since last backup and to bring database in consistent stage.

RMAN> recover database;

Issue following command to open database and reset logs

RMAN> alter database open resetlogs;

Following snapshots show full recovery process.

Rman Recovery (part 1)
Rman Recovery (part 1)
Rman Recovery (part 2)
Rman Recovery (part 2)

4 thoughts on “RMAN Recovery | Restore Controlfile”

  1. Hi,
    Thanks for the code. But in the script there are a wrong, the wrong is ‘restore database using tag full_db;’. The code will be ‘restore database from tag full_db;’ (I get it from the snapshot).

    I have some problems and questions . The problem is – i created some tables using system user. Then i backed up the database using your code. After that i doped the tables. Then i restore and recover the database using your code. But tables is not recovered. Question is – can i take backup every thing such as user, table, view, procedure, trigger, etc.?

    I need the script. Would you like to help me?

    Regards
    Rafi

  2. Hi Ahmed,

    Thanks for correction.

    You problem is strange. Did you used code from this URL to backup database? This code takes backup of full database. Data is stored inside datafiles, this code takes backup of all datafiles.

    Did you removed the original datafiles before restoring the backup?

  3. Hi,
    At first i created some tables such as Employees, Products, etc under user ‘system’. Then –
    1. I backed up the full database.
    2. Dropped/removed the Employees, Products (tables) from user ‘system’.
    3. after 2 days i restored the backup.

    But tables (Employees, Products) was not restored.

    For information – backup and restore process was successful.

    What was the problem? Is it possible to restore original dropped table/view?

  4. Ahmed,

    Dropped tables should be restored from archivelog file. “recover database” command should restore all data from archivelog files.

    So you may be facing following issues,

    1- Make sure that database archiving is turned on. To restore changes made after taking backup, database should be in archiving mod. Have a look at following post.
    http://www.exploreoracle.com/2009/08/12/archivelog-mode-concepts-oracle-hot-backup-and-cold-backup/

    2- Rman was unable to find archivelog files. Perhaps you may have deleted the archivelog files also.

    3- When we make some changes to data, the redo entries are stored in redo log file. In 10g, default size of redo log is 50mb. It means that a new archivelog will be created when the redo log files size reaches 50 mb. So in your case, system might not have transferred the redo-entries from redolog file to archivelog. You can use following command to switch log file. It will create archivelog file for all entries that are current in redo log file.

    alter system switch logfile;

    You may find following command help full to find out stats of redo log files.

    select * from v$log;

    select * from v$logfile;

Leave a Reply

Your email address will not be published. Required fields are marked *