Oracle 19c RAC集群搭建

Posted on Edited on In Views: Symbols count in article: 16k Reading time ≈ 15 mins.
基于RHEL7.6搭建Oracle19c RAC 集群数据库以及基础配置

系统规划

项目环境：虚拟机

主机：3台

  • 2台运行RAC集群
  • 1台共享存储

CPU和内存资源：

RAC集群：4U/8G

共享存储：2U/4G

网络规划

Public IP (内部访问)

1
2
192.168.2.63 rac1
192.168.2.64 rac2

Private IP（节点心跳网络）

1
2
10.10.10.63 rac1-priv
10.10.10.64 rac2-priv

Virtual IP（提供客户端访问 漂移）

1
2
192.168.2.65 rac1-vip
192.168.2.66 rac2-vip

SCAN IP（客户端访问 均衡）

1
192.168.2.67
节点 主机版本 主机名 实例名 Oracle版本 public ip private ip virtual ip scan ip
1 rhel7.6 rac1 mesdb1 19.3 192.168.2.63 10.10.10.63 192.168.2.65 192.168.2.67
2 rhel7.6 rac2 mesdb2 19.3 192.168.2.64 10.10.10.64 192.168.2.66 192.168.2.67

存储规划

系统：

  • /:50g

  • /oracle:100g

  • /backup:200g

共享存储

  • dggrid1 :1g*3

  • dggrid2 :1g*3

  • dgsystem:8g*1

  • dgrecovery:3g*1

  • dgdata01:2g*1

生产数据库空间要求：1T数据

  • dggrid1 :2g*3

  • dggrid2 :2g*3

  • dgsystem:200g*1

  • dgrecovery:1T= 200g*5

  • dgdata01:1T= 200g*5

  • 备份：/backup 3T= 1T *3

系统配置

hosts配置

rac1&rac2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
cat <<EOF>>/etc/hosts
#Public IP
192.168.2.63 	rac1
192.168.2.64 	rac2

#Private IP
10.10.10.63 rac1-priv
10.10.10.64 rac2-priv

#Vip IP
192.168.2.65 rac1-vip
192.168.2.66 rac2-vip

#Scan IP
192.168.2.67 rac-scan
EOF

存储配置

rac1&rac2

服务器主机的存储:lvm

1
2
3
4
5
6
7
pvcreate /dev/sdb /dev/sdc

vgcreate oraclevg /dev/sdb
vgcreate backupvg /dev/sdc

lvcreate -n oraclelv -L 100000M oraclevg
lvcreate -n backuplv -L 200000M backupvg

格式化：

1
2
mkfs.xfs /dev/backupvg/backuplv
mkfs.xfs /dev/oraclevg/oraclelv

挂载：

1
2
3
4
5
6
7
8
9
vi /etc/fstab
/dev/backupvg/backuplv	/backup		xfs		defaults	0 0
/dev/oraclevg/oraclelv	/oracle		xfs		defaults	0 0

mkdir /oracle
mkdir /backup

mount /oracle
mount /backup

ASM磁盘

1
2
3
4
5
6
7
8
9
10
11
##iscsi识别共享存储
yum install -y iscsi-initiator-utils*

systemctl enable iscsi
systemctl start iscsi

##输出targetname，10.10.10.68为iscsi共享存储设备IP地址
iscsiadm -m discovery -t st -p 10.10.10.68
##连接共享存储
iscsiadm -m node -T iqn.1991-05.com.microsoft:win-arg9liue0mv-mesdb-target -p 10.10.10.68 -l
lsblk

todo 多路径配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
for i in d e f g h i j k l;
do  
echo "KERNEL==\"sd?\",SUBSYSTEM==\"block\", PROGRAM==\"/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\",RESULT==\"`/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", SYMLINK+=\"asm-disk$i\",OWNER=\"grid\", GROUP=\"asmadmin\",MODE=\"0660\""
done  



KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc8646bc8413b34f9a", SYMLINK+="asm-dggrid01",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc9617e1089ab64fb4", SYMLINK+="asm-dggrid02",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adcb020780584975001", SYMLINK+="asm-dggrid03",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adcba8050aa37c7be56", SYMLINK+="asm-dggrid04",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc9952fc11f7dfd9fb", SYMLINK+="asm-dggrid05",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc99341d2d641e1b3c", SYMLINK+="asm-dggrid06",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc889634115ea78a36", SYMLINK+="asm-dgsystem01",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adc8ed98c025a591e68", SYMLINK+="asm-dgrecovery01",OWNER="grid", GROUP="asmadmin",MODE="0660"
KERNEL=="sd?",SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name",RESULT=="360003ff44dc75adca5b7eca16123d815", SYMLINK+="asm-dgdata01",OWNER="grid", GROUP="asmadmin",MODE="0660"


