The unsafe factors of Oracle8 and some explanations

The unsafe factors of Oracle8 and some explanations

As an outstanding representative of the Object relational database, Oracle is undoubtedly the most powerful. Whether it's the size of the database, the support of multimedia data types, the parallelism of SQL operations replication, or the security services, Oracle is much stronger than Sybase, Informix, Added to the latest version of Oracle8.0.4, this feature is enhanced, and some new features are introduced, such as data partitioning (partitioning), Object-relational technology (objects relational Technology), index-only tables ( Index only tables), Connection Manager (Connection manager) [NET8 features], advanced queues (Advanced quening), and so on, there is a saying: Oracle8 is applicable to such as PeopleSoft, The best database engine for packaged application systems such as SAP and Baan.

----Although Oracle8 has many advantages, just as Microsoft's Windows system also crashes, any good software has his flaws, a good software is not perfect, he just avoids most common or may be considered problems, And some problems that are not easy to find but very fatal are often overlooked. Oracle8 also exist insecurity factors, many are trying to upgrade to Oracle8 as soon as possible Oracle7.1, Oracle7.2, Oracle7.3 users can not take into account this factor. Of course, this insecurity was not discovered in a flash, but when we managed to manage a very large table, this vulnerability might not or could not be found in a small or medium-sized database created with Oracle. Because the unique characteristics of Oracle8 have reduced this risk to the lowest level, you can rest assured that your database system security.

