|
|
This chapter contains information that the Database Management System (DBMS) system administrator will use to perform basic database maintenance. The tasks described require a detailed working knowledge of UNIX, network communications, and the Abstract Syntax Notation One (ASN.1) language.
This chapter includes the following sections:
All of the tasks in this chapter are performed using root or sa privileges, unless otherwise noted. You can tell when a tasks requires these privileges by the use of the pound sign (#) in the examples or steps.
The system administrator uses two accounts:
This chapter describes the tasks to perform while using these accounts. The Sybase sa account is the equivalent of the UNIX root account, because the Sybase database server does not check permissions while you are logged into the sa account.
If you forget your Sybase system administrator password, you cannot get system administrator access to the Sybase database unless you reinstall Sybase. During the reinstall, you will reestablish a password as part of the installation.
Administering the Sybase Server
This section includes information on how to start, query, shut down, and troubleshoot the server.
For information on how to disable the CiscoWorks default of automatically clearing the database, thereby customizing your backup procedures, refer to the section "Disabling the Automatic Clear Function" later in this chapter.
Performing System Administration
Use the following command to perform system administration tasks such as viewing or adding user accounts to the Sybase database:
The Sybase server must be running in order for your CiscoWorks applications to collect and display data.
To start the Sybase server use the nmstartup command. Typically, you run nmstartup when you boot your system. The nmstartup command also starts other background processes required by CiscoWorks. You set this functionality during the installation and configuration process as described in the CiscoWorks Administration and Installation Guide.
After you use the shutdown_nms script, perform the following steps to restart the Sybase server:
The isalive command is used to query the server to determine if it is connected and operational. Use it to quickly find out if your database functions are available. The query uses the same arguments as in any SQL server client application. If the query succeeds in connecting to the server, it returns a successful status code (zero) to the command line. If the query fails to make a server connection, a message identifying the reason displays and returns a nonzero status code.
To query the Sybase server, make a server connection query by entering the following command syntax:
Following are examples of the isalive command:
If there is a problem with the server, an error message will appear.
Use the shutdown_nms script to shut down the SQL server. The script sequentially shuts down the database to prevent corruption that might otherwise occur if power were simply cut off.
To use the shutdown_nms script, perform the following steps:
For details, see your system administrator for instructions or your Sun System Administration Guide.
If the server does not appear to be running, perform the following steps:
The system name where your server is located appears. Ensure that this system is listed in your domain name server (DNS) or other name server.
For detailed information on Sybase error messages, refer to the Sybase System Administration Guide.
Modifying the Database Password
As shipped, null is the DBMS SA password to access the database is null. Press Return at the password prompt to accept the null password. You can change the password initially, or at any time by performing the following steps:
Monitoring Transaction Log and Disk Space
By monitoring your disk and transaction log use, you can prevent system problems. If you do not provide enough disk or transaction log space, you will receive error messages. One of the consequences of not having enough database space is that your CiscoWorks processes will halt, and the database log will be unable to accept additional messages and will display error messages.
To calculate the amount of disk space used in your system, you must perform two separate procedures. Using the calculations from these procedures, you can determine the amount of disk space used. Check the size of the database periodically (we suggest weekly checks) to ensure that your system runs smoothly.
Allocating Database Disk Space
When CiscoWorks is shipped, the disk space allocated to database functions leaves about 10 megabytes (MB) of free space to record table information and polling. Polling can consume an enormous amount of space for retention of the data being acquired by the queries. The faster a poll rate (shorter interval), the faster disk space is consumed. For this reason, adjust the disk space allocation before polling with Device Polling. This means that when you create tables, be certain to save them with a no poll interval entry, so they do not start polling and fill the database.
Monitoring Transaction Log Utilization
First, you must determine the transaction log space used. Use the following to access data on how much space the transaction log is using and how much is available in the transaction log only.
To monitor your transaction log utilization data, perform the following steps:
Verifying Available Database Space
Sybase requires at least 1 MB of free space for reliable operation. When allocating your disk space, make sure you include this free space.
CiscoWorks database files are preallocated, so they will not visibly grow as you fill the database. As data is added, the access times on the files $NMSROOT/sybase/data/*.dat will change.
To determine the amount of available space in the database, perform the following steps:
Calculating Disk Space Utilization
To calculate disk space utilization, subtract the transaction log size and the used data space from the total disk space.This is the free memory available. To calculate available transaction log space, refer to the section "Verifying Available Database Space."
The following example calculates the free memory available based on the previous example.
Take the total size of the database and subtract the transaction log size and the data space used for the amount of free disk space available.
Using the estimates in the previous examples, the size of the database is 26 MB (or 26,624 KB), minus the transaction log size of 4 MB (or 4096 KB), minus the used data space of 862 KB, which equals available disk space of 21,666 KB or 81.4 percent available.
Purging Data Collected During Device Polling
Data collected during device polling is stored in the polling table. For example, if you have a table containing 20 MIB objects, the data collected from those 20 objects is stored in the polling table. You should periodically review your need to retain much of this data because its value diminishes rather rapidly, while its consumption of space keeps increasing.
This data can be purged without disturbing the configuration table structure, thereby eliminating the need to destroy the complete table and then reconstruct table parameters to reestablish a viable poll base.
There are two methods to purge data: a total purge of the table poll data or a partial erasure. The following procedure uses the SQL delete command through the ISQL interface.
To purge all data in a table, perform the following steps:
To partially purge table data, perform the following steps:
There are two ways to increase disk space allocated to the database.
You can display the amount of space currently being used by each table using the dbcc command in isql to verify the tables. For information on how to use the dbcc command, refer to the section "Monitoring Transaction Log Utilization," earlier in this chapter. Do not use the sp_spaceused command because it will not provide accurate information.
Enlarging Disk Space Using a Shell Script
The enlarge_nms script creates a new file of the size you want to enlarge the database by. The default filename is nms.dat and it adds new files by naming them as nms2.dat, nms3.dat, and so on. The default location for the file is $SYBASE/data, but you can change the location.
To expand database memory allocation using the shell script, perform the following steps:
Enlarging Disk Space Using a Command Sequence
To expand the database space allocation using the command sequence method, perform the following steps:
You can run the alter database portion of the enlarge sequence at any time after the database has been initialized. You do not need to initialize the disk again, unless you want more expansion space allocated than you defined the first time. For example, you run disk init with an expansion setting of 2048 (4 MB) and later attempt to run alter database with a setting of 5 MB. This would not be accepted because the value used in the alter database sequence exceeded the limit established in the disk init sequence.
Modifying the Database Transaction Log
The transaction log is automatically cleared periodically by the system.You can turn off the automatic clear function and clear the log manually, as well as increase the transaction log space.
Disabling the Automatic Clear Function
If you do not want the log to be cleared automatically, disable the clear function by entering the following commands:
If the automatic clear function is off, you can turn it on by using the same command sequence, substituting the word true for the word false as the last word in the first line (1>) of the command sequence.
Manually Clearing the Transaction Log
To manually clear the transaction log, enter the following command:
Enlarging the Transaction Log Space
There are two ways to enlarge the transaction log space on your CiscoWorks workstation:
Enlarging Using sp_extendsegment
To add space to the transaction log with the sp_extendsegment command, enter the following commands:
To add space to the transaction log with the sp_logdevice command, perform the following steps:
For further details, refer to the Sybase System Administration Guide.
There are two database backup procedures. The first is used to back up CiscoWorks software and database information. Use this method when you first receive your software. The second method is used to back up only data in the database on a regular maintenance schedule by using the backup_nms script.
For information on how to disable the CiscoWorks default of clearing the database automatically, so you can customize your back up procedures, refer to the section "Disabling the Automatic Clear Function."
Backing Up the Database and CiscoWorks Software
Follow the backup recommendations and processes described in your network management platform documentation. For detailed information on the UNIX dump, cpio, and tar commands, refer to the appropriate manual pages.
Because network maps can change frequently, and there could be several different maps, save at least one copy of each map in a separate directory for quick access.
The backup_nms script backs up the contents of the CiscoWorks database. It does not back up the CiscoWorks software.
Use this method to create a backup environment. After the first backup is completed, simply enter the backup script filename and the administrator password.
You must be a superuser to run the backup_nms script.
Using backup_nms the First Time
To use the backup_nms script to back up the contents of the CiscoWorks database, perform the following steps:
Using backup_nms for Subsequent Backups
After completing your first backup, the only requirement for subsequent backups is to enter the backup_nms command and the SA password. Subsequent backups will overwrite the file created in the first backup. If you want to retain the first or a subsequent backup, copy the backup file to another filename for safekeeping. The date of the last backup is in the file $NMSROOT/etc/DBMS_backup.log.
To complete a subsequent backup, perform the following steps:
If the environmental variable PASSWORD is set, the script can be run automatically from the Global Command Scheduler. For information on how to use the Global Command Scheduler application, refer to the section "Using the Global Command Scheduler" in Chapter 5.
If you have to recreate your database, run the CiscoWorks recover_nms script to perform the recovery.
To recover a database, perform the following steps:
This section describes how to move the database from one server to another. The servers can be in the same physical workstation or in different workstations. This procedure is for installations that have a preexisting Sybase database.
The process of moving a database involves running a script to define a destination server, creating a dump device in the source server and the destination server, creating the database (nms directory) on the destination server, dumping the database on the source server, and loading it on to the destination server. To point to the new dataserver, set your DSQUERY to the new dataserver and define it in the $SYBASE/interfaces file.
You need to ensure that both server names are located in the interfaces file on the source machine. If you intended to use the same server name, for example, the default SYBASE, edit the server name in the interface file on the source machine on which you are running the script.
Sufficient disk space must be available to hold the contents of nms (and your data) in a UNIX file (temporarily). The file will be written by Sybase on the source machine and read by Sybase on the destination machine.
The source server name will default to SYBASE in the absence of input.
To move the database, perform the following steps:
Using makesample to Recreate a Table File
Several CiscoWorks applications use the example polling tables named sample*. If you write over the sample tables or accidentally delete them, you can recreate them using the makesample script. The makesample script recreates the following tables: sample_mix, sample_traffic, and sample_error.
You can choose which variables to poll in the sample tables by selecting the pick menus located in the Device Polling fields. For more information on the sample tables, refer to the section "Creating a New Polling Table" in Chapter 4.
To recreate your sample tables, perform the following steps:
Copyright 1988-1996 © Cisco Systems Inc.
hostname# user_nms
# $NMSROOT/etc/nmstartup
#
cat $SYBASE/interfaces
query tcp sun-ether lol-ss2 8100
master tcp sun-ether lol-ss2 8100
console tcp sun-ether lol-ss2 8101
#
$SYBASE/bin/isql -Usa -P password
1>
quit
% isalive -Ulloyd -SSYBASE
% isalive -Psecret
#
$NMSROOT/etc/shutdown_nms
Server SHUTDOWN by request.
The SQL Server is terminating this process.
DB LIBRARY error: Unexpected EOF from SQL Server.
%
ps -vax |grep dataserver
#
$NMSROOT/etc/nmstartup
%
cat $SYBASE/interfaces
#
$SYBASE/bin/isql -Usa -P
password
1>
sp_password NULL,
newpassword
#
$SYBASE/bin/isql -Usa -P
current_password
1>
sp_password
current_password
,
newpassword
The
newpassword is the password you want to establish.
2>
go
Password changed.
return status=0
1>
quit
%
$SYBASE/bin/isql -Usa -P
password
1>
use nms
2>
go
1>
dbcc checktable(syslogs)
2>
go
Checking syslogs
The total number of data pages in this table is 2.
*** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.10%.
*** NOTICE: Space free on the log segment is 4.00 Mbytes, 99.90%.
Table has 35 data rows.
DBCC execution completed. If DBCC printed error messages, see your
System Administrator.
1>
quit
#
$SYBASE/bin/isql -Usa -P password
1>
dbcc checkalloc(nms)
2>
go
Checking nms
Database 'nms' is not in single user mode - may find spurious
allocation problems due to transactions in progress.
Alloc page 0 (# of extent=32 used pages=69 ref pages=69)
Alloc page 256 (# of extent=31 used pages=49 ref pages=49)
Alloc page 512 (# of extent=32 used pages=111 ref pages=111)
Alloc page 768 (# of extent=27 used pages=199 ref pages=199)
Alloc page 1536 (# of extent=1 used pages=0 ref pages=0)
Total (# of extent=126 used pages=431 ref pages=784) in this database
DBCC execution completed. If DBCC printed error messages, see
your System Administrator.
%
$SYBASE/bin/isql -Uuser -P
1>
sp_helpdb nms
2>
go
device_fragments
size
usage
-------------- ---------- -------------------
nms
4.000000 MB log only
nms 6.000000 MB data only
nms_data 16.000000 MB data only
device segment
------------- ------------------
nms default
nms logsegment
nms system
nms_data default
nms_data system
(return status = 0)
1>
quit
Total database size - Transaction log size - Used data space = Disk space available for data
26,624 KB (26 MB) - 4096 KB (4 MB) - 862 KB = 21,666 KB
#
$SYBASE/bin/isql -U
user
-P
1>
truncate table
mytable
2>
go
1>
quit
#
$SYBASE/bin/isql -U
user
-P
1>
delete
mytable
2>
where timestamp <
"month day year"
3>
go
1>
quit
# $NMSROOT/etc/enlarge_nms
Please enter the name of server you wish to modify or press return to modify the default server.
../data/nms2.dat.
Device to be created is nms2
UNIX file to be created is ../data/nms2.dat
Size of that file will be 8 MB
Are these values correct? [n] (enter Y for yes)
Do you want to use the new space for the transaction log? [N]
Increasing the size of nms database by 8 Megabytes.
Please be patient.
Extending database by 4096 pages on disk nms2.
%
$SYBASE/bin/isql -Usa -P
password
1>
disk init
2>
name = "
nms2
",
3>
physname = "
../data/nms2.dat
"
../data/nms2.dat with any filename.
4>
vdevno =
n
,
5>
size = n
6>
go
1>
alter database
nms
2>
on
database_name = n
3>
go
4>
quit
#
$SYBASE/bin/isql -Usa -P
1>
sp_dboption nms,"trunc.", false
2>
go
1>
use
database_name
2>
go
1>
checkpoint
2>
go
1>
quit
$NMSROOT/etc/nmlogreset
#
$SYBASE/bin/isql -Usa -P
password
1>
use
nms
2>
go
3>
sp_extendsegment logsegment, nmslog
4>
go
#
$SYBASE/bin/isql -Usa -P
password
1>
sp_helpdevice
2>
go
device_name physical_name description
nms . . /data/nms.data special, physical disk, 8 MB
status cntrltype device_number low high
2 0 2 33554432 33558527
1>
disk init
2>
name = "
nmslog
",
3>
physname = "
../data/nmslog.dat
",
4>
vdevno =
n
,
5>
size = 1024
6>
go
2 MB = 1024 2-KB blocks.)
1>
alter database
nms
2>
on nmslog =
2
3>
go
Extending database by 1024 pages on disk nmslog.
1>
sp_logdevice
nms
,
nmslog
2>
go
1>
sp_helpdb
nms
device_fragments size usage
nms 4 MB data only
nmslog 2 MB log only
2>
quit
%
su
#
cd $NMSROOT/etc
#
backup_nms
No nms/etc/DBMS_backup.log file, this must be the first backup.
Is this OK? [n]
Are you ready? [n]
%
backup_nms
#
recover_nms
Nmpolld is running. It must be stopped before performing the recovery. Kill it? [yes]
%
cd $NMSROOT/etc
%
move_nms
Please enter the name of server FROM which you are moving nms:
SYBASE
unless you have created your system in an environment different from that delivered by the factory.
Please enter password for sybase user 'sa' (on source server):
Enter your DBMS system administrator (sa) password (source
server).
Press Return.
Please enter the name of server TO which you are moving nms:
Is the password for "sa" on destination the same? [Y]. Press Return if the answer is yes.
The size of the nms on the source server is:
database_name database_size reserved data index_size unused
nms 4MB 3490KB 2422KB 142KB 926KB
How large would you like nms to be on your destination server?
Please enter the size of the database nms (in bytes).It must be equal or larger than the size given above:
Where can the temporary file be created on the source machine?
Please enter a path for the temporary file or press Return to accept usr/tmp:
Where will the temporary file be on the destination machine?
%
$NMSROOT/etc/makesample -U
nmsuper
-
Ppassword
