From: melkor!valandil!mal (Michael A Leo)
Subject: Re: cloning a database
Organization: National Radio Astronomy Observatory
Date: Wed, 30 Sep 1992 14:02:31 GMT


	From: uunet!curta.cc.columbia.edu!alan@melkor (Alan Crosswell)
	Subject: cloning a database
	To: info-ingres@math.ams.org
	
	Is there any way to use a checkpoint of a database to clone it?  I want to
	avoid having to do an unloaddb and reload of a large database which I want
	to clone into a test copy.  Given that I've already taken a checkpoint of
	my db, it seems natural to want to just use that tar file to populate the clone
	rather than copying all those tables out again....
	/a
	
I have expressed this need to Ingres several times, and they cannot 
seem to understand why someone would want to do this.

Typical of an organization that doesn't rely on their own product
to do their own work. (Notice how few reliability problems toilets
have;-}

I have come up with a method to do this that kills two birds with
one stone.  My method allows

    a) one to safely back up a database in the event of complete
       disk system failure, including loss of the .cnf, .dmp, and 
       .jnl files.

    b) one to "clone" the production database on another machine 
       for engineering use.

What I have NOT figured out how to do is clone the database on the
same machine, but I'm sure my method + fiddling with the .cnf file
would accomplish this.

My method works as follows:

    1) Use ckpdb -m<device> to back the database up to tape.
       This can be either an on or off line backup.
       With a good ckptmpl.def file, one can backup multiple
       locations to a single tape, continuing on another tape
       if necessary. (I have done this on both VMS and SunOS (UNIX))

    2) Backup the journal and dump areas to the tape(s).  Under
       VMS this may require privileges to read this directories,
       under UNIX, one can use the owner and group bits in a 
       special script to accomplish this.  I append these backups
       to the same tape as the database backup.

       DO NOT DO THIS STEP FIRST!  YOU NEED TO BACKUP THE 
       DUMP AND JOURNAL AREAS AFTER THE CKPDB UPDATES THEM!

       If you use 8mm or DAT you can probably fit all of the 
       above on a single tape.  I am stuck with 1/4" 150MB 
       cartridge under SunOS, but using bar and compression
       I can usually get 200-250 MB databases onto a single
       tape.

    3) Make sure you know how the database was created (default
       locations, etc.) and the journalling state of the database.
       Make sure you know all the locations it is extended to.

So cloning and recovering from a nasty disk failure are the same!
Just

    1) Use createdb and accessdb to make an empty database of 
       exactly the same characterics as the one you wish to clone.
       Make sure the default areas are identical, and all the 
       db is extended to all the same location names.  Watch out
       on UNIX, as some of the pathnames are hardcoded in the 
       .cnf file.

       IT MUST BE NAMED THE SAME UNLESS YOU FIGURE OUT HOW TO
       MUCK WITH THE .CNF FILE.

   1a) Make sure the journalling state is the same.  You
       need to do a checkpoint to turn on journalling 
       if it was on when you backed up.

    2) Make sure no-one is mucking with the db.

    3) Wipe out the contents of the data, jnl, dmp, and ckp
       directories.

    4) Read the jnl and dmp directories from the end of the 
       tape into their proper directories.

    5) Copy the .cnf file from the dump area to the default
       data area (necessary under certain 6.3 versions of 
       Ingres on certain platforms)

    6) Rewind the tape.

    7) Run rollforwarddb.

    8) If you are recovering to a production system of
       critical importance, I would suggest you take
       and OFFLINE checkpoint at this point, turning 
       journalling on or off as desired.

I have done this many, many times, and it works for me (at least 
for 6.4 and 6.3 under VMS and SunOS)

However, I all disclaimers apply!

Mike Leo
ADC Telecommunications
mike.leo@adc.com

P.S. Do not use "REPLY" as my mail header is hosed.
Newsgroups: comp.databases.ingres