Laurent Schneider
ODBC 32bits for Windows 64bits
Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.
You will see there is another powershell, another registry, another ODBC tool, another Oracle client.
%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe
First, we run powershell(x86)
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
C:\oracle\product\11.2.0\client_32 client32bit_11203
Only the Oracle 32bit client is displayed
cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"
We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os
%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
C:\oracle\product\11.2.0\client_64 client64bit_11203
cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
Do not get confused by Windows32 and WOW64.
Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.
Oracle ODBC hello world with powershell
Demo :
cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"
Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.
It is easy to get the Oracle Home name with
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_KEY
----------- ---------------
C:\oracle\product\11.1.0\client_1 OraClient11g_home1
C:\oracle\product\11.2.0\client_1 OraClient11g_home2
Then we create the connection (as we did in ADO or ODP) :
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
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
- cancel the recovery (set dg_broker_start to false and restart db in mount status if you use dataguard)
- set the standby file management to manual alter system set standby_file_management=manual
- move the file with OS commands as you did on the primary
- rename the file with alter database rename file ‘old’ to ‘new’
- 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 !
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 !