Solved

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

Posted on 2001-07-10
7
4,907 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:jbarth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Accepted Solution

by:
dbrower earned 38 total points
ID: 6269044
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
 

Expert Comment

by:orbaj
ID: 6269378
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
 
LVL 2

Expert Comment

by:afkapur
ID: 6270370
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:sunsapollos
ID: 6275228

  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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7038093
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7053112
Recommended disposition:
    Split points between: dbrower and afkapur

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7054061
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question