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.
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.
johnsone
Is the database put into backup mode or shut down when the disk backup is taken?
ASKER
No, it isn't. Does this make those backed up datafiles useless or just inaccurate?
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm using Oracle 8i....is Log Miner available for that old version??
Yes. 8i was the first version it was available for.
ASKER
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
Thanks for your help