Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-10-23
13
Medium Priority
?
1,204 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 39594360
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
ID: 39594376
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39594397
>>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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:bigmoriver
ID: 39594409
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
ID: 39594504
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39594555
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
 

Author Comment

by:bigmoriver
ID: 39594570
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
ID: 39594921
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39594922
>>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
ID: 39596655
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 38

Expert Comment

by:Geert Gruwez
ID: 39596393
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
ID: 39596656
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

916 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