Thursday, February 02, 2006

Backup Control Files - Are they special?

I read an article today that talked about backup control files. Typically, DBAs don’t deal with backup control files on a daily basis so I thought I would take a look. This article asserts that Oracle requires a RESETLOGS operation after performing recovery using a backup control file because –

Oracle needs to update certain structures in the backup control file before opening the database

I certainly agree that Oracle will update the control file when opening the database and resetting the logs. But, let's try to understand what is updated and why. Before we talk about the criticality of the RESETLOGS post-recovery, using a backup control file, let’s try to define what a backup control file really is. Better yet, let’s first list what a backup control file is NOT:

· An OS image of the control file taken while the database is closed.
· An OS image of the control file taken while the database is open (I just shuddered).
· An OS image of the control file taken while the database is mounted.
· A trace backup of the database control file (not unless a can of tomato sauce, some dough and grated cheese is a pizza).


A backup control file is an image of the database control file. But this image possesses very distinctive qualities.

· It contains a file type flag (value=4) that tells Oracle it is a backup control file.
· It is consistent with respect to a single point in time – thus the file type flag for Oracle’s benefit.
· The Stop SCN markers for each data file record are set to
0xffff.ffffffff. To you and I this is hexadecimal for “not available.”

These qualities assure Oracle that the integrity of the backup control file has been preserved. If a database is open and an OS image is taken of the control file it will most likely be corrupt. In contrast, when a backup control file is created using the ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command (or via RMAN) the acquisition of the control file enqueue ensures the file is static during the operation. The file type flag in the backup control file tells Oracle that it cannot (and should not) rely on its redo thread, checkpoint progress and log file records. By instructing Oracle to recover using a backup control file you are telling Oracle to avoid the redo thread records and log file records in the control file – and for good reason.

Well, what about those control file backups to trace – can you create a backup control file using the SQL dumped to trace? Yes. Simply instruct Oracle to RESETLOGS in the CREATE CONTROLFILE statement. The resultant control file will have the same file type flag setting as the backup control file created using RMAN or ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command. You can get at this file type flag data using the oradebug command.

SQL> oradebug setmypid;
SQL> oradebug dump controlf 10;

Be careful during your recovery because you can very easily convert your unsuspecting current control file into a backup control file – destroying the transactional ancestry of the database via the requisite RESETLOGS operation. Consider the following:

sys@erice:SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@erice:SQL> startup mount;
ORACLE instance started.

Total System Global Area 110070328 bytes
Fixed Size 731704 bytes
Variable Size 88080384 bytes
Database Buffers 20971520 bytes
Redo Buffers 286720 bytes
Database mounted.
sys@erice:SQL> recover database using backup controlfile;
ORA-00279: change 4379115 generated at 02/01/2006 22:41:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/dba/erice/erice_arch_1.dbf
ORA-00280: change 4379115 for thread 1 is in sequence #1


Specify log: {=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.
sys@erice:SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


sys@erice:SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done



By issuing recovery using a backup control file I have actually converted the current control file to a backup control file. This does not mean that I could not completely recover this database. All I had to do was issue RECOVER DATABASE USING BACKUP CONTROLFILE and supply the appropriate (preserved) online redo log(s).

Back to the original question – why does Oracle require a RESETLOGS operation post-recovery using a backup control file? Within each control file there are checkpoint progress records that provide Oracle with the on-disk RBA (redo byte address). This tells Oracle the offset into the current redo log file(s) that the LGWR has flushed the redo thread.

Let’s suppose Oracle permitted you to open the database without a RESETLOGS after recovery using a backup control file. Then Oracle must use what it knows to be true about its online redo thread: the log file records, redo stream records and checkpoint progress records. If the redo log files remained intact during your restore and recovery then they probably have previously generated redo. If you have 3 online redo log groups and your backup control file states that the on-disk RBA is half way into group 2 and the existing (non-reset) redo log for group 2 is full of previously generated redo, Oracle would start writing redo to its last know on-disk RBA. Hopefully you see where this is going. Potentially, the last half of redo written to group 2 would correspond to changes post-recovery, i.e. you would have a redo log that is corrupt. There could be a major gap in SCNs between the last entry pre-recovery and the first entry post-recovery within the same redo log file. For this reason, Oracle needs to update the redo thread, log file and checkpoint progress records in accord with the new incarnation of the database.

I am sure we could come up with dozens of scenarios where not resetting the logs would cause corruption. But, I have already given too many words to a post that was intended to be short.

16 Comments:

Anonymous Anonymous said...

Great piece of information very well explained. Thanks Kindly.

8/07/2007 11:52 PM  
Blogger Mehmood Rehman said...

Its very good article, infact unique but very helpfull.

keep up the good work :)

Cheers

9/12/2007 5:40 AM  
Anonymous Anonymous said...

When you say
recover database using backup controlfile;
where is the backedup controlfile?
which file oracle will use for recovery? current or backedup file?
and why to use backup control file?

11/20/2007 10:15 PM  
Anonymous Anonymous said...

Excellent, i was thinking about why we have to resetlogs when using backup control file. Thanks to Google and you now i understand it.

2/21/2008 6:16 PM  
Blogger Eric S. Emrick said...

Great. I am glad it helped you.

Eric

2/21/2008 6:52 PM  
Blogger Kadhiresan Chettiar said...

It is one beautiful artical I have came across.
I have few question .
1) Different between standby and backup controlfile.This is haunting me lot.How does oracle differentiate.Since filetype remains same.
2)While Recovery using backup controlfile does controlfile get updated.As u see it keeps on asking archive logs even which doesnot exists(Archive which was not even generated).

