Ferhat's Blog

There will be only one database

Posts Tagged ‘oracle’

ORA_HASH and NLS CHARACTERSET to compare code

Posted by fsengonul on November 15, 2015

Task :  Find out whether a system trigger or procedure is same in 100+ databases.
First idea : Let’s use ORA_HASH for the source code.
Action 1: Use LISTAGG to create a single line of data from dba_source. Get rid of the spaces.

SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP';

Action 2: Then use it input to ORA_HASH .


--DB Number 1;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
1418146976

--DB Number 2;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
4280078353

And here comes the problem; even though the code is the same , the hash values are grouped into two separate sets.

A little investigation shows that the NLS_CHARACTERSET parameter is different for the two sets of dbs  as AL32UTF8 and WE8ISO8859P9. For the Turkish characters the first characterset uses 2 bytes and the latter uses 1 byte only; and this changes the ora_hash result !

So let’s use CONVERT to  reach a final point:


--DB Number 1:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;

HASH         NLS
---------- ----------------------------------------------------------------
1418146976 AL32UTF8

--DB Number 2:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;
HASH        NLS
---------- ----------------------------------------------------------------
1418146976 WE8ISO8859P9

The SP used to test:

create or replace  procedure helloworld as
begin
    DBMS_OUTPUT.PUT_LINE(q'[Hell'o World ç]');
end;
/
Advertisements

Posted in oracle | Tagged: , , | Leave a Comment »

External tables with symbolic links (DISABLE_DIRECTORY_LINK_CHECK)

Posted by fsengonul on November 12, 2015

The first question that was popped up in my mind when I started to use external tables in 10g days was: “Why doesn’t oracle let us use symbolic links?”

Everybody was mentioning about security issues. If the DBA is the only one to have access to the oracle machine, there may be a pardon for this restriction.

It seems, I’m not alone. When searching for something else I’ve seen DISABLE_DIRECTORY_LINK_CHECK in the 11.2 documentation.

And of course I have that itchy feeling whether it was always there in 10g, also. 🙂

Below is the example continuing from the skeleton from psoug.


SYSTEM@L11R 20151008 14:31:47 SQL> sta demo/external_tables/5_change_it_to_symbolic_link.sql

SYSTEM@L11R 20151008 14:32:20 SQL>

SYSTEM@L11R 20151008 14:32:20 SQL> select * from ext_tab;



EMPN ENAME                JOB                  DE

---- -------------------- -------------------- --

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:32:20 SQL> --Now create sybolic links in OS.

SYSTEM@L11R 20151008 14:32:20 SQL> --

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo1.dat real_demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo2.dat real_demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo1.dat demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo2.dat demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL>



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 16

-rw-r--r--.  1 oracle oinstall  111 Oct  8 13:43 real_demo1.dat

-rw-r--r--.  1 oracle oinstall  121 Oct  8 13:43 real_demo2.dat

drwxr-xr-x. 34 oracle oinstall 4096 Oct  8 13:51 ..

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo2.dat -> real_demo2.dat

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo1.dat -> real_demo1.dat

drwxr-xr-x.  2 oracle oinstall 4096 Oct  8 14:37 .

[oracle@ol6-112-rac1 ext_dir]$







SYSTEM@L11R 20151008 14:32:20 SQL> --let's query again to see the error

SYSTEM@L11R 20151008 14:32:20 SQL> pause;



SYSTEM@L11R 20151008 14:33:22 SQL>

SYSTEM@L11R 20151008 14:33:22 SQL> select * from ext_tab;

select * from ext_tab

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P000, instance ol6-112-rac1.localdomain:RAC1 (1)

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04027: file name check failed: /home/oracle/ext_dir/demo1.dat





Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:03 SQL>

SYSTEM@L11R 20151008 14:34:03 SQL> --This is the error

SYSTEM@L11R 20151008 14:34:03 SQL> --now drop and recreate with DISABLE_DIRECTORY_LINK_CHECK

SYSTEM@L11R 20151008 14:34:03 SQL> pause;



SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> drop table ext_tab;



Table dropped.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> CREATE TABLE ext_tab (

2  empno  CHAR(4),

3  ename  CHAR(20),

4  job    CHAR(20),

5  deptno CHAR(2))

6  ORGANIZATION EXTERNAL (

7    TYPE oracle_loader

8    DEFAULT DIRECTORY ext_dir

9      ACCESS PARAMETERS (

10      RECORDS DELIMITED BY NEWLINE

11      DISABLE_DIRECTORY_LINK_CHECK

12      FIELDS TERMINATED BY ','

13      MISSING FIELD VALUES ARE NULL

14      REJECT ROWS WITH ALL NULL FIELDS

15      (empno, ename, job, deptno))

16      LOCATION ('demo1.dat','demo2.dat')

17    )

18  PARALLEL

19  REJECT LIMIT 0

20  NOMONITORING;



Table created.



Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> pause;



SYSTEM@L11R 20151008 14:34:05 SQL> --let's query again

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL> SELECT * FROM ext_tab;



EMPN ENAME                JOB                  DE

---- -------------------- -------------------- --

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.05

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

<b> </b>



/* What if we put the files in another folder */



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 24

drwxr-xr-x. 35 oracle oinstall  4096 Oct 20 09:11 ..

-rw-r--r--.  1 oracle dba      14504 Oct 20 09:12 log.log

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo1.dat -> ../real_dir/real_demo1.dat

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo2.dat -> ../real_dir/real_demo2.dat

drwxr-xr-x.  2 oracle oinstall  4096 Oct 20 09:13 .





SYSTEM@L11R 20151020 09:12:34 SQL> SELECT * FROM EXT_TAB;



EMPN ENAME                JOB                  DE

---- -------------------- -------------------- --

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.





/* What if we send to /dev/null */



[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo1.dat

[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo2.dat

[oracle@ol6-112-rac1 ext_dir]$



SYSTEM@L11R 20151020 09:14:19 SQL> SELECT * FROM EXT_TAB;



no rows selected



Elapsed: 00:00:00.38

SYSTEM@L11R 20151020 09:26:19 SQL>

Posted in oracle | Tagged: , , | Leave a Comment »

ipmitool to measure electricity usage of Exadata

Posted by fsengonul on September 16, 2013

If you’re curious about the electricity usage of an exadata rack  ( or have shortage of power in your data center) you may try to use a smart PDU.
But there is a better and cheaper way to measure it by using the ipmitool. After the collection, it’s so easy to create a graph and compare different exadata versions.

exadata_electricity_compare
In this graph , 2 X3-2 HC(High Capacity) / 2 X2-2 HP(High Performance) and 1 v2 SATA racks are compared. The electricity usage of the HP disks seems much more than the HC ones. It would be interesting to compare the relationship between the throughput, cpu usage and electricity.The details are below:

[root@xxx01 ~]# ipmitool sensor | grep -i vps
VPS_CPUS         | 50.000     | Watts      | ok    | na
VPS_MEMORY       | 12.000     | Watts      | ok    | na
VPS_FANS         | 42.000     | Watts      | ok    | na
/SYS/VPS         | 370.000    | Watts      | ok    | na

Our sysadmin Mustafa Altuğ Kamacı has coded a nice script to collect this info from all compute and storage cells. The script is triggered from the crontab.

[root@xxx01 ~]# cat /usr/bin/pwrstat
#!/bin/ksh
PATH=$PATH:/usr/bin:/usr/sbin:/bin
export PATH
d=`date '+%d%m%y'`
t=`date '+%H:%M'`
integer P1=0
integer p1=0
for i in `cat /root/group_all`
do
p1=`ssh -q $i "ipmitool sensor get /SYS/VPS|grep 'Sensor Reading'"|awk '{a=a+$4}END{print a }'`
P1=$P1+$p1
done
echo $t " " $P1 "Watt"  >> /home/pwrstat/pwrstat_$d.log
[root@xxx01 ~]#
root@maxdb01 pwrstat]# ls -al
total 376
drwxr-xr-x  2 root root 4096 Sep 16 00:00 .
drwxr-xr-x 23 root root 4096 Sep 10 15:26 ..
-rw-r--r--  1 root root 3173 Aug  1 23:55 pwrstat_010813.log
-rw-r--r--  1 root root 5472 Sep  1 23:55 pwrstat_010913.log
-rw-r--r--  1 root root 5472 Aug  2 23:55 pwrstat_020813.log
-rw-r--r--  1 root root 5472 Sep  2 23:55 pwrstat_020913.log
-rw-r--r--  1 root root 5472 Aug  3 23:55 pwrstat_030813.log
-rw-r--r--  1 root root 5472 Sep  3 23:55 pwrstat_030913.log
-rw-r--r--  1 root root 5472 Aug  4 23:55 pwrstat_040813.log
.
.
.
[root@xxx01 pwrstat]# cat pwrstat_010913.log
00:00   17580 Watt
00:05   17890 Watt
00:10   17350 Watt
00:15   17510 Watt
00:20   17990 Watt
00:25   17800 Watt
00:30   17640 Watt
00:35   17720 Watt
00:40   17780 Watt
00:45   17830 Watt
00:50   17950 Watt
00:55   17410 Watt
01:00   17970 Watt
01:05   17510 Watt
01:10   17600 Watt

Posted in Exadata | Tagged: , , | 2 Comments »