1. 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
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.
3. Fixed Objects 통계 수집
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.
4. 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
{}
