소스 보기

Time Zone 업그레이드

startup upgrade;
select * from V$TIMEZONE_FILE;
select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;

[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 5 13:43:00 2020
Version 19.6.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2097149856 bytes
Fixed Size                  8898464 bytes
Variable Size            1342177280 bytes
Database Buffers          738197504 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> select * from V$TIMEZONE_FILE;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat              14          0

SQL> select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

업그레이드 윈도우를 시작하기 위해 upgrage 모드로 데이터베이스를 기동합니다.

기존 11g 버전의 time zone 정보와 19c 엔진에 설치된 최신 버전의 time zone 정보를 확인합니다.

SET SERVEROUTPUT ON;
exec DBMS_DST.BEGIN_PREPARE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
exec DBMS_DST.FIND_AFFECTED_TABLES;
select * from SYS.DST$AFFECTED_TABLES;
select * from SYS.DST$ERROR_TABLE;
select * from SYS.DST$TRIGGER_TABLE;
exec DBMS_DST.END_PREPARE;

SQL> SET SERVEROUTPUT ON;

SQL> exec DBMS_DST.BEGIN_PREPARE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;

PL/SQL procedure successfully completed.

SQL> select * from SYS.DST$AFFECTED_TABLES;

no rows selected

SQL> select * from SYS.DST$ERROR_TABLE;

no rows selected

SQL> select * from SYS.DST$TRIGGER_TABLE;

no rows selected

SQL> exec DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

업그레이드에 앞서 영향을 받을만한 테이블(TIMESTAMP WITH TIME ZONE 사용 컬럼이 있는 테이블)이 있는지 조회합니다.

exec DBMS_DST.BEGIN_UPGRADE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
shutdown immediate
startup

SQL> exec DBMS_DST.BEGIN_UPGRADE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2097149856 bytes
Fixed Size                  8898464 bytes
Variable Size            1342177280 bytes
Database Buffers          738197504 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

최신 버전의 time zone으로 업그레이드를 시작합니다.

업그레이드 윈도우를 시작하고, 본격적인 업그레이드를 수행하기 위해 데이터베이스를 normal 모드로 재기동합니다.

SET SERVEROUTPUT ON;
DECLARE
  failed_num PLS_INTEGER;
BEGIN
  DBMS_DST.UPGRADE_DATABASE(failed_num);
  DBMS_OUTPUT.PUT_LINE('DBMS_DST.UPGRADE_DATABASE : ' || failed_num);
END;
/

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    failed_num PLS_INTEGER;
  3  BEGIN
  4    DBMS_DST.UPGRADE_DATABASE(failed_num);
  5    DBMS_OUTPUT.PUT_LINE('DBMS_DST.UPGRADE_DATABASE : ' || failed_num);
  6  END;
  7  /
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_ACTIVITY_LOG1$"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_ACTIVITY_LOG2$"
Number of failures: 0
Table list: "APEX_180200"."WWV_QS_RANDOM_NAMES"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_180200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
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
DBMS_DST.UPGRADE_DATABASE : 0

PL/SQL procedure successfully completed.

데이터베이스의 Time Zone을 업그레이드 합니다.

SET SERVEROUTPUT ON;
DECLARE
  failed_num PLS_INTEGER;
BEGIN
  DBMS_DST.END_UPGRADE(failed_num);
  DBMS_OUTPUT.PUT_LINE('DBMS_DST.END_UPGRADE : ' || failed_num);
END;
/

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    failed_num PLS_INTEGER;
  3  BEGIN
  4    DBMS_DST.END_UPGRADE(failed_num);
  5    DBMS_OUTPUT.PUT_LINE('DBMS_DST.END_UPGRADE : ' || failed_num);
  6  END;
  7  /
An upgrade window has been successfully ended.
DBMS_DST.END_UPGRADE : 0

PL/SQL procedure successfully completed.

Time zone 업그레이드에서 오류가 없었다면, 작업을 종료합니다.

select PROPERTY_NAME, PROPERTY_VALUE
from   DATABASE_PROPERTIES
where  PROPERTY_NAME like 'DST_%'
order by PROPERTY_NAME;

SQL> select PROPERTY_NAME, PROPERTY_VALUE
  2  from   DATABASE_PROPERTIES
  3  where  PROPERTY_NAME like 'DST_%'
  4  order by PROPERTY_NAME;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

업그레이드 결과를 SQL로 확인할 수 있습니다.

심볼릭 링크 디렉토리 정리

SET SERVEROUTPUT ON;
@?/rdbms/admin/utldirsymlink.sql

SQL> SET SERVEROUTPUT ON;
SQL> @?/rdbms/admin/utldirsymlink.sql
No DIRECTORY OBJECTS with symlinks found.

PL/SQL procedure successfully completed.

No errors.

심볼릭 링크로 생성된 경로를 사용하는 디렉토리 객체가 있는지 검토합니다.

19c 버전에서는 지원하지 않는 기능이므로 해당되는 디렉토리가 있다면 대체 경로로 재생성해줘야 합니다.

postupgrade_fixups 실행

SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle POST-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 19.0.0.0.0 Build: 1
SQL> REM    Generated on:            2020-03-05 11:13:39
SQL> REM
SQL> REM    Source Database:         ORCL
SQL> REM    Source Database Version: 11.2.0.4.0
SQL> REM    For Upgrade to Version:     19.0.0.0.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-03-05 11:13:39

For Source Database:     ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  depend_usr_tables         YES         None.
    7.  old_time_zones_exist      YES         None.
    8.  dir_symlinks              YES         None.
    9.  post_dictionary           YES         None.
   10.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
   11.  upg_by_std_upgrd          YES         None.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.


Session altered.

마지막으로 postupgrade_fixups.sql 스크립트를 수행합니다.

업그레이드 이후에 별도의 조치가 필요한 에러, 경고 또는 정보를 출력합니다.

Time Zone 업그레이드

심볼릭 링크 디렉토리 정리

Fixed Objects 통계 수집

postupgrade_fixups 실행