Oracle in World: 10/19/08

Friday, October 24, 2008

Which Options are installed on your oracle database

There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.

2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.

Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.

3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID

17 rows selected.

Sunday, October 19, 2008

ORA-00923: FROM keyword not found where expected

Convert Decimal to hexadecimal on Oracle

Way 01


SQL> create or replace package number_utils as
  2          function d_to_hex(decimal_num in integer) return varchar2;
  3          pragma restrict_references (d_to_hex, wnds, wnps, rnps);
  4  end;
  5  /
Package created.

SQL> create or replace package body number_utils as
  2  function d_to_hex (decimal_num in integer)
  3  return varchar2 is
  4          v_result       varchar2(12);
  5          v_hex_digit    varchar2(1);
  6          v_quotient     pls_integer;
  7          v_remainder    pls_integer;
  8  begin
  9          if (decimal_num < 10) then
 10                 v_result := to_char(decimal_num);
 11          elsif (decimal_num < 16) then
 12                 v_result := chr(65+(decimal_num-10));
 13          else
 14                 v_remainder := mod(decimal_num,16);
 15                 v_quotient  := round((decimal_num - v_remainder) /16);
 16                 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
 17          end if;
 18          return v_result;
 19  end  d_to_hex;
 20  end number_utils;
 21  /
Package body created.

SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05

Way 02:


SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
  2    hex_num varchar2(64);
  3    digit  pls_integer;
  4    decimal_num pls_integer:=decimal_val;
  5    hexdigit  char;
  6  BEGIN
  7    while ( decimal_num > 0 ) loop
  8       digit := mod(decimal_num, 16);
  9       if digit > 9 then
 10          hexdigit := chr(ascii('A') + digit - 10);
 11       else
 12          hexdigit := to_char(digit);
 13       end if;
 14       hex_num := hexdigit || hex_num;
 15       decimal_num := trunc( decimal_num / 16 );
 16    end loop;
 17    return hex_num;
 18  END dec_to_hex;
 19  /
Function created.

SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 03:


SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
  2   v_decin NUMBER;
  3   v_next_digit NUMBER;
  4   v_result varchar(2000);
  5  BEGIN
  6    v_decin := dec_num;
  7    WHILE v_decin > 0 LOOP
  8    v_next_digit := mod(v_decin,16);
  9  IF v_next_digit > 9 THEN
 10   IF v_next_digit = 10 THEN v_result := 'A' || v_result;
 11    ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
 12    ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
 13    ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
 14    ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
 15    ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
 16    ELSE raise_application_error(-20600,'Untrapped exception');
 17   END IF;
 18  ELSE
 19   v_result := to_char(v_next_digit) || v_result;
 20  END IF;
 21  v_decin := floor(v_decin / 16);
 22  END LOOP;
 23  RETURN v_result;
 24  END dec_to_hex;
 25  /

Function created.

SQL> select dec_to_hex(17) from dual;

DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11

Way 04:


SQL> create or replace function dec_to_hex(dec_num in number )
  2  return varchar2
  3  is
  4     l_str   varchar2(255) default NULL;
  5     l_num   number  default dec_num;
  6     l_hex   varchar2(16) default '0123456789ABCDEF';
  7          p_base   number:=16;
  8  begin
  9     if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
 10             raise PROGRAM_ERROR;
 11     end if;
 12     loop
 13             l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
 14             l_num := trunc( l_num/p_base );
 15             exit when ( l_num = 0 );
 16     end loop;
 17     return l_str;
 18  end dec_to_hex;
 19  /

Function created.

SQL> select dec_to_hex(120) from dual;

DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.

Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78

SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;
Related Documents
http://arjudba.blogspot.com/2008/04/how-can-one-dump-examine-exact-content.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html

ORA-00600 internal error code, kmgs_pre_process_request_6

Error Description
In our production server while doing data pump import operation at the end of import it fails with following errors, then the instance crashed and database went down.
Errors in file /var/opt/dumpfile/bdump/arju_mman_23373.trc:
ORA-00600: internal error code, arguments: [kmgs_pre_process_request_6], [4], [2
], [0], [3], [0xC992C04D0], [], []
Sun Oct 19 18:16:20 2008
MMAN: terminating instance due to error 822
Instance terminated by MMAN, pid = 23373

In the trace file we got the call stack as,
ksedst, ksedmp, ksfdmp, kgerinv, kgeasnmierr, kmgs_pre_process_request, kmgsdrv, ksbabs, ksbrdp, opirip, opidrv, sou2o, opimai_real, main.

Cause of the problem
As we know in Oracle ORA-600 error series are oracle bug. This is oracle Bug 4433838. From alert log we see our MMAN (Memory Manager) process has been terminated. So something wrong happen with oracle MMAN.

This bug actually fires when the initialization parameter SGA_TARGET is set to an exact multiple of 4Gb. So, while setting big SGA be conscious.

A little bit more about this bug is if the SGA_TARGET value in HEXADECIMAL has 00000000 as the last bytes then you can hit this bug.

Detail Explanation of this bug
In out production server we set our SGA_TARGET=48G and this bug fires. We see 48G is multiple of 12 times of 4G. So this bug can error. A little bit elaborate of this issue,
Of 48G we get decimal value as,
SQL> col dec for 9999999999999
SQL> select 48*1024*1024*1024 dec from dual;
DEC
--------------
51539607552

Now let's get hexadecimal value of this number,
SQL> select to_char(51539607552,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
C00000000

As we see in the hexadecimal representation we get last 8 letters are all 0s; 00000000. The bug happens whenever in hexadecimal representation the 4 bytes have 0. Here in setting of 48G we get last 4 bytes all 0. As for last 0 in binary it represent 0000. So 2 letters of 0 in hexadecimal just form 1 byte(8 bits). So last 8 letters in hexadecimal means we got last 4 bytes 0. And forming this type causes to fire hug.

Solution of the problem

Solution 1)
As bug fires whenever in HEXADECIMAL representation we have 00000000 as the last 4 bytes(8 letters in hex value). So our target is to set SGA_TARGET so that it does not form like this way(4 bytes 0 at last). We can do this if we set our SGA_TARGET not exact multiple of 4G.
Doing set our SGA_TARGET to 47G would happen not the last 4 bytes as all 0s of binary representation.

ALTER SYSTEM SET SGA_TARGET=47G SCOPE=BOTH;
SQL> select to_char(47*1024*1024*1024,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
BC0000000
We see last 7 letters as 0, and hence the bug will not fire.

Solution 2)
Out database version was 10.2.0.1; base version. And this bug fires. This bug remains in oracle patchset 10.2.0.2 and 10.2.0.3. And is fixed in 10.2.0.4 patchset. So applying patchset 10.2.0.4 would fix the problem.

Solution 3)
Upgrade your database to 11.1.0.6 also will fix the problem.

Solution 4)
Apply the one-off Patch 4433838.

Related Documents
ORA-07445: exception encountered: core dump SIGSEGV
Crash Recovery Fails With ORA-27067

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Friday, October 24, 2008

Which Options are installed on your oracle database

Sunday, October 19, 2008

ORA-00923: FROM keyword not found where expected

Convert Decimal to hexadecimal on Oracle

ORA-00600 internal error code, kmgs_pre_process_request_6

Tag Cloud

Blog Archive

Which Oracle database version you use?