Experience in ORACLE Database Management on Unix platforms

This article is a set of effective systems summarized by the author in years of ORACLE Database management work. It greatly improves the work efficiency, so as to predict the potential risks of the database in advance and eliminate them as soon as possible, ensures the safe and stable operation of dozens of database servers across the network.

The purpose of this article is not to introduce the specific technologies mentioned in this Article. For details about DNS settings, SENDMAIL settings, POP3 settings, shell script programming, and unix cron usage, please refer to the relevant materials, there are a large number of technical websites on the Internet.

When you are dealing with a few databases, the management work is relatively simple. You only need to telnet to each database server on a regular basis and run some system information collection programs that have been written beforehand to understand the overall situation of the database, however, when you are dealing with dozens of distributed databases, using the above method will consume a lot of time and energy, and may ignore some important information or points, this affects the performance of the database server and even causes the server to go DOWN.

Since our system is on the UNIX platform, can we use the powerful unix mail function to regularly send statistics on database servers to DBAs? The answer is yes.

The overall idea is to select a database server as the central server and use the free mail system SENDMAIL provided by the UNIX system. The statistical information of all database servers is regularly sent to the central server through the mail system, the central server sends the statistics to the DBA. The implementation steps are as follows:

Step 1: Domain Name Planning

Determine the Domain Name of the whole network, such as abc.net, and assign a domain name to each database server. For example, the domain name of the database server located in Beijing is bj.adb.net, And the Domain Name of the database server located in Shanghai is sh.adb.net.

Step 2: Set up a Domain Name Server

Select a database server as the DNS server for the whole network, and direct the DNS settings of all database servers to this server. The configurations of different UNIX systems are slightly different. For details about how to set up DNS, refer to relevant documents.

DNS knowledge: DNS name resolution FAQs about DNS server formation)

Step 3: configure the email server

Configure the SENDMAIL system for all database servers based on the planned domain name, and perform a test to check whether emails can be sent and received from each other.

SENDMAIL related knowledge: SendMail Getting Started Guide Sendmail functions enterprise sendmail Architecture)

Step 4: configure the POP3 server

To enable DBA to collect database statistics through traditional Client mail systems such as Netscape and Outlook Express, You need to select a database server and install and configure the POP3 server. You can find free POP3 servers online.

POP3 related knowledge: POP3 Post Office Protocol)

Step 5: Compile the Shell Script statistics program

Unix provides powerful Shell scripts and has become a popular programming tool for many DBAs. It can collect information such as memory, hard disk usage, Tablespace usage of the database, and database performance. Many SQL Script readers can obtain the information from this site.

Knowledge about Shell Script: Introduction to Shell Script applications)

In programs, you need to specify an account that sends statistics to the central mail server, such as a dba@bj.abc.net. The scheduled execution of the program can be completed through CRON in the Unix system. CRON is powerful and can be viewed through Unix man.

CRON knowledge: getting started with CRON)

Step 6: Set the client Mail System

Configure your client mail system to receive the mailbox statistics for the dba@bj.abc.net, it is recommended to use a mail system that supports multiple accounts such as Outlook Express, Foxmail, and set the timing such as 5 minutes) the receiving mode.

After the above six steps, you can configure a complete management platform for the distributed database system. The information to be collected can be changed according to different requirements. The mail sample is shown in the appendix in this document.

For more urgent information, such as the full Tablebase or the extended extent, you can send it to the DBA's pager. the readers can use the specific program to make full use of their imagination.

Mail sample:

Disk usage statistics for SZBMS at 01/10/00/08:30:01 AM Filesystem 512-blocks Used Available Capacity Mounted on /dev/rz16a 2321998 118050 1971748 6% / /proc 0 0 0 100% /proc /dev/rz16g 98520854 29853320 58815448 34% /usr /dev/rz1g 6090794 533640 4948074 10% /disk1 /dev/rz3g 4057676 1254588 2397320 35% /disk2 /dev/rz4c 34500632 27540166 3510402 89% /disk3 **Database status report** Pct TABLESPACE Total(Mb) Used(Mb) Free(Mb) Free Largest(Mb) FRAGMENT SALES 1900.0 1379.3 520.7 27.4 100.2 30 INDEXES 4699.1 3543.9 1155.2 24.6 180.0 63 PRODUCT 3800.0 3294.2 505.8 13.3 68.9 248 PERSONNEL 20.0 3.2 16.8 84.0 16.8 1 RBS 100.0 61.5 38.5 38.5 .3 237 RBS1 200.0 150.0 50.0 25.0 50.0 1 SYSTEM 60.0 38.1 21.9 36.5 21.9 1 TEMP 500.0 10.0 490.0 98.0 490.0 1 TOOLS 10.0 .2 9.8 98.0 9.8 1 --------- -------- -------- sum 11289.1 8480.4 2808.7 db buffer cache hit ratio 96.5300416 DOC>60 extents, it can effectively avoid extent exceed maximum DOC>extent limit */ objects which exceed 60 extent are: SEGMENT_NAME TABLESPACE_NAME FILE_ID MAX_EXTENT_NO SER_TRAN_VALUE_IDX_S INDEXES 14 73 SER_TRAN_VALUE_IDX_S INDEXES 16 68

BKJIA editing recommendations: Oracle Enterprise Manager application reference)

(