DBA Daily useful commands

RMAN backup & Restore status
---------------------------

col START_TIME for a30

col END_TIME for a30

col STATUS format a30

col hrs format 999.99

set linesize 300

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,

elapsed_seconds/3600                   hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

set linesize 150

select SESSION_KEY, INPUT_TYPE,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,STATUS,to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,round(elapsed_seconds/60)+1 mins,round(input_bytes/1024/1024/1024) Size_Gigs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

set lines 300

select to_char(start_time,'DD/MM/RR HH24:MI:SS') ,to_char(end_time,'DD/MM/RR HH24:MI:SS') ,Input_bytes/(1024*1024*1024),output_bytes/(1024*1024*1024) from v$rman_Status where OUTPUT_DEVICE_TYPE='DISK'and object_type='DB INCR' order by end_time desc;

select output

from v$rman_output

where session_recid in (select session_recid from v$rman_status

where start_time > sysdate-2)

order by recid ;

set linesize 126

column Pct_Complete format 99.99

column client_info format a25

column sid format 999

column MB_PER_S format 999.99

select s.client_info,

l.sid,

l.serial#,

l.sofar,

l.totalwork,

round (l.sofar / l.totalwork*100,2) "Pct_Complete",

aio.MB_PER_S,

aio.LONG_WAIT_PCT

from v$session_longops l,

v$session s,

(select sid,

serial,

100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",

sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"

from v$backup_async_io

group by sid, serial) aio

where aio.sid = s.sid

and aio.serial = s.serial#

and l.opname like 'RMAN%'

and l.opname not like '%aggregate%'

and l.totalwork != 0

and l.sofar <> l.totalwork

and s.sid = l.sid

and s.serial# = l.serial#

order by 1;

TTITLE OFF

SET HEAD OFF

SELECT 'Throughput: '||

       ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||

       ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)

            FROM v$session_longops

            WHERE opname          LIKE 'RMAN: aggregate input'

              AND sofar           != TOTALWORK

              AND elapsed_seconds IS NOT NULL

       ),SUM(v.value     /1024/1024)),2) || ' Meg/sec'

 FROM gv$sesstat v, v$statname n, gv$session s

WHERE v.statistic# = n.statistic#

  AND n.name       = 'physical write total bytes'

  AND v.sid        = s.sid

  AND v.inst_id    = s.inst_id

  AND s.program LIKE 'rman@%'

GROUP BY n.name

/

SET HEAD ON

Dataguard archive log  Gap status

-------------------------------------------------

SELECT /*+ rule */ ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY;

Tablespace status

-------------------------------------

SELECT d.tablespace_name as name, 

NVL (dfs.BYTES, 0) AS freeSp,  

NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100, 3), 0) AS avPct, 

NVL (dfs.antall, 0) as chunks, 

ddf.autoextend_flag as autoext, 

NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, 

NVL (TRUNC (  (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.maxbytes)*100,3),0) as maxPct 

FROM dba_tablespaces d, 

(SELECT   tablespace_name, SUM (BYTES) BYTES, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs, 

(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag     FROM dba_data_files GROUP BY tablespace_name) ddf 

WHERE d.tablespace_name = ddf.tablespace_name(+) 

AND d.tablespace_name = dfs.tablespace_name(+) 

AND NOT (d.extent_management LIKE 'LOCAL' 

AND d.CONTENTS LIKE 'TEMPORARY') 

UNION ALL 

SELECT LTRIM (d.tablespace_name) as name, 

NVL (TRUNC (ddf.BYTES - NVL(dfs.BYTES, 0)), 0) AS freeSp, 

NVL (TRUNC ((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES * 100), 0) AS avPct, 

DECODE (NVL (TRUNC (((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES) * 100), 0),  0, 1,  100, 0,1) as chunks, 

ddf.autoextend_flag as autoext, 

NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, 

NVL (TRUNC ((NVL(dfs.BYTES, 0)) / (ddf.maxbytes)* 100,3),0) as maxPct 

FROM dba_tablespaces d, 

(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag 

             FROM dba_temp_files GROUP BY tablespace_name) ddf, 

(SELECT   ss.tablespace_name, SUM ( ss.used_blocks * ts.BLOCKSIZE) BYTES, COUNT (1) antall 

             FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs 

WHERE d.tablespace_name = ddf.tablespace_name(+) 

AND d.tablespace_name = dfs.tablespace_name(+) 

AND d.extent_management LIKE 'LOCAL' 

AND d.CONTENTS LIKE 'TEMPORARY' 

ORDER BY 1;

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes)/1048576) TotalSpace

from dba_data_files 

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments 

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

Temp tablespace usage

--------------------------------------

SET PAGESIZE 60

SET LINESIZE 300

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;

CREATE TABLESPACE / Add datafile / resize datafile 

----------------------------------------------

CREATE TABLESPACE "My_abs" DATAFILE 

SIZE 1073741824 

AUTOEXTEND ON NEXT 104857600 MAXSIZE 30720M 

LOGGING ONLINE PERMANENT BLOCKSIZE 32768 

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

ALTER TABLESPACE ARCH_data_TS ADD DATAFILE  SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

ALTER DATABASE DATAFILE '+DATA_DG/ABCP0009EC/DATAFILE/arch_lobs_ts.305.1003252759' resize 30G;

set long 99999999

select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

ASM Disk and Diskgroup size

----------------------------

col path for a30

set linesize 300

select name,path,os_mb,header_Status from v$ASM_DISK;

select INST_ID,name,path from gv$ASM_DISk order by name,INST_ID;

select name,total_mb/1024,free_mb/1024 from v$ASM_DISKGROUP;

select name,path,os_mb,header_Status from v$ASM_DISK where header_Status='CANDIDATE';

ASM Rebalance status

---------------------

set linesize 300

select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION ;

DB directory name and path

--------------------------

set linesize 300

col OWNER for a20

col DIRECTORY_NAME for a20

col DIRECTORY_PATH for a50

select * from  dba_directories;

AWR interval and retention change

---------------------------------

Every 30 min and retain for 15 days (21600 minutes)

exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 21600) ;

AWR Manual snapshot

--------------------

EXEC dbms_workload_repository.create_snapshot;

redo log member size

--------------------

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

GATHER_TABLE_STATS & GATHER_SCHEMA_STATS

----------------------------------------

BEGIN

SYS.DBMS_STATS.GATHER_TABLE_STATS (

OwnName => 'MyOwner',

TabName => 'Mytable',

Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,

Method_Opt => 'FOR ALL COLUMNS SIZE AUTO',

Degree => 4,

Cascade => TRUE,

--  No_Invalidate=> FALSE

);

END;

/

BEGIN

  SYS.DBMS_STATS.GATHER_SCHEMA_STATS (

     OwnName           => 'MyOwner'

    ,Granularity       => 'DEFAULT'

    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO'

    ,Degree            => 4

    ,Cascade           => TRUE

--  ,No_Invalidate     => FALSE

);

END;

/

No_Invalidate     => FALSE

This option will dictate that a change in statistics always invalidates the dependent sql cursors immediately upon a change to the statistics.

Table size in Database / Schema size
-------------------------------------

set linesize 300

col owner for a30

col table_name for a30

select * from (

SELECT

   owner, 

   table_name, 

   TRUNC(sum(bytes)/1024/1024) "SIZE MB"

   FROM

(SELECT segment_name table_name, owner, bytes

 FROM dba_segments

 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

 UNION ALL

 SELECT i.table_name, i.owner, s.bytes

 FROM dba_indexes i, dba_segments s

 WHERE s.segment_name = i.index_name

 AND   s.owner = i.owner

 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

 UNION ALL

 SELECT l.table_name, l.owner, s.bytes

 FROM dba_lobs l, dba_segments s

 WHERE s.segment_name = l.segment_name

 AND   s.owner = l.owner

 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')

 UNION ALL

 SELECT l.table_name, l.owner, s.bytes

 FROM dba_lobs l, dba_segments s

 WHERE s.segment_name = l.index_name

 AND   s.owner = l.owner

 AND   s.segment_type = 'LOBINDEX')

--WHERE table_name='***********'

GROUP BY table_name, owner

ORDER BY SUM(bytes) desc ) where rownum<10 font="">

col owner for a30

col tablespace_name for a30

SELECT owner,SUM(BYTES)/1024/1024/1024 "GB" FROM DBA_SEGMENTS WHERE OWNER in ('ABC') group by owner;

SELECT tablespace_name, Sum(bytes)/1024/1024/1024 "GB"  FROM dba_segments WHERE OWNER in ('ABC') group by  tablespace_name;

Hidden parameter check

-----------------------

SELECT x.ksppinm "Parameter",

       Y.ksppstvl "Session Value",

       Z.ksppstvl "Instance Value"

FROM   x$ksppi X,

       x$ksppcv Y,

       x$ksppsv Z

WHERE  x.indx = Y.indx

AND    x.indx = z.indx

AND    x.ksppinm LIKE '/_%' escape '/' and x.ksppinm='_optimizer_cost_based_transformation'

order by x.ksppinm;

Service creation in RAC
--------------------------

srvctl add service -d MYDB -s My_SRV -r MYDB1,MYDB2

srvctl start serviced MYDB -s My_SRV

srvctl status service -d MYDB -s My_SRV

Blocking session

-----------------

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE

|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' Module=' || S1.MODULE || ') IS BLOCKING '

|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' Module=' || S2.MODULE || ') ' AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID

AND L1.BLOCK > 0 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

Kill session for users

-----------------------

BEGIN

  FOR r IN (select inst_id,sid,serial# from gv$session where username = 'Myuser')

  LOOP

    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 

      || ',' || r.serial# ||',@'|| r.inst_id || '''';

  END LOOP;

END;

/

Dynamic SQL Query

-----------------------

select 'create synonym '||owner||'.'||SYNONYM_NAME||' '||' FOR '|| TABLE_OWNER||'.'||TABLE_NAME||' ;' FROM DBA_SYNONYMS WHERE OWNER like 'ABC%' and table_owner='MY_ABC';

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || tablespace_name || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2')

SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB ('||COLUMN_NAME||') STORE AS SECUREFILE '||SEGMENT_NAME||CHR(10)||'(TABLESPACE My_lob);' SQL_STATEMENTS FROM DBA_LOBS WHERE OWNER like 'OWNER1%'; 

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE Mytablespace1 ' ||  ';' from DBA_INDEXES WHERE OWNER='OWNER1' and  tablespace_name in ('Mytablespace') ;

Database Properties

-----------------------

col PROPERTY_VALUE for a50

set linesize 300

select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like 'NLS%';