基础环境：Oracle 19c RAC+ADG场景描述：19c 主库PDB中设置SGA_TARGET，可以自动同步到备库中，无需DBA再干预。 Oracle中SGA_TARGET 指定所有SGA 组件的总大小，19c中可以在CDB和PDB级设置该参数。一、检查SGA_TARGET参数1、在根容器cdb$root中检查
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL>select p.name,s.NAME,s.ISSES_MODIFIABLE,s.ISSYS_MODIFIABLE,s.ISPDB_MODIFIABLE,s.CON_ID from v$system_parameter s,v$pdbs p where s.name='sga_target' and s.con_id=p.con_id; NAME NAME ISSES ISSYS_MOD ISPDB CON_ID -------------------- -------------------- ----- --------- ----- ---------- PDB$SEED sga_target FALSE IMMEDIATE TRUE 2 HRPDB sga_target FALSE IMMEDIATE TRUE 3 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 2352M
该环境cdb中sga_target总大小为2352m,sga_target参数支持在PDB中设置。2、在主库pdb中设置sga_target 切换到hrpdb中
SQL> alter session set container=hrpdb; Session altered.
修改前pdb中 sga_target是默认值为0
SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 0 SQL> SQL> alter system set sga_target=1G； System altered. SQL>
3、主库中查询sga_target
SQL> set linesize 120 column pdb_name format a10 column name format a30 column value$ format a30 select ps.db_uniq_name, ps.pdb_uid, p.name as pdb_name, ps.name, ps.value$ from pdb_spfile$ ps join v$pdbs p on ps.pdb_uid = p.con_uid order by 1, 2, 3; DB_UNIQ_NAME PDB_UID PDB_NAME NAME VALUE$ ------------------------------ ---------- ---------- ------------------------------ ------------------------------ * 3293159541 HRPDB sga_target 1073741824 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 1G SQL>
在主库PDB中sga_target已经是1G。4、备 库中查询sga_target
SQL> set linesize 120 column pdb_name format a10 column name format a30 column value$ format a30 select ps.db_uniq_name, ps.pdb_uid, p.name as pdb_name, ps.name, ps.value$ from pdb_spfile$ ps join v$pdbs p on ps.pdb_uid = p.con_uid order by 1, 2, 3; DB_UNIQ_NAME PDB_UID PDB_NAME NAME VALUE$ ------------------------------ ---------- ---------- ------------------------------ ------------------------------ * 3293159541 HRPDB sga_target 1073741824 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 1G SQL>
在备库PDB中sga_target已经是1G，主库pdb级调整的sga_target已成功同步到备库。
在生产环境中，有些主备环境的配置不一样，比如备库的CPU和内存等资源比主库低，这样的场景能否使用主库pdb修改sga_target同步到备库呢？
答案是可以。
1、可以通过隐藏参数让主库PDB级修改的sga_target参数同步到备库不应用到PDB中 SQL> alter system set "_parameter_spfile_sync"=false; 2、主库调整的sga_target超过了备库的内存大小，备库pdb会自动使用sga_max_size的值主库PDB中 SQL> alter system set sga_target=2g;备库PDB中
SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 1G sga_min_size big integer 0 sga_target big integer 1G unified_audit_sga_queue_size integer 1048576 SQL>
-the end-
