Search This Blog

Wednesday, 11 July 2018

To clear the log file from trace location:

Find and Delete:

older than 2 days:
---------------------

 find . -name "*trc" -mmin +2880 -exec rm {} \;
 find . -name "*trm" -mmin +2880 -exec rm {} \;

 older than one day:
 ---------------------
 find . -name "*trc" -mmin +440 -exec rm {} \;

Saturday, 23 June 2018

How to check all sessions information (active and inactive):

Below is the query to check connection with services:
======================================

col sid head "Sid" form 9999 trunc
  col SERVICE_NAME for a30;
 col serial# form 99999 trunc head "Ser#"
 col username form a8 trunc
 col osuser form a7 trunc
 col machine form a20 trunc head "Client|Machine"
 col program form a15 trunc head "Client|Program"
 col login form a11
 col "last call" form 9999999 trunc head "Last Call|In Secs"
 col status form a6 trunc
 select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
 substr(program||module,1,15) program,substr(machine,1,22) machine,
 SERVICE_NAME,
 to_char(logon_time,'ddMon hh24:mi') login,
 last_call_et "last call",status,sql_hash_value
 from v$session
 order by login
 /
 

Wednesday, 13 June 2018

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property



        Need to check on DelayMins configuration on all standby’s

ð  DGMGRL> show database verbose 'TESH957P_DC2' 'delaymins';

        Check MRP process is running or not.

ð  SYS@TESH957P SQL>SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where PROCESS like 'MRP%';

          Need to restart Intended State option:

ð  edit database "TESH957P_DC1" set state=apply-off;

ð  edit database "TESH957P_DC1" set state=apply-on;

         And need to cancel the MRP process while enabling i have used " CURRENT LOGFILE " option.

ð  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ð  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Thursday, 14 December 2017

SAR report


Use below command when ever need SAR report:

 sar -P ALL  ( CPU Usage of Individual CPU or Core (sar -P))

sar -W      (View page exchange status)

sar -B    (Paging Statistics)

sar -b     (I/O Transfer Rate)

sar -R     (These fields are displayed (pages are 4K or 8K in size)

sar -r     (RAM Load: Displaying the RAM Load)

sar -S    (Swap Space Used )

sar -H     (hugepages utilization)

sar -v    (status of inode, file and other kernel tables)

sar -q     (Report queue length and load averages)

sar -y                     (Report TTY devices activity.)

sar -d     (Individual Block Device I/O)

sar -n DEV  (Network Utilization Statistics)

sar -n EDEV      (statistics on failures (errors) from the network devices

sar -n NFS       ( statistics about NFS client activity are reported)

sar -n NFSD    (statistics about NFS server activity are reported)

sar -n  SOCK      (statistics on sockets in use are reported (IPv4).

COPY the sa fiel to any temporary location then get the individual log file like below:


=> [root@dcplnx22981099 sa]# cp sa10 /home/users/orcldb/raj/
=> got to the "/home/users/orcldb/raj/"

sar -r -f sa10 > sar10-ram.log 
 sar -P ALL -f sa10 > sar-cpu.log
sar -n DEV -f sa10 > sar10-network.log


[orcldb@dcplnx22981099 raj]$ ls -ltr sa*
-rw-r----- 1 orcldb oinstall 1328084 Oct 11 22:55 sar10
-rw-r--r-- 1 root   root     1758368 Oct 12 02:40 sa10
-rw-r----- 1 orcldb oinstall   27074 Oct 12 02:40 sar10-ram.log
-rw-r----- 1 orcldb oinstall  558663 Oct 12 02:45 sar-cpu.log
-rw-r----- 1 orcldb oinstall   88494 Oct 12 02:45 sar10-network.log

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

Get the Ksar(to graphical mode) from "ksar github" get the the ksar at "GitHub releases page." => v5.2.3 => ksar-5.2.3-all.jar.

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



Friday, 24 November 2017

Archive log restoration to Specific destination:

RMAN> catalog start with '/oracle/RAJA33DT/archive/RAJA33DT';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/RAJA33DT/archive/RAJA33DT

List of Files Unknown to the Database
=====================================
File Name: /oracle/RAJA33DT/archive/RAJA33DT/RAJA33DT-114008-20170701-63999.1.PITR.redo

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/RAJA33DT/archive/RAJA33DT/RAJA33DT-114008-20170701-63999.1.PITR.redo


RMAN> list backup of archivelog all;

RMAN> restore archivelog logseq 113983;

RMAN> restore archivelog logseq 113983;

or

NOTE: (If you want to restore any specific location u have set the destination otherwise it will store default archive location)

run {
allocate channel t1 type 'sbt_tape';
set archivelog destination to '/home/oracle/terry/logfile/';  => (this will change the default restore location.)
restore archivelog logseq xxxxx;

or

restore archivelog from logseq=##### until logseq=#####;
}
=======================================================================
 RMAN MAUNAL BACKUP command:

 rman target=/ log=/oracle/RAJAD/base/admin/RAJAD/var/tmp/rman-arch-04396.temp cmdfile=/oracle/RAJAD/base/admin/RRAJAD/var/tmp/rman-arch-25815.rman

 =================================================================================================
 [orcldb@dcplnx22502547 log]$ cat /oracle/RAJAD/base/admin/RAJAD/var/tmp/rman-arch-25815.rman
set echo on
run {
 set command id to 'RMAN';
 allocate channel DISK01 device type disk format '/oracle/RAJAD/archive/RAJAD/%d-%e-%T-%s.%p.PITR.redo';
 backup tag='PITR' filesperset=32 as compressed backupset skip inaccessible (archivelog all not backed up delete all input);
 delete noprompt archivelog all backed up 1 times to device type disk;
}
exit;

Gathering stats

---------------------------------------------------------
Gathering stats
---------------------------------------------------------
Schema statistics:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('<SCHEMA_NAME>',DBMS_STATS.AUTO_SAMPLE_SIZE);

System statistics:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

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

nohup ./gather_stats_RAJA023P.ksh  >test.log &

[orcldb@dcplnx22502624 sql]$ cat gather_stats_RAJA023P.ksh
sqlplus / as sysdba << eof
set time on timing on echo on;
spool gather_stats_RAJA023P.log
exec dbms_stats.gather_schema_stats(ownname => 'JOBPRO',cascade => TRUE);
spool off
exit
eof
=========================================================================

Indexes with the density below 75%:

Recommendation to rebuild:

==========================================================================
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Indexes that should be rebuilt - list of indexes with density below 75%</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #ffffff} --> -
</STYLE>" -
BODY "TEXT='#212121'"
--TABLE "WIDTH='70%' BORDER='1'"

set termout off;
set echo off;
set verify off
SET FEEDBACK OFF;


begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/

set echo on
column filename new_val filename
column filename2 new_val filename2
select 'sparse_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.html' filename from v$instance;
select 'rebuild_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.sql' filename2 from v$instance;
set echo off

set define &

spool &filename

column index_name format a40
column part_name format a20
column subpart_name format a20
column last_ddl_time  format a13
column x noprint new_value x
column last_analyzed format a13
column compression format a11
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'

alter session set nls_date_format='DD-MON-YYYY';

SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON

select
  instance_name as Database_Name,
  (SELECT SYS_CONTEXT('USERENV','HOST') FROM dual) as Hostname,
  (select sysdate from dual) as Report_created
from
  v$instance;

SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON

select  /*ORACONF*/ /*+ ordered */
  u.name ||'.'|| o.name  index_name,
  op.subname part_name,
  decode(&obj, isp.obj#, o.subname, '') subpart_name,
  to_char(100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
  )/&leafcnt),'999.00') ||'%' density,
  floor( &leafcnt -
  &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
  ) extra_blocks,
  max(o.mtime) last_ddl_time,
  decode(max(&last_analyzed),to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',max(&last_analyzed)) last_analyzed,
  max(&compress) compression,
  (select index_type as index_type from dba_indexes where index_name=o.name and owner=u.name) index_type,
  (select sum(bytes/1024/1024) as index_size from dba_segments where segment_name=o.name and owner=u.name) index_size_in_MB
from
  sys.ind$  i,
  sys.icol$  ic,
  ( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
    union all
    select  /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
  sys.indsubpart$ isp,
  ( select ts#, blocksize value
    from sys.ts$
      )  p,
  sys.hist_head$  h,
  sys.obj$  o,
  sys.user$  u,
  sys.obj$  op
where
  i.obj# = ip.bo#(+) and
  ip.obj# = isp.pobj#(+) and
  &leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
  ic.obj# = i.obj# and
  h.obj# = i.bo# and
  h.intcol# = ic.intcol# and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  op.obj# = &objp
group by
  u.name,
  o.name,
  op.subname,
  decode(&obj, isp.obj#, o.subname, ''),
  &rowcnt,
  &leafcnt,
  &initr,
  &pctf,
  p.value
  having
    100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    )/&leafcnt) <= nvl('&density','75') and
    floor( &leafcnt -
      &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    ) > 0
order by
  5 desc, 4
/


undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off
set markup html off;
set termout on;
--prompt ORACONF:  sparse_tables.html
set termout off;


spool &filename2
SET   SERVEROUTPUT  OFF
set pages 0

set lines 200
column index_name format a200
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'

select  /*ORACONF*/ /*+ ordered */
  'alter index '||u.name ||'.'|| o.name||' rebuild online parallel 4;'  index_name
from
  sys.ind$  i,
  sys.icol$  ic,
  ( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
    union all
    select  /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
  sys.indsubpart$ isp,
  ( select ts#, blocksize value
    from sys.ts$
      )  p,
  sys.hist_head$  h,
  sys.obj$  o,
  sys.user$  u,
  sys.obj$  op
where
  i.obj# = ip.bo#(+) and
  ip.obj# = isp.pobj#(+) and
  &leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
  ic.obj# = i.obj# and
  h.obj# = i.bo# and
  h.intcol# = ic.intcol# and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  op.obj# = &objp
group by
  u.name,
  o.name,
  op.subname,
  decode(&obj, isp.obj#, o.subname, ''),
  &rowcnt,
  &leafcnt,
  &initr,
  &pctf,
  p.value
  having
    100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    )/&leafcnt) <= nvl('&density','75') and
    floor( &leafcnt -
      &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    ) > 0
/

undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off


begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/
============================================================================
Output :  (If you run above sql scrip(any_name.sql). As an output are two files)
Note: please check from the report if all indexes are normal indexes (bit map indexes are not possible to rebuild online)
=> html report (sparse_indexes_<SID_<DATE>.html)  -with all indexes with the density below 75%.
=> sql file (rebuild_indexes_<SID>_<DATE>.sql) - you can run it to rebuild indexes