Resend Failed/Sent Notifications Again in Oracle Apps R12
Resend Failed/Sent Notifications Again in Oracle Apps R12
Purge AWR snapshots in Oracle Database
Purge AWR snapshots in Oracle Database
Shell Script to find the Expiry Date of a Certificate using orapki
Shell Script to find the Expiry Date of a Certificate using orapki
Configure Datasource and JNDI for DB adapter
Configure Datasource and JNDI for DB adapter
In the post, I am sharing the steps on how
to create data source in WebLogic and configure the JNDI for the DB adapter.
To Complete this blog, you must have WebLogic and
oracle database up and running.
Below simple steps that show how to create Datasource.
1) Login into the WebLogic console http://funebs122:7001/console
2) Click on lock and edit.
3) Expand Services and click on Data sources
4) Click New -> Generic Data Source
5) Enter below information and click
Next
Name : UAT
JNDI Name : jdbc/uat
Database Type : Oracle
6) Select Database drive as per your need, let it be default as of now and click Next
7) Click Next
8) Enter the database details and click Next
9) Click on Test Configuration button and click Next
10) Select the DefaultServer as target and click Finish
11) Click on Activate Changes button
You are done with data source creation.
JNDI in DBAdapter
12) Click on the Lock & Edit button and Click on Deployments from the left pane
13) Click on DBAdapter
14) Click on Configuration Tab and Outbound Connection Pools and then click New
15) Check the javax.resource.cci.ConnectionFactory radio
button and click Next
16) Enter JNDI Name and click Finish
17) Expand the connection factory and click on the eis/db/uat JNDI
18) Enter the property value XADatasourceName as jdbc/uat that we have created in the 5th step. press Enter after putting the value and then click Save button.
19) Update the DBAdapter now. Click Lock & Edit button -> Deploments -> Check the DBAdapter -> Update
20) Click Next -> Finish and Activate Changes
You are done with Datasource creation and DBAdapter JDNI configuration.
How to check SSL certificate expiration date for certificate in wallet using orapki
How to check SSL certificate expiration date for the certificate in wallet using orapki
Error while launching forms on an SSL/HTTPS Enabled Oracle Apps
Error while launching forms on an SSL/HTTPS Enabled Oracle Apps
Upgrading an Oracle EBS 12.2.10 Database from 12c to 19c
Upgrading an Oracle EBS 12.2.10 Database from 12c to 19c
- When upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will be converted to a multitenant architecture, which will consist of a Container Database (CDB) with a single Pluggable Database. Only multitenant architecture database are supported for Oracle E-Business Suite with Oracle Database 19c.
- During the upgrade, you will also perform steps to migrate directories defined for PL/SQL File I/O to database directory objects. This requirement is due to the desupport in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.
- Oracle Database Release Update 19.3 as well as Release Update 19.5 and later are certified.
- If the products implemented are not supporting 19c then please don't upgrade DB.
[oracle@funebs122 19cDB_Upgrade_EBS]$ sqlplus '/as sysdba' SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 10 14:08:44 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@FUAT >spool hcheck.log SYS@FUAT >@hcheck.sql
PL/SQL procedure successfully completed. Statement processed. Complete output is in trace file: /oraapps122/DATABASE/FUAT/12.1.0/admin/FUAT_funebs122/diag/rdbms/fuat/FUAT/trace/FUAT_ora_23704_HCHECK.trc
FAIL: Requires resolution on priority.
WARN: Good to resolve.
PASS: No problem.
.- StmtAuditOnCommit ... 1201000200 <= *All Rel* 04/10 14:16:09 WARN HCKW-0013: option# in STMT_AUDIT_OPTION_MAP(ON COMMIT REFRESH) (Doc ID 1361021.1) -- Please run the following: SQL> update STMT_AUDIT_OPTION_MAP set option#=234 where name ='ON COMMIT REFRESH';
[applmgr@funebs122 19cDB_Upgrade_EBS]$ adop phase=prepare,apply,finalize,cutover,cleanup mtrestart=no patchtop=/media/sf_Downloads/19cDB_Upgrade_EBS patches=31088182,26521736,31349591,31800803,28732161 Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
The prepare phase completed successfully. The apply phase completed successfully. The finalize phase completed successfully. The cutover phase completed successfully. The cleanup phase completed successfully. adop exiting with status = 0 (Success)
SYS@FUAT >show parameter SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean FALSE
[oracle@funebs122 dbs]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ echo $ORACLE_HOME /oraapps122/DATABASE/FUAT/12.1.0 [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/12.1.0 [oracle@funebs122 appsutil]$ export ORACLE_SID=FUAT [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil/bin [oracle@funebs122 bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \ > -outdir=$ORACLE_HOME/appsutil/log -appsuser=apps \ > -dbsid=FUAT -skipdbshutdown=yes Enter the APPS Password:
[oracle@funebs122 19.3]$ mkdir -p /oraapps122/DATABASE/FUAT/19c
[oracle@funebs122 ~]$ cat 19cdb.env export ORACLE_HOME=/oraapps122/DATABASE/FUAT/19c export ORACLE_SID=funcdb export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin export ORACLE_BASE=/oraapps122/DATABASE export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib export PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1
[oracle@funebs122 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 11 00:25:01 2021 Version 19.3.0.0.0
[oracle@funebs122 ~]$ cat 19cdb.env
export ORACLE_HOME=/oraapps122/DATABASE/FUAT/19c
export ORACLE_SID=funcdb
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin
export ORACLE_BASE=/oraapps122/DATABASE
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1
export ORA_NLS10=/oraapps122/DATABASE/FUAT/19c/nls/data/9idata
[oracle@funebs122 19c]$ pwd /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 19c]$ cp /oraapps122/APPS/FUAT/fs2/inst/apps/FUAT_funebs122/admin/out/appsutil.zip . [oracle@funebs122 19c]$ ls addnode data install network owm root.sh.old.1 srvm apex dbjava instantclient nls p6880880_190000_Linux-x86-64.zip root.sh.old.2 suptools appsutil.zip dbs inventory odbc perl root.sh.old.3 ucp assistants deinstall javavm olap plsql runInstaller usm bin demo jdbc OPatch precomp schagent.conf utl cfgtoollogs diagnostics jdk opmn QOpatch sdk wwg client dmu jlib oracore R sdo xdk clone drdaas ldap oraInst.loc racg slax crs dv lib ord rdbms sqldeveloper css env.ora LINUX.X64_193000_db_home.zip ords relnotes sqlj ctx has md oss root.sh sqlpatch cv hs mgw oui root.sh.old sqlplus [oracle@funebs122 19c]$ unzip -o appsutil.zip
[oracle@funebs122 19c]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ cp -r $ORACLE_HOME/jdk/jre . [oracle@funebs122 appsutil]$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext
- Run the Database Configuration Assistant (DBCA) to create the container database (CDB).
- When prompted, click on the "Create Database", "Advanced Configuration", and "General Purpose or Transaction Processing" options.
- In the Specify Database Identification screen, check to create an empty container database (CDB) without a PDB.
- Set the Global Database Name, the SID to the new CDB SID (maximum of 8 characters)(funcdb), and check the "Use Local Undo tablespace for PDBs" checkbox. The CDB SID has to be different from the current ORACLE_SID, which will be the PDB SID.
- In the "Network Configuration" section, do not create a listener. In the "Specify Configuration Options" section, set the SGA and PGA sizes to 2G and 1G respectively.
- Click on the Character Sets tab and choose the Character Set and National Character Set to be the same as in the source database.
- In the "Select Database Creation Option" section, click on the "Customise Storage Locations" button. Set the size of the redo log files to be the same as in the source database. Other options can be configured as appropriate.
[oracle@funebs122 ~]$ . 19cdb.env [oracle@funebs122 ~]$ export ORACLE_SID=funcdb [oracle@funebs122 ~]$ $ORACLE_HOME/OPatch/datapatch SQL Patching tool version 19.10.0.0.0 Production on Sun Apr 11 10:47:07 2021 Copyright (c) 2012, 2020, Oracle. All rights reserved. Log file for this invocation: /oraapps122/DATABASE/cfgtoollogs/sqlpatch/sqlpatch_25829_2021_04_11_10_47_07/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) Bootstrapping registry and package to current versions...done Determining current state...done Current state of interim SQL patches: Interim patch 31424070 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Binary registry: Installed PDB CDB$ROOT: Applied successfully on 11-APR-21 02.21.25.128967 AM PDB PDB$SEED: Applied successfully on 11-APR-21 03.27.11.083950 AM Interim patch 32067171 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)): Binary registry: Installed PDB CDB$ROOT: Applied successfully on 11-APR-21 02.21.21.996398 AM PDB PDB$SEED: Applied successfully on 11-APR-21 03.27.07.902371 AM Current state of release update SQL patches: Binary registry: 19.10.0.0.0 Release_Update 210108185017: Installed PDB CDB$ROOT: Applied 19.10.0.0.0 Release_Update 210108185017 successfully on 11-APR-21 02.21.21.977155 AM PDB PDB$SEED: Applied 19.10.0.0.0 Release_Update 210108185017 successfully on 11-APR-21 03.27.07.888208 AM Adding patches to installation queue and performing prereq checks...done Installation queue: For the following PDBs: CDB$ROOT PDB$SEED No interim patches need to be rolled back No release update patches need to be installed No interim patches need to be applied SQL Patching tool complete on Sun Apr 11 10:48:32 2021
[oracle@funebs122 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 11 10:49:59 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0 SQL> SQL> @$ORACLE_HOME/rdbms/admin/catmgd.sql
[oracle@funebs122 ~]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil/bin [oracle@funebs122 bin]$ perl txkGenCDBTnsAdmin.pl -dboraclehome=$ORACLE_HOME \ > -cdbname=funcdb -cdbsid=funcdb -dbport=1521 \ > -outdir=$ORACLE_HOME/appsutil/log
[oracle@funebs122 ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/oraapps122/DATABASE/FUAT/12.1.0/appsutil/FUAT_funebs122.xml -oraclehome=/oraapps122/DATABASE/FUAT/12.1.0 -outdir=/tmp/ xkCfgUtlfileDir -upgradedhome=/oraapps122/DATABASE/FUAT/19c -mode=getUtlFileDir Enter the APPS Password: Script Name : txkCfgUtlfileDir.pl Script Version : 120.0.12020000.15 Started : Mon Apr 12 06:36:59 IST 2021 Log File : /tmp//TXK_UTIL_DIR_Mon_Apr_12_06_36_41_2021/txkCfgUtlfileDir.log Context file: /oraapps122/DATABASE/FUAT/12.1.0/appsutil/FUAT_funebs122.xml exists. ============================================================================== Successfully generated the below file with UTL_FILE_DIR content: /oraapps122/DATABASE/FUAT/12.1.0/dbs/FUAT_utlfiledir.txt ============================================================================== Completed : Mon Apr 12 06:37:02 IST 2021 Successfully Completed the script ERRORCODE = 0 ERRORCODE_END
[oracle@funebs122 ~]$ lsnrctl stop FUAT LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-APR-2021 06:44:19 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=funebs122.lab)(PORT=1551))) The command completed successfully
SYS@FUAT >alter system set local_listener=''; System altered.
[oracle@funebs122 ~]$ $ORACLE_HOME/jdk/bin/java -jar /oraapps122/DATABASE/FUAT/19c/rdbms/admin/preupgrade.jar TERMINAL TEXT Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-04-12T06:51:55
================== PREUPGRADE SUMMARY ================== /oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/preupgrade.log /oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/preupgrade_fixups.sql /oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2021-04-12T06:51:56
[oracle@funebs122 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 12 06:57:44 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@FUAT >@/oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/preupgrade_fixups.sql
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> @/oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/postupgrade_fixups.sql
SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false; System altered.
SQL> alter trigger SYSTEM.EBS_LOGON compile; Trigger altered.
SQL> alter system set compatible='19.0.0' scope=spfile; System altered.
Restart DB
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2147481648 bytes Fixed Size 8898608 bytes Variable Size 520093696 bytes Database Buffers 1593835520 bytes Redo Buffers 24653824 bytes Database mounted. Database opened. SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 19.0.0 noncdb_compatible boolean FALSE
[oracle@funebs122 ~]$ $ORACLE_HOME/OPatch/datapatch
SQL> @?/rdbms/admin/dbmsxdbschmig.sql
SQL> @?/rdbms/admin/prvtxdbschmig.plb
[oracle@funebs122 admin]$ sqlplus '/as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 12 18:33:09 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0 SQL> @adgrants.sql apps
[oracle@funebs122 tmp]$ sqlplus apps/apps @adctxprv.sql manager CTXSYS SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 12 18:38:22 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Last Successful login time: Mon Apr 12 2021 18:38:14 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0 Connecting to SYSTEM Connected. PL/SQL procedure successfully completed. Commit complete.
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 17
SQL> grant text datastore access to public; Grant succeeded.
SQL> alter system enable restricted session; System altered. SQL> @adstats.sql Connected.
SQL> alter system disable restricted session; System altered. SQL> exit
[oracle@funebs122 tmp]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/oraapps122/DATABASE/FUAT/19c Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ export ORACLE_SID=FUAT [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil/bin [oracle@funebs122 bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/oraapps122/DATABASE/FUAT/19c \ > -outdir=/oraapps122/DATABASE/FUAT/19c/appsutil/log -appsuser=apps -dbsid=FUAT Enter the APPS Password:
[oracle@funebs122 bin]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ export ORACLE_SID=funcdb [oracle@funebs122 appsutil]$ sqlplus "/ as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 12 19:18:08 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 2147482336 bytes Fixed Size 9136864 bytes Variable Size 570425344 bytes Database Buffers 1560281088 bytes Redo Buffers 7639040 bytes SQL> @$ORACLE_HOME/dbs/FUAT_initparam.sql
SQL> alter system set LOCAL_LISTENER="funebs122:1521" scope=both; System altered. SQL> shutdown; ORA-01507: database not mounted ORACLE instance shut down.
SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 2147482136 bytes Fixed Size 9136664 bytes Variable Size 436207616 bytes Database Buffers 1677721600 bytes Redo Buffers 24416256 bytes Database mounted. Database opened.
[oracle@funebs122 dbs]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ export ORACLE_SID=funcdb [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil/bin [oracle@funebs122 bin]$ perl txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME \ > -outdir=$ORACLE_HOME/appsutil/log -cdbsid=funcdb \ > -pdbsid=FUAT -servicetype=onpremise
[oracle@funebs122 bin]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ cd $ORACLE_HOME/appsutil/bin [oracle@funebs122 bin]$ perl txkCreatePDB.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log \ > -cdbsid=funcdb -pdbsid=FUAT -dbuniquename=funcdb -servicetype=onpremise
DATA_TOP_1 =========== Enter the non-CDB data top [/oraapps122/DATABASE/FUAT/data]: Enter the corresponding PDB data top [/oraapps122/DATABASE/FUAT/data]:
[oracle@funebs122 bin]$ cd $ORACLE_HOME/appsutil [oracle@funebs122 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME Oracle Home being passed: /oraapps122/DATABASE/FUAT/19c [oracle@funebs122 appsutil]$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \ > -outdir=$ORACLE_HOME/appsutil/log -cdbsid=funcdb -pdbsid=FUAT \ > -appsuser=apps -dbport=1521 -servicetype=onpremise Enter the APPS Password: Enter the CDB SYSTEM Password: