set lines 1000
set pages 1000
show con_name;
col SCHEMA_NAME for a12
col TABLE_NAME             for a28
col COLUMN_NAME for a27
col DATA_TYPE for a34
SELECT col.owner AS schema_name, col.table_name, column_id, column_name, data_type, data_scale AS second_scale
FROM dba_tab_cols col 
JOIN sys.dba_tables tab 
  ON col.owner = tab.owner AND col.table_name = tab.table_name 
WHERE col.data_type LIKE 'TIMESTAMP%TIME ZONE' 
AND col.owner NOT IN (select USERNAME from dba_users where ORACLE_MAINTAINED='Y')
ORDER BY col.owner, col.table_name, column_id;


srvctl setenv database -d DMSCDB -t "TZ=Asia/Riyadh"

restart cluster

grep TZ /u01/app/19.0.0.0/grid/crs/install/s_crsconfig_hq-blkdbuat-v01_env.txt


/* enter this path */ 
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

sqlplus / as sysdba

select version from v$timezone_file;

/* if you have PDBS , you should do these on Container & PDBS */

@utltz_upg_check.sql

@utltz_upg_apply.sql

select version from v$timezone_file;
============================================================================
============================================================================
[oracle@hq-scndbu-v01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 28 12:38:25 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                 13906448 bytes
Variable Size            4127195136 bytes
Database Buffers         1.1945E+10 bytes
Redo Buffers               19644416 bytes
SQL> show parameter clustrer
SQL>
SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL>
SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shu immediate
startup;
ORA-01507: database not mounted





ORACLE instance shut down.
SQL> ORACLE instance started.
Database opened.
SQL>
SQL>
SQL> select version from v$timezone_file;

   VERSION
----------
        32




SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DMSUAT                         READ WRITE NO
SQL> @utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
WARNING: There are 1 open PDBs .
WARNING: They will be closed when running utltz_upg_apply.sql .
INFO: Database RDBMS DST version is DSTv32 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv42 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL> SQL>
SQL>
SQL> @utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv42 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
WARNING: There are 1 open PDBs .
WARNING: They will be closed when CDB$ROOT is restarted
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                 13906448 bytes
Variable Size            4127195136 bytes
Database Buffers         1.1945E+10 bytes
Redo Buffers               19644416 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                 13906448 bytes
Variable Size            4127195136 bytes
Database Buffers         1.1945E+10 bytes
Redo Buffers               19644416 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv42 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.



SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DMSUAT                         READ WRITE NO
SQL> alter session set container=DMSUAT;

Session altered.

SQL> @utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is DMSUAT .
INFO: Database RDBMS DST version is DSTv32 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv42 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.


SQL>
SQL> @utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv42 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is DMSUAT .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Pluggable Database closed.
Pluggable Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Pluggable Database closed.
Pluggable Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_WEB_SRC_MODULES"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_AUTOMATIONS"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_220200"."WWV_FLOW_ACTIVITY_LOG1$"
DECLARE
*
ERROR at line 1:
ORA-08102: index key not found, obj# 75125, file 11, block 126198 (2)
ORA-06512: at "SYS.DBMS_DST", line 1131
ORA-06512: at "SYS.DBMS_DST", line 611
ORA-06512: at "SYS.DBMS_DST", line 209
ORA-06512: at "SYS.DBMS_DST", line 858
ORA-06512: at "SYS.DBMS_DST", line 596
ORA-06512: at "SYS.DBMS_DST", line 1123
ORA-06512: at line 11

[oracle@hq-scndbu-v01 admin]$ sqlplus / as sysdba


SQL> select version from v$timezone_file;

   VERSION
----------
        42


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DMSUAT                         READ WRITE NO
SQL> alter session set container=DMSUAT;

Session altered.

SQL> select version from v$timezone_file;

   VERSION
----------
        42
Version 19.20.0.0.0
[oracle@hq-scndbu-v01 admin]$ sqlplus / as sysdba
Version 19.20.0.0.0

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Redo Buffers               19644416 bytes
SQL> show pdbs;
SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL>
SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

System altered.

SQL> shu immediate
ORA-01507: database not mounted

[oracle@hq-scndbu-v01 admin]$ srvctl start database -d dmsuatc
[oracle@hq-scndbu-v01 admin]$