设置数据库自动内存管理主要是设置两个参数:
memory_max_target
memory_target
如果这两个参数设置为0说明数据库内存采用手动管理,反之则为自动管理。
关闭数据库。
SQL> startup nomount
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6384M
--这里之所以报错是我之前alter system set MEMORY_TARGET='1536M' scope='spfile';
因为之前系统分配给SGA的内存就这么大,我设置的参数绝对不能超过SGA,这也就是导致修改完参数重启数据库出错的原因。
SQL> create pfile ='/home/oracle/initora11g.ora' from spfile;
File created.
创建pfile文件,然后修改pfile文件设置这两个参数大小为7G。
[oracle@TestServer dbhome_1]$ vi /home/oracle/initora11g.ora
atsdb.__db_cache_size=16777216
atsdb.__java_pool_size=16777216
atsdb.__large_pool_size=16777216
atsdb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
atsdb.__pga_aggregate_target=5083496448
atsdb.__sga_target=1610612736
atsdb.__shared_io_pool_size=0
atsdb.__shared_pool_size=1526726656
atsdb.__streams_pool_size=16777216
*.audit_file_dest='/opt/app/oracle/admin/atsdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/atsdb/control01.ctl','/opt/app/oracle/flash_recovery_area/atsdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='atsdb'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=atsdbXDB)'
*.global_names=FALSE
*.memory_max_target=7610612736
*.memory_target=7610612736
*.open_cursors=300
*.pga_aggregate_target=5078253568
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=3555
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
修改完成之后从pfile启动数据库:
SQL>='/home/oracle startup pfile/initora11g.ora'
ORACLE instance started.
Total System Global Area 7616245760 bytes
Fixed Size 2214496 bytes
Variable Size 6979323296 bytes
Database Buffers 603979776 bytes
Redo Buffers 30728192 bytes
Database mounted.
Database opened.
可以看到数据库已经重新启动,因为刚才是在pfile中修改的参数,现在同步到spfile中:
SQL> create spfile from pfile='/home/oracle/initora11g.ora';
File created.
SQL> show parameter memory
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
7296M
memory_target big integer
7296M
shared_memory_address integer
0
SQL>
这里也可以看到现在数据库是采用自动内存管理的。
成功。
转载于:https://blog.51cto.com/buertu/1792389