为了保证内存的设置不影响数据库启动失败设置所以需要备份spfile文件/pfile文件[oracle@urp65 dbs]$cd /u01/app/oracle/oracle/o备份为了保证内存的设置不影响数据库启动失败设置所以需要备份spfile文件/pfile文件[Oracle@urp65 dbs]$cd /u01/app/oracle/oracle/oraInventory/db_1/dbs[oracle@urp65 dbs]$ cp spfilejwdb.ora spfilejwdb.ora.bak cd /u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/ cp init.ora.2132009144930 init.ora.2132009144930.bak实现2种方式1)init.ora时间点文件修改sga-target或增加sga_max_size 修改最大内存、实际使用内存值===》create spfile=spfileSID_NAME.ora 2):1158/em/调整内存值以下是整个过程直接修改测试--------在EM中直接的修还最大的SGA值ALTER SYSTEM SET sga_max_size = 524288000 COMMENT='internally adjusted' SCOPE=SPFILE重新启动数据库SQL> conn sys/jwdb as sysdbaò?á??ó?￡SQL> shutdown immediate;Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 222298336 bytes Database Buffers 297795584 bytes Redo Buffers 2973696 bytesêy?Y?a×°??íê±??￡SQL> show parameter sga_max_size;NAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 500M证明上述方法有效、但是需要启动数据库生效查看对应的文件1)vi /u01/app/oracle/oracle/oraInventory/db_1/dbs/spfilejwdb.ora$ORACLE_HOME/dbs/spfileSID_NAME.ora发现*.sga_max_size=524288000#internally adjusted #此时修改了、在spfilejwdb.ora中增加了一个字段*.sga_target=424288000 2)/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930$ORACLE_HOME/admin/SID_NAME/pfile/init.ora.时间点# SGA Memory###########################################sga_target=424288000--------在EM中直接的修的SGA_TARGET实际值在em中直接的修改内存值Old sga_target=408M sga_max_size=500M ALTER SYSTEM SET sga_target = 471859200 SCOPE=BOTH同时的修改spfile/当前服务进程的值、需要重新启动生效更新消息修改之后报告1)有变化vi /u01/app/oracle/oracle/oraInventory/db_1/dbs/spfilejwdb.ora*.sga_max_size=524288000#internally adjusted*.sga_target=473956352 2)没有变化/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930# SGA Memory###########################################sga_target=424288000已成功进行更改。可能需要一段时间更改才能生效。
3)重新启动数据库SQL> startup ORACLE ày3ìò??-???ˉ?￡Total System Global Area 524288000 bytes ==500M=sga_max_size Fixed Size 1220384 bytes Variable Size 222298336 bytes Database Buffers 297795584 bytes Redo Buffers 2973696 bytes查看SQL> show parameter sga_target NAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_target big integer 452M SQL> show parameter sga_max_size NAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 500M 1)查看内存、没有变化vi /u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930# SGA Memory###########################################sga_target=424288000 2)vi /u01/app/oracle/oracle/oraInventory/db_1/dbs/spfilejwdb.ora 没有变化*.sga_max_size=524288000#internally adjusted*.sga_target=473956352结论修改当前内存时候不需要重新启动数据库、但是修改最大内存需要重新的启动数据库$ORACLE_HOME/admin/SID_NAME/pfile/init.ora.时间点$ORACLE_HOME/dbs/spfileSID_NAME.ora 2者之间不存在必然的联系、除非使用create spfile from create spfile from pfile='/opt/oracle/admin/jwdb/pfile/init.ora.2132009144930'从而产生新的spfile文件格式为了保证内存的设置不影响数据库启动失败设置所以需要备份spfile文件/pfile文件[oracle@urp65 dbs]$cd /u01/app/oracle/oracle/oraInventory/db_1/dbs[oracle@urp65 dbs]$ cp spfilejwdb.ora spfilejwdb.ora.bak cd /u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/ cp init.ora.2132009144930 init.ora.2132009144930.bak------------单独创建spfile文件SQL> create spfile from pfile='/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930'2 / create spfile from pfile='/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930'*μú 1 DD3???′í?ó:ORA-32002: ?T·¨′′?¨ò?óéêμàyê1ó?μ? SPFILE需要关闭数据库在nmount下建立pfile否则如果在使用的话、失败SQL> shutdown immediate;êy?Y?aò??-1?±??￡ò??-D???êy?Y?a?￡ORACLE ày3ìò??-1?±??￡SQL> create spfile from pfile='/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930'2 / SQL> startup ORACLE ày3ìò??-???ˉ?￡Total System Global Area 436207616 bytes Fixed Size 1219856 bytes Variable Size 125829872 bytes Database Buffers 306184192 bytes Redo Buffers 2973696 bytes启动发现内存值变化了spfilejwdb.ora与init.ora2132009144930保持一致的*.sga_target=434288000；
但是此时没有sga_max_size在sfilejwdb.ora中出现、应为在init.ora2132009144930没有指定[oracle@urp65 pfile]$ pwd/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile[oracle@urp65 pfile]$ vi init.ora.2132009144930增sga_max_size=524288000#internally adjusted---关闭SQL> shutdown immediate;êy?Y?aò??-1?±??￡ò??-D???êy?Y?a?￡ORACLE ày3ìò??-1?±??￡SQL> create spfile from pfile='/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930'2 / SQL> startup ORACLE ày3ìò??-???ˉ?￡Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 213909728 bytes Database Buffers 306184192 bytes Redo Buffers 2973696 bytesêy?Y?a×°??íê±??￡êy?Y?aò??-′ò?a?￡启动发现值发生了变化、最大内存变化为524288000 bytes 与sga_max_size=524288000#internally adjusted增加保持一致了因此直接修改 init.ora.2132009144930---->增加sga_max_size=524288000#internally adjusted、*.sga_target=434288000；===》 create spfile 生成达到同时修改的功效、最有效-----直接修改spfilejwdb.ora值*.sga_max_size=504288000#internally adjusted*.sga_target=404288000 SQL> shutdown immediateêy?Y?aò??-1?±??￡ò??-D???êy?Y?a?￡ORACLE ày3ìò??-1?±??￡SQL> startup ORA-01078: failure in processing system parameters LRM-00109: ???????????????? '/u01/app/oracle/oracle/oraInventory/db_1/dbs/initjwdb.ora'报告错误说明、无法直接的修改spfilejwdb.ora文件而是需要通过修改init.ora.2132009144930 实现解决办法:SQL> create spfile from pfile='/u01/app/oracle/oracle/oraInventory/db_1/admin/jwdb/pfile/init.ora.2132009144930'2 / SQL> startup ORACLE Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 213909728 bytes Database Buffers 306184192 bytes Redo Buffers 2973696 bytes设置成功!!!!，