NLS PT R12 Patch application error oracle.apps.xdo.XDOException: Template ARXAGFMX not found

NLS PT R12 Patch application error oracle.apps.xdo.XDOException: Template ARXAGFMX not found

Error:

Parameters passed to XLIFFLoader...
[FILE_NAME] [ apps/apps_st/appl/ar/12.0.0/patch/115/publisher/templates/PT/ARXAGF.xlf]
[APPS_SHORT_NAME] [AR]
[UPLOAD] [UPLOAD]
[DB_USERNAME] [APPS]
[JDBC_CONNECTION] [(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domain.com)(PORT=1551))(CONNECT_DATA=(SID=VISR12)))]
[DB_PASSWORD] [******]
[TEMPLATE_CODE] [ARXAGFMX]

Target file: ARXAGF.xlf
Start uploading...
oracle.apps.xdo.XDOException: Template ARXAGFMX not found
        at oracle.apps.xdo.oa.util.TemplateTranslator.getBaseTemplateInfo(TemplateTranslator.java:338)
        at oracle.apps.xdo.oa.util.TemplateTranslator.createMLSTemplates(TemplateTranslator.java:210)
        at oracle.apps.xdo.oa.util.XLIFFLoader.processUpload(XLIFFLoader.java:709)
        at oracle.apps.xdo.oa.util.XLIFFLoader.process(XLIFFLoader.java:565)
        at oracle.apps.xdo.oa.util.XLIFFLoader.main(XLIFFLoader.java:1073)



Solution:

1. Run this.
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME AR \
-LOB_CODE ARXAGFMX \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE RTF \
-FILE_NAME $AR_TOP/patch/115/publisher/templates/US/ARXAGF.rtf \
-CUSTOM_MODE FORCE


2. Once 1 is successful run this.

UPDATE xdo_templates_b
SET mls_language = 'en', mls_teritorry = 'US'
WHERE application_short_name = 'AR'
and template_code = 'ARXAGFMX';

3. Then restart the failed worker which was working ARXAGF.xlf.

For more info rollow the below document

Patch 12702898:R12.AR.A Fails With oracle.apps.xdo.XDOException: Template ARXAGFMX not found (Doc ID 1495265.1)

RPM Packages and Kernel Parameter Required for EBS upgrade to R12.2 :

RPM Packages Required for EBS upgrade to R12.2 :

xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386
binutils-2.17.50.0.6-9.0.1.x86_64
sysstat-7.0.0-3.el5.x86_64
unixODBC-2.2.11-7.1.i386
unixODBC-devel-2.2.11-7.1.i386
unixODBC-2.2.11-7.1.x86_64
unixODBC-devel-2.2.11-7.1.x86_64
libaio-devel-0.3.106-3.2.x86_64


Kernel Parameter

Parameter Value
kernel.semmsl 2561
kernel.semmns 320001
kernel.semopm 1001
kernel.semmni 1421
kernel.shmall 20971524
kernel.shmmax Half the size of the physical memory (in bytes), and at least 21474836482
kernel.shmmni 4096
kernel.msgmax 8192
kernel.msgmnb 65535
kernel.msgmni 2878
fs.file-max 131072
net.ipv4.ip_local_port_range 10000 650003
net.core.rmem_default 262144
net.core.rmem_max 4194304
net.core.wmem_default 262144
net.core.wmem_max 262144


set using the following entry in the /etc/sysctl.conf file: kernel.sem = 256 32000 100 142

oracle.apps.fnd.framework.OAException: Could not load application module 'oracle.apps.fnd.sso.login.server.MainLoginPageAM'

Error :

Exception Details.
oracle.apps.fnd.framework.OAException: Could not load application module
'oracle.apps.fnd.sso.login.server.MainLoginPageAM'
...
## Detail 0 ##
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_TRANSACTION_ID.
at oracle.apps.fnd.framework.webui.OAJSPHelper.getTransactionId(OAJSPHelper.java:350)
at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:133)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1279)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:532)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:420)
at _OA._jspService(_OA.java:216)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:335)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:478)
...

Relative Env:

Oracle Apps 11i and R12

Reason :

1. Increase or high no of Invalid Objects
2. Tablespace got filled


Solutions:

1. Check no of invalid objects.

select owner, object_type, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type;

2. Compile the objects with  @?/rdbms/admin/utlrp.sql from sys user.
3. if required you can use adadmin to compile invalids
4. Check tablespace free space and add space if required.


For high traffic instance it is better to purge the inactive session periodically.

