In some special situations, database can not be upgraded to 19c by Database Upgrade procedure. For example, run hcheck as sysdba (Doc ID 136697.1). If get below error, you have to fix the problem before moving forward with database upgrade.
Problem Summary
---------------------------------------------------
HCKW-0027: System Tablespace does not have relative file number of 1 (Doc ID 2364065.1)
Problem Description
---------------------------------------------------
Running hcheck.sql
Returned warning:
- SystemNotRfile1 ... 1201000200 > 902000000 03/04 16:03:08 WARN
Confirm file names caused the mess-up:
SQL> select RELATIVE_FNO fno, file_id, substr(file_name,1,50) fileName
from dba_data_files where file_name like '%sys%';
FNO File_ID FILENAME
16 1 /oradata/u10/EBSDEV/system01.dbf
8 8 /oradata/u10/EBSDEV/system06.dbf
87 87 /oradata/u10/EBSDEV/system07.dbf
etc … …
SQL> select RELATIVE_FNO, file_id, substr(file_name,1,50) fileName
from dba_data_files where file_id =16;
FNO File_ID FILENAME
16 16 /oradata/u11/EBSDEV/APPS_TS_TX_IDX36.dbf
Steps on EBS apps side when database export & import are used to upgrade the database:
1. Make sure all pre-requisite patches were applied to apps side
A list of EBS patches are needed before 19c database upgrade. See Doc ID 2552181.1 (Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c).
2. Run through patch cycle to complete adop cleanup
On the current run file system:
Run fs_clone first (if no pending ADOP session exists). It will screen out problems and help to fix potential issues (for example, fs_clone was not executed right after ssl cert renewal):
$ adop phase=fs_clone
If troubleshooting was performed, make sure nothing is running on PATCH (say, fs2) file system and log onto EBS site to do a sanity check.
$ ps -ef | grep fs2
Starting a new OS session on primary node:
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop -status
... ...
===============================================================
Node Name Node Type Phase Status Started Finished Elapsed
--------------- ---------- --------------- --------------- -------------------- -------------------- ------------
node1 master PREPARE COMPLETED 2022/X/14 20:32:32 2022/X/14 20:38:05 0:05:33
APPLY ACTIVE
FINALIZE COMPLETED 2022/X/14 21:27:31 2022/X/14 21:34:38 0:07:07
CUTOVER NOT STARTED
CLEANUP NOT STARTED
node2 slave PREPARE COMPLETED 2022/X/14 20:32:31 2022/X/14 20:38:07 0:05:36
APPLY NOT STARTED
FINALIZE COMPLETED 2022/X/14 21:27:31 2022/X/14 21:34:38 0:07:07
CUTOVER NOT STARTED
CLEANUP NOT STARTED
node3 slave PREPARE COMPLETED 2022/X/14 20:32:31 2022/X/14 20:39:39 0:07:08
APPLY NOT STARTED
FINALIZE COMPLETED 2022/X/14 21:27:31 2022/X/14 21:34:38 0:07:07
CUTOVER NOT STARTED
CLEANUP NOT STARTED
May see concurrent job "Online Patching In Progress"
$ adop phase=cutover
On the new run file system:
$ adop phase=cleanup cleanup_mode=full
Also run SQL scripts by apps:
@$AD_TOP/sql/ADZDSHOWOBJS.sql - to check which objects still need to be tidied up.
@$AD_TOP/sql/ADZDSHOWLOG.sql - to make sure that it ran through properly
@$AD_TOP/sql/ADZDSHOWED.sql - to get the list of Editions
Shutdown apps services
3. DBA upgrades database to 19c by using Export and Import
4. Modify apps files after DBA provides new TNS entry.
Below is an example of the new TNS entry.
EBSDEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebs_db1d.domain.com)(PORT=1560))
(CONNECT_DATA=
(SERVICE_NAME=ebs_EBSDEV)
(INSTANCE_NAME=CEBSDEV)
)
)
To modify the TNS entries, edit the $TNS_ADMIN/tnsnames.ora files to specify the CDB instance name.
$ cd $TNS_ADMIN
$ vi tnsnames.ora
replace every INSTANCE_NAME to CDBEDEV
replace every SERVICE_NAME to ebs_EBSDEV (if it is different from 12c)
$ sqlplus apps/appsPWD
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
$ tnsping ebsdev
... ...
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDEV) (INSTANCE_NAME=CDBEDEV)))
OK (0 msec)
5. Edit $CONTEXT_FILE on both RUN and PATCH file systems
Update the following values in the context file.
Variable Name Value
s_dbhost New database hostname (if changed)
s_dbport New database port (if changed)
s_dbGlnam CDBEDEV (Seems autoconfig does not keep it)
s_apps_jdbc_connect_descriptor NULL (<jdbc_url oa_var="s_apps_jdbc_connect_descriptor"/>)
s_dbport New database port
s_applptmp Defined in UTL_FILE_DIR (e.g. /path/to/EBSDEV/utl_dir)
6. Run AutoConfig on RUN and PATCH file systems in all nodes
SQL> select * from FND_OAM_CONTEXT_FILES;
SQL> select * from fnd_nodes; <== only one row for db node
$ ./adautocfg.sh
Notes:
a). AutoConfig will fail on jtfictx.sh because jtfictx.sql requires AD_CTX_DDL, which is created in a later step. AutoConfig will be run again after AD_CTX_DDL has been created.
b). After AutoConfig, s_apps_jdbc_connect_descriptor will get new value.
c). AutoConfig will overwrite variable s_dbGlnam ?
Table FND_OAM_CONTEXT_FILES might be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well to avoid ADOP session failure in the future.
To run AutoConfig on PATCH file system, disable a trigger first (and afterwards enable it).
SQL> conn system/systemPWD
Connected.
SQL> alter trigger ebs_logon disable;
Trigger altered.
SQL> select * from FND_OAM_CONTEXT_FILES; <== entries for both RUN and PATCH
7. Create the CTX objects
Certain Oracle Text objects are not preserved by the import process. The consolidated export/import utility patch that were applied to the administration server node in Section 4 of Doc ID 2552181.1 contains a perl script, dpost_imp.pl, that will generate an AutoPatch driver file. Use this driver file to call the scripts that create these objects.
$ cd $AU_TOP/patch/115/bin
$ mkdir 6924477
$ perl dpost_imp.pl u6924477.drv 12 <== it creates file u6924477.drv
$ mv u6924477.drv 6924477 <== repeat above steps on other node(s)
$ adop phase=apply hotpatch=yes patchtop=$AU_TOP/patch/115/bin patches=6924477 options=forceapply
8. DBAs run some reports, compile invalid objects and make necessary grants.
9. Run AutoConfig in 2nd time on all apps nodes and start all EBS services.
All shall work by now.
OPTIONAL actions:
1. If WF agent does not start, the issue is with table AQ$_WF_CONTROL_P. Build WF queue on fly.
$ sqlplus applsys/PWD
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> set pages 10000
SQL> select instance_name from v$instance;
SQL> select count(*) from all_objects;
COUNT(*)
----------
62483
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'WF_CONTROL',force => TRUE);
PL/SQL procedure successfully completed.
SQL> select count(*) from all_objects;
COUNT(*)
----------
62446
SQL> select * from all_objects where object_name like'AQ$WF_CONTROL%';
no rows selected
SQL> select * from all_objects where object_name like'AQ$_WF_CONTROL%';
no rows selected
$ sqlplus apps/appsPWD
SQL> @/path/to/EBSPGRND/utl_dir/wfctqrec_new.sql APPLSYS appsPWD
Connected. (see below Notes)
PL/SQL procedure successfully completed.
Commit complete.
$ sqlplus applsys/PWD
SQL> select count(*) from all_objects;
COUNT(*)
----------
62474
SQL> column object_name format a25
SQL> column object_type format a10
SQL> select object_name, object_type from all_objects
where object_name like 'AQ$_WF_CONTROL%';
OBJECT_NAME OBJECT_TYP
------------------------------------- ----------
AQ$_WF_CONTROL_E QUEUE
AQ$_WF_CONTROL_F VIEW
AQ$_WF_CONTROL_G TABLE
AQ$_WF_CONTROL_H TABLE
AQ$_WF_CONTROL_I TABLE
AQ$_WF_CONTROL_L TABLE
AQ$_WF_CONTROL_N SEQUENCE
AQ$_WF_CONTROL_S TABLE
AQ$_WF_CONTROL_T TABLE
AQ$_WF_CONTROL_V EVALUATION
CONTEXT
10 rows selected.
SQL> select object_name, object_type from all_objects
where object_name like 'AQ$WF_CONTROL%';
OBJECT_NAME OBJECT_TYP
------------------------- ----------
AQ$WF_CONTROL VIEW
AQ$WF_CONTROL_R VIEW
AQ$WF_CONTROL_S VIEW
Some database objects are created on "fly" after EBS services are started:
$ cd $ADMIN_SCRIPTS_HOME
$ ./adstrtal.sh apps/appsPWD
SQL> select object_name, object_type from all_objects
where object_name like 'AQ$_WF_CONTROL%';
OBJECT_NAME OBJECT_TYP
------------------------- ----------
AQ$_WF_CONTROL_D TABLE
AQ$_WF_CONTROL_E QUEUE
AQ$_WF_CONTROL_F VIEW
AQ$_WF_CONTROL_G TABLE
AQ$_WF_CONTROL_H TABLE
AQ$_WF_CONTROL_I TABLE
AQ$_WF_CONTROL_L TABLE
AQ$_WF_CONTROL_N SEQUENCE
AQ$_WF_CONTROL_P TABLE
AQ$_WF_CONTROL_S TABLE
AQ$_WF_CONTROL_T TABLE
AQ$_WF_CONTROL_V EVALUATION
CONTEXT
12 rows selected.
SQL> select object_name, object_type from all_objects
where object_name like 'AQ$WF_CONTROL%';
OBJECT_NAME OBJECT_TYP
------------------------------------ ----------
AQ$WF_CONTROL VIEW
AQ$WF_CONTROL_R VIEW
AQ$WF_CONTROL_S VIEW
SQL> select count(*) from all_objects;
COUNT(*)
----------
62487
Three Concurrent Managers shall be started and in running:
. Workflow Mailer Service
. Workflow Agent Listener Service
. Workflow Document Web Services Service
But "Workflow Notification Mailer" may still show Down status due to a wrong SMTP server.
Notes: If running wfctqrec.sql gets error, delete some rows and save it as wfctqrec_new.sql for execution.
$ ls -altr wfctqrec*.sql
-rwxr-xr-x 1 user group 3734 Apr 12 2021 wfctqrec.sql
-rwxr-xr-x 1 user group 2865 Jul 14 17:16 wfctqrec_new.sql
$ diff wfctqrec.sql wfctqrec_new.sql
< BEGIN
< dbms_aqadm.stop_queue( queue_name => 'WF_CONTROL', wait => FALSE);
< EXCEPTION
< WHEN no_queue THEN
< null;
< WHEN others THEN
< RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '
< ||to_char(sqlcode)||' - '||sqlerrm);
< END;
<
< BEGIN
< dbms_aqadm.drop_queue(queue_name=> 'WF_CONTROL');
< EXCEPTION
< WHEN no_queue THEN
< null;
< WHEN others THEN
< RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '
< ||to_char(sqlcode)||' - '||sqlerrm);
< END;
<
< BEGIN
< dbms_aqadm.drop_queue_table( queue_table => 'WF_CONTROL', force => TRUE);
< EXCEPTION
< WHEN queue_table_not_exists THEN
< null;
< WHEN others THEN
< RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '
< ||to_char(sqlcode)||' - '||sqlerrm);
< END;
<
2. After 19c upgrade, three AZ tables for EBS iSetup becomes missing in 19c database. Follow
Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) to re-create them
3. In R12.2, when below line was used to hold concurrent jobs for a period of time for database upgrade completion and then from GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.
SQL> update apps.fnd_concurrent_requests set hold_flag = 'Y'
where status_code in ('Q','I') and hold_flag = 'N';
The problem may be column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.
SQL> select edition_name from all_editions;
EDITION_NAME
-----------------------
ORA$BASE
SQL> update fnd_concurrent_requests
set edition_name='ORA$BASE'
where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)
;
