RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/30/2012 10:52:56
RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

Bug 11715084 - Active duplicate should work when connected to Standby as source DB [ID 11715084.8]

RMAN active duplicate does not currently allow a standby in MRP to be cloned. Backup-based duplicate was altered in 11.1 so that a standby could be used as the source DB, but active duplicate does not work.

This is going to be fixed in RDBMS 12.1(future release) or 11.2.0.4(future patchset).(As on 30-Dec-2012)

The workaround is to Open the DataGuard in Read/Write Mode and Start the Active Duplicate. As it is 11.2.0.3, I opened the DataGuard in Read Only and Stopped the MRP. In 11gR2, you can have the Standby in Read Only, while the MRP Continues.

FORMS Customization - changes required in adovars.env and formservlet.ini

Customization Entry in $APPL_TOP/admin/adovars.env

# Begin customizations
export APPLLCSP=ON
export XXCSTAP_TOP="/pa01/oracle/mkkdevappl/xxcstap/11.5.0"
export XXCSTAR_TOP="/pa01/oracle/mkkdevappl/xxcstar/11.5.0"
export XXCSTGL_TOP="/pa01/oracle/mkkdevappl/xxcstgl/11.5.0"
export XXCSTPO_TOP="/pa01/oracle/mkkdevappl/xxcstpo/11.5.0"
# End customizations

Customization Entry in $IAS_ORACLE_HOME/Apache/Jserv/etc/formservlet.ini

# Begin customizations
XXCSTAP_TOP=/pa01/oracle/mkkdevappl/xxcstap/11.5.0
XXCSTAR_TOP=/pa01/oracle/mkkdevappl/xxcstar/11.5.0
XXCSTGL_TOP=/pa01/oracle/mkkdevappl/xxcstgl/11.5.0
XXCSTPO_TOP=/pa01/oracle/mkkdevappl/xxcstpo/11.5.0
# End customizations

In R12 Custom forms will not open after upgrade, because file formservlet.ini used in 11i has been replaced by default.env in R12. Put entry for the custom top in $ORA_CONFIG_HOME/10.1.2/forms/server/default.env file.
Note: If you have the custom top entries in $ORA_TOP/iAS/Apache/Jserv/etc/formservlet.ini in correct format, then autoconfig will preserve the customization.

SQL_Query - Find the Schedule Concurrent Requests and Request Sets

SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%'
ORDER BY CONC_PROG_NAME;

Workflow Related Scheduled Concurrent Program for DBAs

Purge Obsolete Workflow Runtime Data

Oracle Workflow accesses several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes. The size of these tables and indexes can adversely affect performance. You should purge these tables on a regular basis using FNDWFPR.
This program calls database package WF_Purge.Total.  This program deletes old Workflow runtime data for completed Workflows. It is recommended that this program or package is run regularly as required.

From Workflow Administrator's Guide PDF Document:
The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

Parameter Explanation:
Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key: The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
Age: Minimum age of data to purge, in days.
Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
Commit Frequency: Enter the number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
Signed Notifications: Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.

Workflow Control Queue Cleanup

Parameters: NONE
Schedule:
Frequency:    Run every 12 Hours
Responsibility:  System Administrator
Please tick the checkbox “From the start of the prior run”

Workflow Background Process

This program needs to be submitted in various permutation and combination.

Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout:  No
Process Stuck:  No

Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred:  No
Process Timeout:  Yes
Process Stuck:  No

Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred:  No
"Process Timeout:  No
Process Stuck:  Yes"

Type-4: which Runs Every 2 Hours for Parameters:
Process Deferred:  Yes
"Process Timeout:  Yes
Process Stuck:  Yes"

How to Submit a Workflow Background Process Engine [ID 182936.1], Follow the Note mentioned at the End of this Metalink Note

Workflow Mailer Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Work Items Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Agent Activity Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Gather Schema Statistics Schedule:

Make sure Gather Shema Stats is running on periodical basis, for XXXXX we have scheduled to run every 15 Days.
Following points needs to be noted for the Gather Schema Statistics:
●● Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
●● Do not gather statistics on permanent objects during peak intervals.
●● Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
●● Gathering statistics requires dictionary and object level locks.
●● The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).
●● Plans are not likely to change if the data distribution has not changed.
●● Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
●● Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.

Following CONCSUB Command can be used to schedule the Gather Schema Statistics concurrent Program:
"CONCSUB apps/${xx_appspassword} SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDGSCST START='""01-MAR-2012 02:00:00""'
REPEAT_DAYS=10 'ALL','10','18','NOBACKUP','','LASTRUN','GATHER','','Y'"

To Submit the Program Manually from SQLPlus following commands can be used:
For One Schema:
SQL> exec fnd_stats.gather_schema_statistics('MRP');
For All Schema:
SQL> exec fnd_stats.gather_schema_statistics('ALL');
For One Table:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');

