Oracle Database memory_target SGA Size

Modify Memory_target


Log in with an Oracle user,
Sqlplus "/as sysdba"
Sql> Show parameters target;
Show Parameters SPFile;
Alter system set MEMORY_MAX_TARGET=4G Scope=spfile;
Alter system set MEMORY_MAX_TARGET=4G Scope=both;
Tmpfs/dev/shm Tmpfs defaults,size=24g 0 0

-View which parameter file you are currently using to start the
Sql> SELECT distinct isspecified from V$spparameter;
Isspec

------

FALSE

See if the first value is False or true, if False, it is pfile, otherwise it is spfile.

The first act of TRUE indicates that it was initiated from SPFile,
There are two lines, where the second behavior is false, which indicates that there are two parameter files, one is SPFile, the other is Pfile
Final: Methods for modifying parameters:
First Use Mount-o remount,size=16g TMPFS
(The above command succeeds regardless of the actual memory size, but does not know if the memory is too small for Oracle to actually boot)
or similar to mount-t tmpfs-o remount,size=32g tmpfs/tmp (command succeeded, but failed to test whether Oracle boot is normal or not)
Modify the TMPFS size to enable Oracle to start successfully;
And then:
Log in with Oracle user Lmoracle, execute:
Sqlplus "/as sysdba"
Create pfile= '/pb/lmoracle/a.txt ' from memory;
If Oracle has been booted properly, you can get a file that holds all the parameters:/pb/lmoracle/a.txt
Modify the A.txt in the
memory_target=2g
For
memory_target=24g

Execution of shutdown in Sqlplus;
Exit Sqlplus and re-execute the following command to enter Sqlplus:
Sqlplus "/as sysdba"
Then start the DB instance with the modified parameter file:
Startup pfile= '/pb/lmoracle/a.txt ';
Take a look at the parameters:
Show parameters target;
Unmistakable,
Create SPFile from memory;
This saves the modified parameter file to the default spfile.
Restart the database (or restart the machine)
----------------------------------------------------------------------------------------------

Ora-00845:memory_target not supported in this system error resolution
Oracle 11g Database modified pfile parameter to start database error ora-00845
Sql> Startup
Ora-00845:memory_target not supported on the This system
The new memory auto-managed parameter Memory_target in Oracle 11g,
It automatically adjusts the SGA and PGA, which requires the/DEV/SHM shared file system to be used.
and requirements/DEV/SHM must be greater than memory_target, if/DEV/SHM than Memory_target small will error

Solution Solutions
1. Initialization parameters Memory_target or Memory_max_target cannot be greater than shared memory (/DEV/SHM), in order to resolve this problem, you can increase the/DEV/SHM
Such as:
# mount-t Tmpfs shmfs-o Size=7g/dev/shm
2. To make sure that the operating system restarts, the/etc/fstab file needs to be modified
Shmfs/dev/shm TMPFS size=7g 0
3. If the/DEV/SHM is not mounted, it will also report the above error, the need to ensure that it has been mounted
[Email protected] ~]$ df-h
Tmpfs 16G 8.9G 7.2G 56%/DEV/SHM
The following is a record of an operation
[Email protected] ~]# Cat/etc/fstab | grep TMPFS
Tmpfs/dev/shm Tmpfs defaults,size=8g 0 0
[Email protected] ~]# Mount-o REMOUNT,SIZE=16G/DEV/SHM
[Email protected] ~]# Cat/etc/fstab | grep TMPFS
Tmpfs/dev/shm Tmpfs defaults,size=8g 0 0
[Email protected] ~]# Vi/etc/fstab
/DEV/ROOTVG/LOGVOL02/EXT3 Defaults 1 1
/dev/rootvg/logvol01/tmp ext3 Defaults 1 2
/dev/rootvg/lvol0/ebao ext3 Defaults 1 2
/dev/rootvg/lvol1/backup ext3 Defaults 1 2
Label=/boot/boot ext3 Defaults 1 2
Tmpfs/dev/shm Tmpfs defaults,size=16g 0 0
Devpts/dev/pts devpts gid=5,mode=620 0 0
Sysfs/sys Sysfs Defaults 0 0
PROC/PROC proc Defaults 0 0
/dev/rootvg/logvol00 swap swap defaults 0 0
"/etc/fstab" 10L, 769C written

[Email protected] ~]# Df-h|grep SHM
Tmpfs 16G 0 16G 0%/dev/shm
Sql> Startup
ORACLE instance started.
Total System Global area 9420095488 bytes
Fixed Size 2236248 bytes
Variable Size 2315255976 bytes
Database buffers 7046430720 bytes
Redo buffers 56172544 bytes
Database mounted.
Database opened.
Sql> exit

---------------------------------------------------------------------------
Oracle Adjusts the SGA size


Memory_max_target < SHM Remember, otherwise the Oracle instance will fail to start,
Ora-00845:memory_target not supported the This system is wrong, click View SHM Settings