Schedule "Purge Inactive Sessions" program from sysadmin, it purges icx_trunsuctions

Follow the below note for more details.

Can Not Login To The Applications: oracle.apps.fnd.framework.OAException: FND_NO_TRANSACTION_ID error (Doc ID 737960.1)

When you are starting the DB-Tier Configuration script during an E-Business Suite Clone, you see following failure : RC-00118: Error occurred during creation of database

Error:

When you are starting the DB-Tier Configuration script during an E-Business Suite Clone, you see following failure :
...

getConnectionUsingAppsJDBCConnector() -->
  APPS_JDBC_URL='null'
  Trying to get connection using SID based connect descriptor
getConnection() -->
  sDbHost    :
  sDbDomain  :
  sDbPort    :
  sDbSid     :
  sDbUser    : APPS
  Trying to connect using SID...
getConnectionUsingSID() -->
  JDBC URL: jdbc:oracle:thin:@:
  Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
  Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SERVICE_NAME=)))
  Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
  Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
  JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SERVICE_NAME=.)))
  Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
  Connection could not be obtained; returning null

RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase
...


Relevent env:

Oracle Applications Manager - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Applications Technology Stack - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Applications DBA - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.


Cause:

DNS is not set properly.

Solutions:

Check that DNS is working properly as given below.

nslookup .

You can use the /etc/hosts also if you are using it for resolving the names.



Follow this document for more details.

' adcfgclone dbTier' fails with error 'RC-00118' Error occurred during creation of database - 'Raised by oracle.apps.ad.clone.ApplyDatabase' (Doc ID 1299429.1)

ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_3"

Error message

*** 2014-10-05 09:00:07.681
*** SESSION ID:(5646.61909) 2014-10-05 09:00:07.681
*** CLIENT ID:() 2014-10-05 09:00:07.681
*** SERVICE NAME:(SYS$USERS) 2014-10-05 09:00:07.681
*** MODULE NAME:(DBMS_SCHEDULER) 2014-10-05 09:00:07.681
*** ACTION NAME:(MGMT_CONFIG_JOB_2_3) 2014-10-05 09:00:07.681

ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_3"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1



