What Does HRGLOBAL Do?

What Does HRGLOBAL Do?

HRGLOBAL execution only do the work at database end, in short it works on FNDLOAD, PYLOAD, FFXMLC and various SQL executions. It shall be executed only from Admin Node and nonshared applications just once. Do not be in misconception that you would have to execute multiple times from various node.

I have just given a short summary of various steps it performs, though not in order, but in summary, HRGLOBAL only touches your database.

1. It disbales the HRMS Access using pydsblhr.sql. Manually also if one wants to restric the HRMS access this can be executed.

2. It cleans the Orphan Data Using payorpcleans1.sql payorpcleans2.sql payorpcleans3.sql

3. Calls SQL Files for online patching.

4. Put the details of the current environment in files using hrglobal_info.sql and hrglobal_chkpreq.sql.

5. Executes FNDLOAD to load help data using afscprof.lct file.

6. Run PYLOAD and FNDLOAD to add legislative data.

7. Regenerate the balance data for HR Legislation

8. Do Fast Formula related activities using FFXMLC

9. Perform Country Specific HRMS Database activity.

10. Enable HRMS Access using pyenblhr.sql.

Recreate FND_CONCURRENT_QUEUES Information

Recreate FND_CONCURRENT_QUEUES Information

Publishing this on request of a friend.... It was applicable for him after clone.

Run FND_CONC_CLONE
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables. 

Connect to SQLPLUS as APPS user and run the following statement :
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not return any value please do the following:
cd $FND_TOP/patch/115/sql
START afdcm037.sql;

Check again that FNDSM entries now exist:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

Run CMCLEAN.sql and start the Managers.

This would help after cloning if the Managers are not coming up.

opatch auto - 11gR2

opatch auto - 11gR2

The OPatch utility has automated the patch application for the Oracle Grid Infrastructure (GI) home and the Oracle Database home.

On an 11gR2 RAC installation, if there is no existing database associated to the RDBMS home, when applying a patch using "opatch auto" command, OPatch will patch the Grid Infrastructure home but not the RDBMS home.
But, one or more database exists associated to the RDBMS home, then OPatch will patch both the Grid Infrastructure home and the RDBMS Home.

The opatch auto retrieves the db home information from the configured databases. So, if there is no existing database, then "OPatch auto" will skip that RDBMS Home while patching.

In order to patch the RDBMS home that has no database configured, use "-oh" option in opatch auto.   For example:
opatch auto < Patch Location > -oh /ora/oh1,/ora/oh2,/ora/oh3

You have encountered an unexpected PLSQL Error, Please contact System Administrator

You have encountered an unexpected PLSQL Error, Please contact System Administrator

Enable FND Debug using followin profile options:

FND: Debug Log Enabled Yes
FND: Debug Log Filename <empty>
FND: Debug Log Level STATEMENT
FND: Debug Log Mode Asynchronous with Cross-Tier Sequencing
FND: Debug Log Module %

2. Run the following SQL and and write down this number
SQL> select max(log_sequence) from fnd_log_messages;

3. Reproduce the issue and run the following SQL again to get the relevant information:
SQL> select * from fnd_log_messages where log_sequence > NUMBER_IDENTIFIED_BEFORE_IN_SQL_STATEMENT_AT_STEP_2 order by log_sequence;

Verify in the fnd_log_messages, you can see the following:

106500495|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|
106500496|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |l_err_code :FND_SSO_UNEXP_ERROR, l_tmp_str :ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|
106500497|fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log|Unabled to call fnd_ldap_wrapper.create_user due to the following reason:
An unexpected error occurred. Please contact your System Administrator. (USER_NAME=SHARMAJ1)|

If this is the case, then during cloing, DBAs must have screwed up OID registration from your live system.

Do a fresh registration using txkrun.pl:
SQL> delete from fnd_user_preferences where user_name='#INTERNAL'
$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registerinstance=yes
$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registeroid=yes -provisiontype=3

eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

Recently I got a chance to work on a eBusiness Data Masking requirement. Generally, few members try to differentiate between Data Masking, Data Scrambling and Data Anonymization, unfortunately that is bit misconception.

Definition what Oracle Provides:
Data masking, also known as data scrambling or data anonymization, is the process of obscuring sensitive information copied from a production database with realistic, scrubbed data based on masking rules, to a test or non-production database

I would like to share some learnings from this masking exercise:

1. FND_USER_MASKING_EXEMPTIONS/Exempt Users From Masking

By default seeded users(like SYSADMIN, GUEST) whose USER_ID is less than 1000 are exempted from masking. But if all other usernames are masked then testing users would be impacted, so prepare the list, who need access in the eBusiness and put them in FND_USER_MASKING_EXEMPTIONS.

Data Masking Pack Provides two SQLs
fndusmaexcr.sql - Creates the FND_USER_MASKING_EXEMPTIONS table.
fndusmaexpo.sql - Provides examples of how to populate the table.

FND_USER_MASKING_EXEMPTIONS >>> Definition >>> USER_ID, SOURCE, CURRENT_NAME, NEW_NAME, NEW_PASSWD
The CURRENT_NAME column contains the name of the user to exempt from standard masking.

2. What template needs to be used, JE(Standard) or JG, is decided by the table JE_ES_MODELO_190_ALL.
select OWNER  from all_tables  where TABLE_NAME = 'JE_ES_MODELO_190_ALL';

Use the standard version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JE schema. Use the JG version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JG schema.

High Level Steps for Data Masking is as follows

1. Compile all the objects before you start Generate Mask.
2. Generate Masks Include, Patching Activity for Data Masking Template Patch, Steps for Exempt Users, Create Tables using Pre Generate SQL, Edit Masking Definition, Remove Errors/Warnings.
3. If Environment is SSO Enabled, remove SSO using txkrun.pl deregister OR Delete Directly from FND_USER_PREFERENCES. If any LDAP operation is running like DIP(like OID to eBusiness synch), then there can be deadlock in the database and ORA-00060: deadlock detected while waiting for resource would be reported.
4. Execute the Mask.
5. Save the masking script and template, so that you can run on all instances where masking is required, and after each clone.

*** If You Forget to Provide Required Grant
Provide DBMS_CRYPTO/EXEMPT ACCESS POLICY Grant to SYSTEM, missing this become problemetic, as you need to recreate various triggers, taking the code from some other instance. Otherwise masking would not happen.
GRANT EXECUTE ON DBMS_CRYPTO TO SYSTEM;
GRANT EXEMPT ACCESS POLICY TO SYSTEM;

*** Stop the application, and start the Concurrent Managers Only
Note that several concurrent requests are started during the mask (Employee Update Program and DQM Staging Program). Verify that these programs complete successfully after bringing up the Concurrent Manager. Note that the Employee Update Program may log errors of "Update of Name was not done as the update would result in a duplicate supplier record".

In the Masking execution spool, you would see like following:
Completed Data Masking. Starting cleanup phase.                                 
Starting cleanup of generated procedures                                        
Completed cleanup of generated procedures                                       
Script execution complete                                                       
Program: fndusmaexpr                                                            
Process FND_USER_MASKING_EXEMPTIONS                                             
======================================                                          
... setup context.                                                              
... process records.                                                            
======================================                                          
End program - successful                                                        
request_id = 1152548                                                            
request_id = 1152549                                                            
request_id = 1152550                                                            
EBS drop table - map_scl_dk                                                     
EBS drop table - map_scl_nl_1                                                   

Make sure all these requests are completed successfully.

*** Customisation On Data Masking Template
For Custom columns you can add in this template rule, You can remove if you do not need the seeded template to mask some columns.

*** Add Datafile to SYSTEM Tablespace
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

