오라클 12.2.0.1 -> 19.22.0.0
0.. 먼저 19C 엔진을 설치하고 최신 RU 패치 까지 진행 한다.
기존 12C 설치 홈
export ORACLE_HOME=$ORACLE_BASE/product/12C/db_1
신규 19C 설치 홈
export ORACLE_HOME=$ORACLE_BASE/product/19C/db_1
[oracle@ORA12C ~]$ opatch lspatches
35926646;OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)
35943157;Database Release Update : 19.22.0.0.240116 (35943157)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
패치가 끝나니 autoupgrade.jar도 최신 파일로 업그레이드 된다.
1,config 파일 샘플 생성 및 자성
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
cp sample_config.cfg config.cfg
vi config.cfg
#
# Sample config file for AutoUpgrade
#
# build version 23.4.230921
# build date 2023/09/21 14:43:05 -0400
#
#
# Global configurations
#
# This directory will include the following:
# (1) AutoUpgrade''s global directory
# (2) Any logs, not directly tied to a job
# (3) Config files
# (4) progress.json and status.json
global.autoupg_log_dir=/home/oracle/upg_logs
#
# Database number 1 - Full DB/CDB upgrade
#
upg1.log_dir=/home/oracle/upg_logs/aracdb # Path of the log directory for the upgrade job
upg1.sid=ORA12C # ORACLE_SID of the source DB/CDB
upg1.source_home=/u01/app/oracle/product/12C/db_1 # Path of the source ORACLE_HOME
upg1.target_home=/u01/app/oracle/product/19C/db_1 # Path of the target ORACLE_HOME
upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]
upg1.upgrade_node=ora12c # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost''
#upg1.run_utlrp=[yes|no] # Optional. Whether or not to run utlrp after upgrade
#upg1.timezone_upg=[yes|no] # Optional. Whether or not to run the timezone upgrade
upg1.target_version=19 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
2. 초기 분석 작업
[oracle@aracdb ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode analyze
AutoUpgrade 23.4.230921 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
로그 확인 필요!!!!
vi /home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
[Stage Name] PRECHECKS
[Status] FAILURE
[Start Time] 2024-02-07 17:07:13
[Duration]
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/100/prechecks
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/100/prechecks/ora12c_preupgrade.log
Check failed for ORA12C, manual intervention needed for the below checks
[FLASH_RECOVERY_AREA_SETUP, ARCHIVE_MODE_ON]
원인:
이유: 데이터베이스 검사와 관련하여 /home/oracle/upg_logs/aracdb/ORA12C/100/prechecks에 실패 세부정보가 있습니다.
조치: [MANUAL]
정보: 반환 상태가 ERROR입니다.
ExecutionError: 아니오
오류 메시지: 없음
에러 화인 기존 12C 환경에서 FLASH_RECOVERY_AREA_SETUP, ARCHIVE_MODE_ON
설정을 해줘야 한다.
아카이이브 모드 설정 완료
FRA 영역 설정 완료
fra 경로 사이즈 설정
===============================================
SQL> alter system set db_recovery_file_dest_size = 10G;
System altered.
fra 경로 설정
SQL> alter system set db_recovery_file_dest = '/oradata/fra';
System altered.
===============================================
다시 초기 분석 시작
[oracle@ORA12C ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode analyze
AutoUpgrade 23.4.230921 launched with default internal options
구성 파일 처리 중...
+--------------------------------+
| 자동 업그레이드 실행 시작 중 |
+--------------------------------+
1 Non-CDB(s) will be analyzed
콘솔 명령을 나열하려면 'help'을(를) 입력하십시오.
upg> status
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> /
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> /
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> status
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> ljs
알 수 없는 CMD: ljs
upg> status
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> /
Config
User configuration file [/home/oracle/config.txt]
General logs location [/home/oracle/upg_logs/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
Jobs Summary
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total Containers being processed [0]
Jobs finished successfully [0]
Jobs finished/stopped [0]
Jobs in progress [1]
Progress
+---+---------------------------------------------------------+
|Job| Progress|
+---+---------------------------------------------------------+
|101|[||||||||||||||||| ] 33 %|
+---+---------------------------------------------------------+
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
로그 확인 완료
[oracle@ORA12C ~]$ cat /home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
==========================================
Autoupgrade Summary Report
==========================================
[Date] Wed Feb 07 17:28:14 KST 2024
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] ORA12C
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 19.22.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2024-02-07 17:27:23
[Duration]
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/101/prechecks
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/101/prechecks/ora12c_preupgrade.log
Check passed and no manual intervention needed
------------------------------------------
3. 사전 작업
[oracle@aracdb ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode fixups
AutoUpgrade 23.4.230921 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj -p
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 103| aracdb|PRECHECKS|EXECUTING|RUNNING| 14:44:35| 0s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 103| aracdb|PRECHECKS|EXECUTING|RUNNING| 14:44:35| 0s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+----------+-------+-------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------+
| 103| aracdb|PREFIXUPS|EXECUTING|RUNNING| 14:44:35| 0s ago| |
+----+-------+---------+---------+-------+----------+-------+-------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+----------+-------+------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+------------------+
| 103| aracdb|PREFIXUPS|EXECUTING|RUNNING| 14:44:35| 1s ago|Refreshing DB info|
+----+-------+---------+---------+-------+----------+-------+------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+----------+-------+------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+------------------+
| 103| aracdb|PREFIXUPS|EXECUTING|RUNNING| 14:44:35| 0s ago|Refreshing DB info|
+----+-------+---------+---------+-------+----------+-------+------------------+
Total jobs 1
upg> /Job 103 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
로그 확인 완료
==========================================
Autoupgrade Summary Report
==========================================
[Date] Wed Feb 07 17:32:40 KST 2024
[Number of Jobs] 1
==========================================
[Job ID] 102
==========================================
[DB Name] ORA12C
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 19.22.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2024-02-07 17:30:47
[Duration] 0:00:10
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/102/prechecks
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/102/prechecks/ora12c_preupgrade.log
Check passed and no manual intervention needed
------------------------------------------
[Stage Name] PREFIXUPS
[Status] SUCCESS
[Start Time] 2024-02-07 17:30:58
[Duration]
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/102/prefixups
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/102/prefixups/prefixups.html
------------------------------------------
이상 없음
4. 업그레이드
[oracle@ORA12C ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config config.txt -mode deploy
AutoUpgrade 23.4.230921 launched with default internal options
구성 파일 처리 중...
+--------------------------------+
| 자동 업그레이드 실행 시작 중 |
+--------------------------------+
1 Non-CDB(s) will be processed
콘솔 명령을 나열하려면 'help'을(를) 입력하십시오.
upg> lsj -p
+-----+-------+---------+---------+-------+----------+-------+----------------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+----------------------------+
| 103| ORA12C|PRECHECKS|EXECUTING|RUNNING| 17:34:32| 4s ago|Loading database information|
+-----+-------+---------+---------+-------+----------+-------+----------------------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+-------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+-------+
| 103| ORA12C|PREFIXUPS|EXECUTING|RUNNING| 17:34:32| 0s ago| |
+-----+-------+---------+---------+-------+----------+-------+-------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+------------------+
| 103| ORA12C|PREFIXUPS|EXECUTING|RUNNING| 17:34:32| 4s ago|Refreshing DB info|
+-----+-------+---------+---------+-------+----------+-------+------------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+------------------+
| 103| ORA12C|PREFIXUPS|EXECUTING|RUNNING| 17:34:32| 0s ago|Refreshing DB info|
+-----+-------+---------+---------+-------+----------+-------+------------------+
총 작업 1
upg> /
+-----+-------+-----+---------+-------+----------+-------+---------+
|작업 번호|DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+-----+---------+-------+----------+-------+---------+
| 103| ORA12C|DRAIN|EXECUTING|RUNNING| 17:34:32|28s ago|데이터베이스 종료|
+-----+-------+-----+---------+-------+----------+-------+---------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+-------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+-------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32| 0s ago|Running|
+-----+-------+---------+---------+-------+----------+-------+-------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+-----------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+-----------------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32| 2s ago|0%을(를) 업그레이드했습니다.|
+-----+-------+---------+---------+-------+----------+-------+-----------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+--------+-----------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+--------+-----------------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32|118s ago|5%을(를) 업그레이드했습니다.|
+-----+-------+---------+---------+-------+----------+--------+-----------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+------------------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32| 0s ago|10%을(를) 업그레이드했습니다.|
+-----+-------+---------+---------+-------+----------+-------+------------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+------------------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32| 5s ago|21%을(를) 업그레이드했습니다.|
+-----+-------+---------+---------+-------+----------+-------+------------------+
총 작업 1
upg> /
+-----+-------+---------+---------+-------+----------+-------+------------------+
|작업 번호|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+-----+-------+---------+---------+-------+----------+-------+------------------+
| 103| ORA12C|DBUPGRADE|EXECUTING|RUNNING| 17:34:32|63s ago|21%을(를) 업그레이드했습니다.|
+-----+-------+---------+---------+-------+----------+-------+------------------+
총 작업 1
upg> Job 103 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
---- 더 이상 필요하지 않다고 생각되면 편리하게 GRP를 삭제하십시오. ----
ORA12C에서 GRP 삭제: 복원 지점 AUTOUPGRADE_9212_ORA12C122010 삭제
Please check the summary report at:
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
로그 확인
[oracle@ORA12C ~]$ cat /home/oracle/upg_logs/cfgtoollogs/upgrade/auto/status/status.log
==========================================
Autoupgrade Summary Report
==========================================
[Date] Wed Feb 07 18:07:33 KST 2024
[Number of Jobs] 1
==========================================
[Job ID] 103
==========================================
[DB Name] ORA12C
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 19.22.0.0.0
------------------------------------------
[Stage Name] GRP
[Status] SUCCESS
[Start Time] 2024-02-07 17:34:33
[Duration] 0:00:01
[Detail] Please drop the following GRPs after Autoupgrade completes:
AUTOUPGRADE_9212_ORA12C122010
------------------------------------------
[Stage Name] PREUPGRADE
[Status] SUCCESS
[Start Time] 2024-02-07 17:34:34
[Duration] 0:00:00
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/preupgrade
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2024-02-07 17:34:34
[Duration] 0:00:11
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/prechecks
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/103/prechecks/ora12c_preupgrade.log
Check passed and no manual intervention needed
------------------------------------------
[Stage Name] PREFIXUPS
[Status] SUCCESS
[Start Time] 2024-02-07 17:34:46
[Duration] 0:00:09
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/prefixups
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/103/prefixups/prefixups.html
------------------------------------------
[Stage Name] DRAIN
[Status] SUCCESS
[Start Time] 2024-02-07 17:34:56
[Duration] 0:00:30
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/drain
------------------------------------------
[Stage Name] DBUPGRADE
[Status] SUCCESS
[Start Time] 2024-02-07 17:35:26
[Duration] 0:25:50
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/dbupgrade
------------------------------------------
[Stage Name] POSTCHECKS
[Status] SUCCESS
[Start Time] 2024-02-07 18:01:34
[Duration] 0:00:00
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/postchecks
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/103/postchecks/ora12c_postupgrade.log
Check passed and no manual intervention needed
------------------------------------------
[Stage Name] POSTFIXUPS
[Status] SUCCESS
[Start Time] 2024-02-07 18:01:35
[Duration] 0:04:36
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/postfixups
[Detail] /home/oracle/upg_logs/aracdb/ORA12C/103/postfixups/postfixups.html
------------------------------------------
[Stage Name] POSTUPGRADE
[Status] SUCCESS
[Start Time] 2024-02-07 18:06:12
[Duration] 0:01:20
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/postupgrade
------------------------------------------
[Stage Name] SYSUPDATES
[Status] SUCCESS
[Start Time] 2024-02-07 18:07:33
[Duration]
[Log Directory] /home/oracle/upg_logs/aracdb/ORA12C/103/sysupdates
------------------------------------------
Summary: /home/oracle/upg_logs/aracdb/ORA12C/103/dbupgrade/upg_summary.log
[oracle@ORA12C ~]$ .
5. 업그레이드 완료 후 인벨리드 오브젝트 처리
[oracle@ORA12C ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 7 18:40:15 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> col instance_name form a20
SQL> col version form a20
SQL> col status form a10
SQL> select instance_name, version, status from v$instance;
INSTANCE_NAME VERSION STATUS
-------------------- -------------------- ----------
ORA12C 19.0.0.0.0 OPEN
SQL> set lines 200
SQL> set pages 100
SQL> col comp_id form a10
SQL> col comp_name form a50
SQL> col version_full form a20
SQL> col status form a20
SQL> select COMP_ID, COMP_NAME, VERSION_FULL, STATUS from DBA_REGISTRY;
COMP_ID COMP_NAME VERSION_FULL STATUS
---------- -------------------------------------------------- -------------------- --------------------
CATALOG Oracle Database Catalog Views 19.22.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.22.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.22.0.0.0 VALID
XML Oracle XDK 19.22.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.22.0.0.0 VALID
APS OLAP Analytic Workspace 19.22.0.0.0 VALID
RAC Oracle Real Application Clusters 19.22.0.0.0 OPTION OFF
XDB Oracle XML Database 19.22.0.0.0 VALID
OWM Oracle Workspace Manager 19.22.0.0.0 VALID
CONTEXT Oracle Text 19.22.0.0.0 VALID
ORDIM Oracle Multimedia 19.22.0.0.0 VALID
SDO Spatial 19.22.0.0.0 VALID
XOQ Oracle OLAP API 19.22.0.0.0 VALID
OLS Oracle Label Security 19.22.0.0.0 VALID
DV Oracle Database Vault 19.22.0.0.0 VALID
15 rows selected.
SQL> @?/rdbms/admin/utlrp
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2024-02-07 18:44:24
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 2024-02-07 18:44:25
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 DBMS_DST.GET_LATEST_TIMEZONE_VERSION from dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
42
SQL> col version for a24
SQL> col version for 999999
SQL> select FILENAME, VERSION from V$TIMEZONE_FILE;
FILENAME VERSION
-------------------- -------
timezlrg_42.dat 42
참조 :
AutoUpgrade Tool (문서 ID 2485457.1)
https://dataforum.io/pages/viewpage.action?pageId=57573378#AutoUpgrade%EB%A5%BC%EC%82%AC%EC%9A%A9%ED%95%98%EC%97%AC11.2.0.4%EC%97%90%EC%84%9C19c%EB%A1%9C%EC%97%85%EA%B7%B8%EB%A0%88%EC%9D%B4%EB%93%9C%ED%95%98%EA%B8%B0-%EC%82%AC%EC%A0%84%EC%A4%80%EB%B9%84
