Configuring TCPS for database client connections on Flashgrid-enabled clusters

In this document we assume the following port assignments (all four ports must be different):

  • TCP SCAN Listener: 1521
  • TCP Local Listener: 1522
  • TCPS SCAN Listener: 1523
  • TCPS Local Listener: 1524

1. Add TCPS port to SCAN Proxy configuration

Do the following on all database nodes.

1.1 Modify /etc/flashgrid-scan.cfg

#scan_port: 1521
scan_port_list: [1521, 1523]

1.2 Restart SCAN Proxy service

# systemctl restart flashgrid-scan-proxy.service

2. Add iptables forwarding rule for Local Listener TCPS port

Do the following on all database nodes.

2.1 Remove immutable flag from /etc/sysconfig/iptables

# chattr -i /etc/sysconfig/iptables

2.2 In /etc/sysconfig/iptables add the following forwarding rule under similar rule for TCP port (replace X with address corresponding to the node):

-A PREROUTING -i eth0 -p tcp -m tcp --dport 1524 -j DNAT --to-destination 192.168.1.X

2.3 Add immutable flag to /etc/sysconfig/iptables

# chattr +i /etc/sysconfig/iptables

2.4 Restart iptables service

# systemctl restart iptables.service

3. Add TCPS port to Local Listener configuration

On the first node:

[grid@rac1 ~]$ srvctl modify listener -p "TCP:1522/TCPS:1524";

On each database node, modify LISTENER parameter in listener.ora under GI home (replace rac1.example.com with corresponding host name):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.example.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
      (ADDRESS = (PROTOCOL = TCPS)(HOST =rac1.example.com)(PORT = 1524))
    )
  )

SECURE_REGISTER_LISTENER_SCAN1 = (IPC,TCPS)
SECURE_REGISTER_LISTENER_SCAN2 = (IPC,TCPS)
SECURE_REGISTER_LISTENER_SCAN3 = (IPC,TCPS)
SECURE_REGISTER_LISTENER = (IPC,TCPS)

From the first node:

[grid@rac1 ~]$ srvctl stop listener
[grid@rac1 ~]$ srvctl start listener

[grid@rac1 ~]$ srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1522/TCPS:1524
Listener is enabled.
Listener is individually enabled on nodes: 
Listener is individually disabled on nodes: 

[grid@rac1 ~]$ lsnrctl status
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.example.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1.example.com)(PORT=1524)))

From the second node:

[grid@rac2 ~]$ lsnrctl status
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.example.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac2.example.com)(PORT=1524)))

4. Add TCPS port to SCAN Listener configuration

On the first database node:

[grid@rac1 ~]$ srvctl stop scan_listener
[grid@rac1 ~]$ srvctl stop scan
[grid@rac1 ~]$ srvctl modify scan_listener -p TCP:1521/TCPS:1523
[grid@rac1 ~]$ srvctl start scan
[grid@rac1 ~]$ srvctl start scan_listener

[grid@rac1 ~]$ srvctl config scan_listener
SCAN Listeners for network 1:
Registration invited nodes: 
Registration invited subnets: 
Endpoints: TCP:1521/TCPS:1523
SCAN Listener LISTENER_SCAN1 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN2 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN3 exists
SCAN Listener is enabled.

[grid@rac1 ~]$ lsnrctl status listener_scan3
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.1.23)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521)))
The listener supports no services
The command completed successfully

[grid@rac1 ~]$ crsctl stat res -p |grep ENDPOINTS
ENDPOINTS=TCP:1525
ENDPOINTS=TCP:1522 TCPS:1524
ENDPOINTS=TCP:1521 TCPS:1523
ENDPOINTS=TCP:1521 TCPS:1523
ENDPOINTS=TCP:1521 TCPS:1523

5. Create SSL Certificates and wallets for DB nodes and client

5.1 Create a self-signed CA (You can use your own CA and safely skip this step)

On the first database node:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/CA
[oracle@rac1 ~]$ export CA_HOME=/u01/app/oracle/CA
[oracle@rac1 ~]$ orapki wallet create -wallet  $CA_HOME  -auto_login -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet remove -trusted_cert_all -wallet $CA_HOME -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet add -wallet $CA_HOME -self_signed -dn "CN=TEST-CA" -keysize 1024 -validity 3650 -sign_alg sha256 -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet export -wallet $CA_HOME -dn "CN=TEST-CA" -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123

