Oracle – Find Linux Process ID from SID

Posted by

0

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid "Linux process id", s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


-- Get Linux processes that have no related session(s)
select inst_id, spid, program from gv$process where addr not in ( select paddr from gv$session ) and spid is not null


-- Generate Linux kill commands for processes that have no sessions (sessions that may have been killed at Oracle level
select p.inst_id,s.sid, s.serial#, s.program, s.username, p.spid, p.username,
       'kill -9 '||p.spid
from 
gv$session s
RIGHT OUTER JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where p.spid in(
SELECT spid
FROM   gv$process
WHERE NOT EXISTS (SELECT 1
                  FROM gv$session
                  WHERE paddr = addr));

phpMyAdmin – allow remote access to phpMyAdmin

Posted by

0

To allow access to phpMyAdmin on a server from a remote browser, edit the file:

/etc/httpd/conf.d/phpMyAdmin.conf

and add lines as follows (example allow remote access from 192.168.1.107)

# phpMyAdmin - Web based MySQL browser written in php
#
# Allows only localhost by default
#
# But allowing phpMyAdmin to anyone other than localhost should be considered
# dangerous unless properly secured by SSL

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin


   AddDefaultCharset UTF-8

   
     # Apache 2.4
     
       Require ip 127.0.0.1
       Require ip ::1
       Require ip 192.168.1.107
     
   
   
     # Apache 2.2
     Order Deny,Allow
     Deny from All
     Allow from 127.0.0.1
     Allow from ::1
     Allow from All
   



   
     # Apache 2.4
     
       Require ip 127.0.0.1
       Require ip ::1
       Require ip 192.168.1.107
     
   
   
     # Apache 2.2
     Order Deny,Allow
     Deny from All
     Allow from 127.0.0.1
     Allow from ::1
     Allow from 192.168.1.107
   


# These directories do not require access over HTTP - taken from the original
# phpMyAdmin upstream tarball
#

    Order Deny,Allow
    Deny from All



    Order Deny,Allow
    Deny from All



    Order Deny,Allow
    Deny from All


# This configuration prevents mod_security at phpMyAdmin directories from
# filtering SQL etc.  This may break your mod_security implementation.
#
#
#    
#        SecRuleInheritance Off
#    
#

Linux – cpu info

Posted by

0

lscpu

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                12
On-line CPU(s) list:   0-11
Thread(s) per core:    2
Core(s) per socket:    6
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 63
Model name:            Intel(R) Xeon(R) CPU E5-2643 v3 @ 3.40GHz
Stepping:              2
CPU MHz:               1200.000
BogoMIPS:              6791.95
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              20480K
NUMA node0 CPU(s):     0-11

Oracle – Data Pump

Posted by

0

expdp "\/ as sysdba\" SCHEMAS=xyz,abc ..... FLASHBACK_TIME=systimestamp

SQL> select current_scn from v$database;

expdp "\/ as sysdba\" SCHEMAS=xyz,abc ..... FLASHBACK_SCN=nnnnnnnnnn
where nnnnnnnnnn = current_scn from the previous query

Oracle – Remove / Detach Home from Oracle Inventory

Posted by

0


Must be done from an installed runInstaller (not from distribution)

oracle@server1: ./runInstaller -silent -detachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/oracle/product/112_64/ORCL"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 10111 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/oraInventory
'DetachHome' was successful.

Then, in the inventory.xml file, an entry will appear as follows:

Oracle – Quick Check using Scripts

Posted by

0

Not exhaustive


select * from v$sga_target_advice

select * from v$pga_target_advice

select * from v$shared_pool_advice


-- Dictionary Cache Hit Ratio
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
  FROM   v$rowcache;

 -- -----------------------
  -- Library Cache Hit Ratio
  -- -----------------------
  SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
  FROM   v$librarycache;

 -- -------------------------------
  -- DB Block Buffer Cache Hit Ratio
  -- -------------------------------
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

-- ---------------
  -- Latch Hit Ratio
  -- ---------------
  SELECT (1 - (Sum(misses) / Sum(gets))) * 100
  FROM   v$latch;

-- -----------------------
  -- Disk Sort Ratio
  -- -----------------------
  SELECT (disk.value/mem.value) * 100
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

-- Locks
SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text
FROM v$session a,v$lock b, v$sqlarea c   WHERE b.type in ('TM','TX','UL') and   a.sid=b.sid and 
 lmode > 0 and  ((a.PREV_HASH_VALUE = c.hash_value and  a.prev_sql_addr = c.address and  a.sql_hash_value = 0) or  
(c.hash_value=a.sql_hash_value and c.address = a.sql_address))