disp+work.exe stopped.. SAP instance is down

[MaheshKumar]

Hi All,

Im on Windows Server 2008, Oracle 10.2.4 and BIW7.0.

My system is not coming up.in MMC disp+work.exe shows in gray and its stopped. Will be glad some one can help me to solve the issue.

Kindly find dev_disp, dev_ms and dev_w0 log as attached to this thread

.

dev_w0.txt.zip

dev_ms.txt.zip

dev_disp.txt.zip

[MaheshKumar]

Hi ramesh,

Thanks for the reply. After doing some work around i have tried R3trans -d... still it fa

ailed..

following is log

4 ETW000 R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00).

4 ETW000 unicode enabled version

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

4 ETW000

4 ETW000 date&time   : 11.10.2012 - 20:05:11

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -d

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

4 ETW000  [dev trc     ,00000]  Thu Oct 11 20:05:11 2012                               0  0.000000

4 ETW000  [dev trc     ,00000]  db_con_init called                                     0  0.000000

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

4 ETW000  [dev trc     ,00000]  Loading DB library 'dboraslib.dll' ...                 0  0.000000

4 ETW000  [dev trc     ,00000]  load shared library (dboraslib.dll), hdl 0, addr 0000000186B40000

4 ETW000                                                                            1936  0.001936

4 ETW000  [dev trc     ,00000]      using "D:\usr\sap\BPD\SYS\exe\uc\NTAMD64\dboraslib.dll"

4 ETW000                                                                              17  0.001953

4 ETW000  [dev trc     ,00000]  Library 'dboraslib.dll' loaded                        15  0.001968

4 ETW000  [dev trc     ,00000]  function DbSlExpFuns loaded from library dboraslib.dll

4 ETW000                                                                              21  0.001989

4 ETW000  [dev trc     ,00000]  Version of 'dboraslib.dll' is "700.08", patchlevel (0.25)

4 ETW000                                                                             101  0.002090

4 ETW000  [dev trc     ,00000]  function dsql_db_init loaded from library dboraslib.dll

4 ETW000                                                                              19  0.002109

4 ETW000  [dev trc     ,00000]  function dbdd_exp_funs loaded from library dboraslib.dll

4 ETW000                                                                              85  0.002194

4 ETW000  [dev trc     ,00000]  New connection 0 created                              56  0.002250

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

4 ETW000                                                                             140  0.002390

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

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

4 ETW000                                                                              23  0.002486

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

4 ETW000                                                                              27  0.002513

4 ETW000  [dev trc     ,00000]  CLIENT_ORACLE_HOME is not set as environment variable or

4 ETW000                        DIR_CLIENT_ORAHOME is not set as profile parameter.

4 ETW000                          assuming using instant client with unspecified location.

4 ETW000                                                                             886  0.003399

4 ETW000  [dev trc     ,00000]  Oracle Client Version: '10.2.0.2.0'                10857  0.014256

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

4 ETW000  [dev trc     ,00000]  Client character set UTF16 -> UTF8                  3680  0.017963

4 ETW000  [dev trc     ,00000]  Client NLS setting (OCINlsGetInfo): 'AMERICAN_AMERICA.UTF8'

4 ETW000                                                                              30  0.017993

4 ETW000  [dev trc     ,00000]  Logon as OPS$-user to get SAPBPD's password           15  0.018008

4 ETW000  [dev trc     ,00000]  Connecting as /@BPD on connection 0 (nls_hdl 0) ... (dbsl 700 151208)

4 ETW000                                                                              28  0.018036

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

4 ETW000                                                                             164  0.018200

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      0 0000000004240C40 000000000431D370 000000000431EB28

4 ETW000                                                                              27  0.018227

4 ETW000  [dev trc     ,00000]  Allocating service context handle for con_hdl=0       29  0.018256

4 ETW000  [dev trc     ,00000]  Allocating server context handle                      22  0.018278

4 ETW000  [dev trc     ,00000]  Attaching to DB Server BPD (con_hdl=0,svchp=000000000431E9E8,srvhp=000000000424CD48)

4 ETW000                                                                             128  0.018406

4 ETW000  [dev trc     ,00000]  Assigning server context 000000000424CD48 to service context 000000000431E9E8

4 ETW000                                                                          128363  0.146769

4 ETW000  [dev trc     ,00000]  Allocating user session handle                        34  0.146803

4 ETW000  [dev trc     ,00000]  Starting user session: OCISessionBegin(con_hdl=0, usr='/',svchp=000000000431E9E8, srvhp=000000000424CD48, usrhp=000000000431DB88)

4 ETW000                                                                              33  0.146836

4 ETW000  [dev trc     ,00000]  Assigning user session 000000000431DB88 to service context 000000000431E9E8

4 ETW000                                                                           20541  0.167377

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=53, stmth_p=0000000004255798)

4 ETW000                                                                            7122  0.174499

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

4 ETW000                                                                              28  0.174527

4 ETW000  [dev trc     ,00000]  CbApplInfoGet() failed! Ignore, but uninstall callback to avoid more erroneous calls

4 ETW000                                                                             146  0.174673

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=54, stmth_p=00000000042567F0)

4 ETW000                                                                              20  0.174693

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

4 ETW000                                                                              23  0.174716

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042567F0)

4 ETW000                                                                             532  0.175248

4 ETW000  [dev trc     ,00000]         SELECT SID FROM V$MYSTAT WHERE ROWNUM<2                                                                                  

4 ETW000                                                                              24  0.175272

4 ETW000  [dev trc     ,00000]  Connected to session 134.                            428  0.175700

4 ETW000  [dev trc     ,00000]  Now '/@BPD' is connected: con_hdl=0, nls_hdl=0, session_id=134.

4 ETW000                                                                              29  0.175729

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042567F0)

4 ETW000                                                                              27  0.175756

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

4 ETW000                                                                              23  0.175779

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042567F0)

4 ETW000                                                                             388  0.176167

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

4 ETW000                                                                              33  0.176200

4 ETW000  [dev trc     ,00000]  Got SAPBPD's password from OPS$-user                 606  0.176806

4 ETW000  [dev trc     ,00000]  Disconnecting from connection 0 ...                   23  0.176829

4 ETW000  [dev trc     ,00000]  Rolling back transaction ...                          18  0.176847

4 ETW000  [dev trc     ,00000]  Closing user session (con_hdl=0,svchp=000000000431E9E8,usrhp=000000000431DB88)

4 ETW000                                                                             215  0.177062

4 ETW000  [dev trc     ,00000]  Now I'm disconnected from ORACLE                     375  0.177437

4 ETW000  [dev trc     ,00000]  Connecting as SAPBPD/<pwd>@BPD on connection 0 (nls_hdl 0) ... (dbsl 700 151208)

4 ETW000                                                                              30  0.177467

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

4 ETW000                                                                              24  0.177491

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      0 0000000004240C40 000000000431D370 000000000431EB28

4 ETW000                                                                              26  0.177517

4 ETW000  [dev trc     ,00000]  Assigning username to user session: con_hdl=0, usrhp=000000000431DB88

4 ETW000                                                                             107  0.177624

4 ETW000  [dev trc     ,00000]  Assigning password to user session: con_hdl=0, usrhp=000000000431DB88

4 ETW000                                                                              49  0.177673

4 ETW000  [dev trc     ,00000]  Starting user session: OCISessionBegin(con_hdl=0, usr=SAPBPD/<pwd>, svchp=000000000431E9E8, srvhp=000000000424CD48, usrhp=000000000431DB88)

4 ETW000                                                                              29  0.177702

4 ETW000  [dev trc     ,00000]  Assigning user session 000000000431DB88 to service context 000000000431E9E8

4 ETW000                                                                            4497  0.182199

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=54, stmth_p=0000000004255798)

4 ETW000                                                                              35  0.182234

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

4 ETW000                                                                              23  0.182257

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0000000004255798)

4 ETW000                                                                             444  0.182701

4 ETW000  [dev trc     ,00000]         SELECT SID FROM V$MYSTAT WHERE ROWNUM<2                                                                                  

4 ETW000                                                                              24  0.182725

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                    595  0.183320

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              28  0.183348

4 ETW000  [dev trc     ,00000]  Now 'SAPBPD/<pwd>@BPD' is connected: con_hdl=0, nls_hdl=0, session_id=-1.

4 ETW000                                                                              27  0.183375

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0000000004255798)

4 ETW000                                                                              51  0.183426

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

4 ETW000                                                                              30  0.183456

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0000000004255798)

4 ETW000                                                                             320  0.183776

4 ETW000  [dev trc     ,00000]         SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET') ORDER BY PARAM

4 ETW000                                                                              24  0.183800

4 ETW000  [dev trc     ,00000]         ETER                                                                                                                     

4 ETW000                                                                              22  0.183822

4 ETW000  [dev trc     ,00000]  Database NLS settings: AMERICAN_AMERICA.UTF8         396  0.184218

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0000000004255798)

4 ETW000                                                                             209  0.184427

4 ETW000  [dev trc     ,00000]         SELECT UPPER(INSTANCE_NAME),HOST_NAME,VERSION,TO_CHAR(STARTUP_TIME,'MON DD, YYYY, HH24:MI:SS') FROM V$INSTANCE           

4 ETW000                                                                              24  0.184451

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                   1526  0.185977

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              28  0.186005

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 942          31  0.186036

4 ETW000  [dev trc     ,00000]  Couldn't read V$INSTANCE (con_hdl=0): sqlcode=942

4 ETW000                                                                              52  0.186088

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0000000004255798)

4 ETW000                                                                              28  0.186116

4 ETW000  [dev trc     ,00000]         SELECT SUBSTR(NAME,1,3), TO_CHAR(CREATED,'YYYYMMDDHHMMSS') FROM V$DATABASE                                               

4 ETW000                                                                              22  0.186138

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                   1562  0.187700

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              28  0.187728

4 ETW000  [dev trc     ,00000]  Couldn't get creation timestamp from V$DATABASE: sqlcode=942

4 ETW000                                                                              23  0.187751

4 ETW000  [dev trc     ,00000]  Connection 0 opened (DBSL handle 0)                   16  0.187767

4 ETW000  [dev trc     ,00000]  Thu Oct 11 20:05:12 2012                           67274  0.255041

4 ETW000  [dev trc     ,00000]  build_stmt: reallocating stmt buffer: 256 -> 2000 characters

4 ETW000                                                                              30  0.255071

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=26, stmth_p=0000000004255798)

4 ETW000                                                                             148  0.255219

4 ETW000  [dev trc     ,00000]         SELECT VERSION FROM SVERS                                                                                                

4 ETW000                                                                              25  0.255244

4 ETW000  [dev trc     ,00000]     DbSlRead(): table='SVERS'                          46  0.255290

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 0 input variables given       99  0.255389

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 1 output variables given

4 ETW000                                                                              24  0.255413

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                    696  0.256109

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              28  0.256137

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 942          32  0.256169

4 ETW000  [dblink      ,01299]  ***LOG BZA=>table SVERS      does not exist on database            [dblink#3 @ 1299]

4 ETW000                                                                              98  0.256267

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=62, stmth_p=0000000004255078)

4 ETW000                                                                             379  0.256646

4 ETW000  [dev trc     ,00000]         SELECT COMPCNT, UNICODELG FROM "DDNTT" WHERE TABNAME = 'SVERS'                                                           

4 ETW000                                                                              23  0.256669

4 ETW000  [dev trc     ,00000]     DbSlRead(): table='DDNTT'                          32  0.256701

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 0 input variables given       16  0.256717

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 2 output variables given

4 ETW000                                                                              18  0.256735

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                    507  0.257242

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              27  0.257269

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 942          17  0.257286

4 ETW000  [dev trc     ,00000]  NTAB: SELECT COMPCNT, UNICODE_LG FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc = 103

4 ETW000                                                                              47  0.257333

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=51, stmth_p=00000000042536E8)

4 ETW000                                                                              33  0.257366

4 ETW000  [dev trc     ,00000]         SELECT VERSION FROM "DDNTT" WHERE TABNAME = 'SVERS'                                                                      

4 ETW000                                                                              23  0.257389

4 ETW000  [dev trc     ,00000]     DbSlRead(): table='DDNTT'                          28  0.257417

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 0 input variables given       16  0.257433

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 1 output variables given

4 ETW000                                                                              18  0.257451

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                    504  0.257955

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              28  0.257983

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 942          16  0.257999

4 ETW000  [dev trc     ,00000]  NTAB: SELECT VERSION FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc = 103

4 ETW000                                                                              46  0.258045

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=49, stmth_p=0000000004252690)

4 ETW000                                                                              33  0.258078

4 ETW000  [dev trc     ,00000]         SELECT DBASE FROM "DDNTT" WHERE TABNAME = 'SVERS'                                                                        

4 ETW000                                                                              53  0.258131

4 ETW000  [dev trc     ,00000]     DbSlRead(): table='DDNTT'                          88  0.258219

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 0 input variables given       22  0.258241

4 ETW000  [dev trc     ,00000]  bind_type_and_length(): 1 output variables given

4 ETW000                                                                              17  0.258258

4 ETW000  [dev trc     ,00000]  OCI-call failed with -1=OCI_ERROR                    490  0.258748

4 ETW000  [dev trc     ,00000]     SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000                                                                              27  0.258775

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 942          17  0.258792

4 ETW000  [dbntab.c    ,00000]  *** ERROR => NTAB: SELECT DBASE FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc = 103

4 ETW000                                                                              54  0.258846

4 ETW000  [dbntab      ,01298]  ***LOG BZY=>unexpected return code 103        calling NTAB       [dbntab#4 @ 12987]

4 ETW000                                                                              31  0.258877

4 ETW000  [twdydbacc.c ,00569]  i:0                                                   73  0.258950

4 ETW000  [twdydbacc.c ,00570]  db_fd_p[i].fname:       PGMID                         16  0.258966

4 ETW000  [twdydbacc.c ,00571]  db_fd_p[i].is_key:      0                             10  0.258976

4 ETW000  [twdydbacc.c ,00572]  db_fd_p[i].offset:      0                              9  0.258985

4 ETW000  [twdydbacc.c ,00573]  db_fd_p[i].db_length:   0                              9  0.258994

4 ETW000  [twdydbacc.c ,00574]  db_fd_p[i].fixed_length:4                              9  0.259003

All environment variables are corect..

i have gone through Note 534765 - db connect fails because of wrong db schema in environment

DBMS_TYPE=ora

dbs_ora_schema=SAPBPD

dbs_ora_tnsname=BPD

SQL> select owner from dba_tables where table_name='SAPUSER';

OWNER

------------------------------

OPS$GILSAPBPCDEV\BPDADM

Regards

Mahesh Kumar

[Former Member]

Hi Mahesh,

1) For testing can you please change the dbs_ora_schema=SAP<sourcesid> (production schema name). Restart database and services. After that Run

R3trans -d.

if getting any error just paste it.

2) Use the following procedure to check whether the role exists (with SQL*Plus, for example):

    % sqlplus

   [...]

   SQL> connect / as sysdba

   Connected.

   SQL> select granted_role, default_role

     2> from   dba_role_privs

     3> where  grantee='<username>';

   GRANTED_ROLE                   DEF

   ------------------------------ ---

   CONNECT                        YES

   RESOURCE                       YES

   SELECT_CATALOG_ROLE            YES

   SQL>

where <username> is the name of the relevant database user.

If the system does not output the SELECT_CATALOG_ROLE line, this means that the database user does not have the relevant role. Use the following command to assign the role to the user:

    SQL> grant SELECT_CATALOG_ROLE to <username>;

   Grant succeeded.

   SQL>

and use the above statement to recheck the settings.

If the role exists, but the value of the DEFAULT_ROLE column is set to NO, change this using:

    SQL> alter user <username> default role <role1>, <role2>, ...;

   User altered.

   SQL>

where <role1> is the SELECT_CATALOG_ROLE and you then have to specify all other user roles that were already set to YES.

The database user can now view the objects of the Oracle dictionary.

Thanks

Ramesh Nair

[MaheshKumar]

Hi Ramesh,

Thanks for the reply. I wondered as u said i have changed to dbs_ora_schema=SAP<productionsid>.. R3 trans works as well sap is also started... ?

I did not understand this..

After that i have changed above environment variable to SAPBPD and tried R3trans -d... it did not gave any error.. but work process doesnot start with error..: "SQL error 942: 'ORA-00942: table or view does not exist'"

Thanks for your help Ramesh & Juan...

Regards

Mahesh

[MaheshKumar]

Hi

I have restored using brtools... i have following queries regarding control file....

1.While creating control file can sap be online or it needs to be offline.

2. is following command is correct for creating control file..

create controlfile set database "targetSID" resetlogs noarchivelog

[MaheshKumar]

Hi Juan,

Could able to restore data base sucessfully. Database is mounted and open.

Now i was trying to start sap after digging into dev_w0 log i could find

*** ERROR => CONNECT failed with sql error '1017'

[dbsloci.c    11175]

C  Try to connect with default password

C  Connecting as SAPBPD/<pwd>@BPD on connection 0 (nls_hdl 0) ... (dbsl 700 151208)

To solve this have checked users at sql level.. i found the users are from my production system only. Do i need to do any post processing / create users of development at sql level?

Thanks in advance...

[JPReyes]

The solution for that is on SAP Note 400241

Regards, Juan

[MaheshKumar]

Hi Juan,

Thanks for reply.  I was looking into table spaces in my dev system, they look like PSAPBPP... so on BPP is my productions SID. Just wanted to know are the table space name's SID doesnt changes to BPD after refresh..?

[Former Member]

Hi Mahesh,

Please run below select query

Login to sqlplus "/as sysdba"

>select username, account_status from dba_users;

check the users and there status. If your dev users (sidadm and servicesid) are missing, then i hope you missed 2 step.

1. Go to

C😆 Program files\sapinst.......

search for ORADBUSR.SQL File

Place in any directory suppose d: drive

Run cmd

sqlplus /nolog

@ORADBUSR.SQL SCHEMAOWNER NT COMPUTER|DOMAIN SAP_SID

eg:

schema=SAPSR3

computer/domain=server1

sap_sid=dev

then

@ORADBUSR.SQL SAPSR3 NT server1 dev

2. search for  sapdba_role.sql in usr directory

paste to same location (d: drive)

Run cmd

sqlplus /nolog

@sapdba_role.sql SAPSID NT

eg: @sapdba_role.sql dev NT

3. give grant permission to user

grant connect, sapdba to "SAPSR3"

grant connect, sapdba to "OPS$hostname\sidadm"

grant connect, sapdba to "OPS$hostname\sapservicesid"

grant dba to "OPS$hostname\sidadm"

grant dba to "OPS$hostname\sapservicesid"

4. cmd

R3trans -d

if you are getting error the  run trans.log

paste the error file.

Thanks

Ramesh Nair

[MaheshKumar]

Hi Ramesh,

Thanks for the reply.  I have followed the steps and tried

C:\Users\bpdadm>R3trans -d

This is R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_o

ra_tnsname = 'BPD'"

R3trans finished (0012).

C:\Users\bpdadm>R3trans -x

This is R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00).

unicode enabled version

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_o

ra_tnsname = 'BPD'"

R3trans finished (0012).

Following is trans.log

4 ETW000 R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00).

4 ETW000 unicode enabled version

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

4 ETW000

4 ETW000 date&time   : 11.10.2012 - 11:34:49

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]  Thu Oct 11 11:34:49 2012                               0  0.000000

4 ETW000  [dev trc     ,00000]  db_con_init called                                     0  0.000000

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

4 ETW000  [dev trc     ,00000]  Loading DB library 'dboraslib.dll' ...                 0  0.000000

4 ETW000  [dev trc     ,00000]  load shared library (dboraslib.dll), hdl 0, addr 0000000186B40000

4 ETW000                                                                            1692  0.001692

4 ETW000  [dev trc     ,00000]      using "D:\usr\sap\BPD\SYS\exe\uc\NTAMD64\dboraslib.dll"

4 ETW000                                                                              18  0.001710

4 ETW000  [dev trc     ,00000]  Library 'dboraslib.dll' loaded                        14  0.001724

4 ETW000  [dev trc     ,00000]  function DbSlExpFuns loaded from library dboraslib.dll

4 ETW000                                                                              22  0.001746

4 ETW000  [dev trc     ,00000]  Version of 'dboraslib.dll' is "700.08", patchlevel (0.25)

4 ETW000                                                                             107  0.001853

4 ETW000  [dev trc     ,00000]  function dsql_db_init loaded from library dboraslib.dll

4 ETW000                                                                              19  0.001872

4 ETW000  [dev trc     ,00000]  function dbdd_exp_funs loaded from library dboraslib.dll

4 ETW000                                                                              85  0.001957

4 ETW000  [dev trc     ,00000]  New connection 0 created                              57  0.002014

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

4 ETW000                                                                              34  0.002048

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

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

4 ETW000                                                                              22  0.002125

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

4 ETW000                                                                              27  0.002152

4 ETW000  [dev trc     ,00000]  CLIENT_ORACLE_HOME is not set as environment variable or

4 ETW000                        DIR_CLIENT_ORAHOME is not set as profile parameter.

4 ETW000                          assuming using instant client with unspecified location.

4 ETW000                                                                             787  0.002939

4 ETW000  [dev trc     ,00000]  Oracle Client Version: '10.2.0.2.0'                10837  0.013776

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

4 ETW000  [dev trc     ,00000]  Client character set UTF16 -> UTF8                  3574  0.017376