[oracle@rac1 ~]$ orapki wallet display -wallet $CA_HOME -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=TEST-CA
Trusted Certificates:
Subject:        CN=TEST-CA

5.2 Create Cluster Wallet and Certificate

On the first database node:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/wallet
[oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet
[oracle@rac1 ~]$ orapki wallet create -wallet $W_HOME -pwd Oracle123 -auto_login -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -trusted_cert -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123

[oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=TEST-CA

Find out what is your scan name (from oracle or root user):

[oracle@rac1 ~]$ srvctl config scan|grep "SCAN name"
SCAN name: marihcheck-scan.example.com, Network: 1

Instead of marihcheck-scan indicate scan name returned from the previous command:

[oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet
[oracle@rac1 ~]$ export CA_HOME=/u01/app/oracle/CA
[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -dn "CN=marihcheck-scan" -keysize 1024 -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet export -wallet $W_HOME -dn "CN=marihcheck-scan" -request $W_HOME/marihcheck.req -pwd Oracle123
[oracle@rac1 ~]$ orapki cert create -wallet $CA_HOME -request $W_HOME/marihcheck.req -cert $W_HOME/marihcheck.cer -validity 3650 -sign_alg sha256 -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/marihcheck.cer -pwd Oracle123

[oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=marihcheck-scan
Trusted Certificates:
Subject:        CN=TEST-CA

5.3 Create Client Wallet and Certificate

From the first database node:

Instead of clientvm indicate hostname of the client:

[oracle@rac1 ~]$ export W_HOME=/u01/app/oracle/wallet/client
[oracle@rac1 ~]$ orapki wallet create -wallet $W_HOME -pwd Oracle123 -auto_login -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -trusted_cert -cert /u01/app/oracle/CA/testCA.cer -pwd Oracle123

[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -dn "CN=clientvm" -keysize 1024 -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet export -wallet $W_HOME -dn "CN=clientvm" -request $W_HOME/clientvm.req -pwd Oracle123
[oracle@rac1 ~]$ orapki cert create -wallet $CA_HOME -request $W_HOME/clientvm.req -cert $W_HOME/clientvm.cer -validity 3650 -sign_alg sha256 -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/clientvm.cer -pwd Oracle123
[oracle@rac1 ~]$ orapki wallet display -wallet $W_HOME -summary
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=clientvm
Trusted Certificates:
Subject:        CN=TEST-CA

Change permission on server and client cwallet.sso file:

[oracle@rac1 wallet]$ chmod 640 /u01/app/oracle/wallet/cwallet.sso
[oracle@rac1 wallet]$ chmod 640 /u01/app/oracle/wallet/client/cwallet.sso

Create wallet location on the clientvm and copy generated client wallet from rac1 to the client:

[root@clientvm ~]# mkdir /usr/lib/oracle/19.5/client64/wallet
[root@clientvm ~]# cd /usr/lib/oracle/19.5/client64/wallet
[root@clientvm ~]# scp oracle@rac1:/u01/app/oracle/wallet/client/* .

Delete client folder from rac1:

[oracle@rac1 ~]$ rm -rf /u01/app/oracle/wallet/client/

Copy generated server wallet from rac1 to rac2:

[oracle@rac1 ~]$ scp -r /u01/app/oracle/wallet oracle@rac2:/u01/app/oracle/
cwallet.sso              100% 2445   638.3KB/s   00:00
cwallet.sso.lck          100%    0     0.0KB/s   00:00
ewallet.p12              100% 2400   540.6KB/s   00:00
ewallet.p12.lck          100%    0     0.0KB/s   00:00

6. Configure Oracle network files

6.1 Add the following lines to the server side sqlnet.ora file in RDBMS and GI homes accordingly (on all database nodes)

[oracle@rac1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SSL_VERSION = 0
SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
[grid@rac1 ~]$ cat /u01/app/19.3.0/grid/network/admin/sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SSL_CLIENT_AUTHENTICATION = FALSE

6.2 Add the following entries to the client side sqlnet.ora file:

[root@clientvm admin]# cat sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /usr/lib/oracle/19.5/client64/wallet)
     )
   )

6.3 Add the following lines to the listener.ora in GI home (/u01/app/19.3.0/grid/network/admin/listener.ora) on all database nodes

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )

6.4 Update NODEFQDN entry to TCPS/1524 in tnsnames.ora in RDBMS home on all database nodes

[oracle@rac1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora

DONOTDELETE,NODEFQDN =
  (ADDRESS = (PROTOCOL = TCPS)(Host = rac1.example.com)(Port = 1524))
[oracle@rac2 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora

DONOTDELETE,NODEFQDN =
  (ADDRESS = (PROTOCOL = TCPS)(Host = rac2.example.com)(Port = 1524))

6.5 Reset local_listener parameter to NODEFQND

[oracle@rac1 ~]$ export ORACLE_SID=orclcdb1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> alter system set local_listener='NODEFQDN';

6.6 Restart listeners:

[root@rac1 ~]# srvctl stop listener; srvctl start listener
[root@rac1 ~]# srvctl stop scan_listener; srvctl start scan_listener

6.7 Add the following entries to the client side tnsnames.ora

[root@clientvm admin]# cat tnsnames.ora
TEST_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = marihcheck-scan.example.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclcdb)
    )
  )

TEST_SSL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = rac1.example.com)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclcdb)
    )
  )

