All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....: April 2017

We can check which ports are busy with the following command:

[root@master ~]# sudo lsof -i -P -n | grep LISTEN

sshd 1481 root 3u IPv4 19834 0t0 TCP *:22 (LISTEN)

sshd 1481 root 4u IPv6 19843 0t0 TCP *:22 (LISTEN)

cupsd 1482 root 12u IPv6 20163 0t0 TCP [::1]:631 (LISTEN)

cupsd 1482 root 13u IPv4 20164 0t0 TCP 127.0.0.1:631 (LISTEN)

master 1632 root 13u IPv4 21059 0t0 TCP 127.0.0.1:25 (LISTEN)

master 1632 root 14u IPv6 21060 0t0 TCP [::1]:25 (LISTEN)

dnsmasq 1672 nobody 6u IPv4 21403 0t0 TCP 192.168.122.1:53 (LISTEN)

postgres 3051 postgres 3u IPv6 33191 0t0 TCP [::1]:5432 (LISTEN)

postgres 3051 postgres 4u IPv4 33192 0t0 TCP 127.0.0.1:5432 (LISTEN)

postgres 3051 postgres 5u IPv4 33193 0t0 TCP 192.168.1.108:5432 (LISTEN)

Free command in Linux

We have 2 output format for free command.

1. Linux (RedHat/Oracle Linux/Centos) versions before 7.0

[oracle@radb1 ~]$ free -m

total used free shared buffers cached

Mem: 12010 11739 270 0 42 10666

-/+ buffers/cache: 1031 10979

Swap: 12287 939 11348

Some notes about interpreting the output (in MBs):

-> The first two lines of numbers are concerned about RAM. The final line of numbers is about your swap space.

-> The first three columns seem straightforward: the total capacity, how much of the total is used by processes
and how much of the total is free.

-> The next three columns are a bit more complicated. These are the memory shared among processes, memory that is being
used as buffers (temporary storage) by the kernel and as cached for pages.

-> The used and free entries in the first line show you how much RAM is being used and is free. You should not get worried if you see
the free number being low. Memory lying unused is useless, so kernel tries to use it as buffers and for caching. How much of
the used memory has been put up to use as buffers and cache is also shown in the first line.

-> Concerned about how much memory is truly being used by processes you are running? That is why the confusing second line exists!

used-in-second-line = used-in-first-line - buffers - cached and free-in-second-line = free-in-first-line + buffers + cached.

1031MB = 11739MB - 42MB - 10666MB 10979MB = 270MB + 42MB + 10666MB

In other words, Actual Total Used Memory = 1031MB Actual Total Available Memory = 10979MB

So, why free Linux system is displaying 11739 as used memory, because Linux counts cached memory, buffered memory to
this used memory.

In future any application want to use these buffer/cache then Linux will make it free.

Take a moment on these values. These calculations make sense since if your processes ask for more memory, the kernel
will happily free its buffers and cached resources and hand it over!

-> Finally, the shared is not factored into the second line computation because it is memory that is already shared among the processes.
It is already a part of the used memory. Now if you start asking how much memory is being used by a single process
then the computation factoring in shared memory gets harder.

-> Please ignore the -/+ buffers/cache text. It is completely confusing to the average user. To make any sense it should have
been -/+ buffers+cached.

-> Explanation detailed:

12010 : Display physical/memory available for your system. Output is in MBs.

11739 : Display memory used by system. Include buffers and cached data size as well.

270 : Display total free memory and available for new process to execute.

0 : Display shared memory.

42 : Display total memory buffered by different application on the system.

10666 : Display total memory used for caching of data for future uses.

What Is The Difference Between Buffers And Cache?

Buffers is used to store data for a particular application for temporarily and this data is not used by any other application.

Similar work like bandwidth if try to send burst of data through network, and network card is unable to send this data and
capable to send less data it will keep these hugs amounts of data in buffer so that it can send data constantly in lesser speeds.

Similarly cache is a memory location to store frequently used data for faster access. Another difference between a buffer
and cache is that cache can be used multiple times and buffer is used single time, both are used to temporary data storage
for processing.

reference: https://codeyarns.com/2016/06/15/free-command-in-linux/

2. Linux (RedHat/Oracle Linux/Centos) versions after 7.0

[oracle@rac1 ~]$ free -g

total used free shared buff/cache available

Mem: 2015 300 179 1353 1535 359

Swap: 127 0 127

In new format following formulas can be extracted from the output to calculate the total memory and usage.

total = free + used + buff/cache 2015 = 179 + 300 + 1535

total = shared + available + used 2015 = 1353 + 359 + 300

free = shared + avaialble - buff/cache 179 = 1353 + 359 - 1535

used = total - shared - available 300 = 2015 - 1353 - 359

available = total - shared - used 359 = 2015 - 1353 - 300

shared = total - avaialble - used 1353 = 2015 - 359 - 300

Buff/cache means cached pages from disk into memory for higher system speed. It is cache of contents from disk loaded into RAM.

When program requests reading of file already in cache, its contents is served from RAM. It does not have to wait each time until
it is read from disk. Loading times for SSD are close to acessing RAM, so this will not make huge difference.

Difference between free and available is that free is not used for anything useful. Available includes cache parts, that can be freed
if more memory is required. Buff will fill with IO operations of running system. It will load contents into RAM for higher speed.
Because your server is more busy and longer running than your desktop, it already has full cache. That is ok, free memory is
wasted memory.

--> Good Explanation for Memory Usage in Linux <--

What's going on?

Linux is borrowing unused memory for disk caching. This makes it looks like you are low on memory, but you are not! Everything is fine!

Why is it doing this?

Disk caching makes the system much faster and more responsive! There are no downsides, except for confusing newbies. It does not
take memory away from applications in any way, ever!

What if I want to run more applications?

If your applications want more memory, they just take back a chunk that the disk cache borrowed. Disk cache can always be given
back to applications immediately! You are not low on ram!

Do I need more swap?

No, disk caching only borrows the ram that applications don't currently want. It will not use swap. If applications want more memory,
they just take it back from the disk cache. They will not start swapping.

How do I stop Linux from doing this?

You can't disable disk caching. The only reason anyone ever wants to disable disk caching is because they think it takes memory
away from their applications, which it doesn't! Disk cache makes applications load faster and run smoother, but it NEVER EVER
takes memory away from them! Therefore, there's absolutely no reason to disable it!

Why does top and free say all my ram is used if it isn't?

This is just a difference in terminology. Both you and Linux agree that memory taken by applications is "used", while memory
that isn't used for anything is "free".

But how do you count memory that is currently used for something, but can still be made available to applications?

You might count that memory as "free" and/or "available". Linux instead counts it as "used", but also "available":

Memory that is You'd call it Linux calls it

used by applications Used Used

used, but can be made available Free(or Available) Used(and Available)

not used for anything Free Free

This "something" is (roughly) what top and free calls "buffers" and "cached". Since your and Linux's terminology differs, you might think
you are low on ram when you're not.

How do I see how much free ram I really have?

To see how much ram your applications could use without swapping, run free -m and look at the "available" column:

$ free -m

total used free shared buff/cache available

Mem: 1504 1491 13 0 855 792

Swap: 2047 6 2041

(On installations from before 2016, look at "free" column in the "-/+ buffers/cache" row instead.)

This is your answer in megabytes. If you just naively look at "used" and "free", you'll think your ram is 99% full when it's really just 47%!

For a more detailed and technical description of what Linux counts as "available", see the commit that added the field.

When should I start to worry?

A healthy Linux system with more than enough memory will, after running for a while, show the following expected and harmless behavior:

- free memory is close to 0

- used memory is close to total

