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.
The reason of this error was, that I had some files on a no more mounted (and available to be mounted) filesystem. However Sybase still contained configuration telling it, that file '/opt/sap/databases/tempdev2data.dat' should be used. The problem was quite big, because when the TempDb is not accessible many procedures are not working either! Forget about using GUI Interactive SQL. What was still working was the command line console.
To solve that problem a trick can be used. Just create the files that Sybase would like to see. This can be done with that easy step:
touch /opt/sap/databases/tempdev2data.dat chown sap:sap /opt/sap/databases/tempdev2data.dat
when Sybase starts it will see empty file, and will start using it. Of course you need to have enough disk space on the filesystem and Sybase account needs to have full access to the file (that's why I used chown pointing to sap user and sap group - those values can be different on other systems).
The second problem was:
I wanted to remove no more useful tempdb devices. There is no easy command to do it. What needs to be done is to directly modify system tables. Following steps helped me (don't execute! Read the comment below first!):sp_configure 'allow updates',1 GO reconfigure with override GO select db_name(2) GO select * from sysdevices GO select * from sysusages where dbid = 2 GO delete sysusages where dbid = 2 and vdevno = 14 --change to the value you want to use!!! GO shutdown GO sp_configure 'allow updates',0 GO
What does it mean?
- as we don't have a dedicated command to help us, we need to do all the job manually modifying system tables. This is extremely dangerous. Be careful, have backup of your databases including master and good documentation about databases, devices, segments and usages tables! By default modifying system tables is not allowed, so you need to change configuration to allow it. The last step returns to the original configuration
- we test whether the tempdb has indeed the id equal 2. This is the rule, so this step is just to explain what we are going to do in the next steps
- we display all the devices. You surely know which devices should be removed. You can validate here the names, the ids (vdevno) of devices and system paths
- we need to remove the entries from sysusages table, so let's display entries for database id=2 (tempdb)
- and very important - remove them starting from the recently added value. In my case recently added device was device with vdevno=14. You need to find the correct value by yourself. Take the highest number. If more segments should be available on one device start with the last segment (add appropriate lstart in the were clause)
- now we are in danger - the determined entry is being deleted
- at the end shutdown and restart the server. Take a look at error log. Nothing special should be there. The new tempdb should not be aware of a last segment anymore. When all the segments have been removed, you can remove the device using the sp_dropdevice command. Until the server was not restarted, in the error log following entry could be found:
kernel udstartio: vdn 15 has not been set up
server Error: 822, Severity: 20, State: 3
Could not start I/O for request BLKIO flags = 0x20012, size = 4096, errcode = 0x0, BUF=0x0x000000019b0c7488, MASS=0x0x000000019b0c7488, Buf#=0, page=0x0x000000019a48e000, dbid=2, Mass vdevno=14, vpage=58368, Buf lpage=80896, Mass stat=0x6811110, Buf stat=0x1, size=4096, cid=1 ('tempdb_cache'), Pinned xdes=0x(nil), spid=0.
Usually this means, that you have some hardware problem. Here the reason is known...
https://www.petersap.nl/SybaseWiki/index.php?title=Tempdb_space_management
https://www.slideshare.net/SAPTechnology/ase-tempdb-performance-tuning
https://sort.veritas.com/public/documents/sf/5.0MP3/solaris/html/sf_syb_admin/ch08s04s01.htm
https://groups.google.com/forum/#!topic/comp.databases.sybase/9ozI7wTXEB8
No comments:
Post a Comment