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 Marvell), 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 will also crash, any good software has his flaws, a good software is not perfect, he just avoided most common or may be considered problems, And some problems that are not easy to find but very fatal are often overlooked. Oracle8 also have unsafe 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. The 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 OS 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 possibilityLess sex, because we found that the system CPU peak is often more than 90%, idle very small, database resources are too busy, at the same time more than 10 people lock a large table to operate, a natural part of the user can not enter the system, we write the following SQL statements to the lock table users to monitor: SELECT object_ Id,session_id,serial#,oracle_usename,os_user_name,s_processfrom 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, all two SQL statements query to return the currently locked list of users. But the second SQL statement makes this query very slow by adding sql_text, especially when there are many users at the same time to operate the database, it is recommended not to, the first SQL statement in a very short 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'll kill it right away. Lock process [sid,serial#],sql statement: ALTER SYSTEM KILL Session ' sid,serial# ', but this is not the fundamental solution to the problem, it can only be temporarily alleviated; In addition, we also find that the rollback segment often has " Online "and" offline "phenomenon, we also consider whether the rollback segment 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 backThe optimal parameter of the fallback segment [the fact that the optimal value does not cause a database error] increases the value of optimal, and uses the command set TRANSACTION use ROLLBACK segment to specify a large fallback segment for the system [ Note that the 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, and for a system to execute long queries, optimal should be large enough to avoid ORA-1555, " Smapshot too old "error, and for running small transactions, optimal should be smaller, so that the fallback segment is small enough to put in memory, which will improve 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 opinion, the system may be because of 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, The database problem is temporarily alleviated, you can do update, but Oracle's internal code error still exists, only temporarily will not affect our use of the database, and rollback segmentbegan to appear "online rollback segment" problem, more puzzling is that the database unexpectedly appeared the phenomenon of automatic down machine, so we asked Oracle Company's technical engineers, 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. This may be a bug for Oracle, but any software has its imperfect side, otherwise why out of what patch, with a patch 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 Ability: HP focus on OS capabilities, CPU capacity is not strong, your database should try to avoid the CPU occupancy rate is too large; Dec emphasis on CPU capacity, OS capacity is not strong, your database can consider the appropriate increase in some CPU parameters of the settings; Sun's CPU capacity and OS ability , your database should consider the balance of parameters, too larger than the small is not good. 2, increase the size of the log file, at least one will be less than 8MB, log file too small will lead to check point frequently, which affects 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 the 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, the appropriate increase in db_block_buffer and share_poll_size size, in order to increase the buffer value, increase memory, as soon as possible to improve the 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. Responsible Editor Zhao Zhaoyi#51cto.com TEL: (010) 68476636-8001 to force (0 Votes) Tempted (0 Votes) nonsense (0 Votes) Professional (0 Votes) The title party (0 Votes) passed (0 Votes) The original text: Oracle8 security factors and several instructions return to the network security home