Soumya's Database Blog : January 2021

How to check AD and TXK code levels in EBS

We can use following query to get AD and TXK code level in EBS environment

SQL>col ABBREVIATION for a20

SQL>set lines 300

SQL>col NAME for a40

SQL>col CODELEVEL for a20

SQL> SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

ABBREVIATION NAME CODELEVEL

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

ad Applications DBA C.10

txk Oracle Applications Technology Stack C.10

Shell script to delete applied archive log from standby database

This script will run on standby database.

It will basically check for applied archives on standby database and it will keep last 100's number of archives present and will delete rest of the archives that have been already applied. I have personally tested this script on my environment which is running on RHEL 7 and Database Ver 11.2.0.4.

Warning:- Before running the script directly into Production Environment , please test it on a non production environment.

#Schedule this script on standby database server.

vi /home/oracle/archive_del.sh

#!/bin/bash

ARCHLOG_DIR=/arch/prodtest/archivelog

LogNo=`tail -3000 /db/prodtest/db/tech_st/11.2.0.4/admin/PRODTEST_ccuine103/diag/rdbms/prdtstdr/PRODTEST/trace/alert_PRODTEST.log | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 2 | tail -1 `

echo "Oracle applied LogNo is $LogNo"

let SecLogNo=${LogNo}-100

echo "Archivelog is present from number : $SecLogNo"

echo "*****************************"

cd $ARCHLOG_DIR

for i in `ls *.arc`

Newi=`echo $i | cut -d "_" -f 2`

if [ "$Newi" -lt "$SecLogNo" ]; then

echo "$i to be deleted..."

rm -r $i

done

Schedule to run for every 4 hours

[oracle@ccuine103 ~]$ crontab -l

0 */4 * * * /home/oracle/archive_del.sh

Another simple script that deletes archivelog from standby database

cat archive_delete.sh

. /db/prod/proddb/tech_st/19.3.0/PRODCDB_ccuine103.env

rman nocatalog log=/home/oracle/archive_del.log << EOF

connect target /

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-1';

CROSSCHECK ARCHIVELOG ALL;

DELETE noprompt EXPIRED ARCHIVELOG ALL;

EOF

Steps to change password for APPS in EBS R 12.2

Environment Details


Hostname	ebstest.wizer.com
Database Version	12.1.0.2
E Business Suite Version	R 12.2
OS	OEL 7.7
R12 install base	/u01/oracle/VIS

Important:- Before changing the password for APPS, APPLSYS and APPS_NE the following tables should be backed up .

1. FND_USER

2. FND_ORACLE_USERID

Step 1.

Take backup

SQL> create table FND_USER_291220 as select * from FND_USER ;

Table created.

SQL> create table FND_ORACLE_USERID_291220 as select * from FND_ORACLE_USERID ;

Table created.

Step 2. Shutdown apps tier

Invoke application environment file of fs1

[oracle@ebstest VIS]$ cd /u01/oracle/VIS/fs1/EBSapps/appl

[oracle@ebstest VIS]$ . APPSVIS_ebstest.env

[oracle@ebstest sql]$ cd $ADMIN_SCRIPTS_HOME

[oracle@ebstest scripts]$ adstpall.sh

Step 3. Change APPLSYS password. We can use two methods for changing password.

1. FNDCPASS

2. AFPASSWD

Here I will be using AFPASSWD utility to change the password.

syntax:- AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] -s <APPLSYS>

[oracle@ebstest appl]$ AFPASSWD -c apps@VIS -s APPLSYS

Enter the ORACLE password of Application Object Library 'APPSUSER':

Connected successfully to APPS.

Enter the password for your 'SYSTEM' ORACLE schema:

Connected successfully to SYSTEM.

Log file: AFPWD_VIS_239350.log

Enter new password for user:

Verify new password for user:

AFPASSWD completed successfully.

Step 4. Run autoconfig on AppsTier with newly changed apps password

[oracle@ebstest VIS]$ cd /u01/oracle/VIS/fs1/EBSapps/appl

[oracle@ebstest VIS]$ . APPSVIS_ebstest.env

[oracle@ebstest scripts]$ cd $ADMIN_SCRIPTS_HOME

[oracle@ebstest scripts]$ adautocfg.sh

……

AutoConfig completed successfully.

Step 5. Now start weblogic admin server using following script

