How to leverage the Oracle Database Data Management Function

Currently, Oracle is the largest database, and its management system is also the fastest growing. How can we better utilize the powerful data management functions of Oracle databases in practical applications? This has become an important issue in Oracle system optimization.

This article attempts to explore its "potential" from the system parameters and other software. It attempts to find out the specific relationship between Oracle and the external environment, that is, the platform dependency of Oracle. This article mainly uses the NT platform as an example, describes the platform dependencies of Oracle.

1. Oracle is only a multi-thread (Multiple Threads) Operating System Process on the NT platform)

On the NT platform, every background "process" (such as LGWR and DBWR) and dedicated service "process" of Oracle are subthreads of Oracle processes. This multi-threaded Architecture (Muli_threaded Architecture) is very efficient on NT because all sub-threads share resources of the same master process.

If you add an Oracle Instance, there will be a new Oracle process, which is also owned by the new process. On the NT platform, the operating system does not mark every Oracle thread with names such as LGWR and DBWR. To View information about threads, you can use SQL statements to query related tables and views in the Data Dictionary ).

For example, to view the internal number of the dbwr thread:

 
 

  
  
select b.name , p.spid   
  
  
from v$bgpross b , v$process p   
  
  
where b.paddr=p.addr   
  
  
and name='DBWR'  
 
 

In addition, Oracle's system performance monitoring tool also provides a simple way to view thread information.

2. Space restrictions:

On the NT platform, the address space of the Oracle instance and all connected users is limited to 2 GB, while on the Win9x Platform, this limit is within 3 GB. For NT or Win9x, the address space of a process is limited to 4 GB, while for the NT platform, 2 GB must be reserved for system resources, such as buffers and stacks. For Win9x, the reserved space is 1 GB. NT supports two main file systems: FAT and NTFS. Oracle can be installed in these two file systems, but there are two problems to consider.

The first is security. The FAT file system does not support file-level security. Every user who logs on to the NT server can delete the FAT file. The NTFS file system supports file-level security protection similar to Unix, A user can only access files that he or she has access.

The second is the maximum limit on files. The maximum size of a FAT file is 4 GB, while that of an NTFS file is technically up to 32 GB. Each Data file of Oracle on the NT platform can have a maximum of 4 million Data blocks. If the size of each Data Block is 8 KB, a Data file will have 32 GB, however, an Oracle Database can contain a maximum of 1022 data files, so the maximum size of this Database is 32 TB.

NT supports raw disk partition ). Each raw partition can be assigned a drive letter without being formatted by the system. It is directly used for Oracle to store data files, log files, or control files. Similar to the Unix platform, each bare partition corresponds to a unique Oracle data file, log file, or control file. The difference between the two platforms is that the file naming conventions are different.

For example, in an SQL statement, you can reference the raw partition: datafile' \. \ f: 'size 49 M REUSE.

F: indicates the drive letter corresponding to a raw partition. In addition, the defined Oracle file size is usually 1 MB smaller than the size of the raw partition, to avoid writing to the disk's 0 cylinder.

3. parameters:

On the NT platform, there are some registry parameters for Oracle, which are similar to Shell variables on the Unix platform.

For example, the Oracle_home and Oracle_SID parameters in the Registry are automatically defined in the Registry during installation of the Oracle installer. An Oracle instance is also defined in the registry as a service, which can be seen in the "service" entry in the "control panel" of NT.

Oracle only supports a single Oracle_home directory on the NT platform (improved in the latest version of Oracle). When different Oracle versions are installed on NT, all versions share the same directory structure, oracle_home/bin,different execution files are separated by different numbers, such as oracle72.exe and Oracle73.exe. In the Registry, the Oracle_home parameter is defined in HKEY_LOCAL_MACHINE/software/Oracle/. These parameters can also be manually edited and reset by regedit in the registry.

4. Event viewing and system performance monitoring:

On the NT platform, Event Viewer and Performance Monitor utilities are available to Monitor Oracle information.

When Oracle is started or shut down, a record will be added to the time viewer. the NT system administrator can check the record information in the event viewer to check whether there is any Oracle warning information, if the Oracle audit (audit) option is set, you can view the audit records in the event viewer.

The system performance monitor provides detailed data about all processes, including resource statistics related to Oracle, such as file read Bytes/s, and data buffer hit rate.

Even if you run multiple Oracle instances on the NT platform, the event viewer and system performance monitor only collect data about the instance specified in the Registry parameter Oracle_SID.

5. Security:

As mentioned in point 2nd, to access a database, you must first be able to directly or indirectly access the server running the Oracle database. To ensure database security, first, the platform and network security on which the operating system depends must be considered.

Oracle uses a large number of files that users cannot directly access. For example, data files and log files can only be read and written through Oracle background processing. Therefore, only the DBA who wants to create and delete these files can directly access them at the operating system level. Output files and other backup files must also be protected.

In general, in addition to the user name and password account of the database, you can use the platform features to provide an additional level of user authentication and operating system account. However, on the same server, a database account can be paired with an operating system account, except that the prefix is different. The default prefix is "OPS $", but the value can be modified using the OS _AUTHENT_PREFIX parameter of the Oracle database. This prefix can also be set to an empty string without a prefix.

CONNECT to the Oracle Core on the NT Platform requires a dedicated password, which is defined during installation and is stored in an implicit pwd. ora file by default. The file is located in the $ Oracle_home/database directory.

If you set DBA_AUTHORIZATION = BYPASS in the init. ora file.

Or modify HKEY_LOCAL_MACHINE/software/Oracle/DBA_AUTHORIZATION in the registry.

If the value is set to BYPASS, the DBA does not need a password to connect to the core. This is also worth special attention.

System optimization is a complex issue that involves a wide range of topics. The above points mainly consider how to optimize the system from Oracle's dependence on the platform. We hope to provide you with a system optimization idea.
 

Article by: http://database.csdn.net/page/5534b799-b014-4f80-b8a8-1e4fad0acec0