Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for January, 2016

CELL_OFFLOAD_PROCESSING value in Non-Exadata Surrounds! — “High ‘ksv master wait’ And ‘ASM File Metadata Operation’ Wait Events

Posted by FatDBA on January 27, 2016

It was a case some time back while performing the performance analysis for one of the database, i encountered a curious wait event ‘ASM file metadata operation’. After further analysis i discovered that it’s happening specially when doing operations such as a DROP TABLESPACE, or in this case when using Data Pump. Though the server was basically not doing anything and much of the CPU cycles were IDLE but there was this strange wait event on ‘ASM file metadata operation’ with high waits on ‘ksv master wait’.

Below is the graphical depiction of the situation.
asm1

Image to highlight one of the major spike during the period.
asm2

Reason:
In an Exadata surrounds this is the suitable setting, nevertheless, in a non-Exadata environment, which it was in this case, this causes performance issues to arise as processes on the RDBMS awaits on a reply from the ASM which is trying to delivery smart-scan results.

Solution:
The following solutions are available for non-Exadata databases:
1. For the quickest solution, use the workaround. The workaround does not negatively impact non-Exadata databases.
This parameter is to be set on the database instance.
alter system set cell_offload_processing = false;
2. Upgrade to 12.1, when available. OR
3. Apply the 11.2.0.3 patch set OR
4. Apply one-off Patch 11800170, if available for your RDBMS and Grid Homes


Hope That Helps
Prashant Dixit

Advertisement

Posted in Advanced | Tagged: , | Leave a Comment »

Part 1: ASM Installation on 11gR2 (VMWare)

Posted by FatDBA on January 10, 2016

Hello Everyone,
Today i would like to start series/chapters describing Oracle Automatic Storage Management (Oracle ASM) concepts and provides an overview of Oracle ASM features. Followed posts will covers subjects like Installation, Configuration, Administration/Management, Monitoring. Troubleshooting and Optimization etc.

In this maiden post (Part 1) i would like to discuss and elaborate about ASM installation and related areas.

Prerequisites:
Considering that you already have the OS ready with all packages per-installed before we begin our ASM installation on the top. I will start with right from the scratch.

Step 1:
Preparing Disks or Partitions which will be used while creating the ASM diskgroups.
I’ve created 3 Persistent Disks each of 4GB in size from the VM Disk (I will perform all steps in VM environment).

This is how the VM Setting will look like once you are done with the Disk creation.
*Forgot about the Fifth Hard Disk of 10GB for now. Will explain the usage later on the series.

1

Once you have the disks created, Next you’ll have to format the newly created disks to make them usable: Using fdisk command. Command displays the status of available newly created partitions/disks as:
/dev/sdb, /dev/sdc, /dev/sdd – Each of 4GB (4294 MBs) in size.

[root@localhost ~]# fdisk -l

Disk /dev/sda: 91.3 GB, 91268055040 bytes, 178257920 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000aab6c

Device Boot Start End Blocks Id System
/dev/sda1 * 2048 1026047 512000 83 Linux
/dev/sda2 1026048 178257919 88615936 8e Linux LVM

Disk /dev/sdb: 4294 MB, 4294967296 bytes, 8388608 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Click Here to Read More!!

Posted in Advanced, Basics | Tagged: | Leave a Comment »

runInstaller Error: An unexpected error has been detected by HotSpot Virtual Machine

Posted by FatDBA on January 6, 2016

Hello Everyone,
Installing your Oracle Software using GUI Method requires to call “runInstaller” script and is always an easy step if you have proper permissions, DISPLAY settings in place.
But here i would like to discuss one of the case where I’ve spent several hours to fix one of the error that occurred every-time i called runInstaller script even after setting all required permissions and DISPLAY variables.

It fails to render the installer and creates a log file under /tmp directory with below mentioned contents.

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0x0000003e2ce14d70, pid=4000, tid=140717162321680
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.5.0_51-b10 mixed mode)
# Problematic frame:
# C [ld-linux-x86-64.so.2+0x14d70]
#

————— T H R E A D —————

Current thread (0x000000004220d3f0): JavaThread “AWT-EventQueue-0” [_thread_in_native, id=4014]

siginfo:si_signo=11, si_errno=0, si_code=128, si_addr=0x0000000000000000

Registers:
RAX=0x0000000000000001, RBX=0x000000004216ae50, RCX=0x000000009eba2203, RDX=0x000000000fabfbff
RSP=0x00007ffb44792278, RBP=0x00007ffb447923c0, RSI=0x0000000000000000, RDI=0x0000000000000058
R8 =0x0000000000000000, R9 =0x0000000000000000, R10=0x00007ffb447921f0, R11=0x000000004216ae50
R12=0x00007ffb447923e8, R13=0x0000000041f85330, R14=0x0000000000000000, R15=0x0000000000000000
RIP=0x0000003e2ce14d70, EFL=0x0000000000010202, CSGSFS=0x0000000000000033, ERR=0x0000000000000000
TRAPNO=0x000000000000000d

Top of Stack: (sp=0x00007ffb44792278)
0x00007ffb44792278: 0000003e2ce0aaea 0000000000000000

Signal Handlers:
SIGSEGV: [libjvm.so+0x67ed60], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGBUS: [libjvm.so+0x67ed60], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGFPE: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGPIPE: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGILL: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGUSR1: SIG_DFL, sa_mask[0]=0x00000000, sa_flags=0x00000000
SIGUSR2: [libjvm.so+0x583ed0], sa_mask[0]=0x00000000, sa_flags=0x14000004
SIGHUP: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGINT: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGQUIT: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGTERM: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004

————— S Y S T E M —————

OS:Red Hat Enterprise Linux Server release 6.0 (Santiago)

uname:Linux 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 06:42:08 PST 2015 x86_64
libc:glibc 2.12 NPTL 2.12
rlimit: STACK 10240k, CORE 0k, NPROC 16384, NOFILE 65536, AS infinity
load average:0.09 0.06 0.08

CPU:total 1 em64t

Memory: 4k page, physical 2046684k(69828k free), swap 2031612k(2031612k free)

vm_info: Java HotSpot(TM) 64-Bit Server VM (1.5.0_51-b10) for linux-amd64, built on Jun 6 2013 09:59:46 by java_re with gcc 3.2.2 (SuSE Linux)

time: Sat Jan 2 23:09:21 2016
elapsed time: 2 seconds

The workaround to the problem is to set “LD_BIND_NOW” environment variable to a value “1” as shown below and re-launch the installer.

bash-4.1$ export LD_BIND_NOW=1
bash-4.1$ ./runInstaller
Starting Oracle Universal Installer…

Checking Temp space: must be greater than 120 MB. Actual 27339 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4031 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-07-01_03-29-40AM. Please wait …
bash-4.1$

This bug seems to have reported on 11.2.0.1 & 11.2.0.3.

Hope That Helps
Prashant Dixit

Posted in Advanced, Basics | Tagged: , | 3 Comments »

Linux YUM – Error: Cannot retrieve repository metadata (repomd.xml) for repository

Posted by FatDBA on January 6, 2016

Some time back I’ve got an error message with YUM even when the installation and configuration went successful.
Where it fails every time i called any of the YUM commands with error message “Cannot retrieve repository metadata (repomd.xml) for repository”.

[root@Fatdba ~]# yum list
Loaded plugins: refresh-packagekit
Repository ol6_latest is listed more than once in the configuration
Repository ol6_ga_base is listed more than once in the configuration
ftp://obiftp/YUM_local/GDS/obi/6.1/repodata/repomd.xml: [Errno 14] PYCURL ERROR 22 – “The requested URL returned error: 502”
Trying other mirror.
Error: Cannot retrieve repository metadata (repomd.xml) for repository: base_el6_local. Please verify its path and try again

Solution:
Try the following sequence of steps to fix this problem.

$ sudo su –
# cd /etc/yum.repos.d
# rm -f *
# wget http://public-yum.oracle.com/public-yum-ol6.repo — This needs Internet Connection
# yum clean all
# yum makecache

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

Oracle GI 11.2 Installation on RHEL 7 – Error: ohasd failed to start the Clusterware.

Posted by FatDBA on January 6, 2016

Recently as a part of solution i proposed for a new infrastructure for one of the customer, project team came with an error which they encountered during the GRID Infrastructure – Especially soon after executing the ‘Root.sh’ script. This was actually 11.2.0.4 Grid Infrastructure software installation on Oracle Enterprise 7 which is the latest release from Oracle Corp.

While troubleshooting the problem i experienced much pain getting it to install. The installation process fails when the root.sh script is run.
* Sometimes to configure Grid Infrastructure for a Stand-Alone Server system asks to run the following command as the root user:
/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

The error reported is:

[root@localhost /]# /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node localhost successfully pinned.
Adding Clusterware entries to inittab
ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2016-01-01 02:14:46.806:
[client(11401)]CRS-2101:The OLR was formatted using version 3.
2016-01-01 02:14:49.572:
[client(11424)]CRS-1001:The OCR was formatted using version 3.

ohasd failed to start at /u01/app/11.2.0/grid/crs/install/roothas.pl line 377, line 4.

I hunted though various blog posts and even Oracle Metalink initially but all of them were was of little to no use.
Finally, I stumbled across an apparently poorly indexed (and titled) support note (1951613.1) that made reference to a RHEL 7 specific patch. The patch number is: 18370031.

So a Patch download and a new installation process later, I was finally able to get the GI installer to properly register the ohasd services. In the end, I was glad it was a patch, that resolved the issue since (in theory) Oracle will support it. I was surprised that the Oracle Support tech was not able to locate the patch 🙂

In applying the patch it is a little different. You have to run the GI installer to the point where it instructs you to run root.sh. Before you run root.sh, you then use OPatch to install the provided patch. Then finally you run root.sh.

Below provided are the steps performed during the fix.
1. First i had to deinstall previous GRID configuration (Where I’ve got that error message after roo.sh execution).
– During the deinstallation process it will ask you to execute few scripts which will ultimately help you to deinstall the entire Oracle Restart stack.
2. Download, Unzip and Apply the patch using OPATCH.
3. Execute the root.sh script once you applied the patch.
4. Check the services status using crs_stat.

 

Step 1:

Deinstall previous GRID configuration
[root@localhost deinstall]# su – oracle
Last login: Fri Jan 1 02:17:02 EST 2016 on pts/1
[oracle@localhost ~]$ cd /u01/app/11.2.0/grid/deinstall
[oracle@localhost deinstall]$ ./deinstall

Checking for required files and bootstrapping …
Please wait …
Location of logs /tmp/deinstall2016-01-01_02-30-16AM/logs/
Click Here to Read More!!

Posted in Advanced | Tagged: | 26 Comments »

 
%d bloggers like this: