DBA Heaven

2024-06-18T20:36:00.000+05:30

SPACE MANAGEMENT ::

1. Table space usuage:

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

SELECT tablespaces.tablespace_name,

allocated.total_alloc allocated_mb,

ROUND(allocated.total_alloc - free.total_free,2) total_used_mb,

ROUND (free.total_free, 2) free_mb,

ROUND (((allocated.total_alloc - free.total_free) / allocated.total_alloc) * 100, 2)

AS actual_pct_used,

total_extend_possible / 1024 / 1024 AS total_autoextend_possible_mb,

ROUND (((allocated.total_alloc - free.total_free) / (allocated.total_alloc +(total_extend_possible / 1024 / 1024 ))) * 100, 2)

AS extended_pct_used,

DB.instance_name,

DB.host_name,

to_char(sysdate,'dd-Mon-yyyy HH:mi am') Run_Date

FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_free

FROM dba_free_space

GROUP BY tablespace_name) free,

( SELECT tablespace_name,

SUM (bytes) / 1024 / 1024 total_alloc,

SUM (GREATEST (bytes, maxbytes) - bytes) total_extend_possible

FROM dba_data_files

GROUP BY tablespace_name) allocated,

(SELECT tablespace_name

FROM dba_tablespaces

WHERE CONTENTS NOT IN ('TEMPORARY')) tablespaces,

( select instance_name, host_name

from v\$instance) DB

WHERE allocated.tablespace_name(+) = tablespaces.tablespace_name

AND free.tablespace_name(+) = tablespaces.tablespace_name

ORDER BY 5 DESC;

select

a.tsdf tblspace,

b.used_space "Used Space(MB)",

nvl(a.file_space,0) "allocated size(MB)",

a.extn_space as "maximum allowable (MB)",

(a.extn_space-b.used_space) as "effective free(MB)",

round((((a.extn_space-b.used_space)/a.extn_space)*100),2) as "%FREE"

from

(select tablespace_name tsdf,sum(bytes)/1048576 file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extn_space from dba_data_files group by tablespace_name) a,

(select tablespace_name tsfs,(bytes/1048576) used_space from sm$ts_used) b

where

a.tsdf = b.tsfs (+) and

a.tsdf like upper('%%')

and round((((a.extn_space-b.used_space)/a.extn_space)*100),2) < 10

order by a.tsdf;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

purge dba_recyclebin;

purge recyclebin;

select round(sum(bytes/1024/1024)) MB, segment_name from dba_segments group by segment_name order by 1 desc;

2. To check the tables size

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

select substr(OWNER,1,15) "OWNER",

substr(segment_NAME,1,30) "NAME",

substr(segment_type,1,12)"TYPE",

bytes "BYTES",

extents "EXTENTS"

from sys.dba_segments where

segment_type in ('INDEX','TABLE') and

bytes > 100000000 and owner not in ('SYS') order by bytes desc;

Find Tables with it's Size::

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

SELECT a.owner,

b.segment_name,

b.segment_type,

a.created,

a.last_ddl_time,

b.bytes / 1024 / 1024

FROM dba_objects a, dba_segments b

WHERE b.segment_type = 'TABLE'

AND a.owner = 'OTSLGWEB'

AND b.segment_name LIKE 'CMD_%' (---provide table name)

and b.segment_name=a.object_name

3. Find Undo Tablespace size::

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

select

( select sum(bytes)/1024/1024 from dba_data_files

where tablespace_name like 'APPS_UND%' ) allocated,

( select sum(bytes)/1024/1024 from dba_free_space

where tablespace_name like 'APPS_UND%') free,

( select sum(bytes)/1024/1024 from dba_undo_extents

where tablespace_name like 'APPS_UND%') USed

from dual

Find who is using a UNDO or ROLL BACK segment::

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

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, 'None') orauser,

s.program,

r.name undoseg,

t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

FROM sys.v_$rollname r,

sys.v_$session s,

sys.v_$transaction t,

sys.v_$parameter x

WHERE s.taddr = t.addr

AND r.usn = t.xidusn(+)

AND x.name = 'db_block_size'

4. Find Free space in Temp Tablespace ::

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

select * from v$temp_space_header;

5. FRA Usage::

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

set lines 120

break on report

compute sum of percent_space_used on report

compute sum of percent_space_reclaimable on report

select file_type

, percent_space_used

, percent_space_reclaimable

, number_of_files

, con_id

from v$recovery_area_usage

order by 1

col name format a7

clear breaks

clear computes

select name

, round(space_limit / 1024 / 1024) size_mb

, round(space_used / 1024 / 1024) used_mb

, decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used

from v$recovery_file_dest

order by name

select

name,

floor(space_limit / 1024 / 1024) "Size MB",

ceil(space_used / 1024 / 1024) "Used MB"

from v$recovery_file_dest;

col name format a7

clear breaks

clear computes

select name

, round(space_limit / 1024 / 1024) space_limit_mb

, round(space_used / 1024 / 1024) space_used_mb

, percent_space_used

, percent_space_reclaimable

, percent_space_not_reclaimable

from v$recovery_file_dest

, ( select sum(percent_space_reclaimable) percent_space_reclaimable

, sum(percent_space_used) percent_space_used

, sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable

from v$recovery_area_usage)

order by name

6. Datafile Resize script generator::

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

select 'alter database datafile '''||file_name||''' resize

' ||ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0

-- and tablespace_name= '&Tablespace_Name'

;

7. Top 10 Tables by row count::

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

select * from (

select owner, table_name, num_rows from dba_tables where

-- owner='FINDUR_DB' and

num_rows is not null order by 3 desc)

where rownum<=10 ;

8. LOBS and Table Segments:

--------------------------

set lines 500 pages 500

col column_name format a30

col segment_name format a30

SELECT * FROM (

SELECT L.TABLE_NAME, L.COLUMN_NAME, S.SEGMENT_NAME, S.BYTES/1024/1024/1024 AS "SIZE_GB", L.TABLESPACE_NAME

FROM DBA_LOBS L, DBA_SEGMENTS S

WHERE S.SEGMENT_NAME=L.SEGMENT_NAME AND L.OWNER='FINDUR_DB'

ORDER BY S.BYTES DESC )

WHERE ROWNUM<=10 ;

9. List tablesace max_size, allocated_size, utilized and pct used

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

set linesize 132 tab off trimspool on

set pagesize 105

set pause off

set echo off

set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00

column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00

column "USED (MB)" format 9,999,990.00

column "FREE (MB)" format 9,999,990.00

column "% USED" format 990.00

select a.tablespace_name,

a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",

a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",

nvl(b.tot_used,0)/(1024*1024) "USED (MB)",

(nvl(b.tot_used,0)/a.physical_bytes)*100 "% USED"

from ( select tablespace_name,

sum(bytes) physical_bytes,

sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc

from dba_data_files

group by tablespace_name ) a,

( select tablespace_name, sum(bytes) tot_used

from dba_segments

group by tablespace_name ) b

where a.tablespace_name = b.tablespace_name (+)

--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10

and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)

and a.tablespace_name not like 'UNDO%'

order by 1 ;

10. Code to see HWM of DBF and resize them to HWM

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

--NOTE: Run the below three select statement one by one --

set verify off

set pages 1000

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

--To Check Database block size:--

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

-- To check how much space can be reclaimed --

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

--Script to reclaim unused space from the datafiles of respective tablespace--

set pages 0

set lines 300

column cmd format a300 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

-- this will give recommendations for space mgmt

SELECT

'Segment Advice --------------------------'|| chr(10) ||

'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||

'SEGMENT_OWNER : ' || segment_owner || chr(10) ||

'SEGMENT_NAME : ' || segment_name || chr(10) ||

'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||

'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||

'RECOMMENDATIONS : ' || recommendations || chr(10) ||

'SOLUTION 1 : ' || c1 || chr(10) ||

'SOLUTION 2 : ' || c2 || chr(10) ||

'SOLUTION 3 : ' || c3 Advice

FROM

TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

2024-06-18T20:05:00.001+05:30

Thanks to oracleappsdna.com

/*********************************************************
*PURPOSE: To find out profile option Values              *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SELECT DISTINCT POT.PROFILE_OPTION_NAME "PROFILE_CODE" 
  , POT.USER_PROFILE_OPTION_NAME "PROFILE_NAME" 
       , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) "PF_VALUE"
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , 10005, 'Server'
             , 10006, 'Organization'
			 , a.level_id
              ) "LEVEL_IDENTIFIER"
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, D.USER_NAME
             , 10005, F.HOST || '.' || F.DOMAIN
             , 10006, g.name
             , '-'
              ) "LEVEL_NAME"
 
FROM fnd_application_tl e ,
  fnd_user d ,
  fnd_responsibility_tl c ,
  fnd_profile_option_values a ,
  fnd_profile_options b ,
  fnd_profile_options_tl pot ,
  fnd_nodes f ,
  hr_all_organization_units g
WHERE 1=1
AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id        = a.application_id(+)
AND b.profile_option_id     = a.profile_option_id(+)
AND a.level_value           = c.responsibility_id(+)
AND a.level_value           = d.user_id(+)
AND a.level_value           = e.application_id(+)
AND a.level_value           = f.node_id(+)
AND a.level_value           = g.organization_id(+)
AND pot.language            ='US'
ORDER BY PROFILE_NAME ,
  LEVEL_IDENTIFIER ,
  LEVEL_NAME ,
  PF_VALUE
  ;

2022-12-27T02:10:00.001+05:30

Find below few useful scripts for Performance Management::

# Important MOS notes for TRACING:

There are several ways to trace a particular session.

REFER : General SQL_TRACE / 10046 trace Gathering Examples (Note 1274511.1)

REFER : How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Note 376442.1)

REFER : How To Use SQL Trace And TKPROF For Performance Issues with EBusiness Suite (Note 980711.1)

# sqlplus settings

set linesize 1000

set pagesize 600

set time on

set timing on

define _editor=vi

set sqlprompt "&_user@&_CONNECT_IDENTIFIER> "

# Increase PGA

ALTER SYSTEM SET pga_aggregate_target='4G' SCOPE=BOTH;

# To create Snapshot

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

# sessions with the highest time for a certain wait::

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

# Sessions with highest CPU consumption::

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;

# Sessions with highest DB Time usage::

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module,

st.value/100 as "DB Time(sec)", stcpu.value/100 as "CPU Time (sec)",

round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu,

v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0;

# Find Hidden Parameters::

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

SELECT

a.ksppinm "Parameter",

a.ksppdesc "Description",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value"

FROM

x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE

a.indx = b.indx

AND

a.indx = c.indx

AND

a.ksppinm LIKE '/_opt%' escape '/' ;

# Compile schema::

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

EXEC UTL_RECOMP.recomp_serial('&schema_name');

select

'ALTER ' || OBJECT_TYPE || ' ' ||

OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from

dba_objects

where

status = 'INVALID'

and

object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','SYNONYM') ;

## Find Type of SQL from v$sqlarea ::

select sql_fulltext,executions,loads,first_load_time,rows_processed,

optimizer_mode,parsing_schema_name,module,

DECODE(command_type,

2, 'INSERT',

3, 'SELECT',

6, 'UPDATE',

7, 'DELETE',

26, 'LOCK',

42, 'DDL',

44, 'COMMIT',

47, 'PL/SQL BLOCK',

command_type) CommandType

from v$sqlarea

where

command_type != 3

and upper(sql_fulltext) like '%IE_SE_EXPENSES%'

order by first_load_time desc

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

PL/SQL block to increase CPU usage . For testing only.

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

DECLARE

L_n NUMBER;

BEGIN

WHILE (TRUE)

LOOP

L_ n:= dbms_random.random();

END LOOP;

END;

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

This code above will burn all CPU. Use it wisely.

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

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

Script to insert 1M rows into a table:

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

CREATE TABLE test_table TABLESPACE USERS AS

( SELECT LEVEL id,

SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) date_value,

DBMS_RANDOM.string('A', 20) text_value

FROM dual

CONNECT BY LEVEL <= 1000000);

Ever Hit with ORA-46981 in Oracle EBS Environment ?

2022-08-24T09:03:00.003+05:30

Recently when I was tasked to do a cloning of my production Oracle Apps instance to a target single-node server,

I hit a dead-end with the below error message::

SYS@CDBUAT> conn apps@UATEBS

Enter password:

ERROR:

ORA-46981: Access to service from <DB-Host-IP> was denied.

CDBUAT is my CDB and UATEBS is my PDB.

Source instance is 2 node RAC DB with 2 node application tier servers.

Target server is single-node, hosting DB and Application services together.

DB was restored using disk based snapshot backup / restore tools or RMAN.

Believe me, there were no hits in metalink oracle support for this error. Un-expected !!!. No-one faced this error?

The db service is up. Even after several restart, the error remained the same.

I tried to connect to db by setting 10.1.2 oracle home environment, but error remained the same.

Note that, this is a single node instance, that is, both DB and Application tier are at same node.

Whenever I end up with a dead-end, I would always recall the correct procedure for the activity and compare with the procedures, which I have done.

This is one of my humble suggestions. This has helped me a lot of times.

One small step which I missed to perform is,

"Cleaning up the fnd_nodes" table.

This is not mentioned in the standard Cloning document available in MOS.

Might have been mentioned at the standard cloning procedure document, but not at Doc: 2560690.1 .

As I doubted, the fnd_nodes table still holding the node_names of the source host names along with current/target hostname.

I ran the exec fnd_nodes.setup_clean ; commit ;

Ran the adcfgclone.pl with dbTechStack.

Created TNS files.

Restarted the listener.

Then, voila, the magic happened.

Able to login to PDB as apps user without any hindrance.

2021-05-28T20:26:00.000+05:30

::|| RMAN COLD BACKUP AND RESTORE ||::

I'm going to show, how to execute cold backup using rman and restore the DB using cold backup.

Note:

1. This exercise is done on 11.2.0.2 database with archivelog enabled.

2. FRA need to be configured.

3. Controlfile auto backup must be enabled.

Quick Question: How to ensure all data are restored ?

Answer:

Before executing the cold backup, let me create a dummy table and insert a marker transaction on it. After restore, this marker transaction should be there.

Let's start:-

Step 1: Create a dummy table and insert a marker transaction.

Step 2: Restart the db to mount mode.

Step 3: Take cold backup using RMAN. Ensure controlfile autobackup is enabled.

With this step, the RMAN Cold Backup activity is completed.

Quick Question: whey there's an error message at the end of RMAN log ?

Now, I will start with restoring this cold backup and ensure it is working fine. Remember to verify the marker transaction at the end of restore.

For this restoration exercise, I'm going to remove control, data and log files from my file system and then restore with the cold backup.

Step 4: Restart the database from mount mode to nomount mode.

Step 5: Start restore of controlfiles.

Step 6: Mount the Database.

Step 7: Restore the cold backup.

Step 8: Open the DB with resetlogs and confirm marker transaction is present.

Feedbacks, updates, advises are welcome.

2020-11-05T06:53:00.000+05:30

Some times it will be very hard to List down what are the Incident rules we defined in our OEM Application. Particularly, during IT Audit, we may need to provide the list of Incident Rulesets and details about individual rules.

Copy and paste Incident Rulesets/Rules from OEM 12c is not possible.

OEM 13c has an export/import option, but that will give you an xml file. You need to run the show by converting it to your favorite document.

This script will come in handy to get all details about OEM Incident rules.

SELECT

FROM

(

SELECT

r.RULESET_ID,

r.RULE_ID,

r.RULE_NAME,

r.RULE_NAME_NLSID,

r.CREATED_BY,

r.DESCRIPTION,

r.DESCRIPTION_NLSID,

r.RESOURCE_BUNDLE,

r.RULE_TYPE,

r.RULE_ORDER,

r.IS_REFERED_ACTION,

r.REFERED_RULE_ID,

r.IS_ENABLED,

r.IS_BROKEN,

r.CREATED_DATE,

r.LAST_UPDATED_BY,

r.LAST_UPDATED_DATE,

r.RL_EXPRESSION,

r.Event_Class,

r.PRIV_MODE,

r.Display_Order,

ru.Subscribed_Users

FROM

(

SELECT

EmRulesEO.RULESET_ID,

EmRulesEO.RULE_ID,

EmRulesEO.RULE_NAME,

EmRulesEO.RULE_NAME_NLSID,

EmRulesEO.CREATED_BY,

EmRulesEO.DESCRIPTION,

EmRulesEO.DESCRIPTION_NLSID,

EmRulesEO.RESOURCE_BUNDLE,

EmRulesEO.RULE_TYPE,

EmRulesEO.RULE_ORDER,

EmRulesEO.IS_REFERED_ACTION,

EmRulesEO.REFERED_RULE_ID,

EmRulesEO.IS_ENABLED,

EmRulesEO.IS_BROKEN,

EmRulesEO.CREATION_DATE AS CREATED_DATE,

EmRulesEO.LAST_UPDATED_BY,

EmRulesEO.LAST_UPDATED_DATE,

EmRulesEO.RL_EXPRESSION,

EmrulesEO.Event_Class,

EmrulesEO.PRIV_MODE,

To_Number(EmRuleSetsEO.ruleset_order + (row_number() over (partition

BY EmRulesEO.RULESET_ID order by EmRulesEO.RULE_ORDER))/1000) AS

Display_Order

FROM

Em_Rules Emruleseo,

(

SELECT

RULESET_ID,

rownum AS RULESET_ORDER

FROM

(

SELECT

RULESET_ID

FROM

EM_RULE_SETS

WHERE

IS_VISIBLE=1

ORDER BY

Ruleset_Order

)

EmRuleSetsEO

WHERE

Emrulesetseo.Ruleset_Id = Emruleseo.Ruleset_Id

ORDER BY

ruleset_order ASC,

RULE_ORDER ASC

)

LEFT OUTER JOIN

(

SELECT

acts.rule_id AS rule_id,

em_incident_rules.table_to_string(CAST(COLLECT(notif.recipient) AS SMP_EMD_LONG_STRING_ARRAY)) AS Subscribed_Users

FROM

sysman.em_rule_cond_actions acts,

sysman.em_rule_basic_notifications notif

WHERE

acts.COND_ACTION_ID = notif.COND_ACTION_ID

GROUP BY

rule_id

)

r.rule_id = ru.rule_id

)

QRSLT

WHERE

RULESET_ID = 7

ORDER BY

RULE_ORDER ASC

2017-10-14T22:48:00.003+05:30

After a long time.....

I'm decided to blog .

Here's my original script to find who are all owning System administrator responsibility.

select DISTINCT(PAPF.PERSON_ID), fu.user_name, fu.end_date, frt.responsibility_name,
PAPF.FULL_NAME,
furga.start_date "RESPONSIBILITY_START_DATE",
furga.end_date "RESPONSIBILITY_END_DATE"
from FND_USER_RESP_GROUPS_ALL furga,
fnd_user fu,
fnd_responsibility_tl frt,
PER_ALL_PEOPLE_F PAPF
where
FU.EMPLOYEE_ID=PAPF.PERSON_ID
--fu.employee_id IN (SELECT DISTINCT(PERSON_ID) FROM PER_ALL_PEOPLE_F )
AND fu.user_id=furga.user_id
and frt.responsibility_id=furga.responsibility_id
and furga.responsibility_id=20420
AND FRT.LANGUAGE='US'
-- GROUP BY FU.EMPLOYEE_ID
ORDER BY 1 ;

I've added DISTINCT(PAPF.PERSON_ID) to mitigate the duplicate entries in the result. Since PER_ALL_PEOPLE_F table sometimes contain duplicate entry for a given person_id, when joining with other tables, we'll get duplicate entries.

2017-01-06T19:10:00.002+05:30

A nice story about RMAN.

The story begins, when one of my associate DBAs did an RMAN restoration & recovery 2 days back.

Here, I need to mention the exact date, time and the backup schedules also.

Similar to every organizations' backup policy, we too take full backup (FB) on saturday night and Incremental backup (IB) on other days.

Now the main story:

The DBA restores the Prod DB backup on DEV DB and recovers it such a way, that DEV mush have data upto 30th Dec 2016. He started his work on 2nd Jan 2017, after the day's IB.

The command executed was:

rman auxiliary =/ << EOF
run {
set until time 'TO_DATE('2016-12-30 22:00:00', 'YYYY-MM-DD HH24:MI:SS')';
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database to 'TEST'
BACKUP LOCATION '' NOFILENAMECHECK
pfile=$ORACLE_HOME/dbs/initDEV.ora;
}
EXIT;
EOF

Have you found any problems or syntax errors on this script ?
If yes, then Hats off to You. You are a seasoned DBA.

The restoration went fine except few errors messages.

RMAN-01009: syntax error: found "integer": expecting one of: "newline, ;"
RMAN-01009: syntax error: found "auxiliary": expecting one of: "channel"

The DBA thought there was an issue with "auxiliary channel" definition. Since the restoration and later recovery are went fine without any warnings/errors, he let the process continue.

The work completed and he returned the DB to end-user.

Next morning, he received a customer complaint saying, there are latest data in the DB, which are older than 30th Dec 2016.

Now, comes the another problem.

There's no log file saved for the RMAN recovery op, to verify and prove everything that happen is right.

The whole team were breaking their head to see what went wrong.

Good that the restored DB is DEV. We had a day's time to rectify the issue.

But how could rman can restore till 2nd Jan '17 , while the command set the restoration point till "30th Dec 2016, 2200hrs" ?

I'll post the root cause on tomorrow's post.

2015-02-27T10:20:00.002+05:30

Funny Linux Commands

2015-02-08T14:44:00.003+05:30

% cat “food in cans”
cat: can’t open food in cans

% nice man woman
No manual entry for woman.

% “How would you rate Quayle’s incompetence?
Unmatched “.

% Unmatched “.
Unmatched “.

% [Where is Jimmy Hoffa?
Missing ].

% ^How did the sex change operation go?^
Modifier failed.

% make love
Make: Don’t know how to make love. Stop.

% sleep with me
bad character

% got a light?
No match.

% man: why did you get a divorce?
man:: Too many arguments.

% !:say, what is saccharine?
Bad substitute.

$ drink matter
matter: cannot create

Thanks to : quora.com

Completed My First RAC project.

2012-10-16T16:49:00.000+05:30

My birthday on this year is an unforgettable day!!!.

Yes. I've achieved one of my dreams today.

For the first time on my career, I've configured an Oracle 11gR2 RAC on an E-BIZ environment.

****************************************************
[oraprod@ec-pdb01 bin]$ ./rconfig /u01/rdbms/product/11.2.0/dbhome_1/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml

Converting Database "RAC1" to Cluster Database. Target Oracle Home: /u01/rdbms/product/11.2.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Flash Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database

Operation Succeeded

/u01/rdbms/product/11.2.0/dbhome_1

***************************************************************

I would like to say "Thank You" to Kishoreji (akr14feb) for making me an Apps DBA ,
SriHari (Guruji) for Guiding me on difficult times,
Chandru for providing me a turning-point on life and all my friends.
A special Thanks to my wife Vasanthakala for her support.
Without them I would be a dust.

Reset VM Manager Portal "admin" user password

2012-07-16T14:37:00.001+05:30

Guys,

This is how I reset the 'admin' user password of my Oracle VM Manager Installation.

1. There's a "register" link under the password field in Login page. Click that link and register a new user with simple password like 123456.

2. Login as sys or OVS user using sqlplus and connect to the DB.

3. select account_name, password from ovs.ovs_user where account_name='';

4. Copy the password field output.

5. update ovs.ovs_user set password='' where account_name='';

6. Commit and voila, you can now login with admin username and password will be 123456;

Isn't it Simple?

Difference Between Oracle Standard Edtion and Standard Edtion One (SE and SE1)

2012-02-08T17:20:00.000+05:30

Recently, I came across difference between Oracle Editions in a particular version.

The differences are enormous.

Also, there's a new edition apart from standard and Enterprises Editions. It is Standard Edition One (SE1).

Following table lists the Differences: (Referred with Oracle docs)

Feature/Option	SE1	SE	EE	Notes
High Availability
Oracle Fail Safe	Y	Y	Y	Windows only
Oracle Data Guard—Redo Apply	N	N	Y
Oracle Data Guard—SQL Apply	N	N	Y
Rolling Upgrades—Patch Set, Database, and Operating System	N	N	Y
Online index rebuild	N	N	Y
Online index-organized table organization	N	N	Y	`ALTER` `TABLE` ... `MOVE` `ONLINE` operations
Online table redefinition	N	N	Y	Using the `DBMS_REDEFINITION` package
Duplexed backup sets	N	N	Y
Block change tracking for fast incremental backup	N	N	Y
Unused block compression in backups	N	N	Y
Block-level media recovery	N	N	Y
Parallel backup and recovery	N	N	Y
Tablespace point-in-time recovery	N	N	Y
Trial recovery	N	N	Y
Fast-start fault recovery	N	N	Y
Flashback Table	N	N	Y
Flashback Database	N	N	Y
Flashback Transaction Query	N	N	Y
Scalability
Oracle Real Application Clusters	N	Y	Y	Extra cost with EE, included with SE
Automatic Workload Management	N	Y	Y	Requires RAC
Performance
In-Memory Database Cache	N	N	Y	Extra cost option
Security
Advanced Security Option	N	N	Y	Extra cost option
Oracle Label Security	N	N	Y	Extra cost option
Virtual Private Database	N	N	Y
Fine-grained auditing	N	N	Y
Development Platform
SQLJ	Y	Y	Y	Requires Oracle Programmer
Oracle Developer Tools for Visual Studio .NET	Y	Y	Y	Windows only
Microsoft Distributed Transaction Coordinator support	Y	Y	Y	Windows only
Active Directory integration	Y	Y	Y	Windows only
Native .NET Data Provider—ODP.NET	Y	Y	Y	Windows only
.NET Stored Procedures	Y	Y	Y	Windows only
Manageability
Oracle Change Management Pack	N	N	Y	Extra cost option
Oracle Configuration Management Pack	N	N	Y	Extra cost option
Oracle Diagnostic Pack	N	N	Y	Extra cost option
Oracle Tuning Pack	N	N	Y	Extra cost option, also requires the Diagnostic Pack
Oracle Provisioning and Patch Automation Pack	N	N	Y	Extra cost option
Database Resource Manager	N	N	Y
VLDB, Data Warehousing, Business Intelligence
Oracle Partitioning	N	N	Y	Extra cost option
Oracle OLAP	N	N	Y	Extra cost option
Oracle Data Mining	N	N	Y	Extra cost option
Data Compression	N	N	Y
Bitmapped index, bitmapped join index, and bitmap plan conversions	N	N	Y
Parallel query/DML	N	N	Y
Parallel statistics gathering	N	N	Y
Parallel index build/scans	N	N	Y
Parallel Data Pump Export/Import	N	N	Y
Transportable tablespaces, including cross-platform	N	N	Y	Import of transportable tablespaces supported into SE, SE1, and EE
Summary management—Materialized View Query Rewrite	N	N	Y
Asynchronous Change Data Capture	N	N	Y
Integration
Basic Replication	Y	Y	Y	SE1/SE: read-only, updateable materialized view
Advanced Replication	N	N	Y	Multi-master replication
Oracle Streams	N	N	Y	SE1/SE: apply only
Messaging Gateway	N	N	Y
Transparent Gateways	Y	Y	Y	Extra cost option
Networking
Oracle Connection Manager	N	N	Y
Infiniband Support	N	N	Y
Content Management
Oracle Spatial	N	N	Y	Extra cost option

The Only (of-course two) differences between SE and SE1 are support for RAC and AWR feature.

Your updates are welcome.

Sundar K

ksh not found while pre/post cloning

2012-02-07T18:02:00.000+05:30

I usually receive the below mentioned error while Pre/Post Installation Check.

System Utilities Check

These utilities need to be accessible on your system in order to do administrative tasks.
command : /usr/Stage11i/startCD/Disk1/rapidwiz/bin/adchkutl.sh
Checking for make... found - /usr/bin/make
Checking for ld... found - /usr/bin/ld
Checking for cc... found - /usr/bin/cc
Checking for ar... found - /usr/bin/ar
Checking for ksh...
Unable to find PD KSH version.
Unable to locate all utilities with system path.
PATH = /usr/bin:/usr/Stage11i/startCD/Disk1/rapidwiz/unzip/Linux:/usr/bin:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin

The Solution for this issue is:
export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'

Even though you have performed all your pre-reqs.

Anyone knows what this '@(#)PD KSH v5.2.14 99/07/13.2' mean to?

Food for Thought

2011-12-06T11:41:00.000+05:30

No candle loses its light while lighting another candle'.

So never stop sharing and helping others because it makes our

life more meaningful..

Swap space Calculation

2011-11-14T13:11:00.000+05:30

Refer MOS Doc: 286388.1

There are two swap memory allocation algorithms, used widely.
1. Eager Allocation Model
This algorithm preallocates swap at the time of process creation.
2. Lazy Allocation Model
In this swap is allocated on-demand. (Called Paging).

In Eager model,
Minimum swap required = Total virtual memory of all the processes on the Machine

In Lazy Model:
Minimum swap required = Total virtual memory of all processes - Size of RAM

In both models, the Virtual Memory is calculated using, the following sql (in peak time)

select (value/1024/1024)+(select (sum(st.value )/1024/1024)
from v$sesstat st , v$statname sn
where st.statistic# = sn.statistic#
and sn.name like '%ga memory') "Virtual Memory"
from v$parameter
where name='sga_max_size'
;

For 32-bit machines swap can be
Minimum of 400Mb or 2* size of RAM which ever is greater.
OR
If physical RAM < 2GB , swap = 2* Physical RAM
If Physical RAM >2B & <4GB swap = Size of Physical RAM
If Physical RAM > 4GB swap = 4GB

Difference Between GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP

2011-11-12T14:17:00.000+05:30

Last night I was working through some of the ASM Global Dynamic Views.

I was wondering, why both GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP are having same columns and showing the same data.

I got a nice explanation from www.orafaq.com. (Link: http://www.orafaq.com/wiki/ASM_FAQ)

GV$ASM_DISKGROUP_STAT fetches data from cache while
GV$ASM_DISKGROUP fetches data from disk headers.

The former view (%_stat) operates faster than the later.

Moreover, they suggested to change the references of GV$ASM_DISKGROUP to GV$ASM_DISKGROUP_STAT in $ORACLE_HOME/bin/asmcmdcore, if someone feels that, asmcmd command line utility is slow.
There are 4 references of GV$ASM_DISKGROUP in "asmcmdcore" file.

(These findings are applicable to oracle 10.2.0.3 EE. Readers kindly check the documentation of their Db version)

Stats Gathering script

2011-11-12T13:34:00.000+05:30

Here's a nice Statistics gathering script.

spool '/tmp/gather.spl';
set heading off;
select '################################' from dual;
select 'Start of Gathering Schema Stats...' from dual;

select sysdate from dual;

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('APPS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', casca
de => TRUE);

exec dbms_stats.gather_schema_stats('APPLSYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', ca
scade => TRUE);

exec dbms_stats.gather_schema_stats('AP',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

exec dbms_stats.gather_schema_stats('AR',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

exec dbms_stats.gather_schema_stats('INV',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('GL',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

select sysdate from dual;
select null from dual;
select 'End of Stats gathering!!!' from dual;
select '################################' from dual;
spool off;

Thanks to Nazim.

Status of Conc Request with last update date

2011-11-08T16:38:00.000+05:30

Below given sql query provides the status, phase, user submitted, last update time with Concurrent request name.

select r.concurrent_program_id,
r.request_id,
r.last_update_date,
r.last_updated_by,
u.user_name,r.phase_code,
(case r.phase_code
when 'I' then 'Inactive'
when 'P' then 'Pending'
when 'R' then 'Running'
when 'C' then 'Completed'
else 'N/a'
end) Phase,r.status_code,
( case r.status_code
when 'A' then 'Waiting'
when 'B' then 'Resuming'
when 'C' then 'Normal'
when 'D' then 'Cancelled'
when 'E' then 'Error'
when 'F' then 'Scheduled'
when 'G' then 'Warning'
when 'H' then 'On Hold'
when 'I' then 'Normal'
when 'M' then 'No Manager'
when 'Q' then 'Standby'
when 'R' then 'Normal'
when 'S' then 'Suspended'
when 'T' then 'Terminating'
when 'U' then 'Disabled'
when 'W' then 'Paused'
when 'X' then 'Terminated'
when 'Z' then 'Waiting'
else 'N/a'
end
) Status,
t.user_concurrent_program_name
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl t,
fnd_user u
where
-- status_code='I' and
r.concurrent_program_id=t.concurrent_program_id
and
r.last_updated_by=u.user_id
order by r.last_update_date;

The status and phase code seems mis-matching with the application.
Anybody, knows the correct codes ?

Today's Count of Concurrent Requests

2011-11-04T20:21:00.002+05:30

A) This query provides the concurrent requests submitted since 12AM, today

select count(*) from apps.fnd_concurrent_requests
where request_date > sysdate - (1/24*(select extract(hour from current_timestamp)+4
/*since my timezone is GST+4 */ from dual)) order by request_date ;

This can be fine tuned for count of completed requests.

B) This query provides the count of conc requests submitted in last one hour.

select count(request_date) from apps.fnd_concurrent_requests where request_date > sysdate -(1/24)

I created a job that uses this query to insert count and sysdate values into an "xyz" table on scott schema.
Later, I used this "xyz" table to create a graph (using ODBC) on MS Excel spreadsheet, that represents the Request submitting pattern of my organization.
The funny part is, the graph dives deep at coffee breaks and lunch break.

Your feedback are welcome.
Sundar K

Query to find Concurrent Request completion time

2011-10-25T19:41:00.001+05:30

I use this query to find out the

a) time taken to complete the request in Minutes value.

b) user name

c) request_id

d) Concurrent program name

e) start date/time

f) end date/time

select u.user_name,r.request_id,p.user_concurrent_program_name,r.actual_start_date,r.actual_completion_date,r.completion_text,
(r.actual_completion_date-r.actual_start_date)*1440 "Minutes" from
fnd_concurrent_requests r,
fnd_user u,
fnd_concurrent_programs_tl p
where u.user_id=r.requested_by
and p.concurrent_program_id=r.concurrent_program_id
and phase_code='C' /* Completed */
and request_id > 13246690 /* limit the number of rows for quick processing */
and actual_completion_date is not null
and actual_start_date is not null
and actual_start_date > sysdate-1 /* requests completed in last 24 hrs */
order by "Minutes" desc ;

Hope this will help you.
Sundar k
Sr. Apps DBA

Nice Article about creating Standby using Real Time Apply

2011-06-22T16:53:00.000+05:30

This is a nice article about Creating Standby by using Real Time Apply of archivelogs.

http://kb.dbatoolz.com/ex/pwpkg.dp?p_key=11&p_what=detail&p_sr_id=2693&p_sc_id=19&p_debug=&p_search=&p_suser=&p_sdate=#top

Are you in Comfort Zone ?

2011-06-06T16:56:00.000+05:30

“அந்த நாட்கள் எப்படி இருந்தது?” என்று கேட்டுப் பார்த்தால், “கருப்பாய் அடி வயிற்றை கவ்வுகிற பயமாய் இருந்தது” என்று சொல்லுவார்கள். வேலையிருக்குமா இருக்காதா, பாஸ் ஆவோமா மாட்டோமா என்ற நாட்கள் நமக்கும் நடந்திருக்கலாம், அப்போது நமக்கு எப்படி இருந்தது என்று கேட்டுப் பார்த்தாலும் இப்படித் தான் பதில் சொல்லுவோம். இதைத் தான், பெரும்பள்ளம் அல்லது Pit என்கிறார்கள்.

உங்கள் வேலையில் தினம் தினம் இம்மாதிரி தோன்றுகிறதா? ஆமாம் என்றால் நீங்கள் முன்னேறிக் கொண்டிருக்கிறீர்கள் என்று அர்த்தம். இம்மாதிரி அடிவயிற்று பயம் இல்லையென்றால் உங்களில் comfort zoneல் இருக்கிறீர்கள் என்று அர்த்தம். சிறந்த தலைமைப் பண்புள்ளவர்கள் மீண்டும் மீண்டும் இம்மாதிரி பெரும்பள்ளங்களைத் தேடிப்போய் விழுந்து, மீண்டும் மேலெழுந்து வருகிறார்கள். கேட்டால் சேலன்ஞ் என்கிறார்கள்.
..
..
கேள்விப்பட்டதுண்டா?

Usefull sql scripts

2011-04-17T16:55:00.002+05:30

1. Number of Responsibilities assigned to users. Order by count desc.
============================================
select b.user_name,count(a.responsibility_id)
from fnd_user_resp_groups_direct a,
    fnd_user b
    where a.end_date is null and
    a.user_id=b.user_id and
    b.end_date is null
    group by b.USER_name
    order by 2 desc    ;
============================================

2. Profile options changed in the last 3 days with user name, who changed that.

select
a.profile_option_id "ID",
a.profile_option_name "short_name",
c.user_profile_option_name "Full Name",
b.last_update_date "updated on",
b.last_updated_by "updated_by_ID",
d.user_name "Login_name"
from
fnd_profile_options a,
fnd_profile_option_values b,
fnd_profile_options_tl c,
fnd_user d
where
a.profile_option_id=b.profile_option_id
and
a.profile_option_name=c.profile_option_name
and
b.last_updated_by=d.user_id
and
b.last_update_date > (sysdate -3 )
order by 4 desc;

2011-02-07T08:00:00.003+05:30

Connecting to an Oracle DB using JDBC URL

There is a connection option, called JDBC_URL, available on widely used DB Management tools like TOAD, sqldeveloper, etc.

Most of the dbas prefer tns connections and they have less preference to connections using jdbc url.

Here, I will explain you, how to make a jdbc connection to oracle db using sqldeveloper.

Step 1: Click on the new connection.
Step 2: provide Connection name, username, password details.
Step 3: Select "advanced" on connection type.
Step 4: Copy the tns connection details from tnsnames.ora file (without connection name) and prepend "jdbc:oracle:thin:@" with the tns connection details.
Example:
jdbc:oracle:thin:@DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host-name)(PORT=1521))
(CONNECT_DATA=
(SID=DEV)
)
)

Step 5: Paste this block on the space provided for "Custome JDBC URL".

Test the connection and voila !!!