Oracle19c-ADG搭建

原创张均云和恩墨 2022-12-02

2241

1. 环境准备

1.1 实验环境

hostname	IP	os	软件版本
primary19c	192.168.101.171	centos 7.6	19c
standby19c	192.168.101.172	centos 7.6	19c

本实验使用的vmware虚拟机环境，将主库服务器搭建完成后，可直接克隆，配置相关项后，可用作备库服务器。

1.2 环境配置

1.2.1 硬件配置

内存：6G
硬盘：100G
分区详情：

hostname	IP
/	30G
/u01	30G
/boot	1G
/tmp	10G
swap	12G
/var	17G

1.2.1 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

1.2.3 关闭selinux

vi /etc/selinux/config
##修改SELINUX选项
SELINUX=disabled

1.2.4 配置本地yum源

mkdir -p /mnt/cdroom
mount /dev/sr0 /mnt/cdroom
mv /etc/yum.repos.d/ /etc/yum.repos.d.bak
mkdir /etc/yum.repos.d
vi /etc/yum.repos.d/CentOS-local.repo
##添加以下内容
[base]
name=base
baseurl=file:///mnt/cdrom
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
##更新yum源配置
yum clean all
yum makecache

1.2.5 安装依赖包

yum install -y vim tree net-tools unzip 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

2. 安装oracle

2.1 主库安装软件、实例

2.1.1 修改内核参数

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

2.1.2 资源限制

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

2.1.3 创建用户与组

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

2.1.4 环境变量

[root@standby19c ~]# su - oracle
[oracle@standby19c ~]$ vi ~/.bash_profile
##以下内容添加至文末
export TMP=/tmp
export TMPDIR=$TMP
#主机名
export ORACLE_HOSTNAME=primary19c
#库名称
export ORACLE_UNQNAME=oracle
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0.0/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

应用一下环境变量

[oracle@standby19c ~]$ echo $ORACLE_HOME ##环境变量没有生效

[oracle@standby19c ~]$ source ~/.bash_profile  ##使环境变量生效
[oracle@standby19c ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1

2.1.5 创建文件夹

[root@primary19c ~]# mkdir /u01/app
[root@primary19c ~]# mkdir /u01/app/oracle
[root@primary19c ~]# mkdir /u01/app/oradata
[root@primary19c ~]# mkdir /u01/app/archive
[root@primary19c ~]# mkdir /u01/app/oraInventory
[root@primary19c ~]# mkdir -p /u01/app/oracle/product/19.3.0.0/db_1
[root@primary19c ~]# 
[root@primary19c ~]# chown -R oracle:oinstall /u01/app/
[root@primary19c ~]# chmod -R 775 /u01/app/

2.1.6 注册主机名对应ip

vi /etc/hosts
192.168.101.171 primary19c
192.168.101.172 standby19c

2.1.7 安装ORACLE软件

上传解压安装包

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

2.1.8 编辑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/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0.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=oracle

2.1.9 静默安装ORACLE软件

[oracle@primary19c db_1]$cd $ORACLE_HOME

[oracle@primary19c db_1]$ ./runInstaller -silent -responseFile /tmp/db_install.rsp -ignorePrereq
Launching Oracle Database Setup Wizard...

The response file for this session can be found at:
 /u01/app/oracle/product/19.3.0.0/db_1/install/response/db_2022-12-02_01-46-11PM.rsp

You can find the log of this install session at:
 /tmp/InstallActions2022-12-02_01-46-11PM/installActions2022-12-02_01-46-11PM.log

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/oracle/product/19.3.0.0/db_1/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:
[primary19c]
Execute /u01/app/oracle/product/19.3.0.0/db_1/root.sh on the following nodes:
[primary19c]


Successfully Setup Software.
Moved the install session logs to:
 /u01/app/oraInventory/logs/InstallActions2022-12-02_01-46-11PM

2.1.10 根据提示执行脚本


[root@primary19c ~]# sh /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@primary19c ~]# sh /u01/app/oracle/product/19.3.0.0/db_1/root.sh
Check /u01/app/oracle/product/19.3.0.0/db_1/install/root_primary19c_2022-12-02_13-49-27-987164836.log for the output of root script

2.1.11 配置监听


[oracle@primary19c netca]$ netca -silent -responseFile $ORACLE_HOME/assistants/netca/netca.rsp
****DISPLAY environment variable not set!
    Oracle Net Configuration Assistant is a GUI tool
    which requires that DISPLAY specify a location
    where GUI tools can display.
    Set and export DISPLAY, then re-run.
[oracle@primary19c netca]$ netca -silent -responseFile $ORACLE_HOME/assistants/netca/netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /u01/app/oracle/product/19.3.0.0/db_1/assistants/netca/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/19.3.0.0/db_1/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

2.1.12 安装数据库实例

编写配置文件

[oracle@primary19c netca]$ cd $ORACLE_HOME/assistants/dbca
[oracle@primary19c dbca]$ cp dbca.rsp /tmp
[oracle@primary19c dbca]$ 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

静默安装实例

[oracle@primary19c dbca]$ dbca -silent -createDatabase -responseFile /tmp/dbca.rsp

安装成功


Enter SYSTEM user password:

[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
50% complete
54% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/oracle.
Database Information:
Global Database Name:oracle
System Identifier(SID):oracle
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oracle/oracle.log" for further details.

2.1.13 检查状态

[oracle@primary19c dbca]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 2 14:07:13 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


NAME      DB_UNIQUE_NAME                 OPEN_MODE
--------- ------------------------------ --------------------
ORACLE    oracle                         READ WRITE

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
oracle           OPEN

2.2 备库安装软件

2.2.1 重复2.1.1~2.1.3的操作

2.2.2 修改环境变量

主要修改hostname、uniquename以及sid，其余部分可与主库保持相同

export TMP=/tmp
export TMPDIR=$TMP
#主机名
export ORACLE_HOSTNAME=standby19c  --修改
#库名称
export ORACLE_UNQNAME=oraclestd --修改
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0.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

应用环境变量

[oracle@standby19c ~]$ source ~/.bash_profile
[oracle@standby19c ~]$ echo $ORACLE_SID
oraclestd

2.2.3 创建文件夹


[root@standby19c ~]# mkdir /u01/app
[root@standby19c ~]# mkdir /u01/oracle
[root@standby19c ~]# mkdir /u01/app/oradata
[root@standby19c ~]# mkdir /u01/app/archive
[root@standby19c ~]# mkdir /u01/app/oraInventory
[root@standby19c ~]# mkdir -p /u01/app/oracle/product/19.3.0.0/db_1
[root@standby19c ~]#
[root@standby19c ~]# chown -R oracle:oinstall /u01/app/
[root@standby19c ~]# chmod -R 775 /u01/app/

2.2.4 拷贝、解压oracle安装包

[root@standby19c tmp]# cd /tmp
[root@standby19c tmp]# chown oracle:oinstall *.zip
[root@standby19c tmp]# su - oracle
Last login: Fri Dec  2 14:03:34 CST 2022 on pts/0
[oracle@standby19c ~]$ cd /tmp
[oracle@standby19c tmp]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1
[oracle@standby19c tmp]$ unzip 07-LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

2.2.5 修改db_install.rsp

主要修改最后一项sid

[oracle@standby19c tmp]$ cd $ORACLE_HOME/install/response
[oracle@standby19c response]$ cp db_install.rsp /tmp
[oracle@standby19c response]$ vim /tmp/db_install.rsp
##将以下内容加载文件末尾
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0.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.2.6 静默安装oracle软件

[oracle@standby19c response]$ cd $ORACLE_HOME
[oracle@standby19c db_1]$ ./runInstaller -silent -responseFile /tmp/db_install.rsp -ignorePrereq

2.2.7 根据提示使用root执行脚本

[root@standby19c tmp]# sh /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@standby19c tmp]# sh /u01/app/oracle/product/19.3.0.0/db_1/root.sh
Check /u01/app/oracle/product/19.3.0.0/db_1/install/root_standby19c_2022-12-02_14-34-12-687497830.log for the output of root script

2.2.8 配置监听


[oracle@standby19c ~]$  netca -silent -responseFile $ORACLE_HOME/assistants/netca/netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /u01/app/oracle/product/19.3.0.0/db_1/assistants/netca/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/19.3.0.0/db_1/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0
[oracle@standby19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-DEC-2022 14:36:23

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-DEC-2022 14:35:41
Uptime                    0 days 0 hr. 0 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/standby19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

3. 主库配置

3.1 主库开归档

关库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

启动到mount状态

SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size                  8896064 bytes
Variable Size             318767104 bytes
Database Buffers          922746880 bytes
Redo Buffers                7880704 bytes
Database mounted.

修改归档位置

SQL> alter system set log_archive_dest_1='location=/u01/app/archive';
System altered.

启用强制归档模式

SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.

开库

SQL> alter database open;
Database altered.

查看归档状态

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

切换在线日志，查看归档是否正常

SQL> alter system switch logfile;
System altered.
SQL> !ls /u01/app/archive
1_5_1122386429.dbf

3.2 添加redo日志组

查看当前日志组

SQL> col member for a40
SQL> select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- ----------------------------------------
         3 ONLINE  /u01/app/oradata/ORACLE/redo03.log
         2 ONLINE  /u01/app/oradata/ORACLE/redo02.log
         1 ONLINE  /u01/app/oradata/ORACLE/redo01.log

查看现有redo日志容量

SQL> select group#,bytes/1024/1024 as MB,status from v$log;
    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 INACTIVE
         2        200 ACTIVE
         3        200 CURRENT

创建standby日志组

容量大小要和redo相同-200M
数量是当前日志组的数量多一个

SQL> 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;

查看状态

SQL> SQL> SQL> select group#,status,used from v$standby_log;
    GROUP# STATUS           USED
---------- ---------- ----------
         6 UNASSIGNED          0
         7 UNASSIGNED          0
         8 UNASSIGNED          0
         9 UNASSIGNED          0

3.3 准备文件

3.3.1 创建pfile文件

关库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

从spfile创建pfile

SQL> create pfile from spfile;
File created.

3.3.2 修改pfile文件

[oracle@primary19c dbs]$ cd $ORACLE_HOME/dbs
[oracle@primary19c dbs]$ vi 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

3.3.3 新的pfile启库

先备份原来的spfile

* SQL> create pfile='/tmp/spfile.ora.bak' from spfile;
File created.

从修改过的pfile中创建spfile并启动

spfile的优先级高于pfile，数据库会优先选择spfile启动

SQL> create spfile from pfile;
File created.
SQL> startup 
SQL> show parameter fal_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      oraclestd

3.3.4 传输口令文件和pfile到备库服务器

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

3.4 配置TNS

这里的端口都需要与主备的静态监听中的端口匹配

[oracle@primary19c admin]$ vim tnsnames.ora

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary19c)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )

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

ORACLESTD =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclestd)
    )
  )

