Laurent Schneider

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>


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

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.

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

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

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.

Itching to start playing with 18c?

Now you can on @oraclelivesql !

Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx

— Chris Saxon (@chrisrsaxon) February 17, 2018

The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function

Anthologic post of Anton : forums.oracle.com
Chris magic with Oracle 18c :
livesql.oracle.com

SQL> create table t1(x number primary key);
Table created.
SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

SQL> create table t2 as select * from t1;
Table created.
SQL> desc t2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                                NUMBER

The table T2 has the column X, but not the constraint (primary key / not null).

If you want to do a create table as select but want to keep index / constraints etc, then you rather use datapump

SQL> set autop on
SQL> var job_state varchar2(30)
SQL> declare
  n number;
begin
  n := DBMS_DATAPUMP.open('IMPORT','TABLE','DB01');
  DBMS_DATAPUMP.metadata_filter(n,'NAME_LIST','''T1''');
  DBMS_DATAPUMP.metadata_remap(n,'REMAP_TABLE','T1','T3');
  DBMS_DATAPUMP.start_job(n);
  DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state);
end;
/
PL/SQL procedure successfully completed.
JOB_STATE
--------------------------------------------------
COMPLETED
SQL> desc t3
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

The Table T3 is a copy of T1. DB01 is my implicit loopback database link (database name).

Let me today tell you, I now hate FBI for real.

Let’s start with an easy working example

SQL> CREATE TABLE t(x NUMBER PRIMARY KEY)
Table created.
SQL> insert into t(x) values (1)
1 row created.
SQL> insert into t(x) values (2)
1 row created.
SQL> insert into t(x) values (3)
1 row created.
SQL> commit
Commit complete.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN x * x;
  END;
Function created.
SQL> CREATE INDEX i
   ON t (f (x))
Index created.
SQL> select * from t where f(x)=4

         X
----------
         2
Execution Plan
------------------------------------------------
 0  SELECT STATEMENT 
 1 0  TABLE ACCESS BY INDEX ROWID BATCHED T
 2 1    INDEX RANGE SCAN I

Okay, this is a nice-working example. I can use where f(x)=4.

A non-fbi code would be something like

SQL> create or replace type tn as table of number;
Type created.
SQL> create or replace function f2(y number) 
  return tn deterministic is 
  begin
  if (y<0) then return null; end if;
  return tn (sqrt(y), -sqrt(y));
  end;
Function created.
SQL> select * from t where x  member of f2(4)

         X
----------
         2

Execution Plan
------------------------------------
 0   SELECT STATEMENT
 1  0  INDEX FULL SCAN SYS_C0026437

The reverse function is somehow more challenging to code, but the benefit is enormous, I have no more fbi.

What’s wrong with fbi?

First example : I recreate my function:

SQL> DROP FUNCTION f
Function dropped.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN power(x,2);
  END;
Function created.
SQL> select * from t where f(x)=4
*
Error at line 0
ORA-30554: function-based index I is disabled
SQL> SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_name IN ('F','I')

OBJECT_TYPE             OBJECT_NAME  STATUS 
----------------------- ------------ -------
INDEX                   I            VALID  
FUNCTION                F            VALID  
SQL> SELECT index_name,
       table_name,
       index_type,
       status,
       funcidx_status
  FROM user_indexes
 WHERE index_name = 'I'

INDEX TABLE INDEX_TYPE     STATUS FUNCIDX_STATUS
----- ----- -------------- ------ --------------
I         T FUNCTION-BASED VALID  DISABLED      

Remember this error. ORA-30554. And this not-so-well-known column, USER_INDEXES.FUNCIDX_STATUS. The behavior is pretty agressive, every object is valid, but you can no longer select from the table.

A small parenthese. We all know about unusable indexes. Index often get unusable due to partition maintenance and the like.

SQL> create table t2(x number)
Table created.
SQL> insert into t2 values (1)
1 row created.
SQL> create index i2 on t2(x) unusable
Index created.
SQL> SELECT index_name,
       table_name,
       status
  FROM user_indexes
 WHERE index_name = 'I2'

INDEX TABLE STATUS
----- ----- ---------
I2    T2    UNUSABLE 
SQL> insert into t2 values (2)
1 row created.
SQL> select * from t2 where x=2

         X
----------
         2

Execution Plan
---------------------------------
   0       SELECT STATEMENT
   1    0    TABLE ACCESS FULL T2

The index is not unused, but it prevents neither INSERT nor SELECT.

Let’s add a constraint

SQL> alter index i2 rebuild
Index altered.
SQL> alter table t2 add primary key (x)
Table altered.
SQL> alter index i2 unusable
Index altered.
SQL> insert into t2 values (2)
ORA-01502: index 'I2' or partition of such index is in unusable state
SQL> select * from t2 where x=2

         X
