Centos7.9+Oracle11.2.0.4数据库ADG主备环境搭建

最新推荐文章于 2024-07-13 11:06:29 发布

好记忆不如烂笔头abc

最新推荐文章于 2024-07-13 11:06:29 发布

阅读量3.1k

点赞数 1

分类专栏： oracle adg

本文链接：https://blog.csdn.net/jycjyc/article/details/119377082

版权

oracle 同时被 2 个专栏收录

433 篇文章 6 订阅

订阅专栏

adg

4 篇文章 0 订阅

订阅专栏

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

# 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

解压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

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

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]

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]

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

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

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

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

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]

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]

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

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

---------------------------------------

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

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

else

if [[ "${ip}" -gt 0 ]]; then

/usr/sbin/ifconfig bond0:1 down

[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

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

好记忆不如烂笔头abc

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
Centos7.9+Oracle11.2.0.4数据库ADG主备环境搭建

Oracle11.2.0.4数据库ADG主备环境搭建实现目标两台X86数据库服务器实现oracle11g的主备adg环境，避免单点故障，满足数据库基本容灾备份需求。其中操作系统版本要求centos7.9，数据库版本要求oracle11.2.0.4+最新psu201020版本。环境准备主库备库主机名 prebilla prebillb 本机ip.
复制链接

扫一扫

专栏目录