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')
/

Pending Concurrent Requests Count

The below query returns pending concurrent requests

--------------------------------------
- Count of pending concurrent requests
--------------------------------------
select COUNT (distinct cwr.request_id) Peding_Requests   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);

Tablespace Less than 20% free and add space

-----------------------------------
-This script is used to check the Tablespace less than 20% free.
- Gives wheter hot backp is in progress?
-Gives datafile information for the provided tablespace
- Script Name : tsinfo.sql
- Usage : @tsinfo
- File Location : http://ruappsdba.blogspot.in/2013/09/tablespace-less-than-20-free-and-add.html
----------------------------------

SELECT a.tablespace_name,b.size_kb,a.free_kb,Trunc((a.free_kb/b.size_kb) * 100) "FREE_%" FROM
(SELECT tablespace_name,Trunc(Sum(bytes)/1024) FREE_KB FROM   dba_free_space GROUP BY tablespace_name) a,
(SELECT tablespace_name,Trunc(Sum(bytes)/1024) SIZE_KB FROM   dba_data_files GROUP BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name AND Round((a.free_kb/b.size_kb) * 100,2) < 20
/
select unique status from v$backup
/
col file_name for a60
set line 150
col "Auto Extend" for a10
SELECT tablespace_name,file_name,autoextensible "Auto Extend",bytes/1024/1024 "Used in MB",maxbytes/1024/1024 "Max in MB",increment_by
FROM dba_data_files where tablespace_name='&T_NAME' order by file_name
/

Invalid objects in Database

-----------------------------------
-This script is used to check the invalids
- Script Name : invalids.sql
- Usage : @invalids
----------------------------------
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 for patching
-------------------
create table p&patchno as(select *from dba_objects where status='INVALID');

------------------------------
Use below query 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

-----------------------------------
-This script is used to check Standby Sync Status
- Script Name : sync_dr.sql
- Usage : @sync_dr
----------------------------------

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
/

Standby Database Troubleshooting Sync Issues

• ORA-16047: DGID mismatch between destination setting and target database
• ORA-19502: write error on file "", block number (block size=)

ORA-16047: DGID mismatch between destination setting and target database

ORA-16047: DGID mismatch between destination setting and target database

The error shows a miss-match of DG_CONFIG parameter. Check the below parameters on both Primary and Standby databases.

On Primary:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(RUDBA,RUDBA_STANDBY)

SQL> show parameter fal

SQL> show parameter fal

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      RUDBA

fal_server                           string      RUDBA_STANDBY

SQL> show parameter log_archive_dest_2                  

log_archive_dest_2                   string      SERVICE=RUDBA_STANDBY LGWR ASYNC=20480

                                                 DB_UNIQUE_NAME=RUDBA_STANDBY OPTIONAL REOPEN=15  

                                                 MAX_FAILURE=10 NET_TIMEOUT=30

             

SQL> show parameter log_archive_dest_state_2            

log_archive_dest_state_2             string      ENABLE

On Standby:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(RUDBA,RUDBA_STANDBY)

SQL> show parameter fal

SQL> show parameter fal

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      RUDBA_STANDBY

fal_server                           string      RUDBA

SQL> show parameter log_archive_dest_2

                 

log_archive_dest_2                   string      SERVICE=RUDBA LGWR ASYNC=20480 DB_UNIQUE_NAME=RUDBA

                                                 OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30

SQL> show parameter log_archive_dest_state_2

           

log_archive_dest_state_2             string      DEFER

If any of the above parameters are not set correctly the below error will appear.

set line 200

col DEST_NAME for a50

col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'

/

DEST_ID DEST_NAME          STATUS    BINDING    ERROR

------- ---------------------------- ---------- -----------------------------------------------------------------

      1 LOG_ARCHIVE_DEST_1 VALID     OPTIONAL

      2 LOG_ARCHIVE_DEST_2 DISABLED  OPTIONAL   ORA-16047: DGID mismatch between destination setting and tar database

If the parameters are set correct.

set line 200

col DEST_NAME for a50

col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'

/

DEST_ID DEST_NAME          STATUS    BINDING    ERROR

------- ---------------------------- ---------- -----------------------------------------------------------------

      1 LOG_ARCHIVE_DEST_1 VALID     OPTIONAL

      2 LOG_ARCHIVE_DEST_2 VALID     OPTIONAL