Change Calander R12

Change Calendar R12


If you are on R12, make sure you set the following profile options properly:
FND: Calendar Week Start Day
FND: Forms User Calendar
FND: Tooltip Calendar
Note: 738581.1 - Hijrah Calendar Support in Oracle E-Business Suite R12

How to Create Database user and grant Privileges

How to Create Database user and grant Privileges

-- Create User via SYSDBA
CREATE USER testuser IDENTIFIED BY 123

-- Grant Connection Privilege via SYSDBA
GRANT CONNECT, DBA, RESOURCE TO testuser IDENTIFIED BY 123;

--Grant Select on HR Tables & Views --> Execute the Statements
SELECT 'GRANT SELECT ON ' || object_name || ' to testuser;'
  FROM dba_objects
 WHERE owner = 'HR' AND object_type IN ('TABLE', 'VIEW')

--Grant Execute on Packages --> Execute the Statements


SELECT 'GRANT EXECUTE  ON ' || object_name || ' to testuser;'
  FROM dba_objects
 WHERE object_name LIKE 'XXX%' AND object_type IN ('PACKAGE', 'PACKAGE BODY')

-- View Package Code

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

Note:
If one wants to avoid using the schema name in the beginning of any object in the custom schema, we need to create public synonyms as show below to avoid the same

E.g.: Select * from apps.per_all_people_f

SELECT    'CREATE OR REPLACE PUBLIC SYNONYM '
       || object_name
       || ' FOR '
       || object_name
       || ';'
  FROM dba_objects
 WHERE owner = 'HR' AND object_type = 'TABLE'

Once the Public Synonyms are create, one can use the objects without using the schema name.
E.g.: Select * from per_all_people_f

ORA-01102: cannot mount database in EXCLUSIVE mode

ORA-01102: cannot mount database in EXCLUSIVE mode


Issue:

After installing 11gR2, i faced issue of ORA-01102 cannot mount database in EXCLUSIVE mode when i tried to start the database.

ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode
Here is how I resolved it:

>> Shutdown database

SQL> shutdown immediate;
ORA-01507: database not mounted

>> Find out all the processes belonging to the database still running

ps -ef | grep ora_ | grep $ORACLE_SID

>>  Kill all the processes related to SID only

[oracle@DEV dbs]$ kill -9 324531

>> Check that there are no more processes running

ps -ef | grep ora_ | grep $ORACLE_SID

>> Remove the lk<SID> file from $ORACLE_HOME/dbs location
----------------------------
[oracle@DEV dbs]$ cd $ORACLE_HOME


[oracle@DEV dbs]$ cd dbs/


[oracle@DEV dbs]$ ls
 hc_DEV.dat  init.ora  lkDEV  orapwDEV  spfileDEV.ora


[oracle@DEV dbs]$ rm lkDEV

>>  Start the database
-------------------
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             956303352 bytes
Database Buffers          687865856 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

Total size of Database


Total size of Database

An oracle database consists of data files, redo log files, control files, temporary files. 
The size of the database actually means the total size of all these files.



SQL> select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual

OUTPUT
-----------------------------------------
Size in GB
615.947875976563
---------------------------------------

ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days
 
Cause: The user's account is about to about to expire and the password needs 
to be changed.
Action: Change the password or contact the database administrator.


Solutions:

1) Simply change the password to avoid it temporary

$ sqlplus scott/tiger
  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed


2) Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED 
   
   
 $ sqlplus scott/tiger

  ERROR:
  ORA-28002: the password will expire within 7 days

  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production   

  SQL> connect / as sysdba
  Connected.
  
  SQL> SELECT PROFILE FROM dba_users WHERE username = 'SCOTT';

  PROFILE
  ------------------------------
  DEFAULT

  SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' 
  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

  LIMIT
  ----------------------------------------
  60

  SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

  Profile altered.

  SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

  ACCOUNT_STATUS
  --------------------------------
  EXPIRED(GRACE)

  SQL> connect scott/tiger
  Connected.
  
  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed

How To Change Oracle Database Character Set

How To Change Oracle Database Character Set

>> Change Oracle DB Character Set

If you want to change from WE8MSWIN1252 to AL32UTF8. Check the NLS parameters first.

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET');

>>Changing Commands

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

SQL> ALTER DATABASE [db_name] CHARACTER SET new_character_set;
(db_name is an optional)
For Example :
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;


>> Steps to change the database character set


To change the database character set, perform the following steps:
1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Startup Oracle database

SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
If you get the error ORA-12712, please update the following table.
"ORA-12712: new character set must be a superset of old character set"
SQL> update sys.props$ set VALUE$='AL32UTF8' where NAME='NLS_CHARACTERSET';
SQL> commit;
If you get the error ORA-12721, please login as DBA user.
"ORA-12721: operation cannot execute when other sessions are active"

4. shutdown immediate; or shutdown normal;

5. startup oracle database
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
SQL> shutdown immediate;
SQL> startup;


>> Check the NLS parameters

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET');

How to create Oracle AWR report for a multiple instance (RAC) Oracle database

How to create Oracle AWR report for a multiple instance (RAC) Oracle database

To create the AWR report for a multiple instance Oracle RAC database (use awrrpti.sql) as:
 

1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:
a. format of the report (html/text)
b. enter db id
c. enter instance id
d. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter
e. enter starting snapshot id from the displayed list
f. enter ending snapshot id from the displayed list
g. give a name for the report including file extension (txt/html/htm)
4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.
Here are the steps:

