Laurent Schneider

Sounds like an april fool, but I wonder what is + ‘x’ supposed to do 🙂

SQL> select + 'x' from dual;

+
-
x

it does not convert to number as 0 + ‘1’ would do

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

Sometimes you wait a feature for so long that even if it is small and common, you feel greatly happy

In Toad 11.5.0.38. Greatly replace the “right-click” + “close tab”

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


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


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

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

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

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

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 🙂

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”

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

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

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.

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)

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)

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.

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.(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.(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!

download patch 10404530

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

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

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