Oracle DB - 如果不小心設了sga_max_size，如何完整啟動 Automatic Memory Management

1.啟動AMM

ALTER SYSTEM SET memory_target='805306368' SCOPE=BOTH;
ALTER SYSTEM SET sga_target='0' SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target='0' SCOPE=BOTH;

2. 取消sga_max_size parameter(使用AMM不需要此參數)

CREATE PFILE = '$ORACLE_HOME/dbs/my_pfile.ora' FROM SPFILE;

show parameter spfile
--$ORACLE_HOME/dbs/spfileSID.ora

vi $ORACLE_HOME/dbs/my_pfile.ora
找到*.sga_max_size這個值，刪除之

CREATE SPFILE = '$ORACLE_HOME/dbs/my_spfile.ora' FROM PFILE = '$ORACLE_HOME/dbs/my_pfile.ora';

shutdown immediate

cd $ORACLE_HOME/dbs

mv spfileSID.ora spfileSID.ora.bck

mv my_spfile.ora spfileSID.ora

startup

Oracle DB - 如何完整關閉 AMM (Automatic Memory Management)

SQL> show parameter memory
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 2G
memory_target                        big integer 512M

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2G
sga_target                           big integer 0

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> ALTER SYSTEM SET memory_target='0' SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET memory_max_target='0' SCOPE=spfile;
System altered.

SQL> ALTER SYSTEM SET sga_max_size = 786432000 SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET sga_target = 524288000 SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET pga_aggregate_target=100288000 SCOPE=BOTH;
System altered.

SQL> show parameter memory
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 2G
memory_target                        big integer 0

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2G
sga_target                           big integer 512M

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100288000

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 788529152 cannot be set to more than MEMORY_MAX_TARGET 0.

--如果不小心設了memory_max_target and memory_target，如何完整關閉 Automatic Memory Management

--僅用 ASMM（Automatic Shared Memory Management）

SQL> ALTER SYSTEM SET memory_target='0' SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET memory_max_target='0' SCOPE=spfile;
System altered.

SQL> ALTER SYSTEM SET sga_max_size = 786432000 SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET sga_target = 524288000 SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET pga_aggregate_target=100288000 SCOPE=BOTH;
System altered.

SQL> create pfile='/tmp/my_pfile.ora' from spfile;
File created.

vi /tmp/my_pfile.ora

找到
*.memory_max_target
*.memory_target
刪除之

CREATE SPFILE = '/tmp/my_spfile.ora' FROM PFILE = '/tmp/my_pfile.ora';
shutdown immediate
cd $ORACLE_HOME/dbs
mv spfileSID.ora spfileSID.ora.bck
cp -p /tmp/my_spfile.ora spfileSID.ora
startup


SQL> startup
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size                  2257352 bytes
Variable Size             520097336 bytes
Database Buffers          255852544 bytes
Redo Buffers                6791168 bytes
Database mounted.
Database opened.

SQL> show parameter memory
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 752M
sga_target                           big integer 512M

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100288000