Oracle manually modifies the behavior of SPFile after logging

Oracle 11G introduces the memory_max_target and Memory_target parameters for the database's Memory auto-management (AMM), which is intended to modify the two parameters beyond the size of the physical memory, and the view to see what errors the database reported.

Backing Up SPFile files

$CP Spfileorcl.ora Spfileorcl.ora.bak

Manually modify the two parameter values in the SPFile directly:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/77/4B/wKioL1Zmg8bDEZuLAAB7OyQyROQ347.png "title=" QQ picture 20151208150948.png "alt=" Wkiol1zmg8bdezulaab7oyqyroq347.png "/>

Two worth of size modified to 6,815,744,000,000≈6.7t actual physical memory of about 16G

$ free-g

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/77/4D/wKiom1ZmhF3g5uKDAAAW-i2xEUM178.png "title=" QQ picture 20151208151836.png "alt=" Wkiom1zmhf3g5ukdaaaw-i2xeum178.png "/>

The startup database appears with the following error:

sql> startup Nomount;

Ora-01078:failure in processing system parameters

Lrm-00109:could not open parameter file '/dbbk/oracle/product/11.2.0.1.0/dbs/initorcl.ora '

[Email protected] dbs]$ Oerr ora 1078

01078, 00000, "failure in processing system parameters"

*cause: Failure during processing of INIT. ORA parameters during system startup.

*action:further Diagnostic information should is in the error stack.

[Email protected] dbs]$ Oerr LRM 109

109, 0, "Could not open parameter file '%.*s '"

*cause: The parameter file does not exist.

*action:create an appropriate parameter file.

Theoretically I modify the Memory_target parameter should be an error Memory_target value does not conform to the configuration of physical memory, but the actual report is not found pfile. Does it mean that manually modifying SPFile will cause SPFile to not read properly?

Manually modify the values of the two parameters to the original value and find that the same error is still being started. It can be judged that manual modification of SPFile does cause exceptions. You can see by using the file command that the type of Spfile.ora is a binary data file type.

$ file Spfile.ora

Spfile.ora:data

Restore normal SPFile backup file

Sql> Show parameter SPFile;

NAME TYPE VALUE

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

SPFile String/dbbk/oracle/product/11.2.0.1.0/dbs/spfileorcl.ora

Through the DDL statements I tried several times to modify these two parameters beyond the physical memory value and found some interesting phenomena:

Sql> select * from V$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit 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

Sql> alter system set memory_max_target=6800g Scope=spfile;

Sql> alter system set memory_target=6500g Scope=spfile;

System altered.

In most cases, the database can load the configuration in the SPFile file normally, but occasionally the following error occurs. Record it first, then analyze it individually.

sql> startup Nomount;

Ora-00845:memory_target not supported on the This system

sql> startup Nomount;

Ora-04031:unable to allocate bytes of shared memory ("Shared pool", "Unknown Object", "SGA Heap (2,1)", "Fixed allocation Callback ")

sql> startup Nomount;

ora-04031:unable to allocate 10272 bytes of shared memory ("Shared pool", "Unknown Object", "SGA Heap (1,0)", "KGLSG")

Observe the first error condition:

$ oerr ora 845

00845, 00000, "Memory_target not supported in this system"

*cause:the memory_target parameter is not supported on this operating system OR/DEV/SHM is not sized correctly on L Inux.

*action:refer to documentation for a list of supported operating systems. Or, Size/dev/shm to is at least the sga_max_size in each of the Oracle instance running on the system.

You can see that the explanation of the error alarm is that the Memory_target parameter is not supported in the system, or the size of the /DEV/SHM allocation is insufficient , when the situation occurs, usually SHM this memory allocation is insufficient, this is the shared memory form of the Linux system, All documents are defined according to the nature of Linux. The shared memory of Linux is also designated as a removable partition system file TMPFS. When memory_target exceeds this limit, it may result in a similar error.

$ df-ph

Filesystem Size used Avail use% mounted on

Tmpfs 7.8G 1017M 6.8G 13%/DEV/SHM

But such an error is not only affected by the Memory_target parameter. In any case, modifying the value of Memory_target and Memory_max_target exceeds the actual memory size. This error is not guaranteed to occur, but when I set the value of Sga_max_size and sga_target more than the actual physical memory, it triggered the error. Why is it?

Observation start can be seen, the database load SPFile time is based on the sga_max_size and Sga_target to the SGA allocation, as long as this parameter set reasonable. Even if the Memory_target setting is not reasonable, the database can start normally. then I can understand that when the application requests more memory and exceeds the value of Sga_max_size or the sga_max_size and Sga_target values are not allocated , the memory management of the database is really memory_ Target and Memory_max_target values to decide?

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/77/50/wKioL1ZmlkzwpiuBAAAfZyk9EMQ663.png "title=" QQ picture 20151208163313.png "alt=" Wkiol1zmlkzwpiubaaafzyk9emq663.png "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/77/50/wKioL1ZmlpDgZCqkAAAbZDpOus8596.png "title=" QQ picture 20151208163417.png "alt=" Wkiol1zmlpdgzcqkaaabzdpous8596.png "/>

Sql> alter system set SGA_MAX_SIZE=0 Scope=spfile;

Sql> alter system set sga_target=0 Scope=spfile;

sql> startup Nomount;

ORACLE instance started.

Total System Global area 1068994560 bytes

Fixed Size 2220072 bytes

Variable Size 671092696 bytes

Database buffers 390070272 bytes

Redo buffers 5611520 bytes

When I modify it, I find that the actual SGA automatically allocates about 1G of memory. Is this decided by memory_target? Neither is the answer. View the hidden parameters. found that the default size of the SGA is actually determined by the __sga_target implicit parameter.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/77/52/wKiom1ZmmFKCSpJnAAAqB8a8hkk265.png "title=" QQ picture 20151208164256.png "alt=" Wkiom1zmmfkcspjnaaaqb8a8hkk265.png "/> Then come to the conclusion that the size of the SGA is determined first by the parameters associated with the SGA. Similarly, the PGA can be judged.

Summarize:

11G later introduced the memory_max_target and memory_target parameters for automatic memory management (Automatic memories Management), In fact, the allocation of SGA and PGA is determined by the respective parameters, such as Sga_target, Sga_max_size, Pge_aggregate_target.

The PGA and SGA default values are actually determined by the __pga_aggregate_target and __sga_target implicit parameters respectively.

AMM manages the distribution of the SGA and PGA, and ASMM (Automatic Shared Memory Management) manages the distribution of the various components of the SGA, Asemm (automated SQL execution Memory Management) is the automatic distribution of the PGA Management.

This article is from the "linuxoracle" blog, make sure to keep this source http://onlinekof2001.blog.51cto.com/3106724/1720868

Oracle manually modifies the behavior of SPFile after logging