In this guide, we will upgrade the database timezone file to latest one available after the database has been upgraded from 12c to 19c.
Note: ALWAYS make a backup of the database and the oracle software before you start.
1. Restart the database in upgrade mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3221225152 bytes
Fixed Size 9141952 bytes
Variable Size 1191182336 bytes
Database Buffers 1996488704 bytes
Redo Buffers 24412160 bytes
Database mounted.
Database opened.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PROD1PDB MOUNTED
SQL> alter pluggable database all open upgrade;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PROD1PDB MIGRATE YES
2. Start the upgrade process by executing DBMS_DST.BEGIN_UPGRADE in all the containers using the latest timezone version currently available on your system. In my system, I have 3 containers – CDB$ROOT, PDB$SEED, PROD1PDB.
CDB$ROOT
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_latest_tzv PLS_INTEGER;
3 BEGIN
4 SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION
5 INTO l_latest_tzv
6 FROM dual;
7
8 DBMS_OUTPUT.PUT_LINE('l_latest_tzv=' || l_latest_tzv);
9 DBMS_DST.BEGIN_UPGRADE(l_latest_tzv);
10 END;
11 /
l_latest_tzv=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
PDB$SEED
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_latest_tzv PLS_INTEGER;
3 BEGIN
4 SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION
5 INTO l_latest_tzv
6 FROM dual;
7
8 DBMS_OUTPUT.PUT_LINE('l_latest_tzv=' || l_latest_tzv);
9 DBMS_DST.BEGIN_UPGRADE(l_latest_tzv);
10 END;
11 /
l_latest_tzv=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
PROD1PDB
SQL> alter session set container=PROD1PDB;
Session altered.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_latest_tzv PLS_INTEGER;
3 BEGIN
4 SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION
5 INTO l_latest_tzv
6 FROM dual;
7
8 DBMS_OUTPUT.PUT_LINE('l_latest_tzv=' || l_latest_tzv);
9 DBMS_DST.BEGIN_UPGRADE(l_latest_tzv);
10 END;
11 /
l_latest_tzv=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
3. Restart the database and upgrade the database timezone for all the containers.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3221225152 bytes
Fixed Size 9141952 bytes
Variable Size 1191182336 bytes
Database Buffers 1996488704 bytes
Redo Buffers 24412160 bytes
Database mounted.
Database opened.
CDB$ROOT
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
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> set lines 180 pages 9999
SQL> col filename for a25
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
------------------------- ---------- ----------
timezlrg_32.dat 32 0
SQL> update registry$database set TZ_VERSION=32;
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT tz_version FROM registry$database;
TZ_VERSION
----------
32
PDB$SEED
Note: For the SEED PDB, we need to set _oracle_script to true to be able to open it read write.
Put the PDB$SEED back to read only after the timezone upgrade.SQL> alter session set container=PDB$SEED;
Session altered.
SQL> alter session set "_oracle_script" = true;
Session altered.
SQL> alter pluggable database pdb$seed close;
Pluggable database altered.
SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.
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
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> update registry$database set TZ_VERSION=32;
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT tz_version FROM registry$database;
TZ_VERSION
----------
32
SQL> alter pluggable database pdb$seed close;
Pluggable database altered.
SQL> alter pluggable database pdb$seed OPEN READ ONLY;
Pluggable database altered.