Irrecoverable part III : a fix

After part I:the problem, Part II: two reports here is part III.

We have backed up archivelog during a full and deleted them before the full completed.


RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
15 4.00K  DISK 00:00:00   13:31:08
    BP Key: 15   Status: AVAILABLE
    Piece Name: /FULL/0fq7gc0s_1_1

  List of Archived Logs in backup set 15
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  15  355533  13:29:55 355777  13:31:08

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
25 4.00K  DISK 00:00:00   13:31:26
    BP Key: 25   Status: AVAILABLE
    Piece Name: /ARCH/0pq7gc1e_1_1

  List of Archived Logs in backup set 25
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  15  355533  13:29:55 355777  13:31:08

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
26 3.00K  DISK 00:00:00 13:31:26
  BP Key: 26   Status: AVAILABLE
  Piece Name: /ARCH/0qq7gc1e_1_1

  List of Archived Logs in backup set 26
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  16  355777  13:31:08 355827  13:31:22

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
28 2.50K  DISK 00:00:00 13:31:28
    BP Key: 28   Status: AVAILABLE
    Piece Name: /FULL/0sq7gc1g_1_1

  List of Archived Logs in backup set 28
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  17  355827  13:31:22 355863  13:31:28

Sequence 16 is missing in /FULL/ and is available in /ARCH/

Ok, let’s copy the missing pieces from ARCH to FULL


