In 12c default timezine version is v26 and after database upgrade to 19c the timezone has to be upgrade to v32 since v32 is default timezone version
Before DB Upgarde:
Database: DB1
Hostname: oraclelab1.localdomain.com
OH: /u01/app/oracle/product/12.2.0.1/dbhome_1
Default Timezone: v26
After DB Upgarde:
Database: DB1
Hostname: oraclelab1.localdomain.com
OH: /u01/app/oracle/product/19.0.0.0/dbhome_1
Default Timezone: v32
1. Check current settings:
SELECT * FROM v$timezone_file;
2. Startup database in upgrade mode:
shutdown immediate;
startup upgrade;
3. Start upgrade window:
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
4. Check primary and secondary time zone versions:
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
5. Startup database in normal mode:
SQL> shut immediate;
SQL> startup;
6. Do the upgrade:
SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
7. Check new settings:
SELECT * FROM v$timezone_file;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
Logs:
[oracle@oraclelab1 ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
#DB1:/u01/app/oracle/product/12.2.0.1/dbhome_1:N
DB1:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [Db1] ? DB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ env|grep ORA
ORACLE_SID=DB1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 4 14:54:52 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 3707763120 bytes
Fixed Size 8903088 bytes
Variable Size 872415232 bytes
Database Buffers 2818572288 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB1 OPEN MIGRATE
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/ 2 3 4 5 6 7 8 9
l_tz_version=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL>
SQL> col PROPERTY_NAME for a30
SQL> col VALUE for a10
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 26
DST_UPGRADE_STATE UPGRADE
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3707763120 bytes
Fixed Size 8903088 bytes
Variable Size 872415232 bytes
Database Buffers 2818572288 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB1 OPEN
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_failures PLS_INTEGER;
3 BEGIN
4 DBMS_DST.upgrade_database(l_failures);
5 DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
6 DBMS_DST.end_upgrade(l_failures);
7 DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
8 END;
9 /
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"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
Regards,
Mallik
No comments:
Post a Comment