Laurent Schneider


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount quiet
ORACLE instance started.
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


First time I see this. Let’s try to remember how archiving worked in the nineties.

Log_archive_start wasn’t implicit. Why would you need to run an archiver process during business hours, if you could quietly archive log in the evening ? At that time there were no internal jobs or so, the load was predictable and the dba had plenty of time for a handful of databases (or very often only a single database to tune).

To manually archive in Oracle 7, which still work, we could simply do :

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +DG_RECO01
Oldest online log sequence 157
Current log sequence 161
SQL> archive log 161
ORA-00259: log 1 of open instance DB01 (thread 1) is the current log, cannot archive
SQL> alter system switch logfile;
System altered.
SQL> archive log 161
Statement processed.
SQL>


What you cannot do, in noarchivelog, is to archive all log automatically

SQL> archive log all
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log


But why would you do that?

…

Okay, after looking in the alert log (a wonderful source of information 🙂 ), I found out changing the mode to noarchivelog didn’t implicitely deactivate the standby protection mode.

Here you go

SQL> alter database set STANDBY DATABASE TO MAXIMIZE protection;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
SQL> alter database set STANDBY DATABASE TO MAXIMIZE performance;
Database altered.
SQL> alter database open;
Database altered.


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


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

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

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

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: [email protected]

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 [email protected] -w secretpassword -b dc=example,dc=com cn="Laurent C. Schneider" mail
mail: [email protected]

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 [email protected] -w secretpassword -b dc=example,dc=com cn="Laurent C. Schneider" mail
mail: [email protected]

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 [email protected]: 
$ klist
Ticket cache: FILE:/tmp/krb5cc_001
Default principal: [email protected]

Valid starting     Expires            Service principal
11/13/19 12:11:44  11/13/19 22:11:49  krbtgt/[email protected]
        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: [email protected]
SASL SSF: 56
SASL data security layer installed.
mail: [email protected]

A list of supported mechanism can be retrieved with the -s base option

$ ldapsearch -s base -H ldap://example.com:389  -D [email protected] 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:[email protected]" >> 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 [email protected] -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: [email protected]

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

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

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 …

download.oracle.com

$ 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.

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

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

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.

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/physical

In 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]
$


With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata.

Is this going to help you?

No 😛

Oracle provides support for Active Directory only on Windows. Client and database must run on Windows

Net Services Administrator’s Guide

Maybe some of my readers working on a Linux-free environment may still like it

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 …

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…

Let’s start with some basics. The basics works as well on Unix, Linux and Windows. Later techniques only work on linux/unix

$ ls -l hosts          
-rw-r--r--. 1 root root 211 Oct  5  2015 hosts
$ ls -l xxx  
ls: cannot access xxx: No such file or directory
$ read x
foo
$ 

Outpout and error are displayed on screen and input is read from your keyboard

The output is kwown as file-descriptor-1 or stdout. Sometimes, depending on your OS, it may be exposed as /dev/fd/1 or /dev/stdout. But not all *nix have this.
The error is kwown as file-descriptor-2 or stderr.
The input is known as file-descriptor-0 or stdin.

Instead of keyboard and screen, it could be a file or any other devices, e.g. /dev/null or just a simple file.

$ ls -l hosts 1>file1
$ ls -l xxx 2>file2          
$ read x 0<file3

0 and 1 are optional here.

If is also possible to redirect stdout and vice versa

$ ls -l hosts 1>&2         
-rw-r--r--. 1 root root 211 Oct  5  2015 hosts
$ ls -l xxx 2>&1 
ls: cannot access xxx: No such file or directory

It is possible to close the file descriptor.

sleep 1 1>&- 2>&- 0<&-

Well, sleep has no output and no input and no error, so the effect is not impressive.

If you write to a closed file descriptor, you get an error. Ok, if you close both stdout and stderr, the error will be silent. But there will still be an error.

$ (echo foo) 1>&-     
bash: echo: write error: Bad file descriptor
$ echo $?        
1
$ (echo bar 1>&2) 2>&-      
$ echo $?             
1

if you want to redirect stdin to stdout and stdout to stdin, you better use a new file descriptor

$ (ls -l hosts xxx 1>&2 2>&3) 3>&1            
xxx not found
-rw-rw-r-- 1 root system 2133 Jun 22 2017 hosts

