버전 비교

이전 버전 1

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

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

업그레이드 작업

preupgrade_fixups 스크립트 수행

코드 블럭
titlepreupgrade_fixups 수행
linenumberstrue
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/preupgrade_fixups.sql


펼치기


서식 미적용
[racdb1:/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 24 10:15:15 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/u01/app/oracle/cfgtoollogs/racdb/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-04-22 15:40:44
SQL> REM
SQL> REM    Source Database:         RACDB
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-04-22 15:40:44

For Source Database:     RACDB
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.  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.




11g 데이터베이스 중지

코드 블럭
title모든 노드의 11g 인스턴스 중지 (11g 데이터베이스 중지)
linenumberstrue
shutdown immediate


펼치기


서식 미적용
SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
racdb1

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


서식 미적용
[racdb2:/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 24 10:20:58 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
racdb2

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


서식 미적용
[root@rac1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.chad
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.net1.network
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.ons
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.proxy_advm
               OFFLINE OFFLINE      rac1                     STABLE
               OFFLINE OFFLINE      rac2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     Started,STABLE
      2        ONLINE  ONLINE       rac2                     Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.cvu
      1        ONLINE  ONLINE       rac1                     STABLE
ora.qosmserver
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.racdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------




19c 환경 설정

코드 블럭
languagebash
titlevi .bash_profile 로 $DB_HOME 경로 변경
linenumberstrue
export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1


펼치기


서식 미적용
[root@rac1 ~]# cat ~oracle/.bash_profile | grep DB_HOME
export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1
export ORACLE_HOME=$DB_HOME

[root@rac1 ~]# cat .bash_profile | grep DB_HOME
export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1


서식 미적용
[root@rac2 ~]# cat ~oracle/.bash_profile | grep DB_HOME
export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1
export ORACLE_HOME=$DB_HOME

[root@rac2 ~]# cat .bash_profile | grep DB_HOME
export DB_HOME=$ORACLE_BASE/product/19.0.0/db_1





코드 블럭
languagebash
title1번 노드 재사용 파일 복제 및 패스워드 파일 생성
linenumberstrue
cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $DB_HOME/network/admin/
cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb1.ora $DB_HOME/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwracdb1 force=y format=12


펼치기


서식 미적용
[racdb1:/home/oracle]> cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $ORACLE_BASE/product/19.0.0/db_1/network/admin/
cp: cannot stat `/u01/app/oracle/product/11.2.0/db_1/network/admin/*.ora': 그런 파일이나 디렉터리가 없습니다

[racdb1:/home/oracle]> cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb1.ora $ORACLE_BASE/product/19.0.0/db_1/dbs/

[racdb1:/home/oracle]> orapwd file=$ORACLE_HOME/dbs/orapwracdb1 force=y format=12

Enter password for SYS: <SYS 계정 암호 입력>





코드 블럭
languagebash
title2번 노드 재사용 파일 복제 및 패스워드 파일 생성
linenumberstrue
cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $DB_HOME/network/admin/
cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb2.ora $DB_HOME/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwracdb2 force=y format=12


펼치기


서식 미적용
[racdb2:/home/oracle]> cp $ORACLE_BASE/product/11.2.0/db_1/network/admin/*.ora $ORACLE_BASE/product/19.0.0/db_1/network/admin/
cp: cannot stat `/u01/app/oracle/product/11.2.0/db_1/network/admin/*.ora': 그런 파일이나 디렉터리가 없습니다

[racdb2:/home/oracle]> cp $ORACLE_BASE/product/11.2.0/db_1/dbs/initracdb2.ora $ORACLE_BASE/product/19.0.0/db_1/dbs/

[racdb2:/home/oracle]> orapwd file=$ORACLE_HOME/dbs/orapwracdb2 force=y format=12

Enter password for SYS: <SYS 계정 암호 입력>




싱글 DB로 전환 및 Upgrade 모드 기동

코드 블럭
title싱글 DB 전환 후 1번 노드 upgrade 모드로 기동
linenumberstrue
conn / as sysdba
startup nomount
alter system set cluster_database=false scope=spfile;
startup force upgrade


펼치기


서식 미적용
[racdb1:/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 24 10:23:22 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             738197504 bytes
Database Buffers         2466250752 bytes
Redo Buffers                7872512 bytes

SQL> show parameter cluster_database

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
cluster_database                     boolean                           TRUE
cluster_database_instances           integer                           2

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             738197504 bytes
Database Buffers         2466250752 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0




업그레이드 수행

코드 블럭
languagebash
titledbupgrade 스크립트 실행
linenumberstrue
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1
$ORACLE_HOME/bin/dbupgrade


펼치기


서식 미적용
[racdb1:/home/oracle]> echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/db_1

[racdb1:/home/oracle]> $ORACLE_HOME/bin/dbupgrade

Argument list for [/u01/app/oracle/product/19.0.0/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.7.0.0.0DBRU_LINUX.X64_200403]


/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/db_1]
/u01/app/oracle/product/19.0.0/db_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/db_1]

Analyzing file /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20200424102522]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200424102522/catupgrd_catcon_22590.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200424102522/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200424102522/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 2
Database Name         = racdb
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/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533/catupgrd_catcon_22590.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533]

Parallel SQL Process Count            = 4
Components in [racdb]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [APEX DV EM MGW ODM OLS WK]
DataBase Version      = 11.2.0.4.0

------------------------------------------------------
Phases [0-107]         Start Time:[2020_04_24 10:25:46]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [racdb] Files:1    Time: 121s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [racdb] Files:5    Time: 73s
Restart  Phase #:2    [racdb] Files:1    Time: 2s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [racdb] Files:19   Time: 27s
Restart  Phase #:4    [racdb] Files:1    Time: 2s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [racdb] Files:7    Time: 27s
*****************   Catproc Start   ****************
Serial   Phase #:6    [racdb] Files:1    Time: 17s
*****************   Catproc Types   ****************
Serial   Phase #:7    [racdb] Files:2    Time: 15s
Restart  Phase #:8    [racdb] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [racdb] Files:67   Time: 46s
Restart  Phase #:10   [racdb] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [racdb] Files:1    Time: 123s
Restart  Phase #:12   [racdb] Files:1    Time: 4s
**************   Catproc Procedures   **************
Parallel Phase #:13   [racdb] Files:94   Time: 21s
Restart  Phase #:14   [racdb] Files:1    Time: 1s
Parallel Phase #:15   [racdb] Files:121  Time: 29s
Restart  Phase #:16   [racdb] Files:1    Time: 2s
Serial   Phase #:17   [racdb] Files:22   Time: 7s
Restart  Phase #:18   [racdb] Files:1    Time: 3s
*****************   Catproc Views   ****************
Parallel Phase #:19   [racdb] Files:32   Time: 47s
Restart  Phase #:20   [racdb] Files:1    Time: 2s
Serial   Phase #:21   [racdb] Files:3    Time: 23s
Restart  Phase #:22   [racdb] Files:1    Time: 2s
Parallel Phase #:23   [racdb] Files:25   Time: 317s
Restart  Phase #:24   [racdb] Files:1    Time: 2s
Parallel Phase #:25   [racdb] Files:12   Time: 204s
Restart  Phase #:26   [racdb] Files:1    Time: 2s
Serial   Phase #:27   [racdb] Files:1    Time: 0s
Serial   Phase #:28   [racdb] Files:3    Time: 6s
Serial   Phase #:29   [racdb] Files:1    Time: 0s
Restart  Phase #:30   [racdb] Files:1    Time: 2s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [racdb] Files:1    Time: 2s
Restart  Phase #:32   [racdb] Files:1    Time: 1s
Serial   Phase #:34   [racdb] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [racdb] Files:294  Time: 32s
Serial   Phase #:36   [racdb] Files:1    Time: 0s
Restart  Phase #:37   [racdb] Files:1    Time: 1s
Serial   Phase #:38   [racdb] Files:6    Time: 8s
Restart  Phase #:39   [racdb] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [racdb] Files:3    Time: 92s
Restart  Phase #:41   [racdb] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [racdb] Files:13   Time: 238s
Restart  Phase #:43   [racdb] Files:1    Time: 1s
Parallel Phase #:44   [racdb] Files:11   Time: 21s
Restart  Phase #:45   [racdb] Files:1    Time: 2s
Parallel Phase #:46   [racdb] Files:3    Time: 3s
Restart  Phase #:47   [racdb] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [racdb] Files:1    Time: 15s
Restart  Phase #:49   [racdb] Files:1    Time: 2s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [racdb] Files:1    Time: 32s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [racdb] Files:1    Time: 3s
Restart  Phase #:52   [racdb] Files:1    Time: 3s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [racdb] Files:2    Time: 655s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [racdb] Files:1    Time: 1s
Serial   Phase #:56   [racdb] Files:3    Time: 38s
Serial   Phase #:57   [racdb] Files:3    Time: 6s
Parallel Phase #:58   [racdb] Files:10   Time: 6s
Parallel Phase #:59   [racdb] Files:25   Time: 9s
Serial   Phase #:60   [racdb] Files:4    Time: 14s
Serial   Phase #:61   [racdb] Files:1    Time: 0s
Serial   Phase #:62   [racdb] Files:32   Time: 10s
Serial   Phase #:63   [racdb] Files:1    Time: 0s
Parallel Phase #:64   [racdb] Files:6    Time: 10s
Serial   Phase #:65   [racdb] Files:2    Time: 32s
Serial   Phase #:66   [racdb] Files:3    Time: 113s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [racdb] Files:1    Time: 2s
Serial   Phase #:69   [racdb] Files:1    Time: 5s
Parallel Phase #:70   [racdb] Files:2    Time: 69s
Restart  Phase #:71   [racdb] Files:1    Time: 3s
Parallel Phase #:72   [racdb] Files:2    Time: 4s
Serial   Phase #:73   [racdb] Files:2    Time: 5s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [racdb] Files:1    Time: 1s
Serial   Phase #:76   [racdb] Files:1    Time: 51s
Serial   Phase #:77   [racdb] Files:2    Time: 6s
Restart  Phase #:78   [racdb] Files:1    Time: 1s
Serial   Phase #:79   [racdb] Files:1    Time: 34s
Restart  Phase #:80   [racdb] Files:1    Time: 1s
Parallel Phase #:81   [racdb] Files:3    Time: 100s
Restart  Phase #:82   [racdb] Files:1    Time: 2s
Serial   Phase #:83   [racdb] Files:1    Time: 12s
Restart  Phase #:84   [racdb] Files:1    Time: 1s
Serial   Phase #:85   [racdb] Files:1    Time: 17s
Restart  Phase #:86   [racdb] Files:1    Time: 2s
Parallel Phase #:87   [racdb] Files:4    Time: 175s
Restart  Phase #:88   [racdb] Files:1    Time: 3s
Serial   Phase #:89   [racdb] Files:1    Time: 5s
Restart  Phase #:90   [racdb] Files:1    Time: 2s
Serial   Phase #:91   [racdb] Files:2    Time: 24s
Restart  Phase #:92   [racdb] Files:1    Time: 1s
Serial   Phase #:93   [racdb] Files:1    Time: 3s
Restart  Phase #:94   [racdb] Files:1    Time: 2s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [racdb] Files:1    Time: 20s
Restart  Phase #:96   [racdb] Files:1    Time: 3s
***********   Final Component scripts    ***********
Serial   Phase #:97   [racdb] Files:1    Time: 4s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [racdb] Files:1    Time: 791s
*******************   Migration   ******************
Serial   Phase #:99   [racdb] Files:1    Time: 56s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [racdb] Files:1    Time: 2s
Serial   Phase #:101  [racdb] Files:1    Time: 0s
Serial   Phase #:102  [racdb] Files:1    Time: 63s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [racdb] Files:1    Time: 63s
****************   Summary report   ****************
Serial   Phase #:104  [racdb] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [racdb] Files:1    Time: 2s
Serial   Phase #:106  [racdb] Files:1    Time: 0s
Serial   Phase #:107  [racdb] Files:1     Time: 30s

------------------------------------------------------
Phases [0-107]         End Time:[2020_04_24 11:33:16]
------------------------------------------------------

Grand Total Time: 4054s

 LOG FILES: (/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/racdb/upgrade20200424102533/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:7m:34s]




Time Zone 업그레이드 수행

코드 블럭
titleUpgrade 모드로 기동 후 Time Zone 버전 조회
linenumberstrue
startup upgrade;
select * from V$TIMEZONE_FILE;
select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;


펼치기


서식 미적용
[racdb1:/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 24 11:34:32 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             822083584 bytes
Database Buffers         2382364672 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

SQL> col FILENAME form a20
SQL> select FILENAME, VERSION from V$TIMEZONE_FILE;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

SQL> select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32





코드 블럭
titleTime Zone 업그레이드 영향도 분석
linenumberstrue
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.





코드 블럭
titleTime Zone 업그레이드 윈도우 시작 및 재기동
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 Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             822083584 bytes
Database Buffers         2382364672 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.





코드 블럭
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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
DBMS_DST.UPGRADE_DATABASE : 0

PL/SQL procedure successfully completed.





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





코드 블럭
titleTime Zone 업그레이드 결과 확인
linenumberstrue
select PROPERTY_NAME, PROPERTY_VALUE
  from DATABASE_PROPERTIES
 where PROPERTY_NAME like 'DST_%'
 order by PROPERTY_NAME;


펼치기


서식 미적용
SQL> col PROPERTY_NAME form a30
SQL> col PROPERTY_VALUE form a20
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




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 스크립트 수행

코드 블럭
languagebash
titlepostupgrade_fixups 스크립트 수행
linenumberstrue
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/cfgtoollogs/racdb/preupgrade/postupgrade_fixups.sql


펼치기


서식 미적용
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/u01/app/oracle/cfgtoollogs/racdb/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-04-22 15:40:47
SQL> REM
SQL> REM    Source Database:         RACDB
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-04-18 08:12:18

For Source Database:     RACDB
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
------  ------------------------  ----------  --------------------------------
    4.  old_time_zones_exist      YES         None.
    5.  post_dictionary           YES         None.
    6.  post_fixed_objects        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 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.




객체 재컴파일

코드 블럭
titleInvalid 객체 및 컴포넌트 재컴파일
linenumberstrue
@?/rdbms/admin/utlrp.sql


펼치기


서식 미적용
SQL> select count(*) from DBA_OBJECTS where STATUS = 'INVALID';

  COUNT(*)
----------
      6614

SQL> set pages 20
SQL> col COMP_NAME form a35
SQL> col VERSION_FULL form a15
SQL> col STATUS form a10

SQL> select COMP_NAME, VERSION_FULL, STATUS from DBA_REGISTRY;

COMP_NAME                           VERSION_FULL    STATUS
----------------------------------- --------------- ----------
Oracle Database Catalog Views       19.7.0.0.0      UPGRADED
Oracle Database Packages and Types  19.7.0.0.0      UPGRADED
JServer JAVA Virtual Machine        19.7.0.0.0      UPGRADED
Oracle XDK                          19.7.0.0.0      UPGRADED
Oracle Database Java Packages       19.7.0.0.0      UPGRADED
Oracle Real Application Clusters    19.7.0.0.0      UPGRADED
OLAP Analytic Workspace             19.7.0.0.0      UPGRADED
Oracle Workspace Manager            19.7.0.0.0      UPGRADED
Oracle Text                         19.7.0.0.0      UPGRADED
Oracle XML Database                 19.7.0.0.0      UPGRADED
Oracle Multimedia                   19.7.0.0.0      UPGRADED
Spatial                             19.7.0.0.0      LOADING
Oracle OLAP API                     19.7.0.0.0      VALID

13 rows selected.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2020-04-24 11:47:42

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2020-04-24 11:59:02

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> select count(*) from DBA_OBJECTS where STATUS = 'INVALID';

  COUNT(*)
----------
         0

SQL> select COMP_NAME, VERSION_FULL, STATUS from DBA_REGISTRY;

COMP_NAME                           VERSION_FULL    STATUS
----------------------------------- --------------- ----------
Oracle Database Catalog Views       19.7.0.0.0      VALID
Oracle Database Packages and Types  19.7.0.0.0      VALID
JServer JAVA Virtual Machine        19.7.0.0.0      VALID
Oracle XDK                          19.7.0.0.0      VALID
Oracle Database Java Packages       19.7.0.0.0      VALID
Oracle Real Application Clusters    19.7.0.0.0      VALID
OLAP Analytic Workspace             19.7.0.0.0      VALID
Oracle Workspace Manager            19.7.0.0.0      VALID
Oracle Text                         19.7.0.0.0      VALID
Oracle XML Database                 19.7.0.0.0      VALID
Oracle Multimedia                   19.7.0.0.0      VALID
Spatial                             19.7.0.0.0      VALID
Oracle OLAP API                     19.7.0.0.0      VALID

13 rows selected.




클러스터 DB로 전환

코드 블럭
title클러스터 DB로 전환 및 재기동을 위한 중지
linenumberstrue
alter system set cluster_database=true scope=spfile;
shutdown immediate


펼치기


서식 미적용
SQL> col NAME form a20
SQL> col VALUE form a10
SQL> select NAME, VALUE from V$PARAMETER where name = 'cluster_database';

NAME                 VALUE
-------------------- ----------
cluster_database     FALSE

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0


서식 미적용
[racdb1:/home/oracle]> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.chad
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.net1.network
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.ons
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.proxy_advm
               OFFLINE OFFLINE      rac1                     STABLE
               OFFLINE OFFLINE      rac2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     Started,STABLE
      2        ONLINE  ONLINE       rac2                     Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.cvu
      1        ONLINE  ONLINE       rac1                     STABLE
ora.qosmserver
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.racdb.db
      1        ONLINE  OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------





코드 블럭
languagebash
title데이터베이스 설정 업데이트 및 재기동
linenumberstrue
$DB_HOME/bin/srvctl upgrade database -d racdb -o $DB_HOME
srvctl start database -d racdb


펼치기


서식 미적용
[racdb1:/home/oracle]> $DB_HOME/bin/srvctl upgrade database -d racdb -o $DB_HOME

[racdb1:/home/oracle]> srvctl start database -d racdb


서식 미적용
[racdb1:/home/oracle]> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.chad
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.net1.network
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.ons
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.proxy_advm
               OFFLINE OFFLINE      rac1                     STABLE
               OFFLINE OFFLINE      rac2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.CRS.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     Started,STABLE
      2        ONLINE  ONLINE       rac2                     Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       rac1                     STABLE
      2        ONLINE  ONLINE       rac2                     STABLE
ora.cvu
      1        ONLINE  ONLINE       rac1                     STABLE
ora.qosmserver
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.racdb.db
      1        ONLINE  ONLINE       rac1                     Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /db_1,STABLE
      2        ONLINE  ONLINE       rac2                     Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /db_1,STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------




APEX 설치 (옵션)

코드 블럭
title19c에 호환되는 APEX 설치
linenumberstrue
@?/apex/catapx.sql <APEX 관리자 암호> SYSAUX SYSAUX TEMP /i/ NONE