4 ETW000  [dev trc     ,00000]  Client NLS setting (OCINlsGetInfo): 'AMERICAN_AMERICA.UTF8'

4 ETW000                                                                              30  0.017406

4 ETW000  [dev trc     ,00000]  Logon as OPS$-user to get SAPBPD's password           15  0.017421

4 ETW000  [dev trc     ,00000]  Connecting as /@BPD on connection 0 (nls_hdl 0) ... (dbsl 700 151208)

4 ETW000                                                                              28  0.017449

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

4 ETW000                                                                             164  0.017613

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      0 0000000004210C40 000000000435D370 000000000435EB28

4 ETW000                                                                              28  0.017641

4 ETW000  [dev trc     ,00000]  Allocating service context handle for con_hdl=0       29  0.017670

4 ETW000  [dev trc     ,00000]  Allocating server context handle                      22  0.017692

4 ETW000  [dev trc     ,00000]  Attaching to DB Server BPD (con_hdl=0,svchp=000000000435E9E8,srvhp=000000000421CD48)

4 ETW000                                                                             126  0.017818

4 ETW000  [dev trc     ,00000]  Assigning server context 000000000421CD48 to service context 000000000435E9E8

4 ETW000                                                                           24489  0.042307

4 ETW000  [dev trc     ,00000]  Allocating user session handle                        34  0.042341

4 ETW000  [dev trc     ,00000]  Starting user session: OCISessionBegin(con_hdl=0, usr='/',svchp=000000000435E9E8, srvhp=000000000421CD48, usrhp=000000000435DB88)

4 ETW000                                                                              33  0.042374

4 ETW000  [dev trc     ,00000]  Assigning user session 000000000435DB88 to service context 000000000435E9E8

4 ETW000                                                                           23681  0.066055

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=53, stmth_p=0000000004225798)

4 ETW000                                                                            7143  0.073198

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

4 ETW000                                                                              29  0.073227

4 ETW000  [dev trc     ,00000]  CbApplInfoGet() failed! Ignore, but uninstall callback to avoid more erroneous calls

4 ETW000                                                                             142  0.073369

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=54, stmth_p=00000000042267F0)

4 ETW000                                                                              21  0.073390

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

4 ETW000                                                                              22  0.073412

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042267F0)

4 ETW000                                                                             602  0.074014

4 ETW000  [dev trc     ,00000]         SELECT SID FROM V$MYSTAT WHERE ROWNUM<2                                                                                  

4 ETW000                                                                              25  0.074039

4 ETW000  [dev trc     ,00000]  Connected to session 134.                            458  0.074497

4 ETW000  [dev trc     ,00000]  Now '/@BPD' is connected: con_hdl=0, nls_hdl=0, session_id=134.

4 ETW000                                                                              29  0.074526

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042267F0)

4 ETW000                                                                              27  0.074553

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

4 ETW000                                                                              23  0.074576

4 ETW000  [dev trc     ,00000]  -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=00000000042267F0)

4 ETW000                                                                             363  0.074939

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

4 ETW000                                                                              31  0.074970

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

4 ETW000                                                                             416  0.075386

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 1403         19  0.075405

4 ETW000  [dev trc     ,00000]  Disconnecting from connection 0 ...                  158  0.075563

4 ETW000  [dev trc     ,00000]  Rolling back transaction ...                          26  0.075589

4 ETW000  [dev trc     ,00000]  Closing user session (con_hdl=0,svchp=000000000435E9E8,usrhp=000000000435DB88)

4 ETW000                                                                            1196  0.076785

4 ETW000  [dev trc     ,00000]  Now I'm disconnected from ORACLE                     467  0.077252

4 ETW000  [dev trc     ,00000]  Try to connect with default password                  22  0.077274

4 ETW000  [dev trc     ,00000]  Connecting as SAPBPD/<pwd>@BPD on connection 0 (nls_hdl 0) ... (dbsl 700 151208)

4 ETW000                                                                              22  0.077296

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

4 ETW000                                                                              99  0.077395

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      0 0000000004210C40 000000000435D370 000000000435EB28

4 ETW000                                                                              27  0.077422

4 ETW000  [dev trc     ,00000]  Assigning username to user session: con_hdl=0, usrhp=000000000435DB88

4 ETW000                                                                              21  0.077443

4 ETW000  [dev trc     ,00000]  Assigning password to user session: con_hdl=0, usrhp=000000000435DB88

4 ETW000                                                                              35  0.077478

4 ETW000  [dev trc     ,00000]  Starting user session: OCISessionBegin(con_hdl=0, usr=SAPBPD/<pwd>, svchp=000000000435E9E8, srvhp=000000000421CD48, usrhp=000000000435DB88)

4 ETW000                                                                              28  0.077506

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

4 ETW000                                                                            2808  0.080314

4 ETW000  [dev trc     ,00000]  server_detach(con_hdl=0,stale=1,svrhp=000000000421CD48)

4 ETW000                                                                              22  0.080336

4 ETW000  [dev trc     ,00000]  Detaching from DB Server (con_hdl=0,svchp=000000000435E9E8,srvhp=000000000421CD48)

4 ETW000                                                                              21  0.080357

4 ETW000  [dev trc     ,00000]  Deallocating server context handle 000000000421CD48

4 ETW000                                                                             908  0.081265

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

4 ETW000                                                                              30  0.081295

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 1045         14  0.081309

4 ETW000  [dblink      ,00431]  ***LOG BY2=>sql error 1045   performing CON [dblink#3 @ 431]

4 ETW000                                                                             111  0.081420

4 ETW000  [dblink      ,00431]  ***LOG BY0=>ORA-01045: user SAPBPD lacks CREATE SESSION privilege; logon denied [dblink#3 @ 431]

4 ETW000                                                                              25  0.081445

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

[Former Member]

Your SAPUSER table does not contain required data for connection.

"The table SAPUSER must occur in the system only once and it must be assigned to the user OPS$<sid>ADM. Use the following query to check this:

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

If the system returns an owner <owner> other than OPS$<sid>ADM, you must delete the relevant SAPUSER tables:

DROP TABLE "<owner>".SAPUSER;

If the system does not return OPS$<sid>ADM, you must create the table SAPUSER as <sid>adm and enter the password:

CREATE TABLE "OPS$<sid>ADM".SAPUSER

  (USERID VARCHAR2(256), PASSWD VARCHAR2(256));

INSERT INTO "OPS$<sid>ADM".SAPUSER VALUES ('<sapowner>', '<password>');

"

Please read carefully SAP Note 400241 as Juan suggests.

Regards
Roman

[Former Member]

Hi Mahesh,

Login to sqlplus /nolog

>conn SAPBPD

>password

enter your password

if you forgot your password go to brtools reset the password of SAPBPD

brtools ---8----4----

after that repeat the same step

sqlplus /nolog

>conn SAPBPD

>password

Thanks

Ramesh Nair

[MaheshKumar]

Hi Juan,

Thanks for the reply. Intially i have restored my production backup to development. At that time error was coming like

ORA-00202: control file: 'D:\ORACLE\BPD\ORIGLOGA\CNTRL\CNTRLBPD.DBF'

ORA-27047: unable to read the header block of file

OSD-04001: invalid logical block size (OS 538984480)

As the system was not available i have restored it to previous days sucessfulbackup.

After restore im facing this problem...

[Former Member]

What was the procedure used for restore?

Regards

Roman

[JPReyes]

So you are working with Kranthi?  http://scn.sap.com/thread/3248217

So far seems like your controlfiles are either corrupted or inconsistent.... Have you tried using a trace to create new controlfiles?, you could do a brrestore with "all" option and then a recover.

Regards, Juan

[MaheshKumar]

Yes Juan, You are right... we work together....

Will restore new offline backup today.... will let you know the status... thanks for ur help and time...

Regards

Mahesh

[JPReyes]

ORA-01033: ORACLE initialization or shutdown in progress

Seems like your database is hanging in limbo... Did you do any changes on the system?...

Read the DB alert log and post errors here.

Regards, Juan

[MaheshKumar]

Hi Juan,

Kindly find below log

Tue Oct 09 17:10:07 2012

Shutting down instance: further logons disabled

Tue Oct 09 17:10:07 2012

Stopping background process MMNL

Tue Oct 09 17:10:07 2012

Stopping background process CJQ0

Tue Oct 09 17:10:08 2012

Background process MMNL not dead after 10 seconds

Tue Oct 09 17:10:08 2012

Stopping background process MMON

Tue Oct 09 17:10:09 2012

Background process MMON not dead after 30 seconds

Tue Oct 09 17:10:09 2012

Shutting down instance (immediate)

License high water mark = 1

Tue Oct 09 17:10:09 2012

Stopping Job queue slave processes, flags = 7

Tue Oct 09 17:10:09 2012

Job queue slave processes stopped

Tue Oct 09 17:10:09 2012

ALTER DATABASE CLOSE NORMAL

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

Tue Oct 09 17:10:09 2012

ALTER DATABASE DISMOUNT

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

Tue Oct 09 17:10:23 2012

Starting ORACLE instance (normal)

Tue Oct 09 17:10:23 2012

Specified value of sga_max_size is too small, bumping to 1996488704

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.4.0.

System parameters with non-default values:

  processes                = 80

  sessions                 = 160

  event                    = 10191 trace name context forever, level 1

  sga_max_size             = 1996488704

  shared_pool_size         = 973078528

  shared_pool_reserved_size= 96636764

  filesystemio_options     = setall

  control_files            = D:\ORACLE\BPD\ORIGLOGA\CNTRL\CNTRLBPD.DBF, D:\ORACLE\BPD\ORIGLOGB\CNTRL\CNTRLBPD.DBF, D:\ORACLE\BPD\SAPDATA1\CNTRL\CNTRLBPD.DBF

  control_file_record_keep_time= 30

  db_block_size            = 8192

  db_cache_size            = 973078528

  compatible               = 10.2.0

  log_archive_dest         = D:\oracle\BPD\oraarch\BPDarch

  log_buffer               = 14262784

  log_checkpoint_interval  = 0

  db_files                 = 254

  log_checkpoints_to_alert = TRUE

  dml_locks                = 4000

  replication_dependency_tracking= FALSE

  undo_management          = AUTO

  undo_tablespace          = PSAPUNDO

  _in_memory_undo          = FALSE

  undo_retention           = 43200

  recyclebin               = off

  remote_os_authent        = TRUE

  remote_login_passwordfile= EXCLUSIVE

  job_queue_processes      = 1

  parallel_execution_message_size= 16384

  _table_lookup_prefetch_size= 0

  background_dump_dest     = D:\ORACLE\BPD\SAPTRACE\BACKGROUND

  user_dump_dest           = D:\ORACLE\BPD\SAPTRACE\USERTRACE

  core_dump_dest           = D:\ORACLE\BPD\SAPTRACE\BACKGROUND

  sort_area_size           = 2097152

  sort_area_retained_size  = 0

  db_name                  = BPD

  open_cursors             = 800

  _sort_elimination_cost_ratio= 10

  _b_tree_bitmap_plans     = FALSE

  star_transformation_enabled= true

  query_rewrite_enabled    = false

  _index_join_enabled      = FALSE

  _optim_peek_user_binds   = FALSE

  pga_aggregate_target     = 629145600

  _optimizer_mjc_enabled   = FALSE

PMON started with pid=2, OS id=13628

PSP0 started with pid=3, OS id=25500

MMAN started with pid=4, OS id=4692

DBW0 started with pid=5, OS id=29568

LGWR started with pid=6, OS id=1448

CKPT started with pid=7, OS id=37680

SMON started with pid=8, OS id=31764

RECO started with pid=9, OS id=36932

CJQ0 started with pid=10, OS id=29016

MMON started with pid=11, OS id=18332

MMNL started with pid=12, OS id=2108

Tue Oct 09 17:10:25 2012

ALTER DATABASE   MOUNT

Tue Oct 09 17:10:29 2012

Setting recovery target incarnation to 1

Tue Oct 09 17:10:29 2012

Successful mount of redo thread 1, with mount id 4229846017

Tue Oct 09 17:10:29 2012

Database mounted in Exclusive Mode

Completed: ALTER DATABASE   MOUNT

Tue Oct 09 17:10:30 2012

ALTER DATABASE OPEN

ORA-1122 signalled during: ALTER DATABASE OPEN...

[JPReyes]

ORA-1122 signalled during: ALTER DATABASE OPEN...

That means datafile verification check failed.

Is this a after system copy or a restore??...

Regards, Juan

[MaheshKumar]

Restore...

[JPReyes]

What sort of backup did you restore?... is it from a full offline backup?...  What was the procedure used?...  Basically your datafile info is inconsistent with your controlfile info

Regards

Juan

[MaheshKumar]

I have restored from previous sucessful offline backup..

[JPReyes]

Did your restore finished without errors/warnings?... Also have you tried to stop the Database Instance manually and restart it again?

Regards, Juan