SAPup broke during phase SUBMOD_SHDALIASDEL/SCEXEC_DEL_ALI in module MAIN_SHDIMP / Shadow Import

SCM UPGRADE ISSUE
----
This trouble ticket was created by SAPup on 20141023123211
-------------------------------------------------------------
SAPup broke during phase SUBMOD_SHDALIASDEL/SCEXEC_DEL_ALI in module MAIN_SHDIMP / Shadow Import
Error Message: 12 processes failed, check 'SCEXEC_D.*' for details
-------------------------------------------------------------
-------------------------------------------------------------
Summary of SAPup:
-------------------------------------------------------------
SAPehpi Release: lmt_007
SAPehpi Version: SAPup release lmt_007 version 47.000
Start Release:  702
Target Release:  740
-------------------------------------------------------------
Summary of host system details:
-------------------------------------------------------------
SID:   GWD
Host:   SCMDEV
MS Host:  SCMDEV
GW Host:  SCMDEV
Start Path:  E:\usr\sap\GWD\DVEBMGS00\exe
Kernel Path:  E:\usr\sap\GWD\DVEBMGS00\exe
-------------------------------------------------------------
Summary of operating system details:
-------------------------------------------------------------
OS Type:  Windows NT X86_64
OS Version:  6.0
-------------------------------------------------------------
Summary of database details:
-------------------------------------------------------------
Database Type:  ora
Database Version: 11.2.0.3.0
-------------------------------------------------------------
Summary of RFC details:
-------------------------------------------------------------
Host:   SCMDEV
GW Host:  SCMDEV
Client:   000
Destination:  GWD
Language:  E
System No.:  01
-------------------------------------------------------------






