Recovering from a database problem starts with diagnosing exactly what is wrong with the database. Maybe isql will not connect to the dataserver, maybe the database is marked suspect, or maybe an error message in the errorlog occurs when the dataserver is started. In all of these cases and others, something has gone wrong where it had gone right before. Fortunately, with the proper backups, many of these problems can be fixed and the database restored to full working order.

Sybase has many parts that are inter-related, but like Sherlock Holmes investigating a mystery, if we eliminate from consideration the items that are working correctly, only the error-causing parts will remain. This section provides step-by-step directions to diagnosing and repairing all of these error-causing parts, and when completed, will leave a fully functional dataserver.

A flow-chart that appears at the beginning of these steps should help you in the recovery process. Each item in the chart is numbered the same as the steps and procedures below. The electronic version of this procedure contains a flowchart that is an HTML image map. Each decision or action box in the flow chart is a hyperlink to the appropriate section of the printed procedure. For more detailed information about individual steps, please consult Sybase's System Administration documentation, especially the "Backup and Recovery" chapter.

To begin the investigation, start the dataserver like it is normally started. If problems show up in the Sybase error log file, or the dataserver does not come up start with Step 1 to begin the recovery/diagnosis procedure.
 

Step 1: Runfile Ok?

Sybase starts up by using the startserver program. In both OSs, the program take an additional parameter -f runfilename that is the file containing the server startup command and parameters. If this file is missing, or the path given for the runfile is incorrect, the startserver command will return an error "Cannot execute file runfilename". If the path is incorrect for the file, correct it and start again. If you are unsure of the path being used, change your directory to the location of the runfile then run the command, but only specify the runfile name. Sybase will then try looking in your current default directory for the file.

If the file is missing, it can be recreated fairly easily. There is nothing magical about this text file. It contains the command and parameters to start the server. Figure F contains a sample runfile for a Unix system.

The -e errorlog points to the full filename and path of the dataserver’s errorlog. During an install, Sybase defaults to the $SYBASE/install directory. Because this directory contains a number of important files other than the error log, it is recommended that this be changed to point to another directory, maybe $SYBASE/logs/. Also, if there is more than one dataserver on the system, the errorlog filename should be changed. Appending the ".errorlog" suffix to each dataserver name provides each server with its individual errorlog file, making tracking errors down much simpler.

The next parameter to look at is interface parameter, -i interfacedir. This is the directory where Sybase can find the interface file. In some systems, there might be two interface files - one or more for the users to use, and one for the Sybase system to use. The different interface files could contain different selections of server entries, preventing the users or the dataserver from accessing all the Sybase servers available. If this parameter is omitted, Sybase will look in the directory pointed to by $SYBASE environmental variable.

One parameter that is not shown here is the configuration file parameter -c configfile. As of version 11, Sybase can be started using a configuration file. This file contains the text of all the configuration options on the dataserver, and their values. When no -c parameter is specified, Sybase defaults to the file servername.cfg found in the directory where the dataserver is started.

Two additional parameters can be used with the dataserver program - the version parameter -v and the single user mode parameter -m. The version number parameter is handy to use when you need to see the current version of the database program, and do not want to search for it in the errorlog.

The single-user mode -m, or maintenance mode is used to bring the dataserver up so only the "sa" account can access the dataserver. This is required when doing certain recovery procedures, and in general, when there is a need to prevent user access while maintenance is being done.

It is easy to recreate this file by using a text editor to create a script like the one above, but with the values of your dataserver. Once the file is created, and the account that starts the dataserver has access to it, run the startserver –f runfile command.

Like most database products, Sybase uses shared memory to communicate between dataserver processes, process queries, and store sections of the database for quick access. If Sybase is prevented from acquiring the minimum shared memory it needs, it will fail to come up and instead will error out with a message like the following:

As mentioned above, changes in configuration parameters can cause the Sybase server to need additional memory. This in turn can required the Sybase server to request a larger shared memory segment from the OS. There are two things that can prevent this from happening. One, the maximum shared memory segment size is undersized, and two, there is not enough shared memory on the system to allow this to run.

In the first case, the solution is to increase the maximum shared memory segment value appropriately for the OS. Because Sybase is supported on many different OSs, how to change this is not shown here. Please refer to the appropriate operating system sybase installation manual for further information on setting this value.

In the second case, either shared memory needs to be added to the system, or shared memory needs to be freed up from other processes. Please contact a System Administrator for help in accomplishing either of these.

