Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RMAN Restoring Control File with Redo Logs Intact

Expert Oracle Database Tips by Donald BurlesonMarch 25, 2015

RMAN Restoring Control File with Redo Logs Intact

2nd Scenario

Due to a media failure, Bob has lost all control filesand received the same error message as in Scenario 1 previously. Having control file backups, he decides to restore the backups of the control files and recover the database.

If all the copies of the control file have been lost but the datafiles and online redo logs are fine, then use the backup of the control file to recover from the loss.  Create a small disaster scenario for Bob where he loses his control files. Please note that we already did a backup and our database is running in archivelog mode.

To create Bob's disaster, very rudely move all the control files to a folder called control which is equivalent to losing all of them.

$ cd /u01/oradata/ORCL/
$ ls *ctl
control01.ctl  control02.ctl  control03.ctl
$ mkdir control
$ mv *.ctl control/

And just to make sure that we get an error, access something which would only come from the control file.  Now open a new SQL connection and try the following command, and note that sometimes we may not see the error when still being connected to the previous SQL session.

SQL>
select * from
 v$database;
select * from
 v$database
              *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/ORCL/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

So there are no more control files there with us. Bob decides to shutdown the database, start up in nomount mode, restore control files from backup, recover the database and open it for use.

The first step would be to immediately shut down the database:

SQL>
 shut abort;
ORACLE instance shut down.

Fortunately, Bob has taken the backup using RMAN so he can use it to restore the control file.  Moreover, he configured RMAN to do an automatic backup of the control file as follows:

RMAN> configure controlfile autobackup on;

But before that, he needs at least to have the instance started, as RMAN needs at least the instance to be up:

SQL>
startup nomount
ORACLE instance started.

Total System Global Area  171573248 bytes
Fixed Size                  1298668 bytes
Variable Size             134221588 bytes
Database Buffers           29360128 bytes
Redo Buffers                6692864 bytes
 

And now Bob fires up RMAN and restores the control files from the backup: 

RMAN> restore controlfile from autobackup;
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/ORCL/control01.ctl
output file name=/u01/oradata/ORCL/control02.ctl
output file name=/u01/oradata/ORCL/control03.ctl
Finished restore at 17-NOV-09

We can see that all the three control files have come up. This can also be verified as well.

$ ls *.ctl
control01.ctl  control02.ctl  control03.ctl

The next step would be to recover the database.  So Bob brings the database in the mount stage and issues the resetlogs command to open it up afterwards.

RMAN> alter database mount;
RMAN> recover database;

Now the database can be opened happily.

SQL>
alter
 database open resetlogs;
Database altered.
SQL>
select
 status
from v$instance;
 
STATUS
------------
OPEN

Recovery When Control Files and Data Files are Lost but the Redo Logs are Intact

3rd Scenario

Again, due to the media failure, Bob has lost all control files and datafiles which were in the same hard drive. However, the redo log fileswere saved as they were in a different hard drive.

This would be a semi-disaster situation.  The reason for adding semi? here is that we have lost almost everything with the loss of our datafiles and control files.  Yet, there is a ray of hope left as we still have our current redo logs.  In this case, as the current redo logs and archived logs both are available, it is possible to go ahead with the complete recovery.

Having full backup of the database, Bob does the following:

  • Shuts down the database and starts it up in nomount mode

  • Restores control files and starts the database in mount mode using newly restored control files

  • Restores the datafiles, recovers the database and opens it for use

If we want to test this type of recovery scenario, follow these steps to move all control files and datafiles to another directory:

$ cd /u01/oradata/ORCL/
$ ls
control01.ctl  example01.dbf  redo03.log    temp01.dbf
control02.ctl  redo01.log     sysaux01.dbf  undotbs01.dbf
control03.ctl  redo02.log     system01.dbf  users01.dbf
$ mkdir backup
$ mv *.ctl *.dbf backup/
$ ls
backup  redo01.log  redo02.log  redo03.log

Bob begins the recovery process by shutting down the database in abort mode and starting it up with nomount mode as follows:

SQL> 
shut abort;
SQL>
startup nomount;

Now, in the nomount stage, Bob tries to restore control file from backup:

 RMAN> restore controlfile from autobackup;

As he has the control file restored, he mounts and restores the database:

RMAN> alter database mount;
database mounted
RMAN> restore database;

So Bob has all that he needs.  Now he tries to recover the database:

RMAN> recover database;

Please note that this is a complete recovery only.  But he still needs to open the database using resetlogs.

RMAN> alter database open resetlogs;
database opened

And Bob is done!

 

 

 
 
 
Get the Complete
Oracle Backup & Recovery Details 

The landmark book "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump " provides real world advice for resolving the most difficult Oracle performance and recovery issues. Buy it for 40% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster