Showing posts with label Devices. Show all posts
Showing posts with label Devices. Show all posts

2017-07-30

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.

2017-07-01

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

2017-03-19

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

2016-08-25

sysdevices system table

All metadata describing server, database or objects in the database are saved in system tables. Data in those tables can be queried, but should not be directly modified.

One of such table is sysdevices, listing all defined on server devices. To change/add/remove records in that table use:
  • disk init - to create new devices, 
  • disk resize - to extend already created devices, 
  • disk mirror - to enable mirroring of device, 
  • disk unmirror - to stop mirroring,
  • sp_dropdevice - to remove device, 
  • sp_deviceattr - to change options related to the device
  • sp_diskdefault - to mark a device as default or unmark this property

2016-08-24

Mirroring devices - strategies

Mirroring in Sybase ASE allows to avoid failures of databases. So the answer to question "what to mirror" is the same as "what is the most important"?

  • master database and device is surely one of the most important componentes of server. Failure in master device will cause the entire server to stop. In case of master database additional step should be done apart from just mirroring devices.
    When Sybase ASE starts it needs to load master database. If the device would be destroyed when server was offline, it will not be able to start. Remember that information about mirroring is stored in sysdevices table in... master. Server without master is not aware of exiting mirror. To solve this issue parameter -r can be added to RUN file pointing to a mirrored master device.

Mirroring devices

SAP ASE allows to mirror device making instant copy of a device in another file/device. In case the primary device would contain a bad block, correct data could be read from a spare location. Mark that mirrored are devices - not databases, so in case when a database is stretched between multiple devices, mirroring only some devices doesn't give full protection for the database.

To mirror a device an option 'disable disk mirroring' must be set to off. Following command allows to verify this setting:

execute sp_configure 'disable disk mirroring'

In case the option would be set to true, you can change it's value starting:

sp_configure 'disable disk mirroring', 0

That option is static - this means server restart is required to make mirroring possible.

Managing devices

Dropping a device

After a database has been dropped a device not used by an
 database can usually also be dropped(of course dba may decide not to release disk space). To remove device following command could be used:

exec sp_dropdevice 'BetaDevice'

where BetaDevice is logical name of device that should be removed. After issuing that command only row in sysdevices is removed. File that the device was build on, need to removed manually. The same concerns also a raw device.

If the device is in use - so you have a database using it - don't worry. Such command will end with an error "Device is being used by a database. You can't drop it.".

2016-08-23

Default device and why you should change it?

Every one likes to work with intelligent programs: just put some specific information and the remaining part of parameters should be taken from a set of default parameters. Sybase ASE also works that way. When a new database will be created, one or more devices should be specified, but if this part of command will be omitted, than... a default device will be used. Initially master device is the default one. This is not the best situation, as if by a mistake no device will be specified during database creation it will be placed on master device. According to information in Administration guide it is not recommended situation:
Caution!
Do not store user databases on the master device; doing so makes it difficult to recover both the system databases and any user databases stored on the master device.

2016-08-22

Raw device creation

As raw devices guarantee that write will be completed within transaction (unlike file device even with with DIRECTIO option) and additionally have very good write performance (unlike file with DSYNC option), this is common in production to see raw devices.

To create a raw device you need first to create a device in OS. On Linux station where drive has been attached using IDE controller the disk can be visible via a symbolic file /dev/sdb. First of all partitions should be created on the disk.

To work with partitions execute following steps:

  • start command 
fdisk /dev/sdb

2016-08-21

File based device creation

To create a new database you need first to have devices created. As already described inthe post
Raw devices or files? What is better? we can create raw devices and regular files functioning as devices.

Let's start with an example showing how to create a device basing on a file. All what is required to start with the device creation is a folder on file system having enough free space. First decide where to locate the devices. It is not a bad idea to have a dedicated folder for file devices. Because data files and log files are used differently you should probably think about locating files on separate filesystem anyway. Here let's put  all the files in the same folder.

[sap@localhost ~]$ pwd
/opt/sap
[sap@localhost ~]$ mkdir databases
[sap@localhost ~]$ cd databases
[sap@localhost databases]$ mkdir alfa

Having folder, enough space and read and write permissions there, we can switch to SQL:

disk init name ='AlfaDataDev', 
          physname = '/opt/sap/databases/alfa/alfadatadev.dat', 
          size=10240
GO
disk init name ='AlfaLogDev', 
          physname = '/opt/sap/databases/alfa/alfalogdev.dat', 
          size=2560, 
          skip_alloc = true
GO

2016-08-19

Raw devices or files? What is better?

One of the questions when a new device is to be created is if the device should be a raw device or a file. There are a couple of factors that should be taken into consideration:


  • Normal files are easier to manage. When database grows and you need to add a new disk space to the database, it is enough to create a new file and add it as a device to the database. In case of a raw device you have much more administrative work to do...
  • Normal files have better performance during writes to the disk. Files are usually buffered by OS, so modifying a file in memory is faster than sending the modification to the disk, however...

System database's devices

Information regarding devices in Sybase/ SAP ASE can be found in many system tables, however this will be a very bad idea to modify them directly. When you need a new device some dedicated commands should be used.

System databases are created during installation and normally you don't need to create them manually. Probably the only moment when a manual creation of databases will be required is when installation has been somehow damaged and you need to recover from the disaster.

During installation following devices have been created:

SELECT name,phyname FROM sysdevices

name                           phyname                                                       
------------------------------ -------------------------------------------------
master                         /opt/sap/data/master.dat       
tapedump1                      /dev/nst0                         
tapedump2                      /dev/nst1                         
sysprocsdev                    /opt/sap/data/sysprocs.dat
systemdbdev                    /opt/sap/data/sybsysdb.dat
tempdbdev                      /opt/sap/data/tempdbdev.dat
sybmgmtdev                     /opt/sap/data/sybmgmtdb.dat 

and those devices are used by following databases (we are skipping tape devices here as you see in WHERE clause):

Devices - introduction

To separate database objects from physical server components all databases are build on logical devices. Logical devices seat between a database and physical disk structures. Having database build on devices it is quite easy to migrate database to another server. All you need are just devices with the same names on the another system!

A device can be a file (common solution on Windows implementation) or a raw device (usually used in Unix implementation).

Pages in Sybase / SAP ASE: Virtual and Logical pages

In many places, where command do something with disk or database, you can specify page size. There is also mentioned that default size of the page is 2KB, but this can be set to 4KB, 8KB and so on...
Generally we have two types of pages that can be described by parameters of some commands. There are:

  • Virtual pages
  • Logical pages

SAP Adaprive Server Enterprise

SAP Adaprive Server Enterprise
SAP Adaprive Server Enterprise