You can view the shared memory being used by using the ipcs command. Check the man pages for the correct usage of the command, but the output should look something like this:

If none of these solutions helps the problem, the changes in the configuration options will need to be reverted to allow the dataserver to come up correctly.

When Sybase starts, it needs the interface file so it can set up all the interprocess and network communication parameters. This interfaces file is usually named interfaces, interface, or sql.ini, depending on the operating system.

If Sybase cannot find the file, or there is something wrong with it, Sybase will error-out during startup with a message like one of the following:

The second entry means the lines for the dataserver being started (in this case SYB_MYDB) cannot be found in the interface file. To fix this problem, either add an entry using the appropriate method for your operating system. Please see the Sybase Operating System specific documentation for more information.

Once the entry is in the correct interface file, re-run the startserver command to see if this has fixed the problem.

The master database is the most important database in the system; not only does it contain all the information about all the other databases, but it is also the repository of all information regarding logins, roles, devices, usage of those devices, and configuration options on the system. It is imperative that this database comes up correctly. Otherwise, the rest of the dataserver would not work at all.

The first information that something is wrong with the master database would most likely be the following error messages displayed during startup:

If there are no problems accessing the master data device, then the problem might be the master database has been corrupted. This type of problem could show a number of strange symptoms if the dataserver was already running. For example, isql will crash with program errors when trying to connect, or currently running queries will also crash.

If strange things like these start happening, the best thing to do is to try to restart the dataserver. Because in situations like this, almost all access to the dataserver will, the normal use of the isql command shutdown cannot be used. Instead, the dataserver process must be stopped at the operating system level. In Unix, this can be done using the kill command. This should only ever be used during extreme situations such as when the dataserver is consuming up all the processing of the system. Only use kill if nothing else will help because it could cause corruption of the databases in the dataserver.

Once the dataserver is down, check to make sure memory used by Sybase has been freed. See Step 5.for more information on shared memory. When the shared memory has been freed, try starting the dataserver using normal starting procedures.

If the dataserver fails to start, see if there are errors on the master device in the dataserver error log. Go to Step 15. and make sure their are no problems there. If there are, correct the problem with the device, and try restarting again.

If there are no problems with the physical devices, the master database will need to be completely recovered from backup.

Follow these steps to restore the generic master database:

1. Backup Server Up?

 

 
 
 
 
 

Make sure your backup server is up and running. It will be used later to restore any backups of the master database and to back up the restored database when the recovery is finished.

2. Create data device

 

 
 
 
 
 

Since the master device was corrupted, it must be created anew. Open up the runfile used to start the Sybase server in a text editor. Note the master device path and the size of the master device; they will be used in the buildmaster command below to recreate the master device.

To recreate the master device, enter the following buildmaster command or the OS equivalent needed by the downed server:

buildmaster –d /sybdata/master.dbf –s 8704

The –d option is the master device path and the -s is the size in pages. For this example, the master device being created is 17MB (8704 2K pages) on /sybdata/master.dbf.

3. Startup in Master Recovery Mode

 

 
 
 
 
 

To start the dataserver in master recovery mode, first make a copy of the current runfile. Edit this file, and add the -m master recovery mode parameter. When the change is done, use this file to start the dataserver process with the startserver –f master_runfile command. This mode will allow the master database’s system tables to be updated and prevent users from accessing the system.

4. Recreate Master’s Entries in Usages

 

 
 
 
 
 

Before anything more can be done to restore the data into the master database, the master device needs to be restored to the same usage as it was when the last backup of the master database was performed. Using hardcopies of the system tables sysusages, sysdevices, and sysdatabases, the entries in sysusages can be recreated with alter database and create database commands.

To decide if anything needs to be done to at this point, examine the dbid column in sysusages. If more than one entry for dbid equals 1 (see sysdatabases, dbid = 1 = master db), additional space will need to be added into the master database to make the sysusages in the server match the one on paper. If there is only one entry, the sysusages entry does not need any work and the next step is "Add Backup Server Entry into sysservers".

Figures G-I contain example outputs of the three tables needed for this restore. Note: the T-SQL statement to extract this information is provided above the table output.

select name,dbid from sysdatabases order by dbid

name dbid

------------------------------ ------

master 1

tempdb 2

model 3

sybsystemprocs 4

sybsyntax 5

mydb 6
 
 




Figure G: sysdatabases












select * from sysusages order by vstart