EXECUTING E:\SUM\abap\exe\tp.EXE  pf=E:\SUM\abap\var\EXDBNEW.TPP execdbscript GWD -x ..\var\PTALDR04.XQL DBEXECNONT DBEXECNOCHECK
This is E:\SUM\abap\exe\tp.EXE version 380.04.07 (release 720, unicode enabled)
Warning: Parameter DBCONFPATH is no longer used.
E:\SUM\abap\tmp\SLOG740.LOC is already in use (1), I'm waiting 1 sec (20141023123207). My name: pid 7020 on SCMDEV (gwdadm)
E:\SUM\abap\tmp\SLOG740.LOC is already in use (2), I'm waiting 3 sec (20141023123208). My name: pid 7020 on SCMDEV (gwdadm)
TRACE-INFO: 1:  [     dev trc,00000]  Thu Oct 23 12:32:04 2014                                                         1131  0.001131
TRACE-INFO: 2:  [     dev trc,00000]  DlLoadLib success: LoadLibrary("dboraslib.dll"), hdl 0, addr 000007FEEC180000
TRACE-INFO: 3:                                                                                                           13  0.001144
TRACE-INFO: 4:  [     dev trc,00000]      using "E:\SUM\abap\exe\dboraslib.dll"                                           9  0.001153
TRACE-INFO: 5:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=47=DBSL_CMD_IMP_FUNS_SET) -> changed=30, tagset=1       227  0.001380
TRACE-INFO: 6:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=39=DBSL_CMD_SET_SINGLETASK)                              15  0.001395
TRACE-INFO: 7:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=10=DBSL_CMD_DBCA_PTR_GET)                                14  0.001409
TRACE-INFO: 8:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=10=DBSL_CMD_DBCA_PTR_GET)                                14  0.001423
TRACE-INFO: 9:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=74=DBSL_CMD_CON_INFO_EXT_SUPPORT) -> rc=0=DBSL_ERR_OK, con_vers=0
TRACE-INFO: 10:                                                                                                           28  0.001451
TRACE-INFO: 11:  [     dev trc,00000]  -->DbSlControl(con=-1,cmd=79=DBSL_CMD_CON_INFO_EXT_SUPPORT_FOR_UPGRADE) -> rc=0=DBSL_ERR_OK, con_vers=0
TRACE-INFO: 12:                                                                                                           28  0.001479
TRACE-INFO: 13:  [     dev trc,00000]  -->DbSlConnect(vers=0, type=0, env='[default env]', usr='SAPSR3SHD', kernel dbsl='720')
TRACE-INFO: 14:                                                                                                         1767  0.003246
TRACE-INFO: 15:  [     dev trc,00000]  Prepending C:\oracle\Client\11x_64 to Path.                                       203  0.003449
TRACE-INFO: 16:  [     dev trc,00000]  Register application info.                                                        133  0.003582
TRACE-INFO: 17:  [     dev trc,00000]  Oracle client version: 11.2.0.3.0, (dbsl 720 291112, UNICODE[2])                12334  0.015916
TRACE-INFO: 18:  [     dev trc,00000]  Detailed Oracle client (patch) info not available.                                 14  0.015930
TRACE-INFO: 19:  [     dev trc,00000]    Default connection: tnsname ='GWD'                                               12  0.015942
TRACE-INFO: 20:  [     dev trc,00000]  -->oci_initialize(con=0, char='UTF16', nchar='AL16UTF16', nls=-1),uc_ln=2          23  0.015965
TRACE-INFO: 21:  [     dev trc,00000]  (-) OCIEnvCreate(mode=16384=OCI_UTF16) returned 0 (for default NLS handle)      31071  0.047036
TRACE-INFO: 22:  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_ERROR, con=0, ErrHp=059285F8)                          45  0.047081
TRACE-INFO: 23:  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_ERROR, con=0, ErrBt=059298F8)                          24  0.047105
TRACE-INFO: 24:  [     dev trc,00000]  NLS env. settings: lang=41004D00450052004900430041004E00                           75  0.047180
TRACE-INFO: 25:  [     dev trc,00000]                     terr=41004D0045005200490043004100                               26  0.047206
TRACE-INFO: 26:  [     dev trc,00000]                     char=55005400460031003600                                       14  0.047220
TRACE-INFO: 27:  [     dev trc,00000]  Client NLS setting (by OCINlsGetInfo(con=0)): 'AMERICAN_AMERICA.UTF16'             17  0.047237
TRACE-INFO: 28:  [     dev trc,00000]  charset='UTF16', ncharset='AL16UTF16', UNI_ASC=FALSE                               13  0.047250
TRACE-INFO: 29:  [     dev trc,00000]  Connecting as SAPSR3SHD/<pwd>@GWD on connection 0 (nls 0) ... (dbsl 720 291112, UNICODE[2])
TRACE-INFO: 30:                                                                                                           29  0.047279
TRACE-INFO: 31:  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SVCCTX, con=0, svchp=0592B750)                         24  0.047303
TRACE-INFO: 32:  [     dev trc,00000]  Allocating server context handle                                                   13  0.047316
TRACE-INFO: 33:  [     dev trc,00000]  Attaching to database server GWD (con=0, svchp=0592B750, srvhp=0592FB70)           33  0.047349
TRACE-INFO: 34:  [     dev trc,00000]  Thu Oct 23 12:32:07 2014                                                      2920698  2.968047
TRACE-INFO: 35:  [     dev trc,00000]  Assigning server context 0592FB70 to service context 0592B750                      11  2.968058
TRACE-INFO: 36:  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SESSION, con=0, usrhp=05987510)                        63  2.968121
TRACE-INFO: 37:  [     dev trc,00000]  Assigning username to user session: con=0, usrhp=05987510                          19  2.968140
TRACE-INFO: 38:  [     dev trc,00000]  Assigning password to user session: con=0, usrhp=05987510                          27  2.968167
TRACE-INFO: 39:  [     dev trc,00000]  Starting user session: OCISessionBegin(con=0, usr='SAPSR3SHD', svc=0592B750, srv=0592FB70, usr=05987510)
TRACE-INFO: 40:                                                                                                          129  2.968296
TRACE-INFO: 41:  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR                         6500  2.974796
TRACE-INFO: 42:  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:                       109  2.974905
TRACE-INFO: 43:  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       
TRACE-INFO: 44:                                                                                                           23  2.974928
TRACE-INFO: 45:  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error '1017'                                  24  2.974952
TRACE-INFO: 46:  [     dev trc,00000]     set_ocica() -> SQL error code 1017                                              12  2.974964
TRACE-INFO: 47:  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=1017)                                                 11  2.974975
TRACE-INFO: 48:  [     dev trc,00000]     OCIErrorGet -> SQL error code: 1017                                             87  2.975062
TRACE-INFO: 49:  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       
TRACE-INFO: 50:                                                                                                           19  2.975081
TRACE-INFO: 51:  [     dev trc,00000]     DbSlConnect(con=0) -> orc=0, 99=DBSL_ERR_DB                                     13  2.975094
TRACE-INFO: 52:  [    dblink  ,00000]  ***LOG BY2=>sql error 1017   performing CON                                        32  2.975126
TRACE-INFO: 53:  [    dblink  ,00000]  ***LOG BY0=>ORA-01017: invalid username/password; logon denied                     15  2.975141
tp returncode summary:
TOOLS: Highest return code of single steps was: 0
ERRORS: Highest tp internal error was: 0232
tp finished with return code: 232
meaning:
  connect failed
