Ora-12012: Error On Auto Execute Of Job "Oracle_ocm"."Mgmt_config_job_2_1

OCM job was failing with below error at alert log:

 ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"

ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1

This messages happen because of the OCM collection database job is unable to access the directory location where the OCM data is written .

If you do not use OCM delete the OCM configuration from the database:

Log in to your database as user SYS and drop-cascade user ORACLE_OCM:

SQL> DROP USER ORACLE_OCM CASCADE;

But if OCM is in use to upload collection to My Oracle Support then it needs reinstrumenting the database.

1. export ORACLE_HOME=

    export ORACLE_SID=

2.  Remove the OCM configuration in the database:

     cd $ORACLE_HOME/ccr/bin

     ./configCCR -r

3. Configure the OCM configuration in the database:

    ./configCCR -a

4.  Instrument the Database:

     cd /ccr/admin/scripts

     ./installCCRSQL.sh collectconfig -s -r SYS

5.  Run a manual collection:

    cd $ORACLE_HOME/ccr/bin

    ./emCCR collect

TNS-01189: The listener could not authenticate the user

I was working on restoring database to a new VM lab environment. So the TNS, Listener , PFILe everything was copied over as usual as I'll be doing RMAN restore. So the restoration went fine, but interestingly listener didn't like my user to run it. Error was a bit confusing to me. Here is what I got.

[oracle@lab]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-JUN-2012 09:55:32

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=production.com)(PORT=1521)))

TNS-01189: The listener could not authenticate the user

I was so stupid that I was only looking into the error. Then I suddenly looked into the HOST name. It was picking up my source database as hostname; as I just copied the listener.ora file but didn't modified!

Solution is simple. Just change the listener HOST entry according to the server you are restoring the DB.

vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lab)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

Oracle Number to Word conversion

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||

       decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )

from dual

/

First one has a limitation of number of digits. Below one is good in terms of number of digits.

Set Serveroutput On 1000000

Declare

  V_Input NUMBER := &TESTED;

  Function Numbertowords(P_Number In Out Number) Return Varchar2 Is

    V_Words Varchar2(32767) := ' ';

    V_Temp Number;   

    Type Unitsmap Is Table Of Varchar2(250) Index By Binary_Integer;

    Type Tensmap Is Table Of Varchar2(250) Index By Binary_Integer;   

    V_Unitsmap Unitsmap ;

    V_Tensmap Tensmap   ;

  Begin

    V_Unitsmap(0) := 'Zero';

    V_Unitsmap(1) := 'One';

    V_Unitsmap(2) := 'Two';

    V_Unitsmap(3) := 'Three';

    V_Unitsmap(4) := 'Four';

    V_Unitsmap(5) := 'Five';

    V_Unitsmap(6) := 'Six';

    V_Unitsmap(7) := 'Seven';

    V_Unitsmap(8) := 'Eight';

    V_Unitsmap(9) := 'Nine';

    V_Unitsmap(10) := 'Ten';

    V_Unitsmap(11) := 'Eleven';

    V_Unitsmap(12) := 'Twelve';

    V_Unitsmap(13) := 'Thirteen';

    V_Unitsmap(14) := 'Fourteen';

    V_Unitsmap(15) := 'Fifteen';

    V_Unitsmap(16) := 'Sixteen';

    V_Unitsmap(17) := 'Seventeen';

    V_Unitsmap(18) := 'Eighteen';

    V_Unitsmap(19) := 'Nineteen';

    V_Tensmap(2)  := 'Twenty';

    V_Tensmap(3)  := 'Thirty';

    V_Tensmap(4)  := 'Forty';

    V_Tensmap(5)  := 'Fifty';

    V_Tensmap(6)  := 'Sixty';

    V_Tensmap(7)  := 'Seventy';

    V_Tensmap(8)  := 'Eighty';

    V_Tensmap(9)  := 'Ninety';

    If (P_Number = 0) Then

      Return 'Zero';

    End If;

    If (P_Number < 0) Then

      V_Temp := Abs(P_Number);     

      Return 'Minus ' || Numbertowords(V_Temp);

    End If;

    V_Temp := TRUNC(P_Number / 1000000);

    If ( V_Temp > 0) Then

      V_Words := V_WORDS || Numbertowords(V_Temp) || ' Million';

      P_Number := Mod(P_Number,1000000);

    End If;

    V_Temp := TRUNC(P_Number / 1000);

    If ( V_Temp > 0)Then

      V_Words := V_Words || Numbertowords(V_Temp) || ' Thousand';

      P_Number := Mod(P_Number,1000);

    End If;

    V_Temp := Trunc(P_Number / 100);

    If ( V_Temp > 0) Then

      V_Words := V_Words ||  Numbertowords(V_Temp) || ' Hundred ';

      P_Number := Mod(P_Number,100);

    End If;

    V_Temp := P_Number;

    If (V_Temp > 0) Then

      If (V_Words != ' ') Then

        V_Words := V_Words || 'And ';

       End If;

      If (V_Temp < 20) Then

        V_Words := V_Words || V_Unitsmap(V_Temp);

        Return V_Words;

      Else

       V_Temp := TRUNC(P_Number/ 10);

        V_Words := V_Words || V_Tensmap(V_Temp);

        If ((Mod(P_Number ,10)) > 0) Then

          V_Words :=V_Words|| '-' ||V_Unitsmap(Mod(P_Number,10));

        End If;

      End If;       

    End If;

    Return V_Words;

  End;

Begin   

  Dbms_Output.Put_Line(&TESTED||' ='||Numbertowords(V_Input));

End;

Calender with single sql statement

Found this cool script from a oracle blog.

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",

"Wed", "Thu", "Fri", "Sat"

FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,

MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",

MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",

MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",

MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",

MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",

MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"

FROM ( select dt, (case when dt >=

to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week

from(

SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk

FROM all_objects

WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))

)

GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)

ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

SQLPLUS COPY Command to copy tables across/within DB, useful for LONG data types.

Tables with LONG data type cannot be moved or copied to/from one tablespace/schema/database to another tablespace/schema/database using CTAS or even sql INSERT statement. It would fail with below error:

ORA-00997: illegal use of LONG datatype

So whats the solution. Either you could do export/import or sqlplus COPY command.It works like a charm. Here is a demonstration how I failed and how sqlplus COPY helped!!

Creating a table with LONG datatype.

SQL> create table copy_example (col1 varchar2(10), col2 long);

Table created.

SQL> insert into copy_example values ('11','10000000000000000000000000000000000000000000000000');

1 row created.

SQL> commit;

Commit complete.

Trying to CTAS:

SQL> create table copy_example_2 as select * from copy_example;

create table copy_example_2 as select * from copy_example

                                      *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Then Tried doing INSERT to pull LONG datatype to a different table:

SQL> create table copy_example_2 (col1 varchar2(10), col2 long);

Table created.

SQL> insert into copy_example_2 select * from copy_example;

insert into copy_example_2 select * from copy_example

                                  *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Failing too!!!

Lets see how SQLPLUS COPY command works then. You can copy tables between two databases. I’m using here same database names, you can use it for different databases as long as you have TNS entry on the source box.
Copy command support only CHAR, DATE, LONG, NUMBER andVARCHAR2 data types. It does not support any new data types.

SQL> drop table copy_example_2;

Table dropped.

SQL> copy from scott/tiger@LAB  to scott/tiger@LAB create copy_example_2 using select * from copy_example;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table COPY_EXAMPLE_2 created.

   1 rows selected from scott@lab

   1 rows inserted into COPY_EXAMPLE_2.

   1 rows committed into COPY_EXAMPLE_2 at lab@lab

SQL> select * from system.copy_example_2;

COL1       COL2

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

11         10000000000000000000000000000000000000000000000000

Everything copied over!!! J

Create Oracle Directories for as-is database creation.

On several situations we need to mimick database to lab environments. And to create directories as-is of the source database; found this cool query that can generate all related directory structure creation linux command for us.

select distinct dir from (

select 'mkdir -p '||regexp_replace(fname,'(^.*)/[^/]*$','\1') dir

from (

select name fname from v$controlfile

union all

select name from v$datafile

union all

select name from v$tempfile

union all

select member from v$logfile

)

union all

select 'mkdir -p '||value dir from v$parameter where isdefault='FALSE' and type=2 and not value like '%,%' and value like '/%'

)

RESULT_CACHE parameter 11g to improve query performance

Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. Set up the following schema objects to see how the SQL query cache works.

    CREATE TABLE test_tab (

      id  NUMBER

    );

    INSERT INTO test_tab VALUES (1);

    INSERT INTO test_tab VALUES (2);

    INSERT INTO test_tab VALUES (3);

    INSERT INTO test_tab VALUES (4);

    INSERT INTO test_tab VALUES (5);

    CREATE OR REPLACE FUNCTION slow_function(p_id  IN  test_tab.id%TYPE)

      RETURN test_tab.id%TYPE DETERMINISTIC AS

    BEGIN

      DBMS_LOCK.sleep(1);

      RETURN p_id;

    END;

    /

    SET TIMING ON

The function contains a one second sleep so we can easily detect if it has been executed by checking the elapsed time of the query.

Next, we query the test table using the slow function and check out the elapsed time. Each run takes approximately five seconds, one second sleep for each row queried.

    SELECT slow_function(id) FROM test_tab;

    SLOW_FUNCTION(ID)

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

                    1

                    2

                    3

                    4

                    5

    5 rows selected.

    Elapsed: 00:00:05.15 

Adding the RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.

    SELECT /*+ result_cache */ slow_function(id) FROM test_tab;

    SLOW_FUNCTION(ID)

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

                    1

                    2

                    3

                    4

                    5

    5 rows selected.

    Elapsed: 00:00:05.20

    SELECT /*+ result_cache */ slow_function(id) FROM test_tab;

    SLOW_FUNCTION(ID)

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

                    1

                    2

                    3

                    4

                    5

    5 rows selected.

    Elapsed: 00:00:00.15

The default action of the result cache is controlled by the RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a query to access the result cache.

    SHOW PARAMETER RESULT_CACHE_MODE

    NAME                                 TYPE        VALUE

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

    result_cache_mode                    string      MANUAL   

If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.

    ALTER SESSION SET RESULT_CACHE_MODE=FORCE;

    SELECT slow_function(id) FROM test_tab;

    SLOW_FUNCTION(ID)

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

                    1

                    2

                    3

                    4

                    5

    5 rows selected.

    Elapsed: 00:00:00.14

    SELECT /*+ no_result_cache */ slow_function(id) FROM test_tab;

    SLOW_FUNCTION(ID)

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

                    1

                    2

                    3

                    4

                    5

    5 rows selected.

    Elapsed: 00:00:05.14