recover until time/cancel/change ... using backup controlfile: ora-01195: online backup of file X needs more recovery to be c

I want to make a clean copy on a second machine of an ever running database.
Therefore, i enter
alter tablespace XXX begin backup;
for all tablespaces, copy the datafiles to the second machine, and after
alter tabaespace XXX end backup;
for all tablespaces, too.

Then i enter
alter database backup controlfile to 'newctl.sql';
Now i edit this file: removing the head, strike out "reuse", change "noresetlogs" to "resetlogs", and delete the last three statements. Finally, i copy this to the second machine.

The archive-redologs are also copied to this machine (including initXXXX.ora).

Now, on the second machine, with the oracle-software-installation, datafiles, archivelogs, backup-controlfile, initfile, it should be possible to recover the database.

Now the following:
connect internal
@newctl.sql
alter database open resetlogs;

now the Message "ora-01195" occurs:
online backup of file 1 needs more recovery to be consistent.
I try to find out the last SCN of all datafiles:
select to_char(max(checkpoint_change#, '000000000000000') from v$datafile;
... gives me an number YYY.

now:
recover database until change YYY using backup controlfile;

But this will result in the same.
If i use more archivelogs to recover ("recover until cancel..."), the most time it will work (perhaps 80%).
If i rename the latest online-redolog to an archivelog, there were never problems, and the database was up-to-date.

Are the problem the active rollback segments?
Or unfinished transactions (means this the same?)
Or "uncommited" data inserted during the backup phase?

Sure, i can make some log switches (=checkpoint) and copy the last archivelogs. But if i try to keep this instances in sync?

The last chance i can see, is to copy the controlfiles and online-redologs ONLINE two times, compare these, and if they're identical, it shoud be '(a bit) consistent'.

Or i freeze the oracle processes (which means the same as if the system hangs) and copy the online-redologs and controlfiles then.

Any tips/solutions!

Thanks.

PS: I don't have not more than 75 Question Points. If iv'e got 500, i would give so much.
jbarthAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbrowerConnect With a Mentor Commented:
You need to switch log files immediately after the point in time that you want to recover to. I believe that this will give you the last change that you are looking for
0
 
orbajCommented:
Concur.
The alter tablespace begin backup command freeze the SCN in the corresponding datafile.
This is the SCN returned by the select to_char(max(checkpoint_change#, '000000000000000') from v$datafile;
But you need a SCN after the alter tablespace end backup to have something consistent.
0
 
afkapurCommented:
You are correct. Sometimes you have to apply the latest redo log. I also agree with dbrowser, that you should switch logs before you want to recover. This will archive the latest redo log and then you will not need to apply the latest redo log.

Here are the steps:

1. switch logfile.
2. archive controlfile to trace.
3. for each tablespace:
3a.   take tablespace in backup mode. (begin backup)
3b.   copy all files for the tablespace
3c.   end backup on tablespace.
4. switch logfile.
5. wait for the logfile to be archived.
6. copy controlfile trace file, all datafiles and all archive log files between steps 1 and 5 to the backup server or another server.
7. Layout the datafiles as you wish and modify controlfile accordingly.
8. login as internal
9. start instance in nomount mode.
10. create controlfile with resetlogs. change database name if necessary.
11. recover database until CANCEL.
12. after you have applied all redo log files, enter CANCEL.
13. open database with resetlogs option.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sunsapollosCommented:

  When you backup datafile finished,you must switch logfile.during backup datafile,database is running and add redo log to logfile.
  online backup is not consistent and arch log finished it.during backup,database consistent need online redo log to check consistent.
  alter method,you create your controlfile noresetlog and open is noresetlog and apply online redo log.Of course,if database is running when you copy online redolog,the recover oper will often fail.
0
 
MindphaserCommented:
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
DanRollinsCommented:
Recommended disposition:
    Split points between: dbrower and afkapur

DanRollins -- EE database cleanup volunteer
0
 
MoondancerCommented:
Thanks, Dan.
I finalized this today and will monitor it in the event an adjustment is needed.
Points have been split.
Points for afkapur -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20307701
Moondancer - EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.