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 APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and laterInformation 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 |