Oracle Database Administration

On the RMAN tool, delete archivelog older than a week. # rman target / ; RMAN> delete archivelog until time 'sysdate-7'; or RMAN> delete force archivelog all; or RMAN> delete backup completed before 'sysdate-3'; Restore status restore status set pages 9999 lines 500 set numformat 99999.99 set trim on  set trims on alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS'; select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,    sysdate + TIME_REMAINING/3600/24 end_at     from v$session_longops     where totalwork > sofar     AND opname NOT LIKE '%aggregate%'    AND opname like 'RMAN%' RMAN backup job details for 'n' number of days:- ========================================= Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status. Note : - Enter the number of days required for status report, for 1 day backup status report provide i...

Session related Queries Last/Latest Running SQL ----------------------- set pages 50000 lines 32767 col "Last SQL" for 100 SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL" FROM gv$session s, gv$sqlarea t WHERE s.sql_address =t.address AND s.sql_hash_value =t.hash_value / Current Running SQLs -------------------- set pages 50000 lines 32767 col HOST_NAME for a20 col EVENT for a40 col MACHINE for a30 col SQL_TEXT for a50 col USERNAME for a15 select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait, to_char(logon_time,'DD-MON-RR HH24:MI') login from gv$session S,gV$SQLAREA A,gv$instance i where S.username is not null --  and S.status='ACTIVE' AND S.sql_address=A.address and s.inst_id=a.inst_id and i.inst_id = a.inst_id and sql_text not like 'select S.USERNAME,S.seconds_in_wait%' / Current Running SQLs -------------------- set pages 50000...

Ref:http://select-star-from.blogspot.com.au/ 1. Check the Database details :- ============================= set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance; For RAC: ------- set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance; 2. Monitor the consumption of resources :- ======================================= select * from v$resource_limit where resource_name in ('processes','sessions'); The v$session views shows current sessions (which change rapidly), while the v$resource_limit shows the current and maximum global resour...