Hello SIr . ..
two or three time ,,
i am go through oracle references and ur book ...
but after all i m confused ..(Thick Head)
abt backup and recover y ..
in which conditon we should create new controgile
or when using backup controlfile ..
i know sir u r too busy for answering others importants question , i do no waste ur time ...

sir can u give me any **** other link **** other than oracle technet references ..
for clearity of basic of backup and recovery.

thanx sir...

Thanx Sir ... For ur Reply

Sir ONE thing more ..
For testing
I create a test database .... noarchivelog
with 80.4 m system.dbf [ only catalog.sql and catproc.sql ]
with 1 control file
with 2 redo groups [ each 10 mb with one member ]
no temp and no undo tablespace

sir i shut the database cleanly ..
and take backup [ system.dbf and control file ]

again startup database
under sys schema i create a table "Test" with 1 column
and insert 10 records .. through each insert command ..
mean no nologging ... and commit ;
and note the scn . Assume 10000... using dbms_flashback.get_system_ch..
after commit i drop the table ... [assume scn 10100 ];
and shutdown ..[cleanly ]
i m sure that there is no log switching i checkd this mean no log overwritten...
after that i replace the old system.dbf and controlfile ...
and up the database .. oracle says .. redo group is ahead
so i issued this command
Recover database until change 10000

Media recovery compelete ..
Alter database open resetlogs ..
sir i did not find my table ... where i m wrong ..

i think if we are in noarchive mode after a cold backup
no redo is overwritten we can do incompelete / or full recover as like Archive mode. ..

2. sir in archivelog mode if i lost my controlfile
and had a old backuped control file .. . y should i using
backup controlfile for recovery .. i can create a new file if i know all files locations ...


Thanx

:)) ...I think you need to have a team of translators from different dialects (No offence to that guy!!)

I come from the same land this guy comes from. Even I found it hard to understand , in the beginning. Still, your perseverence to answer questions like these impresses me.

You are as cool as cucumber !

Who read your column for advice and then trash anyone they feel had a question they thought shouldn't have been asked. If they know it all tell them to NOT read your column and go elsewhere where their questions may be thought of in the same manner. This kind of input discourages many folks from even asking a question. Many folks are interested in learning more and shouldn't be discouraged from asking questions or they wouldn't have asked in the first place. BTW - I have never posted. I just read your column from time to time.

Tom,

Basically when we restore from hot backup and controlfile is current then recovery brings the checkpoint scn information in datafile headers and controlfile to same number before opening database. Is this right ?

As you mentioned "Anytime the controlfile is older than any of your datafiles."

In this case we have to use option "using backup controlfile"

In this case checkpoint information in controlfile is older then checkpoint information in datafile headers. How does recovery works in this case?

If i am using old controlfile because I lost all current control files and I am using "using backup controlfile" then my question is:

checkpoint information in controlfile is older then checkpoint information in datafile headers. How does recovery works in this case?

> If i am using old controlfile because I lost all current control files and I
> am using "using backup controlfile" then my question is:
>
> checkpoint information in controlfile is older then checkpoint information in
> datafile headers. How does recovery works in this case?

Important bit here is that the recovery will know what the latest checkpoint was from the datafile headers and it will ensure that you apply all archive logs all the way to that checkpoint. Only then will you be allowed to stop recovery and open database with resetlogs.

Hi,

I am trying to take controlfile backup using the following command

Alter database backup controlfile to trace;

When i check in the trace file i see the following entries of the control file.

DATAFILE
'/d26/data/system_01.dbf',
'/d26/data/rbs01_01.dbf',
'/d26/data/temp_01.dbf',
'/d26/data/tools_01.dbf',
'/d26/data/cpm_d_01.dbf',
'/d26/data/cpm_i_01.dbf',
'/d26/data/cpm_image_01.dbf',
'/d26/data/hrms_d_01.dbf',
'/d26/data/rencstbl_01.dbf',
'/d26/data/rencstbl_02.dbf',
'/d26/data/rencstbl_03.dbf',
'/d26/ora/8.1.7/dbs/MISSING00012',
'/d26/ora/8.1.7/dbs/MISSING00013',
'/d26/data/rencstbl_04.dbf',
'/d26/ora/8.1.7/dbs/MISSING00015',
'/d26/ora/8.1.7/dbs/MISSING00016',
'/d26/ora/8.1.7/dbs/MISSING00017',
'/d26/ora/8.1.7/dbs/MISSING00018',
'/d26/ora/8.1.7/dbs/MISSING00019',
'/d26/ora/8.1.7/dbs/MISSING00020',

What is this missing file , how to avoid this problem . please let me know ASAP.

thanks in advance

pradikan

