Invalid Objects Check

-----------------------------------
-Invalid Objects Check
------------------------------------

SET PAGESIZE 300
SET LINESIZE 150
col OWNER for a25
col OBJECT_NAME for a30
select OWNER,OBJECT_TYPE, STATUS,count(*)
from dba_objects where STATUS='INVALID' group by OWNER,OBJECT_TYPE, STATUS order by owner
/

select count(*) from dba_objects where STATUS='INVALID'
/
prompt Suggestion : @?/rdbms/admin/utlrp.sql

----------------------
Backup before Patching
----------------------

create table p&patchno as(select *from dba_objects where status='INVALID');

----------------------
Check After Patching
----------------------

select count(*) from dba_objects where status='INVALID' and object_name not in(select object_name from &backup_table);

Standby Sync Status Check

TOP

-----------------------------------
-Standby Database Sync Status Check
------------------------------------

select name,open_mode,database_role from v$database
/

set line 200
col DEST_NAME for a50
col BINDING for a10
select PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
/
archive log list

select count(*) from v$archive_gap
/
select max(sequence#) from v$log_history
/
col MESSAGE for a150
select * from(select message from v$dataguard_status where message like '%Media Recovery Log%' order by message desc) where rownum<=5
/

Pending Concurrent Requests with Standard Manager

--------------------------------------------------
-Pending Concurrent Requests with Standard Manager
--------------------------------------------------

col USER_CONCURRENT_PROGRAM_NAME for a50
select cwr.request_id,cwr.parent_request_id,cwr.user_concurrent_program_name,
DECODE (cwr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) ||' / '||
DECODE (cwr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
) "    Status  "
apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Standard Manager')
/

Pending Concurrent Requests with CRM

------------------------------------------
-Pending Concurrent Requests with
-Conflict Resolution Manager(CRm)
------------------------------------------

col USER_CONCURRENT_PROGRAM_NAME for a50
select cwr.request_id,cwr.parent_request_id,cwr.user_concurrent_program_name,
DECODE (cwr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) ||' / '||
DECODE (cwr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
) "    Status  "
FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Conflict Resolution Manager')
/

Pending Concurrent Requests

----------------------------------
-Pending Concurrent Requests Info
----------------------------------
col USER_CONCURRENT_PROGRAM_NAME for a50
select cwr.request_id,cwr.parent_request_id,cwr.user_concurrent_program_name,
DECODE (cwr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) ||' / '||
DECODE (cwr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
) "    Status  " 
FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE    AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name 
in ( select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl) order by cwr.Phase_code
/

Pending Concurrent Requests count with Standard Manager

-----------------------------------
-Pending with Standard Manager Count
-----------------------------------

select count(cwr.request_id) "pending with Standard Manager" FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Standard Manager')
/

Pending Concurrent Requests Count with CRM

-----------------------------------------
-Pending Concurrent Request Count
-with Conflict Resolution Manager(CRM)
-----------------------------------------

select count(cwr.request_id) "Pending with CRM" FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')   AND cwr.hold_flag != 'Y'   AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id   AND cwr.queue_application_id = cq.application_id  and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ( 'Conflict Resolution Manager')
/