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,849 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
7 Comments
 
LVL 1

Accepted Solution

by:
dbrower earned 38 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:sunsapollos
Comment Utility

  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
Comment Utility
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
Comment Utility
Recommended disposition:
    Split points between: dbrower and afkapur

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now