• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1641
  • Last Modified:

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

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
bigmoriver
Asked:
bigmoriver
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
bigmoriverAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
bigmoriverAuthor Commented:
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
 
bigmoriverAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
bigmoriverAuthor Commented:
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
 
bigmoriverAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
NetminderCommented:
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
 
Geert GOracle dbaCommented:
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
 
bigmoriverAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now