INFORMIX-OnLine comes with a set of powerful command line utilities that enable you to monitor, tune, and configure your database server. The command line utilities and the order in which we will discuss them is as follows: ONSTAT - shows server statistics ONSTAT is the command line utility that gets the most usage. It reads IDS's shared memory structures and provides lots of useful information about the state of your server. It does not place any locks on shared memory structure and uses very little overhead, so you can use it at any time. The information is current at the time the command is issued, and the data can change as you are using the command. There are more options for ONSTAT than any other Informix utility. Many of the options are debugging parameters that are not well documented and don't make sense to the average DBA. There are also many very useful options that help you manage your IDS server. ONCHECK - Check and display information about IDSs's disk space ONCHECK is the tool to check and display information about your dbspaces, blobspaces, chunks, tables, indexes, and disk pages. The purpose of this utility is to insure that your database server disk space has no inconsistencies. I like to think of this as the database version of the UNIX utility 'fsck' which checks file systems, or the DOS utility 'chkdsk' which checks DOS disk space. ONCHECK will place locks on all tables and databases that it needs to access. In some cases it will place an exclusive lock on a database or table and prevent other users from accessing the data. You need to be careful when you run ONCHECK to make sure it will not disrupt your users' work. It is a good idea to run ONCHECK when the IDS Server is in quiescent mode so it does not conflict with other users. ONMODE - change IDS operating mode The ONMODE utility has several key functions: it changes the operating mode of IDS, shuts IDS down, allows you to change some configuration parameters on the fly, and provides a means to kill user database connections. Figure 25 provides the complete syntax to the ONMODE command. Figure 25: ONMODE syntax onmode -abcDdFklMmnpQRrSsuyZz -a <kbytes> Increase shared memory segment size -b <version> Revert disk structures to an older version -c Perform a checkpoint -D <max PDQ priority allowed> Set max PDQ -d {standard|{primary|secondary <servername>}} set Data Replication server type -F Free unused memory segments -k Shutdown completely -l Force switch to next logical log -M <decision support memory in kbytes> Set size of Decision Support Memory -m Go to multi-user on-line mode from quiescent mode -n Set shared memory buffer cache to non-resident -O Override dbspace down blocking a checkpoint -p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio, shm, soc, or tli -Q <max # decision support queries> Set max number of Decision Support queries -R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file -r Set shared memory buffer cache to resident -S <max # decision support scans> Set max number of Decision Support Scans -s Shutdown to single user (Graceful shutdown) -u Shutdown and kill all attached sessions (Immediate Shutdown) -y Do not require confirmation mode changes -Z <address> heuristically complete specified transaction -z <sid> Kill specified database session id Shutting down the database server One of the most common uses of the ONMODE utility is to shutdown the database server. To immediately shutdown the server from any mode, type: onmode -ky The '-k' option takes the database server off-line and the 'y' avoids the prompt to confirm your action. This immediately take the server off-line, disconnecting all users. Any user in the middle of a transaction will have their transaction rolled back to the state before they started their transaction. Any work the user was doing will be lost. You must be the user 'root' or 'informix' to perform this function. Figure 26 shows the error message you will get if you are not logged in as 'informix' or 'root' to shutdown IDS. It also shows the message you and your users will receive when IDS has been shutdown and you try to access the database server. Figure 26: Shutting down IDS lester@merlin >onmode -ky Must be a DBSA to run this program lester@merlin >su informix Password: lester@merlin >onmode -ky lester@merlin >onstat - shared memory not initialized for INFORMIXSERVER 'train1' One useful method of invoking this command is to put it in the UNIX shutdown script for your machine. This way when the computer is stopped it will automatically stop IDS. Check with your UNIX System Administrator on the location of the script. I like to add a call to a separate shell script that uses ONMODE to shutdown the server and ONINIT to start it up, based on a parameter passed to the script. See Figure 27 for an example script that starts and stops multiple IDS Servers. A good way to test such a startup script is to execute it as root, using the Bourne Shell with none of the Informix environment variables set. Figure 27: IDS startup and shutdown script ############################################################################# # Module: %W% Date: %D% # Author: Lester B. Knutsen email: lester@access.digex.net # Advanced DataTools Corporation # Discription: Informix IDS startup/Shutodwn script # This script is used to start and stop 3 IDS Servers # used for training named: train1, train2, train3 ############################################################################# # Set Global environment variables ############################################################################# ## Set the location of Informix Programs INFORMIXDIR=/u3/informix7 export INFORMIXDIR ## Add the Informix Programs to your PATH PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin export PATH ############################################################################# # Process and shutdown server ############################################################################# ## Set the Database Server INFORMIXSERVER=train1 export INFORMIXSERVER ## Set the Informix Configuration File ONCONFIG=onconfig.train1 export ONCONFIG state=$1 case $state in start) oninit; echo "Informix Server: $INFORMIXSERVER Started";; stop) onmode -ky; echo "Informix Server: $INFORMIXSERVER Shutdown";; *) echo "usage: ifx.rc start|stop";; esac ############################################################################# # Process and shutdown server ############################################################################# ## Set the Database Server INFORMIXSERVER=train2 export INFORMIXSERVER ## Set the Informix Configuration File ONCONFIG=onconfig.train2 export ONCONFIG state=$1 case $state in start) oninit; echo "Informix Server: $INFORMIXSERVER Started";; stop) onmode -ky; echo "Informix Server: $INFORMIXSERVER Shutdown";; *) echo "usage: ifx.rc start|stop";; esac ############################################################################# # Process and shutdown server ############################################################################# ## Set the Database Server INFORMIXSERVER=train3 export INFORMIXSERVER ## Set the Informix Configuration File ONCONFIG=onconfig.train3 export ONCONFIG state=$1 case $state in start) oninit; echo "Informix Server: $INFORMIXSERVER Started";; stop) onmode -ky; echo "Informix Server: $INFORMIXSERVER Shutdown";; *) echo "usage: ifx.rc start|stop";; esac In addition to the 'onmode -k' option to shutdown, ONMODE has three other options to change the mode of IDS. The '-k' option completely shut down the database server and takes it off-line. There are two options that take the database server to quiescent mode. Quiescent mode is like a maintenance mode or single-user mode where you can access IDS with the utilities but users cannot connect. The command to gracefully take IDS to quiescent mode is: onmode -s The command to immediately take IDS to quiescent mode is: onmode -u The difference between these is that the '-s' option will wait until all users have disconnected before changing modes, and the '-u' option will change modes immediately and kill all connected users. To return to on-line mode rrom quiescent mode so users can once again access the database server, the command is: onmode -m Forcing a checkpoint A checkpoint is one of the key events when IDS syncs shared memory with what is on disk. Several activities depend on the last completed checkpoint. An archive takes its start date and time from the last checkpoint. You cannot delete a logical log that contains the last checkpoint. To force IDS to perform a checkpoint, use the following onmode command and option: onmode -c Forcing a switch in the current logical log Another option to ONMODE allows you to change the current logical log to the next logical log in sequence. This is required if you are going to backup the current logical log or to drop the current logical log. The command and option to change the current logical log is: onmode -l Free unused virtual memory segments As IDS runs, it will add additional virtual shared memory segments as needed. Since this operation has some overhead, IDS does not release unused memory segments, but saves them for future reuse. The 'onstat -g seq' command discussed earlier in the chapter shows you the current virtual memory segments. The command to force IDS to reorganize its virtual memory segments and free unused segments is: onmode -F This operation requires some overhead and will freeze all user processing while IDS reorganizes and frees this segment. Because of the overhead of free memory and then re-adding it later, this operation should only be done when required. Monitor your virtual memory segments with the command 'onstat -g seg'. When you notice an increase in the virtual memory segments, and you see that these are no longer being used, then it may be useful to free them with this command. A common occurrence of this is after running large weekly or month-end batch jobs and reports. These type of jobs will often require extra memory that will be used until the next cycle of processing. This is a good opportunity to use this command. Do not repeatedly run this command at short intervals to free memory. The overhead of freeing memory and then re-acquiring it will slow things down. Killing users' database processes ONMODE provides an option to kill and abort an individual user's database process. This option is aware of a user's database transaction and will rollback any work that was not committed. Operating system commands to kill a user's process (e.g. the UNIX kill -9 command) are not aware of a user's database connection and may not cleanly rollback their work. This can lead to corruption of tables or indexes. The correct procedure to kill a user's database process is: 1. Identify the user's session id using the ONSTAT command with one of the following three options: onstat -u onstat -g sql onstat -g ses onmode -z session_id Figure 28 shows an example of identifying the session id for the user 'lester' using 'onstat -u' and killing the session with 'onmode -z' The session id is 190. Figure 28: Terminating a user's session lester@merlin >onstat -u INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 28 days 11:52:49 -- 10656 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites a2d0018 ---P--D 1 informix - 0 0 0 114 486 a2d0458 ---P--F 0 informix - 0 0 0 0 5657 a2d0898 ---P--B 8 informix - 0 0 0 2 0 a2d1558 ---P--D 12 informix - 0 0 0 0 2 a2d1998 Y--P--- 190 lester 0 a3d1d50 0 1 18 0 5 active, 128 total, 17 maximum concurrent lester@merlin >onmode -z 190 lester@merlin onstat -u INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 28 days 11:53:00 -- 10656 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites a2d0018 ---P--D 1 informix - 0 0 0 114 486 a2d0458 ---P--F 0 informix - 0 0 0 0 5657 a2d0898 ---P--B 8 informix - 0 0 0 2 0 a2d1558 ---P--D 12 informix - 0 0 0 0 2 4 active, 128 total, 17 maximum concurrent Another delay will occur if the user was in the middle of a large update or load in a transaction. IDS will need to rollback the transaction. The general rule that I use is that if the user was in a transaction for 30 minutes loading data, it will take about 30 minutes to rollback their work. IDS must delete all the records it has inserted. You must let the rollback complete. Using ONMODE for configuration changes ONMODE has several options that allow you to change your configuration while the database server is up and running. This saves changing the ONCONFIG file, shutting down the server, and then restarting it with the new configuration. However, changes made using ONMODE are not written to the ONCONFIG file and will be lost when IDS is shutdown and restarted. The following options to ONMODE allow changes: -a <kbytes> Increase shared memory virtual segment size -b <version> Revert OnLine disk structures to an older version of OnLine (e.g 5, 6) -d {standard|{primary|secondary <servername>}} set Data Replication server type -n Set shared memory buffer cache to non-resident -O Override dbspace down blocking a checkpoint -p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio, shm, soc, or tli -R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file -r Set shared memory buffer cache to resident Decision support Configuration changes -D <max PDQ priority allowed> Set max PDQ -M <decision support memory in kbytes> Set size of Decision Support Memory -Q <max # decision support queries> Set max number of Decision Support queries -S <max # decision support scans> Set max number of Decision Support Scans The ONLOG utility allows you to debug transactions using the logical logs. This utility should be used with care as it can display lots of information. The best use of this utility is to research why a user's transaction failed. Figure 29 contains the syntax for this command. Note: Running ONLOG on the current logical log file (the default) will lock the log file and stop all user processing. Figure 29: ONLOG syntax onlog [-l] [-q] [-b] [-d <tape device>] [-n <log file number>] [-u <user name>] [-t <TBLspace number>] [-x <transaction number>] -l Display maximum information about each log record including hex dump -q Do not display program header -b Display information about logged BLOB pages (-d option only) -d Read from tape device -n Display the specified log(s) -u Display the specified user(s) -t Display the specified TBLspace(s) -x Display the specified transaction(s) The ONINIT utility starts IDS and without any other options brings it into on-line mode so users can connect and go to work. This is one of the key commands along with 'onmode -ky' to start and stop your database server. See Figure 27 for a script that uses these commands to automatically start or stop IDS. However, ONINIT is also one of the most dangerous commands. With a single option (-i) it will initialize you rootdbs, wiping out anything that was there and all your hard work. There may be times you will want to do this but be very careful and make sure all your environment variables are set correctly. Starting IDS The command to start IDS is very simple. Just type 'oninit', and it will use four environment variables to identify the database server to start. The environment variables are: INFORMIXDIR - Points to the directory where the Informix products are installed and is used by IDS as a base directory to locate other files it needs. PATH - This is used by your operating system to search for executables and must include the directory located in $INFORMIXDIR/bin. INFORMIXSERVER - This is the name of the IDS server you wish to start. This name is also located in your ONCONFIG file. ONCONFIG - This is the name of the configuration file IDS will use to start the database server. It is located in $INFORMIXDIR/etc. Once these are set and you are logged in as the user 'root' or 'informix', type 'oninit' to start the server. Figure 27 was an example of a script that will start several IDS servers. Normally you want your IDS server to start automatically every time you boot your computer. This script can be called from one of the UNIX startup scripts like '/etc/rc.local' to perform this function. Check with your operating system administrator to find the name of the UNIX startup script that you will need to call this script from. Figure 30 shows all the options to ONINIT. Figure 30: ONINIT syntax oninit [-I] [-p] [-s] [-y] [-V] [-v] -I Startup and initialize rootdbs. This will destroy any existing data in your rootdbs -p Startup and do not delete temp tables during shared memory initialization -s Startup and stay in quiescent mode -V Display version -v Startup in verbose mode. Many additional messages will display that are helpful in debugging problems. The '-I' option of ONINIT is very powerful and dangerous. It will startup IDS and initialize your rootdbs. This process is like formatting a dbspace and will destroy all data that is there. However, this option is very handy when you know what you are doing and want to initialize your rootdbs. When I need to configure several database servers it is easier to write a script to do it rather then type all the commands and edit all the options by hand. Figure 35, shows an example script that does this, and uses the next two utilities we will talk about to initialize a rootdbs, set up several dbspaces, and move the logical logs to a separate dbspace. Verbose Option Another helpful option is the lower case '-v' for verbose. This displays extra messages as IDS goes through the different stages on initialization and is very helpful when debugging an installation. Figure 31 shows the output from this option on my training system. Figure 31: Verbose startup of IDS with the '-v' option lester@merlin >oninit -v Reading configuration file '/u3/informix7/etc/onconfig.train1'...succeeded Creating /etc/.infxdirs ... succeeded Creating infos file "/u3/informix7/etc/.infos.train1" ... "/u3/informix7/etc/.conf.train1" ... succeeded Writing to infos file ... succeeded Checking config parameters...succeeded Allocating and attaching to shared memory...succeeded Creating resident pool 2160 kbytes...succeeded Creating buffer pool 402 kbytes...succeeded Initializing rhead structure...succeeded Initializing ASF ...succeeded Initializing Dictionary Cache and Stored Procedure Cache...succeeded Onlining 0 additional cpu vps...succeeded Onlining 2 IO vps...succeeded Forking main_loop thread...succeeded Initialzing DR structures...succeeded Forking 1 'ipcshm' listener threads...succeeded Forking 1 'tlitcp' listener threads...succeeded Starting tracing...succeeded Initializing 1 flushers...succeeded Initializing log/checkpoint information...succeeded Opening primary chunks...succeeded Opening mirror chunks...succeeded Initializing dbspaces...succeeded Validating chunks...succeeded Forking btree cleaner...succeeded lester@merlin >Initializing DBSPACETEMP list Checking database partition index...succeeded Checking location of physical log...succeeded Initializing dataskip structure...succeeded Checking for temporary tables to drop Forking onmode_mon thread...succeeded Verbose output complete: mode = 5 lester@merlin >onstat - INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 00:00:47 -- 10656 Kbytes Figure 32: Startup messages in the IDS Message Log Sat Aug 9 23:52:16 1997 23:52:16 Event alarms enabled. ALARMPROG = '/u3/informix7/log_full.sh' 23:52:17 DR: DRAUTO is 0 (Off) 23:52:18 INFORMIX-OnLine Initialized -- Shared Memory Initialized. 23:52:18 Physical Recovery Started. 23:52:18 Physical Recovery Complete: 0 Pages Restored. 23:52:18 Logical Recovery Started. 23:52:21 Logical Recovery Complete. 0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks 23:52:22 Dataskip is now OFF for all dbspaces 23:52:22 On-Line Mode 23:52:22 Checkpoint Completed: duration was 0 seconds. This utility allows you to add, drop and change the mirroring of dbspaces. It is the equivalent of the menu options in ONMONITOR. If you seldom change your dbspace configuration it is easier to use the menus in ONMONITOR. The ONSPACES utility is very useful if you need to create scripts to change your dbspace configuration. Figure 33 has the syntax for this utility. Figure 35 is an example of a script that uses this utility to configure a database server from scratch. Figure 33: ONSPACES syntax onspaces { -a spacename -p pathname -o offset -s size [-m path offset] | -c {-d DBspace [-t] | -b BLOBspc -g pagesize} -p pathname -o offset -s size [-m pathoffset] -d spacename [-p pathname -o offset] [-y] | -f [y] off [DBspace-list] | on [DBspace-list] | -m spacename {-p pathname -o offset -m path offset [-y] | -f filename} | -r spacename [-y] | -s spacename -p pathname -o offset {-O | -D} [-y] } -a Add a chunk to an existing DBspace or BLOBspace -c Create a new DBspace or BLOBspace -d Drop a DBspace, BLOBspace or chunk -f Change dataskip default for specified DBspaces -m Add mirroring to an existing DBspace or BLOBspace -r Turn mirroring off for a DBspace or BLOBspace -s Change the status of a chunk ONPARAMS - Change logical and physical log configuration This utility allows you to add logical logs, drop logical logs, and change the location of the physical log. Figure 34 shows the syntax for this command. This utility is handy because you can do some things with it that cannot be done with ONMONITOR. It allows you to add logical logs of different sizes and locations. One common use of this is after you have set up your server, you will often want to move your logs out of the rootdbs into their own dbspaces. Figure 35 contains an example of a script using this command to move the logical logs to their own dbspace and the physical log to its own dbspace. Figure 34: ONPARAMS syntax onparams { -a -d DBspace [-s size] | -d -l logid [-y] |-p -s size [-d DBspace] [-y] } -a Add a logical log -d Drop a logical log -p Change physical log size and location -y Answer YES to all questions Figure 35: Script to initialize a database server, add dbspaces, and add logs ############################################################################# # Module: %W% Date: %D% # Author: Lester B. Knutsen # Advanced DataTools Corporation # Discription: Script to Creat a training environment Informix IDS # database server ############################################################################# ############################################################################# # Set Global environment variables ############################################################################# ## Set the location of Informix Programs INFORMIXDIR=/u3/informix7 export INFORMIXDIR ## Add the Informix Programs to your PATH PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin export PATH ## Set the Database Server INFORMIXSERVER=train2 export INFORMIXSERVER ## Set the Informix Configuration File ONCONFIG=onconfig.train2 export ONCONFIG ############################################################################# # Check that this is the correct ONCONFIG and INFORMIXSERVER ############################################################################# set `grep "^DBSERVERNAME" $INFORMIXDIR/etc/$ONCONFIG` if [ "$2" != "$INFORMIXSERVER" ] then echo "Invalid INFORMIXSERVER: $INFORMIXSERVER" exit fi echo "Creating and Initializing INFORMIXSERVER: $INFORMIXSERVER" echo "Press RETURN to continue" read ans ############################################################################# # Create the disk devices - this training server uses cooked files # but you could replace these command with the commands to use raw files. ############################################################################# touch /u3/dev/rootdbs2 touch /u3/dev/logdbs2 touch /u3/dev/rootdbsM2 touch /u3/dev/data2dbs2 touch /u3/dev/tempdbs2 # Set owner to informix - group informix chown informix:informix /u3/dev/rootdbs2 chown informix:informix /u3/dev/logdbs2 chown informix:informix /u3/dev/rootdbsM2 chown informix:informix /u3/dev/data2dbs2 chown informix:informix /u3/dev/tempdbs2 # Set permissions to read/write owner and group only chmod 660 /u3/dev/rootdbs2 chmod 660 /u3/dev/logdbs2 chmod 660 /u3/dev/rootdbsM2 chmod 660 /u3/dev/data2dbs2 chmod 660 /u3/dev/tempdbs2 ############################################################################# # Initialize the rootdbs - after this anything that was there is wipped out ############################################################################# oninit -i ## must sleep long enough for the sysmaster database to be created or the ## next step will fail. sleep 200 FuGEN ad# Display the log onstat -m ## now shutdown to single user mode onmode -sy # Display status onstat - ############################################################################# # Creat the additional Dbspaces ############################################################################# echo "Creating logdbs..." ## Create dbspace for logical logs onspaces -c -d logdbs -p /u3/dev/logdbs2 -o 0 -s 25000 echo "Creating datadbs..." ## Create dbspace for data onspaces -c -d datadbs -p /u3/dev/data2dbs2 -o 0 -s 50000 echo "Creating tempdbs..." ## Create dbspace from temp tables onspaces -c -d tempdbs -t -p /u3/dev/tempdbs2 -o 0 -s 10000 ############################################################################# # Create additional logical logs in logsdbs ############################################################################# echo "Creating additional Logical Logs" onparams -a -d logdbs -s 4000 onparams -a -d logdbs -s 4000 onparams -a -d logdbs -s 4000 onparams -a -d logdbs -s 4000 onparams -a -d logdbs -s 4000 onparams -a -d logdbs -s 4000 echo "Creating archive to activate new Logical Logs" ontape -s ############################################################################# # Show message log and status ############################################################################# onstat -m echo "IDS Configuration complete" ############################################################################# ONTAPE is the basic utility to backup and restore the whole IDS server. The backup may be performed while the system is running and while users are accessing and updating data. In addition to your basic backup and restore ONTAPE performs a few other functions: Backups the logical logs. Provides a utility to change the logging mode of a database. Performs a restore to start Data Replication. IDS backup strategies and procedures are very important and merit a whole chapter. This is just a quick overview of ONTAPE's syntax and a few options. Figure 36 contains the syntax for ONTAPE. Figure 36: ONTAPE syntax ontape { -a | -c | -l | -p | -r [-D DBspace_list] | -s [-L archive_level] [-A database_list] [-B database_list] [-N database_list] [-U database_list] } -a Automatic backup of logical logs -c Continuous backup of logical logs -l Logical restore -p Physical restore for Data Replication (HDR) -r Full restore DBspaces/BLOBspaces as listed -s Archive full system -A Set the following database(s) to ansi logging -B Set the following database(s) to buffered logging -N Set the following database(s) to no logging -U Set the following database(s) to unbuffered logging The basic ONTAPE restore option restores the whole database server. You cannot restore just one table or database. Use ONUNLOAD and ONLOAD to perform database and table level backups. ONTAPE can only restore to a the same dbspace configuration. The disk layout must match exactly the disk layout when the backup was made. The ONTAPE backup is binary and can only be restored to a computer which is binary compatible and using the same version of Informix. Backing up the IDS server ONTAPE provides a way for you to backup the whole database server while it is running. ONTAPE will keep track of all changes made during its backup, and during a restore rollback any incompletely backed-up changes. The command to start a backup is: ontape -s ONTAPE uses the parameters in the ONCONFIG file to determine the tape device, block size and tape size. These parameters are: TAPEDEV /dev/tapedev # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 1024000 # Maximum amount of data to put on tape (Kbytes) Using ONTAPE requires a dedicated terminal and tape drive during backups only. It will also require an operator to monitor backups and change tapes as needed. For the backups to be used in a restore, the tapes must be labeled carefully and coordinated with Logical Log backup. Figure 37 contains an example of an IDS backup using ONTAPE. Figure 37: ONTAPE backup informix@merlin >ontape -s Please enter the level of archive to be performed (0, 1, or 2) 0 Please mount tape 1 on /dev/rmt/0 and press Return to continue ... 10 percent done. 20 percent done. 30 percent done. Tape is full ... Please label this tape as number 1 in the arc tape sequence. This tape contains the following logical logs: 9 Please mount tape 2 on /dev/rmt/0 and press Return to continue ... Informix does not officially support backing up to disk with ONTAPE but it can be done. Figure 38 contains an example of a shell script that could be used to backup IDS to a disk file. This could be run by the UNIX Cron facility to automatically backup your server at a set time. However, the backup must be small enough to fit on disk. Figure 38: Shell script to backup IDS to disk #################################################################### # Shell script to backup Informix IDS to disk #################################################################### ## Set Informix environment variables ## change these to match your configuration INFORMIXDIR=/usr/informix7.1 export INFORMIXDIR PATH=$INFORMIXDIR/bin:$PATH export PATH ONCONFIG=onconfig export ONCONFIG INFORMIXSERVER=online1 export INFORMIXSERVER ## Echo message to log file echo "Archive Informix IDS for $INFORMIXSERVER" ## Check for valid backup device, prevents accidentally overwriting set `grep "^TAPEDEV" $INFORMIXDIR/etc/$ONCONFIG` if [ "$2" != "/u3/backup/online1.bak" ] then echo "Invalid TAPEDEV $2" date else echo "Archive to TAPEDEV $2" date ## Start ontape and respond to prompts - the following spacing is key ## There must be a 0 for the level followed by blank line for the ## the response to the prompt. { ontape -s <<EOF 0 EOF } | tail -5 ## Only read the last 5 lines to prevent filling up your log when errors echo "Archive Completed" fi IDS must be off-line to perform a restore. The restore will wipe out all your current data and configuration. The disk layout must be exactly configured the same as when you created the backup. Note: Before you begin, write protect your tape. The restore process has a confusing prompt asking "do you want to backup your logical logs?". I know DBA's who have responded yes to this prompt and accidentally wiped out their restore tape. The command to start a restore is: ontape -r Figure 39 shows the full dialog of prompts during the restore process. Figure 39: Performing an IDS restore informix@merlin >ontape -r Please mount tape 1 on /dev/rmt/0 and press Return to continue ... Archive Tape Information Tape type: Archive Backup Tape Online version: INFORMIX-OnLine Version 7.13.UC2 Archive date: Fri Sep 27 17:48:39 1996 User id: informix Terminal id: /dev/pts/0 Archive level: 0 Tape device: /dev/rmt/0 Tape blocksize (in k): 16 Tape size (in k): 2000000 Tape number in series: 1 Spaces to restore:1 [rootdbs ] Archive Information INFORMIX-OnLine Copyright© 1986-1996 Informix Software, Inc. Initialization Time 09/03/96 17:31:15 System Page Size 2048 Version 4 Archive CheckPoint Time 09/27/96 17:48:44 Dbspaces number flags fchunk nchunks flags owner name 1 1 1 1 N informix rootdbs Chunks chk/dbs offset size free bpages flags pathname 1 1 0 50000 38641 PO- /u3/dev/dbspace713 Continue restore? (y/n)y Do you want to back up the logs? (y/n)n Restore a level 1 or 2 archive (y/n) n Do you want to restore log tapes? (y/n)y Roll forward should start with log number 9 Please mount tape 1 on /dev/rmt/0 and press Return to continue ... Do you want to restore another log tape? (y/n)n Program over. informix@merlin >onstat - INFORMIX-OnLine Version 7.13.UC2 -- Quiescent -- Up 00:10:35 -- 8976 Kbytes Next ONTAPE will prompt you if you would like to backup logical logs. This prompt is very confusing if you have not done this a few times. If your system had crashed and IDS was able to backup additional logs it will help you in the recovery process. Put a NEW tape in the drive and respond yes. Do NOT leave your restore tape in or IDS may overwrite it. If you do not want to backup any current logs, respond NO. Now ONTAPE will start the restore. This is no progress report on the restore like there is on the backup. Be patient and wait. If more than one tape is required you will be prompted for it. After the level 0 tape has been restored ONTAPE will ask if you have a level 1 or 2 backup to restore. When all backup levels have been restored, ONTAPE will prompt you for logical log tapes to restore. Start with the tape containing the logical log you are prompted for. You cannot skip logical logs or restore them in a different order. If you do not have any logical log backups simply respond NO to these prompts. Finally, IDS will start to roll forward, or roll back any transactions necessary. When this is completed the IDS server will be in quesicent mode ready for you to check. Backing up logical logs ONTAPE is also used to backup logical logs. The logical log backup device is controlled by the following parameters in your ONCONFIG file: LTAPEDEV /dev/tapedev # Logical Log tape device path (e.g /dev/rmt/0) LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) Note: When all Logical Logs are full IDS will halt all processing until you backup the logs. This will stop all user activity. There are two forms of Logical Log Backup: 1) Continuous Backup (ontape -c) - this runs the ontape process backing up logical logs non-stop until you stop. 2) Automatic Backup (ontape -a) - the ONTAPE process runs backing up all logical logs that need to be backed up. Once all logs have been backed up the process stops. Issues with continuous backup of logical logs to tape Continuous backups requires a dedicated terminal or window in which it runs. This is where it will display tape change prompts and expect an operator to respond to these prompts. It also requires a dedicated tape drive and an operator who will monitor the progress of its backups. The operator must carefully label tapes so they can be used in a restore. To stop continuous backups simple enter "Control-C" or the interrupt character on your system. Anytime the continuous backups is aborted and restarted a new tape must be used or else the old tapes will be overwritten. Each execution of continuous or automatic backups requires a new tape. ONTAPE backups cannot append to the end of the last tape. Continuous backups must also be restarted with a new tape after your system is rebooted. Issues with automatic manual backup of logical logs to tape Automatic backup of logs requires a dedicated terminal and tape drive only during the actual backups. This tape drive may be shared with other activities. However, it still requires an operator to monitor the logs, and start a backup before they all become full. It also requires careful labeling of tapes and coordinating with ontape archives. Figure 40 shows the screen display of the automatic backup. Figure 40: Automatic backup of logical logs Performing automatic backup of logical logs. Please mount tape and press Return to continue ... This tape contains the following logical logs: 11 - 12 Please label this tape as number 1 in the log tape sequence. Please mount next tape and press Return to continue ... *** The tape was not changed *** Please mount next tape and press Return to continue ... This tape contains the following logical logs: 12 - 14 Please label this tape as number 2 in the log tape sequence. Please mount next tape and press Return to continue ... This tape contains the following logical logs: 14 - 16 Please label this tape as number 3 in the log tape sequence. ONTAPE is also used to change the logging mode of a database. To change a database from no logging to some form of logging requires a backup. The command to perform a backup and change a database from no logging to buffered logging is: ontape -s -B database_name ONUNLOAD and ONLOAD - Unloading and loading databases and tables The ONUNLOAD utility and the corresponding ONLOAD utility provide a way to save whole tables or databases in a binary format to tape or disk. These two utilities copy whole pages from dbspaces and save the results in binary format. They must work together. Only ONLOAD can read and load the results of an ONUNLOAD. If you need to unload data in ASCII or text format use the SQL unload statement of DBEXPORT. The advantage of these two utilities is that they are fast. They copy whole pages of data including existing indexes structures. This makes it very fast to reload because indexes do not need to be rebuilt. Also, since the output is stored in binary format, there is some security in the data being protected. There are a few drawbacks to these two utilities: Data is not portable. It can only be loaded using the same version of IDS on the same type of machine. Since the data is stored in binary format the operations need to be performed on computers that are binary compatible. Data is not compressed. Since the data is copied in whole pages, empty data space and empty index structures on a page remain and are reloaded on the target system. SQL unload and load will rebuild the data on pages and rebuild all indexes compressing the data. The utilities will require an exclusive lock on the table or database being unloaded and loaded. These utilities are useful in several ways: They provide fast table level backups. They provide fast database level backups. When transferring tables or databases to other systems with different dbspace layouts. When moving databases or tables from one dbspace to another. Figure 41 contains the syntax for the onunload command and Figure 42 contains the syntax for the onload command. Figure 41: ONUNLOAD syntax onunload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>] <database> [:[<owner>.]<table>] -l Use logical log tape configuration from ONCONFIG file -t Tape devices overriding TAPDEV in ONCONFIG -b Tape block size overriding size in ONCONFIG -s Tape size overriding size in ONCONFIG onload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>] [-d <Dbspace>] <database>[:[<owner>.]<table>] [{-i <old indexname> <new indexname>}] [{-fd oldDBspname newDBspname}] [{-fi indexname oldDBspname newDBspname}] -l Use logical log tape configuration -t Tape devices -b Tape block size -s Tape size -d DBspace name -i Rename index during load -fd Change data fragment dbspace -fi Change index fragment dbspace First we need to unload the items table. ONUNLOAD requires that the file exist if you are unloading to disk. Our first step is to create an empty file using the UNIX touch command. If you are unloading to tape you can skip this step because the tape device already exists. touch items.onunload Next we execute the unload: onunload -t items.onunload stores7:items Now we need to use SQL and dbaccess to drop the items table. We are piping the SQL statements to dbaccess. dbaccess stores7 - <<EOF drop table items; EOF The final step is to reload the items table into a new dbspace. For this example we will load it into a dbspace named itemsdbs. onload -t items.onunload -d itemsdbs stores7:items This will create the items table in the new dbspace.
When all the electrical power is about to fail, or the computer is shutting down for whatever reason, you don't have time to ask all users to log off the database server. And if you do not shutdown IDS, it will crash in an inconsistent state, with data in memory buffers that is not been written to disk, and users in the middle of transactions. When IDS is later restarted in will start a recovery mode to clean up from the crash, but this can take time and there may be problems.
Changing IDS modes
2. Use the following omode command to terminate the user's session:
When you terminate a user's session, their processing may not stop immediately. If they are performing a large query, data may be buffered up on the user's program and they may continue to receive data. Once the buffer is empty, the user will receive an error message indicating the database connection was lost.
ONLOG - debug the logical log
ONINIT - initialize and start-up functions
Initializing the root dbspace
Figure 32 shows the output to the IDS message log of a successful startup. There are two key messages you need to look for in the message log when IDS starts. One is the message 'Physical Recovery Completed: 0 Pages Restored', and the other is 'Logical Recovery Complete'. The zeros for 'Pages Restored' and 'Rolled Back' mean that everything was shutdown cleanly and restarted cleanly with no loss of users' work.
ONSPACES - Adding, deleting, and changing mirroring of dbspaces
Note: You can only drop a dbspace if it is completely empty.
In order to drop a logical log, the log must be backed up and cannot contain the current checkpoint or current logical log.
ONTAPE - the IDS backup and restore utility
Limitations of ONTAPE
Changing TAPEDEV to /dev/null and performing a backup will reset IDS's internal parameters without performing an actual backup.
Backing up to disk
Restoring an IDS server
First ONTAPE will display the disk configuration as it was when the backup was performed. This gives you a chance to verify that you have created the correct devices and links.
When you do not want to backup Logical Logs to tape, set LTAPEDEV to equal "/dev/null". IDS understands this and frees the logical logs as soon as they can be reused without a backup. Otherwise, you must backup your logical logs before they can be reused.
Changing logging mode for a database
If you want to change the logging mode of a database without performing a complete backup simply change TAPEDEV in your ONCONFIG file to "/dev/null". Then run the ONTAPE command listed above. Be sure to change TAPEDEV back to its original after you are done.
Figure 42: ONLOAD syntax
As an example of using these utilities let's take the steps required to move the items table in the stores7 database from one dbspace to another. These utilities are perfect for this task because they are fast.
This will create a binary image of the items table using our file.
Still Reading Commercial Emails For Free? Receive Emails On Topics That Interests You And Get Paid For It! Get $10 Just to signup! Click Here
Custom Search
Sunday, October 12, 2008
Informix Utilities
at
6:55 PM
·
Labels: Informix
Subscribe to:
Post Comments (Atom)
Travel Packages
Travel package in malaysia
as low as USD29.00
Join viewmalaysia.com now!
| Packages: Langkasuka Hotel |
| Duration: Per Night |
| Location:Langkawi |
| 80% of 90,000 Hotels worldwide available at lowest price guaranteed! |
0 comments:
Post a Comment