结论

1,如何采用alter system 调整了RAC数据库实例或整个数据库的参数，会在调整当前节点的$ORACLE_HOME/dbs生成一个SPFILE文件
移除此文件后，当前节点数据库实例会采用基于ASM存储的SPFILE
2,但是另一个RAC数据库实例采用基于ASM存储的SPFILE
3,采用SGA及PGA全自动管理后，SHOW PARAMETER参数memory_max_target可能和sga_target相同
4,在出现第3种情况下，如何确认已经采用SGA及PGA的全自动管理，此在另一篇文章专门测试

背景

ORACLE发展到11G之后，SGA及PGA的管理更进一步，即SGA和PGA可以通过指定一个值，这样数据库就不用再分别分配SGA及PGA的大小，而让ORACLE自动调节其各个参数的大小，这样就更为方便了；
本文不讨论在ORACLE11G各版本因为启动SGA及PGA自动管理的性能隐患及BUG.
我们主要测试在ORACLE RAC架构下如何变更SGA及PGA手工管理为SGA及PGA全自动管理的调整过程

测试

1,节点1及节点2的SGA及PGA配置信息
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 556M
sga_target big integer 0
SQL> show parameter db_cach

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 216M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 24M
large_pool_size big integer 20M
olap_page_pool_size big integer 0
shared_pool_size big integer 288M
streams_pool_size big integer 0

SQL> show parameter pga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 187M

2,在节点1调整参数memory_*相关参数，启用SGA及PGA自动管理
SQL> alter system set memory_max_target=743m scope=spfile sid='*';

System altered.

SQL> alter system set memory_target=743m scope=spfile sid='*';

System altered.

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

Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 524292024 bytes
Database Buffers 247463936 bytes
Redo Buffers 2658304 bytes
Database mounted.
Database opened.

3,查看节点1数据库实例相关SGA及PGA参数的配置信息
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 744M
memory_target big integer 744M
shared_memory_address integer 0
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 744M
sga_target big integer 0
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 216M
SQL> show parameter log_buf

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 2478080
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 20M
olap_page_pool_size big integer 0
shared_pool_size big integer 288M
streams_pool_size big integer 0

4,重启节点2数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 580431872 bytes
Fixed Size 2230432 bytes
Variable Size 348129120 bytes
Database Buffers 226492416 bytes
Redo Buffers 3579904 bytes
Database mounted.
Database opened.
SQL>

5,发现节点2数据库实例仍保持原来的SGA自动管理
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 556M
sga_target big integer 0
SQL> show parameter db_cach

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 216M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 24M
large_pool_size big integer 20M
olap_page_pool_size big integer 0
shared_pool_size big integer 288M
streams_pool_size big integer 0
SQL>
SQL>
SQL>
SQL>
SQL>

6，查看节点1及节点2的数据库采用的SPFILE，发现不一致

---节点1
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfilejingfa1.ora

----节点2
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/jingfa/spfilejingfa.ora
SQL>

7,在节点1数据库实例基于文件系统的SPFILE生成PFILE,然后以生成的PFILE生成基于ASM存储的SPFILE

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfilejingfa1.ora

SQL> create pfile='/home/oracle/mod_pfile.ora' from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile='+DATA/jingfa/spfilejingfa.ora' from pfile='/home/oracle/mod_pfile.ora';

File created.

8，启动节点1数据库实例
SQL> startup
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 524292024 bytes
Database Buffers 247463936 bytes
Redo Buffers 2658304 bytes
Database mounted.
Database opened.

9,查看节点1数据库实例的SGA及PGA配置信息
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 744M
memory_target big integer 744M
shared_memory_address integer 0
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 744M
sga_target big integer 0
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 187M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 20M
olap_page_pool_size big integer 0
shared_pool_size big integer 288M
streams_pool_size big integer 0

10，重启节点2数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

[root@jingfa2 ~]# mount -o remount,size=3g /dev/shm
[root@jingfa2 ~]# exit
logout
[oracle@jingfa2 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 19 15:09:02 2015

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 524292024 bytes
Database Buffers 247463936 bytes
Redo Buffers 2658304 bytes
Database mounted.
Database opened.

11,确认节点2的数据库实例的SGA及PGA管理配置信息,这下正常了
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 744M
memory_target big integer 744M
shared_memory_address integer 0
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 744M
sga_target big integer 0
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 187M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 20M
olap_page_pool_size big integer 0
shared_pool_size big integer 288M
streams_pool_size big integer 0
SQL>

12，但是2个节点采用的SPFILE并未指向相同的ASM存储

---节点1
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfilejingfa1.ora

--节点2
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/jingfa/spfilejingfa.ora

13,尝试把节点1现在指向的文件系统的SPFILE删除，看会如何
[oracle@jingfa1 dbs]$ mv spfilejingfa1.ora spfilejingfa1.ora.orig

14,移除节点1的文件系统的SPFILE后，自动使用了基于ASM存储的SPFILE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2232392 bytes
Variable Size 524292024 bytes
Database Buffers 247463936 bytes
Redo Buffers 2658304 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/jingfa/spfilejingfa.ora
SQL>