dbid segmap lstart size vstart pad unreservedpgs

------ ----------- ----------- ----------- ----------- ------ -------------

1 7 0 1536 4 NULL 112

3 7 0 1024 1540 NULL 608

2 7 0 1024 2564 NULL 608

1 7 1536 1024 3588 NULL 920

5 7 0 1024 4612 NULL 272

1 7 2560 2048 5636 NULL 2048

4 7 0 8192 16777216 NULL 864

6 3 0 5120 67108864 NULL 4720

6 4 5120 2560 83886080 NULL 2552
 
 




Figure H: sysusages












select * from sysdevices

low high status cntrltype name phyname

-------- ----------- ------ --------- ------------ --------------------------

0 10751 3 0 master d_master

67108864 67113983 2 0 mydbdev /sybdata/mydbdev.dbf

83886080 83888639 2 0 mydblogdev /sybdata/mydblogdev.dbf

16777216 16785407 2 0 sysprocsdev /sybdata/sybprocs.dbf

0 1280 16 3 tapedump1 /dev/st0

0 20000 16 4 tapedump2 /dev/st1
 
 




Figure I: sysdevices












Since this is a recovery of the master database, we only have to restore up to the last master database entry in sysusages. These would be those where "dbid = 1," and the vstart less than or equal to the high value from the master device, master, in sysdevices. Notice that there are entries between the first and last master db entries on the master device. When restoring the master database, these entries must also be duplicated. In this example,

Fortunately, buildmaster has already done part of the work by recreating the first three entries that correspond with the master, tempdb, and model databases. In this example, there are only three entries to restore.

Now that the entries to be restored are known, the work of restoring them with the alter database and create database commands can begin. Use the dbid value in sysusages to find out which database entry needs to be added next. In this example, the master database was extended for an additional 1024 blocks. This equates to 2MB of spaces on the master device. Because the master database already exists, this alter database command must be used.

alter database master on master = 2

The next entry has a dbid that corresponds to the sybsyntax database. Its segment size is also 1024, so it size is 2MB. Since this database has not been created yet, the following create database command must be used:

create database sybsyntax on master = 2

Continuing to the final entry, the dbid signifies that it is another entry for the master database. The size in this case is 2048 Blocks that equals 4MB (2048/512). The next command to run would therefore be:

alter database master on master = 4

Now that all the entries for the master database are finished, a load can be performed of the backup, but only after the dataserver knows about the backup server.

5. Add Backup Server Entry into sysservers

Once the system comes up, check the system tables sysusages, sysdevices, and sysdatabases against the backup hardcopies. If there are devices on your hardcopy that are not listed in the system, then an additional device was added since the master backup was performed. In this case, a disk reinit will update the sysdevices table from information on the device. If the alter database or create database commands were run since the last backup, disk refit must be run to resync the dataserver with the additional devices and databases available on the system.

disk reinit

The command to resync devices is disk reinit, and it will add a device back into the sysdevices table without initializing the device itself. To run this command correctly, you will need the parameters used when the device was first being created with the disk reinit command. The command syntax is in Figure J:

disk refit

Once all devices that had been added to the system after the backup have been redefined in sysdevices, all databases additions and changes to databases after the backup can be resynced. The command to do this is called disk refit. What it does is visit every disk defined on the system, and use system information stored in it to reset the sysusages and sysdatabases tables to what they should be. The command syntax is in Figure ?:

Once either or both of these commands have been run, compare the values in sysusages and sysdatabases to make sure they match the hardcopy of these tables. If they do not, the system could come up without all devices and databases being online.

For more information on how to run disk refit and disk reinit, see the Sybase System Administration Guide.

Review sysusages/sysdatabases/sysdevices/syslogins/sysloginroles

Because the master database contains information about all the other databases, it is important to make sure everything is in working order. Follow these checks to help confirm the fitness of your newly restored master database.

1. Check the sysdevices, sysusages and sysdatabases system tables.
2. Review each database, checking all the major tables. Run common selects on these tables verifying the data inside.
3. Run the dbcc checkalloc command on all databases. See the section Database consistency checker: the dbcc utility for information on how to run this command.
4. Double-check the ownership and permissions of all databases. If user logins were added or deleted since the backup of the master database, these changes will need to be run against the system again in the same order they were originally added to the system. If these changes are done out of order, the suids of the logins added will be different than when they were first added to the system. This will cause a mismatch between the suid controlling the ownership and permissions in a database and those in the system logins. Because of this, there could be problems with access to the databases and objects in the dataserver.

