Log backup after catastrophic failure

The theory says, that a database admin should always have up to date backups... unfortunately this is not always true. It is possible that you have full database backup, you perform transaction backups every 3 hours, you even have the backup and recovery plan, but... Sybase due to some hardware failure stopped working and you cannot start it and the business waits and waits...

There is a utility called sybdumptran that allows to make a transaction log backup despite the non working sybase server.

Take a look at the following SQL code:

DISK INIT NAME ='MyDbDataDev', 
     PHYSNAME = '/opt/sap/databases/MyDbDataDev.dat', 
     SIZE=10240
GO

DISK INIT NAME ='MyDbLogDev', 
     PHYSNAME = '/opt/sap/databases/MyDbLogDev.dat', 
     SIZE=2560
GO

CREATE DATABASE MyDb
ON MyDbDataDev = '20M'
LOG ON MyDbLogDev = '5M'
GO

DUMP DATABASE MyDb TO '/home/dumps/MyDb_full.dmp' WITH INIT
GO

USE MyDb
GO

CREATE TABLE TEST(info char(30))
GO

INSERT INTO TEST VALUES('Before the 1st log dump')
GO

DUMP TRAN MyDb TO '/home/dumps/MyDb_log_1.dmp'
GO

INSERT INTO TEST VALUES('Never dumped')
GO

SHUTDOWN

As you see a new database on new devices has been created. Next we took a full backup, next user inserted one record, next the transaction low was dumped, next the user continued to insert the next record and then something went wrong and server stopped! Now you will see how to take a log dump despite stopped server:

More and more tempdb problems

Recently I have played with tempdb. I have practiced a lot of scenarios with different tempdb configuration. This caused of a big mess in the configuration as I needed to perform some activities that sometimes failed and required quick repair.

So finally I had a couple of devices used by tempdb (sometimes even non-existing) and I wanted to make my Sybase server be running again.

The first error was:

Device 'tempdb2data' (with physical name '/opt/sap/databases/tempdev2data.dat', and virtual device number 13) has not been correctly activated at startup time.  Please contact a user with System Administrator (SA) role.
server  Error: 1619, Severity: 21, State: 1
server  Could not open TEMPDB, unable to continue.

Cache: working with cache

In this post I will present the sp_cacheconfig procedure. Knowing it, in the next posts I will be able to show two common usages of the cache in the Sybase.

This command allows to define how the cache will be used. Yes, cache can be used also without knowing how this procedure works, as always blocks read from the disk will go into cache allowing to speed up future read operations. But having knowledge about this procedure you are able to design, how the memory in the cache will be used

The syntax of sp_cacheconfig is following:

sp_cacheconfig [cachename [, "cache_size[P | K | M | G]"] 
 [, logonly | mixed | inmemory_storage][, strict | relaxed]]
 [, "cache_partition=[1 | 2 | 4 | 8 | 16 | 32 | 64]"]
 [, instance instance_name]

As you can see, this command can be started without parameters. In such case only the definition of cache will be displayed. When you want to define the cache, then you should use at least two parameters:

• cache name - this is the name allowing you to reference this cache in future
• cache size - defining the size of the cache

The other parameters define how the cache will work, one of them is the most important in our case:

• logonly or mixed or inmemory_storage and the inmemory_storage is especially interesting. It will be extensively used in future examples. This type of cache allows to store the database in memory!

So for example to have a cache of size 100 MB of memory, that could be used by databases or to be more accurate to save database in memory following command should be used:

sp_cacheconfig inmem_db_cache, "100m", inmemory_storage

to display configuration use

sp_cacheconfig

If you would like to remove configuration - just set its size to );

sp_cacheconfig inmem_db_cache, "100m"

In next posts I will show the cache in 2 scenarios:

• additional temporal database in memory
• disk database preloaded into memory

Linux: How to create RAW device to be used with Sybase?

When using Sybase on Unix, than the recommended way of saving the databases is directly on disk devices and not on files (what is possible as well). There are two types of disk devices on Linux.

One is called a block device and applications use that device using block access. This means that data is read into cache and given to application in blocks. Example of a block device can be: /dev/sda2. If you issue command ll than in the result you can find, that the type of the device is "b" - block:

[root@SAP01 /]# ll /dev/sda2
brw-rw----. 1 root disk 8, 2 Jul  1 08:11 /dev/sda2

However block devices are not recommended for databases. Database should avoid the system cache. The system cache is good for normal files and in case of database, that is the database engine who should manage the caching of data. 

Unfortunately, on Linux (Fedora) the raw devices are not created automatically. So let's review a couple of commands that may help working with pure raw devices:

raw -qa

displays information regarding existing raw devices. If you did not create them, than initailly the result will be empty.

[root@SAP01 /]# raw /dev/raw/raw2 /dev/sda2
/dev/raw/raw2:  bound to major 8, minor 2

This command creates a raw device bound to the block disk device /dev/sda2. Note the arguments:

• the first one is the name of device that will be created and it must point to /dev/raw/ and must begin with raw and must end with a number
• the second is the existing block device

After issuing the command:

[root@SAP01 /]# raw -qa
/dev/raw/raw2: bound to major 8, minor 2

you will see information that now a new raw device is available. This device could be used by Sybase. Please note, that if a block device and raw device would be used simultaneously, than the data may be/ will be corrupted. Use only one type of a physical disk device a time.

More info regarding raw command:
http://www.tldp.org/HOWTO/SCSI-2.4-HOWTO/rawdev.html