$ cd /
$ cp -p ARCH/* FULL/

Now let’s try. I save my directory on an offline location (tape/usb/cdrom) and dropped my database including backups.


cd /
tar cvf /tmp/FULL.tar FULL

Let’s drop (it’s just a test)


RMAN> startup force mount dba 
RMAN> drop database including backups;
database dropped

Ok let’s get this USB stick back from the moon to my datacenter and see if I can restore …


cd /
tar xvf /tmp/FULL.tar 

Let’s do this !


RMAN> startup force nomount
RMAN> restore controlfile from '/FULL/c-2414975447-20150521-01';
RMAN> alter database mount;

Now I want RMAN to look for other files in FULL and also marked expired stuff. The start with does the magic.

RMAN> catalog start with '/FULL/' noprompt;
RMAN> crosscheck backup;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

As @dbastorage said on twitter:
“if it was easy, we would not need DBAs! ”

Irrecoverable full backup part II : reporting

After my post Can you restore from a full online backup ?, I needed to come up with a report.

Assuming that each backup goes in a different directory, I just wrote two reports.

  1. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog)
    
    DIR     FIRST_CHANGE# NEXT_CHANGE#
    ------- ------------- ------------
    /bck01/        284891       285140
    /bck01/        285140       285178
    
    /bck02/        284891       285140
    === GAP ===
    /bck02/        285178       285245 
    
    /bck03/        285178       285245
    /bck03/        285245       286931
    /bck03/        286931       287803
    /bck03/        287803       288148
    

    This could be done with analytics, by checking where the last next_change is not the current first_change, within a directory

    
    SELECT dir, 
      LAG missing_from_change#, 
      first_change# missing_to_change#
    FROM (
      SELECT REGEXP_REPLACE (handle, '[^/\]+$') dir,
        first_change#,
        next_change#,
        LAG(next_change#) OVER (
          PARTITION BY REGEXP_REPLACE (handle, '[^/\]+$')
          ORDER BY first_change#
        ) LAG
      FROM v$backup_piece p
      JOIN v$backup_redolog l 
        USING (set_stamp, set_count))
    WHERE LAG != first_change#;
    
    DIR     MISSING_FROM_CHANGE# MISSING_TO_CHANGE#
    ------- -------------------- ------------------
    /bck02/               285140             285178
    
  2. Reports directories where archivelogs don’t include changes (backup redolog) from the earliest to the latest checkpoint (backup datafile)
    
    SELECT 
      REGEXP_REPLACE (handle, '[^/\]+$') dir,
      MIN (checkpoint_change#),
      MAX (checkpoint_change#),
      MIN (first_change#),
      MAX (next_change#)
    FROM v$backup_piece p
      LEFT JOIN v$backup_datafile f 
        USING (set_stamp, set_count)
      LEFT JOIN v$backup_redolog l 
        USING (set_stamp, set_count)
    WHERE handle IS NOT NULL
    HAVING 
      MIN (checkpoint_change#) < MIN (first_change#)
      OR 
      MAX (checkpoint_change#) > MAX (next_change#)
    GROUP BY REGEXP_REPLACE (handle, '[^/\]+$');
    
    DIR     MINCHECKPT MAXCHECKPT   MINFIRST    MAXNEXT
    ------- ---------- ---------- ---------- ----------
    /bck04/     954292     954299     959487    1145473
    

    the archives for the changes from 954292 to 959487 are missing.

If some archive backups are missing in one directory, it does not mean the database is irrecoverable, the archive backups could be in another directory. But it means that single directory would no longer permit you to restore or duplicate.

Another approach with RESTORE PREVIEW was provided by Franck in my previous post : List all RMAN backups that are needed to recover.

Usual disclaimer: there are plenty of other irrecoverabilty causes from hardware defect to backup “optimization” that are beyond the scope of this post.

Can you restore from a full online backup ?

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT !

A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG:


backup database format
'/u99/backup/DB01/20150518/full_0_%d_s%s_p%p'
plus archivelog format
'/u99/backup/DB01/20150518/arc_%d_s%s_p%p';

Starting backup at 2015-05-18_18:27:55
current log archived
input archived log thread=1 sequence=469
...
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s86_p1
Finished backup at 2015-05-18_18:27:58

Starting backup at 2015-05-18_18:27:58
input datafile file number=00002 name=
/u02/oradata/DB01/undots01.dbf
...
including current control file in backup set
including current SPFILE in backup set
piece handle=
/u99/backup/DB01/20150518/full_0_DB01_s88_p1
Finished backup at 2015-05-18_18:28:16

Starting backup at 2015-05-18_18:28:16
current log archived
input archived log thread=1 sequence=20
piece handle=
/u99/backup/DB01/20150518/arc_DB01_s89_p1
Finished backup at 2015-05-18_18:28:17

This sounds pretty safe, but what happened if you keep this full backup for ever ? do you have all what you need in it to restore ?

It depends. Chance exists that you can restore. To annoy the paranoiacs, here is a counter example.

  1. you start your full backup at 6pm.
    backuping sequence 21,22,23,24
    backup datafiles…

  2. at 7 pm one cronjob issue backup archivelog all delete input;
    backuping and deleting sequence 21,22,23,24,25,26,27,28,29,30

  3. at 8pm your backup is about to finish
    backuping sequence 31

Well, where is my sequence 27 ?

Let’s try


rm /tmp/arch_DB01*
startup force mount;
crosscheck backup;restore database;


Starting restore at 2015-05-18_18:47:45
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 2015-05-18_18:49:51

Ok, the restore was fine. Now what?


RMAN&gt; recover database;

Starting recover at 2015-05-18_18:50:35
using channel ORA_DISK_1

starting media recovery

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 05/18/2015 18:50:36
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 30
RMAN-06025: no backup of archived log for thread 1 with sequence 29
RMAN-06025: no backup of archived log for thread 1 with sequence 27

RMAN&gt; alter database open;

RMAN-00571: =================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =================================================
RMAN-03002: failure of alter db command at 05/18/2015 18:51:29
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/DB01/system01.dbf'

You have lost your database. Even if you did a full online backup plus archivelog, it did not contain all the archivelogs and RMAN did not tell you.

There is more than one way to reduce this annoyance :

1) don’t backup archivelogs during full backup
2) don’t delete archivelog backups done during a full backup
3) report any archivelog backups that run during a full. Either by looking in the RMAN dictionary or in your house logfiles
4) do offline backup for long term archives
5) don’t trust RMAN

And as well :
Test your backups 😀

🎂 10 years 🎂

Thanks to all my readers for being so faithful :)
I’ll post a new solution to calculate factorial.

This is the one I posted 10 years ago :
http://laurentschneider.com/wordpress/2005/05/recursive-sql.html

I also used it in the obfuscation contest


with function f (x number) return number 
is 
begin 
  return case x when 1 then x else x*f(x-1) end; 
end;
select value(t), f(value(t)) 
from table(sys.odcinumberlist(4,6))t

  VALUE(T) F(VALUE(T))
---------- -----------
         4          24
         6         720

It is neither quicker nor shorter than the one I posted in 2005, but it could not have worked in 2005

run sudo, ssh, password, su in simulated interactive mode

Some commands do not like non-interactive mode


$ passwd <<EOF
> oldpassword
> newpassword
> newpassword
> EOF
Changing password for user lsc.
Current password for lsc@example.com: passwd: Authentication token manipulation error
$ echo oraclepassword | su - oracle
standard in must be a tty
$ echo sudopassword | sudo su - oracle
[sudo] password for lsc:
sudo: sorry, you must have a tty to run sudo

But ok, if you really want to run those in a script, you have plenty of clean (no hack there) ways of doing this.

For instance, let’s use a screen called BAR.


$ xterm -e "screen -S BAR" &
[1]     31732

Now we have an interactive terminal. It could be redirected to a frame buffer device if no x server is started.

Not really a human device, but an interactive terminal.

Now let’s send stuff


$ CR="$(echo '\r')"
$ screen -S BAR -X stuff "sudo su - oracle$CR"
$ screen -S BAR -X stuff "sudopassword$CR"
$ screen -S BAR -X stuff "id > /tmp/xxx$CR"
$ screen -S BAR -X stuff "exit$CR"
$ screen -S BAR -X stuff "exit$CR"
[1] +  Done                    xterm -e "screen -S BAR" &
$ cat /tmp/xxx
uid=100(oracle) gid=100(dba) groups=100(dba)

Usual disclaimer: it is a bad security practice to hardcode your passwords in scripts. Use this only if you really understand security. Read man openssl about how to use openssl to encrypt your password. Ask your security friends before trying

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba.

Test case (21 is a not something to do in real life):


SQL> alter system set processes=21 scope=spfile;
System altered.
SQL> startup force quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> quit

From now on, sqlplus as sysdba is impossible.

$ sqlplus -s -L / as sysdba
ERROR:
ORA-00020: maximum number of processes (40) exceeded
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

Okay, if you really really need to run one script, you could connect with sqlplus -prelim and restart the database.


if :|sqlplus / as sysdba|grep ORA-00020
then
  echo shu abort|sqlplus -prelim / as sysdba
  echo startup quiet|sqlplus / as sysdba
fi

If ORA-20 is detected, then the database will be restarted.

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo.

By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user.


SQL> ALTER USER app_user PROXY ONLY CONNECT;
SQL> CONNECT app_user/xyz
ERROR:ORA-28058: login is allowed only through a proxy

comment in external table

Depending the files, you may use different signs for comments, typically


# hash
// slash slash 
/* slash-star star-slash */
: column
-- dash dash

