备注:工作需要开始学习oracle,之前只学习过mysql的一些基础东东,oracle对我而言是一个新的数据体系,作为初学者,很多地方可能写的不对,只是个人理解,误导之处,请谅解,求指正![Byrd:2016年7月3日]
环境基础:
虚拟机IP(LINUX):1.1.1.212
本地IP(WIN):1.1.1.1
变量配置:
[root@oracle01 ~]# uname -a
Linux oracle01.db.hangzhou.t4x.org 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@oracle01 ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@oracle01 ~]# mkdir /byrd/{tools,service} -p
[root@oracle01 tools]# grep "keepcache=1" /etc/yum.conf
keepcache=1
[root@oracle01 tools]# echo "127.0.0.1 $(uname -n)" >>/etc/hosts
[root@oracle01 tools]# grep oracle01 /etc/hosts
127.0.0.1 oracle01.db.hangzhou.t4x.org
[root@oracle01 tools]# yum install gcc gcc-c++ wget vim openssl openssl-devel lrzsz tree unzip -y
[root@oracle01 tools]# rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers kernel-headers ksh libaio libaio-devel libgcc libgomp libstdc++ libstdc++-devel make numactl-devel sysstat unixODBC unixODBC-devel
[root@oracle01 tools]# yum install numactl-devel sysstat unixODBC unixODBC-devel -y
[root@oracle01 tools]# rpm -q binutils compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libXi make sysstat
[root@oracle01 tools]# yum install compat-libstdc++ libaio-devel libXext libXtst libX11 libXau libXi -y
[root@oracle01 tools]# yum install ksh nfs-utils compat-libcap1 compat-libstdc++-33 -y #最后检测时候这几个没有安装
[root@oracle01 tools]# tree /byrd/
/byrd/
├── service
└── tools
├── linuxamd64_12102_database_1of2.zip
├── linuxamd64_12102_database_2of2.zip
├── linuxamd64_12102_grid_1of2.zip
└── linuxamd64_12102_grid_2of2.zip
[root@oracle01 tools]# free -m
total used free shared buffers cached
Mem: 2872 2743 128 0 7 2540
-/+ buffers/cache: 195 2676
Swap: 991 0 991
[root@oracle01 tools]# mount -t tmpfs shmfs -o size=10g /dev/shm
[root@oracle01 tools]# df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
shmfs 10G 0 10G 0% /dev/shm
[root@oracle01 tools]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
29G 5.7G 22G 22% /
tmpfs 10G 0 10G 0% /dev/shm
/dev/sda1 485M 32M 429M 7% /boot
shmfs 10G 0 10G 0% /dev/shm
[root@oracle01 tools]# groupadd oinstall
[root@oracle01 tools]# groupadd -g 502 dba
[root@oracle01 tools]# groupadd -g 503 oper
[root@oracle01 tools]# groupadd -g 504 asmadmin
[root@oracle01 tools]# groupadd -g 505 asmoper
[root@oracle01 tools]# groupadd -g 506 asmdba
[root@oracle01 tools]# useradd -u 502 -g oinstall -G dba,oper,asmdba oracle
[root@oracle01 tools]# useradd -u 503 -g oinstall -G asmadmin,asmdba,asmoper,dba grid
[root@oracle01 tools]# tail /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 10240
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
[root@oracle01 tools]# tail -13 /etc/sysctl.conf
kernel.shmall = 4294967296
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
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
[root@oracle01 tools]# sysctl -p
[root@oracle01 tools]# mkdir /data/app/oracle -p
[root@oracle01 tools]# mkdir /data/app/grid
[root@oracle01 tools]# chown -R grid:oinstall /data
[root@oracle01 tools]# chown -R oracle:oinstall /data/app/oracle
[root@oracle01 tools]# chmod -R 755 /data
[root@oracle01 tools]# tail -4 /home/grid/.bash_profile
export PATH
export ORACLE_BASE=/data/app/grid
export ORACLE_SID=+ASM
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/grid
[root@oracle01 tools]# tail -4 /home/oracle/.bash_profile
export PATH
export ORACLE_BASE=/data/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
ASM配置:
[root@oracle01 tools]# rpm -ivh oracleasm-support-2.1.8-1.el6.x86_64.rpm
warning: oracleasm-support-2.1.8-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [100%]
[root@oracle01 tools]# rpm -ivh oracleasmlib-2.0.4-1.el6.x86_64.rpm
warning: oracleasmlib-2.0.4-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
oracleasm >= 1.0.4 is needed by oracleasmlib-2.0.4-1.el6.x86_64
[root@oracle01 tools]# yum install oracleasmlib*
[root@oracle01 tools]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [FAILED]
[root@oracle01 tools]# find / -name oracleasm.ko
/lib/modules/2.6.32-642.el6.x86_64/extra/oracleasm/oracleasm.ko
/lib/modules/2.6.32-642.1.1.el6.x86_64/weak-updates/oracleasm/oracleasm.ko
[root@oracle01 tools]# reboot
Last login: Fri Jul 1 23:24:29 2016 from 1.1.1.1
[root@oracle01 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [grid]:
Default group to own the driver interface [asmadmin]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@oracle01 ~]# fdisk /dev/sdf
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk: [FAILED]
[root@oracle01 ~]# setenforce 0
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK2 /dev/sdc1
Marking disk "DISK2" as an ASM disk: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK3 /dev/sdd1
Marking disk "DISK3" as an ASM disk: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK4 /dev/sde1
Marking disk "DISK4" as an ASM disk: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm createdisk DISK5 /dev/sdf1
Marking disk "DISK5" as an ASM disk: [ OK ]
[root@oracle01 ~]# /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
grid安装:
[root@oracle01 ~]# chown grid /byrd/tools/linuxamd64_12102_grid_*
[root@oracle01 ~]# chown grid /byrd/tools/
[root@oracle01 ~]# su - grid
[grid@oracle01 ~]$ cd /byrd/tools/
[grid@oracle01 tools]$ unzip linuxamd64_12102_grid_1of2.zip
[grid@oracle01 tools]$ unzip linuxamd64_12102_grid_2of2.zip
[grid@oracle01 ~]$ export DISPLAY=1.1.1.1:0.0
[grid@oracle01 tools]$ /byrd/tools/grid/runInstaller
#####################安装一个Xmanager Enterprise 5--Xmanager - Passive#####################
#####################安装过程请看下面的图片#####################
[grid@oracle01 tools]$ cat /data/app/oraInventory/ContentsXML/inventory.xml #查看安装信息
ASM:配置
[root@oracle01 ~]# tail -1 /home/grid/.bash_profile
export PATH=$ORACLE_HOME/bin:$PATH
[grid@oracle01 ~]$ . .bash_profile
[grid@oracle01 ~]$ echo $PATH
/data/app/grid/product/12.1.0/grid/bin:/data/app/grid/product/12.1.0/grid/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/grid/bin:/home/grid/bin
[grid@oracle01 ~]$ export DISPLAY=1.1.1.1:0.0
[grid@oracle01 ~]$ xhost +
access control disabled, clients can connect from any host
[grid@oracle01 tools]$ asmca
#####################安装过程请看下面的图片#####################
1:建立asm恢复组
2:配置Fast Recovery Area
oRACLE安装:
[root@oracle01 tools]# chown oracle.oinstall linuxamd64_12102_database_*
[root@oracle01 tools]# chown oracle linuxamd64_12102_database_*
[root@oracle01 tools]# chown oracle ../tools/
[root@oracle01 tools]# su - oracle
[oracle@oracle01 ~]$ cd /byrd/tools/
[oracle@oracle01 tools]$ unzip linuxamd64_12102_database_1of2.zip
[oracle@oracle01 tools]$ unzip linuxamd64_12102_database_2of2.zip
[oracle@oracle01 tools]$ /byrd/tools/database/runInstaller
>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-07-02_10-29-26AM. Please wait ...[oracle@oracle01 tools]$ You can find the log of this install session at:
/data/app/oraInventory/logs/installActions2016-07-02_10-29-26AM.log
#####################安装过程请看下面的图片#####################
建立oracle数据库:
[root@oracle01 tools]# tail -1 /home/oracle/.bash_profile
export PATH=$ORACLE_HOME/bin:$PATH
[oracle@oracle01 tools]$ . ~/.bash_profile
[oracle@oracle01 tools]$ export DISPLAY=1.1.1.1:0.0
[oracle@oracle01 tools]$ dbca
#####################安装过程请看下面的图片#####################
查看状态:
[grid@oracle01 ~]$ crs_stat -t -v
CRS-275: This command is not supported in Oracle Restart environment.
[grid@oracle01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE oracle01 STABLE
ora.FRA.dg
ONLINE ONLINE oracle01 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE oracle01 STABLE
ora.asm
ONLINE INTERMEDIATE oracle01 CHECK TIMED OUT,STAB
LE
ora.ons
OFFLINE OFFLINE oracle01 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE oracle01 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE oracle01 STABLE
ora.orcl.db
1 ONLINE ONLINE oracle01 Open,STABLE
--------------------------------------------------------------------------------
[oracle@oracle01 tools]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 2 14:07:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
内核配置建议:
kernel.shmmax
关于内核参数kernel.shmmax,oracle 建议,kernel.shmmax的值不能少于物理内存的一半,而且要大于Oracle中sga-max-size的值,否则会造成oracle性能下降
一般32bit操作系统,直接设置为系统支持的最大内存即可,64bit操作系统设置大于sga-max-size的值即可
如:当前内存为2G 则kernel.shmmax = 2*1024*1024=2097152
当前内存为8G则 kernel.shmmax = 7*1024*1024=7340032
Kernel.shmall
Shmall指系统一次可以使用的共享内存段的最大数量,以页为单位。Oracle默认设置为 kernel.shmall = 2097152
即最大8G(2097152*4/1024/1024),(在调整SGA时需要注意,SGA大小设置不可超过该值),根据系统内存大小和使用的不同可以参考如下:设置的一般规律
kernel.shmall = 8G/4k=8388608k/4k=2097152 ---内存8G
kernel.shmall = 16G/4k=16777216k/4k=4194304 ---内存16G
kernel.shmall = 32G/4k=33554432k/4k=8388608 ---内存32G
(RedHat linux系统中页大小为4096即4K,实际环境以getconf PAGE_SIZE结果为准)
kernel.shmmni
shmmni 指系统共享内存段的最大数量
oracle设置默认值为4096,一般是足够用了,不需要调整
文件句柄数的相关内核参数设置
fs.file-max
fs.file-max指系统能够打开最大的文件句柄数
oracle建议设置为65536,一般不用修改
信号的相关内核参数设置
kernel.sem
kernel.sem是指 semmsl,semmns,semopm,semmni这4个参数
semmsl 指每个线号集的最大信号数,Oracle建议是设置为oracle的最大进程数+10
semmni 指整个系统的信号集的最大数量
semmns 指整个系统的信号总数,也就是semmni*semmsl的结果
semopm 指每个semop系统调用可以执行的信号操作的最大数量
oracle默认设置
semmsl=250
semmns=3200
semopm=100
semmni=128
即kernel.sem= 250 3200 100 128
例:在oracle ora.init文件参数中设置PROCESSES参数为5000
则semmsl=5010,semmni=128,semmns=semmsl* semmni=641280
另外semopm建议设置等于semmsl值即5010
那么kernel.sem=5010 641280 5010 128
网络相关的内核参数设置
net.core.rmem_default
net.core.rmem_default 指网络套接字的默认接收缓冲区的大小,oracle建议设置为265K即262144
net.core.rmem_max
net.core.rmem_max 指网络套接字的最大接收缓冲区的大小,oracle10g及以前版本建议设置为256k即262144
oracle11g建议设置为4M 即4194304
net.core.wmem_default
net.core.wmem_default指网络套接字的默认发送缓冲区的大小,oracle建议设置为265K即262144
net.core.wmem_max
net.core.wmem_max 指网络套接字的最大发送缓冲区的大小,oracle10g及以前版本建议设置为256k即262144
oracle11g建议设置为1M即1048576
net.ipv4.ip_local_port_range
net.ipv4.ip_local_port_range ,指本地的允许打开随机端口范围
oracle10g前建议端口范围为1024 65000,oracle11g建议端口范围为:
9000 65500 并忽略oracle安装程序任何关于这个参数的警告
Asynchronous I/O相关的内核参数设置
fs.aio-max-nr
fs.aio-max-nr 指系统允许的最大的异步IO请求大小
oracle默认设置为1M即1048576,一般不用更改