DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-06512: at "SYS.STANDARD"

  1. #1
    Join Date
    Dec 2012
    Posts
    1

    ORA-06512: at "SYS.STANDARD"

    Hello,
    I have got into serious problem with a after logon trigger. I created an after logon trigger to restrict developer access to priamary role database and wanted to force them to login to active data guard to perform their select queries. But after I installed the after logon trigger, the primary continued work fine but the active data guard standby got into serious trouble.
    There were a lot of trace files all of them with :
    Error in executing triggers on connect internal
    *** 2012-12-07 23:34:40.711
    dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
    ----- Error Stack Dump -----
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01013: user requested cancel of current operation
    ORA-06512: at line 7
    and alert log generated
    ORA-16037: user requested cancel of managed recovery operation
    Now even as sysdba I could not login to database. There were hundreds of sqlplus local and remote connections hung up. Finally I have to connect as prelim connection and shutdown abort.. Then I was able to recover the standby.
    The trigger code is: (not really compact code though).

    create or replace trigger restrictProdAccess
    after logon on database
    DECLARE
    W_NAME VARCHAR2 (20);
    W_DBROLE VARCHAR2 (20);
    W_RESTRICTED VARCHAR2(20);
    BEGIN
    --SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO W_IP FROM DUAL;
    SELECT DATABASE_ROLE into W_DBROLE from V$DATABASE;
    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO W_NAME FROM DUAL;
    IF W_DBROLE = 'PRIMARY' THEN
    IF (W_NAME = 'Dev1' OR W_NAME = 'tester2') THEN
    RAISE_APPLICATION_ERROR (-20001, 'Sorry, you are not allowed here!');
    END IF;
    END IF;

    END;
    /

    What did I do wrong?

    Thanks,
    Jagan

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You should create a new post, rather than adding to a decade old post. You might try adding logging and removing the raise error statement.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Thumbs down

    Quote Originally Posted by jaganreddy98 View Post
    Hello,
    I have got into serious problem with a after logon trigger.
    . . . Etc . . .
    What did I do wrong?
    You did not use the standard and best practice authentication and security functionality provided by Oracle.
    Do your own thing -- die by your own hand.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width