install cx_Oracle on CentOS 7

Here is a simple way to install cx_Oracle on CentOS 7
in order to install , we will use Oracle instant client , and python pip

1. Download and install oracle client from Oracle

yum install libaio
rpm -Uvh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -Uvh oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
rpm -Uvh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

2. Add Oracle client to library path

echo "/usr/lib/oracle/12.1/client64/lib" >/etc/ld.so.conf.d/oracle.conf
ldconfig

3. install epel repo via yum extra

yum install epel-release

4. Install additional

yum install gcc python-pip.noarch python-devel

5. Install  cx_Oracle via pip

pip install cx_Oracle

 

Move ASM disk across storage online

This is a way you can use to copy/move your ASM disks to new location
without the need to stop your instance. it include command to be run
by root and by asm admin
1. creating the new disks(LUN) as ASM devices (run as root)

[[email protected] ~]# /etc/init.d/oracleasm listdisks
DATA1
OCR1
[[email protected] ~]# /etc/init.d/oracleasm createdisk DATA2 /dev/sdb8
Marking disk "DATA2" as an ASM disk:
[ OK ]
[[email protected] ~]#
[[email protected] ~]# /etc/init.d/oracleasm listdisks
DATA1
DATA2
OCR1

2. List disk in disk-group (run as asmadmin)

SQL> select GROUP_NUMBER,NAME,STATE,TOTAL_MB from v$asm_diskgroup ;
GROUP_NUMBER NAME
STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 OCR
MOUNTED
964
2 DATA
MOUNTED
4777
SQL> select NAME,GROUP_NUMBER,REDUNDANCY from v$asm_disk ;
NAME
GROUP_NUMBER REDUNDA
------------------------------ ------------ -------
0 UNKNOWN
DATA1
2 UNKNOWN
OCR1
1 UNKNOWN

3. Adding disk to DATA disk-group (run as asdmadmin)

SQL> ALTER DISKGROUP DATA REBALANCE POWER 0 ;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA2' NAME DATA2 ;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA DROP DISK DATA1 ;
Diskgroup altered.
SQL> ALTER DISKGROUP data REBALANCE POWER 1;
Diskgroup altered.
SQL> select NAME,GROUP_NUMBER,REDUNDANCY from v$asm_disk ;
NAME
GROUP_NUMBER REDUNDA
------------------------------ ------------ -------
0 UNKNOWN
DATA1
1 UNKNOWN
DATA2
1 UNKNOWN
OCR1

Note that DATA1 was marked as DROP , it will stop showing once all data sync to DATA2
the process can be monitored by this query (run as asmadmin)

SELECT g.name "Diskgroup",
100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance",
100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance",
100*(min(d.free_mb/d.total_mb)) "MinFree",
count(*) "DiskCnt",
g.type "Type"
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number and
--d.group_number = 1 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
12 GROUP BY g.name, g.type;
Diskgroup
Imbalance Variance MinFree
------------------------------ ---------- ---------- ---------- ----------
Type
------
OCR
EXTERN
DATA
EXTERN
•
DiskCnt
0 63.3817427 1
0 59.5143395 1

oracleasm cant find disks

When installing grid under Oracle Linux/RedHat , the grid installer may fail to see the asm disks
even if we just install the software , the asmca may also fail to see the disks .
in order to fix it , we just need to set the scanning path to where the disks are

/dev/oracleasm/disks/*

and now the installer can see the disks

Analyze Oracle Database schema

Why is it important to analyze , and what is analyze anyway ?
well analyze is a method to gather statistics on table objects in order for the optimizer
to choose the best way for executing queries .
for example the optimizer may choose to use full table scan or to use table index ,
it does so by looking at the table statistics .
Oracle doesn’t gather statistics on schema’s all by it self and the DBA must do it
as part of database maintenance . its is wise to analyze your schema on regular basis’s ,
depend on the data changes . i will show you a small script that can help you analyze your schema .

#!/bin/sh
#
# This script will call dbms_stats to Analyze SCHEMA_OWNER schema
ORACLE_SID=<sid name>
ORACLE_BASE=<path to oracle base>
ORACLE_HOME=<path to oracle home>
export ORACLE_SID ORACLE_BASE ORACLE_HOME
$ORACLE_HOME/bin/sqlplus -s " / as sysdba" <<eof1
spool /tmp/Analyzing.txt
exec dbms_stats.gather_schema_stats(ownname=>'SCHEMA_OWNER',estimate_percent=>5,cascade=>true);
exit;
eof1

tab completion for sqlplus

Every person who ever worked with bash tab auto complete
know how fast and convenient it is to use .
but when it comes to Oracle sqlplus under Linux there is no such thing as tab completion
on most cases even the arrow keys doesn’t work . in the simple following steps
we will fix all that by using a tool called rlwrap .
in this post i used CentOS because it is similar to RedHat
but before we can compile we need to solve some dependencies first .
rlwrap depends on GNU lib named readline , let compile this one first

yum install ncurses-devel.i386 libtermcap-devel.i386
wget ftp://ftp.gnu.org/gnu/readline/readline-6.2.tar.gz
tar -xzvf readline-6.2.tar.gz
cd readline-6.2/
./configure
make && make install
echo "/usr/local/lib/" >>/etc/ld.so.conf
ldconfig

now we are ready to to compile rlwrap ,

wget http://utopia.knoware.nl/~hlub/rlwrap/rlwrap-0.37.tar.gz
tar -xzvf rlwrap-0.37.tar.gz
cd rlwrap-0.37/
./configure
make && make install

After rlwrap is installed we can start using it . note that it will save the commands
history under the user home directory so don’t use login with passwords .
using rlwrap is simple as starting it before running sqlplus
rlwrap sqlplus myuser/
but there will be no tab auto complete …. well here is the trick ,
rlwrap can take a lists of auto complete words list as a file .
so just create the a file containing all words you wish to auto complete and
just run it
rlwrap -f ~/my_completions sqlplus myuser/
note :
you can put all Oracle dictionary on that file ,all your schema objects
along with all PL/SQL commands

Rotate Oracle logs

Oracle database logs doesn’t rotate by it self , and as time goes by, your
server may hold logs that are too big to read and takes too much storage space .
this can get your server to a maximum capacity , and in some cases crush your server .
The best thing i found is to use logrotate to handle this rotations .
there are 2 files that needs to be rotate ( depend on your infrastructure ) this files
are alert log and listener log . both can grow to unlimited size .
Create a new logrotate rule by edit a files
/etc/logrotate.d/oracle-alert and /etc/logrotate.d/oracle-listener
the oracle-alert file should point to the alert log usualy located under
$ORACLE_HOME/diag/rdbms/<database>/<sid>/trace/alert_.log
here is an example of oracle-alert , that will rotate weekly and store for 4 files back
also it will compress that backups and create a new file with the correct permissions .
* note that Oracle will create a new alert log ,if the file is missing, upon next event

/opt/app/DB/diag/rdbms/example/example1/trace/alert_example1.log {
compress
rotate 4
weekly
create 640 oracle oinstall
}

the next thing to handle is the listener , now the listener log cannot be remove just like that
if you do so , the listener would stop logging into that file . solving it with a special
commands that restart just the loger of the listener .
the location of the listener log is under $ORACLE_HOME/diag/tnslsnr/<database>/listener/trace/listener.log
This example shows how to weekly rotate and compress

/opt/app/DB/diag/tnslsnr/example/listener/trace/listener.log {
compress
rotate 4
weekly
create 640 oracle oinstall
prerotate
su - oracle "lsnrctl set Log_status off"
endscript
postrotate
su - oracle "lsnrctl set Log_status on"
endscript
}