RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

Cause:- Controlfile does nt have the entry of archive log 57889 starting SCN 10236493076989

Action :-
1) Take the new controlfile backup from source
2) Shutdown Target Database. startup the Database in nomount state
3) Restore the new controlfile
Terminal>rman target /
RMAN>restore controlfile from 'restore controlfile from 'controlfile_Location';
Restore has been Completed .
4) Startup the database in mount state
RMAN>sql 'alter database mount';
5) Do a incomplete recovery or complete recovery what ever you want.
RMAN>Recover database;
Restore command may failure with below error.
RMAN-03002: failure of recover command at 08/28/2013 12:23:47
RMAN-06094: datafile 1 must be restored

6)Here we can use catalog option to resolve this issue.Below example "+ORA01_D1" is the disk group name for the entire Datafiles

RMAN>catalog start with '+ORA01_D1';

Rman will show all the Datafile need to be catalogged.
See the below example
List of Cataloged Files
=======================
File Name: +ORA01_D1/MYDB/AUTOBACKUP/2013_08_28/s_824645393.441.824645393
File Name: +ORA01_D1/MYDB/DATAFILE/SYSAUX.317.824646109
File Name: +ORA01_D1/MYDB/DATAFILE/SYSTEM.318.824646105
File Name: +ORA01_D1/MYDB/DATAFILE/UNDOTBS1.328.824646110
File Name: +ORA01_D1/MYDB/DATAFILE/D12.327.824646908
File Name: +ORA01_D1/MYDB/DATAFILE/D13.326.824641013
File Name: +ORA01_D1/MYDB/DATAFILE/D14.325.8246463105
File Name: +ORA01_D1/MYDB/DATAFILE/D15.324.82464632095
File Name: +ORA01_D1/MYDB/DATAFILE/D16.323.824646987
File Name: +ORA01_D1/MYDB/DATAFILE/USERS.322.82464131
File Name: +ORA01_D1/MYDB/DATAFILE/D17.321.824646989
Now it will Prompt for YES/NO . Put 'YES' if everything looks Good.

Now  Oracle identified all the Datafiles which we restored from source.

7) Switches to image copy backup of database.

RMAN> switch database to copy;

8)Recover Database

Rman>Recover Database;

again if it is failed with unknown archive log entry to the controlfile. Please do a incomplete recovery.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57889 and starting SCN of 10236493076989

9)Open Database with reset log sequence

RMAN>sql 'alter database open resetlogs';

10)Check the status

SQL>select status from v$instance;
             OPEN


Thanks,
Jyothish

SQL History of an Oracle Session from ASH

Query help to find out the SQL history of an oracle session from Active Session History(ASH) . Here we can see the historical  Data available from AWR too.

SELECT SQL_ID,PLAN_HASH_VALUE,inst_id, sum(execs) "Execution", round(sum(tot_time)/1000000,2) "Elapsed_time",
(case when sum(execs) > 0 then sum(tot_time)/ sum(execs)/1000000 else 0 end) Average_exe_Second
FROM (SELECT C.SQL_ID ,c.PLAN_HASH_VALUE,b.inst_id,
        c.executions execs ,c.elapsed_time tot_time
 FROM gv$ACTIVE_SESSION_HISTORY A,gv$session b,
        gv$SQLAREA C where
        a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=131
    union all
SELECT C.SQL_ID  ,c.PLAN_HASH_VALUE ,b.inst_id ,c.executions_delta Execs,c.elapsed_time_delta tot_time
 from dba_hist_active_sess_history a, gv$session b, dba_hist_sqlstat c where
a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=12 )
GROUP BY SQL_ID,PLAN_HASH_VALUE,inst_id;

Thanks,
Jyothish

PGA Allocation for an Oracle Process

Query help to find out how much PGA allocated for an Oracle Process.

SELECT
  s.sid,
  p.spid,
  DECODE(s.program, NULL, p.program, s.program) AS "Program",
  pga_used_mem,
  pga_alloc_mem,
  pga_max_mem
FROM gv$process p, gv$session s
WHERE s.paddr = p.addr
ORDER BY s.sid;

Thanks,
Jyothish

PGA Allocated Memory for an Oracle Session

Below Query help to find out PGA Allocated Memory for an Oracle Session.

select
    to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
    nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "Session_Details",
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
    gv$statname  stat1,
    gv$statname  stat2,
    gv$session   ssn,
    gv$sesstat   se1,
    gv$sesstat   se2,
    gv$bgprocess bgp,
    gv$process   prc,
    gv$instance  ins
where stat1.name = 'session pga memory'
and stat2.name = 'session pga memory max'
and se1.sid = ssn.sid
and se2.sid = ssn.sid
and se2.statistic# = stat2.statistic#
and se1.statistic# = stat1.statistic#
and ssn.paddr = bgp.paddr(+)
and ssn.paddr = prc.addr(+) ;

Thanks,
Jyothish

Sort Area Usage/Temp Tablespace Usage of an Oracle Session

Below query will picture of Temp Usage of an Oracle Session.


set lines 132
set feedback on
set pages 99

select s.osuser, s.sid, s.serial#,s.username, s.program,
s.client_info,(sum(u.blocks)*vp.value/1024/1024/1024) AS GB_USED, a.sql_text as sql_t
from sys.gv_$session s, sys.gv_$sort_usage u, sys.gv_$parameter vp, sys.gv_$sqlarea a, sys.gv_$process b
where
s.saddr = u.session_addr and
vp.name = 'db_block_size' and
a.address = s.sql_address and
b.addr = s.paddr
group by s.osuser, s.sid, s.serial#,s.username, s.program,s.client_info, vp.value, a.sql_text;


Thanks,
Jyothish

CPU usage of an Oracle session

Below query help to  find out the CPU usage(Second) for a  oracle session session.

select
   ss.sql_id "SQL_ID",ss.username,ss.status,ss.machine,b.sql_text,b.PLAN_HASH_VALUE,
   VALUE/100 cpu_usage_seconds
from
   gv$session ss,
   gv$sesstat se,
   gv$statname sn,
   gv$sql b
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID and ss.sql_id=b.sql_id
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

Thanks,
Jyothish