Category Archives: dba

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

check if using tcps part II

in your current session, as written there, check sys_context('USERENV', 'NETWORK_PROTOCOL')

in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is…


select sid,program,
  case when program not like 'ora___@% (P%)' then
  (select max(case
when NETWORK_SERVICE_BANNER like '%TCP/IP%' 
      then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%' 
      then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%' 
      then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%' 
      then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%' 
      then 'Named pipe'
when NETWORK_SERVICE_BANNER is null 
      then 'TCPS' end)
    from V$SESSION_CONNECT_INFO i 
    where i.sid=s.sid) end protocol
  from v$session s;

       SID PROGRAM         PROTOCOL
---------- --------------- --------
       415 sqlplus(TNS V1- BEQUEATH
       396 sqlplus(TNS V1- IPC     
         6 Toad            TCP     
         9 Toad            TCPS    
         1 oracle(DIAG)            
       403 Toad            TCP     

disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit


alter session DISABLE COMMIT IN PROCEDURE;

According to the doc, it prevents procedure from committing your data

Test case


SQL> alter session DISABLE COMMIT IN PROCEDURE
Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is 
begin
  commit;
end;
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
BEGIN p; END;
Error at line 17
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 1

But some sys procedures may bypass this restriction


SQL> exec dbms_stats.gather_table_stats(user, 'T')
 PL/SQL procedure successfully completed.
SQL> rollback
Rollback complete.
SQL> select * from t

         X
----------
         1

The row was silently committed.

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost

Landscape for my setup

  • One PC with Windows (PC01.EXAMPLE.COM)
  • One DB Server with Unix (DBSRV01.EXAMPLE.COM)
  • One Microsoft Active Directory Server (MSAD01.EXAMPLE.COM)

Username

  • user01

Tools for troubleshooting

My Software

  • PC : Oracle Client 11.2.0.3
  • Unix : Oracle Server 11.2.0.4
  • On AD : MSAD 2008

There are a lot of buggy releases (it makes me think Oracle does not test Kerberos properly)

Some hits :
11.2.0.2 : Bug 12635212 : TCP/88 is not working.
12.1.0.1 : Bug 17890382 : ZTK return value: 6

Also your PC must be using Kerberos (which is the case if you login to your Active Directory). The DB server needs some client libraries (krb5.client.rte on AIX).

System changes:

  • PC : edit etc\services
  • 
    C:\> find " 88" %SystemRoot%\system32\drivers\etc\services
    
    ---------- C:\WINDOWS\SYSTEM32\DRIVERS\ETC\SERVICES
    kerberos   88/tcp    kerberos5 krb5 kerberos-sec  #Kerberos
    kerberos   88/udp    kerberos5 krb5 kerberos-sec  #Kerberos
    

  • Unix : edit /etc/services
  • 
    $ grep -w 88 /etc/services
    kerberos  88/tcp  kerberos5 krb5  # Kerberos
    kerberos  88/udp  kerberos5 krb5  # Kerberos
    

  • On AD : disable pre-authentication
    this option has to be set for every user, under user -> user01 -> Properties -> Account -> Account options -> Select “Do not require Kerberos preauthentication”

Those are quite painful. There is a bug 2458563 fixed in 8.1.7.4.99 (whatever it means) that should have addressed pre-authentication. still required on 11.2.0.4 apparently no longer needed with a 11.2.0.4 client
Editing etc/services to add the “kerberos5″ string means you need admin rights on Windows and root on Unix.

Okay, now you need to create the config files. You probably should use Kerberos v5 MIT.

Kerberos5 was released in 1993, not sure why you want to use something older than this… Okay, for kerberos4, released in the 80’s, you would need on the PC and on the DB Server something like


EXAMPLE.COM
EXAMPLE.COM MSAD01.EXAMPLE.COM admin server

Otherwise you need to specify : sqlnet.kerberos5_conf_mit=true
I have an open SR to support regarding : 12c upgrade guide
The SQLNET.KERBEROS5_CONF_MIT networking parameter is no longer supported in sqlnet.ora

Okay, here the configuration files

krb5.conf on the database server and on the PC


[libdefaults]
default_realm = EXAMPLE.COM

[realms]
EXAMPLE.COM = {
  kdc = MSAD01.EXAMPLE.COM
}

[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

The config file location (kerberos4 or 5) is specified by sqlnet.kerberos5_conf.

There should be a technical account for your db server created on the MSAD that matched your db server.

On Active Directory, you create a user (e.g. : oracle_DBSRV01) who must not change password on first login. Then you extract the keytab with ktpass


ktpass.exe -princ oracle/dbsrv01.example.com@EXAMPLE.COM -mapuser oracle_DBSRV01 -crypto all -pass password -out c:\dbsrv01.keytab

As an Oracle DBA, you will probably ask this to another team who is used to Kerberos.

To verify it, you can list the content of the keytab


$ $ORACLE_HOME/bin/oklist -k dbsrv01.keytab
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 12:45:08

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Service Key Table: dbsrv01.keytab

Ver      Timestamp                    Principal
 4  01-Jan-1970 01:00:00  oracle/dbsrv01.example.com@EXAMPLE.COM

The principal name must match your full qualified host name. You cannot use a DNS alias.

On your PC check for the login name :


PS> $o = New-Object DirectoryServices.DirectorySearcher; 
  $o.Filter = 'servicePrincipalName=oracle/dbsrv01.example.com'; 
  $o.FindOne().properties.samaccountname
oracle_DBSRV01

And you can verify the principal of that user


C:\> setspn -L oracle_DBSRV01
Registered ServicePrincipalNames for CN=oracle_DBSRV01,OU=MiscUsers,DC=example,DC=com
:
        oracle/dbsrv01.example.com

Now you’ve got your keytab, this must be on the DB Server only (and must be readable for oracle). The location is specified by SQLNET.KERBEROS5_KEYTAB.

Next step is the credential cache (CC) parameter. On your PC with the Oracle 11g client, you must set sqlnet.kerberos5_cc_name to OSMSFT://
On the server it is not neeeded. On Oracle 12c client, you must set it MSLSA:, but due to bug 17890382, it is not working yet (metalink comment : We will have to wait [...] bugs are under investigation).

But before you start, you may want to test the ticket.

On Unix, you can get the ticket with kinit and check it with klist. You need to have your configuration in /etc/krb5/krb5.conf (OS Dependent). Do not forget to destroy your credential cache with kdestroy / okdstry while testing

For the DB Server


$ /usr/krb5/bin/kinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM
$ /usr/krb5/bin/klist
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  oracle/dbsrv01.example.com@EXAMPLE.COM

Valid starting     Expires            Service principal
01/16/14 17:41:26  01/17/14 03:41:26  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:41:26


$ /usr/krb5/bin/kinit user01@EXAMPLE.COM
Password for user01@EXAMPLE.COM:
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  user01@EXAMPLE.COM

Valid starting     Expires            Service principal
01/16/14 17:35:57  01/17/14 03:35:57  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:35:57

Now we can test the okinit (oracle kinit) tool to do the same. There are some specific trace options that could be set

Here the complete sqlnet.ora on the server


DIAG_ADR_ENABLED = OFF
TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
TRACE_FILE_OKINIT = okinit
TRACE_LEVEL_OKINIT = SUPPORT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)
SQLNET.KERBEROS5_CC_NAME = /var/opt/oracle/krb/cc/krb5cc_99
SQLNET.KERBEROS5_CONF = /var/opt/oracle/krb/krb5.conf
SQLNET.KERBEROS5_KEYTAB = /var/opt/oracle/krb/dbsrv01.keytab
sqlnet.kerberos5_conf_mit=true

Note the authentication service. If kerberos is not working, you may no longer be able to log / as sysdba and also some db links may no longer work.

Also note SQLNET.AUTHENTICATION_KERBEROS5_SERVICE, which is the prefix of your principal, oracle/dbsrv01.example.com@EXAMPLE.COM

Then we use okinit as we did for kinit


$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:52:21

Copyright (c) 1996, 2013 Oracle.  All rights reserved.
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:55:27

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: oracle/dbsrv01.example.com@EXAMPLE.COM

   Valid Starting           Expires            Principal
16-Jan-2014 17:54:30  17-Jan-2014 01:54:30  krbtgt/EXAMPLE.COM@EXAMPLE.COM

$ $ORACLE_HOME/bin/okinit user01@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:02

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Password for user01@EXAMPLE.COM:
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:12

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: user01@EXAMPLE.COM

   Valid Starting           Expires            Principal
16-Jan-2014 18:15:06  17-Jan-2014 02:15:02  krbtgt/EXAMPLE.COM@EXAMPLE.COM

In case of error, grep for “k5″ in the trace file.

Do the same on the PC01. To test okinit, temporary change the CC cache to a file


sqlnet.authentication_services=(kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=C:\oracle\krb5
TRACE_UNIQUE_CLIENT=on
TRACE_FILE_CLIENT=kerb_client

sqlnet.kerberos5_conf_mit=true
sqlnet.kerberos5_conf=C:\oracle\krb5\krb5.conf
#sqlnet.kerberos5_cc_name=OSMSFT://
sqlnet.kerberos5_cc_name=C:\oracle\krb5\krbcc

and test as in Unix.

Some errors will be easier to find out with a network sniffer on port 88

With AIX


tcpdump -v -v port 88

On Windows
Start -> Microsoft Network Monitor -> Microsoft Network Monitor -> New capture -> Display filter


Frame.Ethernet.Ipv4.TCP.Port == 88 or Frame.Ethernet.Ipv4.UDP.Port == 88

-> Apply -> Start

If you for instance only see UDP packets but no TCP packets, you probably hit bug 12635212.

I still have some KDC_ERR_S_PRINCIPAL_UNKNOWN errors with my working setup, don’t worry about those.

Ok, now that okinit works, the next step is to log in the database.

Check the adapters on the db server


$ adapters

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming
    NIS Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

Create the user on the database db01 on the server dbsrv01. You need to have OS_AUTHENT_PREFIX=”” and do not set REMOTE_OS_AUTHENT (if you have it set, why would you need Kerberos?)


SQL> create user user01 identified externally as 'user01@example.com';
User created.
SQL> grant create session to user01;
Grant succeeded.

Connect from the PC


$ sqlplus -L /@db01

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 16 18:40:43 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This also works in Toad, SQL-Developer and other tools using the OCI thick client. Just let “User” and “Password” blank.

In SQLDeveloper, make sure you do not check Kerberos but you use OCI Thick and no username and password

There is probably a way to do it with the jdbc thin client as document in Note 1523651.1, I have not gone that far yet

Update:
Note 303436.1 : Improper format of configuration file: Remove TAB characters from KRB5.conf file. Replace with spaces.

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet.


DGMGRL> show database sDB01 delaymins
  DelayMins = '5'

DGMGRL> edit DATABASE sDB01 set property delaymins=2;
Property "delaymins" updated

ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s)
Tue Dec 03 15:34:59 2013
ARC0: Archive log thread 1 sequence 3200 available in 2 minute(s)
Tue Dec 03 15:35:15 2013

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0'

SYSDATE              SEQUENCE#
------------------- ----------
2013-12-03_15:38:00       3199

The old logs are not affected. Let’s wait until the latest Delay=5 got applied.


Tue Dec 03 15:40:02 2013
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3199_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3200_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3201_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3202_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3203_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3204_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3205_827686279.arc

All files which had a delay=2 were “pending” apply. Now we got the apply=2 behavior

Same if you increase the value


DGMGRL> edit DATABASE sDB01 set property delaymins=30;
Property "delaymins" updated

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0';

SYSDATE              SEQUENCE#
------------------- ----------
2013-12-03_15:49:04       3224

ARC3: Archive log thread 1 sequence 3224 available in 2 minute(s)
Tue Dec 03 15:47:22 2013

Here again, the old logs are not affected, we need to wait until the last delay=2 got applied to get a delay=30 behavior.

While you cannot change the delay, there is still a way to workaround the problem.

If you want to immediately increase log to 30 minutes, turn off applying for half an hour.


DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.
-- coffee break
DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

If you want to decrease log from 30 to 2 minutes right now and immediately apply the old logs which have reached this threshold, use sqlplus


ARC1: Archive log thread 1 sequence 3253 available in 30 minute(s)
Tue Dec 03 16:01:26 2013
ARC3: Archive log thread 1 sequence 3254 available in 2 minute(s)
Tue Dec 03 16:01:37 2013

DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.

SQL> recover automatic standby database until time '2013-12-03_16:01:30';
Media recovery complete.

DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

I wrote on delay standby failover here : here

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup


C:\tmp>type dir1\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir1\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01)))

