First, the common command
OnInit changes the system from off-line mode to on-line mode
Oninit-iy (Initialize database, delete all dbspace and chunk)
Oninit-s (offline--> static)
Onmode-ky offline
Onmode-s Graceful shutdown-->quiescent (online to static, let user process complete)
Onmode-u Immediate shutdown-->quiescent (online to static, all users terminate immediately)
Onmode-m quiescent--->online
Onmode-z SID Deletes a session
Onmode-l move the logical log currently in use down
Onmode-c Force checkpoint operation that Onmonitor->force Ckpt
Onstat-View server Status
onstat-d view Dbspace and chunk situations
Onstat-l View Logical Log
Onstat-f View page refreshes and types written to disk, there are three ways to write pages from a shared memory buffer to disk (FG: Foreground write LRU: Queue write Chunk: block write)
Onstat-r Print LRU queues
Onstat-p the basic I/O and performance profile of the display system
Onstat-c View Onconfig File
ONSTAT-M View Message Log
Onstat-f view Dbspace affected by Dataskip
Onstat-x View Transactions
Onstat-k Display all active locks
Onstat-g sub_option Run Multiple threads option
Onstat-g Ses/sql
Onstat-r <value> Repeat options every <value> second
Onstat-g Act shows all activity leads in the system
Onstat-g Ath-r 2 Every two seconds to show clues to all activities in the system
Onstat-i Interactive mode
>sql
>sql SID
IPCS view of Shared memory
IPCRM Delete a shared memory
Ipcrm-m ID
Ipcrm-s ID
Ontape command:
1) 0 level (level one or two) backup
$>ontape-s
$>ontape-s-L 0
$>ontape-s-L 1
$>ontape-s-L 2
Automatic backup of logical log files
$>ontape-a
3 Continuous logical log backup (log file full of one, online system automatically backup one, you can disk tape to tape drive, let IDs automatically back up. To occupy the tape drive)
$>ontape-c
4 Backup recovery (corresponding to level 0 or level one or level two backup)
$>ontape-r (see Ontape Recovery Document for detailed procedure)
5 Backup Restore a few dbspace (must have a logical log backup, or after the physical recovery, or can not use)
$>ontape-r-D dbs1 dbs2 ...
6 The physical recovery used when configuring HDR
$>ontape-p
7) Modify the database log mode
$>ontape-s-B db_name modified to Buffer_log-buffered log mode
$>ontape-s-u db_name modified to Unbuffer_log is not buffered log mode
$>ontape-s-N db_name modified to no log mode the database does not contain logs, that is, transactions are not supported, and any modifications to the database are not recorded in the logical log
$>ontape-s-A db_name is modified to ANSI logging mode and cannot be changed from this mode to other modes, so it is generally not used.
Oncheck command
Oncheck-pe Extend
Oncheck-ce
ONCHECK-CR reserved Pages
ONCHECK-CD tblspace data rows including bitmap
ONCHECK-CI Table Indexes databases
Actions on logical logs:
Log on as an Informix user,
$ onmode-uy (switch from online to quiescent state, all users abort immediately)
ONMODE-M (switch to online)
$ onparams-a-D logdbs-s 100000
Where Logdbs is the dbspaces name, the-s 100000 indicates an increase in 100M space.
Execute the command 4 times in a row so that the new logical log space is 500M in total and can be
Onstat-l View the logical log, the next step is to delete the previous 3 old logical logs,
Make a 0-level backup before deleting
# Ontape-s-L 0
Perform this command to make a level 0 backup recommendation: If allowed to work every day at the end of the business system
After doing a level 0 backup, after the backup to manage backup tapes, do a good job marking.
$ onparams-d-L Logid
Logid is a logical log ID number that can be viewed with onstat-l and then deleted according to the ID number
Except 3 old logical logs.
Move down the logical log that is currently in use
$onmode-L
Iii. dbspace and database import and export
1. Add a new dbspace: (datadbs,15m, offset to 0)
Onspaces-c-D datadbs-p/home/informix/datadbs-o 0-s 15000
2. Add a chunk to the dbspace of Datadbs: (DATADBS_CHUNK1)
Onspaces-a datadbs-p/home/informix/datadbs_chunk1-o 0-s 15000
3. Delete the above chunk
onspaces-d datadbs-p/home/informix/datadbs_chunk1-o 0
4. Delete Dbspace (delete only if there is no data in the Dbspace space to be deleted)
Onspace-d Datadbs
To export a database:
Use the Dbexport tool to unload the data into text and load it onto other servers.
(1) The following steps are to unload the load:
Register with Informix User
Dbexport Cleardb-o Workdir-ss
When the system prompts Dbexport completed. Data is unloaded.
which
-SS ensure that the database library information or table information is retained such as log mode, initial extent size, lock mode, table Dbspace, etc.
-o Specifies that the directory data that holds the unload data is stored in the directory Cleardb.exp directory containing cleardb.sql and *.UNL-like files.
The hint information is stored in the Dbexport.out file.
(2) The steps to load the text are as follows:
Use Informix User registration to ensure that the database is in On_line state and that there is no database with the same name on the server.
Dbimport Cleardb-i Workdir
When the system Dbimport completed. Prompts for data loading complete.
which
-i specifies where to load from.
How to uninstall the database to a tape device without destroying the library's information, such as row-level locks, and mount it on a different server.
1 The steps to uninstall are as follows:
Register with DBA User
Insert the tape that holds the data into the tape drive, confirming that the tape and the tape drive are in good working condition.
Dbexport cleardb-t/dev/rmt/0m-b 512k-s 2048000k-ss
When the system prompts Dbexport completed. Data is unloaded.
which
-SS ensure that database information or table information is kept as log mode, initial extent size, lockmode, table location dbspace
-T tape device/dev/rmt/0m
-S tape Capacity 2G
-B Block Size 512KB
Prompt information is stored in the Dbexport.out file
2 The steps of loading are as follows:
Register with DBA User
Place the tape that holds the unload data on the tape drive, confirm that the tape drive is normal, confirm that the database system is in On_line state, and that there is no database with the same name on the server.
$ dbimport cleardb-t/dev/rmt/0m-b 512k-s 2048000k
When the system Dbimport completed prompts the data to be loaded. The hint information is stored in the Dbimport.out file.
Rebuild table:
1) dbschema-d dbname-t tabname-ss tabname.sql/* Put the table structure in tabname.sql*/
Edit this file and add a new area length at the end of the CREATE TABLE statement
Temporarily mark the Tabname.sql portion of the generated table index as a description statement, that is, precede the index sentence with "{}", for example:
{CREATE INDEX XL on customer (last_name), create index x2 on customer (CUST_NBR);
It is best to build the index later, because the table can be loaded quickly, and the index can be generated in continuous disk space to make it more efficient.
2) Lock the table
Lock table TabName in exclusive mode;
3 unload the table to disk or tape using SQL UNLOAD
Unload to File_or_tape_path select * from TabName
Unload to "/USR/DATA/UNLOAD/CUSTOMER.UNL" select * from Customer;
Check the number of rows in the table (with the table/info command in SQL) to verify that the uninstall was successful. Compare this number with the SQL display message after the mount completes.
Use this to verify the number of rows in the File_or_tape_path:
Wc-l File_or_tape_path
4) Delete Table
5) Use dbacess to produce the form and run Tabname.sql. Do not generate an index at this time
6 reload the form with SQL load from or with Dbload
Load from File_or_tape_path inserts into TabName;
The load form "/USR/DATA/UNLOAD/CUSTOMER.UNL" inserts into the customer;
For the log database, the Mount statement above may produce long transactions or "To many lock" errors. It is recommended to use utilities such as Dbload
Inserts the data into the table. The Dbload-n option forces a commit work after inserting a certain row (1000 lines are recommended).
Check the number of rows in the table (with the table/info command in SQL) to verify that the mount was successful.
7 If you want to produce the index, run the first step to win the bid for the tabname.sql part of the description statement.
8) Update table statistics
Update STATISTICS on tabname;
Syntax OnInit [-S] [-i] [-P] [-y]
OnInit changes the system from off-line mode to on-line mode
Oninit-s changes the system from off-line mode to quiescent mode
Oninit-i Initialization System
Oninit-p when shared memory initialization, do not search, delete temporary tables
Oninit-y answer Yes to prompts automatically
Oninit-v Add this option to display the OnInit process
oninit--type this command to get help with
The OnInit command is used to change the operating mode of the system. The-i option is used to initialize the root dbspace of the system. Note that once the root-dbspace is initialized, it is equal to the entire database system being initialized.
If the user wishes to automatically start the dynamic server system when the computer starts, add the OnInit command (without any options) to the system's initial file (/ETC/RC on many UNIX systems).
Onmode command
Syntax: Onmode [-K] [-m] [-S] [-u] [-y]
Onmode-k execute immediate shutdown and turn the system into off-line mode
Onmode-m changes the system from quiescent mode to on-line mode
ONMODE-S Executive Graceful Shutdown
Onmode-u Executive Immediate Shutdwon
Onmode-y answer Yes to prompts automatically
The Onmode command is also used to change the running mode of a dynamic server. In addition to the above options, Onmode has many options unrelated to changing the operating mode of the system.
-m mirror mirrored device with full path name and offset (KB)
-O offset offset (KB)
-P pathname Chunk the full path name of the device
-S size dbspace size (KB)
-T Create temporary dbspace
The onspaces command is used to create data space, temporary space, and space for storing BLOB data (blobspace). Type onspaces--to get online Help for this command. Using onstat-d or onstat-d, you can see important information about the data space in the system. Includes: Chunk status, Idle, the number of reads and writes per chunk. Multiple system spaces that may be included in the system, especially after data fragmentation, we recommend that users use the command line to create data space.
You can create a data space by using the following command:
Onspaces-c-D datadbs1-o 0-p/dev/rrvol3-s 60000
You can create a temporary data space in the following ways:
Onspaces-c-D tempdbs1-t-o 0-p/dev/rrvol5-s 80000
In the system, the temporary data space is very important, in general, multiple temporary data space should be distributed on a separate physical device.
Removing data space with the Onspaces command
Add or remove chunks
Syntax: onspaces-a-D [-M] [-O] [p]
-A spacename new chunk for Dbspace
-M pathname mirrored device full path name and offset (KB)
Offset of the-O offset primary device (KB)
-P pathname Chunk the full path name of the device
-S size Chunk
-D spacename Delete chunk
Offset of the-O offset chunk device (KB)
Onspaces not only creates the data space, but also deletes the data space, the temporary data space, or the space where the BLOB data is stored. When you delete a data space, you must first guarantee that it is useless, that is, there is no database or table on the data space.
To delete data space, type the following command: onspaces-d dbspace_name/blobspace_name
The data space is originally composed by a chunk (first chunk), once its space is exhausted, the user must append chunk in order to improve the system performance, the user needs to calculate when allocating chunk for the data space to ensure that its size can meet the future needs, otherwise, when the chunk is appended, It is physically not necessarily adjacent to the previous chunk, resulting in increased time to read data. The calculation of space requirements will be elaborated in later chapters. In addition to using the Onspaces command to add or remove chunk from the data space, you can use this command to perform the following tasks: Start mirroring, abort mirroring, or change the state of chunk.
For example, you can add chunk to your data space with the following command:
If you can remove chunk from the data space in the following ways:
onspaces-d datadbs1-o 60002-p/dev/rrvol3-s 60000
Onparams command
Syntax: Onparams-a-d-p [-d] [-S] [-l]
-A new logical log
-D dbspace specify dbspace for log storage
-S size new logical log sizes (KB)
-D Delete Logical log
-L LOGID Specifies that a logical log be deleted
-p Change the physical log
-D dbspace dbspace name stored in new physical log
-S size physical log size (KB)
The system automatically creates logical and physical logs in root dbspace when it is initialized. In the DBMS system, especially in the OLTP environment, the database operation is very frequent, log must record a large amount of information, so users should be able to spread multiple log files on different devices. There is a very simple way to create a logical log at the desired size, create a smaller physical log, and then move the physical log to another device after the system is initialized. Details on how to determine the size of the required physical log will be detailed in a later section. Using the Onstat-l command, you can see that all the new logical logs in the system are identified as a. These logical logs are only actually used when the system is archived. There is an easy way to activate these logical logs: Perform a "pseudo" archive. The steps are as follows: Set the parameter Tapedev to/dev/null and then run the ontape-s once. You can also execute the onbar-f command. Because pseudo-archives do not really archive system information, it is a real time to make sure that the system is properly archived.
You can delete a logical log only if it is really useless. Using Onstat-l, you can see that all the idle logs are marked as F. This logical log cannot be deleted if the logical log contains the information required for a transaction rollback or rapid recovery. Using the Onstat-l command, you can see that the log that accepts the current transaction is marked as C. If the logical log includes the last checkpoint record, it cannot be deleted, only if the checkpoint record is written to the next log loyalty and the previous log is backed up, the log can be deleted. Using the Onstat-l command, you can see that the log containing the last checkpoint record is marked L. The user can use the ONMODE-C command to force the write checkpoint record until the last checkpoint record is written to the required log.
Onstat Tools
Displaying information in dynamic server shared memory
involves a small amount of disk I/O operations
will have minimal impact on system performance.
Provide valuable information about the system
Command line Interactive Monitoring system
In this chapter you will explain the various important options for onstat, and familiarize yourself with their use
The Onstat command is a very useful real-time monitoring tool for the system. This command reads the information directly from the data structure of the shared memory and reports the system condition at that time. Typically, the Onstat command does not perform disk I/O operations and does not lock system resources, so it minimizes the impact on system performance. In short, Onstat is an interactive system monitoring tool provided by Informix.
Some common options for Onstat:
Onstat--List all options
Onstat-i setting into interactive mode
Onstat-show run mode and database engine status
Onstat-g sub_option Run Multiple threads option
Onstat-r repeat options after every second
Onstat-g Act shows all activity leads in the system
Onstat-g Ath-r 2 Every two seconds to show clues to all activities in the system
System Monitoring Interface
Sysmaster database is created automatically when system initialization
Database contains data dictionary information pointing to System shared memory structure
Provides instantaneous system performance and status information
Provides a SQL interface to understand data dictionary information
Allow administrators to automate system administration
Used to perform repetitive monitoring tasks
The Systems Monitoring interface (System monitoring Interface--SMI) is a SQL based system tool that enables users to obtain system information very conveniently through SMI. The system automatically creates a Sysmaster system database at root Dbapace when it is initialized. Each system consists of a sysmaster database in which most of the tables are virtual tables and pointers to data structures in shared memory. SMI can be used to monitor systems automatically, especially when performing repetitive tasks.
The Sysmaster database is read-only, that is, it is not allowed to insert an UPDATE, or delete operation. All users have query permissions on the database.
After system initialization, users must be careful to check that the Sysmaster database is successful. The information to create the Sysmaster database is saved in the message log file (Online.log) Note that since all DDL statements will be recorded in the log, it is important to leave enough space in the log file to ensure that the sysmaster is created.
The Sysmaster database schema is recorded in the $informixdir/etc/sysmaster.sql file.
Oncheck command
To check data structures on dynamic server disks
Locating and patching corrupted indexes and data
Disk I/O operation available
Some operations will be locked on the table while processing
The Oncheck command is used to repair corrupted indexes and data pages on the disk, which can also be used to examine and print the system's data structure. Be careful when using Oncheck, because some options for changing the command add shared locks to the table, for example: oncheck-pt
To prevent damage to the system, a consistency check is performed for all data in shared memory. Once a data page is found to have a consistency error or an index error is found, the system writes the problem and possible solution (using Oncheck) to the message log file and returns ISAM error code 105.
If a consistency error message is found, the user should immediately shut down the system and run Oncheck as prompted in the message log file. If the oncheck fails, you should try to dismount the table and reload it, and if it is an indexed problem, delete the index before rebuilding it. If all of these steps fail, only restore the system based on the original system archive backup.
The following commands can provide extend information:
Oncheck-pe in Onstat–l, the flag bit represents the state of the logical log,
A indicates a newly added log that is not yet available
F indicates idle logs that can be used, typically after a level 0 backup.
U represents a logical log that has been used
L indicates that the current log file contains one checkpoint
c indicates that the current log file is being used
b indicates the log files that have been backed up
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
