How to show all privileges from a user in oracle?

You can try these below views.

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;

select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;

select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

Java Conceptual Diagram

How to kill a process on a port on ubuntu

Sometimes, when we strying to up an service and get message like: the port *** is used by another service. And we are notable to start our app to use that port. In that case, we are in need to free the port by stopping the running process. For this purpose a first we need to find out the process which is using that port.

By using the below query we can get the process id using this port:

sudo lsof -t -i:8080 [For this case 8080 is our reference port number]

to get the process in details, we can use:
ps -ef | grep


And then we can kill the process by :
kill -9

We can use the below command at once to kill the process:
sudo kill $(sudo lsof -t -i:8080)

Sheel Script for Deleting files older than some days

find is the common tool for this kind of task :

find ./target_dir ! -name 'stayhere.txt' -mtime +5 -type f -delete


EXPLANATIONS

./target_dir your directory (replace with your own)
! -name File to exclude
-mtime +5 older than 5 days
-type f only files
-delete no surprise. Remove it to test your find filter before executing the whole command
And take care that ./target_dir exists to avoid bad surprises !

TODO in Ecplise IDE

in Eclipse, no TODO shortcut found; but a possible solution is to create a template so when you write some string and hit Ctrl + spacebar and Enter your TODO code will be entered.

Window -> Preferences -> Java -> Editor -> Templates -> New

• Set the Name of the template (e.g. todo)
• Set the Pattern (e.g. //TODO)

When you type todo and press Ctrl + spacebar and Enter // TODO will be inserted

Such a trivial todo is useless but you can tweak it by using variables as shown in the picture. Use "Insert Variable" button to insert variables.

When you use the pattern shown in the picture following will be inserted:

// TODO inserted by UserName [21. 1. 2015, 13:07:07]

Oracle Database Architectural Quick Reference

how to determine an index need to be rebuild

Is an index required to be rebuilt? We can check a post regarding this:

Richard Foote's Oracle Blog

Indexes will occupy a lot of space, so you can check whether it was being used. For this you can check out for v$object_usage. If needed you can also enable Monitoring usage of indexes by using 

Alter index enable Monitoring Usage ; 

If there is Fragmentation of Index then it needs to be rebuilt. Refer the above link mentioned in this discussion which is a very useful one.

We can check if an index needs to be rebuilt by executing the following statements:


ANALYZE INDEX index_name VALIDATE STRUCTURE;

SELECT HEIGHT, DEL_LF_ROWS, LF_ROWS, LF_BLKS FROM INDEX_STATS;
If the value for DEL_LF_ROWS/LF_ROWS is greater than 2, or LF_ROWS is lower than LF_BLKS, or HEIGHT is 4 then the index should be rebuilt.

Installation of Oracle Database 11g on Linux with ASM

Let’s assume that you have already installed Linux on your server. And disks are already bounded to the server.

1. Creating OS groups and users.

#Creating groups for Grid Infrastructure

groupadd asmadmin
groupadd asmdba
groupadd asmoper

#Creating groups for Oracle Software

groupadd oinstall
groupadd dba
groupadd oper

#Creating user for Grid Infrastructure

useradd -g oinstall -G dba,asmadmin,asmdba,asmoper -d /home/grid grid

#Creating user for Oracle Software

useradd -g oinstall -G dba,oper,asmdba -d /home/oracle oracle

#Setting password for users

passwd grid
passwd oracle

2. Creating necessary directories

mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/oracle
chown -R grid:oinstall /u01
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01

3. Creating .bash_profile-s

#For Oracle user

su – oracle
vi .bash_profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOME

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”
export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

umask 022

#For Grid user

su – grid
vi .bash_profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=+ASM; export ORACLE_SID

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/grid; export ORACLE_BASE

ORACLE_HOME=/u01/app/11.2.0/grid; export ORACLE_HOME

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”; export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

umask 022

4. Setting resource limits

Edit the following files:

# /etc/security/limits.conf

[root@orcl ~]# cat >> /etc/security/limits.conf <grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536

oracle soft nproc 2047
oracle  hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

# /etc/pam.d/login

[root@orcl ~]# cat >> /etc/pam.d/login <session required pam_limits.so
EOF

# /etc/profile

[root@orcl ~]# cat >> /etc/profile <if [ \$USER = “oracle” ] || [ \$USER = “grid” ]; then
if [ \$SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

# /etc/csh.login

[root@orcl ~]# cat >> /etc/csh.login <if ( \$USER == “oracle” || \$USER == “grid” )
then
limit maxproc 16384
limit descriptors 65536
endif
EOF

# /etc/hosts

[root@orcl ~]#  vi /etc/hosts

127.0.0.1 localhost.localdomain localhost
192.168.34.150 orcl

5. Installing ASMlib and creating disk groups

Download link: http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html

# To know your kernel version

uname -r
2.6.18-194.el5xen

# Download the following files

oracleasm-support-2.1.7-1.el5.x86_64.rpm
oracleasm-2.6.18-308.el5-2.0.5-1.el5.x86_64.rpm

oracleasmlib-2.0.4-1.el5.x86_64.rpm

# Installing

rpm -Uvh oracleasm-support-2.1.7-1.el5.x86_64.rpm
rpm -Uvh oracleasm-2.6.18-308.el5-2.0.5-1.el5.x86_64.rpm
rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

# Configure ASM

/usr/sbin/oracleasm configure -i

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

# Load ASM kernel module

/usr/sbin/oracleasm init

##Partition available disks for ASM

#list available disks and partitions

fdisk -l

#partitioning

fdisk /dev/sdb

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-52216, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-52216, default 52216):
Using default value 52216

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

fdisk /dev/sdc

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-52216, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-52216, default 52216): +100M

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

# Restart the server

init 6

# Creating ASM disks

/usr/sbin/oracleasm createdisk VOL1 /dev/sdb1
/usr/sbin/oracleasm createdisk CRSVOL /dev/sdc1

# List disks

/usr/sbin/oracleasm listdisks
VOL1
CRSVOL

# Testing disk discovery, that is used by DBCA

oracleasm-discover

Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library – Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:CRSVOL [208782 blocks (106896384 bytes), maxio 512]
Discovered disk: ORCL:VOL1 [838849977 blocks (429491188224 bytes), maxio 512]

6. Installing Oracle Grid Infrastructure

Download link: http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_grid.zip

Login as a grid user.

# Unzip and install

mkdir  /home/grid/myinstall
unzip /tmp/linux.x64_11gR2_grid.zip  -d  /home/grid/myinstall

cd /home/grid/myinstall
chmod -R 777 *
./runInstaller

Choose the following options:

6.1. Install and Configure Grid Infrastructure for a Standalone Server

6.2.  English

6.3. English

6.4.
Disk Group Name: CRS
Redundancy: External
Add Disks->Candidate Disks, choose just  ORCL:CRSVOL

6.5. Speciify password(s).

6.6.
ASM Database Administrator(OSDBA) Group : asmdba
ASM Instance Administration Operator(OSOPER) Group: asmoper
ASM Instance Administrator(OSASM) Group: asmadmin

6.7.
Oracle Base: /u01/app/gridSoftware Location: /u01/app/11.2.0/grid

6.8. On the pop-up windows press Yes.

6.9. Inventory Directory: /u01/app/oraInventory

6.10. On the prerequisite checks page, there will be failed checks. To solve:

6.10.1 Press Fix & Check Again
6.10.2 Login as root user and run the following:

[root@orcl ~]# /tmp/CVU_11.2.0.1.0_grid/runfixup.sh

6.10.3 Press OK on the Execute Fixup window

Left just libaio* and unixODBC* packages that should be installed. To solve:

6.10.4 Mount Oracle Enterprise Linux installation disk and go to the Server folder, where rpms are located.

[root@orcl Server]# rpm -Uvh libaio-devel-0.3.106-5.i386.rpm
warning: libaio-devel-0.3.106-5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@orcl Server]# rpm -Uvh libaio-devel-0.3.106-5.x86_64.rpm
warning: libaio-devel-0.3.106-5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-2.2.11-7.1.x86_64.rpm
warning: unixODBC-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-devel-2.2.11-7.1.x86_64.rpm
warning: unixODBC-devel-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]

6.10. 5   Press Check Again.(There should not be any error)

6.11.

[root@orcl ~]# /u01/app/oraInventory/orainstRoot.sh
[root@orcl ~]# /u01/app/11.2.0/grid/root.sh

