Problem in running ORADBUSR script

[Former Member]

Dear All,

When i run R3trans -d  i got return code 12; i have checked the permission everything seems to be fine; then i run following query ;

select username from dba_users;

drop user OPS$ORAED2 cascade;

drop user OPS$ED2ADM cascade;

put the file in the location of /oracle/SID/102_64/dbs. And the below command from the same location with

sqlplus "/ as sysdba"

@ORADBUSR.SQL SAPSR3 UNIX ED2 x

i am getting below error,

.  Oracle Version:                     10.2.0.4.0

.  Parametervalue os_authent_prefix:   OPS$

.  Schema Id:                          SR3

.  Database User (Schema):             SAPSR3

.  SAP R/3 Administrator:              OPS$ED2ADM

.  SAP R/3 Serviceuser:                OPS$SAPSERVICEED2

.

User OPS$ORAED2does already exist.(run same query second time thats y)

unable to assign default tablespace to user: SAPSR3

begin

*

ERROR at line 1:

ORA-00959: tablespace 'PSAPSR3USR' does not exist

ORA-06512: at line 126

Please suggest for solution.

From,

Ketan Kapadi

[JPReyes · Accepted solution]

So, does the tablespace 'PSAPSR3USR' exist or not?.. Users wont be able to be defined if the default user tablespace does not exist.

How did you install/create this system?

Regards, Juan

[Former Member · Accepted solution]

Dear Juan,

It is alreay running system but i am getting R3trans -d problem with return code 12 and thns decided to create OPS$ User.

i cheked PSAPSR3USR does not exist  and run following quey,

CREATE TABLE "OPS$ED2ADM".SAPUSER(USERID VARCHAR2(256), PASSWD VARCHAR2(256));

INSERT INTO "OPS$ED2ADM".SAPUSER VALUES ('SAPSR3', 'Gspc1234');

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

but when i running  @ORADBUSR.SQL SAPSR3 UNIX ED2 x gives the same error as mention in the earlier blog.

Please Suggest

[Former Member · Accepted solution]

Hi Ketan,

check default tablespace of user SAPSR3 by using this SQL Statement:

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM                         SYSTEM
SYS                            SYSTEM
OPS$SIDADM                     SYSTEM
ORACLE_OCM                     SYSTEM
OPS$SAPSERVICESID              SYSTEM
DIP                            SYSTEM
OPS$ORASID                     SYSTEM
SAPSR3                         PSAPSR3USR
TSMSYS                         SYSTEM
DBSNMP                         SYSAUX
OUTLN                          SYSTEM

If tablespace PSAPSR3USR is missing, just create it using BRTools.

Otherwise, post the output of statement "select NAME from v$tablespace;"

Regards

Leo Capasso

[JPReyes · Accepted solution]

The problem is that because the user tablespace does not exist you will not be able to define the OPS$ Users and neither the SAPUSER table.

You need to check whats the default tablespace for users as Ketan suggested, if the tablespace does not exist then you need to create/restore it.

Regards, Juan

[Former Member · Accepted solution]

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
OPS$ED2ADM                     SYSTEM
SAPED2                         SYSTEM
OPS$SR3ADM                     SYSTEM
SAPSR3                         SYSTEM
OPS$ORAED2                     SYSTEM
OUTLN                          SYSTEM
SYS                            SYSTEM
SYSTEM                         SYSTEM
SAPSR3DB                       PSAPSR3DB
TSMSYS                         SYSTEM

DIP                            SYSTEM

ORACLE_OCM                     SYSTEM
DBSNMP                         SYSAUX

please find the anser of following query it shows the system tablespace for user SAPSR3 . what i need is change the default table space to PSAPSR3. please suggest?

[former_member365012 · Accepted solution]

Well , I didn't read the entire story here. But the syntax to assign a default tablespace for a particular user is,

ALTER USER SAPSR3

DEFAULT_TABLESPACE PSAPSR3

/

[JPReyes · Accepted solution]

You wont change it to PSAPSR3, you need to create PSAPSR3USR.

Then you can simply change the user default tablespace by using,

alter user SAPSR3 default tablespace PSAPSR3USR;

Regards, juan

[Former Member · Accepted solution]

Dear Juan;

  i have checked in quality and production it is as below,

                SAPSR3DB                      PSAPSR3DB

for only java system. if the system is only ABAP then it is as below,

                SAPSR3                            PSAPSR3SR

Please suggest?

[JPReyes · Accepted solution]

ERROR at line 1:

ORA-00959: tablespace 'PSAPSR3USR' does not exist

ORA-06512: at line 126

The script is looking for the default "schema" (SAPSR3), the user tablespace for the default schema is  PSAPSR3USR

It does not exist in your system.. you can use a different tablespace (not recommended) by following manual steps on SAP Note 400241 - Problems with ops$ or sapr3 connect to Oracle

Regards, Juan

[Former Member · Accepted solution]

Guys !!

Hold on please. Is this is a JAVA system? R3trans -d never works in it by default neither does OP$ mechanism. By default JAVA system doesn't have OP$ users (if I recall correctly) . Please don't think R3trans -d not working for JAVA system means DB connection ain't happening. Remember the connectivity is not like  ABAP it uses JDBC connector instead.

Regards.

Ruchit.

[JPReyes · Accepted solution]

You couldn't be more right... and i missed it  🙂

[Former Member · Accepted solution]

Dear Ruchit,

Thanks for  your answer; i haved checked other system it will not working still system is running now but i have deleted OPS$ how can i check the connectivity with database?

From,
Ketan Kapadi

[Former Member · Accepted solution]

Dear Juan,

hey its my my mistake i have not mention the system type ; ruchit and juan thank you very much for your help. problem is resolved.

From,

Ketan Kapadi

[JPReyes · Accepted solution]

That's ok... I was starting to find it odd, hehe 

[Former Member · Accepted solution]

Hi Ruchit Khushu,

My system is working now but in case of java how to check the connectivity with database?

From,

Ketan Kapadi

[Former Member · Accepted solution]

Hi Ketan,

Try this connection url:

jdbc:oracle:thin:@<DBHOST>:<Listener Port>:orcl

Regards.

Ruchit.

[Former Member · Accepted solution]

Dear Ruchi,

i had written as below in the browser,

jdbc:oracle:thin:@x.x.x.x:1527:orcl (where x.x.x.x is the ip address)

but no output generated

From,

Ketan Kapadi