An old trick is to use additional file descriptor to find a return code of command before the pipe.

$ ((((ls hosts; echo $? >&3) | tr '[a-z]' '[A-Z]' >&4) 3>&1) | (read rc; exit $rc)) 4>&1       
HOSTS
$ echo $?
0
$ ((((ls xxx; echo $? >&3) | tr '[a-z]' '[A-Z]' >&4) 3>&1) | (read rc; exit $rc)) 4>&1        
ls: cannot access xxx: No such file or directory
$ echo $?
2

As I know the trick for so long that I could not credit the author, only found some 21st century posts

If you want to redirect all your outputs to a logfile, you can use exec

#!/bin/ksh
exec 1>>/tmp/mylog
exec 2>>/tmp/mylog
cd /etc 
ls -l hosts          
ls -l xxx
exit

If you want to be able to still use your stdout / stderr, again, open new descriptors

#!/bin/ksh
exec 3>&1
exec 4>&2
exec 1>>/tmp/mylog
exec 2>>/tmp/mylog
cd /etc 
ls -l hosts          
ls -l xxx
echo INFO >&3
echo ERROR >&4
exec 3>&-
exec 4>&-
exit

Bash has also one shortcut

ls xxx host &>log

& redirect both 1 and 2 in one step. Doesn't work on ksh.

Let’s start with Powershell

Get-Date -format "o"
2019-03-08T17:41:02.7346332+01:00

The “O” or “o” standard format specifier represents a custom date and time format string using a pattern that preserves time zone information and emits a result string that complies with ISO 8601
docs.microsoft.com

Now Linux

date "+%Y%m%dT%H:%M:%S.%3N%z"

In SQL

to_char(current_timestamp, 'YYYYMMDD"T"HH24:MI:SS.FF3TZH:TZM') 

for my XML fans

extractvalue(xmlelement(t, current_timestamp),'/*') 

Now in AIX

 perl -e '
      use strict;
      use POSIX "strftime";
      use Time::Piece;
      use Time::HiRes "gettimeofday";
      my($x,$y)=gettimeofday;
      my $s=Time::Piece->new;
      my $t=$s->tzoffset;
      printf "%s.%03d%+03d:%02d\n",
        strftime("%Y%m%dT%H:%M:%S",localtime($x)),
        $y/1000,
        $t/3600,
        abs($t)%3600/60;
      '

Could not have figured out without google 😉
The GNU date could also be installed in AIX, but I am not root

A more generic unix version would be the UTC date

date -u "+%Y%m%dT%H:%M:%SZ" 
20190308T16:58:13Z

a common task of the DBA is to add databases in EM. If you do it 1000 times, you’ll get bored. I guess…

sure you could go to EM Cloud Management Pack, but maybe you use SQLPLUS + Create database + catalog/catproc.

Okay, there are some ways/hacks to do it with emcli/emctl, but let’s face it, there is a tool around the corner that does everything for you :
DBCA

Wait, that GUI thing? Yes and no. Just use the silent option. It is that simple.


dbca -silent
-createDatabase
-templateName General_Purpose.dbc
-gdbname cdb1 -sid cdb1
-sysPassword ***
-systemPassword ***
-emConfiguration CENTRAL
-dbsnmpPassword ***
-emUser sysman
-emPassword ***
-omsHost em.example.com
-omsPort 4900


Wait, that simple?

Yep 😉

More details on how to use silent dbca on oracle-base.com

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba.

In single-tenant (or multi-tenant), things get complicated.

Once again, the doc must be read at least twice 😉

If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in the root and all PDBs […] all common users are audited

This is very tricky, because you don’t want to audit common users only

Let’s try

SQL> conn / as sysdba
Connected.
SQL> create user c##u identified by ***;
User created.
SQL> grant create session to c##u container=all;
Grant succeeded.
SQL> alter session set container=pdb01;
Session altered.
SQL> create user u identified by ***;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> audit connect container=all;
Audit succeeded.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
SQL> sho parameter audit_trail
NAME         VALUE
------------ -------------
audit_trail  DB, EXTENDED
SQL> 

Audit is now logging all connections of all common users on all databases…

