1. Test environment
Os:rhel5u5 (32bit)
Db:oracle 11.2.0.3.0 (32bit)
2. Cause of the exception.
2.1 Oracle 11g Default Sga_target is 0, starting with Oracle 10g, introducing Sga_target initialization parameters to enable Oracle to automatically manage the SGA (Buffer cache (db_cache_size), Shared Pool (shared_pool_size), Large Pool (large_pool_size), Java Pool (java_pool_size), Streams Pool (streams_pool_size)) ASSM (Automatic Shared memory Management) is automatically assigned. The following is a quote from Oracle's official documentation.
Sys> Show parameter SGA;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
LOCK_SGA Boolean FALSE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 632M
Sga_target Big Integer 0
Sys>
Sga_target official explanation.
Sga_target
| Property
Description |
| Parameter type |
Big integer |
| Syntax |
sga_target = integer [K | M | G] |
| Default value |
0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests) |
| Modifiable |
ALTER SYSTEM |
| Range of values |
From MB to operating system-dependent |
| Basic |
Yes |
SGA_TARGET Specifies the total is a size of all SGA. If Sga_target is specified and then the following memory pools be automatically sized:
Buffer Cache (Db_cache_size)
Shared Pool (shared_pool_size)
Large Pool (large_pool_size)
Java Pool (java_pool_size)
Streams Pool (streams_pool_size)
If these automatically tuned memory pools is set to nonzero values, then those values is used as minimum levels by Autom atic Shared Memory Management. You would set minimum values if the application component needs a minimum amount of memory to function properly.
The following pools is manually sized components and is not affected by Automatic Shared Memory Management:
Log Buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Fixed SGA and other internal allocations
2.2 Set the value of Sga_target with Sga_max_size (632M) album to open the ASSM function.
Sys> alter system set SGA_TARGET=632M Scope=spfile;
System altered.
3 Restart DB
3.1 sys> Startup force
Ora-00838:specified value of Memory_target is too small, needs to being at least 644M
Sys> Show parameter PGA;
Ora-01034:oracle Not available
Process id:0
Session id:54 Serial Number:5
3.2 Based on information such as error reporting. You can see that the SGA settings are large. Led to the PGA small, so error. Memory_target=pga+sga. The minimum value for SGA is 10M and the maximum value is 4096 GB-1, see official documentation.
Pga_aggregate_target
| Property
Description |
| Parameter type |
Big integer |
| Syntax |
pga_aggregate_target = Integer [K | M | G] |
| Default value |
Ten MB or 20% of the size of the SGA, whichever is greater |
| Modifiable |
ALTER SYSTEM |
| Range of values |
Minimum:10 MB maximum:4096 GB-1 |
| Basic |
Yes |
Pga_aggregate_target Specifies the TARGET AGGREGATE PGA memory available to all servers processes attached to the instance.
3.3 View ORA-00838 Error, Oracle explanation.
jietestdb<*test11g*/u01/product/oracle/dbs> $oerr ora 00838
00838, 00000, "Specified value of Memory_target is too small, needs to being at least%SM"
*cause:the specified value of Memory_target is less than the sum of the
Specified values for Sga_target and Pga_aggregate_target.
*action:set Memory_target to at least the recommended value.
As can be seen from the ora-00838,oracle explanation, reducing the SGA value, or increasing the Memory_target value, or restoring the pre-modification state will solve the problem.
3.1 First workaround: Restore the previous state without changing the Sga_target or Memory_target value.
3.1.1 This db has been shutdown, the direct startup DB can not be started, the following error.
Sys> Startup
Ora-00838:specified value of Memory_target is too small, needs to being at least 644M
3.1.2 Because DB is off, you can also create a pfile from SPFile, or create SPFile from Pfile. At this point we test the DB Spfiletest11g.ora, which already contains the Sga_target value, such as.
jietestdb<*test11g*/u01/product/oracle/dbs>$
Jietestdb<*test11g*/u01/product/oracle/dbs> $strings Spfiletest11g.ora
test11g.__db_cache_size=155189248
test11g.__java_pool_size=4194304
test11g.__large_pool_size=4194304
Test11g.__oracle_base= '/u01/product ' #ORACLE_BASE set from environment
test11g.__pga_aggregate_target=230686720
test11g.__sga_target=432013312
Test11g.__shared_io_pool_size=0
test11g.__shared_pool_size=251658240
test11g.__streams_pool_size=8388608
*.audit_file_dest= '/u01/product/admin/test11g/adump '
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
*.control_files= '/u01/product/oradata/test
11g/control01.ctl ', '/u01/product/oradata/test11g/control02.ctl ', '/data/test11g/control03.ctl '
*.db_block_size=8192
*.db_domain= "
*.db_name= ' test11g '
*.db_recovery_file_dest= '/u01/product/fast_recovery_area '
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest= '/u01/product '
*.dispatchers= ' (protocol=tcp) (SERVICE=TEST11GXDB) '
*.fast_start_mttr_target=0
*.local_listener= ' listener_test11g '
*.log_archive_dest_1= ' location=/data/test11g/arch/'
*.log_archive_format= '%t_%s_%r.arc '
*.memory_target=661651456
*.open_cursors=1000
*.processes=1000
*.remote_login_passwordfile= ' EXCLUSIVE '
*.sec_case_sensitive_logon=false
*.sessions=1105
*.sga_target=662700032
*.undo_tablespace= ' UNDOTBS1 '
3.1.3 At this time
by SPFILEtest11g. Ora creates the inittest11g. ORA
Sys>
create Pfile from SPFile;
File created.
3.1.4 Vim modifies the created Inittest11g.ora file, deleting the value of *.sga_target=662700032 (because Spfiletest11g.ora is a binary file and cannot be modified directly with the Vi/vim command), Re-create the Spfiletest11g.ora, at which point the value of Sga_target is not included in Spfiletest11g.ora.
Sys>
Create SPFile from Pfile;
File created.
3.1.5 Start DB OK
Sys> Startup
ORACLE instance started.
Total System Global area 661209088 bytes
Fixed Size 1346980 bytes
Variable Size 499122780 bytes
Database buffers 155189248 bytes
Redo buffers 5550080 bytes
Database mounted.
Database opened.
3.2 The second method, created by the above method Inittest11.ora Pfile initialization document, directly add *.sga_target=x (x is a numeric value, the X value of the front bag card SGA, the value of Memory_target is 632M, Minus the value of the remaining PGA is greater than 10M (the PGA minimum), the modified pfile creates the SPFile and starts the DB directly.
3.3 The third method, similar to the second method, if you do not want to modify the value of Sga_target, you can modify the value in the Pfile initialization document to increase *.memory_target=y (Y is a numeric value, Y value cannot be greater than the physical memory size). After the modified pfile, create the SPFile to start the db.
4. Summary.
4.1 If it is the official library PGA do not set too small, according to business needs, if the user process has a large concurrency, sorting and so on need to set the PGA large point. The normal PGA defaults to the Memory_target value of 20%, and the SGA is the Memory_target value of 80%.
4.2 Pfile and SPFile if the new value is set above, sga_target or Memory_target will overwrite the Sga_target or Memory_target value of the previous DB setting after the DB is started. http://blog.itpub.net/28569596/viewspace-1172469/
Ora-00838:specified value of Memory_target is too small (ext)