*** Add Tempfile to Temp Tablespace
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;

**** Rebuild all INVALID/UNUABLE Indexes
ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD;

**** After Masking None of the Business Users are able to Access the System
If I remove the employee from the user and reset the password then that was able to access the system. You can reassign back the employee to the user. This is not a feasible solution if NONLIVE instances are accessed by many users.

"Workflow Directory Services User/Role Validation" Parameters : 100000, Yes, No, No
"Workflow Directory Services User/Role Validation" Parameters : 100000, No, Yes, No   

1. Run the following requests in the order below: 
a. "Create FND_RESP WF Roles"
b. "Sync responsibility role data into the WF table.(obsolete in R12)"
c. "Synchronize WF LOCAL tables"
d. "Workflow Directory Services User/Role Validation"

Make sure Workflow Background Process is properly Scheduled. Follow this for best practice on this:
http://mkkoracleapps.blogspot.com/2012/12/workflow-related-scheduled-concurrent.html

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

This I generally do a post clone steps to purge the login related information.

$ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall

truncate table APPLSYS.FND_LOGINS;
truncate table APPLSYS.FND_LOGIN_RESP_FORMS;
truncate table APPLSYS.FND_LOGIN_RESPONSIBILITIES;
truncate table APPLSYS.FND_UNSUCCESSFUL_LOGINS;
truncate table ICX.ICX_SESSIONS;
truncate table ICX.ICX_FAILURES;
truncate table ICX.ICX_SESSION_ATTRIBUTES;
truncate table ICX.ICX_TEXT;
truncate table ICX.ICX_CONTEXT_RESULTS_TEMP;
truncate table ICX.ICX_TRANSACTIONS;
truncate table APPLSYS.FND_SESSION_VALUES;

$ADMIN_SCRIPTS_HOME/adopmnctl.sh startall

Also if you are not able to execute purge signon conc prog, due to huge volume of data in the underlying tables then truncate is an option that you can pursue. OR you have to delete in multiple iterations.

Relevant Concurrent Program to Purge: Purge Signon Audit Data

Purge Inactive Sessions E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql

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….

Options with adcfgclone.pl

Application Node: appsTier | appltop  | atTechStack

Database Node: dbTier   | database | dbconfig | dbTechStack | addracnode

Context File: Target Context File
If no context is specified, adcfgclone.pl will prompt for the cloned system target values to create the new context file. This script also prompts for the APPS password. Additionally, if the target Application context file is not specified the APPS password will be prompted for a second time in order to create the new context file.

For more information: perl adcfgclone.pl help

for usage examples: perl adcfgclone.pl examples

ORA-01166 in adcfgclone.pl dbTier | Issues with adcfgclone dbTier

ORA-01503: CREATE CONTROLFILE failed
ORA-01166: file number 601 is larger than MAXDATAFILES (600)
ORA-01110: data file 601: '/d10/oracle/mkkdbdata/hrx35.dbf'

Solution:

1. Correct s_dbfiles in source context file with your maximum number of database files. 
In order to find the maximum file id use:
select max(FILE_ID) from DBA_DATA_FILES;

2. Run autoconfig on DB tier on source in order to propagate the changes.

3. Redo all cloning steps.

Issue with ADCFGCLONE | BEGIN failed--compilation aborted at adcfgclone.pl | Issue with vars.pm

$ perl adcfgclone.pl appsTier
Invalid range "a-Z" in transliteration operator at /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/vars.pm line 17.
Compilation failed in require at /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/File/Basename.pm line 132.
BEGIN failed--compilation aborted at /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/File/Basename.pm line 132.
Compilation failed in require at /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/File/Path.pm line 100.
BEGIN failed--compilation aborted at /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/File/Path.pm line 100.
Compilation failed in require at adcfgclone.pl line 89.
BEGIN failed--compilation aborted at adcfgclone.pl line 89.

Solution:

Open the file /u01/oracle/mkkappsora/iAS/Apache/perl/lib/5.00503/vars.pm and modify as follows:
Wrong Entry:   if ($sym =~ tr/A-Za-Z_0-9//c) {
Correct Entry:  if ($sym =~ tr/A-Za-z_0-9//c) {

Cheers!!!!
Malay

Meaning of Services | Services Needs to be enabled while running adcfgclone.pl

Root Services [Enabled]
Oracle Process Manager for $CONTEXT_NAME - [adopmnctl.sh]


Web Entry Point Services [Enabled]
Oracle HTTP Server and Oracle TNS Listener [adapcctl.sh, adalnctl.sh]


Web Application Services [Enabled]
OACORE OC4J Instance[adoacorectl.sh], FORMS OC4J Instance[adformsctl.sh], OAFM OC4J Instance[adoafmctl.sh]


Batch Processing Services [Enabled]
Oracle Concurrent Managers[adcmctl.sh], Oracle Fulfillment Server[jtffmctl.sh]


Other Services [Disabled]
OracleFormsServer-Forms[adformsrvctl.sh] Oracle Metrics Client[adfmcctl.sh] Oracle Metrics Server[adfmsctl.sh] Oracle MWA Service[mwactlwrpr.sh]

Cheers!!!!
Malay

Notification Mailer Override Address and Status

Set Override Address

Override address is set in test instance using "Workflow Administrator Web Application" responsibility in test instance so that all notification mails goes to a single mail ID.

How to Update Override Address from Backend?

select fscpv.parameter_value, fscpt.parameter_id from fnd_svc_comp_params_tl fscpt,fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' and fscpt.parameter_id = fscpv.parameter_id
    
UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='mkhawas@nomail.com'
WHERE parameter_id=(select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID)

exec FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW ( x_component_name => 'Workflow Notification Mailer', x_parameter_name => 'TEST_ADDRESS', x_parameter_value => 'mkhawas@nomail.com', x_customization_level => 'L', x_object_version_number => -1, x_owner => 'ORACLE' );
commit;


Update SMTP Server from Backend:

UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='<<<<<SMTP Server IP or Host>>>>>>'
WHERE parameter_id IN (select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Outbound Server Name' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID);


How to check Notification Mailer is Up/Down from Backend:

select SC.COMPONENT_TYPE, SC.COMPONENT_NAME,
FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS
from FND_SVC_COMPONENTS SC
order by 1, 2;

Cheers !!!!!

HTTP Converted to HTTPS | SSLTERMINATOR in Context File

HTTP Converted to HTTPS | SSLTERMINATOR in Context File

While trying to open using http://mkkserver1.mkkdomain.net:8004/OA_HTML/AppsLocalLogin.jsp its opening the login page and can launch forms, but when going to any JSP Pages like OAM again its throwing error.

IEHTTP Header Log showing the following message:
HTTP/1.1 302 Moved Temporarily
Date: Mon, 14 Mar 2011 06:48:17 GMT
Cache-Control: no-cache
Pragma: no-cache
Expires: Thu, 01 Jan 1970 00:00:00 GMT
Location: https://mkkserver1.mkkdomain.net:8004/OA_HTML/AppsLocalLogin.jsp
Keep-Alive: timeout=15 >>>> Though it was http only, its getting converted to https
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/html

I have checked the $CONTEXT_FILE and verified that webentry, login URLs are perfect, but there is no HTTPS. Basically SSLTERMINATOR is creating the issue

Comment out SSLTERMINATOR in xml file and run autoconfig again. That will resolve the issue.
Hash out the s_enable_sslterminator parameter in $CONTEXT_FILE
Old Value: <sslterminator oa_var="s_enable_sslterminator"/>
New Value: <sslterminator oa_var="s_enable_sslterminator">#</sslterminator>

After Cloning Managers are not coming up

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….


Cheers !!!!!!!