1. Time Zone 업그레이드
기동 및 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 모드로 재기동합니다.
데이터베이스 Time Zone 업그레이드
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을 업그레이드 합니다.
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 업그레이드에서 오류가 없었다면, 작업을 종료합니다.
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로 확인할 수 있습니다.
2. 심볼릭 링크 디렉토리 정리
심볼릭 링크 디렉토리 조회
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 버전에서는 지원하지 않는 기능이므로 해당되는 디렉토리가 있다면 대체 경로로 재생성해줘야 합니다.
3. Fixed Objects 통계 수집
Fixed Objects 통계 수집
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.
데이터베이스의 성능을 고려하여 x$ 테이블과 인덱스에 대한 통계 정보를 수집합니다.
4. postupgrade_fixups 실행
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 스크립트를 수행합니다.
업그레이드 이후에 별도의 조치가 필요한 에러, 경고 또는 정보를 출력합니다.
{}