------------------------------
Sql> Show Parameter SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
LOCK_SGA Boolean FALSE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 512M
Sga_target Big Integer 512M
Sql>
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>

Sql>alter system Set Memory_max_target = 15G scope=spfile;
Sql>alter system Set Memory_target = 15G scope=spfile;
Shutdown immediate
Startup

Sql> Show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Hi_shared_memory_address integer 0
Memory_max_target Big Integer 15G
Memory_target Big Integer 15G
Shared_memory_address integer 0

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

How to modify the Oracle SGA size
Under normal circumstances, the query is very slow.
1. Check the SGA size, connect to the Oracle database as DBA, and enter the show SGA.
2, if the SGA is too small, please modify its size
Modify the principles that the SGA must maintain:
1). Sga_target cannot be greater than sga_max_size and can be set to equal.
2). The SGA plus other processes such as the PGA must consume less memory than the operating system's physical memory.
You must back up all the files in the DBS directory before doing the following.
Method one: Direct SQL command line modification:
(If SPFile file does not exist, create SPFile from Pfile is required first)
Sql>alter system set sga_max_size=1024m scope=spfile;
Then you need to restart the database
Sql>shutdown Immediate
Sql>startup
Sql>show SGA; You can see that the size of the SGA has been modified
After restarting, modify the Sga_target
Sql>alter system set sga_target=1024m Scope=both;


------------------------------------------------------------------------
Set the principle of the SGA and modify its size

One, set the principle of the SGA:
Sometimes there is a problem in configuring the SGA, and the database is not up because of too much memory allocated.

Memory Structure =SGA (System global Zone) +PGA (Program Global Zone)

SGA: Is the memory area used to store database information that is shared by the database process. It contains data and control information for the Oracle server, which is allocated in the actual memory of the computer where the Oracle server resides, if the actual memory is not enough to be written in virtual memory