Hi Tom,
In one your replies under this topic, it was mentioned that during the recovery process , the checkpoint SCN is advanced to match the latest checkpoint SCN (complete recovery) or in case of incomplete recovery to the desired checkpoint SCN .
But for database recovery ,in case of complete hardware failure using the backup controlfile ,is a complete recovery possible ?.. if so where do the recovery process get the latest checkpoint SCN from ? Because all the restored datafiles and controlfiles would not have the latest checkpoint SCN.
Am I missing something ?

Hi Tom,
In one your replies under this topic, it was mentioned that during the recovery process , the checkpoint SCN is advanced to match the latest checkpoint SCN (complete recovery) or in case of incomplete recovery to the desired checkpoint SCN .
But for database recovery ,in case of complete hardware failure using the backup controlfile ,is a complete recovery possible ?.. if so where do the recovery process get the latest checkpoint SCN from ? Because all the restored datafiles and controlfiles would not have the latest checkpoint SCN.
Am I missing something ?

Sandeep, (Tom kyte please correct if I am wrong).

Assuming you have backup of old control files as of SCN 100 and
hot backup of datafiles have SCN from 200 to 300
(different files were put in BEGIN BACKUP mode in different times, so when you restore all of your datafiles
from your hotbackup, the datafile which was put in BEGIN BACKUP MODE first will have SCN 200 and the datafile
which was put in BEGIN BACKUP mode last, will
have SCN of 300, all other datafiles may have SCN between
200 and 300(inclusive).

Now your system crashes at SCN 700, you lost your all
datafiles and online control files, but NOT your archived
log and online redo log files. You have your archived log
files from SCN 200 to 700.

now you will do
1-copy the old control files to there default location
2-copy all the datafiles from your hotbackup to there
respective locations.
3-startup mount
4- RECOVER DATABASE USING BACKUP CONTROL FILE UNTIL CANCEL
5- keep pressing ENTER (or TYPE AUTO and press ENTER), when
prompted for the files. This way you will apply all logs
from SCN 200 to 700 and then you open your database
with ALTER DATABASE OPEN RESETLOGS, the latest SCN will be in your last log file.

Reference to your review
---------------------------------------------------------
re: Latest SCN March 9, 2007 - 6am US/Eastern Bookmark
---------------------------------------------------------

4- RECOVER DATABASE USING BACKUP CONTROL FILE UNTIL CANCEL
5- keep pressing ENTER (or TYPE AUTO and press ENTER), when
prompted for the files. This way you will apply all logs
from SCN 200 to 700 and then you open your database
with ALTER DATABASE OPEN RESETLOGS, the latest SCN will be in your last log file.
---------------------------------------------------------
Dear Tom / Naz (reviewer of march 9, 2007)

Please focus light on the subject.
When the control file is at SCN 100 and datafiles are at SCN between 200 to 300. How will Oracle server know upto what SCN it should accomplish the recovery.

As I know, Oracle recovers the data from archive as per controlfile SCN.

1. In this case having no current control file, we are using clause "using backup controlfile". From where will it (it= 'Recovery process' / Oracle ) get the exact SCN (700) to update the datafile and controlefile.

2. Please guide if I understood correct -- "using backup controlfile" means your controlfile is at older SCN than the datafiles are having.

Thanks in advance.

Hi Tom,

Could you please clear one of my doubts here? when we restore an old copy of backup controlfile and recover the database using backup controfile...Can we perform a complete recovery if all the redo logs are archive logs are intact.

If this is true, Can we say that Oracle picks up the SCN value till which the database has to be restored from Redo Logs?

Thanks in advance

I have a hot backup using RMAN

Suppose i figure out that while my database was down all my multiplexed control files are lost

BUT

i had a backup of control file..... i backed it up using

alter database backup control file to trace; command

i execute the script which i have in the above trace file.....

the control file is created and the database goes to Mount state....... and i open my database using RESETLOGS

at this moment would my backups would be valid?

In the sense would i be able use the backups and restore my database to a earlier point.

Reason i ask this beacuse the Control file script i have in the trace file created says >>

Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:


Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.

-- Data used by Recovery Manager will be lost

-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:


Both the cases say that data used by RMAN will be lost.

could you please make clear the consequences of such a control file recovery

The backup i took was using RMAN in nocatalog mode ... so all the backup info would be in database control file.

Thing i read is that control file contains

A control file contains the following entries:
¿ Database name and identifier
¿ Time stamp of database creation
¿ Tablespace names
¿ Names and locations of data files and redo log files
¿ Current redo log file sequence number
¿ Checkpoint information
¿ Begin and end of undo segments
¿ Redo log archive information
¿ Backup information



It says
¿Current redo log file sequence number
¿Checkpoint information
¿Backup information



I didnt see this information in the trace control file script.... :(

Please explain


Also

Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.

-- Data used by Recovery Manager will be lost.


Whta does that last statement exactly means ?

My guess is that backup taken using RMAN would be obsolete.

I hope you understand what i m confused with ...

RMAN i use is in nocatalog mode ...
so all the backup information would be in the control file of the Database i backed up using RMAN.........

I hope that clarifies why i want to make sure if the backups will be usable when i do a "backup to trace".

Also

Why would i Not
"You in general will not use a backup to trace controlfile - ever"

use it.
Kindly explain

I am recovering the database using  a hot backup . 

My question is :- 

how does oracle knows my control file is a backup controlfile even though scn in my controlfile is much latest then snc in all my datafiles header .. 

It is actually a backup control file but how does oracle knows about it.. because i have read somewhere that if your controlfile scn is lower than any of your datafile scn than oracle ask you to use "using backup controlfile " keyword in recover command .. but my controlfile scn is much latest than any of my datafile scn .. 


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


SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1113663
           1113663
           1113663
           1113663
           1113663

SQL> select checkpoint_change# , last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
           1115964
           1115964
           1115964
           1115964
           1115964


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           1115964

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         56   52428800          1 YES INACTIVE
      1113502 28-AUG-11

         3          1         58   52428800          1 NO  CURRENT
      1115964 28-AUG-11

         2          1         57   52428800          1 YES INACTIVE
      1115934 28-AUG-11


SQL> recover database using backup controlfile ;
ORA-00279: change 1113663 generated at 08/28/2011 17:44:09 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVE\TEST\ARC00056_0757266112.001
ORA-00280: change 1113663 for thread 1 is in sequence #56

is there any other way oracle determines that is a backup controlfile . 
 
 

I understand that "recover ... using backup controlfile" modifies the controlfile in some way to support PITR. What, exactly, does it do?

Russ

Tom:

1) I have older controlfile as trace file.
2) All the datafiles are at different SCN's
3) I have all the online redolog

1)How does controlfile know where to start?
2)What is my reference to start?
3)Which datafile is reliable to start with ?
4) until when to recover?

a commit checkpoint had happened in between and some of the commited blocks are not moved to datafile but recorded in redo. how does controlfile will come to know all these info?

Here is situation where I am confused.

1) @ 19:54 I have taken the controlfile backup as trace file
2) issued thrice 'alter system switchlogfile'
3) Then 'alter tablespace users begin backup'
4) issued thrice (3 groups) 'alter system switchlogfile'
5) Stopped service of my database in 'serives.msc'
6) Moved the last 6 archive log generated by 'alter system switch logfile' to different location
7) Started the service in 'services.msc' 
8) Startup nomount
9) Recreated the controlfile (which was taken in the step 1) 
10) Tried to open the database 'alter database open' and below is the error message @ 20:04

==================================================
For the step 10:

SQL> alter database open;
Alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORA\DATA\SYSTEM01.DBF'  -----> Why is it asking recovery for system datafile though I have issued begin backup to USERS tablespace(which is having different SCN than others)
===================================================

11) I have issued 'recover database' and the error message below
===================================================
SQL> recover database;
ORA-00279: change 261945 generated at 09/17/2013 20:00:13 needed for thread 1
ORA-00289: suggestion: D:\ORA\ADMIN\ARCH\1_11_826314637.DBF
ORA-00280: change 261945 for thread 1 is in sequence #11 -----> how does it came to know from here we need to recover?

----Checked alert log, when I backed up my controlfile and the previous sequence# 7

Who is triggering to search this sequence and how is finds to start from here? 

Finally, as visible; only the physical locations and few details are recorded in trace backup of controlfile,how/who coordinates all these activities.

//Chakra

Tom:

The above 2+ these threads are on same issue. I request you to have a look at last threads to solve confusion.

After the step 11

12) Moved all the archived logs to its default location
13) Issued, 'recovered database using backup controlfile'
14) Supplied required archived logs and at the finally, it was asking a archived redolog which is nothing but my last online logfile (from alert log)
15) I have supplied it but it could not accept it.
16) Finally, issued 'recover database using backup controlfile until cancel' and provided the online logfile and recovery completed; opened with resetlogs.

My question:

1) Recover database; --->simply apply all the redologs till the final one without user intervention.

When do we need to user 'recover database using backup controlfile' and 'recover database using backup controlfile until cancel'

What is difference and I would really appreciate if you provide us the deep insight on this

Hi Tom,

This might sound silly but, I am curious to know this.

In hot backups, Consider below two scenarios?

Scenario 1: (Hot backup)
run {
allocate channel ch1 device type disk;
backup full database;
backup current controlfile;
release channel ch1;
}


Scenario 2: (Hot Backup)

run {
allocate channel ch1 device type disk;
backup tablespace a;
backup tablespace b;
backup tablespace c;
release channel ch1;
}


In above scenarios,

1) Lets assume that, backup database command in scenario 1 started at 2:00 pm with current scn as
1000, It took 2 hrs to complete the backup and it went to next command backup current control file
command. After 2 hrs, SCN number increased to 2000. So, what SCN will "backup control file "
command take here? 1000 or 2000?

2) Same question applied to second scenario as well, does backup of tablespaces a,b and c start
with diff SCN? because, it waits till the prior backup command gets completed.