144 lines
3.5 KiB
Plaintext
144 lines
3.5 KiB
Plaintext
# https://oracle-base.com/articles/misc/update-database-time-zone-file#upgrade-time-zone-file-multiteanant
|
|
|
|
|
|
# check current time zone version
|
|
|
|
select * from V$TIMEZONE_FILE;
|
|
select TZ_VERSION from REGISTRY$DATABASE;
|
|
|
|
|
|
-- qyery dst_check.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;
|
|
----------------------------------
|
|
|
|
# latest available version of the timezone
|
|
select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;
|
|
|
|
|
|
# prepare for the upgrade (optional)
|
|
|
|
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_prepare(l_tz_version);
|
|
END;
|
|
/
|
|
|
|
# execute dst_check.sql
|
|
# DST_UPGRADE_STATE should change from NONE to PREPARE
|
|
|
|
# clean tachnical tables
|
|
truncate table SYS.DST$AFFECTED_TABLES;
|
|
truncate table SYS.DST$ERROR_TABLE;
|
|
|
|
|
|
# find tables and errors affected by the upgrade
|
|
exec DBMS_DST.FIND_AFFECTED_TABLES;
|
|
|
|
select * from SYS.DST$AFFECTED_TABLES;
|
|
select * from SYS.DST$ERROR_TABLE;
|
|
|
|
# perform necessaty checks and finish the prepare step if you want to go ahead with the upgrade
|
|
exec DBMS_DST.END_PREPARE;
|
|
|
|
# Note: for a CDB, TZ should be upgrade in each container
|
|
|
|
# restart the database in UPGRADE mode
|
|
|
|
# BEGIN upgrade
|
|
###############
|
|
SET SERVEROUTPUT ON
|
|
DECLARE
|
|
l_tz_version PLS_INTEGER;
|
|
BEGIN
|
|
SELECT DBMS_DST.get_latest_timezone_version
|
|
INTO l_tz_version
|
|
FROM dual;
|
|
|
|
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
|
|
DBMS_DST.begin_upgrade(l_tz_version);
|
|
END;
|
|
/
|
|
|
|
# restart the database
|
|
|
|
# END 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;
|
|
/
|
|
|
|
# restart the database
|
|
|
|
|
|
# following queries can be used to check the progress of the TZ upgrade table by table
|
|
-- CDB
|
|
COLUMN owner FORMAT A30
|
|
COLUMN table_name FORMAT A30
|
|
|
|
SELECT con_id,
|
|
owner,
|
|
table_name,
|
|
upgrade_in_progress
|
|
FROM cdb_tstz_tables
|
|
ORDER BY 1,2,3;
|
|
|
|
-- Non-CDB
|
|
COLUMN owner FORMAT A30
|
|
COLUMN table_name FORMAT A30
|
|
|
|
SELECT owner,
|
|
table_name,
|
|
upgrade_in_progress
|
|
FROM dba_tstz_tables
|
|
ORDER BY 1,2;
|
|
|
|
# Note: in 21c, the following parameter is supposed tu avoid database restart during TZ upgrade
|
|
# in my test it does not worked
|
|
alter system set timezone_version_upgrade_online=true scope=both sid='*';
|
|
|
|
|
|
-- restart PDB$SEED in UPGRADE mode
|
|
alter pluggable database PDB$SEED close immediate instances=ALL;
|
|
alter pluggable database PDB$SEED open upgrade instances=ALL;
|
|
show pdbs
|
|
alter session set container=PDB$SEED;
|
|
-- run BEGIN TZ upgrade procedure
|
|
|
|
-- restart PDB$SEED in READ-WITE mode
|
|
alter session set container=CDB$ROOT;
|
|
alter pluggable database PDB$SEED close immediate instances=ALL;
|
|
alter pluggable database PDB$SEED open read write instances=ALL;
|
|
alter session set container=PDB$SEED;
|
|
-- run END TZ upgrade procedure
|
|
|
|
-- restart PDB$SEED in READ-WITE mode
|
|
alter session set container=CDB$ROOT;
|
|
alter pluggable database PDB$SEED close immediate instances=ALL;
|
|
alter pluggable database PDB$SEED open instances=ALL;
|
|
|
|
-- check TZ and close PDB$SEED
|
|
alter session set container=CDB$ROOT;
|
|
alter pluggable database PDB$SEED close immediate instances=ALL;
|
|
|