Considerations for upgrading the memory of the Oracle Database Server

Project Background:

The original Oracle database server runs on the HP DL388G7 server with 32 GB memory. Due to business growth, the memory is tight, and server hardware faults occur from time to time. This is caused by a single instance and single server, there is a single point of discovery, So we plan to take some measures to improve it:

1) upgrade Server Memory

2) set up two servers at the server operating system level

3) migrate database data to the new server

I have previously written a data migration article titled "EXP/IMP data migration". The link is as follows:

I have previously written a data migration article titled "SUSE Linux HA dual-host setup". The link is as follows:

--------------------------------------------

If your system's business volume increases, the pressure on database servers increases, and the physical host needs to add memory, how much should you add? How do you adjust the parameters? This article teaches you how to do this step by step.

• 1. Theoretical Method

Parameters to be adjusted include SGA, PGA, process, session value, and shmall and shmmax in kernel parameters.

• 1.1 SGA and PGA Calculation Method

SGA = total physical memory * 50%
PGA = total physical memory x 20%
The remaining 30% is reserved for the operating system. If the memory resources are relatively tight, you need to consider the cost of the system, if the database pressure is not great, in fact, you can set the size of sga and pga to a smaller value, 1.1 points for adjustment. For example, allocate 20% of the physical memory to SGA, allocate 5% to PGA, and then adjust it based on the actual situation.

• 1.2 Kernel Parameter Setting Calculation Method

The following describes how to set the shmall and shmmax parameters in the kernel:
Shmmax <= number of physical memory (G) * 1024*1024*1024 (bytes)

Shmall> = sga (G) x 1024*1024*1024/page_size
We recommend that you directly use the sum of SGA and PGA for computation.

Page_size can be queried using the following command:
Getconf PAGE_SIZE

 

Shmmax <= number of physical memory (G) * 1024*1024*1024 (bytes)
It refers to the maximum value of a single shared memory segment, measured in bytes, which is commonly known as B. generally, we recommend that you use half of the physical memory, which can be slightly larger. I like to set it to the sum of sga and pga.

Shmall = SGA (G)/page_size (bytes) = sga (G) * 1024*1024*1024/page_size. For example, if the sga size is 22 GB, page_size = 4kb = 4096 bytes, then shmall = 22*1024*1024*1024 bytes/4096 bytes = 5767168
Shmall refers to the total number of pages in the shared memory. You can connect to the shared memory as SGA, because PGA is not shared for oracle. Well, let's just talk nonsense.
The page size is usually 4 kb in bytes. the value obtained through the get page_size command is generally 4096 bytes.

Summary: note that the unit is. The shmmax refers to the memory value, unit, unit, and page_size. The unit is bytes, And the shmall is no unit. It is only a number, indicating the number of pages.

 

Organization calculation table:
1 byte (B) = 8 bits (B) bytes = 8 binary digits
1 Kilobyte (K/KB) = 2 ^ 10 bytes = 1,024 bytes kilobytes
1 Megabyte (M/MB) = 2 ^ 20 bytes = 1,048,576 bytes MB
1 Gigabyte (G/GB) = 2 ^ 30 bytes = 1,073,741,824 bytes Gigabit bytes
1 Terabyte (T/TB) = 2 ^ 40 bytes = 1,099,511,627,776 bytes Kyrgyzstan

 

• 2. Operate instances
• 2.1 collect database memory allocation status quo

# Free-m
Total used free shared buffers cached
Mem: 32096 29072 3024 0 49 22406
-/+ Buffers/cache: 6616 25480
Swap: 32765 847 31918
Su-oracle
Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.5.0-Production on Fri Mar 29 16:09:42 2013

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Lock_sga boolean FALSE
Pre_page_sga boolean FALSE
Sga_max_size big integer 8000 M
Sga_target big integer 8000 M
SQL> show parameter pga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 5606 M

SQL> show parameter processes

NAME TYPE VALUE
-----------------------------------------------------------------------------
Aq_tm_processes integer 0
Db_writer_processes integer 8
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 900

SQL> show parameter sessions

NAME TYPE VALUE
-----------------------------------------------------------------------------
Java_max_sessionspace_size integer 0
Java_soft_sessionspace_limit integer 0
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 995
Shared_server_sessions integer

1 2 Next Page