how to rename datafiles on standby

If you rename a file with alter database on primary, it will not occur on standby. If you want to rename it on standby to, you need to do it manually

  1. cancel the recovery (set dg_broker_start to false and restart db in mount status if you use dataguard)
  2. set the standby file management to manual alter system set standby_file_management=manual
  3. move the file with OS commands as you did on the primary
  4. rename the file with alter database rename file ‘old’ to ‘new’
  5. reenable automatic standby file management and dg broker and restart the recovery (start db in mount status if you use dataguard)

Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified.

SQL> CREATE TABLE /XXX(x number);
CREATE TABLE /XXX(x number)
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> CREATE TABLE "/XXX"(x number);

Table created.

Even in DBMS_STATS you should use double quotes


SQL> exec dbms_stats.gather_table_stats(user,'/XXX')
BEGIN dbms_stats.gather_table_stats(user,'/XXX'); END;

*
ERROR at line 1:
ORA-20001: /XXX is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats(user,'"/XXX"')

PL/SQL procedure successfully completed.

It is also a good practice to not use table name like “/XXX”, “FROM” or “ROWID”. But if you use dynamic SQL, be sure your code does not bug on invalid identifier.

It is pretty seldom that Oracle introduces new reserved words, as it breaks code, so if you do

CREATE TABLE MYTABLE(x number);

you can be pretty sure that neither MYTABLE nor X will be reserved in 12c or 13c…

Rman backup compression

Did you know you can make your backup at least twice faster with a single line ?

Demo :

RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_13:08:01
...
Finished backup at 2013-06-05_13:13:59

6 minutes for a compressed backup on a NAS with 24 Channels and 100Gb of raw data. Not bad. But look at this !


RMAN> configure compression algorithm 'low';
new RMAN configuration parameters are successfully stored
RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_14:06:09
...
Finished backup at 2013-06-05_14:08:29
RMAN> configure compression algorithm clear;
RMAN configuration parameters are successfully reset to default value

By configuring this magic parameter, it is now more than twice faster ! This is incredible !

Go to your cashier and grab some coins to get this amazing advanced compression option !

use cron to schedule a job only once

I wrote about not using DAY OF MONTH and DAY OF WEEK simultanously in how to cron

The correct method is to use
15 14 15 05 * /tmp/run-my-job

But… I wrote this five years ago. Hmmm ! Not that correct then since it would run every year 😉

Ok, periodically I check for jobs are scheduled to run a specific date only
$ crontab -l|awk '$1!~/#/&&$3*$4'
15 14 15 05 * /tmp/run-my-job

I have 9 more days to remove this before it runs for the fifth time 🙂

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

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
/

Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.

Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!

1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
excel-screenshot

2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.

3) transfer the excel file to your favorite platform

4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)

$ unzip /tmp/DynamicExcel.xlsx
Archive: /tmp/DynamicExcel.xlsx
inflating: [Content_Types].xml
inflating: _rels/.rels
inflating: xl/_rels/workbook.xml.rels
inflating: xl/workbook.xml
inflating: xl/styles.xml
inflating: xl/worksheets/sheet2.xml
inflating: xl/worksheets/_rels/sheet1.xml.rels
inflating: xl/worksheets/_rels/sheet2.xml.rels
inflating: xl/drawings/_rels/drawing1.xml.rels
inflating: xl/theme/theme1.xml
inflating: xl/worksheets/sheet1.xml
inflating: xl/drawings/drawing2.xml
inflating: xl/charts/chart1.xml
inflating: xl/drawings/drawing1.xml
inflating: xl/sharedStrings.xml
inflating: docProps/core.xml
inflating: docProps/app.xml

5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2

cd xl/worksheets

tr -d '\r' < sheet1.xml | sed 's, head
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
"row",
xmlattributes ((rownum+1) AS "r",
'1:2' AS "spans",
'0.2' AS "x14ac:dyDescent"),
XMLELEMENT ("c",
xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
XMLELEMENT ("v", d-date '1899-12-30')),
XMLELEMENT ("c",
xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
XMLELEMENT ("v", c)))
x
FROM ( SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
FROM v$backup_redolog
WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
AND TRUNC (SYSDATE) - 1 / 86400
GROUP BY TRUNC (next_time, 'DD')
ORDER BY 1);
EOF
tr -d '\r' < sheet1.xml | sed -n 's,.*,,p' > tail
cat head body tail | tr -d '\n' > sheet1.xml
rm head body tail

6) recreate zip file

$ cd ../..
$ zip -r /tmp/DynamicExcel2.xlsx *
adding: [Content_Types].xml (deflated 78%)
adding: docProps/ (stored 0%)
adding: docProps/core.xml (deflated 51%)
adding: docProps/app.xml (deflated 53%)
adding: _rels/ (stored 0%)
adding: _rels/.rels (deflated 60%)
adding: xl/ (stored 0%)
adding: xl/_rels/ (stored 0%)
adding: xl/_rels/workbook.xml.rels (deflated 71%)
adding: xl/workbook.xml (deflated 42%)
adding: xl/styles.xml (deflated 56%)
adding: xl/worksheets/ (stored 0%)
adding: xl/worksheets/sheet2.xml (deflated 45%)
adding: xl/worksheets/_rels/ (stored 0%)
adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%)
adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%)
adding: xl/worksheets/sheet1.xml (deflated 81%)
adding: xl/drawings/ (stored 0%)
adding: xl/drawings/_rels/ (stored 0%)
adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%)
adding: xl/drawings/drawing2.xml (deflated 58%)
adding: xl/drawings/drawing1.xml (deflated 61%)
adding: xl/theme/ (stored 0%)
adding: xl/theme/theme1.xml (deflated 79%)
adding: xl/charts/ (stored 0%)
adding: xl/charts/chart1.xml (deflated 85%)
adding: xl/sharedStrings.xml (deflated 22%)

7) Check it

DynamicExcel2.xlsx

Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.

Remember, NT means New Technology 🙂

There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure

But there is no DEFAULT HOME in the registry.

PS C:\> gci HKLM:\SOFTWARE\ORACLE

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Name Property
---- --------
KEY_agent12c1 ORACLE_HOME : D:/oracle\core\12.1.0.1.0
ORACLE_HOME_NAME : agent12c1
ORACLE_GROUP_NAME : Oracle - agent12c1
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.1.0.2.0
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_db_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_12.1.0.2.0
2_0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_emas_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_12.1.0.1.0
0_agent_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_agent_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_12.1.0.1.0
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0
KEY_OraClient11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\client_1
ORACLE_HOME_NAME : OraClient11g_home1
ORACLE_GROUP_NAME : Oracle - OraClient11g_home1
ORACLE_BUNDLE_NAME : Enterprise
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
OLEDB : D:\oracle\product\11.2.0\client_1\oledb\mesg
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraClient11g_home1
MSHELP_TOOLS : D:\oracle\product\11.2.0\client_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\client_1\dbs
KEY_OraGtw11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\tg_1
ORACLE_HOME_NAME : OraGtw11g_home1
ORACLE_GROUP_NAME : Oracle - OraGtw11g_home1
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME : Enterprise
MSHELP_TOOLS : D:\oracle\product\11.2.0\tg_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\tg_1\dbs
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraGtw11g_home1
KEY_sbin12c1 ORACLE_HOME : D:\oracle\sbin
ORACLE_HOME_NAME : sbin12c1
ORACLE_GROUP_NAME : Oracle - sbin12c1
ODP.NET
remexecservicectr remaining_time : 120000
SYSMAN

How do I set my Oracle Home?

Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.

Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.

BUILD DEFERRED takes ages

When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night.

BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages.


SQL> create materialized view mv1 build deferred as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:17.28
SQL> select * from mv1;

no rows selected

No data collected, but still a long time (17sec here, but much worst in real life)

A workaround is to use ON PREBUILT TABLE on an empty table, just add a few WHERE 1=0 in your subqueries

SQL> create table mv1 as select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0) where 1=0;

Table created.

Elapsed: 00:00:00.04
SQL> create materialized view mv1 on prebuilt table as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:00.15
SQL> select * from mv1;

no rows selected

Elapsed: 00:00:00.00

Much faster !

Rman and DBGSQL message

I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911


RMAN> duplicate target database to DB02
until time "to_date('2013-01-29_00:00:00','YYYY-MM-DD_HH24:MI:SS')"
nofilenamecheck ;

DBGSQL: TARGET> select 2013-01-29_00:00:00 from sys.dual
DBGSQL: sqlcode = 911
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2013 17:58:23
RMAN-05501: aborting duplication of target database
ORA-01861: literal does not match format string

I have not found anything useful except bug Bug 9351175 which is fixed in 11.2 (and I have 11.2.0.2/aix), but I could workaround the problem with


unset NLS_DATE_FORMAT

Because I do like to have readable timestamp (with seconds) in my RMAN logs, I set NLS_DATE_FORMAT in my spfile

alter system set nls_date_format='YYYY-MM-DD_HH24:MI:SS';

Book review : EM12c

OCM Oracle ACE Porus Homi Havewala wrote a new book on Cloud Control

Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos

It is a book about Enterprise Manager, but also a book about the Chaos.

There is no separation of roles […] there is no DBA Designer (the Senior DBA) […] and no DBA Operator (the Junior DBA). The Senior […] does not even have the time […] the Junior then decides to try out different things

It is also a book about em. And about 12c. No mention of 12cR2, which is massively more performant.

I have been using 12c for about one year and multiple topics are covered. There is not much said on the Incident Manager, which seems to be powerfull but it is for me more time consuming than acknowledging the alerts in 10g/11g.

There is a huge advantage of using 12c, to deploy new agents and new targets in very large shops.

I have setup the software library in offline mode (download with pc from metalink and load in em with emcli), the offline fashion is not documented. In all the places where I have been working, an internet communication between outside world (oracle.com) and database server is banned.

Lot’s of screenshots on Exadata. Not sure where the author could try it, the domain were grayed out (why not edit to example.com with Photoshop? )

There is a lot of content (almost 400 pages), to mention only a few : Real Time ADDM, Real Application Testing, Database deployment, data masking.

At the end of book you will enjoy the case studies. Reading thru the lines, it is not always success stories, but rather expectations that em will do the job

The bank also wanted to ensure that their data center was in compliance with security best practices.

As in any IT book, you do not have to read it from page 1 to page 379, myself I have no DB Machine so I could skip the largest chapter which is on Exadata. But it is a pleasant lecture.

The book is very friendly with Oracle. I have experienced awful performance in 12cR1 and Internet Explorer, some annoying and unavoidable java exceptions in the web interface and the dumbest requirement for me was to install cygwin, bash, ssh on one windows server only to deploy the agent!

I recommend the book, it is a good book

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

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ?

Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=]


SELECT * FROM TAB WHERE REGEXP_LIKE(C,'b[[=e=]]b[[=e=]]')

not only [=e=] is easier to read and to type, but also it is more portable if you copy your scripts from DOS to UNIX and use different character sets

return code and sqlplus

Calling a shell script from within sqlplus is buggy…

I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 because some metalink guru closed 3798918 as not reproducible.

As written in return code, host does not return the correct code


SQL> host exit 7
SQL> def _RC
DEFINE _RC = "0" (CHAR)

If you never use _RC, you may believe you are safe. But watch this :

SQL> get foo.sh list
1 #!/bin/sh
2 if /bin/false
3 then
4 echo this is wrong
5* fi
SQL> host ./foo.sh
this is wrong

The return code not being set is not only affecting the _RC variable, but it is also affecting all subshells !

Note this is not reproducable with SQLPLUS /NOLOG

SQL> host false
SQL> def _rc
DEFINE _RC = "1" (CHAR)
SQL> conn x/x
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> host false
SQL> def _rc
DEFINE _RC = "0" (CHAR)

After my (failed or successfull) tentative to connect as x/x, it is reproducible again

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

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business.

In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL.

Most recent update : it is postponed to 13cR2 at least !

*** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning up the ERs. Mass updating all 13%GC% ERs to fixby 13.2GC DEFER. If you want to implement ER in 13.1GC, please update the fixby to = 13.1GC, and update all the other fields as per guidelines published.

Considering the cost of oracle advanced security option (required to get ssl), the lack of ability to influence future product enhancement is disappointing

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

Oracle Certification Portal

On the certification portal, http://certview.oracle.com you can now view your certification. This replace the original Sun Certmanager (used to be certmanager.net/sun) and it provides you an interface to publish your information to third parties.

Both Sun and Oracle Certifications are visible.

If you publish your information, your recipient will receive a link that will show your credentials :

Credentials

Credential Title Certification Date
Oracle Database 11g Administrator Certified Master 19-SEP-12
Oracle Database SQL Certified Expert 19-JUL-07
Certified Expert Oracle Database 10g Real Application Clusters Administrator 27-JUN-07
Oracle Application Server 10g Administrator Certified Associate 28-APR-06
Oracle Database 10g Administrator Certified Professional 14-SEP-05
Oracle9i Database Administrator Certified Master 05-FEB-04
Oracle9i Database Administrator Certified Professional 18-FEB-03
Oracle8i Database Administrator Certified Professional 19-DEC-02
Sun Certified Network Administrator for Solaris 8 08-AUG-02
Oracle8 Database Administrator Certified Professional 17-JAN-02
Sun Certified System Administrator for Solaris 2.6 22-MAY-00

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 !

