4. 업그레이드
4.1 preupgrade_fixup 실행
preupgrade_fixups 수행 및 데이터베이스 중지
|
1
2
3
4
|
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
shutdown immediate;
|
더보기
[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 5 11:19:11 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM Oracle PRE-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:37
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;
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-03-05 11:13:37
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
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. pre_fixed_objects YES None.
3. tablespaces_info NO Informational only.
Further action is optional.
4. exf_rul_exists NO Informational only.
Further action is optional.
5. rman_recovery_version NO Informational only.
Further action is optional.
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 is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_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.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4.2 파일 복사
네트워크, 파라미터, 패스워드 파일 복제
|
1
2
3
|
cp <11g ORACLE_HOME 경로>/network/admin/*.ora <19c ORACLE_HOME 경로>/network/admin/
cp <11g ORACLE_HOME 경로>/dbs/spfile<인스턴스명>.ora <19c ORACLE_HOME 경로>/dbs/
cp <11g ORACLE_HOME 경로>/dbs/orapw<인스턴스명> <19c ORACLE_HOME 경로>/dbs/
|
더보기
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbup)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbup)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@orcl ~]$ cp $ORACLE_HOME/network/admin/*.ora $ORACLE_BASE/product/19c/db_1/network/admin/
[oracle@orcl ~]$ cp $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_BASE/product/19c/db_1/dbs/
[oracle@orcl ~]$ cp $ORACLE_HOME/dbs/orapworcl $ORACLE_BASE/product/19c/db_1/dbs/
패스워드 파일 업그레이드
|
1
|
orapwd file=<19c ORACLE_HOME 경로>/dbs/orapw<데이터베이스명> force=y format=12
|
더보기
[oracle@orcl ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl force=y format=12
Enter password for SYS:<sys 계정 암호 입력>업그레이드를 하지 않았을 경우, sys 계정 암호 변경 시 ORA-28017 에러 발생
더보기
SQL> alter user sys identified by oracle;
alter user sys identified by oracle
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.12.2 이상으로 업그레이드할 경우 암호 복잡도 규칙이 강제 적용되어 OPW-00029 에러 발생
더보기
[oracle@orcl ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl force=y format=12.2
Enter password for SYS:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.
4.3 리스너 재기동
11g 리스너 중지
|
1
|
lsnrctl stop
|
더보기
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@orcl ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-MAR-2020 11:25:03
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbup)(PORT=1521)))
The command completed successfully19c 리스너 기동
|
1
|
lsnrctl start
|
더보기
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19c/db_1
[oracle@orcl ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAR-2020 11:25:54
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19c/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbup/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbup)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbup)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 05-MAR-2020 11:25:54
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dbup/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbup)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
4.4 업그레이드
데이터베이스 기동
|
1
|
startup upgrade;
|
더보기
[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 5 11:26:30 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 1207959552 bytes
Database Buffers 872415232 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
dbupgrade 스크립트 실행
|
1
|
<19c ORACLE_HOME 경로>/bin/dbupgrade
|
더보기
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19c/db_1
[oracle@orcl ~]$ $ORACLE_HOME/bin/dbupgrade
Argument list for [/u01/app/oracle/product/19c/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217]
/u01/app/oracle/product/19c/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19c/db_1]
/u01/app/oracle/product/19c/db_1/bin/orabasehome = [/u01/app/oracle/product/19c/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/db_1]
Analyzing file /u01/app/oracle/product/19c/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20200305112854]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200305112854/catupgrd_catcon_8389.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200305112854/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200305112854/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = orcl
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905/catupgrd_catcon_8389.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905]
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2020_03_05 11:29:19]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 67s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 44s
Restart Phase #:2 [orcl] Files:1 Time: 3s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 18s
Restart Phase #:4 [orcl] Files:1 Time: 3s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 17s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 13s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 10s
Restart Phase #:8 [orcl] Files:1 Time: 3s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:67 Time: 27s
Restart Phase #:10 [orcl] Files:1 Time: 3s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 58s
Restart Phase #:12 [orcl] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:94 Time: 10s
Restart Phase #:14 [orcl] Files:1 Time: 2s
Parallel Phase #:15 [orcl] Files:121 Time: 17s
Restart Phase #:16 [orcl] Files:1 Time: 2s
Serial Phase #:17 [orcl] Files:22 Time: 5s
Restart Phase #:18 [orcl] Files:1 Time: 4s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 20s
Restart Phase #:20 [orcl] Files:1 Time: 3s
Serial Phase #:21 [orcl] Files:3 Time: 13s
Restart Phase #:22 [orcl] Files:1 Time: 2s
Parallel Phase #:23 [orcl] Files:25 Time: 160s
Restart Phase #:24 [orcl] Files:1 Time: 2s
Parallel Phase #:25 [orcl] Files:12 Time: 107s
Restart Phase #:26 [orcl] Files:1 Time: 1s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:3 Time: 5s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 3s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 2s
Restart Phase #:32 [orcl] Files:1 Time: 3s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:294 Time: 22s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 3s
Serial Phase #:38 [orcl] Files:6 Time: 7s
Restart Phase #:39 [orcl] Files:1 Time: 4s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 55s
Restart Phase #:41 [orcl] Files:1 Time: 2s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 111s
Restart Phase #:43 [orcl] Files:1 Time: 4s
Parallel Phase #:44 [orcl] Files:11 Time: 12s
Restart Phase #:45 [orcl] Files:1 Time: 4s
Parallel Phase #:46 [orcl] Files:3 Time: 3s
Restart Phase #:47 [orcl] Files:1 Time: 3s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 10s
Restart Phase #:49 [orcl] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 18s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 3s
Restart Phase #:52 [orcl] Files:1 Time: 3s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 387s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 1s
Serial Phase #:56 [orcl] Files:3 Time: 27s
Serial Phase #:57 [orcl] Files:3 Time: 6s
Parallel Phase #:58 [orcl] Files:10 Time: 5s
Parallel Phase #:59 [orcl] Files:25 Time: 7s
Serial Phase #:60 [orcl] Files:4 Time: 11s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:32 Time: 6s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 9s
Serial Phase #:65 [orcl] Files:2 Time: 24s
Serial Phase #:66 [orcl] Files:3 Time: 88s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 5s
Serial Phase #:69 [orcl] Files:1 Time: 4s
Parallel Phase #:70 [orcl] Files:2 Time: 38s
Restart Phase #:71 [orcl] Files:1 Time: 3s
Parallel Phase #:72 [orcl] Files:2 Time: 5s
Serial Phase #:73 [orcl] Files:2 Time: 4s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcl] Files:1 Time: 4s
Serial Phase #:76 [orcl] Files:1 Time: 38s
Serial Phase #:77 [orcl] Files:2 Time: 5s
Restart Phase #:78 [orcl] Files:1 Time: 4s
Serial Phase #:79 [orcl] Files:1 Time: 20s
Restart Phase #:80 [orcl] Files:1 Time: 2s
Parallel Phase #:81 [orcl] Files:3 Time: 53s
Restart Phase #:82 [orcl] Files:1 Time: 3s
Serial Phase #:83 [orcl] Files:1 Time: 8s
Restart Phase #:84 [orcl] Files:1 Time: 3s
Serial Phase #:85 [orcl] Files:1 Time: 13s
Restart Phase #:86 [orcl] Files:1 Time: 3s
Parallel Phase #:87 [orcl] Files:4 Time: 93s
Restart Phase #:88 [orcl] Files:1 Time: 4s
Serial Phase #:89 [orcl] Files:1 Time: 4s
Restart Phase #:90 [orcl] Files:1 Time: 2s
Serial Phase #:91 [orcl] Files:2 Time: 14s
Restart Phase #:92 [orcl] Files:1 Time: 3s
Serial Phase #:93 [orcl] Files:1 Time: 3s
Restart Phase #:94 [orcl] Files:1 Time: 2s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcl] Files:1 Time: 26s
Restart Phase #:96 [orcl] Files:1 Time: 4s
*********** Final Component scripts ***********
Serial Phase #:97 [orcl] Files:1 Time: 4s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcl] Files:1 Time: 426s
******************* Migration ******************
Serial Phase #:99 [orcl] Files:1 Time: 43s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcl] Files:1 Time: 2s
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 47s
***************** Post Upgrade *****************
Serial Phase #:103 [orcl] Files:1 Time: 37s
**************** Summary report ****************
Serial Phase #:104 [orcl] Files:1 Time: 3s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcl] Files:1 Time: 2s
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 26s
------------------------------------------------------
Phases [0-107] End Time:[2020_03_05 12:09:45]
------------------------------------------------------
Grand Total Time: 2429s
LOG FILES: (/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19c/db_1/cfgtoollogs/orcl/upgrade20200305112905/upg_summary.log
Grand Total Upgrade Time: [0d:0h:40m:29s]
5. 마무리 작업
5.1 Time Zone 업그레이드
기동 및 Time Zone 버전 조회
|
1
2
3
|
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
영향도 분석
|
1
2
3
4
5
6
7
|
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.
업그레이드 윈도우 시작 및 재기동
|
1
2
3
|
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 업그레이드
|
1
2
3
4
5
6
7
8
|
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 업그레이드 종료
|
1
2
3
4
5
6
7
8
|
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 업그레이드 결과 확인
|
1
2
3
4
|
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
5.2 심볼릭 링크 디렉토리 정리
심볼릭 링크 디렉토리 조회
|
1
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.
5.3 Fixed Objects 통계 수집
Fixed Objects 통계 수집
|
1
|
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
|
더보기
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
5.4 postupgrade_fixups 실행
postupgrade_fixups 스크립트 수행
|
1
2
3
|
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.
'DATABASE > ORACLE' 카테고리의 다른 글
| 오라클 엔터프라이즈 매니저(Oracle Enterprise Manager) (0) | 2022.10.13 |
|---|---|
| [오라클] 업그레이드 #3 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
| [오라클] 업그레이드 #2 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
| [오라클] 업그레이드 #1 - Oracle database upgrade (11.2.x to 19.x) (0) | 2022.10.10 |
댓글