Database Name:
--------------
Select name,open_mode from v$database;
Database Startup Time:
----------------------
Select to_char (startup_time, 'HH24:MI DD-MON-YY') "StartupTime" from v$instance;
DB NAME, INSTANCE, OPEN MODE AND LOG MODE Details:
--------------------------------------------------
colu name format a20
colu StartTime format a30
colu SysDateTime format a30
set linesize 150
select to_char(startup_time,'DD-MM-YYYY HH24:MI:SS') StartTime, to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') SysDateTime, name,instance_name,log_mode, open_mode from dual, v$database, v$instance;
Database Size Information:
--------------------------
SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union
SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_SEGMENTS union
SELECT 'SGA Size (GB):' || TO_CHAR(SUM(VALUE)/1024/1024/1024,9999999.99) FROM V$SGA;
SELECT name,a.size_IN_GB,SYSDATE from V$DATABASE vd, (Select sum(bytes)/1024/1024/1024 size_IN_GB from dba_segments WHERE tablespace_name not like '%UNDO%') a;
Database Sysdate:
-----------------
Select to_char(sysdate,'HH24:MI DD-MON-YY') "Date" from v$instance;
ASM Candidate Disk Status:
--------------------------
set linesize 400
col path for a35
select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;
select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;
To Check Fregmented space:
==========================
col owner for a15
col table_name for a30
col TOTAL_SIZE for a25
col ACTUAL_SIZE for a20
col FRAGMENTED_SPACE for a25
select owner,table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from dba_tables WHERE owner in ('PDMUSER_OOTB','PDMUSER','WBMSTAGING_MIG', 'WBMSTAGING') and AVG_ROW_LEN > 0 and NUM_ROWS > 0 order by 2, 6;
Invalid Objects:
----------------
SELECT COUNT(1) FROM DBA_OBJECTS WHERE STATUS='INVALID';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
set linesize 400 pages 1000
col owner for a25
col object_name for a30
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS='INVALID';
Invalid Indexes:
----------------
set linesize 400 pages 1000
set owner for a25
col table_name for a30
col index_name for a35
Select owner,table_name,index_name,status from dba_indexes where status='INVALID';
Running Jobs:
-------------
Select * from dba_jobs_running;
Select /*+ rule */ * from dba_jobs_running;(9i Version)
Broken Jobs:
------------
Select count(*), broken, schema_USER, FAILURES from user_jobs where broken = 'Y' and LAST_DATE <= SYSDATE - 1/24 group by broken, schema_USER, FAILURES;
Select count(*), broken, schema_USER, FAILURES from user_jobs where broken = 'N' and LAST_DATE <= SYSDATE - 1/24 group by broken, schema_USER, FAILURES;
Non Oracle Users or Application Users:
--------------------------------------
set linesize 400
col username for a40
col ORACLE_MAINTAINED for a25
select USERNAME,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='N' order by 1;
Allocated, Free and Used space in each of the datafiles:
--------------------------------------------------------
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Kill Inactive sessions:
-----------------------
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username='DCSUSR' and status='INACTIVE';
Tablespace Status:
-----------------
set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
column PCT_FREE format 999.99
select round(undo_alloc.allocMB,2) allocMB, round(undo_free.freeMB,2) free, round(undo_free.active,2) active,
round(undo_free.expired,2) expired,round(undo_free.unexpired,2) unexpired,
ltrim( round(( (undo_free.freeMB + undo_free.expired)/undo_alloc.allocMB) * 100,2)) as PCT_FREE
from
(
select tablespace_name, sum(bytes)/(1024*1024) as freeMB,
(select sum(bytes)/(1024*1024) from dba_undo_extents where status = 'ACTIVE' ) as active,
(select sum(bytes)/(1024*1024) from dba_undo_extents where status = 'EXPIRED' ) as expired,
(select sum(bytes)/(1024*1024) from dba_undo_extents where status = 'UNEXPIRED' ) as unexpired
from dba_free_space
where tablespace_name = 'UNDO'
group by tablespace_name
) undo_free,
(
select t.name, sum(d.bytes)/(1024*1024) as allocMB
from v$tablespace t, v$datafile d
where t.name = 'UNDO'
and t.ts# = d.ts#
group by t.name
) undo_alloc
where undo_free.tablespace_name (+) = undo_alloc.name;
Tablespace Wise Growth on Daily basis:
======================================
SELECT TO_DATE(TO_CHAR (snpshot.begin_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’) daywise
, dhts.tsname tablespacename
, max(round((dhtsu.tablespace_size* dtblspc.block_size )/(1024*1024*1024),2) ) maximum_allocsize_GB
, max(round((dhtsu.tablespace_usedsize* dtblspc.block_size )/(1024*1024*1024),2)) maximum_usedsize_GB
FROM DBA_HIST_TBSPC_SPACE_USAGE dhtsu
, DBA_HIST_TABLESPACE_STAT dhts
, DBA_HIST_SNAPSHOT snpshot
, DBA_TABLESPACES dtblspc
WHERE dhtsu.tablespace_id= dhts.ts#
AND dhtsu.snap_id = snpshot.snap_id
AND dhts.tsname = dtblspc.tablespace_name
AND dhts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
GROUP BY TO_DATE(TO_CHAR (snpshot.begin_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’)
, dhts.tsname
ORDER BY dhts.tsname
, daywise;
Daily Object’s Growth with respect to Tablespace in last X days:
================================================================
SELECT * FROM
(SELECT ds.tablespace_name
, ds.segment_name
, TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’) daywise
, SUM(dhss.space_used_delta) / 1024 / 1024 “Space used (MB)”
, AVG(ds.bytes) / 1024 / 1024 “Total Object Size (MB)”
, ROUND(SUM(dhss.space_used_delta) / SUM(ds.bytes) * 100, 2) “Percent of Total Disk Usage”
FROM dba_hist_snapshot snpshot
, dba_hist_seg_stat dhss
, dba_objects dobj
, dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE) – &pastdays
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.segment_name = ‘TEST_TAB1′
GROUP BY ds.tablespace_name,ds.segment_name,TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’)
ORDER BY ds.tablespace_name,ds.segment_name,TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’));
Object’s Growth in Last X days:
===============================
SELECT ds.tablespace_name
, ds.segment_name "object name"
, dobj.object_type
, ROUND(SUM(dhss.space_used_delta) / 1024 / 1024 / 1024,2) "Growth (GB)"
FROM dba_hist_snapshot snpshot
, dba_hist_seg_stat dhss
, dba_objects dobj
, dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE-5)
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
GROUP BY ds.tablespace_name,ds.segment_name,dobj.object_type
ORDER BY 3 ASC;
Space Related Views:
====================
There are few important views provided by Oracle which helps us track the growth. These can be listed as:
DBA_SEGMENTS
DBA_TABLESPACES
DBA_HIST_SEG_STAT
DBA_HIST_TABLESPACE_STAT
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_SNAPSHOT
DBMS_SPACE
Tablespace Queryr-2:
===================
SET PAGESIZE 50000
SET LINESIZE 400
COLUMN NAME FORMAT A35
COLUMN DATAFILES FORMAT A50
COLUMN TABLESPACE FORMAT A25
COLUMN OWNER FORMAT A15
Prompt TOTAL AND USED SPACE
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;
set pages 1000
set linesize 132
column pct_used format 999.9 heading " Used (%)"
column ts_name format a41 heading "Name"
column Mbytes format 999999999 heading "Size (MB)"
column used format 999999999 heading "Used"
column free format 999999999 heading "Free"
column free format 999999999 heading "PER_FREE"
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name
, mbytes_alloc mbytes
, mbytes_alloc-nvl(mbytes_free,0) used
, nvl(mbytes_free,0) free
, round(nvl(mbytes_free,0)/(mbytes_alloc)*100,2) Per_free
, ((mbytes_alloc-nvl(mbytes_free,0))/mbytes_alloc)*100 pct_used
from (select sum(bytes)/1024/1024 Mbytes_free
, max(bytes)/1024/1024 largest
, tablespace_name
from dba_free_space
group by tablespace_name) a
, (select sum(bytes)/1024/1024 Mbytes_alloc
, tablespace_name
from dba_data_files
group by tablespace_name) b
where a.tablespace_name (+) = b.tablespace_name
order by 5
/
RMAN full, Incremental and Archivelog backup Details:
-----------------------------------------------------
Set linesize 400
set pages 4000
col output_bytes_display form a15
col input_bytes_display form a15
col STATUS form a25
col time_taken_display form a10
col INPUT_BYTES_PER_SEC_DISPLAY form a15
col OUTPUT_BYTES_PER_SEC_DISPLAY form a17
col input_rate_per_sec form a20
col output_rate_per_sec form a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
input_bytes_display,
output_bytes_display,
--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;
Database Hit Ratio:
-------------------
SELECT 1- (P3.value - P4.value - P5.value) / (P1.value + P2.value) "HIT RATIO"
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3, v$sysstat P4, V$sysstat P5
WHERE P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads'
AND P4.name = 'physical reads direct'
AND P5.name = 'physical reads direct (lob)';
Blocking sessions:
------------------
select blocking_session,sid,serial#,wait_class,seconds_in_wait from gv$session where blocking_session is not NULL order by blocking_session;
SQL> select * from dba_blockers;
no rows selected
SQL> select * from v$session_blockers;
Session Details :
-----------------
col username for a15
col program for a15
select USERNAME,PROGRAM,SID,SESSION_SERIAL#,ELAPSED_TIME/1000000 "elapsed time sec", sql_id from v$sql_monitor where status='EXECUTING';
select sid,username,status,schemaname,osuser,process,machine,program,sql_id,prev_sql_id,module from v$session where sid='591';
select sid,username,status,schemaname,osuser,process,machine,program,sql_id,prev_sql_id,module from v$session where schemaname not in('SYS','DBSNMP')
Row Level Blocking:
-------------------
with w as (
select
chain_id,rownum n,level l
,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' "session"
,lpad(' ',level,' ')||w.wait_event_text ||
case
when w.wait_event_text like 'enq: TM%' then
' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive')
||( select ' on '||object_type||' "'||owner||'"."'||object_name||'" ' from all_objects where object_id=w.p2 )
when w.wait_event_text like 'enq: TX%' then
(
select ' on '||object_type||' "'||owner||'"."'||object_name||'" on rowid '
||dbms_rowid.rowid_create(1,data_object_id,relative_fno,w.row_wait_block#,w.row_wait_row#)
from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id
)
end "wait event"
, w.in_wait_secs/60 "minutes"
, s.username , s.program,s.client_identifier
from v$wait_chains w join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance)
connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance
start with w.blocker_sid is null
)
select * from w where chain_id in (select chain_id from w group by chain_id having max("minutes") >= 1 and max(l)>1 )
order by n;
Long Session Details:
---------------------
set lines 200 pages 200
col sid for 9999
col serial# for 999999
col spid for 999999
col status for a10
col username for a10
col TIM for 9999.99
col module for a16
col program for a20
col osuser for a15
select s.sid,s.serial#,p.spid,s.status,s.username,s.last_call_et/60 tim,
s.module,s.program,s.sql_hash_value,s.osuser
from v$session s,v$process p
where s.type<>'BACKGROUND'
and s.paddr=p.addr
and s.last_call_et/60>10
and s.program not like '%CJQ%'
and s.program not like '%QMN%'
order by 4,6;
Patch Details:
--------------
set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;
Tablespace Reshrink:
--------------------
col cmd for a100
set linesize 400
select bytes/1024/1024/1024 real_size, ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) shrinked_size,
bytes/1024/1024/1024-ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) released_size,
'alter database datafile '|| ''''||file_name||'''' || ' resize ' || ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) || 'G;' cmd from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b Where tablespace_name='PROMPT_INDEX' and
a.file_id = b.file_id(+) and ceil(blocks*8192/1024/1024/1024)- ceil((nvl(hwm,1)* 8192)/1024/1024/1024 ) > 0;
59395600
Database Last Analyse:
----------------------
select owner,table_name,last_analyzed FROM dba_tables ORDER BY LAST_ANALYZED
SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables ORDER BY LAST_ANALYZED;
RMAN Job Progress:
------------------
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
Directory Details:
------------------
set lines 400 pages 100
col OWNER for a10
col DIRECTORY_PATH for a60
col DIRECTORY_NAME for a25
select * from dba_directories;
Schema Objects:
---------------
col owner for a30
col OBJECT_TYPE for a30
select owner,object_type,count(object_type) from dba_objects where owner='&USERNAME' group by owner,object_type --order by Object_type asc;
col owner for a30
col OBJECT_TYPE for a30
select owner,object_type,count(object_type) from dba_objects where owner='USERNAME' group by owner,object_type --order by Object_type asc;
Schema Size Group By Segment:
-----------------------------
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;
Realtime Monitoring:
--------------------
set lines 250
column event format a20 trunc
column osuser format a8 trunc
column logon_time format a19 trunc
column start_time format a17 trunc
column machine format a15 trunc
column sid format 99999
column cno format 999
column serial# format 99999 heading sno
column username format a10 trunc
column program format a20 trunc
column owner format a12 trunc
column object_name format a24 trunc
column LOCKED_MODE format 9999 heading lmde
column name for a15 trunc
select s.sid,s.serial#,s.program,s.username,s.osuser,s.machine,s.sql_id,s.sql_child_number cno,s.prev_sql_id,s.event,
to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time, t.start_time, t.used_urec, t.PHY_IO, t.name
from v$session s,v$transaction t
where s.saddr=t.ses_addr
order by t.start_time;
set lines 250
column event format a20 trunc
column osuser format a8 trunc
column logon_time format a19 trunc
column start_time format a17 trunc
column machine format a15 trunc
column sid format 99999
column cno format 999
column serial# format 99999 heading sno
column username format a10 trunc
column program format a20 trunc
column owner format a12 trunc
column object_name format a24 trunc
column LOCKED_MODE format 9999 heading lmde
column name for a15 trunc
select s.sid,s.serial#,s.program,s.username,s.osuser,s.machine,s.sql_id,s.sql_child_number cno,s.prev_sql_id,s.event,
to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time, t.start_time, t.used_urec, t.PHY_IO, t.name
from v$session s,v$transaction t
where s.saddr=t.ses_addr
order by t.start_time;
Archive Generation:
-------------------
set line 2000
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
where to_date(first_time) > sysdate-10
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time);
Temp Usage:
----------
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
Archive Log History:
--------------------
Set linesize 400;
Select thread#, sequence#, first_change#, to_char(first_time,'dd/mm/yyyy:hh:mi:ss') FirstTime,
next_change#, applied, to_char(completion_time,'dd/mm/yyyy:hh:mi:ss') CompTime from v$archived_log;
Select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
Tracefile deletion:
-------------------
find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/*.aud -type f -mtime +60 -exec rm {} \;
If we got error like "-bash: /usr/bin/find: Argument list too long" use below command to delete
find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.aud' -mtime +10 -exec ls -ltr {} \;
find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.aud' -mtime +90 -exec rm {} \;
Verify Rollback Segment:
------------------------
Status should be ONLINE, not OFFLINE or FULL…. For names and status of ALL rollback segment,
SELECT TABLESPACE_NAME, SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;
Optimizer Statistics:
---------------------
SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables
Where TO_CHAR (last_analyzed, 'yyyy/mm/dd')> '&date' ORDER BY LAST_ANALYZED;
SELECT DISTINCT (TO_CHAR (last_analyzed, 'yyyy/mm/dd')) as last_analyzed FROM dba_tables
ORDER BY LAST_ANALYZED;
Database Growth:
----------------
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" ,
round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" ,
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile
union all select bytes from v$tempfile union all select bytes from v$log) used ,
(select sum(bytes) as p from dba_free_space) free group by free.p;
Locked Objects Identification And Resolution:
---------------------------------------------
STEP-1
Select oracle_username, os_user_name,locked_mode,object_name,
Object_type from v$locked_object a, dba_objects b
where a.object_id=b.object_id order by a.oracle_username;
STEP-2 (TO FIND THE SID OWNER OF LOCKED OBJECTS)
Select Sid, owner from v$access
where object='NAME OF THE OBJECT';
STEP-3(TO FIND THE SERIAL# AND STATUS)
Select username,sid,serial#,status from v$session where sid= ;
STEP-4( TO KILL THE SESSION WHICH HAVE BEEN LOCKED THE SESSION)
Alter system kill session 'sid,serial#';
Fregmentation:
--------------
col TOTAL_SIZE for a25
col ACTUAL_SIZE for a25
col FRAGMENTED_SPACE for a25
col TABLE_NAME for a30
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from dba_tables WHERE owner='&towner' and AVG_ROW_LEN > 0 and NUM_ROWS > 0;
Full Table Scan Finding:
------------------------
SELECT sp.object_owner, sp.object_name,
(SELECT sql_text
FROM v$sqlarea sa
WHERE sa.address = sp.address
AND sa.hash_value = sp.hash_value) sqltext,
(SELECT executions
FROM v$sqlarea sa
WHERE sa.address = sp.address
AND sa.hash_value = sp.hash_value) no_of_full_scans,
(SELECT LPAD (NVL (TRIM (TO_CHAR (num_rows)), ' '),
15,
' '
)
|| ' | '
|| LPAD (NVL (TRIM (TO_CHAR (blocks)), ' '), 15, ' ')
|| ' | '
|| BUFFER_POOL
FROM dba_tables
WHERE table_name = sp.object_name AND owner = sp.object_owner)
"rows|blocks|pool"
FROM v$sql_plan sp
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_owner NOT IN ('SYS','SYSTEM')
ORDER BY 1, 2;
Temp query:
----------
SELECT SUM(total_blocks*8192/1024/1024) Occupied,
SUM(used_blocks*8192/1024/1024) Using,
SUM(free_blocks*8192/1024/1024) Free
FROM gv$sort_segment
-- WHERE tablespace_name = upper('&temp_tablespace_name');
Temp Out Of Space:
------------------
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Select tablespace_name, sum(bytes_used), sum(bytes_free) from v$temp_space_header group by tablespace_name;
Temp utilization by session:
----------------------------
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
To See The Which Tables Need To Shrink/Re-org:
----------------------------------------------
select tablespace_name, segment_name,segment_type, partition_name,recommendations,c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc;
Find PID from SID:
------------------
SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = &sid;
Data guard:
-----------
show parameter db_name;
archive log list;
select max(sequence#),applied from v$archived_log group by applied;
select sequence#,applied from v$archived_log order by 1;
select count(*) from v$archived_log where applied='NO' order by 1;
select * from v$archive_gap;
select thread#,sequence#,applied from v$archived_log;
Select process, status, thread#, sequence#, block# from v$managed_standby;
Select name,open_mode,database_role,protection_mode,current_scn from v$database;
set linesize 400;
set echo off;
archive log list;
Set linesize 400;
Select thread#, max(sequence#) as "last_applied_log" from v$log_history group by thread#;
Select status as Dest_1_Status, error from v$archive_dest where dest_id=1;
Select status as Dest_3_Status, error from v$archive_dest where dest_id=3;
Select process, status, thread#, sequence#, block# from v$managed_standby;
Findout calling which procedure:
--------------------------------
select 'CALLED PLSQL', vs.username, d_o.object_name -- whatever info you need
from dba_objects d_o
inner join
v$session vs
on d_o.object_id = vs.plsql_entry_object_id
union all
select 'CURRENT PLSQL', vs.username, d_o.object_name
from dba_objects d_o
inner join
v$session vs
on d_o.object_id = vs.plsql_object_id;
To Find Blocking Session History:
---------------------------------
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('24/04/2014 11:00', 'dd/mm/yyyy hh24:mi')
and to_date('24/04/2014 23:50', 'dd/mm/yyyy hh24:mi');
Datafile Free Size:
-------------------
col FILE_NAME format a60
SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+) and df.tablespace_name='&TABLESPACE_NAME'
GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes
UNION ALL
select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) - (user_bytes/1024/1024)) "free_space_mb" from dba_temp_files;
FRA:
Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it̢۪s usage, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).
Check and confirm FRA location with below parameter
SQL>Select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
------ --------- -------------
ORADB1 READ WRITE PRIMARY
SQL> Show parameter db_recovery_file_dest;
NAME TYPE VALUE
-------------------------- ----------- ------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 120G
Check FRA usage like Total, used and free space with below select statement
SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;
TOTAL_GB USED_GB
-------- -------
120 88.8515625
Check the fra usage in percentage with below select statement
SQL>col NAME for a15
Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 74.043
Check the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).
Select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 73.53 73.53 282 0
BACKUP PIECE .51 .51 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
or
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 73.53 73.53 282 0
BACKUP PIECE .51 .51 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
Check Size, usage, Reclaimable space used
SQL>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 ;
FLASH_IN_GB FLASH_USED_IN_GB FLASH_RECLAIMABLE_GB PERCENT_OF_SPACE_USED
----------- ---------------- -------------------- ---------------------
120 88.85 88.85 74.04
As per above output FRA usage 74.04%
We have three scenarios to fix FRA issue.
1. We can increase FRA space if we have sufficient free disk space :
Check FRA disk free space with below select statement
SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;
NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1000 303.550781
FRA 200 109.339844
REDO 60 47.4726563
or
[grid@Ora11gOra11g ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 1024000 310836 0 310836 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 204800 111964 0 111964 0 N FRA/
MOUNTED EXTERN N 512 512 4096 4194304 61440 48612 0 48612 0 N REDO/
As per above output we have free space approx 109GB, So we are good to increase FRA with below syntax
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=140G scope=both;
System altered.
Now check FRA Usage after increasing.
SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;
TOTAL_GB USED_GB
---------- ----------
140 88.8515625
SQL> Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 62.043
2. Change the FRA to a new location, new archives will be created to this new location:
SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/backup/archive' scope =both;
System Altered.
Now check FRA location after changing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/archive
db_recovery_file_dest_size big integer 120G
3. Deleting Old archive logfiles to reclaim FRA space.
Check backup of database+archivelogs backup atleast for one week with below select statement, If all backups are successfull and valid backups then we can remove old archive logfiles to reclaim space.
set linesize 400
col output_bytes_display form a15
col input_bytes_display form a15
col STATUS form a25
col time_taken_display form a10
col INPUT_BYTES_PER_SEC_DISPLAY form a15
col OUTPUT_BYTES_PER_SEC_DISPLAY form a17
col input_rate_per_sec form a20
col output_rate_per_sec form a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
input_bytes_display,
output_bytes_display,
--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;
As per above output backup is successfull, Connect rman prompt and remove old archive log files
RMAN>delete noprompt archivelog until time 'sysdate-7' backed up 1 times to device type disk;
or
Manually delete from ASM command prompt
Note:Not recomended approach since by deleting manually it won't release space we need to run (CROSSCHECK ARCHIVELOG ALL command from RMAN to release space)
ASMCMD> pwd
+FRA/ORADBA/ARCHIVELOG
ASMCMD> ls
2020_02_19/
2020_02_20/
2020_02_21/
2020_02_22/
2020_02_23/
2020_02_24/
2020_02_25/
2020_02_26/
ASMCMD>cd 2020_02_19/
ASMCMD>rm *.arc
ASMCMD> ..
ASMCMD> ls
2020_02_20/
2020_02_21/
2020_02_22/
2020_02_23/
2020_02_24/
2020_02_25/
2020_02_26/
After removing old archive logfile with retention of 7 days check current FRA usage.
SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;
TOTAL_GB USED_GB
---------- ----------
120 73.8515625
Check the fra usage in percentage with below select statement
SQL> Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 62.043
Drop Disk :
SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;
NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1300 58.125
FRA 400 393.566406
REDO 210 204.66796875
SQL> set linesize 400
col path for a35
select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;
select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;SQL> SQL>
NAME GROUP_NUMBER DISK_NUMBER HEADER_STATU MODE_ST STATE OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024 PATH
------------------------------ ------------ ----------- ------------ ------- -------- ---------- ------------- ------------ -----------------------------------
REDO_0001 3 1 MEMBER ONLINE NORMAL 200 200 194.945313 /dev/oracleasm/asm11
DATA_0006 1 6 MEMBER ONLINE NORMAL 200 200 8.94921875 /dev/oracleasm/asm10
REDO_0000 3 0 MEMBER ONLINE NORMAL 10 10 9.7109375 /dev/oracleasm/asm09
DATA_0000 1 0 MEMBER ONLINE NORMAL 100 100 4.48046875 /dev/oracleasm/asm01
FRA_0001 2 1 MEMBER ONLINE NORMAL 200 200 196.773438 /dev/oracleasm/asm05
FRA_0000 2 0 MEMBER ONLINE NORMAL 200 200 196.792969 /dev/oracleasm/asm04
DATA_0004 1 4 MEMBER ONLINE NORMAL 200 200 8.9296875 /dev/oracleasm/asm07
DATA_0002 1 2 MEMBER ONLINE NORMAL 200 200 8.96484375 /dev/oracleasm/asm03
DATA_0001 1 1 MEMBER ONLINE NORMAL 200 200 8.9296875 /dev/oracleasm/asm02
DATA_0003 1 3 MEMBER ONLINE NORMAL 200 200 8.93359375 /dev/oracleasm/asm06
DATA_0005 1 5 MEMBER ONLINE NORMAL 200 200 8.9375 /dev/oracleasm/asm08
11 rows selected.
NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1300 58.125
FRA 400 393.566406
REDO 210 204.65625
SQL> alter diskgroup REDO drop disk REDO_0001;
Diskgroup altered.
SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;
NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1300 58.125
FRA 400 393.566406
REDO 10 4.66796875
Find max changes during high archive:
-------------------------------------
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
sum(db_block_changes_delta) as maxchages
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2020_03_20 09','YYYY_MM_DD HH24')
AND to_date('2020_03_20 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by maxchages asc;
UNDO History:
-------------
select sql_text from dba_hist_sqltext where sql_id in (select distinct maxquerysqlid from (select * from (select snap_id, maxquerylen, undoblks, maxquerysqlid,
to_char(begin_time,?yyyy/mm/dd hh24:mi?) begin, to_char(end_time,?yyyy/mm/dd hh24:mi?) end from dba_hist_undostat order by undoblks desc, maxquerylen desc)
where rownum<11
)
);
RMAN FULL, INCRMENTAL AND ARCHIVELOG BACKUP
set linesize 400
col START_TIME for a20
col END_TIME for a20
col output_bytes_display form a15
col input_bytes_display form a15
col STATUS form a25
col time_taken_display form a10
col INPUT_BYTES_PER_SEC_DISPLAY form a15
col OUTPUT_BYTES_PER_SEC_DISPLAY form a17
col input_rate_per_sec form a20
col output_rate_per_sec form a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
input_bytes_display,
output_bytes_display,
--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;
SQL taking more PGA
********************
select SQL_ID,round(PGA_MB,1) PGA_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_PGA_MB) PGA_MB ,(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,rank() over(order by sum(DELTA_PGA_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,PGA_ALLOCATED,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
from
dba_hist_active_sess_history
where
IS_SQLID_CURRENT='Y'
and sample_time between to_date('11-jul-2017 16:15','dd-mon-yyyy hh24:mi') and to_date('11-jul-2017 16:30','dd-mon-yyyy hh24:mi')
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < (11)
order by rank;
Sizing UNDO
***********
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM dba_hist_undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
USEFUL Queries : proc to find number of record in each table
************************************************************
set serveroutput on
spool a.txt
declare
lv_sql varchar2(500);
lv_count number;
lv_owner varchar(30);
lv_tname varchar(30);
cursor c1 is select owner,table_name from dba_tables where owner like 'ORADBA%';
begin
open c1;
loop
fetch c1 into lv_owner,lv_tname;
exit when c1%notfound;
-- this query to get the number of record from each table
lv_sql:='select count(*) from ' || lv_owner || '.' || lv_tname;
execute immediate lv_sql INTO lv_count;
dbms_output.put_line ( lv_owner || ',' || lv_tname || ',' || lv_count);
end loop;
close c1;
end;
/
spool off
golden gate commands
--------------------
ps -ef|grep mgr.prm
ggsci
ggsci>info all
STOP EXTRACT group_name
START EXTRACT group_name
start and stop the golden gate process in cluster
*************************************************
D:\app\xag\bin>agctl start goldengate GGATE
D:\app\xag\bin>agctl stop goldengate GGATE
this is the command
and you have to check crsctl stat res -t
where
xag.GGATE-vip.vip
1 ONLINE ONLINE e75l0265 STABLE
this is running
gg will start only in the node this is running
to relocate the vip to node2 for gg
***********************************
agctl relocate goldengate GGATE --node e75l0067
run this from D:\app\xag\bin folder
rman
****
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup as compressed backupset full database format 'E:\RMANBACKUP\ORADBADB\%d_%U_%T_%s_%p_Full';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup format 'E:\RMANBACKUP\ORADBADB\%d_%U_%T_ARCH' archivelog all;
backup format 'E:\RMANBACKUP\ORADBADB\%d_%U_%T_CTL' current controlfile;
release channel c1;
release channel c2;
}
run
{
allocate channel device type sbt
backup plus archivelog
}
RMAN> crosscheck archivelog all;
RMAN> delete noprompt archivelog like '%' backed up 1 times to device type sbt_tape ;
Recovery catalog
****************
on recovery catalog database:
*****************************
CREATE USER ORALNX_ORADBAMAT IDENTIFIED BY rcatpass TEMPORARY TABLESPACE temp DEFAULT TABLESPACE RMAN_LINUX account unlock;
GRANT RECOVERY_CATALOG_OWNER TO ORALNX_ORADBAMAT;
GRANT UNLIMITED TABLESPACE to ORALNX_ORADBAMAT;
grant connect,resource to ORALNX_ORADBAMAT;
exit
Rman>CONNECT CATALOG ORALNX_ORADBAMAT/rcatpass@rmandb
CREATE CATALOG;
on source database:
******************
RMAN> CONNECT catalog ORALNX_ORADBAMAT/rcatpass@rmandb
recovery catalog database Password: password
connected to recovery catalog database
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
https://heliosguneserol.com/2018/03/22/how-to-monitor-data-pump-job-impdp/
