Backups
LeasePak Documentation Suite NETSOL website
Home Search Help
Backups

LeasePak Server – Configuration and Maintenance

Backups

Table of Contents

Backing Up the LLDB

Top Backup Policies About Datasets

db_snapshot Native Backups

  

Backup Policies

Topics

  • LLDB
    • database system
    • database server
    • NetSol utility script
      • db_snapshot
    • dataset
    • LeasePak tables
      • notebook tables
  • Hosts
    • DBMS host
  • LeasePak
    • LeasePak instance
      • LeasePak release
  • General
    • job
      • backups
        • full backups
        • incremental backups
        • native backup utility
        • quiescent
    • OS
    • disk drive
      • filesystem
        • NFS-mounted filesystem
      • media
  • Storage segment
    • log storage segment
      • transaction log
  • Users & Roles
    • System Administrator
  • Queue Manager
    • C-ISAM files
      • $ENVDIR/data
Planning Backups
IMPORTANT NOTE
NetSol strongly recommends ...
That the System Administrator perform database backups at least daily. These backups should include regular full backups as well as properly scheduled incremental backups. With the Sybase database system, it may also be necessary to backup the transaction log periodically as well. If the administrator is unsure of the best procedures for scheduling backups, he or she should contact the NetSol Help Desk to obtain guidance.

There are a number of ways of performing backups, including:

  1. Each database system has a native backup utility. This can be used to obtain backups of not just the LLDB but also of important database system configuration information, and in a manner that facilitates the proper restoration of the entire system.
  2. Each OS has a native backup utility, or more, which could potentially be used to capture images of the devices on which the database system resides.
  3. There are third-party backup and archival packages available on all LeasePak OSs.
  4. The administrator can also use the LeasePak utility, db_snapshot. This program is the counter-part to the db_restore program discussed above.

Each method has good and bad features:

  1. database system native backup utility:
    1. Good: can perform backups that allow easier retoration of an LLDB.
      Good: can include general database system matter as well.
      Good: optmized by the vendor for that database system.
      Good: can perform backups while the database is online.
    2. Bad: does not backup the $ENVDIR/data directory by default, and may not be able to do so at all.
      Bad: backups are sometimes incompatible with database server instances on other hosts.
      Bad: cannot be used to completely restore the database system in the event of a media failure.
  2. OS native backup utility:
    1. Good: can easily backup disks containing LLDB data, as well as database system configurations and $ENVDIR/data.
    2. Bad: can be complex to use.
      Bad: not very reliable for restoring less than the entire DBMS host.
      Bad: database must be quiescent while the backup is performed.
  3. third-party backup utility, often part of a larger management tool suite:;
    1. Good: may have sophisticated ease-of-use features.
      Good: may have the capability to do the right thing in regards to backing up and restoring database systems.
      Good: may have the capability to handle the $ENVDIR/data directory correctly.
    2. Bad: Potentially has all the bad points of the first two options above.
  4. db_snapshot, a NetSol utility script:
    1. Good: db_snapshot has been optimized for use with both Sybase and Oracle.
      Good: db_snapshot understands the $ENVDIR/data directory perfectly.
      Good: resulting datasets are compatible, except some Sybase notebook tables, with any LeasePak instance of the same release version on any supported OS and on any supported database system.
    2. Bad: does not perform snapshot to backup media, only to disk or NFS-mounted filesystems.
      Bad: requires running the db_snapshot utility then archiving the resulting dataset directory to backup media.
      Bad: does not address backup and restore of entire database system, only one LLDB.
      Bad: LLDB must be quiescent while the backup is performed.

db_snapshot is useful as an alternate, supplemental form of backup. However, the primary backup solution should be a native database system backup, as only native database system backups can be used to fully recover a database system when media failure occurs.

About Datasets

Top Backup Policies About Datasets

db_snapshot Native Backups

  

Flatfile Database Contents
See About datasets.

db_snapshot

Top Backup Policies About Datasets

db_snapshot Native Backups

  

Copying database contents from the LLDB
  • Environments
    • setup_new_env
    • point
  • LLDB
    • LLDB properties
      • database type
    • database system
    • database server
    • NetSol utility script
      • db_create
      • db_restore
      • db_snapshot
    • dataset
      • level7
      • seed
    • LeasePak tables
      • notebook tables
  • Hosts
    • application host
    • DBMS host
  • LeasePak
    • LeasePak instance
      • LeasePak release
    • SETUP
  • General
    • pathname
      • absolute pathname
    • job
      • backups
        • native backup utility
        • quiescent
    • environment variable
    • OS
    • shell
      • command prompt
    • SQL
    • disk drive
      • filesystem
        • NFS-mounted filesystem
      • media
  • Storage segment
    • log storage segment
      • transaction log
  • Users and Roles
    • System Administrator
    • LeasePak administrative users
      • $NSTDBA
        • LeasePak database administrator
      • DBO
    • password
  • Queue Manager
    • C-ISAM files
      • $ENVDIR/data
Use db_snapshot copy the contents of an LLDB to a LeasePak dataset. Unlike the native backup utility, db_snapshot handles both the LeasePak table data and the required accompanying C-ISAM files. A dataset created by db_snapshot can be loaded using db_restore into any LLDB of the same LeasePak version on any supported OS and database system, with the noteworthy exception of the Sybase notebook tables, which can exceed the size supported under the equivalent Oracle tables.
db_snapshot may only be run by $NSTDBA, the LeasePak database administrator. It requires that the operator know the password for the DBO of the LLDB, which was assigned when the LLDB was created by db_create; it requires that the database server for the db-type selected when the environment was created using setup_new_env be up and running and capable of executing interactive SQL commands; it does not require the services of the Queue Manager.
As with most LeasePak db_* commands that affect a particular LLDB, db_snapshot determines the appropriate LLDB to use via the env-name parameter that points to the environment created by setup_new_env; the environment's logdb.msirc file in turn points to the LLDB.
The db_snapshot command may be run from a visitor environment, as well as the usual production and test environments, unlike db_restore because db_snapshot does not alter the LLDB.
If the snapshot name given to db_snapshot has already been used for a snapshot, the previous snapshot contents are removed at the outset, and the new contents copied in. db_snapshot does not allow the operator to snapshot into the seed or level7 datasets.

Common Usage

The following is the most common example of using db_snapshot:

db_snapshot env-name dataset-name [-p proc-count] [[-t|-x] table-list]

The following is the most common way of using db_snapshot using the Custom BCP Query option, -q:

db_snapshot env-name dataset-name -q queryfile':'queryid

where

  • env-name is the environment containing the LLDB whose data is to be copied into the dataset. See also About Datasets.
  • dataset-name is the directory where a group of files containing a some or all of the data in a LeasePak logical database (LLDB) is placed, and must be one of:
  • [-p proc-count], where proc-count is an integer from 1 to 15, which determines the number of concurrent processes that will be used to move the data from the LLDB into the dataset. The [...] indicates that this parameter is optional; if omitted, the number of concurrent processes defaults to 4, as if -p 4 had been typed on the command line.

    Note that the -p option is ignored if the -q option is present.

  • Other Inputs
    • db_snapshot will require that the operator enter the DBO's password when prompted. This password was assigned to the DBO when the LLDB was built using db_create.
    • [-t|-x "tables"], where "tables" is a space-separated list of LeasePak table names to be dumped (-t ooption) or excluded (-x option) from the dataset. These two options and the -q cannot be used together. Only one of -t,-x, or -q may be used at any one time. If omitted, db_snapshot will dump the entire LLDB.
    • ENHANCEMENT IN V6.5A
      In LeasePak Release version 6.5a, an enhancement to db_snapshot (and corresponding changes to db_restore) was made allowing the use of SQL to selectively include only certain tables or rows in the finished dataset. This is done via the -q queryfile':'queryID option. This option is not allowed in the command when the -t or -x option is present. The structure of the queryfile is explained in the document Custom Bcp Queries.
IMPORTANT NOTE
Creating snapshots of live data

The LLDB must be quiescent at the time of the snapshot, or else the resulting data may not be consistent. There might also be lock contention, where either users using the LLDB or db_snapshot is prohibited from moving forward because of contention in accessing the same data. This should only be temporary, but could slow both processes down. Quiescent means that no one is making any changes to the data in the LLDB during that timeframe.

It is best if the users are kept out of the LLDB during the snapshot process.

CRITICAL NOTE
Backing up $ENVDIR/data

Every LeasePak environment has a data subdirectory on the application host. When pointing to an environment, the environment directory is symbolized by the environment variable $ENVDIR. LeasePak creates a number of C-ISAM files in this directory that are intrinsic parts of the LLDB. Any complete backup must backup the *.dat and *.idx files in the environment's data directory.

With a split host architecture, the snapshot and restore processes will execute their script and binary code on the application host, and communicate with the database server via remote procedure calls to perform the reads of the database data. The database server will package up the data read, and transmit it via the network to the application host, where it will arrive at the executing snapshot program, which will then write it out to a file in the dataset.

This network overhead might lead to performance degradation. If a sufficiently fast local network between the two hosts is provided, the loss may be negligible. It is also possible to install another copy of the same LeasePak release on the DBMS host, just to avoid this problem.

db_snapshot Worksheet

Note the following values for running db_snapshot:

Name Description Your Value Notes
env-name environment name Must have been previously created using setup_new_env, and must contain a valid LLDB created using db_create
dataset dataset name Must be a new or existing dataset directory in $datasets or the absolute pathname of a directory on the application host that can contain a dataset. See About Datasets.
-p proc-count # of concurrent processes optional parameter; if given must be 1 to 15, default is 4
DBO's password LLDB owner's password db_snapshot will prompt for this. This password was assigned when the LLDB was created using db_create
Running db_snapshot

Log on the application host as $NSTDBA

Either run db_snapshot with the common usage described above, or click here to see the complete syntax of db_snapshot.

[nsdba76a:~] db_snapshot prod level7 -p 10

The command will produce a display similar to the screen print below. Enter the DBO's password when prompted. For brevity we have deleted several lines (shown by "...") from this screen print; the full output of db_snapshot can be seen by clicking here. 

  [nsdbanna:~] db_snapshot prod prod20111013 -p 10
  2011-10-13 13:09:13 db_snapshot: Unload database (prod)lpr_prod into dataset
	  prod20111013
  2011-10-13 13:09:13 db_snapshot: Running commands as DBO: lpr_prod

  Database Owner 'lpr_prod' password: database owner's password
  2011-10-13 13:09:25 db_snapshot: Creating prod20111013 directory...
  2011-10-13 13:09:26 db_snapshot: Getting list of user tables sorted by size...
  2011-10-13 13:09:55 db_snapshot: Distributing tables into 10 portions...
  2011-10-13 13:10:02 db_snapshot: Performing bcp out for the tables...
  rcc 1
  req 2
  rcr 3
  rst 4
  rtx 5
  rglc 6
  rls 8
  rzga 7
  rlsa 9
  msg 10
  rlsb 5
  rha 7
  rtr 1
  rlo 3
  rsc 2
  reqa 6
  rtd 9
  ...
  ruet 9
  rsq 4
  Portion 6 done Tue Oct 13 13:10:13 PDT 2011
  rxr 10
  rxrp 1
  rub 4
  ruqt 2
  run 8
  rwt 7
  rxup 3
  runt 9
  Portion 10 done Tue Oct 13 13:10:13 PDT 2011
  Portion 5 done Tue Oct 13 13:10:13 PDT 2011
  rxp 9
  ruj 4
  rxf 8
  rxu 2
  Portion 3 done Tue Oct 13 13:10:13 PDT 2011
  Portion 1 done Tue Oct 13 13:10:13 PDT 2011
  rzu 9
  rus 4
  rzp 7
  rzq 8
  Portion 2 done Tue Oct 13 13:10:13 PDT 2011
  ryr 4
  Portion 7 done Tue Oct 13 13:10:13 PDT 2011
  Portion 8 done Tue Oct 13 13:10:13 PDT 2011
  Portion 9 done Tue Oct 13 13:10:13 PDT 2011
  Portion 4 done Tue Oct 13 13:10:14 PDT 2011
  2011-10-13 13:10:14 db_snapshot: End
  Unload complete; check /opt/nst/v62a/log/prod.log for any errors
  [nsdbanna:~]

Native Backups

Top Backup Policies About Datasets

db_snapshot Native Backups

  

Periodic Backup of Database Systems
What does "Native" mean?
Native, in this context, means that the backup software came with the database system, and is able to manipulate the system in ways that non-native products cannot. This insures that the integrity of the backup software is equal to the integrity of the DBMS.
IMPORTANT NOTE
Native DBMS Backups

Because db_snapshot does not backup many of the important structural elements of the database systems, and cannot be used to completely restore the database system in the event of media failure, NetSol recommends that the administrator periodically use native database system commands to back up all LLDBs and transaction logs, including ones used by the database system. For more information, refer to the appropriate documents below:

Reference Documentation
Oracle 11gR2
  • User's guide: Oracle Database Backup and Recovery User's Guide 11gR2
  • Reference guide: Oracle Database Backup and Recovery Reference 11gR2
  • Introduction: Oracle Database 2 Day DBA 11gR2:
    Chapter 9 Performing Backup and Recovery
Oracle 9iR2
  • Oracle 9i Backup and Recovery Concepts
  • Oracle 9i Backup and Recovery Strategies
  • Oracle 9i User-Managed Backup and Recovery Guide
Sybase 15.5
Adaptive Server Enterprise 15.5 System Administration Guide:
  • Developing a Backup and Recovery Plan
  • Backing Up and Restoring User Databases
  • Restoring the System Databases
Sybase 12.5
Adaptive Server Enterprise 12.5 System Administration Guide:
  • Developing a Backup and Recovery Plan
  • Backing Up and Restoring User Databases
  • Restoring the System Databases
Sybase 12.0
  • System Administration Guide

Top Backup Policies About Datasets

db_snapshot Native Backups