Laurent Schneider
Pretty JSON in 21c
Oracle 21c now enables JSON as a datatype
12.2, 18c, 19c:
SQL> SELECT json_object(*)
from scott.emp
where ename='SCOTT';
JSON_OBJECT(*)
--------------
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1987-04-19T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}
21c:
SQL> SELECT json_object(* returning json)
from scott.emp
where ename='SCOTT';
JSON_OBJECT(*RETURNINGJSON)
---------------------------
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1987-04-19T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}
Ok, it looks similar, but it’s a no longer a string (varchar2 or clob), it is a json object.
SQL> create table t(j json);
SQL> insert into t values('{"x":1}');
SQL> select t.j.x from t t;
X ---------- 1
SQL> desc t
Name Null? Type ----------------- -------- ------------ J JSON
What’s more, sqlplus can prettyprint the json
SQL> set jsonprint xxx
SP2-0158: unknown SET option "xxx"
Usage: SET JSONPRINT {NORMAL | PRETTY | ASCII}
SQL> set jsonpr pret
SQL> sho jsonpr
jsonprint PRETTY
SQL> SELECT json_object(* returning json) from scott.emp where ename='SCOTT';
JSON_OBJECT(*RETURNINGJSON) -------------------------------------------------- { "EMPNO" : 7788, "ENAME" : "SCOTT", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : "1987-04-19T00:00:00", "SAL" : 3000, "COMM" : null, "DEPTNO" : 20 }
checksum of a column
Something I always wanted arrived this week, a checksum of a column !
SQL> create table t1(x number);
Table created.
SQL> create table t2(x number);
Table created.
SQL> insert into t1(x) values (1);
1 row created.
SQL> insert into t2(x) values (1);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;
T1 T2 ---------- ---------- 863352 863352
SQL> insert into t1(x) values (2);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;
T1 T2 ---------- ---------- 778195 863352
it is much more convenient than minus / intersect / not in and others to find out if two columns have identical values.
Oracle Database 21c which has just been released on Linux have a few more SQL improvement, like MINUS ALL that deals with duplicates and BIT_AND_AGG (OR, XOR) to aggregate bits.
SQL> select
2 EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(empno, 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /
EMPNO BIN ---------- ---------------- 7369 0001110011001001 7499 0001110101001011 7521 0001110101100001 7566 0001110110001110 7654 0001110111100110 7698 0001111000010010 7782 0001111001100110 7788 0001111001101100 7839 0001111010011111 7844 0001111010100100 7876 0001111011000100 7900 0001111011011100 7902 0001111011011110 7934 0001111011111110 14 rows selected.
SQL> select
2 bit_and_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_and_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /
EMPNO BIN ---------- ---------------- 7168 0001110000000000
SQL> select
2 bit_or_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_or_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /
EMPNO BIN ---------- ---------------- 8191 0001111111111111
It obviously works
Database link and user defined datatypes
To use an object table or an object column over a database link, a type with the same OID as remote type must exist locally.
SQL> conn user1/***@remotedb01
Connected.
SQL> create type tt as object (x number)
2 /
Type created.
SQL> create table t (x tt);
Table created.
SQL> insert into t values (tt(1));
1 row created.
SQL> commit;
Commit complete.
SQL> select t.x.x from t t;
X.X
---------------
1
SQL> conn user1/***@localdb01
Connected.
SQL> select t.x.x from t@remotedb01 t;
select t.x.x from t@remotedb01 t
*
ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined type columns
$ oerr ora 22804
22804, 00000, "remote operations not permitted on object tables or user-defined type columns"
*Cause: An attempt was made to perform queries or DML
operations on remote object tables or on remote
table columns whose type is one of object, REF,
nested table or VARRAY.
But, there is a solution — otherwise I wouldn’t write this post today
SQL> select type_name,TYPE_OID from user_types@remotedb01 t;
TYPE_NAME TYPE_OID
--------- --------------------------------
TT C6760780CC0BFA67E0539A24840A3B40
SQL> create type tt
2 oid 'C6760780CC0BFA67E0539A24840A3B40'
3 as object(x number)
4 /
Type created.
SQL> select t.x.x from t@remotedb01 t;
X.X
---------------
1
It’s that simple, we create the type locally, with the same OID
grep color
When you move away from commercial UNIX to Linux, some goodies are just fun, even if they are simple and old.
Let’s look at grep. By default, the matched pattern is red. But the color could be changed. Some magic regexp could be used to get more than one color
$ tnsping DB01 |
egrep '^TNS-[0-9]*'
TNS-03505: Failed to resolve name
The color could be changed to green
$ tnsping DB02 |
GREP_COLORS="ms=1;32" egrep OK
OK (10 msec)
Now I want to get both, RED and GREEN, so I need to grep for “OK” and “TNS” and apply a different color. Pattern ‘OK|^’ returns always true but only OK will be highlighted
$ tnsping DB01 |
egrep 'OK|TNS-'|
GREP_COLORS="ms=1;32" egrep --color=always 'OK|^'|
egrep 'TNS-[0-9]+|^'
TNS-03505: Failed to resolve name
$ tnsping DB02 |
egrep 'OK|TNS-'|
GREP_COLORS="ms=1;32" egrep --color=always 'OK|^'|
egrep 'TNS-[0-9]+|^'
OK (10 msec)
Download Oracle software with the command line
When downloading software to my database server, I used to first download locally and later copy to my Unix box… but wouldn’t be convenient to download it directly on the database server?
Quite often, you get no X and no Browser and no Internet access on your datacenter. Therefore, we’ll use wget to the purpose. CURL is a similar tool that does the trick as well. WGET also exists for Windows by the way.
First, you need WGET
sudo yum install wget
Then, you need Internet
Ask your network colleagues for a proxy and request access to the following domains
- edelivery.oracle.com
- aru-akam.oracle.com
- ccr.oracle.com
- login.oracle.com
- support.oracle.com
- updates.oracle.com
- oauth-e.oracle.com
- download.oracle.com
- edelivery.oracle.com
- epd-akam-intl.oracle.com
Some of those are documented on Registering the Proxy Details for My Oracle Support but I extended the list for software download (e.g. SQL Developer)
Now, configure your .wgetrc
https_proxy = proxy.example.com:8080
proxy_user = oracle
proxy_passwd = ***
http_user = laurent.schneider@example.com
http_password = ***
The https proxy is your network proxy to access oracle.com from your database server. The proxy user and password may be required on your company proxy. The http user and password are your oracle.com (otn/metalink) credentials.
Later, to figure out the URL, either use the WGET script Oracle sometimes provides
or try to copy the link in your browser, e.g.
https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-20.2.0.175.1842-20.2.0-175.1842.noarch.rpm
At this point, it probably won’t work
$ wget --no-check-certificate "https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-20.2.0.175.1842-20.2.0-175.1842.noarch.rpm"
strings
$ htmltree sqldeveloper-20.2.0.175.1842-20.2.0-175.1842.noarch.rpm
==============================================================================
Parsing sqldeveloper-20.2.0.175.1842-20.2.0-175.1842.noarch.rpm...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
<html> @0
<head> @0.0
<script language="javascript" type="text/javascript"> @0.0.0
"\x0afunction submitForm()\x0a{\x0avar hash = location.hash;\x0aif (hash) {\x0aif..."
<base target="_self" /> @0.0.1
<body onload="submitForm()"> @0.1
<noscript> @0.1.0
<p> @0.1.0.0
"JavaScript is required. Enable JavaScript to use OAM Server."
<form action="https://login.oracle.com/mysso/signon.jsp" method="post" name="myForm"> @0.1.1
We haven’t login.
Let’s get the login cookie
wget --no-check-certificate --save-cookies=mycookie.txt --keep-session-cookies https://edelivery.oracle.com/osdc/cliauth
Your mycookie.txt file should now contains login.oracle.com credentials.
Depending on the piece of software, e.g. sql developer, the authparam must be passed in. The authparam can be seen once you start the download, e.g. in your Downloads list (CTRL-J). When you use the wget script, when available, it probably provides a token= instead of an authparam=. The authparam typically validates you agreed to the license and possibly expires after 30 minutes. But maybe you can read the cookie and figure out how to pass in how to accept the license without Authparam. I haven’t gone that far yet.
wget --load-cookies=mycookie.txt --no-check-certificate "https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-20.2.0.175.1842-20.2.0-175.1842.noarch.rpm?AuthParam=1111111111_ffffffffffffffffffffffffffffffff"
A long post for a short mission, downloading a file…
I remind you that using no-check-certificate and clear text passwords in .wgetrc isn’t a good security practice
Unannouncement: Oracle 20c release date
Just reading the Doc 742060.1, the release 20c, which was due 2020, disappeared from the roadmap. Don’t wait anymore for 20c, there won’t be one. There was a preview release in the cloud, but Oracle failed to release one new release every year. While we are all used to wait 2-6 years for a new major, switching to yearly versions (18 and 19 were just patchset) is a promise Oracle couldn’t hold.
My two cents : desupporting non-cdb in (invisible) 20c is an headache for many customers…
SCP + sudo
Sometimes you like to copy files from A to B and you have sudo rights on A and B and you do a lot of “cp” to /tmp and chmod and chown’s. This is annoying…
Firstly, I dislike tempfiles.
- they use space
- they generate bugs when run in parallel
- they often are prone to code injection
- they remain on disk for years
Secondly, unix guys like pipes. While would one do
p <a >b
q <b >c
when you can
p <a |q >c
?
Lastly, I like to type less. So I wrote a small shell script that copies and uses sudo
combine #scp with #sudo?
ssh srv1 "cd /foo;sudo tar cf – bar"|ssh srv2 "cd /foo;sudo tar xf -"
— laurentsch (@laurentsch) December 7, 2020
at the end, I can
scp++ srv1:/dir/file srv2:/dir
using sudo
see comments for the script
ipcalc in powershell
Last day I wrote how to do it in AIX or Linux ip calc with ifconfig
It isn’t that different in PowerShell, the fun is to the calculation yourself. For translating 0.0.0.0 in 0, we can use [IPADDRESS].
Let’s try…
$ip = [IPADDRESS](
(Get-NetIPAddress -AddressFamily "IPv4" -InterfaceAlias "Ethernet*").
ipaddress)
$prefix = (
Get-NetIPAddress -AddressFamily "IPv4" -InterfaceAlias "Ethernet*").
prefixlength
The length and the ip of the current interface. In my case I have only one
PS> $ip
Address : 1677830336
AddressFamily : InterNetwork
IPAddressToString : 192.168.1.100
PS> $prefix
24
with a prefix length of 24, we need a netmask of 24 bits
11111111.11111111.11111111.00000000
which is
11111111.11111111.11111111.11111111 --> 2^32-1
-
11111111 --> 2^(32-24)-1
to do the math
$netmask=[IPADDRESS]([Math]::Pow(2,32)-[Math]::Pow(2,32-$prefix))
IPAddressToString : 255.255.255.0
let’s bitand
$netid = [IPADDRESS]($ip.Address -band $netmask.address)
IPAddressToString : 192.168.1.0
sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64
This error usually while you do something wrong. Wait, what’s an error when you do everything right?
Okay, here it is:
You install the instantclient 32 rpm
oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64
On that server, you switch home using oraenv
$ . oraenv ORACLE_SID = [oracle] ? DB01 The Oracle base has been set to /u01/app/oracle
You start sqlplus
$ sqlplus -v sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64
Oops!? what happened?
This dubious made-by-Oracle RPM package simply created a sqlplus link in BIN.
lrwxrwxrwx. /bin/sqlplus -> /usr/lib/oracle/12.1/client/bin/sqlplus
Then, oraenv did put ORACLE_HOME at the end position
$ echo $PATH /usr/local/bin:/usr/bin:/u01/app/oracle/product/db19c/db01/bin
Just change the PATH manually and you’ll be fine.
$ PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin $ sqlplus -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.4.0.0.0
network ip calculation with ifconfig
Most *nix are different. I’ll start with a plain Linux output
ifconfig eth0 eth0: flags=4163 mtu 1500 inet 93.184.216.34 netmask 255.255.255.0 broadcast 93.184.216.255
to get the network ip, I just bitwise-and the inet and the netmask. To do it with the shell, I convert the ip to an integer and use the & (AND) operator
IP=$(ifconfig eth0|grep inet|awk '{print $2}') NM=$(ifconfig eth0|grep inet|awk '{print $4}')
I get my IP=93.184.216.34 and NM=255.255.255.0 out of the ifconfig output
IPDEC=0;IFS=. ;for f in $IP;do ((IPDEC*=256));((IPDEC+=$f));done NMDEC=0;IFS=. ;for f in $NM;do ((NMDEC*=256));((NMDEC+=$f));done
By converting the IP-base-256 address, I get IPDEC=1572395042 and NMDEC=4294967040 in decimal
NWDEC=$((IPDEC&NMDEC))
That’s simple. My network IP is 1572395008
Let’s print it
NW=$((NWDEC/256/256/256)).$((NWDEC/256/256%256)).$((NWDEC/256%256)).$((NWDEC%256)) NW=93.184.216.0
Thanks for reading me that far. Ok let blogger Mathieu Trudel-Lapierre tell you : If you’re still using ifconfig, you’re living in the past
ip addr
ip shows your ip, and ipcalc do the calculation
ipcalc -n "$(ip -o -4 -br address show eth0 |awk '{print $3}')" NETWORK=93.184.216.0
ODBC and EZCONNECT or my way out of active directory
The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01
tnsnames.ora DB01 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(host=srv01)(Port=1521) ) (CONNECT_DATA= (SERVICE_NAME=DB01) ) )
But the file is often managed centrally and deployed to the client by the DBA via home scripts. The syntax is pretty ugly and very soon you’ll see, a space or a parenthesis is missing and the loss of service is complete…
When working with Windows, one guy may try to setup active directory resolution. Before Exchange 2003, the schema was extended and that’s it, it works. But later, Microsoft made things more secure, among others by disabling anonymous bind and probably later by enforcing SSL, and one day you’ll see, the connection no longer works. Also, the schema extension could not be reverted, so it is not a thing you do just for fun in production
While there are white papers and blog articles on using authenticated bind, I could not find any support note.
Note 361192.1 mentions :
When anonymous operations are disabled, anonymous operations performed against Active Directory will fail
And note 455031.1 mentions :
Configuring Non-Anonymous LDAP Access Prerequisites: – A working LDAP naming environment should already exist between a client and OID (not Active Directory)
While note 1587824.1 refers the white paper Configuring Microsoft Active Directory for Oracle Net Naming , it clearly states This document is provided for information purposes only
So when AD changes, chances are, you will get an issue. Maybe in 2020Q1 according to https://portal.msrc.microsoft.com/en-us/security-guidance/advisory/ADV190023
Clearly, if you need more, you should go for an Oracle directory server like OID.
But if you need less? maybe you could go for easy connect (EZCONNECT). This is a zero-configuration setup that puts all the configuration out of the DBA tasks. So it seems to be better.
There are plenty of examples on how to use it, even with SSL and RAC and so on. In its simplest form you’ll use
sqlplus scott/tiger@srv01:1521/DB01
instead of
sqlplus scott/tiger@DB01
So a little bit more details but no more ActiveDirectory and no more tnsnames.ora.
But does it work with Excel and family? actually yes
You need to specify
Service Name : //srv01:1521/db01
If you don’t prefix with //, it doesn’t work.
Conclusion: if you are not willing to maintain local tnsnames and struggling with ActiveDirectory security enhancement, consider easyconnect but be aware of the additional slashes in the server name
Connect to ActiveDirectory with ldapsearch on Unix
In ancient times, ldapsearch could query ActiveDirectory without issues. In this examples, I used openldap client 2.4. Other tools may have other parameters.
$ ldapsearch -H ldap://example.com:389 -b dc=example,dc=com cn="Laurent C. Schneider" mail mail: laurent.c.schneider@example.com
In Active Directory (AD) it is no longer the default since Windows Server 2003, unless you change dSHeuristics to 0000002 to allow anonymous access. Not recommended.
Anonymous LDAP operations
In normal case you’ll get :
$ ldapsearch -H ldap://example.com:389 -b dc=example,dc=com cn="Laurent C. Schneider" mail ldap_search: Operations error ldap_search: additional info: 000004DC: LdapErr: DSID-0C0907C2, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v2580 0 matches
Another widely used, simple, not recommended method is to use simple bind over ldap:389.
$ ldapsearch -H ldap://example.com:389 -D user001@example.com -w secretpassword -b dc=example,dc=com cn="Laurent C. Schneider" mail mail: laurent.c.schneider@example.com
It authenticates your user, but it send the password in clear text over the network. Therefore, if you use simple bind, use ldaps too. Microsoft announced an upcoming Windows update in early 2020 that will prevent simple bind in clear text
ADV190023
So for sure, you should prefer SSL. You probably need or already have your pki root-ca’s installed. If you use OpenLdap, the TLS_CACERT is defined in /etc/openldap/ldap.conf.
$ grep TLS_CACERT /etc/openldap/ldap.conf TLS_CACERTDIR /etc/pki/tls/certs $ ldapsearch -H ldaps://example.com:636 -D user001@example.com -w secretpassword -b dc=example,dc=com cn="Laurent C. Schneider" mail mail: laurent.c.schneider@example.com
That should be good enough to survive early 2020…
But, maybe you don’t like to put your password in a script at all.
One could use Kerberos.
$ kinit Password for user001@EXAMPLE.COM: $ klist Ticket cache: FILE:/tmp/krb5cc_001 Default principal: user001@EXAMPLE.COM Valid starting Expires Service principal 11/13/19 12:11:44 11/13/19 22:11:49 krbtgt/EXAMPLE.COM@EXAMPLE.COM renew until 11/20/19 12:11:44 $ ldapsearch -Y GSSAPI -H ldap://example.com:389 -b dc=example,dc=com cn="Laurent C. Schneider" mail SASL/GSSAPI authentication started SASL username: user001@EXAMPLE.COM SASL SSF: 56 SASL data security layer installed. mail: laurent.c.schneider@example.com
A list of supported mechanism can be retrieved with the -s base option
$ ldapsearch -s base -H ldap://example.com:389 -D user001@example.com supportedSASLMechanism supportedSASLMechanisms: GSSAPI supportedSASLMechanisms: GSS-SPNEGO supportedSASLMechanisms: EXTERNAL supportedSASLMechanisms: DIGEST-MD5
If you prefer to use a SSL client certificate, it requires a few steps.
First you need to get one certificate. There are many way to this, like Oracle Wallet manager or Microsoft Certmgr, but you could well use openssl. Using a selfsigned certificate is not a good idea.
openssl.org
Before you submit your certificate for signature. You need to add a subject alternate name with the principal name.
cat /etc/openssl/openssl.cnf > server.cnf echo "[client]" >> server.cnf echo "extendedKeyUsage = clientAuth" >> server.cnf echo "subjectAltName=otherName:msUPN;UTF8:user001@example.com" >> server.cnf
This is (at least in the openssl version I used) not possible in one step. You need to create a local config file (-config) and define a new request extension ([client]).
openssl req -new -subj '/DC=com/DC=example/OU=Users/CN=user001' -key private_key.pem -out server.csr -config server.cnf -reqexts client
Once you have your user-certificate and root-authority, you need to map your client certificate to your AD account
Map a certificate to a user account
In openldap, you then create your own $HOME/.ldaprc
$ cat $HOME/.ldaprc TLS_CERT /home/user001/cert_user001.pem TLS_KEY /home/user001/private_key.pem $ ldapsearch -Y EXTERNAL -ZZ -H ldap://example.lab:389 -D user001@example.com -vvv -b "DC=example,DC=lab" cn="Laurent C. Schneider" mail ldap_initialize( ldap://example.lab:389/??base ) SASL/EXTERNAL authentication started SASL username: cn=user001,ou=Users,dc=example,dc=lab SASL SSF: 0 mail: laurent.c.schneider@example.com
The option -Z means starttls. I connect plain to 389, then start TLS for ldap.
With this command, you connect to AD with an SSL client certificate
free Oracle cloud forever
I could not miss this ! After offering free apex for non-productive usage (apex.oracle.com), free sql environment for playing (livesql.oracle.com), Oracle now offers free for ever infrastructure and database.
With a few clicks, a credit card (that won’t be charged) and a few minutes of patience, you will be able to have your own Linux 7.7 build and your own autonomous database (including backups, patches) and apex, sql developer web edition and more. All on the cloud.
I gave it a try. It looks awesome. You have a server with an UNIX account. You have a database running, I could even set the region to Zurich, so the data stays in Switzerland. You can run webservices via ORDS and access them with your phones. Unlimited possibilities.
It just made my day.
Of course, it is possible to upgrade to a paid version. If you use the free version and provided your private credit card, don’t be fool to try something you cannot afford /!\
SQL Developer WEB is by no mean as rich as SQL developer. You could see a list of tables and have a worksheet, but there is so much missing, like REST-enabling a procedure.
Still, you can do it with one line of code
create or replace procedure u.getemp(empno in number, ename out varchar2) as begin select ename into ename from emp where empno=getemp.empno; end; / exec ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'U', p_object => 'GETEMP', p_object_type => 'PROCEDURE', p_object_alias => 'getemp', p_auto_rest_auth => FALSE); commit;
Now you’ve got your web service.
Doh!
Wait? That’s it?
try it
curl --request POST --url https***.eu-zurich-1.oraclecloudapps.com/ords/u/getemp/ --header 'content-type: application/json' --data '{"empno": "7788"}' {"ename":"SCOTT"}
(as I have only one OCPU, I masked the url, but just post a comment if you want to see it)
Okay, you want a nice looking app with a few more clicks, just install apex
https***.eu-zurich-1.oraclecloudapps.com/ords/f?p=100:1:109634901295466:::::
There a huge difference between apex.oracle.com or livesql.oracle.com and your own database/apex/linux. You got admin rights (PDB_DBA) and productive usage is allowed/encouraged. This means a lot to me.
The versions I received are Oracle Linux Server 7.7 and Oracle Database Enterprise Edition 18.4
Goldengate 19c on AIX for Oracle database
Now Oracle Goldengate 19.1.0.2 can manage 19c AIX Oracle DB instance. Linux has been out for a long time. Sparc is also available. For Windows, HPUX, patience …
$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.2 OGGCORE_19.1.0.0.0_PLATFORMS_190823.0013_FBO AIX 7, ppc, 64bit (optimized), Oracle 19c on Aug 25 2019 22:10:20 Operating system character set identified as US-ASCII. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
on parsing arguments in shell
While most programming languages are accepting arguments as an array of strings, shell doesn’t
arglist.c
#include
int main(int argc, char **argv) {
int i;
for (i=1; argc>i; i++)
printf("$%d=%s\n",i,argv[i]);
}
$ make arglist
cc arglist.c -o arglist
$ ./arglist one two three four
$1=one
$2=two
$3=three
$4=four
To do the same in shell, it requires some dynamic evaluation, for instance with eval
arglist.sh
i=1
while [ $i -le $# ]
do
eval ARGV[$i]=\$$i
echo "\$$i=$(eval echo \"\${ARGV[$i]}\")"
((i+=1))
done
$ ./arglist.sh one two three four
$1=one
$2=two
$3=three
$4=four
To further send the arguments to another script or function, it is important to take consideration of white spaces and file expansion (e.g.: *.*), this is achieved with double-quotes and the at-sign
f "${ARGV[@]}"
I recommend against using eval whenever possible. While less awesome, I would prefer something more verbose and without eval
arglist2.sh
[ -n "$1" ] && echo "\$1=$1"
[ -n "$2" ] && echo "\$2=$2"
[ -n "$3" ] && echo "\$3=$3"
[ -n "$4" ] && echo "\$4=$4"
[ -n "$5" ] && echo "\$5=$5"
$ ./arglist2.sh one two three four
$1=one
$2=two
$3=three
$4=four
Using eval is difficult and dangerous. The innocent may messed up with the quotes resulting in random effects. It is also a common source of code injection
inj.sh
eval x=$1
$ ./inj.sh 1
$ ./inj.sh "1; echo uh-oh"
uh-oh
Ref: Eval Injection
Dump TNSNAMES.ORA from ActiveDirectory
Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux).
echo "# AD" > tnsnames.ora
$o = New-Object DirectoryServices.DirectorySearcher
$o.Filter = 'objectclass=orclNetService'
foreach ($p in $o.FindAll().Properties) {
[String]($p.name+"="+$p.orclnetdescstring) >> tnsnames.ora
}
goodies
Active Dataguard : read only with apply
A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it.
Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do.
Possibly, you can cancel the recovery and open in read only mode.
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
or
Dataguard edit database db01_sb set state='APPLY-OFF'
Now we can open the database
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ ONLY
Now we can query the database
SQL> select count(*) from dba_objects;
COUNT(*)
----------
22783
but not write
SQL> create table t(x number);
create table t(x number)
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
if you need to write, there is more than one way to do it. Either logical standby, or snapshot standby, or create an additional database and create database links and synonyms. My preferred option would be golden gate. But this is beyond the scope of this post.
A good option is to open it without stopping the apply process…
DGMGRL> edit database db01_sb set state='APPLY-ON';
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
This good but is bounded to the Active Dataguard licensing option (EE).
There are also a few differences
One is that you cannot compile view on the fly.
Primary
SQL> create or replace force view bar as select * from foo;
Warning: View created with compilation errors.
SQL> create table foo(x number);
Table created.
SQL> select status from user_objects where object_name='BAR';
STATUS
---------------
INVALID
The view is invalid, but a select would compile. But not on standby read only
Standby
SQL> select * from bar;
select * from bar
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of BAR
ORA-16000: database or pluggable database open for read-only access
Primary
SQL> select * from bar;
no rows selected
SQL> select * from bar;
no rows selected
A more worrying issuing is security. On your main system, you have failed login attempts
Primary
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 3;
Profile altered.
SQL> create user u identified by p;
User created.
SQL> conn u/a@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/b@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/c@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/d@db01_prim
ERROR:
ORA-28000: the account is locked
SQL> conn / as sysdba
Connected.
SQL> alter user u account unlock;
User altered.
On the standby, since it is read only, the last tentatives are not recorded.
SQL> conn u/e@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/f@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/g@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/h@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
which allows you unlimited login attempts
As well, audit records are not generated.
Primary
SQL> audit session;
Standby
SQL> conn u/xxx@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
no rows selected
No audit record from ORA-01017
Primary
SQL> conn u/xxx@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC
USER ACTION TIMESTAMP RETURNCODE
---- ------ ------------------- ----------
U LOGON 2019-06-14_15:39:05 1017
On primary, audit records are saved as expected. There are many other things/tools that won’t work the same way. Because it is read-only. Use with care if you are entitled too.
on logical and physical working directories
many ignore the difference meaning of .. (dot dot) as an argument when used with ls
or cd
this leads to buggy coding and wrong parsing of arguments in scripts
let’s start with an example
$ mkdir $HOME/test $HOME/test/physical $HOME/test/foo $ cd $HOME/test/foo $ ln -s ../physical logical $ cd logical $ ls -l .. total 8 drwxr-xr-x. 2 oracle dba 18:01 foo drwxr-xr-x. 2 oracle dba 18:01 physical $ cd ..; ls -l total 0 lrwxrwxrwx. 1 oracle dba 18:01 logical -> ../physical $
Wait… how could cd ..; ls
and ls ..
have a different output?
Most programs except cd
use the physical path in arguments.
If you are in the physical directory $HOME/test/physical and you issue
program argument
it will behave the same as if you were in the logical path. This is somehow consistent, but confusing
Let’s try
$ cd $HOME/test/physical $ ls -l .. total 8 drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 foo drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 physical $ cd $HOME/test/foo/logical $ ls -l .. total 8 drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 foo drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 physical $
BINGO! I got the same output
Got it? The arguments are parsed using the physical path.
This does not relate to absolute or relative path. While most bug occurs with relative path, a relative path is neither logical nor physical. It is relative. Whether it relates to a physical or it relates to a logical path is the scope of this post.
Okay, we get now that most programs use the “filename” as if you were in the physical path.
Path Logical Physical /home/user01/test/physical /home/user01/test/physical /home/user01/test/physical /home/user01/test/foo/logical /home/user01/test/foo/logical /home/user01/test/physicalIn most case, it makes no difference (which render the bugs less evident to trap). For instance browsing paths in dbca doesn’t do it right, but it is just a side note.
If you use /physicalpath/file or /logicalpath/file or ./file, it really doesn’t matter. It’s relevant with symbolic links on directories and relative path to parents.
So for instance if you want to change to the directory of the first argument it would be wrong to do
cd $(dirname $1)
because cd does use logical path and your program should NOT (to make it symlink-independent).
a not-properly documented (missing for instance on aix 7.2 cd manpage) way is to use the -P option
In Linux there is also a -e option which gives you a non-zero error code on non-existent current working directory (if you are in a path that does not exits, the cd -P won’t work but return 0 by default), but for now, just stick to -P
Let’s see
$ pwd
/home/user01/test/foo/logical
$ cd -P ..
$ pwd
/home/user01/test
wait, you change to .. and went two step back? this is not the default behavior of cd. The default behavior is -L
$ pwd
/home/user01/test/foo/logical
$ cd -L ..
$ pwd
/home/user01/test/foo
hmm… is that not easier? No way! this is just fine for cd (where you navigate to parent regarding to the logical working directory). But it is not the way the arguments are interpreted.
Apart from cd , there is another command that deals with symlink path hassle : pwd. Again, it is not really well documented (missing in Solaris 10 pwd manpage), but it has always been there.
$ pwd
/u01/users/oracle/test/foo/logical
$ pwd -L
/u01/users/oracle/test/foo/logical
$ pwd -P
/u01/users/oracle/test/physical
$
next time you use cp, ls, cat with a .. and symlinks, remember this post !
Last note, one may like to try the long option. Don’t!
$ man pwd | grep -- -P
-P, --physical
$ cd -P .
$ cd --physical .
-bash: cd: --: invalid option
cd: usage: cd [-L|-P] [dir]
$
Select from cdb_* views
There is no privileges strong enough for you to view all objects in all databases
Let’s try
as sys:
SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ---------- ---------- 1 22749 3 22721
as non-sys
SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to c##u; Grant succeeded. SQL> conn c##u/x Connected. SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ---------- ---------- 1 22749
You can try to grant and grant and grant, it won’t help
SQL> conn / as sysdba Connected. SQL> grant dba, cdb_dba, pdb_dba, all privileges, sysdba to c##u with admin option container=all; Grant succeeded. SQL> conn c##u/x Connected. SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ---------- ---------- 1 22749
This is not what you are missing…
SQL> revoke dba, cdb_dba, pdb_dba, all privileges, sysdba from c##u container=all; Revoke succeeded. SQL> grant create session, select any dictionary to c##u; Grant succeeded.
you need container data
SQL> alter user c##u set container_data=all container=current; User altered. SQL> conn c##u/x Connected. SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ---------- ---------- 1 22749 3 22721
Here you go …
changing container in plsql
One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware.
If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_*
OLD:
SQL> select count(*) from dba_users; COUNT(*) ---------- 121
NEW: non-cdb
SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID COUNT(*) ------ ---------- 0 121
NEW: single-tenant
SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID COUNT(*) ---------- ---------- 1 23 3 39
As mentioned in a white paper :
The set container privilege has certain restrictions in PL/SQL
multitenant-security-concepts-12c-2402462.pdf
Sometimes the certain restrictions will puzzle you
SQL> set feed off serverout on SQL> exec dbms_output.put_line('root') root SQL> alter session set container=dora1; SQL> sho serverout serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED SQL> exec dbms_output.put_line('dora1'); SQL> -- NO OUTPUT WTF !!!! SQL> set serveroutput ON SQL> exec dbms_output.put_line('dora1'); dora1 SQL>
The security model prevents you from using alter session (with execute immediate or like in the previous example) to execute plsql.
Now you know…