3.5 配置静态监听

监听配置文件里添加1522端口作为静态监听

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

#添加以下内容作为静态监听
LSNR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.171)(PORT = 1522))
    )
  )
SID_LIST_LSNR =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oracle)
     (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/db_1)
     (SID_NAME = oracle)
    )
   )

启动LSNR监听


[oracle@primary19c ~]$ lsnrctl start LSNR

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-DEC-2022 16:50:28

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary19c/lsnr/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.171)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.171)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LSNR
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-DEC-2022 16:50:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary19c/lsnr/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.171)(PORT=1522)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oracle", status UNKNOWN, has 1 handler(s) for this service...

4. 备库配置

4.1 修改参数文件

先将远程传输的口令文件和参数文件拷贝至$ORACLE_HOME/dbs

[oracle@standby19c tmp]$ cp orapworaclestd initoraclestd.ora $ORACLE_HOME/dbs/
[oracle@standby19c tmp]$ ll $ORACLE_HOME/dbs/
total 12
-rw-r--r-- 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r--r-- 1 oracle oinstall 1661 Dec  2 17:03 initoraclestd.ora
-rw-r----- 1 oracle oinstall 2048 Dec  2 17:03 orapworacle

修改参数文件

#检查以下部分

*.audit_file_dest='/u01/app/oracle/admin/oraclestd/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oradata/ORACLESTD/control01.ctl','/u01/app/oradata/ORACLESTD/control02.ctl'
*.db_block_size=8192
*.db_name='oracle'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracleXDB)'
*.local_listener='LISTENER_ORACLE'
*.log_archive_dest_1='location=/u01/app/archive'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=300m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1200m
*.undo_tablespace='UNDOTBS1'

*.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/ORACLE/','/u01/app/oradata/ORACLESTD/'
*.log_file_name_convert='/u01/app/oradata/ORACLE/','/u01/app/oradata/ORACLESTD/'

创建文件夹

[root@standby19c admin]# mkdir -p /u01/app/oracle/admin/oraclestd/adump
[oracle@standby19c oradata]$ mkdir -p /u01/app/oradata/ORACLESTD

[root@standby19c admin]# ls -ld /u01/app/archive/
drwxrwxr-x 2 oracle oinstall 6 Dec  2 14:17 /u01/app/archive/

[root@standby19c admin]# chown -R oracle:oinstall /u01/app/oracle/admin/oraclestd/adump

4.2 启动备库到nomount


[oracle@standby19c dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 2 17:42:00 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size                  8896064 bytes
Variable Size             318767104 bytes
Database Buffers          922746880 bytes
Redo Buffers                7880704 bytes

确认参数


SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string      /u01/app/oradata/ORACLE/, /u01
                                                 /app/oradata/ORACLESTD/
db_name                              string      oracle
db_unique_name                       string      oraclestd
global_names                         boolean     FALSE
instance_name                        string      oraclestd
lock_name_space                      string
log_file_name_convert                string      /u01/app/oradata/ORACLE/, /u01
                                                 /app/oradata/ORACLESTD/

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      oraclestd

4.3 配置TNS

可与备库保持一致,如果没有配置/etc/hosts尽量把host写成ip

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )


ORACLESTD =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclestd)
    )
  )

4.4 配置备库静态监听

修改配置文件

vim $ORACLE_HOME/dbs/listener.ora 
##追加以下
LSNR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1522))
    )
  )
SID_LIST_LSNR =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oraclestd)
     (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/db_1)
     (SID_NAME = oraclestd)
    )
   )

启动静态监听LSNR

[oracle@standby19c admin]$ lsnrctl start LSNR

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-DEC-2022 17:52:28

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/standby19c/lsnr/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.172)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.172)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LSNR
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-DEC-2022 17:52:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/standby19c/lsnr/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.172)(PORT=1522)))
Services Summary...
Service "oraclestd" has 1 instance(s).
  Instance "oraclestd", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

5. 网络测试

分别在主库和备库使用oracle用户执行以下命令

tnsping oracle 
tnsping oraclestd

若出现以下类似情况，说明正常


[oracle@standby19c admin]$ tnsping oracle

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 02-DEC-2022 18:31:31

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary19c)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle)))
OK (0 msec)
[oracle@standby19c admin]$ tnsping oraclestd

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 02-DEC-2022 18:31:31

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oraclestd)))
OK (0 msec)

6.rman构建备库

6.1 在主库登陆rman

[oracle@primary19c dbs]$ rman target / auxiliary sys/oracle@ORACLESTD

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Dec 2 18:44:06 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORACLE (DBID=1995463867)
connected to auxiliary database: ORACLE (not mounted)

执行rman duplicate


RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=386 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.3.0.0/db_1/dbs/orapworaclestd'   ;
}
executing Memory Script

Starting backup at 02-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 device type=DISK
Finished backup at 02-DEC-22

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oradata/ORACLESTD/control01.ctl';
   restore clone primary controlfile to  '/u01/app/oradata/ORACLESTD/control02.ctl' from
 '/u01/app/oradata/ORACLESTD/control01.ctl';
}
executing Memory Script

Starting backup at 02-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.3.0.0/db_1/dbs/snapcf_oracle.f tag=TAG20221202T195336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-DEC-22

Starting restore at 02-DEC-22
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 02-DEC-22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oradata/ORACLESTD/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oradata/ORACLESTD/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oradata/ORACLESTD/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oradata/ORACLESTD/undotbs01.dbf";
   set newname for datafile  7 to
 "/u01/app/oradata/ORACLESTD/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oradata/ORACLESTD/system01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oradata/ORACLESTD/sysaux01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oradata/ORACLESTD/undotbs01.dbf"   datafile
 7 auxiliary format
 "/u01/app/oradata/ORACLESTD/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oradata/ORACLESTD/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 02-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oradata/ORACLE/system01.dbf
output file name=/u01/app/oradata/ORACLESTD/system01.dbf tag=TAG20221202T195343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oradata/ORACLE/sysaux01.dbf
output file name=/u01/app/oradata/ORACLESTD/sysaux01.dbf tag=TAG20221202T195343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oradata/ORACLE/undotbs01.dbf
output file name=/u01/app/oradata/ORACLESTD/undotbs01.dbf tag=TAG20221202T195343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oradata/ORACLE/users01.dbf
output file name=/u01/app/oradata/ORACLESTD/users01.dbf tag=TAG20221202T195343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-DEC-22

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1122407654 file name=/u01/app/oradata/ORACLESTD/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1122407654 file name=/u01/app/oradata/ORACLESTD/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1122407654 file name=/u01/app/oradata/ORACLESTD/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1122407654 file name=/u01/app/oradata/ORACLESTD/users01.dbf
Finished Duplicate Db at 02-DEC-22

6.2 查看主备库状态

主库


SQL> select name,database_role,protection_mode,open_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE      OPEN_MODE
--------- ---------------- -------------------- --------------------
ORACLE    PRIMARY          MAXIMUM PERFORMANCE  READ WRITE

备库

SQL> select name,database_role,protection_mode,open_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE      OPEN_MODE
--------- ---------------- -------------------- --------------------
ORACLE    PHYSICAL STANDBY MAXIMUM PERFORMANCE  MOUNTED

6.3 打开备库

SQL> alter database open;

Database altered.

SQL> select name,database_role,protection_mode,open_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE      OPEN_MODE
--------- ---------------- -------------------- --------------------
ORACLE    PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY

6.4 验证

在主库上建表、插入数据

SQL> conn scott/tiger
Connected.
SQL> create table emp1 as select * from emp;
Table created.
SQL> insert into emp1 select * from emp1;
14 rows created.
SQL> commit;
Commit complete.

在备库查看

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp1;

  COUNT(*)
----------
        28

^^------------------19c-ADG 一主一备搭建完成------------------------^^

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle19c-ADG搭建

1. 环境准备

1.1 实验环境

1.2 环境配置

1.2.1 硬件配置

1.2.1 关闭防火墙

1.2.3 关闭selinux

1.2.4 配置本地yum源

1.2.5 安装依赖包

2. 安装oracle

2.1 主库安装软件、实例

2.1.1 修改内核参数

2.1.2 资源限制

2.1.3 创建用户与组

2.1.4 环境变量

2.1.5 创建文件夹

2.1.6 注册主机名对应ip

2.1.7 安装ORACLE软件

2.1.8 编辑db_install.rsp

2.1.9 静默安装ORACLE软件

2.1.10 根据提示执行脚本

2.1.11 配置监听

2.1.12 安装数据库实例

2.1.13 检查状态

2.2 备库安装软件

2.2.1 重复2.1.1~2.1.3的操作

2.2.2 修改环境变量

2.2.3 创建文件夹

2.2.4 拷贝、解压oracle安装包

2.2.5 修改db_install.rsp

2.2.6 静默安装oracle软件

2.2.7 根据提示使用root执行脚本

2.2.8 配置监听

3. 主库配置

3.1 主库开归档

3.2 添加redo日志组

3.3 准备文件

3.3.1 创建pfile文件

3.3.2 修改pfile文件

3.3.3 新的pfile启库

3.3.4 传输口令文件和pfile到备库服务器

3.4 配置TNS

3.5 配置静态监听

4. 备库配置

4.1 修改参数文件

4.2 启动备库到nomount

4.3 配置TNS

4.4 配置备库静态监听

5. 网络测试

6.rman构建备库

6.1 在主库登陆rman

6.2 查看主备库状态

6.3 打开备库

6.4 验证

评论