How to Find the Responsibility Name for a Concurrent Program

SELECT frt.responsibility_name, frg.request_group_name,

frgu.request_unit_type,frgu.request_unit_id,

fcpt.user_concurrent_program_name

FROM fnd_Responsibility fr, fnd_responsibility_tl frt,

fnd_request_groups frg, fnd_request_group_units frgu,

fnd_concurrent_programs_tl fcpt

WHERE frt.responsibility_id = fr.responsibility_id

AND frg.request_group_id = fr.request_group_id

AND frgu.request_group_id = frg.request_group_id

AND fcpt.concurrent_program_id = frgu.request_unit_id

AND frt.LANGUAGE = USERENV('LANG')

AND fcpt.LANGUAGE = USERENV('LANG')

AND fcpt.user_concurrent_program_name = :conc_prg_name

ORDER BY 1,2,3,4

Query to take a backup/Details of all database links in oracle

 

SELECT dl.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',dl.DB_LINK,dl.OWNER) FROM dba_db_links dl;

select dl.OWNER,dl.DB_LINK,dl.USERNAME,dl.HOST,CREATED from dba_db_links dl;

DROP Database Links:

drop database link <db_link_name>;              --Needs to run as a specific user (Owner of the db links)

drop public database link <db_link_name>;    --Needs to run as a sys user.

Query to Find the UTLRP Session in an Oracle Database

In RAC:

select a.inst_id,a.sid,a.serial#,b.sql_text,b.sql_id,a.username,a.SCHEMANAME,a.OSUSER,a.MACHINE,a.PROGRAM,a.sql_id,a.action,a.event 

from gv$session a, gv$sqlarea b 

where a.sql_address=b.address and a.action like '%UTL%';

In Non RAC:

select a.inst_id,a.sid,a.serial#,b.sql_text,b.sql_id,a.username,a.SCHEMANAME,a.OSUSER,a.MACHINE,a.PROGRAM,a.sql_id,a.action,a.event 

from v$session a, v$sqlarea b 

where a.sql_address=b.address and a.action like '%UTL%';

Find SCN Timestamp in Oracle Database

 SELECT SCN_TO_TIMESTAMP(13232543131) from dual;

Database version on SQL Server Management Studio

SELECT @@VERSION AS FullVersion;

Database Size in SQL Server Management Studio

--List the size of all databases on the server

SELECT 

DB_NAME(database_id) AS DatabaseName,

    SUM(size * 8 / 1024) AS SizeMB

FROM sys.master_files

GROUP BY database_id;

Find Logging level for EBS R12.2 Workflow services

col component_name format A50

col parameter_name format a45 head "Parameter Name"

col parameter_value format a30 head "Value"

SELECT b.component_name,

  c.parameter_name,

  a.parameter_value

FROM fnd_svc_comp_param_vals a,

  fnd_svc_components b,

  fnd_svc_comp_params_vl c

WHERE b.component_id = a.component_id

AND b.component_type = c.component_type

AND c.parameter_id = a.parameter_id

AND c.parameter_name like '%LOG_LEVEL%';

Find SQL Server version

Look at the first few lines of the Errorlog.n file for that instance. By default, the error log is located at 

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

EBS R12.2 Clone Log files Location

 

The below-mentioned locations are more important to analyze the EBS cloning issues.

/u01/PROD/fs1/inst/apps/PROD_oracleap/admin/log/clone/

/u01/PROD/fs1/EBSapps/comn/clone/bin/

Archive Log Deletion Policy in Oracle Database RMAN Configuration

RMAN> Show all;   --> use this command to view the RMAN configuration settings

1. Eligible to delete archivelog files applied on all standby databases (required for DR setup) and those files should be backed up 1 time on the database (primary/production).

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

2. No Deletion policy. Eligible to delete all available archivelog files from the database.

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

3. Eligible to delete all archivelog files applied on all standby databases (DR environment).

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

4. Eligible to delete archivelog files which are already backed up 1 time by the database.

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;