[oracle@ebstest scripts]$ pwd

/u01/oracle/VIS/fs1/inst/apps/VIS_ebstest/admin/scripts

[oracle@ebstest scripts]$ sh adadminsrvctl.sh start

You are running adadminsrvctl.sh version 120.10.12020000.2

Enter the WebLogic Admin password:

Enter the APPS Schema password:

Step 6. Confirm the admin server running status

[oracle@ebstest scripts]$ sh adadminsrvctl.sh status

You are running adadminsrvctl.sh version 120.10.12020000.2

Enter the WebLogic Admin password:

Enter the APPS Schema password:

The AdminServer is running

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /u01/oracle/VIS/fs1/inst/apps/VIS_ebstest/logs/appl/admin/log/adadminsrvctl.txt for more information ...

Step 7 . Now login to weblogic console from browser

Click on Lock and Edit button

Now under Domain structure, click on services - > Data Sources

Now click on “EBSDataSource”

Now on the page of “Settings of EBSDataSoure” click on connection pool and provide the changed apps password and save it.

Click on Activate changes.

Step 8 . Start the application services.

[oracle@ebstest appl]$ pwd

/u01/oracle/VIS/fs1/EBSapps/appl

[oracle@ebstest appl]$ adstrtal.sh

You are running adstrtal.sh version 120.24.12020000.6

Enter the APPS username: apps

Enter the APPS password:

Enter the WebLogic Server password:

….

All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

Step 9. Verify the WLS Datastore changes by doing followings

Ø Log in to WLS Administration Console.

Ø In the Domain Structure tree, expand Services, then select Data Sources.

Ø On the "Summary of JDBC Data Sources" page, select EBSDataSource.

OIn the "Settings for EBSDataSource" page, select Monitoring > Testing.

Ø Select "oacore_server1".

Ø Click Test DataSource

Ø Look for the message "Test of EBSDataSource on server oacore_server1 was successful".

So this concludes the process of changing password for APPS in EBS R12.2

How to get weblogic url in EBS R 12.2

Sometimes we land in a situation where we don’t have the weblogic url handly. So in this post I will show to find it just by extracting some parameters from the application context file

Go to application tier and invoke application run file system environment file

[oracle@ebstest ]$cd /u01/oracle/VIS/fs1/EBSapps/appl

[oracle@ebstest appl]$ . APPSVIS_ebstest.env

[oracle@ebstest appl]$ grep -i "wls_admin" $CONTEXT_FILE

<wls_admin_host oa_var="s_wls_admin_host">ebstest</wls_admin_host>

<wls_admin_domain oa_var="s_wls_admin_domain">wizer.com</wls_admin_domain>

<wls_admin_user oa_var="s_wls_admin_user">weblogic</wls_admin_user>

<wls_adminport oa_var="s_wls_adminport" oa_type="PORT" base="7001" step="1" range="-1" label="WLS Admin Server Port">7001< wls_adminport>

So from the above output the url should look like this

http://ebstest.wizer.com:7001/console

We can also find out the url using command

http://ebstest.wizer.com:7001/console

Sample listener.ora and tnsnames.ora entries for CDB and PDB for oracle 19c

Environment details:-

Hostname	gg1.soumya.com
Server IP	192.168.0.110
DB Version	19.3.0
Container DB SID	TESTCDB
PDB1 SID	TESTPDB1
PDB2 SID	TESTPDB2
ORACLE_HOME	/u01/app/oracle/product/19.0.0/dbhome_1

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.soumya.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TESTDB)

(SID_NAME = TESTDB)

)

(SID_DESC =

(GLOBAL_DBNAME = TESTPDB1)

(SID_NAME = TESTPDB1)

)

(SID_DESC =

(GLOBAL_DBNAME = TESTPDB2)

(SID_NAME = TESTPDB2)

)

vi $ORACLE_HOME/network/admin/tnsnames.ora

TESTCDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.soumya.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TESTCDB)

)

TESTPDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.soumya.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TESTPDB1)

)

TESTPDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.soumya.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TESTPDB2)

)

Verify Listener status

[oracle@gg1 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-DEC-2020 20:19:14

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gg1.soumya.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 26-DEC-2020 19:57:29

Uptime 0 days 0 hr. 21 min. 45 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/gg1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gg1.soumya.com)(PORT=1521)))

Services Summary...

Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).