oracle19c adg搭建

一、环境搭建

主机	IP
ora19	192.168.232.111
ora19std	192.168.232.112

本文结合：https://blog.csdn.net/weixin_63131036/article/details/136635553

1.配置网络yum源

1.删除redhat7.0系统自带的yum软件包；

rpm -qa|grep yum >oldyum.pkg 备份原信息

rpm -qa|grep yum|xargs rpm -e --nodeps 不检查依赖，直接删除rpm包

2.自行下载所需要的软件包。包名会更新，根据当前最新的下载。如有有依赖问题，下载依赖包进行安装。

阿里云网络源地址：https://mirrors.aliyun.com/centos/7/os/x86_64/Packages/

网易163网络源地址：http://mirrors.163.com/ CentOS

网络源地址：http://centos.ustc.edu.cn/centos/

打开网页，ctrl+f 搜索相关yum包：http://mirrors.163.com/centos/7/os/x86_64/Packages/

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-3.4.3-168.el7.centos.noarch.rpm

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-langpacks-0.4.2-7.el7.noarch.rpm

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-metadata-parser-1.1.4-10.el7.x86_64.rpm

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-plugin-fastestmirror-1.1.31-54.el7_8.noarch.rpm

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-rhn-plugin-2.0.1-10.el7.noarch.rpm

wget http://mirrors.163.com/centos/7/os/x86_64/Packages/yum-utils-1.1.31-54.el7_8.noarch.rpm
3.根据依赖项安装；

rpm -ivh yum-*
4.下载repo配置文件。

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.163.com/centos/7/atomic/x86_64/repo/CentOS-Base.repo
将文件中所有的$releasever**更换为**7，否则无法使用。
sed -i 's/$releasever/7/g' /etc/yum.repos.d/CentOS-Base.repo

5下载KEY文件：

wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 http://mirrors.163.com/centos/7/os/x86_64/RPM-GPG-KEY-CentOS-7

6.清理yum缓存。将服务器软件包信息缓存至本地，提高搜索安装效率

yum -y clean all && yum -y makecache

查看软件包

yum list

2.网络配置

ora19:

ora19std:

3.关闭selinux

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

4.关闭防火墙

停止当前运行的防火墙服务。可以使用以下命令（root用户）

systemctl stop firewalld

禁止防火墙服务在系统启动时自动启动。可以使用以下命令

systemctl disable firewalld

可以查看防火墙状态看是否配置成功

systemctl status firewalld

inactive表示禁用成功

5.hosts文件配置

配置hostname：

hostnamectl set-hostname ora19
hostnamectl set-hostname ora19std

配置hosts文件

cat <<EOF>>/etc/hosts
#Public IP
192.168.232.111     ora19
192.168.232.112     ora19std
EOF

二、准备阶段

1.安装相关软件：

## 安装常用软件
yum install -y vim tree net-tools unzip
## 安装Oracle依赖包
yum install -y binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libX11 libX11.i686 libXau libXau.i686 libXi libXi.i686 libXtst libXtst.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libxcb libxcb.i686 make nfs-utils net-tools smartmontools sysstat unixODBC unixODBC-devel gcc gcc-c++ libXext libXext.i686 zlib-devel zlib-devel.i686


yum install glibc-devel-2.17-317.el7.x86_64

2.用户与组：

## 创建oinstall、dba组与oracle用户
groupadd -g 54321 oinstall
groupadd -g 54322 dba
useradd -u 9999 -g oinstall -G dba oracle
## 设置oracle用户密码
echo "oracle:123456" | chpasswd

3.修改内核参数：

## 修改内核参数文件
cat >> /etc/sysctl.conf << EOF
## add for oracle
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
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
EOF
## 使其生效
/sbin/sysctl -p

4.资源限制：

cat >> /etc/security/limits.d/20-nproc.conf <<EOF
* soft nproc 4096
root soft nproc unlimited
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 134217728
oracle soft memlock 134217728
EOF

5.配置用户环境变量：

su - oracle
vi ~/.bash_profile
## 加入以下配置到文件尾

#日志存放位置
export TMP=/tmp
export TMPDIR=$TMP

#主机名
export ORACLE_HOSTNAME=ora19std
#库名称
export ORACLE_UNQNAME=oraclestd
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1
#库名称
export ORACLE_SID=oraclestd

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

6.创建相关文件目录：

su root
mkdir /u01/app
mkdir /u01/app/oracle
mkdir /u01/app/oradata
mkdir /u01/app/archive
mkdir /u01/app/oracle/oraInventory
mkdir -p /u01/app/oracle/product/19.3.0/db_1
chown -R oracle:oinstall /u01/app/
chmod -R 775 /u01/app/

三、解压安装包

1.将19c安装包上传到以下目录，并将压缩包授权给oracle后用oracle用户解压到$ORACLE_HOME文件夹下

cd /soft
chown -R oracle:oinstall /soft
su - oracle -c "unzip -q /soft/LINUX.X64_193000_db_home.zip -d $ORACLE_HOME"

编辑db_install.rsp:

cd $ORACLE_HOME/install/response
cp db_install.rsp /soft
vim /soft/db_install.rsp

oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oracle/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.SID=oraclestd

2.静默安装Oracle软件:

cd $ORACLE_HOME
./runInstaller -silent -responseFile /soft/db_install.rsp -ignorePrereq

/u01/app/oracle/oraInventory/orainstRoot.sh
/u01/app/oracle/product/19.3.0/db_1/root.sh

3.配置监听：

cat >>/tmp/netca.rsp <<EOF
[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
EOF

netca -silent -responseFile /tmp/netca.rsp

4.主库安装数据库实例

dbca.rsp:

vim /soft/dbca.rsp

gdbName=oracle
sid=oracle
sysPassword=oracle
oracleHomeUserPassword=oracle
templateName=General_Purpose.dbc
emExpressPort=5500
totalMemory=1500
datafileDestination=/u01/app/oradata
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
databaseType=OLTP

静默安装：

dbca -silent -createDatabase -responseFile /soft/dbca.rsp

检查状态

## 数据库状态
select name,db_unique_name,open_mode from v$database;

## 实例状态
select instance_name,status from v$instance;

四、备库安装

备库前边操作和主库一样，但是不用安装数据库实例

五、主库配置

1.开启归档

提前创建好归档目录并将权限赋给oracle用户

archive log list

shutdown immediate;


startup mount;
#修改归档位置:
alter system set log_archive_dest_1='location=/u01/app/archive';

# 启用归档模式和强制归档:
alter database archivelog;
alter database force logging;
alter database open;

查看归档后的目录状态

archive log list;

切换在线日志，验证归档是否正常:

alter system switch logfile;
!ls /u01/app/archive

2. redo日志

查询现有redo日志的数量:

select group#,type,member from v$logfile;

查询现有redo日志的容量:

select group#,bytes/1024/1024 as MB,status from v$log;

创建standby日志组:

容量要和redo相同，数量是redo当前数量+1

alter database add standby logfile
  group 6 ('/u01/app/oradata/ORACLE/standby_redo04.log') size 200m reuse,
  group 7 ('/u01/app/oradata/ORACLE/standby_redo05.log') size 200m reuse, 
  group 8 ('/u01/app/oradata/ORACLE/standby_redo06.log') size 200m reuse, 
  group 9 ('/u01/app/oradata/ORACLE/standby_redo07.log') size 200m reuse;

确认状态

select group#,status,used from v$standby_log;

3. 文件传输

# 口令文件
cd $ORACLE_HOME/dbs 
cp orapworacle /soft/orapworaclestd

参数文件:

sqlplus / as sysdba 
create pfile  from spfile;
exit

vim initoracle.ora  

*.db_unique_name='oracle' 
*.log_archive_config='dg_config=(oracle,oraclestd)' 
*.fal_client='oracle'  
*.fal_server='oraclestd' 
*.log_archive_dest_1='LOCATION=/u01/app/archive valid_for=(all_logfiles,all_roles) db_unique_name=oracle' 
*.log_archive_dest_2='service=oraclestd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=oraclestd' 
*.log_archive_format='%t_%s_%r.arc' 
*.db_file_name_convert='/u01/app/oradata/ORACLESTD/','/u01/app/oradata/ORACLE/' 
*.log_file_name_convert='/u01/app/oradata/ORACLESTD/','/u01/app/oradata/ORACLE/' 
STANDBY_FILE_MANAGEMENT=AUTO

#db_unique_name数据库唯一名称

#fal_client填写本机数据库唯一名称

备份之前的spfile

create pfile='/soft/spfile.bak' from spfile;

启用新的

shutdown immediate;
create spfile from pfile;
startup;

传输文件到备库:

scp /soft/orapworaclestd oracle@ora19std:/soft
scp $ORACLE_HOME/dbs/initoracle.ora oracle@ora19std:/soft/initoraclestd.ora

注意：tnsname.ora,listener.ora里的端口号要是自己创建时的监听，如果自己的不是1521则需要修改

4.tns配置

vi tnsnames.ora

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )

LISTENER_ORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))

ORACLESTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19std)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclestd)
    )
  )

5. 静态监听

cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oracle)
     (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
     (SID_NAME = oracle)
    )
   )

6. 监听重载

lsnrctl start
lsnrctl reload

7. 网络连接测试

tnsping oracle 
tnsping oraclestd

六、备库配置

1.文件处理

cd /soft
cp orapworaclestd initoraclestd.ora $ORACLE_HOME/dbs
修改参数文件
*.audit_file_dest='/u01/app/oracle/admin/oraclestd/adump' 
*.control_files='/u01/app/oradata/ORACLESTD/control01.ctl','/u01/app/oradata/ORACLESTD/control02.ctl'  
*.db_unique_name='oraclestd' 
*.log_archive_config='dg_config=(oraclestd,oracle)' 
*.fal_client='oraclestd' 
*.fal_server='oracle' 
*.log_archive_dest_1='LOCATION=/u01/app/archive valid_for=(all_logfiles,all_roles) db_unique_name=oraclestd' 
*.log_archive_dest_2='service=oracle lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=oracle' 
*.log_archive_format='%t_%s_%r.arc' 
*.db_file_name_convert='/u01/app/oradata/ORACLE/','/u01/app/oradata/ORACLESTD/' 
*.log_file_name_convert='/u01/app/oradata/ORACLE/','/u01/app/oradata/ORACLESTD/'

备库要创建/u01/app/oracle/admin/oraclestd/adump文件，要不然无法启动

mkdir -p /u01/app/oracle/admin/oraclestd/adump
mkdir -p /u01/app/oradata/ORACLESTD

2. 启动备库到nomount

export ORACLE_SID=oraclestd
sqlplus / as sysdba 
create spfile from pfile; 
startup nomount;

# 修改参数standby_file_management
alter system set standby_file_management=MANUAL;

3. tns 配置

vi tnsnames.ora

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )

LISTENER_ORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))

ORACLESTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19std)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclestd)
    )
  )

4.静态监听

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oraclestd)
     (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
     (SID_NAME = oraclestd)
    )
   )

5. 监听重载

lsnrctl start
lsnrctl reload

6.网络连接测试

tnsping oracle 
tnsping oraclestd

sqlplus sys/oracle@oracle as sysdba
sqlplus sys/oracle@oraclestd as sysdba

七、构建备库

主库执行

1.登录到rman:

指定主数据库（target）和备用数据库（auxiliary）

connected状态，主库显示DBID，备库为not mounted状态是正确的

rman target sys/oracle@oracle auxiliary sys/oracle@oraclestd

2.构建备库

run {
allocate channel ch001 type disk;
allocate channel ch002 type disk;
allocate channel ch003 type disk;
allocate channel ch004 type disk;
allocate auxiliary channel ch005 type disk;
allocate auxiliary channel ch006 type disk;
duplicate target database for standby from active database nofilenamecheck using compressed backupset;
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
release channel ch005;
release channel ch006;
}

备库执行

查询备库状态是否已经mount：

select open_mode from v$database;

打开备库

alter database open;

检查文件

select name from v$datafile;

select name from v$tempfile;

select group#,type,member from v$logfile order by 2,1;

开启日志应用

alter database recover managed standby database using current logfile disconnect;

检查数据同步状态:
通过判断transport lag和apply lag两个参数的DATUM_TIME值来判断备库是否在应用主库的日志文件
如果多次查询DATUM_TIME值保持不变，则说明备库已经停止从主库接收数据了

select name,value,TIME_COMPUTED,DATUM_TIME 
from v$dataguard_stats;

检查MRP进程状态:

select process,status from v$managed_standby;

查看数据库打开状态:
READ ONLY WITH APPLY代表ADG启动成功

select open_mode from v$database;