C:\tmp>type dir2\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir2\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02)))

You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first


C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>tnsping db

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 25-NOV-2013 15:47:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\tmp\dir1\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus


C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>sqlplus -L system@db

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 25 16:01:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------
DB02.EXAMPLE.ORG

SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN


create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user, 'T1')
explain plan for 
  select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y 
  where x.object_name = y.object_name and x.owner != y.owner

Explain plan writes in the PLAN_TABLE and could be displayed with


SQL> select * from table(dbms_xplan.display)

PLAN_TABLE_OUTPUT
--------------------------------
Plan hash value: 2344570521

---------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT           |          | 18287 |  3500K|
|   1 |  PX COORDINATOR            |          |       |       |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 | 18287 |  3500K|
|*  3 |    HASH JOIN               |          | 18287 |  3500K|
|   4 |     PX RECEIVE             |          | 19219 |  1839K|
|   5 |      PX SEND HYBRID HASH   | :TQ10001 | 19219 |  1839K|
|   6 |       STATISTICS COLLECTOR |          |       |       |
|   7 |        PX BLOCK ITERATOR   |          | 19219 |  1839K|
|   8 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
|   9 |     BUFFER SORT            |          |       |       |
|  10 |      PX RECEIVE            |          | 19219 |  1839K|
|  11 |       PX SEND HYBRID HASH  | :TQ10000 | 19219 |  1839K|
|  12 |        PARTITION HASH ALL  |          | 19219 |  1839K|
|  13 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
---------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"."OBJECT_NAME"="Y"."OBJECT_NAME")
       filter("X"."OWNER"<>"Y"."OWNER")

But if you want to display progress on a currently running query, use DBMS_SQLTUNE (or Oracle Enterprise Manager SQL Monitoring):


SQL> set lin 150 longc 150 long 1000000;
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_plan_hash_value
  =>2344570521) from dual;

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521) 
----------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y where 
x.object_name = y.object_name and x.owner != y.owner

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  SCOTT (25:10369)
 SQL ID              :  0dpj0fxm2gf81
 SQL Execution ID    :  16777216
 Execution Started   :  08/12/2013 14:48:26
 First Refresh Time  :  08/12/2013 14:48:26
 Last Refresh Time   :  08/12/2013 14:48:59
 Duration            :  34s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus.exe
 Fetch Calls         :  19

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521)
----------------------------------------------------------------

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.25 |    0.13 |     0.12 |    19 |    752 |
=================================================

Parallel Execution Details (DOP=4 , Servers Allocated=8)
========================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |
|                |       |         | Time(s) | Time(s) |
========================================================
| PX Coordinator | QC    |         |    0.08 |    0.03 |
| p000           | Set 1 |       1 |    0.03 |    0.02 |
| p001           | Set 1 |       2 |    0.03 |    0.02 |
| p002           | Set 1 |       3 |         |         |
| p003           | Set 1 |       4 |    0.03 |    0.02 |
| p004           | Set 2 |       1 |    0.02 |    0.01 |
| p005           | Set 2 |       2 |    0.03 |    0.02 |
| p006           | Set 2 |       3 |    0.03 |    0.00 |
| p007           | Set 2 |       4 |    0.02 |    0.00 |
========================================================

SQL Plan Monitoring Details (Plan Hash Value=2344570521)
==================================================================
| Id   |          Operation          |  Rows   | Cost |   Rows   |
|      |                             | (Estim) |      | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT            |         |      |      271 |
| -> 1 |   PX COORDINATOR            |         |      |      271 |
|    2 |    PX SEND QC (RANDOM)      |   18287 |  202 |      615 |
|    3 |     HASH JOIN               |   18287 |  202 |      538 |
|    4 |      PX RECEIVE             |   19219 |   44 |    14481 |
|    5 |       PX SEND HYBRID HASH   |   19219 |   44 |    19219 |
|    6 |        STATISTICS COLLECTOR |         |      |    19219 |
|    7 |         PX BLOCK ITERATOR   |   19219 |   44 |    19219 |
|    8 |          TABLE ACCESS FULL  |   19219 |   44 |    19219 |
|    9 |      BUFFER SORT            |         |      |     1316 |
|   10 |       PX RECEIVE            |   19219 |  158 |    14481 |
|   11 |        PX SEND HYBRID HASH  |   19219 |  158 |    19219 |
|   12 |         PARTITION HASH ALL  |   19219 |  158 |    19219 |
|   13 |          TABLE ACCESS FULL  |   19219 |  158 |    19219 |
==================================================================

The small -> sign shows you where it is and display some actual (not estimates) info.

If I run it again :


