🤘🏾 🧜🏿 😦 Oracle复制和UCP快速连接故障转移 😠 ◽️ 🦎

有时，Java应用程序配置包含数据库服务器的“主” IP地址，例如，在以下情况下，它可以更改：

数据库角色的受控更改。“主要”变为“备用”，反之亦然，“备用”变为“主要”。此过程通常称为“切换”。
紧急将“待机”角色更改为“主要”角色。这通常称为“故障转移”。

在这两种情况下，应用程序不仅必须“了解”新“主”服务器的IP地址，还必须能够在需要时对其进行访问。以下是有关如何使用Oracle通用连接池（UCP）以及“切换”演示的快速指南。

对于实验，我们将使用：

具有16 GB RAM的MacBook（实验需要8 GB以上）
Virtual Box版本6.1.12
具有CentOS 7 Minimal的2个虚拟机（以下称为VM），每个虚拟机具有
- 2个vCPU
- 2048 GB RAM（临时最多8 GB，一次最多一次）
- 40 GB硬盘
- 禁用音频以避免100％CPU负载
Oracle数据库19c

让我们按照以下步骤操作：

我们使用Linux Red Hat类型start创建虚拟机（以下称为VM）。当Virtual Box启动时，它会提示您选择要从中启动VM的iso（在实验中，使用CentOS-7-x86_64-Minimal-1908.iso）。默认情况下保留所有内容，重新启动，更新，安装“ Virtual Box Guest Additions”，关闭防火墙，以免造成障碍。 “每个人都知道清除方式”，因此我们仅注意，在更新VM之后，我们将其网络接口从NAT适配器切换到“虚拟主机适配器” vboxnet0。对于可以在Virtual Box工具中创建的此适配器，手动设置地址192.168.56.1/24并禁用DHCP。基本上，它是VM的默认网关的IP地址和Java应用程序的地址。为了清楚起见。如果您仍然需要CentOS上的互联网，然后您可以在macOS上启用NAT：

使用'sudo su-'命令切换到root。
使用命令“ sysctl -w net.inet.ip.forwarding = 1”允许流量重定向。
将/etc/pf.conf文件的内容添加到/var/root/pfnat.conf文件，在其中，而不是第3行中，插入NAT规则：

从vboxnet0到enX上的nat：网络到任何->（enX）

其中，enX是具有默认路由的网络接口的名称。
运行命令“ pfctl -f pfnat.conf -e”以更新数据包过滤器规则。

可以在一个命令中执行步骤2-4。

sysctl -w net.inet.ip.forwarding=1 \
 && grep -v -E -e '^#.*' -e '^$' /etc/pf.conf | head -n2 > pfnat.conf \
 && INET_PORT=$(netstat -nrf inet | grep default | tr -s ' ' | cut -d ' ' -f 4) \
 && echo "nat on ${INET_PORT} from vboxnet0:network to any -> (${INET_PORT})" >> pfnat.conf \
 && grep -v -E -e '^#.*' -e '^$' /etc/pf.conf | tail -n+3 >> pfnat.conf \
 && pfctl -f pfnat.conf -e

将统一条目添加到所有VM的/ etc /主机，以便它们可以通过域名彼此“ ping”。

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.56.78 oracle1.localdomain oracle1
192.168.56.79 oracle2.localdomain oracle2

安装Oracle

我们使用rpm-packages在oracle1和oracle2上执行“仅软件”安装，该目录可通过Virtual Box从MacOS共享（机器->设置->共享文件夹）。

yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

接下来，我们在VM“ oracle1”上创建一个DBMS实例。为此，请在oralce用户下运行相应的脚本。

/etc/init.d/oracledb_ORCLCDB-19c configure

此过程需要一些时间。在两台主机上创建实例后，将这些行添加到/home/oracle/.bash_profile文件中：

export ORACLE_HOME="/opt/oracle/product/19c/dbhome_1"
export ORACLE_BASE="/opt/oracle"
export ORACLE_SID="ORCLCDB"
export PATH=$PATH:$ORACLE_HOME/bin

好了，为了方便起见，我们还配置了ssh，以便您可以立即以oracle用户身份进行连接。我们通过ssh连接到主机，并在oracle用户下连接到数据库。

user@macbook:~$ ssh oracle@oracle1
Last login: Wed Aug 12 16:17:05 2020
[oracle@oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 16:19:44 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

实际上，Oracle。对于实验，我们还需要设置复制。

Oracle复制。

要配置复制，我们将使用稍微更新的说明：

我们将oracle1服务器上的数据库转移到“归档模式”。为此，请执行sqlplus中的命令：
```
SHUTDOWN IMMEDIATE;
```
```
STARTUP MOUNT;
```
```
ALTER DATABASE ARCHIVELOG;
```
```
ALTER DATABASE OPEN;
```
在不离开sqlplus的情况下，在oracle1服务器上启用“强制日志记录”。
```
ALTER DATABASE FORCE LOGGING;
```
```
ALTER SYSTEM SWITCH LOGFILE;
```

在oracle1服务器上创建“重做日志”文件。

ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 10 ('/opt/oracle/oradata/ORCLCDB/standby_redo01.log')
      SIZE 209715200;

ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 11 ('/opt/oracle/oradata/ORCLCDB/standby_redo02.log')
      SIZE 209715200;

ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 12 ('/opt/oracle/oradata/ORCLCDB/standby_redo03.log')
      SIZE 209715200;

ALTER DATABASE
      ADD STANDBY LOGFILE
      THREAD 1 GROUP 13 ('/opt/oracle/oradata/ORCLCDB/standby_redo04.log')
      SIZE 209715200;

在oracle1服务器上打开“ FLASHBACK”，没有它将无法工作。

SQL>主机

[oracle @ oracle1〜] $ mkdir / opt / oracle / recovery_area

[oracle @ oracle1〜] $ exit

SQL> alter system set db_recovery_file_dest_size = 2g作用域=两者；

SQL> alter system set db_recovery_file_dest ='/ opt / oracle / recovery_area'作用域=两者兼有;

SQL> ALTER DATABASE闪回打开；
我们在oracle1服务器上启用自动功能。

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

tnsnames.ora listener.ora oracle1 oracle2 :

oracle1, $ORACLE_HOME/network/admin/tnsnames.ora


LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

ORCLCDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

oracle1, $ORACLE_HOME/network/admin/listener.ora


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLCDB_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
      (SID_NAME = ORCLCDB)
    )
  )

ADR_BASE_LISTENER = /opt/oracle

oracle2, $ORACLE_HOME/network/admin/tnsnames.ora


LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

ORCLCDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCLCDB)
    )
  )

oracle2, $ORACLE_HOME/network/admin/listener.ora


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLCDB_STBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
      (SID_NAME = ORCLCDB)
    )
  )

ADR_BASE_LISTENER = /opt/oracle

oracle1 listener-.

[oracle@oracle1 ~]$ lsnrctl reload

[oracle@oracle1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:24

Copyright © 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1.localdomain)(PORT=1521)))

STATUS of the LISTENER

— Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 — Production

Start Date 15-AUG-2020 08:09:57

Uptime 0 days 0 hr. 7 min. 26 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1.localdomain)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary…

Service «ORCLCDB» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «ORCLCDBXDB» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «ac8d8d741e3e2a52e0534e38a8c0602d» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «orclpdb1» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

The command completed successfully

( Data Guard: ORCLCDB_DGMGRL)
[oracle@oracle1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:32

Copyright © 1991, 2019, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1.localdomain)(PORT=1521)))

STATUS of the LISTENER

— Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 — Production

Start Date 15-AUG-2020 08:09:57

Uptime 0 days 0 hr. 7 min. 34 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1.localdomain)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary…

Service «ORCLCDB» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «ORCLCDBXDB» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «ORCLCDB_DGMGRL» has 1 instance(s).

Instance «ORCLCDB», status UNKNOWN, has 1 handler(s) for this service…

Service «ac8d8d741e3e2a52e0534e38a8c0602d» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

Service «orclpdb1» has 1 instance(s).

Instance «ORCLCDB», status READY, has 1 handler(s) for this service…

The command completed successfully
SYS oracle1.

SQL> alter user sys identified by "pa_SSw0rd";
oracle2 listener .

[oracle@oracle2 ~]$ lsnrctl start

[oracle@oracle2 ~]$ orapwd file=$ORACLE_BASE/product/19c/dbhome_1/dbs/orapwORCLCDB entries=10 password=pa_SSw0rd

[oracle@oracle2 ~]$ echo "*.db_name='ORCLCDB'" > /tmp/initORCLCDB_STBY.ora

