Oracle11g修改SGA和PGA ORA-00845

修改步骤

[oracle@ywqdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 11:23:44 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> show parameter memory        

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

SQL> show parameter sga

NAME      TYPE  VALUE

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

lock_sga      boolean  FALSE

pre_page_sga      boolean  FALSE

sga_max_size      big integer 4G

sga_target      big integer 4G

SQL> show parameter pga

NAME      TYPE  VALUE

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

pga_aggregate_target      big integer 2283M

SQL> alter system set memory_max_target=11G scope=spfile;

System altered.

SQL> alter system set memory_target=11G scope=spfile;

System altered.

SQL> show parameter sga

NAME      TYPE  VALUE

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

lock_sga      boolean  FALSE

pre_page_sga      boolean  FALSE

sga_max_size      big integer 4G

sga_target      big integer 4G

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

SQL> startup

ORACLE instance started.

Total System Global Area 1.2827E+10 bytes

Fixed Size     2265224 bytes

Variable Size  9797898104 bytes

Database Buffers  3019898880 bytes

Redo Buffers     7307264 bytes

Database mounted.

Database opened.

SQL> show parameter sga

NAME      TYPE  VALUE

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

lock_sga      boolean  FALSE

pre_page_sga      boolean  FALSE

sga_max_size      big integer 11G

sga_target

SQL> alter system set sga_max_size=8G scope=spfile;

System altered.

SQL> alter system set sga_target=8G scope=spfile;

System altered.

SQL> show parameter pga

NAME      TYPE  VALUE

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

pga_aggregate_target      big integer 2283M

SQL> alter system set pga_aggregate_target=3G scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 8551575552 bytes

Fixed Size     2270360 bytes

Variable Size  1191185256 bytes

Database Buffers  7348420608 bytes

Redo Buffers     9699328 bytes

Database mounted.

Database opened.

SQL> show parameter sga

NAME      TYPE  VALUE

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

lock_sga      boolean  FALSE

pre_page_sga      boolean  FALSE

sga_max_size      big integer 8G

sga_target      big integer 8G

SQL> show parameter pga

NAME      TYPE  VALUE

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

pga_aggregate_target      big integer 3G

异常处理

ORA-00845: MEMORY_TARGET not supported on this system

[root@ywqdb ~]# df -h

Filesystem      Size  Used Avail Use% Mounted on

/dev/vda2        50G   15G   33G  31% /

tmpfs           7.8G  224K  7.8G   1% /dev/shm

/dev/vda1       194M   34M  151M  19% /boot

/dev/vdb1       197G  5.1G  182G   3% /oradata

[root@ywqdb ~]# umount /dev/shm

umount: /dev/shm: device is busy.

        (In some cases useful info about processes that use

         the device is found by lsof(8) or fuser(1))

[root@ywqdb ~]# fuser -m -v /dev/shm

                     USER        PID ACCESS COMMAND

/dev/shm:            root       2141 ....m gnome-settings-

                     root       2162 ....m metacity

                     root       2210 ....m gnome-volume-co

                     root       2226 ....m pulseaudio

[root@ywqdb ~]# kill -9 2141

[root@ywqdb ~]# kill -9 2162

[root@ywqdb ~]# kill -9 2210

[root@ywqdb ~]# kill -9 2226

[root@ywqdb ~]# umount /dev/shm

[root@ywqdb ~]# mount -t tmpfs shmfs -o size=12g /dev/shm

You have mail in /var/spool/mail/root

[root@ywqdb ~]# vi /etc/fstab

tmpfs                   /dev/shm                tmpfs   defaults ,size=12G        0 0

[root@ywqdb ~]# df -h

Filesystem      Size  Used Avail Use% Mounted on

/dev/vda2        50G   15G   33G  31% /

/dev/vda1       194M   34M  151M  19% /boot

/dev/vdb1       197G  5.1G  182G   3% /oradata

shmfs            12G     0   12G   0% /dev/shm