一佳互联
虚拟化容器,大数据,DBA,中间件,监控。

Oracle 19c 一主一备ADG搭建

30 12月
作者:admin|分类:DBA运维

ora19c主一备一ADG搭建

一、环境搭建

主机 IP
ora19 192.168.56.101
ora19std 192.168.56.102

主库–安装数据库软件，安装实例

主库安装数据库软件

物理环境

系统：redhat7.9
内存：8G
存储：100G

挂载点 大小
/ 30G
/u01 30G
/boot 1G
/tmp 10G
swap 20G
/var 剩余

image.png

防火墙：关闭

  

   systemctl stop firewalld systemctl disable firewalld

selinux：关闭

  

   vi /etc/selinux.config SELINUX=disabled

yum源配置：

  

   ## 挂载镜像 mkdir -p /mnt/cdrom mount /dev/sr0 /mnt/cdrom ## 编辑yum源文件 cd /etc/yum.repos.d/ vi yum.repo [base] name=base baseurl=file:///mnt/cdrom enabled=1 gpgcheck=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release [root@redhat yum.repos.d] yum clean all

image.png

准备阶段

安装相关软件：

  

   ## 安装常用软件 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

用户与组：

  

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

修改内核参数：

  

   ## 修改内核参数文件 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

资源限制：

  

   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

配置用户环境变量：

  

   su - oracle vi ~/.bash_profile ## 加入以下配置到文件尾 #日志存放位置 export TMP=/tmp export TMPDIR=$TMP #主机名 export ORACLE_HOSTNAME=ora19 #库名称 export ORACLE_UNQNAME=oracle export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1 #库名称 export ORACLE_SID=oracle 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

创建相关文件目录：

  

   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/12.2.0.1/db_1 chown -R oracle:oinstall /u01/app/ chmod -R 775 /u01/app/

设置hosts文件:

  

   vi /etc/hosts 192.168.56.101 ora19
安装Oracle软件

下载Oracle安装文件：
下载地址

上传文件到服务器，并将压缩包授权给oracle后用oracle用户解压到$ORACLE_HOME文件夹下

  

   cd /tmp chown oracle:oinstall *.zip su - oracle cd /tmp unzip *.zip -d $ORACLE_HOME

编辑db_install.rsp:

  

   cd $ORACLE_HOME/install/response cp db_install.rsp /tmp vim /tmp/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/12.2.0.1/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=oracle

静默安装Oracle软件:

  

   cd $ORACLE_HOME ./runInstaller -silent -responseFile /tmp/db_install.rsp -ignorePrereq /u01/app/oracle/oraInventory/orainstRoot.sh /u01/app/oracle/product/12.2.0.1/db_1/root.sh

image.png

image.png

配置监听：

  

   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

image.png

主库安装数据库实例

dbca.rsp:

  

   vim /tmp/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 /tmp/dbca.rsp

image.png

检查状态：

  

   ## 数据库状态 select name,db_unique_name,open_mode from v$database; ## 实例状态 select instance_name,status from v$instance;

image.png

备库–安装数据库软件，不安装实例

备库安装数据库软件

与主库安装数据库软件基本一致

环境变量：

  

   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/12.2.0.1/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

hostname：

  

   hostnamectl set-hostname ora19std

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/12.2.0.1/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

image.png

二、主库配置

1. 开启归档

查看oracle归档状态:

  

   archive log list

关闭数据库:

  

   shutdown immediate;

启动数据库至mount状态:

  

   startup mount;

修改归档位置:

  

   alter system set log_archive_dest_1='location=/u01/app/archive';

/u01/app/archive用于存放归档文件的目录，如果在系统中不存在需要建立并授权给oracle用户

启用归档模式和强制归档:

  

   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 /tmp/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填写本机数据库唯一名称  *.fal_client='oracle' #fal_server填写对端的数据库唯一名称，多个备库时此处可以填写多个用逗号间隔  *.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' #建议归档日志改为arc结尾，避免被误删  *.log_archive_format='%t_%s_%r.arc' #路径转换原则，本机的路径在后面，远程的路径放前面；路径映射应该成对出现，一个路径不能对应多个，要逐一进行映射；数据文件分散在多个路径时用逗号间隔；格式要严格一致，要么都加'/'，要么都不加  *.db_file_name_convert='/u01/app/oradata/ORACLESTD/','/u01/app/oradata/ORACLE/' #redo日志多路复写时记得增加映射路径  *.log_file_name_convert='/u01/app/oradata/ORACLESTD/','/u01/app/oradata/ORACLE/' STANDBY_FILE_MANAGEMENT=AUTO

启用新的参数文件:

备份之前的spfile

  

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

启用新的

  

   shutdown immeidate; create spfile from pfile; startup;

传输文件到备库:

  

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

4. tns 配置

  

   cat tnsnames.ora ORACLE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle) ) ) LISTENER_ORACLE = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1522)) ORACLESTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19std)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oraclestd) ) )

5. 静态监听

特别注意：ORACLE_HOME的路径的db_home1后面不要有’/’，不然会导致sqlplus sys/oracle@oracle as sysdba报错ORA-12537

  

    LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19std)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oraclestd) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1) (SID_NAME = oraclestd) ) )

6. 监听重载

  

   lsnrctl reload

7. 网络连接测试

  

   tnsping oracle tnsping oraclestd

三、备库配置

1. 文件处理

  

   cd /tmp 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=(oracle,oraclestd)' *.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/ORACLESTD/','/u01/app/oradata/ORACLE/' *.log_file_name_convert='/u01/app/oradata/ORACLESTD/','/u01/app/oradata/ORACLE/'

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 配置

  

   cat 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. 静态监听

特别注意：ORACLE_HOME的路径的db_home1后面不要有’/’，不然会导致sqlplus sys/oracle@oracle as sysdba报错ORA-12537

  

    LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19std)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oraclestd) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1) (SID_NAME = oraclestd) ) )

5. 监听重载

  

   lsnrctl reload

6. 网络连接测试

  

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

四、构建备库

主库执行

登录到rman:
connected状态，主库显示DBID，备库为not mounted状态是正确的

  

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

image.png

构建备库:

  

   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;

image.png

打开备库：

  

   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;

image.png

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

  

   select open_mode from v$database;


更多文章推荐

历史上的今天
12月
30
浏览332 评论0
返回
目录 返回
首页
Oracle 19c单实例搭建DG HAProxy从零开始到掌握
↑