Tuesday, July 19, 2022

How to Compile Forms , Reports & Custom.pll in R12.2

How to Compile Custom.pll 

 cd $AU_TOP/resource
 cp CUSTOM.plx CUSTOM.plx_bkup
 cp CUSTOM.pll CUSTOM.pll_bkup
 frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special


How to Compile Forms

Ensure that the $FORMS_PATH includes $AU_TOP/resource, $AU_TOP/resource/stub, and $AU_TOP/forms/US

Custom [ any forms customizations  used -CUSTOM.pll ]:

frmcmp_batch.sh module=$AU_TOP/forms/US/xxxxxxx.fmb userid=apps/apps output_file=$PER_TOP/forms/US/xxxxxxx.fmx module_type=form compile_all=special

Seeded:

frmcmp_batch.sh module=$AU_TOP/forms/US/xxxxxxxxx.fmb userid=apps/apps output_file=$PER_TOP/forms/US/xxxxxxxx.fmx module_type=form

How to Compile Reports

rwconverter.sh userid=APPS/APPS source=$FND_TOP/reports/US/FNDATJAP.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes

rwconverter.sh userid=APPS/APPS source=$FND_TOP/reports/US/FNDATJAP.rdf dest=/tmp/FNDATJAP.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes



Monday, July 18, 2022

Database startup failed with ORA-27102: out of memory in Solaris


Database startup failed with  "ORA-27102: out of memory" in solaris. Even though we had enough memory in the server , value of project.max-shm-memory was low. Please follow below steps to increase the same.


How to Change project.max-shm-memory

$ prctl -n project.max-shm-memory -v 35gb -r -i project user.oraprod


How to check max-shm-memory of a current user?

$ prctl -n project.max-shm-memory -i process $$

process: 17844: -bash

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-shm-memory

        usage           11.6GB

        privileged      35.0GB      -   deny                                 -

        system          16.0EB    max   deny                                 -

Saturday, July 16, 2022

12.2 upgrade driver failed | jtfiaibu.sql | DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

Error:

12.2 upgrade driver failed on jtfiaibu.sql  with DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

"sqlplus -s APPS/***** @/jaydg/oraapps/jayprod/apps/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/jaydg/oraapps/jayprod/apps/fs1/EBSapps/appl/jtf/12.0.0/patch/115/sql/jtfiaibu.sql &un_jtf &pw_jtf &un_apps'

Connected.


PL/SQL procedure successfully completed.


Connected.

DECLARE

*

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

ORA-06512: at ""CTXSYS.DRUE"", line 160

ORA-06512: at ""CTXSYS.TEXTINDEXMETHODS"", line 366

ORA-06512: at ""SYS.DBMS_SQL"", line 1199

ORA-06512: at line 37



Cause:

 jtfiaibu.sql will try to drop and recreate the JTF_AMV_ITEMS_URL_CTX. But Please note that dropping text index using DROP INDEX sql is not enough as it may have some traces in ctxsys.dr$% views. So we had to delete the index from ctxsys.dr% views. 

I feel this issue occurs only on 11.2.0.4 database & not on 12c+ databases.

Solution:

a. Please make sure Oracle Text component is valid in dba_registry.  If not, resolve the dba_registry issue and restart the worker.

b. If Oracle Text is valid & still you are facing the same issue, then follow the below steps.

SQL> select idx_id from ctxsys.dr$index where idx_name='JTF_AMV_ITEMS_URL_CTX';

    IDX_ID

----------

      4153 

SQL> show user;

USER is "APPS"

SQL>  delete from ctxsys.dr$index_value where IXV_IDX_ID = 4153;

86 rows deleted.


SQL> delete from ctxsys.dr$index_object where IXO_IDX_ID = 4153;

9 rows deleted.


SQL> delete from ctxsys.dr$index where idx_id = 4153;

1 row deleted.


SQL> commit;

Commit complete.


C.  Delete DR$ tables .. PLease make sure it doens't have any rows. if required, take a backup of the same.


SQL> select table_name from dba_tables where table_name like 'DR%JTF_AMV_ITEMS_URL_CTX%';


TABLE_NAME

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

DR$JTF_AMV_ITEMS_URL_CTX$I

DR$JTF_AMV_ITEMS_URL_CTX$K

DR$JTF_AMV_ITEMS_URL_CTX$N

DR$JTF_AMV_ITEMS_URL_CTX$R


Drop the above tables.


d. Restart the worker 


EBS: R12.2 Rapidwiz fails due to insufficient diskspace on Solaris/AIX

Rapidwiz failed while performing prereq check due to insufficient diskspace on /tmp directory.  So we tried to set env variable TMPDIR & restarted rapidwiz. however solaris platform doesn't  take the value from env variable TMPDIR and it failed again. 

Finally , We set below variables in order to resolve this issue. Please set TEMP,TMP,TMPDIR,TEMPDIR to the different folder where we have enough space & invoke rapidwiz again.

export TEMP=/u01/temp
export TMP=/u01/temp
export TMPDIR=/u01/temp
export TEMPDIR=/u01/temp

./rapidwiz

Saturday, June 25, 2022

History Option on Sqlplus

 Starting from oracle 12.2, SQL*Plus can keep the history of the commands executed.

You can enable or disable the HISTORY command in the current SQL*Plus session by using the SET HISTORY command.

You can view , delete and run statements easily , instead of scrolling up and down and copy then run previous SQL commands .This would be very useful when running multiple complex statements and you need to re-run them multiple times .


SQL> select name from v$database;

NAME

---------

JAYDEMO

SQL> show hist;

history is OFF

SQL> set hist on;

SQL> select * from global_name;

GLOBAL_NAME

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

JAYDEMO.DEMO.EXAMPLE.COM

SQL> select * from dual;

D

-

X

SQL> history

  1  select * from global_name;

  2  select * from dual;


By default , history keep record of last 100 commands , if you need to increase it , you can do the following :

SQL> set history 1500

SQL> show history

History is ON and set to “1500”


How to automatically turn on this option

But Turing on history every time we login to sqlplus would be challenging. so we can leverage glogin.sql (or) login.sql which would execute the statement we defined every time we login.

The Site Profile file, glogin.sql, for site wide settings. -  $ORACLE_HOME/sqlplus/admin/glogin.sql

Additionally, the User Profile, login.sql, sets user specific settings.  (sqlplus takes the file under $ORACLE_PATH)


Please add below entries in either glogin.sql (or) login.sql 

set hist on;

set history 1000   

Sunday, June 5, 2022

Active Directory Integration with Oracle Linux (SSSD)

I)Create a user linuxsso in AD and add it to Administrator group (or the group which has privilege to add the machine into domain)


II) Install the prereq RPMS

1yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python

Note: For this configuration, the essential package to install is realmd.Realmd provides a simplified way to discover and interact with Active Directory domains. It employs sssd to do the actual lookups required for remote authentication and other heavy work of interacting with the domain

 III) Update the DC as DNS in /etc/resolv.conf

1[root@linux ~]# cat /etc/resolv.conf 2search demo.example.com 3nameserver 10.0.2.18

 IV) Create the DNS record for linux machine on DNS


V) Ensure Linux Node & AD domains are resolvable through nslookup

1[root@linux ~]# nslookup WIN-FRIG2LT3VIU.demo.example.com 2Server: 10.0.2.18 3Address: 10.0.2.18#53 4 5Name: WIN-FRIG2LT3VIU.demo.example.com 6Address: 10.0.2.18 7 8[root@linux ~]# nslookup linux.demo.example.com 9Server: 10.0.2.18 10Address: 10.0.2.18#53 11 12Name: linux.demo.example.com 13Address: 10.0.2.22 14

VI) Realmd (interacting with the domain)

Now that all packages have been installed, the first thing to do is to join the CentOS system to the Active Directory domain. We use the realm application for that. The realm client is installed at the same time as realmd. It is used to join, remove, control access, and accomplish many other tasks. Here is the expected syntax for a simple domain join:

1Ex: realm join --user=[domain user account] [domain name] 2realm join --user=linuxsso demo.example.com

Output:



VII) Check realm list output

1[root@linux network-scripts]# realm list 2demo.example.com 3 type: kerberos 4 realm-name: DEMO.EXAMPLE.COM 5 domain-name: demo.example.com 6 configured: kerberos-member 7 server-software: active-directory 8 client-software: sssd 9 required-package: oddjob 10 required-package: oddjob-mkhomedir 11 required-package: sssd 12 required-package: adcli 13 required-package: samba-common-tools 14 login-formats: %U@demo.example.com 15 login-policy: allow-realm-logins 16[root@linux network-scripts]#

and also check the Linux machine is visible in Domain controller.






VIII) Update /etc/sssd/sssd.conf with additional parameters

  • default_domain_suffix - Set this to the domain name if you do not want to have to type the full user account name when logging in. Instead of having to type linuxsso@demo.example.com always, you can just type linuxsso and the password. This helps a lot when you have a long domain name.

  • Change the fallback_homedir = /home/%u (default value would be /home/%u@%d ). We are chaning this because default setting would create the home dir as /home/linuxsso@demo.example.com which would not be user friendly . so changing /home/%u will create /home/linuxsso as home directory

 

Latest sshd.conf

1[root@linux home]# cat /etc/sssd/sssd.conf 2 3[sssd] 4domains = demo.example.com 5config_file_version = 2 6services = nss, pam 7ldap_referrals = false 8default_domain_suffix = demo.example.com 9 10 11[domain/demo.example.com] 12ad_domain = demo.example.com 13krb5_realm = DEMO.EXAMPLE.COM 14realmd_tags = manages-system joined-with-samba 15cache_credentials = True 16id_provider = ad 17krb5_store_password_if_offline = True 18default_shell = /bin/bash 19ldap_id_mapping = True 20use_fully_qualified_names = True 21fallback_homedir = /home/%u 22access_provider = ad 23[root@linux home]# 24

iX) Make sure you test the AD username resolution by running id command:

1[root@linux home]# id linuxsso 2uid=1242601103(linuxsso@demo.example.com) gid=1242600513(domain users@demo.example.com) groups=1242600513(domain users@demo.example.com) 3[root@linux home]#

X ) Verify that authentication for an Active Directory user is successful:

Note. Type the domain name in upper-case letters.

If everything was configured correctly, the ticket will be created.

1[root@linux home]# kinit linuxsso@DEMO.EXAMPLE.COM 2Password for linuxsso@DEMO.EXAMPLE.COM: 3[root@linux home]# echo $? 40 5[root@linux home]# klist 6Ticket cache: KEYRING:persistent:0:0 7Default principal: linuxsso@DEMO.EXAMPLE.COM 8 9Valid starting Expires Service principal 1006/05/2022 17:23:56 06/06/2022 03:23:56 krbtgt/DEMO.EXAMPLE.COM@DEMO.EXAMPLE.COM 11 renew until 06/12/2022 17:23:53 12

 XI) Now try to login to Linux machine with AD credentials





How to Compile Forms , Reports & Custom.pll in R12.2

How to Compile Custom.pll   cd $AU_TOP/resource  cp CUSTOM.plx CUSTOM.plx_bkup  cp CUSTOM.pll CUSTOM.pll_bkup  frmcmp_batch module=CUSTOM.pl...