Pages

▼

Work



Sessions :

CPU/Mem :  ps -eo pid,ppid,user,tty,cmd,%mem,%cpu --sort=-%cpu | head

DB time statistic : total elapsed processing time spent by database for a particular session.
DB CPU statistics : total cumulative time spent by Oracle for all sessions

Cumulative time spent by Oracle for all sessions :

select
stat_name,
Round(value/1000000) "Time (Sec)"
from v$sys_time_model
where stat_name = 'DB CPU';


CPU for any Oracle user session  :

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

Query for wait events Guy Harrison  :

COLUMN wait_class format a20
COLUMN name       format a30
COLUMN time_secs  format 999,999,999,999.99
COLUMN pct        format 99.99

SELECT   
   wait_class,
   NAME,
   ROUND (time_secs, 2) time_secs,
   ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
   (SELECT
      n.wait_class,
      e.event NAME,
      e.time_waited / 100 time_secs
    FROM
      v$system_event e,
      v$event_name n
    WHERE
       n.NAME = e.event AND n.wait_class <> 'Idle'
    AND
       time_waited > 0
    UNION
    SELECT
      'CPU',
      'server CPU',
      SUM (VALUE / 1000000) time_secs
    FROM
      v$sys_time_model
    WHERE
      stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY
   time_secs DESC;

I/O :


Logical Stdby :

RMAN :

Archivelog wrk :


Windows Cmd :



FRA :  Parameters : DB_RECOVERY_FILE_DEST_SIZE  , DB_RECOVERY_FILE_DEST
            Queries    : SELECT * FROM V$RECOVERY_FILE_DEST;   
                               SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
                       alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

                               select    name,  floor(space_limit / 1024 / 1024) "Size MB",  ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest;

SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM   V$RECOVERY_FILE_DEST A,  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY  SPACE_LIMIT,   SPACE_USED ,   SPACE_RECLAIMABLE ;

Check            : Archive destination is USE_DB_RECOVERY_FILE_DEST
Backup FRA :  BACKUP RECOVERY AREA;

DB Recovery :






















export schema

export full

Standby  database

script to auto tablespace addition

script to allow killing session for application;


auto back and check if successfull and restorable

In duplicate/ which file to go to which file on the second database.

Query running more than 1 hr

No. of session increasing.

No. of Active and   Inactgive sessions.

Sending awr report  and addm report every 4 hrs

High CPU utilization , High Memory utilization

Max amount of Temp Tablespace used ,  Max UNDO used

How to Restore CRS after accidentally run localconfig
How to Restore CRS after CRS diskgroup is lost
https://orasg.com/2015/10/04/how-to-restore-asm-based-ocr-after-complete-loss-of-the-crs-diskgroup-on-linuxunix-systems/

How to Rename  CRS diskgroup ( Containing Voting disk and spfile)