Month: August 2019

Schema Logon Trigger to Block Logon Sessions from Toad, SQL Developer, Sqlplus….

In production environment, sessions from developers using tools like Toad, SQL Developer, Sqlplus, etc can be blocked by after schema logon trigger.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-4098 from After Logon Trigger

SQL> connect testuser
Enter password:
ERROR:
ORA-04098: trigger 'SYS.CHECK_USERS_TRIGGER' is invalid and failed
re-validation


Warning: You are no longer connected to ORACLE.

SOLUTION

There is an after logon trigger which is invalid.  To fix the issue by either way of the following :

1)Fix the after logon trigger to make it valid.

2)Disable the invalid after logon trigger.

SQL>ALTER TRIGGER SYS.CHECK_USERS_TRIGGER DISABLE ;

3)Drop the invalid after logon trigger.

SQL>drop trigger SYS.CHECK_USERS_TRIGGER;

We must be careful when using AFTER LOGON triggers, because if after logon trigger becomes invalid or failed to execute due to some other errors, it prevents the users (even DBA users) from logging in. The only way to resolve this error would be to connect with SYSDBA privileges.

Advertisement

many “asmcmd daemon” high cpu

There are many hung “asmcmd daemon” processes with high CPU usage.

$top 

PID    USER  PR  NI   VIRT RES  SHR   %CPU %MEM TIME+ COMMAND
71337 grid  20  0 5717400 5.2g  1044 43.0  0.7 1:07.71 asmcmd daemon
137157grid  20  0 4751716 4.4g 14880 41.0  0.6 5:43.21 asmcmd daemon
76530 grid  20  0 5671444 5.2g  1044 40.4  0.7 2:46.47 asmcmd daemon
96115 grid  20  0 4750220 4.3g  1044 40.4  0.6 5:00.40 asmcmd daemon
81230 grid  20  0 5704468 5.2g  1036 39.7  0.7 3:58.48 asmcmd daemon
...
..
.
$ ps -eaf | grep -i asmcmd

grid 71337 1 40 Aug06 ? 13:01:13 asmcmd daemon
grid 76530 1 40 Aug06 ? 13:02:52 asmcmd daemon
grid 81230 1 40 Aug06 ? 13:04:04 asmcmd daemon
grid 96115 1 40 Aug06 ? 09:35:06 asmcmd daemon
grid 115047 81230 0 09:33 ? 00:00:00 sh -c /u01/app/12.2.0.1/grid/bin
/clsecho -t -o /u01/app/12.2.0.1/grid/log/diag/asmcmd/user_grid/
racnode1/alert/alert.log "ASMCMD Background (PID = 81230)
 2> /tmp/clsecho_stderr_file.txt
grid 115058 76530 0 09:33 ? 00:00:00 [asmcmd daemon]
grid 115060 126727 0 09:33 ? 00:00:00 [asmcmd daemon]
...
..
.

WORKAROUND

1)Kill the “asmcmd daemon” processes.

2) Upgrade Trace File Analyzer (TFA) to the latest version.

CRS-2674 CRS-2632 ORA-01013 CRS-5017 PRCR-1079 PRCD-1084 Srvctl Start Service

One or two services failed to start up, while the rest services are all good to be started up or shutdown.

$ srvctl start service -d TESTDB -s reports
PRCD-1084 : Failed to start service REPORTS
PRCR-1079 : Failed to start resource ora.testdb.reports.svc
CRS-5017: The resource action "ora.testdb.reports.svc start" 
          encountered the following error:
ORA-01013: user requested cancel of current operation
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/
                     racnode1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.testdb.reports.svc' on 'racnode1' failed
CRS-2632: There are no more servers to try to place resource 
 'ora.testdb.reports.svc' on that would satisfy its placement policy

It seems the information about this database or service is inconsistent in OCR.  We can try the following steps one after another until the it is successful.

1)Remove database from OCR, and add it back again.

$srvctl remove database....

$srvctl add database ....

$ srvctl add instance ....

$ srvctl start service -d TESTDB -s reports

2) if not working in previous step, then restart all clusterware.

#crsctl stop crs

#ctsct start crs

$srvctl start service -d TESTDB -s reports

3) For some reason database outage is unavailable, service can be started up manually.

Start up service on instance 1. Users or applications can connect to database through this service, but srvctl still shows service not running

SQL> exec DBMS_SERVICE.START_SERVICE('REPORTS','TESTDB1');

$ srvctl status service -d testdb -s reports 

Service REPORTS is not running.

Indexing NULL Value in a Table Column

As we know, a normal index does not include NULL value,  so SQL query won’t use this normal index for a SQL query with condition ‘IS NULL’.

Here is a tip for how to create an index on column with NULL value.

SQL> create index IDX_BATCH_ID_NULL on test ( BATCH_ID, 1);

Index created.

Actually the index is created by including two columns of BATCH_ID and constant number 1.

SQL> select count(*) from test where BATCH_ID is null;

COUNT(*)
----------
292

SQL> select count(*) from test where BATCH_ID is not null;

COUNT(*)
----------
172

SQL> create index idx_BATCH_ID on test(BATCH_ID);

Index created.

SQL> SET AUTOTRACE ON EXPLAIN

SQL> select MACHINE_ID from test 
     where BATCH_ID is not null and rownum<=2;

MACHINE_ID
----------------------
MACHINE_1
MACHINE_2

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

--------------------------------------------------------------------
| Id | Operation          | Name|Rows| Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |   2|   130 |    2   (0)| 00:00:01 |
|* 1 |  COUNT STOPKEY     |     |    |       |           |          |
|* 2 |   TABLE ACCESS FULL|TEST | 172| 11180 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------


SQL> select MACHINE_ID from test 
     where BATCH_ID is null and rownum<=2;

MACHINE_ID
---------------------------------------------
MACHINE_3
MACHINE_4

Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

-----------------------------------------------------------------
|Id| Operation        |Name|Rows|Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------
|0 | SELECT STATEMENT |    | 2  |  650 | 2 (0)      | 00:00:01 |
|*1| COUNT STOPKEY    |    |    |      |            |          |
|*2| TABLE ACCESS FULL|TEST|292 |18980 | 2 (0)      | 00:00:01 |
-------------------------------------------------------------------

SQL> create index idx_BATCH_ID_null on test ( BATCH_ID, 1);

Index created.

SQL> select MACHINE_ID from test 
    where BATCH_ID is null and rownum<=2;

MACHINE_ID
------------------------------------------------------
MACHINE_3
MACHINE_4


Execution Plan
----------------------------------------------------------
Plan hash value: 1426346817

--------------------------------------------------------------------
|Id| Operation                   |Name|Rows|Bytes|Cost(%CPU)| Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT             |    |  2 | 130 |  3   (0)|00:00:01|
|*1| COUNT STOPKEY               |    |    |     |         |        |
| 2|  TABLE ACCESS BY INDEX ROWID|TEST|292 |18980|  3   (0)|00:00:01|
|*3|   INDEX RANGE SCAN          | IDX_BATCH_ID_NULL 
                                      | 23 |     |  2   (0)|00:00:01|
----------------------------------------------------------------------