SQL> conn c##u/***
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> conn c##u/***@pdb01
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

Let’s verify :

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail order by timestamp;

CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     1 C##U     LOGON  18:01:05
     1 C##U     LOGOFF 18:01:06
     3 C##U     LOGON  18:01:07
     3 C##U     LOGOFF 18:01:08

So far so good. What about local users?

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

And???

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail where USERNAME='U';
no rows selected

Nope! DBA like me and you don’t care about those C## users, we want ALL users, not all common users.

For this purpose, we need to activate audit on every pluggable.

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb01;
Session altered.
SQL> audit connect;
Audit succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
CREATE SESSION      3

Now it should work

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01
SQL> host sleep 1

… and …

SQL> select * from cdb_audit_trail where username='U';
CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     3 U        LOGON  18:01:12
     3 U        LOGOFF 18:01:13

If you already switched from non-cdb to single-tenant, please check your audit strategy NOW !!!

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c

https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found

. Distinct listagg

. Desupport sqlplus product profile

. listener.log log rotation

Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

2014/05/distinct-listagg I wrote : Too bad the DISTINCT keyword was not implemented

what a good surprise to see this working in Oracle 19 : 19C LISTAGG DISTINCT

very similar to Unix ODBC Oracle

instead of tnsnames, the connections are defined in $SYBASE/interfaces.

the odbc.ini must exists as well in $SYBASE.

if you test with unixODBC-devel, keep in mind to use /usr/bin/isql and not $SYBASE_OCS/bin/isql

$ODBCSYSINI/odbc.ini
[syb]
Driver = Sybase16
DSN = syb
ServerName=SYB01


$ODBCINI/odbcinst.ini
[Sybase16]
Description = Adaptive Server Enterprise
Driver = /u01/app/sybase/product/16.0/DataAccess64/ODBC/lib/libsybdrvodb.so


$SYBASE/odbc.ini
[syb]
DSN = syb
ServerName=SYB01


$SYBASE/interfaces
SYB01
master tcp ether srv01.example.com 15000
query tcp ether srv01.example.com 15000

/usr/bin/isql -v syb user01 passw01
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>


To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc

This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC.

Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC.

What you need is an odbc.ini where you defined your connections
[DB01]
Driver = OracleODBC18
DSN = DB01
ServerName = DB01

and an odbcinst.ini where you define your driver
[OracleODBC18]
Description = Oracle ODBC driver for Oracle 18
Driver = /u01/app/oracle/product/18.1.0/client_64/lib/libsqora.so.18.1

the name / location and options may depend on your software / driver vendor.

ODBC uses TNSNAMES, so it really easy, you just the odbc entry ServerName = DB01 that matches
DB01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = srv01.example.com)(Port = 1521))(CONNECT_DATA = (SERVICE_NAME=DB01.example.com)))
in tnsnames.ora.

ODBC full client allows you to use all connection features like LDAP and SSL.

To test it, I installed unixODBC-devel
sudo yum install unixODBC-devel
export ODBCSYSINI=/home/user1/odbc
cd $ODBCSYSINI
vi odbc.ini odbcinst.ini

the machine-wide ODBCSYSINI is /etc. You can chose to define the ODBCINI user-wide (/home) and the ODBCSYSINI machine-wide (/etc). I wouldn’t use machine-wide passwords. But configuring the drivers only once may be an option. If you are root and you have not too many drivers/versions/bitcode.

Now try to connect :
isql DB01 scott tiger
SQL> select * from scott.emp;
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO|
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00| 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00| 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00| 2975 | | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00| 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00| 2850 | | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00| 2450 | | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00| 3000 | | 20 |
| 7839 | KING | PRESIDENT| | 1981-11-17 00:00:00| 5000 | | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00| 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00| 1100 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00| 950 | | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00| 3000 | | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00| 1300 | | 10 |
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
SQLRowCount returns -1
14 rows fetched
SQL>


Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK OPTION.

But let’s talk about granting too much privs.
disclaimer: it may damaged your database forever

SQL> create or replace view v as select trunc(sysdate) today from dual;
View created.
SQL> create public synonym v for v;
Synonym created.
SQL> grant all on v to public;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> delete from v;
1 row deleted.
SQL> select * from v;
TODAY
----------
2018-10-15
SQL> select count(*) from v;
COUNT(*)
----------
1
SQL> select count(dummy) from dual;
COUNT(DUMMY)
------------
0
SQL> rollback;
Rollback complete.