==================================================================
| Id   |          Operation          |  Rows   | Cost |   Rows   |
|      |                             | (Estim) |      | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT            |         |      |     6451 |

For the same query, we see some progress (6451 rows now).

Check you have licensed the appropriate tuning options before using DBMS_SQLTUNE

Delete one billion row

To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01';
1'000'000'000 rows deleted 
Elapsed: 23:45:22.01
SQL> commit;

This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).

It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).

If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.

There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.

Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100’000’000 rows each night during 10 days.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM<=100000000;
100'000'000 rows deleted 
Elapsed: 04:11:15.31
SQL> commit;

Or if you want to delete in much smaller chunks to accomodate your tiny undo tablespace, you could try


BEGIN
  LOOP
    DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM <= 1000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
   END LOOP;
END;
/

This will run longer than a single transaction, but it is quite usefull if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days…

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality


SQL> select message from v$session_longops where target='SCOTT.EMP';

MESSAGE
------------------------------------------------------------
Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done

The total work is the Oracle estimation :


SQL> select blocks from dba_tables where table_name='EMP';

    BLOCKS
----------
    629683

This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :


SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP';

    BLOCKS SYSDATE             LAST_ANALYZED
---------- ------------------- -------------------
    629683 2013-04-21_09:21:47 2007-10-13_21:40:58

SQL> select blocks from dba_segments where segment_name='EMP';

    BLOCKS
----------
   7749888

I have customized my very long ops query to deal with very long waits.


col target for a20
set lin 150 pages 40000 termout off
alter session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col time_remaining for 999999

select
  lo.target,lo.sofar,seg.blocks,
  lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
  100*lo.sofar/seg.blocks PCT_DONE
from
  dba_segments seg,
  v$session_longops lo
where
  lo.units='Blocks'
  and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
  and regexp_substr(lo.target,'[^.]+') = seg.owner
  and regexp_substr(lo.target,'[^.]+$') = seg.segment_name
/

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history.

Without catalog :


select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from v$backup_datafile 
  group by FILE# ,trunc(completion_time,'IW')
) 
group by d 
order by d;


COMPL        GB
-------- ------
W30-2012   3.73
W31-2012   4.84
W32-2012   5.00
W33-2012   5.05
W34-2012   5.35
W35-2012   5.80
W36-2012   6.12
W37-2012   6.39
W38-2012    .93
W39-2012   7.02
W40-2012   7.56
W41-2012   7.72
W42-2012   7.88
W43-2012   8.08
W44-2012   8.83
W45-2012   9.03
W46-2012   9.45
W47-2012   9.61
W48-2012  10.11
W49-2012  10.29
W50-2012  10.38

The history mostly depends on control_file_record_keep_time. If you do not use an rman catalog, set it to a high value like 93 (3M) or 366 (1Y)

With the rman catalog, use the RC_ view


select DB_NAME,to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select DB_NAME,max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from rc_backup_datafile 
  group by DB_NAME,FILE# ,trunc(completion_time,'IW')
) 
group by DB_NAME,d 
order by DB_NAME,d;


DB_NAME  COMPL        GB
-------- -------- ------
DB01     W30-2012   3.73
DB01     W31-2012   4.83
DB01     W32-2012   5.00
DB01     W33-2012   5.05
DB01     W34-2012   5.34
DB01     W35-2012   5.79
DB01     W36-2012   6.11
DB01     W37-2012   6.39
DB01     W38-2012    .93
DB01     W39-2012   7.01
DB01     W40-2012   7.56
DB01     W41-2012   7.71
DB01     W42-2012   7.87
DB01     W43-2012   8.08
DB01     W44-2012   8.82
DB01     W45-2012   9.02
DB01     W46-2012   9.44
DB01     W47-2012   9.60
DB01     W48-2012  10.10
DB01     W49-2012  10.28
DB01     W50-2012  10.37

If you need to check which table grows the most, check How big was my table yesterday. But remember, RMAN backup is free to use, AWR and the WRI$ tables require the diagnostic pack and the Enterprise edition

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack).

Those tables could also be accessed manually.


SELECT savtime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
  dba_objects o
WHERE 
   o.owner='SCOTT'
   AND o.object_name='EMP'
   and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;


SAVTIME           OWNER    OBJECT_NAME     ROWCNT     BLKCNT
----------------- -------- ----------- ---------- ----------
2012-11-06 06:49  SCOTT    EMP           13215425     120077
2012-11-13 07:28  SCOTT    EMP           12678535     120077
2012-11-20 03:15  SCOTT    EMP           12860640     120077
2012-11-27 03:19  SCOTT    EMP           13045850     120077
2012-12-04 05:41  SCOTT    EMP           13326460     120077

To increase the retention, use DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings

tnsping and instant client

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance.

I just created one function in my .profile


whence tnsping >/dev/null 2>&1 || 
  tnsping() { 
    sqlplus -L -s x/x@$1 </dev/null | 
      grep ORA- | 
        (grep -v ORA-01017 || echo OK)
  }

and tested it


$ tnsping db999
ORA-12154: TNS:could not resolve the connect identifier specified
$ tnsping db01
OK
$ tnsping db02
ORA-12541: TNS:no listener

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link.

After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil…

DEMO:


SQL> create user u1 identified by xxx;

User created.

SQL> grant create session, create database link to u2 identified by xxx;

Grant succeeded.

SQL> create trigger evil after logon on database  begin 
  2  execute immediate 'alter session set current_schema=u1';end;
  3  /

Trigger created.

SQL>
SQL> conn u2/xxx
Connected.
SQL> create database link l;
create database link l
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> show user
USER is "U2"
SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-----------------------------------------
U1

SQL> alter session set current_schema=u2;

Session altered.

SQL> create database link l;

Database link created.

Drop database link in another schema

Today I wrote this script :

drop_database_link.sql


accept owner char prompt "Enter database link owner : "
accept db_link char prompt "Enter link name : "

