Daily

Rman checking through sql
*****************************

col STATUS format a9
col hrs format 999.99
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;

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

with backup size:

set serveroutput on
set linesize 150
set pagesize 300
col time_taken_display for a9
col output_bytes_display for a12

select session_key,
input_type,
status,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
order by session_key desc;


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

select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
from dba_objects where STATUS='INVALID';

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

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

delete archivelog all completed before 'sysdate-1';

RMAN>backup database plus archivelog;

starting backup...........

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


RMAN>delete archivelog all;


-----------------------------
select name from v$asm_diskgroup;

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

select name,total_mb,free_mb from v$asm_diskgroup;

-----------------
AWR:

select dbid from v$database;


DBID
----------
3191997282
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,1,51534, 51538 ));

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,2,51535, 51539 ));


output:copy and save as html file

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

select segment_name,segment_type,bytes/1024/1024/1024 from user_segments where tablespace_name='ABILLITY_TABLES'
order by 3 desc

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

select INSTANCE_NUMBER,INSTANCE_NAME from gv$instance;

------------------
select sum(bytes)/1024/1024/1024 from dba_segments;
-------------
select sum(bytes)/1024/1024/1024 from dba_data_files;

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

select opname,time_remaining from v$session_longops where sofar<>totalwork

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

shirnk datafile
==============================================================================
shirnk datafile
===========
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
=======================================

DATA FILE SHIRNK ...

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


column tablespace_name format a10
column file_name format a32
column file_mb format 9999990
column hwm_mb format 9999990
column used_mb format 9999990
column shrnk_mb format 9999990

break on report
compute sum of file_mb on report
compute sum of hwm_mb on report
compute sum of used_mb on report
compute sum of shrnk_mb on report

select a.*
, file_mb-hwm_mb shrnk_mb
from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_mb,
b.hwm*d.block_size/1024/1024 hwm_mb,
b.used*d.block_size/1024/1024 used_mb
from
dba_data_files a,
(select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
from dba_extents
group by file_id) b,
dba_tablespaces d
where a.file_id = b.file_id
and a.tablespace_name = d.tablespace_name
) a
order by a.tablespace_name,a.file_name;

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