DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, June 30, 2016

OUI-67215: OPatch found the word "warning" in the stderr of the make command.

Opatch Warning During Patch Apply : "ld: warning: option -YP or -Q appears more than once, first setting taken


ERROR:
-------------
You may receive the following warning while applying the patch on Solaris platform

OUI-67215:
                            OPatch found the word "warning" in the stderr of the make command.
                            Please look at this stderr. You can re-run this make command.
                            Stderr output:
                            ld: warning: option -YP appears more than once, first setting taken
                            ld: warning: option -Q appears more than once, first setting taken



CAUSE:
-----------

Make warnings caused due to Options for ld command appears more than once.


SOLUTION:
----------------

 These warnings are safely ignorable.


Ref:Doc ID 1571550.1




Show_user_with_top_io

SELECT ses.sid, ses.serial#, ses.osuser, ses.process, sio.physical_reads
   FROM v$session ses, v$sess_io sio
  WHERE ses.sid  = sio.sid
    AND nvl(ses.username,'SYS') not in ('SYS', 'SYSTEM')
    AND sio.physical_reads = (SELECT MAX(physical_reads)
                                FROM v$session ses2, v$sess_io sio2
                               WHERE ses2.sid = sio2.sid
                                 AND ses2.username
                                  NOT IN ('SYSTEM', 'SYS'));

Show_statistics_of_connected_users

col osuser format a10 trunc heading "OSUSER AS"
col orauser format a10 trunc
col machine format a10 trunc
col sprogram format a15 trunc
col process format a20 trunc
col server format a3 trunc
col sess_id format 9999
col proc_id format a7
--
SELECT  s.osuser osuser,
        s.username orauser,
        s.machine machine,
        s.program sprogram,
        p.program process,
        s.sid sess_id,
        p.spid proc_id,
        s.logon_time,
        s.server server
FROM    v$session s,
        v$process p
WHERE   s.paddr = p.addr
AND     type != 'BACKGROUND'
AND     p.username is not null
ORDER BY 6
/
col osuser clear
col machine clear
col orauser clear
ttitle off

Rac_balance_delta

col instance_name format a20 heading "Instance|name"
col elapsed_seconds format 999,999 Heading "Elapsed|seconds"
col cpu_ms_ps format 999,999.99 heading "CPU ms|p.s."
col db_ms_ps format 999,999.99 heading "DB time|ms p.s."
set pages 1000
set lines 80
set echo on

WITH cluster_delta as (
    SELECT instance_name, start_timestamp,end_timestamp,
     round((end_timestamp-start_Timestamp)*24*3600,2) elapsed_seconds,
           SUM(CASE WHEN stat_name = 'DB CPU'
                    THEN VALUE/1000 END) cpu_ms,
            SUM(CASE WHEN stat_name = 'DB time'
                     THEN VALUE/1000 END) db_ms
    FROM table(opsg_pkg.service_stat_report())
    JOIN gv$instance using (inst_id)
   GROUP BY instance_name,start_timestamp,end_timestamp
)
SELECT instance_name, elapsed_seconds,
       ROUND(cpu_ms / elapsed_seconds, 2) cpu_ms_ps,
       ROUND(cpu_ms / elapsed_seconds, 2) db_ms_ps
FROM cluster_delta
ORDER BY instance_name; 

Logged on Users

SET linesize 200 PAGESIZE 1000 FEED OFF;
COLUMN "sid,serial#" FORMAT A12
COLUMN "pid" FORMAT 999
COLUMN "status" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A16
COLUMN "prg" FORMAT A30
--
SELECT
       ''''
    || s.sid
    || ','
    || s.serial#
    || ''';'                        "sid,serial#"
     , p.spid                        "pid"
     , s.status                      "status"
     , s.schemaname                  "schema"
     , s.osuser                      "osuser"
     , s.machine                     "box"
     , s.program                     "prg"
     , TO_CHAR(s.logon_time, 'DD.MM.YYYY HH24:MI') "logon_time"
  FROM
       v$session s,
       v$process p
WHERE s.paddr  = p.addr
ORDER BY s.username
/

Invalieds

spool show_summary_invalid_objects.lst
set pause off
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;

ttitle left 'Summary of invalid objects for User: ' sql.user -
skip 2

column object_type format a25 wrap heading 'Object|Type'
column status format a8 heading 'Status'

