Oracle 修改机器类型
2022-06-21
步骤如下：
# 修改主机名
echo "$(hostname -I || hostname -i) dbuis" >> /etc/hosts
# 修改监听配置
vim /u01/app/oracle/product/12/db_1/network/admin/listener.ora
# 启动 监听
lsnrctl start
# 链接数据库
sqlplus / as sysdba
# 停机
shutdown abort
# 启动
startup
# 256G 内存配置；这两个参数，建议使用 （内存 * 75%）
alter system set sga_max_size=160G scope=spfile;
alter system set sga_target=160G scope=spfile;
# 192G 内存配置；这两个参数，建议使用 （内存 * 75%）
alter system set sga_max_size=144G scope=spfile;
alter system set sga_target=144G scope=spfile;
########### 16G 内存配置 #################
# 修改 sga 参数；scope 参数需要重启数据库才能生效；16G 内存配置；这两个参数，建议使用 （内存 * 75%）
alter system set sga_max_size=10G scope=spfile;
alter system set sga_target=10G scope=spfile;
# 直接生效
alter system set pga_aggregate_limit=10485760K;
alter system set pga_aggregate_target=10485760K;
alter system set processes=1000 scope=spfile;
alter system set parallel_max_servers=1000;
alter system set parallel_min_servers=100;
alter system set parallel_servers_target=1024;
# 256M
alter system set shared_pool_reserved_size=268435456;
########### 16G 内存配置 #################
# 生成一个配置文件，修改新配置文件参数
create pfile='/u01/app/oracle/product/12/db_1/dbs/pfile_ora' from spfile='/u01/app/oracle/product/12/db_1/dbs/spfileDBUIS.ora';
# 根据新配置文件参数启动 oracle
startup pfile='/u01/app/oracle/product/12/db_1/dbs/pfile_ora'
# 如果新配置能启动，然后就用新配置去覆盖原先的配置文件
create spfile='/u01/app/oracle/product/12/db_1/dbs/spfileDBUIS.ora' from pfile='/u01/app/oracle/product/12/db_1/dbs/pfile_ora';
# 最终启动一下oracle数据库；
startup
Oracle查看SGA和PGA使用率
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;
结果
NAME TOTAL USED FREE PCTUSED
------------------------ ---------- ---------- ---------- ----------
SGA 16310.8477 15933.34 377.51 97.69
PGA 1594 1535.8 58.2 96.35
Shared pool 2592.03 2302.34 289.7 88.82
Large Pool 64 8.19 55.81 12.79
Default pool 3110.14 2451.91 658.23 .79
DEFAULT 16K buffer cache 0 0 0 0
DEFAULT 32K buffer cache 0 0 0 0
Java Pool 32 0 32 0
KEEP pool 0 0 0 0
RECYCLE pool 0 0 0 0
10 rows selected.