记一次el8 Oracle 21c 单机adg搭建过程

一. Orace21c数据库安装配置（el8单机环境）

挂载光驱：
mount /dev/sr0 /mnt

安装vncserver
yum install tigervnc-server
若yum失败则重装yum工具
rpm -qa|grep yum
rpm -qa|grep yum|xargs rpm -e --nodeps
rpm -ivh --nodeps yum-utils-4.0.21-3.el8.noarch.rpm
rpm -ivh --nodeps yum-4.7.0-4.el8.noarch.rpm
注：yum包到https://mirrors.aliyun.com/centos/8/BaseOS/x86_64/os/Packages下载对应系统的包
cd /etc/yum.repos.d
yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
vi redhat.repo
配置：
[BaseOS]
name=BaseOS
baseurl=file:mnt/BaseOS
enabled=1
gpgcheck=0
[AppStream]
name=AppStream
baseurl=file:mnt/AppStream
enabled=1
gpgcheck=0
[epel]
name=EPEL
baseurl=https://mirrors.aliyun.com/epel/8/Everything/x86_64/
gpgcheck=0

sed -i ‘s|^#baseurl=https://download.fedoraproject.org/pub|baserurl=#baseurl=https://mirrors.aliyun.com/|’ /etc/yum.repos.d/epel*
sed -i ‘s|^#metailink|#metalink|’ /etc/yum.repos.d /epel*

yum -y install libnsl
yum -y install ksh
yum -y install make
yum -y install sysstat
yum -y install glibc-devel

关闭防火墙：
systemctl status firewalld.service

使用以下命令确定服务器上的物理RAM大小至少1G以上
grep MemTotal /proc/meminfo

确定配置的交换空间的大小
grep SwapTotal /proc/meminfo

确定tmp目录空间足够1G
df -h /tmp

关闭selinux：
#编辑/etc/selinux/config 文件
vi /etc/selinux/config
SELINUX=disabled

安装数据库依赖包：
yum install bc binutils compat-libcap1 compat-libstdc++33 elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc+±devel libxcb make smartmontools sysstat kmod*

创建oracle用户用户组并设置密码：
groupadd -g 1001 oinstall
groupadd -g 1002 dba
useradd -u 1001 -g oinstall -G dba oracle
passwd oracle

内核参数配置：
vi /etc/pam.d/login
session required pam_limits.so

vi /etc/sysctl.conf
kernel.shmall = 536871
kernel.shmmax = 2147483648
fs.aio-max-nr = 1048576
fs.file-max = 6815744
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

vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

配置系统环境变量：
vi /etc/profile
if [ /$USER="oracle"];thenif[/$SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

配置oracle环境变量：
su - oracle
vi .bash_profile
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/21.3.0/db_1
export ORACLE_SID=xxx
export PATH=$ORACL{E}_{H}OME/bin:$ORACLE_HOME/OPatch:$PATH

创建数据库安装目录并赋权：
mkdir -p /fengrp/oracle/app/product/21.3.0/db_1
mkdir -p /fengrp/oracle/oraInventory
chmod -R 775 /fengrp/oracle
chown -R oracle:oinstall /fengrp/oracle/app
chown -R oracle:oinstall /fengrp/oracle/oraInventory

配置hosts：
vi /etc/hosts
192.168.74.152（ip） adg152（主机名）

dd if=/dev/zero of=/myswapfile bs=1M count=8192
df -h /dev/shm
df -h /tmp
mkswap /myswapfile
vi /etc/fstab
swapon -s

安装数据库：
su - oracle
cd /fengrp/oracle/app/product/21.3.0/db_1
unzip LINUX.X64_213000_db_home.zip
exit
export DISPLAY=:0
xhost +
su - oracle
export DISPLAY=:0
xhost +
正常返回信息为：
access control disabled,clients can connect from any host

若xhost执行失败则下载安装以下安装包：
libXxf86misc-devel-1.0.4-1.el8.x86_64.rpm
xorg-x11-server-utils-debugsource-7.7-27.el8.x86_64.rpm
mcpp-debugsource-2.7.2-20.el8.x86_64.rpm

cd /fengrp/oracle/app/product/21.3.0/db_1
./runInstaller

若安装过程中报错：[INS-10013]和[INS-35990]
Root用户下运行：
cd /fengrp/oracle/app/product/21.3.0/db_1
./root.sh
cd /fengrp/oracle/oraInventory/
./orainstRoot.sh
然后回到安装页面retry

二.adg环境配置

环境规划：
角色 ip hostname cdb name db_unique pdb name 数据库版本
主 192.168.74.152 adg152 orcl orcl_p orclpdb 21.3.0.0.0
备 192.168.74.153 adg153 orcl orcl_s orclpdb 21.3.0.0.0

（1） 配置/etc/hosts:
主：
vi /etc/hosts
#添加以下内容
192.168.74.152 adg152
192.168.74.153 adg153

备：
vi /etc/hosts
#添加以下内容
192.168.74.153 adg153
192.168.74.152 adg152

（2） 配置/etc/hostname:
主：
vi /etc/hostname
#添加以下内容
adg152

备：
vi /etc/hostname
#添加以下内容
adg53

（3） 配置/etc/sysconfig/network：
主：
vi /etc/sysconfig/network
#添加以下内容
NETWORKING=yes
HOSTNAME=adg152

备：
vi /etc/sysconfig/network
#添加以下内容
NETWORKING=yes
HOSTNAME=adg153

（4） 主库开启force logging：
alter database force logging;

（5） 主备库添加standby redo logfile（确保有对应目录且Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数，假如只有一个节点，这个节点有三组redolog，所以Standby redo log组数>=(3+1)*1== 4所以至少需要创建4组Standby redo log）：
alter database add standby logfile group 4 ‘/fengrp/oracle/app/oradata/ORCL/standby_redo04.log’ size 200m;
alter database add standby logfile group 5 ‘/fengrp/oracle/app/oradata/ORCL/standby_redo05.log’ size 200m;
alter database add standby logfile group 6 ‘/fengrp/oracle/app/oradata/ORCL/standby_redo06.log’ size 200m;
alter database add standby logfile group 7 ‘/fengrp/oracle/app/oradata/ORCL/standby_redo07.log’ size 200m;
select group#,member from v$logfile order by group#;

（6） 主备开启归档
Archive log list
shutdown immediate
alter database archivelog;
alter database open;

（7） 配置监听：
主：
tnsnames.ora文件配置：
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)

ORCL_P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCL_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

listener.ora文件配置：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
(ORACLE_HOME = /fengrp/oracle/app/product/21.3.0/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /fengrp/oracle/app

备：
tnsnames.ora文件配置：
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)

ORCL_P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCL_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

listener.ora文件配置：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
(ORACLE_HOME = /fengrp/oracle/app/product/21.3.0/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg153)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

注：监听配置完成后测试是否连通,主备都需要
lsnrctl reload
tnsping orcl_p
tnsping orcl_s
sqlplus sys/123456@orcl_p as sysdba
sqlplus sys/123456@orcl_s as sysdba

（8） 配置pfile文件：
主：
sqlplus / as sysdba
create pfile=‘/fengrp/oracle/app/product/21.3.0/db_1/dbs/orclpfile.ora’ from spfile;
quit
vi orclpfile.ora
#配置如下：
*.audit_file_dest=‘/fengrp/oracle/app/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘21.0.0’
*.control_files=‘/fengrp/oracle/app/oradata/ORCL/control01.ctl’,‘/fengrp/oracle/app/oradata/ORCL/control02.ctl’
*.db_block_size=8192
*.db_name=‘orcl’
*.db_unique_name=‘orcl_p’
*.log_archive_config=‘dg_config=(orcl_p,orcl_s)’
*.diagnostic_dest=‘/fengrp/oracle/app’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.enable_pluggable_database=true
*.local_listener=‘LISTENER_ORCL’
*.log_archive_dest_1=‘location=/fengrp/oracle/app/homes/OraDB21Home1/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl_p’
*.log_archive_dest_2=‘service=orcl_s valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orcl_s’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=‘MANUAL’
*.fal_server=‘orcl_s’
*.memory_target=1512m
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’
*.log_file_name_convert=‘/fengrp/oracle/app/oradata/ORCL/’,‘/fengrp/oracle/app/oradata/ORCL/’
*.db_file_name_convert=‘ORCL’,‘ORCL’

sqlplus / as sysdba
shutdown immediate
create spfile from pfile=‘/fengrp/oracle/app/product/21.3.0/db_1/dbs/orclpfile.ora’;
startup
拷贝文件到备库：
scp orapworcl oracle@adg153:$ORACLE_HOME/dbs

备：
vi orclpfile.ora
#配置如下：
*.audit_file_dest=‘/fengrp/oracle/app/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘21.0.0’
*.control_files=‘/fengrp/oracle/app/oradata/ORCL/control01.ctl’,‘/fengrp/oracle/app/oradata/ORCL/control02.ctl’
*.db_block_size=8192
*.db_name=‘orcl’
*.db_unique_name=‘orcl_s’
*.log_archive_config=‘dg_config=(orcl_p,orcl_s)’
*.diagnostic_dest=‘/fengrp/oracle/app’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.enable_pluggable_database=true
*.local_listener=‘LISTENER_ORCL’
*.log_file_name_convert=‘/fengrp/oracle/app/oradata/ORCL/’,‘/fengrp/oracle/app/oradata/ORCL/’
*.db_file_name_convert=‘ORCL’,‘ORCL’
*.log_archive_dest_1=‘location=/fengrp/oracle/app/homes/OraDB21Home1/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl_s’
*.log_archive_dest_2=‘service=orcl_s valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orcl_p’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=‘MANUAL’
*.fal_server=‘orcl_s’
*.memory_target=1512m
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’
*.db_recovery_file_dest=‘/fengrp/oracle/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=31457280

sqlplus / as sysdba
shutdown immediate
create spfile from pfile=‘/fengrp/oracle/app/product/21.3.0/db_1/dbs/orclpfile.ora’;
startup nomount

（9） 进行Active duplicate:
主：
rman target sys/123456@orcl_p auxiliary sys/123456@orcl_s nocatalog
duplicate target database for standby from active database nofilenamecheck;

（10） 备库打开数据库和实时同步：
alter database open;
alter pluggable database orclpdb open;
alter database recover managed standby database disconnect;
alter database set standby database to maximize availability;

（11） 主库开启pdb：
alter pluggable database orclpdb open;

（12） 验证是否同步：主库创建用户并赋权，在备库查看是否有该用户

三.可能遇到的问题

(1)adg备库alter database open时alter日志报错且不同步：
rrors in file /fengrp/oracle/app/diag/rdbms/orcl_s/orcl/trace/orcl_mz00_3749.trc:
ORA-00313: open failed for members of log group 5 of thread 0
ORA-00312: online log 5 thread 0: ‘/fengrp/oracle/app/homes/OraDB21Home1/dbs/broken4’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
解决办法
源库执行：
select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS

---

     1         15 YES INACTIVE
     2         16 NO  CURRENT
     3          0 YES INACTIVE

alter database clear logfile group 1;
alter database clear logfile group 3;

select group#,status from v$standby_log;
GROUP# STATUS

---

     4 UNASSIGNED
     5 UNASSIGNED
     6 UNASSIGNED
     7 UNASSIGNED

alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;

备库执行：
select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS

---

     1         15 YES INACTIVE
     2         16 NO  CURRENT
     3          0 YES INACTIVE

alter database clear logfile group 1;
alter database clear logfile group 3;

select group#,status from v$standby_log;
GROUP# STATUS

---

     4 UNASSIGNED
     5 UNASSIGNED
     6 UNASSIGNED
     7 UNASSIGNED

alter database recover managed standby database cancel;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database recover managed standby database disconnect; —实时同步（alter database recover managed standby database using current logfile disconnect from session; —切日志后同步）
alter database set standby database to maximize availability;

(2)adg启动与关闭：
关闭：先关闭主库再关闭备库
启动：先启动备库再启动主库（如下）
备库：
sqlplus / as sysdba
startup nomount
alter database mount standby database;
alter database open read only;
quit
lsnrctl start

主库：
sqplus / as sysdba
startup
quit
lsnrctl start

备库：
alter database recover managed standby database disconnect;
alter database set standby database to maximize availability;
alter pluggable database orclpdb open;

主库：
alter pluggable database orclpdb open;

(3)启动监听报错：TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
原因：listener.ora配置格式不正确，括号没有对齐

(4)El8重启网络：
nmcli c reload
nmcli c up ens160

(5)/dev/shm空间不够加大空间
umount -l /dev/shm
mount -t tmpfs shmfs -o size=20g /dev/shm
vi /etc/fstab
shmfs /dev/shm tmpfs size=36g 0 0

(6)取消standby日志应用
alter database recover managed standby database cancel;

(7)安装数据库执行报错./runInstaller：
/fengrp/oracle/app/product/21.3.0/db_1/perl/bin/perl: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory
解决办法：
yum install libnsl

(8)执行./runInstaller报错：ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
解决办法：
vncserver --查看当前使用桌面序号
New ‘localhost.localdomain:7 (oracle)’ desktop is localhost.localdomain:7
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/localhost.localdomain:7.log
如上桌面序号为7则：
export DISPLAY=:7.0
若是用远程工具连接则：
export DISPLAY=远程工具所在主机ip:7.0
再执行：./runInstaller

(9)若执行vncserver报错：bash: vncserver: command not found…
解决办法：yum install tigervnc-server

站在巨人的肩膀上：（参考了以下文章）
https://blog.csdn.net/qq_24442273/article/details/120553771
https://www.cnblogs.com/sandata/p/15234008.html