TEST_SSL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = rac2.example.com)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclcdb)
    )
  )

7. Test connections

[root@clientvm admin]# export ORACLE_HOME=/usr/lib/oracle/19.5/client64
[root@clientvm admin]# export PATH=$ORACLE_HOME/bin:$PATH
[root@clientvm admin]# export TNS_ADMIN=$ORACLE_HOME/network/admin

[root@clientvm admin]# sqlplus system/oracle@TEST_SSL

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:52:33 2020
Version 19.5.0.0.0

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

Last Successful login time: Sat Mar 14 2020 19:54:53 +00:00

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

SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
[root@clientvm admin]# sqlplus system/oracle@TEST_SSL1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:53:23 2020
Version 19.5.0.0.0

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

Last Successful login time: Thu Mar 19 2020 11:53:20 +00:00

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

SQL>
[root@clientvm admin]# sqlplus system/oracle@TEST_SSL2

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 19 11:53:23 2020
Version 19.5.0.0.0

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

Last Successful login time: Thu Mar 19 2020 11:53:20 +00:00

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

SQL>

Note: The same steps are described https://kb.flashgrid.io/configuring-tcps-for-client-connections#6-configure-oracle-network-files, which is also written by me.

Creating an Oracle RAC on Azure cloud with FlashGrid SkyCluster

Configure Bastion host for Azure VM

What is Azure Bastion?

The Azure Bastion is PaaS service that you create inside your virtual network. It provides secure RDP and SSH connectivity to all of the VMs in the virtual network in which it is provisioned so that your target virtual machines do not need a public IP address.

Configuration Steps:

1. Connect to the Azure portal and choose the VM to connect to.

2. From the left panel, choose Bastion under the Operations section, or click Connect from the Overview section.

3. To associate a virtual network with a Bastion, it must contain a subnet with the name AzureBastionSubnet. On the following page, click Manage subnet configuration

4. Add subnet named AzureBastionSubnet

5. After configuring subnet, click link rac1-vm – Bastion in the upper left corner. And then click Create button.

6. Connect to the VM via Azure Bastion

If you have ssh key based authentication, enter correct username and choose the right private key file.

Please note, that the private key file should be in PEM format. If you get the following error message, it means your key file is not in the correct format:

 If so, convert it using ssh-keygen (for windows PuTTYgen) :

$ ssh-keygen -p -m PEM -f my_private_key

Choose the key file again and click Connect.

Resize ASM disks in AWS (FG enabled cluster)

  1. Connect to AWS console https://console.aws.amazon.com
  2. On the left side -> under the section ELASTIC BLOCK STORE -> choose Volumes
  3. Choose necessary disk -> click Actions button -> choose Modify Volume -> change Size
    Please note that all data disks (not quorum disk) must be increased under the same diskgroup, otherwise ASM will not let you to have different sized disks.

Choose another data disks and repeat the same steps.

4. Run the following on database nodes via root user:

# for i in /sys/block/*/device/rescan; do echo 1 > $i; done

5. Check that disks have correct sizes:

# flashgrid-node

6. Connect to the ASM instance from any database node and run:

[grid@rac1 ~]$ sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 23 10:17:50 2019
Version 19.4.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.4.0.0.0

SQL> alter diskgroup GRID resize all; 
Diskgroup altered.

FlashGrid SkyCluster Now Supports Oracle Database 19c

