EURO symbol, sqlplus, cmd.exe and various issues

One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos).

Why?

First, the character set did not support it.


select * from v$nls_parameters where PARAMETER like '%CHARACTERSET%';

PARAMETER                      VALUE
------------------------------ ---------------
NLS_CHARACTERSET               WE8ISO8859P1
NLS_NCHAR_CHARACTERSET         AL16UTF16

If you are still using WE8ISO8859P1, consider migrating to WE8MSWIN1252 using csalter


sqlplus "/ as sysdba" @?/rdbms/admin/csminst
csscan "'sys/sys as sysdba'" full=y tochar=we8mswin1252 array=1024000 process=5
sqlplus "/ as sysdba" @?/rdbms/admin/csalter.plb

It is not always that straight forward, check output from csscan (scan.*) carefully before running csalter.

Ok, now retry


H:\>set NLS_LANG=american_america.we8pc850

H:\>sqlplus.exe scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 10 11:28:01 2012

Copyright (c) 1982, 2007, 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> select chr(128) from dual;

C
-
■

Not good!

Obviously, the PC850 client character is not good enough. Let’s switch to mswin1252 on the client.


H:\>chcp 1252
Active code page: 1252

H:\>set NLS_LANG=american_america.we8mswin1252

H:\>sqlplus.exe scott/tiger

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> select chr(128) from dual;

C
-
Ç

Well, what’s missing now? The font ! Let’s change it from “Raster Fonts” to “Lucida Console”. Either by clicking on the command com properties, or even dynamically with that gem (tested on XP) !


H:\>type Lucida.cs
using System;
using System.Runtime.InteropServices;

public class Lucida
{
  const int STD_OUT_HANDLE = -11;

  [DllImport("kernel32.dll", SetLastError = true)]
  static extern int SetConsoleFont(IntPtr hOut, uint dwFontSize);

  [DllImport("kernel32.dll", SetLastError = true)]
  static extern IntPtr GetStdHandle(int dwType);

  public static void Main()
  {
    SetConsoleFont(GetStdHandle(STD_OUT_HANDLE), 6);
  }
}

H:\>csc Lucida.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.
H:\>Lucida

H:\>sqlplus.exe scott/tiger

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> select chr(128) from dual;

C
-
€

Toad 11.5 is out

The latest Toad is now in production, 11.5, get it from http://toadfororacle.com.

If you have an old license key, 9.6 or older, it may complain at installation time, just ignore. It will be fine at run time.

Enhanced TAB browsing experience, nicer and more visible colors for your connection (production=red…), read-only connections.

Currently it still requires a 32bit clients, even when running on a 64bit Operating System.

How to get rid of corrupted blocks without a backup

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


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

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

For the dba number, identify the block


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

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

From the block_id/file_id, identify the segment


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

If it is a lob, identify the column and data_type


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

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

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

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

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

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

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

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

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

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

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

drop table t;

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

weekinmonth in powershell starts on Sunday…

This April month is full of surprises! Not only I did not have the opportunity to trick my colleagues on Sunday April first, but I had some bug of my code due to the start of the week.

[int](get-date).dayofweek and (Get-WmiObject Win32_LocalTime).dayofweek both refer Sunday as day 0.


PS> get-date
Wednesday, 11. April 2012 18:31:52
PS C:\svn> [int](get-date).dayofweek
3
PS C:\svn> (Get-WmiObject Win32_LocalTime).dayofweek
3

Consistently and unfortunately for me, the week does start on Sunday. And not on Monday.

To get week ending on Sunday, lets add 6 for Monday (5 for Tuesday, and so on) to dayofweek and get the 7-modulo


PS> (6+(get-date).dayofweek)%7
2

Ok, now Mon=0, Tue=1, Wed=2, etc…

Substract it from day of month to truncate to Monday


PS> (get-date).day - (6+(get-date).dayofweek)%7
9

First day of current week is Monday 9th

Now add 5 to get the first Day of month between 0 and 6
Divide by 7…
add 1 to get first week=1 (and not 0).

