Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unhappy Unanswered: How do I recover after accidentally deleting SMS tablespace containers?

    Does anyone know how to rebuild a database when SMS tablespace containers have been deleted while the database was shut down?

    I inadvertently deleted (using rm *, yes really) about 48 large files which were the tablespace containers for one of the schemas in a database I (mis-)manage (DB2 v7.2 under AIX v4.3). I realised what I'd done (I was in the wrong directory) and stopped the rm, so a further 12 containers have survived.

    My initial reaction was to see if the files could be recovered, since the database manager was not running and no other user has access to the filesystem in question, hence the data blocks would have remained undisturbed.

    Unfortunately, though the data still exists, the pointers which would enable the disk blocks to be joined up again in the correct sequence were nulled by the rm command.

    I don't want to recover from the last backup, because it is quite old and would entail reprocessing 13 generations of archived feed data which would take some time (at least a week).

    The design of the database is such that most of the data in the missing containers is replicated in another schema, so what I'd like to do is recreate the missing containers, get the database running again, and repopulate the tables from tables in the other schema. The data which isn't replicated I hope to recover by means of a partial restore from the last full backup, reprocessing only a small subset of the archived feed data.

    This begs several questions:

    I understand that when I attempt to activate the database, it will complain it is damaged, so I do I get past that point?

    Is there anything I need to do before that point?

    Once past that point, what do I need to do?

    How do I find out which file corresponded to which tablespace, or do I only need the list of filenames which are missing, or do I not actually need to know any of this?

    Will I need to recreate all the tables (the DDL is all archived)? I would have thought not, since the definitions must still be in the system catalog which is elsewhere.

    I really hope someone can help...

  2. #2
    Join Date
    Mar 2004
    Toronto, ON, Canada
    Do you have LOGRETAIN on? Your 100% best option is to RESTORE and roll forward through the logs if you have them... you should be able to get to the current point in time, assuming you don't have any unrecoverable loads or not logged LOBs.

    You won't be able to do much with the database now, I don't think it will let you even connect.

    You might be able to use "db2dart" to dump the non-deleted data to files, then import it into a new database.
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Aug 2001
    As Jonathan suggests, restoring and rolling forward is the best solution ...

    Alternatively, if you have a recent file backup of the deleted files, you may try restoring it and activating the database ... Once you activate, you may load the missing data from the other database ...

    If you have created any new tables in the SMS Tablespaces and one of those files have been deleted, it may become difficult

    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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