The latest is used in sql and pl/sql, but :


CREATE TABLE t (x NUMBER)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_pump_dir
  ACCESS PARAMETERS (
    FIELDS TERMINATED BY ';'  -- This is a comment
    (x))
  LOCATION ('x'));

SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: 
  "column, enclosed, (, ltrim, lrtrim, ldrtrim, missing, 
  notrim, optionally, rtrim, reject"
KUP-01007: at line 2 column 38

not in external table access parameters.

No comment is allowed there!

How to convert Excel file to csv

One-liner to convert Excel to CSV (or to and from any other format).
There is a bug 320369 if you have excel in English and your locale is not America. Just change your settings to us_en before conversion.

Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable.

This boils down to segment reorganisation, not dictionary change.

For instance :


CREATE TABLE t(x NUMBER)
PARTITION BY RANGE(x)
(PARTITION p1 VALUES LESS THAN (MAXVALUE));

CREATE INDEX i ON t (x);


INSERT INTO t VALUES (1);

ALTER TABLE T SPLIT PARTITION p1 AT 
  (100) INTO (PARTITION p2, PARTITION p3);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';

INDEX_NAME STATUS  
---------- --------
I          VALID

The partition p1 is splitted into two p2 and p3, all rows from p1 belongs to p2, p3 is created empty, no data manipulation, the index remains valid


delete from t; 
INSERT INTO t VALUES (250);

ALTER TABLE T SPLIT PARTITION p3 AT 
  (200) INTO (PARTITION p4, PARTITION p5);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';

INDEX_NAME STATUS  
---------- --------
I          VALID

Same here, all rows from p3 moved to p5, p4 is created empty, no problem


delete from t; 
INSERT INTO t VALUES (250);
INSERT INTO t VALUES (350);

ALTER TABLE T SPLIT PARTITION p5 AT 
  (300) INTO (PARTITION p6, PARTITION p7);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';
INDEX_NAME STATUS  
---------- --------
I          UNUSABLE

One row goes to p6, one row to p7, the index is invalidated.

To avoid this, use ALTER TABLE T SPLIT PARTITION p5 AT (300) INTO (PARTITION p6, PARTITION p7) UPDATE INDEXES;

What are the consequences?


INSERT INTO t VALUES (320);
1 row inserted

It does not prevent DML in this case.


select * from t where x=320;

         X
----------
       320

Execution Plan
---------------------------------------------------
 0   SELECT STATEMENT Optimizer Mode=ALL_ROWS
 1 0   PARTITION RANGE SINGLE
 2 1     TABLE ACCESS FULL SCOTT.T

The row is retrieved, but the unusable index is not used.

In some case, DML may be prevented.


alter index i rebuild;
alter table t modify (x primary key using index);
ALTER TABLE T SPLIT PARTITION p7 AT 
  (330) INTO (PARTITION p8, PARTITION p9) ;

insert into t values (450);
ORA-01502: index 'SCOTT.I' or partition of such index is in unusable state

The index can no longer be used for constraint enforcement and the INSERT fails.

If the index is partitioned, the index is not marked as unusable as a whole, only the affected (sub)partitions are marked.

Check all your indexes status in dba_indexes.status, dba_ind_partitions.status and dba_ind_subpartitions.status.

Rebuild them with alter index i rebuild, alter index i rebuild partition p and alter index i rebuild subpartition sp.

Did you forget to run root.sh?

Not easy to detect, and depending on the product (agent/database), it may have only limited side effects.

Like external jobs not running, operating systems statistics not collected.

But it is not always easy to diagnose.

For instance if you patch from OMS 12cR2 to 12cR3, and you run the root.sh only in 12cR2, they are very few statistics missing (one is the OS_STORAGE_ENTITY).

Running the root.sh doesn’t generate a log file or an entry in the inventory.

To check if it was executed, check what it is supposed to do. It is a bit different in each version. One think it always does is changing the ownership to root and set the sticky bit for a few binaries. For the database, this is done in sub-scripts called rootadd.sh (10g) or rootadd_rdbms.sh (11g/12c).


eval ls -l $(find $ORACLE_HOME -name "rootadd*sh" -exec awk '$1="$CHOWN"&&$2=="root"{print $3}' {} \;|sort -u)

-rwsr-x--- root dba .../product/11.2.0/db_4/bin/extjob
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/jssu
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmb
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmhs
-rws--x--- root dba .../product/11.2.0/db_4/bin/nmo
-rwsr-x--- root dba .../product/11.2.0/db_4/bin/oradism
-rw-r----- root dba ...11.2.0/db_4/rdbms/admin/externaljob.ora

If the ownership is root, you definitely did run the root.sh.

On the 12c agent, there is a FULL_BINARY_LIST variable that point to list of root binaries in sbin


eval $(grep FULL_BINARY_LIST= $AGENT_HOME/root.sh)
cd $AGENT_HOME/../../sbin
ls -l $FULL_BINARY_LIST

-rws--x--- root dba nmb
-rws--x--- root dba nmhs
-rws--x--- root dba nmo

If all files exist and belong root, it looks like you did run the root.sh.

last partition

if you really need to quickly find the latest partition per table, I have written this little gem


WITH FUNCTION d (b BLOB, len number) RETURN DATE IS
  d DATE;
BEGIN
  IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw('07') and len=83
  THEN
    DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d);
  ELSE
    d := NULL;
  END IF;
  RETURN d;
END;
SELECT 
  u.name owner,
  o.name table_name,
  max(d (bhiboundval, hiboundlen)) last_partition
