业务量：数据库同时访问量在300人左右
RHEL Linux7 + Oracle11.2.0.4数据库
空间要求：200G
数据库名：fghsdb
数据库块大小：8192
数据库字符集：ZHS16GBK
1.生产环境Oracle11gR2 for Linux项目的规划
vmware 12+ 15
物理机CPU I3/I5/I7/E，内存，建议最低16G，有条件32G,硬盘500G
虚拟机
CPU：双核+
内存：8G （2g-8g）
核算系统
os:rhel linux 7.6 x64
ip: 192.168.1.52
host name: ippuxhsdb52
db name:fghsdb
数据量：200G
/ 80G （50〜100G）
/oracle 50G
/oradata 200G
/backup 400G
/archive 200G 保留一周的归档
swap
/—根目录，一般安装标准磁盘
数据库可以ASM和LVM
RHEL 7.6 install
带GUI的服务器、硬件监控工具、JAVA平台、KDE、大系统性能、主框架访问、性能工具、兼容性程序库、开发工具、系统管理工具、选择基础设施服务器-->调试工具
1) hosts文件配置
vi /etc/hosts
192.168.1.52 itpuxhsdb
or
echo "192.168.1.52 itpuxhsdb" >> /etc/hosts
cat /etc/hosts
2)配置语言环境
echo "export LANG=en_US" >> ~/.bash_profile
3)准备文件系统
fdisk -l |grep "dev/sd"
pvcreate /dev/sdb /dev/sdc /dev/sdd /dev/sde
vgcreate oravg /dev/sdb
vgcreate datavg /dev/sdc
vgcreate backvg /dev/sdd
vgcreate archvg /dev/sde
lvcreate -n oralv -L 50000M oravg
lvcreate -n datalv -L 200000M datavg
lvcreate -n backuplv -L 400000M backvg
lvcreate -n archivle -L 200000M archvg
pvs
vgs
lvs
mkfs.xfs /dev/backvg/backuplv
mkfs.xfs /dev/backvg/archivle
mkfs.xfs /dev/datavg/datalv
mkfs.xfs /dev/oravg/oralv
vi /etc/fstab
/dev/oravg/oralv /oracle xfs defaults 0 0
/dev/datavg/datalv /oradata xfs defaults 0 0
/dev/backvg/backuplv /backup xfs defaults 0 0
/dev/backvg/archivelv /archive xfs defaults 0 0
额外在内存使用超过物理内存的1/2的前题下，加大本机的共享内存空间。
none /dev/shm tmpfs default,size=6144m 0 0
mount -o remount /dev/shm
mkdir /oracle
mkdir /oradata
mkdir /backup
mkdir /archive
mount /oracle
mount /oradata
mount /backup
mount /archive
4.创建用户、组
/usr/sbin/groupadd -g 60001 oinstall
/usr/sbin/groupadd -g 60002 dba
/usr/sbin/groupadd -g 60003 oper
useradd -u 61001 -g install -G dba,oper oracle
passwd oracle
mkdir -p /oracle
mkdir -p /oracle/app/oracle/product/11.2.0
mkdir -p /oracle/app/oraInventory
chown -R oracle:oinstall /oracle
chown -R oracle:dba /oradata
chown -R oracle:dba /backup
chmod -R 775 /oracle
chmod -R 775 /oradata
chmod -R 775 /backup
chmod -R 775 /archive
mount /dev/cdrom /mnt
5.配置yum软件安装环境及软件包安装
mount /dev/cdrom /mnt
cd /etc/yum.repos.d
mkdir bk
mv public-*.repo bk/
or
mv *.repo bk/
vi itpux.repo
[EL]
name = Linux 7.x DVD
name = Linux 7.x DVD
baseurl = file:///mnt
gpgcheck=0
enable=1
:wq!
cat /etc/yum.repos.d/itpux.repo
yum list |more
#From Public Yum or ULN
yum -y install autoconf
yum -y install automake
yum -y install binutils
yum -y install binutils-devel
yum -y install bison
yum -y install cpp
yum -y install dos2unix
yum -y install ftp
yum -y install gcc
yum -y install gcc-c++
yum -y install lrzsz
yum -y install python-devel
yum -y install compat-db*
yum -y install combat-gcc-34
yum -y install combat-gcc-34-c++
yum -y install combat-libcap1
yum -y install combat-libstdc++-33
yum -y install combat-libstdc++-33.i686
yum -y install glibc-*
yum -y install glibc-*.i686
yum -y install libXpm-*.i686
yum -y install libXp.so.6
yum -y install libXt.so.6
yum -y install libXtst.so.6
yum -y install libXext
yum -y install libXext.i686
yum -y install libXtst
yum -y install libXtst.i686
yum -y install libX11
yum -y install libX11.i686
yum -y install libXau
yum -y install libXau.i686
yum -y install libxcb
yum -y install libxcb.i686
yum -y install libXi
yum -y install libXi.i686
yum -y install libXtst
yum -y install libstdc++-docs
yum -y install libgcc_s.so.1
yum -y install libstdc++-.i686
yum -y install libstdc++-devel
yum -y install libstdc++-devel.i686
yum -y install libaio
yum -y install libaio.i686
yum -y install libaio-devel
yum -y install libaio-devel.i686
yum -y install libXp
yum -y install libaio-devel
yum -y install numactl
yum -y install numactl-devel
yum -y install make -y
yum -y install sysstat -y
yum -y install unixODBC
yum -y install unixODBC-devel
yum -y install elfutils-libelf-devel-O.97
yum -y install elfutils-libelf-devel
yum -y install redhat-lsb-core
yum -y install unzip
yum -y install *vnc*
3.6修改系统相关参数
1)修改资源限制参数
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 8000000
oracle soft memlock 8000000
—memlock 单位KB，略低于物理内存。
2)控制给用户分配的资源
vi /etc/security/limits.d/20-nproc.conf
* soft nproc 16384
echo "session required pam_limits.so" >> /etc/pam.d/login
vi /etc/security/limits.d/90-nproc.conf
* - proc 16384 //每个用户的进程数限制
cat /etc/pam.d/login
vi /etc/sysctl.conf
#ORACLE SETTING
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.panic_on_oops = 1
kernel.shmmax = 5153960756
kernel.shmall = 1228800
kernel.shmmni = 4096
vm.nr_hugepages = 2500
sysctl -p
--kernel.shmmax = 5033164800 单个共享内存段最大值，让数据库在一个共享段里面的可以容纳整个SGA。
8g：数据库最大使用内存，不要超过物理内存的80%（sga*80%+pga*20%）
8g:sga<4.8g, pga<1.6g
4.8*1024*1024*1024 = 5153960756
4800*1024*1024 = 5033164800
--kernel.shmall = 1228800 控制共享内存页数= shmmax/PAGESIZE=5033164800/4096=1228800
getconf PAGESIZE
--kernel.shmmni = 4096 共享内存段的最大数量
vm.nr_hugepages = 2900 --生产环境LINUX必配，否则性能差，物理内存大于8G一定要配。
（sga_max_size+1g)/2M < memlock
1.(4.8g+1g)/2M=5800/2=2900
2. sga_max_size/2M+100M = 4800/2+100M=2400+100 = 2500M 比较安全
cat /proc/meminfo
--4)关闭透明页（rhel 6.x）//rhel7没有这个bug
cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
vi /etc/rc.d/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/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
chmod +x /etc/rc.d/rc.local
--5)关闭numa功能
yum install numactl -y
numastat
numactl --show
numactl --hardware
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"
grub2-mkconfig -o /etc/grub2.cfg
--3.7配置安全
--1)禁用SELINUX
i /etc/selinux/config
SELINUX=disabled
setenforce 0
--2)关防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
重启os
--3.8配置oracle环境变量
su -oracle
vi ~/.bash_profile
PS1="[`whoami`@`hostname`:"'$PWD]$'
export PS1
export TMP=/tmp
export LANG=en_US
export TMPDIR=$TMP
export ORACLE_UNQNAME=fghsdb
ORACLE_SID=fghsdb; export ORACLE_SID
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMAT
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_BASE/product/11.2.0/db_1/bin:$ORACLE_HOME/bin; export PATH
THREADS_FLAG=native; export THREADS_FLAG
umask=022
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
su - oracle
env |grep ORACLE
4.RHEL Linux平台Oracle软件的下载
5.Oracle11gR2 for Linux数据库软件安装
6.Oracle11gR2 for Linux监听程序的配置
su - oracle
cd /backup
ls
unzip p13390677_112040_Linux-x86-64_1of7.zip
unzip p13390677_112040_Linux-x86-64_2of7.zip
vncserver
password
Verify:
y
ps -ef|grep vnc
reboot
tigervnc
cd /backup
ls
cd database/
ls
./runInstaller -jreLoc /etc/alternatives/jre_1.8.0
rpm -e ksh-20120801-139.el7.x86_64
rpm -ivh --force --nodeps pdksh-5.2.14-30.x86_64.rpm
rmp -ivh /backup/compat-libstdc-33-3.2.3-72-e17-x86_64.rpm
su - oracle
cd $ORACLE_HOME
cd sysman/lib
cp ins_emagent.mk ins_emagent.mk.bak
vi ins_emagent.mk
/NMECTL
$(MK_EMAGENT_NMECTL) -lnnz11
:wq!
su - oracle
netca
lsnrctl status
lsnrctl start
su - root
netstat -an|grep 1521
以下为数据库安装
Enable automatic maintenance tasks
Use the Administrative Password for All Accounts
Use Common Location for All Database Files
Database Files Location:/oradata
Specify Fast Recovery Area 闪回
Enable Archiving 归档
上面两种都不开
Enterprise Manager Repository [SYSAUX] 必须打钩
sga<=物理内存*80%*80%
pga<=物理内存*80%*20%
SiZing块大小 问开发 8192
Processes 进程数2000
Character Sets字符集
Choose from the list of character sets
ZhS16GBK GBK 16-bit Simplified Chinese
Dedicated Server mode //每人一个进程
Shared Server Mode //2000个人一个进程
一般选每人一个进程
Storage
Controlfile Options Maximum Datafiles: 8192
Tablespaces
SYSAUX
Size:>File Size:20480 M Bytes
关闭自动扩展
SYSTEM
Size:>File Size:20480 M Bytes
关闭自动扩展
TEMP
20G
UNDOTBS1
20G
USERS
200M
Redo Log Groups
生产环境5组
每组200M
Create Database
Save as a Database Template
Generate Database Creation Scripts
上述三个打钩
su - oracle
lsnrctl status
sqlplus "/as sysdba"
sqlplus /nolog
SQL> conn /as sysdba;
or
lsnrctl status
Listener Parameter File: /Oracle/app/oracle/product/11.2.0/db_1/network/admin/
cd /Oracle/app/oracle/product/11.2.0/db_1/network/admin/
ls
cat tnsnames.ora
tnsping fghsdb
sqlplus /nolog
conn sys/oracle@fghsdb as sysdba;
conn system/oracle/fghsdb;
select open_mode from v$database;
select instance_name,status from v$instance;
select name from v$datafile;
show parameter sga;
show parameter pga;
su - oracle
cd /backup
ls
unzip p13390677_112040_Linux-x86-64_1of7.zip
unzip p13390677_112040_Linux-x86-64_2of7.zip
vncserver
password
Verify:
y
ps -ef|grep vnc
reboot
tigervnc
cd /backup
ls
cd database/
ls
./runInstaller -jreLoc /etc/alternatives/jre_1.8.0
rpm -e ksh-20120801-139.el7.x86_64
rpm -ivh --force --nodeps pdksh-5.2.14-30.x86_64.rpm
rmp -ivh /backup/compat-libstdc-33-3.2.3-72-e17-x86_64.rpm
su - oracle
cd $ORACLE_HOME
cd sysman/lib
cp ins_emagent.mk ins_emagent.mk.bak
vi ins_emagent.mk
/NMECTL
$(MK_EMAGENT_NMECTL) -lnnz11
:wq!
su - oracle
netca
lsnrctl status
lsnrctl start
su - root
netstat -an|grep 1521
1)下载补丁及补丁opatch工具
2）上传补丁相关文件到服务器解压
3）停止所有的数据库服务
4）生产环境有已数据的，备份数据库的安装代码，一定要RMAN物理备份数据库。
5）安装opatch工具并打补丁
6）检查补丁情况
7）启动数据库服务
8）测试应用
mv DATABASE\ PATCH\ SET\ UPDATE\ 11.2.0.4.161018/ psu
cd psu
unzip p24006111_112040_Linux-x86-64.zip
unzip p6880880_112000_Linux-x86-64.zip
su - oracle
lsnrctl stop
emctl stop dbconsole
sqlplus "/as sysdba"
shutdown immediate;
ps -ef|grep oracle
ps -ef|grep ora_
cd $ORACLE_HOME/
ls
cd OPatch
./opatch version
cd ..
mv OPatch OPatch_old
cp -r /backup/psu/OPatch .
cd OPatch
./opatch version
vi ~/.bash_profile
/oracle/app/oracle/product/11.2.0/db_1/OPatch
:wq!
source ~/.bash_profile
cd /backup/
cd psu
ls
cd 24006111/
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
y
y
y
Oracle Home= '/oracle/app/oracle/product/11.2.0/db_1'
y
有告警没关系，没错误就好。
./opatch version
opatch lsinventory
cd $ORACLE_HOME/rdbms/admin
sqlplus "/as sysdba"
startup
粘贴一脚本运行
select action_time,action,comments from registry$history;
col COMMENTS format a50
/
最后编译一下对象。//SQL>
shutdown immediate;
lsnrctl start
cmctl start dbconsole 如果没用，不要开
--9、Oracle11gR2 for Linux数据库启动停止
主机重启后，需要手工启动数据库
lsnrctl start
sqlplus "/as sysdba"
startup;
sqlplus "/as sysdba"
shutdown immediate;
lsnrctl stop
reboot
10、Oracle11gR2基础维护与EM监控管理
--1）180天密码过期
select * from dba_profiles where profile='DEFAULT';
alter profile default limit PASSWORD_LIFE_TIME unlimited;
select * from dba_profiles where profile='DEFAULT';
--2)关闭审计
show parameter audit;
alter system set audit_trail=none scope=spfile;
改完重启才生效
--3）参数
show parameter sga;
show parameter pga;
show parameter proc //job 1000 processes 2000
show parameter open
alter system set open_cursors=2000 scope=spfile;
--4）警告日志
show parameter dump
user_dump_list /oracle/app/oracle/diag/rdbms/fghsdb/fghsdb/trace
cd /oracle/app/oracle/diag/rdbms/fghsdb/fghsdb/trace
ls
alert_fghsdb.log
more alert_fghsdb.log
/ORA- //一般有问题都是ORA开头的。
--5）EM管理控制台的使用
--6)归档日志的设置
cd /oradata
ls
fghsdb
sqlplus "/as sysdba"
archive log list;
show parameter recovery
alter system set db_recovery_file_dest_size=100g;
alter system set db_recovery_file_dest='/archive';
show parameter log
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter database open;
alter system switch logfile;
cd /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/archive/
ls
FGHSDB
cd FGHSDB
ls
archivelog
cd archivelog/
ls
2019_02_28
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;
--7)关闭所有的数据库服务，备份一次安装代码
su - root
emctl status dbconsole
lsnrctl stop
emctl stop dbconsole
cd /backup
tar zcvf oracle.tar.gz /oracle
