Useful Scripts for ADOP Session R12.2

Error: Unable to continue as already another user is using adzdoptl.pl.
Previous session exist, cannot continue as per user input.
Workaround: Update the internal adop repository table for the latest session setting the status to completed.
Run the following statement to find out the session that is in running state:
select adop_session_id from ad_adop_sessions where status='R';
Set the status to 'C' (Completed) for that session, to re-try the phase that was interrupted:
SQL>update ad_adop_sessions set status='C' where status='R;
commit;
Take a backup of ADOP table
SQL> create table ad_adop_sessions_17decbakpp as select * from ad_adop_sessions;
Table created.
column id format 99
column bn format a12
column pr format 99999999
column afs format a40
column pfs format a40
column cs format a9
column nn format a12
column ed format a28
column drv format a28
column pt format a35
select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt
from ad_adop_session_patches where bug_number='&1';
select prepare_status,apply_status,finalize_status,cutover_status,cleanup_status,abort_status,status,
node_name from APPS.AD_ADOP_SESSIONS where adop_session_id=64;
SELECT 'ALTER MATERIALIZED VIEW'''||MVIEW_NAME||'COMPILE;'''FROM DBA_MVIEWS;
update ad_adop_sessions set status='C' where adop_session_id=3;
column id format 99
column bn format a12
column pr format 99999999
column afs format a40
column pfs format a40
column cs format a9
column nn format a10
column ed format a28
column drv format a28
column pt format a35
select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt
from ad_adop_session_patches
order by end_date desc;
select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt
from ad_adop_session_patches where bug_number='22046612';
select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt from ad_adop_session_patches where adop_session_id='65';
select path,status,serial_number,version from fnd_oam_context_files where name not in ('TEMPLATE','METADATA') and (status is null or status !='VALID');
col NODE_NAME for a20
set pages 50
set lines 200
select NODE_NAME, SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN, SUPPORT_DB from fnd_nodes where node_name <> 'AUTHENTICATION';
select text from user_source where name like 'PA_FP_WEBADI_PKG' and line = 2
and type like 'PACKAGE%' ;
clear columns
set pagesize 999
column node_name heading "Node Name" format a8
column support_cp heading "CP" format a3
column support_forms heading "Frm" format a3
column support_web heading "Web" format a3
column support_admin heading "Adm" format a3
column support_db heading "DB" format a3
column host heading "Host" format a8
column domain heading "Domain" format a15
column virtual_ip Heading "Virtual Name" format a8
column server_address Heading "IP Address" format a16
Select node_name, support_cp, support_forms, support_web, support_admin, support_db, host, domain, virtual_ip, server_address
from fnd_nodes
where node_name not in ('AUTHENTICATION', 'METADATA', 'TEMPLATE');
set pagesize 999
clear columns
column name heading "Name" format A17
column DB heading "DB" format A2
column Admin heading "Adm" format A3
column Web heading "Web" format A3
column Forms heading "Frm" format A3
column Conc heading "Cnc" format A3
column FDev heading "FD" format A3
column CDev heading "CD" format A3
column WDev heading "WD" format A3
column adAdmin heading "adAd" format A4
column aDWeb heading "adW" format A3
column adForms heading "adF" format A3
column adConc heading "adC" format A3
column adFDev heading "adFD" format A4
column adCDev heading "adCD" format A4
column adWDev heading "adWD" format A4
SELECT name
, extractValue(xmltype(text),'//*[@oa_var=''s_isDB'']') AS DB
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdmin'']') AS Admin
, extractValue(xmltype(text),'//*[@oa_var=''s_isWeb'']') AS Web
, extractValue(xmltype(text),'//*[@oa_var=''s_isForms'']') AS Forms
, extractValue(xmltype(text),'//*[@oa_var=''s_isConc'']') AS Conc
, extractValue(xmltype(text),'//*[@oa_var=''s_isFormsDev'']') AS FDev
, extractValue(xmltype(text),'//*[@oa_var=''s_isConcDev'']') AS CDev
, extractValue(xmltype(text),'//*[@oa_var=''s_isWebDev'']') AS WDev
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdAdmin'']') AS adAdmin
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdWeb'']') AS adWeb
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdForms'']') AS adForms
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdConc'']') AS adConc
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdFormsDev'']') AS adFDev
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdConcDev'']') AS adCDev
, extractValue(xmltype(text),'//*[@oa_var=''s_isAdWebDev'']') AS adWDev
FROM fnd_oam_context_files
WHERE name NOT IN('TEMPLATE','METADATA') AND status! ='H'
and ctx_type = 'A';
 col NODE_NAME for a15
 set lines 200
 set pages 50
 col EDITION_NAME for a20
 col NODE_TYPE for a15
select ADOP_SESSION_ID, EDITION_NAME, NODE_TYPE, NODE_NAME, APPLTOP_ID, PREPARE_STATUS,
APPLY_STATUS, FINALIZE_STATUS,CUTOVER_STATUS, CLEANUP_STATUS, ABORT_STATUS, STATUS
from ad_adop_sessions
order by ADOP_SESSION_ID;
col NODE_NAME for a15
 set lines 200
 set pages 50
 col EDITION_NAME for a20
 col NODE_TYPE for a15
select ADOP_SESSION_ID, EDITION_NAME, NODE_TYPE, NODE_NAME, APPLTOP_ID, PREPARE_STATUS,
APPLY_STATUS, FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS, ABORT_STATUS, STATUS
from AD_ADOP_SESSIONS where ADOP_SESSION_ID='&id';
update ad_adop_sessions set ABORT_STATUS='N' where ADOP_SESSION_ID='32';
Note:
       Y denotes that the phase is done
       N denotes that the phase has not been completed
       X denotes that the phase is not applicable
       R denotes that the phase is running (in progress)
       F denotes that the phase has failed
       P (is applicable only to APPLY phase) denotes at least one patch is already applied for the session id
       C denotes that the status of this ADOP session has completed