Move or Rename Tempfile in Oracle

#####  Move tempfile from location  ‘/u01/data/temp01.dbf’ to ‘/u03/data/temp01.dbf’  #####

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
FILE#  FILE_NAME STATUS
-----------------------------------------------
 1  /u01/data/temp01.dbf  ONLINE
 2 /u02/data/temp02.dbf   ONLINE

SQL> ALTER DATABASE TEMPFILE '/u01/data/temp01.dbf' OFFLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
----------------------------------------------------
 1 /u01/data/temp01.dbf  OFFLINE
 2 /u02/data/temp02.dbf  ONLINE


#Copy the old temp files to other location(/u03):

SQL> !cp -p /u01/data/temp01.dbf /u03/data/temp01.dbf;

SQL> ALTER DATABASE RENAME FILE '/u01/data/temp01.dbf' TO '/u03/data/temp01.dbf';

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
  FILE#   FILE_NAME STATUS
--------------------------------------------------------
  1    /u03/data/temp01.dbf OFFLINE
  2   /u02/data/temp02.dbf  ONLINE

SQL> ALTER DATABASE TEMPFILE '/u03/data/temp01.dbf' ONLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
---------------------------------------------------------
 1 /u03/data/temp01.dbf   ONLINE
 2 /u02/data/temp02.dbf   ONLINE


#Remove the old temp file
SQL> !rm -rf /u01/data/temp01.dbf

ORA-01105: mount is incompatible with mounts by other instances

Database startup with srvctl failed with following error :

PRCR-1079 : Failed to start resource ora.database.db
CRS-5017: The resource action "ora.database.db start" encountered the following error:
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

Verified the value of db_recovery_file_dest & db_recovery_file_dest_size parameters in database :

SQL> sho parameter db_recovery

NAME                                 TYPE

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

VALUE

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

db_recovery_file_dest                string

+DATA

db_recovery_file_dest_size           big integer

15G

SQL>

SQL> sho parameter db_recovery

NAME                                 TYPE

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

VALUE

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

db_recovery_file_dest                string

+DATA

db_recovery_file_dest_size           big integer

14G

SQL>

Changed db_recovery_file_dest_size to match on both instances :

SQL> alter system set db_recovery_file_dest_size=15G scope=both;

System altered.

SQL>

Started DB successfully using srvctl.

Query to find Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

How to clear Cache in EBS 11i and R12 environments and in similar environments

Apache / iAS
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server


To clear middle tier cache in release 12:

- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache
(please review Note 759038.1 for details).
In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache


Web Browser

for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.

for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.

for Safari:
- go to Safari menu
- select Empty Cache => Click 'Empty' in the dialogue box
for Netscape:
- go to menu Edit => Preferences,
- choose 'Cache' in 'Advanced' category,
- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.
- close all Netscape windows and restart new browser session.

Jinitiator

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:
- delete all files in directory:

C:\Program Files\Oracle\Jinitiator \jcache\

for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator " icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\\Oracle Jar Cache)


Java
Java/JRE plug-in (Windows)
- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
(you can select Applets, Applications or other files)

JVM

- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's


Portal
- go to the url: http://:/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.


WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://:/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

Workflow Analyzer

The EBS Workflow Analyzer is a script that reviews the current Workflow Footprint, analyzes the configurations, environment, providing feedback, and recommendations on Best Practices and areas of concern.

Go to Doc ID 1369938.1  for more details and script download with a short overview video on it.

Proactive Benefits:

• Immediate Analysis and Output of Workflow Environment
  • Identifies Aged Records
  • Identifies Workflow Errors & Volumes
  • Identifies looping Workflow items and stuck activities
  • Identifies Workflow System Setup and configurations
  • Identifies and Recommends Workflow Best Practices
• Easy To Add Tool for regular Workflow Maintenance
• Execute Analysis anytime to compare trending from past outputs

The Workflow Analyzer presents key details in an easy to review graphical manner.   See the examples below.

Workflow Runtime Data Table Gauge The Workflow Runtime Data Table Gauge will show critical (red), bad (yellow) and good (green) depending on the number of workflow items (WF_ITEMS).

Workflow Error Notifications Pie Chart A pie chart shows the workflow error notification types.

Workflow Runtime Table Footprint Bar Chart
A pie chart shows the workflow error notification types and a bar chart shows the workflow runtime table footprint.

The analyzer also gives detailed listings of setups and configurations. As an example the workflow services are listed along with their status for review:

The analyzer draws attention to key details with yellow and red boxes highlighting areas of review:

You can extend on any query by reviewing the SQL Script and then running it on your own or making modifications for your own needs:

Find more details in these notes:

• Doc ID 1369938.1 Workflow Analyzer script for E-Business Suite Worklfow Monitoring and Maintenance
• Doc ID 1425053.1 How to run EBS Workflow Analyzer Tool as a Concurrent Request

Unable to Start “Output Post Processor” Concurrent Manager

Unable to Start “Output Post Processor” Concurrent Manager

Error in logfile :

$ cat FNDOPP106924.txt
Unable to initialize state monitor.
oracle.apps.fnd.cp.gsm.GenCartCommException: ORA-01403: no data found
ORA-06512: at "APPS.FND_CP_GSM_IPC", line 539
ORA-06512: at line 1

        at oracle.apps.fnd.cp.gsm.GenCartComm.initService(GenCartComm.java:233)
        at oracle.apps.fnd.cp.gsm.GenCartComm.(GenCartComm.java:80)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.init(GSMStateMonitor.java:74)
        at oracle.apps.fnd.cp.gsf.GSMStateMonitor.(GSMStateMonitor.java:62)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.init(GSMServiceController.java:117)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.(GSMServiceController.java:72)
        at oracle.apps.fnd.cp.gsf.GSMServiceController.main(GSMServiceController.java:446)
0.0119120 secs]
$

Checked and found "Service Manager" was down.

Started "Service Manager" and then started "Output Post Processor".

Query a Language Patch

SQL> col PATCH_NAME format a15
SQL> col PATCH_TYPE format a10
SQL> col DRIVER_FILE_NAME format a20
SQL> col PLATFORM format a10
SQL> select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
  2  from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
  3    4  and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
  5  and AP.PATCH_NAME = '17666002';

PATCH_NAME      PATCH_TYPE DRIVER_FILE_NAME     CREATION_DATE   PLATFORM   LANG
--------------- ---------- -------------------- --------------- ---------- ----
17666002        ONE-OFF    u17666002.drv        11-MAR-11       LINUX      US
17666002        ONE-OFF    u17666002.drv        11-MAR-11       GENERIC    FRC
17666002        ONE-OFF    u17666002.drv        11-MAR-11       GENERIC    F

SQL>