1. preupgrade_fixup 실행

preupgrade_fixups 수행 및 데이터베이스 중지
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

본격적인 업그레이드에 앞서 preupgrade_fixups.sql 스크립트를 실행합니다.

자동으로 처리할 수 없는 DBA가 수동으로 처리해야 할 사항들을 확인할 수 있습니다.

그리고 11g의 인스턴스를 중지합니다. 이후 부터는 19c 인스턴스로 작업을 진행합니다.

2. 파일 복사

네트워크, 파라미터, 패스워드 파일 복제
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/

기존에 사용 중이던 네트워크 설정 파일(listener.ora, tnsnames.ora), 파라미터 파일 그리고 패스워드 파일을 19c의 경로로 복사합니다.

네트워크 설정 파일의 경우, 기존 11g의 홈경로가 저장되어 있을 경우 19c에 맞게 수정해야 합니다.

패스워드 파일 업그레이드
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.

복사한 패스워드 파일을 12c 이상의 버전으로 업그레이드 합니다.


3. 리스너 재기동

11g 리스너 중지
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 successfully

11g의 리스너를 중지합니다.

19c 리스너 기동
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

19c의 리스너로 기동합니다.

4. 업그레이드

데이터베이스 기동
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 스크립트 실행
<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]

OS의 oracle 계정에서 dbupgrade 쉘 스크립트를 실행합니다.

작업이 완료되면 자동으로 데이터베이스가 shutdown 됩니다.

  • 레이블 없음