ORA-06512: at "SYS.STANDARD"
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
W_NAME VARCHAR2 (20);
W_DBROLE VARCHAR2 (20);
--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!');
What did I do wrong?
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.
this space intentionally left blank
You did not use the standard and best practice authentication and security functionality provided by Oracle.
Originally Posted by jaganreddy98
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
Click Here to Expand Forum to Full Width