Montag, 27. Februar 2012

total abuse of technology

I had a (for my environment) unusual request:
After the migration of a Repository Database from 9i to latest 10g I was asked to keep a backup of the old DB for at least 3 years.
This does not sound very unusual, but it's not that simple in our environment. We do only keep backups for weeks to some month, worst case. I also cannot just backup the datafiles at all: The old database run on Solaris, but we are switching to Linux right now. With just some bad luck I would not have any system to restore (or open) this database backup at all.
This brought me to another solution; in my point of view it was not worth to write a blog about it, but I was asked by Leighton L. Nelson and so I write:

  1. export of the full database
    I run a simple export of the database. There is no expdp in 9i, so the choice was easy.

  2. compress the files
    the dump (and the logfile!) where tared together and compressed. Just to save space.

  3. prepare a proper store
    As mentioned above, there is no dedicated system for this purpose. So I had to prepare a place where the dump is safe. As a DBA, of course I know a good place to store data: A database!
    First a DBFS came to my mind. But the DB is in Version 10.2 - no DBFS.
    But it's quite simple to do the important steps manually:
    create tablespace old_dump datafile '+<DG>' autoextend on;
    create user old_dump identified by dump_old1 default tablespace old_dump;
    alter user old_tech_dump quota unlimited on old_dump;
    connect old_dump/dump_old1
    create table old_dump_store 
    (id integer primary key, description VARCHAR(2000), file_store BLOB) 
    LOB (file_store) STORE AS  SECUREFILE 

  4. insert the dump (and some metadata)

    There is a nice way in SQL Developer to load a file to a BLOB. It's just so simple.
    At last some words in the comment field are worth - so everyone knows what's inside the BLOB.
It still might sound strange to save the dump of an old database into it's descendant. But at the end: do you know a better place to store data than a database?


Schretzi hat gesagt…

You should not post such things readable by you personal storage admin ;)

Let me guess that you put that into a production DB (safest place?!?!), so we keep backups of 3 year old data (nothing is as old as the data of yesterday) on production high end storage?

Mathias Zarick hat gesagt…

Hi Martin,

nice post. Probably I would do this similar. :-)
You can also think about setting tablespace to read only.
And I wonder why you could use a SecureFile LOB after you stated, that DB is 10g?? :-)
Have you found a way to activate backport here?
Have a nice Day

Martin Berger hat gesagt…

you are totally right, there is no SECUREFILE in 10g! I somehow mixed up my notes.
thank you for the correction, and sorry for the confusion.