Solved

restoring oracle database until sequence # but it seems to blow by and restore to later point

Posted on 2013-10-23
13
841 Views
Last Modified: 2013-10-24
I am not sure what I am doing wrong. My database gets backed up at 5am. I want the backup from the 22nd at 5am, but when I restore and recover using until sequence number it appears to blow right by my sequence number and takes to a later point.

Basic steps are

startup nomount
restore controlfile from a backupset
restore database until sequence #
recover database until sequence #
alter database open resetlogs

I look at the database and I for sure see things that were done later in the day.

I will attach my rman output and the list of my backup that I want to use.
RMAN.pdf
0
Comment
Question by:bigmoriver
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
It's possible that the log number you are providing has data past a specific time?

If you want to recover to a specific time, why not recover until time?

http://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmrecov.htm

The following example performs an incomplete recovery until November 15 at 9 a.m.

RUN
{
  SET UNTIL TIME 'Nov 15 2002 09:00:00';
  # SET UNTIL SCN 1000;       # alternatively, specify SCN
  # SET UNTIL SEQUENCE 9923;  # alternatively, specify log sequence number
  RESTORE DATABASE;
  RECOVER DATABASE;
}
0
 

Author Comment

by:bigmoriver
Comment Utility
How do I get the exact time that I want I know when the backup finishes I see my archive logs but how do I correspond the exact time

1    6536    7223179984 20-OCT-13 7223195706 21-OCT-13
  1    6537    7223195706 21-OCT-13 7223199786 21-OCT-13
  1    6538    7223199786 21-OCT-13 7223213072 21-OCT-13

BACKUPSET      2747      1      6538       14.91M      Oct 21, 2013 12:31:57 AM EDT      Oct 21, 2013 5:05:33 AM EDT

SET UNTIL TIME 'Oct 21 2013 05:05:33';  ?

that look right?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>How do I get the exact time that I want I know when the backup finishes

Honestly don't know.  My PITR is shaky.  It's one of those features you cover in class then forget about.

What are you trying to recover?  If you don't know the SCN or exact time, then you must know something that can help you narrow it down.
0
 

Author Comment

by:bigmoriver
Comment Utility
I put down my list backup  in the attachment looks right to me on the scn and sequence. I have done it a couple of different way, let me try the time thing and see what happens.  Good thing is it a hyper small db. 18G.
0
 

Author Comment

by:bigmoriver
Comment Utility
RMAN> RUN
{
  set until time "to_date('2013 oct 21 05:05','yyyy mon dd hh24:mi')";
  ##SET UNTIL TIME 'Oct 21 2013 05:05:33';
  RESTORE DATABASE;
  RECOVER DATABASE;
}2> 3> 4> 5> 6> 7>

executing command: SET until clause

Starting restore at 23-OCT-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/23/2013 11:25:19
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time


Researching

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
4279    4280    PROD     132039356        CURRENT 1          28-MAY-10
9725    9726    IMDBP    364591345        CURRENT 1          01-SEP-10
73857   73872   ODYSSEY  767854026        PARENT  1          05-SEP-10
73857   73858   ODYSSEY  767854026        PARENT  972274     13-JUL-11
73857   74457   ODYSSEY  767854026        PARENT  7229142306 22-OCT-13

Googling tells me that I may need to reset my incarnation. But I don't have backups that far back. I had a 4 day rotation, I bumped it up to 7 days, once I saw that.  But it says I need to reset it to an earlier. My previous earlier is 13-JUL-11. I have no backups that far back.

Confused, on how to get around this. Could I reset it to an earlier incarnation even though I don't have that backup.  I have the backup I want to restore it to.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I'm very far from an Expert with RMAN.

I believe you need the controlfile from any incarnation you are wanting to use.

You might want to open an SR with Oracle Support as well as asking here.

Hopefully an RMAN Expert will be along later.
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.

 

Author Comment

by:bigmoriver
Comment Utility
I got it, I reset the incarnation to 73858
and then went thru my restoring controlfile from the backupset I want and ran your set until time, restore and recover. It looks great.
0
 

Author Comment

by:bigmoriver
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for bigmoriver's comment #a39594376

for the following reason:

I am giving you the solution because you made me try something that produced an error, that I fixed on my own. I consider this place a place to bounce things around, and to think differently about the problem, and that is what you did.

Change incarnation prior to resetlogs logs date
restore controlfile to the one I want.
 then

RUN
{
  set until time "to_date('2013 oct 21 05:05','yyyy mon dd hh24:mi')";
  ##SET UNTIL TIME 'Oct 21 2013 05:05:33';
  RESTORE DATABASE;
  RECOVER DATABASE;
}
Alter database open resetlogs;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I am giving you the solution because you made me try something that produced an error

You have accepted your own post as the only solution with no assist.

I also mentioned the previous control file in http:#a39594555.
0
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for bigmoriver's comment #a39594376
Assisted answer: 500 points for slightwv's comment #a39594555

for the following reason:

Restarting for Asker.

Netminder
Senior Admin
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
before starting rman (or sqlplus) set the environment variable nls_date_format='dd/mm/yyyy hh24:mi:ss'

both apps will then display the time of the datacolumn too

or use a select if the database is available
select to_char(complection_time, 'dd/mm/yyyy hh24:mi:ss') from v$archived_log;
0
 

Author Closing Comment

by:bigmoriver
Comment Utility
I am giving you the solution because you made me try something that produced an error, that I fixed on my own. I consider this place a place to bounce things around, and to think differently about the problem, and that is what you did.

Change incarnation prior to resetlogs logs date
restore controlfile to the one I want.
 then

RUN
{
  set until time "to_date('2013 oct 21 05:05','yyyy mon dd hh24:mi')";
  ##SET UNTIL TIME 'Oct 21 2013 05:05:33';
  RESTORE DATABASE;
  RECOVER DATABASE;
}
Alter database open resetlogs;
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

772 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

9 Experts available now in Live!

Get 1:1 Help Now