Oracle's unsafe factors and several points

Oracle's unsafe factors and several points

As an outstanding representative of Object Relational Database Service, Oracle is undoubtedly the most powerful. Oracle is much better than SYBASE and Informix in terms of database scale, multimedia data type support, SQL operation replication concurrency, and security services, the latest version of Oracle8.0.4 enhances this feature and introduces some new features, such as Data partition and Object Relational Technology), Index only tables, Connection Manager [NET8 features], Advanced queue, and so on, so there is a saying that Oracle8 is applicable to Peoplesoft, the best database engine for SAP, Baan, and other encapsulated application systems.

---- Although Oracle8 has many advantages, just as Microsoft's WINDOWS system crashes, any good software also has its defects. A good software cannot be perfect, he only avoids most common or potential problems, while some problems that are not easily discovered but are very fatal are often neglected. There are also insecure factors in Oracle8. Many users who want to upgrade Oracle7.1, Oracle7.2, and Oracle7.3 to Oracle8 as soon as possible cannot ignore this factor. Of course, this insecure factor was not discovered at once, but was discovered when we managed a very large table, this risk may not occur in small or medium-sized databases created using Oracle or cannot be found at all, because the unique features of Oracle8 have minimized this risk, you can rest assured that your database system is secure.

---- The Oracle8 database we installed works in the host-terminal mode, and the system host uses four HP-9000 minicomputers and GB of memory. The maximum number of transactions set at the initial stage of the database creation is determined by the default value of Oracle8 [This is also the default value of Oracle7]: The Block value is 2 K, the number of transactions is 32 (for a database that needs to process a very large volume, we generally set the block value to be greater than 2 K, at least 4 K or 16 K, of course, this is also related to the CPU capacity of the host and the I/0 capacity value), and no partition is created during database creation, which may leave a hidden risk for database problems in the future. Due to the large number of users accessing the database each day and the large number of users operating on the same table, the table is often locked and users complain that they cannot enter the system, the packet sent by the host is too slow and the ORA-600 error is often reported. At first we thought it was a system network problem, but this was a relatively small possibility, because we found that the system's CPU peak is often more than 90%, the idle space is very small, the database resources are too busy, at the same time, more than a dozen people lock a large table to perform operations. Naturally, some users cannot access the system. For this reason, we have written the following SQL statements 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 may ask why the following SQL statement is not used for queries:

SELECT a. username, a. sid, a. serial #, B. id1,

C. SQL _text from v $ session,

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;

---- Both of the preceding SQL statements query and return the list of users currently locked. However, the second SQL statement adds SQL _text, which slows down the query, we recommend that you do not use it when many users operate the database at the same time. The first SQL statement will query who is locking the table in a short time, which is conducive to database management, once a user cannot access the lock process, the lock process [SID, SERIAL #] will be immediately killed. The SQL statement is as follows: ALTER SYSTEM KILL SESSION 'sid, SERIAL #', however, this is not the fundamental solution to the problem. It can only be mitigated temporarily. In addition, we also find that rollback segments often have the phenomenon of "online" and "offline, so we considered whether it was a problem caused by rollback segments: As we operate on large rollback segments, users will immediately report that they cannot enter. We know that the size of the rollback segment is directly dependent on the Activity Status of the database, and each EXTENTS should have the same value (recommended for Oracle) [The initial extents value can be 2 K (32) select] from the list of 4 K (69), 8 K (142), 16 K, 32 K, etc. This will ensure that when you delete a partition, you can reuse its space without wasting it. In addition, MINEXTENTS should be set to 20, which will not enable the rollback segment to use the space being used by the active firm when extending another EXTENT, therefore, we can determine the size of the rollback segment accordingly and find out the size of the rollback segment required for normal database operation, therefore, we reset the OPTIMAL parameter of the rollback segment [the fact is that the value of OPTIMAL is not enough to cause database errors], increasing the value of OPTIMAL, USE the set transaction use rollback segment command to specify a large rollback segment for the system. [Note that the OPTIMAL parameter must be large enough so that ORACLE does not need to shrink back and reassign EXTENTS frequently. Overwrite value, performance will be reduced due to additional segment resetting, OPTIMAL should be set to large enough for a system that requires long-running queries to avoid ORA-1555, "Smapshot too old" errors, for small transactions, OPTIMAL should be smaller so that the rollback segment is small enough to be placed in the memory, which will improve the system performance.], But we found that after doing so, the ORA-600 error still appears, and the lock table is getting more and more serious; we also consider temporarily locking this table, do not let the user, first, let's take a look at all the user's lock processes. Since the host-terminal approach was adopted at the beginning, it was impossible to completely clear them unless all external network connections were disconnected, but that is undoubtedly a restart of the database, and finally we chose to restart the database.

---- After the database is restarted, the system resources can be released at a time. The user obviously feels that the speed is up and can be ensured to be used normally. In our opinion, the system may not be DOWN for a long time, when too many user logins cause database deadlocks, a very serious problem occurs. One data in that table cannot be updated. An ORACLE internal code error is reported when an UPDATE occurs, we didn't care at the time, but soon we found that the numbers in another table were duplicated. According to the data uniqueness of ORACLE8, this phenomenon should not exist at all, because we only created a unique index in this table, we called ORACLE technical engineers. Maybe ORACLE's technical engineers encountered this problem for the first time, their answer is that the data dictionary is too old and the data index is broken. We recommend that you rebuild the index and report the problem to Asia Pacific. Under the guidance of ORACLE's technical engineers, we rebuilt the table and re-built the index. During the re-indexing process, the table failed several times, in addition, the previous table cannot be dropped. After careful searching, we found that there are indeed duplicate indexes in ORACLE8. A table has two duplicate indexes, which directly leads to database HANG and cannot be accessed, however, checking the system status, process, and LISTENER is normal. From the log file, the file size is too small (7 MB) and check point is frequent, which affects the system performance. After the above adjustments, the database problem is temporarily mitigated and can be updated, but the internal code error of ORACLE still exists, but it does not affect our use of the database yet, the online rollback segment problem occurs at the beginning of the rollback segment. what's even more puzzling is that the database is DOWN automatically. So we asked ORACLE's technical engineers again, their answer is that ORACLE has noticed some questions about ORACLE8.0.4, And they will attach PA to the database. TCH, hoping to solve these problems, but considering applying a PATCH to the database, all the data will be EXPORT, which is a very dangerous operation, in addition, all the programs on the host must be re-compiled once, and it cannot be completed in less than a week, which is impossible at all, therefore, we are still waiting for a better solution from ORACLE.