oracle 18c静默安装

2022-07-08

database, linux

阅读次数: 1000000

Oracle 18c其实就是12.2.0.2，19c就是12.2.0.3。db_home.zip 安装包大概4.25G，解压后有8.9G，数据库软件安装完成后有9.4G，创建完数据库后有13G。注：安装18c时需要把压缩文件解压到ORACLE_HOME路径下，在此目录下运行./runInstaller

检查oracle 18c所需依赖包

rpm -q binutils compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libXi make sysstat

yum安装依赖包

yum install -y binutils compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libXi make sysstat

创建所需操作系统用户组和用户

创建前先检查selinux和防火墙是否打开
创建用户和组：

groupadd oinstall
groupadd dba
groupadd oper
groupadd backupdba
groupadd dgdba
groupadd kmdba
useradd -g oinstall -G dba,backupdba,dgdba,kmdba,oper oracle
passwd oracle

配置系统参数文件

根据官方文档将参数整合成一个脚本命令，直接复制下方命令执行即可：

DAYTIME=$(date +%Y%m%d)
mv /etc/sysctl.conf /etc/sysctl.conf.${DAYTIME}
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
##shmmal's Calculation formula: physical memory 8G：(8*1024*1024*1024)/4096=2097152
##shmmax's Calculation formula: physical memory 8G：(8/2)*1024*1024*1024 -1=4294967295
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
EOF

sysctl -p

配置系统资源限制

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
EOF

配置 pam.d

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

配置 /dev/shm

cp /etc/fstab /tmp/fstab
memTotal=$(grep MemTotal /proc/meminfo | awk '{print $2}')
shmTotal=$(df -k /dev/shm | awk '{print $2}' | head -n 2 | tail -n 1)
if [ "$(grep -E -c "/dev/shm" /etc/fstab)" -eq 0 ]; then
	cat <<EOF >>/etc/fstab
tmpfs /dev/shm tmpfs size=${memTotal}k 0 0
EOF
	mount -o remount /dev/shm
else
  if [ "$shmTotal" -lt "$memTotal" ]; then
    shmTotal=$memTotal
    line=$(grep -n "/dev/shm" /etc/fstab | awk -F ":" '{print $1}')
    sed -i "${line} d" /etc/fstab
    cat <<EOF >>/etc/fstab
tmpfs /dev/shm tmpfs size=${memTotal}k 0 0
EOF
	fi
	mount -o remount /dev/shm
fi

配置用户环境变量

配置 root 用户

cat <<EOF >>/root/.bash_profile
alias so='su - oracle'
export PS1="[\`whoami\`@\`hostname\`:"'\$PWD]# '
EOF

配置 oracle 用户

cat <<EOF >>/home/oracle/.bash_profile
umask 022
export TMP=/tmp
export TMPDIR=\$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u2/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/18.0.0/dbhome_1
export ORACLE_HOSTNAME=oracle-back
export ORACLE_TERM=xterm
export TNS_ADMIN=\$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=topprdb
export PATH=/usr/sbin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$ORACLE_HOME/OPatch:\$PATH
alias sas='sqlplus / as sysdba'
export PS1="[\`whoami\`@\`hostname\`:"'\$PWD]\$ '
EOF

创建相关文件目录

创建$ORACLE_HOME目录：
mkdir -p /u2/oracle/product/18.0.0/dbhome_1
创建相关目录：
mkdir /u2/archivelog
mkdir /u2/fast_recovery_area
mkdir /u2/oradata
mkdir /u2/oraInventory
mkdir -p /opt/oracle/oraInventory/logs/

解压oracle安装包

unzip LINUX.X64_180000_db_home.zip -d /u2/oracle/product/18.0.0/dbhome_1/
chown -R oinstall.oracle /u2
chmod -R 775 /u2

静默安装oracle软件

切换到oracle用户：
su - oracle

直接命令行安装：

$ORACLE_HOME/runInstaller -silent  -force -noconfig  -ignorePrereq \
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0 \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u2/oraInventory \
ORACLE_BASE=/u2/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSOPER_GROUP=oper \
oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
oracle.install.db.OSDGDBA_GROUP=dgdba \
oracle.install.db.OSKMDBA_GROUP=kmdba \
oracle.install.db.OSRACDBA_GROUP=dba \
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

使用root用户执行
/u2/oraInventory/orainstRoot.sh
/u2/oracle/product/18.0.0/dbhome_1/root.sh

静默创建监听

cd $ORACLE_HOME/bin
./netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp

静默创建实例

创建临时变量：

## 实例名称
ORACLE_SID=topprdb
## 数据文件目录
ORADATADIR=/u2/oradata
## 归档日志目录
ARCHIVEDIR=/u2/archivelog
## 数据库字符集
CHARACTERSET=AL32UTF8
## 国家字符集
NCHARACTERSET=AL16UTF16
## 数据库内存这里取物理内存的 50%
memTotal=$(grep MemTotal /proc/meminfo | awk '{print $2}')
totalMemory=$((memTotal / 2048))
## 是否为 CDB 模式，TRUE 或者 FALSE
ISCDB=TRUE

静默创建数据库实例：

dbca -silent -createDatabase \
-ignorePrereqFailure \
-templateName General_Purpose.dbc \
-responseFile NO_VALUE \
-gdbName ${ORACLE_SID} \
-sid ${ORACLE_SID} \
-sysPassword oracle \
-systemPassword oracle \
-redoLogFileSize 1024 \
-storageType FS \
-databaseConfigType SINGLE \
-datafileDestination ${ORADATADIR} \
-enableArchive true \
-archiveLogDest ${ARCHIVEDIR} \
-characterset ${CHARACTERSET} \
-nationalCharacterSet ${NCHARACTERSET} \
-emConfiguration NONE \
-automaticMemoryManagement false \
-totalMemory ${totalMemory} \
-databaseType OLTP \
-createAsContainerDatabase ${ISCDB}

数据库主从同步（同样适合其他版本）

主库：topprdr IP:192.168.1.208
备库：topprdb IP:192.168.1.209
db_name统一为topprdr,unique_name为各自的SID

开启主库force logging和归档

进入sqlplus：
检查归档模式是否开启
archive log list;
检查force logging是否开启
SELECT LOG_MODE,FLASHBACK_on,FORCE_LOGGING FROM V$DATABASE;
开启force logging
alter database force logging;
开启归档步骤（开启归档需要关闭数据库）
停监听
lsnrctl status
lsnrctl stop
关闭数据库
sqlplus / as sysdba
shutdown immediate;
主库启动到Mount状态
startup mount;
开启归档
alter database archivelog;
打开主库
alter database open;
测试切换归档，确保归档正常
alter system switch logfile;

配置主库参数

直接使用命令设置

alter system set db_unique_name=topprdr scope=both;

alter system set log_archive_config='dg_config=(topprdr,topprdb)' scope=both;

alter system set log_archive_dest_1='location=/u2/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=topprdr';

alter system set log_archive_dest_state_2='DEFER' scope=both sid='*';

alter system set log_archive_dest_2='service=topprdb LGWR ASYNC NOAFFIRM COMPRESSION=ENABLE delay=0 reopen=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=topprdb';

alter system set log_archive_max_processes = 8 scope=both;

alter system set remote_login_passwordfile='exclusive' scope=spfile  sid='*';

alter system set fal_server=topprdb;

alter system set standby_file_management=auto scope=both;

alter system set db_file_name_convert='/u2/oradata/TOPPRDB/','/u2/oradata/TOPPRDR';

alter system set log_file_name_convert='/u2/oradata/TOPPRDB/','/u2/oradata/TOPPRDR';

alter system set "_ktb_debug_flags" = 8 scope=both;

参数说明：

