R3trans error after oracle upgrade

[Former Member]

Hi,

Hi,

We are doing a database upgrade as a part of NW04s upgrade.Our source

Oracle release was 9.2.0.6 and now we have upgraded it to 10.2.0.2.The

database upgrade was successfull and the database is up and running.When we execute R3trans -d it fails with the return code 12,the detailed error is as follows

.....4 ETW000 [dev trc ,00000] Assigning user session 110c26278 to service context 110c1b1d8

4 ETW000 20252 0.284724

4 ETW000 [dev trc ,00000] BEGIN DBMS_APPLICATION_INFO.SET_MODULE (:A0, :A1); END;

4 ETW000 94 0.284818

4 ETW000 [dev trc ,00000] BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO (:A0); END;

4 ETW000 127 0.284945

4 ETW000 [dev trc ,00000] Now '/@BIG' is connected (con_hdl 0, nls_hdl 0).

4 ETW000 2112 0.287057

4 ETW000 [dev trc ,00000] ALTER SESSION SET NLS_SORT = BINARY

4 ETW000 57 0.287114

4 ETW000 [dev trc ,00000] SELECT USERID,PASSWD FROM SAPUSER WHERE USERID IN (:A0, :A1)

4 ETW000 859 0.287973

4 ETW000 [dbsloci. ,00000] *** ERROR => ORA-942 when accessing table SAPUSER

4 ETW000 2276 0.290249

4 ETW000 [dev trc ,00000] Disconnecting from connection 0 ... 113 0.290362

4 ETW000 [dev trc ,00000] Rolling back transaction ... 136 0.290498

4 ETW000 [dev trc ,00000] Close user session (con_hdl=0,svchp=110c1b1d8,usrhp=110c26278)

4 ETW000 377 0.290875

4 ETW000 [dev trc ,00000] Now I'm disconnected from ORACLE 1563 0.292438

4 ETW000 [dev trc ,00000] Try to connect with default password 28 0.292466

4 ETW000 [dev trc ,00000] Connecting as SAPBIW/<pwd>@BIG on connection 0 (nls_hdl 0) ... (dbsl 640 110706)

4 ETW000 39 0.292505

4 ETW000 [dev trc ,00000] Nls CharacterSet NationalCharSet C EnvHp ErrHp ErrHpBatch

4 ETW000 43 0.292548

4 ETW000 [dev trc ,00000] 0 WE8DEC 1 110c12820 110c1ba58 110c1b388

4 ETW000 40 0.292588

4 ETW000 [dev trc ,00000] Assigning username to user session 110c26278 85 0.292673

4 ETW000 [dev trc ,00000] Assigning password to user session 110c26278 78 0.292751

4 ETW000 [dev trc ,00000] Starting user session (con_hdl=0,svchp=110c1b1d8,srvhp=110c1d838,usrhp=110c26278)

4 ETW000 41 0.292792

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCISessionBegin' failed: rc = 1017

4 ETW000 27471 0.320263

4 ETW000 [dbsloci. ,00000] *** ERROR => CONNECT failed with sql error '1017'

4 ETW000 39 0.320302