Press enter…

7. Creating ASM disk groups for database

Connect as a grid user.

[grid@orcl ~]$ asmca

7.1 Press Create button.

7.2
Disk Group Name: DATARedundancy: External(None)Select Member Disks->Show Eligible, choose ORCL:VOL1

8. Installing Database, create instance on ASM

Connect as an oracle user.

Download Link: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html?ssSourceSiteId=ocomen

Unzip downloaded file to /home/oracle/myinstall.

[oracle@orcl linux.x64_11gR2_database]$ ./runInstaller

8.1 Uncheck “I with to receive security updates via My Oracle Support”

8.2 On the pop-up window choose yes.

8.3 Install database software only

8.4 Single instance database installation

8.5 English

8.6 Enterprise Edition (4.29GB)

8.7
Oracle Base: /u01/app/oracleSoftware Location:  /u01/app/oracle/product/11.2.0/db_1

8.8
Database Administrator(OSDBA) Group: dba
Database Operator(OSOPER) Group: oper

8.9 I have some failed checks that are fixable so I press Fix & Check Again button, login as a root user and run:

[root@orcl ~]# /tmp/CVU_11.2.0.1.0_oracle/runfixup.sh

Click OK on the Execute Fixup window.

8.10

[root@orcl ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
Press Enter…
y
y
y

9. Run DBCA as an oracle user.

[oracle@orcl ~]$ dbca

and follow the steps (choose ASM instead of File System option)

That’s all.

Source

Create ER Diagram using Oracle Sql Developer

To create a diagram for existing database schema or its subset:

File → Data Modeler → Import → Data Dictionary → select DB connection (add if none) → Next → last few steps intuitive.

(SQL Developer version 3.2.09.23.)

Useful VI commands

===================================================================================
Regular Expressions
===================================================================================
. (dot)    Any single character except newline
*            zero or more occurrences of any character
[...]        Any single character specified in the set
[^...]       Any single character not specified in the set
^            Beginning of the line
$            End of line
\<           Beginning of word
\>           End of word
=======================================================================================
1. Add something at the beginning of each line 

:%s/^/@

2. Add something at the end of each line 

:%s/$/.sql

3. Delete blank lines

:g/^$/d
:g/^ *$/d

4.remove all leading white space(s) from all lines
:1,$ s/^\s*//g

5.Remove the ^M characters at the end of all lines (Dos to Unix Conversion)

:%s/^V^M//g  ====The ^v is a CONTROL-V character and ^m is a CONTROL-M. When you type this, it will look like this: 
:%s/^M//g:%s/\r//g : Delete DOS returns ^M " 
Is your Text File jumbled onto one line? use following 
:%s/\r/\r/g : Turn DOS returns ^M into real returns

6. Duplicate every Line

:g/^/t.

7. Delete Duplicate Lines

:%s/^\(.*\)\n\1$/\1/        : delete duplicate lines
:%s/^\(.*\)\(\n\1\)\+$/\1/  : delete multiple duplicate lines [N]

8. Delete blank lines

:v/\S/d                     : Delete empty/blank lines
:g/^\s*$/d                  : delete all blank lines

9. Read lines from external command
:r!ls -ltr

10. Being able to determine the line number of the current line or the total number of 
lines in the file

:.= returns line number of current line at bottom of screen
:=  returns the total number of lines at bottom of screen
^g  provides the current line number, along with the total number of lines,in the file at the 
 bottom of the screen

Difference between scattered read and sequential read

A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. 

This is an o/s operation, most commonly used for single block reads. Single block reads are mostly commonly seen for index block access or table block access by a rowid (Eg: to access a table block after an index entry has been  seen)

This can also be a multiblock read. Then it will usually be a read from a SORT (TEMPORARY) segment as multiblock reads for table scans (and index fast full scans) usually show up as waiting on "db file scattered read"

A db file scattered read is the same type of event as "db file sequential read", except  that Oracle will read multiple data blocks. Multi-block reads are typically used on full  table scans. The name "scattered read" may seem misleading but it refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory.

if a query plan is "index range scan, table access by index rowid", it will use db file sequential reads, read index, read table block, read index, read table block - all single block IO. 

There are many blocks being read - sequentially from index to table, index to table. That is what db file sequential read 'means', sequentially from index to table. db file sequential read is the wait even for SINGLE BLOCK IO. A block at a time, block by block, from index to table. 

If We have a query on a table like 'select * from t where a=10', and we don't have an index on column a. Further, the FTS resulting from this query just returns 1 row.. is it a scattered read because all the blocks were scanned & read into the buffer to find that 1 block..?.. 

It would probably employ multiblock reads - which if done using physical IO would wait on db file scattered reads - meaning read a bunch of blocks and SCATTER them in the buffer cache.

Source: Ask Tom Site of Oracle

Terminator Termina Shortcut in Ubuntu

Its very useful to use shortcut than moving cursor operate terminal. Here is the shortcut for operating Terminator terminal in Ubuntu :

Excuting local shell script to remote Server

System admin needs to get different information from different servers for various purposes at different times. For the case of collecting informartion from many different servers, its best practise to make a script with the required query and execute that on those servers and collect the output. And that output may be needed to transfer to location PC for further annalysis.

The above task is so much time consuming for the case of different severs and gatthering the inforamtion in the local PC.  To get rid from the fatigue, we can perfrom the task with a little tricks.

Step 1: Wer can write down the scirpt with all the commands to execute on those servers.

Step 2: We can execute the script on the remote servers from local machine and forward the output to the output file.

We are focusing here the process of executing the local script to remote servers.

Suppose, we have a script[dbhostcheck.sh] as below for checking the system information:

#! /bin/bash

echo "Host Name:"|hostname

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "Disk Checking"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
df

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "CPU Utilization"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
sar 1 10

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "Memory Utilization"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"

vmstat 1 10

NO we want to execute this script on a remote server. For this purpose we can excute it with the below command:

ssh user_name@name_of_host "/usr/local/bin/bash -s" -- <./dbhostcheck.sh >output.txt

Through the ssh user_name@name_of_host we are remotely accessing the host
"/usr/local/bin/bash -s": we are making the the bash for collecting standard output.-- <./dbhostcheck.sh:  Providing script information to the bash
 >output.txt redirecting the output to the file names  output.txt

Finding jobs currently running or history about the jobs

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

Also one can use the following view to find the history details of job that has run.

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;

To find the jobs that haven’t succeeded
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where status ‘SUCCEEDED’;

ORA-01720: grant option does not exist for a table

We need users of specific role, need select privileges on tables and views owned by schema owners.


However while We try to grant select privileges on some views, I come accross a somewhat particular error as:

paul @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;
grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT
                         *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MMUSER_GW.DBBL_AGENT_DATA'

The reason for it is that the view VIEW_CUST_AGENT_DIST_INFO owned by AGENTBIP is built on top of the table DBBL_AGENT_DATA owned by someone else ie MMUSER_GW.

AGENTBIP cannot give privileges on these kind of views to someone else ie USER_1 as long as OWNER_VIEW has not the privileges WITH GRANT OPTION for the underlying tables

The solution

paul @ agentdb-live > Grant select on MMUSER_GW.DBBL_CONSUMER_ACCOUNT to AGENTBIP with grant option;

Grant succeeded.

SYSTEM @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;

Grant succeeded.

Find and kill a process in one line using bash

For operation and other pusposes, we need to kill process in linux system. For this purpose, at first we find the process id then we kill the process with the process id. i.e

[agentrpt@localhost ~]$ ps -ef | grep 'jar'
agentrpt 13131     1  0 16:49 ?        00:00:00 java -jar ABS_ALERT_MIDDLEWARE.jar
agentrpt 13278     1  0 16:54 ?        00:00:00 java -jar SMSMailClient.jar
agentrpt 13402 13367  0 16:58 pts/0    00:00:00 grep jar

For killing process of java -jar SMSMailClient.jar, We need to executge command as:
kill 13278

We can do it in a simple way. For the using the script regulary we can create a script with the below command.

In bash, you should be able to do:

kill $(ps aux | grep '[SMS]MailClient.jar' | awk '{print $2}')

Details on its workings are as follows:

The ps gives you the list of all the processes.
The grep filters that based on your search string, [SMS] is a trick to stop you picking up the actual grep process itself.
The awk just gives you the second field of each line, which is the PID.

Remove all files/directories except for one file

find . ! -name 'filetoexist.txt' -type f -exec rm -f {} +

will remove all files except filetoexist.txt. To remove directories, change -type f to -type d and add -r option to rm.

To exclude list of files: ! \( -name one_file -o -name two_file \)

In bash, to use rm !(file.txt), we will have to enable extglob:

$ shopt -s extglob
$ rm !(file.txt)

Note that extglob only works in bash and Korn shell family. And using rm !(file.txt) can cause an Argument list too long error.

In zsh, you can use ^ to negate pattern with extendedglob enabled:

$ setopt extendedglob
$ rm ^file.txt

or using the same syntax with ksh and bash with options ksh_glob and no_bare_glob_qual enabled.

To get IP Address of Sun Server

For a normal user (i.e., not 'root') ifconfig isn't in his path, but it's the command.

More specifically: /usr/sbin/ifconfig -a

If we want the IP Address easily, we need some scripting as:

/usr/sbin/ifconfig -a | awk 'BEGIN { count=0; } { if ( $1 ~ /inet/ ) { count++; if( count==2 ) { print $2; } } }'

Modifying file using Vim and awk

In some case, we have to change a big file with the same type of text. We can use vim editor for doing this type of task. Suppose, we have a files as below:

Now for a need, we need to generate delete script from this file contents as below:

For doing so, 

1. we have opened the file using vim editor. vim loglistws.txt

2.Then we have used the below commnads to add double quate around the words : 

   :%s/EJB_Str/"EJB_Str/g

   

Then save the file using :w

   :%s/\.log/\.log"/g

3. Now save the file using :wq!

4. Now we have made a new file form the existing file as:

    cat loglistws.txt | awk '{print "rm ",$9,$10,$11,$12}' > myscriptws.sh

Comparing between two packages

Many times we need to compare between different packages, wars, jars to find out the specific differences. We can use different tools for this purpose i.e pkgdiff, japi-complicance-checker, clirr. Here the process of finding differences has been depicted using the pkgdiff.

1. Download the zipped file from: https://github.com/lvc/pkgdiff/archive/1.7.2.tar.gz
2. Untar the files using the command: tar xvfz somefilename.tar.gz
3. Open the unzip folder.  in this folder make script is available.
4. Install the file using the command:  sudo make install prefix=/usr
5. Now compare the files using:  pkgdiff filename.jar filename_new.jar
6. The above command will provide the result.
7. A report will also be generated in html. We can get the details report from that file.

Archive Redo Log

An Oracle database can run in one of two modes. By default, the database is created in NOARCHIVELOG mode. Oracle Database lets you save filled groups of redo log files to one or more offline destinations using its ARCH process, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving.

When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the big corporate types tend to frown when a weeks worth of current production accounting data is lost forever. We can check the status of the archiving mode of a database suing hte following query:

SQL> archive log list;

SQL> select log_mode from v$database;

we can also with the below commnad to find the process:
$ ps -ef|grep -i _arc

To run a command repeatedly in Linux

For many cases, we need to execute the command repeatedly in the shell. We can do it by cron. But in that cat its not feasible to use cron if the interval time is less than 1 min or more. Suppose, we are inclined to get the output like the top command for a specific command. We can do that in the following ways as:

watch Command
# watch free -m [By Default 2 Sec Interval]
# watch -n 10 script.sh [For the interval of 10 Seconds]
Sleep Command:
a) inside For Loop
# for i in {1..10}; do echo -n "This is a test in loop $i "; date ; sleep
b) Inside while Loop
# while true; do echo -n "This is a test of while loop";date ; sleep 5; done

Creating DB Link in Oracle Database

At first we need to add the desired database connection info in tnsnames.ora file. Usually location of tnsnames.ora file in $ORACLE_HOME/network/admin.  We will add connection info as below:

 <connection_name> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <db_host_name>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <remote_db_service_name>)
    )
  )

SARMON: nmon for Solaris

nmon (short hand for Nigel's Monitor) is a computer performance system monitor tool for the AIX and Linux operating systems developed by IBM employee Nigel Griffiths.

SARMON is for Solaris machine with the functionality of nmon.

The installation is very ease

1. Get the .zip file form Sarmon Download Link

2. Save on /var/tmp

3. Unzip the .zip

# unzip sarmon_v1.01.bin_sparc.zip

4. Make backup of original files

# cp /usr/bin/sar /usr/bin/sar.orig
# cp /usr/bin/timex /usr/bin/timex.orig
# cp /usr/lib/sa/sadc /usr/lib/sa/sadc.orig

5. Move the sarmon to /usr/local

# mv /var/tmp/sarmon_v1.01.bin_sparc /usr/local/sarmon

6. Copy files

# cp /usr/local/sarmon/sar /usr/bin/sar
# cp /usr/local/sarmon/timex /usr/bin/timex
# cp /usr/local/sarmon/sadc /usr/lib/sa/sadc

7. Edit the crontab and put the entry for collect

0 0 * * * /usr/local/sarmon/sa1 300 288 &

This start collect every day at 00:00

The files generated are in /var/adm/sa/ (day-by-day)

Database Startup time

Startup information is stored in v$instance.startup_time,we can get the last start up time for an instance using:

 SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;

During a normal shutdown, Oracle writes message to the alert log, but under an emergency abort (i.e. un-plug the server), nothing appears in the logs, nor within Oracle. Oracle stops abruptly, and there is no way to see it, except periodic daemon polling for a PMON or SMON process.

Startup and shutdown are written to the alert log, externally, but you could define the alert log as an external table and then make a SQL query to parse out the shutdown and startup messages.

If you assume that PMON startup time is the same as the database startup time, you can get the uptime here:

select to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') from v$session where sid=1; 

To get the history of Database Startup, we can check using below query:

SELECT startup_time FROM dba_hist_database_instance ORDER BY startup_time DESC;

From the below query we can calculate the uptime, from v$instance and compute the total uptime for the database.

select 
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from 
   sys.v_$instance; 

Alter System vs Alter Database

ALTER SYSTEM is an Instance Level commands generally it applies for running processes, parameters etc where as ALTER DATABASE is a Database Level commands generally it applies to the physical structure of the database. Consider the RAC environment most of our ALTER SYSTEM command local to the instance (ALTER SYSTEM DUMP is an exceptional) and ALTER DATABASE command for the whole database.

Mostly we can use ALTER SYSTEM command when the database status is OPEN while Alter database we can use in MOUNT state.In the sense of Auditing ALTER DATABSE command cannot be audited where as ALTER SYSTEM can.

Finally Use the ALTER DATABASE statement to modify, maintain, or recover an existing and Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

Changing Database Default Tablespace for Users

At the time of user[Schema] creation, we define the tablespace for that user. But for some case, we may need to change the tablespace for a user. We can easily perform that following the below steps:

1. Create new Tablespace for schema
create tablespace datafile '/u01/app/oracle/oradata/orclpaul/_DBF.DBF' size 5120m autoextend on;
2. Take Export of schema on Seperate drive.
EXP SYSTEM/SYSMAN@pauldb-uat OWNER= FILE=/home/paul/dbdump/_2501016.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= /home/paul/dbdump/_2501016.log
3. Now create new schema or if you have already existing schema then drop and recreate it.
drop OLD_SCHEMA_NAME CASCADE;

CREATE USER
  IDENTIFIED BY VALUES
  DEFAULT TABLESPACE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

  --  Role for
  GRANT DBA TO WITH ADMIN OPTION;
  ALTER USER DEFAULT ROLE ALL;
  --  System Privilege for
  GRANT UNLIMITED TABLESPACE TO WITH ADMIN OPTION;
  -- Tablespace Quota for
  ALTER USER QUOTA UNLIMITED ON ;

alter user quota 0 on quota unlimited on ;


In the case while changing the default tablespace for the user's in the same database, don't forget to assign quota unlimited on new tablespace and revoke unlimited quota on old tablespace. This might not required while changing the tablespace of user's of different database.

revoke unlimited tablespace from ;

4. Now try to import the dump you have taken. Make sure dump is properly copied on the drive.

IMP SYSTEM/sysman@pauldb-uat FILE=/home/paul/dbdump/_2501016.DMP FROMUSER= TOUSER= LOG= /home/paul/dbdump/_2501016.log

Don't forget to assign back quota unlimited on tablespace.
alter user DEFAULT tablespace QUOTA UNLIMITED ON ;