DBA's Journey
This blog is to share my experiences and learnings on Oracle Database,EBS,OCI & Ansible.
Tuesday, July 19, 2022
How to Compile Forms , Reports & Custom.pll in R12.2
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
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 typelinuxsso
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...
-
Count: Before Terraform 0.12.6, the only way to create multiple instances of the same resource was to use a count parameter. One o...
-
In eBusiness Suite (EBS) 12.2.x you cannot query the AD_BUGS table to check if patches have been applied.The AD_BUGS table may have entries...
-
We used to refresh our DEV database from production.so we need to preserve the DEV dblinks before we start refresh . we need to restore aft...