Saturday, October 29, 2011

SAP IDES Server

http://www.ides.consolut.eu/sap/bc/gui/sap/its/zuser_req

http://www.ides.consolut.eu/

... conveniently and free of charge with consolut

consolut provides an IDES environment of this type at www.ides.consolut.eu. During your free-of-charge online tour through the SAP ERP world, you will experience typical business processes in the consumer goods, machinery and service sectors.

As we would like to offer you as many functions as possible, access authorizations are very comprehensive. In the interest of all users, we appeal to your fairness and ask you not to make excessive use of these authorizations.
How to obtain an user profile for the SAP IDES system ?

Please use the following URL in order to request user information:
Request a free IDES User http://www.ides.consolut.eu/user_request

(Privacy information)

We will create a user profile for you and send you your password as soon as possible. Please use a mail address where you can actually be reached as it is sometimes necessary to lock out a user for licence reasons. We can then send a new password to your email address.
Start WebAccess http://www.ides.consolut.eu/
Reset Password / Unlock User http://www.ides.consolut.eu/user
Hi,

On first day post diwali holidays, come across below problem with my development server.

Getting below error message when executed startsap ALL
/usr/sap/DEV/SYS/exe/run/startdb: Terminating with error code 12


When check startdb.log.
Its showing database started successfully but is there is a issue with R3trans which failed to connect with database.

IPLDEVQLS:devadm 23> tail -f startdb.log
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

------------------------------ Sat Oct 29 10:22:46 IST 2011
Connect to the database to verify, that the database is now open

R3trans check finished with return code: 12
*** ERROR: Startup of database failed
Notify Database Administrator.
/usr/sap/DEV/SYS/exe/run/startdb: Terminating with error code 12

Why R3trans -d failed hence check trans.log,
Now got the reason.

4 ETW000 [dev trc ,00000] Starting user session: OCISessionBegin(con_hdl=0, usr=SAPSR3/, svchp=0x60000000010d1968, srvhp=0x60000000010d51b8, usrhp
=0x6000000001163ee0)
4 ETW000 272 0.150937
4 ETW000 [dev trc ,00000] OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR 2936 0.153873
4 ETW000 [dev trc ,00000] OCISessionBegin(OCI_DEFAULT) failed with SQL error 28001: 179 0.154052
4 ETW000 [dev trc ,00000] ORA-28001: the password has expired
4 ETW000 35 0.154087
4 ETW000 [dev trc ,00000] server_detach(con_hdl=0,stale=1,svrhp=0x60000000010d51b8) 21 0.154108
4 ETW000 [dev trc ,00000] Detaching from DB Server (con_hdl=0,svchp=0x60000000010d1968,srvhp=0x60000000010d51b8)
4 ETW000 33 0.154141
4 ETW000 [dev trc ,00000] Deallocating server context handle 0x60000000010d51b8 from con_hdl=0
4 ETW000 1118 0.155259
4 ETW000 [dbsloci. ,00000] *** ERROR => CONNECT failed with sql error '28001' 36 0.155295
4 ETW000 [dev trc ,00000] set_ocica() -> SQL error code 28001 20 0.155315
4 ETW000 [dev trc ,00000] -->oci_get_errmsg (con_hdl=0, rc=28001) 21 0.155336
4 ETW000 [dev trc ,00000] OCIErrorGet -> SQL error code: 28001 112 0.155448
4 ETW000 [dev trc ,00000] ORA-28001: the password has expired

