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.

Unix command to sort biggest size file in the directory

$du -sk * | sort -n

How to empty a Unix file system with cat /dev/null command dev null

First take the backup of existing file :

$cp dev_rd.log /sapcd/XVPdev


then empty the file

$cat /dev/null > dev_rd.log

still it was giving error : File exists.

It meant that noclobber option is set so gave command

$cat /dev/null >! dev_rd.log

and it worked

Removing semaphores shared memory keys with ipcrm

Sometimes we are not able to remove the shared memory keys with cleanipc 52 remove command, we can find the culprit this way :

ipcs | grep crdadm | awk '{printf("ipcrm -s %s\n", $2);}'

or if we already have the error like that

Dec 8 10:52:08 root@globiz63 logpipe[16010]: (start_app crd0v 52 crdadm 1): OsKey: 25238 0x00006296 Semaphore Key: 38 remove failed **** - errno = 1 (Not owner)


you may find the cultprit by

## ipcs | grep 0x00006296

it will give the ownere as well

now log in as the owner and give the command

## ipcrm -s 687 (687 is the semaphore ID)

it should go now.