db_unique_name 用于区分主库与备库
log_archive_config 用于控制发送归档日志到远程位置、接收远程归档日志，dg_config不分顺序
log_archive_dest_1 用于指定本地归档存放路径、valid_for指定传输的内容，使用all_logfiles,all_roles统一存放standby log,不要设置standby_archive_dest
log_archive_dest_2 用于指定归档推送目的地，LGWR ASYNC NOAFFIRM表示使用最大性能模式，即使DG库异常也不会影响主库运行
log_archive_dest_state_2 用于控制归档推送状态，先设置为defer（不推送）
log_archive_max_processes 用于指定归档进程个数
remote_login_passwordfile 用于限制管理员远程登录，默认为NONE，需要修改为EXCLUSIVE或SHARED，需要重启数据库生效
fal_server FAL指获取归档日志(Fetch Archived Log)
standby_file_management 用于控制主库增删文件时，是否自动在备库做出相应的修改
db_file_name_convert 即使路径一致也建议设置，主备库参数顺序相反，主库参数顺序为’备,‘主’；备库参数顺序为’主’,‘备’，参数内容为绝对路径或者磁盘组名，如果是asm只需要磁盘组名，OMF会使用db_unique_name自动生成文件名。
log_file_name_convert 即使路径一致也建议设置，主备库参数顺序相反，主库参数顺序为’备,‘主’；备库参数顺序为’主’,‘备’，参数内容为绝对路径或者磁盘组名，如果是asm只需要磁盘组名，OMF会使用db_unique_name自动生成文件名。
_ktb_debug_flags 用于规避ADG切换导致索引坏块的问题

配置主库standby redolog

Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数，大小和redo log一致即可
检查日志组
select GROUP#,THREAD#,MEMBERS,BYTES/1024/1024 MB,STATUS from v$log;
select GROUP#,MEMBER,STATUS,TYPE from v$logfile;
创建standby redolog
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u2/oradata/TOPPRDR/redo04' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u2/oradata/TOPPRDR/redo05' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u2/oradata/TOPPRDR/redo06' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u2/oradata/TOPPRDR/redo07' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u2/oradata/TOPPRDR/redo08' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/u2/oradata/TOPPRDR/redo09' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '/u2/oradata/TOPPRDR/redo10' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u2/oradata/TOPPRDR/redo11' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u2/oradata/TOPPRDR/redo12' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u2/oradata/TOPPRDR/redo13' size 1G;
检查日志组
select GROUP#,THREAD#,MEMBERS,BYTES/1024/1024 MB,STATUS from v$log;
select GROUP#,MEMBER,STATUS,TYPE from v$logfile;

复制主库密码文件到ADG库

——主库ADG库均使用文件系统———

复制主库其中一个节点密码文件到备库，并按DG库实例名修改文件名
scp $ORACLE_HOME/dbs/orapwtopprdr 192.168.1.209:/u2/oracle/product/18.0.0/dbhome_1/dbs/orapwtopprdb
若没有密码文件，则用orapwd命令在主库新建，再拷贝到备库
orapwd file=orapwtopprdr password=oracle entries=10 format=12

———-主库、ADG库均使用ASM—————————

如果oracle使用ASM，则需要用：

#主库
#确认密码文件位置
su - oracle
srvctl config database -d topprdr
Password file: +DATA/topprdr/PASSWORD/pwdtopprdr.256.1071437689
#从ASM复制密码文件到文件系统
su - grid
asmcmd
pwget --dbuniquename topprdr
pwcopy +DATA/topprdr/PASSWORD/pwdtopprdr.256.1071437689 /tmp/orapwtopprdb
#从主库文件系统拷贝密码文件到ADG库文件系统
su - oracle
scp /tmp/orapwxstopprdr 192.168.1.209:/tmp/

#ADG库
#导入主库密码文件
orapwd input_file='/tmp/orapwtopprdr' file='+DATA/topprdr/PASSWORD/pwdtopprdb'  dbuniquename='topprdb'

#异常处理，若出现OPW-00010: Could not create the password file. This resource has a Password File.错误
#先设置ADG库集群密码位置为空
srvctl modify database -db topprdb -pwfile ''
#再次导入密码文件
orapwd input_file='/tmp/orapwtopprdb' file='+DATA/topprdr/PASSWORD/pwdtopprdb'  dbuniquename='topprdb'
# 修改ADG库OCR中记录的数据库密码文件信息
srvctl modify database -db topprdb -pwfile '+DATA/topprdr/PASSWORD/pwdtopprdb'

——主库使用ASM，ADG库使用文件系统——-

#主库
#确认密码文件位置
su - oracle
srvctl config database -d topprdr
Password file: +DATA/topprdr/PASSWORD/pwdtopprdr.256.1071437689
#从ASM复制密码文件到文件系统
su - grid
asmcmd
pwget --dbuniquename topprdr
pwcopy +DATA/topprdr/PASSWORD/pwdtopprdr.256.1071437689 /tmp/orapwtopprdb
#从主库文件系统拷贝密码文件到ADG库文件系统
su - oracle
scp /tmp/orapwtopprdb 192.168.1.209:/tmp/