begin
  dbms_scheduler.create_job(
    job_name=>'&owner..drop_database_link',
    job_type=>'PLSQL_BLOCK',
    job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
  );
  dbms_scheduler.run_job('&owner..drop_database_link',false);
  dbms_lock.sleep(2);
  dbms_scheduler.drop_job('&owner..drop_database_link');
end;
/

I am using the scheduler to run a job as another user. The database link owner does not need to have any privilege, neither CREATE SESSION nor CREATE JOB. It could locked and expired.

ORA-01722: invalid number and sql loader

Your manager asked you to load an Excel file in a table. Ok, you look at the header, create a basic table with meaningfull datatype, you open Excel and save as csv, you load your data. But then you get ORA-1722. What happened ?

Ok, let’s do it


create table t(x number not null, y number, z number not null);


LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3


$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Record 2: Rejected - Error on table T, column Y.
ORA-01722: invalid number

Here it is pretty eye-popping, but you probably have 10 years of market data to load with hundreds of columns and most of the columns are empty or/and obsolete.

The thing is, Excel did put a space for your “number” datatype, space is not a valid number !


SQL> select to_number(' ') from dual;
select to_number(' ') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

A workaround is for each nullable numeric column to specify nullif column=blank


LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y NULLIF Y=BLANKS, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3

$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Table T:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

I hope I will remember this next time I am sqlloading from Excel into Oracle !

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user.

Those time are not really for away, only starting at Oracle 11g, you could have salted/case sensitive passwords. Salted means that Scott may have many different passwords keys for tiger.


 SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:96A5FF65BFF84D7AAC6F8F00879881E8506FE57F555E5BA2927B606DC4F1

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:AE23FB94A462C44A75040CE3BA731E3EF08C4A270F5940491045CBCEF63C

Some users may have only the 10g version (password not changed after migrating to 11g), some may have the 11g version of both, and -who knows- some may have already have SHA-2/512 passwords. SHA2 has many advantages. The chance that 2 passwords provides exactly the same string are much lower than in SHA1 (collision) and it performs twice faster on 64 bits servers.


SQL> select username, password_versions from dba_users where username like 'U_;
USERNAME                       PASSWORD
------------------------------ --------
U1                             10G
U2                             11G
U3                             10G 11G
U4                             12C

Probably you never saw this unless you are in beta 12. But actually it is documented in the 11gR2 Documentation.

12C if a new SHA-2 based SHA-512 hash exists

shutdown timeout

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always.

Today I discovered a 9iR2 new feature : shutdown timeout !
Shutdown Timeout
If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: user requested cancel of current operation.

Ok, if and only if I am getting this ORA-1013, I shutdown abort, startup, shutdown immediate.

It is very unusual that a shutdown immediate does not terminate in 1 hour, and hard to reproduce. For this test case, I am doing a shutdown normal

1) make sure you have at least one other session open
2) shutdown normal
3) wait about 60 minutes (defined in _shutdown_completion_timeout_mins, not a supported parameter to change)


SQL> shutdown normal
ORA-01013: user requested cancel of current operation
SQL> 

Now we received a ORA-1013 (but I did not use CTRL-C). The instance is now half-stopped, most sessions and background processes like MMON, CJQ, SMCO are already dead and it is probably a good idea to restart it properly. Maybe with startup force and shutdown immediate.

SQL> startup force
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2166160 bytes
Variable Size             658510448 bytes
Database Buffers          402653184 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

This is all documented :
After ORA-01013 occurs, you must consider the instance to be in an unpredictable state…If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT

I am extremly happy to have discovered this, because it will still allow a shutdown abort after one hour of shutdown immediate, which is an extremly rare case, but it is an enhancement for my backup scripts.

OCM 11g upgrade

Last Friday I went to Munich for THE upgrade exam. I have a few recommendations based on my preparation.

DISCLAIMER: no recommendation is based on the exam

Check you have a similar environment. You will get 11gR1 database and 10g entreprise manager. Personnaly I did my preparation on 11gR2 and 11g/12c em, I did not find useful to prepare on some oldish version. You should have some Linux / Unix around. It does not really matter if you use AIX, HPUX, Solaris or Linux.

I bought myself a keyboard with German Layout (the one with ß, Ä and Ö) to gain speed

Read each topic carefully and get proficiency:
– Database:
I know the meaning of the initialisation parameters. In need, I can quickly check one in the Reference
I am familiar with the sql syntax and have read the SQL Reference more than once

– Network configuration :
I know how to configure sqlnet.ora, tnsnames.ora and listener.ora without looking at the documentation.
I have carefully tested all possible parameters in Net Service Guide

– ADR :
I know how to set up the ADR location in the database and network.
I know how to use the command line utility, documented in utilities -> adrci

– RMAN :
I know how to configure RMAN with the configure statement. I am efficient in doing backups and restores
I have read the RMAN Reference carefully.

– Dataguard :
I am confident with the dgmgrl utility and with oem to configure and monitor dataguard.
I have read the Dataguard Broker Guide

– Materialized view
I have read and tested the examples in the Datawarehouse Guide -> Materialized views

– Secure Lob
I have played with lobs as basicfile and securefiles to check the differences and new features. I have read the secure files guide

– Streams
Streams is a very complex product. I have attended last year a five days course in Paris to only realise it is utterly tricky to maintain and debug, it case of errors.
I know where to look at if the capture, propagation or apply fail. Mostly in alert log, but also in the DBA views. I know how to restart the different processes. I am confident with both the OEM and PL/SQL Packages DBMS_STREAMS_*.
I have read the Streams Concept and Streams Replication Administration

– Resource manager
I know both the GUI and command line. I have read Admin guide -> Resource Manager

