Logon trigger to change CURSOR_SHARING=FORCE

Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.

Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.

CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID      VARCHAR2(30)                 NOT NULL,
  MACHINE     VARCHAR2(200),
  PROGRAM    VARCHAR2(200),
  CREATE_DT  DATE                            DEFAULT sysdate
)
TABLESPACE USER01;

CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
  v_db_user      VARCHAR2(30);
  v_machine     VARCHAR2(200);
  v_program     VARCHAR2(200);
  v_os_user      VARCHAR2(30);
  v_count          INTEGER;
BEGIN
  v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
  v_machine  := SYS_CONTEXT('USERENV', 'HOST');
  v_program   := SYS_CONTEXT('USERENV', 'MODULE');
  v_os_user := SYS_CONTEXT('USERENV','OS_USER');

  SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
  WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
  AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
  ;  -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases

  IF v_count >=1 THEN
     EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
     INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
               VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
  END IF;   -- LOGIN_AUDIT is another table.
 END;
/

Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;

Re-install Grid Control Agent (10.2.0.4)

Here are steps to re-install Grid Control agent on db_server:

0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.

2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.

3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.

4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix

5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh

#!/bin/sh
export ORACLE_BASE=/u01/app/oracle

export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10

./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log

6. Run the script to fire the installation process
./agentinstall.sh

If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.

Troubleshootings:

1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).