4 ETW000 37 0.155485
4 ETW000 [dblink ,00433] ***LOG BY2=>sql error 28001 performing CON [dblink#8 @ 433] 186 0.155671
4 ETW000 [dblink ,00433] ***LOG BY0=>ORA-28001: the password has expired [dblink#8 @ 433] 22 0.155693
2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DEV'"

So root cause is password of SAPSR3 user is expired.

To resolve connect with sys user and set the password same as old.
SQL> conn sys as sysdba
Enter password:
Connected.

SQL> conn SAPSR3
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for SAPSR3
New password:
Retype new password:
Password changed
Connected.
SQL> exit

R3trans -d and its get success.
IPLDEVQLS:devadm 28> R3trans -d
This is R3trans version 6.14 (release 700 - 19.01.11 - 11:44:00).
unicode enabled version
R3trans finished (0000).

So can start SAP Instance.
IPLDEVQLS:devadm 29> startsap ALL

Checking DEV Database
------------------------------
ABAP Database is running

Starting SAP-Collector Daemon
------------------------------
11:14:03 29.10.2011 LOG: Effective User Id is root
***********************************************************************
* This is Saposcol Version COLL 20.95 700 - V2.7.1 2008-08-11 HP-UX IA64
* Usage: saposcol -l: Start OS Collector
* saposcol -k: Stop OS Collector
* saposcol -d: OS Collector Dialog Mode
* saposcol -s: OS Collector Status
* The OS Collector (PID 9370) is already running .....
************************************************************************
saposcol already running

Starting SAP Instance DVEBMGS00
------------------------------
Startup-Log is written to /home/devadm/startsap_DVEBMGS00.log
Instance Service on host IPLDEVQL started
Instance on host IPLDEVQL started

IPLDEVQLS:devadm 30>


Conclusion :
As of Oracle Database 11g Release 2 (11.2), the default limit for PASSWORD_LIFE_TIME for the DEFAULT profile is 180 days. Prior to Oracle Database 11g Release 2 (11.2), the default was UNLIMITED.

Permanent solution refer SAP Note 1519872 - SAP Database User Profile SAPUPROF

Tuesday, August 16, 2011

How to find the which objects in which tablespace OR list of objects in tablespace.

select owner, table_name, tablespace_name
from dba_tables
where table_name='EMP';


select owner, table_name, tablespace_name
from dba_tables
where tablespace_name='PSAPSR3USR';

Tuesday, July 19, 2011

How to resolve HARMFUL_STATISTICS ERROR message appeared during DATABASE CHECK job.

In my database check job come across below error message.

19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.DDXTF
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.DDXTT
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.TRBAT
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.DDXTF~0
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.DDXTT~0
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.TRBAT~0


To resolve neeed to delete harmful statistics through BRTOOLS.

tstmig:orapip 1> brtools
BR0651I BRTOOLS 7.20 (12)

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.02
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

1 = Instance management
2 - Space management
3 - Segment management
4 - Backup and database copy
5 - Restore and recovery
6 - Check and verification
7 - Database statistics
8 - Additional functions
9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
7

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.17
BR0663I Your choice: '7'

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.17
BR0656I Choice menu 13 - please make a selection
-------------------------------------------------------------------------------
Processing database statistics

1 = Update database statistics
2 - Collect missing
4 - Manage database statistics
statistics
3 - Delete harmful statistics
5 - Collect dictionary statistics
6 - Collect system statistics
7 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.20
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.20
BR0657I Input menu 46 - please enter/check input values
-------------------------------------------------------------------------------
BRCONNECT main options for deletion of harmful statistics

1 - BRCONNECT profile (profile) ....... [initPIP.sap]
2 - Database user/password (user) ..... [/]
3 ~ Database owner for delete (owner) . []
4 ~ Ignore DBSTATC table (ignore) ..... []
5 ~ Exclude from delete (exclude) ..... []
6 ~ Tables for delete (table) ......... [harmful]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.53
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.53
BR0657I Input menu 47 - please enter/check input values
-------------------------------------------------------------------------------
Additional BRCONNECT options for deletion of harmful statistics

1 - Confirmation mode (confirm) ...... [yes]
2 - Query mode (query) ............... [no]
3 ~ Force options (force) ............ []
4 - Extended output (output) ......... [no]
5 - Message language (language) ...... [E]
6 - BRCONNECT command line (command) . [-p initPIP.sap -l E -f stats -d -t "harmful"]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c

BR0280I BRTOOLS time stamp: 2011-07-19 11.38.02
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0291I BRCONNECT will be started with options '-p initPIP.sap -l E -f stats -d -t "harmful"'

BR0280I BRTOOLS time stamp: 2011-07-19 11.38.02
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c

BR0280I BRTOOLS time stamp: 2011-07-19 11.38.11
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

###############################################################################

BR0801I BRCONNECT 7.20 (12)
BR0805I Start of BRCONNECT processing: cegiznmy.dst 2011-07-19 11.38.12
BR0484I BRCONNECT log file: /oracle/PIP/sapcheck/cegiznmy.dst

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.17
BR0813I Schema owner found in database PIP: SAPSR3*

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.32
BR0814I Number of tables in schema of owner SAPSR3: 71043
BR0836I Number of tables treated as info cubes for owner SAPSR3: 93

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.55
BR0815I Number of indexes in schema of owner SAPSR3: 84920

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0818I Number of tables found in DBSTATC for owner SAPSR3: 391

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0807I Name of database instance: PIP
BR0808I BRCONNECT action ID: cegiznmy
BR0809I BRCONNECT function ID: dst
BR0810I BRCONNECT function: stats
BR0812I Database objects for processing: HARMFUL
BR0852I Number of tables to delete statistics: 3
Owner SAPSR3: 3
DDXTF DDXTT TRBAT
BR0856I Number of indexes to delete statistics: 0
BR0860I Delete option set - statistics will be deleted

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRCONNECT:
c

BR0280I BRCONNECT time stamp: 2011-07-19 11.39.15
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0280I BRCONNECT time stamp: 2011-07-19 11.39.15
BR0872I Deleting table and index statistics...
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.DDXTF
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.DDXTT
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.TRBAT

BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0873I Statistics deleted for 3/0 tables/indexes

BR0806I End of BRCONNECT processing: cegiznmy.dst 2011-07-19 11.39.16
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.17
BR0802I BRCONNECT completed successfully

###############################################################################

BR0292I Execution of BRCONNECT finished with return code 0

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.17
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRTOOLS:
s

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.34
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.36
BR0257I Your reply: 'y'
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.36
BR0654I BRTOOLS terminated with errors
tstmig:orapip 2> exit
tstmig:orapip 3> logout

Wednesday, July 13, 2011

How to Lock / Unlock Client in SAP

Run these functions with a client input which is to be locked/unlocked. This function set flag '' Client is locked temporarily for client copy" in client maintenance menu.The client will be available for users DDIC and SAP*. If any other user tries to login, system gives message that ' Client locked temporarily'.

1. Run transaction SE37
2. Enter to lock the client - SSCR_LOCK_CLIENT / to unlock the client - SSCR_UNLOCK_CLIENT in function module name
3. press F8 or test run (single run).
4. Specify the client and execute(F8).

How to send Message to Specific user in SAP

1. Run transaction SE37
2. Enter TH_POPUP in function module name
3. press F8 or test run (single run)
4. Specify the client
5. Enter username in user
6. Enter Message in message
7. execute

Wednesday, June 15, 2011

How to apply SAP Bundle patch


SAP Note 1027012 - MOPatch - Install Multiple Oracle Patches in One Run
SAP Note 839182 - Installing patches using OPatch
SAP Note 1175996 - Automated Oracle 10g11g patches check
Extract of SAP Note 1027012 - MOPatch - Install Multiple Oracle Patches in One Run

Use the following commands to determine the versions of the OPatch and MOPatch utilities installed in an Oracle Home:
$ORACLE_HOME/OPatch/opatch version
$ORACLE_HOME/MOPatch/mopatch.sh -h
 

  1. Check SAP Bundle patch installed version.
You can find the version of the currently installed SBP in an Oracle Home in file $ORACLE_HOME/sapbundle/version.txt. After you have successfully run catsbp.sql, you can also find the SBP version in the database in view dba_registry_history in the most recent row associated with bundle series SBP.

  1. Oracle SAP Bundle Patch applies.
Need to follow below steps along with referring README.html file of SAP Bundle Patch zip file.
 
2.2 Installing the SAP Bundle Patch (as per README.html file of SAP Bundle Patch zip file)
Follow the steps below.
o    Check 1.4 OPatch and MOPatch Utility Information
You must use OPatch version 11.2.0.3.1 for installing this SBP. Later 11.2 OPatch versions are supported as well, but Oracle recommends to use version 11.2.0.3.1, since it has been fully tested to interoperate with this SBP.

You must use MOPatch version 2.1.13 or later for installing this SBP.
o    Stop all database applications, in particular all SAP applications.
Performed in step 1 above.
o    Shut down the database instances, the listeners, and other processes associated with the Oracle Home.
su - ora<sapsid>
testsap1:oraehp 51> lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 27-AUG-2012 17:00:07

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EHP.WORLD))
The command completed successfully
testsap1:oraehp 52>


o    Update OPatch version which downloaded from service market place.
su - ora<sapsid>
cp /tmp/opatch-112031.zip $ORACLE_HOME           
cd $ORACLE_HOME
unzip -d $ORACLE_HOME opatch-112031.zip
rm opatch-112031.zip
 
o    Update MOPatch version which downloaded from service market place.
su - ora<sapsid>
cp /tmp/mopatch-2_1_13.zip $ORACLE_HOME
cd $ORACLE_HOME
unzip -d $ORACLE_HOME mopatch-2_1_13.zip
rm mopatch-2_1_13.zip

o    Recommended as per README file
      Some of the patches from a previous SBP have been reverted to an earlier patch version or obsoleted without being replaced by newer patches. Uninstall these patches manually as follows:

su - ora<sapsid>
/bin/sh $ORACLE_HOME/OPatch/opatch nrollback -silent -local -id 9935787,10401327,13603688,13603695

Check installed Opatch id with below command
/oracle/EHP/112_64/OPatch/opatch lsinventory -patch

o    Install the SAP Bundle Patch (11.2.0.2.7) as follows:
Create patch base directory
su - ora<sapsid>
cd /oracle/<SAPSID>
mkdir patches.112027
mv /tmp/SAP_112027_201208_LINX8664.zip /oracle/<SAPSID>/patches.112027
cd /oracle/<SAPSID>/patches.112027
/bin/sh $ORACLE_HOME/MOPatch/mopatch.sh -v -s SAP_112027_201208_LINX8664.zip
           
Check the generated MOPatch report for errors or unresolved conflicts. To resolve them, refer to section 4, "Known Issues" and to section "Resolving Patch Conflicts and Errors" of SAP Note 1027012 MOPatch - Install Multiple Oracle Patches in One Run.

o    Start up the database instances, the listeners, and other processes associated with the Oracle Home.
su - oraehp
lsnrctl start
sqlplus “/nolog”
conn sys as sysdba
startup;

o    2.3 Executing Post-Installation Instructions (setting oracle parameters)

Patch 14208494 (CBO Merge Patch), added in SBP 11.2.0.2.7 201208

Please use the following statements to adjust your _fix_control and event settings in your spfile according to SAP note 1431798 with the new SBP:

ALTER SYSTEM SET "_FIX_CONTROL"=
'5099019:ON','5705630:ON','6055658:OFF','6399597:ON','6430500:ON','6440977:ON',
'6626018:ON','6972291:ON','8937971:ON','9196440:ON','9495669:ON','13077335:ON',
'13627489:ON'
COMMENT='SAP_112027_201208 RECOMMENDED SETTINGS'
SCOPE=SPFILE;

ALTER SYSTEM SET EVENT=
'10027',
'10028',
'10142',
'10183',
'10191',
'10995 level 2',
'31991',
'38068 level 100',
'38085',
'38087',
'44951 level 1024'
COMMENT='SAP_112026_201207 RECOMMENDED SETTINGS'
SCOPE=SPFILE;

Please restart the database after you have adjusted these parameters.

o    For each database instance running from the Oracle home*, connect to the database instance as SYSDBA using SQL*Plus and run the post-installation script catsbp.sql: ( total time taken 2 hrs )

cd /oracle/EHP/11202/sapbundle/SBP_112027_201208

sqlplus / as sysdba
SQL> @/oracle/EHP/11202/sapbundle/SBP_112027_201208/catsbp.sql
SQL> quit

The script evaluates the log files generated by all intermediate steps for unkown errors. If it detects such errors, refer to the log files for more information, resolve the errors and, depending on the error cause, re-start the script. The script may furthermore perform some checks on whether additional post-installation actions are required depending on the state of the database.

To avoid an extra shutdown-startup cycle of the database instance, you should implement any system parameter changes (for example, additions of events and fix controls) from section 2.3, "Executing Post-Installation Instructions" in the above step as well.

o    2.3 Executing Post-Installation Instructions

Patch 14243939, added in SBP 11.2.0.2.7 201208

    After catsbp.sql has been executed please check if there are some invalid objects left over.
    If this is the case please run utlrp.sql to get rid of those invalid objects.

    Please perform the following steps:
      cd /oracle/EHP/11202/
      sqlplus sys as sysdba
    -- List all invalid objects before executing utlrp.sql
       spool invalid_objects_before_utlrp.txt
set cmdsep on
set lines 150 pages 40
col compiled_at for a30; col compiled_by for a10; col owner for a10; col object_name for a35;
col object_type for a12; col object_id for 999999 head obj_id
select do.owner,do.object_name,do.object_type,urc.*
from utl_recomp_compiled urc, dba_objects do where urc.obj# = do.object_id order by compiled_at;

select owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status
from dba_objects where status = 'INVALID' order by object_name;
spool off

    -- Execute utlrp
       @/oracle/EHP/11202/rdbms/admin/utlrp.sql

    -- List all invalid objects after executing utlrp.sql
       spool invalid_objects_after_utlrp.txt
set cmdsep on
set lines 150 pages 40
col compiled_at for a30; col compiled_by for a10; col owner for a10; col object_name for a35;
col object_type for a12; col object_id for 999999 head obj_id
select do.owner,do.object_name,do.object_type,urc.*
from utl_recomp_compiled urc, dba_objects do where urc.obj# = do.object_id order by compiled_at;

select owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status
from dba_objects where status = 'INVALID' order by object_name;
spool off
Patch 14252895, added in SBP 11.2.0.2.7 201208 (already fixed earlier hence not perform this time)

    To enable this fix event 31991 level 1 has to be set.

    To set this event 31991 please use the 'ALTER SYSTEM SET EVENT' statement that is listed in the Post-Installation Instructions section for the current CBO Merge Patch.

    Please restart the database after you have adjusted these parameters.

Patch 13575265, added in SBP 11.2.0.2.5 201203      (not performed below as not using DATA VAULT)

    For Database Vault systems, if Database Vault is already installed,
    run the following script (for RAC systems, run the script on one of the nodes):

    sqlplus / as sysdba                                                    
    spool patch_13575265.log                                           
    @?/rdbms/admin/prvtlbac.plb                                       
    spool off                                                         
    exit  

    The following error message can be ignored:
    'ORA-00955: name is already used by an existing object'

o    Oracle PSUs don't change the oracle release version/fifth digit. That is also described in metalink note #861152.1.
The significance of each number (reading from left to right) is as follows: The release number is 11.2.0.2.0.
11 is the major database release number
2 is the database maintenance release number
0 is the application server release number
2 is the component-specific release number
0 is the platform-specific release number

You can verify the installed PSUs / SBPs with the following query (check the comment section):
1.execute below query
set linesize 500
select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,
substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments
from registry$history;

2. execute below query (this get updated post executation catsbp.sql script after SBP update. if you fogot to execute this script you will not get detail in this view)
select * from dba_registry_history;

o    Start all database applications, in particular all SAP applications.