– Advisors and baselines
A very valuable resource to read is the 2 Days Perf Guide. It may sound strange to prepare an OCM exam with a 2-Days guide, but it is an awesome lecture.
To get deeper and learn the API usage, read Performance Tuning -> Optimizing SQL Statements

– Replay a capture workload
I learnt both the PL/SQL and EM usage in the Real Application Testing User Guide

Almost all the other topics are self explanatory and almost trivial once you know the syntax by heart…

I spent over one year preparing this exam and if you browse my recent posts, you will find detailled example that somehow relate to miscellaneous findings
Check mount option in linux
Transport tablespace over db links
Fast start failover
On star transformation
my first ADR package

For those of my readers who are on this way, good luck!

PS: no, I do not know the result yet…

Difference between Paris and Zurich

When I was a child, I used to go skiing in the alps, and occasionaly cross the borders. I remember that late in the season (Eastern skiing) restaurants were already empty in France when we had lunch, because our neithbough countries introduced summertime before us.

It is a long way back, namely summers 1976 to ’80. In 1975 and before, neither of us had day light saving. In 1981 and later, we both had it.

Ok, I just had an issue with a wrong date in a customer application. Somehow our database is set with POSIX format, let’s say +01:00 and +02:00, derived from CET/CEST unix timezone (TZ=CET-1CEST,M3.5.0,M10.5.0)

Due to some obscure multiple conversions, dates for summer 1976-80 are wrong, so we sent birthday cards too early to our customers…


SQL> select to_char(cast(
  timestamp '1979-08-01 00:00:00 CET'
    as timestamp with local time zone),
      'YYYY-MM-DD') from dual;