The sybsystemprocs database is where Sybase locates all the system stored procedures. When a stored procedure is created here, it is available from any database in the dataserver. Therefore, when this database is unavailable, many important system stored procedures will be unavailable, such as sp_help, sp_helpdb, and sp_helpdevices. The rest of the dataserver databases will come up as long as they do not have problems, but the functionality of the dataserver will be severely degraded.

If the dataservers error log file shows there are problems initializing a data device, then follow these commands to check OS device problems, fix them, and maybe replace them.

1. Check ownership and permissions on devices.

 

 
 
 
 
 

Use the OS commands to check permissions and make sure the process running the Sybase dataserver program has permissions to read and write to these devices. If they do not, either change the permissions on the devices, or change the user that is running the dataserver program to one that does have the correct permissions

2. Is the device functioning correctly or does the device even exist?

 

 
 
 
 
 

Sometimes, because of a change in the system, the operating system might no longer "see" the device. Also, there might be hardware failures or configuration errors that could cause the device to not appear. Use the appropriate OS procedure to check the device’s status and review all errorlogs.

3. Unable to Detect Device at Startup?

If a drive goes bad, but another one of the same size is available, the new one can be used in the place of the old in the Sybase database. This is because Sybase uses logical devices that point to the actual devices.

All databases that were using this device will need to be restored. To find out which databases these are, run the T-SQL command shown in Figure L:

Once the device is dropped, the new device can be added back into its place. Use the same disk init command that was used to create the bad device, but replace the physical name of the bad device with the name of the good device and use a different vdevno. For example, if the original disk’s physical name was /dev/rdsk/c0t2d1s0 with a disk init command of:

When the sybsystemprocs database needs to be restored, follow these directions:

1. Find out which devices sybsystemprocs was installed on.

 

 
 
 
 
 

First, try the command sp_helpdb sybsystemprocs to find out how large and on what device it was created on. Most likely this will not work. In that case, run the T-SQL command in Figure M instead:

select sysdevices.name, sysusages.size/512

from sysdatabases, sysusages, sysdevices

where

sysdatabases.name = "sybsystemprocs" and

sysusages.dbid = sysdatabases.dbid and

sysdevices.low <= sysusages.vstart and

sysdevices.high >= sysusages.vstart

Example output:

name

------------------------------ -----------

sysprocsdev 16
 
 




Figure M: Finding out size of device







2. Drop the sybsystemprocs database.

 

 
 
 
 
 

In case the sybsystemprocs database is corrupt, it is best to drop it and recreate it fresh. First try the T-SQL command drop database sybsystemprocs. But, if it is not allowed by the system, use dbcc dbrepair (sybsystemprocs,dropdb) to drop the database.

3. Recreate the sybsystemprocs database on the device shown in the above command.

 

 
 
 
 
 

1> create database sybsystemprocs on sysprocsdev= 16MB

4. Run installmaster or Restore from Backup

 

 
 
 
 
 

If there is a current backup of the sybsystemprocs database, then it can be used to restore the database with the load command like this:

load database sybsystemprocs from "device"

Use the appropriate OS command for the dataserver environment.

If there is no backup of the sybsystemprocs database, it will need to be recreated using T-SQL script installmaster. This script can be run safely without worry that it will affect other databases. To run the script, provide it as input to the isql program like this:

isql –U sa –S SYB_MYDB –P mypasswd \

–i $SYBASE/scripts/installmaster

or

isql –U sa –S SYB_MYDB –P mypasswd \

< $SYBASE/scripts/installmaster

There will be a lot of output from this command, but it can be safely ignored until the script ends with the message "Loading of the master database is complete".

5. Add any additional Stored Procedures/Changes

 

 
 
 
 
 

If additional stored procedures were added to the system since the backup or since the initial install, recreate them now using the the scripts used to original create them or by entering the commands interactively.

6. Check Stored Procedures

 

 
 
 
 
 

Like with all the other recoveries, it is important to check out the recovery. The following command will show if the sybsystemprocs database is restored correctly.

sp_helpdb sybsystemprocs

If output describing the sybsystemprocs database is displayed when this is run, then system stored procedures have been restored correctly. Run any user defined stored procedures to confirm they run correctly.

7. Dump the sybsystemprocs database

