database hange due to archive location full

#####database hange due to archive location full


1
 if you are not able to login into sqlprompt
move the old archive file to new location using mv command

2
if you are able to login into sqlprompt
change the archive location 
ALTER SYSTEM SET log_archive_dest_1='location=/oradata/export/archive' SCOPE=BOTH;

backup delete old

DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-2';

Archive log delete

DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';

Blocking session

col BLKD for a10
col BLKR for a10
col BLKDUSR for a10
col BLKRUSR for a10
col BLKDPRG for a12
col BLKRPRG for a12
col WHY for a20
col OBJECT for a15
col STATUS for a10
prompt "=*=*=*=*=*=Blocked and Blocking sesssion =*=*=*=*=*="

select a.inst_id||' - '||a.sid BLKD, a.username BLKDUSR, a.program BLKDPRG, a.blocking_instance||' - '||a.BLOCKING_SESSION BLKR,
       b.username BLKRUSR, b.program BLKRPRG, a.event||' - ran '||act.name WHY, t.start_time, t.status, obj.owner||'.'||obj.object_name object, obj.object_type
  from gv$session a,
       gv$session b,
       gv$transaction t,
       gv$locked_object lobj,
       dba_objects obj,
       audit_actions act
 where a.blocking_session is not null
   and a.blocking_instance=b.inst_id
   and a.blocking_session=b.sid
   and t.inst_id=b.inst_id
   and t.ses_addr=b.saddr
   and lobj.inst_id=b.inst_id
   and lobj.session_id=b.sid
   and lobj.object_id=obj.object_id
   and a.command=act.action;

prompt "=*=*=*=*=*=Blocking sesssion =*=*=*=*=*="

select
(select username from gv$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from gv$session where sid=b.sid) blockee,b
.sid
from gv$lock a, gv$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

output
BLKD       BLKDUSR    BLKDPRG      BLKR       BLKRUSR    BLKRPRG      WHY                  START_TIME           STATUS     OBJECT
---------- ---------- ------------ ---------- ---------- ------------ -------------------- -------------------- ---------- ---------------
OBJECT_TYPE
-------------------
1 - 956

select sid,SERIAL#,status,username from v$session where sid=956;
alter system kill session '956, 32834' immediate;

rman kill session

select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b where a.addr=b.paddr and client_info
like 'rman%';

alter system kill session '997, 42171' immediate;

########### RMAN completion status

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;




 BEST COMMAND -


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;


select START_TIME,END_TIME,STATUS from v$rman_backup_job_details order by SESSION_KEY;

select 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;