How to identified active files/executables when using opatch to deinstall software

During a opatch rollback operation, we saw the following error message in the opatch logfile:

[Feb 19, 2023 2:29:51 PM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:


                                    Following active files/executables/libs are used by ORACLE_HOME :/sw/oracle/product/19.18
                                    /sw/oracle/product/19.18/lib/libclntsh.so.19.1

Reason:
Some processes are still using the /sw/oracle/product/19.18/lib/libclntsh.so.19.1.
Use the fuser utility with verbose output to find the process:

fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac
                     oracle    595787 ....m ggsci

There were two open processes using the file libclntsh.so.19.1: 1. the Golden Gate Manager 2. a utility called prometheus_oracle_exporter

Solution:

log in as the Golden Gate software owner
ggsci --> info all --> list all processes
stop mgr !

One of two processes quit its handler on the file:

 fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac

For the prometheus agent, we simply kill the agent, and the output from fuser now reveals that no file handlers are open:

kill 48439
fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1

How to list all nfs mountpoints

findmnt -t nfs

TARGET SOURCE                                               FSTYPE OPTIONS
/u01   pzl2ora1:/Oracle/software/pzh0oric/u01 nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u02   pzl2ora1:/Oracle/pzh0oric/u02          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u03   pzl2ora1:/Oracle/pzh0oric/u03          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u04   pzl2ora1:/Oracle/pzh0oric/u04          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac

How to add a string to specific line in a text file

Here is an example of how I inserted a line needed in a postgres pg_restore command, at line 20:

sed -i '20s/^/SET search_path to sales,public;\n/' myfile.txt

If your string contains special characters, remember to escape it properly. In the next example, I want to append the string \timing at line number 7 in myfile.txt:

 sed -i '7s/^/\\timing on\n/' myfile.txt

How to replace a specific string in a very large text file

Here is how I removed the string "public." from a 4G file:

sed -i 's/public\.//g' mylargetextfile.txt

Notice the forward slash in front of the puncuation mark. It tells sed to interpret it literally, not as a special character used in regular expressions. Source: Stackoverflow

How to exctract a specific file from a .tar file

Say I would like to extract an old version of the oracle inventory, packed in a tar file called "oracle.tar". Change directory to the physical location you want to extract the files to:

cd /home/oracle

Find the path to the file you are interested in:

tar -tvf oracle.tar | grep oraInst.loc
-rw-rw---- oracle/dba        55 2018-01-25 15:02 ./oraInventory/oraInst.loc

Extract the tar file using the path displayed above:

tar xvf /u01/data/oracle.tar ./oraInventory/oraInst.loc

How to repeat a command for all the lines i a text file using vi

This is a handy command for all those using the vi editor to fix scripts with many lines. For example, I had to create statements like this:

catalog start with '/recovery_data/PRODDB01/archivelog/2021_05_06/o1_mf_1_391574_j98pt1n9_.arc';

and then execute it as a script in RMAN. I did the following

cd /recovery_data/PRODDB01/archivelog/2021_05_06
ls *.arc > register_06052021.cmd

open the file in vi:

vi register_06052021.cmd

check the number of lines:

:set number [enter]

press Shift+G [enter] - you're taken to the end of the file. In my case, the file had a total of 98 lines Go to the top of the file:

:0 [enter]

Now type to record your action:

q a I catalog start with '/recovery_data/PRODDB01/archivelog/2021_05_06/  ESC j q

Repeat it 97 times:

97 @ a

I also needed to add a closing '; at the end of each statement, to be able to pass it to RMAN. Turned out to be as easy as going to the top of the file again, and then execute:

q a A '; ESC j q

Repeat it 97 times:

97 @ a

How to change ownership of a symlink in unix

As pointed out in a post on StackExchange.com:

On a Linux system, when changing the ownership of a symbolic link using chown, by default it changes the target of the symbolic link (ie, whatever the symbolic link is pointing to).

Make a mount point directory in the root of your server, and give it the ownership you require:

su - 
cd /
mkdir -p /u09/fra/PRODDB01
chown -R oracle:dba u09
cd /u09/fra/PRODDB01/

Create a symlink that points to your desired destination:

ln -s /data1/onlinelog/TESTDB01 onlinelog
 ls -altr
total 8
drwxr-xr-x 3 oracle dba  4096 Apr 14 10:05 ..
lrwxrwxrwx 1 root   root   19 Apr 14 10:13 onlinelog -> /data1/onlinelog/TESTDB01
drwxr-xr-x 2 oracle dba  4096 Apr 14 10:13 .

Note that the symbolic link is owned by root, not user oracle, as I intended. The normal way of chaning ownership did not work:

chown oracle:dba onlinelog

However, add the -h option:

 chown -h oracle:dba onlinelog

And you will have your ownership of the symlink changed:

 ls -la
total 8
drwxr-xr-x 2 oracle dba 4096 Apr 14 10:14 .
drwxr-xr-x 3 oracle dba 4096 Apr 14 10:05 ..
lrwxrwxrwx 1 oracle dba   19 Apr 14 10:14 onlinelog -> /data1/onlinelog/TESTDB01

How to view the contens of a zip file without extracting it

Method 1:

zip -sf myzipfile.zip

Method 2:

zipinfo myzipfile.zip

How to select a specific line number using awk

The file clonetext.txt contains:

  Clone Volume: true
                      Clone Parent server Name: myserver1-cluster49
                        SnapClone Parent Volume: myvolume_mirror1
                        

You would like to get the name of the volume, which is the 4th word on the 3rd line of the file. When grepping for the keyword "Clone" all lines are returned:

 cat clonetext.txt | grep Clone
  Clone Volume: true
                      Clone Parent server Name: myserver1-cluster49
                        SnapClone Parent Volume: myvolume_mirror1

Grepping for the 4th column helps, but you still get two rows, not just the single one you're interested in:

oracle@oric-db01:[mydb01]# cat clonetext.txt | grep Clone | awk -F' ' '{print $4}'

Name:
myvolume_mirror1

Adding the NR flag to your command solves the problem:

cat clonetext.txt | grep Clone | awk -F' ' 'NR==3 {print $4}'
myvolume_mirror1

How to zip files in the current directory

In its simplest form, to zip a number of similary named files, use this syntax:

zip client_sql_trace client*.trc

where the resulting zip file will be called "client_sql_trace.zip" and contain all files in the current directory named client_.trc.

How to add a semicolon (or any other character) at the end of a line using vi

Open your text file in vi, and type

:%s/$/;/g

this will add a semicolon at the end of each line in your file.

How to display Linux kernel and version information on debian platforms

Use the lsb (Linux Standard Base) utility:

lsb_release -a

Output:

No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.1 LTS
Release: 18.04
Codename: bionic

Replace the -a switch with the -d switch to limit the output to the version only.

or view the file /etc/os-release:

cat /etc/os-release:

NAME="Ubuntu"
VERSION="18.04.1 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.1 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

last - show listing of last logged in users

A potentiall very useful tool when investigating what really happened, is the command "last".

Here is an abbrivated output:

root     pts/4        psl0ssh01.skead. Thu Jan 31 12:56   still logged in
oracle   pts/4        w7x64proddvh111. Thu Jan 31 12:50 - 12:52  (00:02)
root     pts/6        psl0ssh01.skead. Thu Jan 31 12:18 - 12:23  (00:05)
root     pts/5        psl0ssh01.skead. Thu Jan 31 11:08   still logged in
admin    pts/5        psa0addm2.skead. Wed Jan 30 20:21 - 20:23  (00:01)
admin    pts/5        psa0addm2.skead. Wed Jan 30 20:11 - 20:21  (00:09)

The shell variable HISTTIMEFORMAT and the "history" command

If you need to investigate the history of commands on your linux/Unix server, set the HISTTIMEFORMAT parameter first:

HISTTIMEFORMAT="%d/%m/%y %T " 

After that, you'll find a much more precis listing of your history:

  962  31/01/19 11:08:50 cd product/
  963  31/01/19 11:08:50 ls -al
  964  31/01/19 11:08:50 du -sh *
  965  31/01/19 11:08:50 cd ..

instead of

  962  cd product/
  963  ls -al
  964  du -sh *
  965  cd ..

The man page for history states

If the HISTTIMEFORMAT variable is set, the time stamp information associated with each history entry is written to the history file, marked with the history comment character.

How to send a file to a specific directory using wget

Here demonstrated while getting a file from our RH satellite server:

wget -P /home/oracle/myfiles http://satellite/oracle/cloningscripts/post_clone.sql

If the directory /home/oracle/mfiles is not present, it will be created.

How to recursively zip a folder and its subfolders and add password protetion+encryption

Below I am compressing all files and subfolders in the folder /home/oracle/outputfiles:

cd /home/oracle/outputfiles
zip -r --encrypt myzipfile.zip *

You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use

zip -r -q myzipfile mydir

where myzipfile is the name of the resulting zip file, and mydir is the name of the directory.
The .zip extension will be added to myzipfile automatically.

How to display processes in Linux in a tree-like fashion

The simplest way to see operating system processes and their sub-processes is to use

ps aufx

Output will look something like the following, where I have used the Oracle agent and its child proceesses to illustrate the formatted process tree:

oracle   33037  0.0  0.0 162892 15600 ?        S    Jan29   0:06 /u01/oracle/product/agent13c/agent_13.2.0.0.0/perl/bin/perl /u01/oracle/product/agent13c/agent_13.2.0.
oracle   33127  0.3  0.9 2714968 313308 ?      Sl   Jan29   5:01  \_ /u01/oracle/product/agent13c/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx128M -XX:MaxPermSize=
oracle   48285  0.0  0.0 331652 23220 ?        S    15:41   0:00      \_ /u01/oracle/product/agent13c/agent_13.2.0.0.0/perl/bin/perl /u01/oracle/product/agent13c/agent

Alternatively, use pstree:

pstree -p
pstree -p 12345

where 12345 is the process id.

For example, the Oracle agent is started via a perl script, and spawns multiple java child processes:

 ps -ef |grep agent | grep perl
oracle    12345      1  0 08:53 ?        00:00:00 /sw/oracle/product/agent13c/GoldImage/agent_13.4.0.0.0/perl/bin/perl /sw/oracle/product/agent13c/GoldImage/agent_13.4.0.0.0/bin/emwd.pl agent /sw/oracle/product/agent13c/agent_inst/sysman/log/emagent.nohup

Use the process id as an argument to pstree to see all the child processes process 12345 has spawned (output abbreviated):

pstree -p 12345
perl(12345)───java(32690)─┬─{java}(32691)
                          ├─{java}(32692)

How to match an exact string using grep

grep -w string

See it in use here

How to remove unwanted parameters from init.ora using a shell script

1. Create a "removal list" containing your unwanted parameters. For example, put the following into a text file called removal_list.txt, and save it in $ORACLE_HOME/dbs:

_b_tree_bitmap_plans
_fast_full_scan_enabled
_grant_secure_role
_like_with_bind_as_equality
_projection_pushdown

2. create a Shell script to loop through the lines in remval_list.txt, called remove_params.sh:

for a in $(cat removal_list.txt ); do
 echo now testing $a
 sleep 1
 curr_param=`grep -w $a init${ORACLE_SID}.ora | awk -F '[=]' '{ print $1 }'`
 if [ $curr_param ]; then
  echo removing $curr_param
  sed -i "/${curr_param}/d" init${ORACLE_SID}.ora
  sleep 1
 else
  echo $a not found in parameter file. Skipping...
  sleep 1
 fi;
done
exit 0

It is important to use the -w flag with grep, as many Oracle initialization parameters can be named similarly and the script will then save multiple values in the curr_param variable

3. Make Your script executable:

chmod 755 remove_params.sh

4. execute the script:

./remove_params.sh

Expected output:

now testing _b_tree_bitmap_plans
removing _b_tree_bitmap_plans
now testing _fast_full_scan_enabled
removing _fast_full_scan_enabled
now testing _grant_secure_role
_grant_secure_role not found in parameter file. Skipping...
now testing _like_with_bind_as_equality
removing _like_with_bind_as_equality
now testing _projection_pushdown
_projection_pushdown not found in parameter file. Skipping...

How to find the time for the last system reboot on your linux server

# who -b
         system boot  2017-11-10 01:23