Truncate


PS> [math]::floor(((get-date).day - (6+(get-date).dayofweek)%7 + 5)/7)+1
3

Which slicely differs from weekinmonth when 1st of month is Sunday !


PS> (Get-WmiObject Win32_LocalTime).weekinmonth
2

Pfeww…

[alert] AIX Posix Timezone issue

Maybe you did get or you will get an issue with the date command in AIX.

expected behavior, Linux


$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date
Sat Mar 17 00:14:54 NZDT 2012
$ TZ=Pacific/Auckland date
Sat Mar 17 00:14:58 NZDT 2012

unexpected behavior, AIX


$ TZ=Pacific/Auckland date
Sat Mar 17 00:15:50 GMT+13:00 2012
$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date
Fri Mar 16 23:15:52 NZST 2012

The consequence : date, and all other unix commands like ls, who, ps that display the date in human readable format, and all programs that use ctime are affected


$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 perl -e 'use POSIX;print ctime(time)'
Fri Mar 16 23:19:51 2012

Reference and link to the fixes : www-01.ibm.com/support/docview.wss?uid=isg3T1013017

Powershell and dates

I wrote about unix timestamp i powershell.

I wrote : It is chockingly easy !
but I should have written : … it is not correct :-(


PS> ./perl -e "print time.'`n'"
1331454753
PS> get-date -u %s
1331458358.05694

there is about 3605 seconds difference. 1 hour for Europe/Zurich and 5 seconds to type on a slow keyboard…

Even more confusing


PS> get-date "1970-01-01 00:00:00 +00:00" -u %s
3600

Ok, why that? this is related to the Kind


PS> (get-date "1970-01-01 00:00:00 +00:00").kind
Local

A Unix timestamp of Kind Local is useless. Don’t use this.

Unix timestamp should only refers to Utc.


PS> get-date
Sonntag, 11. März 2012 10:47:35
PS> get-date ((get-date).touniversaltime()) -u %s
1331459257.78432

still it is very confusing to using the unixformat in Windows


PS> get-date -u %c__%Z__%s
So Mrz 11 10:55:05 2012__+01__1331463305.85197
PS> get-date ((get-date).touniversaltime()) -u %c__%Z__%s
So Mrz 11 09:55:46 2012__+01__1331459746.07913

the %s does not work as expected with local (as it refers to a local 1970-01-01) and %Z does not work at all with utc (it sould be +00

OK, let’s switch to the Microsoft time. You count the time as the number of tenth of microseconds (or ticks) since 1 Jan 0001. Again it may differ if you do not use utc.

Let’s define constant $c as number of ticks until 1970-01-01, in utc


PS> $c = (get-date "1970-01-01 00:00:00 +00:00").touniversaltime().ticks
PS> $c
621355968000000000

Let’s convert it to a human readable format


PS C:\> New-Object datetime($c)

Donnerstag, 1. Januar 1970 00:00:00

So far so good.

Lets convert 1331461119 back and forth


PS> (New-Object datetime($c + 1331461119 * 10000000)).tolocaltime()

Sonntag, 11. März 2012 11:18:39

PS> (New-Object datetime($c + 1331461119 * 10000000))

Sonntag, 11. März 2012 10:18:39
PS> ( (get-date "2012-03-11 10:18:39 +00:00").touniversaltime().ticks - $c ) / 10000000
1331461119
PS> ( (get-date "2012-03-11 11:18:39 +01:00").touniversaltime().ticks - $c ) / 10000000
1331461119

BTW, what does Oracle think of the number of ticks of current date ?


PS> $d=36217;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)

TO_CHAR(DA
----------
0100-02-28

Sonntag, 28. Februar 0100 00:00:00

PS C:\> $d=36218;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)

TO_CHAR(DA
----------
0100-02-29

Montag, 1. März 0100 00:00:00

Apparently Microsoft does not have a leap year in 100AD.

Ok, whatelse to say about date?

There is one more format that is commonly used in Microsoft world, the so-called DMTF or Distributed Management Task Force. Let’s have a quick look with 1-JAN-2000.


PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00"))
20000101000000.000000+060
PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00 +00:00").touniversaltime())
20000101000000.000000+000
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+060')
Samstag, 1. Januar 2000 00:00:00
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+000').touniversaltime()
Samstag, 1. Januar 2000 00:00:00

to ADO or to ODP ?

I just read on System.Data.OracleClient Namespace that System.Data.OracleClient are deprecated.
The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

The alternative is to use ODP (from Oracle) instead of ADO (from Microsoft). More powerful of course.

ADO (within powershell):


PS C:\> [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\system.data.oracleclient\2.0.0.0__b77a5c561934e089\system.data.orac...

PS C:\> $connection=New-Object DATA.OracleClient.OracleConnection("Data Source=DB01;User Id=scott;Password=tiger")
PS C:\> $connection.Open()
PS C:\> (new-Object DATA.OracleClient.OracleCommand("select 'Hello World' from dual",$connection)).ExecuteScalar()
Hello World
PS C:\> $connection.Close()

ODP :

PS C:\> [Reflection.Assembly]::LoadFile("C:\oracle\product\11.1.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\Oracle.DataAccess\2.111.6.0__89b483f429c47342\Oracle.DataAccess.dll

PS C:\> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS C:\> (new-object Oracle.DataAccess.Client.OracleCommand("select 'helloworld' from dual",$connection)).executescalar()
helloworld
PS C:\> $connection.close()
PS C:\> $connection.dispose()

Read more : Oracle Data Provider for .NET Developer’s Guide

PS: also possible


PS> $dataset = New-Object Data.dataset
PS> (New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$connection)).fill($dataset)
14
PS> ($dataset.tables[0]|where{ $_.empno -eq 7902 }).ename
FORD

on Windows Management Instrumentation

I have a bit versed into powershell over the last months, I just cannot stop discovering new gems.

Ex : Win32 Classes

Self-explanatory examples :


PS> (get-wmiobject win32_processor).currentclockspeed
3292
PS> (get-wmiobject win32_operatingsystem).version
5.1.2600
PS> (get-wmiobject win32_proxy).ProxyServer
proxy.example.com
PS> (get-wmiobject win32_proxy).ProxyPortNumber
8080

it goes on and on and on… the ways those things are accessible from the prompt in interactive command line mode or within a script is amazing me

scott.emp in Sybase

As part of my dba job, I have a few Sybase DBs around. I created the well known Oracle Scott tables to play with

$ isql -U sa -P ""
1> sp_addlogin "scott","tiger7"
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> create database lsc01
2> go
CREATE DATABASE: allocating 1536 logical pages (6.0 megabytes) on disk 
  'data_dev' (1536 logical pages requested).
Database 'lsc01' is now online.
1> use lsc01
2> go
1> sp_adduser scott
2> go
New user added.
(return status = 0)
1> grant create table to scott
2> go
1> exit
$ isql -U scott -P tiger7 -D lsc01
1> create table dept(deptno int constraint pk_dept primary key, dname varchar(14), loc varchar(13))
2> go
1> insert into dept values(10,'ACCOUNTING','NEW YORK')
2> go
(1 row affected)
1> insert into dept values(20,'RESEARCH','DALLAS')
2> go
(1 row affected)
1> insert into dept values(30,'SALES','CHICAGO')
2> go
(1 row affected)
1> insert into dept values(40,'OPERATIONS','BOSTON')
2> go
(1 row affected)
1> create table emp(empno int constraint pk_emp primary key, ename varchar(10), job varchar(9), mgr int null, hiredate date, sal float, comm float null, deptno int constraint fk_dept references dept)
2> go
1> insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20)
2> go
(1 row affected)
1> insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
2> go
(1 row affected)
1> insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
2> go
(1 row affected)
1> insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
2> go
(1 row affected)
1> insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
2> go
(1 row affected)
1> insert into emp values (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
2> go
(1 row affected)
1> select ename,dname,d.deptno,empno from dept d left join emp e on (d.deptno=e.deptno)
2> go
 ename                dname                        deptno      empno       
 -------------------- ---------------------------- ----------- ----------- 
 KING                 ACCOUNTING                            10        7839 
 CLARK                ACCOUNTING                            10        7782 
 MILLER               ACCOUNTING                            10        7934 
 FORD                 RESEARCH                              20        7902 
 SCOTT                RESEARCH                              20        7788 
 JONES                RESEARCH                              20        7566 
 ADAMS                RESEARCH                              20        7876 
 SMITH                RESEARCH                              20        7369 
 WARD                 SALES                                 30        7521 
 BLAKE                SALES                                 30        7698 
 ALLEN                SALES                                 30        7499 
 JAMES                SALES                                 30        7900 
 TURNER               SALES                                 30        7844 
 MARTIN               SALES                                 30        7654 
 NULL                 OPERATIONS                            40        NULL 

(15 rows affected)
1> commit
2> go

Sql file to download : emp-dept.sql

There are obviously a few differences, the password is associated with a login that belongs to the “db server”, and the user is created in the database “lsc01″ and associated to the login. In Sybase, one database server (typically one Unix Process), contains more than one database. There is also a master database, which is as sensitive as your system tablespace. Still in the official Sybase administrator course, they already teach you how to manually update the dictionary…

There is no varchar2 or number, and the default for a column is not null. The equivalent of DESC EMP is sp_help emp, but it is more verbose. The columns are case-sensitive, create table EMP is not the same as create table emp.

Not all “ANSI” code will work, like DATE ’2000-01-01′, FULL OUTER JOIN, USING and all those Oracle “ANSI” things are not meant “COMPATIBLE WITH SYBASE” but more “CONFORM TO ANSI”

ok, that said, it was pretty straightforward

Unix timestamp in powershell

I was a bit playing with powershell. I wanted to know the week of month, day of weeks, and other gems. It is chockingly easy !

Unix Timestamp :

PS> get-date -uformat %s
1329322194.18894

do not miss this important update : Powershell and Dates
To get the day of week as number, you can chose between [int](get-date).dayofweek and get-date -uformat %u

What does uformat stand for ? Unix Format ! This is probably why powershell is so good, they integrated all unix and .net goodies in one tool :-)

Ok, one more, get the week of month :

PS> (Get-WmiObject Win32_LocalTime).weekinmonth
3

This system is for the use of authorized users only.

How to bypass the login banners?

There is actually more than one banner to bypass. One of the them is the message of the day banner, commonly located in /etc/motd. Typically friendly, example in AIX

****************************************************************************
*                                                                          *
*                                                                          *
*  Welcome to AIX Version 6.1!                                             *
*                                                                          *
*                                                                          *
*  Please see the README file in /usr/lpp/bos for information pertinent to *
*  this release of the AIX Operating System.                               *
*                                                                          *
*                                                                          *
***************************************************************************

This is easy to bypass, simply place .hushlogin file on your serverside homedirectory :


$ touch $HOME/.hushlogin
$

Yes, it is that easy.

A bit more cumbersome is the ssh banner. Which rather have an aggressive look with criminal punishment threats.


|-----------------------------------------------------------------|
| This system is for the use of authorized users only.            |
| Individuals using this computer system without authority, or in |
| excess of their authority, are subject to having all of their   |
| activities on this system monitored and recorded by system      |
| personnel.                                                      |
|                                                                 |
| In the course of monitoring individuals improperly using this   |
| system, or in the course of system maintenance, the activities  |
| of authorized users may also be monitored.                      |
|                                                                 |
| Anyone using this system expressly consents to such monitoring  |
| and is advised that if such monitoring reveals possible         |
| evidence of criminal activity, system personnel may provide the |
| evidence of such monitoring to law enforcement officials.       |
|-----------------------------------------------------------------|

Well, you could delete it from your ssh server but this would not please your sysadmins. The banner is actually a pre-authentication banner, so you must configure it on the client. With the unix ssh client, you lower the log level. Either with a command line option, -o LogLevel=quiet, or in a configfile

$ cat $HOME/.ssh/config
LogLevel=Quiet
StrictHostKeyChecking=no
NumberOfPasswordPrompts=1

This is so quiet that you will not get any feedback if you cannot connect, but I prefer quiet than noisy.

A very commonly used Windows ssh client is putty, and there, O miracle, there is a pre-authentication-banner option to uncheck in SSH-Auth.

This appeared in putty 0.62 and it made my day today :-)

The new metalink interface

I just started using the New My Oracle Support User Interface

It is a zero-flash interface with more capabilities than the “html” interface we used the previous years. It still provides about the same browser experience as the flash one.

Have a try… according to Oracle : “it will eventually replace the Flash version”
To my French speaking readers : eventually does not mean “éventuellement” but rather “à l’occasion”.
Google Translate is quite ok this time : “elle finira par remplacer la version Flash”

change system wide PATH variable in Windows

This is utterly simple and mostly you did it with the mouse.

To do it with command line and powershell, proceed this way.

Open a powershell window


powershell

or if you have no right to edit the machine-wide environment, runas admin

runas /user:yourdomain\youruserplusadmin powershell

then access the environment assembly


[environment]::setEnvironmentVariable("PATH","c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0","MACHINE")

MACHINE is a for a Machine-wide setting and USER is for a USER-wide setting.

I hope it will be of some use

Completly unrelated, Enterprise Manager 12c for AIX is out. Do not wait it for HPUX, it is not planned. For Windows 64bit, it will come “between March and May 2012″ according to note 793512.1 on metalink

Transport tablespace over db links

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

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


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

Tablespace created.

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

Table created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace test read only;

Tablespace altered.


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

create a database link on the target database DB02


SQL> create database link l using 'DB01';

Database link created.

Then import the tablespace via db link


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

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

Fast start failover

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

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

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

lsc01 my primary, lsc05 my standby


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

then you need to start the observer in the background :


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

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

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


ENABLE FAST_START FAILOVER

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


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

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

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

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


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

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

Ok, let’s see how fast I can switch


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

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

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

Failover to standby with a delay (until time)

Disaster scenario :

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

First, you create your primary and standby databases.

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


DGMGRL> edit database sdb01 set property DelayMins=1440;

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

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

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

Wait one day ;-)

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


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

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

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

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

Ok. Shutdown the production.


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

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


SQL>  alter database recover managed standby database cancel;

Database altered.

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

On standby


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

On standby, start the database as a primary database


SQL> alter database activate standby database;

Database altered.
SQL> alter database open;

Database altered.

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

Enterprise Manager command line interface

emcli has been around for a while, but in 12c the installation has never been easier

do not search on otn for the jar, go to

download with : Setup –>My Preferences –>Command line interface –> download

install with : java -jar emclikit.jar client -install_dir=/u01/app/oracle/emcli

configure with : emcli setup -url=https://precision.example.com:4901/em -username=sysman -password=sysmanpw -dir=/u01/app/oracle/emcli.

That’s it.

Let’s try


$ emcli get_targets
Status  Status           Target Type           Target Name                        
 ID                                                                               
1       Up               host                  precision.example.com              
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mgc                                
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/O
                                               CMRepeater                         
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mpbs                               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-sysman_mds               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-owsm                     
1       Up               oracle_apache         /EMGC_GCDomain/instance1/ohs1      
1       Up               oracle_apm            /EMGC_GCDomain/GCDomain/EMGC_OMS1/o
                                               racle.security.apm(11.1.1.3.0)     
1       Up               oracle_beacon         EM Management Beacon               
1       Up               oracle_database       LSC01                              
1       Up               oracle_database       LSC02                              
1       Up               oracle_database       LSC03                              
1       Up               oracle_database       LSC05                              
1       Up               oracle_database       LSC04                              
1       Up               oracle_dbsys          LSC01_sys                          
1       Up               oracle_dbsys          LSC03_sys                          
1       Up               oracle_dbsys          LSC04_sys                          
1       Up               oracle_dbsys          LSC02_sys                          
1       Up               oracle_em_service     EM Console Service                 
1       Up               oracle_em_service     EM Jobs Service                    
1       Up               oracle_emd            precision.example.com:1830         
1       Up               oracle_emrep          Management Services and Repository 
-9      n/a              oracle_home           oms12g1_8_precision                
-9      n/a              oracle_home           WebLogicServer10_3_5_0_0_precision 
-9      n/a              oracle_home           OraDb10g_home1_5_precision         
-9      n/a              oracle_home           OraDb11g_home1_1_precision         
-9      n/a              oracle_home           agent12g1_13_precision             
-9      n/a              oracle_home           webtier12g1_24_precision           
-9      n/a              oracle_ias_farm       EMGC_GCDomain                      
1       Up               oracle_listener       LISTENER_precision.example.com     
1       Up               oracle_oms            precision.example.com:4890_Manageme
                                               nt_Service                         
1       Up               oracle_oms_console    precision.example.com:4890_Manageme
                                               nt_Service_CONSOLE                 
1       Up               oracle_oms_pbs        precision.example.com:4890_Manageme
                                               nt_Service_PBS                     
-9      n/a              weblogic_domain       /EMGC_GCDomain/GCDomain            
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_OMS1  
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER 

All green (one could argue command line has no color)

delete unused shared memory segments from an Oracle instance

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

First, list the ipc process


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

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

Get a list of the running databases


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

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

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

Ok, with ipcrm


$ ipcrm -m 79691782

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

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

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

- Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

- Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

- Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

- Configure the clients
sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

ldap.ora
DEFAULT_ADMIN_CONTEXT = “DC=example,DC=com”
DIRECTORY_SERVER_TYPE = AD

- test it!
tnsping first


C:\> tnsping db01

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 10-NOV-2011 14:42:16

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

Used parameter files:
C:\oracle\product\11.2.0\client_3\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
_DATA=(SID=DB01)))
OK (20 msec)

I wrote a simple java program to check the connection :


import java.sql.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    ods.setDriverType("oci");
    ods.setTNSEntryName("DB01");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


C:\> set PATH=C:\oracle\product\11.2.0\client_3\bin
C:\> javac -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld.java
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar -Doracle.net.tns_admin=C:\oracle\product\11.2.0\client_3\network\admin HelloWorld
Hello World

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error


import java.sql.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    ResultSet res = DriverManager.
      getConnection("jdbc:oracle:thin:@ldap://example.com:389/db01,cn=OracleContext", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


C:\>java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld
Exception in thread "main" java.sql.SQLException: I/O-Fehler: JNDI Package failure avax.naming.NamingException: [LDAP:error code 1 - 000004DC: LdapErr: DSID-0C0906DC, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v1db0 ]; remaining name 'cn=db01,cn=OracleContext'
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at HelloWorld.main(HelloWorld.java:5)

As the error message says, the ldap server requires a bind

Let’s try to bind


import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    ods.setDriverType("thin");
    Properties prop = new Properties();
    prop.put("java.naming.security.authentication", "simple");
    prop.put("java.naming.security.principal","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
    prop.put("java.naming.security.credentials", "my_ad_pw");
 
    ods.setConnectionProperties(prop);

    ods.setURL("jdbc:oracle:thin:@ldap://w2k8.local:389/db01,cn=OracleContext,DC=example,DC=com");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

This works!

Check if it a program is already running in Unix

There is more than one way to do it, the safe is probably to check if /home/lsc/OH_YES_I_AM_RUNNING exists and believe it. This is called the file.PID method and is widely used (Apache used to use it since a long long time). It needs file. It needs cleanup if you reboot your server in the middle of something (and surely you do not want to delete old pid files yourself)

Ok, often you see this :


ps -ef | grep program

There you list all processes and check the lines that contain program. So some does a vi program or anything worse (emacs?), you will get more rows than needed.

Maybe it is fine to run program with different arguments, this must be decided.

Well, take a simple test case :
x1.sh and x2.sh :

#!/bin/ksh
while :
do
  date  > /dev/null
done

let’s try to use ps


$ nohup ./x1.sh &
$ nohup ./x2.sh &
$ jobs
[2] +  Running                 nohup ./x2.sh &
[1] -  Running                 nohup ./x1.sh &
$ ps -ef | egrep 'x[12]'
  u22  9240796  6226164  30 14:56:52  pts/2  0:00 /bin/ksh ./x2.sh
  u22 20840608  6226164  31 14:56:48  pts/2  0:01 /bin/ksh ./x1.sh

So fine so good, I see I have one instance of each program.

Let’s try to see if the results are consistent over time :

 $ n=9999;while :
  do 
    ps -ef | 
      egrep 'x[12].sh'>f
    if [ $(wc -l <f) != $n ]
    then 
      n=$(wc -l <f)
      echo
      date
      cat f
      echo "==> $n"
    fi
  done

Fri Oct 28 15:01:01 CEST 2011
  u22  9240796  6226164  32 14:56:52  pts/2  0:14 /bin/ksh ./x2.sh
  u22 20840608  6226164  28 14:56:48  pts/2  0:14 /bin/ksh ./x1.sh
==>        2

Fri Oct 28 15:01:08 CEST 2011
  u22  9240796  6226164  50 14:56:52  pts/2  0:14 /bin/ksh ./x2.sh
==>        1

Fri Oct 28 15:01:09 CEST 2011
  u22  9240796  6226164  52 14:56:52  pts/2  0:14 /bin/ksh ./x2.sh
  u22 20840608  6226164  53 14:56:48  pts/2  0:15 /bin/ksh ./x1.sh
==>        2

Fri Oct 28 15:01:17 CEST 2011
  u22  9240796  6226164  40 14:56:52  pts/2  0:15 /bin/ksh ./x2.sh
  u22 10944520  9240796   0 15:01:17  pts/2  0:00 /bin/ksh ./x2.sh
  u22 20840608  6226164  31 14:56:48  pts/2  0:16 /bin/ksh ./x1.sh
==>        3

the fact that a subshell (pid 10944520 ) of x2 appear is not a problem for me. I have much more of a problem at 15:01:08 where x1 disappeared !

Conclusion : you cannot trust ps

shell and list of files

How do you loop thru a list of files?

For instance you want to archive than delete all pdf documents in the current directory :

Bad practice :


tar cvf f.tar *.pdf
rm *.pdf

There are multiple issue with the command above

1) new files could come during the tar, so the rm will delete files that have not been archived


filelist=$(ls *.pdf)
tar cvf f.tar $filelist
rm $filelist

2) if there is no file, tar and rm will return an error


filelist=$(ls|grep '\.pdf')
if [ -n "$filelist" ]
then
  tar cvf f.tar $filelist
  rm $filelist
fi

3) this will not work for long list (above 100k documents)


filelist=/tmp/filelist.$(date "+%Y%m%d%H%M%S").$$.$RANDOM
ls|grep '\.pdf' > $filelist
if [ -s "$filelist" ]
then
  tar cvfL f.tar $filelist
  for f in $(<filelist)
  do
    rm $f
  done
fi

As you see, this require special handling. tar for instance use the -L option to accept a list of files, rm could delete files one by one (or in bunches with xargs -L).

This 100’000 limit (the limit may vary for your shell/os) is something that often gets forgotten.

Typical error that could occur are


ksh: no space
bash: Arg list too long

pstree in AIX

For those who do not want to download some linuxlike freeware on your aix box, use ps -T :)


ps -fT 2412672
     UID     PID    PPID   C    STIME    TTY  TIME CMD
  oracle 2412672       1   0   Sep 05      -  0:00 /u01/app/oracle/product/OAS
  oracle  630956 2412672   0   Sep 05      -  6:11     \--/u01/app/oracle/prod
  oracle 1347672  630956   0   Sep 05      - 15:32        |\--/u01/app/oracle/
  oracle 1437836  630956   0   Sep 05      -  1:02        |\--/u01/app/oracle/
  oracle  880820 1437836   0   Sep 05      -  0:32        |   |\--/u01/app/ora
  oracle 1036532 1437836   0   Sep 05      -  0:00        |   |\--/u01/app/ora
  oracle 1134796 1437836   0   Sep 05      -  0:01        |   |\--/u01/app/ora
  oracle 1343712 1437836   0   Sep 05      -  0:33        |   |\--/u01/app/ora
  oracle 1368166 1437836   0   Sep 05      -  1:11        |   |\--/u01/app/ora
  oracle 1384684 1437836   0   Sep 05      -  0:33        |   |\--/u01/app/ora
  oracle 1392862 1437836   0   Sep 05      -  0:32        |   |\--/u01/app/ora
  oracle 1396898 1437836   0   Sep 05      -  0:33        |   |\--/u01/app/ora
  oracle 1482978 1437836   0   Sep 05      -  0:32        |   |\--/u01/app/ora
  oracle 1527890 1437836   0   Sep 05      -  0:00        |   |\--/u01/app/ora
  oracle 1781798 1437836   0   Sep 05      -  0:32        |   |\--/u01/app/ora
  oracle 2195474 1437836   0   Sep 26      -  0:13        |    \--/u01/app/ora
  oracle 1626296  630956   0   Sep 05      - 13:49         \--/u01/app/oracle/

