Resend Failed/Sent Notifications Again in Oracle Apps R12
Resend Failed/Sent Notifications Again in Oracle Apps R12
In this post, I am sharing how to resend the notification mail to users if it fails or user have not received.
1) Check if the Notification mailer/Agent listener is running
select (case when (select count(1)
from apps.fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC') > 0
then 'Notification Mailer Running'
else 'Notification Mailer Not Running'
end) "Status"
from dual
2) Run the below query to get the exact number based on dates
select *
from wf_notifications
where 1=1
and trunc(begin_date) <= trunc(sysdate-4)
and mail_status = 'FAILED'
and status = 'OPEN'
order by trunc(begin_date)
Get details based on the recipient roles
select * from wf_notifications where recipient_role='HIMANSHU';
3) Run the concurrent program 'Resend Failed/Error Workflow Notifications' from 'System Administrator' responsibility.
> All parameters are optional, please select the parameter values accordingly so that only required notifications get re-processed.
Mail Status: Error/Failed
Message Type: Workflow Item Types
Recipient Role: List of workflow roles
Notifications sent on or after: Date
Notifications sent on or before: Date
4) For resending the Sent notifications, please 'Rewind'
>> Goto Workflow > Status Monitor
>> Search with 'Type Internal Name' and 'Item Key'
>> You can get it from wf_notifications table
>> Click on 'Activity History' > Select the Activity with 'Notified' Status and 'Rewind'
>> Again select the same activity and 'Apply'
>> You will get a message for Rewind.
Check the wf_notifications table for the same 'message_type' and 'item_type', there should be two entries with the previous one 'Closed'.
5) If notification is getting failed, again and again, check the user preferences for that recipient.
Select *
from fnd_user_preferences
where user_name = <> ;
You can also debug the notification with the notification id
Run the @$FND_TOP/sql/wfmlrdbg.sql
> Parameter: Enter Value for 1: <Notification_ID >
It will generate the debug file in the same directory.
Purge AWR snapshots in Oracle Database
Purge AWR snapshots in Oracle Database
Display retention period:
select dbms_stats.get_stats_history_retention from dual;
SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;
More Details via below query:
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) “Snapshot Interval”,
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) “Retention Interval”
from dba_hist_wr_control;
AWR Snapshot Purging Policy
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
(retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL);
retention = snapshot interval (1 hr ) X 24 X X 7 days
To retain 1 weeks data set retention to 10080
- Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => ‘DEFAULT’);
- Retain data for 28 days (4 weeks)
exec dbms_workload_repository.modify_snapshot_settings(retention => 40320, interval => 60, topnsql => ‘DEFAULT’);
By using the MODIFY_SNAPSHOT_SETTINGS procedure, you can control:
- How much AWR information is retained, by changing the RETENTION minutes parameter.
The default is seven days (10,080 minutes); the minimum is one day.
- How often the system automatically generates snapshots, by changing the INTERVAL
- The number of Top SQL statements for which to capture performance data.
Check the list of AWR Snapshots present in DB
set line 999 pages 999
col begin_interval_time for a25
col end_interval_time for a25
SELECT snap_id, begin_interval_time, end_interval_time
FROM sys.wrm$_snapshot ORDER BY snap_id;
Manually purge after x days and before
exec dbms_stats_purge_stats(SYSDATE-30);
Drop AWR snapshots in range
EXECUTE dbms_workload_repository.drop_snapshot_range(low_snap_id =>22465 , high_snap_id =>22470);
For information Generate a report of awrinfo.sql located in the $ORACLE_HOMEW/rdbms/admin folder.
Shell Script to find the Expiry Date of a Certificate using orapki
Shell Script to find the Expiry Date of a Certificate using orapki
I am sharing the script to find the expiry date of the certificate and send mail 30 days before expiration.
My Certificate: Server_certificate.cer
Script:
. ~/.bash_profile
cd $INST_TOP/certs/Apache
$ORACLE_HOME/bin/orapki cert display -cert Server_certificate.cer >/tmp/certificate_info
$ORACLE_HOME/bin/orapki cert display -cert Server_certificate.cer |grep -i Valid > /tmp/cert_valid_txt
EXP_DATE=`cat /tmp/cert_valid_txt|cut -f2- -d':' |awk '$1=$1'`
TODAY_DATE=`date`
no_of_days_left=$(( ($(date -d "$EXP_DATE" +%s) - $(date -d "$TODAY_DATE" +%s) )/(60*60*24) ))
if [ $no_of_days_left -lt 30 ]
then
echo "-----------------------------------------------------------------------" >> /tmp/certificate_info
echo "Certificate is Going to Expire, Please get a new certificate and deploy" >> /tmp/certificate_info
mailx -s "Critical: `hostname -a` EBS Certificate is Going to Expire in 30 days" himanshu@funoracleapps.com< /tmp/certificate_info
fi
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
In this post I am going to share how to find the certificate expiry information from an oracle wallet.
Locate the location where the wallet files are present
cd $INST_TOP/certs/Apache
ls
cwallet.sso
ewallet.p12
Find DN
applmgr@funebs122:/u01/oracle/apps/inst/certs/Apache> orapki wallet display -wallet ewallet.p12
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=xyz EV RSA CA 2018,OU=www.funebs122.lab,O=xxx Inc,C=US <====dn
Export ewallet.p12 to the certificate
applmgr@funebs122:/u01/oracle/apps/inst/certs/Apache> orapki wallet export -wallet ewallet.p12 -dn "CN=xyz EV RSA CA 2018,OU=www.funebs122.lab,O=xxx Inc,C=US" -cert certificate_expiry
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
applmgr@funebs122:/u01/oracle/apps/inst/certs/Apache> ls
certificate_expiry <===exported certificate
Check certificate validity
applmgr@funebs122:/u01/oracle/apps/inst/certs/Apache> orapki cert display -cert /u01/oracle/apps/inst/certs/Apache/certificate_expiry -summary
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Subject: CN=xyz EV RSA CA 2018,OU=www.funebs122.lab,O=xxx Inc,C=US
Issuer: CN=xyz EV RSA CA 2018,OU=www.funebs122.lab,O=xxx Inc,C=US
Valid Until: Sat Nov 06 15:22:57 AST 2023 <=== Certificate validity
Error while launching forms on an SSL/HTTPS Enabled Oracle Apps
Error while launching forms on an SSL/HTTPS Enabled Oracle Apps
'The connection to this website is untrusted'
URL = https://company.com:443
The certificate is not valid
Solution:
1. From the client, access: Control Panel->Java->Security->Certificates
2. User tab->Change drop down to 'Secure Site'
3. Import the server SSL certificate. Not the code signing certificate, but the server user ssl certificate.
Upgrading an Oracle EBS 12.2.10 Database from 12c to 19c
Upgrading an Oracle EBS 12.2.10 Database from 12c to 19c
Oracle EBS 12.2 On-Premises databases are now certified with the CDB architecture (multitenant architecture). EBS database will run as a single PDB.In this post I am going to share how to upgrade and EBS database from 12c to 19c.
Current EBS Environnment:
Apps : 12.2.10
DB: 12.1.0.2
Server : OEL 7.9
Oracle SID: FUAT
AD and TXK : Delta patch 12 already Applied
Important Information Regarding the Upgrade to Oracle Database 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.
Steps which I performed:
1) As per oracle Document 136697.1, hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c, to run hcheck.sql, which looks for some known common Data Dictionary problems.
run connected as SYS schema
[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
View the spool output
Various status seen in reports can be as below.
CRITICAL: Is of highest risk and requires urgent fix.
FAIL: Requires resolution on priority.
WARN: Good to resolve.
PASS: No problem.
FAIL: Requires resolution on priority.
WARN: Good to resolve.
PASS: No problem.
My execution showed me one warning.
.- 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';
So I executed solution as mentioned in spool. Re-run script to make sure all is passed.
2) Apply required patches on EBS as required based on the version and AD/TXK delta versions.
As per my environment I am required to apply below patches.
Patch 26521736 - 19c interoperability patch for Release 12.2
Patch 31088182
Patch 31349591
Patch 31800803
Patch 28732161 - Apply on Release 12.2.6 or later versions.
Please review readme of all the patches.
Apply patches.(Note all patches I have kept at my common share area)
[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:
Once Patches applied we can see as below
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)
Patch 31800803 Post steps:
1. On Application RUN file system
Source the environment variables for Oracle Applications
. EBSapps.env run
Run AutoConfig on the RUN file system.
Execute admkappsutil.pl utility to create the file appsutil.zip
perl $AD_TOP/bin/admkappsutil.pl
This will create appsutil.zip in <INST_TOP>/admin/out
2. On the Database Tier (as the ORACLE user):
Source the environment variables for RDBMS ORACLE_HOME
cd <RDBMS ORACLE_HOME>
./<RDBMS ORACLE_HOME>/<CONTEXT_NAME>.env
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>
cd <ORACLE_HOME>
unzip -o appsutil.zip
Run AutoConfig on the <RDBMS ORACLE_HOME>
3) Make sure below parameter is false in DB.
SYS@FUAT >show parameter SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean FALSE
4) Create the initialization parameter setup files
Run the following commands to create the $ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<ORACLE_SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \
-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> \
-dbsid=<ORACLE_SID> -skipdbshutdown=yes
[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:
5) Install 19c Database Oracle Home
Refer my previous post on installing the 19c DB Home and pre-requisites. This will install 19.3 DB version.
Follow same, and make sure the 19c is installed in different path.
Only use option to setup the software only.
[oracle@funebs122 19.3]$ mkdir -p /oraapps122/DATABASE/FUAT/19c
6) Create the 19c Environment File
Once you have the 19c Oracle Home installed, create a simple environment to set the environment easily with parameter as below. "funcdb" is the CDB which I am going to create now.
The ORACLE_BASE environment variable is set accordingly.
The ORACLE_HOME environment variable points to the new 19c Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located: $ORACLE_HOME/perl/lib/<perl version> and $ORACLE_HOME/perl/lib/site_perl/<perl version>
export ORA_NLS10=/u02/oratest/app/oracle/19.3/nls/data/9idata
[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
7) Apply latest database patches on the 19c Home
Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes from section 3.3.
These are important to be on latest to avoid any issues.
Based on my analysis, I will be applying below patches. DB binary will be updated to 19.10 from 19.3
32218454-DATABASE RELEASE UPDATE 19.10.0.0.0 (Patch)
32067171-OJVM RELEASE UPDATE 19.10.0.0.0 (Patch)
29867728
31405300
31424070
[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
Open new terminal and source the 19c environment file.
Unzip all mentioned above patches
Donwload latest Opatch Utility
Go to each patch unzipped directory and apply them
cd 32218454
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
cd 32067171
opatch apply
cd 29867728
opatch apply
cd 31405300
opatch apply
cd 31424070
opatch apply
Do not perform any post patch installation steps as they will be done after upgrade.
8)Create the nls/data/9idata directory
Source 19c env
On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/nls/data/9idata directory.
unset PERL5LIB
$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 19c Oracle home
[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
9) Copy appsutil.zip from Application Tier to Database 19c home
perl $AD_TOP/bin/admkappsutil.pl
Copy appsutil.zip file to 19c Oracle Home and unzip
[oracle@funebs122 ~]$ cd $ORACLE_HOME
[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
10) Install JRE 8
Source 19c environment
To install JRE 8 on the appsutil directory, copy the $ORACLE_HOME/jdk/jre directory to $ORACLE_HOME/appsutil/jre. Run the following commands:
[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
11) Create the CDB
On the database server node Source 19c environment and run below
- 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.
12)Run datapatch on the CDB
Use the following commands to load any necessary patches on the CDB.
Source 19 environment
[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
13)Create the CDB MGDSYS schema
Source 19c Environment
Use SQL*Plus to connect to the CDB as SYSDBA and run the
export ORACLE_SID=funcdb
[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
This creates the new MGDSYS schema on the CDB.
14) Create the CDB TNS files
On the database server node, run the following perl script to generate the required TNS files. Note that this script does not create a listener.
Source 19c Environment file
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkGenCDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> \
-cdbname=<CDB SID> -cdbsid=<CDB SID> -dbport=<Database port> \
-outdir=<ORACLE_HOME>/appsutil/log
[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
15) Shut down the CDB
Source 19c environment
Use SQL*Plus to connect to the CDB as SYSDBA and use the following command to shut down the database:
$ sqlplus "/ as sysdba"
SQL> shutdown;
16) On Application Tier
Complete patching cycle and remove adop created editions
This will clean up the editions created by previous adop patching cycles. These commands also complete any open patching cycle.
On the current run file system:
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
Source the new Environment
On the new run file system:
$ adop phase=cleanup cleanup_mode=full
17)Configure UTL_FILE_DIR
Oracle EBS stores the PLSQL IO files in Database Node /usr/tmp usually in all versions of EBS starting from 11i till R12.2. For this, EBS uses UTL_FILE_DIR initialization parameter. However, from 18c, UTL_FILE_DIR initialization parameter has been desupported. Hence for 19c, Oracle for EBS have introduced new apps.v$parameter and apps.v$parameter2 views in the APPS schema that provide a supplemental UTL_FILE_DIR parameter. You need to follow the instructions given in Section 3.1.1 of :
Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)
Source 12c DB environment
[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
18) Shut down the application tier server processes
On each application tier server node, shut down all server processes or services. The applications will be unavailable to users until all remaining tasks in this section are completed.
19) Drop SYS.ENABLED$INDEXES
Source 12c DB environment
If the SYS.ENABLED$INDEXES table exists, connect to the database as SYSDBA, and run the following command to drop the table:
$ sqlplus "/ as sysdba"
SQL> drop table sys.enabled$indexes;
20) Remove the MGDSYS schema(Conditional)
If you are upgrading from a database version prior to Oracle 12c, log on to the old database server node, use SQL*Plus to connect to the database as SYSDBA, and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.
$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql
21) Shut down the database listener
Source 12c Environment
On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home.
[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
Note: To ensure that the database does not inadvertently point to a non-existent listener during the upgrade, verify that you do not have the LOCAL_LISTENER initialization parameter set.
SYS@FUAT >alter system set local_listener=''; System altered.
22) Database Pre-Upgrade Steps
Source 12c Environment
Run the Pre-upgrade Tool:
Connect to the 12c Database and run the command as below:
[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
Based on recommendation perform the action to fix pre-upgrade issues.
Also, Execute the pre-upgrade fixup script.
[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
23) Upgrade the database instance
Source 19c Enviornment
Invoke DBUA using the following command. The keep events parameter allows events in the preupgraded database to be enabled during the upgrade.
Make sure 12c db entry is present in /etc/oratab
$ORACLE_HOME/bin/dbua -keepEvents
Follow as per the screen below. Nore I have already fixed the pre-upgrade recommendations in earlier steps, so will ignore them in this dbua screen.
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
24) Run the Post Upgrade Script generated during the Pre-Upgrade Steps.
Sorce 19c Environment
export ORACLE_SID=FUAT
SQL> @/oraapps122/DATABASE/FUAT/12.1.0/cfgtoollogs/FUAT/preupgrade/postupgrade_fixups.sql
25) Post-Upgrade Tasks
Source 19c Environment
There are few important post-upgrade miscellaneous tasks that need to be performed.
If you previously had the SEC_CASE_SENSITIVE_LOGON initialization parameter set to FALSE, re-enable the parameter.
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
26) Run DataPatch
Source 19c Environment
[oracle@funebs122 ~]$ $ORACLE_HOME/OPatch/datapatch
27) Connect to sysdba and run below
Source 19c Environment
SQL> @?/rdbms/admin/dbmsxdbschmig.sql
SQL> @?/rdbms/admin/prvtxdbschmig.plb
28) Run ADgrants
Source 19c Environment
Copy adgrants.sql from $APPL_TOP/admin to DB tier and run it
[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
Note: When running adgrants.sql, you may get ORA-00942 errors. These are caused by adgrants.sql trying to create grants on non-existent objects. The errors can be ignored.
29) Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as apps and run the script using the following command:
$ sqlplus apps/<apps password> @adctxprv.sql <SYSTEM password> CTXSYS
[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.
30) Compile invalid objects
Use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 17
31) Grant datastore access
Use SQL*Plus to connect to the database as SYSDBA and run the following command:
$ sqlplus "/ as sysdba"
SQL> grant text datastore access to public; Grant succeeded.
32) Gather statistics for the SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session; System altered. SQL> @adstats.sql Connected.
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session; System altered. SQL> exit
33) Create the new MGDSYS schema (Conditional)-Not required by me
If you upgraded from a database version prior to Oracle 12c, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema.
$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql
34) Convert Database to Multitenant Architecture or PDB
There are two databases that are associated with the 19c Oracle home, the CDB(funcdb) and the non-CDB database (FUAT). Moreover, the non-CDB database will be migrated to the PDB database. Set the appropriate environment variables to connect to the appropriate database.
Note: The txkOnPremPrePDBCreationTasks.pl script shuts down the non-CDB database. Do not manually bring up the non-CDB database. There will be no access to the non-CDB database until after the migration of the non-CDB database to the PDB.
make sure all temp, sys, the system has sufficient free space.
a) Create the PDB descriptor
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<source SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \
-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> -dbsid=<source SID>
[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:
b) Update the CDB initialization parameters
On the database server node, copy the <source SID>_initparam.sql and <source SID>_datatop.txt files from the source $ORACLE_HOME/dbs directory to the new $ORACLE_HOME/dbs directory. Then use SQL*Plus to connect to the CDB as SYSDBA, and run the following commands to update the CDB initialization parameters:
[oracle@funebs122 dbs]$ pwd
/oraapps122/DATABASE/FUAT/12.1.0/dbs
[oracle@funebs122 dbs]$ cp FUAT_initparam.sql FUAT_datatop.txt /oraapps122/DATABASE/FUAT/19c/dbs/
[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.
if an error related to the connection broker, please comment that line in pfile and start again.
c) Check for PDB violations
Use the following commands to run the txkChkPDBCompatability.pl script. This checks the PDB for any violations.
[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
Note: Use the same command options for Oracle Cloud Infrastructure Compute environments.
Review all warnings and resolve all errors. Do not run noncdb_to_pdb.sql as that will be run by txkCreatePDB.pl in the next step.
d) Create the PDB
Load the environment variables by running the following commands.
[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
I am keeping the data top for non-CDB and PDB the same.
DATA_TOP_1 =========== Enter the non-CDB data top [/oraapps122/DATABASE/FUAT/data]: Enter the corresponding PDB data top [/oraapps122/DATABASE/FUAT/data]:
e) Run the post PDB script
Use the following commands to run the txkPostPDBCreationTasks.pl script. This updates the PDB configuration.
[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:
f) Change Initialization Parameters
Use the following sections in My Oracle Support Knowledge Document 396009.1, Database Initialization Parameter Settings for Oracle E-Business Suite Release 12, as a guideline in modifying your initialization parameters.
[oracle@funebs122 dbs]$ export ORACLE_SID=funcdb
[oracle@funebs122 dbs]$ sqlplus '/as sysdba'
alter system set event='10946 trace name context forever, level 8454144' scope=spfile;
alter system set sec_case_sensitive_logon=FALSE scope=spfile;
35) Run Autoconfig on the Application tier
Add the CDB entry in tnsnames.ora of Application
As the user of the applications server node, on both the Patch and Run APPL_TOP, modify the $TNS_ADMIN/tnsnames.ora file to specify the CDB instance name. The following shows the format of the new TNS entry.
funcdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=funebs122.lab)(PORT=1521))
(CONNECT_DATA = (SERVICE_NAME=ebs_FUAT)(INSTANCE_NAME=funcdb))
)
Update the following values in the context file of every Applications tier server node.
Variable Name Value
s_dbport New database port
s_apps_jdbc_connect_descriptor NULL
s_applptmp Directory (not /usr/tmp) defined in UTL_FILE_DIR
To identify the allowable directories for s_applptmp use, connect to the Oracle E-Business Suite database instance as the apps user and run the following query:
SQL> select value from v$parameter where name='utl_file_dir';
Run AutoConfig on both patch and run APPL_TOPs using the following command.
$INST_TOP/admin/scripts/adautocfg.sh
36) Apply post-upgrade WMS patches (Conditional)
If you upgraded from a database version prior to 12c, apply Patch 19007053. Since the WLS admin server has not yet been brought up, apply the patch in downtime mode on the run APPL_TOP by running the following command:
$ adop phase=apply patches=19007053 apply_mode=downtime
37) Recreate custom database links (Conditional)
If the Oracle Net listener in the 19c Oracle home is defined differently to the one used by the old Oracle home, you must recreate any custom self-referential database links that exist in the Oracle E-Business Suite database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the database instance as apps and run the following query:
$ sqlplus apps/<apps password>
SQL> select db_link from all_db_links;
The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig.
If you have custom self-referential database links in the database instance, use the following commands to drop and recreate them:
$ sqlplus apps/<apps password>
SQL> drop database link <custom database link>;
SQL> create database link <custom database link> connect to <user> identified by
<password> using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)
(PORT=<port number>))(CONNECT_DATA=(SID=<ORACLE_SID>)))';
38) Start Application Services and Validate the Database Version
This completes our upgrade to 19c
References:
MOS Doc Id. 1594274.1 Install Oracle Database binaries 19c and apply EBS overlay patches as per
MOS Doc Id. 2552181.1 Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle EBS Database 19c
MOS (Doc ID 396009.1) Database Initialization Parameters for Oracle E-Business Suite Release 12
(MOS Note 2530665.1) Using Oracle Database 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2
(MOS Note 2554156.1) Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19
Subscribe to:
Posts
(
Atom
)
