DBA cockpit ORA-00942 error

After upgrade to EHP2 we were getting the error ORA-00942 on oracle 11g . Implemented the script attached with SAP Note 1546456 and it resolved our issue.

brconnect command to update stats without user intervention or password

brconnect -c -u / -f stats -t all -f collect -p 4

"Message Type X" dump coming while accessing the ST03N data

We were facing the Message_Type_X dump when ever we are trying to see
the historic data in ST03N in APP System.

We go to ST03N -> Administration -> Expert Mode -> Workload --> Total
(When we try to expand the tree node total, immediatly we get the dump)

We found the nearest note to our problem Note 1035246 - ST03N -
MESSAGE_TYPE_X short dump directly after start; where in option 1 is
only valid in our case;and solution job SAP_COLLECTOR_FOR_PERFMONITOR
is available in the system and the report SWNCREORG program doesn’t
exist in the System.

Solution : Resolution is to run the report RSSTAT60 once, the report
corrects any discrepancy between the tables MONI and SAPWLMOIDX. It corrected the error.

Transports stuck in PEQ for long time

Please perform the following cleanup activities to make sure the
transports run smoothly:

1. Ensure that tp.exe process is not running at the OpSys level before
proceeding with the next steps.

2. STMS -> Import Monitor -> remove the obsolete entries "RUNNING" or
error messages ( 1 entry, Select the entry, right click -> Delete
option )

3. please relocate the files under /usr/sap/trans/R3UPG/tmp/ to another temp
directory. Except below files!:
amodprot., moveold.sh, cmodact., tms_test.,
IMODPROT. UMODPROT.

Once the above steps are checked, Kindly refresh the import queue and
reimport the request

Creating mirror logs in oracle if they are missing mirrlogA mirrlogB

SQL) set lines 1000;
SQL) set pagesize 80;
SQL) select group#,status,member from v$logfile;



GROUP# STATUS MEMBER
-----------------------------------------------------------------------------
1 /oracle/SRD/origlogA/log_g11m1.dbf
1 INVALID /oracle/SRD/mirrlogA/log_g11m2.dbf

2 /oracle/SRD/origlogB/log_g12m1.dbf
2 INVALID /oracle/SRD/mirrlogB/log_g12m2.dbf

3 /oracle/SRD/origlogA/log_g13m1.dbf
3 INVALID /oracle/SRD/mirrlogA/log_g13m2.dbf

4 /oracle/SRD/origlogB/log_g14m1.dbf
4 INVALID /oracle/SRD/mirrlogB/log_g14m2.dbf


The above is the system status we can find with command. Invalid is coming due to that the mirrlog doesn't

exist, there is no file at that location.

We may follow the below procedure:



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g11m2.dbf';

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g11m2.dbf' to group 1;



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g12m2.dbf';

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g12m2.dbf' to group 2;



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g13m2.dbf';

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogA/log_g13m2.dbf' to group 3;


ALTER DATABASE DROP LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g14m2.dbf';

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/SRD/mirrlogB/log_g14m2.dbf' to group 4;



If it stills shows invalid under status then give the below command :



alter system switch logfile;



SQL) select group#, status, member from v$logfile;

GROUP# STATUS MEMBER
----------------------------------------------------------------------------
1 /oracle/SRD/origlogA/log_g11m1.dbf
2 /oracle/SRD/origlogB/log_g12m1.dbf
2 INVALID /oracle/SRD/mirrlogB/log_g12m2.dbf
3 /oracle/SRD/origlogA/log_g13m1.dbf
3 INVALID /oracle/SRD/mirrlogA/log_g13m2.dbf
4 /oracle/SRD/origlogB/log_g14m1.dbf
4 INVALID /oracle/SRD/mirrlogB/log_g14m2.dbf

The invalid status will disappear.

For more info; please read SAP Note 491160

Method for resizing redo logs adding new members and groups

Please read SAP Note 79341 - Checkpoint not complete

Location for Alert log file for Oracle 11g

/oracle/(SID)/saptrace/diag/rdbms/(sid)/(SID)/trace

Alter database end backup

To find if database or any tablespace is in backup mode, the status in V$BACKUP is ACTIVE
SQL) select * from v$backup;

If the backup is stuck and you need to end that back up, give the below sql command :

SQL) ALTER DATABASE END BACKUP;
Database altered.

If one tries to perform shutdown of database when database is in BACKUP mode, you will receive the following error “ORA-01149″

SQL) shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/oracle/SRS/sapdata1/sys_01.dbf’

If one tries to set the database that is already in backup mode, one will receive the following error “ORA-01146″

SQL) alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-01146: cannot start online backup – file 1 is already in backup
ORA-01110: data file 1: ‘/oracle/SRS/sapdata1/sys_01.dbf’

Using Device Pools in SPAD

There was a requirement from customer to create a printer that saves a file at specific location and also prints simultaniously on a given printer.

In SPAD, instead of choosing it as a standard printer give Device Pool and save.
You will get a new tab Device pool where in you can define both the printers and choose option to print on both.

This works !!!:)

Kernel Downgrade

We had a requirement where customer wanted to downgrade the kernel: The kernel in sandbox system EBS was on a lower level 7.20 90 and on test system EBQ it was on a higher level 7.20 114.

Due to some project go live that was scheduled the development was working in EBS System but when it was taken to the EBQ System, it didn't work well. SAP told that this is a kernel bug.

So, customer wanted to downgrade the same.

We followed the below procedure :

1) Dowloaded SAPEXE and SAPEXEDB files of version 90 from Service Marketplace.
2) Extracted those files in the already existing kernel of EBQ /sapmnt/EBQ/exe
3) We checked for lib dbsl there are two files dbhdbsllib.so dbsdbslib.so that also came included in the SAPEXE sar file.

In nutshell, extracting SAPEXE and SAPEXEDB kernel files in /sapmnt/EBQ/exe did the trick and the kernel was successfully downgraded from "Rel 7.20 patch 114" to "Rel 7.20 patch 90" exaclty same as in EBS System.

Security parameters related to Passwords expiry and other settings

LOGIN/PASSWORD_EXPIRATION_TIME
0 (Number of days after which users will be required to change the parameters)

login/min_password_lng
6 8 Minimum password length will change from 6 to 8 characters


login/min_password_digits
0 1 Users will be required to use atleast 1 digit in their passwords


login/min_password_letters 0 1 Users will be required to use atleast 1 alphabet in their passwords


login/min_password_lowercase 0 1 Users will be required to use atleast 1 lowercase alphabet in their


login/min_password_uppercase0 1 Users will be required to use atleast 1 uppercase alphabet in their
passwords

login/min_password_specials 0 1 Users will be required to use atleast 1 special character in their passwords

login/password_max_idle_initial 0 14 Validity of initial password 0 0 Dates until password must be changed

Oracle sql query for autoextend off

To find dba data files which are autoextensible:

$select FILE_NAME, AUTOEXTENSIBLE from dba_data_files where FILE_NAME like '%sapdata2%' and AUTOEXTENSIBLE='YES';

To turn off the auto extend for a file :

$ALTER DATABASE DATAFILE '/oracle/BIP/sapdata2/sr3usr_3/sr3usr.data3' autoextend off;

Oracle sql query to find sequence number of archive logs

Oracle sql query to find sequence number of archive logs to be applied for recovery during restoring online database:

$ Select SEQUENCE#,to_char(FIRST_TIME,'YYYY-MM-DD:HH24:MI:SS') FIRST_TIME from v$archived_log where to_char(FIRST_TIME,'YYYY-MM-DD HH24:mi:ss') between '2011-01-10 01:26:54' and '2011-01-11 21:00:00';

Index related oracle sql queries

To create index GLPCA~YG at Database level :

$create index 'GLPCA~YG' on 'GLPCA' ('RCLNT', 'KOKRS', 'RBUKRS', 'RYEAR', 'RPRCTR', 'RACCT', 'POPER') PCTFREE 01 INITRANS 002 TABLESPACE PSAPSR3 STORAGE (INITIAL 000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001)
To display all indexes starting with the name GLPCA in database:

$select index_name from dba_indexes where index_name like 'GLPCA%';
To drop index GLPCA~YG1 in database:

$drop index sapsr3."GLPCA~YG1";

sort command in unix

To find out the biggest file in the directory in descending order

$du -sk * | sort -n

other beautiful command to find in which file the parameter is defined in directory:

$cd /sapmnt/PEP/profile
$grep "DIR_CCMS" *

ccms agent shared memory problems

During startup of CCMS agent, we were getting the shared memory errors:

$sapccm4x -DCCMS pf=/sapmnt/PEP/profile/PEP_DVEBMGS00_pep0v

INFO: use SAPLOCALHOST ew1app

INFO: CCMS agent sapccmsr working directory is /usr/sap/ccms/EW1_02/sapccmsr

INFO: CCMS agent sapccmsr config file is /usr/sap/ccms/EW1_02/sapccmsr/csmconf
INFO: Central Monitoring System is EW1. (found in config file)
INFO: additional Central Monitoring System is SMS. (found in config file)
INFO: Agent is running (actual pid file is detected)

**********************************************************
Another CCMS Agent is already running with this profile.
**********************************************************

EXITING with code 1

Executing the below command resolved this issue:

sapccm4x -initshm pf=/sapmnt/PEP/profile/PEP_DVEBMGS00_pep0v

when we started again it came up.