Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Oracle Cloud InfrastructureAdvent Calendar 2021

Day 1
@ora_gonsuke777in日本オラクル株式会社

Using CMAN (Oracle Connection Manager) and Role-Based Services/NLB to create a single endpoint for Primary connection in OCI DBCS Data Guard configuration. (Oracle Database, Oracle Cloud Infrastructure)

Last updated at Posted at 2021-12-28

This article is the 1st day of the JPOUG Advent Calendar 2021 and OCI Advent Calendar 2021 part 2.
The Advent Calendar is a little empty, so I'm writing an article retroactively.
:)

Oracle Cloud Infrastructure Advent Calendar 2021
https://qiita.com/advent-calendar/2021/oci

This is an English translation of the following Japanese article.

CMAN(Oracle Connection Manager) と ロールベース・サービス/NLB で
OCI DBCS Data Guard構成 の Primary接続 を シングル・エンドポイント化してみる。
(Oracle Database, Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/9460b784a2a34240fe80

CMAN (Oracle Connection Manager) is a proxy server for Oracle Database.

10 Configuring Oracle Connection Manager
https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-oracle-connection-manager.html
Oracle Connection Manager is a proxy server that forwards connection requests to databases or other proxy servers.

In this article, CMAN, the Data Guard role-based service, and NLB (Network Load Balancer) to create a single endpoint for the Data Guard configuration of OCI DBCS.

1. Review of past articles and what I want to achieve in this article

This article is a sequel to the following article.

Try to enable Data Guard on OCI DBCS VM and configure role-based services to transparently connect to PRIMARY roles. (Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/7f705f29fb65050bf295

The above configuration still provides a transparent connection to the Primary role, but the disadvantage is that it requires the following complex connection string to be written on the Client side.

AYUDBCS_PRIMARY2=(
  DESCRIPTION_LIST=
    (LOAD_BALANCE=OFF)(FAILOVER=ON)
    (
      DESCRIPTION=
        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=5)(RETRY_DELAY=3)(RETRY_COUNT=3)(ENABLE=BROKEN)
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=TCP)(HOST=ayudbcs1-scan.ayuprvsubnet2.ayuvcn1.oraclevcn.com)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ayudbcs_primary2.ayuprvsubnet2.ayuvcn1.oraclevcn.com))
    )
    (
      DESCRIPTION=
        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=5)(RETRY_DELAY=3)(RETRY_COUNT=3)(ENABLE=BROKEN)
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=TCP)(HOST=ayudbcs2-scan.ayuprvsubnet2.ayuvcn1.oraclevcn.com)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ayudbcs_primary2.ayuprvsubnet2.ayuvcn1.oraclevcn.com))
    )
)

As mentioned earlier, in this article I will try to hide the physical configuration of Data Guard with CMAN/Role-Based Service/NLB and make it a single endpoint.
The single endpoint simplifies the connection string on the Client side.

2. connection topology

Connect the PC to the Data Guard Primary with the following configuration
The reason why there are two CMAN Compute units is that they are redundant for fault tolerance, but if you don't need fault tolerance, there is no problem with one unit.
cman001.jpg

The configuration of each OCI resource is as follows.

Compute: E4 flex oCPU 1 * 2 units (for CMAN)
OS : Oracle Linux 7.9
Oracle Client 19c(19.3, Not Instant Client, Custom Install)
DBCS : VM Standard2.2, EEHP, Version 19c(19.12), 1node * 2 sets (Primary and Standby for Data Guard)
Network Load Balancer * 1 unit

3. OCI DBCS Data Guard configuration and role-based services registration.

The details are omitted in this article.
Configure the OCI DBCS to Data Guard configuration as described in the following article, and register the role-based service.

