Recovering failed master database

As always, to recover a server, you need to have a backup!

USE master
GO
DUMP DATABASE master to '/home/dumps/master.dmp'
GO
SHUTDOWN

Here we additionaly have stopped the server to make it a little destroyed... Here you can check where the masterdevice exists and how to change its name:

[sap@SAPSybase install]$ ls
core.jsa_20161016_210528.1147  core.jsa_20170219_172003.1157  core.jsa_20170309_224222.1180  RUN_SAP01_BS       SAP01.log   veritas
core.jsa_20170219_121910.1154  core.jsa_20170219_173024.1170  core.jsa_20170327_022202.1180  SAP01_BS.log       showserver
core.jsa_20170219_170522.1147  core.jsa_20170219_180321.1181  RUN_SAP01                      SAP01_JSAGENT.log  sybhauser
[sap@SAPSybase install]$ more RUN_SAP01
#!/bin/sh
#
# SAP ASE page size (KB):       4k
# Master device path:   /opt/sap/data/master.dat
# Error log path:       /opt/sap/ASE-16_0/install/SAP01.log
# Configuration file path:      /opt/sap/ASE-16_0/SAP01.cfg
# Directory for shared memory files:    /opt/sap/ASE-16_0
# License properties file:      /opt/sap/ASE-16_0/sysam/SAP01.properties
# Interfaces file directory:    /opt/sap
# SAP Adaptive Server Enterprise name:  SAP01
#
/opt/sap/ASE-16_0/bin/dataserver \
-d/opt/sap/data/master.dat \
-e/opt/sap/ASE-16_0/install/SAP01.log \
-c/opt/sap/ASE-16_0/SAP01.cfg \
-M/opt/sap/ASE-16_0 \
-N/opt/sap/ASE-16_0/sysam/SAP01.properties \
-i/opt/sap \
-sSAP01 \

[sap@SAPSybase install]$ ls /opt/sap/data
empty  master.dat  sybmgmtdb.dat  sybsysdb.dat  sysprocs.dat  tempdbdev.dat
[sap@SAPSybase install]$ mv /opt/sap/data/master.dat /opt/sap/data/master.dat.org
[sap@SAPSybase install]$ ls /opt/sap/data
empty  master.dat.org  sybmgmtdb.dat  sybsysdb.dat  sysprocs.dat  tempdbdev.dat

Of course now Sybase cannot start:

Restore database form a damaged media

In the following example you will see how to recover from a damaged media failure. Let's assume, we have a database named A1 with one database file and one log file. The files are located on different devices (in our case on two separate disk files).

USE A1
GO

SELECT * FROM TestTran
GO

DUMP DATABASE A1 TO '/home/dumps/A1_Full.dmp'
GO

INSERT INTO TestTran (SomeText) VALUES ('The transaction that was not backed up')
GO

SHUTDOWN

The table TestTran contained already 3 rows. Here additional new row has been added, but it was never dumped neither to a full database dump, nor to log dump. After that server was stopped. This will allow us to perform some destructive activities on the database file, however in normal situation this could be a disk failure.

Now in operationg system let's rename the data file for the database. Finally the files of the database devices are like here:

[root@SAPSybase]# ll /opt/sap/databases/a1
total 67584
-rw-r-----. 1 sap sap 62914560 Apr 23 16:49 a1.old.mdf
-rw-r-----. 1 sap sap  6291456 Apr 23 16:48 a1.trn

After this damage Sybase can be started again:

cd /opt/sap/ASE-16_0/install
../bin/startserver -f ./RUN_SAP01

Already during the startup, and also in the error log following error will be displayed:

Sybase ASE dump history

This is good to have backup, but it is also good to know which backups you have! By default the backup history is not enabled in Sybase, so immediately execute:

sp_configure 'enable dump history', 1

This is an  online operation, so no restart is required. Having the option "on", after some backups check the following query:

sp_dump_history @operation = 'list', @database_name = 'ALFA'

The output can  be similar to:

After enabling the history, consider having also a job that purges the old backup history records. For this @operation should be equal 'purge'. The history is saved in a file. The path to the file is returned by @operation listfiles. This is just a text file that can be dispalyed also using the operating system commands:

[root@SAPSybase ASE-16_0]# cd /opt/sap/ASE-16_0
[root@SAPSybase ASE-16_0]# more dumphist

Result is like:

Loading transaction dumps. Point in time restore

The load operation of log files is not so boring as the dump operation, because:

• during the load operation, the correct sequence of dump files needs to be loaded
• the databases to be loaded, needs to be created first, and it's size must be big enough to accommodate the backups that should be restored
• the restore can stop at some, chosen by you date and time
• the last command during the restore is to ONLINE the database

Let's take look at the following example:

USE ALFA
GO

CREATE TABLE TestTran
(
   id INT IDENTITY PRIMARY KEY,
   SomeText NVARCHAR(300),
   Date DATETIME DEFAULT GETDATE()
)
GO

DUMP DATABASE ALFA TO '/home/dumps/alfa_full.dmp' WITH INIT
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the first transaction')
GO
DUMP TRAN ALFA TO '/home/dumps/alfa_log1.dmp'
GO
SELECT * FROM TestTran
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the second transaction')
GO
--wait a moment, remember the current time (in between the inserts)
SELECT GETDATE()
GO
INSERT INTO TestTran (SomeText) VALUES ('This is the third transaction')
GO
DUMP TRAN ALFA to '/home/dumps/alfa_log2.dmp'
GO

SELECT * FROM TestTran
GO

DUMP TRANSACTION - command and options

All the transactions before being saved in the database, first need to be put in the transaction log. Of course with time the transaction log would fill up, so the old, closed transactions need to be removed from the log. To do this you can choose one of the solutions:

• set database option 'trunc log on chkpt', what is good on test systems, but not in production. Why? Because on test systems we don't care about the data itself, while on production we would like to have possibility to restore the database to any point in time, so we need to have all the transactions somewhere. In case the option 'trunk log on chkpt' has been set, when Sybase saves data in the database (checkpoint finishes) all the transactions already saved and closed by users can be and are removed from the log. So the log can fill up only if someone started a really long or huge transaction.
• from time to time perform log dump, and monitor the space in the logs. 

The syntax for log backup is:

dump tran <db_name> to <device_name>
[, stripe on <device_name>...]
[with <options>]

This command usually takes all the transactions committed already by the user and saves them on the device. As the data is already saved somewhere it can be removed from the transaction log, making space for other transactions. In case the transaction log would not be executed on time, the log can become full and all the transactions may be suspended or rolled back depending on database option named  'ABORT TRAN ON LOG FULL', what was already described in Sybase log space exhausted, transactions frozen -what to do?

Let's take closer look to the options that can be added to the dump transaction command

dump tran db_name with truncate_only

This command causes the log being truncated, without being actually backed up! Use it only if you don't care about the data. You can also try this command when you have problem with the full log in the database. After truncating the log, checkpoint will be written to the log, so if you have problem with full log, this command may fail. You don't need to specify the device name, as the backup will not be done. Remember to perform new full backup of the database, after this operation, as the backup chain will be broken at this moment.