Technical points of concern for Oracle (RAC) database optimization and Accident Analysis

Oracle 11g Audit

The Oracle audit (Audit) function is used to monitor database operations performed by users. audit records can be stored in data dictionary tables (called audit records: SYS. in the AUD $ table, view dba_audit_trail)

Or operating system audit records (default location: $ oracle_base/admin/$ oracle_sid/adump.

Regardless of whether or not the database audit function is enabled, the following Oracle operating systems will forcibly record:

• Connect to the instance with the administrator privilege;
• Start the database;
• Shut down the database.

Note: Audit is disabled for Oracle 10 Gb by default, while audit is enabled for Oracle 11g by default! Generally, the audit function is required for the core data of listed companies.

When audit data/logs are full, Oracle RAC cannot be used! Therefore, Oracle 11g should focus on the audit function, and close it if not necessary.

Oracle 10g ASM

In a massive database environment, DBA may spend a lot of time Managing disks. For example, if a tablespace occupies the entire disk, DBA needs to add another disk to the operating system, and then create a new data file on the new disk. If it is a single disk, this is not very cumbersome. The problem is that if we used raid or LVM, a large amount of data is still distributed on those disks, if we want to evenly distribute the data on the previous disk and the Newly Added Disk, it may take a day or even a few days to export and import the original data.

If there is one way, we can add a disk to the system, and then tell Oracle that we want to use this disk. The rest of the work is done by Oracle, what a wonderful thing!

Oracle10g has provided this function, which is Automatic Storage Management (ASM ). Oracle10g ASM not only helps DBAs free from tedious disk space management, but also deserves more attention that ASM provides both strip and image functions, these functions were originally implemented by configuring raid separately.

ASM provides the vertical integration function of the file system and volume manager dedicated for Oracle database files. ASM distributes I/O loads across all available resources to optimize performance while eliminating the need for manual I/O adjustment (avoiding hot spots by dispersing database files. ASM helps DBAs manage the dynamic database environment, allowing DBAs to adjust the storage allocation without shutting down the database while expanding the database scale.

ASM allows DBA to define a storage group (called a disk group ). Then, the Oracle kernel manages the names of files in the memory group and the placement of database files. DBAs can use the new SQL commands (create diskgroup, alter diskgroup, and drop diskgroup) to change storage allocation-add or delete disks. You can also use Enterprise Manager (EM) and database configuration Assistant (dbca) to manage disk groups.

ASM promotes non-invasive storage configuration changes through automatic rebalancing. It allocates database files in all available memories to optimize performance and resource utilization.

ASM is a capability that saves DBA time by automating manual memory so that it can manage more and more databases with higher efficiency.

Make sure that your ASM function is correctly configured and enabled.

Oracle system global area (SGA)

When you start an Oracle database, the system first plans a fixed area in the memory to store the user's required data and the required system information during Oracle operation. This region is called the system global area (SGA.
SGA has several important areas:

• Database buffer cache-database Buffer
• Redo log buffer-redo log Buffer
• Shared Pool-shared area
• Java pool, large pool...

Make sure that the database allocated to you has enough SGA space. Oracle SGA ulimit

The ulimit command in Linux provides a convenient way to restrict resources and optimize system performance. You can use the shell STARTUP script, application startup script, and command input on the console to restrict the use of system resources, including the size of the created Kernel File, the size of the process data block, the size of the file created by the Shell Process, the size of the Memory Lock, the size of the resident memory set, the number of opened file descriptors, And the allocation the maximum stack size, CPU time, the maximum number of threads for a single user, and the maximum virtual memory that a shell process can use ...... Obviously, ulimit is very practical for our applications and development on the Linux platform.

However, for Oracle SGA, we need to remove the restrictions on it.

For example, Oracle cannot be started after the lock_sga parameter is set in 64-bit Linux + Oracle 10g R2. In Linux, you can set ulimit-l unlimited to solve the problem.

Oracle RAC failover

The foundation of Oracle RAC high availability is Failover (Failover ). It makes the failure of any node in the RAC cluster do not affect the user's use. The user connected to the faulty node is automatically transferred to the healthy node, and the user cannot feel this switch.

Oracle RAC (10 Gb) failover can be divided into three types:

• Client-side connect time failover

That is, if multiple addresses are configured in the client tnsname, the user attempts to connect to the first address in the address table when initiating a connection request. If the connection attempt fails, continue to use the second address until the connection is successful or all the addresses are traversed.

Feature: it only takes effect at the moment when a connection is established. That is to say, the client-side connect time failover detects node faults only when a connection is initiated. If the node does not respond, the next address in the address list is automatically attempted. Once the connection is established, the node will not be processed if it fails. The client shows that the session is disconnected and the user program must re-establish the connection. In addition, the configuration of the Failover cluster is written to the client.

Obviously, client-side connect time failover is generally not used in the production environment.

• TAF-transparent application failover

Currently, most applications use database connection pools, that is, several persistent connections to the database are established at startup, and applications reuse these connections throughout their lifecycle. The working method of client-side connet time failover does not greatly help application availability in this case.

Therefore, the new failover mechanism-TAF was introduced from Oracle 8.1.5. The so-called TAF means that after the connection is established, when the application system runs, if an instance fails, the user connected to the instance will be automatically migrated to other healthy instances. For applications, this migration process is transparent and does not require user intervention. Of course, this transparency should be guided because your uncommitted transactions will be rolled back. Compared with client-side connect time failover, TAF greatly improves ha by throwing a connection error, and restarting the application.

The traditional TAF fault cluster still needs to be configured on the client. In the case of a large number of applications, the modification cost for server changes is relatively high.

• Service-side TAF

Service-side TAF can be seen as a variant of TAF. First, service-side TAF is also a TAF. All TAF features are available. Second, such TAF is configured on the server, unlike traditional TAF, It is configured on the client.

Client-side TAF is used to modify tnsnames on the client. configured in the ora file. If many clients use this database, all client applications need to be changed each time the server is adjusted, which is both inefficient and error-prone. The service-side TAF saves the fail_mode configuration in the database through the server, saves all TAF configurations in the data dictionary, thus saving the client configuration work, therefore, the TNS file on the client does not require any TAF configuration options.

Of course, the service-side TAF client must point to a RAC VPs to ensure polling of the Database Cluster.

The Oracle DB console provides a web application interface that monitors all aspects of Oracle RAC Application Instances in real time, such as server resources, performance, execution plans, row-level locks, and other running details.