How to Disable the triger on standby database and how to resolve the error- ORA-00604: error occurred at recursive SQL level 1

We have added an AFTER LOGON ON DATABASE,AFTER DDL ON DATABASE and AFTER SERVERERROR ON DATABASE trigger.
Standby databases are not writable and so this is not going to work.

As a result every users connecting to the standby database were complaining:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
ORA-02063: preceding 3 lines
00604. 00000 -"error occurred at recursive SQL level %s"
*Cause:An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Another issue raises the replication does not work i.e. Data sync is no working.

Realising my mistake I dropped/disable the trigger but this didn’t make the problem go away on the standby database.

A quick look in the alert log on the primary and there was a logon failure related to Data Guard:

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
PING[ARCl]: Heartbeat failed to connect to standby 'adgrpt'. Error is 16191.

I checked the SYS passwords on both nodes in case an underlying problem had suddenly occurred but all was ok.

Looking at the output we have a big clue here,
as it looks like the error the users were getting:

ORA-16000: database open for read-only access

Why are we still getting that when I’ve dropped the trigger on the primary database? It is immediately apparent that
 I have broken Data Guard and my drop is in a log file awaiting shipping to the standby. This is a catch 22:
I can’t apply the log because the trigger won’t let Data Guard authenticate and Data Guard can’t authenticate because of the presence of the trigger.

Two questions sprang to my mind:

    Can I disable the trigger?
    Why is the trigger affecting the SYS user?

I can’t workaround the trigger in any documented way because any attempt to do so on the physical standby is prevented by its read-only status, for example:

SQL> grant ADMINISTER DATABASE TRIGGER to appuser;
grant ADMINISTER DATABASE TRIGGER to appuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Here user SYS is connecting with a password not “as sysdba” and so we are hitting the logon trigger.

we can the following system parameter .

alter system set "_system_trig_enabled"=FALSE;

I can then restart the recovery with:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

And of course not forgetting to re-enable system triggers:

alter system set "_system_trig_enabled"=TRUE;

It should be pointed out that this method is nice because it doesn’t involve restarting the standby and therefore interrupting any running reports
but if you don’t like undocumented parameters or you have other system triggers you depend on you can instead:

    Shut down the standby.
    Restart the standby in MOUNT mode. This means that any triggers will not fire.
    Start the recovery.
    If the trigger has been dropped on the primary it should replicate to the secondary.
    Open the database.


Source URL: https://oraganism.wordpress.com/2015/08/26/non-working-logon-trigger-on-data-guard-standby-database/

Shared Pool Utilization Script

DECLARE
   object_mem       NUMBER;
   shared_sql       NUMBER;
   cursor_mem       NUMBER;
   mts_mem          NUMBER;
   used_pool_size   NUMBER;
   free_mem         NUMBER;
   pool_size        VARCHAR2 (512);                     -- Now from V$SGASTAT
BEGIN
   -- Stored objects (packages, views)
   SELECT SUM (sharable_mem)
     INTO object_mem
     FROM v$db_object_cache;

   -- Shared SQL -- need to have additional memory if dynamic SQL used
   SELECT SUM (sharable_mem)
     INTO shared_sql
     FROM v$sqlarea;

   -- User Cursor Usage -- run this during peak usage.
   --  assumes 250 bytes per open cursor, for each concurrent user.
   SELECT SUM (250 * users_opening)
     INTO cursor_mem
     FROM v$sqlarea;

   -- For a test system -- get usage for one user, multiply by # users
   -- select (250 * value) bytes_per_user
   -- from v$sesstat s, v$statname n
   -- where s.statistic# = n.statistic#
   -- and n.name = 'opened cursors current'
   -- and s.sid = 25;  -- where 25 is the sid of the process
   -- MTS memory needed to hold session information for shared server users
   -- This query computes a total for all currently logged on users (run
   --  multiply by # users.
   SELECT SUM (VALUE)
     INTO mts_mem
     FROM v$sesstat s, v$statname n
    WHERE s.statistic# = n.statistic# AND n.NAME = 'session uga memory max';

   -- Free (unused) memory in the SGA: gives an indication of how much memory
   -- is being wasted out of the total allocated.
   SELECT BYTES
     INTO free_mem
     FROM v$sgastat
    WHERE NAME = 'free memory' AND pool = 'shared pool';

   -- For non-MTS add up object, shared sql, cursors and 20% overhead.
   used_pool_size := ROUND (1.2 * (object_mem + shared_sql + cursor_mem));

   -- For MTS mts contribution needs to be included (comment out previous line)
   -- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
   SELECT SUM (BYTES)
     INTO pool_size
     FROM v$sgastat
    WHERE pool = 'shared pool';

   -- Display results
   DBMS_OUTPUT.put_line ('Shared Pool Memory Utilization Report');
   DBMS_OUTPUT.put_line ('Obj mem:  ' || TO_CHAR (object_mem) || ' bytes');
   DBMS_OUTPUT.put_line ('Shared sql:  ' || TO_CHAR (shared_sql) || ' bytes');
   DBMS_OUTPUT.put_line ('Cursors:  ' || TO_CHAR (cursor_mem) || ' bytes');
   -- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
   DBMS_OUTPUT.put_line (   'Free memory: '
                         || TO_CHAR (free_mem)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (free_mem / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool utilization (total):  '
                         || TO_CHAR (used_pool_size)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (used_pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool allocation (actual):  '
                         || pool_size
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Percentage Utilized:  '
                         || TO_CHAR (ROUND (used_pool_size / pool_size * 100))
                        );
END;