Here is an example of database logon trigger for blocking specific users from accessing database through tools like Toad or SQL Developer tools, etc.
Read more of this content when you subscribe today.
Any database users with “ADMINISTER DATABASE TRIGGER” will bypass the database logon trigger.
SQL> grant ADMINISTER DATABASE TRIGGER to testuser; Grant succeeded. SQL> connect testuser Enter password: Connected.
cluvfy comp clocksync [-n <node_list>] [-noctss] [-verbose] USAGE: cluvfy comp clocksync [-n <node_list>] [-noctss] [-verbose] <node_list> is the comma-separated list of non-domain qualified node names on which the test should be conducted. If "all" is specified, then all the nodes in the cluster will be used for verification. -noctss does not check Oracle Cluster Synch service, but checks only the platforms native clock synch service(such as NTP) DESCRIPTION: Checks Oracle Cluster Time Synchronization Service(CTSS) on all nodes in the nodelist. If no '-n' option is provided, local node is used for this check. If the "-noctss" option is specified, then Oracle CTSS check is not performed, instead the platforms native Time Synchronization is checked.
$ cluvfy comp clocksync
Verifying Clock Synchronization ...
CTSS is in Observer state. Switching over to clock synchronization
checks using NTP
Verifying Network Time Protocol (NTP) ...
Verifying '/etc/chrony.conf' ...PASSED
Verifying Daemon 'chronyd' ...PASSED
Verifying NTP daemon or service using UDP port 123 ...PASSED
Verifying chrony daemon is synchronized with at least one external
time source ...PASSED
Verifying Network Time Protocol (NTP) ...PASSED
Verifying Clock Synchronization ...PASSED
Verification of Clock Synchronization across the cluster nodes was
successful.
CVU operation performed:Clock Synchronization across the cluster nodes
Date: 03/09/2018 3:31:04 PM
CVU home: /u01/app/12.2.0.1/grid/
User: grid
$ cluvfy comp clocksync -n all -verbose
Verifying Clock Synchronization ...
Node Name Status
--------- ------------------------
racnode1 passed
racnode2 passed
Node Name State
--------- ------------------------
racnode1 Observer
racnode2 Observer
CTSS is in Observer state.
Switching over to clock synchronization checks using NTP
Verifying Network Time Protocol (NTP) ...
Verifying '/etc/chrony.conf' ...
Node Name File exists?
--------- ------------------------
racnode1 yes
racnode2 yes
Verifying '/etc/chrony.conf' ...PASSED
Verifying Daemon 'chronyd' ...
Node Name Running?
--------- ------------------------
racnode1 yes
racnode2 yes
Verifying Daemon 'chronyd' ...PASSED
Verifying NTP daemon or service using UDP port 123 ...
Node Name Port Open?
--------- ------------------------
racnode1 yes
racnode2 yes
Verifying NTP daemon or service using UDP port 123 ...PASSED
Verifying chrony daemon is synchronized with at least one external
time source ...PASSED
Verifying Network Time Protocol (NTP) ...PASSED
Verifying Clock Synchronization ...PASSED
Verification of Clock Synchronization across the cluster nodes
was successful.
CVU operation performed:Clock Synchronization across the cluster nodes
Date: 03/09/2018 3:35:14 PM
CVU home: /u01/app/12.2.0.1/grid/
User: grid
In production environment, sessions from developers using tools like Toad, SQL Developer, Sqlplus, etc can be blocked by after schema logon trigger.
Read more of this content when you subscribe today.
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.
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.
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] ... .. .
1)Kill the “asmcmd daemon” processes.
2) Upgrade Trace File Analyzer (TFA) to the latest version.
Make DBA Life Easy 