버전 비교

이전 버전 1

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 2

changes.mady.by.user 윤병훈

에 저장

키

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

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





코드 블럭
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;


펼치기


서식 미적용
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





코드 블럭
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.





코드 블럭
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.





코드 블럭
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.





코드 블럭
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




심볼릭 링크 디렉토리 정리

코드 블럭
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.




Fixed Objects 통계 수집

코드 블럭
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.


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.





코드 블럭
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




...