#ADG库
#导入主库密码文件
orapwd input_file='/tmp/orapwtopprdb' file='/u01/app/oracle/product/18.0.0/db_1/dbs/orapwtopprdb' SYS=Y

ADG库使用pfile启动到nomount

从主库生成pfile文件
create pfile='/home/oracle/pfile_dg.ora' from spfile;
参考主库参数，创建ADG库pfile文件

#需要修改的参数
audit_file_dest
control_files
db_create_file_dest
db_file_name_convert
log_file_name_convert
db_unique_name
fal_server
local_listener
log_archive_dest_*
log_archive_dest_state_*
remote_listener
standby_archive_dest

修改后的内容

*._ktb_debug_flags=8
*.audit_file_dest='/u2/oracle/admin/topprdb/adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_files='/u2/oradata/TOPPRDB/control01.ctl','/u2/oradata/TOPPRDB/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u2/oradata/TOPPRDR/','/u2/oradata/TOPPRDB'
*.db_name='topprdr'
*.db_unique_name='TOPPRDB'
*.diagnostic_dest='/u2/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=topprdbXDB)'
*.enable_pluggable_database=true
*.fal_server='TOPPRDR'
*.local_listener='LISTENER_TOPPRDB'
*.log_archive_config='dg_config=(topprdr,topprdb)'
*.log_archive_dest_1='location=/u2/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=topprdb'
*.log_archive_dest_2='service=topprdr LGWR ASYNC NOAFFIRM COMPRESSION=ENABLE delay=0 reopen=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=topprdr'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=8
*.log_file_name_convert='/u2/oradata/TOPPRDR/','/u2/oradata/TOPPRDB'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=779m
*.processes=480
*.remote_login_passwordfile='exclusive'
*.sga_target=3113m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

备库使用pfile启动到nomount
sqlplus / as sysdba
startup nomount pfile='/home/oracle/pfile_dg.ora';

启动备库监听

vim /u2/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
加入静态注册信息

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = topprdb)
      (ORACLE_HOME = /u2/oracle/product/18.0.0/dbhome_1)
      (SID_NAME = topprdb)
    )
  )

重启备库监听
lsnrctl stop
lsnrctl start

配置主库tnsnames.ora文件，增加备库tns信息，用于与主库通讯

TOPPRDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.209)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topprdb)
    )
  )

配置备库tnsnames.ora文件，增加主库tns信息，用于与备库通讯

TOPPRDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.208)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topprdr)
    )
  )

主库与备库相互验证tns
rman target sys/oracle@topprdb
rman target sys/oracle@topprdr

创建备库spfile文件
sqlplus / as sysdba
create spfile from pfile='/home/oracle/pfile_dg.ora';

重启ADG库
shutdown immediate
exit

sqlplus / as sysdba
startup nomount

在备库通过RMAN复制主库
连接主库与ADG库
rman target sys/oracle@topprdr auxiliary sys/oracle@topprdb
执行复制
duplicate target database for standby from active database

主库开启投递归档
alter system set log_archive_dest_state_2='ENABLE' scope=both sid='*';

开启备库
sqlplus / as sysdba
alter database open;

备库开启日志应用
alter database recover managed standby database parallel 8 using current logfile disconnect from session;

检查日志传输情况，主库与备库分别执行，对比结果
select thread#,max(sequence#) from v$archived_log group by thread# order by 1;

备库启停维护

取消ADG同步
停止备库日志应用，在备库执行
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
停止主库投递归档到备库，在主库执行
alter system set log_archive_dest_state_2=defer scope=both;

开启备库日志应用
alter database recover managed standby database parallel 8 using current logfile disconnect from session;

修改备库为read only模式实现简单主备读写分离

alter database open read only;
select database_role,open_mode from v$database;
可以看到DATABASE_ROLE为PHYSICAL STANDBY，OPEN_MODE为READ ONLY WITH APPLY

从主库创建pdb后，主库可以使用pdb的service id来远程登陆，用户名也是创建pdb的用户名，而备库同样可以使用pdb名来连接，但用户名和密码都是orapwtopprdb通过密码文件来认证，所以远程连接备库读取数据库时，用户名密码是填主库具有远程连接权限的用户名和密码。

学而时习之实践验证真理