Category Archives: Unix

Find and replace a string in several text files

Maybe you are looking for a particular phrase in log files or maybe you are a programmer and need to find some string that is inside many different code files?

Here is how you can find and replace a string in several text files.

For this example, the Oracle DBA wants to replace  strings NSR* in all rman scripts:

#!/usr/bin/ksh

for base in /oracle/scripts/rman/rcv/*.rcv
do
TMP=$(mktemp test.XXXXXX)
sed 's/NSR_SERVER=networker-hme0, NSR_DATA_VOLUME_POOL=BU UNT FULL/NSR_SERVER=freppax-laaf02h, NSR_DATA_VOLUME_POOL=FullHP/g' $base > "$TMP" && mv "$TMP" $base
done

export PS1="\$ORACLE_SID $LOGNAME@$(hostname):\${PWD}> "
stty erase ^H
export HISTSIZE=100
export EDITOR=vi
set -o vi
alias ll="ls -altr"
umask 022

Lock, expire and delete Linux account

delete Linux account

Sometimes the Oracle DBA has to I delete a Unix user account under Linux operating systems including the home directory.

How do I expire, delete or remove a user’s access from my server?

Deleting user account in Linux is an administrative task to remove user login credentials from system configuration files such as /etc/passwd, /etc/shadow and files which are owned by that particular user from the Unix server.

These command must be run as root user on Linux.

# Just Lock the password
usermod -L myusername
# Just Expire the account
chage -E0 myusername
# Delete the account. userdel is a low level utility for removing users. On Debian, administrators should usually use deluser instead.

# Be careful, User deletion is irreversible!
userdel myusername

-- Use these 2 options to delete that user's home directory and the spool of mails
-r : Remove Unix user account including home directory and mail spool
-f : Delete Linuxuser account with force removal of files

The userdel command modifies the following system account files:
/etc/group, /etc/login.defs, /etc/shadow, /etc/subgid and /etc/subuid.

 

How to clean associated objects?

If you want to clean other objects like cron jobs, files, print jobs; you will have to do it manually like that.

How to clean cron table
crontab -r -u myusername

How to clean print jobs
lprm myusername

How to change the owner of files owned by myusername
find / -user myusername -exec chown newUserName:newGroupName {} \;

 

Author: Vincent Fenoll, Oracle DBA in Montreal

 

Unable to create audit trail file or Read-only file system

This morning, the Cloud Control and other applications are down.

Hard for the customer :(  but as an Oracle DBA I love problems in production so it’s a great day  today!

First, I tried to edit a file with vi but a swap error raised:  “E297: Write error in swap file

Another read-only error with SQL*Plus and access to the audit file trail:

sqlplus / as sysdba
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.

ERROR:
ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925

 

It seems some of my file systems are read-only!

 

How to check if the file system are Read/Write or read-only?

cat /proc/mounts
proc /proc proc rw,relatime 0 0
sysfs /sys sysfs rw,relatime 0 0
devtmpfs /dev devtmpfs rw,relatime,size=8068884k,nr_inodes=2017221,mode=755 0 0
devpts /dev/pts devpts rw,relatime,gid=5,mode=620,ptmxmode=000 0 0
tmpfs /dev/shm tmpfs rw,relatime 0 0
/dev/sda2 / ext4 ro,relatime,data=ordered 0 0
/dev/sda1 /boot ext4 rw,relatime,data=ordered 0 0
/dev/sda7 /u01 ext4 ro,relatime,data=ordered 0 0
/dev/sda3 /tmp ext4 ro,relatime,data=ordered 0 0
/dev/sda5 /var ext4 ro,relatime,data=ordered 0 0

==> KO: Because we can see “ext4 ro” ro = Read only!

Infrastructure team informed us that the NAS was in trouble.
The reboot of the server solved the problem.

 

We can also verify other things, like free space and inodes space.

 

I can also check free space

 

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 9.5G 5.6G 3.4G 63% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 477M 158M 291M 36% /boot
/dev/sda7 91G 78G 8.8G 90% /u01
/dev/sda3 5.7G 26M 5.4G 1% /tmp
/dev/sda5 5.7G 267M 5.1G 5% /var

==> Everything is OK

 

In case you have a space problem you can list big files with this command:

find . -type f -size +50M
(ex: file > 50Mb)

'c' for bytes 'w' for two-byte words 'k' for Kilobytes (units of 1024 bytes) 'M' for Megabytes (units of 1048576 bytes) 'G' for Gigabytes (units of 1073741824 bytes)

 

Finally, I can check free inodes space 

 

An inode is used for each file on the filesystem. So running out of inodes generally means you’ve got a lot of small files laying around. 

 If you are very unlucky you have used about 100% of all inodes.  This bash command may help you:

df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda2 640848 73501 567347 12% /
tmpfs 2021073 2 2021071 1% /dev/shm
/dev/sda1 128016 60 127956 1% /boot
/dev/sda7 6045696 244708 5800988 5% /u01
/dev/sda3 384272 3009 381263 1% /tmp
/dev/sda5 384272 2088 382184 1% /var

We don’t have more than 12% inodes used (for /). I don’t have any problems with the number of inodes used.

In case you have a lot of inodes used, you can list the directories sorted with the number of files with this command:

find / -xdev -printf '%h\n' | sort | uniq -c | sort -k 1 -n

 

Author: Vincent Fenoll – Oracle DBA

ZFS snapshots (Overview)

A snapshot is a read-only copy of a file system or volume. Snapshots can be created almost instantly, and they initially consume no additional disk space within the pool. It’s important for the Oracle DBA in Montreal to have a list of these commands.

How can I create a new ZFS snapshot?

$sudo zfs snapshot rpool/DBORA/u01@u01snapshop

How can I list all ZFS files system and snapshots (in Bold)?

$ zfs list -r -t all

Or

$ zfs list -o space -r rpool

How can I know the creation date of the ZFS snapshots?

$ zfs list -r -t snapshot -o name,creation

How can I delete a ZFS snapshop?

$ sudo zfs destroy rpool/DBORA/u01@now

How can I use my ZFS snapshop to restore a single file?

I want to restore the file /u01/test.sh

$cd /u01
$cd .zfs/snapshot/u01snapshop/
$cp test.sh /u01/

or to restore a copie without replace current one :

$cp test.sh /u01/test2.sh

You can use a snapshot like any other (read-only) FS to move files or folder to another place.

How can I use my ZFS snapshop to restore a File system?

This command will discard all changes made to the file system since the snapshot was created:

$sudo zfs rollback rpool/DBORA/u01@u01snapshop
cannot rollback to ‘rpool/DBORA/u01@u01snapshop’: more recent snapshots exist
use ‘-r’ to force deletion of the following snapshots:
rpool/DBORA/u01@u01snapshop2
rpool/DBORA/u01@u01snapshop3

$sudo zfs rollback -r rpool/DBORA/u01@u01snapshop


More informations in the Oracle documentation.


Vincent Fenoll DBA Oracle Montreal

SQL Informations

The Oracle database administrator knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan.

SELECT    'USERNAME : ' || s.username  || CHR (10)   
		|| 'SCHEMA : '  || s.schemaname || CHR (10) 
		|| 'OSUSER : '  || s.osuser    || CHR (10)  
		|| 'PROGRAM : ' || s.program   || CHR (10)  
		|| 'MACHINE : ' || s.machine   || CHR (10)  
		|| 'TERMINAL : ' || s.terminal  || CHR (10)  
		|| 'SPID : '    || p.spid      || CHR (10)  
		|| 'SID : '     || s.sid       || CHR (10)  
		|| 'SERIAL# : ' || s.serial#   || CHR (10)  
		|| 'TYPE : '    || s.TYPE      || CHR (10)  
		|| 'SQL ID : '  || q.sql_id    || CHR (10)  
		|| 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  
		|| 'SQL TEXT : ' || q.sql_text 
          RESULT
  FROM v$session s, v$process p, v$sql q
 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';
 

To find the cpu intensive queries:
http://www.oracle-scripts.net/cpu-issues/

To display the Oracle explain plan:
http://www.oracle-scripts.net/display-cursor/

Vincent Fenoll – Montreal