Try to enable Data Guard on OCI DBCS VM and configure role-based services to transparently connect to PRIMARY roles. (Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/7f705f29fb65050bf295

#In the primary
srvctl add service -db ayucdb3_nrt1rt -service ayudbcs_primary -pdb ayupdb3 -role PRIMARY

#In the Standby
srvctl add service -db ayucdb3_nrt1gh -service ayudbcs_primary -pdb ayupdb3 -role PRIMARY

#Service started in the Primary
srvctl start service -db ayucdb3_nrt1rt -service ayudbcs_primary

#Verify Service Registration at the listener
lsnrctl status
：
Service "ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com" has 1 instance(s).
  Instance "AYUCDB3", status READY, has 2 handler(s) for this service...
：

4. Installing Oracle Client 19c (Not Instant Client, custom installation required)

Install Oracle Client 19c (Not Instant Cliet) on CMAN Compute.
The details are omitted in this article. Please refer to the following article.
Please note that you need to install CMAN and Network Listener in your custom installation.

Installing Oracle Connection Manager
https://qiita.com/sa_to_ru13/items/c49366ee61afec045455

How to create a database link from Oracle Autonomous Database to a private on-premise DB
https://qiita.com/RexZheng/items/92fb55686fff9811bfbe

5. Open the DB connection port of CMAN Compute (firewall-cmd)

CMAN Compute will launch the CMAN process and listen to the DB connection port (1521 in this article).
It needs to open the port on OS Firewall.

I forgot this and wasted about 3 hours on it...

firewall-cmd --add-port=1521/tcp --zone=public --permanent
firewall-cmd --reload
firewall-cmd --list-all
：
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens3
  sources:
  services: dhcpv6-client ssh
  ports: 1521/tcp ***this!!!
：

Open port on both CMAN Compute.

6. CMAN configuration (edit cman.ora) and CMAN startup

Edit cman.ora to configure CMAN. I refer to the aforementioned article.

sudo su - oracle
cd /u01/app/oracle/product/19.0.0/client_1/network/admin
vi cman.ora

The contents of cman.ora are as follows. It's OK almost copy and paste.
Modify only the host part with the Compute host name (FQDN).

cman_listener = (configuration=
  (address=(protocol=tcp)(host=ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(port=1521))
  (parameter_list =
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=*)(dst=*)(srv=*)(act=accept)
    )
  )
)

Start CMAN with the following command.

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}
export TNS_ADMIN=${ORACLE_HOME}/network/admin
cmctl startup -c cman_listener
：
CMCTL for Linux: Version 19.0.0.0.0 - Production on 12-DEC-2021 12:10:20

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

Current instance cman_listener is not yet started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(port=1521)))
Starting Oracle Connection Manager instance cman_listener. Please wait...
CMAN for Linux: Version 19.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name             cman_listener
Version                   CMAN for Linux: Version 19.0.0.0.0 - Production
Start date                12-DEC-2021 12:10:20
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 ADMIN
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/19.0.0/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/ayu-cman01/cman_listener/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/ayu-cman01/cman_listener/trace
The command completed successfully.

It can check the running status of CMAN with cmctl show status and cmctl show services.

cmctl show status -c cman_listener
cmctl show services -c cman_listener

To shut down, use cmctl shutdown.

cmctl shutdown -c cman_listener

7. Edit tnsnames.ora and REMOTE_LISTENER in DBCS, and register the service.

Edit tnsnames.ora in DBCS to add the entry for CMAN.

sudo su - oracle
cd $ORACLE_HOME/network/admin
vi tnsnames.ora

The following are the entries to be added. Set the CMAN listening point (host name or IP + port number).

CMAN01 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ayu-cman01.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(PORT = 1521))

CMAN02 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ayu-cman02.ayuprvsubnet02.ayuvcn01.oraclevcn.com)(PORT = 1521))

After adding the entry to tnsnames.ora, edit the REMOTE_LISTENER parameter to register and activate the entry.

sqlplus /nolog
CONNECT /AS SYSDBA
ALTER SYSTEM SET REMOTE_LISTENER="CMAN01,CMAN02" SCOPE=BOTH SID='*';
ALTER SYSTEM REGISTER;
SHOW PARAMETER REMOTE_LISTENER

System altered.
System altered.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      CMAN01,CMAN02

Please execute the above on both Primary and Standby.
If all goes well, the DB service will be registered in the CMAN equivalent to REMOTE LISTENER.

Check the registration status of the DB service on the CMAN side.

sudo su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}
export TNS_ADMIN=${ORACLE_HOME}/network/admin
cmctl show status -c cman_listener
cmctl show services -c cman_listener
：
Service "ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com" has 1 instance(s).
  Instance "AYUCDB3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.82)(PORT=1521)))
：

It can see that the role-based service has been registered in CMAN.

8. Configuring NLB (Network Load Balancer)

The details are omitted in this article.
Please refer to the following article and set CMAN Compute to Backends of NLB(Network Load Balancer).

Try to connect to the DBCS created in the Private Subnet via the Network Load Balancer in the Public Subnet. (Oracle Cloud Infrastructure)
https://qiita.com/ora_gonsuke777/items/e20357387dfa6f12d2b7

In this case, we have configured CMAN Compute as NLB Backends as follows.

cman002.jpg

9. Connecting from client PC.(SQL*Plus, EZCONNECT)

Let's try to connect from the client (PC).
First, let's try to connect using PowerShell's sqlplus (Instant Client).

Editing the connection string in the Host Name(or IP):Port/Service-Name of the EZCONNECT.
For the hostname part, specify the public IP of the Network Load Balancer.

$env:ORACLE_HOME = "C:\tools\OracleInstantClient19c"
cd C:\tools\OracleInstantClient19c
./sqlplus /nolog

CONNECT AYSHIBAT/xxxxxxxx@1xx.1xx.1xx.1xx:1521/ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com

SET LINESIZE 120;
COLUMN HOST_NAME FORMAT A40;
COLUMN INSTANCE_NAME FORMAT A40;
SELECT HOST_NAME, INSTANCE_NAME FROM V$INSTANCE;
SELECT DB_UNIQUE_NAME FROM V$DATABASE;

Connected.

HOST_NAME                                INSTANCE_NAME
---------------------------------------- ----------------------------------------
ayudbcs3                                 AYUCDB3

SQL> SELECT DB_UNIQUE_NAME FROM V$DATABASE;

DB_UNIQUE_NAME
------------------------------
AYUCDB3_nrt1rt

Now it is a successful connection, let's try to switchover to the Data Guard Association in the OCI Console.
cman003.jpg

After the Data Guard switchover is complete, try the same connection again.

$env:ORACLE_HOME = "C:\tools\OracleInstantClient19c"
cd C:\tools\OracleInstantClient19c
./sqlplus /nolog

CONNECT AYSHIBAT/xxxxxxxx@1xx.1xx.1xx.1xx:1521/ayudbcs_primary.ayuprvsubnet02.ayuvcn01.oraclevcn.com

SET LINESIZE 120;
COLUMN HOST_NAME FORMAT A40;
COLUMN INSTANCE_NAME FORMAT A40;
SELECT HOST_NAME, INSTANCE_NAME FROM V$INSTANCE;
SELECT DB_UNIQUE_NAME FROM V$DATABASE;

Connected.

HOST_NAME                                INSTANCE_NAME
---------------------------------------- ----------------------------------------
ayudbcs4                                 AYUCDB3

SQL> SELECT DB_UNIQUE_NAME FROM V$DATABASE;

DB_UNIQUE_NAME
------------------------------
AYUCDB3_nrt1gh

It is a success connected to the Old-Standby (Primary after Switchover).

10. Connection from client PC. (SQL Developer, JDBC URL, EZCONNECT)

Next, let's try to connect from SQL Developer on the client PC.
Editing the following EZCONNECT string in the JDBC URL (JDBC connection string).

jdbc:oracle:thin:@//NLB Public IP:port/role-based service name

Set the custom JDBC URL as shown below.
cman004.jpg

The results of the SQL execution after connecting are as follows.
cman005.jpg

Data Guard switchover again and try to reconnect and re-execute SQL.
cman006.jpg

It is success connects to the Primary instance after the switchover.

11. Notes and Improvement Points

First of all, CMAN is started manually in this article.
It is better to register in systemd and launch automatically after the OS starts.
I ran out of steam this time.

In addition, the Network Load Balancer is a SPOF.
NLB is self-healing, but the SLA is not 100%.
If you want to increase the fault tolerance here, it is recommended to make the NLB redundant as well.

12. Summary

This article has abstracted the DBCS (Oracle Database) Data Guard configuration into a single endpoint.

In this article, it is connected to the Primary role, but since role-based services can also be configured on the Standby role, it is also possible to configure ...... to connect to the Read-Only Standby role of Active Data Guard.

The configuration of this article is rather tedious.
I would like to see CMAN provided as a Managed Cloud Service in the OCI.

Nevertheless, the configuration of this article may also be useful.
I hope everyone will make use of it!

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?