在Oracle 11gR2环境下，通过ipcs命令查看共享内存，竟然发现分配给Oracle的内存只有4096Bytes，而在Oracle 10g环境下从未发现这种问题！
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[root@rh6 ~]# ipcs -a------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x00000000 98307 gdm 600 393216 2 dest
0x00000000 131076 gdm 600 393216 2 dest
0x00000000 163845 gdm 600 393216 2 dest
0x00000000 196614 gdm 600 393216 2 dest
0x00000000 229383 gdm 600 393216 2 dest
0x4b4218ec 557064 oracle 660 4096 0
------ Semaphore Arrays --------key semid owner perms nsems0x00000000 0 root 600 1
0x00000000 98305 root 600 1
0x000000a7 327682 root 600 1
0xbe61d9cc 983043 oracle 660 154
------ Message Queues --------key msqid owner perms used-bytes messages |
数据库版本：
|
1
2
3
4
5
6
7
8
|
16:27:09 SYS@ test3 >select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
|
Oraccle 11g的通过以下两个参数实现内存的自动个管理：
|
1
2
3
4
5
6
7
|
16:27:19 SYS@ test3 >show parameter mem
NAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0
memory_max_target big integer 300Mmemory_target big integer 300Mshared_memory_address integer 0
|
1、会不会是参数memory_max_target有关系呢？把它设为0，然后重启数据库。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
16:28:11 SYS@ test3 >alter system set memory_target=0 ;
System altered.16:36:44 SYS@ test3 >show parameter mem
NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------hi_shared_memory_address integer 0
memory_max_target big integer 300Mmemory_target big integer 0
shared_memory_address integer 0
16:30:51 SYS@ test3 >startup force ;
ORACLE instance started.Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes
Database mounted.Database opened. |
再看共享内存：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[oracle@rh6 ~]$ ipcs -a------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x4b4218ec 622600 oracle 660 4096 0
------ Semaphore Arrays --------key semid owner perms nsems0xbe61d9cc 1114115 oracle 660 154
------ Message Queues --------key msqid owner perms used-bytes messages |
发现分配给Oracle的共享内存仍然很小，看来不是memory_target 参数的问题！
2、尝试调整memory_max_target参数，将其恢复到系统默认值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
16:39:49 SYS@ test3 >alter system set sga_max_size=300m scope=spfile;
System altered.16:40:06 SYS@ test3 >alter system reset memory_max_target scope=spfile sid='*';
System altered.16:40:40 SYS@ test3 >startup force nomount;
ORACLE instance started.Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes
16:40:52 SYS@ test3 >show parameter mem
NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
16:40:59 SYS@ test3 >show parameter sga
NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 300Msga_target big integer 180M |
查看系统共享内存:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[oracle@rh6 ~]$ ipcs -a------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x4b4218ec 884744 oracle 660 316669952 16
------ Semaphore Arrays --------key semid owner perms nsems0xbe61d9cc 1638403 oracle 660 154
------ Message Queues --------key msqid owner perms used-bytes messages |
看来是设置了memory_max_target参数的原因，导致通过ipcs查看到分配给Oracle的内存为4096Bytes！
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1599787，如需转载请自行联系原作者