图灵小队—Oracle数据库上线前检查及优化流程（完整版）

一、系统环境

优化首先见：https://www.topunix.com/post-10615.html

1、目录检查

[root@localhost ~]# df -h

/、/oracle、/backup、swap

2、内存检查

[root@localhost ~]# free -m

3、lvm格式检查

[root@localhost ~]# df -h

4、防火墙检查

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# systemctl disable firewalld.service 
[root@localhost ~]# systemctl status firewalld.service

5、Selinux检查

（1）永久生效：

[root@localhost ~]# vi /etc/selinux/config
SELINUX=disabled

（2）立即生效：

[root@localhost ~]#setenforce 0

6、DNS配置检查

[root@localhost ~]#cat /etc/resolv.conf 
nameserver 223.5.5.5

7、系统时间同步

（1）安装ntp

yum install -y ntp

（2）配置ntp.conf

vi /etc/ntp.conf

#server 0.rhel.pool.ntp.org iburst
#server 1.rhel.pool.ntp.org iburst
#server 2.rhel.pool.ntp.org iburst
#server 3.rhel.pool.ntp.org iburst
server prd.zhiban.com iburst

（3）重启ntp服务并设置自动启动

systemctl restart ntpd
systemctl enable tpd

（4）查看当前时间

#date

8、物理时钟检查

（1）物理时时间查看

# hwclock --show

（2）如果物理时钟时间和系统时间不一致，则将物理时钟时间同步

#hwclock --systohc

9、系统时区检查

（1）时区检查

一般国内使用东八区时区，Asia/Shanghai (CST, +0800)，如果不是需要修改

[root@qdmes241 restore]# date -R
Fri, 26 Aug 2022 09:52:37 +0800

[root@qdmes241 restore]# timedatectl 
Local time: Fri 2022-08-26 09:53:54 CST
Universal time: Fri 2022-08-26 01:53:54 UTC
RTC time: Fri 2022-08-26 01:54:31
Time zone: Asia/Shanghai (CST, +0800)
NTP enabled: yes
NTP synchronized: no
RTC in local TZ: no
DST active: n/a

（2）所有时区列表

[root@qdmes241 restore]# timedatectl list-timezones

（3）时区修改为东八区即可

[root@qdmes241 restore]# timedatectl set-timezone Asia/Shanghai

10、关闭NetworkManager网络管理器

linux 7系列安装了两种网络管理工具network和Networkmanager。
在CentOS/RHEL/OL7系统上，目前有NetworkManager和network两种网络管理工具。
如果两种都配置会引起冲突，而且NetworkManager在网络断开的时候，会清理路由；
如果一些自定义的路由，没有加入到NetworkManager的配置文件中，路由就被清理掉，网络连接后需要自定义添加上去。

systemctl stop NetworkManager
systemctl disable  NetworkManager

11、系统监控工具（20230409增加）

（1）编辑/etc/.bash_profile

alias topc='ps -e -o pcpu,pid,user,tty,args|sort -n -k 1 -r|head'
alias topm='ps -e -o pmem,pid,user,tty,args|sort -n -k 1 -r|head'

（2）sql性能锁表等输出脚本

sql_out.sh

（3）日常项检查输出脚本

cpu/内存/io等

ping，tenlet等

（4）sql日常查看等脚本输出

v$flash_recovery_area_usage;

（5）综合脚本

（6）讲一个资源密集型进程映射到一个数据库进程

topc后通过查询数据字典进行进程确定。（11g优化攻略6-9）

可以kill，可以查看执行计划。

二、系统参数

1、字符集检查（尤为重要，如果数据库字符集不一致，重新DBCA卸载并新装实例即可）

SQL> col value for a30
SQL> select *  from nls_database_parameters;

字符集要设置为AL32UTF8

2、内核参数检查

（1）内核参数sysctl.conf

[root@localhost ~]# vi /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
kernel.shmmax = 85899345920
kernel.shmall = 20971520
kernel.shmmni = 4096
vm.nr_hugepages = 40960

生效上述参数：sysctl -p

其中shmmax、shmall、shmmni、vm.nr_hugepages，memlock等

总原则：物理内存>memlock>kernel.shmmax>vm.nr_hugeages>DB Memory(SGA+PGA)>SGA

（2）资源限制

[root@localhost ~]# vi /etc/security/limits.conf

#ORACLE SETTING
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 16384
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 100000000    --根据上述参数设置
oracle soft memlock 100000000    --根据上述参数设置

3、内存大页开启

vm.nr_hugepages的值根据上述参数值设置

（1）查看是否使用

