Thursday, February 13, 2020

MySQL 8 installation on CentOS 7.7




login as: root
root@172.26.72.112's password:
Last login: Thu Feb 13 13:01:36 2020 from 172.26.71.179
[root@centospdmn ~]# yum check mysql-*
Loaded plugins: fastestmirror, langpacks
check ['mysql-community-release-el7-5.noarch.rpm', 'mysql-community-release-el7-                                                                                        5.noarch.rpm.1', 'mysql-community-release-el7-5.noarch.rpm.2', 'mysql-community-                                                                                        release-el7-5.noarch.rpm.3']
[root@centospdmn ~]# yum check mysql-*
Loaded plugins: fastestmirror, langpacks
check ['mysql-community-release-el7-5.noarch.rpm', 'mysql-community-release-el7-5.noarch.rpm.1', 'mysql-community-release-el7-5.noarch.rpm.2', 'mysql-community-release-el7-5.noarch.rpm.3']
[root@centospdmn ~]# yum install mysql-community-release-el7-5.noarch.rpm
Loaded plugins: fastestmirror, langpacks
Examining mysql-community-release-el7-5.noarch.rpm: mysql-community-release-el7-5.noarch
Marking mysql-community-release-el7-5.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-release.noarch 0:el7-5 will be installed
--> Processing Conflict: mysql80-community-release-el7-3.noarch conflicts mysql-community-release
Loading mirror speeds from cached hostfile
 * base: centos.myfahim.com
 * centosplus: mirror.dhakacom.com
 * extras: mirror.dhakacom.com
 * updates: mirror.dhakacom.com
No package matched to upgrade: mysql-community-release
--> Finished Dependency Resolution
Error: mysql80-community-release conflicts with mysql-community-release-el7-5.noarch
 You could try using --skip-broken to work around the problem

 You could try running: rpm -Va --nofiles --nodigest
[root@centospdmn ~]#
[root@centospdmn ~]# rpm -qa | grep mysql*
Binary file mysql80-community-release-el7-1.noarch.rpm matches
Binary file mysql80-community-release-el7-1.noarch.rpm.1 matches
Binary file mysql80-community-release-el7-1.noarch.rpm.2 matches
Binary file mysql-community-release-el7-5.noarch.rpm matches
Binary file mysql-community-release-el7-5.noarch.rpm.1 matches
Binary file mysql-community-release-el7-5.noarch.rpm.2 matches
Binary file mysql-community-release-el7-5.noarch.rpm.3 matches
[root@centospdmn ~]# ll
total 136
-rw-------. 1 root root  1713 Jan 31 12:23 anaconda-ks.cfg
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Desktop
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Documents
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Downloads
-rw-r--r--. 1 root root  1744 Jan 31 12:25 initial-setup-ks.cfg
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Music
drwxr-xr-x. 2 root root  4096 Feb  5 00:26 mysql
-rw-r--r--. 1 root root 25820 Apr 18  2018 mysql80-community-release-el7-1.noarch.rpm
-rw-r--r--. 1 root root 25820 Apr 18  2018 mysql80-community-release-el7-1.noarch.rpm.1
-rw-r--r--. 1 root root 25820 Apr 18  2018 mysql80-community-release-el7-1.noarch.rpm.2
-rw-r--r--. 1 root root  6140 Nov 12  2015 mysql-community-release-el7-5.noarch.rpm
-rw-r--r--. 1 root root  6140 Nov 12  2015 mysql-community-release-el7-5.noarch.rpm.1
-rw-r--r--. 1 root root  6140 Nov 12  2015 mysql-community-release-el7-5.noarch.rpm.2
-rw-r--r--. 1 root root  6140 Nov 12  2015 mysql-community-release-el7-5.noarch.rpm.3
-rw-r--r--. 1 root root   582 Feb  4 15:28 notes
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Pictures
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Public
-rw-r--r--. 1 root root  3736 Sep 10 00:29 repomd.xml
-rw-r--r--. 1 root root     0 Feb  4 13:19 sudo
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Templates
drwxr-xr-x. 2 root root     6 Feb 10 13:16 Videos
[root@centospdmn ~]# rpm -Uvh mysql80-community-release-*
warning: mysql80-community-release-el7-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
warning: package mysql80-community-release-el7-1.noarch was already added, skipping mysql80-community-release-el7-1.noarch
warning: package mysql80-community-release-el7-1.noarch was already added, skipping mysql80-community-release-el7-1.noarch
Preparing...                          ################################# [100%]
        package mysql80-community-release-el7-3.noarch (which is newer than mysql80-community-release-el7-1.noarch) is already installed
[root@centospdmn ~]# rpm -Uvh mysql80-community-*
warning: mysql80-community-release-el7-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
warning: package mysql80-community-release-el7-1.noarch was already added, skipping mysql80-community-release-el7-1.noarch
warning: package mysql80-community-release-el7-1.noarch was already added, skipping mysql80-community-release-el7-1.noarch
Preparing...                          ################################# [100%]
        package mysql80-community-release-el7-3.noarch (which is newer than mysql80-community-release-el7-1.noarch) is already installed
[root@centospdmn ~]# rpm -qa| grep mysql*
Binary file mysql80-community-release-el7-1.noarch.rpm matches
Binary file mysql80-community-release-el7-1.noarch.rpm.1 matches
Binary file mysql80-community-release-el7-1.noarch.rpm.2 matches
Binary file mysql-community-release-el7-5.noarch.rpm matches
Binary file mysql-community-release-el7-5.noarch.rpm.1 matches
Binary file mysql-community-release-el7-5.noarch.rpm.2 matches
Binary file mysql-community-release-el7-5.noarch.rpm.3 matches
[root@centospdmn ~]# yum remove mysql*
Loaded plugins: fastestmirror, langpacks
No Match for argument: mysql
No Match for argument: mysql80-community-release-el7-1.noarch.rpm
No Match for argument: mysql80-community-release-el7-1.noarch.rpm.1
No Match for argument: mysql80-community-release-el7-1.noarch.rpm.2
No Match for argument: mysql-community-release-el7-5.noarch.rpm
No Match for argument: mysql-community-release-el7-5.noarch.rpm.1
No Match for argument: mysql-community-release-el7-5.noarch.rpm.2
No Match for argument: mysql-community-release-el7-5.noarch.rpm.3
No Packages marked for removal
[root@centospdmn ~]# yum clean all
Loaded plugins: fastestmirror, langpacks
Cleaning repos: base centosplus extras updates
Cleaning up list of fastest mirrors
[root@centospdmn ~]# yum update
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
 * base: mirrors.piconets.webwerks.in
 * centosplus: mirrors.piconets.webwerks.in
 * extras: mirrors.piconets.webwerks.in
 * updates: mirrors.piconets.webwerks.in
base                                                                                                                                             | 3.6 kB  00:00:00
centosplus                                                                                                                                       | 2.9 kB  00:00:00
extras                                                                                                                                           | 2.9 kB  00:00:00
updates                                                                                                                                          | 2.9 kB  00:00:00
(1/5): base/7/x86_64/group_gz                                                                                                                    | 165 kB  00:00:00
(2/5): extras/7/x86_64/primary_db                                                                                                                | 159 kB  00:00:00
(3/5): centosplus/7/x86_64/primary_db                                                                                                            | 2.7 MB  00:00:01
(4/5): updates/7/x86_64/primary_db                                                                                                               | 6.7 MB  00:00:06
(5/5): base/7/x86_64/primary_db                                                                                                                  | 6.0 MB  00:00:07
No packages marked for update
[root@centospdmn ~]# yum check mysql*
Loaded plugins: fastestmirror, langpacks
check ['mysql', 'mysql80-community-release-el7-1.noarch.rpm', 'mysql80-community-release-el7-1.noarch.rpm.1', 'mysql80-community-release-el7-1.noarch.rpm.2', 'mysql-community-release-el7-5.noarch.rpm', 'mysql-community-release-el7-5.noarch.rpm.1', 'mysql-community-release-el7-5.noarch.rpm.2', 'mysql-community-release-el7-5.noarch.rpm.3']
[root@centospdmn ~]# find / -name "mysql80-community-release-el7-1.noarch.rpm" -print
find: ‘/proc/28854’: No such file or directory
find: ‘/run/user/1000/gvfs’: Permission denied
/etc/yum.repos.d/mysql80-community-release-el7-1.noarch.rpm
/root/mysql80-community-release-el7-1.noarch.rpm
/var/tmp/yum-root-NIT_J_/mysql80-community-release-el7-1.noarch.rpm
/tmp/mozilla_admin0/mysql80-community-release-el7-1.noarch.rpm
/tmp/gnome-software-EJVPF0/mysql80-community-release-el7-1.noarch.rpm
[root@centospdmn ~]# find / -name "mysql*" -print
find: ‘/proc/10326’: No such file or directory
find: ‘/run/user/1000/gvfs’: Permission denied
/sys/fs/selinux/booleans/mysql_connect_any
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo
/etc/yum.repos.d/mysql80-community-release-el7-1.noarch.rpm
/etc/my.cnf.d/mysql-clients.cnf
/etc/selinux/targeted/active/modules/100/mysql
/root/mysql-community-release-el7-5.noarch.rpm
/root/mysql-community-release-el7-5.noarch.rpm.1
/root/mysql80-community-release-el7-1.noarch.rpm
/root/mysql80-community-release-el7-1.noarch.rpm.1
/root/mysql80-community-release-el7-1.noarch.rpm.2
/root/mysql
/root/mysql/mysql-8.0.19-1.el8.aarch64.rpm-bundle.tar
/root/mysql/mysql-community-test-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-libs-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-server-debug-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-libs-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-server-debug-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-test-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-devel-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-debugsource-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-common-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-client-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-server-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-server-debuginfo-8.0.19-1.el8.aarch64.rpm
/root/mysql/mysql-community-client-8.0.19-1.el8.aarch64.rpm
/root/mysql-community-release-el7-5.noarch.rpm.2
/root/mysql-community-release-el7-5.noarch.rpm.3
/var/tmp/yum-admin-C2BCfb/x86_64/7/mysql-connectors-community
/var/tmp/yum-admin-C2BCfb/x86_64/7/mysql-tools-community
/var/tmp/yum-admin-C2BCfb/x86_64/7/mysql80-community
/var/tmp/yum-root-NIT_J_/mysql80-community-release-el7-1.noarch.rpm
/var/tmp/yum-root-NIT_J_/mysql57-community-release-el7-11.noarch.rpm
/var/lib/yum/repos/x86_64/7/mysql-connectors-community
/var/lib/yum/repos/x86_64/7/mysql-tools-community
/var/lib/yum/repos/x86_64/7/mysql80-community
/var/cache/yum/x86_64/7/mysql80-community
/tmp/gnome-software-RFPMF0/mysql80-community-release-el7-3.noarch-4.rpm
/tmp/mozilla_admin0/mysql80-community-release-el7-3.noarch-3.rpm
/tmp/mozilla_admin0/mysql80-community-release-el7-3.noarch-4.rpm
/tmp/mozilla_admin0/mysql80-community-release-el7-1.noarch.rpm
/tmp/mozilla_admin0/mysql80-community-release-el7-1.noarch-1.rpm
/tmp/gnome-software-EJVPF0/mysql80-community-release-el7-1.noarch.rpm
/tmp/gnome-software-LQEFF0/mysql80-community-release-el7-1.noarch-1.rpm
/tmp/gnome-software-VZEAF0/mysql80-community-release-el7-3.noarch-3.rpm
/usr/lib/firewalld/services/mysql.xml
/usr/lib/python2.7/site-packages/sos/plugins/mysql.py
/usr/lib/python2.7/site-packages/sos/plugins/mysql.pyc
/usr/lib/python2.7/site-packages/sos/plugins/mysql.pyo
/usr/lib64/mysql
/usr/lib64/mysql/plugin/mysql_clear_password.so
/usr/share/augeas/lenses/dist/mysql.aug
/usr/share/man/man5/mysql_table.5.gz
/usr/share/vim/vim74/syntax/mysql.vim
/usr/share/mysql
/usr/share/qemu-kvm/qemu-ga/fsfreeze-hook.d/mysql-flush.sh.sample
/home/admin/Downloads/mysql80-community-release-el7-3.noarch.rpm
/home/admin/Downloads/mysql80-community-release-el7-3.noarch(1).rpm
/home/admin/Downloads/mysql80-community-release-el7-3.noarch(2).rpm
/home/admin/Downloads/mysql80-community-release-el7-3.noarch(3).rpm
/home/admin/Downloads/mysql80-community-release-el7-3.noarch(4).rpm
[root@centospdmn ~]# rpm -ivh /root/mysql/mysql-community-server-8.0.19-1.el8.aarch64.rpm
warning: /root/mysql/mysql-community-server-8.0.19-1.el8.aarch64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        ld-linux-aarch64.so.1()(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        ld-linux-aarch64.so.1(GLIBC_2.17)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libc.so.6(GLIBC_2.28)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libcrypto.so.1.1()(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libcrypto.so.1.1(OPENSSL_1_1_0)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libcrypto.so.1.1(OPENSSL_1_1_1)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libdl.so.2(GLIBC_2.17)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libgcc_s.so.1(GCC_4.5.0)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libm.so.6(GLIBC_2.17)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libpthread.so.0(GLIBC_2.17)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        librt.so.1(GLIBC_2.17)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libssl.so.1.1()(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libssl.so.1.1(OPENSSL_1_1_0)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libssl.so.1.1(OPENSSL_1_1_1)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(CXXABI_1.3.11)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(CXXABI_1.3.8)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(CXXABI_1.3.9)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(GLIBCXX_3.4.20)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(GLIBCXX_3.4.21)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libstdc++.so.6(GLIBCXX_3.4.22)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libtirpc.so.3()(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        libtirpc.so.3(TIRPC_0.3.0)(64bit) is needed by mysql-community-server-8.0.19-1.el8.aarch64
        mysql-community-client(aarch-64) >= 8.0.11 is needed by mysql-community-server-8.0.19-1.el8.aarch64
        mysql-community-common(aarch-64) = 8.0.19-1.el8 is needed by mysql-community-server-8.0.19-1.el8.aarch64
[root@centospdmn ~]# yum install  ld-linux-aarch64.so.1
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.piconets.webwerks.in
 * centosplus: mirrors.piconets.webwerks.in
 * extras: mirrors.piconets.webwerks.in
 * updates: mirrors.piconets.webwerks.in
No package ld-linux-aarch64.so.1 available.
Error: Nothing to do
[root@centospdmn ~]# yum install mysql-community-client
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.piconets.webwerks.in
 * centosplus: mirrors.piconets.webwerks.in
 * extras: mirrors.piconets.webwerks.in
 * updates: mirrors.piconets.webwerks.in
No package mysql-community-client available.
Error: Nothing to do
[root@centospdmn ~]# cd /etc/yum.repos.d/
[root@centospdmn yum.repos.d]# ll
total 72
-rw-r--r--. 1 root root  1248 Feb 10 13:51 CentOS-Base.repo
-rw-r--r--. 1 root root  1664 Feb  5 00:48 CentOS-Base.repo_bkp
-rw-r--r--. 1 root root  1309 Sep  5 18:35 CentOS-CR.repo
-rw-r--r--. 1 root root   649 Sep  5 18:35 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root   314 Sep  5 18:35 CentOS-fasttrack.repo
-rw-r--r--. 1 root root   630 Sep  5 18:35 CentOS-Media.repo
-rw-r--r--. 1 root root  1331 Sep  5 18:35 CentOS-Sources.repo
-rw-r--r--. 1 root root  6639 Sep  5 18:35 CentOS-Vault.repo
-rw-r--r--. 1 root root 25820 Apr 18  2018 mysql80-community-release-el7-1.noarch.rpm
-rw-r--r--. 1 root root  2076 Feb 10 13:27 mysql-community.repo
-rw-r--r--. 1 root root  2108 Apr 24  2019 mysql-community-source.repo
drwxr-xr-x. 2 root root   248 Feb  5 00:40 test
[root@centospdmn yum.repos.d]# vi mysql-community.repo
[root@centospdmn yum.repos.d]# vi mysql-community-source.repo
[root@centospdmn yum.repos.d]# rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
Retrieving https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
warning: /var/tmp/rpm-tmp.m1X3NU: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
        package mysql80-community-release-el7-3.noarch is already installed
[root@centospdmn yum.repos.d]# sed -i 's/enabled=1/enabled=0/' /etc/yum.repos.d/mysql-community.repo
[root@centospdmn yum.repos.d]# yum --enablerepo=mysql80-community install mysql-community-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.piconets.webwerks.in
 * centosplus: mirrors.piconets.webwerks.in
 * extras: mirrors.piconets.webwerks.in
 * updates: mirrors.piconets.webwerks.in
mysql80-community                                                                                                                                | 2.5 kB  00:00:00
mysql80-community/x86_64/primary_db                                                                                                              |  97 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:8.0.19-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.19-1.el7 for package: mysql-community-server-8.0.19-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.19-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.19-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.19-1.el7.x86_64
---> Package mysql-community-common.x86_64 0:8.0.19-1.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.0.1.el7.centos.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.0.1.el7.centos.x86_64
---> Package mysql-community-libs.x86_64 0:8.0.19-1.el7 will be obsoleting
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:8.0.19-1.el7 will be obsoleting
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                            Arch                          Version                                Repository                                Size
========================================================================================================================================================================
Installing:
 mysql-community-libs                               x86_64                        8.0.19-1.el7                           mysql80-community                        4.0 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
 mysql-community-libs-compat                        x86_64                        8.0.19-1.el7                           mysql80-community                        1.3 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
 mysql-community-server                             x86_64                        8.0.19-1.el7                           mysql80-community                        436 M
Installing for dependencies:
 mysql-community-client                             x86_64                        8.0.19-1.el7                           mysql80-community                         41 M
 mysql-community-common                             x86_64                        8.0.19-1.el7                           mysql80-community                        605 k

Transaction Summary
========================================================================================================================================================================
Install  3 Packages (+2 Dependent packages)

Total download size: 483 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-common-8.0.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY:-- ETA
Public key for mysql-community-common-8.0.19-1.el7.x86_64.rpm is not installed
(1/5): mysql-community-common-8.0.19-1.el7.x86_64.rpm                                                                                            | 605 kB  00:00:00
(2/5): mysql-community-libs-8.0.19-1.el7.x86_64.rpm                                                                                              | 4.0 MB  00:00:03
(3/5): mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm                                                                                       | 1.3 MB  00:00:01
(4/5): mysql-community-client-8.0.19-1.el7.x86_64.rpm                                                                                            |  41 MB  00:00:30
(5/5): mysql-community-server-8.0.19-1.el7.x86_64.rpm                                                                                            | 436 MB  00:03:45
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   2.1 MB/s | 483 MB  00:03:50
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-3.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.19-1.el7.x86_64                                                                                                           1/6
  Installing : mysql-community-libs-8.0.19-1.el7.x86_64                                                                                                             2/6
  Installing : mysql-community-client-8.0.19-1.el7.x86_64                                                                                                           3/6
  Installing : mysql-community-server-8.0.19-1.el7.x86_64                                                                                                           4/6
  Installing : mysql-community-libs-compat-8.0.19-1.el7.x86_64                                                                                                      5/6
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                                                                                   6/6
  Verifying  : mysql-community-libs-8.0.19-1.el7.x86_64                                                                                                             1/6
  Verifying  : mysql-community-libs-compat-8.0.19-1.el7.x86_64                                                                                                      2/6
  Verifying  : mysql-community-client-8.0.19-1.el7.x86_64                                                                                                           3/6
  Verifying  : mysql-community-common-8.0.19-1.el7.x86_64                                                                                                           4/6
  Verifying  : mysql-community-server-8.0.19-1.el7.x86_64                                                                                                           5/6
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                                                                                   6/6

Installed:
  mysql-community-libs.x86_64 0:8.0.19-1.el7          mysql-community-libs-compat.x86_64 0:8.0.19-1.el7          mysql-community-server.x86_64 0:8.0.19-1.el7

Dependency Installed:
  mysql-community-client.x86_64 0:8.0.19-1.el7                                       mysql-community-common.x86_64 0:8.0.19-1.el7

Replaced:
  mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!
[root@centospdmn yum.repos.d]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@centospdmn yum.repos.d]# grep "A temporary password" /var/log/mysqld.log
2020-02-13T07:50:26.382733Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: =(!TBp2H3);M


Note that your temporary password will be different. You will need this password for changing the password of the root user account.

[root@centospdmn yum.repos.d]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)
[root@centospdmn yum.repos.d]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)
[root@centospdmn yum.repos.d]# grep "A temporary password" /var/log/mysqld.log
2020-02-13T07:50:26.382733Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: =(!TBp2H3);M
[root@centospdmn yum.repos.d]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@centospdmn yum.repos.d]#

[root@centospdmn yum.repos.d]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@centospdmn yum.repos.d]# chkconfig mysqld on
Note: Forwarding request to 'systemctl enable mysqld.service'.
[root@centospdmn yum.repos.d]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@centospdmn yum.repos.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>

Tuesday, February 11, 2020

Error if python is not installed while Installing OCI CLI and the resolution

PS C:\Windows\system32> Set-ExecutionPolicy RemoteSigned

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose

 Arguments to python script: "C:\Users\ANIL\AppData\Local\Temp\tmp3F28.tmp"
    In progress...

ownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

        ******************************************************************************
        You have started the OCI CLI Installer in interactive mode. If you do not wish
        to run this in interactive mode, please include the -AcceptAllDefaults option.
        If you have the script locally and would like to know more about
        input options for this script, then you can run:
        help .\install.ps1
        If you would like to know more about input options for this script, refer to:
        https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst
        ******************************************************************************
VERBOSE: No valid Python installation found.

Python is required to run the CLI.
Install Python now? (Entering "n" will exit the installation script)
[Y] Yes  [N] No  [?] Help (default is "Y"): Y
VERBOSE: Downloading Python...
VERBOSE: Downloading Python...
Exception calling "DownloadFile" with "2" argument(s): "The underlying connection was closed: An unexpected error occur
red on a send."
At line:164 char:55
+         (New-Object System.Net.WebClient).DownloadFile <<<< ($Uri, $OutFile)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException


I have installed Python version 3.8.1 and have the following :- OCI CLI Installed successfully


PS C:\Windows\system32> powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).D
ownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

        ******************************************************************************
        You have started the OCI CLI Installer in interactive mode. If you do not wish
        to run this in interactive mode, please include the -AcceptAllDefaults option.
        If you have the script locally and would like to know more about
        input options for this script, then you can run:
        help .\install.ps1
        If you would like to know more about input options for this script, refer to:
        https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst
        ******************************************************************************
VERBOSE: Python found in registry: HKCU:\Software\Python\PythonCore
VERBOSE: Downloading install script to C:\Users\ANIL\AppData\Local\Temp\tmp3F28.tmp
VERBOSE:  False   False
VERBOSE: Using Python executable: C:\Users\ANIL\AppData\Local\Programs\Python\Python38-32\python.exe to run install
script...
VERBOSE: Arguments to python script: "C:\Users\ANIL\AppData\Local\Temp\tmp3F28.tmp"
-- Verifying Python version.
-- Python version 3.8.1 okay.

===> In what directory would you like to place the install? (leave blank to use 'C:\Users\ANIL\lib\oracle-cli'): E:\oci\
oci-cli
-- We will install at 'E:\oci\oci-cli'.

===> In what directory would you like to place the 'oci.exe' executable? (leave blank to use 'C:\Users\ANIL\bin'): E:\oc
i\oci-cli\bin
-- Creating directory 'E:\oci\oci-cli\bin'.
-- The executable will be in 'E:\oci\oci-cli\bin'.

===> In what directory would you like to place the OCI scripts? (leave blank to use 'C:\Users\ANIL\bin\oci-cli-scripts')
: E:\oci\oci-cli\bin\oci-cli-scripts
-- The scripts will be in 'E:\oci\oci-cli\bin\oci-cli-scripts'.

===> Currently supported optional packages are: ['db (will install cx_Oracle)']
What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any optional packages)?:
-- The optional packages installed will be ''.
-- Trying to use python3 venv.
-- Executing: ['C:\\Users\\ANIL\\AppData\\Local\\Programs\\Python\\Python38-32\\python.exe', '-m', 'venv', 'E:\\oci\\oci
-cli']
-- Executing: ['E:\\oci\\oci-cli\\Scripts\\pip', 'install', '--cache-dir', 'C:\\Users\\ANIL\\AppData\\Local\\Temp\\tmpav
p9sq1k', 'oci_cli', '--upgrade']
Collecting oci_cli
  Downloading https://files.pythonhosted.org/packages/0b/06/3ead2c0a3703d6dcc049affce4638c47279d8b20b60b1d0f08a1237ade4f
/oci_cli-2.9.2-py2.py3-none-any.whl (7.3MB)
     |████████████████████████████████| 7.3MB 2.2MB/s
Collecting certifi (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/b9/63/df50cac98ea0d5b006c55a399c3bf1db9da7b5a24de7890bc9cfd5dd9e99
/certifi-2019.11.28-py2.py3-none-any.whl (156kB)
     |████████████████████████████████| 163kB 2.2MB/s
Collecting PyYAML==5.1.2 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/e3/e8/b3212641ee2718d556df0f23f78de8303f068fe29cdaa7a91018849582fe
/PyYAML-5.1.2.tar.gz (265kB)
     |████████████████████████████████| 266kB 1.7MB/s
Collecting six==1.11.0 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/67/4b/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90a2d863fe3056e86a
/six-1.11.0-py2.py3-none-any.whl
Collecting configparser==3.5.0 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/7c/69/c2ce7e91c89dc073eb1aa74c0621c3eefbffe8216b3f9af9d3885265c01c
/configparser-3.5.0.tar.gz
Collecting terminaltables==3.1.0 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/9b/c4/4a21174f32f8a7e1104798c445dacdc1d4df86f2f26722767034e4de4bff
/terminaltables-3.1.0.tar.gz
Collecting pytz==2016.10 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/f5/fa/4a9aefc206aa49a4b5e0a72f013df1f471b4714cdbe6d78f0134feeeecdb
/pytz-2016.10-py2.py3-none-any.whl (483kB)
     |████████████████████████████████| 491kB 1.7MB/s
Collecting retrying==1.3.3 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/44/ef/beae4b4ef80902f22e3af073397f079c96969c69b2c7d52a57ea9ae61c9d
/retrying-1.3.3.tar.gz
Collecting cryptography==2.8 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/74/e1/6e360b4dbd2a63c1f506402bfa6fa56aa5826337830d8ee86e5e2ec9457b
/cryptography-2.8-cp38-cp38-win32.whl (1.3MB)
     |████████████████████████████████| 1.3MB 2.2MB/s
Collecting jmespath==0.9.3 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/b7/31/05c8d001f7f87f0f07289a5fc0fc3832e9a57f2dbd4d3b0fee70e0d51365
/jmespath-0.9.3-py2.py3-none-any.whl
Collecting python-dateutil==2.7.3 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/cf/f5/af2b09c957ace60dcfac112b669c45c8c97e32f94aa8b56da4c6d1682825
/python_dateutil-2.7.3-py2.py3-none-any.whl (211kB)
     |████████████████████████████████| 215kB 2.2MB/s
Collecting pyOpenSSL==18.0.0 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/96/af/9d29e6bd40823061aea2e0574ccb2fcf72bfd6130ce53d32773ec375458c
/pyOpenSSL-18.0.0-py2.py3-none-any.whl (53kB)
     |████████████████████████████████| 61kB 2.0MB/s
Collecting idna<2.7,>=2.5 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/27/cc/6dd9a3869f15c2edfab863b992838277279ce92663d334df9ecf5106f5c6
/idna-2.6-py2.py3-none-any.whl (56kB)
     |████████████████████████████████| 61kB 2.0MB/s
Collecting oci==2.10.4 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/f0/e9/74ee0fdc42e26a1052a7d37d55aad5a36f473b969e79cc9d9b8eadc9cfb8
/oci-2.10.4-py2.py3-none-any.whl (3.5MB)
     |████████████████████████████████| 3.5MB 2.2MB/s
Collecting click==6.7 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/34/c1/8806f99713ddb993c5366c362b2f908f18269f8d792aff1abfd700775a77
/click-6.7-py2.py3-none-any.whl (71kB)
     |████████████████████████████████| 71kB 2.2MB/s
Collecting arrow==0.10.0 (from oci_cli)
  Downloading https://files.pythonhosted.org/packages/54/db/76459c4dd3561bbe682619a5c576ff30c42e37c2e01900ed30a501957150
/arrow-0.10.0.tar.gz (86kB)
     |████████████████████████████████| 92kB 1.5MB/s
Collecting cffi!=1.11.3,>=1.8 (from cryptography==2.8->oci_cli)
  Downloading https://files.pythonhosted.org/packages/0a/e4/cb57bf30f98fc7c36c0db2759b875d3990ad531a8f2e9e535da2bdcc138f
/cffi-1.14.0-cp38-cp38-win32.whl (165kB)
     |████████████████████████████████| 174kB 2.2MB/s
Collecting pycparser (from cffi!=1.11.3,>=1.8->cryptography==2.8->oci_cli)
  Downloading https://files.pythonhosted.org/packages/68/9e/49196946aee219aead1290e00d1e7fdeab8567783e83e1b9ab5585e6206a
/pycparser-2.19.tar.gz (158kB)
     |████████████████████████████████| 163kB 2.2MB/s
Installing collected packages: certifi, PyYAML, six, configparser, terminaltables, pytz, retrying, pycparser, cffi, cryp
tography, jmespath, python-dateutil, pyOpenSSL, idna, oci, click, arrow, oci-cli
  Running setup.py install for PyYAML ... done
  Running setup.py install for configparser ... done
  Running setup.py install for terminaltables ... done
  Running setup.py install for retrying ... done
  Running setup.py install for pycparser ... done
  Running setup.py install for arrow ... done
Successfully installed PyYAML-5.1.2 arrow-0.10.0 certifi-2019.11.28 cffi-1.14.0 click-6.7 configparser-3.5.0 cryptograph
y-2.8 idna-2.6 jmespath-0.9.3 oci-2.10.4 oci-cli-2.9.2 pyOpenSSL-18.0.0 pycparser-2.19 python-dateutil-2.7.3 pytz-2016.1
0 retrying-1.3.3 six-1.11.0 terminaltables-3.1.0
WARNING: You are using pip version 19.2.3, however version 20.0.2 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.

===> Modify PATH to include the CLI and enable tab completion in PowerShell now? (Y/n): Y
--
-- ** Close and re-open PowerShell to reload changes to your PATH **
-- In order to run the autocomplete script, you may also need to set your PowerShell execution policy to allow for runni
ng local scripts (as an Administrator run Set-ExecutionPolicy RemoteSigned in a PowerShell prompt)
--
-- Installation successful.
-- Run the CLI with E:\oci\oci-cli\bin\oci.exe --help
VERBOSE: Successfully installed OCI CLI!
PS C:\Windows\system32>



Tuesday, January 21, 2020

Git – commit. Creating snapshot



[root@oeldb repo]# git status
On branch master

Initial commit

Changes to be committed:
  (use "git rm --cached <file>..." to unstage)

        new file:   test.txt
        new file:   test1.py
        new file:   test2.html

[root@oeldb repo]# git commit

*** Please tell me who you are.

Run

  git config --global user.email "you@example.com"
  git config --global user.name "Your Name"

to set your account's default identity.
Omit --global to set the identity only in this repository.

fatal: unable to auto-detect email address (got 'root@oeldb.(none)')
[root@oeldb repo]#


[root@oeldb repo]# git config --global user.email "root@oeldb.com"
[root@oeldb repo]# git config --global user.name "rootuser"

Git commit with a message

[root@oeldb repo]# git commit -m "First Commit"
[master (root-commit) 338337c] First Commit
 3 files changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 test.txt
 create mode 100644 test1.py
 create mode 100644 test2.html
[root@oeldb repo]#

List of all the tracked files :- use -a option

[root@oeldb repo]# git commit -a -m "First Commit"


To view the previous commits

[root@oeldb repo]# git log
commit 338337c67665a0a64bba46fb9b9efe1ebf0738f3
Author: rootuser <root@oeldb.com>
Date:   Thu Jan 16 18:00:10 2020 +0530

    First Commit
[root@oeldb repo]#

Author – the one who committed. Contributors.





Thursday, January 16, 2020

Git functions - Add, remove, cached in the repository




[root@oeldb repo]# git status
On branch master

Initial commit

nothing to commit (create/copy files and use "git add" to track)

[root@oeldb repo]# cd .git/

[root@oeldb .git]# git status
fatal: This operation must be run in a work tree

[root@oeldb .git]# cd ..

Run the git command from the branch master

[root@oeldb repo]# git status
On branch master

Initial commit

nothing to commit (create/copy files and use "git add" to track)
[root@oeldb repo]# ll
total 0
[root@oeldb repo]# ls -al
total 12
drwxr-xr-x. 3 root root 4096 Jan 16 14:13 .
drwxr-xr-x. 7 root root 4096 Jan 16 13:08 ..
drwxr-xr-x. 7 root root 4096 Jan 16 15:25 .git

Create a file

[root@oeldb repo]# touch test.txt

You can see it in the staging area

[root@oeldb repo]# git status
On branch master
Initial commit
Untracked files:
  (use "git add <file>..." to include in what will be committed)
        test.txt
nothing added to commit but untracked files present (use "git add" to track)

[root@oeldb repo]# ll
total 0
-rw-r--r--. 1 root root 0 Jan 16 15:26 test.txt
[root@oeldb repo]# pwd
/usr/local/git/repo

Create additional files

[root@oeldb repo]# touch test1.py
[root@oeldb repo]# touch test2.html
[root@oeldb repo]# touch test3.sql
[root@oeldb repo]# ll
total 0
-rw-r--r--. 1 root root 0 Jan 16 15:26 test1.py
-rw-r--r--. 1 root root 0 Jan 16 15:26 test2.html
-rw-r--r--. 1 root root 0 Jan 16 15:26 test3.sql
-rw-r--r--. 1 root root 0 Jan 16 15:26 test.txt

List the files in branch master

[root@oeldb repo]# git status
On branch master

Initial commit

Untracked files:
  (use "git add <file>..." to include in what will be committed)

        test.txt
        test1.py
        test2.html
        test3.sql
nothing added to commit but untracked files present (use "git add" to track)

Commit the addition of files

[root@oeldb repo]# git add .

[root@oeldb repo]# git status
On branch master

Initial commit

Changes to be committed:
  (use "git rm --cached <file>..." to unstage)

        new file:   test.txt
        new file:   test1.py
        new file:   test2.html
        new file:   test3.sql

Remove the file permanently

[root@oeldb repo]# git rm -f test3.sql
rm 'test3.sql'

[root@oeldb repo]# git status
On branch master
Initial commit
Changes to be committed:
  (use "git rm --cached <file>..." to unstage)

        new file:   test.txt
        new file:   test1.py
        new file:   test2.html

You see the file (test3.sql) is not available.

[root@oeldb repo]# ll
total 0
-rw-r--r--. 1 root root 0 Jan 16 15:26 test1.py
-rw-r--r--. 1 root root 0 Jan 16 15:26 test2.html
-rw-r--r--. 1 root root 0 Jan 16 15:26 test.txt

Remove the file from the staged area

[root@oeldb repo]# git rm --cached test2.html
rm 'test2.html'

[root@oeldb repo]# git status
On branch master
Initial commit
Changes to be committed:
  (use "git rm --cached <file>..." to unstage)

        new file:   test.txt
        new file:   test1.py
Untracked files:
  (use "git add <file>..." to include in what will be committed)
        test2.html

[root@oeldb repo]# ll
total 0
-rw-r--r--. 1 root root 0 Jan 16 15:26 test1.py
-rw-r--r--. 1 root root 0 Jan 16 15:26 test2.html
-rw-r--r--. 1 root root 0 Jan 16 15:26 test.txt

The difference between rm -f and rm --cached.

rm -f – removes the file completely
rm --cached – remove from the staged area.

Commit
[root@oeldb repo]# git add .

[root@oeldb repo]# git status
On branch master
Initial commit
Changes to be committed:
  (use "git rm --cached <file>..." to unstage)
        new file:   test.txt
        new file:   test1.py
        new file:   test2.html
[root@oeldb repo]#

Now, you’ve see the file (test2.html) is back in the stage area.

Monday, January 13, 2020

Cross platform Oracle Database migration from Windows to Linux with upgrade

This post gives us hands on steps on how to migrate a database from Windows platform to Linux platform with an upgrade from 12.1.0.2 to 18.5.

Mostly we get to do this kind of exercise in a Lab setup or a setup that was initially used as test setup and then one client wants this live and can afford a downtime.

Source -
OS - Windows (Microsoft Windows x86 64-bit)
Oracle Database version - 12.1.0.2

Target -
OS - Linux (Linux x86 64-bit)
Oracle Database version - 18.5.0.0

List of Steps Needed to Complete the Migration:-

1. Check platform compatibility between source and target OS

2. Start the database in read only mode

3. Check database readiness for transport from Windows to Linux using DBMS_TDB.CHECK_DB

4. Check if there are any external objects

5. Execute the Rman Convert database command

6. Copy converted datafiles, generated Transport Script and Parameter File to Linux

7. Edit the init.ora for the new database

8. Edit the Transport Script and Parameter File changing the windows paths to Linux Paths

9. Execute the Transport Script

10.Change the Database ID

11.Check database integrity

Let's get into hands on now :-

Check the Endian format
-------------------------------------------------------------
col platform_name format a40;
set pages 100
select * from v$transportable_platform order by 2;
Windows and Linux use same Endian format - Little Endian

Check the platform of existing database
-------------------------------------------------------------
select platform_id,platform_name from v$database;


On source database
-------------------------------------------------------------
shutdown immediate;

startup nomount;

alter database open read only;


Verify whether the database can be migrated and whether the current state is correct
-------------------------------------------------------------

Fails means database couldn't be transported
If it doesn't return resolved, it means the state of the database is correct


set serveroutput on
declare
v_return boolean;
begin
v_return: dbms_tdb.check_db('Linux x86 64-bit '); --- Add the
end;
/

It should return PL/SQL procedure successfully completed.

Verify whether the database has external tables directories or bfiles;
-------------------------------------------------------------
declare
v_return boolean;
begin
v_return:= dbms_tdb.check_external;
end;
/

--- The following directories exist in the database:

Check the INVALID object count
-------------------------------------------------------------

select owner, count(*) from dba_objects where status='INVALID' group by owner;

Source Server - Create a folder for the converted data files
-------------------------------------------------------------
New Folder -> test
c:\test

Use RMAN convert database to transport the entire database for destination platform
-------------------------------------------------------------

C:\rman target /

convert database new database 'newdb'  --- Enter the required new name for the database
transport script 'c:\test\transport.sql'
db_file_name_convert 'C:\<Location_of_directory_where_all_data_files_are_placed\' 'c:\test' --- folder which will contain the data files
to platform 'Linux x86 64-bit';

--- This will start converting the datafiles

Now, create a parameter file
-------------------------------------------------------------
create pfile 'C:\test\pfile.ora' from spfile;

Check in the directory to see if the files are created
-------------------------------------------------------------
You will have all the .dbf files, pfile and transport.sql files

Move the files to the target - Linux machine
-------------------------------------------------------------
Target directory - /tmp/test

Create required directories you will mention in pfile:-

mkdir admin
mkdir dbname --- Enter database name that is created
mkdir adump bdump cdump udump
mkdir flash_recovery_area
mkdir -p oradata/db_name --- Enter database name that is created, let's say lindb

Move all the data files to the oradata location

mv /tmp/test/*.DBF $ORACLE_HOME/oradata/lindb

Open the pfile and edit the changes corresponding to the directory structure
-------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle

*.audit_file_dest='C:\app\oracle\admin\windb\adump'
*.audit_file_dest='/u02/app/oracle/admin/windb/adump'

mkdir /u02/app/oracle/admin/windb/adump

*.control_files='C:\app\oracle\oradata\windb\control01.ctl','C:\app\oracle\fast_recovery_area\windb\control02.ctl'
*.control_files='/u02/app/oracle/oradata/windb/control01.ctl','/u02/app/oracle/oradata/windb/control02.ctl'

mkdir /u02/app/oracle/oradata/windb/

*.db_name='windb'
*.db_name='windb'

*.db_recovery_file_dest='C:\app\oracle\fast_recovery_area\windb'
*.db_recovery_file_dest='/u02/app/oracle/oradata/windb/fast_recovery_area'

mkdir -p /u02/app/oracle/oradata/lindb/fast_recovery_area


*.diagnostic_dest='C:\app\oracle'
*.diagnostic_dest='/u02/app/oracle/oradata/windb/diag'

mkdir -p /u02/app/oracle/oradata/windb/diag

*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'


*.local_listener='LISTENER_windb'
*.local_listener='LISTENER'



Open the TRANSPORT.SQL and edit the changes corresponding to the directory structure
-------------------------------------------------------------

Mention the pfile location

new redo log files

datafile location

temp file locations

pfile location

Export the new database ORACLE_SID
-------------------------------------------------------------

You will observe the database is an idle instance:-

You can run

@TRANSPORT.SQL

or execute commands step by step

All the database files will be restored as in the source database.

Now run the following scripts

catupgrd.sql
utlirp.sql
utlrp.sql


The complete exercise, right from Installing software to migrating the complete database can be viewed at https://youtu.be/HpOn1rE_d-c

Feel free to comment and correct me if any mistakes.

Sunday, January 5, 2020

How To Add a New Disks to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration



=====================================================================================
How To Add a New Disks to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
=====================================================================================
Steps:-
1. scandisks, listdisks, querydisks
2. Confirm the disks are visible at OS level
3. Verify that kfed can read on the candidate disks
4. Check the current disk groups,header_status of disk groups that are available from SQL prompt and Create a dummy diskgroup.
5. Mount it and if mount state is MOUNTED, then drop the created TEST_DG(test disk group)
6. If the header_status = FORMER, then alter the diskgroup to add candidate disks using rebalance. If you validate the TOTAL_GB of diskgroup, then it should increase.


1) AS ORACLE OR GRID OS USER, RUN THE NEXT COMMANDS ON EACH NODE (IF ASMLIB API IS USED):
# /etc/init.d/oracleasm scandisks #(only this command needs to be executed as root user)
$> id
$> /etc/init.d/oracleasm listdisks
$> /etc/init.d/oracleasm querydisk <each disk from previous output>
2) CONFIRM THE DISKS ARE VISIBLE (I/O) AT OS LEVEL (AS ORACLE OR GRID OS USER) FROM EACH NODE:
$> id
$> ls -l <disk name>
$> dd if=<raw/character or block partition device in question> of=/dev/null count=100 bs=8192
Example:
$> dd if=/dev/sda1 of=/dev/null count=100 bs=8192 # raw device
3) VERIFY THAT KFED CAN READ ON THE CANDIDATE DISK(S) AT OS LEVEL (AS ORACLE OR GRID OS USER) FROM EACH NODE:
kfed is an undocumented ASM utility that is used to read and modify ASM metadata blocks.
$> kfed read <raw/character or block partition device in question>
Example:
kfed read /dev/sda1 | more
Note: If the disk is not a FORMER disk, then kfed on new disks will return: kfbh.type: 0 ; 0x002: KFBTYP_INVALID and that is expected.
4. VALIDATE THE DISKS BY CREATING DUMMY DISKGROUP
Before to add any new disk into existing diskgroup or while creating a new diskgroup, we need create a dummy diskgroup with that new disks and check If we are able to mount it successfully if we don't get any issue then the is no issue with these disk and it can be added into diskgroup.
=====================================================================================
i) CHECK THE DISKGROUPS THAT ARE AVAILABLE
=====================================================================================
oracle:+ASM@nmdc01 /oracle > sqlplus / as sysasm
SQL>select name Diskgroup,round(total_mb/1024,2) "Total_TB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup;
DISKGROUP Total_GB Free_GB Available%
---------- ---------- ---------- ----------
ARCHLOG 50 49.88 99.76
LOG2 10 9.68 96.87
LOG1 10 9.68 96.87
DATAFILE 299.98 41.17 13.73
=====================================================================================
ii) CHECK THE CONSISTENCY OF THAT NEW DISK, KNOW THE HEADER_STATUS AND ITS VALUE
SQL>select path,os_mb/1024,header_Status from v$asm_Disk where header_Status!='MEMBER';
PATH OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0 99.9941406 CANDIDATE
/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0 99.9941406 CANDIDATE
HEADER_STATUS
--------------
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement.
UNKNOWN - Automatic Storage Management disk header has not been read.
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option.
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict.
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
=====================================================================================
c) CREATE A DUMMY DISKGROUP WITH THAT NEW DISKS
=====================================================================================
SQL> CREATE DISKGROUP TEST_DG EXTERNAL REDUNDANCY DISK '<YOUR CANDIDATE DISK>';
Example:-
SQL> create diskgroup TEST_DG external redundancy disk '/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0','/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0';
Diskgroup created.
5) Now, manually mount it on the other ASM Diskgroup(s):
SQL> ALTER DISKGROUP TEST_DG MOUNT;
SQL> select name,state from v$asm_Diskgroup;
NAME STATE
------------- --------
ARCHLOG MOUNTED
LOG2 MOUNTED
LOG1 MOUNTED
DATAFILE MOUNTED
TEST_DG MOUNTED
SQL> drop diskgroup TEST_DG;
Diskgroup dropped.
SQL> select path,os_mb/1024,header_Status from v$asm_Disk where header_Status!='MEMBER';
PATH OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0 99.9941406 FORMER
/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0 99.9941406 FORMER
FORMER -> indicates that those disk were already part of the diskgroup and now it can be added into existing diskgroup or New diskgroup.
=====================================================================================
6) ADD THAT NEW DISK INTO EXISTING DISKGROUP WITH APPROPRIATE REBALANCE POWER LIMIT
=====================================================================================
REBALANCE - should be given based on number of CPU available on the host
SQL> alter diskgroup DATAFILE add disk '/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0' rebalance power 6;
Diskgroup altered.
After we issued above command we can monitor the rebalance operation by queriyng v$asm_operation view.
SQL> Select operation, state, power, est_rate, est_minutes, round((sofar/EST_WORK)*100,2) pct_comp From v$asm_operation;
OPERA STAT POWER EST_RATE EST_MINUTES PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN 6 4912 2 16.72
When it returns "no rows selected" re-balance activity currently in completed.
SQL>select name Diskgroup,round(total_mb/1024,2) "Total_TB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup;
DISKGROUP Total_GB Free_GB Available%
---------- ---------- ---------- ----------
ARCHLOG 50 49.88 99.76
LOG2 10 9.68 96.87
LOG1 10 9.68 96.87
DATAFILE 399.97 141.16 35.29
After we add the new disk DATAFILE diskgroup capacity got increased and header_status of that new disk also got changed to MEMBER.
References:-
(Doc ID 557348.1)

Thursday, January 2, 2020

LISTENER is in restricted mode because post upgrade the database went into restricted mode



[oracle@oeldbapex dbs]$ ps -ef | grep tns
root        15     2  0  2019 ?        00:00:00 [netns]
oracle    1684     1  0 14:35 ?        00:00:00 /u02/sw//bin/tnslsnr LISTENERLINDB -inherit
oracle    1695   352  0 14:36 pts/8    00:00:00 grep tns
[oracle@oeldbapex dbs]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 02-JAN-2020 14:36:50

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENERLINDB
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                02-JAN-2020 14:35:30
Uptime                    0 days 0 hr. 1 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/sw/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oeldbapex/listenerlindb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldbapex.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "lindb" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
Service "lindbXDB" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oeldbapex dbs]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 02-JAN-2020 14:38:24

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENERLINDB
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                02-JAN-2020 14:35:30
Uptime                    0 days 0 hr. 2 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/sw/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oeldbapex/listenerlindb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldbapex.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "lindb" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
Service "lindbXDB" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oeldbapex dbs]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 02-JAN-2020 14:38:39

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENERLINDB
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                02-JAN-2020 14:35:30
Uptime                    0 days 0 hr. 3 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/sw/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oeldbapex/listenerlindb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldbapex.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "lindb" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
Service "lindbXDB" has 1 instance(s).
  Instance "lindb", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oeldbapex dbs]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Jan 2 14:39:24 2020
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>  Select logins from v$instance;

LOGINS
----------
RESTRICTED

SQL> select a.INSTANCE_NAME, a.ACTIVE_STATE,a.logins, b.open_mode
from v$instance a inner join v$database b
on UPPER(a.instance_name) = b.name;  2    3

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
lindb            NORMAL    RESTRICTED READ WRITE

SQL> alter system disable restricted session;

System altered.

SQL>  select logins from v$instance;

LOGINS
----------
ALLOWED

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@oeldbapex dbs]$ lsnrctl status LISTENERLINDB

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 02-JAN-2020 14:41:10

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oeldbapex.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENERLINDB
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                02-JAN-2020 14:35:30
Uptime                    0 days 0 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/sw/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oeldbapex/listenerlindb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldbapex.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "lindb" has 1 instance(s).
  Instance "lindb", status READY, has 1 handler(s) for this service...
Service "lindbXDB" has 1 instance(s).
  Instance "lindb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oeldbapex dbs]$