4 ETW000 [dblink ,00419] ***LOG BY2=>sql error 1017 performing CON [dblink#2 @ 419]

4 ETW000 133 0.320435

4 ETW000 [dblink ,00419] ***LOG BY0=>ORA-01017: invalid username/password; logon denied [dblink#2 @ 419]

4 ETW000 37 0.320472

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'BIG'"

what could be the reason?

Regards,

Cherry

[Former Member]

Change you TNSNAMES.ORA

the parameters (SERVER = DEDICATED) is not support by Oracle 10 and Sap

modify you TNSNAMES.ORA and delete this entry

example ...you new TNSNAMES.ORA

DWT.WORLD =

(DESCRIPTION =

(ADDRESS = (COMMUNITY = sap.world)(PROTOCOL = TCP)(Host = sdr3bw4)(Port = 1553))

(CONNECT_DATA =

(SID = DWT)

(GLOBAL_NAME = DWT.world)

)

)

LISTENER_DWT.WORLD =

(ADDRESS = (COMMUNITY = sap.world)(PROTOCOL = TCP)(Host = sdr3bw4)(Port = 1553))

Regards,

Eva Belén Garcia

[Former Member]

After setting the environment variable ORA_NLS33 /oracle/<SID>/920_64/ocommon/nls/admin/data

The problem was solved and the SAP is running fine.

[Former Member]

Hi Cherry

Login from your <SID>ADM user and run r3trans -d and other commands with proper syntax.It will not show any such error

Regards

Varun

[Former Member]

To make my 10g to work correctly with 640 kernel

i had to set NLS33

Also i had to use client 9207 as 10g was not working perfectly well with 10 client ( TMS and Support pack apllication were termating)

Refer note 953388 and 886783

Thanks

Naushad

[Former Member]

This error seems to be related to environement variables.

Did you take out the NLS variables related to 9i that the upgrade guide talks about and put the ones for 10g in?

Pleasae make sure these variables are permanently set in the the user env.

[Former Member]

Hi,

Whats the problem whether ur Oracle is not coming up or SAP.

First try to resolve error related to Oracle and then concentrate on SAP.

Looking at the conversations in blog, its confusing since u have posted in the same blog as that of Mr.Cherry

Anyways, just check your Oracle Client.

Check out the compatiblity matrix for SAP Kernel->Oracle Client->Oracle database version.

Please refer to SAP notes: 819829 and 180430

Kind Regards

Neenad

[Former Member]

I have tried everything in this thread to get R3trans -d to work and not error out at:

4 ETW000 [dev trc ,00000] got NLS_LANG='AMERICAN_AMERICA.UTF8' from environment

4 ETW000 56 1.060707

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCIEnvCreate' failed: rc = -1

4 ETW000 458786 1.519493

4 ETW000 [dblink ,00419] ***LOG BY2=>sql error -1 performing CON [dblink#2 @ 419]

4 ETW000 50760 1.570253

4 ETW000 [dblink ,00419] ***LOG BY0=>'OCI_ERROR' [dblink#2 @ 419] 48 1.570301

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

- I have Oracle 10.2.0.2 patched & running

- I have the 10g client for brtools

- I have the latest 92x_64 client for the 640 kernel

- I have checked the env variables, listener, tnsnames files and they are okay. tnsping works

_ I have the latest dbsl - dboraslib.sl for HP-UX

I do not know what to do next to get R3trans -d to connect to the database?

[Former Member]

I think its the problem with sidadm and orasid enviornment variables. double check the environment variables

thanks

prince jose

[Former Member]

R3trans -d connects if I

setenv ORACLE_HOME /oracle/DWT/920_64

But the home should be

setenv ORACLE_HOME /oracle/DWT/102_64

The database hase been upgraded to 10.2.0.2.

The clients are:

/oracle/10x_64

/oracle/92x_64

[Former Member]

I got R3trans –d to work by doing the following from note 836517. My confidence is lacking in this solution.

Can anyone tell me if these are the 2 correct env variables to use???? Some of the documentation said to remove ORA_NLS33.

Logon in as dwtadm

setenv ORA_NLS33 /oracle/client/92x_64/ocommon/nls/admin/data

setenv SHLIB_PATH /usr/sap/DWT/SYS/exe/run:/oracle/client/10x_64/instantclient

Kernel: 640 (latest download)

Oracle: 10.2.0.2

The clients are:

/oracle/10x_64

/oracle/92x_64 (latest download)

[Former Member]

Hello William,<br>

I'll try to help ( sorry if some info was already provided but I haven't check the complete thread )

<br>

First I recommend you to check the FAQ about the oracle client ( do not know the number now, I'll post it tomorrow )<br>

<br>

The oracle client neede by SAP on UNIX systems depends <i><b>ONLY </b></i>on the kernel that is being used.

<br>

You have kernel 6.40. This kernel is dynamically linked agains oracle client 9.2.

<br>

This means that you have to install and configure this oracle client ( ORA_NLS33 is needed )

<br>

One requirement to use kernel 6.40 with oracle 10G is the usage of oracle client 9.2.0.7 ( this is a <b>MUST</b>, you cannot use an older client )

<br>

Continuing with the explanation. <img src="http://forum.explosm.net/images/smilies/facts3.gif" alt="Facts" />

<br>

<br>

You installed oracle 10g ( 10.2.0.2 ) tht means that you have to use BR* tools 700.

<br>

This tools are linked against oracle instantclient. For that reason you also have to configure your environment to use the instant client.

<br>

For that reason you need to set the SHLIB_PATH to include the instantclient directory ( no other oracle directory should be included )

<br>

I hope this clarify a little the issue.

[Former Member]

When using kernel 640 & Oracle 10g

1) Do you know if you have to change any SAP profile parameters to use the old Oracle 9.2 directories? Or should these paramters point to 10g?

DIR_ORAHOME

DIR_CLIENT_ORAHOME

DIR_SERVER_ORAHOME

2) What has to be changed on the application servers? Just SHLIB_PATH to include the instantclient and ORA_NLS33?

[Former Member]

Hi,

Regarding the second question, on the application servers you do not need to change anything.

BR* tools run always on the database server, therefore, no need for the instantclient on the AS at this moment; when/if you upgrade to ECC6 - Kernel 700 then you will need to install the instanclient, but not now.

At SAP level, it is not "necessary" to change the mentioned parameters. SAP Kernel will use the correct ones regardless of those settings.

You should only change the one related to the ORACLE_HOME, as it has changed.

[Former Member]

Hi William,

I'm struggling with the exact same problem now. Did you resolve the problem at your site? If so, what did you do?

Thanks.

Best regards,

Lars-Erik

[Former Member]

This is if you only have one SID on your box.

user: <sid>adm

home: /home/<sid>adm

script: .dbenv_<host>.csh

1. If you using 10.2.0.2 & kernel 640 then add these lines at the end of the script.

2. These lines need to be removed after you upgrade to kernel 7.00

setenv ORA_NLS33 /oracle/client/9207/ocommon/nls/admin/data

1. end Oracle RDBMS Environment

[Former Member]

You may want to use one of these lines for the ORA_NLS33 value:

setenv ORA_NLS33 /oracle/client/9207/ocommon/nls/admin/data

or setenv ORA_NLS33 /oracle/client/920_64/ocommon/nls/admin/data

or setenv ORA_NLS33 /oracle/<SID>/920_64/ocommon/nls/admin/data

This is only if you are using 10g with kernel 640.

[Former Member]

Check tnsping for the listener. Maybe the listener environment still has Oracle 9 entries. You might have to stop the listener, change to 10g and start back the listener.

[Former Member]

more tnsnames.ora

1. tnsnames.ora Network Configuration File: /oracle/DWT/102_64/network/admin/tnsn

ames.ora

1. Generated by Oracle configuration tools.

################

1. Filename......: tnsnames.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........:

################

DWT.WORLD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DWT)

)

)

LISTENER_DWT.WORLD =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553))

...skipping...

1. tnsnames.ora Network Configuration File: /oracle/DWT/102_64/network/admin/tnsn

ames.ora

1. Generated by Oracle configuration tools.

################

1. Filename......: tnsnames.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........:

################

DWT.WORLD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DWT)

)

)

LISTENER_DWT.WORLD =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553))

[Former Member]

tnsping DWT

TNS Ping Utility for HPUX: Version 10.2.0.2.0 - Production on 05-DEC-2006 10:52:07

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

Used parameter files:

/oracle/DWT/102_64/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWT)))

OK (0 msec)

[Former Member]

<b>Here is the original problem:</b>

R3trans -d

This is R3trans version 6.13 (release 640 - 12.12.05 - 14:24:42).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

R3trans finished (0012).

<b>R3tranx -x log has this error:</b>

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCIEnvCreate' failed: rc = -1

4 ETW000 3233 0.043222

4 ETW000 [dblink ,00419] ***LOG BY2=>sql error -1 performing CON [dblink#2 @ 419]

4 ETW000 475 0.043697

4 ETW000 [dblink ,00419] ***LOG BY0=>'OCI_ERROR' [dblink#2 @ 419] 46 0.043743

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

<b>Here is the whole log:</b>4 ETW000 R3trans version 6.13 (release 640 - 12.12.05 - 14:24:42).

4 ETW000 unicode enabled version

4 ETW000 ===============================================

4 ETW000

4 ETW000 date&time : 05.12.2006 - 13:19:46

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -x

4 ETW000 trace at level 2 opened for a given file pointer

4 ETW000 [dev trc ,00000] Tue Dec 5 13:19:46 2006 564 0.000564

4 ETW000 [dev trc ,00000] db_con_init called 41 0.000605

4 ETW000 [dev trc ,00000] create_con (con_name=R/3) 50 0.000655

4 ETW000 [dev trc ,00000] Loading DB library '/usr/sap/DWT/SYS/exe/run/dboraslib.sl' ...

4 ETW000 128 0.000783

4 ETW000 [dev trc ,00000] load shared library (/usr/sap/DWT/SYS/exe/run/dboraslib.sl), hdl 0

4 ETW000 36789 0.037572

4 ETW000 [dev trc ,00000] Library '/usr/sap/DWT/SYS/exe/run/dboraslib.sl' loaded

4 ETW000 56 0.037628

4 ETW000 [dev trc ,00000] function DbSlExpFuns loaded from library /usr/sap/DWT/SYS/exe/run/dboraslib.sl

4 ETW000 68 0.037696

4 ETW000 [dev trc ,00000] Version of '/usr/sap/DWT/SYS/exe/run/dboraslib.sl' is "640.00", patchlevel (0.109)

4 ETW000 280 0.037976

4 ETW000 [dev trc ,00000] function dsql_db_init loaded from library /usr/sap/DWT/SYS/exe/run/dboraslib.sl

4 ETW000 63 0.038039

4 ETW000 [dev trc ,00000] function dbdd_exp_funs loaded from library /usr/sap/DWT/SYS/exe/run/dboraslib.sl

4 ETW000 109 0.038148

4 ETW000 [dev trc ,00000] New connection 0 created 50 0.038198

4 ETW000 [dev trc ,00000] 0: name = R/3, con_id = -000000001 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, c

on_max = 255, con_opt = 255, occ = NO

4 ETW000 476 0.038674

4 ETW000 [dev trc ,00000] db_con_connect (con_name=R/3) 224 0.038898

4 ETW000 [dev trc ,00000] find_con_by_name found the following connection for reuse:

4 ETW000 54 0.038952

4 ETW000 [dev trc ,00000] 0: name = R/3, con_id = 000000000 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, co

n_max = 255, con_opt = 255, occ = NO

4 ETW000 63 0.039015

4 ETW000 [dev trc ,00000] Got ORACLE_HOME=/oracle/DWT/102_64 from environment

4 ETW000 814 0.039829

4 ETW000 [dev trc ,00000] -->oci_initialize (con_hdl=0) 105 0.039934

4 ETW000 [dev trc ,00000] got NLS_LANG='AMERICAN_AMERICA.UTF8' from environment

4 ETW000 55 0.039989

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCIEnvCreate' failed: rc = -1

4 ETW000 3233 0.043222

4 ETW000 [dblink ,00419] ***LOG BY2=>sql error -1 performing CON [dblink#2 @ 419]

4 ETW000 475 0.043697

4 ETW000 [dblink ,00419] ***LOG BY0=>'OCI_ERROR' [dblink#2 @ 419] 46 0.043743

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

[Former Member]

Hi,

I donnt know upto what extend it will resolve this issue.anyway replace the r3trans with newer release and try once more

Regards

Umesh K

[Former Member]

Hi

post out put value for parameter local_listener.

regards

Vinod

[Former Member]

1) Where do I get the newer release of R3trans?

2) Where do I get the local_listener information?

[Former Member]

Change you TNSNAMES.ORA

the parameters (SERVER = DEDICATED) is not support by Oracle 10 and Sap

modify you TNSNAMES.ORA and delete this entry

example ...you new TNSNAMES.ORA

DWT.WORLD =

(DESCRIPTION =

(ADDRESS = (COMMUNITY = sap.world)(PROTOCOL = TCP)(Host = sdr3bw4)(Port = 1553))

(CONNECT_DATA =

(SID = DWT)

(GLOBAL_NAME = DWT.world)

)

)

LISTENER_DWT.WORLD =

(ADDRESS = (COMMUNITY = sap.world)(PROTOCOL = TCP)(Host = sdr3bw4)(Port = 1553))

Regards,

Eva Belén Garcia

[Former Member]

Hello!

I am also getting an R3trans -d error after upgrading Oracle from 9.2.0.6 to 10.2.0.2 on UNIX.

Ehrn I run R3trans -d I get this:

This is R3trans version 6.13 (release 640 - 12.12.05 - 14:24:42).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

R3trans finished (0012).

The R3trans -x log has this error:

4 ETW000 [dev trc ,00000] -->oci_initialize (con_hdl=0) 104 0.039793

4 ETW000 [dev trc ,00000] got NLS_LANG='AMERICAN_AMERICA.UTF8' from environment

4 ETW000 55 0.039848

4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCIEnvCreate' failed: rc = -1

4 ETW000 3158 0.043006

4 ETW000 [dblink ,00419] ***LOG BY2=>sql error -1 performing CON [dblink#2 @ 419]

4 ETW000 426 0.043432

4 ETW000 [dblink ,00419] ***LOG BY0=>'OCI_ERROR' [dblink#2 @ 419] 45 0.043477

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DWT'"

I looked at SP notes 403004, 602843 & 400241 and every thing looks okay.

Any advice would be appreciated!

[Former Member]

Hi,

I have one doubt ...did u follow the upgrade guide for oracle 10g..I hope this problem is with tnsnames.ora..check it

Regards

Umesh k

[Former Member]

Hi

Have a look on note 403004.This will help you.

Regards

Vinod

[Former Member]

Hi Vinod,

Thanks for the quick response,in fact R3trans works perfect with <sid>adm,it fails only with ora<sid>,whether it can be with any environment variable settings?

I haven't permenantly set the env variables for ORACLE_HOME(to the new location) yet,i am exporting those values now.

Whether ora<adm> internally checks any env variable of ,sid>adm?

Thanks

Cherry

[Former Member]

Cherry

Dont mind for giving ref as notes I have large backlogs so that is

Have a look on 602843 which tells abt env on unix.And 400241

If u cant able to solve then I will give u explanation.Let me know the outcome

Regards

Vinod

[Former Member]

Hi,

R3trans is not supposed to work with ora<sid>

SAP connection system is based on the ops$ mechanism and this is only configured for <sid>adm.

Of course, you could "replicate" it for ora<sid> but I do not see it the utility.

the oracle user should never need R3trans ( something different is br* tools )

Regards

[Former Member]

Vinod,

Rewarded points for your answers,now i am getting a abnormal error,while trying to start the database it gives me

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_BIG'

i checked the tnsnames.ora,listener.ora and everything is fine. i have two system srunning in this server,one on oracle 9i and th eother on 10g.the 9i database is running fine,listener is started from 10g .while starting 10g database i am getting the above error.

Regards,

Cherry

[Former Member]

Hi,

Check is there any entry in the Table SAP<SID>.SAPUSER...As from the post its clearly mentioned that the sap user is not in the SAPBIW Schema..

Regards

Umesh k

[Former Member]

Cherry,

Could u paste ut listener.ora.U have one listener or 2 listener means u have confgd 10g listener to give service to 9i and 10g Db.

LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.

If u have confgd this have a look

Regards

Vinod

[Former Member]

This is hat I have dor the listerner & tnsnames.

more listener.ora

1. LISTENER.ORA Network Configuration File: /oracle/DWT/102_64/network/admin/list

ener.ora

1. Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = DWT.WORLD))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = DWT))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdr3bw4)(PORT = 1553))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = DWT)

(ORACLE_HOME = /oracle/DWT/102_64)

)

)

LOGGING_LISTENER = OFF

################

1. Filename......: listener.ora

2. Created.......: created by SAP AG, R/3 Rel. >= 6.10

3. Name..........:

4. Date..........: