cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

ERROR => ORA-1031 when accessing table SAPUSER

Go to solution
former_member182034
Active Contributor

on ‎01-31-2012 8:04 PM

0 Kudos

Dear Expert,

I am getting following issue when work process is being to start..


---------------------------------------------------
trc file: "dev_w0", trc level: 1, release: "700"
---------------------------------------------------
*
*  ACTIVE TRACE LEVEL           1
*  ACTIVE TRACE COMPONENTS      all, MJ
*
M sysno      00
M sid        PRD
M systemid   562 (PC with Windows NT)
M relno      7000
M patchlevel 0
M patchno    236
M intno      20050900
M make:      multithreaded, Unicode, 64 bit, optimized
M pid        5288
M 
M  
M Wed Feb 01 01:00:22 2012
M  kernel runs with dp version 243000(ext=110000) (@(#) DPLIB-INT-VERSION-243000-UC)
M  length of sys_adm_ext is 576 bytes
M  ***LOG Q0Q=> tskh_init, WPStart (Workproc 0 5288) [dpxxdisp.c   1349]
I  MtxInit: 30000 0 0
M  DpSysAdmExtCreate: ABAP is active
M  DpSysAdmExtCreate: VMC (JAVA VM in WP) is not active
M  DpShMCreate: sizeof(wp_adm)		38544	(1752)
M  DpShMCreate: sizeof(tm_adm)		5912704	(29416)
M  DpShMCreate: sizeof(wp_ca_adm)		24064	(80)
M  DpShMCreate: sizeof(appc_ca_adm)	8000	(80)
M  DpCommTableSize: max/headSize/ftSize/tableSize=500/16/552064/552080
M  DpShMCreate: sizeof(comm_adm)		552080	(1088)
M  DpSlockTableSize: max/headSize/ftSize/fiSize/tableSize=0/0/0/0/0
M  DpShMCreate: sizeof(slock_adm)		0	(104)
M  DpFileTableSize: max/headSize/ftSize/tableSize=0/0/0/0
M  DpShMCreate: sizeof(file_adm)		0	(72)
M  DpShMCreate: sizeof(vmc_adm)		0	(1864)
M  DpShMCreate: sizeof(wall_adm)		(41664/36752/64/192)
M  DpShMCreate: sizeof(gw_adm)	48
M  DpShMCreate: SHM_DP_ADM_KEY		(addr: 0000000010000050, size: 6622896)
M  DpShMCreate: allocated sys_adm at 0000000010000050
M  DpShMCreate: allocated wp_adm at 0000000010002270
M  DpShMCreate: allocated tm_adm_list at 000000001000B900
M  DpShMCreate: allocated tm_adm at 000000001000B960
M  DpShMCreate: allocated wp_ca_adm at 00000000105AF1E0
M  DpShMCreate: allocated appc_ca_adm at 00000000105B4FE0
M  DpShMCreate: allocated comm_adm at 00000000105B6F20
M  DpShMCreate: system runs without slock table
M  DpShMCreate: system runs without file table
M  DpShMCreate: allocated vmc_adm_list at 000000001063DBB0
M  DpShMCreate: allocated gw_adm at 000000001063DC30
M  DpShMCreate: system runs without vmc_adm
M  DpShMCreate: allocated ca_info at 000000001063DC60
M  DpShMCreate: allocated wall_adm at 000000001063DC70
M  rdisp/queue_size_check_value :  -> off
M  ThTaskStatus: rdisp/reset_online_during_debug 0
X  EmInit: MmSetImplementation( 2 ).
X  MM global diagnostic options set: 0
X  <ES> client 0 initializing ....
X  Using implementation view
X  <EsNT> Using memory model view.
M  <EsNT> Memory Reset disabled as NT default
X  ES initialized.
X  mm.dump: set maximum dump mem to 96 MB
M  ThInit: running on host PRDSAPX5
M  calling db_connect ...
B  create_con (con_name=R/3)
B  Loading DB library 'F:\usr\sap\PRD\DVEBMGS00\exe\dboraslib.dll' ...
B  Library 'F:\usr\sap\PRD\DVEBMGS00\exe\dboraslib.dll' loaded
B  Version of 'F:\usr\sap\PRD\DVEBMGS00\exe\dboraslib.dll' is "700.08", patchlevel (0.234)
B  New connection 0 created
C  Prepending F:\usr\sap\PRD\DVEBMGS00\exe to Path.
C  Oracle Client Version: '10.2.0.2.0'
C  application info callback registered
C  Client NLS setting (OCINlsGetInfo): connection handle 0 -> 'AMERICAN_AMERICA.UTF8'
C  Logon as OPS$-user to get SAPSR3's password
C  Connecting as /@PRD on connection 0 (nls_hdl 0) ... (dbsl 700 251109)
C  Nls CharacterSet                 NationalCharSet                   EnvHp      ErrHp ErrHpBatch
C    0 UTF8                                                      0000000012F1A8F0 0000000012F223F0 0000000012F32308
C  Attaching to DB Server PRD (con_hdl=0,svchp=0000000012F321C8,srvhp=0000000012F34FA8)
C  Starting user session: OCISessionBegin(con_hdl=0, usr='/',svchp=0000000012F321C8, srvhp=0000000012F34FA8, usrhp=0000000012F22C08)
C     CbApplInfoGet() failed (ignored 1).
C  Now '/@PRD' is connected: con_hdl=0, nls_hdl=0, session_id=530.
C  
C Wed Feb 01 01:00:23 2012
C     OCIStmtExecute() failed with -1=OCI_ERROR
C     SQL error 1031:
C  *** ERROR => ORA-1031 when accessing table SAPUSER
 [dbsloci.c    12684]
C  Disconnecting from connection 0 ...
C  Closing user session (con_hdl=0,svchp=0000000012F321C8,usrhp=0000000012F22C08)
C  Now I'm disconnected from ORACLE
C  Try to connect with default password
C  Connecting as SAPSR3/<pwd>@PRD on connection 0 (nls_hdl 0) ... (dbsl 700 251109)
C  Nls CharacterSet                 NationalCharSet                   EnvHp      ErrHp ErrHpBatch
C    0 UTF8                                                      0000000012F1A8F0 0000000012F223F0 0000000012F32308
C  Starting user session: OCISessionBegin(con_hdl=0, usr=SAPSR3/<pwd>, svchp=0000000012F321C8, srvhp=0000000012F34FA8, usrhp=0000000012F22C08)
C  Now 'SAPSR3/<pwd>@PRD' is connected: con_hdl=0, nls_hdl=0, session_id=530.
C  Database NLS settings in V$NLS_PARAMETERS: AMERICAN_AMERICA.UTF8
C  DB instance PRD is running on PRDSAPX5 with ORACLE version 10.2.0.5.0 since FEB 01, 2012, 00:59:42
B  Connection 0 opened (DBSL handle 0)
B  Wp  Hdl ConName                        ConId     ConState     TX  HC  PRM RCT FRC TIM MAX OPT Date     Time   DBHost                        
B  000 000 R/3                            000000000 ACTIVE       NO  NO  YES NO  NO  000 255 255 20120201 010022 PRDSAPX5                      
M  db_connect o.k.
M  ICT: exclude compression: *.zip,*.cs,*.rar,*.arj,*.z,*.gz,*.tar,*.lzh,*.cab,*.hqx,*.ace,*.jar,*.ear,*.war,*.css,*.pdf,*.js,*.gzip,*.uue,*.bz2,*.iso,*.sda,*.sar,*.gif,*.png
I  
I Wed Feb 01 01:00:38 2012
I  MtxInit: 0 0 0
M  SHM_PRES_BUF			(addr: 0000000014940050, size: 14640128)
M  SHM_ROLL_AREA		(addr: 000007FFCB580050, size: 268435456)
M  SHM_PAGING_AREA		(addr: 0000000015740050, size: 134217728)
M  SHM_ROLL_ADM			(addr: 000000001D750050, size: 2760892)
M  SHM_PAGING_ADM		(addr: 00000000092D0050, size: 525344)
M  ThCreateNoBuffer		allocated 544152 bytes for 1000 entries at 0000000009950050
M  ThCreateNoBuffer		index size: 3000 elems
M  ThCreateVBAdm		allocated 12176 bytes (50 server) at 0000000008FB0050
X  EmInit: MmSetImplementation( 2 ).
X  MM global diagnostic options set: 0
X  <ES> client 0 initializing ....
X  Using implementation view
X  ES initialized.
X  mm.dump: set maximum dump mem to 96 MB
M  Deactivate statistics hyper index locking
B  dbntab: NTAB buffers created
B  dbntab: Buffer FTAB(hash header)  (addr: 000000001DA000E0, size: 584)
B  dbntab: Buffer FTAB(anchor array) (addr: 000000001DA00330, size: 1026712)
B  dbntab: Buffer FTAB(item array)   (addr: 000000001DAFADD0, size: 4106688)
B  dbntab: Buffer FTAB(data area)    (addr: 000000001DEE5790, size: 30720000)
B  dbntab: Buffer IREC(hash header)  (addr: 000000001FC400E0, size: 584)
B  dbntab: Buffer IREC(anchor array) (addr: 000000001FC40330, size: 1026712)
B  dbntab: Buffer IREC(item array)   (addr: 000000001FD3ADD0, size: 1026624)
B  dbntab: Buffer IREC(data area)    (addr: 000000001FE35810, size: 10507264)
B  dbntab: Buffer STAB(hash header)  (addr: 00000000208400E0, size: 584)
B  dbntab: Buffer STAB(anchor array) (addr: 0000000020840330, size: 1026712)
B  dbntab: Buffer STAB(item array)   (addr: 000000002093ADD0, size: 1026624)
B  dbntab: Buffer STAB(data area)    (addr: 0000000020A35810, size: 9862144)
B  dbntab: Buffer TTAB(hash header)  (addr: 00000000213A00E0, size: 2872)
B  dbntab: Buffer TTAB(anchor array) (addr: 00000000213A0C20, size: 1026712)
B  dbntab: Buffer TTAB(item array)   (addr: 000000002149B6C0, size: 2566680)
B  dbntab: Buffer TTAB(data area)    (addr: 000000002170E0E0, size: 18736764)
B  dbstat: table statistics switched on for 81863 tables
B  dbstat: TABSTAT buffer created (addr: 00000000228F0050, size: 40024544)
B  db_con_shm_ini:  WP_ID = 0, WP_CNT = 22, CON_ID = -1
B  dbtbxbuf: Buffer TABL  (addr: 0000000024F20160, size: 101680128, end: 000000002B018560)
B  dbtbxbuf: Profile: max_objects = 5000, displace = 1, reorg = 1
B  dbtbxbuf: request_unit = 2000, sync_reload = 5, inval_reload = 5
B  dbtbxbuf: protect_shm = 0, force_checks = 0
B  dbtbxbuf: tsize_retry = 50039168
B  ***LOG BB0=> buffer TABL       started with length 101680128  bytes [dbtbxbuf#3 @ 16202] [dbtbxbuf1620 2]
B  
B Wed Feb 01 01:00:39 2012
B  dbtbxbuf: Buffer TABLP (addr: 000000002B020160, size: 92160000, end: 0000000030804160)
B  dbtbxbuf: Profile: max_objects = 500, displace = 1, reorg = 1
B  dbtbxbuf: request_unit = 2000, sync_reload = 5, inval_reload = 5
B  dbtbxbuf: protect_shm = 0, force_checks = 0
B  dbtbxbuf: tsize_retry = 45987456
B  ***LOG BB0=> buffer TABLP      started with length 92160000   bytes [dbtbxbuf#3 @ 16202] [dbtbxbuf1620 2]
B  dbtbxbuf: Reading TBX statistics:
B  dbtbxbuf: Opening F:\USR\SAP\PRD\DVEBMGS00\data\tbxnew failed (No such file or directory).
B  dbtbxbuf: Renaming F:\USR\SAP\PRD\DVEBMGS00\data\tbxnew to F:\USR\SAP\PRD\DVEBMGS00\data\tbxstat failed (No such file or directory).
B  dbtbxbuf: 0 object entries precreated
C     OCIStmtExecute() failed with -1=OCI_ERROR
C     SQL error 376:
C  *** ERROR => Error 376 in stmt_fetch() from oci_execute_stmt(), orpc=0
 [dbsloci.c    14048]
C  *** ERROR => ORA-376 occurred when executing SQL stmt (parse error offset=0)
 [dbsloci.c    14067]
C  sc_p=000000000980A1E8,no=3,idc_p=0000000000000000,con=0,act=1,slen=32,smax=256,#vars=0,stmt=0000000012FC3100,table=DDLOG                         
C  SELECT MAX(SEQNUMBER) FROM DDLOG;
C  sc_p=000000000980A1E8,no=3,idc_p=0000000000000000,con=0,act=1,slen=32,smax=256,#vars=0,stmt=0000000012FC3100,table=DDLOG                         
C  prep=0,lit=0,nsql=0,lobret=0,#exec=1,dbcnt=0,upsh_p=0000000000000000,ocistmth_p=0000000012FC3CE8
C  IN : cols=0,rmax=1,xcnt=0,rpc=0,rowi=0,rtot=0,upto=-1,rsize=0,vmax=0,bound=0,iobuf_p=0000000000000000,vda_p=0000000000000000
C       lobs=0,lmax=0,lpcnt=0,larr=0000000000000000,lcurr_p=0000000000000000,rret=0
C  OUT: cols=1,rmax=1,xcnt=1,rpc=0,rowi=0,rtot=0,upto=-1,rsize=4,vmax=32,bound=1,iobuf_p=0000000012F94050,vda_p=0000000012FC5540
C       lobs=0,lmax=0,lpcnt=0,larr=0000000000000000,lcurr_p=0000000000000000,rret=0
C  SELECT MAX(SEQNUMBER) FROM DDLOG;
B  ***LOG BYL=> DBQ action required because of database error            [dbsh#3 @ 1104] [dbsh    1104 ]
B  SQL code: 376, SQL text: ORA-00376: file 35 cannot be read at this time
B  ORA-01111: name for data file 35 is unknown - rename to correct file
B  ORA-01110: data file 35: 'E:\ORACLE\PRD\102\DATABASE\MISSING00035'
B  ***LOG BY4=> sql error 376    performing SEL on table DDLOG      [dbsynseq#1 @ 256] [dbsynseq0256 ]
B  ***LOG BY0=> ORA-00376: file 35 cannot be read at this time
ORA-01111: name for data file 35 is unknown - rename to correct file
ORA-01110: data file 35: 'E:\ORACLE\PRD\102\DATABASE\MISSING00035' [dbsynseq#1 @ 256] [dbsynseq0256 ]
B  db_syinit failed
M  *** ERROR => ThCallHooks: event handler db_init for event CREATE_SHM failed [thxxtool3.c  261]
M  *** ERROR => ThIPCInit: hook failed [thxxhead.c   2158]
M  ***LOG R19=> ThInit, ThIPCInit ( TSKH-IPC-000001) [thxxhead.c   1585]
M  in_ThErrHandle: 1
M  *** ERROR => ThInit: ThIPCInit (step 1, th_errno 17, action 3, level 1) [thxxhead.c   10631]
M  
M  Info for wp 0
M  
M    pid = 5288
M    severity = 0
M    status = 0
M    stat = WP_RUN
M    waiting_for = NO_WAITING
M    reqtype = DP_RQ_DIAWP
M    act_reqtype = NO_REQTYPE
M    rq_info = 0
M    tid = -1
M    mode = 255
M    len = -1
M    rq_id = 65535
M    rq_source = 
M    last_tid = 0
M    last_mode = 0
M    semaphore = 0
M    act_cs_count = 0
M    csTrack = 0
M    csTrackRwExcl = 0
M    csTrackRwShrd = 0
M    mode_cleaned_counter = 0
M    control_flag = 0
M    int_checked_resource(RFC) = 0
M    ext_checked_resource(RFC) = 0
M    int_checked_resource(HTTP) = 0
M    ext_checked_resource(HTTP) = 0
M    report = >                                        <
M    action = 0
M    tab_name = >                              <
M    attachedVm = no VM
M  
M  *****************************************************************************
M  *
M  *  LOCATION    SAP-Server PRDSAPX5_PRD_00 on host PRDSAPX5 (wp 0)
M  *  ERROR       ThInit: ThIPCInit
M  *
M  *  TIME        Wed Feb 01 01:00:39 2012
M  *  RELEASE     700
M  *  COMPONENT   Taskhandler
M  *  VERSION     1
M  *  RC          17
M  *  MODULE      thxxhead.c
M  *  LINE        10851
M  *  COUNTER     1
M  *
M  *****************************************************************************
M  
M  PfStatDisconnect: disconnect statistics
M  Entering TH_CALLHOOKS
M  ThCallHooks: call hook >BtcCallLgCl< for event BEFORE_DUMP
M  ThCallHooks: call hook >ThrSaveSPAFields< for event BEFORE_DUMP
M  *** ERROR => ThrSaveSPAFields: no valid thr_wpadm [thxxrun1.c   730]
M  *** ERROR => ThCallHooks: event handler ThrSaveSPAFields for event BEFORE_DUMP failed [thxxtool3.c  261]
M  Entering ThSetStatError
M  ThIErrHandle: do not call ThrCoreInfo (no_core_info=0, in_dynp_env=0)
M  Entering ThReadDetachMode
M  call ThrShutDown (1)...
M  ***LOG Q02=> wp_halt, WPStop (Workproc 0 5288) [dpnttool.c   333]

how can i resolve this issue?

Regards,

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

nicholas_chang
Active Contributor
‎02-01-2012 2:50 AM
0 Kudos

Hi,

You need to identify the problematic datafiles, which is file 35 and rename back to its correct name. 

SQL text: ORA-00376: file 35 cannot be read at this time B ORA-01111: name for data file 35 is unknown - rename to correct file B ORA-01110

Do a search with ORA-01111 for instruction on how to identify and resolve the problem, also Note 19519 - MISSING9999 in v$datafile or BR274W in BRBACKUP.

Refer to oracle alert.log for more info.

Thanks,

nicholas Chang

Show replies
Show replies
former_member188883
Active Contributor
‎02-01-2012 7:32 AM
0 Kudos

Hi,

In addition to the information shared by Nicholas, request you to provide the background of getting this error.

Have you performed system copy or is it a fresh installation ?

Regards,

Deepak Kori

former_member182034
Active Contributor
‎02-01-2012 7:34 AM
0 Kudos

hi Dear,

alert.log information is same which i already sent u: 


ORACLE Instance prd (pid = 11) - Error 376 encountered while recovering transaction (10, 3) on object 58087.
Errors in file f:\oracle\prd\saptrace\background\prd_smon_3320.trc:
ORA-00376: file 38 cannot be read at this time
ORA-01111: name for data file 38 is unknown - rename to correct file
ORA-01110: data file 38: 'E:\ORACLE\PRD\102\DATABASE\MISSING00038'

SQL>SELECT FILE#, STATUS, NAME FROM V$DATAFILE


F:\ORACLE\PRD\SAPDATA3\SR3700_13\SR3700.DATA13
        30 ONLINE
F:\ORACLE\PRD\SAPDATA4\SR3USR_1\SR3USR.DATA1
     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------
        31 RECOVER
R
        32 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00032
        33 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00033
     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------
        34 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00034
        35 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00035
        36 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00036
     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------
        37 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00037
        38 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00038
        39 RECOVER
E:\ORACLE\PRD\102\DATABASE\MISSING00039
39 rows selected.

the files from 31 to 39 are not exist in E:\ORACLE\PRD\102\DATABASE and when i try to delete then getting below errors

SQL> RECOVER DATAFILE 'E:\ORACLE\PRD\102\DATABASE\MISSING00031';
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 31 is unknown - rename to correct file
ORA-01110: data file 31: 'E:\ORACLE\PRD\102\DATABASE\MISSING00031'
ORA-01157: cannot identify/lock data file 31 - see DBWR trace file
ORA-01111: name for data file 31 is unknown - rename to correct file
ORA-01110: data file 31: 'E:\ORACLE\PRD\102\DATABASE\MISSING00031'

now what i have to do mean recover these file or drop?

while result of R3Trans is:

C:\>R3Trans -d
This is R3Trans version 6.14 (release 700 - 03.11.09 - 14:15:00).
unicode enabled version
2EETW152 Cannot open file "trans.log".
R3Trans finished (0012).

Regards,

nicholas_chang
Active Contributor
‎02-01-2012 7:58 AM
0 Kudos

Hi,

You'll risk of losing data if you drop the datafile. Have you follow option 1 from Note 19519 to rename the "MISSING####" datafiles to the actual file name?

former_member182034
Active Contributor
‎02-01-2012 9:04 AM
0 Kudos

hi Nicholas,

Actually, I did copy the sapdata files from source system and these files are not exist on source or my current system.

Regards,

nicholas_chang
Active Contributor
‎02-01-2012 9:18 AM
0 Kudos

have you identify the missing files with sql command:

select * from dba_data_fie where file_id = 35; ?

former_member182034
Active Contributor
‎02-01-2012 10:27 AM
0 Kudos

dear,

the status of files from 31 to 39 is:

SQL> select * from dba_data_files where file_id=39; (31 to 39)

FILE_NAME
---------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\ORACLE\PRD\102\DATABASE\MISSING00039
        39 PSAPSR3                                              AVAILABLE
          39
RECOVER

strange...these files are not exist under E:\ORACLE\PRD\102\DATABASE.

Regards,

Answers (0)

Ask a Question