 |
| Special September 2016 - Managing database online backups and restores |
|---|
| Category : Admin |
| Level : Advanced |
| Back to Overview |
Thank you for joining the september cego web special.
Backup and restore ist one of the most important feature of a database system.
The challenge is to perform backups online and to keep the required downtime for a urgent restore very small.
After the restore, data consisteny of the affected data must be ensured.
To do this in a controlled way, a basic understanding of the database log file handling is required.
In this workshop, we will explore several relevant aspects to get prepared for backup and restore administration.
To perform any online backup in cego, the archive mode for a tableset must be enabled. If archive mode is enabled, the online redo logs are copied to the configured archive log destination. For redundancy reasons, several archive logs destinations could be configured. It is recommended to use different physical devices for the several log destinations. For our web special, we use the tableset lit with just one archive destionation in /usr/local/cgdb/arch So lets connect with the admin console cgadm to the database, to start with the configuration.
CGADM > add archlog LIT_A path '/usr/local/cgdb/arch' to lit; Archlog added ok ( 0.000 s ) CGADM > list archlog for lit; +-------------+----------------------+ | ARCHLOGINFO | ARCHLOGINFO | | ARCHID | ARCHPATH | +-------------+----------------------+ | LIT_A | /usr/local/cgdb/arch | +-------------+----------------------+ Tableset information ok ( 0.001 s ) |
If the log destinations have been configured, the archive mode can be set to on.
CGADM > enable archlog for lit; Arch mode enabled ok ( 0.000 s ) |
To have a clean tableset, we drop the tableset and recreate it. If you have created a virgin tableset, this is not required.
CGADM > stop tableset lit; Tableset lit stopped ok ( 4.015 s ) CGADM > drop tableset lit; Tableset lit dropped ok ( 0.002 s ) CGADM > create tableset lit; Tableset lit created ok ( 0.384 s ) CGADM > start tableset lit; Tableset lit started ok ( 0.000 s ) |
Now, the tableset starts all completed online redo logs are copied to the log destination. This can be seen, if we force a logfile switch for the tableset. For this, the corresponding tableset must be in state ONLINE.
CGADM > sync tableset lit; Tableset lit in sync ok ( 4.020 s ) |
The sync command writes a checkpoint for the tableset and completes the current online redo log. The completed log is detected by the log manager and is copied to the archive log destination. After copied succesful, the online redo log is marked as FREE You can check the status of the online redo logs with the following admin command.
CGADM > list logfile for lit; +------------------------------------+-----------+---------------------+---------------------+---------------------+ | LOGFILE | LOGFILE | LOGFILE | LOGFILE | LOGFILE | | NAME | STATUS | SIZE | OFFSET | USAGE | +------------------------------------+-----------+---------------------+---------------------+---------------------+ | /usr/local/cgdb/data/lit_redo0.log | FREE | 1000000 | 0 | 0% | | /usr/local/cgdb/data/lit_redo1.log | FREE | 1000000 | 0 | 0% | | /usr/local/cgdb/data/lit_redo2.log | ACTIVE | 1000000 | 37 | 0% | +------------------------------------+-----------+---------------------+---------------------+---------------------+ Tableset information ok ( 0.001 s ) |
If we are listing the content of the archive log destination, the first archive logs should have been written there.
bigmac > pwd /usr/local/cgdb/arch bigmac> ls -la total 8 drwxr-xr-x 3 lemke wheel 102 22 Jun 15:19 . drwxr-xr-x 13 lemke wheel 442 22 Jun 15:08 .. -rw-r--r-- 1 lemke wheel 37 22 Jun 15:19 lit-000000000001.dbf |
The numeric id in the archive logfile is the log sequence number ( LSN ), which is incremented with each log entry.
The LSN is a unique identification of each log entry inside a tableset configuration ( transaction space ).
So you should never modify or rename these files.
With the so configured log archive, the tableset is ready now for backup and restore operations.
To have some sample data in the tableset, we create a stupid table and add some rows to it.
litdev > create table t1 ( a int, b string(30)); Table t1 created ok ( 0.000 s ) litdev > insert into t1 values ( 1 , 'OLD'); 1 tuples inserted ok ( 0.000 s ) litdev > insert into t1 values ( 1 , 'OLD'); 1 tuples inserted ok ( 0.000 s ) litdev > insert into t1 values ( 1 , 'OLD'); 1 tuples inserted ok ( 0.000 s ) |
To perform an online backup for this data, we have to set the corresponding tableset to backup mode.
CGADM > begin backup for lit; Backup mode started ok ( 0.004 s ) |
In backup mode, the original of all modified datafile pages are stored to the redo log files.
In case of a database restore, these pages are first restored, before the transaction recovery starts.
So during online backup mode, the tableset can be used in a normal way including all modifying transactions.
You just have to be aware, that for checkpoints the dirty pages are written to the log files, which might reduce the performance of the
database system.
For the backup, the relevant files can be listed with an admin command
CGADM > list bufile for lit; +-------------------------------------+ | BUILEINFO | | FILENAME | +-------------------------------------+ | /usr/local/cgdb/data/lit_ticket.xml | | /usr/local/cgdb/data/lit.sys | | /usr/local/cgdb/data/lit.temp | | /usr/local/cgdb/data/lit_data.dat | | /usr/local/cgdb/data/lit_data02.dat | +-------------------------------------+ Tableset information ok ( 0.000 s ) |
These files should be now saved to any file archive. Here we use a tar archive file and for our testing purpose, we create it in the data path.
bigmac > cd /usr/local/cgdb/data bigmac > tar -cvf litbu.tar lit_ticket.xml lit.sys lit.temp lit_data.dat lit_data02.dat a lit_ticket.xml a lit.sys a lit.temp a lit_data.dat a lit_data02.dat |
In real production environment, you rather should use a scripted based method, so you might use the cgadm command line option --cmd to list the files and provide this list directly to the tar command.
tar -cvf litbu.tar `cgadm --server=bigmac --user=cgadm/secret --raw --cmd="list bufile for lit"` |
If the files have been saved succesful, the online backup mode can be finished.
CGADM > end backup for lit; Backup mode finished ok ( 0.003 s ) |
The backup history for the tableset can be reviewed with the admin console.
CGADM > list bustat for lit; +---------------------+---------------------+---------------------------------------------------+ | BUSTAT | BUSTAT | BUSTAT | | TS | BUINFO | BUMSG | +---------------------+---------------------+---------------------------------------------------+ | 22.06.2016 15:19:41 | EXTERNAL SYNC | NONE | | 22.06.2016 15:25:03 | BEGIN BACKUP | NONE | | 22.06.2016 15:29:32 | END BACKUP | NONE | +---------------------+---------------------+---------------------------------------------------+ BUStat info ok ( 0.000 s ) |
We are now ready to restore the backup back to the database. For this we first have to stop the database.
CGADM > stop tableset lit; Tableset lit stopped ok ( 4.014 s ) |
The external log manager program is set to NONE. We will discuss the usage of this program later on.
CGADM > set logmng 'NONE'; Log Manager set ok ( 0.000 s ) |
Now the tar archive can be unpacked to restore the relevant backup files.
bigmac > tar -xvf litbu.tar x lit_ticket.xml x lit.sys x lit.temp x lit_data.dat x lit_data02.dat |
Now we can start tableset recovery up to the latest transaction with the following command
CGADM > recover tableset lit; Tableset recovered to lsn 11 Recover completed ok ( 8.019 s ) |
After the recovery, the tableset should be online and ready for use. Lets check, if your stupid table is still complete
litdev > select * from t1; +-----------+-------------------------------+ | t1 | t1 | | a | b | +-----------+-------------------------------+ | 1 | OLD | | 1 | OLD | | 1 | OLD | +-----------+-------------------------------+ 3 tuples ok ( 0.000 s ) |
Well right it looks good. We add now more tuples to the table to create additional entries in the redo logs.
litdev > insert into t1 values ( 2 , 'NEW'); 1 tuples inserted ok ( 0.000 s ) litdev > insert into t1 values ( 2 , 'NEW'); 1 tuples inserted ok ( 0.000 s ) litdev > insert into t1 values ( 2 , 'NEW'); 1 tuples inserted ok ( 0.000 s ) |
We do stop now the tableset to get ready to restore the backup another time.
CGADM > stop tableset lit; Tableset lit stopped ok ( 4.016 s ) |
And again, we unpack the tar archive with the backup files.
bigmac > tar -xvf litbu.tar x lit_ticket.xml x lit.sys x lit.temp x lit_data.dat x lit_data02.dat |
A second time, we start the tableset recovery. As you can see, the target LSN has increased now.
CGADM > recover tableset lit; Tableset recovered to lsn 17 Recover completed ok ( 6.026 s ) |
Since we have performed a crash recovery, the sample table should contain all data, which we have inserted.
litdev > select * from t1; +-----------+-------------------------------+ | t1 | t1 | | a | b | +-----------+-------------------------------+ | 1 | OLD | | 1 | OLD | | 1 | OLD | | 2 | NEW | | 2 | NEW | | 2 | NEW | +-----------+-------------------------------+ 6 tuples ok ( 0.000 s ) |
This looks good.
The recovery procedure shown above performs a recovery up to the latest commited transaction. Sometimes it is required, to recover just to a special point in time in the past. Obviously, this point in time must be between any performed online backup and the current time. To start an point-in-time recovery is very similar to recover-up-to-crash. Just add the timestamp to the recover command
CGADM > recover tableset lit to '01.09.2016 12:00:00'; Tableset recovered to lsn 17 Recover completed ok ( 6.026 s ) |
The format of the time string must match to one of the customized datetime format values. Please check section Date format handling for further details, how to customize datetime formats.
Finally, we want to explore how written logfiles could be managed in way, that they can be made available for restore if required. Since the online redo log files are written to the archive log destination in a continious way, it must be ensured, that there is enough disk space to hold the written logs at least for a specific time. To avoid an overflow of the archive log destination, the logfiles are written and archived to any long term storage device ( e.g. a tape library ) For cego, we could provide a tape archiver with the following code
#!/bin/bash
# file path, where offline redo logs are written ready to get archived
ARCHPATH=/usr/local/cgdb/arch
# file path of mounted tape device
TAPEPATH=/usr/local/cgdb/tape
cd $ARCHPATH
# if no file for tape archive are available, we have to return null string
shopt -s nullglob
for f in *.dbf
do
echo File is $f
if [ ! -e "$TAPEPATH/$f" ]
then
echo -n "Archiving file $f to tape ..."
mv $f $TAPEPATH/$f
if [ $? -eq 0 ]
then
# everything is fine
echo "ok"
else
# handle tape write error
echo "not ok"
fi
fi
# sleep 1
done
exit 0
|
This job should be executed in a periodic manner (for example, every hour).
To restore the log files back to the archive destination, an external log manager is required. A scripted version for this manager might look like
#!/bin/bash
ARCHFILE=$1
ARCHPATH=$2
TAPEPATH=/usr/local/cgdb/tape
for i in 1 2 3
do
if [ -e "$TAPEPATH/$ARCHFILE" ]
then
TARGETPATH=`echo $ARCHPATH | awk -F: '{ print $1 }'`
echo "Restoring $ARCHFILE ..."
cp $TAPEPATH/$ARCHFILE $TARGETPATH
if [ $? -eq 0 ]
then
# return code 0 signals requested file could be provided in archive destination
exit 0
else
# return code 0 signals, an error occured while providing the requested archive log file
exit 2
fi
fi
sleep 1
done
# return code 1 signals end of backup
exit 1
|
Since the external log manager is directly called from the cego recovery process, the command line arguments and exit codes must match the specified conventions. For a detailed description please check section Recovery procedures in the cego users guide. The external log manager is configured with
CGADM > set logmng '/usr/local/cgdb/cglogmng'; Log Manager set ok ( 0.000 s ) |
Another way to create an online backup is to perform a synchronized file copy of all relevant files if they are all in a consistent state. This can be done in as a folow up step after a checkpoint has been written. To do this is a synchronized way, the backup script must be triggered by the checkpointing routine.
First, we have a look to the script mksnap for the synchronized backup
#!/bin/bash
TS=$1
echo "Making snapshot for tableset ${TS}"
isFirst=yes
for f in `cgadm --server=bigmac.local --user=cgadm/secret --raw --cmd="list bufile for $TS"`
do
# first file in list is ticket file, we have to ignore for snapshot backups
if [ $isFirst == "yes" ]
then
isFirst=no
else
fileList="$fileList $f"
fi
done
tar -P -cvf ${TS}snap.tar $fileList
echo "Snap shot done"
|
The script can be used in combination with the sync command
CGADM > sync tableset wespecial with '/usr/local/cgdb/mksnap webspecial'; Tableset webspecial in sync with escape command ok ( 2.106 s ) |
The backup can be used in the same way as seen above for any kind of point-in-time and recover-up-to-crash revovery procedures.