January 14th 2015

Simple script shutdown database remote

Beberapa waktu lalu ada kebutuhan untuk membuat script shutdown database secara remote dari server lain. Sebenarnya ini bukanlah hal yang baik dari sisi security karena bisa saja disalah gunakan. Dengan memanfaatkan profile pada user oracle, kita bisa melakukan shutdown database remote dengan melakukan ssh ke db server menggunakan user oracle ( dengan catatan tahu password oracle usernya..happy )

Agar pada saat melakukan ssh tanpa perlu lagi untuk memasukkan password, kita bisa memanfaatkan fitur sshpass untuk non-interactive password authentication. Pastikan sshpass sudah terinstall di host machine.

1
2
3
4
5
6
7
8
#!/bin/bash
HOST=hostdb01
USER=oracle
PASSWD=0Rcl\$ppxl
/usr/bin/sshpass -p $PASSWD /usr/bin/ssh $USER@$HOST ". \$HOME/.bash_profile
export ORACLE_SID=TESTDB
echo \"shutdown immediate\" | sqlplus \"/ as sysdba\"
"

Dengan mengetahui password dari oracle user pada dbserver, kita bisa memanfaatkannya untuk melakukan shutdown pada database target :D . Salah satu point hardening database oracle yaitu dengan tidak menggunakan default user "oracle" pada saat instalasi..happy.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[apps@hostbackend01 ~]$ ./shutdown_db
 
SQL*Plus: Release 11.2.0.3.0 Production ON Tue Jan 13 13:46:17 2015
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL> DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

Tags: ,

No Comments yet »

January 12th 2015

TNS Lost Contact dan log error "libocrb11.so: undefined reference" pada saat create database 11gR2 di Ubuntu

Saat melakukan instalasi database 11gR2 pada OS Ubuntu 14.04.1 LTS, pada saat create database selalu muncul error TNS Lost contact. Bila dilihat detail log error sebagai berikut :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
INFO:  - Linking Oracle
 
INFO: rm -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle
 
INFO: gcc  -o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -L/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/11.2.0/dbhome_1/lib/ -L/u01/app/oracle/product/11.2.0/dbhome_1/lib/stubs/   -Wl,-E /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /u01/app/oracle/product/11.2.0/
INFO: dbhome_1/lib/nautab.o /u01/app/oracle/product/11.2.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/11.2.0/dbhome_1/lib/naect.o /u01/app/oracle/product/11.2.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/1
INFO: 1.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 `if [ -f /u01/app/oracle/product/11.2.0/dbhome_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /u01/app/oracle/product/11.2.0/dbhome_1/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /u01/app/oracle/
INFO: product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /u01/app/oracle/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/dbhome_1/lib/
INFO: ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/u01/app/oracle/product/11.2.0/dbhome_1/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -ls
INFO: kgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11
INFO:  -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /u01/app/oracle/product/11.2.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/11.2.0/dbhome_1/lib -lm    `cat /u01/app/oracle/product/11.2.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/11.2.0/dbhome_1/lib
 
INFO: /u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference TO `kgfoCloseFile'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoCommit2P'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoErrorMessage'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference TO `kgfoGetSize'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoOpenDirty'
/u01/app/oracle/produc
INFO: t/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoIO'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference TO `kgfoCreateCtxExt'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoCreate2P'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoCheckHdl'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrutl11.so: undefined reference TO `lfifcp'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.
INFO: so: undefined reference to `kgfoDestroyCtx'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoRenameFile'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference TO `kgfoCreateFile'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoDeleteFile'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoFnameMax'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined refer
INFO: ence TO `kgfoCheckMount'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoControl'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrb11.so: undefined reference to `kgfoOpenFile'
/u01/app/oracle/product/11.2.0/dbhome_1/lib//libocrutl11.so: undefined reference TO `lfieno'
collect2: error: ld returned 1 exit status
make: *** [/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle] Error 1
 
INFO: End output from spawned process.
INFO: ----------------------------------
SEVERE: oracle.sysman.oii.oiil.OiilActionException: Error in invoking target 'irman ioracle' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk'. See '/u01/app/oracle/product/11.2.0/dbhome_1/install/relinkActions2015-01-08_04-27-18-PM.log' for details.
        at oracle.sysman.oii.oiis.OiisMakeDeps.invokeMakefile(OiisMakeDeps.java:537)
        at oracle.sysman.oii.oiis.OiisMakeDeps.doRelink(OiisMakeDeps.java:614)
        at oracle.sysman.oii.oiis.OiisMakeDeps.doOperation(OiisMakeDeps.java:799)
        at oracle.sysman.oii.oiis.OiisMakeDeps.main(OiisMakeDeps.java:809)

Setelah googling & cek ricek, error tersebut terjadi karena pada saat relink library menggunakan versi gcc yang lebih baru. Default gcc pada Ubuntu 14.04.1 adalah gcc-4.8 sedangkan proses relink binary oracle harus menggunakan gcc-4.4

Install gcc-4.4, lalu create symbolink link pada gcc akan menyelesaikan masalah diatas.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
root@oradb01:/usr/bin# ls -ltr gcc*
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-ranlib-4.8
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-nm-4.8
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-ar-4.8
-rwxr-xr-x 1 root root 775888 Apr  5  2014 gcc-4.8
lrwxrwxrwx 1 root root     14 Apr  8  2014 gcc-ranlib -> gcc-ranlib-4.8
lrwxrwxrwx 1 root root     10 Apr  8  2014 gcc-nm -> gcc-nm-4.8
lrwxrwxrwx 1 root root     10 Apr  8  2014 gcc-ar -> gcc-ar-4.8
lrwxrwxrwx 1 root root      7 Jan  8 16:36 gcc -> gcc-4.8
 
# rm gcc
# ln -s gcc-4.4 gcc
# ls -ltr gcc*
-rwxr-xr-x 1 root root 349120 2011-09-16 07:31 gcc-4.6
-rwxr-xr-x 1 root root 275952 2011-09-17 03:10 gcc-4.5
-rwxr-xr-x 1 root root 259232 2011-10-05 14:56 gcc-4.4
lrwxrwxrwx 1 root root 7 2012-02-01 11:25 gcc -> gcc-4.4
 
 
root@oradb01:/usr/bin# ls -ltr gcc*
-rwxr-xr-x 1 root root 255080 Mar 20  2014 gcc-4.4
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-ranlib-4.8
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-nm-4.8
-rwxr-xr-x 1 root root  26968 Apr  5  2014 gcc-ar-4.8
-rwxr-xr-x 1 root root 775888 Apr  5  2014 gcc-4.8
lrwxrwxrwx 1 root root     14 Apr  8  2014 gcc-ranlib -> gcc-ranlib-4.8
lrwxrwxrwx 1 root root     10 Apr  8  2014 gcc-nm -> gcc-nm-4.8
lrwxrwxrwx 1 root root     10 Apr  8  2014 gcc-ar -> gcc-ar-4.8
lrwxrwxrwx 1 root root      7 Jan  8 16:36 gcc -> gcc-4.4

Lalu jalankan perintah relink kembali.

1
/u01/app/oracle/product/11.2.0/dbhome_1/bin/relink ALL

Tags: ,

No Comments yet »

January 9th 2015

Tips Install 11gR2 pada Ubuntu

Sebenarnya Linux Ubuntu (Debian base) tidak di support oleh oracle, hal ini sudah di state pada dokumentasi oracle. Karena ada kebutuhan untuk install database pada distro ini, maka test install 11.2.0.4 pada Ubuntu 14.04.1. Beberapa notes yang bisa dijadikan referensi ketika melakukan instalasi pada Ubuntu :

1. Install package-package berikut ini :

1
2
3
4
5
6
7
8
build-essential 
libaio1 
gawk 
ksh 
libmotif3 
alien 
libtool 
lsb-rpm

2. Default shell yang masih menggunakan dash, dirubah menjadi bash.

1
2
3
4
5
6
root@oradb01:~# cd /bin
root@oradb01:~# ls -ltr /bin/sh
lrwxrwxrwx 1 root root 4 Jan  8 14:37 /bin/sh -> dash
root@oradb01:~# ln -sf bash /bin/sh
root@oradb01:~# ls -ltr /bin/sh
lrwxrwxrwx 1 root root 4 Jan  8 14:37 /bin/sh -> bash

3. Create symlink untuk beberapa command seperti awk, rpm, rc.

1
2
3
4
5
root@oradb01:~# ln -s /usr/bin/awk /bin/awk
root@oradb01:~# ln -s /usr/bin/rpm /bin/rpm
root@oradb01:~# ln -s /usr/bin/basename /bin/basename
root@oradb01:~# mkdir /etc/rc.d
root@oradb01:~# FOR i IN 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done

4. Create group, create user, setting kernel parameter pada /etc/sysctl.conf, setting limit pada /etc/security/limit.conf sama seperti pada umumnya melakukan instalasi di Redhat.

5. Pastikan versi dari library sesuai dengan requirement.

Pada saat pre-requisite check ketika melakukan instalasi database, akan muncul dependensi package yang belum diinstall. Ignore semua dependensi tersebut.

Ketika proses instalasi berjalan, akan muncul error :

Untuk solving error invoking target ‘install’ & ‘agent nmhs’, create symbolik link pada file libgcc_s.so.1.

1
ln -fs /u01/app/oracle/product/11.2.0/dbhome_1/lib/stubs/libgcc_s.so.1 /lib64/libgcc_s.so.1

Lalu tekan continue pada popup error tersebut.

Tags: , ,

Comments Off

November 13th 2014

Root.sh failed to start ketika melakukan instalasi oracle ASM 11.2.0.3 (Oracle Restart)

Pada saat melakukan instalasi ASM instance versi 11.2.0.3 di sistem operasi Redhat Linux 6 berjalan dengan lancar, hanya saja terdapat kendala saat menjalankan root.sh. Terdapat error seperti berikut :

1
2
3
4
5
6
7
8
9
10
11
12
13
The following environment VARIABLES are SET AS:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/grid
Entries will be added TO the /etc/oratab file AS needed BY
DATABASE Configuration Assistant WHEN a DATABASE IS created
Finished running generic part OF root script.
Now product-specific root actions will be performed.
USING configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
/u01/app/oracle/product/11.2.0/grid/bin/clscfg.bin: error while loading shared libraries: libcap.so.1: cannot OPEN shared object file: No such file OR directory
Failed TO CREATE KEYS IN the OLR, rc = 127, Message:
 
Failed TO WRITE the checkpoint:'' WITH STATUS:FAIL.Error code IS 256

Bila dilihat dari error yang terjadi, terdapat library (libcap.so.1) yang tidak ada / tidak ditemukan. libcap.so.1 terdapat di dalam package compat-libcap1. So, pastikan compat-libcap1 sudah terinstall..winking

Tags:

No Comments yet »

February 7th 2014

Enable/Disable service startup pada RAC 11gR2

Pada saat selesai melakukan instalasi Oracle RAC, secara default status semua service (cluster, asm, database) yaitu enable sehingga pada saat server di reboot maka oracle service akan otomatis up. Untuk melakukan disable dan enable service pada level cluster, asm, dan database, kita bisa menggunakan command srvctl enable/disable service.

Disable service database

1
[oracle@rac11n1 ~]$ srvctl disable DATABASE -d SASDB

Disable service instance

1
[oracle@rac11n1 ~]$ srvctl disable instance  -d SASDB -i SASDB1

Disable service asm

1
[oracle@rac11n1 ~]$ srvctl disable asm -n SASDB1

Enable service database

1
[oracle@rac11n1 ~]$ srvctl enable DATABASE -d SASDB

Enable service instance

1
[oracle@rac11n1 ~]$ srvctl enable instance  -d SASDB -i SASDB1

Enable service asm

1
[oracle@rac11n1 ~]$ srvctl enable asm -n SASDB1

Tags: ,

No Comments yet »

Next »