Clearing redo logfile in oracle database.

Sometimes redo log file become corrupted while database open the database may "lock up" and we are unable to do anything because archiving can't continue this case you may execute 'ALTER DATABASE CLEAR LOGFILE' statement to reinitialize the redo log file and database continue processing.


SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         3      69151          2 YES UNUSED
         5      69150          2 YES INACTIVE
         4      69152          2 NO  CURRENT



Note:- Specifying UNARCHIVED makes backups unusable if the redo log is needed for recovery. Do not use CLEAR LOGFILE to clear a log needed for media recovery.

If the corrupted redo log file has not been archived, use the unarchived keywordin the alter database clear logfile statemnent.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;


The unarchived option clears the corrupt online redo log and avoids archiving it.

Users Notification Preference Becomes Disabled In FND_USER_PREFERENCES Table

Lets understand why notification_preference for recipients to become DISABLED.

If your email server crashed and caused an outage of several hours then its caused the recipients notification_preference to change to DISABLED in the FND_USER_PREFERENCES and WF_LOCAL_ROLES tables.


We can find out  those user that are DISABLED using following sql statement in FND_USER_PREFERENCES .

SQL>select user_name,preference_value from fnd_user_preferences where PREFERENCE_VALUE = 'DISABLED';

SQL>select * from WF_LOCAL_ROLES where notification_preference in ('DISABLED','QUERY') and orig_system='PER';

SQL>select *from WF_LOCAL_ROLES where notification_preference='DISABLED';

To identify disabled user using below statement.

SQL>SELECT * FROM FND_USER_PREFERENCES WHERE user_name LIKE '&user_name' AND module_name = 'WF' ;

SQL>select *from WF_LOCAL_ROLES where notification_preference='DISABLED';

-----------Please ensure a backup is performed for recovery as needed. Apply in TEST instance before in PROD ---------

.

We need to update  FND_USER_PREFERENCES and WF_LOCAL_ROLES manually.

FND_USER_PREFERENCES for particular user and all user:- 

SQL>update FND_USER_PREFERENCES set PREFERENCE_VALUE = 'MAILHTML' where USER_NAME='SYSADMIN' and MODULE_NAME='WF' and PREFERENCE_NAME='MAILTYPE' ;

SQL>commit;


For all User:-

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED';

SQL>commit;


WF_LOCAL_ROLES for particular user and all user:- 

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED' and name='ISBENIWAL-I0001';

SQL>commit;


For all User:-

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED';

SQL>commit;


Another way to fix this issue:-

Set ‘General Preferences show flag’ profile at user level, so that it will enable the ‘Preference’ flag in user application.

Go to preference tab, Notifications, set email style to ‘HTML mail with attachments’.

If this is ‘Disabled’ or ‘Do not send me email’ users wont get notifications through mail.If you want to receive notification change it as 'HTML mail with attachments'.


Sometimes it is difficult to set it for every user.

IAS component OC4JGroup:default_group (OC4J:oacore) stopped/failed automatically in EBS

In our production instance R12.1.X.X  IAS component "oacore" frequently

down/failed.

Check the services in case of oacore failure..

$sh adapcctl.sh status

You are running adapcctl.sh version 120.7.12010000.2
Checking status of OPMN managed Oracle HTTP Server (OHS) instance.

Processes in Instance: PROD.xxxxxxxxxx
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------

OC4JGroup:default_group          | OC4J:oafm           |   19410 | Alive

OC4JGroup:default_group          | OC4J:forms          |   19319 | Alive

OC4JGroup:default_group          | OC4J:oacore        |   19140 | Failed

HTTP_Server                             | HTTP_Server       |   19069 | Alive

OC4JGroup:default_group          | OC4J:oacore    |  23108 | failed

To resolve the above issue  need to be modify/increase the JVM processes as below in context file: 

<oacore_jvm_start_options oa_var="s_oacore_jvm_start_options">-

server -verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=160M -

XX:NewRatio=2 

To 

<oacore_jvm_start_options oa_var="s_oacore_jvm_start_options">-

server -verbose:gc -Xmx1024M -Xms512M -XX:MaxPermSize=256M -

XX:NewRatio=2 

=> Stop the services

 
=> Run autoconfig 

=> Restart the services 


Check it again:-

$sh adapcctl.sh status

You are running adapcctl.sh version 120.7.12010000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: PROD.xxxxxxxxx
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------

OC4JGroup:default_group          | OC4J:oafm          |   19410 | Alive

OC4JGroup:default_group          | OC4J:forms         |   19319 | Alive

OC4JGroup:default_group          | OC4J:oacore       |   19140 | Alive

HTTP_Server                             | HTTP_Server     |   19069 | Alive

In my case issue was resolved successfully ......😁