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

Background:

According to the demand of the production environment, in order to increase stability and avoid excessive expansion of memory components, ASMM automatic shared memory management is used and set manually.

The size of the shared_pool_size,db_cache_size.

The reason for the following operations is that an error was reported when directly modifying the operation:

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

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

-1. View current memory component parameter values

Col name for a30

Col VALUE for a30

Select INST_ID,name, VALUE / 1024 / 1024 size_MB, issys_modifiable

From gv$parameter

Where name in ('pga_aggregate_target'

'memory_target'

'memory_max_target'

'sga_max_size'

'sga_target'

'shared_pool_size'

'db_cache_size'

'java_pool_size'

'large_pool_size'

'streams_pool_size')

Order by 2,1

-- 2. View the maximum value used by PGA

Select INST_ID,value/1024/1024 SIZE_M from gv$pgastat where name='maximum PGA allocated'

INST_ID SIZE_M

--

1 286.93457

2 269.886719

-3. Current system parameter configuration

NAME TYPE VALUE

-

Memory_target big integer 10G

Memory_max_target big integer 10G

Sga_max_size big integer 10G (there is a problem with the current value setting of this system)

Sga_target big integer 0

Pga_aggregate_target big integer 0

-4 the memory component parameters are planned to be adjusted as follows:

-- automatic shared memory management (ASMM)

Note that this is because the system is a mixture of OLAP and OLTP functions, so memory allocation is special

Memory_target big integer 0

Memory_max_target big integer 0

Sga_max_size big integer 8G

Sga_target big integer 0

Pga_aggregate_target big integer 10G

Shared_pool_size big integer 4G

Db_cache_size big integer 2G

-5. Confirm the parameter file and back up the parameter file before operation

Show parameter spfile; (parameter files should be backed up before operation)

NAME TYPE VALUE

-

Spfile string + DATEDG/rac11g/spfilerac11g.ora

Create pfile='/home/oracle/oldpfile.ora' from spfile ='+ DATEDG/rac11g/spfilerac11g.ora'

-6. Modify memory component parameters:

1 node:

Alter system set memory_max_target=0 scope=spfile sid='*'

Alter system set sga_max_size=8192M scope=spfile sid='rac11g1'

Alter system set memory_target=0 sid='rac11g1'

Alter system set sga_target=0 sid='rac11g1'

Alter system set pga_aggregate_target=10240M sid='rac11g1'

Shutdown immediate

2 nodes:

Alter system set sga_max_size=8192M scope=spfile sid='rac11g2'

Alter system set memory_target=0 sid='rac11g2'

Alter system set sga_target=0 sid='rac11g2'

Alter system set pga_aggregate_target=10240M sid='rac11g2'

Shutdown immediate

-7. Create a pfile using the new spfile and then modify it

Node 1:

Create a pfile using the new spfile and then modify it

Create pfile='/home/oracle/newpfile.ora' from spfile ='+ DATEDG/rac11g/spfilerac11g.ora'

-8. Comments (see documentation ID 1397761.1)

Note:

Vi / home/oracle/newpfile.ora

# * .memory _ max_target=0

# rac11g1.memory_target=0

# rac11g2.memory_target=0

-- 9. Rebuild the parameter file with the modified newpfile (the original spfile can rename the backup):

Create spfile='+DATEDG/rac11g/spfilerac11g.ora' from pfile='/home/oracle/newpfile.ora'

-10. Start the database separately

Start the database (both nodes are started separately):

Startup

-- 11. Confirm the parameter setting (two nodes):

Confirm the parameter setting (two nodes):

Show parameter memory_max_target

Show parameter sga_max_size

Show parameter memory_target

Show parameter sga_target

Show parameter pga_aggregate_target

-12. Set the shared_pool_size and db_cache_size parameters:

Set the shared_pool_size and db_cache_size parameters:

1 node:

Alter system set shared_pool_size=4096M sid='rac11g1'

Alter system set db_cache_size= 2048M sid='rac11g1'

2 nodes:

Alter system set shared_pool_size=4096M sid='rac11g2'

Alter system set db_cache_size= 2048M sid='rac11g2'

-- 13. Confirm the parameter setting (two nodes):

Confirm the parameter setting (two nodes):

Show parameter shared_pool_size

Show parameter db_cache_size

-- 14.

Check the log for error messages:

-

Note:

Reference documentation:

ORA-00843, ORA-00849 When Trying To Change SGA_TARGET With MEMORY_MAX_TARGET=0 Being Active (document ID 1397761.1)

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.