My Oracle Support Banner

EBS : DBUA takes long time to select "Enable Parallel Upgrade" and "Upgrade TimeZone Data" on Step 4 and takes long time to move to next screen (Doc ID 2883214.1)

Last updated on JULY 20, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

Symptoms

DBUA takes long time to select "Enable Parallel Upgrade" and "Upgrade TimeZone Data" on Step 4 and takes long time to move to next screen

DBUA Trace Log shows that loadNonUserTablespaces & loadUserOnlyTablespaces queries take long time -

INFO: Feb 16, 2022 12:52:31 PM oracle.assistants.dbua.util.loaders.RecoverySettingsBeanLoader loadBackupControlFilename
INFO: Backup Control Filename loading finished

INFO: Feb 16, 2022 1:03:03 PM oracle.assistants.dbua.util.loaders.RecoverySettingsBeanLoader loadNonUserTablespaces
INFO: Non-user tablespaces calculated for optimized backup:[TS1,TS2,............,TSn]                                                        .........Here TS1,TS2,......,TSn are the Standard Tablespaces created during Installation

INFO: Feb 16, 2022 1:03:03 PM oracle.assistants.dbua.util.loaders.RecoverySettingsBeanLoader loadNonUserTablespaces
INFO: Loading of non-user tablespaces has finished

INFO: Feb 16, 2022 1:12:18 PM oracle.assistants.dbua.util.loaders.RecoverySettingsBeanLoader loadUserOnlyTablespaces
INFO: User-only tablespaces calculated for optimized backup: [TS1,TS2,............,TSn]                                                      .........Here TS1,TS2,......,TSn are the User created Tablespaces

INFO: Feb 16, 2022 1:12:18 PM oracle.assistants.dbua.util.loaders.RecoverySettingsBeanLoader loadUserOnlyTablespaces
INFO: Loading of non-user tablespaces has finished

 The actual query taking time is below, which is inside preupgrade_package.sql-

 with pivot_users as
(SELECT username
FROM dba_users
where user_id in (
SELECT schema# FROM sys.registry$
WHERE namespace = 'SERVER'
UNION
SELECT schema# FROM sys.registry$schemas
WHERE namespace = 'SERVER'
UNION
SELECT user# FROM sys.user$
WHERE type#=1 AND bitand(spare1,256)=256))

select tablespace_name from dba_tablespaces
MINUS
SELECT tablespace_name FROM dba_tablespaces
WHERE contents != 'UNDO' AND contents != 'TEMPORARY' AND
status = 'ONLINE' AND tablespace_name != 'SYSTEM' AND
tablespace_name != 'SYSAUX' AND tablespace_name NOT IN
(SELECT PROPERTY_VALUE FROM database_properties
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE'
UNION
SELECT unique(default_tablespace) from dba_users
WHERE username IN (select username from pivot_users)
UNION SELECT distinct(tablespace_name) from dba_segments
WHERE owner IN (select username from pivot_users)

UNION
select distinct t.name
from modeltab$ m, ts$ t, sys_objects s
where m.obj#=s.object_id and s.ts_number=t.ts#)

The above query, when executed in isolation, takes a lot of time to complete , if there are large no of Tablespaces.

Using latest Pre-Upgrade Utility from MOS Note 884522 and replacing
files(preupgrade_package.sql,preupgrade_driver.sql,dbms_registry_extended.sql,parameters.properties,preupgrade_messages.properties,components.properties,preupgrade.jar) also does not bring about any change.



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.