Query To Get Archive Log Apply Rate Speed of a Standby Database

Please run below query on Standby Oracle Database

Download(archlogapplymon.sql)

REM +======================================================================+
REM                    
REM File Name: archlogapplymon.sql
REM 
REM Description:
REM   Query To Get Archive Log Apply Rate Speed of a Standby Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @archlogapplymon.sql 
REM
REM   
REM +======================================================================+

set linesize 180
col Values for a70
col Recovery_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recovery_start",
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" 
from v$recovery_progress 
where start_time=(select max(start_time) from v$recovery_progress);

Sample Output:

Undo/redo logs filled up due to the following query and produces high number of archive logs

Issue:

Undo/redo logs filled up due to the following query and produces high number of archive logs:

UPDATE QUERY SET OBJVERSION = NVL(OBJVERSION,0) + 1 WHERE ID = :1

Query To find which transactions/sessions consuming the UNDO tablespace

Cause:

Found that this is a bug in Agile on Solaris SPARC (64-bit):

Bug 14661425 : AGILE - ROGUE QUERY PRODUCES HIGH NUMBER OF ARCHIVE LOGS FILLING DATABASE

Solution:

Upgrade to Agile PLM 9.3.3 to resolve the problem.

GA Patch is available for 9.3.1.2:
Patch:14763729: 9.3.1.2.93: AGILE - ROGUE QUERY PRODUCES HIGH NUMBER OF ARCHIVE LOGS FILLING DATABASE

Query To find which transactions/sessions consuming the UNDO tablespace

Query To find which transactions/sessions consuming the UNDO tablespace

Use below query to determine which users/sessions are using and how much UNDO is being used


Download(undotsusage.sql)

REM +======================================================================+
REM                    
REM File Name: undotsusage.sql
REM 
REM Description:
REM   Query To check transaction/query exhausting the UNDO tablespace
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @undotsusage.sql 
REM   
REM +======================================================================+

Clear columns
SET pages 100
SET Lines 280
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

With:
  USED_UBLK = Number of undo blocks used
  USED_UREC = Number of undo records used

Script to Start/Stop Oracle EBS Workflow Mailer Components From Backend Database

If somehow, you are not able to access Oracle EBS front-end then you can use below scripts to Start/Stop Oracle EBS Workflow Mailer Components from Back-end Database:

Get the details of Oracle EBS Workflow Mailer Components:

col component_name for a20;
col COMPONENT_STATUS for a20;
select COMPONENT_NAME, COMPONENT_STATUS,Component_Id from fnd_svc_components;

Connect to APPS schema then execute below to Start Workflow Mailer Component:

declare
l_Component_Id number :=10006; --Enter workflow Component_Id which needs to be started
l_errcode number;
l_errstr varchar2(4000);
begin
FND_SVC_COMPONENT.Start_Component(l_Component_Id, l_errcode, l_errstr);
commit;
end;
/

Connect to APPS schema then execute below to Stop Workflow Mailer Component:

declare
l_Component_Id number :=10006; --Enter workflow Component_Id which needs to be stopped
l_errcode number;
l_errstr varchar2(4000);
begin
FND_SVC_COMPONENT.Stop_Component(l_Component_Id, l_errcode, l_errstr);
commit;
end;
/

Check Workflow Notification Preference for FND User

Query To Check Workflow Notification Preference for FND User

select * from fnd_user_preferences
where user_name = 'SYSADMIN'  --Enter FND User Name here
AND PREFERENCE_NAME = 'MAILTYPE';

SELECT name,email_address, 

       nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference) 

       as "Notification_Preference"

FROM wf_roles
WHERE name = upper('&recipient_role') --Enter FND User Name here

/

Query To Check Notification Status

select * from WF_NOTIFICATIONS
where ORIGINAL_RECIPIENT='SYSADMIN' --Enter FND User Name here

/

Test DBA_DIRECTORY and UTL_FILE_DIR read/write permissions

To test the DBA_DIRECTORY permissions and see if we are able to create the test file:

DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'DBA_DIR_NAME', 'filepath_new_file_name.txt', 'W' );
  utl_file.put_line( l_file, 'Here is some text' );
  utl_file.fclose( l_file );
END;

To read the file from DBA_DIRECTORY path:

DECLARE
  l_exists     boolean;
  l_size       integer;
  l_block_size integer;
BEGIN
  utl_file.fgetattr( 'DBA_DIR_NAME', 
                     'filepath_new_file_name.txt', 
                     l_exists, 
                     l_size, 
                     l_block_size );
   if( l_exists )
   then
     dbms_output.put_line( 'The file exists and has a size of ' || l_size );
   else
     dbms_output.put_line( 'The file does not exist or is not visible to Oracle' );
   end if;
END;

Note : To check the utl_file_dir path then replace the DBA_DIRECTORY name with the path in above script

Unregister a Database from RMAN catalog DB

Connect to RMAN catalog Database:

SQL> conn rman/rman@catlogdb
Connected.


Get the details of a database which need to de-register

SQL> SELECT db_key, dbid, name,RESETLOGS_TIME FROM rc_database where NAME like '%DBNAME%';

    DB_KEY       DBID NAME     RESETLOGS_TIME
---------- ---------- -------- ------------------
   3288657 3228736951 DBNAME 08-MAR-16

 
Execute below to Unregister a database from RMAN catalog DB
   
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(db_key,dbid);

Example:
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(3288657,3228736951);