Dilli's Oracle/MySQL Linux Blog

Sunday, April 19, 2020

Install Oracle Database 19c in CentOS 8

8:57 AM Posted by Dilli Raj Maharjan Linux, Oracle 19c 1 comment

Oracle Database 19c, is the long term support release of the Oracle Database 12c and 18c. It offers Premier and Extended Support through to 31st March, 2023 and 31st March, 2026 respectively. It is available for all of the Operating system platforms: Linux, Windows, Solaris, HP/UX and AIX platforms. It is available for Oracle Cloud too.

Though Linux version 8 is not in the support matrix we can tweak parameter to install Oracle 19c in Linux version 8. Following is the support matrix for Oracle 19c installation on Linux.

Supported Linux OS matrix for Oracle 19c
Operating System	Kernel	Kernel Version
Oracle Linux 7.4	Unbreakable Enterprise Kernel 4	4.1.12-124.19.2.el7uek.x86_64 or later
Oracle Linux 7.4	Unbreakable Enterprise Kernel 5	4.14.35-1818.1.6.el7uek.x86_64 or later
Oracle Linux 7.5	Red Hat Compatible kernel	3.10.0-862.11.6.el7.x86_64 or later
Red Hat Enterprise Linux 7.5		3.10.0-862.11.6.el7.x86_64 or later
SUSE Linux Enterprise Server 12 SP3		4.4.162-94.72-default or later
SUSE Linux Enterprise Server 15		4.12.14-23-default or later

Modify Kernel parameters for Oracle 19c. Add following parameters and values to file /etc/sysctl.conf. Once sysctl.conf file is modified execute sysctl -l /etc/sysctl.conf to reflect change.

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

sysctl -p /etc/sysctl.conf

Kernel parameters added to /etc/sysctl.conf

Execute sysctl -p /etc/sysctl.conf to reflect kernel parameter change.

Modify user limits. Add following lines to /etc/security/limits.d/oracle-limit.conf file.
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 134217728
oracle soft memlock 134217728

User limits modified in file oracle-limit.conf

Install all required Linux packages. Execute following yum command as user root to install all required packages and their dependencies.
yum -y install bc binutils dnf elfutils-libelf libnsl libXau \

elfutils-libelf-devel fontconfig-devel gcc glibc glibc-devel \
ksh libaio libaio-devel libgcc librdmacm-devel librdmacm-devel libstdc++ \
libstdc++ libstdc++-devel libX11 libxcb libXi libXrender libXrender-devel \
libXtst make net-tools nfs-utils python36 smartmontools sysstat targetcli

yum installation on going.

Once all required package is installed it looks something like above.

Add all required groups and users.

Change password for user oracle

Verify user with id oracle command

Modify selinux parameter. Set SELINUX to permissive and execute setenforce permissive

Stop Firewall and disable firewall at startup

Create Oracle Home directory and change ownership to user oracle and group oinstall.

Upload downloaded Oracle 19c software to Oracle Home directory. Once upload is completed extract oracle software.

Switch user to oracle and configure required environment variables for Oracle.

Create additional directory for SQLPATH. This directory can be used for centralized sql files path.

It is always better to validate the display setting before you execute the runInstaller command. Install xterm linux package and execute xterm command to verify display has been configured properly. If display has been set properly then you will be poped up with new xterm windows as below.

xterm windows that will be displayed if you have properly configured display settings.

Export environment variable CV_ASSUME_DISTID to 7.6. Otherwise there will be compatibility error installation will not begin at all.

export CV_ASSUME_DISTID=OEL7.6

Change directory to Oracle Home where Installer file has been extracted. Execute runInstaller to being installation.

Landing window after executing runInstaller command. Select Create and configure option. Click on Next to continue.

Select Desktop class and click on Next to continue.

Provide global dbname Password and Click on Next to continue.

Select Inventory location and click on Next to continue.

Select Automatically run configuration script. Provide root password and click on Next to continue.

All pre-requisite Check passed successfully so Installation window directly redirected to Summary window. I have clicked on Back button to view list of the pre-requisite and its status.

List of Pre-requisites that have been checked and its status as success. Click on Next to continue.

Once again summary page. Click on Install to begin installation.

Installation in progress..

Prompted to confirm execution or root.sh script with user root and password provided duration configuration windows earlier. Click on Yes to continue.

Noticed database Configuration has failed. I have checked installation log and noticed following error message.
INFO: [Apr 18, 2020 11:36:32 AM] ... GenericInternalPlugIn.handleProcess() entered.
INFO: [Apr 18, 2020 11:36:32 AM] ... GenericInternalPlugIn: getting configAssistantParmas.
INFO: [Apr 18, 2020 11:36:32 AM] ... GenericInternalPlugIn: checking secretArguments.
INFO: [Apr 18, 2020 11:36:32 AM] ... GenericInternalPlugIn: starting read loop.
INFO: [Apr 18, 2020 11:36:48 AM] [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: [Apr 18, 2020 11:36:48 AM] Skipping line: [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: [Apr 18, 2020 11:36:48 AM] ACTION: Specify a free port.
INFO: [Apr 18, 2020 11:36:48 AM] Skipping line: ACTION: Specify a free port.
INFO: [Apr 18, 2020 11:36:49 AM] Completed Plugin named: Oracle Database Configuration Assistant
INFO: [Apr 18, 2020 11:36:49 AM] Setup completed with overall status as Failed

Database configuration failed.

Checked /etc/hosts file and noticed I do not have fully qualified domain name in the hosts file. So added fully qualified domain name and clicked on Retry button in Installer window.

Noticed database configuration has started without issue. Click on Details button to view detail log of the installation.

Click on Close to close Details window.

Once installation completes Database configuration was successful message will be displayed. Click on Close to close installation window.

My OCM 11g Journey

8:38 PM Posted by Dilli Raj Maharjan OCM 2 comments

My OCM badge

OCM was my BIG dream 😁. I had heard about OCM in 2005 and always dreamed to become OCM. Honestly, I was dying to become Oracle Certified Master.

I setup my mind to sit for OCM 11g exam in 2013. I enrolled for OCM 11g preparation class in August 2012. The class was phenomenal and had learned a lot from instructor Karan Dodwal. He taught us how to prepare and what to prepare for the exam like Oracle pro does. The class was not a common OCM preparation class it was an Oracle expert guiding us the proper way to prepare OCM. The class was so helpful to start my preparation. He is so inspiring and motivating.

I had attended all pre-requisite course from Oracle University and prepared for almost four years, till 2017. Though I had good instructor for OCM preparation, my OCM preparation was lit bit frustrating. Basically, I did not have list of activity to be done after reading each Oracle documentation. I had 3 days work week at that time so I spent plenty of time reading entire oracle documentation word by word. Since I did not have any list of activity to practice. My preparation was like 1 step forward and 2 step backward. Due to lack of proper activity guide, I was able to prepare just 1% in entire four years.

I was desperately searching for any reference book with list of activity for each skillset. I had read reviews about few OCM books and came to know that those books had not helped people. In mid 2016, I heard about the book "OCM 11G" -by KARMAN AGJAYEV A. I did not read a single review about the book but ordered the book from Amazon. I don't know how I decided to purchase a book that costs around 8000 in my currency, may be my sixth sense insist to do that. Though it took around 3 months to reach the book to my desk, purchasing it was one of my best decision I had made ever.

My Amazon Shipping confirmation email.

For me the book is like a good map. In real, it does not matter how good the map is, mountaineer has to reach summit himself. In contrast, whatever skill the mountaineer has, without a good map he is half prepared to reach summit. So good book, work experiences and dedication of an examiner are core components to pass OCM. I am not marketing this book, but literally this book is awesome and helped me a lot. The best part of the book is a list of activities to practice, Oracle document reference and in-depth explanation with solid and clear examples. I enjoyed a lot reading this book. Thank you Karman for writing such an awesome books.

Me with OCM 11g book.

Finally it happened, on the 19th December, 2017 I received email from Oracle that I passed OCM. I am very thankful to my friends in Banglore: Posh Kanta Pandey, Harihar Paneru and Ajay Acharya. They are good people who had supported me lot during my exam preparation. Without them it was not possible at all. I stayed with them for entire 2 months for the exam. Guys! you are rockstars. Thank you once again. I like to thank my family, friends and everyone who helped me to pass OCM exam.

Email from Oracle Certification Program after I passed exam.

Following are few tips I found useful during my exam.

Prepare exam as you do prepare for College exam. Write important point in paper and read them repeatedly.
Good Typing skill is important. Accuracy is more important than words per min. Signup for free in https://play.typeracer.com and do at least 50 type challenges every day.
Good Skill to navigate Oracle Documentation. Download Oracle documentation offline. Install web server, Apache and start navigating it without Search feature.
Practice on Linux machine, Forget Windows. If possible install Linux on your workstation. Version of Linux should match version specified in URL http://education.oracle.com.
Practice with normal USB keyboard. Not a fancy and bluetooth ones
At least 15 days leave prior to exam. Relax and prepare well. Don't get panic.
Linux tips and tricks to save time. Linux short cuts for copy/paste and others.
Linux text editor skills. Prepare for vim.
Mentally prepare to become OCM. You should believe it before You have it.

A page from my notebook.

A regret 😞

I am extremely sorry for posting this post 2 years late. I must have posted it immediately after exam. I am really a badass, always waiting for right time. I was unknown, the right time is right now, that's why it is termed as present, a gift to human mankind. So guys don't wait for right time, do it now.

Oracle Resource Manager

9:49 PM Posted by Dilli Raj Maharjan Oracle 11g Installation, Oracle Resource Manager 1 comment

Resource Manager

Oracle Database Resource Manager enables you to limit database resources. Oracle Database Resource Manager lets you specify how much of the resources you want to assign. It provides granular control of database resources allocated to users, applications, and services. It can be used to guarantee certain session a minimum and maximum amount of CPU. Limiting parallelism, active session pools to control maximum number of sessions within the group. The most interesting features is manage runaway sessions and switch groups. Manage runaway sessions help us with terminating user session whenever a session or call consumes more than specified resources. Any session in a particular group can be switched to another group if the session consumes more than specified resource.

Elements of Oracle Database Resource Manager

1. Resource consumer group

2. Resource plan

3. Resource plan directive

Resource consumer group is collection of user based on their need. SYS_GROUP and OTHER_GROUPS are two predefined groups. User SYS and SYSTEM belongs to SYS_GROUP and all other users within a plan that do not belongs to any group falls in OTHER_GROUPS.

Resource plan is the directives stating how the resource should be shared among the consumer groups.

The set of Reource plan directive determines how resources will be distributed among the users with in a Resource Plan.

Create dummy table for testing.