Centos7.9+Oracle11.2.0.4数据库ADG主备环境搭建
1.实现目标
两台X86数据库服务器实现oracle11g的主备adg环境,避免单点故障,满足数据库基本容灾备份需求。其中操作系统版本要求centos7.9,数据库版本要求oracle11.2.0.4+最新psu201020版本。
2.环境准备
主库 | 备库 | |
主机名 | prebilla | prebillb |
本机ip | 10.1.1.51 | 10.1.1.52 |
adg服务IP | 10.1.1.53 | |
数据库名称db_name | prebill | |
数据库db_unique_name | prebilla | prebillb |
数据库字符集 | ZHS16GBK | |
国家字符集 | AL16UTF16 | |
root密码 | welcome1 | |
oracle密码 | welcome1 | |
sys\system密码 | SYS_Passw0rd | |
操作系统 | centos7.9 | |
数据库版本 | oracle11.2.0.4 | |
数据库补丁版本 | 11.2.0.4.201020 | |
opatch补丁工具版本 | 11.2.0.3.29 |
以下为其中一台信息:
[root@prebillb keepalived]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#10.1.1.52 prebillb –注意此处注释本机IP和主机名是为了保证adg监听主机名时可以监听服务IP10.1.1.53
10.1.1.51 prebilla –此处填写另外一台主机名和IP是为了dg broker里边识别对方主机
主库/etc/hosts:
备库/etc/hosts:
[root@prebillb keepalived]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@prebillb keepalived]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 189G 0 189G 0% /dev
tmpfs 189G 0 189G 0% /dev/shm
tmpfs 189G 27M 189G 1% /run
tmpfs 189G 0 189G 0% /sys/fs/cgroup
/dev/mapper/centos-root 100G 1.9G 99G 2% /
/dev/sda1 1014M 153M 862M 16% /boot
/dev/mapper/centos-home 1.6T 130G 1.4T 9% /home—用于安装oracle数据库
/dev/loop0 4.4G 4.4G 0 100% /mnt/iso—挂接好iso光盘用于yum安装包
tmpfs 38G 0 38G 0% /run/user/0
[root@prebillb keepalived]# lsblk --系统安装的目录结构参考如下
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 1.6T 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 1.6T 0 part
├─centos-root 253:0 0 100G 0 lvm /
├─centos-swap 253:1 0 16G 0 lvm [SWAP]
└─centos-home 253:2 0 1.5T 0 lvm /home
loop0 7:0 0 4.4G 0 loop /mnt/iso
[root@prebillb keepalived]# vgs
VG #PV #LV #SN Attr VSize VFree
centos 1 3 0 wz--n- 1.63t 0
[root@prebillb keepalived]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
home centos -wi-ao---- 1.52t
root centos -wi-ao---- 100.00g
swap centos -wi-ao---- 16.00g
[root@prebillb keepalived]# pvs
PV VG Fmt Attr PSize PFree
/dev/sda2 centos lvm2 a-- 1.63t 0
[root@prebillb keepalived]# free -g
total used free shared buff/cache available
Mem: 377 111 136 0 129 264
Swap: 15 0 15
[root@prebillb keepalived]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 40
On-line CPU(s) list: 0-39
Thread(s) per core: 2
Core(s) per socket: 10
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 79
Model name: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
Stepping: 1
CPU MHz: 2199.731
CPU max MHz: 2200.0000
CPU min MHz: 1200.0000
BogoMIPS: 4389.97
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 25600K
NUMA node0 CPU(s): 0-9,20-29
NUMA node1 CPU(s): 10-19,30-39
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch epb cat_l3 cdp_l3 invpcid_single intel_ppin intel_pt ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm rdt_a rdseed adx smap xsaveopt cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm arat pln pts spec_ctrl intel_stibp
[root@prebillb keepalived]# sar 1 3
Linux 3.10.0-1160.el7.x86_64 (prebillb) 07/29/2021 _x86_64_ (40 CPU)
09:45:30 AM CPU %user %nice %system %iowait %steal %idle
09:45:31 AM all 0.08 0.00 0.13 0.00 0.00 99.80
09:45:32 AM all 0.00 0.00 0.03 0.00 0.00 99.97
09:45:33 AM all 0.00 0.00 0.00 0.00 0.00 100.00
Average: all 0.03 0.00 0.05 0.00 0.00 99.92
[root@prebillb keepalived]#
3.实现步骤
3.1环境配置
两台修改基本一致
3.1.1网络配置
nmtui文字图交互式操作很方便。
命令修改主机名:hostnamectl set-hostname prebilla
hostnamectl --static
3.1.2系统参数
[root@prebilla keepalived]# cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 400000000000 #此处比物理内存小一点即可
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 51200 #此处设置内存大页,比sga大一些,且比物理内存小,算法为51200*2/1024=100G,意味着设置SGA的时候小于100G即可,一般可设置80G-100G
sysctl -p生效
3.1.3关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld
3.1.4关闭SELINUX
vi /etc/selinux/config
SELINUX=disabled
临时关闭:setenforce 0
3.1.5时间同步
手动同步时间:ntpdate xxx.xxx.xxx.xxx
检查时间和时区是否正确:
date
timedatectl status
timedatectl set-timezone "Asia/Shanghai"
timedatectl set-time '2015-11-20 16:10:40'
timedatectl set-time '2015-11-20 16:10:40'
启用ntp:
(也可考虑使用chronyc替换ntp 参考:
https://blog.csdn.net/jycjyc/article/details/109717197
)
1) yum install ntp -y
2) systemctl start ntpd
3) systemctl enable ntpd
4) vi /etc/ntp.conf
#注释下面4行
#server 0.centos.pool.ntp.org iburst
#server 1.centos.pool.ntp.org iburst
#server 2.centos.pool.ntp.org iburst
#server 3.centos.pool.ntp.org iburst
替换成实际时间服务器:
server xxx.xx.xxx.xxx
5) vi /etc/sysconfig/ntpd
# Command line options for ntpd
SYNC_HWCLOCK=yes
OPTIONS="-x"
6) systemctl restart ntpd
7) systemctl status ntpd
8) ntpq -p
查看ntp服务是否能够被同步,显示为“LOCAL”,表示成功。如果没有任何显示,客户端将无法同步。
3.1.6配置本地yum源
挂接ISO:
mdkir -p /mnt/iso
mount -t iso9660 -o loop /CentOS-7.iso /mnt/iso
[root@prebilla /]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 189G 0 189G 0% /dev
tmpfs 189G 0 189G 0% /dev/shm
tmpfs 189G 19M 189G 1% /run
tmpfs 189G 0 189G 0% /sys/fs/cgroup
/dev/mapper/centos-root 100G 2.0G 99G 2% /
/dev/sda1 1014M 153M 862M 16% /boot
/dev/mapper/centos-home 1.6T 131G 1.4T 9% /home
/dev/loop0 4.4G 4.4G 0 100% /mnt/iso
tmpfs 38G 0 38G 0% /run/user/0
[root@prebilla /]# cat /etc/yum.repos.d/local.repo
[local]
name=local
baseurl=file:///mnt/iso
enabled=1
gpgcheck=0
3.1.7安装软件包
yum -y install binutils compat-libstdc++-33 gcc gcc-c++ glibc glibc-common glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat openssh-clients compat-libcap1 xorg-x11-utils xorg-x11-xauth elfutils unixODBC unixODBC-devel libXp elfutils-libelf elfutils-libelf-devel smartmontools psmisc
3.1.8/etc/security/limits.conf添加内容
oracle soft nofile 10240
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock unlimited
oracle soft memlock unlimited
3.2用户创建
3.2.1建用户
方法1:
groupadd -g 1001 oinstall
groupadd -g 1003 dba
useradd -u 1001 -g oinstall -G dba oracle
passwd oracle设置oracle密码
方法2:
参考生产服务器的用户信息编辑修改:
cat /etc/passwd
cat /etc/group
完成后pwconv
修改目录属主chown -R oracle:oinstall /home/oracle
passwd oracle
3.2.2环境变量:
[oracle@prebilla ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=prebill
export ORACLE_UNQNAME=prebilla #备库为prebillb,此行可删除
export NLS_LANG=American_america.zhs16gbk
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
3.3 软件安装
两种方式:
- 从已有其它生产环境打包tar软件:此次使用的方法
[oracle@radiusn ~]$ pwd
/home/oracle
[oracle@radiusn ~]$ ll
total 3050528
drwxr-xr-x 3 oracle oinstall 28 Aug 4 2017 app
-rw-r--r-- 1 oracle oinstall 2863122012 Jun 1 14:17 app.tar.gz
[oracle@radiusn ~]$ du -sh app.tar.gz
2.7G app.tar.gz
tar -zcvf app.tar.gz app --exclude=app/oracle/diag --exclude=app/oracle/oradata --exclude=app/oracle/admin --exclude=app/oracle/cfgtoollogs
主备库创建相同目录路径,scp传输后cd /home/oracle
然后tar -zxvf app.tar.gz解压即可
准备两文件:
注意权限:
chmod 664 /etc/oratab
[oracle@prebilla ~]$ cat /etc/oraInst.loc
inventory_loc=/home/oracle/app/oracle/oraInventory
inst_group=oinstall
[oracle@prebillb admin]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
prebillb:/home/oracle/app/oracle/product/11.2.0/db_1:N #主库则为prebilla
2)新装:由于不能图形化直连,所以可使用静默安装方式
[oracle@prebilla ~]$ cat /etc/oraInst.loc
inventory_loc=/home/oracle/app/oracle/oraInventory
inst_group=oinstall
./runInstaller -silent -debug -force -noconfig -IgnoreSysPreReqs \
FROM_LOCATION=/home/soft/database/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/home/oracle/app/oracle/oraInventory \
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/db_1 \
ORACLE_HOME_NAME="Oracle11g" \
ORACLE_BASE=/home/oracle/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
DECLINE_SECURITY_UPDATES=true
3.4 数据库创建
3.4.1建库:
cd /home/oracle/app/oracle/product/11.2.0/db_1/assistants/dbca/templates
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname prebill -sid prebill -sysPassword SYS_Passw0rd -systemPassword SYS_Passw0rd -responseFile NO_VALUE -datafileDestination /home/oracle/app/oracle/oradata -redoLogFileSize 500 -recoveryAreaDestination NO_VALUE -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -memoryPercentage 20 -databaseType OLTP -emConfiguration NONE
3.4.2表空间按需求配置:
建表空间和加数据文件脚本参考:
create tablespace bill_ts datafile '/home/oracle/app/oracle/oradata/prebill/bill_ts01.dbf' size 10240M autoextend on next 10M;
alter tablespace bill_ts add datafile '/home/oracle/app/oracle/oradata/prebill/bill_ts02.dbf' size 10240M autoextend on next 10M;
create tablespace idr_ts datafile '/home/oracle/app/oracle/oradata/prebill/prebill01.dbf' size 10240M autoextend on next 10M;
alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill03.dbf' size 10240M autoextend on next 10M;
alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill04.dbf' size 10240M autoextend on next 10M;
alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill05.dbf' size 10240M autoextend on next 10M;
alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill06.dbf' size 10240M autoextend on next 10M;
create tablespace INDEX_TS datafile '/home/oracle/app/oracle/oradata/prebill/index_ts01.dbf' size 8192M autoextend on next 10M;
create tablespace IDRERROR_TS datafile '/home/oracle/app/oracle/oradata/prebill/idrerror_ts01.dbf' size 8192M autoextend on next 10M;
create tablespace STATIC_TS datafile '/home/oracle/app/oracle/oradata/prebill/static_ts01.dbf' size 1024M autoextend on next 10M;
create tablespace USER_TS datafile '/home/oracle/app/oracle/oradata/prebill/user_ts01.dbf' size 8192M autoextend on next 10M;
create tablespace INDX datafile '/home/oracle/app/oracle/oradata/prebill/indx_ts01.dbf' size 1024M autoextend on next 10M;
查看表空间占用脚本:
[oracle@prebilla ~]$ pwd
/home/oracle
[oracle@prebilla ~]$ cat tbs.sql
set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
3.4.3建用户
根据需求创建:
create user prebill identified by bisys3#$ default tablespace IDR_TS;
grant connect,resource,dba to prebill;
3.4.4日志组优化
alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/prebill/redo04.log') size 500M;
创建standby日志为adg使用,比日志组多一组,大小一致。
alter database add standby logfile thread 1 group 5 ('/home/oracle/app/oracle/oradata/prebill/standby_redo05.log') size 500M;
alter database add standby logfile thread 1 group 6 ('/home/oracle/app/oracle/oradata/prebill/standby_redo06.log') size 500M;
alter database add standby logfile thread 1 group 7 ('/home/oracle/app/oracle/oradata/prebill/standby_redo07.log') size 500M;
alter database add standby logfile thread 1 group 8 ('/home/oracle/app/oracle/oradata/prebill/standby_redo08.log') size 500M;
alter database add standby logfile thread 1 group 9 ('/home/oracle/app/oracle/oradata/prebill/standby_redo09.log') size 500M;
3.5 监听配置
3.5.1监听listener.ora内容:
方法1:修改监听:
[oracle@prebilla admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prebill)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = prebill)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prebilla)(PORT = 1521)) #备库为prebillb,使用主机名的好处是可以实现监听该主机上的所有IP:对服务IP实现自动监听,避免了数据库切换后应用需要修改数据库连接串的问题。
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
[oracle@prebilla admin]$lsnrctl start
[oracle@prebilla admin]$lsnrctl status
方法2:静默建监听:
netca -silent -responsefile /home/oracle/app/oracle/product/11.2.0/db_1/assistants/netca/netca.rsp
3.5.2sqlnet.ora设置参考:
[oracle@prebilla admin]$ more sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE
3.5.3tnsnames.ora配置参考:
PREBILL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.53)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=prebill)
)
)
PREBILLA=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.51)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=prebill)
)
)
PREBILLB=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.52)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=prebill)
)
)
3.6 参数优化调整
alter profile default limit failed_login_attempts unlimited;
alter profile default limit password_life_time unlimited;
alter system set audit_trail=none scope=spfile sid='*';
alter system set recyclebin=off scope=spfile sid='*';
alter system set sga_target=80G scope=spfile sid='*';--根据物理内存调整
alter system set pga_aggregate_target=20G sid='*';--根据物理内存调整
alter system set event='10949 trace name context forever, level 1:28401 trace name context forever, level 1:10795 trace name context forever, level 2' scope=spfile sid='*';
alter system set "_use_adaptive_log_file_sync"=false sid='*';
alter system set "_undo_autotune"=false scope=both sid='*';
alter system set db_unique_name='prebilla' scope=spfile sid='*';
alter system set db_files=1000 scope=spfile sid='*';
3.7 数据库打补丁
介质如下:
参考readme,基本步骤如下:(备库不更新数据字典3.7.4和3.7.6,因为主库更新后,再恢复出备库)
3.7.1替换opatch工具
opatch工具替换:
[oracle@prebilla ~]$ mv *.zip ./patch/
[oracle@prebilla ~]$ cd patch/
[oracle@prebilla patch]$ ll
total 551392
-rw------- 1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip
-rw------- 1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip
[oracle@prebilla patch]$ unzip 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip
Archive: 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip
creating: OPatch/
[oracle@prebilla patch]$ pwd
/home/oracle/patch
[oracle@prebilla patch]$ ll
total 551396
-rw------- 1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip
drwxr-x--- 16 oracle oinstall 4096 Apr 20 17:38 OPatch
-rw------- 1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip
[oracle@prebilla patch]$ cd $ORACLE_HOME/
[oracle@prebilla db_1]$ mv OPatch OPatch.BAK
[oracle@prebilla db_1]$ mv /home/oracle/patch/OPatch ./
[oracle@prebilla db_1]$ opatch -v
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
解压psu201020补丁:
[oracle@prebilla patch]$ ll
total 551392
-rw------- 1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip
-rw------- 1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip
[oracle@prebilla patch]$ unzip psu2020-1020.zip
Archive: psu2020-1020.zip
creating: 31720776/
creating: 31720776/31537677/
3.7.2停监听和数据库
lsnrctl stop
sqlplus / as sysdba
shutdown immediate
3.7.3打db补丁
[oracle@prebilla patch]$ pwd
/home/oracle/patch
[oracle@prebilla patch]$ cd 31720776/
[oracle@prebilla 31720776]$ ll
total 40
drwxr-xr-x 30 oracle oinstall 4096 Sep 24 2020 31537677
drwxr-xr-x 4 oracle oinstall 67 Sep 8 2020 31668908
-rw-rw-r-- 1 oracle oinstall 11566 Oct 19 2020 PatchSearch.xml
-rw-r--r-- 1 oracle oinstall 20741 Oct 20 2020 README.html
[oracle@prebilla 31720776]$ cd 31537677
[oracle@prebilla 31537677]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /home/oracle/app/oracle/product/11.2.0/db_1
Central Inventory : /home/oracle/app/oracle/oraInventory
from : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.29
OUI version : 11.2.0.4.0
Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-40-59PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@prebilla 31537677]$ pwd
/home/oracle/patch/31720776/31537677
[oracle@prebilla 31537677]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /home/oracle/app/oracle/product/11.2.0/db_1
Central Inventory : /home/oracle/app/oracle/oraInventory
from : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.29
OUI version : 11.2.0.4.0
Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-41-32PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 30298532 30670774 31103343 31537677
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/app/oracle/product/11.2.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '30298532' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'
ApplySession: Optional component(s) [ oracle.rdbms.tg4tera, 11.2.0.4.0 ] , [ oracle.rdbms.tg4sybs, 11.2.0.4.0 ] , [ oracle.rdbms.tg4ifmx, 11.2.0.4.0 ] , [ oracle.rdbms.tg4db2, 11.2.0.4.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.4.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.rdbms.hsodbc, 11.2.0.4.0...
Patching component oracle.ldap.rsf, 11.2.0.4.0...
Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...
Applying sub-patch '30670774' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.network.rsf, 11.2.0.4.0...
Patching component oracle.ldap.rsf, 11.2.0.4.0...
Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...
Patching component oracle.swd.oui, 11.2.0.4.0...
Patching component oracle.ctx, 11.2.0.4.0...
Applying sub-patch '31103343' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Applying sub-patch '31537677' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.dv, 11.2.0.4.0...
Patching component oracle.rdbms.rman, 11.2.0.4.0...
Patching component oracle.ldap.rsf, 11.2.0.4.0...
Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...
Patching component oracle.oracore.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.util, 11.2.0.4.0...
Patching component oracle.dbdev, 11.2.0.4.0...
Patching component oracle.ctx, 11.2.0.4.0...
Patching component oracle.buildtools.rsf, 11.2.0.4.0...
Composite patch 31537677 successfully applied.
Log file location: /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-41-32PM_1.log
OPatch succeeded.
[oracle@prebilla 31537677]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /home/oracle/app/oracle/product/11.2.0/db_1
Central Inventory : /home/oracle/app/oracle/oraInventory
from : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.29
OUI version : 11.2.0.4.0
Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-42-53PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
3.7.4更新db数据字典
[oracle@prebilla 31537677]$ opatch lspatches
31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)
OPatch succeeded.
[oracle@prebilla 31537677]$ cd $ORACLE_HOME/rdbms/admin
[oracle@prebilla admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 27 13:49:19 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1.0737E+10 bytes
Database Buffers 7.4625E+10 bytes
Redo Buffers 151117824 bytes
Database mounted.
Database opened.
SQL> @catbundle.sql psu apply
SQL> set head off
SQL> select * from registry$history order by id;
24-AUG-13 12.03.45.119862 PM
APPLY SERVER
11.2.0.4 0
Patchset 11.2.0.2.0
PSU
27-JUL-21 01.59.04.290989 PM
APPLY SERVER
11.2.0.4.201020OJVMPSU 0
OJVM PSU post-install
27-JUL-21 01.59.03.587257 PM
jvmpsu.sql SERVER
11.2.0.4.201020OJVMPSU 0
RAN jvmpsu.sql
27-JUL-21 10.42.39.670216 AM
APPLY SERVER
11.2.0.4 191015
PSU 11.2.0.4.191015
PSU
27-JUL-21 01.49.47.045440 PM
APPLY SERVER
11.2.0.4 201020
PSU 11.2.0.4.201020
PSU
27-JUL-21 01.59.04.293219 PM
APPLY
31668908
Patch 31668908 applied
6 rows selected.
SQL>
3.7.5打ojvm补丁
[oracle@prebilla 31668908]$ pwd
/home/oracle/patch/31720776/31668908
[oracle@prebilla 31668908]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /home/oracle/app/oracle/product/11.2.0/db_1
Central Inventory : /home/oracle/app/oracle/oraInventory
from : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.29
OUI version : 11.2.0.4.0
Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-34PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@prebilla 31668908]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.29
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /home/oracle/app/oracle/product/11.2.0/db_1
Central Inventory : /home/oracle/app/oracle/oraInventory
from : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.29
OUI version : 11.2.0.4.0
Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-59PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 31668908
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/app/oracle/product/11.2.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '31668908' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'
ApplySession: Optional component(s) [ oracle.sqlj, 11.2.0.4.0 ] , [ oracle.sqlj.companion, 11.2.0.4.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.javavm.server, 11.2.0.4.0...
Patching component oracle.precomp.common, 11.2.0.4.0...
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.javavm.client, 11.2.0.4.0...
Patching component oracle.dbjava.jdbc, 11.2.0.4.0...
Patching component oracle.dbjava.ic, 11.2.0.4.0...
Patch 31668908 successfully applied.
Log file location: /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-59PM_1.log
OPatch succeeded.
3.7.6更新ojvm数据字典
[oracle@prebilla 31668908]$ cd $ORACLE_HOME/sqlpatch
[oracle@prebilla sqlpatch]$ ls
31668908
[oracle@prebilla sqlpatch]$ cd 31668908/
[oracle@prebilla 31668908]$ pwd
/home/oracle/app/oracle/product/11.2.0/db_1/sqlpatch/31668908
[oracle@prebilla 31668908]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 27 13:55:26 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1.0737E+10 bytes
Database Buffers 7.4625E+10 bytes
Redo Buffers 151117824 bytes
Database mounted.
Database opened.
SQL> @postinstall.sql
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1.0737E+10 bytes
Database Buffers 7.4625E+10 bytes
Redo Buffers 151117824 bytes
Database mounted.
Database opened.
SQL> exit
3.7.7补丁检查:
opatch lspatches
opatch lsinventory
select ACTION_TIME,ID,BUNDLE_SERIES,version from dba_registry_history;
3.8 adg配置及测试
3.8.1归档模式:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/fast_recovery_area
Oldest online log sequence 12
Next log sequence to archive 13
Current log sequence 13
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> shutdown immediate
SQL> startup
归档日志删除策略设置:
[oracle@prebillb ~]$ crontab -l
30 12 * * * /home/oracle/clear_archivelog.sh
[oracle@prebillb ~]$ cat /home/oracle/clear_archivelog.sh
#!/bin/sh
source ~/.bash_profile
rman target / >> /home/oracle/clear_archive20210727.log << EOF
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 8';
exit;
EOF
rman主备都配置:
[oracle@prebillb ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 4 13:44:38 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PREBILL (DBID=1311192676)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PREBILLB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_prebill.f'; # default
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PREBILLB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_prebill.f'; # default
RMAN>
3.8.2准备参数文件:
主库生成参数文件,修改指定内容后拷贝到备库:
create pfile='/home/oracle/a.txt' from spfile;
修改a.txt中db_unique_name='prebillb'
根据参数文件中的路径在备库创建不存在的目录,例如:
mkdir -p /home/oracle/app/oracle/fast_recovery_area/prebill
mkdir -p /home/oracle/app/oracle/admin/prebill/adump
mkdir -p /home/oracle/app/oracle/oradata/prebill
最终参数文件参考:
*._undo_autotune=FALSE
*.archive_lag_target=0
*.audit_file_dest='/home/oracle/app/oracle/admin/prebill/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/app/oracle/oradata/prebill/control01.ctl','/home/oracle/app/oracle/fast_recovery_area/
prebill/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill'
*.db_files=1000
*.db_name='prebill'
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=1288490188800
*.db_unique_name='prebilla'
*.dg_broker_start=TRUE
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prebillXDB)'
*.event='10949 trace name context forever, level 1:28401 trace name context forever, level 1:10795 trace name context forever, level 2'
*.fal_client='prebilla'
*.fal_server='prebillb'
*.java_jit_enabled=TRUE
*.log_archive_config='dg_config=(prebilla,prebillb)'
*.log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla'
*.log_archive_dest_2='service="prebillb"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="prebillb" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_2='ENABLE'
prebill.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
prebill.log_archive_trace=0
*.log_file_name_convert='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill'
*.open_cursors=300
*.pga_aggregate_target=21474836480
*.processes=2000
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=85899345920
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
3.8.3密码文件拷贝到备库:
cd $ORACLE_HOME/dbs
scp orapwprebill 10.1.1.52:/home/oracle/app/oracle/product/11.2.0/db_1/dbs/
3.8.4测试主备库连通性:
两节点都操作,确保可连接:
sqlplus sys/SYS_Passw0rd@prebilla as sysdba
sqlplus sys/SYS_Passw0rd@prebillb as sysdba
3.8.5备库恢复操作:
在备库操作:
[oracle@dgrac1 ~]$ cat b.sh
rman target sys/SYS_Passw0rd@prebilla auxiliary sys/SYS_Passw0rd@prebillb log /home/oracle/rman-`date +%Y%m%d-%H%M`.log <<EOF
run
{
allocate channel cl1 type disk;
allocate auxiliary channel c1 type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel cl1;
}
EOF
[oracle@dgrac1 ~]$ chmod +x dg.sh
[oracle@dgrac1 ~]$ nohup ./dg.sh &
查看日志:tail -f rman-20191119-1310.log
3.8.6主备库配置dg参数:
主库:
alter system set log_archive_config='DG_CONFIG=(prebilla,prebillb)' scope=both;
alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla' scope=spfile;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla scope=spfile;
alter system set log_archive_dest_2='SERVICE=prebillb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prebillb' scope=both;
alter system set fal_server='prebillb' scope=both;
alter system set fal_client='prebilla' scope=both;
alter system set standby_file_management=AUTO scope=both;
alter system set DB_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';
备库:
alter system set log_archive_config='DG_CONFIG=(prebilla,prebillb)' scope=both;
alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebillb' scope=spfile;
alter system set log_archive_dest_2='SERVICE=prebilla LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prebilla' scope=both;
alter system set fal_server='prebilla' scope=both;
alter system set fal_client='prebillb' scope=both;
alter system set standby_file_management=AUTO scope=both;
alter system set DB_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';
3.8.7主备库测试同步状态:
备库
startup
启动到实时应用日志状态:
recover managed standby database using current logfile disconnect from session;
检查状态视图:
select process ,client_process , sequence# ,status from v$managed_standby ;
select database_role ,protection_mode ,protection_level ,open_mode from v$database;
主库
startup
create user jyc identified by jyc;
grant dba to jyc;
conn jyc/jyc@prebilla
create table test (id int);
insert into values(1);
commit;
备库:
conn jyc/jyc@prebillb
select * from test;
3.8.8手动切换dg
DG主备切换测试
主库上查询:
SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
此处的信息一定要显示是“to standby”
备库信息:
SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
-- 主库的执行:
SQL> alter database commit to switchover to physical standby with session shutdown;--应该自动关闭库了。
Database altered.
SQL> conn / as sysdba
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
--- 备库上操行:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
---- 在之前的主库上执行:
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
3.9 dg broker配置及测试
3.9.1配置命令参考:
alter system set dg_broker_start=true scope=both sid='*';--启用dg broker,两节点都得设置
alter system reset log_archive_dest_2 scope=both sid='*';--12c以上需要设置
以下内容主节点操作即可:
dgmgrl sys/SYS_Passw0rd@prebilla
create configuration adg as primary database is prebilla connect identifier is prebilla;
add database prebillb as CONNECT IDENTIFIER IS prebillb MAINTAINED AS PHYSICAL;
show CONFIGURATION verbose
show database verbose prebilla
show database verbose prebillb
SWITCHOVER TO prebillb;
ENABLE CONFIGURATION;
修改属性参数:StaticConnectIdentifier
edit database prebilla set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prebilla)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prebill)(INSTANCE_NAME=prebill)(SERVER=DEDICATED)))';
edit database prebillb set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prebillb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prebill)(INSTANCE_NAME=prebill)(SERVER=DEDICATED)))';
注意此处的service_name和3.5监听配置中的一致。
避免问题:https://sqlora.blog.csdn.net/article/details/119177917
3.9.2切换操作
dgmgrl sys/SYS_Passw0rd@prebilla
switchover to prebillb
3.10 adg服务IP配置
主备库都添加,每三秒检测一次。
[root@prebilla keepalived]# crontab -l
* * * * * /etc/keepalived/ip.sh
[root@prebilla keepalived]# cat /etc/keepalived/ip.sh
step=3
for ((i = 0; i < 60; i = (i + step))); do
$(/etc/keepalived/db_ip.sh)
sleep $step
done
exit 0
[root@prebilla keepalived]# cat /etc/keepalived/db_ip.sh
#!/bin/bash
dbstats=`ps -ef | grep ora_smon | grep -v grep | wc -l`
dgstats=`ps -ef | grep ora_mrp | grep -v grep | wc -l`
ip=`/usr/sbin/ip a|grep bond0:1|wc -l`
if [[ "${dbstats}" -gt 0 ]] && [[ "${dgstats}" -eq 0 ]]; then
if [[ "${ip}" -eq 0 ]]; then
/usr/sbin/ifconfig bond0:1 10.1.1.53 netmask 255.255.255.0 up
fi
else
if [[ "${ip}" -gt 0 ]]; then
/usr/sbin/ifconfig bond0:1 down
fi
fi
[oracle@prebilla admin]$ vi tnsnames.ora
PREBILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.53)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prebill)
)
)
"tnsnames.ora" 149L, 3017C written
执行3.9.2切换操作测试效果满足要求。
[oracle@prebilla admin]$ sqlplus jyc/jyc@prebill
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 28 16:49:42 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string /home/oracle/app/oracle/oradat
a/prebill, /home/oracle/app/or
acle/oradata/prebill
db_name string prebill
db_unique_name string prebilla
global_names boolean FALSE
instance_name string prebill
lock_name_space string
log_file_name_convert string /home/oracle/app/oracle/oradat
a/prebill, /home/oracle/app/or
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
acle/oradata/prebill
processor_group_name string
service_names string prebilla
SQL> exit