버전 비교

이전 버전 2

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.

Time Zone 업그레이드

코드 블럭
title기동 및 Time Zone 버전 조회
linenumberstrue
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 정보를 확인합니다.


코드 블럭
title영향도 분석
linenumberstrue
SET SERVEROUTPUT ON;
exec DBMS_DST.BEGIN_PREPARE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
exec DBMS_DST.FIND_AFFECTED_TABLES;
exec DBMS_DST.END_PREPARE;
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> exec DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

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.
BEGIN_UPGRADE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION); shutdown immediate startup
펼치기
서식 미적용
SQL> exec DBMS_DST.BEGIN_UPGRADE(DBMS_DST.GET_LATEST_TIMEZONE_VERSION);
An upgradeEND_PREPARE;
A prepare window has been successfully startedended.

PL/SQL procedure successfully completed.

SQL>



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


코드 블럭
title업그레이드 윈도우 시작 및 재기동
linenumberstrue
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;
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 모드로 재기동합니다.


코드 블럭
title데이터베이스 Time Zone 업그레이드
linenumberstrue
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을 업그레이드 합니다.


코드 블럭
titleTime Zone 업그레이드 종료
linenumberstrue
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 업그레이드에서 오류가 없었다면, 작업을 종료합니다.


코드 블럭
titleTime Zone 업그레이드 결과 확인
linenumberstrue
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로 확인할 수 있습니다.

심볼릭 링크 디렉토리 정리

코드 블럭
title심볼릭 링크 디렉토리 조회
linenumberstrue
SET 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 버전에서는 지원하지 않는 기능이므로 해당되는 디렉토리가 있다면 대체 경로로 재생성해줘야 합니다.

Fixed Objects 통계 수집

코드 블럭
titleFixed Objects 통계 수집
linenumberstrue
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


펼치기


서식 미적용
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

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.
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.



데이터베이스의 성능을 고려하여 x$ 테이블과 인덱스에 대한 통계 정보를 수집합니다.

postupgrade_fixups 실행

코드 블럭
titlepostupgrade_fixups 스크립트 수행
linenumberstrue
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   


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
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

ForSQL> 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.0

Preup                             Preupgrade
Action       
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
            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  depend_usr_tables         YES 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        None.
    7.  old_time_zones_exist        YES         None.Preupgrade
Action    8.  dir_symlinks              YES        Issue None.Is
Number  Preupgrade Check 9.Name  post_dictionary   Remedied    Further DBA   YES     Action
------  ------------------------  ----------  --------------------------------
    None6.
   10.  post_fixed_objects depend_usr_tables       NO  YES        Informational onlyNone.
    7.  old_time_zones_exist      YES         None.
    8.  dir_symlinks              YES     Further  action is optionalNone.
    119.  upg_by_std_upgrdpost_dictionary           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.
코드 블럭
select PATCH_ID, ACTION, STATUS, ACTION_TIME, SOURCE_VERSION, TARGET_VERSION from DBA_REGISTRY_SQLPATCH;
펼치기 서식 미적용SQL> select PATCH_ID, ACTION, STATUS, ACTION_TIME, SOURCE_VERSION, TARGET_VERSION from DBA_REGISTRY_SQLPATCH; PATCH_ID ACTION STATUS ACTION_TIME SOURCE_VERSION TARGET_VERSION ---------- ------- -------- -------------------------- --------------- --------------- 30557433 APPLY SUCCESS 20/03/05 12:06:52.894093 19.1.0.0.0 19.6.0.0.0 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0
   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 스크립트를 수행합니다.

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