----------
         2

Okay, frequent readers may wonder why I did DROP FUNCTION and CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION.

Fine, let’s try.

SQL> CREATE or replace FUNCTION f (x NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   RETURN power(x,2);
END;
Function created.
SQL> alter index i rebuild
Index altered.
SQL> alter index i enable
Index altered.
SQL> select x, f(x) from t where f(x)=4

         X       F(X)
---------- ----------
         2          4
SQL> create or replace function f(x number) 
  return number deterministic is  
begin
  return 1;
end;
Function created.
SQL> select x, f(x), f(2) from t where f(x)=4

         X       F(X)       F(2)
---------- ---------- ----------
         2          4          1

Oh my goodness, select returns completly wrong result, but the index is valid and enabled.

There is more than way to solve this

1. rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies

SQL> select name from user_dependencies d 
  where referenced_type = 'FUNCTION' 
  and type = 'INDEX' and 
  (
    select last_ddl_time 
    from user_objects i 
    where i.object_name=d.name
  ) < (
    select last_ddl_time 
    from user_objects f 
    where f.object_name=d.referenced_name
  )
NAME
-----
I                                                                               
SQL> alter index i rebuild
Index altered.
SQL> select x, f(x), f(2) from t where f(x)=4
no rows selected.
SQL> select x, f(x), f(2) from t where f(x)=1

         X       F(X)       F(2)
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1

SQL> select name from user_dependencies d 
  where referenced_type = 'FUNCTION' 
  and type = 'INDEX' and 
  (
    select last_ddl_time 
    from user_objects i 
    where i.object_name=d.name
  ) < (
    select last_ddl_time 
    from user_objects f 
    where f.object_name=d.referenced_name
  )
no rows selected.

2. file an SR and encourage Oracle to test features before making them available
3. stop using FBI immediately

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.

CREATE FUNCTION 
  status (DIRECTORY_NAME VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF (DBMS_LOB.FILEEXISTS(
    BFILENAME (DIRECTORY_NAME, '.')) = 1)
  THEN
    RETURN 'VALID';
  ELSE
    RETURN 'INVALID';
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN SQLERRM;
END;
/

SELECT
  directory_name NAME,
  directory_path PATH,
  status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS   
---- ---- ---------
FOO  /foo INVALID  
TMP  /tmp VALID    
BAK  /u99 VALID    

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.

CREATE FUNCTION 
  status (DIRECTORY_NAME VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF (DBMS_LOB.FILEEXISTS(
    BFILENAME (DIRECTORY_NAME, '.')) = 1)
  THEN
    RETURN 'VALID';
  ELSE
    RETURN 'INVALID';
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN SQLERRM;
END;
/

SELECT
  directory_name NAME,
  directory_path PATH,
  status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS   
---- ---- ---------
FOO  /foo INVALID  
TMP  /tmp VALID    
BAK  /u99 VALID    

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba

CREATE FUNCTION dba_status
  (owner VARCHAR2, db_link VARCHAR2)
  RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  status   VARCHAR2 (4000);
BEGIN
  EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
  EXECUTE IMMEDIATE
    'begin :1 := "' || owner 
    ||'".status(''' || db_link || '''); end;'
    USING OUT status;
  EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
  COMMIT;
  RETURN status;
END;
/

SELECT 
  owner, db_link, dba_status (owner, db_link) 
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)                                   
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba

CREATE FUNCTION dba_status
  (owner VARCHAR2, db_link VARCHAR2)
  RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  status   VARCHAR2 (4000);
BEGIN
  EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
  EXECUTE IMMEDIATE
    'begin :1 := "' || owner 
    ||'".status(''' || db_link || '''); end;'
    USING OUT status;
  EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
  COMMIT;
  RETURN status;
END;
/

SELECT 
  owner, db_link, dba_status (owner, db_link) 
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)                                   
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

If one database link is invalid, you cannot select through it

SQL> select * from dual@z;
select * from dual@z
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.

create function status(db_link varchar2) 
return varchar2 is 
  x number;
begin
  execute immediate 'select 1 from dual@"'||
        DB_LINK||
        '"' into x;
  return 'OK';
exception
  when others then return sqlerrm;
end;
/

select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

If one database link is invalid, you cannot select through it

SQL> select * from dual@z;
select * from dual@z
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.

create function status(db_link varchar2) 
return varchar2 is 
  x number;
begin
  execute immediate 'select 1 from dual@"'||
        DB_LINK||
        '"' into x;
  return 'OK';
exception
  when others then return sqlerrm;
end;
/

select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}}

— laurentsch (@laurentsch) September 19, 2017

As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target

$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.

$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout

emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, powershell.

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}}

— laurentsch (@laurentsch) September 19, 2017

As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target

$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.

$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout

emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, powershell.