[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18  2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24  2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:39:08 2011
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 999 line 300
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text
Type Specified:  text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
* 2330100236        1 DEVDB        DEVDB        DEVSERV
Enter value for dbid: 2330100236
Using 2330100236 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DEVDB         DEVDB           24128 07 Apr 2011 00:00      1
24129 07 Apr 2011 01:00      1
24130 07 Apr 2011 02:00      1
24131 07 Apr 2011 03:00      1
24132 07 Apr 2011 04:00      1
24133 07 Apr 2011 05:00      1
24134 07 Apr 2011 06:00      1
24135 07 Apr 2011 07:00      1
24136 07 Apr 2011 08:00      1
24137 07 Apr 2011 09:00      1
24138 07 Apr 2011 10:00      1
24139 07 Apr 2011 11:00      1
24140 07 Apr 2011 12:00      1
24141 07 Apr 2011 13:00      1
24142 07 Apr 2011 14:00      1
24143 07 Apr 2011 15:00      1
24144 07 Apr 2011 16:00      1
24145 08 Apr 2011 18:00      1
24146 08 Nov 2011 18:00      1
24147 08 Feb 2012 13:56      1
24148 12 Oct 2013 18:00      1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24147
Begin Snapshot Id specified: 24147
Enter value for end_snap: 24148
End   Snapshot Id specified: 24148
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24147_24148.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_report_24147_24148.txt
Using the report name awr_report_24147_24148.txt
WORKLOAD REPOSITORY report for
DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
———— ———– ———— ——– ————— ———– —
DEVDB         2330100236 DEVDB               1 11-Mar-11 12:42 11.1.0.7.0  NO
Host Name        Platform                         CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV          Linux x86 64-bit                    8     8       4      31.29
Snap Id      Snap Time      Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap:     24147 08-Feb-12 13:56:12       287       3.1
End Snap:     24148 12-Oct-13 18:00:50       123        .9
Elapsed:          881,524.64 (mins)
DB Time:        5,752,234.99 (mins)
Cache Sizes                       Begin        End
~~~~~~~~~~~                  ———- ———-
Buffer Cache:     1,536M     1,536M  Std Block Size:         8K
Shared Pool Size:     2,560M     2,560M      Log Buffer:   157,036K
Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         —————    ————— ———- ———-
DB Time(s):                6.5            2,543.7     123.07     345.85
DB CPU(s):                0.0                0.1       0.00       0.01
Redo size:               34.4           13,407.1
Logical reads:                4.8            1,877.2
Block changes:                0.2               63.0
Physical reads:                0.0                0.2
Physical writes:                0.0                1.9
User calls:                0.0                7.4
Parses:                0.0               15.3
Hard parses:                0.0                0.1
W/A MB processed:           15,009.8        5,851,091.6
Logons:                0.0                0.4
Executes:                0.1               20.7
Rollbacks:                0.0                0.0
Transactions:                0.0
————————————————————-
————————————————————-
————————————————————-
————————————————————-
End of Report
Report written to awr_report_24147_24148.txt
SQL>

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

Solution:-

Step1:
You can truncate these tables for deleting entire data without any back up.The truncate is more faster compare to delete command because data in the table is not write to roll back segments.

1. SQL>truncate table xla.XLA_DIAG_SOURCES;
2. SQL>truncate table xla.XLA_DIAG_EVENTS;


Step2:
Turn OFF the “SLA: Diagnostics Enabled” profile option at all levels.

Step3:
Check the tables, tablespace and archive logs status

Get all the Concurrent Program Request Details

Get all the Concurrent Program Request Details

select   request_id,
           parent_request_id,
           fcpt.user_concurrent_program_name Request_Name,
           fcpt.user_concurrent_program_name program_name,
           DECODE(fcr.phase_code,
                        'C','Completed',
                        'I','Incactive',
                        'P','Pending',
                        'R','Running') phase,
           DECODE(fcr.status_code,
                        'D','Cancelled',
                        'U','Disabled',
                        'E','Error',
                        'M','No Manager',
                        'R','Normal',
                        'I','Normal',
                        'C','Normal',
                        'H','On Hold',
                        'W','Paused',
                        'B','Resuming',
                        'P','Scheduled',
                        'Q','Standby',
                        'S','Suspended',
                        'X','Terminated',
                        'T','Terminating',
                        'A','Waiting',
                        'Z','Waiting',
                        'G','Warning','N/A') status,
           round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
           round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) *  1440),2) wait_time,
           fu.User_Name Requestor,
           fcr.argument_text parameters,
           to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
           to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') startdate,
           to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') completiondate,
           fcr.completion_text
From    fnd_concurrent_requests fcr,
           fnd_concurrent_programs fcp,
           fnd_concurrent_programs_tl fcpt,
           fnd_user fu
Where 1=1
and      fcr.concurrent_program_id = fcp.concurrent_program_id
and      fcp.concurrent_program_id = fcpt.concurrent_program_id
and      fcr.program_application_id = fcp.application_id
and      fcp.application_id = fcpt.application_id
and      fcr.requested_by = fu.user_id
and      fcpt.language = 'US'
and      fcr.actual_start_date like sysdate
GROUP BY  request_id,
                 parent_request_id,
                 fcpt.user_concurrent_program_name,
                 fcr.requested_start_date,
                 fu.User_Name,
                 fcr.argument_text,
                 fcr.actual_completion_date,
                 fcr.actual_start_date,
                 fcr.phase_code,
                 fcr.status_code,
                 fcr.resubmit_interval,
                 fcr.completion_text,
                 fcr.resubmit_interval,
                 fcr.resubmit_interval_unit_code,
                 fcr.description
Order by 1 desc



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

How to change user's password in Oracle 11g and change back it to original

How to change user's password in Oracle 11g and change back it to original

Imp points
(1) Note down current password found in data dictionary.
(2) Modify password
(3) Do required tasks
(4) Reset the password


Get current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','TEST') FROM dual;

   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


Password is in encrypted format.

Now change password temporarily:-
SQL> alter user TEST identified by 1234;

Login to the user with new password:-
SQL> conn test/1234
Connected.

Once the job is done we can change password back to the original :-

SQL> alter user TEST identified by values 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';


DONE

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

ERROR :

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Solution:-

Create System Environment variable in that PC with following name and value :

Name : JAVA_TOOL_OPTIONS
Value : -Djava.vendor="Sun Microsystems Inc."

Table Lock


>> TABLE LOCK

select * from dba_waiters;

select * from dba_blockers;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S where SID='< >';

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

++++++++++++++++++++++++++++++++++++++++++++++++

>> Table Lock


1)     Run the Following Script to Get Locked Tables in Session
BEGIN
   DBMS_OUTPUT.enable (1000000);

   FOR do_loop IN (SELECT session_id,
                          a.object_id,
                          xidsqn,
                          oracle_username,
                          b.owner owner,
                          b.object_name object_name,
                          b.object_type object_type
                     FROM v$locked_object a, dba_objects b
                    WHERE xidsqn != 0 AND b.object_id = a.object_id)
   LOOP
      DBMS_OUTPUT.put_line ('.');
      DBMS_OUTPUT.put_line ('Blocking Session : ' || do_loop.session_id);
      DBMS_OUTPUT.
       put_line (
            'Object (Owner/Name): '
         || do_loop.owner
         || '.'
         || do_loop.object_name);
      DBMS_OUTPUT.put_line ('Object Type : ' || do_loop.object_type);

      FOR next_loop
         IN (SELECT sid
               FROM v$lock
              WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
      LOOP
         DBMS_OUTPUT.put_line ('Sessions being blocked : ' || next_loop.sid);
      END LOOP;
   END LOOP;
END;

2)     Get SID & Serial Number to Kill Session

SQL> select * from v$session where sid in 'SID,SERIAL#'
SQL> alter system kill session 'SID,SERIAL#'

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors



Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

NOTE:848387.1 - With NUMA Enabled, Database Fails To Open With ORA-600[ksbmoveme4], ORA-27300, ORA-27301, ORA-27302 Errors Reported
NOTE:300956.1 - Ora-27302: sskgxpsnd1 - Starting Instance
NOTE:314179.1 - Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302
NOTE:3411021.8 - Bug 3411021 - Oracle process may die due to ORA-27300/ORA-27301/ORA-27302 at skgxppost1
NOTE:746888.1 - ORA-27302: Failure Occurred at: skgxpvfymmtu Signalled in the Alert.log
BUG:7620133 - HIGH NUMBER OF DBW* PROCESSES SPAWNED IN 11.1.0.7
NOTE:458442.1 - 10.2.0.3 PMON Crashes on Startup on AIX 5L 5.3 ML05 -- Works on ML06
NOTE:277399.1 - DBMS_SCHEDULER Fails To Execute Jobs With Program_type EXECUTABLE On HP-UX
NOTE:392006.1 - Ora-27300 OS system dependent operation:fork failed with status: 11
NOTE:295832.1 - Unable To Create Database Ora-00603, ORA-27300
NOTE:1252265.1 - ORA-27300 ORA-27301 ORA-27302: failure occurred at: skgpalive1
NOTE:438205.1 - ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash
NOTE:453959.1 - Cannot Connect As "/ as sysdba" ORA-27140 ORA-27300 ORA-27301 ORA-27302 And ORA-27303 In Trace File
NOTE:356640.1 - ORA-27300, ORA-27301, ORA-27302: Failure Occurred At: Skgpalive1 During 'Shutdown'
NOTE:465002.1 - Database Crash With Error ORA-00490
NOTE:466370.1 - ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: OS failure message: Not enough storage ORA-27300 ORA-27302
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:371074.1 - ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
BUG:7232946 - ORA-600[KSKRECONFIGNUMA2] CAUSES INSTANCE CRASH
NOTE:580552.1 - Database Crashes With ORA-04030 ORA-07445 ORA-27300 ORA-27301 ORA-27302
NOTE:557153.1 - ORA-27370 ORA-27301 (Permission denied) When Running Job Of Type EXECUTABLE
BUG:3411021 - LMD PROCESS CRASH DUE TO ORA-27300/ORA-27301/ORA-27302 AT SKGXPPOST1
NOTE:560309.1 - Database Cannot Start Due to Lack of Memory
BUG:6991131 - ORA-27300, ORA-27301 AND ORA-27302 HAPPENED DURING SHUTDOWN USING SRVCTL
NOTE:364353.1 - ORA-00603 ORA-27504 ORA-27300 ORA-27504 in the Alert Log
NOTE:1274030.1 - Startup Instance Failed with ORA-27140 ORA-27300 ORA-27301 ORA-27302 and ORA-27303 on skgpwinit6
NOTE:949468.1 - Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28
NOTE:6629265.8 - Bug 6629265 - Intermittent ORA-27504 / ORA-27300 ioctl error 11
BUG:9059053 - ORA-00603 ORA-27504 ORA-27300 ORA-27301 ORA-27302 IN ALERT.LOG
NOTE:1038058.6 - LIST OF UNIX ERRORS AND EXPLANATIONS
NOTE:297498.1 - Resolving Instance Evictions on Windows Platforms
BUG:10010310 - ORA-27300: INVALID_PROCESS_ID FAILED WITH STATUS: 0; ORA-27302: SKGPALIVE1
NOTE:6441119.8 - Bug 6441119 - Instance crash due to ORA-27300 / ORA-27152

Create a new control file without backup

Create a new control file without backup

Start the database in NOMOUNT mode. For example, enter:

STARTUP NOMOUNT

Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option . The following example assumes that the character set is the default US7ASCII:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1 (
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
     GROUP 2 (
       '/diska/prod/sales/db/log2t1.dbf',
       '/diskb/prod/sales/db/log2t2.dbf'
     )  SIZE 100K,
DATAFILE
     '/diska/prod/sales/db/database1.dbf',
     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):

RECOVER DATABASE

Open the database after recovery completes (RESETLOGS option not required):

ALTER DATABASE OPEN;

Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;

How to change sys password in Oracle 10g

How to change sys password in Oracle 10g

To Change the ""SYS"  Password

SQL> conn sys as sysdba
Enter password: (null) no need to give anything
Connected.
 

SQL> passw system
Changing password for system
New password: (new password)
Retype new password: (new password)
Password changed
 

SQL> conn system/(new password)
Connected.
 

SQL> passw sys
Changing password for sys
New password: (new sys password )
Retype new password: (new sys password )
Password changed
 

SQL>

Package using which session

Package using which session

Trying to compile package but its not compiling and going in infinite loop.

Run this query to now which session is using package. It will give you SIDs

SELECT * FROM v$access WHERE object = 'XXXX_HR_COMMON_PKG';

Now run this command to get SERIAL# of that SID

select SERIAL# from v$session where sid='<SID>';

Now kill sessions

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Its easy :)

Huge generation of archive logs

Huge generation of archive logs

Doc ID 167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
Doc ID 1507157.1 - Master Note: Troubleshooting Redo Logs and Archiving
Doc ID 781999.1 - General Guideline For Sizing The Online Redo Log Files
Doc ID 782935.1 - Troubleshooting High Redo Generation Issues
Doc ID 199298.1 - Diagnosing Excessive Redo Generation - Limit with Nologging

Components version in R12

Components version in R12



Apache Version
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

Report Version
string -a APXAPRVL.rdf|grep Header

Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode
cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12

Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
 
Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

Oracle Jinitiator in 11i/R12/12i 

Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )


Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check

adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
 
Identity Management component Version/Release Number

Oracle Single Sign On
select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)


To find software/binary version
$ORACLE_HOME/bin/oidldapd -version


To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion
select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
 

Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0 


For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
select * from ias_versions;
or
select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version

