generate an dblink

 SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

Steps to start/stop Workflow Notification Mailer--backend

 

Steps to start/stop Workflow Notification Mailer

 

·         Check workflow mailer service current status

 

SQL> select running_processes from fnd_concurrent_queues

 where concurrent_queue_name = 'WFMLRSVC';  

 

RUNNING_PROCESSES

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

                1

Number of running processes should be greater than 0

 

·         Find current mailer status

 

SQL> select component_status

 from fnd_svc_components

 where component_id =

 (select component_id

 from fnd_svc_components

 where component_name = 'Workflow Notification Mailer'); 

 

COMPONENT_STATUS

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

DEACTIVATED_USER

 

 

Possible values are :

RUNNING STARTING,

STOPPED_ERROR,

DEACTIVATED_USER

DEACTIVATED_SYSTEM.

 

·         Stop Workflow mailer

 

declare

p_retcode number;

p_errbuf varchar2(100);

m_mailerid fnd_svc_components.component_id%TYPE;

begin

select component_id

into m_mailerid

from fnd_svc_components

where component_name = 'Workflow Notification Mailer';

fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);

commit;

end;

/

 

·         Start the Notification Mailer

 

declare

p_retcode number;

p_errbuf varchar2(100);

m_mailerid fnd_svc_components.component_id%TYPE;

begin

select component_id

into m_mailerid

from fnd_svc_components

where component_name = 'Workflow Notification Mailer';

fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);

commit;

end;

/

 

 

TKPROF Overview

 

TKPROF Overview.

 

TKPROF is used for diagnosing performance issues.  It formats a trace file into readable format for performance analysis.

Syntax

tkprof    tracefile_name.trc  tracefileoutput.txt sys=no  sort='(prsela,exeela,fchela)'  explain=apps/apps passwd.

 

 

sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements.  Default=enable. 

explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan provides the predicted optimizer execution path without actually executing the statement.  tkprof shows you the actual execution path and statistics after the statement is executed.

 

sort – Sorts the SQL statements in the trace file by the criteria required.It provides SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers.

prsela – The elapsed time spent parsing the SQL.

exeela – The elapsed time spent executing the SQL.

fchela – The elapsed time spent fetching rows.

Tablespace alerts

 

 

To check the tablespace in GB

 

select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from

    (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space

    from dba_free_space group by tablespace_name UNION

    select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from dba_temp_free_space) a,

    (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size

    from dba_data_files group by tablespace_name UNION

    select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size

    from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;  

Output

TABLESPACE_NAME                    SIZEGB        FREEGB

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

APEX                                                 6                     1.8

APPS_TS_ARCHIVE                        89                   24.8

APPS_TS_INTERFACE                  101.742188      38.3

APPS_TS_MEDIA                           1025                 54

 

 

To check the tablespace in MB

 

SELECT df.tablespace_name "Tablespace",

  totalusedspace "Used MB",

  (df.totalspace - tu.totalusedspace) "Free MB",

  df.totalspace "Total MB",

  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"

FROM

  (SELECT tablespace_name,

    ROUND(SUM(bytes) / 1048576) TotalSpace

  FROM dba_data_files

  GROUP BY tablespace_name

  ) df,

  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,

  2    3    4      tablespace_name

  FROM dba_segments

  GROUP BY tablespace_name

  ) tu

WHERE df.tablespace_name = tu.tablespace_name; 

 

Output

 

Tablespace                        Used MB    Free MB   Total MB     % Free

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

APEX                                 4351       1793       6144         29

APPS_TS_ARCHIVE                     65696      25440      91136         28

APPS_TS_INTERFACE                   64938      39246     104184         38

APPS_TS_MEDIA                      994267      55333    1049600          5

APPS_TS_NOLOGGING                   18935     727661     746596         97

 

 

Checking datafile location

set lines 1000

set pages 1000

col file_name for a60

col tablespace_name for a25

col creation_time for a20

select ddf.file_id, ddf.file_name, ddf.tablespace_name, to_char(df.CREATION_TIME,'dd-MON-yyyy hh24:mi:ss') creation_time,ddf.bytes/1024/1024/1024 "Size GB"

from dba_data_files ddf, v$datafile df

where ddf.file_id=df.file#

and tablespace_name='ranesh_DI_DATA'

order by df.creation_time;

 

Output

 

   FILE_ID FILE_NAME                                                    TABLESPACE_NAME           CREATION_TIME           Size GB

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

        40  datafile/xxecs_dist_data01.dbf          ranesh_DI_DATA           06-SEP-2007 19:03:23         30

        65 /datafile/xxecs_dist_data02.dbf          ranesh_DI_DATA           27-APR-2008 23:57:36         30

        68 datafile/xxecs_dist_data03.dbf          ranesh_DI_DATA          11-AUG-2008 13:31:06         30

 

 

 

 

 

Check the size of particular tablespace.

 

set linesize 400;

set lines 200

col "Name" format a20

col GB for 9999999

col file_name for a100

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",

round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from

(select tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files

where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) a,

(select tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space

where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) b

Where a.tbl=b.tblsp;

 

Output

 

Name                 Total Size Free Space   Pct Used   Pct Free

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

ranesh_DI_DATA      3608.07031 24.3586426         99          1

 

I need to bounce ,status,changes of each Concurrent Manager

The status of the concurrent manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

Deactivating the concurrent manager:When you deactivate a manager all requests (concurrent programs) currently running are allowed to complete before the manager(s) shut down.

SQL>update fnd_concurrent_queues set control_code='D' where concurrent_queue_name=‘Standard Manager’;

 

Set max and target processes to 0

SQL>update fnd_concurrent_queues set running_processes=0,MAX_PROCESSES=0 where concurrent_queue_name=‘Standard Manager’;

commit;

see the status of the Manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

Activating the concurrent manager:

SQL>update fnd_concurrent_queues set control_code='R' where concurrent_queue_name=‘Standard Manager’;

commit;

 

See the status of the Manager

SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;

SQL to find Pending Concurrent Requests & Status of Conncurrent Request

 

SQL to find Pending Concurrent Requests & Status of Conncurrent Request

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);

Pending with 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 with Standard Manager

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 with Internal Manager

select COUNT (distinct cwr.request_id) "Pending with Internal 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 ('Internal Manager');

Pending with Any other Manager
this sql Prompts for the Manager Name

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 ('&Concurrent_Manager_Name');

User Concurrent Queue Status

select MAX_PROCESSES,RUNNING_PROCESSES from apps.fnd_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME

in(select CONCURRENT_QUEUE_NAME from apps.fnd_CONCURRENT_QUEUES_TL where USER_CONCURRENT_QUEUE_NAME='&User_Concurrent_Queue_name');

 

Concurrent queue name and its user concurrent queue name

col CONCURRENT_QUEUE_NAME for a30

col USER_CONCURRENT_QUEUE_NAME for a60

set line 200

select UNIQUE FCQ.CONCURRENT_QUEUE_NAME,FCQT.USER_CONCURRENT_QUEUE_NAME,fcq.max_processes,fcq.running_processes

from apps.fnd_concurrent_queues fcq,apps.fnd_concurrent_queues_tl fcqt where fcq.concurrent_queue_id=fcqt.concurrent_queue_id and fcq.application_id=fcqt.application_id order by fcq.running_processes desc;

Councurrent–>Manager–>Administer Screen
from back-end

select cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes,count(cwr.request_id)

FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu,apps.fnd_concurrent_queues fcq

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 fcq.concurrent_queue_id=cq.concurrent_queue_id and fcq.application_id=cq.application_id

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)

group by cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes;

 

 

Block Change Tracking

Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.

You can enable block change tracking when the database is either open or mounted. This section assumes that you intend to create the block change tracking file as an Oracle Managed File in the database area, which is where the database maintains active database files such as data files, control files, and online redo log files.

To determine if block change tracking is enabled, check the STATUS and FILENAME columns in the V$BLOCK_CHANGE_TRACKING view, using the following statement from the SQL or RMAN prompt:

SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

 

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

 

Enabling and Disabling Oracle Change Tracking file

 

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

 

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;  

We can also create the change tracking file in a desired location, using the following SQL statement:

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

 

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;  

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

 

Checking Whether Change Tracking is enabled

 

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not. 

 

SQL> select status from V$BLOCK_CHANGE_TRACKING;

        ENABLED   => block change tracking is enabled.

        DISABLED  => block change tracking is disabled.

 

Query V$BLOCK_CHANGE_TRACKING to display the filename.

 

SQL> select filename from V$BLOCK_CHANGE_TRACKING;

 

Moving the Change Tracking File in Oracle

 

If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location.

1. If necessary, determine the current name of the change tracking file:

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/app/rman/rman_change_track.f

2. Shutdown the database.

SQL> SHUTDOWN IMMEDIATE

3. Using host operating system commands, move the change tracking file to its new location.

$ mv /u02/rman/rman_change_track.f /u01/app/rman/rman_change_track.f

4. Mount the database and move the change tracking file to a location that has more space. For example:

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RENAME FILE '/u01/app/rman/rman_change_track.f' TO '/u01/app/rman/rman_change_track.f';

5. Open the database.

SQL> ALTER DATABASE OPEN;

 

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/app/rman/rman_change_track.f

If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:

 

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/rman/rman_change_track.f';

 

If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.

 

Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.

 

Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. The following two factors that may cause the file to be larger than this estimate suggests:

·                     To avoid overhead of allocating space as database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB increments. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

·                     For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;