Database Logon Trigger to Block Sessions from Some Users using Toad,SQL Developer….

Here is an example of  database logon trigger for blocking specific users from accessing database through tools like  Toad or SQL Developer tools, etc.

Subscribe to get access

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.

ORA-02085: database link DBLINK_TEST connects to TESTDB

If parameter global_names =TRUE, the database link name has to match the remote database name.

Try to query a remote table through a database link:

SQL> select count(*) from testuser.test@dblink_test;
select count(*) from testuser.test@dblink_test;
*
ERROR at line 1:
ORA-02085: database link UDBLINK_TEST connects to TESTDB

REASON

Check database parameter global_names =TRUE. If parameter global_names =TRUE, the database link name has to match the remote database name( TESTDB).

WORKAROUND

Turn off  global_names in session level, then it should be ok.

SQL>alter session set global_names=false;
Session altered.

SQL>select count(*) from testuser.test@dblink_test;

 COUNT(*)
----------
       100

How to Download Oracle Patches From Oracle Support By Using WGET or CURL

1) Set up global variables :

export MOS_USER=testuser@domain.com
export MOS_PASSWORD="Password"

export PROXYUSER="testuser"
export PROXYPASSWD="Password123"
export USERAGENT="Mozilla/5.0"
export use_proxy=on
export http_proxy="http://proxy.domain.com:80/"
export https_proxy="https://proxy.domain.com:80/"
export COOK=$HOME/$.cookie

2) The following command to authenticate uses HTTP/HTTPS:

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--http-user=${MOS_USER} --http-password=${MOS_PASSWORD} 
--save-cookies=$COOK --keep-session-cookies --no-check-certificate 
"https://updates.oracle.com/Orion/Services/download" 
-no-verbose
2018-02-26 14:35:17 URL:https://updates.oracle.com/Orion/Services/download 
[118] -> "download.1" [1]

3) Get all supported platforms and language codes:

a) Output the query result into a temp file:

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--no-check-certificate --load-cookies=$COOK 
"https://updates.oracle.com/Orion/SavedSearches/switch_to_simple" 
-O $HOME/output.tmp -q

$ ls -l $HOME/output.tmp
-rw-r----- 1 testuser users 4528477 Feb 26 14:41 /home/testuser/output.tmp

b) Extract the Platform and Language Code, here we are only interested in platform “226P<—>Linux x86-64″ with default English language:

$ grep -A200 "<select name=plat_lang"  /home/testuser/output.tmp |
 grep "^<option"|awk -F "[\">]" '{print $2 "<--->" $4}' |
 |grep -v "<--->selected"

537P<--->Acme Packet 1100
529P<--->Acme Packet 3820
...
..
.
541P<--->Linux ARM 64-bit
214P<--->Linux Itanium
525P<--->Linux SPARC
46P<--->Linux x86
226P<--->Linux x86-64
912P<--->Microsoft Windows (32-bit)
208P<--->Microsoft Windows Itanium (64-bit)
539P<--->Microsoft Windows Phone
233P<--->Microsoft Windows x64 (64-bit)
...
..
.
117L<--->Traditional Chinese (ZHT)
116L<--->Turkish (TR)
37L<--->UK English (GB)
39L<--->Ukrainian (UK)
43L<--->Vietnamese (VN)
999L<--->Worldwide Spanish (ESW)

4) Get URLs of patch 6880880 for Linux X86-64 platform.

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--no-check-certificate --load-cookies=$COOK 
"https://updates.oracle.com/Orion/SimpleSearch/process_form?search_type
=patch&patch_number=6880880&plat_lang=226P" -O $HOME/output1.tmp -q

$ ls -ltr $HOME/output1.tmp
-rw-r----- 1 tetsuser users 4544310 Feb 26 15:44 /home/testuser/output1.tmp

$ grep "Download/process_form" output1.tmp | sed 's/ //g' | sed "s/.*href=\"//g" | sed "s/\".*//g"
https://updates.oracle.com/Orion/Download/process_form/p6880880_139000_Generic.zip?aru=21939900&file_id=98828928&patch_file=p6880880_139000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_112000_Linux-x86-64.zip?aru=21895918&file_id=64217272&patch_file=p6880880_112000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_121010_Linux-x86-64.zip?aru=21886824&file_id=65461237&patch_file=p6880880_121010_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_132000_Generic.zip?aru=17856597&file_id=72275045&patch_file=p6880880_132000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_111000_Linux-x86-64.zip?aru=19416466&file_id=26541776&patch_file=p6880880_111000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_131000_Generic.zip?aru=16531511&file_id=62900088&patch_file=p6880880_131000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_101000_Linux-x86-64.zip?aru=13915384&file_id=42098007&patch_file=p6880880_101000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_102000_Linux-x86-64.zip?aru=13116068&file_id=34545782&patch_file=p6880880_102000_Linux-x86-64.zip&

5) Download the patch by using URL from step 4):

curl:

$ curl -b $COOK -c $COOK --insecure --output p6880880_122010_Linux-x86-64.zip 
-L "https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&"

  % Total % Received % Xferd Average Speed Time Time Time Current
 Dload Upload Total Spent Left Speed
100 90.8M 100 90.8M 0 0 5008k 0 0:00:18 0:00:18 --:--:-- 20.7M

wget:

$ wget --load-cookies=$COOK --save-cookies=$COOK --keep-session-cookies 
--no-check-certificate -O p6880880_122010_Linux-x86-64.zip 
"https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&"
...
..
.
Proxy request sent, awaiting response... 200 OK
Length: 95262503 (91M) [application/zip]
Saving to: `p6880880_122010_Linux-x86-64.zip'

100%[=================================================================================================>] 95,262,503 21.9M/s in 16s

2018-02-26 17:57:20 (5.65 MB/s) - `p6880880_122010_Linux-x86-64.zip' saved [95262503/95262503]

6) Validate the download zip file:

OPatch patch of version 12.2.0.1.12 for Oracle software releases 12.1.0.x 
(installer) and 12.2.0.x (JAN 2018) (Patch)

p6880880_122010_Linux-x86-64.zip90.8 MB(95262503 bytes)
 
MD508D733176A76D99547CDC5ABF7DEF192
 
SHA-14B4EE360C1AF6515CC18F9C36B3AD06EF64B5E0D
 
SHA-2565BD98A31C8E134DFF1DE833FFA0834D62C606036A1626AF6ED529854D215707F

a) “unzip -t”

$ unzip -t p6880880_122010_Linux-x86-64.zip
Archive: p6880880_122010_Linux-x86-64.zip
 testing: OPatch/ OK
 testing: OPatch/operr.bat OK
 testing: OPatch/opatch_env.sh OK
...
..
.
No errors detected in compressed data of p6880880_122010_Linux-x86-64.zip.

b) MD5 “md5sum”

$ md5sum p6880880_122010_Linux-x86-64.zip
08d733176a76d99547cdc5abf7def192 p6880880_122010_Linux-x86-64.zip

c) SHA-1 “sha1sum”

$ sha1sum p6880880_122010_Linux-x86-64.zip
4b4ee360c1af6515cc18f9c36b3ad06ef64b5e0d p6880880_122010_Linux-x86-64.zip

d)SHA-256 “sha256sum”

$ sha256sum p6880880_122010_Linux-x86-64.zip
5bd98a31c8e134dff1de833ffa0834d62c606036a1626af6ed529854d215707f p6880880_122010_Linux-x86-64.zip

How to Tell if Oracle Client is 32-bit or 64-bit installed on Windows

Some vendor applications specifically require 32bit Oracle client.

It is easy to tell 32 bit or 64 bit oracle on Unix/Linux by :

$ file sqlplus
sqlplus: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped

In Windows, there are a couple of ways to check Oracle client is 32 bit or 64 bit by :

TNSPING

—  32bit Oracle client installed

D:\>tnsping
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 29-NOV-2016 09:24:50

— 64 bit Oracle client installed

c:\>tnsping
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 29-NOV-2016 09:27:19
Copyright (c) 1997, 2014, Oracle. All rights reserved.

TASK MANAGER

Run command “sqlplus” in command prompt window, then open “task manager”.

—  32bit Oracle client installed

oracleclient32bit

— 64 bit Oracle client installed

oracleclient64bit

%ORACLE_HOME%\inventory\ContentsXML\oraclehomeproperties.xml file

—  32bit Oracle client installed

...
..
  
 <ARU_ID>912</ARU_ID>
 <ARU_ID_DESCRIPTION>Microsoft Windows (32-bit)
..
.
 <PROPERTY NAME="ARCHITECTURE" VAL="32"/>
..
.

— 64 bit Oracle client installed

...
..
 <ARU_ID>233</ARU_ID>
 <ARU_ID_DESCRIPTION>Microsoft Windows (64-bit AMD)
..
.
 <PROPERTY NAME="ARCHITECTURE" VAL="64"/>
..
.

PERL under %ORACLE_HOME%

Perl  installed according to the bit version of the ORACLE_HOME but not the OS bit version

cd %ORACLE_HOME%\perl\bin
perl -version

This is perl 5, version 14, subversion 4 (v5.14.4) built for MSWin32-x86-multi-thread ( 32-bit ORACLE_HOME )

This is perl 5, version 14, subversion 4 (v5.14.4) built for MSWin32-x64-multi-thread ( 64-bit ORACLE_HOME )

JAVA under %ORACLE_HOME%

32-bit Java will be installed with 32-bit Oracle software & 64 bit with 64-bit Oracle software

—  32bit Oracle client installed.  32-bit doesn’t shows the bit version.

cd %ORACLE_HOME%\jdk\bin
java -version

java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) Client VM (build 20.75-b01, mixed mode)

—  64bit Oracle client installed

cd %ORACLE_HOME%\jdk\bin
java -version

java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

INS-20802 Oracle Net Configuration Assistant Failed When Install 12c 32bit Oracle Client on Windows Server 2012 R2

INS-20802 error when install 32bit Oracle client on 64bit Windows.

SYMPTOM

When installing Oracle 12.1.0.2 32bit client onto Windows Server 2012R2, the following error occurred.

fail
INS-20802 Oracle Net Configuration Assistant Failed

SOLUTION

1) Click “..\winnt_12102_client32.zip\client32\stage\ext\bin\vcredist_x86.exe” and install “Microsoft Visual C++ 20xx Redistributable Package (x86)”. Then click “Retry again” to complete the installation successfully.

OR

2) Before installation, change “”..\winnt_12102_client32.zip\client32\install\oraparam.ini”. Then start the installation.

From :

MSVCREDIST_LOC=vcredist_x64.exe

TO;

MSVCREDIST_LOC=vcredist_x86.exe