Can't Drop a User From DB

Symptom：

Issue is that we can't drop a user from sqlplus
SQL>  select object_name , OBJECT_TYPE,CREATED , STATUS from dba_objects
where owner='SQL_WIFGWIHDMSBUHBARKGXAYFYMT';

no rows selected

SQL> DROP USER SQL_WIFGWIHDMSBUHBARKGXAYFYMT CASCADE;
DROP USER SQL_WIFGWIHDMSBUHBARKGXAYFYMT CASCADE

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Oracle Text error:
DRG-11338: policy SQL_WIFGWIHDMSBUHBARKGXAYFYMT"."JSON_FTS_IDX does not exist
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.CTX_ADM", line 279
ORA-06512: at line 1

the user doesn't has any object show in dba_objects , and drop user failed
with "DRG-11338: policy SQL_WIFGWIHDMSBUHBARKGXAYFYMT"."JSON_FTS_IDX does not exist"

Diagnose:

1. Get your sqlplus ospid like xxxx
2. ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
3. DROP USER SQL_WIFGWIHDMSBUHBARKGXAYFYMT CASCADE;
4. check  trace directory *xxxx.trc
   the trc file includes the sql running including recursive sql.
5.ALTER SESSION SET EVENTS '10046 trace name context off';

Solution:

  Find the 'JSON_FTS_IDX' exists on dr$index while not exist on dba_objects.  Drop 'JSON_FTS_IDX' manually to clear the way

Simple Code of iRule to Route traffic to Specific Node and Block IP

Symptom:

  We have 2 or more miditers running behind F5 bigip, we need to login specific node via bigip, instead of chosen by bigip itself.  It is useful to debug issues on specific nodes

Solution:

 Use url like
https://testapp.test.com/myapps?mynode=112.68.136.22
Below code to grab the IP address and use this IP to specific node in the bigip pool.

when HTTP_REQUEST {
set nodeselect [findstr [string tolower [HTTP::uri] ] "mynode=" 7 ";" ]
if { $nodeselect != "" } {
if { [HTTP::cookie exists "JSESSIONID"] } {
persist delete uie [string tolower [HTTP::cookie "JSESSIONID"]] 
} else {
set jsess [findstr [string tolower [HTTP::uri]] "jsessionid" 11 ";"]
if { $jsess != "" } {
persist delete uie $jsess 
}
}
pool "testapp.test.com_pool" member $nodeselect 7777
}
}


Add IP to be blocked in testapp_addresses_to_block data group, then use below irule code
when CLIENT_ACCEPTED {
     if { [class match [IP::remote_addr] equals testapp_addresses_to_block] } {
       discard
       event disable all
     }
}

Cookie Sharing Among Chrome Tabs

Symptom:

  When we have 2 or more Midtiers running behind F5 bigip,  we are easy to be confused with which node my Chrome is on.  Especially we have multiple tabs open on the same domain when we debug issues.
Say I open 1 tab to point to node1 and check around , then I open another tab to point to node2 to check around , the first tab with node1 will go to node2 when I click around to next page

Reason:

 The cookies with same domain among Chrome tabs are shared. When you open second tab with node2 on the same domain,  the new cookie will be shared or overwrite the cookie of your first tab .
Thus your first tab goes to node2 when you click around

Solution:

  It can be quite annoying when you debug issues.  The workaround is to open a new incognito window to separate the cookies .

Permission denied Issue in HDFS of Hadoop

Symptom:

   When you try to create a new directory in HDFS via
hadoop fs -mkdir  /user/test
Error is:
mkdir: Permission denied: user=oracle, access=WRITE, inode="/user":hdfs:supergroup:drwxr-xr-x

Solutions:

  It's due to the /user belongs  hdfs user and with permission 755. It does not allow other users to create directories on it

 Use sudo to do it:
sudo -u hdfs  hadoop fs -mkdir  /user/test

Or su - hdfs to do it

How Putty Into Public Cloud VMs in Your Corporate Network

Symptom:

    In your corporate network, there is no access to outside public IP address. We need to putty into public cloud VMs to  check  services . Disconnect intranet would be painful for that.

Solution:

    In your putty session setting, connection --> proxy , set your corporate proxy details in it. Then your putty would be able to access it.
    Pay attention to putty timeout alerts as proxy may cut the idle sessions in a while depends how corporate proxy is set.

Session Blockers In DB After OS Panic or DB Aborted

Symptom:

   Due to OS panic or we did shutdown abort on oracle DB. DB was started well.
   However in a while , there were quite many blocking sessions ( enq: TX - row lock contention)  which caused Application performance issues

Diagnosis:

From blocking sid  and run sql checking, we found the blockers were idle sessions and had  such wait event: "SQL * NET message from client ".
   About how idle sessions can be blockers , please refer stackexchange link
   So it turns out that the unreleased locks of last sessions before OS panic and shutdown abort will remain in the DB. After DB restarts, the locks are still in place.

Solutions:

     We need to clear the blockers manually or bounce the MT to refresh all the connections.