single table ONLY recovery
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: single table ONLY recovery

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    single table ONLY recovery

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You forgot to mention Oracle version as well as your backup/recovery strategy.

    Can't tell without these pieces of information.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2009
    Posts
    91
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Last edited by PAVB; 03-01-2009 at 05:14 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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 ....

  7. #7
    Join Date
    Feb 2009
    Posts
    91
    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.

  8. #8
    Join Date
    Feb 2009
    Posts
    91
    Quote Originally Posted by JChiappa View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width