Laurent Schneider
New environment for OCM 11g
For my readers who are preparing the ocm 11g exam, the environment just changed (From 13th May 2013 onwards)
Instead of using OEM 10g, you will be using OEM 11g.
The upgrade exam is still using OEM 10g and DB 11gR1 (!) but I did not care installing OEM 10g and I prepared with OEM 11g.
Ref: p_exam_id:11GOCM
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<DATE '2011-01-01';
1'000'000'000 rows deleted
Elapsed: 23:45:22.01
SQL> commit;
This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).
It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).
If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.
There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.
Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100’000’000 rows each night during 10 days.
SQL> DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM<=100000000;
100'000'000 rows deleted
Elapsed: 04:11:15.31
SQL> commit;
Or if you want to delete in much smaller chunks to accomodate your tiny undo tablespace, you could try
BEGIN
LOOP
DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM <= 1000;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
END;
/
This will run longer than a single transaction, but it is quite usefull if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days…
When v$session_longops is not long enough
With large table scans, sometimes the estimated total work is far beyond reality
SQL> select message from v$session_longops where target='SCOTT.EMP';
MESSAGE
------------------------------------------------------------
Table Scan: SCOTT.EMP: 7377612 out of 629683 Blocks done
The total work is the Oracle estimation :
SQL> select blocks from dba_tables where table_name='EMP';
BLOCKS
----------
629683
This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :
SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP';
BLOCKS SYSDATE LAST_ANALYZED
---------- ------------------- -------------------
629683 2013-04-21_09:21:47 2007-10-13_21:40:58
SQL> select blocks from dba_segments where segment_name='EMP';
BLOCKS
----------
7749888
I have customized my very long ops query to deal with very long waits.
col target for a20
set lin 150 pages 40000 termout off
alter session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col time_remaining for 999999
select
lo.target,lo.sofar,seg.blocks,
lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
100*lo.sofar/seg.blocks PCT_DONE
from
dba_segments seg,
v$session_longops lo
where
lo.units='Blocks'
and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
and regexp_substr(lo.target,'[^.]+') = seg.owner
and regexp_substr(lo.target,'[^.]+$') = seg.segment_name
/
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)
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,<row r="2".*,,' > 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,.*</sheetData>,</sheetData>,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
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';