less /proc/meminfo |grep Huge

（2）如下显示则表示正常使用内存大页

HugePages_Total:82120
HugePages_FREE:77159

（2）如下显示则表示没有使用内存大页

如果没有使用则考虑需要进行修改memlock等。

4、透明大页关闭

（1）查看是否开启

always即为关闭，设置为never

[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never
[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

（2）临时关闭，立即生效

[root@localhost ~]# echo never > /sys/kernel/mm/transparent_hugepage/enabled
[root@localhost ~]# echo never > /sys/kernel/mm/transparent_hugepage/defrag

（3）永久关闭，重启生效

[root@localhost ~]# vi /etc/rc.d/rc.local

 if test -f /sys/kernel/mm/transparent_hugepfage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

三、数据库参数

1、EM管理控制台停用

[oracle@localhsot:/home/oracle]$emctl status dbconsole
[oracle@localhsot:/home/oracle]$emctl stop dbconsole

2、配置oracle用户的.bash_profile

（1）下载安装rlwrap

[root@linux1 ~]# wget https://download.topunix.com/rlwrap-0.42-1.el6.x86_64.rpm

[root@linux1 ~]# rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm

（2）进入oracle用户，并编辑.bash_profile

[root@localhost /]# su - oracle
[oracle@localhost ~]$ vi .bash_profile

export PS1="[`whoami`@`hostname`:"'$PWD]$'
export TMP=/tmp
export LANG=en_US.UTF-8
export TMPDIR=$TMP
export ORACLE_UNQNAME=mesorcl
export ORACLE_SID=mesorcl;              
export ORACLE_BASE=/oracle/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_TERM=xterm;
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS";
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_BASE/product/11.2.0/db_1/bin:$ORACLE_HOME/bin;
export THREADS_FLAG=native;
umask=022;
export LOG=$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace
alias log='/bin/tail -100f $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'

3、配置grid用户的.bash_profile（如果是asm或rac）

（1）进入grid用户

[root@localhost /]# su - grid

[grid@localhost:/home/grid]$vi /home/grid/.bash_profile
export PS1="[`whoami`@`hostname`:"'$PWD]$'
umask 022
#alias sqlplus="rlwrap sqlplus"
export TMP=/tmp
export LANG=en_US.UTF-8
export TMPDIR=$TMP
export ORACLE_UNQNAME=+asm
export ORACLE_SID=+ASM
export ORACLE_TERM=xterm
export ORACLE_BASE=/oracle/app/grid
export ORACLE_HOME=/oracle/app/11.2.0/grid
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS" 
export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin
export THREADS_FLAG=native;    ####线程的标志，grid用户一定要#要的

export LOG=$ORACLE_BASE/diag/asm/$ORACLE_UNQNAME/$ORACLE_SID/trace
alias log='/bin/tail -100f $ORACLE_BASE/diag/asm/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias sqlplus='rlwrap sqlplus'
alias asmcmd='rlwrap asmcmd'

4、SGA和PGA配置

（根据上述列表配置)

sga_target=物理内存*0.8*0.8 
pga_target=物理内存*0.8*0.8

SQL>show parameter sga_target;
SQL>show parameter pga_aggregate_target;

--如果不满足大小，重新修改设置如（重启数据库生效)：

SQL> alter system set sga_max_size=80G scope=spfile;  --根据上述表对应进行填写
SQL> alter system set sga_target=80G scope=spfile;    --根据上述表对应进行填写

SQL> alter system set pga_aggregate_target =20G;      --根据上述表对应进行填写

5、密码过期问题修改

SQL>select * from dba_profiles where profile ='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
SQL>alter profile default limit password_life_time unlimited;

6、关闭审计

SQL>show parameter audit_trail;
SQL>alter system set audit_trail=none scope=spfile;
SQL>shutdown immediate
SQL>startup
SQL>show parameter audit_trail;

7、redo log的配置

（1）确认redo log组至少5个组，且每一组成员至少2个，每一个成员大小至少为200M

SQL> select group#,bytes/1024/1024 MB,STATUS from v$log;
SQL> select GROUP#,status,member from v$logfile;

--如果每一个成员大小不是大于等于200M，需要逐个进行修改；

SQL>alter system switch logfile; ---会将current状态改为active，inactive改为active
SQL>alter system checkpoint;      --执行后，立即将active状态改为inactive.
SQL>select group#,bytes/1024/1024 MB,STATUS from v$log;

SQL>ALTER DATABASE DROP LOGFILE GROUP 组号; --删除status为inactive的组
SQL>alter database add logfile group 1 ('/oracle/app/oracle/oradata/mesorcl/redo01a.log') size 200m; --增加上述删除的组

--如果组的总数量少于5个(默认为3个)，需要增加：

SQL>alter database add logfile group 4 ('/oracle/app/oracle/oradata/mesorcl/redo04a.log') size 200m;
SQL>alter database add logfile group 5 ('/oracle/app/oracle/oradata/mesorcl/redo05a.log') size 200m;

--如果每一个组的成员数量少于2个，需要增加：

SQL> alter database add logfile member '/oracle/oradata/mesorcl/redo01b.log' to group 1; 
SQL> alter database add logfile member '/oracle/oradata/mesorcl/redo02b.log' to group 2; 
SQL> alter database add logfile member '/oracle/oradata/mesorcl/redo03b.log' to group 3; 
SQL> alter database add logfile member '/oracle/oradata/mesorcl/redo04b.log' to group 4; 
SQL> alter database add logfile member '/oracle/oradata/mesorcl/redo05b.log' to group 5; 

8、sqlplus的glogin配置

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

set timing on
set time on
set pagesize 100
set linesize 400
column table_name format a50
column index_name format a50
column owner      format a20
column file_name  format a60
column name format a60 
column global_name noprint
set termout on
set sqlprompt "_user'@'_connect_identifier>"

----------------------------------下面的设置暂不需要----------------------------------------------------------------------------
set timing on
set time on
set pagesize 100
set linesize 400
column table_name format a30
column index_name format a30
column owner format a20
col file_name for a60
define gname=idle
column global_name new_value gname
column global_name noprint
set termout on
select sys_context('USERENV','CURRENT_USER') ||'@'||sys_context('USERENV','DB_NAME') global_name from dual;
set sqlprompt '&gname>'
--------------------------------------------------------------------------------------------------------------------------

9、开启归档日志

SQL>!mkdir -p /oracle/app/oracle/product/11.2.0/db_1/dbs/arch
SQL>alter system set db_recovery_file_dest_size=500g;  --根据/oracle空间进行分配
SQL>alter system set db_recovery_file_dest='/oracle/app/oracle/product/11.2.0/db_1/dbs/arch';
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;

10、数据库时区检查

SQL> select dbtimezone from dual;
DBTIME
------
+08:00

如果不是则进行下面操作：

select dbtimezone from dual;       --查看数据库时区
select sessiontimezone from dual;  --查看会话时区

（1）查看是否有表处于 TIMESTAMP WITH LOCAL TIME ZONE columns状态

如果直接修改dbtimezone，可能会报错：

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

SQL>select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231
and o.obj# = c.obj#
and u.user# = o.owner#;
/

执行此句，如果能查询到数据进行第2步操作，否则转到3

（3）执行下列语句，对上述查出的表进行备份

--创建一个临时表

create table ttt(order_id number ,oeder_Date date);

--导入数据

insert into ttt(order_id,oeder_date) select order_id,order_date from oe.orders

--修改oe.orders表

alter table oe.orders drop column order_date
alter table oe.orders add order_date date

--将临时表中的数据恢复到oe.orders

update oe.orders a set order_date = (select oeder_Date from ttt b where a.order_ID = b.order_id);

--删除临时表

drop table ttt

（3）修改 DBTIMEZONE

alter database set time_zone='+08:00';

（4）重启数据库后查看DBTIMEZONE

SQL>select DBTIMEZONE from dual;

11、增加 listener.ora 和sqlnet.ora内容

（1）listener.ora增加内容：

vi /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

DIAG_ADR_ENABLED_LISTENER=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER=180

（2）修改sqlnet.ora内容

vi /oracle/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

DIAG_ADR_ENABLED=OFF
SQLNET.INBOUND_CONNECT_TIMEOUT=180

（3）重新加载监听

$lsnrctl reload
$lsnrctl status

（4）解决的问题：

TNS-12535、TNS-00505，Fatal NI connect error 12170.

（5）问题的原因：

由于 Automatic Diagnostic Repository 中的 Oracle Net diagnostic 在默认情况下是开启的；

当数据库和客户端的连接超过特定时间，就会把连接信息写入到 alert 日志中，产生大量的监听日志，导致相关的日志文件不断增大。

12、调整硬解析模式--将常量解析改为强制软解析

alter system set cursor_sharing=force scope=both;

四、备份及容灾系列

1、冷备

#su - oracle
SQL>shutdown immediate
$cd /oracle
$tar zcvf app.20211123.tar.gz app
SQL>startup

2、rman备份

（1）配置备份目录及脚本目录

[oracle@localhosts:/home/oracle]$mkdir -p /backup/rman
[oracle@localhosts:/home/oracle]$mkdir -p /backup/scripts

（2）脚本查看并创建相应脚本

[oracle@jshyora:/home/oracle]$vi /backup/scripts/rman0.sh

主机脚本-点击查看（备份数据库+归档+参数+控制文件+备份片限制10g+密码加密）

主机脚本-点击查看（备份数据库+归档+参数+控制文件+备份片限制10g+密码加密+压缩功能）

（3）配置定时任务

[oracle@localhosts:/home/oracle]$crontab -e

02 00 * * * sh /backup/scripts/rman0.sh >/dev/null

3、expdp备份

（1）配置备份目录及脚本目录

[oracle@localhosts:/home/oracle]$mkdir -p /backup/expdp

（2）脚本查看并创建相应脚本

[oracle@jshyora:/home/oracle]$vi /backup/scripts/expdp.sh

expdp备份脚本查看

（3）配置定时任务

[oracle@localhosts:/home/oracle]$crontab -e

04 00 * * * sh /backup/scripts/expdp.sh >/dev/null

4、定时删除归档

一般备份脚本自带，如果没有做备份，需要设置脚本进行删除

5、ADG配置

各厂实际情况进行配置。

可参考：https://www.topunix.com/post-3694.html

五、后期优化

1、SGA的优化建议

select * from v$sga_target_advice;

2、PGA的优化建议

select * from v$pga_target_advice;

3、AMM的memory的优化建议

select * from v$memory_target_advice order by memory_size;

4、修改进程数、游标数，游标缓存

（1）先查看游标缓存及游标是否够用

SELECT 'session_cached_cursors' PARAMETER,
LPAD(VALUE, 5) VALUE,
DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%')
USAGE
FROM (SELECT MAX(S.VALUE) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME = 'session cursor cache count'
AND S.STATISTIC# = N.STATISTIC#),
(SELECT VALUE FROM V$PARAMETER WHERE NAME =
'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
LPAD(VALUE, 5),
TO_CHAR(100 * USED / VALUE, '990') || '%'
FROM (SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME IN
('opened cursors current', 'session cursor cache count')
AND S.STATISTIC# = N.STATISTIC#
GROUP BY S.SID),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

（2）根据上述测试进行修改

alter system set processes=2000 scope=spfile;              --进程2000
alter system set open_cursors=2000 scope=spfile;           --游标2000
alter system set session_cached_cursors=300 scope=spfile; --游标缓存300
alter system set db_files=8192 scope=spfile;               --最大数据文件数量8192个

（3）查询进程数、游标数、连接数

select count(*) from v$process;
select count(*) from v$session;
select count(*) from v$session where status='ACTIVE';

5、统计信息时间调整（新环境暂时不调整）

参见：https://www.topunix.com/post-5650.html

（1）将周一到周日的信息统计设置为中午11点30

SQL>select * from dba_autotask_client;

begin
dbms_scheduler.set_attribute
(
  name => 'MONDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=MON;byhour=11;byminute=30; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'TUESDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=TUE;byhour=11;byminute=30; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'WEDNESDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=WED;byhour=11;byminute=30; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'THURSDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=THU;byhour=11;byminute=30; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'FRIDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=FRI;byhour=11;byminute=30; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'SATURDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=SAT;byhour=7;byminute=50; bysecond=0'
);
end;
/

begin
dbms_scheduler.set_attribute
(
  name => 'SUNDAY_WINDOW',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=SUN;byhour=7;byminute=50; bysecond=0'
);
end;
/

6、关闭整个SQL Tuning advisor优化建议（新环境先不要关闭）

SQL>select client_name,status,window_group from dba_autotask_client;

BEGIN
 dbms_auto_task_admin.disable(
 client_name => 'sql tuning advisor',
 operation => NULL,
 window_name => NULL);
END;
/

7、优化ASH

参见：https://www.topunix.com/post-6157.html

（1）查看当前（默认1m)

select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm ='_ash_size'
order by
translate(x.ksppinm, ' _', ' ');

（2）建议为128M，一般我们设置128*1.5=192m

SQL>alter system set "_ash_size"=201326592;

（3）再次查看

select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm ='_ash_size'
order by
translate(x.ksppinm, ' _', ' ');

六、数据库补丁

1、本机备份（expdp,rman）

https://www.topunix.com/post-6495.html

2、异机备份（expdp,rman）

https://www.topunix.com/post-5165.html

七、数据库备份

https://www.topunix.com/post-6082.html

八、数据库数据导入

https://www.topunix.com/post-3370.html