SELECT DISTINCT (object_type), status, COUNT(*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY owner, object_type, status;

Patch Error while appying 22502456 RDBMS Database Patch Set Update 11.2.0.4.160419

          Patch Error while appying 22502456  RDBMS Database Patch Set Update 11.2.0.4.160419

Error:
---------

The patch directory area must be a number.

ERROR: OPatch failed because of problems in patch area.

When you pop-up with following error while appying the RDBMS Database Patch Set Update 11.2.0.4.160419

Cause:
-------------

OPatch version may be lower to the patch area.

Cross-check with your database opatch utility version.


oratest@hostname # ./opatch version
OPatch version    : 11.2.0.3.6

OPatch succeeded.


Solution:
--------------
Please download latest opatch patch 6880880 for release "11.2.0.0.0" (description "OPatch 11.2").

apply it as below

cd $ORACLE_HOME
mv OPatch OPatch.bkp
unzip <download directory>/p6880880_<version>_<platform>.zip
cd OPatch
./opatch version

Include OPatch in PATH

export PATH=$ORACLE_HOME/OPatch:$PATH

and then reapply patch 22502456





oratest@hostname # ./opatch version
OPatch Version: 11.2.0.3.12

OPatch succeeded.


Now, the 22502456 patch completed successfully. 

Current Database Account

set termout off
set head off
set termout on

select 'User: '|| user || ' on database ' || global_name,
       '(Terminal='||USERENV('TERMINAL')||
       ', Session-Id='||USERENV('SESSIONID')||')'
from   global_name;

set head on feed on

Datafile Activity

SELECT d.name file_name,
d.blocks,
f.phywrts,
f.phyrds,
f.readtim,
f.writetim,
t.name tablespace_name,
f.phywrts/d.blocks wrts_per_block,
f.phyrds/d.blocks rds_per_block
FROM v$filestat f,
v$datafile d,
sys.ts$ t
WHERE f.file#=d.file#
AND d.blocks >0
AND t.ts#=d.ts#
UNION ALL
SELECT d.name file_name,
d.blocks,
f.phywrts,
f.phyrds,
f.readtim,
f.writetim,
t.name tablespace_name,
f.phywrts/d.blocks wrts_per_block,
f.phyrds/d.blocks rds_per_block
FROM v$tempstat f,
v$tempfile d,
sys.ts$ t
WHERE f.file#=d.file#
AND d.blocks >0
AND t.ts#=d.ts# ;

buffer_busy_sample



column wait format a15
column address noprint
column piece noprint
break on wait

select /*+ ordered */ distinct
  w.wait,
  t.address,
  t.piece,
  translate(t.sql_text, chr(13), ' ') sql_text
from
  ( select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
  )  w,
  sys.v_$session  s,
  sys.v_$sqltext  t
where
  s.sid = w.sid and
  t.address = s.sql_address
order by
  w.wait,
  t.address,
  t.piece
/




how to check the database health checkup

spool tuning_stats.txt

ttitle 'SYSTEM STATISTICS'

select 'LIBRARY CACHE STATISTICS:' from dual;

ttitle off

select 'PINS - # of times an item in the library cache was executed - '||
        sum(pins),
       'RELOADS - # of library cache misses on execution steps - '||
        sum (reloads),
       'RELOADS / PINS * 100 = '||round((sum(reloads) / sum(pins) *
100),2)||'%'
from    v$librarycache
/

prompt Increase memory until RELOADS is near 0 but watch out for
prompt Paging/swapping
prompt To increase library cache, increase SHARED_POOL_SIZE
prompt
prompt ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size.
prompt
prompt Library Cache Misses indicate that the Shared Pool is not big
prompt enough to hold the shared SQL area for all concurrently open cursors.
prompt If you have no Library Cache misses (PINS = 0), you may get a small
prompt increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which
prompt prevents ORACLE from deallocating a shared SQL area while an
prompt application
prompt cursor associated with it is open.
prompt
prompt For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user.
prompt
prompt------------------------------------------------------------------------

column xn1 format a50
column xn2 format a50
column xn3 format a50
column xv1 new_value xxv1 noprint
column xv2 new_value xxv2 noprint
column xv3 new_value xxv3 noprint
column d1  format a50
column d2  format a50

prompt HIT RATIO:
prompt
prompt Values Hit Ratio is calculated against:
prompt

select lpad(name,20,' ')||'  =  '||value xn1, value xv1
from   v$sysstat
where  name = 'db block gets'
/

select lpad(name,20,' ')||'  =  '||value xn2, value xv2
from   v$sysstat
where  name = 'consistent gets'
/

select lpad(name,20,' ')||'  =  '||value xn3, value xv3
from   v$sysstat b
where  name = 'physical reads'
/

set pages 60

select 'Logical reads = db block gets + consistent gets ',
        lpad ('Logical Reads = ',24,' ')||to_char(&xxv1+&xxv2) d1
from    dual
/

select 'Hit Ratio = (logical reads - physical reads) / logical reads',
        lpad('Hit Ratio = ',24,' ')||
        round( (((&xxv2+&xxv1) - &xxv3) / (&xxv2+&xxv1))*100,2 )||'%' d2
from    dual
/

prompt If the hit ratio is less than 60%-70%, increase the initialization
prompt parameter DB_BLOCK_BUFFERS.  ** NOTE:  Increasing this parameter will
prompt increase the SGA size.
prompt
prompt------------------------------------------------------------------------

col name format a30
col gets format 9,999,999,999,999
col waits format 9,999,999,999,999

prompt ROLLBACK CONTENTION STATISTICS:
prompt

prompt GETS - # of gets on the rollback segment header
prompt WAITS - # of waits for the rollback segment header

set head on;

select name, waits, gets
from   v$rollstat, v$rollname
where  v$rollstat.usn = v$rollname.usn
/

set head off

select 'The average of waits/gets is '||
   round((sum(waits) / sum(gets)) * 100,2)||'%'
From    v$rollstat
/

prompt
prompt If the ratio of waits to gets is more than 1% or 2%, consider
prompt creating more rollback segments
prompt
prompt Another way to gauge rollback contention is:
prompt

column xn1 format 9999999999
column xv1 new_value xxv1 noprint

set head on

select class, count
from   v$waitstat
where  class in ('system undo header', 'system undo block',
                 'undo header',        'undo block'          )
/

set head off

select 'Total requests = '||sum(count) xn1, sum(count) xv1
from    v$waitstat
/

select 'Contention for system undo header = '||
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from  v$waitstat
where   class = 'system undo header'
/

select 'Contention for system undo block = '||
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from    v$waitstat
where   class = 'system undo block'
/

select 'Contention for undo header = '||
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from    v$waitstat
where   class = 'undo header'
/

select 'Contention for undo block = '||
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
from    v$waitstat
where   class = 'undo block'
/

prompt
prompt If the percentage for an area is more than 1% or 2%, consider
prompt creating more rollback segments.  Note:  This value is usually very
prompt small
prompt and has been rounded to 4 places.
prompt
prompt------------------------------------------------------------------------

prompt REDO CONTENTION STATISTICS:
prompt
prompt The following shows how often user processes had to wait for space in
prompt the redo log buffer:

select name||' = '||value
from   v$sysstat
where  name = 'redo log space requests'
/

prompt
prompt This value should be near 0.  If this value increments consistently,
prompt processes have had to wait for space in the redo buffer.  If this
prompt condition exists over time, increase the size of LOG_BUFFER in the
prompt init.ora file in increments of 5% until the value nears 0.
prompt ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.
prompt
prompt -----------------------------------------------------------------------


col name format a15
col gets format 99999999999
col misses format 999999999999
col immediate_gets heading 'IMMED GETS' format 999999999999
col immediate_misses heading 'IMMED MISS' format 999999999999
col sleeps format 999999

prompt LATCH CONTENTION:
prompt
prompt GETS - # of successful willing-to-wait requests for a latch
prompt MISSES - # of times an initial willing-to-wait request was unsuccessful
prompt IMMEDIATE_GETS - # of successful immediate requests for each latch
prompt IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latch
prompt SLEEPS - # of times a process waited and requests a latch after an
prompt          initial willing-to-wait request
prompt
prompt If the latch requested with a willing-to-wait request is not
prompt available, the requesting process waits a short time and requests
prompt again.
prompt If the latch requested with an immediate request is not available,
prompt the requesting process does not wait, but continues processing
prompt

set head on

select name,          gets,              misses,
       immediate_gets,  immediate_misses,  sleeps
from   v$latch
where  name in ('redo allocation',  'redo copy')
/

set head off

select 'Ratio of MISSES to GETS: '||
        round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%'
from    v$latch
where   name in ('redo allocation',  'redo copy')
/

select 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
        round((sum(immediate_misses)/
       (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'
from    v$latch
where   name in ('redo allocation',  'redo copy')
/

prompt
prompt If either ratio exceeds 1%, performance will be affected.
prompt
prompt Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number of
prompt processes copying information on the redo allocation latch.
prompt
prompt Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contention
prompt for redo copy latches.

rem
rem This shows the library cache reloads
rem

set head on

prompt
prompt------------------------------------------------------------------------

prompt
prompt Look at gethitratio and pinhit ratio
prompt
prompt GETHITRATIO is number of GETHTS/GETS
prompt PINHIT RATIO is number of PINHITS/PINS - number close to 1 indicates
prompt that most objects requested for pinning have been cached.  Pay close
prompt attention to PINHIT RATIO.
prompt

column namespace    format a20   heading 'NAME'
column gets         format 99999999999 heading 'GETS'
column gethits      format 99999999999 heading 'GETHITS'
column gethitratio  format 999.99   heading 'GET HIT|RATIO'
column pins         format 99999999999  heading 'PINHITS'
column pinhitratio  format 999.99   heading 'PIN HIT|RATIO'

select namespace,    gets,  gethits,
       gethitratio,  pins,  pinhitratio
from   v$librarycache
/

rem
rem
rem This looks at the dictionary cache miss rate
rem

prompt
prompt------------------------------------------------------------------------

prompt THE DATA DICTIONARY CACHE:
prompt
prompt
prompt Consider keeping this below 5% to keep the data dictionary cache in
prompt the SGA.  Up the SHARED_POOL_SIZE to improve this statistic. **NOTE:
prompt increasing the SHARED_POOL_SIZE will increase the SGA.
prompt

column dictcache format 999.99 heading 'Dictionary Cache | Ratio %'

select sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache
from   v$rowcache
/

prompt
prompt------------------------------------------------------------------------

prompt
prompt SYSTEM EVENTS:
prompt
prompt Not sure of the value of this section yet but it looks interesting.
prompt

col event format a37 heading 'Event'
col total_waits format 999999999999 heading 'Total|Waits'
col time_waited format 99999999999999 heading 'Time Wait|In Hndrds'
col total_timeouts format 9999999999 heading 'Timeout'
col average_wait heading 'Average|Time' format 999999999.999

set pages 999

select *
from   v$system_event
/

prompt
prompt------------------------------------------------------------------------

rem
rem
rem This looks at the sga area breakdown
rem

prompt THE SGA AREA ALLOCATION:
prompt
prompt
prompt This shows the allocation of SGA storage.  Examine this before and
prompt after making changes in the INIT.ORA file which will impact the SGA.
prompt

col name format a40

select name, bytes
from   v$sgastat
/

set head off

select 'total of SGA                            '||sum(bytes)
from    v$sgastat
/

prompt
prompt------------------------------------------------------------------------

rem
rem Displays all the base session statistics
rem

set head on
set pagesize 110

column name        format a55            heading 'Statistic Name'
column value       format 9,999,999,999,999,999  heading 'Result'
column statistic#  format 999999999           heading 'Stat#'

ttitle center 'Instance Statistics' skip 2

prompt
prompt Below is a dump of the core Instance Statistics that are greater than0.
prompt Although there are a great many statistics listed, the ones of greatest
prompt value are displayed in other formats throughout this report.  Of
prompt interest here are the values for:
prompt
prompt  cumulative logons
prompt(# of actual connections to the DB since last startup - good
prompt  volume-of-use statistic)
prompt
prompt  #93  table fetch continued row
prompt  (# of chained rows - will be higher if there are a lot of long fields
prompt  if the value goes up over time, it is a good signaller of general
prompt  database fragmentation)
prompt

select statistic#,  name,  value
from   v$sysstat
where  value > 0
/

prompt
prompt -----------------------------------------------------------------------

set pages 66;
set space 3;
set heading on;

prompt
prompt Parse Ratio usually falls between 1.15 and 1.45.  If it is higher, then
prompt it is usually a sign of poorly written Pro* programs or unoptimized
prompt SQL*Forms applications.
prompt
prompt Recursive Call Ratio will usually be between
prompt
prompt   7.0 - 10.0 for tuned production systems
prompt  10.0 - 14.5 for tuned development systems
prompt
prompt Buffer Hit Ratio is dependent upon RDBMS size, SGA size and
prompt the types of applications being processed.  This shows the %-age
prompt of logical reads from the SGA as opposed to total reads - the
prompt figure should be as high as possible.  The hit ratio can be raised
prompt by increasing DB_BUFFERS, which increases SGA size.  By turning on
prompt the "Virtual Buffer Manager" (db_block_lru_statistics = TRUE and
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters),
prompt you can determine how many extra hits you would get from memory as
prompt opposed to physical I/O from disk.  **NOTE:  Turning these on will
prompt impact performance.  One shift of statistics gathering should be enough
prompt to get the required information.
prompt

ttitle left 'Ratios for this Instance' skip 2

column pcc   heading 'Parse|Ratio'       format 99.99
column rcc   heading 'Recsv|Cursr'       format 99.99
column hr    heading 'Buffer|Ratio'      format 999,999,999,999,999.999
column rwr   heading 'Rd/Wr|Ratio'       format 999,999,999.9
column bpfts heading 'Blks per|Full TS'  format 999,999,999

REM Modified for O7.1 to reverse 'cumulative opened cursors' to
REM 'opened cursors cumulative'
REM was:sum(decode(a.name,'cumulative opened cursors',value, .00000000001))
REM pcc,
REM and:sum(decode(a.name,'cumulative opened cursors',value,.00000000001))
REM rcc,

select sum(decode(a.name,'parse count',value,0)) /
       sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) pcc,
       sum(decode(a.name,'recursive calls',value,0)) /
       sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) rcc,
       (1-(sum(decode(a.name,'physical reads',value,0)) /
       sum(decode(a.name,'db block gets',value,.00000000001)) +
  sum(decode(a.name,'consistent gets',value,0))) * (-1)) hr,
       sum(decode(a.name,'physical reads',value,0)) /
       sum(decode(a.name,'physical writes',value,.00000000001)) rwr,
       (sum(decode(a.name,'table scan blocks gotten',value,0)) -
       sum(decode(a.name,'table scans (short tables)',value,0)) * 4) /
       sum(decode(a.name,'table scans (long tables)',value,.00000000001))
bpfts
from   v$sysstat a
/

prompt
prompt -----------------------------------------------------------------
prompt This looks at overall i/o activity against individual
prompt files within a tablespace
prompt
prompt Look for a mismatch across disk drives in terms of I/O
prompt
prompt Also, examine the Blocks per Read Ratio for heavily accessed
prompt TSs - if this value is significantly above 1 then you may have
prompt full tablescans occurring (with multi-block I/O)
prompt
prompt If activity on the files is unbalanced, move files around to balance
prompt the load.  Should see an approximately even set of numbers across files
prompt

set pagesize 100;
set space 1

column pbr       format 999999999999  heading 'Physical|Blk Read'
column pbw       format 9999999999    heading 'Physical|Blks Wrtn'
column pyr       format 9999999999    heading 'Physical|Reads'
column readtim   format 999999999999  heading 'Read|Time'
column name      format a40       heading 'DataFile Name'
column writetim  format 999999999999  heading 'Write|Time'

ttitle center 'Tablespace Report' skip 2

compute sum of f.phyblkrd, f.phyblkwrt on report

select fs.name name,  f.phyblkrd pbr,  f.phyblkwrt pbw,
       f.readtim,     f.writetim
from   v$filestat f, v$datafile fs
where  f.file#  =  fs.file#
order  by fs.name
/

prompt
prompt -----------------------------------------------------------------

prompt GENERATING WAIT STATISTICS:
prompt
prompt This will show wait stats for certain kernel instances.  This
prompt may show the need for additional rbs, wait lists, db_buffers
prompt
 ttitle center 'Wait Statistics for the Instance' skip 2

column class  heading 'Class Type'
column count  heading 'Times Waited'  format 99,999,999,999,999
column time   heading 'Total Times'   format 99,999,999,999,999

select class,  count,  time
from   v$waitstat
where  count > 0
order  by class
/

prompt
prompt Look at the wait statistics generated above (if any). They will
prompt tell you where there is contention in the system.  There will
prompt usually be some contention in any system - but if the ratio of
prompt waits for a particular operation starts to rise, you may need to
prompt add additional resource, such as more database buffers, log buffers,
prompt or rollback segments
prompt
prompt -----------------------------------------------------------------

prompt ROLLBACK STATISTICS:
prompt

ttitle off;

set linesize 80

column extents    format 999,999        heading 'Extents'
column rssize     format 999,999,999,999  heading 'Size in|Bytes'
column optsize    format 999,999,999,999  heading 'Optimal|Size'
column hwmsize    format 99,999,999,999   heading 'High Water|Mark'
column shrinks    format 999,999        heading 'Num of|Shrinks'
column wraps      format 999,999        heading 'Num of|Wraps'
column extends    format 999,999,999      heading 'Num of|Extends'
column aveactive  format 999,999,999,999  heading 'Average size|Active Extents'
column rownum noprint

select rssize,    optsize,  hwmsize,
       shrinks,   wraps,    extends,  aveactive
from   v$rollstat
order  by rownum
/

prompt
prompt -----------------------------------------------------------------

set linesize 80

break on report

compute sum of gets waits writes on report

ttitle center 'Rollback Statistics' skip 2

select rownum,  extents,  rssize,
       xacts,   gets,     waits,   writes
from   v$rollstat
order  by rownum
/

ttitle off

set heading off

prompt
prompt -----------------------------------------------------------------

prompt
prompt SORT AREA SIZE VALUES:
prompt
prompt To make best use of sort memory, the initial extent of your Users
prompt sort-work Tablespace should be sufficient to hold at least one sort
prompt run from memory to reduce dynamic space allocation.  If you are getting
prompt a high ratio of disk sorts as opposed to memory sorts, setting
prompt sort_area_retained_size = 0 in init.ora will force the sort area to be
prompt released immediately after a sort finishes.
prompt

column value format 999,999,999,999

select 'INIT.ORA sort_area_size: '||value
from    v$parameter
where   name like 'sort_area_size'
/

select a.name,  value
from   v$statname a,  v$sysstat
where  a.statistic#  =   v$sysstat.statistic#
and    a.name        in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/

prompt
prompt -----------------------------------------------------------------

set heading on
set space 2

prompt
prompt This looks at Tablespace Sizing - Total bytes and free bytes
prompt

ttitle center 'Tablespace Sizing Information' Skip 2

column tablespace_name  format a30            heading 'TS Name'
column sbytes           format 9,999,999,999,999  heading 'Total Bytes'
column fbytes           format 9,999,999,999,9999  heading 'Free Bytes'
column kount            format 999,999            heading 'Ext'

compute sum of fbytes on tablespace_name
compute sum of sbytes on tablespace_name
compute sum of sbytes on report
compute sum of fbytes on report

break on report

select a.tablespace_name,    a.bytes sbytes,
       sum(b.bytes) fbytes,  count(*) kount
from   dba_data_files a,  dba_free_space b
where  a.file_id  =  b.file_id
group  by a.tablespace_name, a.bytes
order  by a.tablespace_name
/

set linesize 80

prompt
prompt A large number of Free Chunks indicates that the tablespace may need
prompt to be defragmented and compressed.
prompt
prompt -----------------------------------------------------------------

set heading off

ttitle off

column value format 99,999,999,999,999

select 'Total Physical Reads', value
from    v$sysstat
where   statistic# = 39
/

prompt
prompt If you can significantly reduce physical reads by adding incremental
prompt data buffers...do it.  To determine whether adding data buffers will
prompt help, set db_block_lru_statistics = TRUE and
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters.
prompt You can determine how many extra hits you would get from memory as
prompt opposed to physical I/O from disk.  **NOTE:  Turning these on will
prompt impact performance.  One shift of statistics gathering should be enough
prompt to get the required information.
prompt

set heading on

clear computes

ttitle off

prompt
prompt -----------------------------------------------------------------
prompt CHECKING FOR FRAGMENTED DATABASE OBJECTS:
prompt
prompt Fragmentation report - If number of extents is approaching Maxextents,
prompt it is time to defragment the table.
prompt

column owner  noprint  new_value  owner_var
column segment_name  format a30          heading 'Object Name'
column segment_type  format a9           heading 'Table/Indx'
column sum(bytes)    format 999,999,999,999  heading 'Bytes Used'
column count(*)      format 999,999          heading 'No.'

break on owner skip page 2

ttitle center 'Table Fragmentation Report' skip 2 -
       left 'creator: ' owner_var skip 2

select a.owner,     segment_name,  segment_type,
    sum(bytes),  max_extents,   count(*)
from   dba_extents a,  dba_tables b
where  segment_name  =  b.table_name
having count(*) > 3
group  by a.owner, segment_name, segment_type, max_extents
order  by a.owner, segment_name, segment_type, max_extents
/

ttitle center 'Index Fragmentation Report' skip 2 -
  left 'creator: ' owner_var skip 2

select a.owner,     segment_name,  segment_type,
       sum(bytes),  max_extents,   count(*)
from   dba_extents a, dba_indexes b
where  segment_name = index_name
having count(*) > 3
group  by a.owner, segment_name, segment_type, max_extents
order  by a.owner, segment_name, segment_type, max_extents
/

prompt
prompt -----------------------------------------------------------------

spool off

how to create sql tuning task

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
       prompt 'Task_Name: '
 DECLARE
 ret_val VARCHAR2(4000);

BEGIN

ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);


dbms_sqltune.execute_tuning_task('&&Task_name');

END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name


How to find out the active sql details

break on sid skip 1 on serial# on username on executions on status
column sid format 9999
column username format a12
column sql_text form a64

select a.sid,a.serial#,a.username,c.executions,status, b.sql_text
from v$session a
    ,v$sqltext b
    ,v$sqlarea c
where a.username is not null
and status='ACTIVE'
--and sid in (174)
and   a.sql_address = b.address(+)
and   a.sql_address = c.address(+)
order by status,1,2,b.piece;

Steps to know the rollback segments used by a user

-- #############################################################################################
--
-- %Purpose: Show which Users are accessing which Rollback Segments.
--
--           It is sometimes useful to know which users are accessing the rollback segments.
--           This is important when a user is continally filling the rollback segments
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_rollback_segment_usage.lst

ttitle 'Current Rollback Segment Usage' -
skip 2

column "Rollback Segment Name" format a18;
column  "Oracle User Session" format a40;

select r.name "Rollback Segment Name",
       p.spid "Process ID",
       s.username||'('||l.sid||')' "Oracle User Session",
       sq.sql_text
  from v$sqlarea sq, v$lock l, v$process p, v$session s, v$rollname r
 where l.sid = p.pid(+)
   and s.sid = l.sid
   and trunc(l.id1(+) / 65536) = r.usn
   and l.type(+) = 'TX'
   and l.lmode(+) = 6
   and s.sql_address = sq.address
   and s.sql_hash_value = sq.hash_value
 order by r.name
/
spool off;
set feed on echo off termout on pages 24 verify on
ttitle off

STEPS TO ANALYZE TABLE LIST CHAINED ROWS

-- #############################################################################################
--
-- %Purpose: Show block chaining (chained rows) with ANALYZE TABLE LIST CHAINED ROWS
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set echo off termout off
DROP TABLE lst_chained_rows$tmp;
set termout on
CREATE TABLE lst_chained_rows$tmp
(
owner_name    VARCHAR2(30),
table_name    VARCHAR2(30),
cluster_name  VARCHAR2(30),
head_rowid    ROWID,
timestamp     DATE
);
--
accept user_namen char     prompt 'Username or %: '
accept tabellen_namen char prompt 'Tablename or %: '
set feed off echo off termout off pages 0 verify off array 1
--
SPOOL list_chained_rows.sql
SELECT  'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME,
        'LIST CHAINED ROWS INTO lst_chained_rows$tmp;'
FROM  sys.dba_tables
WHERE owner LIKE UPPER('&user_namen')
AND table_name LIKE UPPER('&tabellen_namen')
ORDER BY owner, table_name;
--
spool off
set feed on echo on termout on pages 66 verify on
@list_chained_rows.sql
--
set echo off
column table_name format a30
column owner_name format a16 trunc
--
spool list_chained_rows.lst
--
SELECT  RPAD(owner_name,16,'.') owner_name,
  RPAD(c.table_name,30,'.') table_name,
  num_rows,
  COUNT(*) ch_rows, pct_free
FROM  sys.dba_tables t, lst_chained_rows$tmp c
WHERE t.owner = c.owner_name
AND t.table_name = c.table_name
GROUP BY owner_name, c.table_name, pct_free, num_rows
UNION
SELECT  'No block chaining',NULL,0,0,0
FROM  dual
WHERE   0 = ( SELECT  COUNT(*)
        FROM  lst_chained_rows$tmp
        WHERE rownum = 1 )
ORDER BY 1,2;
spool off
set feed on echo off termout on pages 24 verify on
ttitle off
DROP TABLE lst_chained_rows$tmp;

Steps to know the Execution Plan of a DML statment

-- #############################################################################################
--
-- %Purpose: Displays the execution plan for a SQL DML statement
--
-- The SQL statement should be in a separate text file,
-- with either a ";" at the end of the line or a "/" on
-- the next line.  A plan_table table is required.
--
-- Usage: sqlplus user/pwd @explain filename
--
-- #############################################################################################
--
set feedback off arraysize 10 trimspool on linesize 1000
--
alter session set optimizer_percent_parallel = 100;
--
delete from plan_table;
commit;
--
set echo on
explain plan for
@&1
set echo off
--
col "Query Plan" for a70
--
select
  to_char(id,'999') ID,
  to_char(parent_id,'999') pt,
  initcap(
    lpad(' ',2*(level-1)) ||
    operation || ' ' ||
    options || ' ' ||
    decode(object_name,null,null,'of') || ' ' ||
    object_name || ' ' ||
    object_type || ' ' ||
    decode(id,0,'Cost = ' || ltrim(to_char(position,'999,999,999')))
    ) "Query Plan",
  to_char(cardinality,'999,999,999') "Row Count",
  substr(InitCap(other_tag),1,30) Other
from
  plan_table
start with id = 0 connect by prior id = parent_id
/
--
rollback;
--
set feedback on

Steps to Reclaim Unused Space From Indexes and Tables

-- #############################################################################################
--
-- %Purpose: How to reclaim UNUSED_SPACE from Indexes and Tables using DBMS_SPACE.UNUSED_SPACE
--
-- Before growing a datafile in a tablespace that shows on your
-- space analysis reports, search for space that can be reclaimed
-- from an object that was poorly sized initially. Tables and indexes
-- can be altered with a deallocate unused, thus reclaiming unused
-- space above the high-water mark.
--
-- Example: ALTER TABLE emp DEALLOCATE UNUSED;
--
-- This script prompts you for two pieces of information:
--
-- 1. The type of segment to retrieve, (i=indexes, t=tables)
--
-- 2. The tablespace_name to retrieve from.
--
-- Simply put, this allows you to retrieve one of these segment
-- types by tablespace_name. It is important to note that deallocating
-- unused space became available with Oracle version 7.3.
--
-- #############################################################################################--
--
accept type prompt "Enter the type of segment to check (i=index, t=table): "
accept ts_name prompt "Enter the tablespace name that you wish to check: "
set serveroutput on
feedback off
--
spool unused_space.lst
--
DECLARE
  v_total_blocks   NUMBER;
  v_total_bytes    NUMBER;
  v_unused_blocks  NUMBER;
  v_unused_bytes   NUMBER;
  v_file_id        NUMBER;
  v_block_id       NUMBER;
  v_last_block     NUMBER;
  v_used           NUMBER;
  v_owner          VARCHAR2(12);
  v_segment        VARCHAR2(80);
  v_type           CHAR(1);

  CURSOR index_c is
  SELECT owner, index_name
    FROM sys.dba_indexes
   WHERE tablespace_name = upper('&ts_name');

  CURSOR table_c is
  SELECT owner, table_name
    FROM sys.dba_tables
   WHERE tablespace_name = upper('&ts_name');

BEGIN
  DBMS_OUTPUT.ENABLE(100000);
  v_type := '&type';
  IF (v_type = 'i' or v_type = 'I') THEN
     OPEN index_c;
     FETCH index_c into v_owner, v_segment;
     WHILE index_c%FOUND LOOP
           --
           DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes,
                                   v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
           --
           DBMS_OUTPUT.PUT_LINE(CHR(10));
           DBMS_OUTPUT.PUT_LINE('Index Name                   = '||v_segment);
           DBMS_OUTPUT.PUT_LINE('Total Blocks                 = '||v_total_blocks);
           DBMS_OUTPUT.PUT_LINE('Total Bytes                  = '||v_total_bytes);
           DBMS_OUTPUT.PUT_LINE('Unused Blocks                = '||v_unused_blocks);
           DBMS_OUTPUT.PUT_LINE('Unused Bytes                 = '||v_unused_bytes);
           v_used := v_total_blocks - v_unused_blocks;
           DBMS_OUTPUT.PUT_LINE('Used Blocks                  = '||v_used);
           v_used := v_total_bytes - v_unused_bytes;
           DBMS_OUTPUT.PUT_LINE('Used Bytes                   = '||v_used);
           DBMS_OUTPUT.PUT_LINE('Last used extents file id    = '||v_file_id);
           DBMS_OUTPUT.PUT_LINE('Last used extents block id   = '||v_block_id);
           DBMS_OUTPUT.PUT_LINE('Last used block              = '||v_last_block);
           fetch index_c into v_owner, v_segment;
      END LOOP;
      CLOSE index_c;
  ELSIF (v_type = 't' or v_type = 'T') THEN
    OPEN table_c;
        FETCH table_c into v_owner, v_segment;
        WHILE table_c%FOUND LOOP
            --
            DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes,
                                    v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
            --
            v_used := v_total_bytes - v_unused_bytes;
            DBMS_OUTPUT.PUT_LINE(CHR(10));
            DBMS_OUTPUT.PUT_LINE('Table Name                   = '||v_segment);
            DBMS_OUTPUT.PUT_LINE('Total Blocks                 = '||v_total_blocks);
            DBMS_OUTPUT.PUT_LINE('Total Bytes                  = '||v_total_bytes);
            DBMS_OUTPUT.PUT_LINE('Unused Blocks                = '||v_unused_blocks);
            DBMS_OUTPUT.PUT_LINE('Unused Bytes                 = '||v_unused_bytes);
            v_used := v_total_blocks - v_unused_blocks;
            DBMS_OUTPUT.PUT_LINE('Used Blocks                  = '||v_used);
            v_used := v_total_bytes - v_unused_bytes;
            DBMS_OUTPUT.PUT_LINE('Used Bytes                   = '||v_used);
            DBMS_OUTPUT.PUT_LINE('.    KBytes                  = '||v_used/1024);
            DBMS_OUTPUT.PUT_LINE('.    MBytes                  = '||(v_used/1024)/1024);
            DBMS_OUTPUT.PUT_LINE('Last used extents file id    = '||v_file_id);
            DBMS_OUTPUT.PUT_LINE('Last used extents block id   = '||v_block_id);
            DBMS_OUTPUT.PUT_LINE('Last used block              = '||v_last_block);
            fetch table_c into v_owner, v_segment;
      END LOOP;
      CLOSE table_c;
  END IF;
END;
/
spool off

Steps to Tune Redologs undo Checkpoints for Contention,Waits and Number/Duration of Checkpoints

-- #############################################################################################
--
-- %Purpose: Tuning Redologs und Checkpoints (Contention, Waits, Number/Duration of Checkpoints)
--
-- #############################################################################################

1). Redolog Buffer Contention
-----------------------------
SELECT SUBSTR(name,1,20) "Name",gets,misses,immediate_gets,immediate_misses
  FROM v$latch
 WHERE name in ('redo allocation', 'redo copy');

Name                       GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------- ---------- ---------- -------------- ----------------
redo allocation     277'446'780  2'534'627              0                0
redo copy                33'818     27'694    357'613'861          150'511

MISSES/GETS (must be < 1%)

Redo allocation: (2'534'627 / 277'446'780) * 100 = 0.91 %
Redo Copy:       (27'694 / 33'818) * 100 = 81.8 %

IMMEDIATE_MISSES/(IMMEDIATE_GETS+IMMEDIATE_MISSES) (must be < 1%)

Redo Copy: 150'511/(150'511+357'613'861) = 0.04 %

2). Waits on Redo Log Buffer
----------------------------
SELECT name,value
 FROM v$sysstat
 WHERE name = 'redo log space requests';

The value of 'redo log space requests' reflects the number
of times a user process waits for space in the redo log buffer.
Optimal is if the value is near 0 (Oracle Manual says this ...)

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log space requests                                               22641

4). Number of Checkpoints per hour
----------------------------------
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_logswitches.lst

ttitle left 'Redolog File Status from V$LOG' skip 2

select group#, sequence#,
       Members, archived, status, first_time
  from v$log;

ttitle left 'Number of Logswitches per Hour' skip 2

select to_char(first_time,'YYYY.MM.DD') day,
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'99') "00",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'99') "01",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'99') "02",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'99') "03",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'99') "04",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'99') "05",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'99') "06",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'99') "07",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'99') "08",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'99') "09",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'99') "10",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'99') "11",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'99') "12",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'99') "13",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'99') "14",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'99') "15",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'99') "16",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'99') "17",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'99') "18",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'99') "19",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'99') "20",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'99') "21",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'99') "22",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'99') "23"
  from v$log_history
 group by to_char(first_time,'YYYY.MM.DD') 
/
spool off;


DAY   00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
07/07   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   2   0   0   0   0   0   0   0   0
07/08   0   0   0   0   0   0   0   0   0   0   0   5   0   4   1   0   1   0   0   0   0   0   0   0
07/12   0   0   0   0   0   0   0   0   0   0   1   1   0   1   1   0   0   0   0   0   0   0   0   0
07/13   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
07/14   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   1   1   1   0   0
07/15   1   0   0   0   0   0   0   0   0   0   0   0   2   1   0   0   1   2   2   0   0   0   0   0
07/16   0   0  10  10  15  11   5   0   0   0   0   0   2   5   5   4   5   7   6   6   7   4   4   4
07/17   2   2   1   3   4   6   9  10  11  11  12  12  11  11  12  11  11  12  12   9   9  10  12   9
07/18  12   9  10  10   8   8   9  10   9   8   9  10  10  11  10  11  10  10  11  10  11   9  10  10
07/19   9   3   1   1   0   0   4   6   7   7   4   5  11  10   5   4   5   7   6   8   7   5   5   3
07/20   1   1   8  10   7   5   4   5   4   5   7   7   9   7   9   9   7   9  10  11  12  11  12   9
07/21   9  10  10  10  12  10   7   8   9   8   9  10  11  11  11   8  10  10  12   7   6   7   7   7
07/22   8   7   9  10   8   6   7   8   8   8   9   9   9  10   9   9   9   9   9   9  10   7   6   7
07/23   5   5   7   7   7   2   3   3   4   5   6   5   5   4   3   3   4   4   6   6   5   9   8   5
07/24   4   4   5   4   7   6   5   8   8  11  11  11     

log_checkpoint_interval = 900'000'000  (OK, must be greather than Redolog-File)
log_checkpoint_timeout  = 1200 (Set it to 0, so time-based checkpoints are disabled)

5). Time needed to write a checkpoint
-------------------------------------
Beginning database checkpoint by background
Mon Aug  2 16:37:36 1999
Thread 1 advanced to log sequence 2860
  Current log# 4 seq# 2860 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP04.log
Mon Aug  2 16:43:31 1999
Completed database checkpoint by background

==> 6 Minutes

Mon Aug  2 16:45:15 1999
Beginning database checkpoint by background
Mon Aug  2 16:45:15 1999
Thread 1 advanced to log sequence 2861
  Current log# 5 seq# 2861 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP05.log
Mon Aug  2 16:50:29 1999
Completed database checkpoint by background

==> 5.5 Minutes

Mon Aug  2 16:51:50 1999
Beginning database checkpoint by background
Mon Aug  2 16:51:51 1999
Thread 1 advanced to log sequence 2862
  Current log# 6 seq# 2862 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP06.log
Mon Aug  2 16:56:44 1999
Completed database checkpoint by background

==> 5.5 Minutes

Wednesday, June 29, 2016

Weblogic Reports and forms services Start/Stop


Weblogic Reports and forms services Start/Stop


Start a WebLogic Forms and Reports Services :

export MW_HOME=/u01/app/oracle/middleware
export DOMAIN_HOME=$MW_HOME/user_projects/domains/ClassicDomain
export FR_INST=$MW_HOME/asinst_1

echo "Starting AdminServer"
nohup $DOMAIN_HOME/bin/startWebLogic.sh > /dev/null 2>&1 &
sleep 120

echo "Starting WLS_FORMS"
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_FORMS > /dev/null 2>&1 &
echo "Starting WLS_REPORTS"
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_REPORTS > /dev/null 2>&1 &

echo "Start remaining processes using OPMN"
$FR_INST/bin/opmnctl startall

echo "Sleep for 10 minutes before calling reports startserver"
sleep 600
curl http://localhost:8888/reports/rwservlet/startserver > /dev/null 2>&1 &

echo "Done!"


Stop a WebLogic Forms and Reports Services :

export MW_HOME=/u01/app/oracle/middleware
export DOMAIN_HOME=$MW_HOME/user_projects/domains/ClassicDomain
export FR_INST=$MW_HOME/asinst_1

echo "Stop OPMN processes"
$FR_INST/bin/opmnctl stopall

echo "Stop WLS_FORMS"
$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_FORMS
echo "Stop WLS_REPORTS"
$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_REPORTS

echo "Stop AdminServer"
$DOMAIN_HOME/bin/stopWebLogic.sh

echo "Tidy up temp files"
find $DOMAIN_HOME/servers -name "*.lok" -exec rm -f {} \;
find $DOMAIN_HOME/servers -name "*.DAT" -exec rm -f {} \;

echo "Done!"

Steps to start and stop a WebLogic domain and managed server

Steps to start and stop  a WebLogic domain and managed server

Start a WebLogic domain and managed server :

export MW_HOME=/u01/app/oracle/middleware
export DOMAIN_HOME=$MW_HOME/user_projects/domains/clusterDomain

echo "Starting AdminServer"
nohup $DOMAIN_HOME/startWebLogic.sh > /dev/null 2>&1 &
sleep 120

echo "Starting clusterServer_1"
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh clusterServer_1 > /dev/null 2>&1 &
sleep 60

echo "Done!"

Stop a WebLogic domain and managed server :

export MW_HOME=/u01/app/oracle/middleware
export DOMAIN_HOME=$MW_HOME/user_projects/domains/clusterDomain

echo "Stopping clusterServer_1"
$DOMAIN_HOME/bin/stopManagedWebLogic.sh clusterServer_1

echo "Stopping AdminServer"
$DOMAIN_HOME/bin/stopWebLogic.sh

echo "Tidy up temp files"
find $DOMAIN_HOME/servers -name "*.lok" -exec rm -f {} \;
find $DOMAIN_HOME/servers -name "*.DAT" -exec rm -f {} \;

echo "Done!"


Steps to start and stop Node-manager in weblogic

Steps to start and stop Node-manager in weblogic


Starting node-manager :

export MW_HOME=/u01/app/oracle/middleware
export WLS_HOME=$MW_HOME/wlserver_10.3

echo "Starting Node Manger"
nohup $WLS_HOME/server/bin/startNodeManager.sh > /dev/null 2>&1 &

echo "Done!"



Stopping node-manager :


export MW_HOME=/u01/app/oracle/middleware
export WLS_HOME=$MW_HOME/wlserver_10.3


echo "Stopping Node Manger"
kill -9 `ps -ef | grep [N]odeManager | awk '{print $2}'`


To find the dependent packages or objects related to a package



To find the dependent packages or objects related to a package





Below query is used to find the dependencies for an objects or a package
So we can avoid the invalidation of objects before compilation itself.



set pages 2000
select substr(a.owner,1,10),a.name,a.type,b.status,last_ddl_Time
from
(select distinct owner,name,type from dba_dependencies
where referenced_name = UPPER('&1')
) a,
dba_objects b
where a.name=b.object_name
and   a.owner=b.owner
and   a.type = b.object_type
order by 3
/

Query to find current cache hit ratio in database


Query to find current cache hit ratio in database



Below query represents the exact cache hit ratio happening currently in database

The minimum figure of 89% is quoted below, but depending on the type of environment this need to be changed.


PROMPT
PROMPT Hit ratio should exceed 89%

SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
       Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
         Sum(Decode(a.name, 'db block gets', a.value, 0)) -
         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0))))
             *100,2) "Hit Ratio %"
FROM   v$sysstat a;

Steps to Run Expdp Impdp Jobs in Background with nohup mode

How to Run Expdp Impdp Jobs in Background


Step 1: Create export or import parameter file


$ cat exp.par


userid=abc/def


job_name=tab_export


directory=EXP_DIR


dumpfile=TST_table_exp.dmp


logfile=TST_tab_exp.log


REUSE_DUMPFILES=y


tables=sox.PROFILE,sox.PROFIL_ACC_TYPE,sox.PURCHASE_STEP,sox.RULE,sox.SUB,sox.SUB_TABLE,




Step 2: Create a shell script which calls the expdp in nohup and change the permission to executable.


$ cat export.sh


nohup expdp parfile=/home/oracle/st/exp.par &


$ chmod 744 export.sh


Step 3: run the shell script in nohup. This will release the prompt immediately and there will not be any running job in the prompt. You can see the datapump job running in DBA_DATAPUMP_JOBS view.


$ nohup export.sh &


[1] 30221