Large zip on Windows

I have never been a Microsoft fanatic nor an anti-microsoft terrorist, but today I could not believe that large compressed folders got corrupted in Windows !

I have send a relatively small zip file (5gb, peanuts) from AIX to Windows per sftp and in Windows Explorer, some files in the compressed folder (read zip) were just pointing to the wrong content.

I had some issues with large zip files on unix, but this was last century! Howcome could a modern filesystem/operating system have such issues?

I have found a few bugs on support.microsoft.com.

Ex: Compressed folder becomes corrupted when larger than 2 gigabytes
Workaround : make sure that you limit the size of a compressed folder to 2 GB or less

Amazing!

_optimizer_random_plan parameter

I was trying to find a workaround for a bug in 11.2.0.2

SELECT *  FROM 
  (SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'), 
  (SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%') 
  WHERE C = B(+);

         B          C
---------- ----------
         2          3

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|      |     1 |     4 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

As dummy is not Y, B could not be 2.

Ok, I tried :


alter session set "_optimizer_random_plan"=1;

SELECT *  FROM 
  (SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'), 
  (SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%') 
  WHERE C = B(+);

         B          C
---------- ----------
                    3

Execution Plan
----------------------------------------------------------
Plan hash value: 837538736

-------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1146 |  5730 |    27G|
|   1 |  MERGE JOIN OUTER    |      |   603K|  2946K|    27G|
|*  2 |   TABLE ACCESS FULL  | DUAL |   392K|   767K|   136K|
|   3 |   VIEW               |      |     2 |     6 | 69180 |
|*  4 |    FILTER            |      |       |       |       |
|*  5 |     TABLE ACCESS FULL| DUAL |   123K|   240K| 69180 |
-------------------------------------------------------------

Cool, I got correct results! the fact that the cost jumped from 4 to 27 Billions is just a minor annoyance I suppose :twisted:

I also tried


alter session set "_optimizer_random_plan"=0; -- default

alter session  set "_complex_view_merging"=false;

SELECT *  FROM 
  (SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'), 
  (SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%') 
  WHERE C = B(+);

         B          C
---------- ----------
                    3

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     1 |     5 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |     3 |            |          |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

The cost is now 5 and instead of 4 and the results are correct

The first thing I did is opening a SR, now I am impatiently waiting for Oracle Support guidance…

Duplicate table over database link

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

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


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

PL/SQL procedure successfully completed.

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.start_job(:n)

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

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

Oracle Certified Master