Home » RDBMS Server » Backup & Recovery » wat is using backup controlfile option
wat is using backup controlfile option [message #258377] Sat, 11 August 2007 06:38 Go to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
If i am recreating controlfile,how i can use recover database option ?bcoz all the contents in current controlfile will be lost na???
Another question is if i use recover database using backup controlfile option after recreating controlfile ,exactly wat does it mean by backup controlfile
whether it is the one before i recreated the controlfile or what???
then where we are getting information for recovery i.e where the info req for recovery is maintained after recreation of controlfile????
Re: wat is using backup controlfile option [message #258385 is a reply to message #258377] Sat, 11 August 2007 07:29 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Even though you manually recreate the controlfile, Oracle is able to obtain information from the datafile headers, archive logs and redo logs for recovery. If the redo logs are all available then you can open the database without RESETLOGS.

Using the backup controlfile option with a recreated controlfile is indicating to Oracle that the controlfile you will be using for recovery is NOT the current controlfile.
Re: wat is using backup controlfile option [message #258388 is a reply to message #258377] Sat, 11 August 2007 07:53 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
If we are telling oracle not to use current controlfile then where from recovery is being performed...by refering wat oracle is recovering the DB...
Re: wat is using backup controlfile option [message #258397 is a reply to message #258377] Sat, 11 August 2007 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you talk about manual recovery or RMAN recovery?

Regards
Michel
Re: wat is using backup controlfile option [message #258398 is a reply to message #258397] Sat, 11 August 2007 09:39 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
manual recovery...

[Updated on: Sat, 11 August 2007 09:40]

Report message to a moderator

Re: wat is using backup controlfile option [message #258399 is a reply to message #258398] Sat, 11 August 2007 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean without RMAN, in old fashion way: SQL*Plus + alter database recover ...

Regards
Michel
Re: wat is using backup controlfile option [message #258400 is a reply to message #258399] Sat, 11 August 2007 10:01 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
ya ofcourse withour RMAN...user managed recovery but it's not a old fashoines i suppose...
Re: wat is using backup controlfile option [message #258401 is a reply to message #258388] Sat, 11 August 2007 10:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
pramodgaree wrote on Sat, 11 August 2007 08:53
If we are telling oracle not to use current controlfile then where from recovery is being performed...by refering wat oracle is recovering the DB...

Did you not read what I posted ???

ebrian wrote on Sat, 11 August 2007 08:29
Even though you manually recreate the controlfile, Oracle is able to obtain information from the datafile headers, archive logs and redo logs for recovery. If the redo logs are all available then you can open the database without RESETLOGS.

Using the backup controlfile option with a recreated controlfile is indicating to Oracle that the controlfile you will be using for recovery is NOT the current controlfile.

Maybe we aren't understanding your cryptic question.
Re: wat is using backup controlfile option [message #258402 is a reply to message #258401] Sat, 11 August 2007 10:09 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
Basic concept of recovery is that..it will compare checkpoint cnt n checkpoint SCNs from control file n then it will compare it with datafile headers....
any how in datafile headers that info will be there...but when i recreate controlfile or use backup controlfile wat will happen???
Re: wat is using backup controlfile option [message #258403 is a reply to message #258402] Sat, 11 August 2007 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference Oracle applies archived logs on each file that has a SCN less than the one in logs.
The difference is that instead of stopping at the control file scn, it stops when you say it to stop with "cancel" or "until" clause on recover.

Regards
Michel
Re: wat is using backup controlfile option [message #258404 is a reply to message #258403] Sat, 11 August 2007 10:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Michel Cadot wrote on Sat, 11 August 2007 11:14
There is no difference Oracle applies archived logs on each file that has a SCN less than the one in logs.
The difference is that instead of stopping at the control file scn, it stops when you say it to stop with "cancel" or "until" clause on recover.

And if you have all the archive and redo logs available, you can do a full recovery without a "cancel" or "until" and therefore, without a RESETLOGS.
Re: wat is using backup controlfile option [message #258405 is a reply to message #258404] Sat, 11 August 2007 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And if you have all the archive and redo logs available, you can do a full recovery without a "cancel" or "until"

There is no end with a backup control file, so I don't think you can avoid a "cancel" or "until" as Oracle always asks for the next log.

Regards
Michel

[Updated on: Sat, 11 August 2007 10:23]

Report message to a moderator

Re: wat is using backup controlfile option [message #258409 is a reply to message #258405] Sat, 11 August 2007 11:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
A cancel is not always needed with a recreated controlfile. You can actually do an 'alter database backup controlfile to trace' and view it's contents for the recovery steps in each situation.

SQL> alter database backup controlfile to trace as 'e:\ctrlbackup.ctl';

Database altered.

SQL> select * from v$controlfile;

STATUS  NAME
------- --------------------------------------------
	E:\ORACLE\ORADATA\WIN9I\CONTROL01.CTL
	E:\ORACLE\ORADATA\WIN9I\CONTROL02.CTL
	E:\ORACLE\ORADATA\WIN9I\CONTROL03.CTL


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del e:\ORACLE\ORADATA\WIN9I\*.ctl

SQL> -- After removing comments from backed up controlfile
SQL> -- and the RESETLOGS section

SQL> host type e:\ctrlbackup.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WIN9I" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'E:\ORACLE\ORADATA\WIN9I\REDO01.LOG'  SIZE 50M,
  GROUP 2 'E:\ORACLE\ORADATA\WIN9I\REDO02.LOG'  SIZE 50M,
  GROUP 3 'E:\ORACLE\ORADATA\WIN9I\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'E:\ORACLE\ORADATA\WIN9I\SYSTEM01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\UNDOTBS01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\CWMLITE01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\DRSYS01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\EXAMPLE01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\INDX01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\ODM01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\TOOLS01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\USERS01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\XDB01.DBF',
  'E:\ORACLE\ORADATA\WIN9I\TEST_RESTORE.DBF'
CHARACTER SET WE8MSWIN1252
;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;


SQL> @e:\ctrlbackup.ctl
ORACLE instance started.

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             100663296 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


System altered.


Database altered.


SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select * from v$controlfile;

STATUS  NAME
------- ----------------------------------
	E:\ORACLE\ORADATA\WIN9I\CONTROL01.CTL
	E:\ORACLE\ORADATA\WIN9I\CONTROL02.CTL
	E:\ORACLE\ORADATA\WIN9I\CONTROL03.CTL

[Updated on: Sat, 11 August 2007 11:56]

Report message to a moderator

Re: wat is using backup controlfile option [message #258410 is a reply to message #258409] Sat, 11 August 2007 12:18 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
Thx for ur valuable opinions...i hav one more doubt here..if i recreate controlfile with same DB name and NORESETLOGS..obviously i can use recover database..so,when i am using recover database obviously DB will use current controlfile...So,it is nothin but recover database in normal scenario only coz im recreating controlfile immediately after DB shutdown...But even after NORESETLOGS controlfile creation y it is asking for recovery for a clean backup n y not in normal case..in recreation of controlfile is there any creation of any SCN s or what??/
Re: wat is using backup controlfile option [message #258411 is a reply to message #258409] Sat, 11 August 2007 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but you don't use recover "using backup controlfile" so Oracle used the scn at the time you executed the create control file statement and as it was the one of the current database there was no problem.

Regards
Michel
Re: wat is using backup controlfile option [message #258416 is a reply to message #258409] Sat, 11 August 2007 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even when you shutdown normal/immediate and use the current control file, if you tell it is a backup one, you need to use "cancel" or "until":
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup mount
ORACLE instance started.
Total System Global Area  209715200 bytes
Fixed Size                  1289748 bytes
Variable Size             163578348 bytes
Database Buffers           41943040 bytes
Redo Buffers                2904064 bytes
Database mounted.
SYS> recover database using backup controlfile;
ORA-00279: change 2457407 generated at 08/11/2007 20:40:32 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\BASES\ARCHIVES\MIKA\616708629_001_00443.ARC
ORA-00280: change 2457407 for thread 1 is in sequence #443


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Regards
Michel
Re: wat is using backup controlfile option [message #258437 is a reply to message #258416] Sat, 11 August 2007 23:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I think there was some confusion of the context of "backup controlfile". My example was in regards to manually recreating the controlfile from a "backup controlfile" and not demonstrating recovering while specifying "recover database using backup controlfile".

That being said, even with using "recover database using backup controlfile" specifying "cancel" or "until" is not always required. If all the redo logs are available, then simply do a full recovery and specify RESETLOGS.

SQL> startup mount
ORACLE instance started.

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             100663296 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 1287535 generated at 08/12/2007 00:09:44 needed for thread 1
ORA-00289: suggestion : E:\DB_BACKUPS\ARCHIVE\ARC00013.001
ORA-00280: change 1287535 for thread 1 is in sequence #13


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\oradata\win9i\redo01.log
ORA-00310: archived log contains sequence 11; sequence 13 required
ORA-00334: archived log: 'E:\ORACLE\ORADATA\WIN9I\REDO01.LOG'


SQL> recover database using backup controlfile;
ORA-00279: change 1287535 generated at 08/12/2007 00:09:44 needed for thread 1
ORA-00289: suggestion : E:\DB_BACKUPS\ARCHIVE\ARC00013.001
ORA-00280: change 1287535 for thread 1 is in sequence #13


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\oradata\win9i\redo02.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
Re: wat is using backup controlfile option [message #258467 is a reply to message #258437] Sun, 12 August 2007 05:52 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
One more final doubt in this regard..in one scenario i have recreated controlfile with NORESETLOGS and then i have used recover database.So it will use current controlfile for recovery and it should use online redolog files contents if at all it going for recovery..but in my scenario i have used empty online redo files..but when i opened DB after recreating controlfile with NORESETLOGS it asked recovery n when i used recover databse it performed recovery and said media recovery complete.!!!!But as i dont have anythin in my online redo lo files i wonder how it performed recovery..how it might have performed recovery???plz help me in this regard.
Re: wat is using backup controlfile option [message #258482 is a reply to message #258467] Sun, 12 August 2007 11:27 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
pramodgaree wrote on Sun, 12 August 2007 06:52
...i have used empty online redo files..

I'm not sure what you mean by this.

SQL> alter database backup controlfile to trace as '/tmp/bkpctrl.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/sun10g/control01.ctl
/oracle/oradata/sun10g/control02.ctl
/oracle/oradata/sun10g/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/sun10g/redo03.log
/oracle/oradata/sun10g/redo02.log
/oracle/oradata/sun10g/redo01.log

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! rm /oracle/oradata/sun10g/*.ctl

SQL> ! > /oracle/oradata/sun10g/redo01.log

SQL> ! > /oracle/oradata/sun10g/redo02.log

SQL> ! > /oracle/oradata/sun10g/redo03.log

SQL> startup nomount 
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1279336 bytes
Variable Size              62917272 bytes
Database Buffers          138412032 bytes
Redo Buffers                2912256 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "SUN10G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/sun10g/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/sun10g/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/sun10g/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/sun10g/system01.dbf',
  '/oracle/oradata/sun10g/undotbs01.dbf',
  '/oracle/oradata/sun10g/sysaux01.dbf',
  '/oracle/oradata/sun10g/users01.dbf',
  '/oracle/oradata/sun10g/example01.dbf',
  '/oracle/oradata/sun10g/test.dbf'
CHARACTER SET WE8ISO8859P1
;
CREATE CONTROLFILE REUSE DATABASE "SUN10G" NORESETLOGS        ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00333: redo log read error block 1 count 1
ORA-01517: log member: '/oracle/oradata/sun10g/redo01.log'
ORA-27091: unable to queue I/O
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1


SQL> 


Can you provide actual output of what you are performing?
Re: wat is using backup controlfile option [message #258739 is a reply to message #258482] Mon, 13 August 2007 08:52 Go to previous messageGo to next message
pramodgaree
Messages: 22
Registered: August 2007
Junior Member
My scenario is that i have redo log files intact but the contents are different.To be exact i am taking a backup from my physical standby database which uses rcovery from archived logs.So for my physiacl standby databse redo log files are always empty or it don't have any info whatever.when i take backup from that n when i recreate controlfile with same name,NORESETLOGS it asked recovery while opening database.When i giver recover databse recovery was complete. I wonder how recover Databse statement worked when the there r no contents in online redo logfiles.
Re: wat is using backup controlfile option [message #258832 is a reply to message #258739] Mon, 13 August 2007 11:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Oracle server returns the SINGLE result if it needs recvoery or not
Media recovery complete.

post the result of

select * from v$log;
Re: wat is using backup controlfile option [message #258834 is a reply to message #258832] Mon, 13 August 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a question to whom?
OP ask a theorical question (I mean it is not a real current problem).
Brian and I don't have any problem, we are just discussing on different cases.
So, who must post the result of v$log?

Regards
Michel
Re: wat is using backup controlfile option [message #258837 is a reply to message #258739] Mon, 13 August 2007 11:43 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
pramodgaree wrote on Mon, 13 August 2007 07:52
My scenario is that i have redo log files intact but the contents are different.To be exact i am taking a backup from my physical standby database which uses rcovery from archived logs.So for my physiacl standby databse redo log files are always empty or it don't have any info whatever.when i take backup from that n when i recreate controlfile with same name,NORESETLOGS it asked recovery while opening database.When i giver recover databse recovery was complete. I wonder how recover Databse statement worked when the there r no contents in online redo logfiles.





Quote:
When i giver recover databse recovery was complete. I wonder how recover Databse statement worked when the there r no contents in online redo logfiles.


I asked on the basis of this question.

From v$log i guess we will kno whats the status of the log files.

like active
unused
stale
current

correct me if i'm wrong.
Re: wat is using backup controlfile option [message #258843 is a reply to message #258837] Mon, 13 August 2007 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I wonder what could be the status of standby database online redo logs? Wink

Or maybe it is in the question you quoted:
Quote:
when the there r no contents in online redo logfiles.
Cool

Regards
Michel
Re: wat is using backup controlfile option [message #258846 is a reply to message #258843] Mon, 13 August 2007 11:52 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Oh no Embarassed
again mistake.I'm sorry i didnt notice the word STANDBY.
Previous Topic: What is the ideal backup? (oracle 10gR2)
Next Topic: RMAN (magically) purging old directories
Goto Forum:
  


Current Time: Sun Dec 04 00:24:29 CST 2016

Total time taken to generate the page: 0.08560 seconds