Oracle Database Administration

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; select THREAD#, trunc(first_time) as "DATE"  , count(1) num  , sum(blocks*block_size)/1024 as KB  , sum(blocks*block_size)/1024/1024 as MB  , sum(blocks*block_size)/1024/1024/1024 as GB  from v$archived_log  where first_time > trunc(sysdate-10)  group by thread#, trunc(first_time)  order by 2,1;  find the size of the archivelog  select decode(grouping (trunc(COMPLETION_TIME)),1,'TOTAL',TRUNC(COMPLETION_TIME)) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024/1024 SIZE_MB  from V$ARCHIVED_LOG group by cube (trunc (COMPLETION_TIME)) order by 1 Lists the volume of archived redo by hour for the specified day @redo_by_hour (day 0=Today, 1=Yesterday etc.) SET VERIFY OFF PAGESIZE 30 WITH hours AS ( SELECT TRUNC(SYSDATE) - &1 + ((level-1)/24) AS hours FROM dual CONNECT BY level < = 24 ) SELECT h.hours AS date_hour, ROUND(SUM(blocks * block_si...

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...