Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Getting backup strategy straight

    We're looking at the merits of converting all of our current WANG databases into a single Oracle instance, using separate tablespaces for the collections of objects corresponding to each WANG database. (You're welcome to offer any suggestions here as well.)

    So, this means that, on occasion, we might need to backup a single tablespace before a major change to that particular "database". In the case that a problem occurs, is it possible to restore a single tablespace without a shutdown? Our goal would be to only inconvenience those users who work with any of the objects on that one tablespace.

    -Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Or would this be a better use for the exp/imp utilities, to restore a set of tables within a schema back to a prior state, all of which reside on the same tablespace? The key still being that the database still needs to be up for users working with other tables.

    Thanks
    cf

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Given your requirements, personally, I would create seperate instances for each existing database.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Our goal, though, is to use 10g, and have one production instance across multiple servers. If we had a separate instance per database, that would be 10 instances, all requiring Grid support.

    Is it just tough to accomplish what we're after, or just not possible? Would it matter if each database was split out into it's own schema? From the limited amount of info I have, I didn't think multiple schemas would provide added benefit.

    -Chuck

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    in 10g you can have multiple instances within 1 grid.

    anyways, you can also recover one tablespace while the db is up and running so that answers your first question.

    I forget how 10g does it but I believe it is even simpler than TSPITR.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    "anyways, you can also recover one tablespace while the db is up and running so that answers your first question."

    Can you give me enough information so that I can go gather some info on this? So far, I haven't found a way to do this while the db is up, except to save off table data into flat files, and then truncate and reload that data if I need to restore.

    -Chuck

  7. #7
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    If this is a production instance, you best be backing up on more than 'on occassion', unless the data is entirely reproducable from the source. If you are in archivelog mode, put the tablespace in hot backup mode and back up the datafile.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    I think I may be explaining myself poorly, and perhaps my concerns are irrelevant in the Oracle context. Thanks for the answers so far, BTW.

    We backup the data nightly using a Cold Backup. We reserve weekends for database maintenance. The weekends are also used by our analysts for DDL changes. There will be occasions when an analysts request that a DDL change be moved into production for their group of tables (ie, their 'system' as we call it) finding out on a Monday that they hadn't fully tested their change.

    The analyst opts to return to the Friday evening state of their DDL. In the meantime, other users have been accessing other data in unrelated tables.

    My goal is not to return the entire database to the Friday evening state, but only the 100 or so tables in this one particular 'system'. Our plan is to store all 100 tables on their own tablespace, and so ideally, we'd want to be able to restore to the backup tablespace without applying any redo/archive logs. And to do this without bringing down the entire instance.

    Thanks,
    Chuck

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    RMAN and a catalog and archivelog mode and Tablespace POint In Time recovery can be your friends.

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    As the Duck said, TSPITR may be your best option, review:

    http://download-west.oracle.com/docs...ov.htm#1004797

    To Quote:
    Tablespace Point-in-Time Recovery
    The tablespace point-in-time recovery (TSPITR) feature enables you to recover one or more tablespaces to a point-in-time that is different from the rest of the database. TSPITR is most useful when you want to:

    Recover from an erroneous drop or truncate table operation
    Recover a table that has become logically corrupted
    Recover from an incorrect batch job or other DML statement that has affected only a subset of the database
    Recover one independent schema to a point different from the rest of a physical database (in cases where there are multiple independent schemas in separate tablespaces of one physical database)
    Recover a tablespace on a very large database (VLDB) rather than restore the whole database from a backup and perform a complete database roll-forward

    See Also:
    Oracle9i User-Managed Backup and Recovery Guide to learn how to perform user-managed TSPITR, and Oracle9i Recovery Manager User's Guide to learn how to perform TSPITR with RMAN.


    Media Recovery Options
    Because you are not completely recovering the database to the most current time, you must tell Oracle when to terminate recovery. You can perform the following types of media recovery.

    Type of Recovery Function
    Time-based recovery
    Recovers the data up to a specified point in time.

    Cancel-based recovery
    Recovers until you issue the CANCEL statement (not available when using Recovery Manager).

    Change-based recovery
    Recovers until the specified SCN.

    Log sequence recovery
    Recovers until the specified log sequence number (only available when using Recovery Manager).

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    Last question:

    If you know that a series of major DDL changes are going to occur, is it possible to export a tablespace, make your changes, and if things go wrong, delete the tablespace with the changes, and import the 'backup' copy?

    -Thanks,
    Chuck
    Last edited by chuck_forbes; 02-12-04 at 16:58.

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    In case there's someone else with the same need, it looks like the concept of 'TRANSPORTABLE TABLESPACES' fulfills all the criteria we're looking for, in theory.

    Now we just need to practice implementing it.

    Thanks for all the help. The suggestions here really helped me refine my searches.

    -Chuck

Posting Permissions

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