FROM sys.tabpart$ tp
  JOIN sys.obj$ o USING (obj#)
  JOIN sys.user$ u ON u.user# = o.owner#
WHERE u.name='SCOTT'
group by u.name, o.name
order by last_partition desc;

It doesn’t cover all partitioning type, but it is pretty fast and simple

Do you have a partition in 2015

You need to check the high_value from dba_tab_partitions.
Or you you could metadata.

With metadata, it is not a long, it either clob or clob-xml.



  SELECT t.table_name,
         MAX (
            TO_DATE (
               REGEXP_SUBSTR (
                  EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
                  ' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
               'YYYY-MM-DD HH24:MI;:SS'))
            high_value
    FROM user_tables t,
         TABLE (
            XMLSEQUENCE (
               EXTRACT (
                  xmltype (DBMS_METADATA.get_xml ('TABLE', t.table_name)),
                  '//ROWSET/ROW/TABLE_T/PART_OBJ/PART_LIST/PART_LIST_ITEM/HIBOUNDVAL'))) x
   WHERE partitioned = 'YES'
  HAVING MAX (
            TO_DATE (
               REGEXP_SUBSTR (
                  EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
                  ' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
               'YYYY-MM-DD HH24:MI;:SS')) < DATE '2015-01-02'
GROUP BY t.table_name
ORDER BY 1;

TABLE_NAME HIGH_VALUE                    
---------- ------------------------------
T1         2015-01-01                    
T11        2000-01-01                    
T20        1436-03-09                    
T6         2000-01-01                    
T7         2014-12-21                    
T8         2015-01-01

Ok, out of xml, I get all HIBOUNDVAL and hazardously try some regular expression to find a pattern.

It already returned most of my table that could have caused me New Eve headache. But partitioned is not that simple. Line 3 for instance is not in a gregorian format (it’s a bug). And it does not cover index partition, subpartitions, interval partitions, neither partition with more than one date column as key.

This sounds a lot, and there is only one HIGH_VALUE for multiple key, and the High_value is something like 0,TO_DATE(' 3543-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),MAXVALUE

Okay, if you forget about non-gregorian calendars, and ignore about MAXVALUE, we could use something like


curid := DBMS_SQL.open_cursor;
txt :=
  'select '
  || REPLACE (g.high_value, 'MAXVALUE', 'NULL')
  || ' from dual';
DBMS_SQL.parse (curid, txt, DBMS_SQL.NATIVE);

Then from dba_PART_KEY_COLUMNS (pkc) or dba_SUBPART_KEY_COLUMNS, joined with dba_tab_columns, we could retrieve the datatype (date and timestamp with or without time zone) and the pkc.column_position.


DBMS_SQL.DEFINE_COLUMN (curid,
  f.column_position,
  'date',
  12);

Casting to date should be fine.

Then we fetch and retrieve the Nth column


IF DBMS_SQL.FETCH_ROWS (curid) > 0
THEN
  DBMS_SQL.COLUMN_VALUE (curid, f.column_position, namevar);
END IF;

If you read me that far, you probably can write the rest of the code for yourself.

Could be useful to do this before end-of-year 😉

SSL with PKCS12 truststore

Many many moons ago I vaguely remember having a similar issue with java keystore / truststore and microsoft certificates stores.

When you start using SSL for your listener, you could potentially face a large number of issues amoung your toolsets. In my opinion, the most disastrous one is that you cannot monitor your database with Enterprise Manager and a TCPS listener. I tried with 10g, 11g, 12c and I seriously doubt it will come in 13c, even a dozen of ERs have been filled. The best workaround I found is to use a separate listener to monitor your database and monitor the ssl-listener itself with IPC.

Today I had to deal with a driver from Datadirect, which finally works perfectly fine with SSL, but the challenge was to know what to put in the keystore and truststore…

In SQLNET, you use the single-sign-on wallet (cwallet.sso) created by OWM/orapki or ldap.

In Java, per default, you use a java keystore, that you generate with keytool (or even use the default cacerts). There is only a lexical difference between a keystore and a truststore, they could both be the same file. As documented in the JSSE Ref
A truststore is a keystore that is used when making decisions about what to trust

But for some other tools, the java keytool won’t do the trick, if the truststore cannot be of the type JKS.

One common type is the PKCS12. This is the your ewallet.p12 you get with the Wallet Manager.

E.g. from java :


  -Djavax.net.ssl.trustStore=ewallet.p12
  -Djavax.net.ssl.trustStoreType=PKCS12
  -Djavax.net.ssl.trustStorePassword=***

To use single-sign-on, use trustStoreType=SSO as I wrote there : jdbc-ssl

Other command formats are X509 base64 or DER file. The openssl command line allows you easy conversion


openssl pkcs12 -in ewallet.p12 -out file.pem
openssl x509 -outform der -in file.pem -out file.der

or in Windows Explorer, just click on your p12 file and then click on the certificate to export in the certificate store.

anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication.

It works pretty fine until 11.2.0.2, I wrote multiple posts on ssl.

You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default wallet will do.


orapki wallet create -wallet . -auto_login_only

sqlnet.ora

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=.)))
ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)
NAMES.DIRECTORY_PATH=(TNSNAMES)

tnsnames.ora

DB01=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=srv01.example.com)(PORT=1521))(CONNECT_DATA=(SID=DB01)))

or if you use java, the default truststore -usually located in $JAVA_HOME/jre/lib/security/cacerts, will also do.


    System.setProperty("oracle.net.ssl_cipher_suites", "SSL_DH_anon_WITH_DES_CBC_SHA");

On some plateform however you may get something like : IBM’s Client TrustManager does not allow anonymous cipher suites.

So far so good, but if you upgrade your listener to 11.2.0.3/4 or 12c, the anonymous suites won’t be accepted if not explicitely set up in sqlnet.ora. This is documented in Note 1434966.1

You will get something like “ORA-28860: Fatal SSL error”, “TNS-12560: TNS:protocol adapter error” in Oracle or “SSLHandshakeException: Received fatal alert: handshake_failure”, “SQLRecoverableException: I/O-Error: Received fatal alert: handshake_failure” in java.

There are two -obvious- ways to fix this. The preferred approach is to not use anonymous suite (they seem to have disappeared from the supported cypher suites in the doc).

For this task, you use another cipher suite. The easiest way is to not specify any or just use one like TLS_RSA_WITH_AES_128_CBC_SHA (java) / SSL_RSA_WITH_AES_128_CBC_SHA (sqlnet). Even if you do not use client authentication, you will then have to authenticate the server, and import the root ca in the wallet or the keystore.
sqlnet.ora


# comment out ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)

java

// comment out : System.setProperty("oracle.net.ssl_cipher_suites", "SSL_DH_anon_WITH_DES_CBC_SHA");
System.setProperty("javax.net.ssl.trustStore","keystore.jks");
System.setProperty("javax.net.ssl.trustStoreType","JKS");
System.setProperty("javax.net.ssl.trustStorePassword","***");

Or, as documented in metalink, define the suite in sqlnet.ora and listener.ora if you use 11.2.0.3 or 11.2.0.4.

How to *really* send a script to the background

Let’s check this small script

foo.sh


#!/bin/sh
echo foo.1:`date` | tee $HOME/tmp/foo.txt
sleep 3
echo foo.2:`date` | tee -a $HOME/tmp/foo.txt


$ $HOME/tmp/foo.sh
foo.1:Thu Nov 27 17:34:53 CET 2014
foo.2:Thu Nov 27 17:34:56 CET 2014

Very obvious, I write to the console, wait three seconds, then write to the console.

Ok, let’s take another script that would call this script in the background using &

bar.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh &
echo bar.2:`date`


$ $HOME/tmp/bar.sh
bar.1:Thu Nov 27 17:36:32 CET 2014
bar.2:Thu Nov 27 17:36:32 CET 2014
$ 
foo.1:Thu Nov 27 17:36:32 CET 2014
foo.2:Thu Nov 27 17:36:35 CET 2014

bar is printing the date, calling foo in the background, then printing the date, then it returns to you, and foo is still running.

BUT this is only in a relative background …

Let’s try this


$ time $HOME/tmp/bar.sh > /dev/null

real    0m0.01s
user    0m0.00s
sys     0m0.00s

So it takes no time to run bar you believe ?

Let’s try, for instance, over ssh (or cron or whatever)


$ time ssh localhost $HOME/tmp/bar.sh > /dev/null
real    0m3.81s
user    0m0.01s
sys     0m0.01s

running bar suddenly waits 3 seconds for foo to finish.

To be sure the script is sent to the farest background, you need to close the file descriptors, stdin, stdout, stderr

I rewrote it as

baz.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh <&- >&- 2>&- &
echo bar.2:`date`


$ time ssh localhost $HOME/tmp/baz.sh >/dev/null
real    0m0.44s
user    0m0.00s
sys     0m0.00s

Now the script baz is immediately finished and does not wait for foo to complete

KeepAlive socket in 12c listener

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes).

12.1.0.2 on AIX

just released today http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

only in Enterprise Edition at the moment, and now available on HPUX, zLinux and AIX

This is the first and last patchset for 12cR1

one more stragg


select
  to_char(
    sum(
      power(100,rownum-1)*
      deptno
    ),
    'FM99G99G99G99G99', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptlist 
from dept

DEPTLIST       
---------------
40;30;20;10

I also wrote about distinct listagg. The same applies for sum distinct.


select 
  to_char(
    sum(power(1e3,d-1)*deptno),
    'FM999G999G999', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptsum, 
  to_char(
    sum(distinct power(1e2,d-1)*deptno),
    'FM99G99G99', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptsumdist, 
  to_char(
    sum(power(1e1,d-1)),
    'FM9G9G9', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptcount, 
  to_char(
    sum(power(1e4,c-1)*comm),
    'FM9999G9999G9999G9999G9999', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) commlist 
from (
  select comm, deptno, 
    dense_rank() over (order by deptno) d, 
    dense_rank() over (order by comm) c 
  from emp);   

DEPTSUM      DSUMDIST COUNT COMMLIST
------------ -------- ----- -------------------
180;100;030  30;20;10 6;5;3 1400;0500;0300;0000

xml to csv in powershell

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull.

Let’s take a file called emp.xml


<EMPTABLE>
  <DEPT>
    <DEPTNO>10</DEPTNO>            
    <EMPLIST>
      <ENAME>CLARK</ENAME>
      <ENAME>MILLER</ENAME>
      <ENAME>KING</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>20</DEPTNO>
    <EMPLIST>
       <ENAME>SMITH</ENAME>
       <ENAME>FORD</ENAME>
       <ENAME>ADAMS</ENAME>
       <ENAME>SCOTT</ENAME>
       <ENAME>JONES</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>30</DEPTNO>     
    <EMPLIST>
       <ENAME>ALLEN</ENAME>
       <ENAME>WARD</ENAME>
       <ENAME>MARTIN</ENAME>
       <ENAME>BLAKE</ENAME>
       <ENAME>TURNER</ENAME>
       <ENAME>JAMES</ENAME>
    </EMPLIST>
  </DEPT>
</EMPTABLE>

To get all employees, it is awfully easy
([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME


CLARK
MILLER
KING
SMITH
FORD
ADAMS
SCOTT
JONES
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

Now I actually want to have each employee together with his department. I create an object for each department add the ename and the deptno


$d=([xml](gc emp.xml)).EMPTABLE.DEPT | % { 
  foreach ($i in $_.EMPLIST.ENAME) {
    $o = New-Object Object
    Add-Member -InputObject $o -MemberType NoteProperty -Name DEPTNO -Value $_.DEPTNO
    Add-Member -InputObject $o -MemberType NoteProperty -Name ENAME -Value $i
    $o
  }
}
$d


DEPTNO     ENAME
------     -----
10         CLARK
10         MILLER
10         KING
20         SMITH
20         FORD
20         ADAMS
20         SCOTT
20         JONES
30         ALLEN
30         WARD
30         MARTIN
30         BLAKE
30         TURNER
30         JAMES

This could be convert to multiple format.

HTML
$d|ConvertTo-HTML

DEPTNO ENAME
10 CLARK
10 MILLER
10 KING
20 SMITH
20 FORD
20 ADAMS
20 SCOTT
20 JONES
30 ALLEN
30 WARD
30 MARTIN
30 BLAKE
30 TURNER
30 JAMES

CSV
$d|ConvertTo-CSV


"DEPTNO","ENAME"
"10","CLARK"
"10","MILLER"
"10","KING"
"20","SMITH"
"20","FORD"
"20","ADAMS"
"20","SCOTT"
"20","JONES"
"30","ALLEN"
"30","WARD"
"30","MARTIN"
"30","BLAKE"
"30","TURNER"
"30","JAMES"

JSON
$d|ConvertTo-JSon


[
    {
        "DEPTNO":  "10",
        "ENAME":  "CLARK"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "MILLER"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "KING"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SMITH"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "FORD"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "ADAMS"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SCOTT"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "JONES"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "ALLEN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "WARD"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "MARTIN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "BLAKE"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "TURNER"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "JAMES"
    }
]

Or even to xml with ($d|ConvertTo-XML).OuterXml

It is so lightening fast that you could process pretty large files (millions of lines) in just a few seconds

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.PORT);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/
GRANT EXECUTE ON pw_function_ad TO PUBLIC;
CREATE PROFILE AD LIMIT 
  PASSWORD_VERIFY_FUNCTION pw_function_AD;
ALTER PROFILE AD LIMIT 
  PASSWORD_LIFE_TIME 30;
ALTER PROFILE AD LIMIT 
  PASSWORD_REUSE_MAX UNLIMITED;

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ERROR:
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed
Connected.

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.SSL_PORT);
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory


PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database


SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';

PTIME
-------------------
2014-11-10_10:33:08

If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

rowid of the last insert

If you look for the last insert result, check returning into.

Is it identity column, the rowid, any expression, get it back


SQL> var r varchar2(24)
SQL> var x number
SQL> var d varchar2(30)
SQL> insert into t values (default) 
  returning rowid,x,sysdate into :r,:x,:d;

1 row created.

SQL> print

R
--------------------
AAAaFTAAIAAAAILAAD

         X
----------
         6

D
--------------------------------
2014-11-07_13:33:03

It is documented in the SQL Reference. No need for PL/SQL here. I it very usefull if you use sequences too.


SQL> insert into t(x) values (s.nextval) returning x into :x;

1 row created.

SQL> print x

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

PLS-00201 in stored procedures

When you grant table access thru a role, you cannot use that role in a stored procedure or view.


create role r;

create user u1 identified by ***;
grant create procedure, create session to u1;

create user u2 identified by ***;
grant create procedure, create session, r to u2;

conn u1/***
create procedure u1.p1 is begin null; end; 
/

grant execute on u1.p1 to r;

conn u2/***

create procedure u2.p2 is begin u1.p1; end; 
/

sho err procedure u2.p2

Errors for PROCEDURE U2.P2:

L/COL ERROR
----- -------------------------------------------
1/26  PL/SQL: Statement ignored
1/26  PLS-201: identifier U1.P1 must be declared

However, If i run it in an anonymous block, it works


declare
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin
  p2; 
end; 
/

PL/SQL procedure successfully completed.

But this only works when my role is active. If my role is no longer active, then it obviously fails.


set role none;

declare 
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin 
  p2; 
end; 
/
ERROR at line 1:
ORA-06550: line 4, column 5:
PLS-00201: identifier 'U1.P1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

It is all written in the doc,

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights

I knew the behavior but not the reason behind it. Thanks to Bryn for bringing me so much knowledge on plsql.

to R1 or to R2

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to 12.1.0.2 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant or in Memory.

12.1.0.2, which is not available on AIX yet, will be the last patchset of 12cR1. It is the first and only patchset for that release. It is actually more than a patchset, as it introduced in memory database and JSON in the database.

The next release is expected beginning of 2016 on Linux. 11.2.0.4 patching ends January 2018.

Should I I go to an already aborted release or should I buy extended support for 11.2.0.4 until 2018 ?

Probably I will go both ways, depending on the applications.

select pdf from sqlplus

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd


sqlplus -s scott/tiger <<EOF |xxd -p -r >doc.pdf
set pages 0 lin 17000 long 1000000000 longc 16384
select document from emp where ename=user;
EOF

Obviously, it could also be a sound, a video or an image !

select a.b.c.d.e from t a

I just learnt a new syntax. With 12.1.0.2, you can dive into your JSON document within sql, it looks a bit unusual to me


SQL> CREATE TABLE t
  (b CLOB CHECK (b IS JSON));
Table created.

SQL> insert into t(b) values 
  ('{"c":{"d":{"e":1}}}');
1 row created.

SQL> select a.b.c.d.e from t a;
C
----------------------
1

Largely inspired from Tom Kyte session

Oracle on Windows

@mkratoch is speaking at 10am UGF2633: Managing Oracle 12c on Windows

Again, 12cR1ps1 came before AIX and HPUX.


SQL> select dbms_utility.port_string, version 
  from v$instance;

PORT_STRING          VERSION         
-------------------- -----------------
IBMPC/WIN_NT64-9.1.0 12.1.0.2.0        

import into UTF8 database

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column.

The root cause is the default semantics in a database being BYTE


SQL> select VALUE, ISDEFAULT 
  from v$parameter 
  where NAME='nls_length_semantics'
VALUE   ISDEFAULT
------- ---------
BYTE    TRUE

It means, one char equals one byte. But after conversion, one char is larger than one byte and does not fit any longer.

single-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-------------
WE8MSWIN1252
SQL> create table t(x char(1));
Table created.
SQL> insert into t values ('é');
1 row created.
SQL> commit;
Commit complete.
$ expdp scott/tiger dumpfile=t.dmp tables=t
. . exported "SCOTT"."T"            1 rows

multi-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-----------
UTF8
$ impdp scott/tiger dumpfile=t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table 
  "SCOTT"."T"
ORA-12899: value too large for column X 
  (actual: 2, maximum: 1)
ORA-02372: data for row: X : 0X'E9'
. . imported "SCOTT"."T"            0 out of 1 rows

How do I import my data?

1) import the metadata


$ impdp scott/tiger dumpfile=t.dmp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE

2) change the char_used of the column(s) from (B)yte to (C)har

SQL> select 
  column_name, char_used, data_length, data_type 
  from user_tab_columns 
  where table_name='T' and char_used='B';
COLUMN_NAME  C DATA_LENGTH DATA_TYPE
------------ - ----------- ---------
X            B           1 CHAR
SQL> alter table t modify x char(1 char);
Table altered.

3) import the data

$ impdp scott/tiger dumpfile=t.dmp content=data_only
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"            1 rows

4) check

SQL> select x, length(x), lengthb(x) from t;
X  LENGTH(X) LENGTHB(X)
- ---------- ----------
é          1          2

My column has now a length of one char and two bytes.

ssl version

I wrote about ssl version in jdbc thin yesterday

The default version also no longer works for the thick client with 12c client and 11g Server.

With 11gR2 :


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0
OK (100 msec)

with 12cR1 :


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0
TNS-12560: TNS:protocol adapter error

in trace file I see


ntzgsvp: no SSL version specified - using default version 0
ntzdosecneg: SSL handshake failed with error 29048.
ntzCreateConnection: returning NZ error 29048 in result structure
ntzCreateConnection: failed with error 542
nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=29048, nt[1]=542, nt[2]=0; ora[0]=29048, ora[1]=0, ora[2]=0

I could not see this as a documented change yet, but if you force ssl_version to be 3.0, both client versions works


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0
OK (80 msec)
C:> find "version" tnsping.trc sqlnet.ora

---------- TNSPING.TRC
nlstddp_dump_ptable:   ssl_version = 3.0
ntzGetStringParameter: found value for "ssl_version" configuration parameter: "3.0"

---------- SQLNET.ORA
ssl_version=3.0