Monday Jun 09, 2008

DTrace: change ORACLE SID

I got a origin unknown ear file which accesses ORACLE in its code. I heard it specifies oracle instance in old form like below.

jdbc:oracle:thin:@theirhost.theirdomain:1521:ORCL

Here, 'ORCL' is the instance name.

I want to run the ear file without touching anything but I don't have an oracle instance named 'ORCL'. Instead, I have an instance named 'ora9'. Like:

$ ps -ef | grep ora_
  ora10g 27413     1   0   Jun 04 ?           6:02 ora_cjq0_ora9
  ora10g 27401     1   0   Jun 04 ?           0:16 ora_mman_ora9
  ora10g 27405     1   0   Jun 04 ?           0:26 ora_lgwr_ora9
  [...]

$ sqlplus 'scott/tiger@(description=(connect_data=(sid=ora9))(address=(port=1521)(host=localhost)(protocol=tcp)))'
SQL\*Plus: Release 10.2.0.1.0 - Production on Mon Jun 9 11:50:56 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL>

So..., now I need to either...

  1. Rename the instance.  ora9 -> ORCL.
  2. Make the instance appear as 'ORCL' to clients.

Renaming the instance is no big deal if you can bring down the whole instance. I could change it with just 2 command lines. It is not worth blogging about it. So..., in part because I want to make it worth to blog about, I decided to experiment on #2 by using dtrace.

  1. oracle clients connects via listener over the net. See how the listener gets ORACLE SID. It can be specified in listener.ora file or notified from oracle instance at runtime.

    $ lsnrctl start && truss -r all -t \\!all -p $(pgrep tnslsnr)
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-JUN-2008 12:30:03
    [...]
    The command completed successfully
    /1:     read(14, 0x1002D3986, 8208)                     = 104
    /1:       \\0 h\\0\\001\\0\\0\\001 901 ,\\0\\0  \\07FFF7F\\b\\0\\0\\001\\0 .\\0 :\\0\\0\\0\\0
    /1:        A A\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0 ( C O N N E
    /1:        C T _ D A T A = ( C O M M A N D = s e r v i c e _ r e g i s t e
    /1:        r _ N S G R ) )
    /1:     read(14, 0x1002D1896, 8208)                     = 2170
    /1:       \\b z\\0\\006\\0\\0\\0\\0\\0\\0\\0\\b p $\\bFF0301\\012 4 4 x x 4 x01 #01 #01
    /1:        g E #01 #01 g E #01\\0 x g E #01\\0\\080\\0\\0 H02\\0\\0\\004\\0\\0 !\\0\\0
    /1:       \\00106 WC8D0\\090\\0\\0\\0 # B E C 7 6 C 2 C C 1 3 6 - 5 F 9 F - E 0
    /1:        3 4 - 0 0 0 3 B A 1 3 7 4 B 3\\003\\0 e\\001\\001\\0\\0\\0\\0\\0\\0\\080\\0
    /1:       07F805\\0\\0\\004\\0\\0 !\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\001\\0\\0\\010\\002\\0\\0\\0\\0
    /1:       \\0\\0\\0\\0\\00106 ; g\\b\\0\\0\\001\\0\\0\\0\\0FFFFFFFF yD2\\090\\0\\0\\0\\0\\0\\0
    /1:       \\0\\0\\0\\0\\00106 ; jB0 ND3 ` xC791 k05E0 D\\014 O8E k1A\\0\\0\\005\\0\\0
    /1:       \\0\\0\\0\\0\\003A1F8 gC8\\0\\0\\00F\\0\\0\\0\\0FFFFFFFF yD201A8\\0\\0\\018\\0\\0
    /1:       14\\0\\0\\0\\0\\f\\0\\0\\0AA\\0\\0\\0\\0\\0\\0\\002\\0\\0\\00106 ; fF0 o r a 9\\0 (
    /1:        H O S T = m y h o s t 1 )\\0\\0\\0\\001\\0\\0\\01E\\001\\0\\0\\0\\0\\0\\0\\0\\0
    /1:       \\003A1EE10B8\\0\\0\\002\\0\\0\\0\\0\\0\\0\\003A1EE1090\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0
    

    Here you can see listener got register packet from either dispatcher(D000) or PMON. SID string(ora9) appears in 282th byte.

  2. Let's spoof tnslsnr process by dtrace. This is all what is necessary to make oracle clients over the net to work with new SID.

    #!/usr/sbin/dtrace -s
    
    #pragma D option destructive
    #pragma D option defaultargs
    
    dtrace:::BEGIN
    /$$1 == ""/
    {
            printf("Usage: %s (new sid in 4 letters)\\n", $0);
            exit(1);
    }
    
    syscall::read:entry
    /execname=="tnslsnr"/
    {
      self->buf=arg1;
    }
    
    syscall::read:return
    /execname=="tnslsnr" && arg1 == 2170/
    {
      copyoutstr($$1,self->buf+282,4);
      exit(0);
    }
    

  3. Let's see how it works.

    $ /tmp/newSID.d ORCL
    dtrace: script '/tmp/newSID.d' matched 3 probes
    dtrace: allowing destructive actions
    \^Z
    [1]+  Stopped                 /tmp/newSID.d ORCL
    $ bg
    [1]+ /tmp/newSID.d ORCL &
    $ lsnrctl start
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-JUN-2008 13:45:50
    [...]
    The command completed successfully
    CPU     ID                    FUNCTION:NAME
      0   1393                      read:return
    [1]+  Done                    /tmp/newSID.d ORCL
    $ sqlplus 'scott/tiger@(description=(connect_data=(sid=ora9))(address=(port=1521)(host=localhost)(protocol=tcp)))'
    ERROR:
    ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
    $ sqlplus 'scott/tiger@(description=(connect_data=(sid=ORCL))(address=(port=1521)(host=localhost)(protocol=tcp)))'
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options
    SQL>
    


The script above only works for 4 letters length SID. To make it work with longer SID like 'O10GUTF8',  I tried to tamper with read() system call's return value. But I couldn't find a way.

About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today