Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues

Find Workflow Notification Mailer is up and Running?


SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';


Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory


Find the Failed One’s?


Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';


Check pending e-mail notification that was pending for process.


Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;


Sql> SELECT * FROM wf_notifications WHERE STATUS='OPEN' AND mail_status = 'SENT'
ORDER BY begin_date DESC

Check the Workflow notification has been sent or not?


select mail_status, status from wf_notifications where notification_id=


--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference


1. Verify whether the message is processed in WF_DEFERRED queue


select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= ''
- notification id


2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue


select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '
To check what all mails have went and which all failed ?


Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';


Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';




Users complain that notifications are stuck ?


Use the following query to check to see whatever the users are saying is correct


SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;


E.g o/p of query -


MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent ---
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error




If Mail not received by User ?


select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;


Status – Active
Notification_preference-> Mailtext
Email Address should not be null


Notification not sent waiting to be mailed ?


SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?


$FND_TOP/sql
run wfmlrdbg.sql
******************************

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications

Please see (Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.

Database Initialization Parameter Sizing

Database Initialization Parameter Sizing
 

Sizing recommendations based on the active Oracle E-Business Suite user counts.
Parameter NameDevelopment or Test Instance11-100 Users101-500 Users501-1000 Users1001-2000 Users
processes200
20080012002500
sessions400400160024005000
sga_target Footnote 11G1G2G3G14G
shared_pool_size (csp)N/AN/AN/A1800M3000M
shared_pool_reserved_size (csp)N/AN/AN/A180M300M
shared_pool_size (no csp)400M600M800M1000M2000M
shared_pool_reserved_size (no csp)40M60M80M100M100M
pga_aggregate_target1G2G4G10G20G
Total Memory Required Footnote 2~ 2 GB~ 3 GB~ 6 GB~ 13 GB~ 34 GB
Footnote 1

    The parameter sga_target should be used for Oracle 10g or 11g or 12c based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle 9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g or 12c-based systems, since undo retention is set automatically as part of automatic undo tuning.

    Enabling the 11g or 12c Automatic Memory Management (AMM) feature is supported in EBS, and has been found to be useful in scenarios where memory is limited, as it will dynamically adjust the SGA and PGA pool sizes. AMM is enabled by using the memory_target and memory_max_target initialization parameters. MEMORY_TARGET specifies the system-wide sharable memory for Oracle to use when dynamically controlling the SGA and PGA as workloads change. The memory_max_target parameter specifies the maximum size that memory_target may take. AMM has proven useful for small to mid-range systems as it simplifies both the configuration and management. However, many customers with large production systems have experienced better performance with manually sized pools (or large minimum values for the pools). On Linux, Hugepages has resulted in improved performance; however, this configuration is not compatible with AMM. For large mission-critical applications systems, it is advisable to set sga_target with a minimum fixed value for shared_pool_size and pga_aggregate_target.

Footnote 2

    The total memory required refers to the amount of memory required for the database instance and associated memory, including the SGA and the PGA. You should ensure that your system has sufficient available memory in order to support the values provided above. The values provided above should be adjusted based on available memory so as to prevent paging and swapping.

 

SHOW PROCESS PARAMETER IN ORACLE

SHOW PROCESS PARAMETER IN ORACLE

sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions
And if you are increasing sessions parameter you should consider increasing processes and transactions parameter as well.
Here is the formula you can use to determine their values

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

E.g.
processes=500
sessions=555
transactions=610

sql> alter system set processes=500 scope=both sid='*';
sql> alter system set sessions=555 scope=both sid='*';
sql> alter system set transactions=610 scope=both sid='*';


Current Utilization of process/sessions

select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions');

To change the number of process

SQL> alter system set processes = 100 scope=spfile;

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

Database Initialization Parameter Sizing

This section provides sizing recommendations based on the active Oracle E-Business Suite user counts. The following table should be used to size the relevant parameters:



Parameter NameDevelopment or Test Instance11-100 Users101-500 Users501-1000 Users1001-2000 Users
processes200
20080012002500
sessions400400160024005000
sga_target Footnote 11G1G2G3G14G
shared_pool_size (csp)N/AN/AN/A1800M3000M
shared_pool_reserved_size (csp)N/AN/AN/A180M300M
shared_pool_size (no csp)400M600M800M1000M2000M
shared_pool_reserved_size (no csp)40M60M80M100M100M
pga_aggregate_target1G2G4G10G20G
Total Memory Required Footnote 2~ 2 GB~ 3 GB~ 6 GB~ 13 GB~ 34 GB
      
 >> Footnote 1
The parameter sga_target should be used for Oracle 10g or 11g or 12c based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle 9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g or 12c-based systems, since undo retention is set automatically as part of automatic undo tuning.
 
Enabling the 11g or 12c Automatic Memory Management (AMM) feature is supported in EBS, and has been found to be useful in scenarios where memory is limited, as it will dynamically adjust the SGA and PGA pool sizes. AMM is enabled by using the memory_target and memory_max_target initialization parameters. MEMORY_TARGET specifies the system-wide sharable memory for Oracle to use when dynamically controlling the SGA and PGA as workloads change. The memory_max_target parameter specifies the maximum size that memory_target may take. AMM has proven useful for small to mid-range systems as it simplifies both the configuration and management. However, many customers with large production systems have experienced better performance with manually sized pools (or large minimum values for the pools). On Linux, Hugepages has resulted in improved performance; however, this configuration is not compatible with AMM. For large mission-critical applications systems, it is advisable to set sga_target with a minimum fixed value for shared_pool_size and pga_aggregate_target.
>> Footnote 2

The total memory required refers to the amount of memory required for the database instance and associated memory, including the SGA and the PGA. You should ensure that your system has sufficient available memory in order to support the values provided above. The values provided above should be adjusted based on available memory so as to prevent paging and swapping.

 

Crontab example


 Crontab example

Copying file in same server

==========


* * * * * <command> #Runs every minute
30 * * * * <command> #Runs at 30 minutes past the hour
45 6 * * * <command> #Runs at 6:45 am every day
45 18 * * * <command> #Runs at 6:45 pm every day
00 1 * * 0 <command> #Runs at 1:00 am every Sunday
00 1 * * 7 <command> #Runs at 1:00 am every Sunday
00 1 * * Sun <command> #Runs at 1:00 am every Sunday
30 8 1 * * <command> #Runs at 8:30 am on the first day of every month
00 0-23/2 02 07 * <command> #Runs every other hour on the 2nd of July

==========
CREATE :-

testCron.txt

ENTRY :-

*/1 * * * * scp -rp /Oracle/CRONTAB_TEST/TEST/PRIM/* /Oracle/CRONTAB_TEST/TEST/SEC/

SUBMIT :-

crontab testCron.txt

EDIT :-

crontab -e

REMOVE :-

crontab -r



RMAN Backup Scripts

RMAN Backup Scripts

Whole Database backup

RMAN> backup database;
Default backup location is Flash Recovery Area (FRA) - $ORACLE_HOME/dbs

Check  RFA setup
SQL> show parameter recovery_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 20G

MOS - 305648.1 What is a Flash Recovery Area and how to configure it ?
 

Set  backup other than Flash Recovery Area

RMAN> backup database format '/backups/TEST/df_t%t_s%s_p%p';

 

Backup Individual Tablespace

RMAN> backup tablespace SYSTEM, UNDOTBS, USERS; 


Backup Individual Datafile
RMAN> backup datafile 6;

RMAN> backup datafile 6 format '/backups/TEST/df_t%t_s%s_p%p';

RMAN> backup datafile 2,4,6,8,10;

RMAN> backup datafile '/Data1/proddata/system01.dbf'; 


Backup Controlfile & Spfile

RMAN> backup current controlfile;

RMAN> backup current controlfile format '/backups/TEST/df_t%t_s%s_p%p';

RMAN> backup spfile; 


Backup Archivelogs
RMAN> backup archivelog all;
 

>> Time based

RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
>> With delete input option

RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/TEST/%d_archive_%T_%u_s%s_p%p'; 


Backup whole database including archivelogs
RMAN> backup database plus archivelog;
 

>> With delete input option

RMAN> backup database plus archivelog delete input;

RMAN> backup database plus archivelog delete input format '/backups/TEST/df_t%t_s%s_p%p';




----------

Determine when your computer was last rebooted?

Determine when your computer was last rebooted?

Just run cmd.exe with this command


systeminfo| find "System Boot Time"

CHECK TEMPLATE NAME

CHECK TEMPLATE NAME

SQL> select distinct t.USER_CONCURRENT_PROGRAM_NAME,tt.TEMPLATE_NAME ,
fa.APPLICATION_NAME
from apps.fnd_concurrent_programs_tl t,
apps.fnd_concurrent_programs b,
apps.XDO_TEMPLATES_TL tt,
apps.fnd_application_tl fa
WHERE b.application_id = t.application_id
AND b.concurrent_program_id = t.concurrent_program_id
AND t.LANGUAGE = USERENV ('LANG')
and b.EXECUTION_METHOD_CODE='P'
and t.USER_CONCURRENT_PROGRAM_NAME like 'KK%'
and b.OUTPUT_FILE_TYPE='XML'
and b.application_id=fa.application_id
and fa.LANGUAGE='US'
and b.ENABLED_FLAG='Y'
and fa.application_id<>800
and tt.template_code=b.CONCURRENT_PROGRAM_NAME

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

COMPILE FMB TO CREATE FMX

COMPILE FMB TO CREATE FMX

GO TO THE LOCATION OF FMB FILE

$ frmcmp_batch module=XXXXXXLCIT.fmb userid=apps/**** Module_Type=FORM Output_File=/Apps/PROD/apps/apps_st/appl/inv/12.0.0/forms/US/XXXXXXLCIT.fmx Compile_All=special

Batch=NO

$ frmcmp_batch module=$AU_TOP/forms/US/GLXBDENT.fmb userid=apps/appsnew1 output_file=$GL_TOP/forms/US/GLXBDENT.fmx compile_all=special batch=yes

>> COMPILE PLL TO CREATE PLX

frmcmp_batch userid=apps/apps module=INVLTENT.pll output_file=INVLTENT.plx module_type=library compile_all=special


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

READ ONLY USER

READ ONLY USER

SQL> create tablespace testuser datafile '/data1/proddata/testuser.dbf'size 100m

SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='TESTUSER'

SQL> create user testuser identified by testuser

SQL> alter user testuser default tablespace testuser

SQL> grant connect to testuser

SQL> grant select on dba_source to testuser

SQL> grant select on dba_objects to testuser

SQL> grant select any table to testuser

(INCASE IF GIVING COMPLETE GRANT ON ANY PACKAGE OR PROCEDURE)

SQL> grant execute, debug on <PACKAGE / PROCEDURE NAME > to testuser

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

To compile invalid objects from script

To compile invalid objects from script

SQL> select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID' and object_type in

('PACKAGE','FUNCTION','PROCEDURE');

>>> To View Oracle Compile invalid object Errors

select * from SYS.USER_ERRORS where NAME = <object_name> and type = <object_type>

e.g. select * from SYS.USER_ERRORS where NAME = 'AST_UWQ_OLIST_WORK_ACTION' and TYPE = 'PACKAGE BODY'

The type column can be types such as the following:

PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER

INVALID OBJECTS

INVALID OBJECTS

>>> TO check count of invalid objects
select count(*) from dba_objects where status='INVALID';

>>> To identify invalid objects

COLUMN object_name FORMAT A30

SELECT owner, object_type, object_name,status FROM dba_objects WHERE status='INVALID'ORDER BY owner, object_type, object_name;


>>> CHECK INVALID OBJECTS BY GROUP

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


>>> CHECK INVALID OBJECTS BY GROUP

select owner, object_name, object_type from dba_objects where status ='INVALID' order by 1,2;

WORKFLOW NOTIFICATION COUNT

Workflow Notification Count

SQL> select count(*) from wf_notifications where mail_status='MAIL' and status='OPEN';


SQL> select component_status from apps.fnd_svc_components where component_id = (select component_id from apps.fnd_svc_components where component_name = 'Workflow

Notification Mailer');


SQL> SELECT component_name, component_status, component_status_info FROM fnd_svc_components_v WHERE component_name like 'Workflow%';

USERS WITH SYSADMIN PRIVILEGES

USERS WITH SYSADMIN PRIVILEGES
SQL> SELECT fu.*
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';

Count number of concurrent_users connected to Oracle Apps

Count number of concurrent_users connected to Oracle Apps


SQL> select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d where b.paddr =

c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = ‘USER_NAME’ OR 1=1);


Past 1 hour.
SQL> select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

Past 1 day.
SQL> select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1 and user_id != '-1';

Last 15 minutes.

SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id,

disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

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

Database Disaster Recovery using only RMAN Backups

Database Disaster Recovery using only RMAN Backups

Before we take our case to the RMAN Backups, 2 pre-conditions need to be fulfilled:

1. A Server (the same server or a new one) should be made available in a state where Oracle
Product can be installed; i.e., it should be brought online with a functional Operating System
and necessary Environment Configurations, same as how it was before the crash.

2. Same version of Oracle Product (including any patch sets) needs to be installed with which the RMAN Backup was taken. This is critical for the recovery to be successful.
For the demonstration, the Operating System used Microsoft Windows 2003 and the Oracle
Database Product used 10g R2 (10.2.0.4) Standard Edition.

The following steps would outline the procedures for recovering the Database using only RMAN Backup:
1. Create the Oracle Service
2. Create the relevant folder hierarchy under <ORACLE_BASE> directory
3. Restore the SPFILE from autobackup, and use the SPFILE to start the instance in NOMOUNT state
4. Restore the controlfile from autobackup, and MOUNT the database
5. Restore the Database from RMAN backup files
6. Recover the Database from RMAN backup files, and OPEN the database with RESETLOGS

GETTING STARTED
1. Create the Oracle Service
We need to create an Oracle Service using 'oradim' utility, without passing any PFILE information
C:\>oradim -new -sid DBTEST -intpwd DBTEST
Instance created.

NOTE: 10g onwards, we can directly specify the SPFILE for oracle service creation; thanks to the newly introduced
SPFILE option in oradim utility.

2. Create the relevant folder hierarchy under <ORACLE_BASE> directory
Now, we need to create the relevant directories for Oracle Database, as follows:
a. Create BDUMP, CDUMP, and UDUMP directories under '<ORACLE_ BASE>\admin'
directory,

b. Create <DATABASE_NAME> directory under '<ORACLE_BASE>\oradata' directory; in our
case DBTEST.

3. Restore the SPFILE from autobackup, and use the SPFILE to start the
instance in NOMOUNT state
Here, we need to connect to the Recovery Manager using the ‘rman’ utility, and then start the Recovery process. First we need to set the Database ID (DBID) of the Database. At times, we may or may not know the DBID of the Database. There are 2 ways to find the DBID:

a. If RMAN Backup text logs are maintained, the DBID can be found when the initial connection
is made to the target , or


b. If autobackup is enabled and has the autobackup format set (let’s say the format is '%F'), then
the filename of the autobackup has the DBID in it.

In our case, 'c-1106578690-20100408-00' is the name of the autobackup file; so the DBID should be
'1106578690'.

Next, we need to set the autobackup location, which will help us identify the location of all the RMAN
files.
In our case, the RMAN autobackup and backup files are located at '\\testdb\dbtest\'.

C:\>set oracle_sid=DBTEST
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 8 11:40:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> set dbid=1106578690;
executing command: SET DBID
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'C:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\INITDBTEST.ORA'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1247828 bytes
Variable Size 58721708 bytes
Database Buffers 92274688 bytes
Redo Buffers 7139328 bytes
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

NOTE: It is a good practice to generate the text logs whenever your RMAN backup runs and have the command “show all” run before the backup and “list backup” after the backup completes. This ensures that we have information on the complete set of RMAN configurations and a list of all the backups with their details in the text log.

Next, we need to restore the SPFILE from the autobackup file and then use it to restart the instance in NOMOUNT state. You can approach this in two ways, as demonstrated below:

a. Restoring the SPFILE from the autobackup to a non-default location:
We can restore the SPFILE to a non-default location by using any one of the below 2 methods:

RMAN> restore spfile to 'C:\spdbtest01.ora' from autobackup;
Starting restore at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20100408
channel ORA_DISK_1: autobackup found: \\testdb\dbtest\c-1106578690-20100408-
00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-APR-10

OR,

RMAN> restore spfile to 'C:\spdbtest02.ora' from "\\testdb\dbtest\c-
1106578690-20100408-00";
Starting restore at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: \\testdb\dbtest\c-1106578690-20100408-
00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-APR-10

Now, we need to restart the instance in NOMOUNT state using the restored parameter file.
RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should be referencing the SPFILE, so as to start the instance. For this, we need to create a text file, 'init<SID>.ora', and then mention the path and name of the restored SPFILE in it. In our case, the ‘initDBTEST.ora’ would contain only the following statement:

SPFILE=C:\spdbtest01.ora

Next, we need to start the instance in NOMOUNT state using the newly created PFILE (referencing the SPFILE).

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started
Total System Global Area 377487360 bytes
Fixed Size 1249080 bytes
Variable Size 113246408 bytes
Database Buffers 255852544 bytes
Redo Buffers 7139328 bytes

b. Restoring the SPFILE from the autobackup to the default location:
We can restore the SPFILE to the default location (“<ORACLE_HOME>/database” in Windows) by using any one of the below 2 methods:

RMAN> restore spfile from autobackup;
Starting restore at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20100408
channel ORA_DISK_1: autobackup found: \\testdb\dbtest\c-1106578690-20100408-
00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-APR-10

OR,

RMAN> restore spfile from "\\testdb\dbtest\c-1106578690-20100408-00";
Starting restore at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: \\testdb\dbtest\c-1106578690-20100408-
00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-APR-10

The SPFILE has been restored in the DATABASE directory of the default home location. Oracle will now automatically look for an SPFILE in this location when you try to start the instance

RMAN> shutdown immediate
Oracle instance shut down

Start the instance in NOMOUNT state. Here, we do not need to reference any PFILE or SPFILE, which makes the task a lot simpler.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 377487360 bytes
Fixed Size 1249080 bytes
Variable Size 113246408 bytes
Database Buffers 255852544 bytes
Redo Buffers 7139328 bytes

Once the database is restarted in NOMOUNT state, we need to restore the controlfiles so as to restore and recover the database using the RMAN Backups.
Again here, we can restore the controlfiles by using any one of the below 2 methods:

RMAN> restore controlfile from autobackup;
Starting restore at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: C:\oracle\flash_recovery_area
database name (or database unique name) used for search: DBTEST
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100408
channel ORA_DISK_1: autobackup found: \\testdb\dbtest\c-1106578690-20100408-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 08-APR-10

OR,

RMAN> restore controlfile from "\\testdb\dbtest\c-1106578690-20100408-00";
Starting restore at 08-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 08-APR-10

5. Restore the Database from RMAN backup files
With the restored controlfiles, we are now in a position to restore the Database. We need to mount the Instance first and then restore the database files from the backup.

RMAN> alter database mount;
database mounted
RMAN> restore database;
Starting restore at 08-APR-10
Starting implicit crosscheck backup at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 08-APR-10
Starting implicit crosscheck copy at 08-APR-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-APR-10
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\ORT_TBSP01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\OR_TBSP01.DBF
restoring datafile 00008 to C:\ORACLE\ORADATA\DBTEST\OR_TBSP02.DBF
restoring datafile 00009 to C:\ORACLE\ORADATA\DBTEST\OR_TBSP03.DBF
restoring datafile 00010 to C:\ORACLE\ORADATA\DBTEST\OR_TBSP04.DBF
restoring datafile 00011 to C:\ORACLE\ORADATA\DBTEST\ORT_INDX01.DBF
restoring datafile 00012 to C:\ORACLE\ORADATA\DBTEST\ORT_INDX02.DBF
restoring datafile 00013 to C:\ORACLE\ORADATA\DBTEST\OR_INDX01.DBF
restoring datafile 00014 to C:\ORACLE\ORADATA\DBTEST\OR_INDX02.DBF
restoring datafile 00015 to C:\ORACLE\ORADATA\DBTEST\OR_INDX03.DBF
restoring datafile 00016 to C:\ORACLE\ORADATA\DBTEST\OR_TEST01.DBF
restoring datafile 00017 to C:\ORACLE\ORADATA\DBTEST\PY_TBSP01.DBF
restoring datafile 00018 to C:\ORACLE\ORADATA\DBTEST\IT_INDX01.DBF
restoring datafile 00019 to C:\ORACLE\ORADATA\DBTEST\IT_TBSP01.DBF
restoring datafile 00020 to C:\ORACLE\ORADATA\DBTEST\PY_TBSP02.DBF
restoring datafile 00021 to C:\ORACLE\ORADATA\DBTEST\PY_INDX01.DBF
channel ORA_DISK_1: reading from backup piece
\\TESTDB\DBTEST\DBTEST_04LAJPNP_1_1_20100408
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_04LAJPNP_1_1_20100408
tag=TAG20100408T110945
channel ORA_DISK_1: restore complete, elapsed time: 00:31:38
Finished restore at 08-APR-10

Once restoration of the database is complete, we need to recover the database by applying the archivelogs from the backup.

RMAN> recover database;
Starting recover at 08-APR-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=32
channel ORA_DISK_1: reading from backup piece
\\TESTDB\DBTEST\DBTEST_05LAJR4F_1_1_20100408
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05LAJR4F_1_1_20100408
tag=TAG20100408T113335
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
archive log
filename=C:\ORACLE\FLASH_RECOVERY_AREA\DBTEST\ARCHIVELOG\2010_04_08\O1_MF_1_32_
5VVJG77X_.ARC thread=1 sequence=32
channel default: deleting archive log(s)
archive log
filename=C:\ORACLE\FLASH_RECOVERY_AREA\DBTEST\ARCHIVELOG\2010_04_08\O1_MF_1_32_
5VVJG77X_.ARC recid=33 stamp=715790711
unable to find archive log
archive log thread=1 sequence=33
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/08/2010 14:45:21
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33 lowscn
27801294
Don't panic when you see the above Error Message. You just need to open the database with
RESETLOGS option, as there are no more archivelogs to apply.

RMAN> alter database open resetlogs;
database opened

The Database Disaster Recovery using RMAN is now complete.

Change DATE TIME Linux

Change DATE TIME Linux
Check date
$ date



Change Date
date -s "2 OCT 2006 18:00:00"


=========================

CANCEL REQUEST from BACKEND

CANCEL REQUEST from BACKEND

SQL> update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id='REQUEST_ID';
SQL> update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&req_id;

CANCEL ALL REQEUST OF ONE USER

SQL> select * from fnd_user where user_name like 'user_name';

SQL> update fnd_concurrent_requests set status_code='D', phase_code='C' where requested_by=:p_user_id;


===============================

Printer url linux Oracle

Printer url linux Oracle

http://localhost.localdomain:631/

===============================

How do you check whether Trace is enabled to particular concurrent program from the back end?

How do you check whether Trace is enabled to particular concurrent program from the back end?
 

Solution:

1. select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Program Name%'; (You will get Id)

2. select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id ='concurrent program id';

===============================

ORA-04021 Timeout when compiling package

ORA-04021 Timeout when compiling package

1) Find who is accessing the object using below sql
     SELECT * FROM v$access WHERE object = 'XXRH_SUPX_ORCL_PKG';

2) Check session details using below query. SID will be from the result of above query
    SELECT * FROM V$SESSION WHERE SID IN (1040,1529,1016)

3) Kill the session which is accessing the package.
   ALTER SYSTEM KILL SESSION '1016,58335'


===============================

Extract User Password

Extract User Password
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE APPS.get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/
--------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.get_pwd
AS
   FUNCTION decrypt (
      KEY     IN VARCHAR2,
      VALUE   IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/


---------------------------------------
SELECT usr.user_name,
       get_pwd.
        decrypt (
          (SELECT (SELECT get_pwd.
                           decrypt (fnd_web_sec.get_guest_username_pwd,
                                    usertable.encrypted_foundation_password)
                     FROM DUAL)
                     AS apps_password
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                fnd_web_sec.get_guest_username_pwd,
                                1,
                                INSTR (fnd_web_sec.get_guest_username_pwd,
                                       '/')
                                - 1)
                        FROM DUAL)),
          usr.encrypted_user_password)
          PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'TESTUSER';

Privilege to view Package body without Execute Grant

 Privilege to view Package body without Execute Grant

SELECT DBMS_METADATA.get_ddl ('PACKAGE', 'HR_PERSON_API', 'APPS') FROM DUAL;

How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

Method 1

Check Patches applied in Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:PORT/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch was returned.

Method 2

Use 'adphrept' utility
Patch History report usage:

adphrept query_depth \
bug_number or ALL \
bug_product or ALL \
end_date_from (mm/dd/rr or ALL) \
end_date_to (mm/dd/rr or ALL) \
patchname/ALL \
patchtype/ALL \
level/ALL \
language/ALL \
appltop/ALL \
limit to forms server? (Y/N) \
limit to web server? (Y/N) \
limit to node server? (Y/N) \
limit to admin server? (Y/N) \
only patches that change DB? (Y/N)

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions

Example: To get the complete patch details for patches applied in Dec 2000:

On UNIX:

$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N

Method 3
Use the following sql.
set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('','');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,
p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/
Note: Please enter Patch number in place of and , e.g '3240000'
Example Output
when p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943'):
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was EXPLICIT
.
dcollierpc8:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED

Method 4

You might also use the following query, however the most reliable methods are the described above.
SQL> SELECT DISTINCT RPAD(a.bug_number,
11)|| RPAD(e.patch_name,
11)|| RPAD(TRUNC(c.end_date),
12)|| RPAD(b.applied_flag, 4) BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('','');
ORDER BY 1 DESC;

WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!

WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!

[appsprod@erpprod PROD]$ scp -rp abc.tar.gz root@*****:/u01

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that the RSA host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
59:16:33:26:4g:d1:8j:75:47:2=:7d:ae:87:93:52:a4.
Please contact your system administrator.
Add correct host key in /home/appsprod/.ssh/known_hosts to get rid of this message.
Offending key in /home/appsprod/.ssh/known_hosts:1
RSA host key for ******** has changed and you have requested strict checking.
Host key verification failed.
lost connection

SOLUTION
 

login from root
vi /home/appsprod/.ssh/known_hosts


comment above mantioned ip
e.g 
# 192.192.192.192