Tuesday, February 16, 2010

Oracle 9.0.1.1 exp bug

I tried to run an intranet app from home, with a database connection from my laptop tunneled to Telkom HQ Data Center.
Seems that the application tooks too many queries and data volume were bit too much (OK, maybe I should profile the data volume & query counts...), it took me an hour of trial and error so that I realized : it is impossible to run the app without timing out while in tunneled connection.
OK, then proceed to think.. If this is were my usual application, what would I do ? I would fire up MySQL Administrator and backup the externally located database in my system, then restore it to my local MySQL database.
For oracle database, the tool alternatives were:
- use TOAD to move the database (which, I think would be tedious, and also I have no TOAD installed in my system)
- use Oracle Datapump, which we cannot use because the database are 9.0.1.1, where such technology haven't exist yet, and also .. I have no experience at this moment on Datapump.
- migrate to MySQL, and migrate back to Oracle, because each RDMBS vendor provided tools to migrate from almost any database into _their_ RDBMS
- use exp/imp
Migrating back and forth gives risk of inconsistent data type in the target database. So I choose Exp/imp.
But.. exp/imp have constraints that must be satisfied :
  1. version of the exp utility must match with the source database
  2. version of the imp utility must match with the target database
  3. the user being used in the source database and target database must in the similar rank, that is, they're both SYSDBAs or they're both normal users
These facts I found out the hard way. Please be free to comment if any of these were incorrect.
The exp utility installed in my laptop system are 10.2.0.1.0, and the server is 9.0.1.1. So I must either (a) find one oracle client installation which version is 9.0.1.1, or (b) run the exp in the server.
Running exp on the server, I got these:

EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.


Puzzled for some moment, and after some googling I found :
GRANT EXECUTE ON sys.lt_export_pkg TO exp_full_database;
or
GRANT EXECUTE ON sys.lt_export_pkg TO PUBLIC;

And now I could export sucessfully with no strange warnings. Then downloaded the exported DMP file, import it here using oracle 10.2's IMP, and continue debugging the app..

About Oracle and MySQL
Some readers who knows me well will wonder why most of my posts is about troubleshooting Oracle, while I am one kind of person who will use MySQL for all purpose if I could. I think, its not that I am forced to work with much more Oracle database than I liked to, its more like that I seldom have problems with MySQL - export/imports run beautifully using MySQL Administrator, and no corrupted data yet when using MySQL.
Ok to be sure lets backup my one and only production app running on mysql :)

Monday, February 8, 2010

saprfc installation on centos 5


After hesitating for a while, I struggled to bring saprfc to live on a Centos 5 installation.
Ok, first, the people I'm helping already installed rfc sdk in /usr/sap/rfcsdk. I am using RFC SDK extracted from RFC_45-20000055.SAR that I've downloaded from SAP Service Marketplace Support links (http://service.sap.com/). The SAR file is suitable for Linux i386 architecture, you might want to use RFC_45-10003377.SAR for Linux x86_64 architecture.
Now i need to compile sap:rfc extension. I used saprfc-1.4.1 downloaded from http://saprfc.sourceforge.net/. But, no phpize in path, so i must install php-devel first
> yum install php-devel
but, because the proxy setup is not done (our server lives outside the DMZ), I must edit yum.conf :
> vi /etc/yum.conf
and add one line :
> proxy=http://....
Then I'm ready to try php-devel again.
> yum install php-devel
OK. now try to compile sap:rfc. R the INSTALL file in a flash, and tries these steps (the part b in the INSTALL file)
> cd /home/saprfc-1.4.1
> phpize
What? no phpize? I was mistaken thinking that php-devel contains the phpize executable..
Seems that my yum install php-devel failed. Retrying, now with php-xml and php-snmp (becuase yum seems complained that these are not compatible with the version of php-devel I tried to install)
> yum install php-devel php-xml php-snmp
Hurray.. install complete.
retries compiling sap:rfc
> cd /home/saprfc-1.4.1
> phpize
[root@localhost saprfc-1.4.1]# phpize
Configuring for:
PHP Api Version: 20041225
Zend Module Api No: 20050922
Zend Extension Api No: 220051025
config.m4:30: warning: AC_CANONICAL_HOST invoked multiple times
config.m4:30: warning: AC_CANONICAL_HOST invoked multiple times
I don't know about AC_CANONICAL_HOST, lets ignore them for now.
> ./configure
all seems OK. If there is an error about missing gcc you should
>yum install gcc
If not, just proceed to make..
> make install
it compiles a saprfc.so in /usr/lib/php/modules.
I took a liberty to write these ini /etc/php.d/saprfc.ini :
; Enable saprfc extension module
extension=saprfc.so
now restart apache and pray..
> /etc/init.d/httpd restart

PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/librfccm.so' - /usr/lib/php/modules/librfccm.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/saprfc.so' - libstdc++-libc6.2-2.so.3: cannot open shared object file: Too many levels of symbolic links in Unknown on line 0
Wonderful. Things so complicated from this moment.

I think i found the problem:
[root@localhost lib]# ldd /usr/lib/librfccm.so
linux-gate.so.1 => (0x0035e000)
libdl.so.2 => /lib/libdl.so.2 (0x004ae000)
librt.so.1 => /lib/librt.so.1 (0x006de000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00110000)
libc.so.6 => /lib/libc.so.6 (0x00127000)
libstdc++-libc6.2-2.so.3 => not found
libm.so.6 => /lib/libm.so.6 (0x0026a000)
/lib/ld-linux.so.2 (0x00896000)
[root@localhost lib]# locate libstdc++-libc6.2-2.so.3
/usr/lib/libstdc++-libc6.2-2.so.3
[root@localhost lib]# ls -l /usr/lib/libstdc++-libc6.2-2.so.3
lrwxrwxrwx 1 root root 18 Jan 26 10:47 /usr/lib/libstdc++-libc6.2-2.so.3 -> libstdc++.so.5.0.5
[root@localhost lib]# locate libstdc++.so.5.0.5
/usr/lib/libstdc++.so.5.0.5
[root@localhost lib]# ls -l /usrlib/libstdc++.so.5.0.5
ls: /usrlib/libstdc++.so.5.0.5: No such file or directory
[root@localhost lib]# ls -l /usr/lib/libstdc++.so.5.0.5
lrwxrwxrwx 1 root root 18 Jan 26 10:50 /usr/lib/libstdc++.so.5.0.5 -> libstdc++.so.5.0.5

Yes. Endless link of links.
Tried to fix these with updating compat-libstdc++-33 and compat-libstdc++-296 (yes, I only know that the current version of stdc++ being installed is libstdc++so.6.x, and have no idea which of the compat packages contains the 5.0.5 version)

yum update compat-libstdc++-33 compat-libstdc++-296
...

[root@localhost ~]# ldd /usr/lib/librfccm.so
linux-gate.so.1 => (0x00854000)
libdl.so.2 => /lib/libdl.so.2 (0x00110000)
librt.so.1 => /lib/librt.so.1 (0x00114000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00811000)
libc.so.6 => /lib/libc.so.6 (0x00358000)
libstdc++-libc6.2-2.so.3 => /usr/lib/libstdc++-libc6.2-2.so.3 (0x0011d000)
libm.so.6 => /lib/libm.so.6 (0x0015f000)
/lib/ld-linux.so.2 (0x00896000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00186000)

everything seems in order.

> /etc/init.d/httpd restart

and now... drum roll please..