MySQL DB Creation using rpm method not using repo

Steps to perform fresh install of MySQL in CentOS (applicable to RHEL or Oracle Linux as well) x64

1) Collect the basic details as to what are you trying to do?

a. Install MySQL?

Ans: Yes
b. With vendor support or community edition?
Ans: Community edition
c. Which version?
Ans: 8.0.x
d. Which platform?
Ans: Centos 7.x x64 (RHEL or OEL)
e. Is the candidate MySQL under support now and is supported in the candidate platform?
Ans: Yes
f. How do you want to install?
Ans: rpm

Related url: mysql supported platform and versions

2) Based on the option chosen:

a. rpm based install
1. Download the rpms from (by choosing the corresponding options) https://dev.mysql.com/downloads/mysql/
b. Transfer the rpms thus downloaded to the server (either do a winscp or share it via NAS)
c. Decompress and untar the rpm files
d. Install the rpms as shown below...

Prereqs: libaio

sudo yum install mysql-community-{server,client,client-plugins,icu-data-files,common,libs}-*

note: if installing as root, skip sudo command.

Actual Install log:

root@vcentos79-postgres-1:/tmp/mysql-rpm-bin[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr
total 2015240
-rw-r--r--. 1 7155 31415   16755300 Jun 25 04:06 mysql-community-client-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    3745824 Jun 25 04:06 mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415     681724 Jun 25 04:06 mysql-community-common-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  528347988 Jun 25 04:07 mysql-community-debuginfo-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    1947800 Jun 25 04:07 mysql-community-devel-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    4217912 Jun 25 04:07 mysql-community-embedded-compat-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    2344364 Jun 25 04:07 mysql-community-icu-data-files-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    1563264 Jun 25 04:07 mysql-community-libs-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415     685312 Jun 25 04:08 mysql-community-libs-compat-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   67410132 Jun 25 04:08 mysql-community-server-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   25637968 Jun 25 04:08 mysql-community-server-debug-8.0.34-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  378442676 Jun 25 04:10 mysql-community-test-8.0.34-1.el7.x86_64.rpm
-rwxr-xr-x. 1 root root  1031792640 Aug 20 13:10 mysql-8.0.34-1.el7.x86_64.rpm-bundle.tar
drwxrwxrwt. 8 root root         149 Aug 20 13:11 ..
drwxr-xr-x. 2 root root        4096 Aug 20 13:28 .

root@vcentos79-postgres-1:/tmp/mysql-rpm-bin[root@vcentos79-postgres-1 mysql-rpm-bin]# yum install mysql-community-{server,client,client-plugins,icu-data-files,common,libs}-*

Loaded plugins: fastestmirror
Examining mysql-community-server-8.0.34-1.el7.x86_64.rpm: mysql-community-server-8.0.34-1.el7.x86_64
Marking mysql-community-server-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-server-debug-8.0.34-1.el7.x86_64.rpm: mysql-community-server-debug-8.0.34-1.el7.x86_64
Marking mysql-community-server-debug-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-client-8.0.34-1.el7.x86_64.rpm: mysql-community-client-8.0.34-1.el7.x86_64
Marking mysql-community-client-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm: mysql-community-client-plugins-8.0.34-1.el7.x86_64
Marking mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm: mysql-community-client-plugins-8.0.34-1.el7.x86_64
Marking mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-icu-data-files-8.0.34-1.el7.x86_64.rpm: mysql-community-icu-data-files-8.0.34-1.el7.x86_64
Marking mysql-community-icu-data-files-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-common-8.0.34-1.el7.x86_64.rpm: mysql-community-common-8.0.34-1.el7.x86_64
Marking mysql-community-common-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-8.0.34-1.el7.x86_64.rpm: mysql-community-libs-8.0.34-1.el7.x86_64
Marking mysql-community-libs-8.0.34-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-compat-8.0.34-1.el7.x86_64.rpm: mysql-community-libs-compat-8.0.34-1.el7.x86_64
Marking mysql-community-libs-compat-8.0.34-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted
---> Package mysql-community-client.x86_64 0:8.0.34-1.el7 will be installed
---> Package mysql-community-client-plugins.x86_64 0:8.0.34-1.el7 will be installed
---> Package mysql-community-common.x86_64 0:8.0.34-1.el7 will be installed
---> Package mysql-community-icu-data-files.x86_64 0:8.0.34-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.34-1.el7 will be obsoleting
---> Package mysql-community-libs-compat.x86_64 0:8.0.34-1.el7 will be obsoleting
---> Package mysql-community-server.x86_64 0:8.0.34-1.el7 will be installed
--> Processing Dependency: net-tools for package: mysql-community-server-8.0.34-1.el7.x86_64
Determining fastest mirrors
..
---> Package mysql-community-server-debug.x86_64 0:8.0.34-1.el7 will be installed
--> Running transaction check
---> Package net-tools.x86_64 0:2.0-0.25.20131004git.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================
 Package                        Arch   Version                  Repository                                           Size
==========================================================================================================================
Installing:
 mysql-community-client(B         x86_64 8.0.34-1.el7             /mysql-community-client-8.0.34-1.el7.x86_64          79 M
 mysql-community-client-plugins(B x86_64 8.0.34-1.el7             /mysql-community-client-plugins-8.0.34-1.el7.x86_64  20 M
 mysql-community-common(B         x86_64 8.0.34-1.el7             /mysql-community-common-8.0.34-1.el7.x86_64          10 M
 mysql-community-icu-data-files(B x86_64 8.0.34-1.el7             /mysql-community-icu-data-files-8.0.34-1.el7.x86_64 3.9 M
 mysql-community-libs(B           x86_64 8.0.34-1.el7             /mysql-community-libs-8.0.34-1.el7.x86_64           7.5 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-libs-compat(B    x86_64 8.0.34-1.el7             /mysql-community-libs-compat-8.0.34-1.el7.x86_64    3.6 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-server(B         x86_64 8.0.34-1.el7             /mysql-community-server-8.0.34-1.el7.x86_64         295 M
 mysql-community-server-debug(B   x86_64 8.0.34-1.el7             /mysql-community-server-debug-8.0.34-1.el7.x86_64   120 M
Installing for dependencies:
 net-tools                      x86_64 2.0-0.25.20131004git.el7 base                                                306 k
Transaction Summary
==========================================================================================================================
Install  8 Packages (+1 Dependent package)
Total size: 540 M
Total download size: 306 k
Is this ok [y/d/N]: y
Downloading packages:
net-tools-2.0-0.25.20131004git.el7.x86_64.rpm  0% [                                     ]  0.0 B/s |    0 B  --:--:-- ETA 
net-tools-2.0-0.25.20131004git.el7.x86_64.rpm                                                      | 306 kB  00:00:00       Erasing    : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                   10/10 
  Verifying  : mysql-community-server-8.0.34-1.el7.x86_64                                                            1/10 
  Verifying  : mysql-community-client-plugins-8.0.34-1.el7.x86_64                                                    2/10 
  Verifying  : mysql-community-server-debug-8.0.34-1.el7.x86_64                                                      3/10 
  Verifying  : mysql-community-icu-data-files-8.0.34-1.el7.x86_64                                                    4/10 
  Verifying  : net-tools-2.0-0.25.20131004git.el7.x86_64                                                             5/10 
  Verifying  : mysql-community-common-8.0.34-1.el7.x86_64                                                            6/10 
  Verifying  : mysql-community-libs-8.0.34-1.el7.x86_64                                                              7/10 
  Verifying  : mysql-community-libs-compat-8.0.34-1.el7.x86_64                                                       8/10 
  Verifying  : mysql-community-client-8.0.34-1.el7.x86_64                                                            9/10 
  Verifying  : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                   10/10 
Installed:
  mysql-community-client.x86_64 0:8.0.34-1.el7            mysql-community-client-plugins.x86_64 0:8.0.34-1.el7           
  mysql-community-common.x86_64 0:8.0.34-1.el7            mysql-community-icu-data-files.x86_64 0:8.0.34-1.el7           
  mysql-community-libs.x86_64 0:8.0.34-1.el7              mysql-community-libs-compat.x86_64 0:8.0.34-1.el7              
  mysql-community-server.x86_64 0:8.0.34-1.el7            mysql-community-server-debug.x86_64 0:8.0.34-1.el7             
Dependency Installed:
  net-tools.x86_64 0:2.0-0.25.20131004git.el7                                                                             
Replaced:
  mariadb-libs.x86_64 1:5.5.68-1.el7                                                                                      
Complete!
]0;root@vcentos79-postgres-1:

Post install validation:

[root@vcentos79-postgres-1 mysql-rpm-bin]# rpm -qa|grep -i libaio
libaio-0.3.109-13.el7.x86_64

[root@vcentos79-postgres-1 mysql-rpm-bin]# rpm -qa|grep -i mysql
mysql-community-common-8.0.34-1.el7.x86_64
mysql-community-libs-compat-8.0.34-1.el7.x86_64
mysql-community-client-plugins-8.0.34-1.el7.x86_64
mysql-community-client-8.0.34-1.el7.x86_64
mysql-community-icu-data-files-8.0.34-1.el7.x86_64
mysql-community-server-debug-8.0.34-1.el7.x86_64
mysql-community-libs-8.0.34-1.el7.x86_64
mysql-community-server-8.0.34-1.el7.x86_64
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

[root@vcentos79-postgres-1 mysql-rpm-bin]# grep -i mysql /etc/passwd
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false <<< notice the account mysql cant login. Since it is only a user account to keep up with binary permission.
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/bin|tail
-rwxr-xr-x.  1 root root   7739568 Jun 22 13:51 mysql
-rwxr-xr-x.  1 root root   6537048 Jun 22 13:51 my_print_defaults
-rwxr-xr-x.  1 root root   6841208 Jun 22 13:51 myisampack
-rwxr-xr-x.  1 root root   6771432 Jun 22 13:51 myisamlog
-rwxr-xr-x.  1 root root   6823752 Jun 22 13:51 myisam_ftdump
-rwxr-xr-x.  1 root root   6932360 Jun 22 13:51 myisamchk
-rwxr-xr-x.  1 root root   6583552 Jun 22 13:51 lz4_decompress
-rwxr-xr-x.  1 root root   6696224 Jun 22 13:51 innochecksum
-rwxr-xr-x.  1 root root   6779904 Jun 22 13:51 ibd2sdi
dr-xr-xr-x.  2 root root     24576 Aug 20 13:34 .

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/bin/my*
-rwxr-xr-x. 1 root root    7669 Jun 22 13:22 /usr/bin/mysqldumpslow
-rwxr-xr-x. 1 root root    4368 Jun 22 13:22 /usr/bin/mysqld_pre_systemd
-rwxr-xr-x. 1 root root 7539888 Jun 22 13:51 /usr/bin/mysql_upgrade
-rwxr-xr-x. 1 root root 6471328 Jun 22 13:51 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x. 1 root root 6603920 Jun 22 13:51 /usr/bin/mysql_ssl_rsa_setup
-rwxr-xr-x. 1 root root 7444280 Jun 22 13:51 /usr/bin/mysqlslap
-rwxr-xr-x. 1 root root 7424792 Jun 22 13:51 /usr/bin/mysqlshow
-rwxr-xr-x. 1 root root 7414056 Jun 22 13:51 /usr/bin/mysql_secure_installation
-rwxr-xr-x. 1 root root 8026832 Jun 22 13:51 /usr/bin/mysqlpump
-rwxr-xr-x. 1 root root 7510368 Jun 22 13:51 /usr/bin/mysql_migrate_keyring
-rwxr-xr-x. 1 root root 7422208 Jun 22 13:51 /usr/bin/mysqlimport
-rwxr-xr-x. 1 root root 7507440 Jun 22 13:51 /usr/bin/mysqldump
-rwxr-xr-x. 1 root root 6551848 Jun 22 13:51 /usr/bin/mysql_config_editor
-rwxr-xr-x. 1 root root 7439304 Jun 22 13:51 /usr/bin/mysqlcheck
-rwxr-xr-x. 1 root root 7891984 Jun 22 13:51 /usr/bin/mysqlbinlog
-rwxr-xr-x. 1 root root 7429928 Jun 22 13:51 /usr/bin/mysqladmin
-rwxr-xr-x. 1 root root 7739568 Jun 22 13:51 /usr/bin/mysql
-rwxr-xr-x. 1 root root 6537048 Jun 22 13:51 /usr/bin/my_print_defaults
-rwxr-xr-x. 1 root root 6841208 Jun 22 13:51 /usr/bin/myisampack
-rwxr-xr-x. 1 root root 6771432 Jun 22 13:51 /usr/bin/myisamlog
-rwxr-xr-x. 1 root root 6823752 Jun 22 13:51 /usr/bin/myisam_ftdump
-rwxr-xr-x. 1 root root 6932360 Jun 22 13:51 /usr/bin/myisamchk

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/bin/mysql
-rwxr-xr-x. 1 root root 7739568 Jun 22 13:51 /usr/bin/mysql

[root@vcentos79-postgres-1 mysql-rpm-bin]# /usr/bin/mysqladmin --version
/usr/bin/mysqladmin  Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/sbin|grep -i mysql
-rwxr-xr-x.  1 root root     112425080 Jun 22 13:51 mysqld-debug
-rwxr-xr-x.  1 root root      64679040 Jun 22 13:51 mysqld

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /etc/my.cnf
-rw-r--r--. 1 root root 1243 Jun 22 13:44 /etc/my.cnf

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -ld /var/lib/mysql
drwxr-x--x. 2 mysql mysql 6 Jun 22 13:43 /var/lib/mysql

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/lib/mysql
total 4
drwxr-x--x.  2 mysql mysql    6 Jun 22 13:43 .
drwxr-xr-x. 28 root  root  4096 Aug 20 13:34 ..

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/log/mysqld.log
-rw-r-----. 1 mysql mysql 0 Aug 20 13:34 /var/log/mysqld.log

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/lib|tail
drwxr-xr-x.  2 root    root      76 Mar 19 23:00 VBoxGuestAdditions
drwxr-xr-x.  2 root    root      30 Mar 20 20:35 logrotate
drwxr-x---.  2 mysql   mysql      6 Jun 22 13:43 mysql-keyring
drwxr-x---.  2 mysql   mysql      6 Jun 22 13:43 mysql-files
drwxr-x--x.  2 mysql   mysql      6 Jun 22 13:43 mysql
drwx------.  2 root    root    4096 Aug 20 12:51 NetworkManager
drwxr-xr-x.  2 root    root    4096 Aug 20 13:33 rpm
drwx------.  2 root    root      29 Aug 20 13:34 rsyslog
drwxr-xr-x. 28 root    root    4096 Aug 20 13:34 .
drwxr-xr-x.  6 root    root      80 Aug 20 13:34 yum

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /etc/init.d/mysqld
ls: cannot access /etc/init.d/mysqld: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /etc/init.d/mysql
ls: cannot access /etc/init.d/mysql: No such file or directory
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/run/mysql/mysqld.pid
ls: cannot access /var/run/mysql/mysqld.pid: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/run/mysql/mysql.sock
ls: cannot access /var/run/mysql/mysql.sock: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/include/mysql
ls: cannot access /usr/include/mysql: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/libs/mysql
ls: cannot access /usr/libs/mysql: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/lib/mysql
ls: cannot access /usr/lib/mysql: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /usr/share/mysql
ls: cannot access /usr/share/mysql: No such file or directory

e. Start the mysql server

[this should initialize the data directory,An SSL certificate and key files are generated in the data directory, validate_password is installed and enabled]

systemctl start mysqld

Actual startup log:

[root@vcentos79-postgres-1 mysql-rpm-bin]# systemctl start mysqld
[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/lib/mysql
total 90576
drwxr-xr-x. 28 root  root      4096 Aug 20 13:34 ..
-rw-r-----.  1 mysql mysql       56 Aug 20 13:47 auto.cnf
-rw-r-----.  1 mysql mysql  8585216 Aug 20 13:47 #ib_16384_1.dblwr
drwxr-x---.  2 mysql mysql     8192 Aug 20 13:47 performance_schema
-rw-------.  1 mysql mysql     1676 Aug 20 13:47 ca-key.pem
-rw-r--r--.  1 mysql mysql     1112 Aug 20 13:47 ca.pem
-rw-------.  1 mysql mysql     1676 Aug 20 13:47 server-key.pem
-rw-r--r--.  1 mysql mysql     1112 Aug 20 13:47 server-cert.pem
-rw-------.  1 mysql mysql     1676 Aug 20 13:47 client-key.pem
-rw-r--r--.  1 mysql mysql     1112 Aug 20 13:47 client-cert.pem
-rw-------.  1 mysql mysql     1676 Aug 20 13:47 private_key.pem
-rw-r--r--.  1 mysql mysql      452 Aug 20 13:47 public_key.pem
drwxr-x---.  2 mysql mysql      143 Aug 20 13:47 mysql
drwxr-x---.  2 mysql mysql       28 Aug 20 13:47 sys
-rw-r-----.  1 mysql mysql     5632 Aug 20 13:47 ib_buffer_pool
drwxr-x---.  2 mysql mysql      187 Aug 20 13:47 #innodb_temp
-rw-r-----.  1 mysql mysql       16 Aug 20 13:47 binlog.index
-rw-r-----.  1 mysql mysql      157 Aug 20 13:47 binlog.000001
-rw-------.  1 mysql mysql        6 Aug 20 13:47 mysql.sock.lock
srwxrwxrwx.  1 mysql mysql        0 Aug 20 13:47 mysql.sock
drwxr-x--x.  7 mysql mysql     4096 Aug 20 13:47 .
drwxr-x---.  2 mysql mysql     4096 Aug 20 13:47 #innodb_redo
-rw-r-----.  1 mysql mysql 12582912 Aug 20 13:47 ibtmp1
-rw-r-----.  1 mysql mysql 12582912 Aug 20 13:47 ibdata1
-rw-r-----.  1 mysql mysql 16777216 Aug 20 13:47 undo_001
-rw-r-----.  1 mysql mysql 16777216 Aug 20 13:47 undo_002
-rw-r-----.  1 mysql mysql   196608 Aug 20 13:47 #ib_16384_0.dblwr
-rw-r-----.  1 mysql mysql 25165824 Aug 20 13:47 mysql.ibd

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/lib/mysql/mysql.pid
ls: cannot access /var/lib/mysql/mysql.pid: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/lib/mysql/mysqld.pid
ls: cannot access /var/lib/mysql/mysqld.pid: No such file or directory

[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/run/mysql/mysqld.pid
ls: cannot access /var/run/mysql/mysqld.pid: No such file or directory
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]#
[root@vcentos79-postgres-1 mysql-rpm-bin]#
[root@vcentos79-postgres-1 mysql-rpm-bin]#
[root@vcentos79-postgres-1 mysql-rpm-bin]# ls -altr /var/run/mysql/mysqld.pid
ls: cannot access /var/run/mysql/mysqld.pid: No such file or directory
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]#
[root@vcentos79-postgres-1 mysql-rpm-bin]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-08-20 13:47:12 BST; 2min 29s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 10711 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 10777 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─10777 /usr/sbin/mysqld
Aug 20 13:47:04 vcentos79-postgres-1 systemd[1]: Starting MySQL Server...
Aug 20 13:47:12 vcentos79-postgres-1 systemd[1]: Started MySQL Server.
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]#
[root@vcentos79-postgres-1 mysql-rpm-bin]# view /var/log/mysqld.log
[root@vcentos79-postgres-1 mysql-rpm-bin]#

[root@vcentos79-postgres-1 mysql-rpm-bin]# netstat -plantu|grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN      10777/mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      10777/mysqld
[root@vcentos79-postgres-1 mysql-rpm-bin]#

mysql default port: 3306

f. Change mysql user root password [not linux root]

sudo grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p << password should be expired, so change it.
store the new password safely.

Actual output:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
Actual password update routine:
[root@vcentos79-postgres-1 mysql-rpm-bin]# grep 'temporary password' /var/log/mysqld.log
2023-08-20T12:47:07.425831Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: u(bZs7GNQW&0

[root@vcentos79-postgres-1 mysql-rpm-bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

[root@vcentos79-postgres-1 mysql-rpm-bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@vcentos79-postgres-1 mysql-rpm-bin]#

g. Retrieve the deployment information and store it for future use

Refer below for the deployment info:

Video Tutorial of the blog content:

Thank you!

PostgreSQL: Schema Copy from one DB to other

Objective: Perform schema copy in postgresql from one db to other

Method: pg_dump/pg_restore

Backup dump destination: /pgwal/logicalbkp (4GB space left)

pg version: 14.7 (source) | 15.2 (target)

PGDATA: /pgdata/14/data (14.7) | /pgdata/15/data (15.2)

Schema: test

DB Name: pgbenchdb

option to use: -n, --schema=PATTERN         dump the specified schema(s) only

Steps:

1) Perform a backup of the schema (5432 port)

Command:

\dn+

\dt test.*

select * from test.table1;

output:

postgres=# \c pgbenchdb

psql (15.2, server 14.7)

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         |

 test   | postgres |                      |

(2 rows)

pgbenchdb=# \dt test.*

        List of relations

 Schema | Name | Type  |  Owner

--------+------+-------+----------

 test   | tbl1 | table | postgres

(1 row)

pgbenchdb=#

Backup Command:

pg_dump \

-d pgbenchdb \

-U postgres \

-p 5432 \

-n test \

-v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

Backup Command Output:

-bash-4.2$ pg_dump \

> -d pgbenchdb \

> -U postgres \

> -p 5432 \

> -n test \

> -v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$

2) Verify the backup for any errors

ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

Output:

-bash-4.2$ ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-rw-r--r--. 1 postgres postgres 1222 Aug 16 22:31 /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-bash-4.2$

3) Perform a restore of the schema in target (same server - 5433 port)

Before restore:

postgres=# \c pgbenchdb

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description       

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         | 

(1 row)

Restore Command:

psql -p 5433 \

-d pgbenchdb \

-U postgres \

-f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \

-L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

Restore Command Output:

-bash-4.2$ psql -p 5433 \

> -d pgbenchdb \

> -U postgres \

> -f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \

> -L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

SET

SET

SET

SET

SET

 set_config

------------

(1 row)

SET

SET

SET

SET

CREATE SCHEMA

ALTER SCHEMA

SET

SET

CREATE TABLE

ALTER TABLE

COPY 4

4) Validate for any error

grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

Output:

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

SET client_min_messages = warning;

-bash-4.2$

5) Verify the table accessibility in target:

Command:

\dn+

\dt test.*

select * from test.table1;

Output:

postgres=# \c pgbenchdb

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         |

 test   | postgres |                      |

(2 rows)

pgbenchdb=# \dt test.*

        List of relations

 Schema | Name | Type  |  Owner

--------+------+-------+----------

 test   | tbl1 | table | postgres

(1 row)

pgbenchdb=# select * from test.tbl1;

 id | name

----+------

  1 | R

  1 | R

  1 | R

  1 | R

(4 rows)

pgbenchdb=#

YouTube Video of the same:

Oracle RDBMS: Perform an Online Incremental level 0 backup

In this blog we will see how to perform an online incremental level 0 backup

1) Ensure to collect minimum information on what our target for this operation

DB Version: 12.2.0.1
DB name: GGSRC03T
DB size: 1551 MB
Mountpath: /oraarch [this is the default path, since I have my db_recovery_file_dest set to this path]
Usage of encryption walltet or keystore: Yes

Actual output:
[oracle@vcentos79-oracle-ggsrc GGSRC03T]$ sqlplus '/as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 12 12:55:02 2023
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
GGSRC03T  READ WRITE
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
--------------------
                1551
SQL> set lines 300
SQL> set pages 3000
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------    -----------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC03T/wallet/
OPEN                           AUTOLOGIN            SINGLE    YES                0

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@vcentos79-oracle-ggsrc GGSRC03T]$

1) Frame RMAN incremental backup command

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level 0 database format '/oraarch/GGSRC03T/DBBackup/rman_lvl0_%d_%U' plus archivelog format '/oraarch/GGSRC03T/DBBackup/rman_arch_%d_%U';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}


2) Verify the syntax of the backup command if this is framed newly

rman checksyntax

>>> Paste the above syntax into rman prompt

Actual output:
[oracle@vcentos79-oracle-ggsrc GGSRC03T]$ rman checksyntax
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Aug 12 12:57:08 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
2> 3> 4> 5> 6> 7> backup as compressed backupset incremental level 0 database format '/oraarch/GGSRC03T/DBBackup/rman_lvl0_%d_%U' plus archivelog format '/oraarch/GGSRC0    3T/DBBackup/rman_arch_%d_%U';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}8> 9> 10> 11> 12>
The command has no syntax errors
RMAN> exit

Recovery Manager complete.

3) Verify if you have necessary disk space in the candidate mountpoint

df -h /oraarch/GGSRC03T/DBBackup

Actual output:

[oracle@vcentos79-oracle-ggsrc GGSRC03T]$ df -h /oraarch/GGSRC03T/DBBackup/

Filesystem                           Size  Used Avail Use% Mounted on

/dev/mapper/appdata--vg-oraarch--lv  4.0G  963M  3.1G  24% /oraarch

[oracle@vcentos79-oracle-ggsrc GGSRC03T]$

4) Perform the backup

Copy/paste the command in RMAN command prompt

Good Practise: Put it in script file and launch in background. I am doing this in foreground, since my db is small.

Actual output:

[oracle@vcentos79-oracle-ggsrc GGSRC03T]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Aug 12 12:58:11 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: GGSRC03T (DBID=3898417486)
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level 0 database format '/oraarch/GGSRC03T/DBBackup/rman_lvl0_%d_%U' plus archivelog format '/oraarch/GGSRC03T/DBBackup/rman_a    rch_%d_%U';
release channel c1;
2> 3> 4> 5> 6> 7> 8> 9> release channel c2;
release channel c3;
release channel c4;
}10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=56 device type=DISK
...
Starting backup at 12-AUG-23
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=5 STAMP=1144099002
input archived log thread=1 sequence=7 RECID=6 STAMP=1144099006
input archived log thread=1 sequence=8 RECID=7 STAMP=1144668770
...
piece handle=/oraarch/GGSRC03T/DBBackup/rman_arch_GGSRC03T_0j23klk1_1_1 tag=TAG20230812T125841 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:08
channel c3: finished piece 1 at 12-AUG-23
...
Finished backup at 12-AUG-23
Starting backup at 12-AUG-23
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/GGSRC03T/datafile/o1_mf_system_ldwzp14y_.dbf
input datafile file number=00005 name=/oradata/GGSRC03T/datafile/o1_mf_encrypt__ldx2d7bg_.dbf
...
piece handle=/oraarch/GGSRC03T/DBBackup/rman_lvl0_GGSRC03T_0m23klkh_1_1 tag=TAG20230812T125857 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
Finished backup at 12-AUG-23
Starting backup at 12-AUG-23
current log archived
...
piece handle=/oraarch/GGSRC03T/DBBackup/rman_arch_GGSRC03T_0p23klm8_1_1 tag=TAG20230812T125952 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-AUG-23
Starting Control File and SPFILE Autobackup at 12-AUG-23
piece handle=/oraarch/GGSRC03T/autobackup/2023_08_12/o1_mf_s_1144673994_lfgx1t9l_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-AUG-23
released channel: c1
...
released channel: c4
RMAN> exit

Recovery Manager complete.
[oracle@vcentos79-oracle-ggsrc GGSRC03T]$

5) Verify the logfile of the backup

Copy above rman output to the logfile...
[oracle@vcentos79-oracle-ggsrc dba]$ vi rman_lvl0_backup_12aug23.log
[oracle@vcentos79-oracle-ggsrc dba]$ grep -Ei "RMAN-|ORA-" rman_lvl0_backup_12aug23.log

5) Backup key store

Example:
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
USING 'hr.emp_keystore' 
FORCE KEYSTORE 
IDENTIFIED BY software_keystore_password
TO '/etc/ORACLE/KEYSTORE/DB1/';

Actual: the backup command will append the timestamp, so we just need to append the keyword to it for us to identify the tde backup

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
USING 'rman_lvl0_tde_bkp' 
FORCE KEYSTORE 
IDENTIFIED BY "oracle"
TO '/oraarch/GGSRC03T/TDEBackup/';
Actual command:
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE
USING 'rman_lvl0_tde_bkp'
FORCE KEYSTORE
IDENTIFIED BY "oracle"
TO '/oraarch/GGSRC03T/TDEBackup/';  2    3    4    5
keystore altered.


6) Verify the key existance

ls -altr /oraarch/GGSRC03T/TDEBackup/
SQL> !ls -altr /oraarch/GGSRC03T/TDEBackup/
total 8
drwxr-x---. 6 oracle oinstall   75 Aug 12 12:37 ..
-rw-------. 1 oracle oinstall 3848 Aug 12 12:37 ewallet_2023081211374128_rman_lvl0_tde_key_12aug23.p12
-rw-------. 1 oracle oinstall 3848 Aug 12 13:02 ewallet_2023081212021006_rman_lvl0_tde_bkp.p12
drwxr-xr-x. 2 oracle oinstall  122 Aug 12 13:02 .
SQL>

This closes the rman online incremental level 0 backup excercise on a DB with TDE enabled.

Video of the above content:

Oracle RDBMS 12.2: Backup a keystore

 A quick blog on how to backup a keystore in oracle RDBMS version 12.2

I refered to 19c document, but it is still valid in 12.2

url: oracle tde backup link

Example command:

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
USING 'hr.emp_keystore' 
FORCE KEYSTORE 
IDENTIFIED BY software_keystore_password
TO '/etc/ORACLE/KEYSTORE/DB1/';

Actual command:

SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE
USING 'rman_lvl0_tde_key_12aug23'
FORCE KEYSTORE
IDENTIFIED BY "oracle"
TO '/oraarch/GGSRC03T/TDEBackup/';  2    3    4    5
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE
*
ERROR at line 1:
ORA-46626: cannot create a backup of the keystore

SQL> !oerr ora 46626
46626, 00000, "cannot create a backup of the keystore"
// *Cause: The backup of the keystore could not be created.
// *Action: Check the trace file for more information and try again.

>>>>>> I missed to create the necessary directory

SQL> !mkdir -p /oraarch/GGSRC03T/TDEBackup/

SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE
USING 'rman_lvl0_tde_key_12aug23'
FORCE KEYSTORE
IDENTIFIED BY "oracle"
TO '/oraarch/GGSRC03T/TDEBackup/';  2    3    4    5
keystore altered.
SQL>

SQL> !ls -altr /oraarch/GGSRC03T/TDEBackup/

total 4

drwxr-x---. 6 oracle oinstall   75 Aug 12 12:37 ..

-rw-------. 1 oracle oinstall 3848 Aug 12 12:37 ewallet_2023081211374128_rman_lvl0_tde_key_12aug23.p12

drwxr-xr-x. 2 oracle oinstall   68 Aug 12 12:37 .

The keystore is backed up in the directory we requested for.

This is important we backup the keystore with DB backup regularly or atleast when we change the master key. Otherwise the DB backup can never be used in restore/recovery.

Thanks

Oracle GoldenGate Setup for remote extract from 12.2 database using 21.3 Microservice Architecture

Objective: Oracle GoldenGate Setup for remote extract from 12.2 database using 21.3 Microservice Architecture

In continuation to the effort of db upgrade and replication.

We have completed...
1. TDE setup
2. Database Vault

In this note.. we will see how to 
1) Prepare the source db for GG extract
2) Setup new extract using existing deployment

3) start the extract

--next blog-- below
4) Initial load (which is rman backup->restore->upgrade)
5) setup replicat & start

Objective:
Setup replication between 12.2 source to target 19.3

Source:
DB Name: GGSRC03T
Schema to replicat: encvault_test
Complexity: TDE and Database Vault enabled are enabled for the schema
Server IP:192.168.194.10
Server Port: 1521

Target:
DB Name: GGTGT03T
Schema to replicat: encvault_test
Complexity: TDE and Database Vault enabled are enabled for the schema
Server IP:192.168.194.11
Server Port: 1521

1) Prepare the source db for GG extract

reference url: Oracle Document

## CGGNORTH DATABASE SETUP AT CDB LEVEL
ALTER SESSION SET CONTAINER=cdb$root;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
ALTER SYSTEM SET STREAMS_POOL_SIZE=2G;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ARCHIVE LOG LIST;
CREATE TABLESPACE GG_DATA DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M;
CREATE USER c##ggadmin IDENTIFIED BY PASSWORD CONTAINER=ALL DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE TEMP;
GRANT ALTER SYSTEM TO c##ggadmin CONTAINER=ALL;
GRANT DBA TO c##ggadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##ggadmin CONTAINER=ALL;
GRANT ALTER ANY TABLE TO c##ggadmin CONTAINER=ALL;
GRANT RESOURCE TO c##ggadmin CONTAINER=ALL;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(' c##ggadmin ',CONTAINER=>'ALL');

Ours is a standalone 12.2 setup. So we will skip CDB related changes.

Command:

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;

Output:

SQL> create pfile='/home/oracle/dba/pfileGGSRC01T_Bkp.ora' from spfile;
File created.
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
System altered.

Command:

ALTER SYSTEM SET STREAMS_POOL_SIZE=2G;

Output:
SQL> alter system set sga_max_size=3G scope=spfile;
System altered.

SQL> alter system set sga_target=3G scope=spfile;
System altered.
SQL> alter system set pga_aggregate_target=500M scope=spfile;
System altered.
SQL> alter system set STREAMS_POOL_SIZE=750M scope=spfile;
System altered.
SQL>

Command:

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Output:
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

----------------------------------------------If you have database vault setup, modify the pfile for the below setting.
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> alter system set db_recovery_file_dest_size=2G;
System altered.
SQL> sho parameter db_REco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oraarch
db_recovery_file_dest_size           big integer 2G
SQL> SQL>
SQL>
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Current log sequence           15
SQL>
-----------------------------------------------------------
>> need to bounce the db once for all.

SQL> sho parameter db_Rec
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oraarch
db_recovery_file_dest_size           big integer 2G
db_recycle_cache_size                big integer 0
SQL> sho parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                               DEST
..
log_archive_dest_18                  string
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
SQL> sho parameter golden
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE
resource_manage_goldengate           boolean     FALSE
SQL> sho parameter stream
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 752M
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL> alter database open;
------------------------------------------------
*.db_recovery_file_dest_size=2G
*.db_recovery_file_dest='/oraarch'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
-----------------------------------------------------------------

Command:

CREATE TABLESPACE GG_DATA DATAFILE '/oradata/GGSRC01T/gg_data_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;

output:

SQL> CREATE TABLESPACE GG_DATA DATAFILE '/oradata/GGSRC01T/gg_data_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;
Tablespace created.
SQL>

Create GGADMIN user using either dbv_admin or dbv_acctmgr user. I used dbv_owner

Command:

CREATE USER ggadmin IDENTIFIED BY "ggadmin" DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE TEMP;

Output:

SQL> conn dbv_owner
Enter password:
Connected.

SQL> CREATE USER ggadmin IDENTIFIED BY "ggadmin" DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE TEMP;
User created.
SQL> grant connect to ggadmin;
Grant succeeded.
SQL>
SQL> grant DV_GOLDENGATE_ADMIN to ggadmin; <<< pls grant this priv as well. Otherwise you will face an error later during GG registration.
Grant succeeded.


Command:

Other privileges to be granted as sys:
GRANT ALTER SYSTEM TO ggadmin;
GRANT DBA TO ggadmin;
GRANT CREATE SESSION TO ggadmin;
GRANT ALTER ANY TABLE TO ggadmin;
GRANT RESOURCE TO ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

Output:

SQL> GRANT ALTER SYSTEM TO ggadmin;
Grant succeeded.
SQL> GRANT DBA TO ggadmin;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ggadmin;
Grant succeeded.
SQL> GRANT ALTER ANY TABLE TO ggadmin;
Grant succeeded.
SQL> GRANT RESOURCE TO ggadmin;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');
PL/SQL procedure successfully completed.
SQL>
Post DB bounce  - verification:
SQL> select name,open_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME      OPEN_MODE            FORCE_LOGGING                           SUPPLEME
--------- -------------------- --------------------------------------- --------
GGSRC03T  READ WRITE           YES                                     YES

SQL>

>> source prepared.

2) Setup new extract using existing deployment

Add credential in preparation for extract:

ADD CREDENTIALS step:

[oracle@vcentos79-oracle-sa1 ~]$ export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1
[oracle@vcentos79-oracle-sa1 ~]$ export PATH=$PATH:$OGG_HOME/bin

[oracle@vcentos79-oracle-sa1 ~]$ adminclient
Oracle GoldenGate Administration Client for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 12:32:46
Operating system character set identified as UTF-8.

OGG (not connected) 2> connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca
OGG (http://127.0.0.1:9011 oggdep01) 3>

Add credential:

alter credentialstore add user ggadmin@192.168.194.10:1521/ggsrc03t alias ggsrc03t  PASSWORD ggadmin;

OGG (http://127.0.0.1:9011 oggdep01) 14> alter credentialstore add user ggadmin@192.168.194.10:1521/ggsrc01t alias ggsrc01t  PASSWORD ggadmin;

2023-07-23T12:09:43Z  INFO    OGG-15114  Credential store altered.
OGG (http://127.0.0.1:9011 oggdep01) 15>

<<< but the above credential will fail with ORA-01017 invalid user/pswd. Better to update the password through cURL or web UI for the deployment.
After password update..

OGG (http://127.0.0.1:9011 oggdep01) 3> DBLOGIN USERIDALIAS ggsrc03t
Successfully logged into database.
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 4>

Add trandata, heartbeat & checkpoint tables:
ADD SCHEMATRANDATA encvault_test
ADD HEARTBEATTABLE
ADD CHECKPOINTTABLE ggadmin.ggs_checkpoint

Output:

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 4> ADD SCHEMATRANDATA encvault_test
2023-08-05T20:03:35Z  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "encvault_test".
2023-08-05T20:03:35Z  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "encvault_test".
2023-08-05T20:03:35Z  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "encvault_test"

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 5> ADD HEARTBEATTABLE
2023-08-05T20:03:51Z  INFO    OGG-14101  Successfully added heartbeat table.

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 6> ADD CHECKPOINTTABLE ggadmin.ggs_checkpoint
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 7>

In the source db:

OWNER                         ,OBJECT_NAME                   ,CREATED             ,LAST_DDL_TIME       ,OBJECT_TYPE
------------------------------,------------------------------,--------------------,--------------------,-----------------------
.....
GGADMIN                       ,GG_HEARTBEAT_SEED             ,05/AUG/2023 21:03:48,05/AUG/2023 21:03:48,TABLE
GGADMIN                       ,GG_HEARTBEAT                  ,05/AUG/2023 21:03:48,05/AUG/2023 21:03:48,TABLE <<< HEARTBEAT table
GGADMIN                       ,GG_HEARTBEAT_HISTORY          ,05/AUG/2023 21:03:48,05/AUG/2023 21:03:48,TABLE
GGADMIN                       ,GG_LAG                        ,05/AUG/2023 21:03:48,05/AUG/2023 21:03:48,VIEW
GGADMIN                       ,GG_LAG_HISTORY                ,05/AUG/2023 21:03:48,05/AUG/2023 21:03:48,VIEW
GGADMIN                       ,GG_UPDATE_HB_TAB              ,05/AUG/2023 21:03:51,05/AUG/2023 21:03:51,PROCEDURE
GGADMIN                       ,GG_PURGE_HB_TAB               ,05/AUG/2023 21:03:51,05/AUG/2023 21:03:51,PROCEDURE
GGADMIN                       ,GG_UPDATE_HEARTBEATS          ,05/AUG/2023 21:03:51,05/AUG/2023 21:03:51,JOB
GGADMIN                       ,GG_PURGE_HEARTBEATS           ,05/AUG/2023 21:03:51,05/AUG/2023 21:03:51,JOB
GGADMIN                       ,GGS_CHECKPOINT                ,05/AUG/2023 21:03:57,05/AUG/2023 21:03:57,TABLE <<< CHECKPOINT table
GGADMIN                       ,SYS_C007358                   ,05/AUG/2023 21:03:57,05/AUG/2023 21:03:57,INDEX
GGADMIN                       ,GGS_CHECKPOINT_LOX            ,05/AUG/2023 21:03:57,05/AUG/2023 21:03:57,TABLE
GGADMIN                       ,SYS_C007364                   ,05/AUG/2023 21:03:57,05/AUG/2023 21:03:57,INDEX


Necessary tables are added.

Next step: Add Extract -> Add Extract Trail file -> Register the db

Adding Extract:

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 7> add extract rxt2, INTEGRATED TRANLOG, begin NOW
2023-08-05T20:05:42Z  INFO    OGG-08100  Integrated Extract added.
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 8>

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 8> info extract rxt2 detail
Extract    RXT2      Initialized  2023-08-05 21:05   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:13 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2023-08-05 21:05:42
                     SCN 0.0 (0)
Encryption Profile   LocalWallet
                     *** No TARGETEXTTRAILS specified yet ***

Integrated Extract outbound server first scn: Unavailable.
Integrated Extract outbound server filtering start scn: Unavailable.
  Extract Source                          Begin             End
  Not Available                           * Initialized *   2023-08-05 21:05

Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RXT2.rpt (does not yet exist)
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rxt2.prm (does not yet exist)
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RXT2.cpe
Process file         /oggdata/oggdep01/var/run/RXT2.pce
Error log            /oggdata/oggdep01/var/log/ggserr.log
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 9>

## >Add extract trail by modifying the param:

Create the file /oggdata/oggdep01/etc/conf/ogg/rxt2.prm with the below params:
EXTRACT rxt2
USERIDALIAS ggsrc03t DOMAIN OracleGoldenGate
EXTTRAIL src/r2
DDL INCLUDE MAPPED
TABLE encvault_test.*;
TABLE encvault_test.*;

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 10> view params rxt2
EXTRACT rxt2
USERIDALIAS ggsrc03t DOMAIN OracleGoldenGate
EXTTRAIL src/r2
DDL INCLUDE MAPPED
TABLE encvault_test.*;
TABLE encvault_test.*;
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 11>

==========If you want you can put a limit for extract trail file. I didnt test it though, still it sets some limitation, see info command output below.

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc01t@GGSRC01T) 7> ADD EXTTRAIL src/r2 EXTRACT rxt2 MEGABYTES 1500
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc01t@GGSRC01T) 8>
=================================================================
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 11> info extract rxt2 detail
Extract    RXT2      Initialized  2023-08-05 21:05   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:04:23 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2023-08-05 21:05:42
                     SCN 0.0 (0)
Encryption Profile   LocalWallet
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  src/r2                                               0          0       1500 EXTTRAIL

Integrated Extract outbound server first scn: Unavailable.
Integrated Extract outbound server filtering start scn: Unavailable.
  Extract Source                          Begin             End
  Not Available                           * Initialized *   2023-08-05 21:05

Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RXT2.rpt (does not yet exist)
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rxt2.prm
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RXT2.cpe
Process file         /oggdata/oggdep01/var/run/RXT2.pce
Error log            /oggdata/oggdep01/var/log/ggserr.log
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 12>

Now register the DB:
To register the db, we have to disconnect our current session and reconnect
>> exit previous connection

[oracle@vcentos79-oracle-sa1 ~]$ adminclient
Oracle GoldenGate Administration Client for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 12:32:46
Operating system character set identified as UTF-8.

OGG (not connected) 1> connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

OGG (http://127.0.0.1:9011 oggdep01) 2> DBLOGIN USERIDALIAS ggsrc03t
Successfully logged into database.

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 3> register extract rxt2 database

2023-08-05T20:12:31Z  ERROR   OGG-08221  Cannot register or unregister Extract group RXT2 because of the following SQL error: OCI Error ORA (status = 26723-ORA-26723: user "GGADMIN" requires the role "DV_STREAMS_ADMIN/DV_XSTREAM_ADMIN/DV_GOLDENGATE_ADMIN"
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 581
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 635
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 404
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_IVK", line 176
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 156
ORA-06512: at line 1
).
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 4>

To fix  OGG-08221 -- we need to grant additional privs to ggadmin user. I suppose this is occuring because of database vault. Irrespective of source or target, if Databse Vault is configured, we need to grant additional privileges [DV_GOLDENGATE_ADMIN].

SQL> conn dbv_owner
Enter password:
Connected.

SQL> grant DV_GOLDENGATE_ADMIN to ggadmin;
Grant succeeded.
SQL> exit

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 4> register extract rxt2 database
2023-08-05T20:16:51Z  INFO    OGG-02003  Extract group RXT2 successfully registered with database at SCN 1443018.

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 5> info extract rxt2 detail
Extract    RXT2      Initialized  2023-08-05 21:05   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:14:58 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2023-08-05 21:05:42
                     SCN 0.0 (0)
Encryption Profile   LocalWallet
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  src/r2                                               0          0       1500 EXTTRAIL

Integrated Extract outbound server first scn: 0.1443018 (1443018)
Integrated Extract outbound server filtering start scn: 0.1443018 (1443018)
  Extract Source                          Begin             End
  Not Available                           * Initialized *   2023-08-05 21:05

Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RXT2.rpt (does not yet exist)
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rxt2.prm
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RXT2.cpe
Process file         /oggdata/oggdep01/var/run/RXT2.pce
Error log            /oggdata/oggdep01/var/log/ggserr.log

3) Start the extract: 

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 6> start extract rxt2
2023-08-05T20:21:42Z  INFO    OGG-00975  Extract group RXT2 starting.
2023-08-05T20:21:42Z  INFO    OGG-15426  Extract group RXT2 started.

OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 7> info extract rxt2 detail
Extract    RXT2      Initialized  2023-08-05 21:05   Status RUNNING <<< Extract running, same can be verified using GG browser as well.
Checkpoint Lag       00:00:00 (updated 00:16:03 ago)
Process ID           739
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2023-08-05 21:05:42
                     SCN 0.0 (0)
Encryption Profile   LocalWallet
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  src/r2                                               0          0       1500 EXTTRAIL

Integrated Extract outbound server first scn: 0.1443018 (1443018)
Integrated Extract outbound server filtering start scn: 0.1443018 (1443018)
  Extract Source                          Begin             End
  Not Available                           * Initialized *   2023-08-05 21:05

Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RXT2.rpt
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rxt2.prm
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RXT2.cpe
Process file         /oggdata/oggdep01/var/run/RXT2.pce
Error log            /oggdata/oggdep01/var/log/ggserr.log
OGG (http://127.0.0.1:9011 oggdep01 as ggsrc03t@GGSRC03T) 8>

Thank you!