To Submit the Gather Schema Statistics from EBS Front End:
●● Make sure you are logged in as SYSADMIN User
●● Use the System Administrator Responsibility to Submit the Concurrent Program.

DROP function was hanging for ever, even if you try to recompile the objects it throws ORA-04021

ORA-04021 timeout occurred while waiting to lock object... Same is applicable if the package status is getting invalidated.

drop function AGCST.AG_ASG_CHG_MAIL_PROG;

The background behind it was, this function is used in workflow and the initiated workflow was still accesing the objects.

Get the SID Information which are accessing this object and kill the same:
select * from v$access where owner='AGCST';

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' from gv$session where INST_ID=2 and SID IN(select SID from gv$access
where object like '%AR%CASH%REC%' and type='TABLE'
and INST_ID=1);

Once you complete killing all the SIDs, it will allow you to drop the object.

Removing OAF personalization document in the database using JDR_UTILS package

This method only requires access to the database and must be used with great care to ensure the correct document is deleted. For this method you need to know the document name of the OAF page with the personalization

Run the following commands to show the available personalization documents:

set serverout on;
exec jdr_utils.listcustomizations('/oracle/apps/<product>/<subcategories>/<pagename>');
exec jdr_utils.listcustomizations('/oracle/apps/fnd/framework/navigate/webui/HomePG');
exec jdr_utils.listcustomizations('/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');

This will print the details:
exec jdr_utils.printdocument('/oracle/apps/eam/workorder/lov/server/customizations/site/0/MaterialsVO');
This returns the documentnames of the personalization document for this OAF page.

For example for SITE level on Homepage
/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG
SQL> exec jdr_utils.listcustomizations('/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');
/oracle/apps/eam/workorder/webui/customizations/site/0/CreateUpdateWOPG
PL/SQL procedure successfully completed.

Backup the personalization document by exporting it to a file

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/ /<pagename> \
-username apps -password <appspwd> -dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" \
-rootdir /tmp
This create an XML document in the /tmp directory, like
/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml

Delete the personalization document

SQL> exec jdr_utils.deletedocument('/oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/<pagename>');
For the home page example this is:
SQL> exec jdr_utils.deletedocument('/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG');
Restart Apache to ensure that Java caching is cleared
To restore the personalization document deleted, use the command (optional)
-adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter \
/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml \
-username apps -password apps -dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" -rootdir /tmp

OAF Personalizations Issue - Temporary disable all Personalizations

In case it's not possible to determine the exact document name of the OAF page failing the following method can be used. The caveat of this method however is that during the change of profile option mentioned this makes *all* personalizations will be disabled so will also affect other pages. So only to be used on TEST environment or during maintenance window for PROD environment.

Update the Profile Option: "Disable Self-Service Personal" = YES
This can be set at SITE and APPLICATION level

Navigate to the OAF page personalized(may be done using another user). It should now open since personalizations are not taken into account. Depending on the situation you have following options:

Cause of the failure is known (e.g. last change made)
Revert the personalization step causing the problem
Apply the changes

Cause of the failure is unknown
Click pencil "Manage Personalizations"
Now Deactivate or Delete the personalization document

The difference between Deactivate and Delete is that in the case of Deactivating the personalization document in the MDS repository is not removed, while this is the case for Delete. Especially when there are multiple personalization documents, but it's not clear which causes the problem the 'Deactivate' allows these to be tested individually without the need to reload or recreate the personalization documents. 

After fixing the problem:  "Disable Self-Service Personal" = NO (for the level it was set to YES)

OAF Page Migration - adjava XMLImporter

1. SCP the Directory Structure from Source(DEV) to Target(CRP2).

As an example we are migrating cwsa OAF Pages, then SCP the ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa directory from DEV to CRP2.
Use the following command using CRP2 applmgr user:
$ scp -r appldev@mkkapplserver1:${xx_srcjavatopxx}/xxcto/oracle/apps/eam/cwsa/* ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa/

2. Execute XMLImporter Command

cd ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa/
find . -name "*.xml" -print | grep webui | wc -l

This would give you the name of all the XMLs required to be imported, likes LOVs..... DO NOT miss any LOVs......

Go to the Directory where .xml file is copied inside the webui directory.
adjava oracle.jrad.tools.xml.importer.XMLImporter XXCTOCreatePropVehiclePG.xml -username apps -password ${xx_appspassword} -rootdir ${JAVA_TOP} -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP) (Host = mkkapplserver1.mkkdomain.local)(Port = 1525)))(CONNECT_DATA = (SID = CRP2)))"
Make sure ADJAVA Import completed Successfully. This would load the OAF Pages to the MDS Repository.

3. Restart OACORE Services

. ${HOME}/db_apps_env.sh
. ${xx_scripts_top}/db_apps_env.sh
${ADMIN_SCRIPTS_HOME}/adoacorectl.sh stop
${ADMIN_SCRIPTS_HOME}/adoacorectl.sh start

WFLOAD - Workflow Migration

WFLOAD Download Command

$ WFLOAD apps/${xx_appspassword} 0 Y DOWNLOAD AGWOCOMP.wft <Type Internal Name>
$ WFLOAD apps/***** 0 Y DOWNLOAD $AGCST_TOP/workflow/AGWOCOMP.wft AGWOCOMP

WFLOAD Upload Command

$ WFLOAD apps/${xx_appspassword} 0 Y UPGRADE $ROI_TOP/admin/NAME_ACC.wft

WFLOAD <apps/pwd> 0 Y {UPLOAD | DOWNLOAD | UPGRADE | FORCE} [@<appl-shortname>:]<filepath> [<item type>]

******************************************************

WFLOAD Upload Issue due to NLS_LANG Characterset:

  wferr:
  - 1300: Could not load.
  - 1223: Parse error in line 1141ׂÍA“Á’è‚̃wƒbƒ_[‚É‘®‚µ'.
  - 1203: Warning: line 1141: unescaped quote.
  - 1217: No equal siׂÍA“Á’è‚̃wƒbƒ_[‚É‘®‚µ' truncated to within 30 bytes.
Reason: The NLS_LANG environment variable was not appropriately set for the environment.
Solution: Once NLS_LANG was set to japanese_Japan.JA16SJIS, the .wft file loaded without error. for the file wrongly Arabic Language was set

*********************************************************

WFLOAD Issue ORA-01480 trailing null missing from STR bind value

$ WFLOAD apps/${xx_appspassword} 0 Y DOWNLOAD SERVEREQ.wft SERVEREQ
>>> Item type SERVEREQ
wferr:
  - 1114: Could not load from database.
  - 1115: Could not load all definitions referenced by 'SERVEREQ' item type.
  - 1115: Could not load contents of 'SERVEREQ' item type.
  - 1101: Could not load item types from database. FILTER=SERVEREQ
  - 210: Oracle Error: ORA-01480: trailing null missing from STR bind value.  SQL text: SELECT protect_level, custom_level, name, display_name, description, wf_selector, read_role, write_role, execute_role,        persistence_type, to_char(persistence_days) FROM   wf_item_types_vl WHERE  name like :itemtype ORDER BY name

There are possible three solutions for this issue, verify and implement, whichever is applicable to you:


Check#1: Workflow Internal Name is More than 7 Character Long?

Is it happening for longer than 7 Characters Long or All(Workflow Internal Name), try to down POAPPRV(PO Approval Workflow and Verify the same.
If Check#1 fails, check the Patch#11838073. This is applicable for 12.1.1, 12.1.2 and 12.1.3.
For release 12.0.x oneoff   12361895 - 1OFF:9440402:12.0.4:12.0.4:ORA-01480 WHEN TRYING TO DOWNLOAD 8-CHARACTER LONG
Apply Patch 12361895:R12.OWF.A and it's pre-reqs in a test environment.

Check#2: Protection Level of Workflow Definition are Different?

Is it happening for some workflows and some are going successful, Protection Level of Workflow Definition are Different?
If this is the case then the probable reason is mismatch in the protection level of the workflow definition

Successful:
WFLOAD apps/${xx_appspassword} 0 Y DOWNLOAD POAPPRV.wft POAPPRV
Failure:
WFLOAD apps/${xx_appspassword} 0 Y DOWNLOAD PAWFPPWP.wft PAWFPPWP

Verify the Protection Level of Workflow Definition using following command:
SELECT protect_level, custom_level, name, display_name, description, wf_selector, read_role, write_role, execute_role, persistence_type, to_char(persistence_days) FROM wf_item_types_vl WHERE name like 'PAWFPPWP' ORDER BY name;

Relinking the WFLOAD Executable would resolve the issue:
$ cd $AD_TOP/bin
$ adrelink force=y "fnd WFLOAD"

Check#3: Is it happening for OKSKPRCS ITEM_TYPE Only?

Check if the file versions are lesser than these:
wfdes.lc 120.3.12000000.2
wfldr.lc 120.3.12000000.2
If yes, Apply Patch 12361895:R12.OWF.A and it's pre-reqs in a test environment.

Database Listener Issue | TNS-12555: TNS permission denied

[oracrp2@mkkdbserver1 ~]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 13-MAR-2011 23:45:33
Copyright (c) 1991, 2004, Oracle.  All rights reserved.
Starting /hypprod/oracrp2/OraHome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.1.0.5.0 - Production
System parameter file is /hypprod/oracrp2/OraHome_1/network/admin/listener.ora
Log messages written to /hypprod/oracrp2/OraHome_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkkdbserver1.mkkdomain.local)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12555: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00525: Insufficient privilege for operation
   Linux Error: 1: Operation not permitted


Solution: 1
****************

[root@mkkdbserver1 ~]# rm -rf /var/tmp/.oracle
Please be aware that this step should ONLY be taken as a last resort and ONLY when there are no  Net connections to the database(s).  This procedure could cause active sessions to be terminated.  It is NOT recommended to take this action in a RAC environment.  see Note 391790.1 for details.
Clear the /var/tmp/.oracle directory and start from fresh:
1. Stop all listeners
2. Remove the /var/tmp/.oracle directory, e.g. run the following from the root account:
rm -rf /var/tmp/.oracle
3. Check the listener(s) configuration contains unique keys specified for each IPC endpoint and each listener
4. Start the listener(s) from the normal Oracle OS user.
Clearing the /var/tmp/.oracle directory will force the listeners to recreate the socket files from fresh, each with its own correct ownership.
You need to make sure that you always start the listener(s) from the same OS user(s) and that you do not reuse IPC key endpoints between listeners running under different users, otherwise the problem will resurface.

Solution: 2
**************

1. Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.
2. Confirm that the user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.
3. If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.
To implement the solution, please use the following example:
1. cd /var/tmp
2. Check the whether the .oracle directory exists:
cd .oracle
3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777
mkdir /var/tmp/.oracle
chmod 01777 /var/tmp/.oracle
chown root /var/tmp/.oracle
chgrp root /var/tmp/.oracle
4. Next try starting the TNS Listener using the ‘lsnrctl start <listener_name>’ command.

 

After Cloning Managers are not coming up

Method#1:

● Clean the FND_NODE Info:
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
●● Run Autoconfig on DB node and then Apps Node
●●● Run $FND_TOP/patch/115/sql/afdcm037.sql script
●●●● Start Services (adstrtal.sh)


Method#2:

● Ensure Concurrent :GSM Enabled profile is set to ‘Y’
●● Run $FND_TOP/patch/115/sql/afdcm037.sql

●●● Do Relinking of FND Conc Libraries:
cd $FND_TOP/bin
adrelink.sh force=y "fnd FNDLIBR"
adrelink.sh force=y "fnd FNDSM"
adrelink.sh force=y "fnd FNDFS"
adrelink.sh force=y "fnd FNDCRM"

Before bringing the managers up, execute CMCLEAN….. In most of the cases it helped….

FNDLOAD Utility - Some Ready Reference

CUSTOM_MODE=FORCE in Upload

FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct AG_HR_EMP_RETIRE_PROG.ldt CUSTOM_MODE=FORCE


MENU

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct <Menu_Name>.ldt MENU MENU_NAME="<Menu_Name>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <Menu_Name>.ldt


CONCURRENT PROGRAM

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <ccm_shrt_nm>.ldt PROGRAM APPLICATION_SHORT_NAME="ROI" CONCURRENT_PROGRAM_NAME="<ccm_shrt_nm>"
FNDLOAD apps/${xx_appspassword}  O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct <ccm_shrt_nm>.ldt


REQUEST_SET

FNDLOAD apps/${xx_appspassword}  O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <req_set>.ldt REQ_SET_LINKS REQUEST_SET_NAME="<req_set>"
FNDLOAD apps/${xx_appspassword}  0 Y UPLOAD @FND:patch/115/import/afcprset.lct <req_set>.ldt REQ_SET_LINKS



RESPONSIBILITIES

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct <Resp_Key>.ldt FND_RESPONSIBILITY RESP_KEY="<Resp_Key>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <Resp_Key>.ldt FND_RESPONSIBILITY




VALUE_SET

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <val_set>.ldt VALUE_SET FLEX_VALUE_SET_NAME=<val_set>
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct <val_set>.ldt




REQUEST GROUP

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <req_grp>.ldt REQUEST_GROUP REQUEST_GROUP_NAME="<req_grp>" APPLICATION_SHORT_NAME="ROI"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct <req_grp>.ldt




PRINTER STYLES

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct <prntr_styl>.ldt STYLE PRINTER_STYLE_NAME="<prntr_styl>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct <prntr_styl>.ldt




ALERT

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct <alert>.ldt ALR_ALERTS APPLICATION_SHORT_NAME="FND" ALERT_NAME="<alert>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $ALR_TOP/patch/115/import/alr.lct <alert>.ldt




USER

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct <user_name>.ldt FND_USER USER_NAME='<user_name>'
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <user_name>.ldt

Notes for using FNDLOAD against FND_USER:-
1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER 
3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users. 




LOOKUP

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct <lookup_type>.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXCTO' LOOKUP_TYPE="<lookup_type"
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct <lookup_type>.ldt



FUNCTION

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCTO_VEH.ldt FUNCTION FUNCTION_NAME="XXCTO_VEH%"
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCTO_VEH.ldt

OBIEE xmlpserver URL Login Issue

Issue with URL http://121.10.19.56:9704/xmlpserver
Unable to login to BI publisher through administrator user getting the below error:
oracle.apps.xdo.security.ValidateException


●● Change the Following file:
/obieeapp/oracle/OBIEE2/OracleBI/xmlp/XMLP/Admin/Configuration/xmlp-server-config.xml

Change No#1:
Old:
<property name="SECURITY_MODEL" value="BI_SERVER"/>
New:
<property name="SECURITY_MODEL" value="XDO"/>
Change No#2:
Old:
<property name="BI_SERVER_SECURITY_URL" value="jdbc:oraclebi://mkkobieesrv1:9703/"/>
New:
<property name="BI_SERVER_SECURITY_URL" value="jdbc:oraclebi://121.10.19.56:9703/"/>

Basically these are the relevant files to look into, in our case we changed only in xmlp-server-config.xml:
$ORACLE_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\xmlp-server-config.xml
$ORACLE_HOME\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml
$ORACLE_HOME\xmlp\XMLP\Admin\Security\principals.xml

●● Shutdown all BI Related Services.
●● Assuming your OBI admin user is, 'Administrator' and its password is 'Administrator', Run the cryptotools utility to add 'bipublisheradmin' user alias
cd <OracleBI>/setup
. ./sa-init.sh >>> Use this instruction when you are running OBIEE 32-bit
. ./sa-init64.sh >>> Use this instruction when you are running OBIEE 64-bit
<OracleBI>/web/bin/cryptotools credstore -add -inFile <OracleBIData>/web/config/credentialstore.xml -alias bipublisheradmin -username Administrator -password Administrator -passphrase admin
/obieeapp/oracle/OBIEE2/OracleBI/web/bin/cryptotools credstore -add -inFile /obieeapp/oracle/OBIEE2/OracleBIData/web/config/credentialstore.xml -alias bipublisheradmin -username Administrator -password Administrator -passphrase admin
●● Start all BI Related Services.

If SSO Integration in Place you cannot change the password from System Administrator, User Management, FND_USER_PKG

ORA-20001: APP-FND-02602: Unabled to change password for user [username] for this following reason:
This password is not managed by Oracle Applications so it can not be changed here..
ORA-06512: "APPS.APP_EXCEPTION", at line 72
ORA-06512: "APPS.FND_USER_PKG", at line 322
ORA-06512: "APPS.FND_USER_PKG", at line 1147
ORA-06512: at line 1

This error occurs if you have both SSO-enabled E Business Suite (EBS) and are trying to use the OIM E Business Suite User Management connector to manage passwords in EBS. If you SSO-enable an EBS user, then their password is no longer stored in EBS, rather, it is stored in OID / SSO. As a result, EBS refuses to allow OIM to update the password.

You cannot use the OIM E Business Suite User Management connector to manage passwords for SSO-enabled EBS users. You need to choose one of the following:
1. Do not use the Update Password feature of OIM EBS User Management connector, or
2. not to SSO enable E Business Suite 

Change the %Application%SSO% Profile Options:
Application SSO LDAP Synchronization
Application SSO Login Types

Assign Responsibility From Backend

Find the RESPONSIBILITY_ID from RESPONSIBILITY_NAME:

select RESPONSIBILITY_ID,RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_NAME like 'Service%Contra%AMERICAS%CORE';

Verify whether the User is already having the responsibility assigned:

select USER_NAME,USER_ID from fnd_user where USER_NAME = 'MKHAWAS';
select USER_ID,RESPONSIBILITY_ID from FND_USER_RESP_GROUPS_DIRECT where USER_ID=29273 and RESPONSIBILITY_ID=53545;

Assign the Responsibility Using the Following SQL:

DECLARE

  v_user_name  VARCHAR2(30) := '&Enter_User_Name';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY 
  into v_appl_id ,v_resp_key 
  from FND_RESPONSIBILITY_VL 
where RESPONSIBILITY_ID=('&Enter_Resp_Id');

select APPLICATION_SHORT_NAME 
  into v_resp_appl 
  from FND_APPLICATION_VL   
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;
commit;
/

How to Interpret the ACCOUNT_STATUS Column in DBA_USERS

The DBA_USERS.ACCOUNT_STATUS can have the following values :

select * from user_astatus_map;

STATUS# STATUS
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

These values are directly related to two features 'Account Locking' and 'Password Aging and Expiration'




Account Locking - LOCKED/LOCKED(TIMED)

An account can be locked by a DBA or is locked automatically after a number of failed login attempts. 
When a PASSWORD_LOCK_TIME is defined, the account unlocks automatically after the set time: this is indicated by LOCKED(TIMED). The LOCKED(TIMED) value is what you always see if the account was locked due to the number of failed logins > FAILED_LOGIN_ATTEMPTS.

You only see LOCKED if the account is explicitly locked during create or alter user/role.

You may expect that when the account is automatically locked and PASSWORD_LOCK_TIME is set to unlimited, the account would appear as just LOCKED. This is however not the case: it still shows as LOCKED(TIMED). However the account never unlocks automatically because of the unlimited PASSWORD_LOCK_TIME (infinity).

To add to the confusion, at some point it was decided that even if the account is locked automatically, we should not set it to LOCKED(TIMED) if the PASSWORD_LOCK_TIME is unlimited, since in that case the account would never automatically unlock, this would give up on a crucial piece of information, namely if the account was locked manually or automatically, this change was introduced in 11.2.0.1. However this change caused a regression in Bug 9693615 causing the lock_date to be NULL in dba_users in case the account was locked automatically, the fix to this bug backed out the change again and now we have the LOCKED(TIMED) for automatically locked accounts back with this fix

So a DBA will know that when the ACCOUNT_STATUS is LOCKED(TIMED) that the lock was a result of a failed login attempt, even if the lock will not expire. In 11.2.0.1 (without the fix to Bug 9693615) you can verify if the account was locked automatically if the lock_date in dba_users is null.




Password Expiration - EXPIRED/EXPIRED(GRACE)

A password can be set to expire, with or without a grace period:
●● When a password expires and no grace is defined, the password is set at EXPIRED, meaning that the user is prompted for a new password upon the next login attempt.
●● When a grace is defined, during the grace period, a warning is issued, and the ACCOUNT_STATUS is set to EXPIRED(GRACE).




Password Expiration and Account Locking are two separate features:

●● An account cannot be locked by exceeding the expire or subsequent grace time.
●● An account that is neither expired nor locked appears as OPEN (STATUS# 0),

Since account locking (based on failed login attempts) and expiration (based on not changing password) are basically unrelated.

In addition to the EXPIRED (1,2) and LOCKED (4,8) bits being set, you can have combinations of both with STATUS# 5,6,9 and 10: internally the STATUS# are added for the combinations of expired and locked.
For example : 'EXPIRED' + 'LOCKED' = 1 + 8 = 9 = 'EXPIRED & LOCKED'

Issue with Debugging using PL/SQL Developer

ORA-0131: Insufficient privileges
Note: Debugging requires the DEBUG CONNECT SESSION system privilege

grant DEBUG CONNECT SESSION to XXXXXXX;
grant DEBUG ANY PROCEDURE to XXXXXX;

Database Sizing Related Parameters, Ready Reference for 1000+ Users

This is the parameter sizing I have used for 3 production instances  for 3 different clients... working good.... just putting here for easy reference:


*.sga_target = 14G
*.shared_pool_size = 3G
*.shared_pool_reserved_size = 300M
*.streams_pool_size = 150M
*.java_pool_size = 1G
*.pga_aggregate_target = 20G
*.result_cache_max_size = 150M
*.db_cache_size = 2G
*.large_pool_size=300M
*.db_keep_cache_size = 500M
*.db_writer_processes = 3
*.processes = 2500
*.sessions = 5000
*.open_cursors = 1500

After 11gR2 Grid Infrastructure Cluster Installation, run Cluster Verification Utility to Verify Everything is Up and Running

After 11gR2 Grid Infrastructure Cluster Installation, run Cluster Verification Utility to Verify Everything is Up and Running

[oraprod@mkkprodracdb1 grid]$ ./runcluvfy.sh stage -post crsinst -n mkkprodracdb1,mkkprodracdb2

Performing post-checks for cluster services setup

Checking node reachability...
Node reachability check passed from node "mkkprodracdb1"


Checking user equivalence...
User equivalence check passed for user "oraprod"
Checking time zone consistency...
Time zone consistency check passed.


Checking Cluster manager integrity...

Checking CSS daemon...
Oracle Cluster Synchronization Services appear to be online.

Cluster manager integrity check passed


UDev attributes check for OCR locations started...
UDev attributes check passed for OCR locations


UDev attributes check for Voting Disk locations started...
UDev attributes check passed for Voting Disk locations

Default user file creation mask check passed

Checking cluster integrity...


Cluster integrity check passed


Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all cluster nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+PRODOCR1" available on all the nodes


Checking size of the OCR location "+PRODOCR1" ...

Size check for OCR location "+PRODOCR1" successful...
Size check for OCR location "+PRODOCR1" successful...

WARNING:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Checking CRS integrity...

CRS integrity check passed

Checking node application existence...

Checking existence of VIP node application (required)
Check failed.
Check failed on nodes:
        mkkprodracdb2

Checking existence of ONS node application (optional)
Check passed.

Checking existence of GSD node application (optional)
Check ignored.

Checking existence of EONS node application (optional)
Check passed.

Checking existence of NETWORK node application (optional)
Check passed.


Checking Single Client Access Name (SCAN)...

Checking name resolution setup for "mkkprodscan.mkkdomain.intranet"...

Verification of SCAN VIP and Listener setup passed
OCR detected on ASM. Running ACFS Integrity checks...

Starting check to see if ASM is running on all cluster nodes...

ASM Running check passed. ASM is running on all cluster nodes

Starting Disk Groups check to see if at least one Disk Group configured...
Disk Group Check passed. At least one Disk Group configured

Task ACFS Integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

User "oraprod" is not part of "root" group. Check passed

Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...
CTSS resource check passed


Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed

Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP


Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "ntpd"

NTP daemon slewing option check passed

NTP daemon's boot time configuration check for slewing option passed

NTP common Time Server Check started...
PRVF-5408 : NTP Time Server "192.5.41.41" is common only to the following nodes "mkkprodracdb1"
PRVF-5408 : NTP Time Server "193.204.114.232" is common only to the following nodes "mkkprodracdb1"
PRVF-5408 : NTP Time Server "69.36.224.15" is common only to the following nodes "mkkprodracdb1"
Check of common NTP Time Server passed

Clock time offset check from NTP Time Server started...
Clock time offset check passed

Clock synchronization check using Network Time Protocol(NTP) passed


Oracle Cluster Time Synchronization Services check passed

Post-check for cluster services setup was unsuccessful.
Checks did not pass for the following node(s):
        mkkprodracdb2
[oraprod@mkkprodracdb1 grid]$

[oraprod@mkkprodracdb1 grid]$ ./runcluvfy.sh stage -post crsinst -n all -verbose

Performing post-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "mkkprodracdb1"
  Destination Node                      Reachable?
  ------------------------------------  ------------------------
  mkkprodracdb2                           yes
  mkkprodracdb1                           yes
Result: Node reachability check passed from node "mkkprodracdb1"


Checking user equivalence...

Check: User equivalence for user "oraprod"
  Node Name                             Comment
  ------------------------------------  ------------------------
  mkkprodracdb2                           passed
  mkkprodracdb1                           passed
Result: User equivalence check passed for user "oraprod"
Checking time zone consistency...
Time zone consistency check passed.


Checking Cluster manager integrity...

Checking CSS daemon...

  Node Name                             Status
  ------------------------------------  ------------------------
  mkkprodracdb2                           running
  mkkprodracdb1                           running

Oracle Cluster Synchronization Services appear to be online.

Cluster manager integrity check passed


UDev attributes check for OCR locations started...
Result: UDev attributes check passed for OCR locations


UDev attributes check for Voting Disk locations started...
Result: UDev attributes check passed for Voting Disk locations


Check default user file creation mask
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   0022                      0022                      passed
  mkkprodracdb1   0022                      0022                      passed
Result: Default user file creation mask check passed

Checking cluster integrity...

  Node Name
  ------------------------------------
  mkkprodracdb1
  mkkprodracdb2

Cluster integrity check passed


Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all cluster nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+PRODOCR1" available on all the nodes


Checking size of the OCR location "+PRODOCR1" ...

Size check for OCR location "+PRODOCR1" successful...
Size check for OCR location "+PRODOCR1" successful...

WARNING:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Checking CRS integrity...
The Oracle clusterware is healthy on node "mkkprodracdb2"
The Oracle clusterware is healthy on node "mkkprodracdb1"

CRS integrity check passed

Checking node application existence...

Checking existence of VIP node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   yes                       unknown                   failed
  mkkprodracdb1   yes                       online                    passed
Result: Check failed.

Checking existence of ONS node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   no                        online                    passed
  mkkprodracdb1   no                        online                    passed
Result: Check passed.

Checking existence of GSD node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   no                        does not exist            ignored
  mkkprodracdb1   no                        does not exist            ignored
Result: Check ignored.

Checking existence of EONS node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   no                        online                    passed
  mkkprodracdb1   no                        online                    passed
Result: Check passed.

Checking existence of NETWORK node application
  Node Name     Required                  Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodracdb2   no                        online                    passed
  mkkprodracdb1   no                        online                    passed
Result: Check passed.


Checking Single Client Access Name (SCAN)...
  SCAN VIP name     Node          Running?      ListenerName  Port          Running?
  ----------------  ------------  ------------  ------------  ------------  ------------
  mkkprodscan.mkkdomain.intranet  mkkprodracdb1   true          LISTENER      1581          true

Checking name resolution setup for "mkkprodscan.mkkdomain.intranet"...
  SCAN Name     IP Address                Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  mkkprodscan.mkkdomain.intranet  192.19.1.130              passed
  mkkprodscan.mkkdomain.intranet  192.19.1.129              passed
  mkkprodscan.mkkdomain.intranet  192.19.1.128              passed

Verification of SCAN VIP and Listener setup passed
OCR detected on ASM. Running ACFS Integrity checks...

Starting check to see if ASM is running on all cluster nodes...

ASM Running check passed. ASM is running on all cluster nodes

Starting Disk Groups check to see if at least one Disk Group configured...
Disk Group Check passed. At least one Disk Group configured

Task ACFS Integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

Checking to make sure user "oraprod" is not in "root" group
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  mkkprodracdb2   does not exist            passed
  mkkprodracdb1   does not exist            passed
Result: User "oraprod" is not part of "root" group. Check passed

Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...
Check: CTSS Resource running on all nodes
  Node Name                             Status
  ------------------------------------  ------------------------
  mkkprodracdb2                           passed
  mkkprodracdb1                           passed
Result: CTSS resource check passed


Querying CTSS for time offset on all nodes...
Result: Query of CTSS for time offset passed

Check CTSS state started...
Check: CTSS state
  Node Name                             State
  ------------------------------------  ------------------------
  mkkprodracdb2                           Observer
  mkkprodracdb1                           Observer
CTSS is in Observer state. Switching over to clock synchronization checks using NTP


Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP Configuration file check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
  Node Name                             Running?
  ------------------------------------  ------------------------
  mkkprodracdb2                           yes
  mkkprodracdb1                           yes
Result: Liveness check passed for "ntpd"

Checking NTP daemon command line for slewing option "-x"
Check: NTP daemon command line
  Node Name                             Slewing Option Set?
  ------------------------------------  ------------------------
  mkkprodracdb2                           yes
  mkkprodracdb1                           yes
Result:
NTP daemon slewing option check passed

Checking NTP daemon's boot time configuration, in file "/etc/sysconfig/ntpd", for slewing option "-x"

Check: NTP daemon's boot time configuration
  Node Name                             Slewing Option Set?
  ------------------------------------  ------------------------
  mkkprodracdb2                           yes
  mkkprodracdb1                           yes
Result:
NTP daemon's boot time configuration check for slewing option passed

NTP common Time Server Check started...
PRVF-5408 : NTP Time Server "192.5.41.41" is common only to the following nodes "mkkprodracdb1"
NTP Time Server "182.23.102.198" is common to all nodes on which the NTP daemon is running
PRVF-5408 : NTP Time Server "193.204.114.232" is common only to the following nodes "mkkprodracdb1"
PRVF-5408 : NTP Time Server "69.36.224.15" is common only to the following nodes "mkkprodracdb1"
NTP Time Server ".LOCL." is common to all nodes on which the NTP daemon is running
Check of common NTP Time Server passed

Clock time offset check from NTP Time Server started...
Checking on nodes "[mkkprodracdb2, mkkprodracdb1]"...
Check: Clock time offset from NTP Time Server

Time Server: 182.23.102.198
Time Offset Limit: 1000.0 msecs
  Node Name     Time Offset               Status
  ------------  ------------------------  ------------------------
  mkkprodracdb2   -25.438                   passed
  mkkprodracdb1   11.585                    passed
Time Server "182.23.102.198" has time offsets that are within permissible limits for nodes "[mkkprodracdb2, mkkprodracdb1]".

Time Server: .LOCL.
Time Offset Limit: 1000.0 msecs
  Node Name     Time Offset               Status
  ------------  ------------------------  ------------------------
  mkkprodracdb2   0.0                       passed
  mkkprodracdb1   0.0                       passed
Time Server ".LOCL." has time offsets that are within permissible limits for nodes "[mkkprodracdb2, mkkprodracdb1]".
Clock time offset check passed

Result: Clock synchronization check using Network Time Protocol(NTP) passed


Oracle Cluster Time Synchronization Services check passed

Post-check for cluster services setup was unsuccessful.
Checks did not pass for the following node(s):
        mkkprodracdb2
[oraprod@mkkprodracdb1 grid]$

Highlighted issue was due to IP Conflict in DNS, after the IP Conflict is resolved in DNS, and root.sh run in both Database Server Nodes, performed the following steps to add the VIP Manually:

Aftre the IP Conflicts resolved manually run the following command from db02:

# ./srvctl add vip -n mkkprodracdb2 -k 1 -A mkkprodracdb2-vip/255.255.255.0/eth0
# ./srvctl start vip -n mkkprodracdb2
# ./srvctl start listener -n mkkprodracdb2