Solved

Recover Oracle data without a backup

Posted on 2011-09-02
9
426 Views
Last Modified: 2013-12-07
Hi all. We have a working database that accidentally had some data changed and was committed. Is there any way to restore a system archive from yesterday without any proper backup in place - i.e. relying on inherent Oracle saving.

System is Oracle 10g on Solaris
0
Comment
Question by:mgferg
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 9

Expert Comment

by:jerrypd
ID: 36475797
not that I am aware of - - the transaction logs have probably already been applied at this point, and even if you were to delete them, the data would become "inconsistent".
0
 
LVL 11

Expert Comment

by:jgiordano
ID: 36475805
when you say no backup, no offsite or enterprise backup? what about exports? or snapshots? All would be configured and are not automatic so you would know if they are configured.

If the answer is no, then you are up a creek... sorry
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36475896
Had you caught this early enough you might have been able to use flashback query.  You can try it but I doubt it will work.

I cannot remember if logminer shows before images or not but if you have the archived redo logs around you might check it out.
0
 

Author Comment

by:mgferg
ID: 36476243
thanks - how can I check was is possibly available?

next option is to restore the db somewhere else and save the data from their - any pointers on how thi sis done please - ie copy the db file to a different location then what?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36476258
To restore the database somewhere else you need a backup.

There might be some low level queries to see the last available SCN or timestamp the the online redo logs but I don't know of it.

Easiest way is to try a flashback query from the time before the delete/commit.  It will either work or it won't.

To see what logminer provides, check the docs.
0
 

Author Comment

by:mgferg
ID: 36483326
So what I want to know is - if I restore the db directory somewhere else- how can I load this in conjunction with the db that is currently running?

i.e. the directoy with all the .ctl, .log, .dbf - the main db file is in this dir, call it mydata.dbf - how can this be "mounted" so I can retrieve the valid data, that can then be used to update the current productionon one?

Worst case I could replace this file with the backup, but not sure if I should restore all files, or just the db file is ok (?). Assuming all files ...

Thanks - this is a production system, so would really appreciate some help that I'm out of my depth with.

Regards,
Mark
0
 
LVL 11

Expert Comment

by:jgiordano
ID: 36483677
I am not sure if i understand but you DO have a backup? It would help to understand what kind of backup you have.

 If you want to clone the current db there are a few ways. 1) shut production db, fully copy all the files onto another box.
2) install on a new box and apply exports of the data you want.
3) If your prod db is running in archival log mode you can back it up, take the DBFs and apply the archive logs.

Then you can restore your backup. But again depending on your backup strategy it may be as simple as installing oracle somewhere and importing your backup then grabbing the data.
0
 
LVL 12

Accepted Solution

by:
praveencpk earned 250 total points
ID: 36483746
>>>Worst case I could replace this file with the backup, but not sure if I should restore all files

if you have a backup you can restore the database using until time and and open it with reset logs.

1) copy backup file to temporay location in the new host.
2) rman TARGET / NOCATALOG
3)rman>CATALOG START WITH 'new_loc/backup/temp/';
4)rman>RUN
{
  ALLOCATE CHANNEL c1
  RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP;
  SHUTDOWN ABORT;
}
4) Change any location-specific parameters, for example, those ending in _DEST and _PATH, to reflect the new directory structure.
5) STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
6) rman> RUN
{
  ALLOCATE CHANNEL c1
  RESTORE CONTROLFILE FROM AUTOBACKUP;
  ALTER DATABASE MOUNT;
}
7) execute the recovery script.
rman>
RUN
{
  # allocate a channel to the tape device
  ALLOCATE CHANNEL c1
  # rename the datafiles and online redo logs
  SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
  SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
  SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
      TO ''?/oradata/test/redo01.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
      TO ''?/oradata/test/redo02.log'' ";
SET UNTIL TIME 'Nov 15 2002 09:00:00';
RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  # recover the database
  RECOVER DATABASE;
}
EXIT
7) last and final step
RMAN> ALTER DATABASE OPEN RESETLOGS;
 hope this helps you..........


check this doc for more info...........

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

Author Comment

by:mgferg
ID: 36484322
It does, thanks so much for the help. We've managed in the interim to recover data a different way. I would like to test this though (and document), should this happen again.

(btw, the backup we have is the entire db directory with all the file extensions as listed above)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 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

18 Experts available now in Live!

Get 1:1 Help Now