Problem
----We installed the ORACLE8 database is working in the host-terminal mode, the system host uses four HP-9000 minicomputer, 1.5G of memory. The maximum number of transactions set at the beginning of the library is the default value of Oracle8 [which is also the default value of Oracle7]: The block value is 2K and the transaction count is 32 (for a very large database to handle, we typically set the block value to be greater than 2K, at least 4K or 16K, Of course, this is also related to the CPU capacity of the host and the value of i/0 capability, and when the library is not in the partition to build tables, which may be the future of the database problems left a hidden danger. Because the day accesses the database very many users, but also to the same table operation's number of users is too big, causes that table frequently to be locked, unceasingly has the user to complain that they cannot enter the system, the host sends the data packet too slow, frequently reported ORA-600 the mistake. At first we thought it was a system network problem, but this possibility is relatively small, because we found that the system CPU peak is often more than 90%, idle very small, database resources are too busy, while more than 10 people lock a large table to operate, a natural part of the user can not access the system, For this we have written the following SQL statement to monitor the lock table users:
SELECT object_id,session_id,serial#,
Oracle_usename,os_user_name,s_process
From V$locked_object 1,
V$session S WHERE 1.session_id=s.sid;

----Some people may ask why not use the following SQL statements to query:
SELECT A.username,a.sid,a.serial#,b.id1,
C.sql_text from V$session A,
V$lock B,v$sqltext C where a.lockwait=b.kaddr and
A. Sql_address=c.address and A.sql_hash_value=c.hash_value;

----The above two SQL statements query to return the currently locked list of users, but the second SQL statement makes the query very slow due to the addition of sql_text, especially if there are many users working on the database at the same time, not recommended, the first SQL Statement will be in a very short period of time to query who is in the lock table, which is conducive to the management of the database, but a user can not enter, we immediately kill the lock process [sid,serial#],sql statement: ALTER SYSTEM kill session ' sid,serial# ' , but this is not the fundamental way to solve the problem, can only temporarily alleviate; In addition, we also found that the rollback segment often has "online" and "offline" phenomenon, so we consider whether the rollback section is caused by the problem: because we have a large rollback segment to operate, immediately there will be users can not enter the response. We know that the size of the fallback segment is directly dependent on the active state of the database, each extents should have the same value (Oracle's recommendation) [INITIAL extents values can be selected from the 2K (32), 4K (69), 8K (142), 16K, 32K, etc. list] , which will ensure that when you delete a zone, you can reuse its space without causing waste, and the other minextents should be set to 20, which will not allow the fallback segment to extend another extent to use the space that is being used by the active transaction, so we can determine the fallback segment size accordingly. To find out the size of the required rollback segment when the database is running correctly, we reset the optimal parameter of the fallback segment [The fact is that the optimal value is not enough to cause database errors], the value of optimal is increased, and the command set TRANSACTION use ROLLBACK Segment specifies a large fallback segment for the system [note that optimal parameters are large enough to allow Oracle to not often back up and redistribute extents, and if set to less than the minimum coverage, performance will fall due to additional segment resets, for a system to execute long queries, Optimal should be set large enough to avoid ORA-1555, "smapshot too old" errors, and for small transactions, the optimal should be smaller so that the fallback segment is small enough to be placed in memory, which improves system performance. ], but we found that after doing so, ORA-600 errors still appear, and the lock table is more and more serious; we also consider to temporarily lock this table, not allow users to enter, the user's lock process to kill all the time to see, because the beginning of the use of the host-terminal mode of work, so simply can not clear out, Unless all of the external network connections are disconnected, but that is a reboot of the database, we finally chose to restart the database.
----Restart the database after the system resources are released, the user obviously feel the speed up, to ensure normal use, in our view the system may be due to the long time no down machine, the number of users logged in too many database deadlock, a very serious problem arises, One of the data in that table could not be update, and an update reported an Oracle internal code error, which we didn't care about, but soon we found that there was a duplication of numbers in another table, which should not exist at all, according to the ORACLE8 Data Index table. Because we only built a unique index on this table, we called the Oracle Company's technical engineers, and perhaps Oracle's technical engineers were the first to encounter the problem, their answer being that the data dictionary is too old, the data index is broken, the index is rebuilt, and the problem is reflected in the Asia Pacific. Under the guidance of Oracle's technical engineer, we rebuilt the table and rebuilt the index, and in the process of rebuilding the index, we started unsuccessfully and failed to drop the previous table, and after careful searching, we found that there was a duplication of indexes in the ORACLE8. A table has two duplicate index, directly causes the database hang, does not have the access, but views the system state, the process, the listener but all is normal, looks from the log file, the file is too small (7MB), the CHECK point frequently, affects the system performance, through the above adjustment, Database problems temporarily alleviated, you can do update, but Oracle's internal code error is still there, only temporarily will not affect our use of the database, and Rollback section began to appear "online rollback segment" problem, Even more puzzling is the fact that the database has the phenomenon of automatic down machine, so we asked Oracle Company's technical engineers again, Their response is that Oracle has noticed some of the problems with the ORACLE8.0.4 version, they will be patch to the database, hoping to solve these problems, but given that a patch to the database will be able to export all the data, this will be a very dangerous operation, and all the host Program to recompile one, not one weeks is not able to do, and that is simply impossible, so we are still waiting for Oracle Company a better solution.

Description
----The above problem may be a bug in Oracle, but any software has its imperfect side, otherwise why the patch, there will certainly be better than no patch, but we design a system must also take into account the following aspects:
----1, host system CPU capacity and i/0 capacity: HP focus on i/0 capabilities, CPU capacity is not strong, your database should try to avoid the CPU occupancy rate is too large; Dec focus on CPU capacity, i/0 capacity is not strong, your database can consider the appropriate increase some CPU parameters of the settings Sun's CPU capacity and i/0 ability is similar, your database should consider the balance parameter, too much than small is not good.

----2, increase the size of the log file, at least one less than 8MB, the log file too small will cause check point frequent, thus affecting the performance of the system.

----3, the rollback segment is best to maintain a more reasonable value, for some large rollback segment can be appropriately increased minextents, and set optimal, to ensure that the general things to deal with the need for regular dynamic allocation of space and timely recovery space.

----4, to make full use of CPU system resources and improve the CPU hit rate, can be appropriately increased log_simultaneous_copies,db_block_latches,db_writes settings.

----5, appropriately increase the size of Db_block_buffer and share_poll_size, in order to increase the buffer value, increase memory, as soon as possible to improve buff and SQL hit.

----6, host-terminal mode although it is easy to maintain data, but host-terminal mode of host load is too heavy, it is recommended to use client-server approach to build the system.