$ nohup: appending output to `nohup.out'



[1]+  Done                    nohup export.sh


ORA-1166 file number 101 is larger than MAXDATAFILES (100)

In the process of upgrading EBS R12 DB from 11.2.0.3 to 11.2.0.4 using DBUA. The end of the upgrade, DBUA fails with following error message.

During the DBUA, I have selected option 'move the database files location'.

ORA-1503: CREATE CONTROLFILE failed
ORA-1166: file number 101 is larger than MAXDATAFILES (100)
ORA-1110: data file 101: '/u01/EBS/data/opmor01.dbf'

So then I have verified oracle documentation and support docs. There is a bug in DBUA, the problem is Database Upgrade Assistant (DBUA) tries to re-create the control file with MAXDATAFILES set to 100 instead of using the value from the old control file.

To fix this issue one of the following workarounds should be used:

1) Upgrade the database using DBUA (without moving the database files option) and If you are using ASM, after completed the upgrade use RMAN to move the database files to ASM.
2) Manually upgrade the database and manually re-create the control file (setting MAXDATAFILES to a higher value).


Done!!

Find Roles and Privelages for USER

Following query will be useful to findout Roles and Privileges assigned to a user.

Here, used SCOTT as User:

COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
  SELECT NULL grantee, username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('SCOTT')
  UNION
  SELECT grantee,granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee,privilege
  FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;

Sample Output:

USER,HIS ROLES AND PRIVILEGES
-------------------------------------------
     SCOTT
          CREATE MATERIALIZED VIEW
          CREATE SESSION
          CREATE TABLE
          CREATE TYPE
          CREATE VIEW
          RESOURCE
               CREATE CLUSTER
               CREATE INDEXTYPE
               CREATE OPERATOR
               CREATE PROCEDURE
               CREATE SEQUENCE
               CREATE TABLE
               CREATE TRIGGER
               CREATE TYPE
          UNLIMITED TABLESPACE

16 rows selected.

SQL>

Log files in Oracle EBS Release 12.1.3 and Oracle EBS Release 12.2.4

The Log files locations in Oracle EBS Release 12.1.3 and  Oracle EBS R 12.2.4  are given below:

1.Instance startup and configuration Log files are located for INST_TOP in Oracle Release 12.1.3 are below:

$INST_TOP/logs/appl/admin/log
Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp](Apache log files)
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J, oa*, opmn.log)
$INST_TOP/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

2. Log files related to cloning in R12.1.3 are as below:

 Preclone log files in source instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
Application Tier –
$INST_TOP/apps/$CONTEXT_NAME/admin/log/(StageAppsTier_MMDDHHMM.log)

Clone log files in target instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log
Apps Tier – $INST_TOP/admin/log/ApplyAppsTier_.log

3. Patching related log files in R12.1.3 are as below:

i) Application Tier adpatch log – $APPL_TOP/admin/$SID/log/
ii) Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
iii) Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
iv) Database Tier opatch log – $ORACLE_HOME/.patch_storage


4. Autoconfig related log files in R12.1.3 are as below:

a) Database Tier Autoconfig log :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log


b) Application Tier Autoconfig log : 
$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

5.Autoconfig context file location in R12.1.3 :
$INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml


6)R12.1.3 Installation Logs in R12.1.3 are as below:

 Database Tier Installation
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log
Application Tier Installation
$INST_TOP/logs/.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log
$INST_TOP/logs/ora/10.1.2/install/make_.log
$INST_TOP/logs/ora/10.1.3/install/make_.log
$INST_TOP/admin/log/ApplyAppsTechStack.log
$INST_TOP/admin/log/ohclone.log
$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_.log
$APPL_TOP/admin/$CONTEXT_NAME/log//adconfig.log
$APPL_TOP/admin/$CONTEXT_NAME/log//NetServiceHandler.log
Inventory Registration:
$Global Inventory/logs/cloneActions.log
$Global Inventory/logs/oraInstall.log
$Global Inventory/logs/silentInstall.log

7) Log files related with relink,Network,OUT inventory logs for R12.1.3 are as below:
 1) Database Tier
1.1) Relink Log files :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log
1.2) Alert Log Files :
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log
1.3) Network Logs :
$ORACLE_HOME/network/admin/$SID.log
1.4) OUI Logs :
OUI Inventory Logs :
$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs
2) Application Tier
$ORACLE_HOME/j2ee/DevSuite/log
$ORACLE_HOME/opmn/logs
$ORACLE_HOME/network/logs
Tech Stack Patch 10.1.3 (Web/HTTP Server)
$IAS_ORACLE_HOME/j2ee/forms/logs
$IAS_ORACLE_HOME/j2ee/oafm/logs
$IAS_ORACLE_HOME/j2ee/oacore/logs
$IAS_ORACLE_HOME/opmn/logs
$IAS_ORACLE_HOME/network/log
$INST_TOP/logs/ora/10.1.2
$INST_TOP/logs/ora/10.1.3
$INST_TOP/logs/appl/conc/log
$INST_TOP/logs/appl/admin/log


In EBS R12.2.4 the log files locations are as below:

1)Log files file Online patching (adop) in EBS R12.2.4 are in below location:

The adop log files are located on the non-editioned file system (fs_ne), under:

$NE_BASE/EBSapps/log/adop/<adop_session_id>/<phase>_<date>_<time>/<context_name>/log

This log directory will contain patch logs,patch worker logs.

adop(phase=fs_clone) Online pathcing filesystem cloning process related log files are found under:

$INST_TOP/admin/log


2)Log files for Autoconfig process in Oracle EBS R12.2.4 are below:

On Applicaion Tier: $INST_TOP/admin/log/<MMDDhhmm>
On Database Tier: $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>

3)Log files for start/stop of services from $ADMIN_SCRIPTS_HOME

In below directory we will find log files related to start/stop process of oacore, forms, apache, opmn,
weblogic admin server/node manager:

$LOG_HOME/appl/admin/log


4)Log/Out files for Concurrent programs/managers in Oracle R12.2.4 are in below location:

Log/Out files for Oracle Release 12.2 are stored in Non-Editioned filesystem(NE).

Log files: $APPLCSF/$APPLLOG (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/log)
Out files: $APPLCSF/$APPLOUT (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/out)


5)Log files for OPMN and OHS processes in Oracle R12.2.4 are in below location:

Below directory contains log files related OPMN process(opmn.log),
OPMN Debug logs(debug.log), HTTP Transaction logs (access.log),security settings related logs.

$IAS_ORACLE_HOME/instances/<ohs_instance>/diagnostics/logs


6)Log file for Weblogic Node Manager in Oracle R12.2.4 are in below location:

Log file is generated by Node Manager and contains data for all domains that
are controlled by Node Manager on a given physical machine.

$FMW_HOME/wlserver_10.3/common/nodemanager/nmHome1/nodemanager.log


7)Log file for Weblogic  in Oracle R12.2.4 for Oracle Management Service are below

Initial settings for AdminServer and Domain level information is written in this log file

$EBS_DOMAIN_HOME/sysman/log


8)Log files for server processes initiated through Weblogic in Oracle R12.2.4 are in below location:
Stdout and stderr messages generated by the forms, oafm and oacore services are located
at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.

$EBS_DOMAIN_HOME/servers/<server_name>/logs/<server_name>.out


Enjoy Oracle EBS R12.2 learning and Performing tasks.