Wednesday, 30 July 2014

ORA-00445: background process "m001" did not start after 120 seconds ---ORA-00445: background process "J003" did not start after 120 seconds

ORA-00445: background process "m001" did not start after 120 seconds 
    Incident details in: /app/u01/app/oracle/diag/rdbms/incident/incdir_3721/db1_mmon_7417_i3721.trc 
    ERROR: Unable to normalize symbol name for the following short stack (at offset 2): 
    Tue Jun 21 03:03:06 2011 
    ORA-00445: background process "J003" did not start after 120 seconds 
    or 
    Waited for process W002 to initialize for 60 seconds 
    The system appears to be running very slowly and defunct processes can appear. 
    Changes 
    REDHAT 5 kernel 2.6.18-194.el5 #1 SMP Tue Mar 16 
    Oracle 11.2.0.2 Single Instance 
    IBM: Linux on System z 
    Cause 
    Recent linux kernels have a feature called Address Space Layout Randomization (ASLR). 
    ASLR  is a feature that is activated by default on some of the newer linux distributions. 
    It is designed to load shared memory objects in random addresses. 
    In Oracle, multiple processes map a shared memory object at the same address across the processes. 
     
    With ASLR turned on Oracle cannot guarantee the availability of this shared memory address. 
    This conflict in the address space means that a process trying to attach a shared memory object to a specific address may not be able to do so, resulting in a failure in shmat subroutine. 
     
    However, on subsequent retry (using a new process) the shared memory attachment may work. 
    The result is a random set of failures 
    Solution 
    It should be noted that this problem has only been positively diagnosed in Redhat 5 and Oracle 11.2.0.2. 
    It is also likely, as per unpublished BUG:8527473,  that this issue will reproduce running on Generic Linux platforms running  any Oracle 11.2.0.x. on Redhat/OEL kernels which have ASLR.  
     
    ASLR also exists in SLES10 and SLES 11 kernels and by default ASLR is turned on, however, to date not problem has been seen on SuSE. 
     
    You can verify whether ASLR is being used as follows: 
     
     # /sbin/sysctl -a | grep randomize 
    kernel.randomize_va_space = 1 
     
    If the parameter is set to any value other than 0 then ASLR is in use. 
    On Redhat 5 to permanently disable ASLR. 
    add/modify this parameter in /etc/sysctl.conf 
    kernel.randomize_va_space=0 
    kernel.exec-shield=0 
     
    You need to reboot for kernel.exec-shield parameter to take effect.  
    Note that both kernel parameters are required for ASLR to be switched off. 

Friday, 30 May 2014

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], []

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], []


The Problem:

Oracle Database remains in mounted state. It can't be opened. If it is focibly opened it throws error (ora-00600) as shown below:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []

SOLUTION:

Use the command- 'recover database' as shown below:

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.3517E+10 bytes
Fixed Size                  2213456 bytes
Variable Size            1.4496E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              160751616 bytes
Database mounted.
SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

Monday, 7 April 2014

Bulk collect is the bulk binding syntax for select statements.(Performance tuning - PL/SQL)


Bulk Collect


Executing sql statements in plsql programs causes a context switch between the plsql engine and the sql engine.
Too many context switches may degrade performance dramatically. In order to reduce the number of these context
switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk binding is available for select, insert, delete and update statements.

Bulk collect is the bulk binding syntax for select statements. All examples below are simplified versions of the live cases i have seen.

One of the things i usually come across is that developers usually tend to use cursor for loops to process data. They declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch.


SQL> create table a_all_objects as select * from all_objects;

Table created.

SQL> select count(*) from a_all_objects;

  COUNT(*)
----------
     90154

SQL> insert into a_all_objects select * from a_all_objects;

90154 rows created.

SQL> /

180308 rows created.

SQL> commit;

Commit complete.

SQL> set timing on
SQL> declare
cursor c1 is select OBJECT_NAME from a_all_objects;
      rec1 c1%rowtype;
  begin
  open c1;
  loop
     fetch c1 into rec1;
     exit when c1%notfound;

     null;

  end loop;
  end;
  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:06.17
SQL>
SQL> declare
 cursor c1 is select OBJECT_NAME from a_all_objects;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
  begin
  open c1;

     fetch c1 bulk collect into rec1;


  end;
  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.37
