Stay at Home!! Be Safe!! Take Care!!

Thanks all my viewers for your support, I am an Oracle Ace now. For Any queries, please mail us at support@funoracleapps.com

How to Find which Files Are Backed Up In RMAN Backup Set

No comments

How to Find which Files Are Backed Up In RMAN Backup Set 



Query to find the  Backed Up Logs.

SQL> select recid,set_stamp,sequence#,first_change#,next_change# 
     from  v$backup_redolog;

The V$BACKUP_REDOLOG view queries the Control File so this can be done with or 
without a Catalog.


Query to find sequence is kept in which backup piece

SQL> select r.sequence#, p.handle
 from v$backup_piece p, v$backup_redolog r
 where r.set_stamp = p.set_stamp
   and r.set_count = p.set_count
   and r.sequence# = 63 

Query to find datafiles  is kept  in which backup piece. 

 SQL> select d.file#, p.handle
from v$backup_piece p, v$backup_datafile d
where d.set_stamp = p.set_stamp
  and d.set_count = p.set_count
  and d.file# = 3

No comments :

Post a Comment

Query to find Session details using SPID with full details

No comments

Query to find Session details using SPID with full details



Script:

set serveroutput on;
define uxproc=29894; /*replace the value with SPID*/
DECLARE
  v_sid number;
  vs_cnt number;
  s sys.v_$session%ROWTYPE;
  p sys.v_$process%ROWTYPE;
  cursor cur_c1 is select sid from sys.gv_$process p, sys.gv_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');
BEGIN
    dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.v_$process p, sys.v_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');  
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP   
      FETCH cur_c1 INTO v_sid;    
    EXIT WHEN (cur_c1%NOTFOUND);   
select * into s from sys.v_$session where sid  = v_sid;
  select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
  dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);
  dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);
  dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);
  dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
  dbms_output.put_line('Ora User    : '|| s.username);
dbms_output.put_line('Details     : '|| s.action||' - '||s.module);
  dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
  dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
  dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
  dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99990.0') || ' min');
  dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
  dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));
  dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.sql_hash_value order by piece) 
loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
dbms_output.put_line('Previous SQL statement:');
  for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.prev_hash_value order by piece) 
loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
dbms_output.put_line('Session Waits:');
  for c1 in ( select * from sys.v_$session_wait where sid = s.sid) 
loop
    dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  end loop;
--  dbms_output.put_line('Connect Info:');
--  for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
--    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
--  end loop;
  dbms_output.put_line('Locks:');
  for c1 in ( select  /*+ RULE */ decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
          decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,
           decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
        decode(l.type, 'MR', o.name,
                      'TD', o.name,
                      'TM', o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                            ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) objname
        from  sys.v_$lock l, sys.obj$ o
        where sid   = s.sid
          and l.id1 = o.obj#(+) ) 
loop
  dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
  end loop; 
dbms_output.put_line('=====================================================================');
END LOOP;  
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');  
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
  close cur_c1;
exception
    when no_data_found then
      dbms_output.put_line('Unable to find process id &&uxproc!!!');
  dbms_output.put_line('=====================================================================');
      return;
    when others then
      dbms_output.put_line(sqlerrm);
      return;
END;
/

No comments :

Post a Comment

Concurrent Manager Recovery Wizard

No comments

Concurrent Manager Recovery Wizard

In Oracle Application earlier we used CMCLEAN.sql. This script re-sets the flags for requests to completed to allow the Managers to come up. No longer supported and MUST not be used.

Concurrent Manager Recovery Wizard has to be used. It is a OAM managed GUI for recovering Concurrent Manager. It should only be run when the concurrent manager services are down.

How to use Concurrent Manager Recovery Wizard.

  • Go to OAM > Site Map > Diagnostics and Repair > Troubleshooting Wizards > Concurrent Manager Recovery

  • Ensure that the Concurrent Manager is down prior to progressing with launching the Wizard

  • This step will let you know if any of the concurrent managers are with status Running via backend tables, we can update directly here if we know for sure that managers are down.

  • This step will let  you know if any of the concurrent processes are running, we can directly update status here in GUI if we know for sure that Concurrent Manager is down.

  • Requests listed below need to be reset for conflict resolution. Also the status of all conflict domains will be reset to allow requests to run.

  • Summary screen is shown now with list of action taken in the previous screens. Concurrent Managers can be started now


No comments :

Post a Comment

How to make a FND_USER Non SSO in and SSO enabled EBS Environment

No comments

How to make a FND_USER Non SSO in and SSO enabled EBS Environment



Steps:

1) Connect as Apps user
2) SQL> @$FND_TOP/patch/115/sql/fndssouu.sql HIMANSHU.SINGH

PL/SQL procedure successfully completed.


Commit complete.
3) Set profile   at user level to "LOCAL"


4) Reset Password for user using FNDCPASS.

Password is changed successfully for user HIMANSHU.SINGH.
FNDCPASS completed successfully.

5) Login with Non SSO URL

No comments :

Post a Comment

Query to find Concurrent Request Details from Concurrent Program Name

No comments

Query to find Concurrent Request Details from Concurrent Program Name






Script

SELECT
   distinct user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type,
    hold_flag,
    user_name
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    --and user_name = upper('HIMSINGH')
    and user_concurrent_program_name in 
('Active Users')
    and Phase_code='P'
ORDER BY REQUEST_DATE DESC;

The where conditions can be modified as per your need.

No comments :

Post a Comment