tp=>sapparam(1c): No Profile used.
tp=>sapparam: SAPSYSTEMNAME neither in Profile nor in Commandline
ERROR: Connect to GWD failed (20141023123207, probably wrong environment).
SAPup> Process with PID 7020 terminated with status 232 at 20141023123211!
===============================================================
Thu Oct 23 13:15:31 2014
M  calling db_connect ...
B  Loading DB library 'E:\SUM\abap\exe\dboraslib.dll' ...
B  Library 'E:\SUM\abap\exe\dboraslib.dll' loaded
B  Version of 'E:\SUM\abap\exe\dboraslib.dll' is "720.00", patchlevel (0.327)
B  con_info_ext_support(): DBSL supports extended connect protocol
B    ==> connect info for default DB will be read from ssfs
B  con_info_exp_support(): profile parameter auth/shadow_upgrade is set
B  con_info_ext_support(): DBSL supports extended connect protocol for shadow instance
B   ==> Using shadow upgrade key set to get connect info from ssfs
N  RSecSSFs: Configuration value "rsec/ssfs_datapath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_DATAPATH [rsecssfs.c 4029]
N  RSecSSFs: Configuration value "rsec/ssfs_keypath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_KEYPATH [rsecssfs.c 4038]
N  RSecSSFs: Reverting overwriting of profile by different environment (disabled in dw) [rsecssfs.c 4048]
N  RSecSSFs: Configuration value "rsec/ssfs_datapath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_DATAPATH [rsecssfs.c 4029]
N  RSecSSFs: Configuration value "rsec/ssfs_keypath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_KEYPATH [rsecssfs.c 4038]
N  RSecSSFs: Reverting overwriting of profile by different environment (disabled in dw) [rsecssfs.c 4048]
N  RSecSSFs: Configuration value "rsec/ssfs_datapath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_DATAPATH [rsecssfs.c 4029]
N  RSecSSFs: Configuration value "rsec/ssfs_keypath" from profile explicitly overwritten by different value in environment variable RSEC_SSFS_KEYPATH [rsecssfs.c 4038]
N  RSecSSFs: Reverting overwriting of profile by different environment (disabled in dw) [rsecssfs.c 4048]
C  Prepending C:\oracle\Client\11x_64 to Path.
C  Register application info.
C  Oracle client version: 11.2.0.3.0, (dbsl 720 291112, UNICODE[2])
C  Detailed Oracle client (patch) info not available.
C  Client NLS setting (by OCINlsGetInfo(con=0)): 'AMERICAN_AMERICA.UTF16'
C  Connecting as SAPSR3SHD/<pwd>@GWD on connection 0 (nls 0) ... (dbsl 720 291112, UNICODE[2])
C  Attaching to database server GWD (con=0, svchp=1B353BA0, srvhp=1B354280)
C  Starting user session: OCISessionBegin(con=0, usr='SAPSR3SHD', svc=1B353BA0, srv=1B354280, usr=1B46EDC0)
C 
C Thu Oct 23 13:16:32 2014
C     OCISessionBegin(OCI_DEFAULT) failed with SQL error 3113:
C     ORA-03113: end-of-file on communication channel                                                                          
C  Server handle (con=0,svchp=1B353BA0,srvhp=1B354280) detached
C  *** ERROR => CONNECT failed with sql error '3113'
 [dbsloci.c    15284]
B  ***LOG BV3=> severe db error 3113      ; work process is stopped [dbsh         1241]
B  ***LOG BY2=> sql error 3113   performing CON [dblink       544]
B  ***LOG BY0=> ORA-03113: end-of-file on communication channel [dblink       544]
M  ***LOG R19=> ThInit, db_connect ( DB-Connect 000256) [thxxhead.c   1484]
M  in_ThErrHandle: 1
M  *** ERROR => ThInit: db_connect (step 1, th_errno 13, action 3, level 1) [thxxhead.c   11318]
M 
M  Info for wp 0
M 
M    pid = 10592
M    severity = 0
M    status = 0
M    stat = WP_NEW
M    waiting_for = NO_WAITING
M    reqtype = DP_RQ_DIAWP
M    act_reqtype = NO_REQTYPE
M    req.req_info =
M    req.tid = -1
M    req.uid = 4294967295
M    req.mode = 255
M    req.len = 0
M    req.rq_id = 65535
M    req.rq_source =
M    last_tid = 0
M    last_uid = 0
M    last_mode = 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  ThIErrHandle: current request:
M 
M  -IN-- sender_id ?                 tid  -1    wp_ca_blk   -1      wp_id -1
M  -IN-- action    -                 uid  -1    appc_ca_blk -1      type  -   
M  -IN-- new_stat  NO_CHANGE         mode 255   len         0       rq_id -1
M 
M  *****************************************************************************
M  *
M  *  LOCATION    SAP-Server SCMDEV_GWD_01 on host SCMDEV (wp 0)
M  *  ERROR       ThInit: db_connect
M  *
M  *  TIME        Thu Oct 23 13:16:32 2014
M  *  RELEASE     720
M  *  COMPONENT   Taskhandler
M  *  VERSION     1
M  *  RC          13
M  *  MODULE      thxxhead.c
M  *  LINE        11599
M  *  COUNTER     1
M  *
M  *****************************************************************************
M 
M  PfStatDisconnect: disconnect statistics
M  Entering TH_CALLHOOKS
M  ThCallHooks: call hook >ThrBtcCallLgCl< for event BEFORE_DUMP
M  ThCallHooks: hook >ThrBtcCallLgCl< o.k.
M  ThCallHooks: call hook >ThrSaveSPAFields< for event BEFORE_DUMP
M  *** ERROR => ThrSaveSPAFields: no valid thr_wpadm [thxxrun1.c   867]
M  *** ERROR => ThCallHooks: event handler ThrSaveSPAFields for event BEFORE_DUMP failed [thxxtool3.c  303]
M  ThIErrHandle: 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 (Workp. 0 10592) [dpnttool.c   339]


=======================================================
---------------------------------------------------
trc file: "dev_ms", trc level: 1, release: "720"
---------------------------------------------------
[Thr 6960] Thu Oct 23 13:19:20 2014
[Thr 6960] ms/http_max_clients = 500 -> 500
[Thr 6960] MsSSetTrcLog: trc logging active, max size = 52428800 bytes
systemid   562 (PC with Windows NT)
relno      7200
patchlevel 0
patchno    413
intno      20020600
make       multithreaded, Unicode, 64 bit, optimized
pid        10928
[Thr 6960] ***LOG Q01=> MsSInit, MSStart (Msg Server 1 10928) [msxxserv.c   2280]
[Thr 6960] MsInitAclInfo: acl file E:\SUM\abap\GWD\SYS\global\ms_acl_info.DAT not found, unrestricted access
[Thr 6960] MsGetOwnIpAddr: my host addresses are :
[Thr 6960]   1 : [192.168.77.217] SCMDEV.geepas.local (HOSTNAME)
[Thr 6960]   2 : [127.0.0.1] SCMDEV.geepas.local (LOCALHOST)
[Thr 6960] MsHttpInit: full qualified hostname = SCMDEV.geepas.local
[Thr 6960] HTTP logging is switch off
[Thr 6960] MsHttpOwnDomain: own domain[1] = geepas.local
[Thr 6960] *** I listen to port 3601 (3601) ***
[Thr 6960] CUSTOMER KEY: >F1866472886<
[Thr 6960] build version=720.2013.01.17
[Thr 6692] Thu Oct 23 13:20:59 2014
[Thr 6692] MsSExit: received SIGINT (2)
[Thr 6692] ***LOG Q02=> MsSHalt, MSStop (Msg Server 10928) [msxxserv.c   7631]


==================================

Solution:
Check the User account status: of  SAPSR3SHD

SQL> select username,account_status from dba_users;

Reset the password

SQl> alter user SAPSR3SHD ACCOUNT UNLOCK;

Change the Password in  SQL Pluse

SQL> alter user SAPSR3SHD identified by <password>


Also reset passwor in Br connect:

brconnect -u / -f chpass -o SAPSR3SHD -p passowrd




And copy SSF file from

E:\usr\sap\GWD\SYS\global\security\rsecssfs\data\SSFS_GWD   to

E:\SUM\abap\GWD\SYS\global\security\rsecssfs\data\SSFS_GWD

And restart the shadow instance service and start the instance

And check the password in sapuser table 
open command prompt and execute below:
--------------------------------------

sqlplus /
select * from sapuser;
exit

Thanks
Yoonus

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 19 17:35:45 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1987641344 bytes

Fixed Size                  2256504 bytes

Variable Size            1006633352 bytes

Database Buffers          973078528 bytes

Redo Buffers                5672960 bytes

Database mounted.

Database opened.

SQL>

 

 

Solution:

This log mainly happen when you reset the parameter ,so to avoid this error appear to reset the parameters

alter system reset log_archive_start scope = spfile;

SQL> alter system reset log_archive_start scope = spfile;

The system has been changed.

SQL> shutdown immediate;
The database has been shut down.
The database has been uninstalled.
ORACLE instance shut down. 

SQL> startup
ORACLE instance started.

Thanks

Yoonus 

CJS-00084 SQL statement or script failed. DIAGNOSIS: Error message: ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

ERROR 2014-10-07 10:37:22.120
CJS-00084  SQL statement or script failed. DIAGNOSIS: Error message: ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
Disconnected
. SOLUTION: For more information, see ora_sql_results.log and the Oracle documentation.



SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DU
PLEX_DEST
SQL> shut immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DU
PLEX_DEST
SQL>

Solution:

Change the spfile value and init<SID> value on following parameter

LOG_ARCHIVE_DEST_1( set value for this parameter in both file)
 LOG_ARCHIVE_DEST (Remove this parameter from both file)

And start the serverice and startup the database;

 
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1987641344 bytes
Fixed Size                  2256504 bytes
Variable Size            1006633352 bytes
Database Buffers          973078528 bytes
Redo Buffers                5672960 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

Thanks
Yoonus


 

Table reorganization process cancelled after process went to materialized view mode.

Table Re org issues. ORACLE

BALADAT Table reorganization process cancelled after process went to materialized view mode.Cannot restart again it says BALDAT have materialized view.

SQL> DROP MATERIALIZED VIEW LOG ON SAPSR3.BALDAT.

 Again the process is still active in V$session table which cannot even process the –f stats collection .

Again required to kill the session from v$session table

SQL> ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’

After this process –stats collect successfully ran.

Use below Qry to check.

I USE DROP TABLE

SQL> DROP TABLE SAPSR3.BALDAT#$ 

WHICH CREATED FOR RE-ORAGANIZATION Check the uncompleted update process on table

SQL> SELECT sid,TIMESTAMP,USERNAME,LAST_UPDATE_TIME, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1

 

DBSIZE.TPL file will create while exporting the SAP or else you can copy from export CD.

DBA JOBS

==================
SQL> select job, last_date last_refresh,
   next_date next_refresh, total_time,
   broken, failures, what
   from dba_jobs;
  select r.rowner, r.rname, r.job, c.name,
   r.next_date next_refresh, r.broken
   from all_refresh r, all_refresh_children c
   where r.job = c.job;
----------------------
The following query will indicate when an mview was last successfully refreshed and what type of refresh was done.

   SQL> column last_refresh_type format a18
   column owner format a7
   column mview_name format a12
   select owner, mview_name, last_refresh_type, last_refresh_date
   from dba_mviews;

M_view checking
==================
SQL> SELECT MVIEW_NAME, FAST_REFRESHABLE FROM USER_MVIEWS;
select mview_name, last_refresh_date from all_mviews;
select * from  dba_mviews;


SQL> select a.name, b.value
 from v$statname a, v$mystat b
  where a.statistic# = b.statistic#
  and a.name = 'redo size'

SQL> SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH


SQL> SELECT   S.USERNAME,   s.osuser,
         S.SID,
         S.SERIAL#,
         L.TYPE,
         L.ID1,
         L.ID2
FROM     V$LOCK L, V$SESSION S
WHERE    L.SID=S.SID
AND S.SID='477'
UPDATE v$SESSION SET STATUS='INACTIVE' WHERE SID='477';

=============================================================

SQL> column "MVIEW BEING REFRESHED" format a30
column INSERTS format 9999999
column UPDATES format 9999999
column DELETES format 9999999
column sid_knst format 9999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR
"MVIEW BEING REFRESHED",
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE',
3, 'WRAPUP', 'UNKNOWN' ) STATE,
TOTAL_INSERTS_KNSTMVR INSERTS,
TOTAL_UPDATES_KNSTMVR UPDATES,
TOTAL_DELETES_KNSTMVR DELETES
from X$KNSTMVR X
WHERE type_knst=6 and
exists (select 1 from v$session s
where s.sid=x.sid_knst and
s.serial#=x.serial_knst);
ALTER SYSTEM KILL SESSION  'SID , SERIAL'
UPDATE v$SESSION SET STATUS='INACTIVE' WHERE SID='477';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
==============================================================
SQL> column "pid" format a30
column spid format 9999999
SELECT   P.PID,
         P.SPID,
         DECODE (P.USERNAME,
                 '?', DECODE(S.USERNAME,
                             NULL, P.USERNAME,
                             '(' || S.USERNAME || ')'),
                 P.USERNAME),
         NVL(S.TERMINAL, P.TERMINAL),
         P.LATCHWAIT,
         NVL(S.PROGRAM, P.PROGRAM)
FROM     V$PROCESS P, V$SESSION S
WHERE    P.ADDR = S.PADDR(+) and s.sid='477'
order by 4
===================================================================

SQL> SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program,
       s.status,
       S.CLIENT_IDENTIFIER,
       S.MODULE,
       S.MACHINE
FROM   v$session s
   WHERE TYPE = 'USER'
   AND AUDSID != USERENV('SESSIONID')
   and s.status='ACTIVE'
order by  s.osuser


SQL> SELECT sid
  FROM v$lock
 WHERE type = 'TX'
   AND id1 = (SELECT object_id
                FROM all_objects
               WHERE owner = owner
                 AND object_name = object_name)



SQL> SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program,
       s.status,
       S.CLIENT_IDENTIFIER,
       S.CLIENT_INFO,
       S.MODULE,
       S.MACHINE,
       S.LOGON_TIME,
       S.STATE
FROM   v$session s
   WHERE TYPE = 'USER'
   AND AUDSID != USERENV('SESSIONID')
   and s.status='ACTIVE'
order by  s.osuser


Thanks
Yoonus
 

HP Data Protector [12:8315] Listener for the selected Oracle database is not running. Integration faild

[12:8315] Listener for the selected Oracle database is not running.
ORA-12154: TNS:could not resolve the connect identifier specified
SP2-0640: Not connected
sqlcode 12154

SAP BACKUP Integration FAILD

Check the SQL Net file in following Location , it should be same in both location

$ORACLE_HOME$\NETWORK\ADMIN

F:\usr\sap\GWD\SYS\profile\oracle


Make sure which SQLnet file is system reading executing below command and copy paste and make the same in both location.

tnsping net_service_name count