[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/pdbseed

[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/ORCLPDB1

[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/admin/ORCLCDB/adump

[oracle@oracle2 ~]$ mkdir /opt/oracle/recovery_area

[oracle@oracle2 ~]$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/tmp/initORCLCDB_STBY.ora'

[oracle@oracle2 ~]$ rman TARGET sys/pa_SSw0rd@ORCLCDB AUXILIARY sys/pa_SSw0rd@ORCLCDB_STBY

RMAN>从活动数据库DORECOVER SPFILE SET中为备用数据库复制目标数据库db_unique_name ='ORCLCDB_STBY'注释'正在备用'NOFILENAMECHECK;
在两个服务器（oracle1和oracle2）上启动Data Guard。

SQL> ALTER SYSTEM SET dg_broker_start = true;
在oracle1服务器上，连接到Data Guard管理控制台并创建配置。

[oracle @ oracle1〜] $ dgmgrl sys / pa_SSw0rd @ ORCLCDB

DGMGRL>创建配置my_dg_config作为主要数据库是ORCLCDB连接标识符是ORCLCDB;

DGMGRL>将数据库ORCLCDB_STBY添加为连接标识符，将ORCLCDB_STBY保持为物理；

DGMGRL>启用配置；

因此，作为创建副本并启用Data Guard的结果，我们具有以下状态：

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 40 seconds ago)

这是一个坏条件。让我们稍等...

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

现在状态很好！但是，副本是非常被动的，它不接受读取请求（OPEN MODE：MOUNTED）。

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 
	 2 PDB$SEED			  MOUNTED
	 3 ORCLPDB1			  MOUNTED

让我们激活副本，以便它接受读取请求。然后，我们将来将可以使用“ Primary”库卸载服务器。这就是所谓的“ Active Data Guard”或活动待机。在oracle2服务器上，在sqlplus中执行以下命令序列：

alter database 
      recover managed standby database cancel;

alter database open;

alter database 
      recover managed standby database 
      using current logfile 
      disconnect from session;

alter pluggable database all open;

现在您可以从副本中读取内容（开放模式：只读）：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ ONLY  NO

在oracle1服务器上的DataGuard控制台中，一切看起来都不错：

DGMGRL> show configuration;

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orclcdb      - Primary database
    orclcdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 19 seconds ago)

尽管事实上我们现在拥有Data Guard Active，但是该集群仍然处于被动状态。他仍然不会对我们和我们令人惊叹的Java应用程序说什么，Primary不再是Primary。为此，必须在群集服务器上运行另一个服务ONS（Oracle Notification Services）。看起来安装Oracle数据库不足以运行此服务，我们将需要安装Oracle Grid。

安装和配置Oracle Grid。

这里的一切都非常简单：在安装Oracle数据库的过程中，我们将仅遵循官方说明。

oracle1 oracle2 root Oracle Grid, gcc-c++, oracle asm. Virtual Box, Oracle, Oracle Grid .
```
mkdir -p /opt/oracle/product/19c/grid \
        && cp -r /mnt/oracle_grid_19300/LINUX/* /opt/oracle/product/19c/grid/ \
        && chown -R oracle:oinstall /opt/oracle/product/19c/grid
```
```
yum install -y gcc-c++
```
```
groupadd asm && usermod -aG asm oracle
```
, oracle, Oracle Grid.
```
cd /opt/oracle/product/19c/grid/ && ./runcluvfy.sh stage -pre hacfg
```
Verifying Physical Memory ...FAILED

Required physical memory = 8GB

Verifying Swap Size ...FAILED

Required = 2.6924GB (2823138.0KB); Found = 2GB (2097148.0KB)]

- , swap, Oracle Grid , .
oracle2, oracle1.

SQL> shutdown immediate

# poweroff

oracle1 8192 , VM swap root.

dd if=/dev/zero of=/swap_file bs=1G count=7 \
            && chmod 600 /swap_file && mkswap /swap_file \
            && echo '/swap_file  swap  swap  defaults  0 0' >> /etc/fstab \
            && swapoff -a && swapon -a

, , .

[oracle@oracle1 grid]$ cd /opt/oracle/product/19c/grid/ && ./runcluvfy.sh stage -pre hacfg

Pre-check for Oracle Restart configuration was successful.

! Oracle Grid.
oracle oracle1 /opt/oracle/product/19c/grid/ grid_configwizard.rsp.
```
cd /opt/oracle/product/19c/grid/ && touch grid_configwizard.rsp
```
grid_configwizard.rsp , oracle restart, ASM .

oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0

INVENTORY_LOCATION=/opt/oracle/oraInventory

oracle.install.option=CRS_SWONLY

ORACLE_BASE=/opt/oracle

oracle.install.asm.OSDBA=oinstall

oracle.install.asm.OSASM=asm

oracle.install.asm.SYSASMPassword=oracle

oracle.install.asm.diskGroup.name=data

oracle.install.asm.diskGroup.redundancy=NORMAL

oracle.install.asm.diskGroup.AUSize=4

oracle.install.asm.diskGroup.disksWithFailureGroupNames=/dev/sdb

oracle grid .

./gridSetup.sh -silent \
               -responseFile /opt/oracle/product/19c/grid/grid_configwizard.rsp

, oracle grid root, .
```
/opt/oracle/product/19c/grid/root.sh
```
, oracle restart root roothas.sh.
```
cd /opt/oracle/product/19c/grid/crs/install/ && ./roothas.sh
```

oracle runInstaller.

cd /opt/oracle/product/19c/grid/oui/bin/ \
                            && ./runInstaller -updateNodeList \
                                ORACLE_HOME=/opt/oracle/product/19c/grid \
                                -defaultHomeName CLUSTER_NODES= CRS=TRUE

oracle1 /etc/fstab , /swap_file, 2048 RAM.
" Oracle Grid" VM oracle2.

, , Oracle Restart. oracle1 oracle Oracle Restart.

srvctl add database -db ORCLCDB \
            -oraclehome /opt/oracle/product/19c/dbhome_1 \
            -role PRIMARY

/opt/oracle/product/19c/grid/bin/crsctl modify \
            res ora.cssd -attr "AUTO_START=always" -unsupported

/opt/oracle/product/19c/grid/bin/crsctl stop has

/opt/oracle/product/19c/grid/bin/crsctl start has

oracle2 oracle Oracle Restart.

/opt/oracle/product/19c/grid/bin/crsctl modify \
            res ora.cssd -attr "AUTO_START=always" -unsupported

/opt/oracle/product/19c/grid/bin/crsctl stop has

/opt/oracle/product/19c/grid/bin/crsctl start has

srvctl add database -db ORCLCDB_STBY \
           -oraclehome /opt/oracle/product/19c/dbhome_1 \
           -role PHYSICAL_STANDBY \
           -spfile /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora \
           -dbname ORCLCDB -instance ORCLCDB

在两个服务器oracle1和oracle2上，在oracle用户下，将侦听器添加到Oracle Restart配置中。
```
srvctl add listener
```
在oracle1服务器上，在oracle用户下，添加到配置并启动数据库服务ORCLCDB。
```
srvctl add service -db ORCLCDB -service orclpdb -l PRIMARY -pdb ORCLPDB1
```
```
srvctl start service -db ORCLCDB -service orclpdb
```
在oracle2服务器上，在oracle用户下，将数据库服务ORCLCDB_STBY添加到配置中，并启动数据库实例。
```
srvctl add service -db ORCLCDB_STBY -service orclpdb -l PRIMARY -pdb ORCLPDB1
```
```
srvctl start database -db ORCLCDB_STBY
```
在oracle1和oracle2服务器上，启动ons服务。
```
srvctl enable ons
```
```
srvctl start ons
```

Java测试应用程序上的“切换”演示

结果，我们有2个oracle服务器，它们具有活动-备用模式下的复制，以及一个将向其发送切换事件的ons服务，并且Java应用程序将能够在这些事件到达时对其进行处理。

让我们在Oracle中创建一个测试用户和表。

oracle1 oracle sqlplus

```
sqlplus / as sysdba
```
```
alter session set container=ORCLPDB1;
```

CREATE USER testus 
       IDENTIFIED BY test 
       DEFAULT TABLESPACE USERS 
       QUOTA UNLIMITED ON USERS;

GRANT CONNECT, 
      CREATE SESSION, 
      CREATE TABLE 
      TO testus;

CREATE TABLE TESTUS.MESSAGES (TIMEDATE TIMESTAMP, MESSAGE VARCHAR2(100));

创建测试用户和表之后，我们连接到oracle2服务器并“打开”实例以进行读取，以确保复制有效。

 
 [oracle@oracle2 ~]$ sqlplus / as sysdba
 SQL> alter pluggable database all open;
 SQL> alter session set container=ORCLPDB1;
 SQL> column table_name format A10;
 SQL> column owner format A10;
 SQL> select table_name, owner from dba_tables where owner like '%TEST%';

TABLE_NAME OWNER
---------- ----------
MESSAGES   TESTUS

测试应用程序仅包含一个Main类，在一个循环中，它尝试将一条记录添加到测试表中，然后读取同一条记录（请参见“ putNewMessage（）”和“ getLastMessage（）”方法）。还有一个“ getConnectionHost（）”方法，该方法使用“ Reflection API”从“连接”对象中获取与数据库的连接的IP地址。如您在控制台中所见，“切换”后，该地址会更改。

启动测试应用程序，然后在Data Guard控制台中执行“切换”。

DGMGRL> switchover to orclcdb_stby;

我们在应用程序日志中看到连接IP地址的变化方式，停机时间为1分钟：

[Wed Aug 26 23:56:55 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:55|  ?
[Wed Aug 26 23:56:56 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:56|  ?
[Wed Aug 26 23:56:57 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:56:57|  ?
[Wed Aug 26 23:56:58 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:02 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:06 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:10 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:14 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:18 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:23 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:27 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:31 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:35 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:39 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:43 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:47 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:57:51 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:52|  ?
[Wed Aug 26 23:57:53 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:53|  ?
[Wed Aug 26 23:57:54 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:57:54|  ?

切换回确定。

DGMGRL> switchover to orclcdb;

情况是对称的。

[Wed Aug 26 23:58:54 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:58:54|  ?
[Wed Aug 26 23:58:55 MSK 2020]: Host 192.168.56.79: - 2020-08-26 23:58:55|  ?
[Wed Aug 26 23:58:56 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:00 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:04 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:08 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:12 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:16 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:20 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:24 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:28 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:32 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:36 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:40 MSK 2020]: SQLException: -  !
[Wed Aug 26 23:59:44 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:59:45|  ?
[Wed Aug 26 23:59:46 MSK 2020]: Host 192.168.56.78: - 2020-08-26 23:59:46|  ?

另外，让我们注意oralce1和oracle2主机上的TCP连接。数据端口仅在主服务器上忙于连接，ONS端口在主服务器和副本服务器上均忙。

切换前。


oracle1
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22"

ESTAB 0 0 192.168.56.78:1521 192.168.56.1:49819 users:(("oracle_21115_or"...))
ESTAB 0 0 192.168.56.78:1521 192.168.56.1:49822 users:(("oracle_21117_or"...))
ESTAB 0 0 [::ffff:192.168.56.78]:6200 [::ffff:192.168.56.1]:49820 users:(("ons"...))


oracle2
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22"

ESTAB 0 0 [::ffff:192.168.56.79]:6200 [::ffff:192.168.56.1]:49821 users:(("ons"...))

切换后。


oracle1
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v 22  Wed Aug 26 16:57:57 2020

ESTAB 0 0 [::ffff:192.168.56.78]:6200 [::ffff:192.168.56.1]:51457 users:(("ons"...))


oracle2
Every 1.0s: ss -tapn | grep 192.168.56.1 | grep ESTAB | grep -v ":22" Wed Aug 26 16:58:35 2020

ESTAB 0 0 192.168.56.79:1521 192.168.56.1:52259 users:(("oracle_28212_or"...))
ESTAB 0 0 192.168.56.79:1521 192.168.56.1:52257 users:(("oracle_28204_or"...))
ESTAB 0 0 [::ffff:192.168.56.79]:6200 [::ffff:192.168.56.1]:51458 users:(("ons"...))

结论

因此，我们用最少的设置在实验室环境中模拟了Data Guard集群，并实现了测试Java应用程序的故障转移连接。现在，我们知道开发人员从DBA那里想要什么，而DBA从开发者那里想要什么。它仍然可以启动和测试快速启动故障转移，但是，也许是在另一篇文章的框架内。

Oracle复制和UCP快速连接故障转移

安装Oracle

Oracle复制。

安装和配置Oracle Grid。

Java测试应用程序上的“切换”演示

结论

More articles: