Ivan Kartik - Oracle and Linux Blog

How to not put company in to a risk (from DBA perspective)

Let me start this post with following quotation "People are definitely a company's greatest asset. A company is only as good as the people it keeps".
I fully agree with these sentences because regarding current topic it's definitely true and appropriate.

Internet is a great thing, it's a perfect source of information. Back to 1998 in times when I started with my first steps with Oracle Database there wasn't so many public knowledge sources about it. Software had been shipped on CDs and it had been very rare to see online documentation. Not many books in the book store, no Blogs and of course no Oracle ACE program as well :-). Those days are just memories now and by the time Internet became great source of information or software like Oracle Database XE thus place where one can get great portion of knowledge or even lessons learnt from other people thus it's very easy to leverage the experience. The side effects of this transformation of Internet is that many times people take information found there as a single point of truth, many people have slow down their skills development, because many solutions for potential problems can be easily found there moreover less experienced people don't study things closely they found or they are not so careful. The potential result could be at least unpleasant.


1. Your company may become under-licensed

I hear very often that "Oracle is too expensive" or "We pay a lot of money for it". Honestly I agree, it is expensive but also has lot to offer for that price. But most of the time when I hear such complains, I always ask questions like "Does it need to be?", "Do you really need all stuff (options, packs or other features) you pay for?". But there is another potential problem I'd like to point out in this paragraph. Every company has (should have) department or specific group of people responsible for Software Asset Management (or precisely Software Licenses Management). They maintain the evidence of license contracts, current price holds and usually they should have software repository which holds all information about used software and purchased licenses. What does it with IT staff like DBAs? What does it have with Oracle as technology itself? A quite lot.

In old times DBA's world and scope was everything what was stored or has happened within a database. Experienced DBA knew that not just DB configuration and application code have influenced database performance, so they felt that it has been necessary to see things in full picture thus to have solid understanding of full stack from the bottom to the top which means to understand things around storage layer, OS layer, network layer, DB layer (of course) and application layer as well (SQL and PL/SQL). This knowledge is and always has been influential to the fast identification and resolution of issues. In fact exactly this approach came later known as DBA 2.0 - The Next Generation DBA initiative back in 2008 (perhaps too late but at least...).
Database area has moved further since 2008 (now probably it's time for DBA 3.0) and in these days modern DBA should be familiar with current trends, e.g. Cloud technologies, automation and also probably the most underestimated thing in DBA's world - Licensing.
As I stated in the beginning of this article amount of information on Internet growth over years dramatically and it is possible to find many Cookbooks, HOWTOs or even scripts.

Consider this example taken from known web page found on Internet listed in top 3 results of search engine) according to page rank it's obvious that this page might have huge number of visitors:

---------------------------------
If you need to create a snapshot manually, because you don’t like the one-hour interval, or if you disabled taking snapshots at all:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Create a report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

This script will ask you for the format of the report (html or plain text), the snapshot ID for start and end of the report (an overview of the last n day’s snapshots is given) and for a report name, that’s used for the report file name (.html or .lst).

Stay happy with your databases,
---------------------------------

Now what's the catch here? Let me ask you couple of questions:

- Did you know that AWR feature is part of Diagnostic pack which is separately licensed pack?
- Does have your Oracle Database Diagnostic pack licensed?
- Does your instance control usage of Diagnostic pack?

If your answer was 3x "No" (or "No" to last two questions) your database might be under-licensed at this moment. In this particular case the absence of this knowledge and using the default configuration settings resulted to under-licensed environment. Answer for the second question should be given by department or team responsible for Software Asset Management (mentioned at the top of this article).
Each new version of the database comes with new and interesting features, not just for administrators but also for developers. It's almost impossible to expect from developer that he/she would know how the features are licensed but this is the point which DBA should be aware of, moreover only DBA is able to control usage of extra paid features.
Well perhaps, in this case the script is free of charge but database software and especially feature which is being used is not free at all.
Let's say that your DB environment has (just) 4xCPU (Intel) cores while list price (CPU metric) for Diagnostic pack is approx. 5000 USD (check your price hold), that means approx. 10000 USD (CapEx) and (approx.) 2200+ USD (OpEx every year) "damage" for your company. The web page states "Stay happy with your databases", unfortunately in this perspective it sounds more like sarcasm. Although Diagnostic pack is very useful pack but this is not definitely the best way how to tell to the management "We need it!".

I checked several Blogs listed in top 20 results given by my favourite search engine. Almost of them were from well known people in Oracle community (most of them had been written by members of the Oracle ACE program, thus more less trusted persons in the Oracle community) but only one of them contained disclaimer or warning about usage of extra paid option/pack. But also this particular page provides scripts where the disclaimer/warning is missing among other remarks in top of the script.

Knowledge is key here and study of new features shouldn't be limited to features only but I believe that study of licensing implications should be part of your standard practice. This is probably bad message for a Google/Stackoverflow DBAs, which are (very often) lazy and refusing continuous self-education, relying on the knowledge of the other's instead on their own. Unfortunately during my consultancies I often see that database environments are not licensed correctly, let's say 7 of 10 environments and that's really bad ratio.
Note that Licensing is really complex topic (don't hesitate to hire some consultant well experienced in this area) but to understand the basics could protect you from potential (non-technical) problems and your company as well.


2. Your system may become accidentaly unstable, unaccessible or even compromized

This paragraph is about different type of hidden danger while the source (Internet of course) remains the same. I'm not going to talk about it's generally bad idea to have accessible listener/dispatcher port accessible from Internet, about using of default passwords or other basic things. Let me explain the issue by example. I have created simple demo which is using the same command as in previous paragraph, you can find it on this link: http://ivan.kartik.sk/demos/pjdemo.html

Have you ever heard about "PasteJacking"? So, this is it. Now consider less skilled DBA with lack of knowledge, with bad (or not so good) stress factor in stressful situation such as performance degradation, outage of mission or business critical database. He finds a "quick-win solution", a script published in some article on the Internet. What he will do in this situation?
Now consider the script which may contain DROP commands or other sophisticated evil code...

Know the source and even if source is page of well known person in the community or industry doesn't necessarily mean that his pages weren't compromised thanks to bug in publishing system he is using.
You should always be careful and check and fully understand what you are going to "paste" to the SQLPlus, shell, etc.

Another story is using "solution" which is not appropriate for the problem. Some time ago some guys were facing to some issue where the flushing of buffer cache was a workaround that helped. Of course they found this workaround on Internet. You may guess what happened during another completely different issue although with similar symptoms, but unfortunately not the same. Yes, flushing of buffer cache was the first thing that guys tried in order to "fix" the problem. It didn't work for that particular issue moreover it has created another one and outstanding issue suddenly became even worse.
Well, glucose is commonly used in medicine but don't try give it to a patient with diabetes when he fainted due to lack of insulin.

Rock solid knowledge and adopting a good practices or habits is the key how to not put your database in to problems or even danger. With rock solid knowledge you will understand Blogs and articles published on Internet as a additional information sources and not as a easy problem solvers. Knowledge is the asset for thus skilled people make company's greatest asset.

Let me finish this post with a funny quote: "Don't believe everything you read on or copying from the Internet. Abraham Lincoln"

Installation of Oracle Database 12c Release 2 on Redhat 7, Oracle Linux 7

Here is short installation HOWTO for Oracle Database 12c Release 2 for Redhat 7, Oracle Linux 7 (and possibly CentOS 7 which is not officially certified and supported distribution). This post covers Oracle Database software installation (only). In case you want to use ASM or other Grid features and you don't have Grid Infrastructure 12c R2 installed refer to my previous post: http://ivan.kartik.sk/index.php?controller=post&action=view&id_post=76 If you don't need ASM or GI features you can continue with this part directly.
This post also contains some steps which were used during Oracle Grid Infrastructure 12c R2 installation, so you can skip those if you followed my GI installation HOWTO.

1. OS preparation

# - stands for "root" action
$ - stands for "oracle" (owner of Oracle software)

Note: You can skip this step if you have installed the Grid Infrastructure Software.

# groupadd dba
# groupadd oinstall
# useradd -g oinstall -G dba oracle

Create ORACLE_HOME directory. (Note: In my example I use /opt/oracle/12201 directory, change it to your preferred location):

# mkdir -p /opt/oracle/12201
# chown -R oracle:oinstall /opt/oracle

2. Install required packages

Note: You can skip this step completely once you have installed the Grid Infrastructure Software (as described in my previous post related to Grid Infrastructure installation).

First install required packages (use this command if your computer has access to Internet or you have registered and enabled your media as yum repository).

Add/set kernel parameters to/in /etc/sysctl.d/99-sysctl.conf file:

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

Execute sysctl -p command to apply settings:

# sysctl -p

Add/set limits to/in /etc/security/limits.d/99-oracle-rdbms.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

4. Software download and install

Download installation archive (linuxx64_12201_database.zip) from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html

Unpack the installation archive and start the installer:

$ unzip linuxx64_12201_database.zip
$ cd database
$ ./runInstaller

Next steps after installation

In case you want startup your Oracle Database(s) automatically and you don't have Oracle Grid Infrastructure installed, you can follow the steps described in the post Configuring automatic startup of Oracle Database under systemd on RHEL 7/OEL 7/CentOS 7 at http://ivan.kartik.sk/index.php?controller=post&action=view&id_post=65

 

 

 

Bad bad systemd...

This post will be short...
I'm not a big fan of systemd and honestly I can't understand how this disease (which became an epidemy) could have happened to Linux. Sometimes I wish an eternal insomnia to person who is responsible for this (I am joking of course). Anyway systemd is (unfortunately) part of my OS and (despite numerous benefits) it often offers me surprising moments.

[root@ol7-01 ~]# reboot
Failed to start reboot.target: Connection timed out
See system logs and 'systemctl status reboot.target' for details.
Failed to open /dev/initctl: No such device or address
Failed to talk to init daemon.

Of course the same result was produced by systemctl reboot, init 6, reboot -f -r now or even systemctl -f reboot

Last resort has been in using the deadly force in order to reboot the system:

[root@ol7-01 ~]# systemctl -ff reboot
Rebooting.

I used the "deadly force" term for "-ff" (you can use --force --force, or -f -f) because it's appropriate for logic behind this directive. Take a look what man page for systemd says:

"When used with halt, poweroff, reboot or kexec, execute the selected operation without shutting down all units. However, all processes will be killed forcibly and all file systems are unmounted or remounted read-only. This is hence a drastic but relatively safe option to request an immediate reboot. If --force is specified twice for these operations, they will be executed immediately without terminating any processes or unmounting any file systems. Warning: specifying --force twice with any of these operations might result in data loss."

So use this option as a last resort.

Installation of Oracle Grid Infrastructure 12c Release 2 on Redhat Linux 7 and Oracle Linux 7

I prepared short installation HOWTO Oracle Grid Infrastructure 12c Release 2 for Redhat 7, Oracle Linux 7 (and Centos 7). Installation on first two (supported) Linux distros was almost fine, just some workaround were needed. But you can expect some problems on (unsupported) Centos distro where some workarounds were needed in order to have fully working product. These specific workarounds you can at the end of this post. This post also tries to answer some potential questions you may have.

Oracle 12cR2 brought interesting new feature called Oracle ASM Filter Driver (ASMFD) which is in fact replacement for ASMLIB. As official documentation states ASMFD simplifies configuration and management of disk devices for Oracle ASM (just like AMSLIB does) moreover ASMFD is using a "Filter Driver" that rejects any invalid I/O request in order to protect ASM disks by filtering out non-Oracle I/O operations which could cause accidental overwrites thus corruption. Note that ASMFD cannot be used simultaneously with ASMLIB.

So (logically) this article covers installation of Oracle GI 12.2.0.1 aka 12cR2 for x86-64 Linux using ASMFD feature. I've used "Minimal" version of Linux installation.

1. OS preparation

# - stands for "root" user action
$ - stands for "oracle" user (owner of Oracle software) action

# groupadd dba
# groupadd oinstall
# useradd -g oinstall -G dba oracle

Create $GI_HOME directory. (Note: In my example I use /opt/oraclecrs/12201 directory, change it to your preferred location):

# mkdir -p /opt/oraclecrs/12201
# chown -R oracle:oinstall /opt/oraclecrs

2. Install required packages

Install required packages (use this command if your computer has access to Internet or you have registered and enabled your media as yum repository). Note: Some packages are required by Database installation which I expect it will be the next step once Oracle GI has been installed. If you are using Oracle Linux or in case of Redhat if have configured OL public YUM repository then you can use "yum install oracle-database-server-12cR2-preinstall" and it will install all required packages and also perform Linux parameters configuration described in next step (So, you can skip this step and step#3 as well). Note, in case of Redhat this package will install also UEK kernel.

# yum install unzip xorg-x11-utils xorg-x11-apps xorg-x11-utils compat-libcap1 libstc++-devel \ 
gcc-c++ glibc-devel nfs-utils-1.2.3-15 ksh libaio-devel sysstat smartmontools

3. Set OS kernel parameters and user limits

Add/set kernel parameters to/in /etc/sysctl.d/99-sysctl.conf

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

Execute sysctl -p command:

# sysctl -p

Add/set limits to/in /etc/security/limits.d/99-oracle-rdbms.conf:

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

4. Software download and deploy

Create install directory in /home/oracle for downloaded software:

$ mkdir /home/oracle/install

Download linuxx64_12201_grid_home.zip file from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html to the /home/oracle/install directory.

Deploy GRID software:

$ cd /opt/oraclecrs/12201
$ unzip -q /home/oracle/install/linuxx64_12201_grid_home.zip

Note: Since Oracle 12cR2 you can unzip archive within $GI_HOME.

5. Install package cvuqdisk RPM package

Install $GI_HOME/cv/rpm/cvuqdisk-1.0.10-1.rpm package from $GI_HOME. Note in case of Oracle Linux with internet connection or Redhat with configured OL public YUM repository you can use "yum install cvuqdisk" command.

# yum localinstall /opt/oraclecrs/12201/cv/rpm/cvuqdisk-1.0.10-1.rpm

6. ASM disk configuration (ASMFD)

Now we need to prepare (stamp) the disks that will be used for Oracle ASM. Note in my case /dev/sdb and /dev/sdc are dedicated for ASM.

# /opt/oraclecrs/12201/bin/asmcmd afd_label DATA_0000 /dev/sdb --init
# /opt/oraclecrs/12201/bin/asmcmd afd_label DATA_0001 /dev/sdc --init

Just simple check if disks are prepared:

# /opt/oraclecrs/12201/bin/asmcmd afd_lslbl /dev/sdb
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA_0000                             /dev/sdb

# /opt/oraclecrs/12201/bin/asmcmd afd_lslbl /dev/sdc
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA_0001                             /dev/sdc

Possible error:

# ./bin/asmcmd afd_label DATA_0000 /dev/sdb
/opt/oraclecrs/12201/bin/kfod: line 22: /scratch/app/user/product/12.2.0/grid/bin/kfod.bin: No such file or directory

Solution:

Modify $GI_HOME/bin/kfod (in my case /opt/oraclecrs/12201/bin/kfod ) and replace path in OHOME variable to correct one.
Change this entry: OHOME=/scratch/app/user/product/12.2.0/grid to this: OHOME=/opt/oraclecrs/12201

 

7. Run gridSetup in order to configure Grid infrastructure

$ cd /opt/oraclecrs/12201
$ ./gridSetup.sh

 8. Use "Configure Oracle ASM Filter Driver" option during ASM disk group creation

 undefined

When your installation was finished successfully you can log on to ASM instance in order to check v$asm_disk.

 

SQL> select path, name, library, header_status from v$asm_disk;

PATH            NAME            LIBRARY                                                          HEADER_STATU
--------------- --------------- ---------------------------------------------------------------- ------------
AFD:DATA_0000   DATA_0000       AFD Library - Generic , version 3 (KABI_V3)                      MEMBER
AFD:DATA_0001   DATA_0001       AFD Library - Generic , version 3 (KABI_V3)                      MEMBER

As you can see AFD Library (thus ASMFD) is being used. Output is very similar to output from ASM Library (ASMLIB).
Just for comparison there is output from ASMLIB configuration:

SQL> select path, name, library, header_status from v$asm_disk;

PATH            NAME            LIBRARY                                                          HEADER_STATU
--------------- --------------- ---------------------------------------------------------------- ------------
ORCL:DATA_0000  DATA_0000       ASM Library - Generic Linux, version 2.0.12 (KABI_V2)            MEMBER
ORCL:DATA_0001  DATA_0001       ASM Library - Generic Linux, version 2.0.12 (KABI_V2)            MEMBER

 

Possible Errors and Solutions:

Problem:
[INS-41223] ASM Filter Driver is not supported on this platform

AFD-620: AFD is not supported on this operating system version: ...
AFD-9201: Not Supported

Solution: Well, message is clear, you are using unsupported Linux distribution (e.g.: Centos). If you have a good reason to play with Centos see "Centos specific important steps" bellow, otherwise I would suggest to use certified Linux distribution (e.g.: Redhat Enteprise Linux, Oracle Linux).


Problem:
[INS-41223] ASM Filter Driver is not supported on this platform
Action - To proceed, do not specify or select the Oracle ASM Filter Driver option.
Additional Information:
AFD-9202: AFD can not be installed/loaded because ASMLib is installed.
AFD-9201: Not Supported

Solution: Again, message is very clear. You can not use ASMFD while ASMLIB is in use. So you have to choose one. If there is reason for using ASMLIB then don't check the "Configure Oracle ASM Filter Driver" option during ASM disk group creation.

Problem:
bin/kfod: line 22: /scratch/app/user/product/12.2.0/grid/bin/kfod.bin: No such file or directory

Solution:
Edit $GI_HOME/bin/kfod script and replace path in OHOME=/scratch/app/user/product/12.2.0/grid to correct one.

 

Centos specific important steps:

So you are stubborn, well then... :-) Couple of tweaks have to be perfomed prior "gridSetup" execution (before step #7).

1. Edit $GI_HOME/lib/osds_acfslib.pm file and add following line:

($release =~ /^centos-release/) ||    # Centos Linux

- after line 359 (as next line after "enterprise-release" condition)
- after line 911 (as next line "enterprise-release" condition, originally at 910 line)
- after line 1146 (as next line "enterprise-release" condition, originally at 1144 line)

2. Edit /etc/redhat-release file

# cp /etc/redhat-release /etc/redhat-release.old
# sed -e 's/Centos/Redhat/' /etc/redhat-release.old > /etc/redhat-release

 Now you can execute gridSetup.

 

Next step after installation:

Installation of Oracle Database 12c Release 2 Database Software - http://ivan.kartik.sk/index.php?controller=post&action=view&id_post=78

 

Oracle Database 12c Release 2 available for download

Pretty long awaited second release of 12c (On-Premise) version is (apparently) officially available for download at: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Currently Oracle Solaris SPARC, Oracle Solaris x86-64 and (of course) Linux x86-64 versions are available.
As always I suggest to read a New features Guide in order to explore new things brought by current release: http://docs.oracle.com/database/122/NEWFT/toc.htm

Enjoy learning and exploring...

 

Oracle Linux 7.3 release available

Just one week after RHEL 7.3 release Oracle has announced the general availability of Oracle Linux 7.3 as well.

There is lot of improvements and features but the most notable fact about OL 7.3 is that comes with Unbreakable Enterprise Kernel 4 (UEK4) and older UEK3 is not shipped in distribution while it still supported. Starting with 7.3 version OL supports UEFI Secure Boot. The bad news is that kpatch package has been removed from the distribution.

For more information about new version see the release notes at: https://docs.oracle.com/cd/E52668_01/E79987/html/index.html

You can download the ISOs either from MOS site or Oracle Software Delivery Cloud (aka edelivery.oracle.com).

 

 

Oracle Database 12cR2 online documentation available

Following the Cloud-first strategy Oracle has announced the general availability of Oracle Database 12c Release 2 via the new Oracle Exadata Express Cloud Service. Software for on-premises installation has not been released yet but if you want to learn what's new or what is coming in the 12cR2 you can browse documentation which is available at: http://docs.oracle.com/en/database/

Home ← Older posts