Oracle Portal
select version from portal.wwc_version$;

Database Component

 
To find database version
select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

Adding responsibility to SYSADMIN user resulted ORA-20001: FND_CANT_INSERT_USER_ROLE


Adding responsibility to SYSADMIN user resulted the below error.
Issue : Adding responsibility to SYSADMIN user resulted the below error in R12

Oracle error - 20001: ORA-20001: FND_CANT_INSERT_USER_ROLE
(USERNAME=SYSADMIN)
(ROLENAME=FND_RESP|FND|FND_FUNC_ADMINI|STANDARD)
(ROUTINE=FND_USER_RESP_GROUPS_API.Insert_Assignment) has
been detected in FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT

Resolution:

Issue exists in default R12 installation

Refer Oracle Note id : 454988.1

1. Run the concurrent program "Workflow Directory Services User/Role Validation" with parameters : 100000, Yes, Yes, No

2. Bounce all services under oacore or bounce opmnctl

How to Trace a Concurrent Request And Generate TKPROF File

How to Trace a Concurrent Request And Generate TKPROF File

 1. Enable Tracing For The Concurrent Manager  Program

    Responsibility: System Administrator
    Navigate: Concurrent > Program > Define
    Query Concurrent Program
    Select the Enable Trace Checkbox

Turn On Tracing

    Responsibility: System Administrator
    Navigate: Profiles > System
    Query Profile Option Concurrent: Allow Debugging
    Set profile to Yes

 Run Concurrent Program With Tracing Turned On

    Logon to the Responsibility that runs the Concurrent Program
     In the Submit Request Screen click on Debug Options (B)
    Select the Checkbox for SQL Trace

 2. Find Trace File Name

SQL> select req.request_id , req.logfile_node_name node , req.oracle_Process_id   ,req.enable_trace ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename ,prog.user_concurrent_program_name  ,execname.execution_file_name ,execname.subroutine_name ,phase_code,status_code,ses.SID,ses.serial#, ses.module,ses.machine FROM fnd_concurrent_requests req , v$session ses , v$process proc ,v$parameter dest ,v$parameter dbnm ,fnd_concurrent_programs_vl prog, fnd_executables execname WHERE 1=1 AND req.request_id = &request --Request ID

/opt/oracle/enable/admin/ENBL1/udump  --> trace file location

3. TKPROF Trace File

$tkprof enbl2_ora_23852_837166_CR1814778.trc /home/vk837166/837166_CR1814778 explain=apps/nobel4pce sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10? long running queries

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort=’(prsela,exeela,fchela)’ print=10

Log Miner


Log Miner

To properly investigate the problem:


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

BEGIN
   DBMS_LOGMNR.
    START_LOGMNR (STARTTIME   => :l_start,
                  ENDTIME     => :l_end,
                  OPTIONS     => DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/



1. select operation,count(*) from v$logmnr_contents group by operation;


2. select username, operation, count(*) from v$logmnr_contents where operation in ('DELETE','INSERT','UPDATE','DDL')  group by operation, username order by 3 desc,2,1;

3. select count(*) as hits, seg_owner||'.'||seg_name "SEG_OWNER.SEG_NAME"  from v$logmnr_contents group by seg_owner, seg_name order by 1 desc ;

4. Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:
SQL> SELECT s.sid, s.serial#, s.username, s.program,  i.block_changes  FROM v$session s, v$sess_io i  WHERE s.sid = i.sid  ORDER BY 5 desc;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

5. Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:
SQL> SELECT s.sid, s.serial#, s.username, s.program,  t.used_ublk, t.used_urec  FROM v$session s, v$transaction t  WHERE s.taddr = t.addr  ORDER BY 5 desc, 6 desc;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Please run these queries a couple of times while the excessive logs are being generated and let us know the results (in a readable format like csv, for example).

6. alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select trunc(first_time, 'HH') , count(*)
from v$loghist
group by trunc(first_time, 'HH')
order by trunc(first_time, 'HH');

Workflow Mailer Troubleshooting

Workflow Mailer Troubleshooting

>> Check workflow mailer service current status
  SQL> select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';
 

Number of running processes should be greater than 0

 >> Find current mailer status
  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');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM
 

>> Stop notification mailer
SQL>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Stop Mailer
       --------------
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /
 


>> Start notification mailer
SQL>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a
count of data is at best only a snapshot of a particular moment in time.

  Here is a more robust script for monitoring the wf_notifications table:
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS
messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'

 Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.

select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;
Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql

The following SQL to collect all the info except IMAP account password.

select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = ‘WF_MAILER’
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in (‘OUTBOUND_SERVER’, ‘INBOUND_SERVER’,
‘ACCOUNT’, ‘FROM’, ‘NODENAME’, ‘REPLYTO’,'DISCARD’ ,’PROCESS’,'INBOX’)

How to Enable the Workflow Logging:

From Self Service > Select “Workflow Manager” under “Oracle Applications Manager” > Click “Notification Mailers” > Service Components (Service Components: <SID>) >
b. Click “Workflow Mailer Service” under “Container” Column.
e. From “Service Instances for Generic Service Component Container:<SID>”page, click “Pull Down” button from the Start All/ Stop All.
f . Select Stop All > Go.
g. We conformed that for the Services to read Actual 0 Target 0 and Deactivated.
h. Restart the mailer services using the “Start All” button.
I. We run the following SQL to make sure service are stopped.
SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like ‘Workflow%’;
Enable the Statement logging in Workflow Mailer.
Log files are created  $APPLCSF/$APPLLOG/FNDCPGSC*.txt i.e. the log file for the Active process for Workflow Mailer Service and Agent Listener services.
To retrieve the last 2 log files for Workflow Mailer and Agent Listener services, run the following command:
ls -lt $APPLCSF/$APPLLOG/FNDCPGSC*.txt

How does workflow Notification Mailer IMAP (Inbound Processing) Works:
This is the inbound flow:

1. Approver sends email response which is directed to the value defined in Replyto address.
a. This address has been setup by the customer’s mail administrator to route incoming mail to the IMAP Inbox folder.
2. The Workflow Inbound Agent Listener picks up the message. Only messages which are in ‘unread’ state are evaluated; the rest of the messages in the inbox are ignored.

3. The message is scanned to see if it matches entries in the TAG file . Mail tags are defined in the OAM mailer configuration pages and these list strings of text and actions to take if those strings are encountered. An example of this are ‘Out of Office’ replies. If the string of the message matches a mail tag and the action is ‘Ignore’ then nothing else will happen.

4. The message is then scanned for the presence of a Notification Id (NID). This NID is matched against valid NID for the mailer node.

5. If valid NID is not detected, (and there is no matching TAG file entry) then the message is placed into the DISCARD folder.

6. If a valid NID is detected the listener raises a subscription to the WF_NOTIFICATION_IN queue and places the mail message in the Processed folder.

7. From this point on the message is handled by the product workflow (like PO APPROVAL) . An event created by that group will monitor the WF_NOTIFICATION_IN queue and will trigger the rest of the approval workflow.

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key

2. There is seeded subscription to this Event

3. Event is placed on WF_DEFERRED agent

4.Event is dequeued from WF_DEFERRED and subscription is processed

5. Subscription places event message to WF_NOTIFICATION_OUT agent.

6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (IfTest Address/Override Address is set then email is sent to Test Address

E-Mail Notification is sent if all below conditions are truea) Notification status is OPEN or CANCELED   and
b) Notification mail_status is MAIL or INVALID  and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status  FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)

How to purge e-mail notifications from the Workflow queue

Sometimes Due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.


The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = ‘OPEN’ and mail_status = ‘MAIL’ are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = ‘SENT’. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = ‘SENT’ where mail_status = ‘MAIL’;

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer


Workflow Mailer Debugging Script for Debugging Emails issues
This article contains various Workflow and Business Event debugging scripts.

--Checking workflow Components status whether are they running or stopped.
select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components
where component_type like 'WF%'
order by 1 desc,2,3;


Query to get the log file of active workflow mailer and workflow agent listener Container--Note All Workflow Agent Components logs will stored in single file i.e. container log file.
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;


Linux Shell script Command to get outbound error in Mailergrep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

Linux Shell script Command to get inbound processing error in Mailer grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;



Query to Check Workflow Mailer Backlog --State=Ready implies that emails are not being sent & Waiting mailer to send emails

select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


Check any particular Alert Message email has be pending by Mailer
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';


Check The Workflow notification has been sent or not

select mail_status, status from wf_notifications where notification_id=<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


Check Whether workflow background Engine is working for given workflow or not in last 2 days -- Note: Workflow Deferred activities are run by workflow background engine.
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
order by last_update_date desc

 Check whether any business event is pending to process
i.e. Query to get event status & parameters value of particular event in wf_deferred table.
select wd.user_Data.event_name,wd.user_Data.event_key,
rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,
n.NAME Parameter_name, N.value Parameter_value ,
decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
where lower(wd.user_data.event_name)='<event Name >'
order by wd.user_Data.event_name, wd.user_Data.event_key, n.name



When the workflow mailer is either disabled or the mail server restricts its access to the workflow mailer, all the workflow notifications will retry and upto the limit set at the workflow mailer configuration and later all the notifications will be failed. This will result in 2 things at Application level

i) MAIL_STATUS column of the WF_NOTIFICATIONS set to FAILED.

ii) After a series of failure notifications the Notification Style for the respected User will be set as Disabled and a corresponding user details are sent to SYSADMIN.

A similar issue araised in one of the client and the many of the users notification style has been set to Disabled.

Solution:

To overcome the above issue, initially fix the workflow mailer issue and later need to change the Notification Preference at Workflow Administrator to "HTML with Attachments" (or any other as per the client's requirement).

But sometime this setting won't be affected to all the users whose Preference is set to Disabled. To overcome this situation:

i) Backup the following tables:

CREATE FND_USER_PREFERENCES_BAK as select * from FND_USER_PREFERENCES;
CREATE WF_LOCAL_ROLES_BAK as select * from WF_LOCAL_ROLES;

ii) To set the mail preference for all the users execute the following:

update wf_local_roles
set notification_preference='<wished_preference>'
where orig_system in ('FND_USR','PER');

update fnd_user_preferences
set preference_value='<wished_preference>'
where preference_name='MAILTYPE'
and module_name='WF'
and user_name <> '-WF_DEFAULT-';

iii) To update the mail preference of the users only whose preference is set to "FAILED", execute the below steps:

 update wf_local_roles
set notification_preference='<wished_preference>'
where orig_system in ('FND_USR','PER')
and name in
(select user_name
from fnd_user_preferences
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED');

update fnd_user_preferences
set preference_value='<wished_preference>'
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED';

Possible values for <wished_preference> are:
QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments")
MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTML (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")

iii) To Resend all the failed notifications again to the respected users run the following concurrent program as SYSADMIN, with the relevant parameters.
Retry Errored Workflow Activities

Cisco VPN on Windows Reason 442: Failed to enable Virtual Adapter

Cisco VPN on Windows Reason 442: Failed to enable Virtual Adapter

1. Click Start and type regedit in the Search field and hit enter.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA
3. Find the String Value called DisplayName
4. Right click and select Modify from the context menu.
5. In Value data, remove @oemX.inf,%CVirtA_Desc%;. The Value data should only contain Cisco Systems VPN Adapter for 64-bit Windows.
6. Click Ok.
7. Close Registry Editor.
8. Retry your Cisco VPN Client connection.