SQL>
SQL>
SQL> alter system flush buffer_cache;

System altered.

As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.

The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process,
 we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.


SQL> declare
cursor c1 is select object_name from a_all_objects;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
  begin
  open c1;
  loop
     fetch c1 bulk collect into rec1 limit 200;
     for i in 1..rec1.count loop
             null;
     end loop;
     exit when c1%notfound;
  end loop;
  end;
  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL>

Monday, 24 March 2014

ORACLE: sql - JOIN vs IN vs EXISTS and JOIN vs NOT IN vs NOT EXISTS the logical difference

ORACLE: sql - JOIN vs IN vs EXISTS  and JOIN vs NOT IN vs NOT EXISTS the logical difference

There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.

This is simply not true. To see why not, let's review what each statement does.



IN:

Returns true if a specified value matches any value in a subquery or a list.

Exists:

Returns true if a subquery contains any rows.

Join:

Joins 2 resultsets on the joining column.



If not read carefully it looks pretty same so far, doesn't it.

The difference comes when you take into account the "dreaded" THREE-VALUED LOGIC.

Let's review that also:


-- this is true
SELECT 1 from dual  WHERE 1 = 1;

-- this is false
SELECT 1 from dual  WHERE 1 = 0;

-- this is unknown - it is usually expected to be false, but that only shows
-- misunderstanding of nulls. It's not false it's only treated as false in the filter
SELECT 1 from dual  WHERE 1 = NULL;


-- this is also unknown - but logicaly it would seem it would be true
-- but unknown compared to unknown equals uknown and it is treated as false in the filter
SELECT 1 from dual  WHERE NULL = NULL;

The where will return a row only if the condition evaluates to true which UNKNOWN isn't.



So let's demonstrate this with some code. I've commented it heavily so it can be self explaining.

I've also shown a parser bug in the IN Query. Well... it's a bug if you ask me. It shouldn't behave like that.



------------------------------------------------------------------
-- Prepare tables and data
------------------------------------------------------------------
CREATE TABLE t1 (id number, title VARCHAR(20), somenumCol number);

CREATE TABLE t2 (id number, t1Id number, someData VARCHAR(20));


INSERT INTO t1
SELECT 1, 'title 1', 5 from dual UNION ALL
SELECT 2, 'title 2', 5 from dual UNION ALL
SELECT 3, 'title 3', 5 from dual UNION ALL
SELECT 4, 'title 4', 5 from dual UNION ALL
SELECT null, 'title 5', 5 from dual UNION ALL
SELECT null, 'title 6', 5 from dual;

INSERT INTO t2
SELECT 1, 1, 'data 1' from dual UNION ALL
SELECT 2, 1, 'data 2' from dual UNION ALL
SELECT 3, 2, 'data 3' from dual UNION ALL
SELECT 4, 3, 'data 4' from dual UNION ALL
SELECT 5, 3, 'data 5' from dual UNION ALL
SELECT 6, 3, 'data 6' from dual UNION ALL
SELECT 7, 4, 'data 7' from dual UNION ALL
SELECT 8, null, 'data 8' from dual UNION ALL
SELECT 9, 6, 'data 9' from dual UNION ALL
SELECT 10, 6, 'data 10' from dual UNION ALL
SELECT 11, 8, 'data 11' from dual ;

------------------------------------------------------------------
-- we want to get all data in t1 that has a child row in t2
------------------------------------------------------------------

-- join gives us more rows than we need, because it joins to every child row

SELECT    t1.*
FROM    t1
        JOIN t2 ON t1.id = t2.t1Id  ;


1    title 1    5
1    title 1    5
2    title 2    5
3    title 3    5
3    title 3    5
3    title 3    5
4    title 4    5  

 
-- distinct would solve that but it's not pretty nor efficient
SELECT    DISTINCT t1.*
FROM    t1
        JOIN t2 ON t1.id = t2.t1Id  ;


4    title 4    5
2    title 2    5
3    title 3    5
1    title 1    5


-- now this is a weird part where somenumCol is a column in t1
-- but the parser doesn't seem to mind that

 
SELECT    t1.*
FROM    t1
WHERE    t1.id IN (SELECT somenumCol FROM t2) ;


NO Rows Returned

-- here in and exists both get correct results
SELECT    t1.*
FROM    t1
WHERE    t1.id IN (SELECT t1id FROM t2);


1    title 1    5
2    title 2    5
3    title 3    5
4    title 4    5


SELECT    t1.*
FROM    t1
WHERE    exists (SELECT * FROM t2 WHERE t1.id = t2.t1id) ;

1    title 1    5
2    title 2    5
3    title 3    5
4    title 4    5
------------------------------------------------------------------
-- we want to get all data in t1 that doesn't have a child row in t2
------------------------------------------------------------------

-- join gives us the correct result

SELECT    t1.*
FROM    t1
        LEFT JOIN t2 ON t1.id = t2.t1Id
WHERE    t2.id IS NULL  ;


    title 6    5
    title 5    5


-- IN doesn't get correct results.
-- That's because of how IN treats NULLs and the Three-valued logic
-- NULL is treated as an unknown, so if there's a null in the t2.t1id
-- NOT IN will return either NOT TRUE or NOT UNKNOWN. And neither can be TRUE.
-- when there's a NULL in the t1id column of the t2 table the NOT IN query will always return an empty set. 


SELECT    t1.*
FROM    t1
WHERE    t1.id NOT IN (SELECT t1id FROM t2)  ;


NO Rows Returned 
 
-- NOT EXISTS gets correct results

SELECT    t1.*
FROM    t1
WHERE    NOT EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id);


    title 6    5
    title 5    5

We can see that it's best to use EXISTS because it always behaves as the user would think it does.





Tuesday, 18 February 2014

solution :- constraint (SYS.I_SOURCE1) violated


I am trying to drop an object from oracle using sqlplus then meanwhile i am cancelling the operation .
After i am drop the same object then  raise the error  that object does not exist.


And after i am creating the same object with some modification  then getting the error
constraint (SYS.I_SOURCE1)  violated  .

solution

find any object are not in user and existing in SYS.SOURCE$

select * from SYS.SOURCE$ where SOURCE  like '%OBJECTNAME%';

select OBJECT_NAME,OBJECT_ID from all_objects where owner='USER' and object_type='TYPE';

once you confirm which object are missing in user and existing in sys the you can delete the entries from sys.



    error : unique constraint (SYS.I_SOURCE1) violated

  delete from SYS.SOURCE$  where OBJ#=74615 and line=1;
    
     error : unique constraint (SYS.I_IDL_SB41) violated

    delete from  SYS.IDL_SB4$  where OBJ#=74615;
   
    error : unique constraint (SYS.I_IDL_UB11) violated
   
    delete from  SYS.IDL_UB1$  where OBJ#=74615;

    error : unique constraint (SYS.I_IDL_CHAR1) violated

    delete from  SYS.IDL_CHAR$ where OBJ#=74615;

    error : unique constraint (SYS.I_IDL_UB21) violated

   delete from   SYS.IDL_UB2$  where OBJ#=74615;





ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]

ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]

ORA-51108: unable to access diagnostic repository - retry command
ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_INFO]
ORA-06512: at "SYS.DBMS_IR", line 522

ORA-12012: error on auto execute of job "SYS"."DRA_REEVALUATE_OPEN_FAILURES"

ORA-51108: unable to access diagnostic repository - retry command




If you face this type of errors then you can proceed the following steps

SQL> exec dbms_hm.drop_schema;

PL/SQL procedure successfully completed.

SQL> exec dbms_hm.create_schema;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Tuesday, 21 January 2014

Purging trace and dump files with 11g ADRCI

Purging trace and dump files with 11g ADRCI


In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.

SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files

The ADRCI command show control will show us what the current purge settings are as shown below.

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/orcl11/orcl11:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           720                  8760                 2010-07-07 08:46:56.405618 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category.

We can change this by using the ADRCI command ‘set control’

In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)

adrci> set control (SHORTP_POLICY =360)

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/orcl11/orcl11:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

We can also manually purge information from the ADR using the ‘purge’ command from ADRCI (note this is defined in minutes and not hours!).

In this example we are purging all trace files older than 6 days. We see that the LAST_MANUPRG_TIME column is now populated.

adrci> purge -age 8640 -type TRACE 

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/orcl11/orcl11:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00        2010-08-27 09:50:07.399853 +08:00        1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00