To supress this error message, we have to disable it as per the (https://blogs.oracle.com/UPGRADE/entry/upgrade_to_11_2_0)

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

It has been tried in stage First.

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

PL/SQL procedure successfully completed.



Another way is to reconfigure this OCM if you want to use this feature.

Cause:

SQL>select * from dba_directories where DIRECTORY_NAME like '%OCM_CONFIG%';

MGMT_DB_LL_METRICS wants to write to ORACLE_OCM_CONFIG_DIR2, which is not created by the built-in instrumentation scripts.

Sotutions:

1. You have to run the below command to check whether OCM is configured.

$ORACLE_HOME/ccr/bin/deployPackages -l

( some time there is a permission issue on $ORACLE_HOME/ccr/bin/deployPackages )
-- Then you can check it and give execute permission to $ORACLE_HOME/ccr/bin/deployPackages
-- Then try again

Installation Steps:

If OCM is not installed, please install it using steps given in the below document. It will create the directory during the installation. NO need to follow step 2.

Oracle Configuration Manager Installation and Administration Guide


2.
Connect as sysdba

SQL> @ORACLE_HOME/ccr/admin/scripts/ocmjb10.sql

SQL> @ORACLE_HOME/ccr/admin/scripts/execute execocm.sql

3.

Output should be like, if everything is fine :

SQL> select * from dba_directories where DIRECTORY_NAME like '%OCM_CONFIG%';

OWNER    DIRECTORY_NAME         DIRECTORY_PATH
-------- ---------------------- ------------------------------------------------------------------
SYS      ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.3/dbhome_1/ccr/state
SYS      ORACLE_OCM_CONFIG_DIR  /u01/app/oracle/product/11.2.0.3/dbhome_1/ccr/hosts/dc2oda-1/state

**Directory ORACLE_OCM_CONFIG_DIR2 should be returned in output.

Daily archive log growth in Oracle Database

SQL> set line 300
SQL> set pages 300
SQL> select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,
  2       sum(blocks*block_size)/1048576/1024 "Size(GB)",
     count(sequence#) "Total Archives"
     from (select distinct sequence#,
                  thread#,
                  COMPLETION_TIME,
                  blocks,
                  block_size
           from v$archived_log
           where completion_time>=sysdate-14)
     group by to_char(COMPLETION_TIME,'DD/MON/YYYY')
     order by 1;  3    4    5    6    7    8    9   10   11   12

DAY                    Size(GB) Total Archives
-------------------- ---------- --------------
17/JUL/2014          42.7481585             72
18/JUL/2014          130.772517            187
19/JUL/2014          208.798485            276
20/JUL/2014          179.345992            238
21/JUL/2014          135.881896            171
22/JUL/2014           101.37771            140
23/JUL/2014          103.462677            141
24/JUL/2014          108.522655            161
25/JUL/2014          96.6876049            134
26/JUL/2014          95.9838228            125
27/JUL/2014          96.2189264            135
28/JUL/2014          106.510056            160
29/JUL/2014          92.0687962            122

13 rows selected.

SQL>

How to generate the XML Debug Logs (Output Post Processor) for 'Payment Register'.

How to generate the XML Debug Logs (Output Post Processor) for 'Payment Register'.


1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
 - create a temp directory - mkdir temp
 - set the permissions - chmod 777 temp
 - Use pwd and copy this directory location - it will be used in step 2


[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp


2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
   -In the results - the complete path is present showing where the rt.jar resides
   - change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg

Enter these two lines:
LogLevel=STATEMENT
LogDir=complete_path_to_$XDO_TOP/temp directory you created


[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$




3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh apps/apps



4. Engage PO Output for Communication - using standard style sheet

Purchasing - Reports/Run
 - Pass in the parameters to print the purchase order
 - Leave the Debug parameter as the default
 - Submit the request

5. Retrieve the logs

Move to $XDO_TOP/temp
 - Here there are files crearted, please collect these and put into a zip file.

Process to generate EBS PL/SQL profiler for a concurrent program.

Process to generate EBS PL/SQL profiler for a concurrent program.

Setup the profile option
• Responsibility: System Administrator
• Navigate: Profiles > System
• Select check box for User (User level) and provide the user name
• Query Profile Option Concurrent: Allow Debugging
• Click Find button

Set profile to Yes on the User level. (Otherwise Debug Options is not active under Submit Request Screen)

Run Concurrent Program with pl/sql profiler option setup
• Logon to the Responsibility that runs the Concurrent Program
• Select the concurrent program on Name field and provide necessary Parameters in the Submit Request screen
• (In this doc., Assume We have active user as an example concurrent program.)
• In the Submit Request Screen, click on Debug Options button.
• Select the Checkbox for PL/SQL Profiler
• And then click OK button.


How to generate pl/sql profiler output for the concurrent program under EBS? (Doc ID 808005.1)

Cconcurrent Manager cleaning scripts

SELECT  concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM    fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE   fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND     fcq.application_id = fcp.queue_application_id
AND     process_status_code not in ('K', 'S')
AND     concurrent_queue_id=;


If it returen something


UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL
AND     concurrent_queue_id=


select * from fnd_concurrent_processes where concurrent_queue_id=;

no rows selected

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0 where concurrent_queue_id=;

1 row updated.

SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL AND concurrent_queue_id=;

no rows selected

SELECT concurrent_queue_name manager,control_code ccode FROM fnd_concurrent_queues where concurrent_queue_id= ;

MANAGER                        C
------------------------------ -
Invoice_Processing_Manager

1 row selected.

UPDATE fnd_concurrent_queues SET target_node = null where  concurrent_queue_id=;

1 row updated.

commit;

Commit complete.

Running concurrent requests in Oracle Apps EBS (11i,R12)

Set Pages 1000
Set head on
Column Manager   Format A12
Column Request   Format 999999999
Column Program   Format A30
Column User_Name Format A15
Column Started   Format A15
Column OSprocess Format A9
Column SPID      Format A9
Column LocalProcess Format A9
prompt Managers that are currently running a request;
prompt ;
Select /*+ RULE */ substr(Concurrent_Queue_Name,1,12) Manager,
       Request_Id Request, User_name,
       Fpro.OS_PROCESS_ID OSprocess,
      fcr.oracle_process_id LocalProcess,
       substr(Concurrent_Program_Name,1,35) Program, Status_code,
       To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
  from apps.Fnd_Concurrent_Queues Fcq, apps.Fnd_Concurrent_Requests Fcr,
      apps.Fnd_Concurrent_Programs Fcp, apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes Fpro
 where
       Phase_Code = 'R' And
       Status_Code <> 'W' And
       Fcr.Controlling_Manager = Concurrent_Process_Id       And
      (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    And
       Fcq.Application_Id      = Fpro.Queue_Application_Id ) And
      (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
       Fcr.Program_Application_Id = Fcp.Application_Id )     And
       Fcr.Requested_By = User_Id
       order by Started
/

Concurrent request details

set line 300
set pages 200
select r.request_id, s.inst_id,s.sid, s.serial#, s.status,
 substr(r.oracle_process_id, 1, 13) "REQUEST OS",
 r.status_code,r.phase_code, substr(fp.OS_PROCESS_ID, 1, 13) "CM OS ID"
from apps.fnd_concurrent_requests r,
 apps.fnd_concurrent_processes fp,
 gv$session s,
 gv$process p
where r.controlling_manager = fp.concurrent_process_id and
 r.oracle_process_id = p.spid and
 s.inst_id = p.inst_id and
 p.addr = s.paddr and
 r.request_id in (&reqid) order by r.request_id;

Oracle Database Tablespace status

SET lines 202 pages 99 feedback off
COLUMN tablespace_name        format a25             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes/1024/1024 total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       )/1024/1024 total_used_space,
       fragments.total_tbs_free_bytes/1024/1024 total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs/1024/1024 max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
                )
             )
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);

