single table ONLY recovery

[PaulH]

I have the following setup

Database is in archive log mode and one particular table got corrupted or dropped. How can I recover that one table ONLY when database is online and people are working?

I have been reading up about point in time recovery I dont think that is what
I want to do. My understanding is that will bring the entire database to a state as of a particular time.

Most of the test cases I see online mention restoring all the datafiles (not control files and logs). I don't think that is what I want to do either since that
will affect the people working.

Can somebody outline the steps I need to follow to test this scenario or point me to some good online documenation that explains how to do this. My gut
feeling tells me this sounds a bit complex.


Thanks in advance to all who answer this post

[PAVB]

You forgot to mention Oracle version as well as your backup/recovery strategy.

Can't tell without these pieces of information.

[PaulH]

I am running in an Oracle 10g environment.

For simplicty, lets assume the following was done for all my tablespaces
and I have backups of control file and redo logs.

alter tablespace abc begin backup
!cp /tmp/abc01.dbf /tmp/backup/
alter tablespace abc end backup

Next lets assume I used RMAN to backup all my tablespaces and files.

How can I recover that one missing table with both strategies?

Thanks in advance for your help

[davey23uk]

you do point in time recovery to another server, export the table you want and import it into your prod database

you cannot just recover a simple table in oracle

[PAVB]

Too bad you didn't setup flashback feature, a flashback query to a point-in-time just before the incident would allow you to repopulate your table.

By the way, your backup/recovery strategy doesn't work. In your first post your told us your database is in archivelog mode, in your last post you told us you are taking hot backups but, as far as I can tell you are not backing up your archivelogs so you cannot recover.

[JChiappa]

Adding to it, the questioner said "one particular table got corrupted ** OR *** dropped", the "OR DROPPED" case is targeted very simply by the RECYCLE BIN, in 10g ....

[PaulH]

I apologize for the lack of terminolgy I am new at this and I want to make
sure I have all my bases covered. My backup script does the following

For every tablespace I backup each file ... this is how I am backing it up.

alter tablespace pbhtables begin backup;
! cp /ctron/oracle/pbh10.2.0/pbhtable01.dbf /ctron/oracle/pbh10.2.0/backup
alter tablespace pbhtables end backup;

I am also backing up each archive log one at a time my backup directory.

Lets assume my backup was done at 12PM and I had a table xyz defined. At
12:15PM a user dropped the xyz. My requirement is to recover the table xyz
with out taking down the database.

What I was thinking is that I can take all my datafiles and control files and
place them into another location, create another database called DB2 and do a point in time recovery to DB2 until 12PM so my table will now appear in the DB2 database.

My questions are:

1) Do I have everything I need to recover my table from the DB2 database. Ie
I am not backing up redo-logs. Do I need to that?

2) Can somebody outline the steps I need to take in order for this to work.
I know I need to do something like this.

1) cp orig init.ora to db2.ora (and edit paths)
2) restore datafiles and control file into new location

I think I can use alter database command to rename datafiles to update the control file ( have not tested this yet). I am looking for a set of instructions
to follow. I saw something on the web about cloning a DB. That is close to
what i need but not exact.

I want to test this scenario to see if it actually works so any details and
proper syntaxs would be appreciated or some good documentation on the internet will help too.

thanks for all that answer this post and sorry about the confusion.

[PaulH]

Adding to it, the questioner said "one particular table got corrupted ** OR *** dropped", the "OR DROPPED" case is targeted very simply by the RECYCLE BIN, in 10g ....

I saw the recyle bin option and it looks like it can do exactly what I need but
from my understanding that only works in 10G or higher. I am looking for a generic solution since I do have some old 9i DB's that I still have to support.

I started off with the database cloning script below but the problem I am running into is that when the DB is cloned, its not in archive mode anymore
and therefore how will it know about my previously created archive files that
I want to do a point in time recovery from. There has to be a way to handle this, I hope

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;


STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq


STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;


STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile


STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode