Find out Oracle home directory of running Oracle instance on Linux

If /etc/oratab is deleted or corrupted, then
ps aux | grep ora_pmon
oracle   10937  0.0  0.0 2445960 6732 ?        Ss   May25  11:01 ora_pmon_CDRUAT
strings /proc/10937/environ | grep ORACLE_HOME
ORACLE_HOME=/opt/oracle/product/12.1.0.2/ee_3

Creating a file system for Oracle database

Environment: Oracle Linux 7.5

Steps:

1. Create Virtual Disk on VM.

List all disks.

lsblk

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

2. Create a partition table and a GPT partition: mkpart <PART-LABEL> <START> <END>

parted /dev/xvdc

(parted) mktable gpt

(parted) mkpart OMR 0% 100%

(parted) quit

3. Create EXT4 file system

mkfs.ext4 -T largefile4 /dev/xvdc1

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

4. Mount and set permissions

mkdir -p /data001/oradata/OEMDB

vi /etc/fstab

add the following:

UUID=xxxx /data001/oradata/OEMDB ext4 noatime,nodiratime,nobarrier 1 2

mount /data001/oradata/OEMDB

df -h

chown oracle:oinstall /data001/oradata/OEMDB

ls -ld /data001/oradata/OEMDB

5. Reducing reserved blocks

By default 5% of the space to be usable only by root. E.g. You can reduce it to 0.5% by doing:

tune2fs -m 0.5 /dev/xvdc1

tune2fs -l /dev/xvdc1

Documentation

Improve I/O Performance On ext3/ext4 File Systems With The "noatime" Mount Option (Doc ID 1561740.1)

Available Mount Options to Improve ext4 Filesystem Performance (Doc ID 1476869.1)

Supported and Recommended File Systems on Linux (Doc ID 236826.1)

Oracle Database - Filesystem & I/O Type Supportability on Oracle Linux 6 (Doc ID 1601759.1)

Create XFS file system

In similar way, create FRA 30GiB, XFS file system, /data001/oradata/fast_recovery_area

mkfs.xfs /dev/xvde1

mkdir -p /data001/oradata/fast_recovery_area

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

vi /etc/fstab

UUID=xxxx /data001/oradata/fast_recovery_area  xfs  defaults 0 0

mount /data001/oradata/fast_recovery_area

chown oracle:oinstall /data001/oradata/fast_recovery_area

ls -ld /data001/oradata/fast_recovery_area

Memory swapping on Linux

Environment
Oracle Linux 7 with Oracle databases 12.1

Monitoring
free -h
top
vmstat [options] [delay [count]]

monitoring a particular process
ps -o "ppid pid vsz rss pmem comm args" --pid
top -p  
pmap -x

adding SWAP to top and changing SORT column

a. Run the TOP command:

# top

b. On your keyboard press the " f " key and scroll down using the arrow key until you have selected "SWAP" then press to select it. This should add a " * " symbol in front of it.

c. While still selecting "SWAP"  press the arrow key, which highlights the entire SWAP line, and using the arrow key move it up to one of the first options (anywhere above "COMMAND").

d. While still having "SWAP" selected, type the " s " key which will configure top to SORT by the currently selected option, in this case SWAP.

e. Finally " q " to save the configuration changes and view the results.

f. Perform your review as needed and press "q" again to exit top command.

g. If you want to save this top configuration press W (upper case)

Kernel parameters and tuned service

check the current parameters

sysctl -a|grep 'dirty\|swap'

check status of tuned.service, it overwrites parameters in /etc/sysctl.conf !

systemctl status tuned.service

Check the active tune profile. 

tuned-adm active

Current active profile: virtual-guest

see all profiles

cd /usr/lib/tuned

ls -lh

For example, modify the current profile

cd /usr/lib/tuned/virtual-guest

vi tuned.conf

comment existing vm.* parameters and add the following

# recommended by RedHat. Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7.

vm.swappiness = 1

vm.dirty_background_ratio = 3

vm.dirty_ratio = 80

vm.dirty_expire_centisecs = 500

vm.dirty_writeback_centisecs = 100

re-activate the changed profile

tuned-adm profile virtual-guest

show the current kernel parameters

sysctl -a|grep 'dirty\|swap'

MOSC Documentation

Linux OS, Swapping and Databases (Doc ID 1295478.1)

How to Check Swap Usage Live via the 'top' Command on Oracle Linux 5, 6, and 7. (Doc ID 2422888.1)

Why is SWAP being used instead of available physical memory? (Doc ID 2404462.1)

Oracle Linux 7 - sysctl parameter doesn't take effect after reboot (Doc ID 2195319.1)

Oracle Linux: Modifying kernel parameters using sysctl (Doc ID 390279.1)

What are Dirty Pages? (Doc ID 2304722.1)

RedHat Documentation

Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7

Red Hat Enterprise Linux 7 Performance Tuning Guide Chapter 3. Tuned

Red Hat Enterprise Linux 7 Performance Tuning Guide 3.2. Performance Tuning with tuned and tuned-adm

Configuring Disk Devices Manually for Oracle ASM (without ASMLib)

Environment: Oracle Linux Server release 7.3 on Oracle VM 3.4.3

In Oracle VM Manager, edit VM and add a new Virtual Disk, e.g.

 

In the VM as root, see the new disk:

 

lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   30G  0 disk

├─xvda1 202:1    0  512M  0 part /boot

├─xvda2 202:2    0    8G  0 part /

├─xvda3 202:3    0    4G  0 part /tmp

├─xvda4 202:4    0    1K  0 part

├─xvda5 202:5    0  1.5G  0 part /home

└─xvda6 202:6    0   16G  0 part [SWAP]

xvdb    202:16   0   50G  0 disk

└─xvdb1 202:17   0   50G  0 part /opt/oracle

xvdc    202:32   0   10G  0 disk

└─xvdc1 202:33   0   10G  0 part /opt/netbackup

xvdd    202:48   0  800G  0 disk

└─xvdd1 202:49   0  800G  0 part /data001/oradata/CDRDEV

xvde    202:64   0  200G  0 disk

└─xvde1 202:65   0  200G  0 part /data001/fast_recovery_area

xvdf    202:80   0  800G  0 disk

└─xvdf1 202:81   0  800G  0 part /data001/oradata/CDRSIT

xvdg    202:96   0  100G  0 disk

└─xvdg1 202:97   0  100G  0 part /data001/archivelogs

xvdh    202:112  0  800G  0 disk

└─xvdh1 202:113  0  800G  0 part /data001/oradata/CDRDEV1

xvdi    202:128  0  800G  0 disk

└─xvdi1 202:129  0  800G  0 part /data001/oradata/CDRSIT1

xvdj    202:144  0  800G  0 disk

└─xvdj1 202:145  0  800G  0 part

 

For persistent device naming, we can configure ASMLIB or set udev rules. We need to use a partition UUID (not a filesystem UUID) in udev rules. For this, use parted to create a GPT partition.

parted /dev/xvdj

mktable gpt

 

to create a GPT partition:

mkpart PART-LABEL START END

e.g.

mkpart asmpart1 0% 100%

(parted) print

Model: Xen Virtual Block Device (xvd)

Disk /dev/xvdj: 859GB

Sector size (logical/physical): 512B/512B

Partition Table: gpt

Disk Flags:

 

Number  Start   End    Size   File system  Name      Flags

 1      1049kB  859GB  859GB               asmpart1

 

udevadm info --query=property /dev/xvdj1

DEVLINKS=/dev/disk/by-partlabel/asmpart1 /dev/disk/by-partuuid/48940eb5-f6fa-4558-aeba-b12b81199e27 /dev/oracleasm/disk1

DEVNAME=/dev/xvdj1

DEVPATH=/devices/vbd-51856/block/xvdj/xvdj1

DEVTYPE=partition

ID_FS_TYPE=oracleasm

ID_FS_USAGE=filesystem

ID_PART_ENTRY_DISK=202:144

ID_PART_ENTRY_NAME=asmpart1

ID_PART_ENTRY_NUMBER=1

ID_PART_ENTRY_OFFSET=2048

ID_PART_ENTRY_SCHEME=gpt

ID_PART_ENTRY_SIZE=1677717504

ID_PART_ENTRY_TYPE=ebd0a0a2-b9e5-4433-87c0-68b6b72699c7

ID_PART_ENTRY_UUID=48940eb5-f6fa-4558-aeba-b12b81199e27

ID_PART_TABLE_TYPE=gpt

MAJOR=202

MINOR=145

SUBSYSTEM=block

TAGS=:systemd:

USEC_INITIALIZED=800372237892

 

 

vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

 

To implement the new rules:

udevadm trigger

 

ls -la /dev/oracleasm/disk1

lrwxrwxrwx. 1 root root 8 Feb 15 12:32 /dev/oracleasm/disk1 -> ../xvdj1

 

ls -la /dev/xvd*

brw-rw----. 1 root   disk 202,   0 Feb 15 12:32 /dev/xvda

brw-rw----. 1 root   disk 202,   1 Feb 15 12:32 /dev/xvda1

brw-rw----. 1 root   disk 202,   2 Feb 15 12:32 /dev/xvda2

brw-rw----. 1 root   disk 202,   3 Feb 15 12:32 /dev/xvda3

brw-rw----. 1 root   disk 202,   4 Feb 15 12:32 /dev/xvda4

brw-rw----. 1 root   disk 202,   5 Feb 15 12:32 /dev/xvda5

brw-rw----. 1 root   disk 202,   6 Feb 15 12:32 /dev/xvda6

brw-rw----. 1 root   disk 202,  16 Feb 15 12:32 /dev/xvdb

brw-rw----. 1 root   disk 202,  17 Feb 15 12:32 /dev/xvdb1

brw-rw----. 1 root   disk 202,  32 Feb 15 12:32 /dev/xvdc

brw-rw----. 1 root   disk 202,  33 Feb 15 12:32 /dev/xvdc1

brw-rw----. 1 root   disk 202,  48 Feb 15 12:32 /dev/xvdd

brw-rw----. 1 root   disk 202,  49 Feb 15 12:32 /dev/xvdd1

brw-rw----. 1 root   disk 202,  64 Feb 15 12:32 /dev/xvde

brw-rw----. 1 root   disk 202,  65 Feb 15 12:32 /dev/xvde1

brw-rw----. 1 root   disk 202,  80 Feb 15 12:32 /dev/xvdf

brw-rw----. 1 root   disk 202,  81 Feb 15 12:32 /dev/xvdf1

brw-rw----. 1 root   disk 202,  96 Feb 15 12:32 /dev/xvdg

brw-rw----. 1 root   disk 202,  97 Feb 15 12:32 /dev/xvdg1

brw-rw----. 1 root   disk 202, 112 Feb 15 12:32 /dev/xvdh

brw-rw----. 1 root   disk 202, 113 Feb 15 12:32 /dev/xvdh1

brw-rw----. 1 root   disk 202, 128 Feb 15 12:32 /dev/xvdi

brw-rw----. 1 root   disk 202, 129 Feb 15 12:32 /dev/xvdi1

brw-rw----. 1 root   disk 202, 144 Feb 15 12:32 /dev/xvdj

brw-rw----. 1 oracle dba  202, 145 Feb 15 12:32 /dev/xvdj1

 

to see all disks and partitions:

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

 

Alternatively, you can use a partition label name ID_PART_ENTRY_NAME:

cat /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_NAME}=="asmpart2", SYMLINK+="oracleasm/disk2", OWNER="oracle", GROUP="dba", MODE="0660"

 

lsblk -o name,partlabel,partuuid

NAME    PARTLABEL PARTUUID

xvda

├─xvda1

├─xvda2

├─xvda3

├─xvda4

├─xvda5

└─xvda6

xvdb

└─xvdb1

xvdc

└─xvdc1

xvdd

└─xvdd1

xvde

└─xvde1

xvdf

└─xvdf1

xvdg

└─xvdg1

xvdh

└─xvdh1

xvdi

└─xvdi1

xvdj

└─xvdj1 asmpart1  48940eb5-f6fa-4558-aeba-b12b81199e27

xvdk

└─xvdk1 asmpart2  0b8ba20c-a10d-4564-b582-0692fd9657e8

 

 

 

 

 

Huge Pages on Oracle Linux

Environment

Oracle Linux Server release 7.3
Oracle database 11.2, 12.1

Quick checking

# cat /proc/sys/vm/nr_hugepages

2054

or

# cat /proc/meminfo | grep Huge
AnonHugePages:         0 kB
HugePages_Total:    2054
HugePages_Free:      546
HugePages_Rsvd:      542
HugePages_Surp:        0
Hugepagesize:       2048 kB




checking if transparent huge pages are disabled

# cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

Viewing database 12.1 alert log

**********************************************************************

Mon Oct 16 12:28:54 2017

Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

Mon Oct 16 12:28:54 2017

 Per process system memlock (soft) limit = 128G

Mon Oct 16 12:28:54 2017

 Expected per process system memlock (soft) limit to lock

 SHARED GLOBAL AREA (SGA) into memory: 2050M

Mon Oct 16 12:28:54 2017

 Available system pagesizes:

  4K, 2048K

Mon Oct 16 12:28:54 2017

 Supported system pagesize(s):

Mon Oct 16 12:28:54 2017

  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)

        4K       Configured               3               3        NONE

     2048K             2054            1025            1025        NONE

**********************************************************************




Viewing database 11.2 alert log

************************ Large Pages Information *******************

Per process system memlock (soft) limit = 128 GB




Total Shared Global Region in Large Pages = 2050 MB (100%)




Large Pages used by this instance: 1025 (2050 MB)

Large Pages unused system wide = 4 (8192 KB)

Large Pages configured system wide = 2054 (4108 MB)

Large Page size = 2048 KB

********************************************************************

If number of Huge pages is less than required, than SGA will use both page types. Database 12.1 aler.log:

  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)

        4K       Configured               3          187704        NONE

     2048K              659            1025             658        NONE




Simplified setup

1. Have the memlock user limit set in /etc/security/limits.conf file. Set the value (in KB) slightly smaller than total RAM (90%) , at least it must be bigger than HugePages size.
*   soft   memlock    14680064
*   hard   memlock    14680064
Atentu! If you use systemd to auto startup databases, systemd ignores limits.conf. You need to add the following to a service unit file
[Service]
LimitMEMLOCK=infinity
LimitNOFILE=65535



2. Get the required number of pages from alert.log (EXPECTED_PAGES) or by running Oracle script hugepages_settings.sh (Doc ID 401749.1)




3. edit vm.nr_hugepages in /etc/sysctl.conf as root

4. to reload the parameters, reboot Linux or use sysctl -p




Proper setup and more info

HugePages on Oracle Linux 64-bit (Doc ID 361468.1)

HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

Oracle 12.2 Documentation: Administering Oracle Database on Linux










Automating Oracle database Shutdown and Startup by systemd

Environment

Oracle Linux Server release 7.3 (or Redhat 7.3)
Oracle database 12.1.0.2

Documentation

MOSC Oracle Doc ID 2229679.1, Doc ID 2049901.1
Overview of systemd for RHEL 7

Automating by systemd

alternatives are Oracle Restart or SysV init ( /etc/init.d in older Linux).
Service Unit file typically has extension .service and stored in
/usr/lib/systemd/system
/etc/systemd/system
/usr/lib/systemd/user
/etc/systemd/user
For more details read Chapter 9. Managing Services with systemd

Create or edit a service unit file:
cd /etc/systemd/system
vi oracle_database.service
[Unit]
Description=The Oracle Database Service
After=network.target

[Service]
Type=forking
RemainAfterExit=yes
KillMode=none
TimeoutStopSec=10min
# memlock limit is needed for SGA to use HugePages
LimitMEMLOCK=infinity
LimitNOFILE=65535

User=oracle
Group=oinstall
# Please use absolute path here
# ExecStart=$ORACLE_HOME/bin/dbstart $ORACLE_HOME &
# First argument of dbstart is used to bring up Listener
ExecStart=/opt/oracle/product/12.1.0/se2_1/bin/dbstart /opt/oracle/product/12.1.0/se2_1 &
ExecStop=/opt/oracle/product/12.1.0/se2_1/bin/dbshut /opt/oracle/product/12.1.0/se2_1
Restart=no

[Install]
# Puts wants directive for the other units in the relationship

WantedBy=default.target

First argument of dbstart/dbshut is used to bring up/shutdown Oracle Listener. This script will start all databases listed in the /etc/oratab file whose third field is a "Y". If you use ASM or cluster services, read more in dbstart description.

Huge Pages
systemd ignores /etc/security/limits.conf. If HugePages are configured, you need to use LimitMEMLOCK and LimitNOFILE, otherwise SGA will use small pages and database alert log will show:

 Increase per process memlock (soft) limit to at least 2050MB to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory

TimeoutStopSec=   Configures the time to wait for stop. If a service is asked to stop, but does not terminate in the specified time, it will be terminated forcibly via SIGTERM, and after another timeout of equal duration with SIGKILL (see KillMode= in systemd.kill(5)). Takes a unit-less value in seconds, or a time span value such as "5min 20s". Pass "infinity" to disable the timeout logic. Defaults to DefaultTimeoutStopSec= from the manager configuration file (see systemd-system.conf(5)).
For more info use
man systemd.service

reload systemd and enable the service:
systemctl daemon-reload
systemctl enable oracle_database.service
list services:
systemctl list-unit-files --type service|grep oracle
Start the service and check its status
systemctl start oracle_database.service
systemctl status oracle_database.service

Cron jobs are no longer running

Environment: Red Hat Enterprise Linux Server release 6.6 (Santiago)

# crontab -e

Authentication token is no longer valid; new one required
You (root) are not allowed to access to (crontab) because of pam configuration.

there were no changes in /etc/security/access.conf

the problem is root`s expired password

# chage -l root

Last password change                                    : Mar 20, 2015

Password expires                                        : Apr 29, 2015

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : 40

Number of days of warning before password expires       : 7

# passwd root

and check 

# crontab -l

NFS mount options for Oracle database

OEM 12c job failed to create standby database for Oracle 10.2 on Oracle Linux.
NFS mounted staging area is used.
Production database alert log shows
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/mnt/software/oracle_CCASEPRD_linux/CCASEPRD_43/CONTROL_FILE_BACKUP_STANDBY_NO_RECOVERY'

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 2944'

which is also confirmed by SQLPLUS running the same command.

The problem is solved by using nolock option for NFS mount.

The following NFS options are successful:

rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nolock,vers=3,timeo=600

Useful info on Oracle Support:
Mount Options for Oracle files when used with NFS on NAS devices (Doc ID 359515.1)
Howto Optimize NFS Performance with NFS options. (Doc ID 397194.1)
Poor Performance of APPS 11i or 12 When Using Shared APPL_TOP with NAS (Doc ID 802704.1)

Citrix Linux Client 64bit (AMD64) for Ubuntu

Solution is here:
http://www.madox.net/blog/2009/05/04/citrix-linux-client-64bit-amd64-for-ubuntu-jaunty-904/

I've done it on Ubuntu 10.04 Lucid Lynx with
Citrix Linux Client version 11.1
linuxx86-11.100.158406.tar
libmotif3_2.2.3-4_i386.deb

Solaris cheat sheet for DBA

There are some handy Unix commands for DBA. It was done on Solaris 10.

System Configuration

prtconf

Find physical memory

prtconf | grep Memory

Information about CPU
Physical processors

psrinfo -p

psrinfo -pv

Virtual processors

psrinfo

psrinfo -v

Information about swap
List all swap areas

swap -l

Show swap usage

swap -s

Swapping activity (3 seconds 5 samples)

vmstat -S 3 5

Performance Monitoring

top

prstat

top processes with totals per user

prstat -a

sorted by memory and totals per user (shared memory shown).

prstat -a -s rss

Archiving
zip (-r) recursively and (-y) with symbolic links

zip -vry file.zip target_dir

tar and compress (tar stores symbolic links)

tar -cvf - target_dir | gzip -c > destination_dir/file.tar.gz

Use RELATIVE path for target directory, unless you want to restore files to the same location.
Unpacking tar.gz

cd destination_dir

gunzip < file.tar.gz | tar xvf -

Checksum and digest
digest -va md5 file_name
digest -va sha1 file_name
cksum file_name

32 or 64?
Is my Solaris 64-bit or 32-bit?
/usr/bin/isainfo -kv
Is my Oracle 64-bit or 32-bit?
cd $ORACLE_HOME/bin
file oracle

Oracle files
cat /var/opt/oracle/oratab
cat /var/opt/oracle/oraInst.loc

Solaris files
/etc/vfstab File system mount table
/var/adm/loginlog Log of login attempts
/var/adm/messages Syslog location

Video problem in Skype on Linux

Environment:
Ubuntu 9.10 Karmic Koala 64-bit, Skype 2.1.0.47 (deb package downloaded from skype.com)
Problem:
test video is just a green screen.
Solution:
You need to use correct v4l1compat.so library.
az@az1:~$ locate v4l1compat.so
/usr/lib/libv4l/v4l1compat.so
/usr/lib32/libv4l/v4l1compat.so

Second one works ok for 64-bit linux. Try from shell:
LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype &

To make it permanent go to (using Gnome)
System > Preferences > Main Menu
and change properties of Skype shortcut to

env LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype