DBA Heaven

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'));

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
  ;

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 ?

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.

::|| 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.

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

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.

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.

Funny Linux Commands

% 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