[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

restoring table in new instance from archive logs -Oracle 8i

Hi All,

Let me start by saying I'm not a DBA but a web developer who must maintain an Oracle 8i DB to run a website.

I need to retrieve the data of a single user that was deleted from a particular table in the DB without disrupting data in current/live table of same name. The DB is in archivelog mode, I have no rman backup but the entire hard drive is backed up weekly (ie datafiles, control files, etc). Last week's data is good enough for this restore.

I'm guessing I need to create a new instance into which I can restore the DB in order to retrieve this table but rather not if I don't have to). I have never done a restore before so any help and/or pointers to instructions would be most appreciated.
0
uomobello
Asked:
uomobello
  • 5
  • 4
1 Solution
 
johnsoneSenior Oracle DBACommented:
Is the database put into backup mode or shut down when the disk backup is taken?
0
 
uomobelloAuthor Commented:
No, it isn't. Does this make those backed up datafiles useless or just inaccurate?
0
 
johnsoneSenior Oracle DBACommented:
They would be what Oracle calls a fuzzy backup.  They are pretty much useless.  Before doing a backup, you should be doing:

ALTER TABLESPACE <ts> BEGIN BACKUP;

And then once it is complete you should be doing:

ALTER TABLESPACE <tx> END BACKUP;

This will write additional information to the redo logs to allow Oracle to be able to recover.



You can look into the Log Miner utility which may be able to get you what you are looking for.  You will need to know the archive log where the table was dropped and you may be able to recover the table using the SQL_UNDO column that Log Miner will give you.
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.

 
johnsoneSenior Oracle DBACommented:
Since it seems you are backing up the entire file system at the OS level, you need to do the alter for all the tablespaces in the database.
0
 
uomobelloAuthor Commented:
You are correct in assuming we're doing a file backup at the OS level - our my hosting company is doing that. They are using a utility called BrightStor by Computer Associates and a version specifically designed for Oracle. This utility may be doing the ALTERTABLESPACE command because I was able to recover the DB once by applying the archived log files from one of these backups. However, I will check with my hosting company about the alter tablespace.

A point here though, I did not drop the table......one user's data was inadvertently deleted from the table, if this makes a difference.
0
 
johnsoneSenior Oracle DBACommented:
If the hosting company is using a utility, then it is most likely hanlding the alters for you.

If the data was deleted, then you should be able to recover it using Log Miner.  You just need to supply the archives where the delete took place and it should have the commands to put it back.
0
 
uomobelloAuthor Commented:
I'm using Oracle 8i....is Log Miner available for that old version??
0
 
johnsoneSenior Oracle DBACommented:
Yes. 8i was the first version it was available for.
0
 
uomobelloAuthor Commented:
I'm in the process of learning how to use log miner and testing it on my machine before using it on the web server where the deletion took place.

Thanks for your help
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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