dba installation

change Oracle installer language in Windows

setup -Duser.language=fr

It works also on UNIX

runInstaller -J-Duser.language=fr

Documented in Sap Note : 1431796

12c dba security

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 :

TNS Ping Utility for 64-bit Windows: Version
OK (100 msec)

with 12cR1 :

TNS Ping Utility for 64-bit Windows: Version
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

TNS Ping Utility for 64-bit Windows: Version
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

dba security

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
then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%'
then 'IPC'
then 'SDP'
then 'Named pipe'
then 'TCPS' end)
where i.sid=s.sid) end protocol
from v$session s;

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

dba sqlplus


Some settings matter when using dbms_metadata.

define large clobs

set long 1000000

large long columns

set longchunksize 32000

long lines

set linesize 32000

no trailing spaces

set trimspool on

no header

set heading off

no page size

set pages 0

no page feed (^L)

set newpage none

no start of page

set embedded on

no tabulator (^T)

set tab off

no feedback (n rows returned)

set feedback off

no echo

set echo off

Per default you get no terminator


When running the spooled code, allow blank lines

set sqlblanklines on

Get rid of &

set define off

Get rid of a leading #

set sqlprefix off

Get rid of . on a single line

set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet

CREATE TABLE T(x number default -

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to


So just forget about trailing dash for now.

Now try

SQL> create view v as select '
3 .
4 #?
5 &_date
6 ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

select '

' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.

dba sql

disable commit in procedure

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


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

Test case

Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
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


The row was silently committed.

dba security

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)


  • user01

Tools for troubleshooting

My Software

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

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

Some hits : : Bug 12635212 : TCP/88 is not working. : 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

    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 (whatever it means) that should have addressed pre-authentication. still required on apparently no longer needed with a 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


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

default_realm = EXAMPLE.COM


[domain_realm] = 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/ -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 - 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/

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

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

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/
$ /usr/krb5/bin/klist
Ticket cache: FILE:/var/krb5/security/creds/krb5cc_99
Default principal: oracle/

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

TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
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

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/

Then we use okinit as we did for kinit

$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/

Kerberos Utilities for IBM/AIX RISC System/6000: Version - 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 - 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/

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




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:


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 '';
User created.
SQL> grant create session to user01;
Grant succeeded.

Connect from the PC

$ sqlplus -L /@db01

SQL*Plus: Release 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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

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

dataguard dba

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'

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

------------------- ----------
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';
-- coffee break
DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';

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

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

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

I wrote on delay standby failover here : here

dba windows

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

C:\tmp>type dir1\tnsnames.ora

C:\tmp>type dir2\sqlnet.ora

C:\tmp>type dir2\tnsnames.ora

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 - Production on 25-NOV-2013 15:47:31

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
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 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;


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

dba sql tuning

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

Predicate Information (identified by operation id):

3 - access("X"."OBJECT_NAME"="Y"."OBJECT_NAME")

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;

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


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

dba sql

Delete one billion row

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


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.


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


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

dba sql

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

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


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

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

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


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

100*lo.sofar/seg.blocks PCT_DONE
dba_segments seg,
v$session_longops lo
and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
and regexp_substr(,'[^.]+') = seg.owner
and regexp_substr(,'[^.]+$') = seg.segment_name

dba rman sql

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

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

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

dba sql

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
dba_objects o
AND o.object_name='EMP'
and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;

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

dba sqlplus

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 | 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
$ tnsping db02
ORA-12541: TNS:no listener

dba sql

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…


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> conn u2/xxx
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;


SQL> alter session set current_schema=u2;

Session altered.

SQL> create database link l;

Database link created.

dba sql

Drop database link in another schema

Today I wrote this script :


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

job_action=>'BEGIN execute immediate ''drop database link &db_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);

(X, Y, Z)
2; ;2

$ 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

2; ;2

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

12c dba security

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

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';

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

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.

11g certification dba

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…

dba sql

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;


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;


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

dba security sql Uncategorized

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.


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

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.

dba sqlplus

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;


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

dba sql

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;

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

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

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

dataguard dba

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'

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


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



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=
edit database lsc01 set property StaticConnectIdentifier=

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

dba unix

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

dba plsql sql

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 :='IMPORT','TABLE','L')

PL/SQL procedure successfully completed.

SQL> print n

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

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.

dba linux unix

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;
echo "}"
)| neato -Tpng | uuencode Emp.png | mailx

(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 ?


---------------------------------------- ----------
Oracle Database Catalog Views
Oracle Database Packages and Types

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