TO_CHAR(CAST(TIMESTAMP'1979-08-0100:00:
---------------------------------------
1979-08-01

but if set my session timezone to Europe/Zurich, which is currently equivalent to CET, I got discrepancies


SQL> alter session set time_zone='Europe/Zurich' ;

Session altered.

SQL> select to_char(cast(
  timestamp '1979-08-01 00:00:00 CET' 
    as timestamp with local time zone),
      'YYYY-MM-DD') from dual;

TO_CHAR(CAST(TIMESTAMP'1979-08-0100:00
--------------------------------------
1979-07-31

A good reason to specify the time zone name correctly in your create database statement !

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations.

Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role.

Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE

update, 2012-07-24
For purge dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;

For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.

Kind of


create table scott.t as 
  select distinct owner,table_name,privilege 
  from dba_tab_privs t 
  where privilege not in ('USE','DEQUEUE') and owner='SYS' ;
begin
  for f in(select * from scott.t) loop 
    execute immediate 
      'grant '||f.privilege||' on "'||f.owner||'"."'
        ||f.table_name||'" to scott with grant option'; 
  end loop;
end;
/

It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.

This may help you to increase your security by reducing your connections as sys.

What does # mean in sqlplus?

The script used to be


shutdown abort

it has been replaced by


#shutdown abort
shutdown immediate

Let’s try !


SQL> #shutdown abort
ORACLE instance shut down.
SQL> shutdown immediate
ORA-01012: not logged on

sqlplus just silently ignored the # symbol and executed the first statement.

Thanks to Maxim comment, here is a new case to explain the sql prefix #


SQL> sho sqlpre
sqlprefix "#" (hex 23)
SQL> select
  2  #prompt hello world
hello world
  2  * from dual;

D
-
X

While within (or outside of) an sqlplus block, you can tell sqlplus to immediately run a sqlplus statement

The correct syntaxes to put comment are documented in Placing Comments in Scripts


SQL> remark shutdown abort
SQL> rem shutdown abort
SQL> -- shu abort
SQL> /* shutdown abort */

How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify


$ dbv BLOCKSIZE=8192 file=sysaux01.dbf
DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application
...
DBVERIFY - Verification complete

Total Pages Examined         : 131072
Total Pages Processed (Data) : 69691
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 28669
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 15755
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 16957
Total Pages Marked Corrupt   : 9
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3220271881 (11.3220271881)

For the dba number, identify the block


def dba=12585405
col block_id new_v block_id
col file_id new_v file_id
select dbms_utility.data_block_address_block(&dba) block_id, 
dbms_utility.data_block_address_file(&dba) file_id from dual;

  BLOCK_ID    FILE_ID
---------- ----------
      2493          3

From the block_id/file_id, identify the segment


col owner new_v table_owner 
col segment_name new_v segment_name
select owner,segment_name,segment_type from dba_extents where file_id=&file_id and &BLOCK_ID between block_id and block_id + blocks - 1;
OWNER
------------------------------
SEGMENT_NAME
-----------------------------------
SEGMENT_TYPE
------------------
SYS
SYS_IL0000008786C00008$$
LOBINDEX

If it is a lob, identify the column and data_type


select tablespace_name,owner, table_name, column_name, data_type from dba_lobs join
dba_tab_columns using (owner, table_name, column_name) where segment_name =
'&segment_name' and owner='&table_owner';
TABLESPACE_NAME                OWNER
------------------------------ ------------------------------
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
SYSAUX                         SYS
WRI$_DBU_FEATURE_USAGE         FEATURE_INFO
CLOB

If you are lucky, you will find a useless segment that you will just drop. Or maybe you will be able to move all segments in another tablespace and drop the tablespace with the corrupt blocks.

If you are pretty unlucky like me today, you will find sys segments in system or sysaux.

Either you export all users data and import them again in a new database (but this means downtime), or you start moving the segments in another tablespace. Or dropping and recreating them.
Check Tablespace maintenance tasks

Once dropped or moved or emptied, you may still see the corrupted blocks. Do not forget to purge the recyclebin, either with PURGE DBA_RECYCLEBIN or PURGE TABLESPACE tbs1 USER usr1

Even then the corruption may belong to no more segment but still appear in dbverify. One workaround is to fill the tablespace (check it does not extend) with a dummy table

create table t(x number, y varchar2(4000) default lpad('x',4000,'x')) tablespace tbs1;

exec while true loop insert into t(x) select rownum r from dual connect by level<10000;commit;end loop

exec while true loop insert into t(x) select rownum r from dual connect by level<100;commit;end loop

exec while true loop insert into t(x,y) select rownum r,'x' from dual;commit;end loop

exec while true loop insert into t(x,y) values (null,null);commit;end loop

drop table t;

Run dbv again and again until you get completly rid of errors. If you drop and recreate sys objects, or even if you simply move them out of the sys tablespace, dictionary corruption and ora-600 is possible. But well, you had corruption anyway …

Transport tablespace over db links

You do not want to export the metadata from the source database, but rather use a database link to get this.

As prerequisite, you have made a set of self-contained tablespaces in read-only mode and you have copied the datafiles.


SQL>  create tablespace test datafile '/u02/oradata/db01/test01.dbf' size 10m;

Tablespace created.

SQL> create table scott.x(x number) tablespace test;

Table created.

SQL> insert into scott.x values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace test read only;

Tablespace altered.


$ scp srv01:/u02/oradata/db01/test01.dbf /u02/oradata/db02

create a database link on the target database DB02


SQL> create database link l using 'DB01';

Database link created.

Then import the tablespace via db link


impdp scott/tiger network_link=l transport_tablespaces=TEST transport_datafiles=/u02/oradata/db02/test01.dbf logfile=DATA_PUMP_LOG:impdp.log

This avoid the “expdp” step, but it does not avoid copying the datafiles

Fast start failover

There are loads of things to do to prepare you for a fast start failover.

First you must have a working set of primary / standby database
Then you must have both databases in flashback mode.
Then verify your dataguard configuration with OEM.

Ok, once you are so far, you will need to review your dataguard property.

lsc01 my primary, lsc05 my standby


edit database lsc01 set LogXptMode='SYNC';
edit database lsc01 set FastStartFailoverTarget= 'lsc05'
edit database lsc05 set LogXptMode='SYNC';
edit database lsc05 set FastStartFailoverTarget= 'lsc01'
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

then you need to start the observer in the background :


nohup dgmgrl -silent sys/*** "start observer" &

note: it does not work if you connect with /. You will get DGM-16979 if you use / or if you use different passwords in standby and primary.

so far so good, let’s enable fast_start failover in dgmgrl


ENABLE FAST_START FAILOVER

Before you switch, check the listener.ora is correctly configured :


SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=LSC01_DGMGRL.example.com)
      (SID_NAME=LSC01)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=LSC05_DGMGRL.example.com)
      (SID_NAME=LSC05)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = precision.example.com)(PORT = 1521))
  )

The GLOBAL_DBNAME is mandatory to enable a painless switchover. Otherwise the standby startup will fail with ORA-12514.

UPDATE: alternatively in 11gR2 you can set the dataguard property StaticConnectIdentifier to use SID instead of service name :


edit database lsc05 set property StaticConnectIdentifier=
  '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=precision.example.com)(PORT=1521))
    (CONNECT_DATA=(SID=LSC05)))';
edit database lsc01 set property StaticConnectIdentifier=
  '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=precision.example.com)(PORT=1521))
    (CONNECT_DATA=(SID=LSC01)))';

in this case you will not need global_dbname in listener.ora. See note 308943.1

Ok, let’s see how fast I can switch


$ time dgmgrl -silent sys/*** "switchover to lsc05"
Performing switchover NOW, please wait...
New primary database "lsc05" is opening...
Operation requires shutdown of instance "LSC01" on database "lsc01"
Shutting down instance "LSC01"...
ORACLE instance shut down.
Operation requires startup of instance "LSC01" on database "lsc01"
Starting instance "LSC01"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "lsc05"

real    1m10.685s
user    0m0.052s
sys     0m0.109s

About one minute. most of the time was spent restarting the original primary as a standby, the primary was already available for queries after about 20 seconds.

Failover to standby with a delay (until time)

Disaster scenario :

1) you have a primary database
2) you have a standby database
3) you want to be able to failover to the standby database until a given time (ex: within the last 24 hours)

First, you create your primary and standby databases.

If you have dataguard broker, you then set the DELAYMINS to 1440 (=1 day) for the standby database.


DGMGRL> edit database sdb01 set property DelayMins=1440;

If you do not use dg broker, then set the delay in your log_archive_dest_2 parameter

SQL> ALTER SYSTEM SET log_archive_dest_2='service=sdb01','LGWR ASYNC NOAFFIRM delay=1440';

Ok, verify your configuration, with OEM, with show configuration or simply with alter system archive log current.

Wait one day ;-)

After one day, you will have a lag between the last retrieved logfile and the last applied redo log entry.


SQL> select applied, max(NEXT_TIME) from v$archived_log group by applied;
APPLIED   MAX(NEXT_TIME)
--------- -------------------
NO        2011-11-17_14:06:53
YES       2011-11-17_13:51:46

Obviously I did not wait one day for this test, but I already see a log of 15 minutes.

Now for some obscure reason, your beloved colleague messed up the primary database and you must recover the database until 14:00.

Note that you cannot use the dataguard broker or OEM to do this.

Ok. Shutdown the production.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now cancel the standby process (but do not issue a recover managed standby database finish).


SQL>  alter database recover managed standby database cancel;

Database altered.

Now we need to recover the standby until time. Check the syntax twice before you type enter!

On standby


SQL> recover automatic standby database until time '2011-11-17 14:00:00';
Media recovery complete.

On standby, start the database as a primary database


SQL> alter database activate standby database;

Database altered.
SQL> alter database open;

Database altered.

Before writing this post I did some research on how to do it with dataguard, but dataguard does not seem to offer point in time failover. Either you do an immediate failover (and you will lose 1440 minutes of data) or you do a complete failover (and you will apply all logs, inclusive the one after 14:00)

delete unused shared memory segments from an Oracle instance

Once upon a time, a dba issues some kill -9 to clean up dying database processes. Or the database instance crashes. This will left some shared memory segments. Note 68281.1 describe how to remove them on a server with multiple databases.

First, list the ipc process


$ ipcs
IPC status from /dev/mem as of Mon Nov 14 11:28:58 CET 2011
T        ID     KEY        MODE       OWNER    GROUP
Message Queues:
q         0 0x4107001c -Rrw-rw----     root   printq

Shared Memory:
m         0 0x7800006f --rw-rw-rw- itmuser1 itmusers
m         1 0x78000070 --rw-rw-rw- itmuser1 itmusers
m   5242882 0x41d2ba80 --rw-r-----   oracle      dba
m  99614723 0xb0d4d164 --rw-rw----   oracle      dba
m  12582917 0xb84cbc28 --rw-rw----   oracle      dba
m  79691782 0x1058873f --rw-------   oracle      dba
m 638582792 0x78000382 --rw-rw-rw-     root   system
m 218103817 0x780003b7 --rw-rw-rw-     root   system
Semaphores:
s         1 0x6202c477 --ra-r--r--     root   system
s   6291461 0x0102c2d8 --ra-------     root   system
s         6 0xa100004b --ra-ra-ra-     root   system

Get a list of the running databases


$ ps -ef | grep pmon | grep -v grep
  oracle  483334       1   1   Aug 16      -  6:46 ora_pmon_db03
  oracle 1253476       1   0   Oct 31      -  2:00 ora_pmon_db01
  oracle 2298042       1   0   Sep 05      - 11:07 ora_pmon_db02

Then, for each database, get the ipc information
$ export ORACLE_SID=db01
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
5242882         0x41d2ba80
$ export ORACLE_SID=db02
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
99614723        0xb0d4d164
$ export ORACLE_SID=db03
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
12582917        0xb84cbc28

Compare it with the first list, and if you are absolutely sure to do what you are doing, remove the oracle segments that are not assigned to any database instance with ipcrm. If possible try first to figure out where they come from and do a shutdown abort of the not-correctly-stopped database.

Ok, with ipcrm


$ ipcrm -m 79691782

I removed the segment that apparently does not relate to any running instance

This could help you if you are really forced to remove some shared memory segments and you cannot afford shutting down other databases.

Duplicate table over database link

The old-style approach would be CREATE TABLE T AS SELECT * FROM T@L, where T is the table you want to duplicate and L the database link pointing to the remote database

If you want to keep more info about the structure of t, the constraints, the indexes, you may use Datapump. Here it is…


SQL> var n number
SQL> exec :n := dbms_datapump.open('IMPORT','TABLE','L')

PL/SQL procedure successfully completed.

SQL> print n
         N
----------
        28

SQL> exec dbms_datapump.metadata_filter(:n,'SCHEMA_LIST','''SCOTT''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.metadata_filter(:n,'NAME_LIST','''T''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.start_job(:n)

PL/SQL procedure successfully completed.
SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER

This is utterly simple. If you mess up with the link name, global name, syntax, and so on, you may end up with orphan jobs in DBA_SCHEDULER_JOBS. There is some metalink note on dropping the underlying tables (Note 336914.1) but first log off, get a coffee, and they may vanish after a few minutes.

Generate network graph from command line

I recently wrote on gnuplot, today I tried another command line utility to generate graphs, graphviz, version 2.24.0 on AIX5L.

Pretty straightforward syntax :

(
  echo "digraph Emp {"
    sqlplus -s -L scott/tiger << EOF
      set pages 0 lin 120 hea off feed off
      select 
        ename ||'->'|| 
        (select ename from emp where empno=e.mgr) || ';' 
      from emp e where mgr is not null;
EOF
  echo "}"
)| neato -Tpng | uuencode Emp.png | mailx laurentschneider@example.com

(or neato -Tpng -o Emp.png to save locally, or -Tps|lp, etc…)

On the number of installed components

I recently posted about network fained fine grained security. More precisely, I posted about the new requirement to have XDB to be able to send a mail or do a nslookup on 11g.

What option should you install on your database ?


SQL> select COMP_NAME,VERSION from DBA_REGISTRY;

COMP_NAME                                VERSION
---------------------------------------- ----------
Oracle Database Catalog Views            11.2.0.2.0
Oracle Database Packages and Types       11.2.0.2.0

What else do you need? If you have java, you will need a java pool. If you have xdb, you will need a xdb schema. The more options you install, the more bugs you will get, the bigger the dictionary will be, the more memory you will need.

But in my experience the worst part of having java, xdb, olap and family installed on your database is that every upgrade will take you hours instead of minutes ! That’s for me a sufficient argument to stick to catalog and catproc (the top base components listed above).

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed

I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco :
The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

I do not want to install XDB to send mail. Sounds like an overkill…

Ok, as an hard core dba I created a wrapper in the sys schema, something you probably should not do !

ex:
10g


SQL> conn scott/tiger
Connected.
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
UTL_INADDR.GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

after upgrade
11g


SQL> conn scott/tiger
Connected.
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual
       *
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

My workaround to “disable” Fine-Grained Access to External Network Services


SQL> conn / as sysdba
SQL> create or replace function my_utl_inaddr_GET_HOST_ADDRESS(HOST VARCHAR2) return VARCHAR2 is begin return utl_inaddr.GET_HOST_ADDRESS; end;
  2  /

Function created.

SQL> grant execute on my_utl_inaddr_GET_HOST_ADDRESS to scott;

Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select sys.my_utl_inaddr_GET_HOST_ADDRESS('localhost') from dual;
SYS.MY_UTL_INADDR_GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

If you want to use the recommended way of granting access to utl_tcp and the like, check note 453756.1