FlashGrid SkyCluster Version 19.06 now has full support of GI/DB 19c, which means using FG launcher tool (https://www.flashgrid.io/skycluster-for-aws/#launch , https://www.flashgrid.io/skycluster-for-azure/#launch , https://www.flashgrid.io/skycluster-for-gcp/#launch ) , you can setup multi-node Real Application Clusters in the cloud automatically in about 2 hours.

“Oracle 19c is a long-term support release from Oracle with extended support available through 2026”

https://www.kb.flashgrid.io/release-notes/cloud-provisioningFlashGrid

Oracle Golden Gate Setup on FlashGrid enabled clusters

Network Access:

  1. Setup VPC peering between sites.

2. Make sure that port UDP/4801 is open via the security group settings for each side.

Preparing the System:

  1. /etc/hosts file on each side must contain entries about other side:

10.30.0.5 rac1-hq-ext.example.com rac1-hq-ext
10.30.0.6 rac2-hq-ext.example.com rac2-hq-ext
10.30.0.4 racq-hq-ext.example.com racq-hq-ext
10.40.0.6 rac1-dr-ext.example.com rac1-dr-ext
10.40.0.5 rac2-dr-ext.example.com rac2-dr-ext
10.40.0.4 racq-dr-ext.example.com racq-dr-ext

2. On the first node of the source cluster, add entries in nodes section about target cluster in /etc/flashgrid-clan.cfg:

nodes = {‘rac1-hq’: {‘address’: ‘rac1-hq-ext’, ‘id’: 101, ‘role’: ‘database’},
‘rac2-hq’: {‘address’: ‘rac2-hq-ext’, ‘id’: 102, ‘role’: ‘database’},
‘racq-hq’: {‘address’: ‘racq-hq-ext’, ‘id’: 103, ‘role’: ‘quorum’},
‘rac1-dr’: {‘address’: ‘rac1-dr-ext’, ‘id’: 201, ‘role’: ‘database’},
‘rac2-dr’: {‘address’: ‘rac2-dr-ext’, ‘id’: 202, ‘role’: ‘database’},
‘racq-dr’: {‘address’: ‘racq-dr-ext’, ‘id’: 203, ‘role’: ‘quorum’}}

3. Modify /home/fg/.ssh/authorized_keys file on each cluster node and add public key of the other side.

4. Run the following command from the first node of the source cluster to deploy clan config:

 # flashgrid-clan-cfg deploy-config -f


Create shared folder for Golden Gate on ACFS

Create separate diskgroup called GGDG for Golden Gate software and create the ACFS-shared filesystem on it.

  1. Log on to the first node of the RAC cluster as the grid user.
  2. Create a volume:

$ sudo su – grid

$ asmcmd

ASMCMD> volcreate -G GGDG -s 10G ACFSGG

3. Determine the device name of the volume:

ASMCMD> volinfo –all

Diskgroup Name: GGDG

Volume Name: ACFSGG
Volume Device: /dev/asm/acfsgg-458
State: ENABLED
Size (MB): 10240
Resize Unit (MB): 512
Redundancy: MIRROR
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:

4. Create the filesystem on the volume:

[grid@rac1-hq ~]$ mkfs -t acfs /dev/asm/acfsgg-458

5. As the root, create an empty directory and mount ACFS volume:

# mkdir /GG_HOME
# chmod 775 /GG_HOME
# chown oracle:oinstall /GG_HOME

Setup the file system to be automounted by Clusterware:

  1. Using srvctl add filesystem

# srvctl add filesystem -device /dev/asm/acfsgg-458 -path /GG_HOME -volume acfsgg -diskgroup GGDG -user oracle -fstype ACFS -description “ACFS for Golden Gate”

2. Start filesystem service:

# srvctl start filesystem -device /dev/asm/acfsgg-458

3. Do the same steps on the target cluster for creating ACFS-shared filesystem for GG.

Installing the GoldenGate software

  1. Download and install Golden Gate software on shared ACFS mount point. Software binaries (123012_fbo_ggs_Linux_x64_shiphome.zip) can be downloaded from http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

2. Place downloaded software in /tmp/Install directory. Give Oracle Database owner the necessary permissions and install the software via oracle user:

# chown oracle:oinstall /tmp/Install/123012_fbo_ggs_Linux_x64_shiphome.zip

# su – oracle
$ cd /tmp/Install/

$ unzip 123012_fbo_ggs_Linux_x64_shiphome.zip
$ cd fbo_ggs_Linux_x64_shiphome/Disk1

$ ./runInstaller -silent -nowait -showProgress INSTALL_OPTION=ORA12c SOFTWARE_LOCATION=/GG_HOME/home_1 START_MANAGER=false MANAGER_PORT= DATABASE_LOCATION= INVENTORY_LOCATION=/u01/app/oraInventory UNIX_GROUP_NAME=oinstall

3. Do the same steps on the target cluster also.

Install Oracle Grid Infrastructure Standalone Agents

  1. Download the software from http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/index.html
  2. Place downloaded software in /tmp/Install. Give grid user necessary permissions. From the first node of the cluster run the following via grid user:

# chown grid:dba /tmp/Install/xagpack81b.zip
# su – grid
$ cd /tmp/Install
$ unzip xagpack81b.zip
$ ./xag/xagsetup.sh –install –directory /u01/app/grid/xag –all_nodes
Installing Oracle Grid Infrastructure Agents on: rac1-hq
Installing Oracle Grid Infrastructure Agents on: rac2-hq
Done.

3. Do the same steps on the target cluster also.


Create application VIP

  1. From the first node of the target cluster as root create application VIP:

# $GRID_HOME/bin/appvipcfg create -network=1 -ip=192.168.1.250 -vipname=gg_vip_dr -user=root

2. Start VIP:

# crsctl start resource gg_vip_dr

3. Allow oracle user to start the vip:

# crsctl setperm resource gg_vip_dr -u user:oracle:r-x

Application VIP is necessary on the target server only. If you plan to have dual systems and each cluster can be a target, then create VIP on each side.

XAG Registration

  1. From the first node as oracle user create Golden Gate Agent.

On source:

$ /u01/app/grid/xag/bin/agctl add goldengate gg_replicate –gg_home /GG_HOME/home_1 –instance_type source –nodes rac1-hq,rac2-hq –filesystems ora.ggdg.acfsgg.acfs –databases ora.orcl.db –oracle_home /u01/app/oracle/product/12.2.0/dbhome_1

On target:

$ /u01/app/grid/xag/bin/agctl add goldengate gg_replicate –gg_home /GG_HOME/home_1 –instance_type target –nodes rac1-dr,rac2-dr  –vip_name gg_vip_dr –filesystems ora.ggdg.acfsgg.acfs –databases ora.orcl.db –oracle_home /u01/app/oracle/product/12.2.0/dbhome_1

ArchiveLog mode setup for source database

The steps to convert database to archivelog mode:

  1. Stop database if it is running:

$ srvctl stop database -db orcl

2. Do the following from to the first node only:

SQL> startup mount;

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G;

SQL> alter system set db_recovery_file_dest=’+FRA’;

SQL> alter database archivelog;

SQL> alter database add supplemental log data;

SQL> shutdown immediate;

3. Start database

$ srvctl start database -db orcl

Schema setup on source and target

Set the following parameters on each side:

  1. Set enable_goldengate_replication parameter to true:

SQL> alter system set enable_goldengate_replication=true;

2. For integrated capture, set streams_pool_size parameter:

SQL> alter system set streams_pool_size=4G scope=spfile;

3. Create chemas on source and target databases:

SQL> create tablespace ggcw datafile ‘+DATA’ size 100m autoextend on next 5m maxsize unlimited;

SQL> create user ggcw identified by ggcw default tablespace ggcw temporary tablespace temp quota unlimited on ggcw;

SQL> create user ggcw identified by ggcw;
SQL> grant connect, resource to ggcw;
SQL> grant select any dictionary, select any table to ggcw;
SQL> grant create table to ggcw;
SQL> grant flashback any table to ggcw;
SQL> grant execute on dbms_flashback to ggcw;
SQL> grant execute on utl_file to ggcw;
SQL> grant create any table to ggcw;
SQL> grant insert any table to ggcw;
SQL> grant update any table to ggcw;
SQL> grant delete any table to ggcw;
SQL> grant drop any table to ggcw;

SQL> @/GG_HOME/home_1/marker_setup
Enter Oracle GoldenGate schema name:ggcw

SQL> @/GG_HOME/home_1/ddl_setup
Enter Oracle GoldenGate schema name:ggcw
SQL> @/GG_HOME/home_1/role_setup
Enter Oracle GoldenGate schema name:ggcw
SQL> grant ggs_ggsuser_role to ggcw;
SQL> @/GG_HOME/home_1/ddl_enable
SQL> @/GG_HOME/home_1/ddl_pin ggcw
SQL> @/GG_HOME/home_1/sequence.sql ggcw

 

Setup a basic configuration of OGG

  1. To be able to run GGSCI command create the following symbolic links in /GG_HOME/home_1 via oracle user on both sides:

$ cd /GG_HOME/home_1
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so libnnz12.so
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 libclntsh.so.12.1
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so libons.so
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 libclntshcore.so.12.1

2. Create subdirs from GGSCI command line interface on source and target:

[oracle@rac1-hq ~]$ . oraenv
ORACLE_SID = [orcl1] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
[oracle@rac1-hq ~]$ /GG_HOME/home_1/ggsci

GGSCI> create subdirs

3. Enable Supplemental logging on source database tables:

GGSCI> DBLOGIN USERID ggcw PASSWORD ggcw

GGSCI> ADD TRANDATA HR.*

4. Edit global parameters on source and target and create checkpoint table:

GGSCI> edit params ./GLOBALS

GGSCHEMA ggcw
CHECKPOINTTABLE ggcw.CKPTAB

GGSCI> ADD CHECKPOINTTABLE

5. Create manager parameter file on source and target:

GGSCI > edit params mgr

PORT 7809
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60
AUTOSTART ER *

6. Create Integrated and Classic extracts.

6.1 Integrated capture:

Make sure you have set the correct value for ORACLE_SID.

GGSCI> DBLOGIN USERID ggcw PASSWORD ggcw

GGSCI> register extract ext1 database

GGSCI> add extract ext1, integrated tranlog, begin now

GGSCI> ADD EXTTRAIL ./dirdat/rt, EXTRACT ext1, MEGABYTES 100

GGSCI> EDIT PARAMS ext1

EXTRACT ext1
USERID ggcw, PASSWORD ggcw
RMTHOST 192.168.1.250, MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE hr.emp;

Note: The RMTHOST parameter must reference the resource VIP IP of the target.

6.2 Classic capture:

6.2.1 Please apply patch for bug 27379190 before using classic capture, see Doc ID 2360874.1

6.2.2 If archivelogs are located on ASM, then need to add the following entries in $ORACLE_HOME/network/admin/tnsnames.ora:

Cluster Node 1:

ASM =
(ADDRESS=(PROTOCOL=BEQ)
(PROGRAM=/u01/app/12.2.0/grid/bin/oracle)
(ARGV0=oracle+ASM1)
(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))’)
(ENVS=’ORACLE_HOME=/u01/app/12.2.0/grid,ORACLE_SID=+ASM1′))

Cluster Node 2:

ASM =
(ADDRESS=(PROTOCOL=BEQ)
(PROGRAM=/u01/app/12.2.0/grid/bin/oracle)
(ARGV0=oracle+ASM2)
(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))’)
(ENVS=’ORACLE_HOME=/u01/app/12.2.0/grid,ORACLE_SID=+ASM2′))

6.2.3 Set the correct value for ORACLE_SID and login to the database using GGSCI command line interface:

GGSCI> DBLOGIN USERID ggcw PASSWORD ggcw

6.2.4 Add extract with number of threads:

GGSCI> ADD EXTRACT ext2, TRANLOG, THREADS 2, BEGIN NOW

GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT ext2, MEGABYTES 100

GGSCI> EDIT PARAMS ext2

EXTRACT ext2
USERID ggcw, PASSWORD ggcw
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD MyPassword2017
RMTHOST 192.168.1.250, MGRPORT 7809
RMTTRAIL ./dirdat/et
TABLE hr.sal;

6.2.5 Create replicats on target :

GGSCI> DBLOGIN USERID ggcw, PASSWORD ggcw

GGSCI> ADD REPLICAT rep1, EXTTRAIL ./dirdat/rt

GGSCI> ADD REPLICAT rep2, EXTTRAIL ./dirdat/et

At this point if the ADD REPLICAT command fails with the following error “RROR: No checkpoint table specified for ADD REPLICAT” simply exit that GGSCI session and then start another one before issuing ADD REPLICAT. The ADD REPLICAT command fails if issued from the same session where the GLOBALS file was created.

GGSCI> EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggcw, PASSWORD ggcw
MAP hr.emp, TARGET hr.emp;

GGSCI> EDIT PARAMS rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggcw, PASSWORD ggcw
MAP hr.sal, TARGET hr.sal;

7. Start golden gate on source and target servers via oracle user:

$ /u01/app/grid/xag/bin/agctl start goldengate gg_replicate