Our priority is to set the SGA, which theoretically accounts for the 1/2--1/3 of OS system physical memory
Principle: Sga+pga+os Using memory < total physical RAM
Sga= ((db_block_buffers*blocksize) + (shared_pool_size+large_pool_size+java_pool_size+log_buffers) +1MB

1, SGA system global zone. (including the following five districts)
A, Data buffer: (db_block_buffers) stores data that is read in by a disk data file.
Size: db_block_buffers*db_block_size
Oracle9i set the data buffer to: db_cache_size
Principle: The main setting object in SGA, generally available memory is 40%.
B, Shared pool: (shared_pool_size): Data dictionary, SQL buffer, PL/C syntax parsing. Increase the speed.
Principle: Main Setting object in SGA, generally available memory 10%
C, log buffer: (log_buffer) store modification information for the database.
Principle: 128K----1M, should not be too big
D, Java Pool (java_pool_size) is mainly used for the development of Java language.
Principle: If you do not use Java, in principle can not be less than 20M, to 30M is usually enough
E, candidates (large_pool_size) If MTS is not set, it is used primarily for database backup recovery Manager rman.
Principle: should not use mts,5----10M, should not be too big
Sga=. db_block_buffers*db_block_size+ shared_pool_size+ Log_buffer+java_pool+size+large_pool_size
Principle: The 55-58% of available memory can be achieved.

2. PGA Program Global Zone
PGA: Contains data and control information for a single server process or a single background process.
The SGA shared with several processes is opposite the PGA is a zone that is used only by one process, and the PGA is allocated when the process is created to be recycled when the process is terminated.
A, Sort_area_size used to sort the occupied memory
B, hash_area_size for hash joins, bitmap indexing
These two parameters in the non-MTS are belong to the PGA, not the SGA, is allocated for each session separately, on our server in addition to the OS + SGA, we must consider these two parts
Principle: OS uses memory +sga+ number of concurrent execution processes * (SORT_AREA_SIZE+HASH_ARA_SIZE+2M) < 0.7* total memory
Instance Configuration
One: How much physical memory
Second: How much memory is estimated to be used by the operating system
Three: Whether the database uses a file system or a bare device
Four: How many concurrent connections
V: Whether the application is an OLTP type or an OLAP type
The basic principle is that the db_block_buffer can usually be as large as possible, shared_pool_size to moderate, Log_buffer usually up to hundreds of K to 1M is almost

For example:
Memory 2G Single CPU db_block_size is 8192 bytes
Around Sga=0.55*2048m=1126.4m
Recommended shared_pool_size = 200M, db_block_buffer *db_block_size = 800M
Specific: shared_pool_size=209715200 #200M
db_block_buffer=103192 #800M
Log_buffer = 131072 # 128k (128k*cpu number)
Large_pool_size= 31457280 #30M
Java_pool_size = 20971520 # M
Sort_area_size = 524288 # 512k (65k--2m)
Sort_area_retained_size = 524288 # MTS when sort_area_retained_size = Sort_area_size

Second, modify the size of the SGA:
1.sga_target cannot be greater than sga_max_size and can be set to equal. 2.SGA plus other processes, such as the PGA, must occupy less than the operating system's physical memory.
You must back up all the files in the DBS directory before doing the following.
Method one: Direct SQL command line modification:
Sql>alter system set sga_target=1024m scope=spfile;
Sql>alter system set sga_max_size=1024m scope=spfile;
(If the SPFile file does not exist, you need to create Createspfile from Pfile;)
Alter system set SGA_TARGET=12G Scope=spfile;
Alter system set SGA_MAX_SIZE=12G Scope=spfile;
And then
Sql>shutdown Immediate
Sql>startup
Sql>show SGA; You can see that the size of the SGA has been modified
Method Two: Modify by em:
Log in with SYS to EM: Management--(database configuration) all initialization parameters->spfile->
Sga_target and sga_max_size can be defined directly in this interface
Then restart the database!


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

CMD under partial operation:, oracle10.2.0g

Sql>conn Sys/rezin as Sysdba
is connected.
sql> shutdown immediate;
The database is closed.
The database has been uninstalled.
The Oracle routine has been closed.
Sql> Startup
The Oracle routine has been started.

Totalsystem Global Area 1073741824 bytes
Fixedsize 1253124 bytes
Variablesize 696254716 bytes
Database buffers 369098752 bytes
Redobuffers 7135232 bytes
The database is loaded.
The database is already open.
Sql> show SGA;
Totalsystem Global Area 1073741824 bytes---original size
Fixedsize 1253124 bytes
Variablesize 696254716 bytes
Database buffers 369098752 bytes
Redobuffers 7135232 bytes
Sql> alter system set sga_target=300m Scope=spfile;
The system has changed.
Sql>alter system set sga_max_size=280m scope=spfile;
The system has changed.
Sql>shutdown Immediate
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> Startup
The ORACLE routine has been started.

Totalsystem Global Area 314572800 bytes---modified size
Fixedsize 1248720 bytes
Variablesize 96469552 bytes
Database buffers 209715200 bytes
Redobuffers 7139328 bytes
The database is loaded.
The database is already open.


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

An issue with the inability to log on after the Oracle 11g installation (Memory_target not supported the This system)

Problem Resolution: Modify the size of the DEV/SHM, specific and memory corresponding to the size can be referenced in the Oracle official quick installation tutorial written in the settings
[Email protected] ~]$ Sqlplus
Sql*plus:release 11.2.0.1.0 Production on Mon Jul 1 20:03:07 2013
Copyright (c) 1982, Oracle. All rights reserved.
Enter User-name:sys as Sysdba
Enter Password:
Connected to an idle instance.
Sql> startup;
Ora-00845:memory_target not supported on the This system
Memory_target do not know where to set, according to the online data, set the system parameters, but still invalid
[Email protected] ~]# Mount-o REMOUNT/DEV/SHM
[Email protected] ~]# df-h/dev/shm/
Filesystem Size used Avail use% mounted on
Shmfs 20G 0 20G 0%/dev/shm
Want to restart the database also error ~ ~ ~
sql> shutdown immediate;
Ora-01034:oracle Not available
Ora-27101:shared Memory realm does not exist
linux-x86_64 error:2: No such file or directory
Sql> Startup Mount
Ora-00845:memory_target not supported on the This system

(1) Login ORCL Database
SQLPLUS Sys/[email protected] as SYSDBA
(2) Set memory size
ALTER SYSTEM SET memroy_max_target=500m scope=spfile; (scope=spfile means write as SPFILE file, takes effect after database restart)
(3) Set the auto-allocated memory size
ALTER SYSTEM SET memory_target = 500M scope=spfile; (indicates that the total size of the PGA and SGA is automatically adjusted to 500M)
ALTER SYSTEM SET sga_target =0 scope=spfile;
ALTER SYSTEM SET pga_aggregate_target =0 scope=spfile;
(4) Closing the database
SHUTDOWN IMMEDIATE
(5) Restart the database EXP1 make the configuration effective
Startup

This is done as follows, assuming your instance is named ORCL
Execute under Linux:
1. CD $ORACLE _home/dbs
2, Sqlplus/as SYSDBA
3, create Pfile from SPFile;
Then modify the content inside the pfile, the Memery_target modified to Sga_target and Pga_aggregate_target, a 256M, a 200M.
and remove the option to start all ORCL.
5, Startup pfile= ' Initorcl.ora '
6, create SPFile from pfile= ' Initorcl.ora '
7. Startup force

Memory_target is a new memory auto-management parameter added by Oracle database 11g following 10g after adding Sga_target,
Parameters, of course, are set in Pfile or SPFile.


*.memory_target=54087647232
It's supposed to be 55G, so I tweaked it.
vi/etc/fstab--adjusting the shared memory size
mount/dev/shm--Execution available
Df-h/dev/shm--Verify that the value is 60G,
The server is then booted under the Oracle user startup.

Note: The contents of the essay are from the online data collation, for reference only.

Oracle Database memory_target SGA Size