Oracle, Sybase, Informix database running status monitoring

After checking Oracle's process command output, the output display should include at least some of the following processes:
Oracle The process of writing a data file, the output appears as: "ORA_DBW0_ORCL"

Oracle The process of writing log files, the output is shown as: "ORA_LGWR_ORCL"
Oracle The process that listens to the instance state, and the output appears as: "ORA_SMON_ORCL"
Oracle a process that listens for the state of the client connection process, and the output appears as: "ORA_PMON_ORCL"
Oracle The process of archiving, the output is shown as: "ORA_ARC0_ORCL"
Oracle The process for checkpoint, the output is displayed as: "ORA_CKPT_ORCL"
Oracle The process of recovery, the output is shown as: " ORA_RECO_ORCL"

Where "status" represents the current instance state of Oracle, must be "OPEN";

"Database_status" indicates the state of the current Oracle database and must be active.

Where "Log_mode" represents the current archiving method for Oracle.

"Archivelog" means that the database is running in archive mode,

"Noarchivelog" means that the database is running in non-archive mode.

In our system the database must be running in the archive mode.

Ora-of:

Err's:

Fail of:

The output should have more than 3 records (including 3), and "STATUS" should be empty. State is null representation control
File status is normal.

Output should have more than 3 (including 3) records, "STATUS" should be Not "INVALID", not "DELETED".

"Online" indicates normal line status

"Online" indicates normal line status Status of all Oracle rollback segments

Related parameter values in the Oracle initialization file

Growth of Oracle table Space

If records are returned, the extension of those objects is already reaching the maximum extension value when it is defined. For these objects, you want to modify its storage structure parameters. content within the system table space

If a record returns, there are some objects in the system tablespace that are not system and SYS users. Further examination of whether these objects are relevant to our application is needed. If relevant please move these objects to a non-system table space, and you should check the default tablespace values for these objects

If records are returned, the next extension of those objects is greater than the maximum extended value of the table space to which the object belongs, and the storage parameters for the corresponding table space need to be adjusted

Select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from V$process p,v$session A, v$ Locked_object b,all_objects C where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id

SELECT SID, Serial#, username, osuser from v$session;

Alter system kill session ' sid,serial# ';

Select segment_name, Sum (bytes)/1024/1024 from User_extents Group by segment_name

Select owner, Segment_name,sum (bytes/1024/1024) from dba_segments

GROUP BY Owner,segment_name

Select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0 ts_size

From Dba_tablespaces T, Dba_data_files D

where t.tablespace_name = D.tablespace_name

Group BY T.tablespace_name; ) , and its deadlock checking

Another daily job of the system administrator is to monitor the operation of the system. There are several main aspects:

1, monitoring the current user and process information

Using System procedures: sp_who

Description: This command displays all registered users and process information for the current system, as shown in the following table for information about a system.

Display from left to right: process number, current state, registered user name, host name, block count, database name, and current command.

Deadlock if the current state is lock sleep

If the total number of processes is found to be close to the maximum number of connections (using System procedures: sp_configure "user conn" view), inactive or unrelated processes should be dropped to ensure that the system is operating normally, and also to monitor illegal users or users who use databases that are not within their own use. )

Using System procedures: sp_spaceused

Description: This procedure displays the number of rows, pages of data, and the space occupied by a target or all targets in the current database. The following table is the information for a database log table:

Namerow_totalreserveddataindex_sizeunused

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

Syslogsnot avail32kb32kb0kbnot Avail

The main objectives of daily monitoring are: User database, database log table (syslogs), and billing raw data tables. If you find that the footprint is too large, you want to dump the log table, you should expand the space or clear the garbage data for other targets.

tools

Informix self-brought tools Oncheck

Oncheck utilities tend to display configuration and disk usage information, which resides on disk and is less varied.

oncheck -pe 

to get the physical layout of large chunks of information  

dbspace  name, owner, and dbspace  build date   
the size of a large page (in pages), the number of pages used and the number of available pages  

All tables in a large chunk and their initial page numbers, the list of table lengths in pages  




Oncheck  -PT&NBSP

Executes the oncheck -pt command with database name and table name parameters to obtain information   for each tblspace in the following database or table,

Scope quantity  

the size of the first range  

The size of the next range  

The number of pages assigned  

The number of pages used  




Oncheck  -PB&NBSP

can perform a ONCHECK -PB command with a database or table name as a parameter. &NBSP

The table or database uses the number of BLOB pages in all blobspaces  

The full extent of the blob,blobpage in the table or database  




Monitor data replication status &NBSP

to monitor the data replication status of the online database server to determine the following information: &NBSP

Database server type (primary, subordinate, or standard)   
The name of the other pairing database server  &NBSP
Whether data replication is running   
The value of the data replication parameter   


Onstat -g dri &NBSP

To get monitoring information for the entire data replication. &NBSP

Database server type  &NBSP
Data replication status (on, off, or off)   
paired database servers   
Latest data replication checkpoint   &NBSP
Data Replication configuration parameter values Tools

Informix self-brought tools Onstat

The Onstat utility reads data from shared memory and reports accurate statistical results during the execution of the command. That is, Onstat describes information that is dynamically changed during processing, such as buffers, locks, and user situations.

Detailed command:

Using Onperf monitoring
Online includes a tool called Onperf graphics monitoring. This tool is able to monitor most of the information provided by Onstat.

The following is an introduction to using command-line utilities Onstat

Onstat-c ONCHECK-PR Check configuration information


ONSTAT-M;ONSTAT-P Check checkpoint information


ONSTAT-G SEG Monitor Shared memory segment


Onstat-p: Statistics on read and write caching are available. Including:

Number of reads from the shared memory buffer (bufreads),
Percent read from cache (%cached)
Number of write shared memory (bufwrits)
Percent written to cache (%cached)

Onstat-b: The following buffer information is available:

Address of each normal shared memory buffer
Page number of all memory pages in the current shared memory
The address of the thread that currently occupies the buffer
The address of the first thread waiting for each buffer

Onstat-b: You can get information on each of the following buffers:

The address of the thread that is currently occupying each buffer
Page number of the page in the buffer
Type of page in buffer
Buffer setting type of lock
The address of the thread that is currently occupying the buffer
The address of the first thread waiting for each buffer


You can compare the address of the user's thread with the address that appears in the Onstat-u display to get the session ID number.

Onstat-k: Displays locking information about the activity.

Generate locked user Leads
Type of lock
Range of the lock


Onstat-g Glo

Displays information about each virtual processor that is currently running and the cumulative information for each virtual processor class (starting at system startup).


Onstat-u

Displays information about all active threads that require the Rsam task control block structure. Active threads include threads that belong to a user session, and some corresponding server daemons clues. The following information is also included:

Address of individual threads
Mark the current state of a clue
The session ID and user logon ID of the thread that belongs to the session. Represents the daemon thread for the session ID of 0.
Whether the thread is waiting for a specific resource and the address of the resource
Number of locks occupied by threads
Number of read calls and write calls that the thread has performed
The maximum number of concurrent user leads allocated since the most recent online initialization


Onstat-g ses

You can monitor resources assigned to a thread.

Onstat-x:

Displays the following information about each open transaction:
The address of the transaction in shared memory
Current state of the transaction
What is the stage of the transaction
Characteristics of the transaction
The thread that owns the transaction
Number of locks occupied by transactions
Log file of the Begin work record
Isolation level
Number of attempts to begin recovery of threads