Golden Gate installation for MySQL

 

Topic: Golden Gate installation for MySQL  

 

Oracle Goldengate is used for real-time, heterogeneous data integration and replication, enabling near-zero downtime data migrations, high availability for enterprise systems, real-time data ingestion into big data platforms, and continuous data transformations and streaming data pipelines. It captures committed transactions in real-time from operational systems and delivers them to analytical systems, data lakes, and data warehouses across various platforms and databases, including Oracle, non-Oracle, and NoSQL databases.

Deployment Diagram:

 

Infrastructure Details:

Oracle DB Details
OS version	Red Hat Enterprise Linux release 7.6 (Maipo)
MySQL Version	8.0.43-commercial
GG Version	Version 19.1.0.0.0
Host Name	node01.example.com

 

In this QuickStart, we learn:

 

·         Validate MySQL Version

·         Download and stage Goldengate to MySQL Server

·         Create a directory for GG

·         Install and Configure GG

·         Configure the Manager Process and start it  

 

 

Step-1: Validate MySQL Version

 

 

Step-2: Download and stage Goldengate to MySQL Server

 

[root@Node01 tmp]# ls -ltr GG_19.1.0.0.0_MySQL_Linux_x86-64.zip

-rw-r--r--. 1 root root 70098487 Aug 27 12:55 GG_19.1.0.0.0_MySQL_Linux_x86-64.zip

[root@Node01 tmp]#

[root@Node01]# cd /tmp

[root@Node01 tmp]# unzip -q GG_19.1.0.0.0_MySQL_Linux_x86-64.zip

 

Step-3: Create a directory for GG

 

[root@Node01 tmp]# mkdir -p /u01/app/gg/product/19c

[root@Node01 tmp]# chown mysql:mysql -R /u01/app/gg

 

Step-4: Install and Configure GG

 

[root@Node01 tmp]# su – mysql

-bash-4.2$ cd /tmp

-bash-4.2$tar -xvf

ggs_Linux_x64_MySQL_64bit.tar -C /u01/app/gg/product/19c

-bash-4.2$ cd /u01/app/gg/product/19c/

 

Step-5: Configure the Manager Process and start it  

 

GGSCI (Node01.example.com) 2> edit param mgr

 

 

Configure TCP/IP with SSL and TLS for Database Connections

Topic: Configure TCP/IP with SSL and TLS for Database Connections

 

why do we need to configure the TLS connection in any database?

For more secure connections, you can enable Transport Layer Security (TLS) support on the MySQL server and configure your clients to establish encrypted connections.

 

Deployment Diagram:

In this QuickStart, we learn:

 

·         Stop the MySQL service

·         Set the ssl=on in the MySQL config file my.cnf

·         Start the MySQL Service

·         Validate the SSL mode

·         Check the connection status

 

Step-1 Stop the MySQL service

[root@node01 ~]# systemctl status mysqld   

[root@node01 ~]# systemctl stop  mysqld

[root@node01 ~]# systemctl status mysqld

 

 

Step-2 Set the ssl=on and cipher in the MySQL config file my.cnf

[root@node01 ~]# cat /etc/my.cnf | grep -i ssl

ssl=on

[mysql@node01 ~]$ cat /etc/my.cnf | grep -i Cipher

tls_ciphersuites=TLS_AES_256_GCM_SHA384

[mysql@node01 ~]$

 

Step-3 Start the MySQL Service

 

[root@node01 ~]# systemctl start mysqld

[root@node01 ~]# systemctl status mysqld

 

 

Step-4 Validate the SSL mode

 

[mysql@node01 ~]$ mysql -u root -p

mysql> show global variables like 'have_%ssl';

mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';

 

 

Step-5 Check the connection status

 

-   Using the MySQL command line

 

[mysql@node01 ~]$ mysql -u test04 -p --protocol=TCP

mysql> status;

 

-   Mysql connection validation using Mysql workbench

 

SELECT

    t.THREAD_ID,

    t.PROCESSLIST_USER,

    t.PROCESSLIST_HOST,

    t.CONNECTION_TYPE,

    sbt.VARIABLE_VALUE AS cipher

FROM

    performance_schema.threads t

LEFT JOIN

    performance_schema.status_by_thread sbt

    ON (t.THREAD_ID = sbt.THREAD_ID AND sbt.VARIABLE_NAME = 'Ssl_cipher')

WHERE

    t.PROCESSLIST_USER IS NOT NULL;

 

Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

 

Error: 

[MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

 [MY-011302] [Server] Plugin mysqlx reported: 'Failed at SSL configuration: "SSL context is not usable without certificate and private key"'

Solution: 

Copy all certificates in the MySQL datadir directory location. and give the permissions as per below:

chmod 600 ca-key.pem
chmod 644 ca.pem
chmod 600 server-key.pem
chmod 644 server-cert.pem
chmod 600 client-key.pem
chmod 644 client-cert.pem
chmod 600 private_key.pem

chmod 644 public_key.pem

PRKO-2012 : filesystem object is not supported in Oracle Restart

 

Subject:      ACFS not supported in Oracle Restart on 19c 

Error: 

 [oracle@asm-node01 ~]$ srvctl status filesystem -d /dev/asm/ggdisk-457

Usage: srvctl <command> <object> [<options>]

    commands: enable|disable|start|stop|status|add|remove|modify|update|getenv|setenv|unsetenv|config|upgrade|downgrade

    objects: database|service|asm|diskgroup|listener|home|ons|oraclehome

For detailed help on each command and object and its options use:

  srvctl <command> -help [-compatible] or

  srvctl <command> <object> -help [-compatible]

PRKO-2012 : filesystem object is not supported in Oracle Restart

Solution; 

[root@asm-node01 dev]# /bin/mount -t acfs /dev/asm/ggdisk-457 /u01/app/GG

Note: You can add this script to the root crontab after reboot. ACFS will mount the given directory 

acfsutil version: CLSU-00107: operating system function: open64; failed with error data: 2; at location: OOF_1

 

Error: 

[grid@asm-node01 ~]$ acfsutil version

acfsutil version: CLSU-00107: operating system function: open64; failed with error data: 2; at location: OOF_1

acfsutil version: CLSU-00101: operating system error message: No such file or directory

acfsutil version: CLSU-00104: additional error information: open64 (/dev/ofsctl)

acfsutil version: ACFS-00502: Failed to communicate with the ACFS driver.  Verify the ACFS driver has been loaded.

Solution: 

[root@asm-node01 init.d]# cd /u01/app/19.0.0/grid/bin

[root@asm-node01 bin]# ./acfsroot install

ACFS-9300: ADVM/ACFS distribution files found.

ACFS-9314: Removing previous ADVM/ACFS installation.

Use of uninitialized value $sub_command in string eq at /u01/app/19.0.0/grid/lib/osds_acfsroot.pm line 1749.

ACFS-9315: Previous ADVM/ACFS components successfully removed.

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9307: Installing requested ADVM/ACFS software.

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9308: Loading installed ADVM/ACFS drivers.

ACFS-9298: Installing SELinux policy for ACFS.

ACFS-9297: Creating ACFS SELinux policy file /usr/share/oracleacfs/acfs.cil.

ACFS-9321: Creating udev for ADVM/ACFS.

ACFS-9323: Creating module dependencies - this may take some time.

ACFS-9154: Loading 'oracleoks.ko' driver.

ACFS-9154: Loading 'oracleadvm.ko' driver.

ACFS-9154: Loading 'oracleacfs.ko' driver.

ACFS-9327: Verifying ADVM/ACFS devices.

ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.

ACFS-9156: Detecting control device '/dev/ofsctl'.

ACFS-9309: ADVM/ACFS installation correctness verified.

[root@asm-node01 bin]# ./acfsroot install -v

ACFS-9505: Using acfsutil executable from location: '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfsutil'

ACFS-9300: ADVM/ACFS distribution files found.

ACFS-9314: Removing previous ADVM/ACFS installation.

Use of uninitialized value $sub_command in string eq at /u01/app/19.0.0/grid/lib/osds_acfsroot.pm line 1749.

ACFS-9315: Previous ADVM/ACFS components successfully removed.

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9307: Installing requested ADVM/ACFS software.

ACFS-9503: ADVM and ACFS driver media location is '/u01/app/19.0.0/grid/usm/install/Oracle/EL8UEK/x86_64/5.4.17-2011.0.7/5.4.17-2011.0.7-x86_64/bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/Oracle/EL8UEK/x86_64/5.4.17-2011.0.7/5.4.17-2011.0.7-x86_64/bin/oracleadvm.ko' to the path '/lib/modules/5.4.17-2136.307.3.1.el8uek.x86_64/extra/usm/oracleadvm.ko'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/Oracle/EL8UEK/x86_64/5.4.17-2011.0.7/5.4.17-2011.0.7-x86_64/bin/oracleoks.ko' to the path '/lib/modules/5.4.17-2136.307.3.1.el8uek.x86_64/extra/usm/oracleoks.ko'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/Oracle/EL8UEK/x86_64/5.4.17-2011.0.7/5.4.17-2011.0.7-x86_64/bin/oracleacfs.ko' to the path '/lib/modules/5.4.17-2136.307.3.1.el8uek.x86_64/extra/usm/oracleacfs.ko'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/advmutil' to the path '/sbin/advmutil'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/advmutil.bin' to the path '/sbin/advmutil.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/fsck.acfs' to the path '/sbin/fsck.acfs'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/fsck.acfs.bin' to the path '/sbin/fsck.acfs.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/mkfs.acfs' to the path '/sbin/mkfs.acfs'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/mkfs.acfs.bin' to the path '/sbin/mkfs.acfs.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/mount.acfs' to the path '/sbin/mount.acfs'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/mount.acfs.bin' to the path '/sbin/mount.acfs.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfsdbg' to the path '/sbin/acfsdbg'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfsdbg.bin' to the path '/sbin/acfsdbg.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfsutil' to the path '/sbin/acfsutil'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfsutil.bin' to the path '/sbin/acfsutil.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/umount.acfs' to the path '/sbin/umount.acfs'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/umount.acfs.bin' to the path '/sbin/umount.acfs.bin'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/usm/install/cmds/bin/acfssihamount' to the path '/etc/init.d/acfssihamount'

ACFS-9504: Copying file '/u01/app/19.0.0/grid/lib/libacfs19.so' to the path '/opt/oracle/extapi/64/acfs/orcl/1/'

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9308: Loading installed ADVM/ACFS drivers.

ACFS-9298: Installing SELinux policy for ACFS.

ACFS-9297: Creating ACFS SELinux policy file /usr/share/oracleacfs/acfs.cil.

ACFS-9321: Creating udev for ADVM/ACFS.

ACFS-9323: Creating module dependencies - this may take some time.

ACFS-9154: Loading 'oracleoks.ko' driver.

ACFS-9154: Loading 'oracleadvm.ko' driver.

ACFS-9154: Loading 'oracleacfs.ko' driver.

ACFS-9327: Verifying ADVM/ACFS devices.

ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.

ACFS-9156: Detecting control device '/dev/ofsctl'.

ACFS-9309: ADVM/ACFS installation correctness verified.

[root@asm-node01 bin]#

make: perl: Command not found make: *** [ins_rdbms.mk:573: javavm_refresh] Error 127 '

 Error: 

Patching component oracle.javavm.client, 19.0.0.0.0...

Make failed to invoke "/usr/bin/make -f ins_rdbms.mk javavm_refresh ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1 OPATCH_SESSION=napply"....'make: perl: Command not found

make: *** [ins_rdbms.mk:573: javavm_refresh] Error 127

Solution: 

check perl install on you system or not 

[root@asm-node01 ~]# dnf/yum/rpm  list installed | grep perl

[root@asm-node01 ~]#

[root@asm-node01 ~]# dnf/yum install perl

Note: as per the lmux version use yum or DNF to install Perl package 

[grid@asm-node01 37262208]$ export PATH=$ORACLE_HOME/perl/bin:$PATH

[grid@asm-node01 37262208]$ export PERL5LIB=$ORACLE_HOME/perl/lib

ORA-12547: TNS:lost contact CRS-2675: Stop of 'ora.***.db' on '01' failed

 Issue: 

[oracle@asm-node01 ~]$ srvctl stop database -db DB19c

PRCD-1334 : failed to stop database DB19c

PRCD-1124 : Failed to stop database DB19c and its services

PRCD-1131 : Failed to stop database DB19c and its services on nodes

PRCR-1133 : Failed to stop database DB19c and its running services

PRCR-1132 : Failed to stop resources using a filter

ORA-12547: TNS:lost contact

CRS-2675: Stop of 'ora.db19c.db' on 'asm-node01' failed

Solution: 

[oracle@asm-node01 ~]$ cd $ORACLE_HOME/bin

[oracle@asm-node01 bin]$ ls -ltr oracle

-rwxrwxr-x. 1 oracle asmadmin 441173504 Jun 25 16:55 oracle

[oracle@asm-node01 bin]$ chmod 6751 oracle

[oracle@asm-node01 bin]$ ls -ltr oracle

-rwsr-x--x. 1 oracle asmadmin 441173504 Jun 25 16:55 oracle

--> Stop the database service using SRVCTL 

[oracle@asm-node01 ~]$ srvctl stop database -db DB19c

[oracle@asm-node01 ~]$ srvctl statUS database -db DB19C