Concurrent Manager monitoring scripts

set line 200
set pages 100
col "Concurrent Manager's Name" format a60
spool comcurrent_manger_status.lst
SELECT b.USER_CONCURRENT_QUEUE_NAME AS "Concurrent Manager's Name",
       a.max_processes AS "TARGET Processes",
       a.running_processes AS "ACTUAL Processes",
       'Mismatch' Status
FROM apps.fnd_concurrent_queues a,apps.FND_CONCURRENT_QUEUES_VL b
 WHERE a.CONCURRENT_QUEUE_NAME = b.CONCURRENT_QUEUE_NAME
 and a.max_processes <> a.running_processes;

SELECT b.Pending_Standby,
       b.Pending_Normal,
       b.name AS "Concurrent Manager's Name",
       a.max_processes AS "TARGET Processes",
       a.running_processes AS "ACTUAL Processes",
       b.Running_Normal
  FROM apps.fnd_concurrent_queues a,
       (  SELECT c.USER_CONCURRENT_QUEUE_NAME Name,
                 c.CONCURRENT_QUEUE_NAME,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'P', DECODE (d.STATUS_CODE, 'Q', 1, 0),
                            0))
                    Pending_Standby,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'P', DECODE (d.STATUS_CODE, 'I', 1, 0),
                            0))
                    Pending_Normal,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'R', DECODE (d.STATUS_CODE, 'R', 1, 0),
                            0))
                    Running_Normal
            FROM apps.FND_CONCURRENT_QUEUES_VL c, apps.FND_CONCURRENT_WORKER_REQUESTS d
           WHERE     c.concurrent_queue_id = d.concurrent_queue_id
                 AND d.Requested_Start_Date <= SYSDATE
        GROUP BY c.USER_CONCURRENT_QUEUE_NAME, c.CONCURRENT_QUEUE_NAME) b
 WHERE a.CONCURRENT_QUEUE_NAME = b.CONCURRENT_QUEUE_NAME;

How to create restore point in Oracle Database 11g

To create restore point you need to do the below steps.

1. Archive log should be enabled.
2. db_flashback_retention_target need to be set
3. db_recovery_file_dest_size need to be set
4. SHUTDOWN IMMEDIATE;
5. STARTUP MOUNT;
6. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

SQL> show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 300G

recovery_parallelism                 integer     0

SQL> ALTER SYSTEM SET db_recovery_file_dest='+ORCL';

System altered.

SQL>  ALTER SYSTEM SET db_recovery_file_dest='+ORCL' sid='*';

System altered.

SQL> CREATE RESTORE POINT BEFORE_CHANGES GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

To restore database need to follow the below steps.

1. SHUTDOWN IMMEDIATE;
2. STARTUP MOUNT;
3. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

Query to get user details with responsibility list in Oracle Apps EBS

select fu.user_name, frt.RESPONSIBILITY_NAME, furg.end_date

from

fnd_user_resp_groups furg,

FND_RESPONSIBILITY fr,

fnd_responsibility_tl frt,

fnd_user fu

where fu.user_id = furg.user_id

and furg.responsibility_id = fr.RESPONSIBILITY_ID

and frt.responsibility_id = fr.RESPONSIBILITY_ID

order by 1;

Above query is to list the user with responsibility in Oracle Apps

How to change password in Oracle Transportation Management(OTM)

How to change password in Oracle Transportation Management(OTM)

If you want to change other usernames/passwords, then the list expands beyond that. I believe the the following is a complete list of OTM DB users:
glogowner
glogdba
glogdev
glogload
archive
build
reportowner


Step 1: Shutdown OTM application services.

a) Stop the Web Server



b) Stop the Application Server



Step 2: Change the database passwords.
Use alter command to change the passwords

Step 3: Encode the password for GLOG.
i) Run the below command to encode the password
java glog.util.appclass.Base64Encoding


java glog.util.appclass.Base64Encoding

ii)
Backup for glog.properties.

iii)
Update glog.properties


glog.crypto.password.mode=Encrypt

For glogdba password update following property



glog.database.password=



For glogload password update following property



glog.database.load.password=

Step 4: Start application services for OTM.

How to control Oracle Management Agent .... Part 3(How to troubleshoot Errors related to startup of Management Agent)

How to troubleshoot Errors related to startup of Management Agent:
If the EM agent is not starting we have to check the emctl.log file and the also $AGENT_INSTANCE_HOME/sysman/logs directory for more logs.
Below are some common issues and hint to troubleshoot them,

1 Management Agent starts up but is not ready
The Management Agent goes through the following process when it starts up:

a.Starting up (the Management Agent has just received the request to start up and is going to start the initialization sequence)

b.Initializing (the Management Agent is iterating over each of its components and is initializing them)

c.Ready (All components have been initialized and the Management Agent is ready to accept requests)

The command to start the Management Agent (emctl start agent) has a default timeout of 120 seconds. At the end of that timeout, it will return control to the caller and will indicate what the last state of the Management Agent was when it returns control. Depending on the number of targets being monitored by the Management Agent, step 2 listed above could take a long time and it is possible that when the command exits, the state of the agent is "Initializing" and the command reports that the "agent is running but is not ready".

You can increase the timeout by setting an environment variable "EMAGENT_TIME_FOR_START_STOP". The value should indicate the number of seconds to wait before returning control to the caller.

2 Management Agent fails to start because of time zone mismatch between agent and OMS
The Management Agent uses the time zone set in emd.properties file. During the install process of the Management Agent, the agent and the host target are registered with the OMS along with the time zone. If the Management Agent's time zone is modified at any point after the installation, the OMS will signal the Management Agent to shut down as soon as it detects this mismatch.

To reset the Management Agent's time zone, run the following command:

emctl resettz agent

3 Agent fails to start due to possible port conflict
If the Management Agent cannot start and emctl reports that there is a possible port conflict, check the Management Agent's port (based on emd.properties:EMD_URL) and see if there is another application, such as another agent, running on the machine that is already bound to the port.

To resolve this issue, stop the application currently bound to the Management Agent's port.

4 Agent secure/unsecure fails
Securing or unsecuring of the Management Agent can fail if the password to secure the agent against the OMS is incorrect or if the OMS is locked or down. You can find the reason for the failure in the /sysman/log/secure.log file.

How to control Oracle Management Agent .... Part 2 (How to control Oracle Management Agent in Windows)

How to stop, start Management Agent on Windows

Installation of Oracle Management Agent on a Windows system, creates one new service in the Services control panel.
From that control pannel you can start and stop the services

Component Service Name Format Description
Oracle Management Agent
  OracleAgent
For example:

OracleOraHome1Agent
 Use this to start and stop the Management Agent.


If you have problem in stoping in Windows NT platform (not to other Windows platforms, such as Windows 2000 or Windows XP systems) You can also use the below command to stop agent.

$PROMPT> \bin\emctl istop agent
After stopping the Management Agent using the emctl istop agent command, start the Management Agent using the Services control panel.


How to check the Status of the Management Agent on Windows:

To check the status of the Management Agent on Windows systems:

1.Change directory to the following location in the AGENT_INSTANCE_HOME directory:

AGENT_INSTANCE_HOME\bin
2.Enter the following emctl command to check status of the Management Agent:

$PROMPT> emctl status agent

How to control Oracle Management Agent ....Part 1

How to control Oracle Management Agent

Below are the activities mainly requeired for working with OEM Agent. We need the emctl utility to control this.

1. How to stop, start, and check status of Management Agent on UNIX
2. How to stop, start Management Agent on Windows
3. How to check the Status of the Management Agent on Windows
4. How to troubleshoot Errors related to startup of Management Agent 

How to stop, start, and check status of Management Agent on UNIX:

When you start the agent on UNIX systems, it starts the parent watchdog process and the child Java process for the agent. The watchdog monitors the agent Java process and attempts to start it if it fails abnormally.

To start, stop, or check the status of the Management Agent on UNIX systems:

1.You have to go to the AGENT_INSTANCE_HOME/bin directory.

2.Need to run the emctl command with appropriate parameter

Below is an example to stop an agent

$PROMPT> cd AGENT_INSTANCE_HOME/bin
$PROMPT> emctl stop agent

Command:
emctl start agent
 Starts the Management Agent

emctl stop agent
 Stops the Management Agent

emctl status agent
 it shows the status

$ ./emctl status agent
Oracle Enterprise Manager 12c Release 3 Cloud Control 12.1.0.3.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.2.0
OMS Version       : 12.1.0.2.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /scratch/aime/work/agtbase7453/agent_inst
Agent Binaries    : /scratch/aime/work/agtbase7453/core/12.1.0.3.0
Agent Process ID  : 7834
Parent Process ID : 7781
Agent URL         : https://slc01lwi.us.oracle.com:3872/emd/main/
Repository URL    : https://slc01lwi.us.oracle.com:4900/empbs/upload
Started at        : 2013-06-05 01:33:33
Started by user   : aime
LLast Reload       : (none)
Last successful upload                       : 2013-06-20 22:17:50
Last attempted upload                        : 2013-06-20 22:17:50
Total Megabytes of XML files uploaded so far : 20.08
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 62.89%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2013-06-20 22:17:49
Last successful heartbeat to OMS             : 2013-06-20 22:17:49
Next scheduled heartbeat to OMS              : 2013-06-20 22:18:49
---------------------------------------------------------------

Important OS command related to printer

Please go thoroughly with the below commands in OS uses.

1. lpadmin
2. lpstat
3. lpoptions 

lpadmin -p -E -v 'socket://:9100' -m -D 'Description' -L 'Lebel'

lpstat -a

lpoptions -d printer_name

How to collect Memory utilization in linux

How to collect Memory utilization

Ran the below command

sar -rq 1
Linux 2.6.31-201.18.1.el5uek (abcdef.testing.com)    01/25/2014

01:28:07 AM kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad
01:28:08 AM  14303340 250232948     94.59   1688944 144753888  25165816         0      0.00         0

01:28:07 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
01:28:08 AM         2      2085      1.34      1.39      1.36

Average:    kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad
Average:     14303340 250232948     94.59   1688944 144753888  25165816         0      0.00         0

Average:      runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
Average:            2      2085      1.34      1.39      1.36

Memory utilization = (kbmemused-kbcached-)*100/(kbmemfree+kbmemused)

Some uses
Memory utilization = (kbmemused-kbcached-kbbuffers)*100/(kbmemfree+kbmemused)

How to restart Oracle Standby Database(DR Database)

How to restart Oracle Standby Database(DR Database)

1. First stop the managed recovery with the below command. in standby database
              alter database recover managed standby database cancel;
2. Once it is done you have to stop the database using the srvctl or by sqlplus.
3. Startup standby database to mount stage
              startup mount;
4. Then you have to start the managed recovery.
              alter database recover managed standby database using current logfile disconnect from session;

How to move or rename a datafile of a tablespace in oracle database

Datafile movement 12c Database Code

ALTER DATABASE MOVE DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' TO '+DATA_BKP/orcl/datafile/orcl_custom.702.836915343';

In 12c Database you need not have to make the datafile offline to do the changes. You can do it online. It is a new feature. 

For other version you need to follow the below steps.

step1: Bring the datafile offline

ALTER DATABASE DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' OFFLINE;

step 2: Nove the datafile to new location

rman target /

COPY DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' TO '+DATA_BKP';

step 3: Rename the datafile with new details with alter database  

ALTER DATABASE RENAME FILE '+DATA/orcl/datafile/orcl_custom.702.836915343'

TO '+DATA_BKP/orcl/datafile/';

step 4: Make a copy of datafile

rman target /

SWITCH DATAFILE '+DATA_BKP/orcl/datafile/' TO COPY;

step 5: Recover the datafile

RECOVER DATAFILE '+DATA_BKP/orcl/datafile/';

step 6: Bring the datafile online

ALTER DATABASE DATAFILE '+DATA_BKP/orcl/datafile/' ONLINE;

How to take a snapshot in oracle Database

To create a snapshot you have to run the below command in oracle database.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

New snapshot with snap id will be generated.