11g db not starting with error HPUX-ia64 Error: 1: Not owner

While starting the db got error


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 25 10:48:47 2016

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

Connected to an idle instance.

SQL> startup
ORA-27125: unable to create shared memory segment
HPUX-ia64 Error: 1: Not owner
SQL> exit
Disconnected

Cause :  lock_sga=true was set in init file

we have set as per oracle action plan below which got into affect post reboot .

$ grep lock_sga initWCPROD.ora

#*.lock_sga=TRUE

..We also tried relink and checked for shmmax and shmall parameters , but those were not culprits .

oracle.apps.xdo.XDOException: Java heap space (R12.1. OPP issue)

EBS R12.1.3  all reports started going into warning  .

Logs of opp were showing oracle.apps.xdo.XDOException: Java heap space

We restarted OPP issue got resolved .

No rca recieved from oracle as we are allready on optimum opp setup :)

ORA-29701: unable to connect to Cluster Synchronization Service

Recently we got this issue on our 12C db .
We started getting error in all applications log as ORA-29701: unable to connect to Cluster Synchronization Service

we have ASM without RAC . DB 12.1.0.2 

What log shows : 


crsctl check has


this errors out unable to communicate to high availability .


restarted machine and issue got resolved .


Root cause : Some one by mistake ran rm -fr on /tmp and  that iuntern removed socket files from /tmp/.oracle issue .

We found details only after followup on sr .

From alert.log =========== 2016-03-01 12:31:28.503 [ORAAGENT(3757)]CRS-5016: Process "/oracle/grid/12.1.0/product/bin/lsnrctl" spawned by agent "ORAAGENT" for action "check" failed: details at "(:CLSN00010:)" in "/oracle/grid/12.1.0/diag/crs/iprdebd1/crs/trace/ohasd_oraagent_oragrid.trc" 2016-03-01 12:31:37.582 [ORAAGENT(3757)]CRS-5020: Not all endpoints are registered for listener LISTENER 2016-03-01 14:04:05.802 [CLSECHO(14981)]CRS-10001: CRS-6021: Could not set system resource limits for Clusterware: "ulimit -n 65536".<<<<<<<<<<<<<< 2016-03-01 14:14:12.247 [OHASD(14978)]CRS-8500: Oracle Clusterware OHASD process is starting with operating system process ID 14978 2016-03-01 14:14:12.248 [OHASD(14978)]CRS-0715: Oracle High Availability Service has timed out waiting for init.ohasd to be started.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2016-03-01 15:11:26.249 [OHASD(2972)]CRS-8500: Oracle Clusterware OHASD process is starting with operating system process ID 2972 2016-03-01 15:11:26.258 [OHASD(2972)]CRS-0714: Oracle Clusterware Release 12.1.0.2.0. 2016-03-01 15:11:26.346 [OHASD(2972)]CRS-2112: The OLR service started on node iprdebd1. 2016-03-01 15:11:26.538 [OHASD(2972)]CRS-1301: Oracle High Availability Service started on node iprdebd1.  From listener.log  ================= 01-MAR-2016 12:30:45 * service_update * IFFPROD * 0 01-MAR-2016 12:30:51 * service_update * IFFPROD * 0<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Tue Mar 01 12:31:28 2016 System parameter file is /oracle/grid/12.1.0/product/network/admin/listener.ora<<<<<<<<<<<<<<Log messages written to /oracle/grid/12.1.0/diag/tnslsnr/iprdebd1/listener/alert/log.xml Trace information written to /oracle/grid/12.1.0/diag/tnslsnr/iprdebd1/listener/trace/ora_7662_1.trc Trace level is currently 0

Thanks

OTM 6.2.10 purging of unwanted transmission data

 We perfomred and saved good amount of space by purging unwanted data on otm by following standard purge methods as per oracle  .Below are some of details .Activity needs downtime during index rebuild .

Biggest gain will be speed .

1.       Using GLOGOWNER/ user of database execute below command,

set timi on time on

set serveroutput on

                exec domainman.transmission_purge(265,4000,1000);

                Date Difference: 01.01.2015 – 22.09.2015 = 265

Keep 265 days old data, activity will take max 4000 minutes and execute it for bunches of             1000 records

265 Days can vary depending on the data to be required in OTM as per client’s approval

2.       Execute below statement to release space on database level

set timi on time on

ALTER TABLE I_TRANSMISSION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSMISSION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

ALTER TABLE I_TRANSMISSION DISABLE ROW MOVEMENT;

                ALTER TABLE I_TRANSACTION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSACTION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

                ALTER TABLE I_TRANSACTION DISABLE ROW MOVEMENT;

After that we have to rebuild indexes to avoid performance issue, please see below attached list of indexes which need to be rebuild.

Oracle SOA 11.1.1.5 purging

SOA being middleware mostly does not requires much of data to be stored .We can follow standard oracle purge doc for this .Steps we did are below


select sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='DEV_SOAINFRA';

SUM(BYTES/1024/1024/1024)
-------------------------
               325.999969

connect as DEV_SOAINFRA

Create packages 

@/home/oraqa/soa_purge/soa_purge_scripts.sql

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

@soa_purge_scripts.sql

Procedure created.

Function created.

Type created.

Type body created.

PL/SQL procedure successfully completed.

Package created.

Package body created.

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

Run Purge.sql  ---Modify Dates accordingly

$ cat purge.sql

DECLARE

   MAX_CREATION_DATE timestamp;

   MIN_CREATION_DATE timestamp;

   batch_size integer;

   max_runtime integer;

BEGIN

   MIN_CREATION_DATE := to_timestamp('2014-01-01','YYYY-MM-DD');

   MAX_CREATION_DATE := to_timestamp('2014-12-31','YYYY-MM-DD');

    max_runtime := 920;

   batch_size := 5000;

     soa.delete_instances(

     min_creation_date => MIN_CREATION_DATE,

     max_creation_date => MAX_CREATION_DATE,

     batch_size => batch_size,

     max_runtime => max_runtime,

     purge_partitioned_component => false,

     ignore_state => true);

  END;

ALTER TABLE XML_DOCUMENT MODIFY lob (DOCUMENT) (shrink space);

Oracle DB 11g : How to restrict users to login to DB from specific conditions

Lot of time developers run big queries on prod from toad etc and screw up entire database .This is how you can enable filetrs on user machine  ,tool etc . You can create a logon trigger at db level to restrict users to login from toad and sql developer .


create or replace TRIGGER block_toad
  AFTER LOGON ON DATABASE
  DECLARE
   --Declare a cursor to find out the program
   --the user is connecting with.
    CURSOR user_prog IS
    SELECT program,username FROM v$session
    WHERE audsid=sys_context('USERENV','SESSIONID');
   --Assign the cursor to a PL/SQL record.
   user_rec user_prog%ROWTYPE;
   BEGIN
   OPEN user_prog;
   FETCH user_prog INTO user_rec;
   IF user_rec.program IN ('TOAD.exe', 'toad.exe','Toad.exe','TOAD.EXE','SQL Developer') and user_rec.username in ('APPS')
   THEN
   RAISE_APPLICATION_ERROR(-20001, 'You are not authorised to use **** as per policy in prod ');
END IF;
 CLOSE user_prog;
 END;
/

Profile options changed recently for R12

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;

OAM 11gR2 and Microsoft AD with WNA