Wait… what happened ???

SQL> delete from v;
1 row deleted.
SQL> select count(*) from v;
COUNT(*)
----------
1


This is a biaised test, because nobody creates view in SYS schema and nobody shall ever do GRANT ALL TO PUBLIC. But sometimes, people do. Because of the grant, you have emptied dual. 😮

The COUNT(*) is a magic thing. select count(*) from dual returns 1. Unless your instance collapses.


SQL> delete dual;
1 row deleted.
SQL> alter session set "_fast_dual_enabled"=false;
Session altered.
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> rollback;


One reader once asked for assistance because he tried it and its db was broken. I won’t help you. Just do it for fun on a database that you can recreate afterwards.

Okay, enough fun for today, let’s see another side effect of excessive rights.

SQL> create user u identified by ***;
User created.
SQL> create role r;
Role created.
SQL> grant create view, create session to u;
Grant succeeded.
SQL> conn u/***@db01
Connected.
SQL> create view v as select trunc(trunc(sysdate)-.5) yesterday from dual;
View created.
SQL> create role r;
Role created.
SQL> select * from v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant delete on v to r;
grant delete on v to r
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'
SQL> grant select on v to r;
Grant succeeded.


So far so good, I have created a view and granted select only on that view. I cannot delete DUAL. I cannot grant delete.

Now learn about this less-known annoyance

SQL> conn / as sysdba
Connected.
SQL> grant select, update, insert, delete on u.v to r;
Grant succeeded.


What? SYS can give access to my view to a role, even if I have no DELETE right on the underlying?

SQL> grant create session, r to user2;
Grant succeeded.
SQL> conn user2/***@DB01
Connected.
SQL> select * from u.v;
YESTERDAY
----------
2018-10-14
SQL> delete from v;
delete from v
*
ERROR at line 1:
ORA-01031: insufficient privileges


Sofar, it didn’t have so many side effect. It is not uncommon to see scripts that automatically generate grants ; and it is also not uncommon to see those script going doolally.

But, one side effect is preventing future CREATE OR REPLACE statements.

SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'


CREATE OR REPLACE no longer work. You need to revoke the right. Either with a DROP VIEW or with


SQL> revoke insert, update, delete on v from r;
Revoke succeeded.
SQL> create or replace view v as select trunc(trunc(sysdate+6,'YYYY')+400,'YYYY')-7 xmas from dual;
View created.
SQL> select * from v;
XMAS
----------
2018-12-25


I’d recommend against using SYS to grant access to user tables. Use the schema owner.

Sometimes you use sqlplus and your line is longer than your linesize


SQL> select n||';'||x from t2;
1;one
2;twoPxMQztzLaqjWjGKOXIVIVrrHC
fJkTLbRgCPiENfWrrCjUMRSkmCfLUY
RdVASFacGtyEnUplOAXspDJZmSLPfg
ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy
lZLQROFxIxOzqeQeFMGXcGkuJwnGGg
zvNSuCFMVxyQgboLAUpDcYnBsuVyXX
vFtWPICwqmcEdmzRRkOzzPethrqjRR
aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh
ZcxJBvSCVXwONmS
prVzTykEfSsePyYwyLVoyYrVLynUzs
MLFWQxwUKNsVcYzUOAhslNldnBpITS
rxPlpJbLSjJqgxNxsGVsrYhkWAMufk
QnRayieEkSDYrNqyLejJuggADNxcgV
tszjJIYKCxPweNGhXsOFKGbMkTBPCf
DXwjBNgQYswbaNWBOEtSTHjIhdLAyM
nbhyhRKKdfaTTpTgHqQelVWmnkBHjA
ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR
SjdVlRwMas

3;three
SQL>


There are two extra line breaks, one after ONmS and one after wMas
An easy solution is to set long lines with set lin 32767 longc 32767 long 2000000000… but, once you reach 32K, end of the game.

I come up with an easy workaround, using clob2file

create directory d as '/tmp';
begin
for f in (select rownum r, n||';'||x||chr(10) txt from t)
loop
dbms_lob.clob2file(f.txt, 'D',
'F'||to_char(f.r,'FM00009')||'.txt');
end loop;
end;
/

ls -la F?????.txt
-rw-r----- 1 oracle 6 Sep 26 13:40 F00001.txt
-rw-r----- 1 oracle 506 Sep 26 13:40 F00002.txt
-rw-r----- 1 oracle 8 Sep 26 13:40 F00003.txt
cat F?????.txt

1;one
2;twoPxMQztzLaqjWjGKOXIVIVrrHC
fJkTLbRgCPiENfWrrCjUMRSkmCfLUY
RdVASFacGtyEnUplOAXspDJZmSLPfg
ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy
lZLQROFxIxOzqeQeFMGXcGkuJwnGGg
zvNSuCFMVxyQgboLAUpDcYnBsuVyXX
vFtWPICwqmcEdmzRRkOzzPethrqjRR
aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh
ZcxJBvSCVXwONmSprVzTykEfSsePyY
wyLVoyYrVLynUzsMLFWQxwUKNsVcYz
UOAhslNldnBpITSrxPlpJbLSjJqgxN
xsGVsrYhkWAMufkQnRayieEkSDYrNq
yLejJuggADNxcgVtszjJIYKCxPweNG
hXsOFKGbMkTBPCfDXwjBNgQYswbaNW
BOEtSTHjIhdLAyMnbhyhRKKdfaTTpT
gHqQelVWmnkBHjAZTrGqdtlYAgoXNH
noryxHxVVyaMiGRSjdVlRwMas
3;three


Now you can produce files with very long lines. It would also possible to dump everything in one file (by using DBMS_LOB.CREATETEMPORARY + DBMS_LOB.APPEND ) or to dump all files in parallel…

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction

For instance, one local and one remote

insert into t values(1);
insert into t@db02 values(2);
commit;


If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The transaction then shows up als pending.

Oracle documentation mentions about ORA-2PC-CRASH-TEST transaction comment to test this behavior, however, anything like note 126069.1 who starts with grant dba to scott; should be banned.

Apart from granting DBA to scott and using commit tansaction commment 'ORA-2PC-CRASH-TEST-7', I can still use my good (bad?) old shutdown abort.


SQL> insert into t values(1);
1 row created.
SQL> insert into t@db02 values(2);
1 row created.
SQL> -- shutdown abort on db02
SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 2.7.4509 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from DB02
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 prepared


Now you’ve got an issue. Not only the state of the transaction is unknown, but the in-doubt transaction may prevent further DMLs

SQL> update t set x=x+1;
update t set x=x+1
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 2.7.4509


You need to decide whether to commit or rollback the transaction. Let’s say I want to rollback. I need to have FORCE TRANSACTION privilege


SQL> rollback force '2.7.4509';
Rollback complete.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 forced rollback
SQL> update t set x=x+1;
0 rows updated.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.20.4519')
PL/SQL procedure successfully completed.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;
no rows selected


The lock disappears, dbms_transaction.purge_log_db_entry can also cleanup old entries.

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t

Nope – not via "upgrade". You can use Data Pump, Transportable Tablespaces or Full Transportable Export/Import as a direct option but you can't upgrade and plugin in one pass unfortunately 🙁

— Mike Dietrich (@MikeDietrichDE) August 22, 2018

If your database is huge and you don’t want to move anything nor change SID nor change path, this is my own receipt.

1. Backup your database
2. Create a XML description of your CDB
   
shutdown immediate
startup read only
exec dbms_pdb.describe('/save/DB01/DB01.xml')
shutdown immediate

3. Setup init.ora parameter for PDB
   
startup force nomount restrict
alter system set enable_pluggable_database=true scope=spfile;
alter system set db_name=CDBDB01 scope=spfile;
alter system set instance_name=DB01 scope=spfile;
startup force nomount

4. Create a CDB database
   Yes, you have read it correctly. You are required to create a database. There is -so far I’ve googled- no way to avoid this step. You can use dbca or sqlplus.
   Don’t overwrite the datafile. Use for instance /data/DB01/cdb/system.dbf instead of /data/DB01/system.dbf
   
create database CDBDB01
user sys identified by ***
user system identified by ***
character set al32utf8
national character set al16utf16
logfile
('/onlinelog/DB01/redo01a.dbf','/onlinelog/DB01/redo01b.dbf') size 32M REUSE,
('/onlinelog/DB01/redo02a.dbf','/onlinelog/DB01/redo02b.dbf') size 32M REUSE
extent management local
datafile '/data/DB01/cdb/system01.dbf' size 10M autoextend on
sysaux datafile '/data/DB01/cdb/sysaux01.dbf' size 10M autoextend on
default tablespace admin datafile '/data/DB01/cdb/admin01.dbf' size 10m autoextend on
extent management local autoallocate
default temporary tablespace temp
tempfile '/data/DB01/cdb/temp01.dbf' size 10M autoextend on
undo tablespace undotbs1 datafile '/data/DB01/cdb/undotbs1_01.dbf' size 10M autoextend on
set time_zone='Europe/Zurich'
ENABLE PLUGGABLE DATABASE
SEED FILE_NAME_CONVERT = ('/data/DB01/cdb/', '/data/DB01/seed/')
LOCAL UNDO ON
USER_DATA TABLESPACE users datafile '/data/DB01/cdb/users01.dbf' size 10m autoextend on;
perl catcon.pl -u sys/*** -d oh/rdbms/admin -b catalog_DB01 -e -l log catalog.sql
perl catcon.pl -u sys/*** -d oh/rdbms/admin -b catproc_DB01 -e -l log catproc.sql

   It’s a pretty cumbersome step. Maybe you need Oracle Text or maybe you have DEC character set… all those details must be engineered by the DBA. This is pretty insane Oracle doesn’t offer a tool to migrate to a non-deprecated architecture.
5. Plug-in the database
   
CREATE PLUGGABLE DATABASE DB01 USING '/save/DB01/DB01.xml' nocopy tempfile reuse;

   If you miss this step, Ô râge, Ô désespoir, you probably must restart from the beginning.
6. run noncdb_to_pdb
   
alter session set container=DB01;
alter pluggable database DB01 open;
alter session set container=DB01;
@?/rdbms/admin/noncdb_to_pdb.sql


Once you are so far, you’ll have all advantages of Multi-Tenant. PDB-Clone. PDB-Flashback. Lockdown profiles. And you’ll be able to consolidate if you buy the multitenant option. You’ll save money on hardware (SGA/CPU) to spent on software (Multitenant option).

Most importantly, you’ll have moved to a non-deprecated architecture

Conclusion: there is more than one way to move to pluggable. But there is no way to migrate to pluggable without creating a new database

Unix users don’t use this. Maybe some java developers do. But no Unix sysadmins. Never.

On Windows, things are getting more secure every release, especially if you pay attention to those details.

In Unix, if I have a script called “getdate” which shows me the date, I can copy it to another machine.


$ cat ./getdate
date
$ ./getdate
Mon Aug 20 13:05:40 CEST 2018

Works locally.


$ scp getdate srv02:
$ ssh srv02 ./getdate
Mon Aug 20 13:06:18 CEST 2018

Works on other servers.

This is a huge risk because anybody could modify anycode and you’ll never know.

Back to powershell.

On powershell, you can define policies.

Or disable policy because you do not want to sign your code.

> Set-ExecutionPolicy remotesigned

and if you are not admin

> Set-ExecutionPolicy -scope currentuser unrestricted


Until one day you find :

> Get-ExecutionPolicy -Scope MachinePolicy
AllSigned


what is the answer to life the universe and everything ?

Code signing. You go to your security admin, send him a certification request for code signing, import it in mmc, then sign your code. Your secadmin can show you how to the request with mmc. Or google it. It is not specific to powershell at all. It can be done with openssl as well I suppose.


> gc getdate.ps1
get-date
> .\getdate.ps1
.\getdate.ps1 : File C:\temp\getdate.ps1 cannot be loaded. The file C:\temp\getdate.ps1 is not digitally signed. You cannot run this script on the current system. For more information about running scripts and setting execution policy, see about_Execution_Policies at https:/go.microsoft.com
At line:1 char:1
+ .\getdate.ps1
+ ~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
> Set-AuthenticodeSignature getdate.ps1 (dir Cert:\CurrentUser\My\A232D77888B55318)[0]
> gc getdate.ps1
get-date
# SIG # Begin signature block
# MIITSQYJKoZIhvcNAQcCoIITOjC
# 9q4xO/0AczlLX5Zjjn3ByPNrAkkv
# 1GTsSZ9LkPUItDIpJZMk8nTzY4nI
# DUi0+XirQLiHiSB1hlhN/lVyMlyb
# vOdiHnCv9GMTMGsZbSjh/Q4lDIrX
# HIpaQH6BcIy8NAnnHw212dhqrJr7
# TqCHE8CYsvBFBs+9ZfD4zhUys1d
# SIG # End signature block
> .\getdate.ps1
Monday, August 20, 2018 1:22:00 PM
> Get-AuthenticodeSignature getdate.ps1
Directory: D:\temp
SignerCertificate Status Path
----------------- ------ -----------
A232D77888B55318B Valid getdate.ps1


If now I copy it to another server

I may get an error or a warning (depending on the policy)

> ./getdate.ps1

Do you want to run software from this untrusted publisher?
File C:\temp\getdate.ps1 is published by CN=srv01.example.com, OU=Example and is not trusted on your system.
Only run scripts from trusted publishers.
[V] Never run [D] Do not run [R] Run once [A] Always run [?] Help (default is "D"): R

Montag, 20. August 2018 13:29:43
>


if the code change, you get an Unauthorized access

> gc getdate.ps1
get-date -format U
# SIG # Begin signature block
# MIITSQYJKoZIhvcNAQcCoIITOjC
# 9q4xO/0AczlLX5Zjjn3ByPNrAkkv
# 1GTsSZ9LkPUItDIpJZMk8nTzY4nI
# DUi0+XirQLiHiSB1hlhN/lVyMlyb
# vOdiHnCv9GMTMGsZbSjh/Q4lDIrX
# HIpaQH6BcIy8NAnnHw212dhqrJr7
# TqCHE8CYsvBFBs+9ZfD4zhUys1d
# SIG # End signature block
> ./getdate.ps1
./getdate.ps1 : File C:\temp\getdate.ps1 cannot be loaded. The contents of file C:\temp\getdate.ps1 might have been
changed by an unauthorized user or process, because the hash of the file does not match the hash stored in the digital
signature. The script cannot run on the specified system. For more information, run Get-Help about_Signing..
At line:1 char:1
+ ./getdate.ps1
+ ~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
>


If you change code, you need to resign

> Set-AuthenticodeSignature getdate.ps1 (dir Cert:\CurrentUser\My\A232D77888B55318BE97E2AD7758EA0F0EA6C75B)[0]
> .\getdate.ps1
2018-08-20 13:35:00Z


I recently reported an issue regarding single tenant.

In old-time non-cdb, the SID used to be unique on a server. If you connect to srv01:port:sid, then you know where you connect.

Unfortunately, this is no longer true. If for instance you have two database sid’s S01 and S02 with a pluggable P01, and both run on the same server, chances exist you’ll get an invalid username / password or connect to the wrong datatabase.


$ lsnrctl services
LSNRCTL for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production on 13-JUL-2018 14:20:23

Copyright (c) 1991, 2017, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv01.example.com)(PORT=1521)))
Services Summary...
Service "P01.example.com" has 2 instance(s).
Instance "S01", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "S02", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
$ echo 'select instance_name from v$instance;'|sqlplus -s -L system/***@srv01.example.com:1521/p01.example.com
INSTANCE_NAME
----------------
S01
$ echo 'select instance_name from v$instance;'|sqlplus -s -L system/***@srv01.example.com:1521/p01.example.com
INSTANCE_NAME
----------------
S01
$ echo 'select instance_name from v$instance;'|sqlplus -s -L system/***@srv01.example.com:1521/p01.example.com
INSTANCE_NAME
----------------
S02


As demonstrated, it’s pretty random.

When copying database sid S01 to S02, it is no longer sufficient to rename the database (with NID or RMAN duplicate)

You can of course use DUPLICATE PLUGGABLE, but this means quite a bit of change in your procedures in place if you switch to single-tenant only because non-cdb is deprecated.

Otherwise, also if you use operating system commands + nid + rename datafile to copy your databases, you need to rename the global name.

This is as simple as

SQL> alter pluggable database P01 open restricted force;
Pluggable database altered.
SQL> alter session set container=P01;
Session altered.
SQL> alter pluggable database P01 rename global_name to P02;
Pluggable database altered.
SQL> alter pluggable database P02 open force;
Pluggable database altered.


DISCLAIMER: this apply mostly to environments with dba-scripts that were designed for non-cdbs. For more modern environments, use PLUG/UNPLUG/DUPLICATE PLUGGABLE…

Some Oracle documentation wants you to setup ssh with no password and no passphrase.

Configuring ssh

This is not really something your security admin will like.

First, using DSA, which is deprecated and disabled by default in OpenSSH 7.0, is a pretty dump instruction
OpenSSH 7.0 and greater similarly disable the ssh-dss (DSA) public key algorithm. It too is weak and we recommend against its use.
http://www.openssh.com/legacy.html
The two recommended key types are rsa and ecdsa. You should not use dsa

Second, ssh-key without passphrase is a huge security hole. If one get access to your key, for instance on a disk, a tape backup, etc, she’ll get access as oracle to all your database nodes. Best practice to use a pass phrase. Depending on your setup, it is sufficient to get ssh keys at installation/upgrade time only.

Third, providing interactive ssh-login as Oracle is against best practice for tracability. You better use SUDO or another elevation mechanism.

Let’s try:

First, use a recommended algoryhtm and key-length.
ssh-keygen -t rsa -b 4096
or
ssh-keygen -t ecdsa -b 521

Then, use a passphrase

Enter passphrase (empty for no passphrase): ***
Enter same passphrase again: ***


Then, when creating you authorized key, disable unwanted features, like pseudo terminal
~/.ssh/id_ecdsa

-----BEGIN EC PRIVATE KEY-----
AAAABBBBCCCC
-----END EC PRIVATE KEY-----


~/.ssh/id_ecdsa.pub

ecdsa-sha2-nistp521 AAAABBBB/cccc== oracle@srv001


~/.ssh/authorized_keys

no-agent-forwarding,no-port-forwarding,no-pty,no-user-rc,no-x11-forwarding ecdsa-sha2-nistp521 AAAABBBB/cccc== oracle@srv001


Also, you could deactivate some features on the client config
~/.ssh/config

ForwardX11=no
BatchMode=yes
ForwardAgent=no


This could also be done one the server sshd_config, but if you are not the sysadmin, don’t mess up with it.

Because you have a passphrase, you need to use an agent before starting your installation. Because pseudo-terminal (no-pty) is disabled, you cannot get a prompt. Because x11 is disabled (no-x11-forwarding), you cannot start an xterm

$ ssh srv002
Permission denied
$ eval $(ssh-agent)
Agent pid 12345
$ ps -fp 12345
UID PID PPID CMD
oracle 123451 0 ssh-agent
$ ssh-add ~/.ssh/id_ecdsa
Enter passphrase for ~/.ssh/id_ecdsa:
Identity added: ~/.ssh/id_ecdsa (~/.ssh/id_ecdsa)
$ ssh -t srv002
PTY allocation request failed on channel 0
$ ssh -Y srv002 aixterm
X11 forwarding request failed on channel 0
1363-008 X server named was not found.
$ ssh srv002 date
Fri Jul 13 12:50:22 CEST 2018


Those are basic steps to make your ssh less unsecure.

Whenever you select and describe in sqlplus it looks ugly

default: pagesize 14 linesize 80

change the default: it is often too large or too narrow

Let’s try WINDOW in sqlplus 18.1, which is available for download on Solaris / Linux / Windows

SQL> set lin window
SQL> sho lin
linesize 95 WINDOW
SQL> sho pages
pagesize 86


And look the result :

Almost perfect 🙂

I think it would nicer if DESC didn’t have this empty line

The cool thing with SET LINESIZE WINDOW is that it is dynamic (as I tested with CMD.EXE/windows and XTERM/Linux). Your window is too narrow, you make it bigger, re-run your select, it looks nicer

but… pagesize cannot be set to NON-WINDOW

SQL> set lin window
SQL> set pages 50000
SQL> sho pages
pagesize 21
SQL>