This discussion is archived
8 Replies Latest reply: Dec 31, 2012 1:38 PM by 745659 RSS

Need to restore database to last week

745659 Newbie
Currently Being Moderated
Hi,

I am running 10g on Linux, and last week someone messed up a database and it needs to be restored to 27 December.

I am not a DBA, out regular DBA is on vacation till mid January, I have been tasked with this because I am the next most knowledgeable person (which is not much!).

I know we have archivelog on (I can see .arc files for the database), and I also have a backup of the actual data files and control files from when the database was first created. So in other words, I have all archive files and the initial data files.

What i do not know is how to set the restore till December 27. If someone could help, that would be appreciated.

PS, I know the DBA uses RMAN too, so maybe that can be used for this?

Thanks.
  • 1. Re: Need to restore database to last week
    Aman.... Oracle ACE
    Currently Being Moderated
    You can use the date format of DBPITR using the format
    set until time <here comes your desired date and time>
    Please see,
    http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr001.htm#sthref596

    Aman....
  • 2. Re: Need to restore database to last week
    745659 Newbie
    Currently Being Moderated
    So would I need to use RMAN then, and I cannot do it using just sqlplus?

    Do you know of an example in some blog or something where something similar is done?

    Please note, I am not using any tape drives, I will be copying the original datafiles to the box and so how will RMAN know where the original files are?

    Sorry, I am not a DBA, and I have never used RMAN, and so I just need some assistance that is a bit more concise - reading all the manuals is not really feasible, I need some example that is similar to the problem I am encountering.

    Thanks.
  • 3. Re: Need to restore database to last week
    EdStevens Guru
    Currently Being Moderated
    user8973806 wrote:
    So would I need to use RMAN then, and I cannot do it using just sqlplus?

    Do you know of an example in some blog or something where something similar is done?

    Please note, I am not using any tape drives, I will be copying the original datafiles to the box and so how will RMAN know where the original files are?

    Sorry, I am not a DBA, and I have never used RMAN, and so I just need some assistance that is a bit more concise - reading all the manuals is not really feasible, I need some example that is similar to the problem I am encountering.

    Thanks.
    Probably your best course of action would be to open an SR with Oracle Support and get them to help walk you through the process. There are a lot of details to be worked out and you don't want to dig your hole any deeper. The very first thing you want to do is shut down the database and copy all data files, all control files, all init and spfile to a safe location. To get a list of data files and control files
    sql> select file_name from dba_data_files;
    sql> show parameter control
    This will at least allow you to get back to where you are now if something should go wrong.
  • 4. Re: Need to restore database to last week
    JohnWatson Guru
    Currently Being Moderated
    To attempt this operation is not sensible for a non-DBA person. If you take responsibility for this and get it wrong, your company will lose the database and you will lose your job. The only responsible course of action is to say that this is beyond your skillset, and that you need the assistance of a consultant. It is the sort of thing that any decent consulting company will do for you quickly and reliably, and it can be done remotely of course.
    My boss would be happy to send you a quote :)
  • 5. Re: Need to restore database to last week
    745659 Newbie
    Currently Being Moderated
    Thanks Ed, John.

    It is an internal test database, not a production one, so unfortunately, Management will not be asking anyone outside the company to help with this.

    Yes, it's annoying that it has been lumbered onto me, but you know how companies operate these days - "just google it"!

    OK, I'll try and google around and hopefully will be able to find a similar scenario.

    Thanks.
  • 6. Re: Need to restore database to last week
    JohnWatson Guru
    Currently Being Moderated
    Well, if you really want to try it, here you go:

    From a unix prompt,

    export ORACLE_SID=name_of_your_database_instance
    export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
    rman target /

    and then from the RMAN prompt,

    run{
    shutdown abort;
    startup mount;
    set until time 'Dec 27 2012 00:00:00';
    restore database;
    recover database;
    alter dataabse open resetlogs;}

    If your DBA has set things up in a sensible way, that will work. But considering that he seems to have disappeared without leaving any sort of procedures manual, you may not be able to assume that he has done anything sensibly. Good luck.
  • 7. Re: Need to restore database to last week
    jgarry Guru
    Currently Being Moderated
    You might also want to ask if that particular date was for a data import. Sometimes users don't understand the different types of backup and data propagation, so you might look around for an import log file around that date. We can help you further if you find such a beast. It would say things like
    Connected to: Oracle10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 10.2.0.5.0 - Production
    
    Export file created by EXPORT:V09.02.00 via conventional path
    import done in WE8ISO8859P1 character set and UTF8 NCHAR character set
    . importing (some schema) objects into (your schema).
    And then a bunch of import statements.

    You probably have a directory set up somewhere for exporting, so look there for *log files, and also check the oracle directory and your dba's user directory.

    I know since data refreshes normally go from production to dev, I rarely backup dev. Some dba's also use rman duplicate, so you might also want to look in the beginning of the alert log to see if there are hints that the db was restored with rman on Dec. 27. Do you know if you are using a catalog with rman?
  • 8. Re: Need to restore database to last week
    745659 Newbie
    Currently Being Moderated
    Thanks a lot guys, appreciated!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points