Shulou(Shulou.com)06/01 Report--

This article introduces you how to switch between Oracle 11g AMM and ASMM, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Now Oracle is developing in the direction of intelligence. If we are looking for some Oracle books from the 8i/9i era, how to configure the appropriate database memory pool size is a very important topic. But after entering 10g, automatic memory pool tuning has become an important Oracle feature.

At 10g, Oracle launched ASMM (Automatic Shared Memory Management), which realized the self-adjustment of the internal structure of Oracle SGA and PGA. After entering 11g, AMM (Automatic Memory Management) implements the parameter MEMORY_TARGET, which integrates the planning of SGA and PGA.

By default, Oracle 11g uses AMM. During installation, we specify the percentage of memory used by Oracle, which is used as the initial value for MEMORY_TARGET and MEMORY_MAX_TARGET. If these two parameters are set to non-zero values, then Oracle uses the AMM management policy.

At the same time, if we set these two parameters to 0, AMM shuts down automatically. After the corresponding SGA_TARGET and PGA_AGGREGATE_TARGET parameters are non-zero, the Oracle is automatically degraded using the ASMM feature.

This article briefly introduces the switching between AMM and ASMM.

1. Introduction of the experimental environment.

We chose 11.2.0.3 to conduct the experiment, and the current state is ASMM.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

Currently MEMORY_TARGET is set to zero and AMM is not enabled.

SQL > show parameter target

NAME TYPE VALUE

-

Archive_lag_target integer 0

Db_flashback_retention_target integer 1440

Fast_start_io_target integer 0

Fast_start_mttr_target integer 0

Memory_max_target big integer 0

Memory_target big integer 0

Parallel_servers_target integer 16

Pga_aggregate_target big integer 108M

Sga_target big integer 252M

2. From ASMM to AMM

In 11g, if ASMM is used, the corresponding memory shared segment is the real shared segment.

[oracle@SimpleLinux] $ipcs-m

-Shared Memory Segments-

Key shmid owner perms bytes nattch status

0x00000000 32768 oracle 640 4194304 32

0x00000000 65537 oracle 640 260046848 32

0x01606d30 98306 oracle 640 4194304 32

The following parameter adjustment, here the author has a suggestion, many times the failure of the startup umount phase is caused by improper parameter modification. Before making parameter changes, you can use create pfile to back up the parameters, which can speed up the system repair in the event of a failure.

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/dbs/spfileora11g.ora

SQL > create pfile from spfile

Done

Modify the system parameters so that the target values of sga and pga are set to 0 and the target setting of memory is non-0. Note that many of the parameters in this process are static and can be modified in spfile visibility before restarting the server takes effect.

SQL > alter system set memory_max_target=360m scope=spfile

System altered

SQL > alter system set memory_target=360m scope=spfile

System altered

SQL > alter system set sga_target=0m scope=spfile

System altered

SQL > alter system set sga_max_size=0 scope=spfile

System altered

SQL > alter system set pga_aggregate_target=0 scope=spfile

System altered

Restart the database server to view the parameter configuration.

SQL > conn / as sysdba

Connected.

SQL > startup force

ORACLE instance started.

Total System Global Area 263651328 bytes

Fixed Size 1344284 bytes

Variable Size 176164068 bytes

Database Buffers 83886080 bytes

Redo Buffers 2256896 bytes

Database mounted.

Database opened.

SQL > show parameter target

NAME TYPE VALUE

-

Archive_lag_target integer 0

Db_flashback_retention_target integer 1440

Fast_start_io_target integer 0

Fast_start_mttr_target integer 0

Memory_max_target big integer 360M

Memory_target big integer 360M

Parallel_servers_target integer 16

Pga_aggregate_target big integer 0

Sga_target big integer 0

After AMM starts, the system shared segment becomes a "virtual" shared segment.

[oracle@SimpleLinux dbs] $ipcs-m

-Shared Memory Segments-

Key shmid owner perms bytes nattch status

0x00000000 163840 oracle 640 4096 0

0x00000000 196609 oracle 640 4096 0

0x01606d30 229378 oracle 640 4096 0

3. From AMM to ASMM

Here's how to go from AMM to ASMM. To turn off AMM completely, be sure to set both MEMORY_TARGET and MEMORY_MAX_TARGET to 0.

SQL > alter system set memory_max_target=0 scope=spfile

System altered

SQL > alter system set memory_target=0 scope=spfile

System altered

SQL > alter system set pga_aggregate_target=100m scope=spfile

System altered

SQL > alter system set sga_target=260m scope=spfile

System altered

SQL > alter system set sga_max_size=260m scope=spfile

System altered

Note that if you restart the system at this time, an error will be reported.

SQL > startup force

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 272629760 cannot be set to more than MEMORY_MAX_TARGET 0.

SQL >

The cause of this problem is the internal checking of parameters during Oracle startup. Because the MEMORY_MAX_TARGET is "displayed" assigned, it conflicts with the SGA_TARGET assignment.

The solution is to use parameter default values. After the pfile is created, delete the MEMORY_TARGET and MEMORY_MAX_TARGET record rows that display an assignment of 0. Then use pfile to start the database and rebuild spfile.

SQL > create pfile from spfile

2

File created.

-- before revision

* .db_recovery_file_dest='/u01/app/fast_recovery_area'

* .db_recovery_file_dest_size=10737418240

* .diagnostic_dest='/u01/app'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=ora11gXDB)'

* .log_checkpoints_to_alert=TRUE

* .memory_max_target=0

* .memory_target=0

* .open_cursors=300

* .pga_aggregate_target=104857600

* .processes=150

-after revision

* .db_recovery_file_dest='/u01/app/fast_recovery_area'

* .db_recovery_file_dest_size=10737418240

* .diagnostic_dest='/u01/app'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=ora11gXDB)'

* .log_checkpoints_to_alert=TRUE

* .open_cursors=300

* .pga_aggregate_target=104857600

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

Use pfile to start the database and rebuild spfile.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup pfile=/u01/app/oracle/dbs/initora11g.ora

ORACLE instance started.

Total System Global Area 272011264 bytes

Fixed Size 1344372 bytes

Variable Size 176163980 bytes

Database Buffers 88080384 bytes

Redo Buffers 6422528 bytes

Database mounted.

Database opened.

SQL > show parameter target

NAME TYPE VALUE

-

Archive_lag_target integer 0

Db_flashback_retention_target integer 1440

Fast_start_io_target integer 0

Fast_start_mttr_target integer 0

Memory_max_target big integer 0

Memory_target big integer 0

Parallel_servers_target integer 16

Pga_aggregate_target big integer 100M

Sga_target big integer 260M

SQL > create spfile from pfile

2

File created.

After the reboot, the ASMM switch is complete.

SQL > startup force

ORACLE instance started.

Total System Global Area 272011264 bytes

Fixed Size 1344372 bytes

Variable Size 176163980 bytes

Database Buffers 88080384 bytes

Redo Buffers 6422528 bytes

Database mounted.

Database opened.

-- Real shared memory segment structure

[oracle@SimpleLinux dbs] $ipcs-m

-Shared Memory Segments-

Key shmid owner perms bytes nattch status

0x00000000 425984 oracle 640 8388608 25

0x00000000 458753 oracle 640 264241152 25

0x01606d30 491522 oracle 640 4194304 25

-- HugePage usage

[oracle@SimpleLinux dbs] $grep Huge / proc/meminfo

HugePages_Total: 67

HugePages_Free: 1

HugePages_Rsvd: 0

Hugepagesize: 4096 kB

[oracle@SimpleLinux dbs] $

AMM and ASMM are very important tools for us to manage the database. With the help of self-regulating mechanism, we can manage the database itself. 11g AMM should be said to be convenient, but in some cases, such as HugePage, we may need to switch back to ASMM. Take the right as a record and keep the friends who need it for inspection.

On how to carry out Oracle 11g AMM and ASMM switch to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.