Dynamic number of columns

I used to believe you cannot have a dynamic number of columns. Today Tom referenced Anton on asktom.

It leaded me there, back in time, 2006, on the OTN forums
https://forums.oracle.com/forums/message.jspa?messageID=1297717#1297717

Difficult to write an article on this without copy-pasting most of Anton code, so just read it on the link above.

Then you will see the magic :


SQL> select * from table( NColPipe.show( 'test', 3 ) );

test1 test2 test3
---------- ---------- ----------
row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3

SQL> desc NColPipe
Name Null? Type
----------------------------------------- -------- ----------------------------
L_PARM VARCHAR2(10)
ROWS_REQUESTED NUMBER
RET_TYPE ANYTYPE
ROWS_RETURNED NUMBER

METHOD
------
STATIC FUNCTION ODCITABLEDESCRIBE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RTYPE ANYTYPE OUT
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
STATIC FUNCTION ODCITABLEPREPARE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCTX NCOLPIPE OUT
TI ODCITABFUNCINFO IN
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
STATIC FUNCTION ODCITABLESTART RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCTX NCOLPIPE IN/OUT
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
MEMBER FUNCTION ODCITABLEFETCH RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NROWS NUMBER IN
OUTSET ANYDATASET OUT

METHOD
------
MEMBER FUNCTION ODCITABLECLOSE RETURNS NUMBER

The function is returning ANYDATASET and implemeting ODCITABLEDESCRIBE. This is all clean documented code.

Read more:
Data Cartridge Developer’s Guide – Using Pipelined and Parallel Table Functions – Describe Method
Sometimes it is not possible to define the structure of the return type from the table function statically … You can implement a ODCITableDescribe() routine

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.

remove the current directory

Can I remove the current directory?

I used to believe you cannot.

Solaris:

$ uname -s
SunOS
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove any directory in the path of the current working directory
/tmp/bla
$

AIX:

$ uname -s
AIX
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove the current directory /tmp/bla.
$

Today I did a rm that I expected to fail, but …


$ uname -s
Linux
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
$

Wait, did it work?

$ cd /tmp/bla
$ pwd
/tmp/bla
$ cd /tmp/bla
$ ls -lad /tmp/bla
ls: /tmp/bla: No such file or directory
$ cd /tmp/bla
$

Somehow I am still there, in /tmp/bla, but /tmp/bla has been removed. What a strange operating system 😉

American = fast

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang

Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252

C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fr Jul 6 10:30:25 2012

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

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

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2389703825

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 2 | HASH GROUP BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Now let’s try with the PC setup with american_america

C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\>sqlplus scott/tiger@db01

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 6 10:31:57 2012

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

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

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Execution Plan
----------------------------------------------------------
Plan hash value: 637087546

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

The “cost” and “plan” changed. Why this? Maybe an optimizer bug, but it is related to NLS_SORT, which is GERMAN for nls_lang=GERMAN_SWITZERLAND and BINARY for nls_lang=AMERICAN_AMERICA.

Binary!

How to quit crontab -e without overwritting cron

Imagine my crontab

* * * * * /usr/bin/date > /tmp/foo

I am writing the date to /tmp/foo every minute
$ cat /tmp/foo
Thu Jul 5 08:45:01 CEST 2012

Now I want to view my crontab in my EDITOR (vi).


$ crontab -e

I do not quit yet.

In the meantime, my colleague modify the crontab.


* * * * * /usr/bin/date > /tmp/bar

Later, I quit vi with :q!

O Surprise, the crontab is * * * * * /usr/bin/date > /tmp/foo again

According to the doc :

When you finish creating entries and exit the file, the crontab command
copies it into the /var/spool/cron/crontabs directory

Even if you did not make change, you overwrite the content of your crontab !

If you want to exit your crontab editor really without overwritting the crontab, you need to kill yourself.

Ex:

CTRL-Z
[2] + Stopped (SIGTSTP) crontab -e
$ kill %2
$
[2] + Stopped (SIGTTOU) crontab -e

Thanks to Colin comment, I realized I could not kill with kill, let’s kill with -9

$ kill -9 %2
$
[2] + Killed crontab -e