Section 17.8. Common Sybase Procedures
17.8. Common Sybase ProceduresThis section describes a full diagnostic and recovery procedure for your system. We'll begin with the basics and work toward more complicated recovery procedures. First we'll cover some commonly used proceduresprocedures that are referenced by the rest of this chapter. 17.8.1. Procedure 1: How to Start SybaseIn the $SYBASE/$SYBASE_ASE/install directory you will find your server's run files. Run files are simple shell scripts that begin with the pattern run_ and usually are named run_<SERVERNAME>. They contain shell commands that are used to start your Sybase servers with appropriate command-line arguments. Backup server run files are normally named run_<SERVERNAME>_BACK, and monitor server run files are named run_<SERVERNAME>_MON. A default installation includes several of these run files, one for your database server, one for your backup server, and one for other servers in the Sybase product suite. Example 17-4 shows a sample run file for a Unix system. Example 17-4. Sample run file
On a Unix system, a server is started with the command startserver -f run_FILE. The startserver command is a very simple binary executable that basically runs the file in the background.
17.8.2. Procedure 2: How to See Whether Your Server Is AliveThe obvious way to see if your server is OK is to connect to it with a utility like the isql program. isql works with both the Sybase server and the Sybase Backup server. Here is an example of this process, connecting to a dataserver SYB_MYDB and to a backup server SYB_BACKUP: # isql -S SYB_MYDB -U sa -P passwd 1> quit # isql -S SYB_BACKUP -U sa -P passwd 1> quit If either of these processes is not up, or if you entered an invalid sa password, an error message like the following appears: Operating-system error: Connection refused DB-LIBRARY error: Unable to connect: SQL Server is unavailable or does not exist. If you suspect there is a problem with your server or you cannot log in, you can check that the system processes are running. Sybase processes can be checked either by running the standard ps command or by running a Sybase-provided shell script file called $SYBASE/$SYBASE_ASE/install/showserver. showserver, in fact, calls the ps command. A running system should have one or more dataserver processes and one backupserver process. Example 17-5 shows both commands and their outputs from a Linux system. Example 17-5. Sample ps and showserver outputs
If the backupserver or dataserver processes are not up and running, follow the appropriate operating system commands to start them. On Windows, Sybase runs these processes as servicesyou can see their availability by looking in the Control Panel under Administrative Tools. 17.8.3. Procedure 3: How to Shut Down Your ServerSybase offers several ways to shut down your server. The primary mechanism to shut down your server is through the T-SQL command shutdown. When this command is run, the database disables all logins except the login for the system administrator, performs a checkpoint on each database, and waits for all currently running statements or procedures to complete. If any user connections have not completed their transactions, the shutdown waits for them to complete. If you do not want to wait for these transactions to complete, you can try the command shutdown with nowait. The nowait option stops all transactions immediately and continues with the shutdown, but because there could be uncommitted transactions in progress, recovery time is increased. It's best to check with your users before doing this. (Otherwise, there might be one less DBA in the world when the users track down the person responsible for pulling the rug out from under them.) Sybase 12.5.4 introduced a wait time for the shutdown command, so that it will wait for a period of time, then do a shutdown nowait if the system hasn't shut down by then. 17.8.3.1. The first thing to trynormal shutdownA normal shutdown is the ideal way to shut down your database server. It minimizes server restart times by allowing all active transactions to complete and then flushing all unwritten pages to disk by running a checkpoint in each database. Since most transactions are short, a normal shutdown should complete within a few seconds. All transactions are written to disk and marked as committed, so the server recovery process is very fast. 1>shutdown Server SHUTDOWN by request. The SQL Server is terminating this process. DB-LIBRARY error: Unexpected EOF from SQL Server. 17.8.3.2. The second thing to tryshutdown with nowaitshutdown with nowait kills running processes and then shuts down your dataserver. Transactions that have not completed abort and are rolled back when the dataserver starts up. This increases startup times; because checkpoints were not run and data not flushed to disk, the server must reconstruct active transactions from the transaction log while starting up. The server goes through all transactions you applied and applies only those that were committed. This method is used quite frequently to shut down the server when shutdown does not respond quickly. 1>shutdown with nowait Server SHUTDOWN by request. The SQL Server is terminating this process. DB-LIBRARY error: Unexpected EOF from SQL Server. 17.8.3.3. The third thing to trykill -15 on the dataserverThere are rare times when you cannot log in to your dataserver to shut it down. Sybase has coded the dataserver process to interpret signal 15 (sent by a kill 15) as a shutdown with nowait. In other words, the server runs a shutdown with nowait when it gets a kill 15. This can, therefore, be considered a safe way to stop your dataserver, although active transactions are rolled back when the server starts up again, and server restart time is increased. 17.8.3.4. What you should NEVER dokill -9 on the dataserverCommercial databases are very robust these days. In all likelihood, your server will survive a kill 9 on the dataserver process and recover just fine. But it would be nice to guarantee server recovery, something you cannot do when you run kill 9. So run this command on your dataserver processes only as an absolute last resort. A kill 9 will remove the process from the process table and does not allow that process to run its embedded cleanup routines. Pages are swapped out of memory; this can be considered the same as flipping the power switch on your system. Always try the other mechanisms to shut down your server before you try this. 17.8.3.5. On WindowsOn Windows, you should first try the normal shutdown commands. If these do not work, you can stop a server by stopping the appropriate Windows service using the Services option in the Control Panel. 17.8.4. Procedure 4: How to Set Server Configuration OptionsServer-level options are set using the stored procedure sp_configure. Running sp_configure without parameters lists current configuration options. To set options, users run sp_configure configoption, configvalue. Most Sybase configuration options are dynamic and affect your running server immediately (although a few require a reboot of the server). 17.8.5. Procedure 5: How to Set Database-Level OptionsEach database in your system has several associated configuration options. Examples of these options include TRuncate log on checkpoint (which removes the unused part of the transaction log every few minutes), abort transaction on log full (which aborts running transactions if the transaction log fills; the default behavior is to suspend the process), select into/bulkcopy (which permits certain operations to run faster by not putting them in the transaction log), single user mode, and dbo use only. The following procedure sets a database option optionname in database mydbname: sp_dboption mydbname , "optionname ," true go use baddbname go checkpoint go 17.8.6. Procedure 6: How to Run a QueryAs mentioned earlier, the isql command is not very feature-rich. One of the features it does not support is easy output redirection to a file. There are two options for this. The first is the free program sqsh (created by Scott Gray and maintained by Michael Peppler), which is basically an advanced version of isql that supports command history and output redirection. The next is ASEisql, a free graphical utility. You can also, of course, use SQL Advantage, the graphical query viewer that Sybase ships with its server product line. Viewing SQL commands like sp_helpdb is much simpler when using a graphical utility.
|
EAN: 2147483647
Pages: 237