ORACLE ACTIVE SESSION IN SAP BG JOBS!!! KILL SESSIONS

AVTIVEV$SESSOINS:
===================


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


OUTPUT:

SID	SERIAL#	OSUSER	PROGRAM	STATUS	CLIENT_IDENTIFIER	CLIENT_INFO	MODULE	MACHINE	LOGON_TIME	STATE
566	57711	PRODUCTION\SAPServiceGEP	JDBC Thin Client	ACTIVE			JDBC Thin Client	GEEPASAPL3	01-NOV-15	WAITING

SQL> ALTER SYSTEM KILL SESSION '566,57711';


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


UPD-PROCESS-IN DATABASE:
============================


SELECT
   LAST_UPDATE_TIME, to_char(start_time,'hh24:mi:ss') stimeopname,message,( sofar/totalwork)* 100 percent, sofar,
   totalwork,
   time_remaining,
    units,sid,USERNAME
FROM
   v$session_longops
   order by 1, 4


OUTPUT:

LAST_UPDATE_TIME	STIMEOPNAME	MESSAGE	PERCENT	SOFAR	TOTALWORK	TIME_REMAINING	UNITS	SID	USERNAME
31-OCT-15	17:56:23	Table Scan:  SAPSR3.TBTCO: 115284 out of 115284 Blocks done	100	115284	115284	0	Blocks	134	SAPSR3

ORACLE DATA FILE ALLOCATION( FOR SAP)
=======================================


SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name




TOP 10 ORACLE TABLE DETAILS:
====================================


SELECT * FROM(select SEGMENT_NAME,
    SEGMENT_TYPE,
    BYTES/1024/1024/1024 GB,
    TABLESPACE_NAME
from
    dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10





Thanks
Yoonus

Configure retention period for Automatic Workload Repository

              1326067 - Configure retention period for Automatic Workload Repository            

Symptom

Automatic Workload Repository (AWR):
This note describes how you configure the retention period and the snapshot interval for performance data in the Automatic Workload Repository (AWR) for an Oracle database.

By default, the system retains AWR performance data in the AWR repository for a period of 7 days. For important SAP systems, we recommend that you set an AWR retention period of at least 42 days (6 weeks). This kind of retention period enables you to analyze performance problems by comparing the performance between monthly closings based on the AWR performance data.

For more information about AWR, see SAP Notes 853576, 974781, 1028068, 828268, and 1149771.

Other Terms

AWR, Automatic Workload Repository
DBA_HIST_WR_CONTROL

Reason and Prerequisites

Prerequisites for using the AWR

• Oracle Database Release 10.2 or higher
• Sufficient free space for the AWR repository in the SYSAUX tablespace

Solution

Configuration of the AWR retention period

1. Determine the AWR retention period that is currently set.
   
   To do this, use the following SQL command:
   SQL> select retention from dba_hist_wr_control;
2. Define the new AWR retention period.
   Oracle default setting: 7 days
   Recommendation for SAP: At least 42 days
3. Check whether you have to extend the SYSAUX tablespace
   Determine how much freespace is available in the SYSAUX tablespace, how much space is required due to the longer retention period for the AWR, and whether the tablespace must therefore be extended. SAP Note 872851 describes how much space is occupied by which component in the SYSAUX tablespace.
   
   If you increase the AWR retention period from 7 to 42 days, you should calculate a sixfold requirement for the AWR repository. To calculate whether the free space is sufficient for the higher retention period, you can use the following SQL queries:
   
   The space currently filled by the AWR in the SYSAUX tablespace:
   SQL> select SPACE_USAGE_KBYTES/1024 "AWR Size [MBytes]"  from V$SYSAUX_OCCUPANTS where occupant_name = 'SM/AWR';
   
   Total space still free in the SYSAUX tablespace:
   SQL> select sum(bytes)/(1024 * 1024) "SYSAUX MBytes free"  from dba_free_space where tablespace_name = 'SYSAUX';
   
   Total space used in SYSAUX:
   SQL> select sum (space_usage_kbytes) from v$SYSAUX_OCCUPANTS; 
4. Set the desired AWR retention period
   
   The corresponding SQL commands are (this example assumes 6 weeks = 42 days (NUM_DAYS)):
   OS> sqlplus / as sysdba
   SQL> DEFINE NUM_DAYS = 42
   SQL> select retention from dba_hist_wr_control;
   SQL> begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
   SQL> /
   SQL> select retention from dba_hist_wr_control;
   SQL> UNDEFINE NUM_DAYS
   SQL> exit

Configuration of the AWR snapshot interval
The default setting for the AWR snapshot interval is 60 minutes, which is sufficient in most cases. You can make the relevant settings using the following command:

SQL> SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;
SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => 60); END;
SQL> /
SQL> SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;
If you require snapshots in shorter intervals for a more exact analysis of a specific situation, you can shorten this interval, for example, to 30 minutes:
SQL> DEFINE SNAP_INTERVAL=30
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MONTH-DD HH24:MI:SS') FROM DUAL;
SQL> SELECT SNAP_INTERVAL "CURRENT SNAP_INTERVAL" FROM DBA_HIST_WR_CONTROL;
SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( INTERVAL =>  &&SNAP_INTERVAL); END;
SQL> /
SQL> SELECT SNAP_INTERVAL "NEW SNAP_INTERVAL" FROM DBA_HIST_WR_CONTROL;