If the database is still offline at this point, then something is wrong with it internally, and it should be restored from backup. Before anything else is done, retrieve information about the database to use in the recreation. Enter the command in Figure N to find out about the allocations used by this database.

1. Drop the database

 

 
 
 
 
 

First, try to drop the database using this T-SQL command:

drop databasebaddbname

If this command fails, use this dbcc command to drop the database:

dbcc repairdb(dropdb,baddbname)

To verify the database has been dropped, run the stored procedure sp_helpdb. If the database is shown in the output, something went wrong with the drop command.

2. Recreate the database

 

 
 
 
 
 

Using the information from Step 2 above, recreate the database using the same allocations it had. Here is an example based upon the example output in Step 2.

create database baddbname

on device1 = 3

log on logdev1 = 2

alter database baddbname

on device3 = 4

3. Bring the database online.

 

 
 
 
 
 

At this point, the database has been recreated, but the system will not bring it online until it is told to. The reason the system does this is it has no way of knowing if there are any more transaction logs to process. To tell Sybase the database should be brought up by running the online database baddbname command.

4. Load the database from dumps.

 

 
 
 
 
 

Now reload the database using the most recent database and transaction dumps. First, apply the full database backup. For our example database, here is an example Unix load command using the tape device /dev/nrmt0.

load database baddbname from ‘/dev/nrmt0’

After this completes, apply each transaction log starting with the oldest and finishing with the newest. The system will not allow load of the transaction logs to occur out of order. In fact, if any of the logs are missing or corrupt, the rest of the logs cannot be applied. To load the transaction logs for our example above, enter the following command, repeating for each transaction dump:

load transaction baddbname from ‘/dev/nrmt0’

For more information on how to load dumps and transaction logs, please refer to the section on Restoring from a hot backup.

5. Dump the database.

Is the tempdb online and available? To see this, review the messages generated when trying to start the database. There will be error messages complaining about tempdb not being available. The system will not be able to come up at this point.

The model database is used by the dataserver when creating all other databases. If a specific attribute (larger size, tables, permissions, stored procedures) is required in all databases, make the change first in the model database. Then, from that point on, every database created will contain this change.

If you have a backup of the model database and you can use the isql use command, then the database can be restored directly from backup using the load command. If not, then the database must be restored from scratch. Follow the following commands to accomplish this:

1. Run buildmaster

 

 
 
 
 
 

If there is no backup available of the model database, the buildmaster command can be used to restore the model database to the same state as when the dataserver was first installed. Here is a Unix example:

buildmaster –d/syback/master.dbf –x

In this example, the -d parameter is the master database file path, and the -x parameter tells buildmaster to restore the model database. Be sure to use the appropriate buildmaster command for your OS. This can be found in the Sybase Utilities manual.

2. If a backup of the model database is available, use it in a load command to restore it from the backup.

 

 
 
 
 
 

load database model from"/sybackups/model.dmp"

3. If additional changes were made since the last backup, apply them at this point.
4. Backup the model database using the dump command.

If when the dataserver is coming up, and an error occurs with any of the devices associated with that database, or something has corrupted the database, the non-system database could be prevented from coming and will be marked "suspect".

To see if all the databases after the system databases are online, review the messages generated when trying to start the database. There will be error messages complaining about any databases not being available. Fortunately, because all the system databases are available, the Sybase server will come up. If this happens, proceed to Step 15 to make sure all the user database devices are all available. If all the database are available, then the system is up and no recovery is required.

If you have reached this step, then some problem that might be specific to the OS, version of Sybase, or other factors is occuring. Please refer to the Sybase System Administration guides for your OS, or contact Sybase support for additional help.

If any additional scripts had been run on the database since the last dump, then these scripts need to be run again. Since they are user created, you will need to refer to the user’s instructions on how to run these.

On top of additional scripts, there might be bcp files of data from these databases. Follow the steps needed to restore these data files to the originating database tables. Please refer to the Logical Backups section below on loading data into the system.

Using hardcopies of the system tables and any important user tables, compare the current data with the data captured in the hardcopies. If there are discrepancies, then there could be additional problems with the dataserver. Refer to the Sybase Administration manuals to find out if the differences need to be repaired or are ok. Double check all permissions on the database if any changes were made in the syslogin or sysrole tables. If there are, correct before turning the system over to the user population.

Now that the database has been restored, dump the database to a new dump file. This way a current dump of the database, including all scripts and data changes, will be saved. This will help facilitate recovery in case future problems.