vi /etc/udev/rules.d/99-oracle-asmdevices.rules
##重载udev
udevadm control --reload-rules
udevadm trigger --type=devices

ls -lsa /dev/asm*

image-20220110141222202

配置yum源

rac1&rac2

1
2
3
## 挂载镜像
mount /dev/cdrom /mnt
df -Th

配置yum源

1
2
3
4
5
6
7
8
9
cat <<EOF>>/etc/yum.repos.d/local.repo
[local]
name=local
baseurl=file:///mnt
gpgcheck=0
enabled=1
EOF

yum repolist all

安装依赖包：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
yum groupinstall -y "Server with GUI"
yum install -y bc \
binutils \
compat-libcap1 \
compat-libstdc++-33 \
gcc \
gcc-c++ \
elfutils-libelf \
elfutils-libelf-devel \
glibc \
glibc-devel \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
libxcb \
libX11 \
libXau \
libXi \
libXtst \
libXrender \
libXrender-devel \
make \
net-tools \
nfs-utils \
smartmontools \
sysstat \
e2fsprogs \
e2fsprogs-libs \
fontconfig-devel \
expect \
unzip \
openssh-clients \
readline* \
tigervnc* \
psmisc --skip-broken

手动上传安装缺失依赖包：

1
2
3
mkdir /soft
cd /soft
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm

检查依赖包安装

1
rpm -q bc binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ elfutils-libelf elfutils-libelf-devel glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libxcb libX11 libXau libXi libXtst libXrender libXrender-devel make net-tools nfs-utils smartmontools sysstat e2fsprogs e2fsprogs-libs fontconfig-devel expect unzip openssh-clients readline | grep "not installed"

防火墙配置

rac1&rac2

关闭防火墙

1
2
3
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service

selinux 配置

rac1&rac2

1
2
3
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
getenforce

时间同步配置

rac1&rac2

禁用chronyd

1
2
3
4
yum install -y chrony
timedatectl set-timezone Asia/Shanghai
systemctl stop chronyd.service
systemctl disable chronyd.service

使用ntp同步时间

1
2
3
4
5
6
7
cat <<EOF>>/var/spool/cron/root
00 12 * * * /usr/sbin/ntpdate -u ntp1.aliyun.com && /usr/sbin/hwclock -w
EOF
##查看计划任务
crontab -l
##手动执行
/usr/sbin/ntpdate -u ntp1.aliyun.com && /usr/sbin/hwclock -w

关闭透明大页和numa

rac1&rac2

1
2
sed -i 's/quiet/quiet transparent_hugepage=never numa=off/' /etc/default/grub
grub2-mkconfig -o /boot/grub2/grub.cfg

重启后检查是否生效

1
2
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /proc/cmdline

avahi-daemon 配置

rac1&rac2

1
2
3
4
5
6
yum install -y avahi*
systemctl stop avahi-daemon.socket
systemctl stop avahi-daemon.service
pgrep -f avahi-daemon | awk '{print "kill -9 "$2}'
systemctl disable avahi-daemon.socket
systemctl disable avahi-daemon.service

NOZEROCONF配置

rac1&rac2

1
2
3
cat <<EOF>>/etc/sysconfig/network
NOZEROCONF=yes
EOF

系统资源限制

rac1&rac2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
cat <<EOF>>/etc/security/limits.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft nproc 2047
oracle hard nproc 16384
oracle hard memlock 134217728
oracle soft memlock 134217728

grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
grid soft nproc 2047
grid hard nproc 16384
EOF

配置pam.d/login：

1
2
3
4
cat <<EOF>>/etc/pam.d/login
session required pam_limits.so 
session required /lib64/security/pam_limits.so
EOF

系统参数配置

rac1&rac2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
memTotal=$(grep MemTotal /proc/meminfo | awk '{print $2}')
totalMemory=$((memTotal / 2048))
shmall=$((memTotal / 4))
if [ $shmall -lt 2097152 ]; then
	shmall=2097152
fi
shmmax=$((memTotal * 1024 - 1))
if [ "$shmmax" -lt 4294967295 ]; then
	shmmax=4294967295
fi
cat <<EOF>>/etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = $shmall
kernel.shmmax = $shmmax
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 = 1048576
net.ipv4.conf.ens224.rp_filter = 1
net.ipv4.conf.ens192.rp_filter = 2
EOF

生效：

1
sysctl -p

用户、组、目录创建

rac1&rac2

用户组

1
2
3
4
5
6
7
8
9
10
/usr/sbin/groupadd -g 54321 oinstall
/usr/sbin/groupadd -g 54322 dba
/usr/sbin/groupadd -g 54323 oper
/usr/sbin/groupadd -g 54324 backupdba
/usr/sbin/groupadd -g 54325 dgdba
/usr/sbin/groupadd -g 54326 kmdba
/usr/sbin/groupadd -g 54327 asmdba
/usr/sbin/groupadd -g 54328 asmoper
/usr/sbin/groupadd -g 54329 asmadmin
/usr/sbin/groupadd -g 54330 racdba

用户

1
2
3
4
5
6
7
8
9
10
11
12
/usr/sbin/useradd -u 11012 -g oinstall -G asmadmin,asmdba,asmoper,dba,racdba,oper grid
/usr/sbin/useradd -u 54321 -g oinstall -G asmdba,dba,backupdba,dgdba,kmdba,racdba,oper oracle
##修改用户密码为oracle
echo "oracle" |passwd oracle --stdin
echo "oracle" |passwd grid --stdin
##查看用户组
id grid
id oracle
##重载udev
udevadm control --reload-rules
udevadm trigger --type-devices
ll /dev/asm*

目录

1
2
3
4
5
6
7
8
9
10
11
12
13
mkdir -p /oracle/app/19.3.0/grid
mkdir -p /oracle/app/grid
mkdir -p /oracle/app/oracle/product/19.3.0/db
mkdir -p /oracle/app/oraInventory
mkdir -p /home/oracle/scripts
chown -R oracle:oinstall /backup
chown -R oracle:oinstall /home/oracle/scripts
chown -R grid:oinstall /oracle
chown -R grid:oinstall /oracle/app/grid
chown -R grid:oinstall /oracle/app/19.3.0/grid
chown -R grid:oinstall /oracle/app/oraInventory
chown -R oracle:oinstall /oracle/app/oracle
chmod -R 775 /oracle

环境变量配置

rac1&rac2

grid用户：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
cat <<EOF>>/home/grid/.bash_profile
################OracleBegin#########################
umask 022
export TMP=/tmp
export TMPDIR=\$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/oracle/app/grid
export ORACLE_HOME=/oracle/app/19.3.0/grid
export ORACLE_TERM=xterm
export TNS_ADMIN=\$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=+ASM2
export PATH=/usr/sbin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$ORACLE_HOME/OPatch:\$PATH
alias sas='sqlplus / as sysasm'
export PS1="[\`whoami\`@\`hostname\`:"'\$PWD]\$ ' 
EOF

注意： 每个节点的 ORACLE_SID 不一样（+ASM1/+ASM2），需要自行修改

oracle用户：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
cat <<EOF>>/home/oracle/.bash_profile
################OracleBegin#########################
umask 022
export TMP=/tmp
export TMPDIR=\$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.3.0/db
export ORACLE_HOSTNAME=rac2
export ORACLE_TERM=xterm
export TNS_ADMIN=\$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=mesdb2
export PATH=/usr/sbin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$ORACLE_HOME/OPatch:\$PATH
alias sas='sqlplus / as sysdba'
export PS1="[\`whoami\`@\`hostname\`:"'\$PWD]\$ '
################OracleEnd#########################
EOF

注意： 每个节点的 ORACLE_HOSTNAME（rac1/rac2）和 ORACLE_SID（mesdb1/mesdb2）不一样，需要自行修改

1
2
## 重启系统
shutdown -r now

安装包上传解压

1
2
3
4
5
6
7
##创建安装介质存放目录
mkdir /soft
##上传安装介质到/soft目录
LINUX.X64_193000_db_home.zip
LINUX.X64_193000_grid_home.zip
p32545008_190000_Linux-x86-64.zip
p6880880_190000_Linux-x86-64.zip

19C 的安装包需要解压到对应的 ORACLE_HOME 目录下

静默解压grid安装包

1
2
chown -R grid:oinstall /soft
su - grid -c "unzip -q /soft/LINUX.X64_193000_grid_home.zip -d /oracle/app/19.3.0/grid/"

静默解压oracle安装包

1
2
chown -R oracle:oinstall /soft
su - oracle -c "unzip -q /soft/LINUX.X64_193000_db_home.zip -d /oracle/app/oracle/product/19.3.0/db/"

静默解压补丁安装包

1
2
3
4
5
6
7
8
9
10
cd /soft

##解压OPatch补丁包
chown -R grid:oinstall /soft
su - grid -c "unzip -q -o /soft/p6880880_190000_Linux-x86-64.zip -d /oracle/app/19.3.0/grid"
##解压RU补丁包
su - grid -c "unzip -q /soft/p32545008_190000_Linux-x86-64.zip -d /soft"

chown -R oracle:oinstall /soft
su - oracle -c "unzip -q -o /soft/p6880880_190000_Linux-x86-64.zip -d /oracle/app/oracle/product/19.3.0/db"

注意： 由于19C支持安装grid软件前打RU补丁，因此提前解压OPatch和RU补丁，为安装做准备

cvuqdisk安装

root用户下，cvuqdisk安装（rac01&rac02):

1
2
3
4
5
rpm -ivh /oracle/app/19.3.0/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm 
##传输到节点二安装

scp /oracle/app/19.3.0/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm rac2:/tmp
rpm -ivh /tmp/cvuqdisk-1.0.10-1.rpm

注意： 19C 版本的 cvu 包换位置了，目录为：$ORACLE_HOME/cv/rpm/，以上所有软件只需要在节点一上传解压即可

安装Grid软件

配置grid用户vnc

1
2
3
4
yum install -y tigervnc*
chown -R grid:oinstall /soft
su - grid
vncserver

开始安装：

1
2
3
4
5
6
##应用环境变量
source ~/.bash_profile
##进入安装目录
cd $ORACLE_HOME
##执行安装程序开始安装，通过-applyRU参数指向补丁解压位置，提前安装grid补丁
./gridSetup.sh -applyRU /soft/32545008

image-20220110145154657

打完补丁，进入安装界面，选择集群安装

image-20220110145247453

选择standlone模式

image-20220110145325890

修改scan名称与hosts文件配置一致

image-20220110145429347

添加rac2节点，配置互信

image-20220110145805853

网卡配置

image-20220110150250058

存储类型，选择ASM

image-20220110150520054

GIMR，这里不选择安装：

image-20220110150553342

ASM磁盘组

image-20220110150816058

image-20220110150759154

ASM密码 mesasm2022

image-20220110150920096

默认，下一步

image-20220110151006818

EM管理，暂不开启

image-20220110151033309

管理组，默认

image-20220110151056236

安装地址，默认

image-20220110151115523

image-20220110151129756

不自动执行脚本

image-20220110151215234

安装前检查

image-20220110151243543

image-20220110152429085

忽略，开始安装

image-20220110152505759

image-20220110152517367

image-20220110152542962

执行root脚本

image-20220110152952149

先在节点1执行

1
2
/oracle/app/oraInventory/orainstRoot.sh
/oracle/app/19.3.0/grid/root.sh

image-20220110154201184

image-20220110154722451

执行完成后，继续安装

忽略

image-20220110154915575

安装完成

image-20220110154946582

检查集群状态

1
2
su - grid
crsctl stat res -t

image-20220110160312452

检查grid补丁

1
2
3
su - grid
opatch lspatches
sqlplus -version

image-20220110160414752

创建磁盘组

1
asmca

image-20220110155103889

image-20220110155557309

检查asm磁盘：

1
2
3
4
asmcmd lsdg

## 重启检查集群状态
reboot

image-20220110160532100

安装Oracle软件

1
2
3
4
5
6
7
8
chown -R oracle:oinstall /soft
su - oracle
vncserver

##应用环境变量
source ~/.bash_profile
cd $ORACLE_HOME
./runInstaller -applyRU /soft/32545008/

image-20220110171230005

进入安装界面，选择仅安装软件

image-20220110171312175

选择集群模式：

image-20220110171332064

配置互信

image-20220110171428128

image-20220110171419257

安装企业版

image-20220110171459738

安装位置，默认

image-20220110171525099

用户组 默认

image-20220110171546241

不配置自动执行脚本

image-20220110171930293

安装前检查

image-20220110172130358

只配置了一个SCAN,关于DNS相关的都忽略

image-20220110172448494

image-20220110172946918

开始安装

image-20220110173710353

执行root脚本

1
/oracle/app/oracle/product/19.3.0/db/root.sh

image-20220110173804416

image-20220110173827746

检查补丁版本

1
2
opatch lspatches
sqlplus -version

image-20220110173912817

创建数据库

1
dbca

选择创建数据库实例

image-20220110180252118

自定义模式

image-20220110180553410

自定义数据库

image-20220110180630914

选择节点

image-20220110180642777

PDB&CDB

image-20220110180720005

存储配置

image-20220110181334789

暂不开启归档闪回

image-20220111090704484

生产环境无特殊要求全部安装

image-20220111090911968

配置项：

内存：SGA&PGA

image-20220111091136127

进程数：1500

image-20220111091201951

字符集：16GBK

image-20220111091226787

EM管理器，暂不开启

image-20220111091648137

sys/system密码：mesdb2022

image-20220111091717329

创建选项，默认

image-20220111091941917

预检查

image-20220111092033716

可忽略

image-20220111092121232

开始创建

image-20220111092147664

创建完成

image-20220111105256812

检查：

1
2
3
4
5
# 查看集群状态
crsctl stat res -t  

sqlplus / as sysdba
select instance_name,status from gv$instance;

image-20220111105817916

基础维护

RAC停止

1
2
3
4
5
6
su - oracle
sqlplus "/as sysdba"
shutdown immediate;

su - grid
crsctl stop crs

RAC启动

1
2
3
4
5
6
7
su - grid
crsctl start crs
crsctl status res -t

-- crsctl disable crs

alter pluggable database mespdb open;

自启动触发器

1
2
3
4
5
6
CREATE TRIGGER open_all_pdbs
	ALTER STARTUP ON DATABASE
BEGIN
	EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbsl;
/

查看资源状态

grid:

1
[grid@rac2:/home/grid]$ crsctl stat res -t

oracle:

1
srvctl status database -db mesdb

image-20220111133031684

PDB&CDB

1
2
3
4
5
6
7
8
9
10
11
12
## 查看当前容器
show con_name;

## 查看所有PDB
show pdbs

## 切换pdb
alter session set container = mespdb;

## 切换根容器
conn /as sysdba

image-20220111133445262

1
2
3
4
5
6
7
8
9
## 创建表空间
create tablespace mes datafile '+dgdata01' size 20m;

## 查看数据文件
select name from v$datafile;

create user mesadmin identified by mes2022 default tablespace mes;

grant dba to mesadmin;

image-20220111135231663

创建测试数据

1
2
3
4
5
6
create table EMPLOYEE (id number(12) primary key,name varchar(20));
insert into EMPLOYEE values(1,'Oracle');
insert into EMPLOYEE values(2,'EAP');
commit;

select * from EMPLOYEE

image-20220111135523550

image-20220111135537825

节点2：

image-20220111135703283

OCR镜像

1
2
ocrconfig -add +DGGRID2
ocrcheck

image-20220111143132715

密码过期策略

后续配置在容器数据库下修改

关闭密码 180 天过期限制

1
2
3
conn /as sysdba
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
alter profile default limit password_life_time unlimited;

image-20220111145953420

关闭审计

1
2
show parameter audit;
alter system set audit_trail=none scope = spfile;

归档

1
archive log list;

image-20220111151040444

1
2
3
show parameter recover;
alter system set db_recovery_file_dest_size=10g; ## 略小于存储 留出冗余空间
alter system set db_recovery_file_dest='+dgrecovery';

image-20220111151357258

1
2
3
4
5
6
7
8
9
10
11
alter system set cluster_database=false scope=spfile;
shutdown immediate; ## 关闭两个节点

## 启动rac1到mount状态
startup mount;
## 开启归档
alter database archivelog;
alter system set cluster_database=true scope=spfile;
shutdown immediate;
## 2节点启动数据库
startup;

检查归档状态，切换日志

1
2
archive log list;
alter system switch logfile;

image-20220111153451555

闪回

1
2
3
4
5
## 生产环境不开启闪回
alter database flashback on;

## 关闭
alter database flashback off;

其他参数

1
2
3
4
5
6
7
show parameter db_files;
alter system set db_files=8191 scope=spfile;

show parameter processes;

show parameter open_cursors;
alter system set open_cursors=2000 scope=spfile;

image-20220111150512989

集群测试

hosts:

1
192.168.2.67 rac-scan

tnsnames.ora:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mesdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
    )
  )

mespdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mespdb)
    )
  )

image-20220111173611145

1
2
3
sqlplus /nolog
conn system/mesdb2022@mesdb
select instance_name from v$instance;

4个窗口分别是 mesdb2、mesdb1、mesdb2、mesdb1

image-20220111174008051

1
crsctl stop crs # 关闭

image-20220111180802477

OLTP

session:基于会话，如果用户连接丢失,将创建一个新的会话连接到备用节点,用户的所有未提交的操作必须回滚,然后再次执行,SELECT操作也被中止

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mesdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
      )
    )
  )

image-20220112093541671

OLAP

建议使用

select:如果用户连接丢失,将使用游标和之前的快照继续执行SELECT操作,叫做SQL接管,SQL接管能够无缝接管理的事务类型只有SELECT语句.其它的操作也必须要回滚然后再次执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mesdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
      )
    )
  )

image-20220112093721196

参考资料：Oracle RAC Failover 详解

Related Posts