Sybase Installation Failed

Almost at the end of the installation, installation fails with error and aborts the installation of the Sybase. The situation repeats and ends always in the same way.What can cause this error?

The installation of Sybase is indeed tricky. Before I succeeded with it, I have failed 1000 times. The successful one installation was the 1001st trial :)

Sybase listener is not starting with the new settings in interfaces file

One of the final step in Sybase installation can be change in the port that the server binds to, or adding a specific network interface, where sybase should listen on. This can be done using the sp_listener procedure and change in the interfaces file. Here,  there is an example of interfaces file and the failed command sp_listener. We want to allow Sybase to listen on the external interface 192.168.10.20:

SAP01
        master tcp ether SAP01 5000
        master tcp ether 192.168.10.20 5000
        query tcp ether SAP01 5000

SAP01_BS
        master tcp ether SAP01 5001
        query tcp ether SAP01 5001

SAP01_XP
        master tcp ether SAP01 5002
        query tcp ether SAP01 5002

SAP01_JSAGENT
        master tcp ether SAP01 4900
        query tcp ether SAP01 4900

and the port change from within of Sybase:

[root@SAP01 ~]# su - sap

-bash-4.3$ isql -Usa -SSAP01 -Ppassword

3>
4> sp_listener 'status'
5> go
 proto  host  port  status
 -----------------------------------------------------
 tcp   SAP01  5000  active

DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

1> sp_listener 'start','192.168.10.20:5000'
2> go
Msg 15936, Level 16, State 1:
Server 'SAP01', Procedure 'sp_listener', Line 107:
An error was encountered when starting a listener with protocol tcp, host
192.168.10.20, port 5000.
Msg 15038, Level 16, State 1:
Server 'SAP01', Procedure 'sp_listener', Line 107:
The specified port 5000 is invalid.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 1)"

Well, one line in the configuration looks sometimes differently.  In my case in the interfaces file there was no entry for:

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.

Error: ninit: bind, Cannot assign requested address

When Sybase starts than it binds to network interfaces as defined in master entires of interfaces file under the server name. For example for the server SAP01, the colored entries determine network interfaces where sybase will bind:


SAP01
        master tcp ether localhost 5000
        query tcp ether localhost 5000
        master tcp ether 192.168.137.100 5000

SAP01_BS
        master tcp ether localhost 5001
        query tcp ether localhost 5001


SAP01_XP
        master tcp ether localhost 5002
        query tcp ether localhost 5002


SAP01_JSAGENT
        master tcp ether localhost 4900
        query tcp ether localhost 4900

However sometimes network was not ready when server was started and than following entries can be started in the errorlog:

network name localhost, interface IPv4, address 127.0.0.1, type tcp, port 5000, filter NONE
network name 192.168.137.100, interface IPv4, address 192.168.137.100, type tcp, port 5000, filter NONE
ninit: bind, Cannot assign requested address

Probably server restart will solve the issue, however you may decide to use an online operation:

Start following commands:

sp_listener status
GO

sp_listener start, '192.168.137.100:5000'
GO

sp_listener status
GO

The 'status' procedures just display the interfaces where sybase is listening. The 'start' command performs the binding operation. Values of IP and port should be the same as in the interfaces file.

In the same time in the errorlog following successful message should be recorded:

network name 192.168.137.100, interface IPv4, address 192.168.137.100, type tcp, port 5000, filter NONE
Listener with protocol tcp, host 192.168.137.100, port 5000 started.

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 log space exhausted, transactions frozen -what to do?

The main reason for the log file existence is logging all the user transactions incoming to the database. Every transaction needs to go through the transaction log. The main reason of troubles related to log is a full log condition. Indeed, if the log is full, we cannot execute any transaction and all the activity needs to be halted. The log should be big enough to accommodate all the transactions. Transaction can be stored longer or shorter depending on the settings of database

• if the database has option 'truncate on checkpoint', whenever transaction has been finished, after a CHECKPOINT, when all the modified by this transaction pages have been saved in the database, the log entries are not longer required
• if the option is not set, then first a backup of the log needs to be taken. After transaction has finished, and dbo has performed the backup of the log, the space in the log can be released.

Let's consider following example:

CREATE DATABASE Beta ON
BetaDataDev='10M'
LOG ON BetaLogDev='5M'
GO

USE Beta
GO

CREATE TABLE TestTran (ID INT IDENTITY PRIMARY KEY, SomeText NVARCHAR(1000))
GO

BEGIN TRAN
    WHILE(1=1)
    BEGIN
        DECLARE @info VARCHAR(100)
        INSERT TestTran VALUES(@info)
        SET @info = Convert(VARCHAR(100),GETDATE())
        PRINT  @info
    END

DISK INIT error: open device failed

On modern and secure operating systems, everything is secured with permissions and never production services are started using a high privileged accounts. That is good, as the systems are better secured and even one compromised service will not cause failing the entire server or application. On the other hand administering such a good secured system can become nightmare, because you will ever and ever receive error messages, when the permissions are not set correctly.

Let's look at following example. You want to create a new raw device for SAP Sybase databases:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

When starting the command following error is shown:

DISK INIT encountered an error while attempting to open/create the physical file for device '/deb/sdb1'. Consult the Adaptive Server error log for more details. For the cluster edition, check the error log of each instance.

So let's look into the error log:

tail /opt/sap/SAP-16_0/install/SAP01.log

What can be found there?

00:0006:00000:00026:2017/03/19 10:47:47.73 kernel  basis_dsizecheck: attempt to open device '/dev/sdb1' failed, system error is: Permission denied

What does it mean? Sybase is running on the system on some specific account. This account does not have access to the device file /dev/sdb1. Let's change the permission, for example changing the owner of the device:

chown sap /dev/sdb1
chown sap /dev/sdb2

Well, it is time to retry the creation of devices:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

Backups to devices

As described in Dump Devices article, dump devices can make administration much easier, however they can be used only for local dumps...

Let's assume you have some devices defined. To display the list of devices you can use following command:

sp_helpdevice

Knowing that dumpsdev is a valid name of dump device you try to execute following dump command on the Backup Server. Note, that you try to use the name of the device:

DUMP DATABASE ALFA TO dumpsdev AT SAP01_BS WITH COMPRESSION=9

Unfortunatelly the dump command ends with error message:

Could not execute statement.
Error encountered by Backup Server.  Please refer to Backup Server
messages for details.
SQLCODE=8009
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=16
Line 31

Errors starting Backup Server

Following errors can be seen when starting Backup Server:

Error 1

[sap@SAPSybase ~]$ /opt/sap/ASE-16_0/bin/startserver -f /opt/sap/ASE-16_0/install/RUN_SAP01_BS 
[sap@SAPSybase ~]$ Backup Server/16.0 SP02 PL01/EBF 25184/P/Linux AMD Opteron/Enterprise Linux/ase160sp02pl00/4902/64-bit/OPT/Wed Sep 9 09:04:15 2015 Confidential property of SAP AG or an SAP affiliate company. Copyright 2015 SAP AG or an SAP affiliate company. All rights reserved. Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of SAP AG or an SAP affiliate company. Use, duplication or disclosure of the software and documentation by the U.S. Government is subject to restrictions set forth in a license agreement between the Government and SAP AG or an SAP affiliate company, or other written agreement specifying the Government's rights to use the software and any applicable FAR provisions, for example, FAR 52.227-19. SAP AG or an SAP affiliate company 
Logging Backup Server messages in file '/opt/sap/ASE-16_0/install/SAP01_BS.log' 
Open Server Error: 16253.10.0: srv_thread_props(): property SRV_T_LISTENADDR not valid for service thread .

This error was in my case - fake! Backup server has been started correctly! This can be easy verified:

Method 1 - showserver command in bash

In the bash session start:

showserver

An example output can be similar to:

[sap@SAPSybase ~]$ showserver
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S sap       1272  1271  0  80   0 -  6541 poll_s 16:30 pts/0    00:00:00 

  /opt/sap/ASE-16_0/bin/backupserver -e/opt/sap/ASE-16_0/install/SAP01_BS.log -N25 -C20 

  -I/opt/sap/interfaces -M/opt/sap/ASE-16_0/bin/sybmultbuf -SSAP01_BS
0 S sap       1111  1106 16  80   0 - 488674 futex_ 16:27 ?       00:00:31 

  /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

The second line tells that backup server is up and running

Possible errors during device creation

When raw device is being created and something goes wrong, usually an error message displayed to the administrator working in his tools is not very explanatory. This may be error message like this:

Could not execute statement.

DISK INIT encountered an error while attempting to open/create the
physical file for device '/dev/sdb1'. Consult the Adaptive Server error
log for more details. For the Cluster Edition, check the error log of
each instance.
SQLCODE=5123
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=50
File: "devices.sql", line 1

To verify what happened behind the scene, the best is to log at ASE error log on the server. The default error log location on server SAP01 is:

/opt/sap/ASE-16_0/install/SAP01.log

Cannot execute file RUN_SYBASE

If Sybase ASE has not started automatically you may wish to start it manually. Command used to start server is:

startserver

located by default in /opt/sap/ASE-16_0/bin. This script by default searches for a file /opt/sap/ASE-16_0/install/RUN_SYBASE. This file contains startup parameters used by server during start. However if during the installation you chosed to name the server differently, such a file is not present. Instead of this another file should be placed in that directory. On my server it is RUN_SAP01. To direct startserver script to use that file you can pass the path as parameter using -f:

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