In most cases, we recommend that you set the standard interval again when the short interval is no longer required.




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


C:\Users\SAPADMIN> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 31 16:10:23 2015
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DEFINE NUM_DAYS = 42
SQL> select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00007 00:00:00.0
SQL> begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
  2  /
old   1: begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
new   1: begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* 42); end;
PL/SQL procedure successfully completed.
SQL> select retention from dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00042 00:00:00.0
SQL> UNDEFINE NUM_DAYS
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL error 1732 when accessing table "PROGDIR".

Category               Installation Errors
Runtime Errors         DBIF_RSQL_SQL_ERROR
Except.                CX_SY_OPEN_SQL_DB
Date and Time          27.08.2015 20:37:59


 Short text
     SQL error 1732 when accessing table "PROGDIR".


 What happened?
     Error Text of the Database: "ORA-01732: data manipulation operation not legal
      on this view"


 What can you do?
     Note which actions and input led to the error.
     For further help in handling the problem, contact your SAP administrator
     .
     You can use the ABAP dump analysis transaction ST22 to view and manage
     termination messages, in particular for long term reference.
     Note which actions and input led to the error.
     For further help in handling the problem, contact your SAP administrator
     .
     You can use the ABAP dump analysis transaction ST22 to view and manage
     termination messages, in particular for long term reference.




Solution

Please reset Oracle parameter _simple_view_merging as it is not needed
at your system.

SQL>alter system reset "_simple_view_merging";

 

SAP note: 1431798 - Oracle 11.2.0: Database Parameter Settings

Thanks

Yoonus

4EETQ399 1 [ dbsloci.c,13255]: SAPUSER error or connect error 99=DBSL_ERR_DB, oerr=12518.

1 ETQ000 ==================================================
1 ETQ399 Executing SQL script '..\var\SQLDBSCRIPT.TQL'.
2 ETQ399 Connecting to database 'ora'.
3EETQ008 Error message: DBSL error 99 (db code -1): Connect failed
4EETQ399 1 [ dbsloci.c,13221]: Logon as OPS$-user to get SAPSR3's password
4EETQ399 1 [ dbsloci.c,16053]: Connecting as /@SOL
on connection 0 (nls 0) ... (dbsl 721 030315, UNICODE[2])
4EETQ399 1 [   dboci.c, 4491]: OCIHandleAlloc(con=0): Service svc=0B357960 created, rc=0
4EETQ399 1 [   dboci.c, 4154]:    OCIServerAttach(OCI_DEFAULT) failed with SQL error 12518:
4EETQ399 1 [   dboci.c, 4157]:    ORA-12518: TNS:listener could not hand off client connection                                                                 
4EETQ399 1 [   dboci.c, 4666]: OCIServerAttach(con=0, svc=0B357960): Error 12518 attaching new srv=0B357B40 to 'SOL'
4EETQ399 1 [   dboci.c, 4669]: OCIHandleFree(con=0): Server handle srv=0B357B40 freed.
4EETQ399 1 [   dboci.c, 4507]: OCIHandleFree(con=0): Service svc=0B357960 freed (i=0).
4EETQ399 1 [   dboci.c, 4511]: rc=12518 -> Trying to reattach to server 'SOL'
4EETQ399 1 [   dboci.c, 4491]: OCIHandleAlloc(con=0): Service svc=0B357960 created, rc=0
4EETQ399 1 [   dboci.c, 4154]:    OCIServerAttach(OCI_DEFAULT) failed with SQL error 12518:
4EETQ399 1 [   dboci.c, 4157]:    ORA-12518: TNS:listener could not hand off client connection                                                                 
4EETQ399 1 [   dboci.c, 4666]: OCIServerAttach(con=0, svc=0B357960): Error 12518 attaching new srv=0B357B40 to 'SOL'
4EETQ399 1 [   dboci.c, 4669]: OCIHandleFree(con=0): Server handle srv=0B357B40 freed.
4EETQ399 1 [   dboci.c, 4507]: OCIHandleFree(con=0): Service svc=0B357960 freed (i=1).
4EETQ399 0 [ dbsloci.c,16069]: CONNECT failed with SQL error '12518'
4EETQ399 1 [ dbsloci.c,13255]: SAPUSER error or connect error 99=DBSL_ERR_DB, oerr=12518.


Solution:

Wrong password combination of OPS$sapserviceSID in database and OPS$sapserviceSID on Oslevel,
make both password is same.,
 

BR0301E SQL error -942 at location db_connect-6, SQL statement:'SELECT NAME FROM V$DATABASE WHERE ROWNUM = 1'ORA-00942: table or view does not exist

Error after Upgrade or System copy:

C:\Users\gpdadm>brconnect -u / -c -f stats -t all -f collect -p 4
BR0801I BRCONNECT 7.20 (36)
BR0805I Start of BRCONNECT processing: ceqyckdt.sta 2015-07-13 12.02.33
BR0484I BRCONNECT log file: E:\oracle\GPD\sapcheck\ceqyckdt.sta
BR0280I BRCONNECT time stamp: 2015-07-13 12.02.34
BR0301E SQL error -942 at location db_connect-6, SQL statement:
'SELECT NAME FROM V$DATABASE WHERE ROWNUM = 1'
ORA-00942: table or view does not exist
BR0310E Connect to database instance GPD failed
BR0280I BRCONNECT time stamp: 2015-07-13 12.02.34
BR0301W SQL error -942 at location brc_dblog_open-1, SQL statement:
'INSERT INTO SAP_SDBAH (BEG, FUNCT, SYSID, OBJ, RC, ENDE, ACTID, LINE) VALUES ('
20150713120233', 'sta', 'GPD', 'ALL', '9999', ' ', 'ceqyckdt', '7.20 (36)')'
ORA-00942: table or view does not exist
BR0324W Insertion of database log header failed
BR0806I End of BRCONNECT processing: ceqyckdt.sta 2015-07-13 12.02.34
BR0280I BRCONNECT time stamp: 2015-07-13 12.02.34
BR0804I BRCONNECT terminated with errors

Solution:

Solution:

SQL> grant sapconn, unlimited tablespace to PSAPSR3
  2  ;
grant sapconn, unlimited tablespace to PSAPSR3
                                       *
ERROR at line 1:
ORA-01917: user or role 'PSAPSR3' does not exist

SQL> grant sapconn, unlimited tablespace to SAPSR3;
Grant succeeded.
SQL> COMMIT;
Commit complete.
SQL> grant sYSTEM, unlimited tablespace to SAPSR3;
grant sYSTEM, unlimited tablespace to SAPSR3
      *
ERROR at line 1:
ORA-01919: role 'SYSTEM' does not exist

SQL> grant sysdba, unlimited tablespace to SAPSR3;
Grant succeeded.
SQL> commit;
Commit complete.
SQL>



SQL> grant sysoper to sapsr3;
Grant succeeded.
SQL> grant sapconn to sapsr3
  2  ;
Grant succeeded.
SQL> grant sapdba to sapsr3;
Grant succeeded.
SQL> alter user SAPSR3 default role all;
User altered.
SQL> select grantee, granted_role, default_role
  2        from dba_role_privs
  3         where grantee = 'SAPSR3';
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SAPSR3                         SAPCONN                        YES
SAPSR3                         SAPDBA                         YES
SQL> select * from session_roles;
no rows selected
SQL>

 ====================================================================
D:\usr\sap\GPD\SYS\exe\NTAMD64>sqlplus /nolog @sapdba_role  SR3
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 23 19:29:19 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option
D:\usr\sap\GPD\SYS\exe\NTAMD64> sqlplus /nolog @sapdba_role SAPSR3

sqlplus /nolog @sapconn_role.sql SR3
sqlplus /nolog @sapconn_role.sql SAPSR3

Symptom If database accesses are performed using the database tools BRBACKUP, BRARCHIVE, BRCONNECT, and BRSPACE, the relevant authorizations are missing. BRBACKUP fails with the following error messages, for example: BR0051I BRBACKUP 7.00 (20) BR0055I Start of database backup: bddzbuxf.ant 2009-11-10 10.12.35 BR0280I Time stamp 2009-11-10 10.12.36 BR0301W SQL error -1031 at location BrLicCheck-7 ORA-01031: insufficient privileges BR0301W SQL error -942 at location BrbDbLogOpen-1 ORA-00942: table or view does not exist BR0324W Insertion of database log header failed BR0280I Time stamp 2009-11-10 10.12.37 BR0301E SQL error -1031 at location BrCntrlCopy-1 ORA-01031: insufficient privileges BR0320E Copying of control file to ... failed BR0314E Collection of information on database files failed BR0056I End of database backup: bddzbuxf.ant 2009-11-10 10.12.37 BR0280I Time stamp 2009-11-10 10.12.37 BR0054I BRBACKUP terminated with errors In relation to table functions, the following errors, for example, can occur in SAP Release 7.40 SP10 or above: BR0301E SQL error -1031 at location stats_tfun_update-2, SQL statement: 'BEGIN SAP091.UCONRFC_ATTRG (0, 1); END;' ORA-01031: insufficient privileges ORA-06512: at "SAP091.UCONRFC_ATTRG", line 1 ORA-06512: at line 1 BR1867E Updating statistics failed for table function SAP091.UCONRFC_ATTR Other Terms SAPDBA role, BR*Tools Reason and Prerequisites 1. You did not create the SAPDBA role during an SAP/Oracle upgrade, as outlined in the upgrade instructions. 2. You use one of the tools mentioned above in a lower SAP release and the database authorizations have not been adjusted. 3. The database grants were deleted. Solution Download the SQL script for creating the SAPDBA role from the attachment to this SAP Note (for Oracle 10g: sapdba_role_ora10, for Oracle 11g: sapdba_role_ora11, and for Oracle 12c: sapdba_role_ora12 ). Execute this script as follows (sapdba_role.sql in the current directory): sqlplus /nolog @sapdba_role <SAPSCHEMA_ID> Where <SAPSCHEMA_ID> is the schema ID of the SAP database user: - for User SAPR3:      R3 - For the user SAPSR3:     SR3 - for User SAP<SID>:   <SID> - for User SAP<SID>DB: <SID>DB Sample call for a standard installation with the schema SAPSR3: sqlplus /nolog @sapdba_role SR3 and with the schema SAPR3: sqlplus /nolog @sapdba_role R3 The log file sapdba_role.log is created in the current directory. Note 1: ---------- The schema ID is not to be confused with the schema name. Therefore, the following call is incorrect: sqlplus /nolog @sapdba_role SAPSR3 Caution 2 ---------- For MCOD systems, the script must be executed for all SAP schemas, for example, for ABAP schemas and Java schemas in a database: sqlplus /nolog @sapdba_role SR3 sqlplus /nolog @sapdba_role SR3DB Caution 3 ---------- Since non-ABAP systems/schemas (a pure SAP J2EE database, for example) do not contain all of the tables mentioned in the SAPDBA role, the log file will contain error messages, for example: grant ALL on SAPSR3DB.DBAML to sapdba                       * ERROR at line 1: ORA-00942: table or view does not exist You can ignore these error messages.

Thanks
Yoonus

1173159 - BR*Tools errors occur when accessing "database" directory

Symptom

The following errors occur in Oracle 10g or higher:

Symptom 1
---------
When you access the directory %ORACLE_HOME%\database, the system displays the following warnings of BR*Tools on Windows platforms, for example:
BR0252W Function remove() failed for 'D:\oracle\SID\102\database\sapSID.ora' at location BrInitOraCreate-1
BR0253W errno 13: Permission denied
BR0252W Function remove() failed for 'D:\oracle\SID\102\database\sapSID.ora' at location BrInitOraCopy-7
BR0253W errno 13: Permission denied

Symptom 2
---------
After you use the Database Upgrade Assistant (DBUA) to apply Oracle patch set 10.2.0.4, BRBACKUP and BRCONNECT fail and the system issues error messages such as:
BR0252E Function CreateFile() failed for 'E:\oracle\SID\102\database\spfileSID.ora' at location BrFileStatGet-2
BR0253E errno 5: Access is denied.
BR0273E Determination of file status for E:\oracle\SID\102\database\spfileSID.ora failed

Other Terms

BR*Tools, BRBACKUP, BRCONNECT

Reason and Prerequisites

1173159 - BR*Tools errors occur when accessing "database" directory

These problems occur only if you use the SAP system (transaction DB13) to start BRBACKUP or BRCONNECT.
Symptom 1 occurs if the Oracle software was not installed in an SAPinst operation in which the access authorizations are automatically adjusted to the database directory.
Symptom 2 does not occur if you applied patch set 10.2.0.4 manually (not using DBUA).

Solution

For symptom 1:
Under user <SID>ADM in a command prompt window, change the access authorizations on the database directory using the following commands:
cacls %ORACLE_HOME%\database /E /G ORA_OPER:F
cacls %ORACLE_HOME%\database /E /G ORA_%ORACLE_SID%_OPER:F
cacls %ORACLE_HOME%\database /E /G ORA_DBA:F
cacls %ORACLE_HOME%\database /E /G ORA_%ORACLE_SID%_DBA:F
For symptom 2:
To eliminate symptom 2, you must change the access authorizations so that they do not apply only to the database directory, but to all database files and the spfile. To avoid a large amount of manual steps, in BRCONNECT 7.00 Patch 39 and BRCONNECT 7.10 Patch 19, the workaround option "-SFP" (set file permissions) is provided for the "check" function. You can use this to perform a BRCONNECT call under the user <SID>ADM to set all of the required access authorizations, for example:
brconnect -u / -c -f check -SFP
In the process, the access authorizations to the following directories and the subdirectories and files under them are modified:
%ORACLE_HOME%\database,
<X>:\oracle\<DBDID>\origlog
<X>:\oracle\<DBDID>\mirrlog
<X>:\oracle\<DBDID>\sapdata<N>
If the system issues any warnings about Windows groups that do not exist (for example, ora_oper), you can ignore these.

Long long run MAIN_SHDRUN/ACT_UPG with all component Update

1387739 - Out of memory errors during shadow system operation

 

 

Long long run MAIN_SHDRUN/ACT_UPG with all component Update  sum,ehp ETC

 

 

Symptom

Out of memory error in ACT_UPG phase during EHP installation using SUM.
Dump EXSQL_DSQL_NOROLL in RUN_RADCUCNT_ALL SUM phase.

Other Terms

SUM ACT_UPG RDDMASGL TSV_TNEW_PAGE_ALLOC_FAILED SYSTEM_NO_ROLL TSV_LIN_ALLOC_FAILED RUN_RADCUCNT_ALL EXSQL_DSQL_NOROLL TSV_TNEW_OCCURS_NO_ROLL_MEMORY

Reason and Prerequisites

Mass-Activation program of the ABAP dictionary can run out of memory depending of the kind and number of packages included in the EHP installation.

Solution

Generally the solution is to change the memory parameters of the shadow system so that the dictionary activation program can use more physical memory.

Explanation of some terms used below:

• DIR_PUT can be found using the transaction AL11.

• Depending on the version of the update tool you are using following substitutions might be required:

           SUM -> SAPEHPI
           SAPup -> SAPehpi

Changing memory parameters of the shadow system

The relevant profile parameters of the shadow system and their values recommended for fixing the problem are:

abap/heap_area_total = 10000000000
abap/heap_area_nondia = 10000000000
ztta/roll_area = 20000000

To change the parameter you need to process as follows:

1. Stop the shadow system.

2. Add/change the mentioned parameters in the instance profile of the shadow  system. To find out the right file name and path of the shadow instance profile look in the log file <DIR_PUT>/log/SAPup.log for

              # SHD PROFILE PATH
              # SHD PROFILE NAME

3. Start shadow system.

4. Repeat the ACT phase.

Before changing this parameters make sure you have read the general memory notes 146289, 88416 (MS Windows), 941735 (64-bit Linux).
Preventive setting of the parameters
In some cases you might expect the described error to arise: for example, if the error already occurred during SUM execution on the test or development system and you are now about to update your productive system using the same EHP and SP stack.
If you are absolutely sure that the described error will arise in your planed SUM application you can set the memory parameters for the shadow system preventively in the phase START_SHDI_FIRST:

1. Execute the command

              <DIR_PUT> /bin/SAPup stop START_SHDI_FIRST
              You should execute this command after the update was started but before the phase START_SHDI_FIRST was processed.

2. When the EHPI/SUM stops in START_SHDI_FIRST change the profile parameters as described in the previous section.

              You don't need to start/stop shadow system!

3. Continue with the SUM.

It is not recommended to change the memory parameters of the shadow system in advance as described here if you are not sure that the error will occur! The changed settings may significantly reduce the performance of your productive system during the preprocessing phase of the SUM execution.