Journaling or Checkpoint? 
Author Message
 Journaling or Checkpoint?

We have Ingres 6.4 with SUNOS 4.1.3. I have done some Tests to install
an effective Backup for users Databases.

When do you recommend journaling? In case of user-mistakes as deleting
values it seems better to keep some checkpoints.

How can I keep the last 3 checkpoints and delete the older one?

I would be grateful for your advice |

Hanna



Mon, 23 Dec 1996 15:21:42 GMT
 Journaling or Checkpoint?

: We have Ingres 6.4 with SUNOS 4.1.3. I have done some Tests to install
: an effective Backup for users Databases.
: When do you recommend journaling?

It depends on your type of user environment.  If your database is part of a
transaction-based system i.e. high volume database updates/inserts/deletes,
then I would probably turn on journaling and do checkpoints often.  If your DB
is part of a read-only decision support system, then maybe infrequent check-
pointing would suffice.  I prefer binary unloaddb to checkpointing for DS
DB's, because I can restore just one table if I need to (for that one user
who drops the wrong table every week.)

: In case of user-mistakes as deleting
: values it seems better to keep some checkpoints.

The big disadvantage of checkpoints is trying to get the entire user community
to agree to roll back the entire database to some point in time in the past.
Enevitably you'll have one or two users who will say `why should I have to
re-enter my data just because some pin head screwed up HIS table!'...

I like to do unloaddb's once a day, (disk is cheap), even for DB's with a
moderate amount of updating going on.  

IMHO.
--
 Tom Gillespie                                                 _   _
 Harris Semiconductor                                         | | | |
 Manufacturing Systems                                       _/\/\/\/
 Palm Bay, FL                                                 | | | |



Tue, 24 Dec 1996 10:24:49 GMT
 Journaling or Checkpoint?

Quote:

>We have Ingres 6.4 with SUNOS 4.1.3. I have done some Tests to install
>an effective Backup for users Databases.

>When do you recommend journaling? In case of user-mistakes as deleting
>values it seems better to keep some checkpoints.

>How can I keep the last 3 checkpoints and delete the older one?

>I would be grateful for your advice |

>Hanna

Hanna,

  If you EVER plan to have heap tables with indices on the primary keys or
other columns, I STRONGLY discourage using journalling.  During rollforwarddb,
every update to that table will require a table scan.

  This is an Ingres 6.4 "architectural limitation", and is supposedly fixed
in OpenIngres 1.0, that legendary software I'm not allowed to trial.

Cheers,

Michael Leo
ADC Telecommunications
(612) 936-8305 (voice)



Mon, 23 Dec 1996 20:44:26 GMT
 Journaling or Checkpoint?
Hanna,

  : We have Ingres 6.4 with SUNOS 4.1.3. I have done some Tests
  : to install an effective Backup for users Databases.

  : When do you recommend journaling? In case of user-mistakes
  : as deleting values it seems better to keep some checkpoints.

Two critical factors:
  - you are able to go OFF-LINE (locked for users) or have to
    stay in production mode (on-line)
  - you can recover a WHOLE database or are restricted to
    local reparation of the troubled parts (tables, rows).

Situations:
  - crashes: mostly complete DB's have to be recovered
  - user mistakes: mostly parts of a DB are concerned.

Facilities:
  - ckpdb        : the Ingres backup mechnism: for performing
                   checkpoints and starting journals
  - rollforwarddb: for recovery DB's until specified moment
  - unloaddb     : the Ingres archive mechanism
  - auditdb      : reading the journals interactively.

Restrictions:
  - though checkpointing is an on-line facility,
    rollforwarddb is not!
  - you have to take ALL journals or none; specification of parts
    of the journals (e.g. some table) is not possible;
  - the transaction logfile doesnot registrate based on individual
    mutations, but based on recovery from the past
  - auditdb cannot show on-line transactions stored in the
    dmp directory

Warnings:
  - don't forget to SET JOURNALING on all tables concerned (SQL)!
  - don't put ckp+jnl+dmp directories on the same disk as the
    transaction logfile!
  - do use ckpdb to each DB individually, e.g. in a foreach loop
    (ckpdb -s is not always proof against inconsistant DB's)
  - checkpoints may be Ingres & OS version related; the user data
    part of exports with unloaddb (-c) less!

Conclusions:
  - for off-line recovery of complete DB do use checkpointing +
    journaling (all with ckpdb')
  - for on-line recovery of complete DB do use Ingres Replicator
  - DB auditdb or rollforwarddb are neither reliable nor intended
    for partial recovery of DB's, for off-line cases yet available;
    better:
    collect all relevant mutations in dedicated tables (by database
    procedures or application) and journal them all;
  - to be more resistant to Ingres or OS upgrades and some DB incon-
    sistencies think about archiving with unloaddb (-c) + copyapp out.

  : How can I keep the last 3 checkpoints and delete the older one?

Such an option would be nice.
Now we do it by hand (carefully). If the checkpoint # differ more then
`16' from the actual value, it is even the only way (ckpdb -d refuses)!
Another way is copying all checkpoints to another location, out off
the ..|ingres|.. tree. But don't forget also copying out the ..|dmp|..
directory. Then you can use use `ckpdb -d' for deleting all old
checkpoints in the Ingres-owned location.

Jan.

#                                =                                      #
 A. van Setten                   | post  :  PO Box 354 / 2600 AJ DELFT

 Computing Centre                | phone :  +31 (0)15-78 4821 / 78 2000
 The Netherlands                 | fax   :  +31 (0)15-78 3787
#                                =                                      #



Mon, 23 Dec 1996 21:39:38 GMT
 Journaling or Checkpoint?

: The big disadvantage of checkpoints is trying to get the entire user community
: to agree to roll back the entire database to some point in time in the past.
: Enevitably you'll have one or two users who will say `why should I have to
: re-enter my data just because some pin head screwed up HIS table!'...

If you are correcting individual errors, then it is sometimes feasible
to audit the journal files for the before-image and make the corrections
using that.  It is sort of labour-intensive and it may require too much
detective work if you don't know WHO made the error you are looking
for, but it is an option to consider instead of rolling back the
whole database.

========================================================================


BKB Engineering Ltd.,                      (MIME-capable mail agent)
11211-76th Avenue,                
Edmonton, Alberta                          Tel: (403)438-2531 (office)
T6G 0K2                                         (403)437-0860 (home)
Canada                                     FAX: (403)437-3367

========================================================================



Tue, 24 Dec 1996 12:32:58 GMT
 Journaling or Checkpoint?

: Warnings:
:   - don't forget to SET JOURNALING on all tables concerned (SQL)!
:   - don't put ckp+jnl+dmp directories on the same disk as the
:     transaction logfile!
:   - do use ckpdb to each DB individually, e.g. in a foreach loop
:     (ckpdb -s is not always proof against inconsistant DB's)
:   - checkpoints may be Ingres & OS version related; the user data
:     part of exports with unloaddb (-c) less!

To Jan's very exhaustive answer I would just add one additional
step/warning.  Once you have SET JOURNALING on a table, you MUST
run ckpdb with the +j option to ACTUALLY START the journaling
happening.  This need be done only once, after you change the
journaling status of a table, and it requires an exclusive lock on
the database for that checkpoint.

I don't think the manuals really make much of a point of this, and
about two months ago I had to help someone salvage what he could of
a database that he thought was being journalled because he'd
done the SET JOURNALING.

========================================================================


BKB Engineering Ltd.,                      (MIME-capable mail agent)
11211-76th Avenue,                
Edmonton, Alberta                          Tel: (403)438-2531 (office)
T6G 0K2                                         (403)437-0860 (home)
Canada                                     FAX: (403)437-3367

========================================================================



Tue, 24 Dec 1996 12:59:39 GMT
 Journaling or Checkpoint?

Quote:

>The big disadvantage of checkpoints is trying to get the entire user community
>to agree to roll back the entire database to some point in time in the past.
>Enevitably you'll have one or two users who will say `why should I have to
>re-enter my data just because some pin head screwed up HIS table!'...

So save their data before the rollback, then reload it.

Quote:
>I like to do unloaddb's once a day, (disk is cheap), even for DB's with a
>moderate amount of updating going on.  

Not a bad idea.

        Darrin
--
M Darrin Chaney, Senior Database Programmer, University Computing Services, IU


"I want- I need- to live, to see it all..."



Wed, 25 Dec 1996 02:03:57 GMT
 Journaling or Checkpoint?

Quote:

>: Warnings:
>:   - don't forget to SET JOURNALING on all tables concerned (SQL)!

>To Jan's very exhaustive answer I would just add one additional
>step/warning.  Once you have SET JOURNALING on a table, you MUST
>run ckpdb with the +j option to ACTUALLY START the journaling
>happening.  This need be done only once, after you change the
>journaling status of a table, ...

Roy mentions an important point. I would add an additional
warning and have a related question.

His note suggests we have repeat the +j option after each next
change of journaling status. No! Moreover it could even be done
BEFORE. Apart from the [+-]j option, the ckpdb command itself is
always required.
After you created additional tables and changed the journaling
status of those tables, there is NO need to repeat the +j option:
If a database is already journaled (look with `infodb <DB>)',
a default checkpoint with `ckpdb' (without any +j or -j) is enough,
takes also in account all new tables and prevents you from
exclusive locks.

As a matter-of-fact `ckpdb' combines three functions:
   1. controling the JNL status of the DB
   2. controling the JNL activation of individual tables
   3. refreshing the backup

If I'm reading DBA Manual, pp 15-11, correctly, there is even a simple
way without going off-line and locking at DB-level:
   1. createdb ...
   2. ckpdb +j ...                                    [JNL status DB]
   3. SQL: CREATE TABLE ... with journaling           [JNL activ tables]
               some System Catalogs would be locked exclusively
               now all tables are journaled immediately
   4. ckpdb  ...                                      [refresh backup]

Is my interpretation correct?

Thanks,
Jan.

#                                =                                      #
 A. van Setten                   | post  :  PO Box 354 / 2600 AJ DELFT

 Computing Centre                | phone :  +31 (0)15-78 4821 / 78 2000
 The Netherlands                 | fax   :  +31 (0)15-78 3787
#                                =                                      #



Tue, 24 Dec 1996 17:21:04 GMT
 Journaling or Checkpoint?

Quote:


>>: Warnings:
>>:   - don't forget to SET JOURNALING on all tables concerned (SQL)!

>>To Jan's very exhaustive answer I would just add one additional
>>step/warning.  Once you have SET JOURNALING on a table, you MUST
>>run ckpdb with the +j option to ACTUALLY START the journaling
>>happening.  This need be done only once, after you change the
>>journaling status of a table, ...

>Roy mentions an important point. I would add an additional
>warning and have a related question.

>His note suggests we have repeat the +j option after each next
>change of journaling status. No! Moreover it could even be done
>BEFORE. Apart from the [+-]j option, the ckpdb command itself is
>always required.
>After you created additional tables and changed the journaling
>status of those tables, there is NO need to repeat the +j option:
>If a database is already journaled (look with `infodb <DB>)',
>a default checkpoint with `ckpdb' (without any +j or -j) is enough,
>takes also in account all new tables and prevents you from
>exclusive locks.

>As a matter-of-fact `ckpdb' combines three functions:
>   1. controling the JNL status of the DB
>   2. controling the JNL activation of individual tables
>   3. refreshing the backup

>If I'm reading DBA Manual, pp 15-11, correctly, there is even a simple
>way without going off-line and locking at DB-level:
>   1. createdb ...
>   2. ckpdb +j ...                                    [JNL status DB]
>   3. SQL: CREATE TABLE ... with journaling           [JNL activ tables]
>               some System Catalogs would be locked exclusively
>               now all tables are journaled immediately
>   4. ckpdb  ...                                      [refresh backup]

>Is my interpretation correct?

>Thanks,
>Jan.

>#                                =                                      #
> A. van Setten                   | post  :  PO Box 354 / 2600 AJ DELFT

> Computing Centre                | phone :  +31 (0)15-78 4821 / 78 2000
> The Netherlands                 | fax   :  +31 (0)15-78 3787
>#                                =                                      #

Except that ckpdb +j requires an exclusive lock on the db ...

Michael Leo
ADC Telecommunications
(612) 936-8305 (voice)



Fri, 27 Dec 1996 22:48:22 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Table journaling and checkpoints - +j only needed once per DB (fwd)

2. Journaling NOT enabled after next checkpoint

3. checkpoint and truncate log on checkpoint

4. checkpoint and truncate log on checkpoint

5. big difference in start checkpoint and end checkpoint

6. Extended checkpoint times (Blocked Checkpoint)

7. journaling capability

8. unlimited undo/journaling

9. journaling in contrib ...

10. Fwd: Re: journaling in contrib ...

11. journaling

12. Journaling


 
Powered by phpBB® Forum Software