Regards
Hubert

3/29/2008 11:55 PM  
Blogger Eric S. Emrick said...

1) Different between standby and backup controlfile.This is haunting me lot.How does oracle differentiate.Since filetype remains same.

There is a flag in the control file that distinguishes a standby control file from a non-standby control file. The command "alter database mount standby database" is required to mount a standby control file.

2)While Recovery using backup controlfile does controlfile get updated.As u see it keeps on asking archive logs even which doesnot exists(Archive which was not even generated).

Yes, the control file is updated during recovery (to match file header checkpoint information). If you are using a backup control file and performing incomplete recovery Oracle might ask for an archive log that doesn't exist, because any redo/archive records in the control file are ignored in the recovery process as you have told Oracle it is a backup control file. However, if you get to a point where it is asking for an archive log that never existed, then your database is probably consistent and can be opened.

4/01/2008 8:27 PM  
Blogger Peekay said...

Dear Eric,
Thanks for the wonderful article. You have touched up a topic which has been hauting me for a while.
I do have some queries - hope you can clarify them for me please?
a) When we say "recover database using backup controlfile" - which backed up controlfile will be used for that?
b) Under what circumstances do we have to recover using backup controlfile rather than the simple "recover database"
Thanks and Regards
Peekay

7/16/2008 5:20 AM  
Blogger Eric S. Emrick said...

a) When we say "recover database using backup controlfile" - which backed up controlfile will be used for that?

The backup control file used is the control file(s) specified in you spfile/pfile. If you are using RMAN your restore/recovery parameters will determine which control file gets restored.

b) Under what circumstances do we have to recover using backup controlfile rather than the simple "recover database".

You need to use "using backup controlfile" for any incomplete recovery scenario or when you have lost all of your "current" control files, to name a couple. There are many other scenarios as well. The point is, using a backup control file necessitates a reset logs operation because the redo thread records in the control file are useless when using a backup control file to recover a database.

7/16/2008 10:56 PM  
Anonymous Anonymous said...

Hi Erik-
I am new to Oracle DBA tasks.

Please help me in resolving a database recovery testing.

I have tried to recover a test database(10g with NOARCHIVELOG) using the backup control file option.

--Taken the backup of
controlfiles and database
files including SYSTEM

--Deleted the redo log files

During the step RECOVER DATABASE USING BACKUP CONTROL FILE, ran into the issue that you have mentioned in the article.
Since I dont have .arc file I went for the "CANCEL" option.

ALTER DATABASE OPEN RESETLOGS led to
ORA-01113: file 1 needs media recovery for the SYSTEM01.DBF

How to recover a database using the Backup control file option

Thanks,
Kumar

7/25/2008 8:10 AM  
Anonymous Anonymous said...

Kumar,

NOARCHIVELOG means "No Archived Logs". What you're trying to do requires archived logs. Your database MUST be in archived mode for you to have any chance of database recovery. Hope it heps.

th

7/25/2008 11:25 PM  
Blogger Unknown said...

Eric,
Great information provided. I need little help in understanding incomplete recovery using backup controlfile. When we use "recover database using backup controlfile until cancel", in essence, we are instructing oracle to ignore the current control file and perform recovery i.e., oracle will assume that the current controlfile is lost & is not there and will start recovering. My question is how does Oracle know from what Log sequence# the recover should start from? Where does it get this information from? Is it from datafile header status?

To more my question, here is what i tried..

Oracle 9i

1. current log sequence # 10

2. Took a backup of control file at this sequence # 10

RMAN> backup current controlfile format '/opt/oracle/test01_curr.ctl';

3. Performed 10 log switches so that current log seq# is 20.

4. When the database is open, deleted all the control files. So, nothing worked and i had to "shutdown abort"

5. Restored the control file backup test01_curr.ctl using rman.

startup nomount
RMAN> restore controlfile from '/opt/oracle/test01_curr.ctl';

RMAN> alter database mount;

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 64385 generated at 11/24/2008 15:39:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/admin/test01/adump/1_11.dbf
ORA-00280: change 64385 for thread 1 is in sequence #11

So, We know that data file header status was updated when we performed the log switches, so the most recent SCN or checkpoint should correspond to Seq # 20. But we did take the backup of control file at sequence # 10. The recovery started asking archive logs from sequence # 11?????.

The recover did ask until logs 20 and i had to give the path for most recent online redo log file and open resetlogs.

Could you please throw some light on this?

11/24/2008 5:35 PM  
Anonymous John said...

Hi;

When I shutdown abort, instance recovery is done automatically once I open the database.

What I want to ask is, when I issue
'recover database using backup controlfile' statement even only once,
I can not recover the database with normal 'recover database' clause.

Does this statement modify controlfile or datafiles? Whats the reason for this?

12/13/2009 5:39 AM  
Blogger daspeac said...

I have heard about another way of microsoft sql 2000 data extraction. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

9/17/2010 3:42 PM  
Anonymous esxibackups said...

backup all files that are created and used the company's operations. This has already been done by creating multiple copies of the documents and their storage closets in the presentation. Today, computers are an essential tool for businesses. As such, the offside backup programs have become part of the priorities of business today. This is because companies are using some computer programs that have created their specific actions, such as the purpose of payroll and inventory.

7/07/2011 7:58 AM  
Anonymous Anonymous said...

hi im gayathri

im very new to this dba tasks,
i tried to recover my controlfile from backed up location ..
but throughing errors..

give answers in steps..

thanks!

1/23/2013 12:38 PM  

Post a Comment

<< Home