- available memory (or "free + buffers/cache") has enough room (let's say, 20%+ of total)

- swap used does not change

Warning signs of a genuine low memory situation that you may want to look into:

- available memory (or "free + buffers/cache") is close to zero

- swap used increases or fluctuates

- dmesg | grep oom-killer shows the OutOfMemory-killer at work

reference: https://www.linuxatemyram.com/

List each file size in current directory

[oracle@cdb1 ~]$ du -sh *

4.0K afiedt.buf

4.0K check.sql
4.0K Desktop
4.0K Documents
4.0K Downloads
7.2G install

List files with time in reverse order

[root@rhel77 Desktop]# ls -ltr -h

total 114M

-rwxrwx---. 1 root root 114M Mar 28 23:47 postgresql-9.5.6.tar

drwxrwxrwx. 6 1107 1107 4.0K Mar 29 23:24 postgresql-9.5.6

-l: List in long format. If the output is to a terminal, a total sum for all the file sizes is output on a line before the long listing.
-r: Reverse the order of the sort to get reverse lexicographical order
-t: Sort by time modified (most recently modified first) before sorting the operands by lexicographical order.

How to reload udev rules without reboot?

[root@rac2 ~]# udevadm control --reload-rules && udevadm trigger
https://unix.stackexchange.com/questions/39370/how-to-reload-udev-rules-without-reboot

Usage of ps command

Type the following ps command to display all running processes:

# ps aux | less

a: select all processes

u: select all processes on a terminal, including those of other users

x: select processes without controlling ttys

See processes run by user dfs

-bash-3.00$ ps -fu dfs

UID PID PPID C STIME TTY TIME CMD

dfs 17262 1 0 Oct 23 ? 2:47 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1940 -classpath dfs_1_6.jar co

dfs 17260 1 0 Oct 23 ? 3:25 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1920 -classpath dfs_1_6.jar co

dfs 17264 1 0 Oct 23 ? 2:48 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1922 -classpath dfs_1_6.jar co

dfs 17266 1 0 Oct 23 ? 2:53 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1930 -classpath dfs_1_6.jar co

Installing package without dependency as

[root@stageoem1]# rpm -ivh --nodeps --force oracleasmlib-2.0.4-1.el6.x86_64.rpm

   warning: oracleasmlib-2.0.4-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID
ec551f03: NOKEY
Preparing...                ########################################### [100%]
1:oracleasmlib           ########################################### [100%]

To determine the available RAM and swap space, enter the following command:

[oracle11g@localhost ~]$ grep MemTotal /proc/meminfo

MemTotal: 5850308 kB

[oracle11g@localhost ~]$ grep SwapTotal /proc/meminfo
SwapTotal: 6176764 kB

To determine the amount of free disk space on the system, enter the following command:

[oracle11g@localhost ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ol-root 50G 4.5G 46G 9% /

devtmpfs 2.8G 0 2.8G 0% /dev

tmpfs 2.8G 84K 2.8G 1% /dev/shm

tmpfs 2.8G 8.8M 2.8G 1% /run

tmpfs 2.8G 0 2.8G 0% /sys/fs/cgroup

/dev/mapper/ol-home 64G 4.6G 60G 8% /home

/dev/sda1 497M 206M 292M 42% /boot

none 532G 49G 484G 10% /media/sf_Shared_Folder

To determine the amount of shared memory available, enter the following command:

[oracle11g@localhost ~]$ df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.8G 84K 2.8G 1% /dev/shm

To display system information type uname command

[oracle@rac2 ~]$ uname -r

4.1.12-94.3.9.el7uek.x86_64

-a: print all information, in the following order except omit -p and -i if unknown

-s: print the kernel name

-n: print the network node hostname

-r: print the kernel release

-v: print the kernel version

-m:print the machine hardware name

-p: print the processor type or "unknown"

-i: print the hardware platform or "unkown"

-o: print the operating system

--help: display the help and exit

--version: output version information and exit

To determine the distribution and version of Linux installed, enter the following command

[oracle@rac2 ~]$ cat /proc/version
Linux version 4.1.12-94.3.9.el7uek.x86_64 (mockbuild@) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) ) #2 SMP Fri Jul 14 20:09:40 PDT 2017

Display the umask in octal and in symbolic form

[oracle@rac2 ~]$ umask

0022

[oracle@rac2 ~]$ umask -S

u=rwx,g=rx,o=rx

To determine if the required packages are installed, enter commands similar to the following:

[oracle11g@localhost ~]$ rpm -q glibc-2.12-1.7.el6

package glibc-2.12-1.7.el6 is not installed

To determine if a listener process is running and to identify its name and the Oracle home directory in which it is installed:

[oracle@Oracle11g ~]$ ps -ef | grep tnslsnr

oracle 2467 1 0 13:16 ? 00:00:03 /home/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle 2939 1 0 13:17 ? 00:00:02 /home/oracle/app/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit

oracle 25392 5891 0 19:04 pts/0 00:00:00 grep tnslsnr

To determine if the oraInst.loc file exists, enter the following command:

[oracle11g@localhost ~]$ more /etc/oraInst.loc

inventory_loc=home/oracle/oraInventory inst_group=oinstall

All groups are in /etc/group

[root@localhost ~]# cat /etc/group

oinstall:x:1001:

dba:x:1002:oracle11g

oper:x:502:oracle11g

grid:x:1003:grid

asmadmin:x:1005:grid

asmdba:x:1006:oracle11g,grid

asmoper:x:1007:grid

To determine if an Oracle software owner user named oracle, or grid exists, enter a command similar to the following:

[oracle11g@localhost ~]$ id oracle

uid=501(oracle) gid=501(oinstall) groups=502(dba),503(oper)
To create an oracle user, enter a command similar to the following:

# /usr/sbin/useradd -u 502 -g oinstall -G dba,asmdba oracle

- The -u option specifies the user ID. Using this command flag is optional because the system can provide you with an automatically generated user ID number. You must note the oracle user ID number because you need it during preinstallation.

- The -g option specifies the primary group, which must be the Oracle Inventory group, for example oinstall.

- The -G option specifies the secondary groups, which must include the OSDBA group, and, if required the OSOPER and ASMDBA groups.

Set the password of the oracle user:

# passwd oracle

Add groups

[root@localhost oracle11g]# usermod -g oinstall -G grid, vboxsf, asmdba, asmadmin, asmoper grid

[root@localhost oracle11g]# groups grid

grid : oinstall vboxsf asmadmin asmdba asmoper grid

[root@localhost oracle11g]# groups oracle

oracle : oinstall wheel vboxsf dba oper asmdba oracle

Check 'oracle' related environment parameters
[oracle@Oracle11g ~]$ env | grep ORACLE

ORACLE_UNQNAME=testdb

ORACLE_SID=testdb2

ORACLE_BASE=/home/oracle/app

ORACLE_HOME=/home/oracle/app/product/11.2.0/dbhome_1

The difference between "-" and "no hyphen" is that the latter keeps your existing environment (variables, etc); the former creates a new environment (with the settings of the actual user, not your own).

Ex: [oracle11g@oracle11g~]$ su - grid versus [oracle11g@oracle11g~]$ su grid

Output for former is

[oracle11g@orcl11g ~]$ su - grid

[grid@orcl11g~]$ env | grep ORACLE

ORACLE_UNQNAME=+ASM

ORACLE_SID=+ASM

ORACLE_BASE=/u01/app/grid

ORACLE_HOME=/u01/app/grid /product/11.2.0/grid

Output for latter is

[oracle11g@oracle11g ~]$ su grid

[grid@orcl11g~]$ env | grep ORACLE

ORACLE_UNQNAME=ora11g

ORACLE_SID=ora11g

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle /product/11.2.0/dbhome_1

If you encounter the ORA-00845 error, is to increase the /dev/shm mountpoint size.

For example:

# mount -t tmpfs shmfs -o size=7g /dev/shm

To make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=7g 0

To mount NFS for Oracle we have to choose correct options. Because of bug if not correct options selected then following error will be occured:

channel ORA_DISK_1: reading from backup piece /backup/cdb_prod_bakup/ivs1evnc_1_1
RMAN-00571: =======================================
RMAN-00569: =====ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =======================================================
RMAN-03002: failure of recover command at 04/13/2017 10:22:09
ORA-19870: error while restoring backup piece /backup/cdb_prod_bakup/ivs1evnc_1_1
ORA-19625: error identifying file /backup/cdb_prod_bakup/data_D-CDBPROD_I-3983442973_TS-ACCOUNTING_FNO-6_07rhhtg0
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 12

Mount as
[root@cdb1 scripts]# mount -t nfs 10.254.212.1:/backup /backup -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600,actimeo=0

And add following line two /etc/fstab file as

10.254.212.1:/backup /backup nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600,actimeo=0 0 0

To mount or unmount

umount -t nfs 10.254.212.1:/backup /backup

mount -t nfs 10.254.212.1:/backup /backup

references:

http://www.dba-oracle.com/t_ora_27054.htm

http://dbaforums.org/oracle/index.php?showtopic=21342

http://oletange.blogspot.com/2012/04/umount-device-is-busy-why.html

Set backspace as erase key to kill characters in SQL*Plus and RMAN environment

In Linux shell type following

$stty erase <ctrl V backspace key>

In .bash_profile save as following for permanent erase
stty erase ^H #Erase and kill characters

To find and delete files older than specific date

Example: rm -rf $(find /u01/app/oracle/dump -type d -mtime +4) # Find all dump directories older than +4 day and delete it from local

Example: cd <path to the directory where logs are>

rm -rf `ls -t | awk "NR>10"` # Delete all files older than 10 days

Using the Locate Command

The locate command is faster than the find command because it uses a previously built database, whereas the find command searches
in the real system, through all the actual directories and files. The locate command returns a list of all path names containing
the specified group of characters.

All other Linux command links:

http://www.thegeekstuff.com/2010/12/5-ways-to-execute-linux-command

http://www.comptechdoc.org/os/linux/usersguide/linux_ugbasics.html

http://www.computerhope.com

https://ask.fedoraproject.org/en/question/8847/just-installed-postgresql-what-path-is-it-stored/

https://www.cyberciti.biz/faq/unix-linux-check-if-port-is-in-use-command/

https://www.cyberciti.biz/faq/show-all-running-processes-in-linux/