OS 환경 : Oracle Linux 6.8, 8.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 19.27.0.0
방법 : 오라클 11gR2 to 19c 업그레이드 시 preupgrade.jar 스크립트 사용법
본문에서는 오라클 11gR2 to 19c 업그레이드 전후 점검을 위해 사용하는 preupgrade.jar 스크립트에 대해 설명함
이 스크립트는 업그레이드 전후에 점검해야할 부분을 알려주고 스크립트까지 제공해줌
동일 서버의 경우 아래와 같이 간편하게 실행이 가능함
|
1
|
$ 대상$ORACLE_HOME(11gR2)/jdk/bin/java -jar 타겟$ORACLE_HOME(19c)/preupgrade.jar TERMINAL TEXT
|
884522.1에서 preupgrade.jar 파일만 가져온 경우 아래와 같이 수행하면됨
*테스트결과 19c $ORACLE_HOME에서 preupgrade.jar 파일만 가져와서 실행해도 정상적으로 실행되었지만,
884522.1에 따르면 다운받은 preupgrade_19_cbuild_13_lf.zip 파일을 11gR2 $ORACLE_HOME/rdbms/admin에 가져간뒤 압축을 해제해서 쓰라고 되어있음
|
1
2
3
4
5
6
|
# chown oracle:dba preupgrade_19_cbuild_13_lf.zip
# mv preupgrade.jar 대상$ORACLE_HOME(11gR2)/rdbms/admin/
# su - oracle
$ cd 대상$ORACLE_HOME(11gR2)/rdbms/admin/
$ unzip preupgrade_19_cbuild_13_lf.zip
$ 대상$ORACLE_HOME(11gR2)/jdk/bin/java -jar ./preupgrade.jar TERMINAL TEXT
|
출력 결과
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
$ $ORACLE_HOME/jdk/bin/java -jar ./preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2025-06-22T11:08:07
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORA11DB
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: No Patch Bundle applied
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
This action may be done now or when starting the database in upgrade mode
using the 19 ORACLE HOME.
Parameter Currently 19 minimum
--------- --------- ------------------
processes 150 300
The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.
2. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
3. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
INFORMATION ONLY
================
4. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
CATALOG 11.2.0.4.0 VALID SYS
CATJAVA 11.2.0.4.0 VALID SYS
CATPROC 11.2.0.4.0 VALID SYS
EXF 11.2.0.4.0 VALID EXFSYS
JAVAVM 11.2.0.4.0 VALID SYS
OWM 11.2.0.4.0 VALID WMSYS
RAC 11.2.0.4.0 VALID SYS
XML 11.2.0.4.0 VALID SYS
Review the information before upgrading.
5. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
TEMP 20 MB 150 MB
UNDOTBS1 355 MB 436 MB
Minimum tablespace sizes for upgrade are estimates.
6. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
Database Oracle home to remove both EXF and RUL.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
Starting with Oracle Database release 12.1, the Expression Filter (EXF)
and Database Rules Manager (RUL) features are desupported, and are
removed during the upgrade process. This step can be manually performed
before the upgrade to reduce downtime.
7. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
2965 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : +RECO
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
8. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
9. Here is a count of invalid objects by users:
User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORA11DB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
10. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 14 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
11. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
12. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORA11DB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/postupgrade_fixups.sq
l
==================
PREUPGRADE SUMMARY
==================
/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade.log
/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade_fixups.sql
/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2025-06-22T11:08:08
|
스크립트 수행이 완료됨
업그레이드 전후에 수행해야할 여러가지 제안사항들이 나옴
아래는 해당 정보를 요약해놓은 글임
1. 기본 정보
현재 DB 버전: 11.2.0.4.0
타겟 버전: 19.0.0.0.0
DB 이름: ORA11DB
Edition: EE (Enterprise Edition)
ARCHIVELOG 모드: 활성화됨
Timezone File: 14 (19c는 32를 기본으로 사용함(19.27은 44 사용))
2. 업그레이드 대상 컴포넌트
모든 주요 Oracle 컴포넌트는 업그레이드 대상이며, 현재 상태는 모두 VALID함:
Oracle Server, Java VM, XDK, RAC, Workspace Manager 등
3. 업그레이드 전 필수 조치사항 (REQUIRED ACTIONS)
없음
4. 업그레이드 전 권장 조치사항 (RECOMMENDED ACTIONS)
processes 파라미터 증가
파라미터 현재값 19c 최소값
processes 150 300
업그레이드 중 최소 300으로 설정이 필요. 업그레이드 후 조정 가능.
Dictionary Statistics 수집(preupgrade_fixups 스크립트 수행시 자동 적용)
|
1
|
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
|
오라클 내부 딕셔너리 테이블의 통계 수집. 옵티마이저 성능 향상 목적(사용량이 적은 시간대에 수행)
Fixed Object 통계 수집(preupgrade_fixups 스크립트 수행시 자동 적용)
|
1
|
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
|
x$ 테이블 등 고정 오브젝트 통계. 성능에 큰 영향.
등록된 모든 컴포넌트 확인
CATALOG, CATJAVA, CATPROC 등 모두 VALID 상태
테이블스페이스 최소 크기 요구사항
Tablespace 현재 크기 최소 필요 크기
TEMP 20MB 150MB
UNDOTBS1 355MB 436MB
업그레이드 중 공간 부족하지 않도록 확보 필요
EXF/RUL 제거
@타겟$ORACLE_HOME(19c)/rdbms/admin/catnoexf.sql 스크립트 수행
Expression Filter, Rule Manager는 12c부터 지원 중단 => 업그레이드 전 제거 권장(권장하긴 지만 업그레이드 중에 자동으로 실행됨)
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/scripts-upgrade-oracle-database.html#GUID-7935BC31-8818-4757-A6E7-A180C3E95A62
Archive Log 공간 확보
최소 2965MB 확보 필요
대상: +RECO
RMAN Recovery Catalog 버전 확인
Recovery Catalog가 있다면, RMAN 클라이언트와 버전 호환성 확인 필요
INVALID 객체 없음
5. 업그레이드 전 자동 수정 스크립트
|
1
|
@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade_fixups.sql
|
6. 업그레이드 후 권장 조치사항
Timezone File 업그레이드
현재 14 → 19c는 32 사용(19.27은 44 사용)
DBMS_DST 패키지를 통해 업그레이드 권장
Dictionary Statistics 재수집(postupgrade_fixups 스크립트 수행시 자동 적용)
|
1
|
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
|
Fixed Object Statistics 재수집 (대표 workload 후)
|
1
|
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
|
7. 업그레이드 후 자동 수정 스크립트
|
1
|
@/oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/postupgrade_fixups.sql
|
8. 요약
업그레이드 전 preupgrade_fixups.sql 실행
업그레이드 후 postupgrade_fixups.sql 실행
로그 파일 : /oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade.log
참고용. preupgrade_fixups 스크립트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
|
$ cat /oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/preupgrade_fixups.sql
REM
REM Oracle PRE-Upgrade Fixup Script
REM
REM Auto-Generated by: Oracle Preupgrade Script
REM Version: 19.0.0.0.0 Build: 13
REM Generated on: 2025-06-22 11:08:04
REM
REM Source Database: ORA11DB
REM Source Database Version: 11.2.0.4.0
REM For Upgrade to Version: 19.0.0.0.0
REM
REM
REM Setup Environment
REM
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
DECLARE
db_name V$DATABASE.NAME%TYPE;
con_name VARCHAR2(128);
fixup_result BOOLEAN := TRUE;
recyclebin_cleaned BOOLEAN := TRUE;
check_result_xml VARCHAR2(32767);
BEGIN
--
-- Gather the current execution context
--
EXECUTE IMMEDIATE
'SELECT name FROM v$database' INTO db_name;
EXECUTE IMMEDIATE
'SELECT dbms_preup.get_con_name FROM sys.dual' INTO con_name;
--
-- Now validate that the current execution context
-- matches the context when this script was generated.
--
IF db_name <> 'ORA11DB' THEN
dbms_output.put_line('WARNING - This script was generated ' ||
'for database ORA11DB.');
END IF; -- if db_name is ORA11DB
dbms_output.put_line('Executing Oracle PRE-Upgrade Fixup Script');
dbms_output.put_line(' ');
dbms_output.put_line('Auto-Generated by: Oracle Preupgrade Script');
dbms_output.put_line(' Version: 19.0.0.0.0 Build: 13');
dbms_output.put_line('Generated on: 2025-06-22 11:08:04 ');
dbms_output.put_line(' ');
dbms_output.put_line('For Source Database: ORA11DB');
dbms_output.put_line('Source Database Version: 11.2.0.4.0');
dbms_output.put_line('For Upgrade to Version: 19.0.0.0.0');
dbms_output.put_line(' ');
-- Starting DB ORA11DB
dbms_output.put_line('Preup Preupgrade ');
dbms_output.put_line('Action Issue Is ');
dbms_output.put_line('Number Preupgrade Check Name Remedied Further DBA Action');
dbms_output.put_line('------ ------------------------ ---------- --------------------------------');
--
-- CHECK/FIXUP name: parameter_min_val
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
-- This action may be done now or when starting the database in upgrade mode
-- using the 19 ORACLE HOME.
--
-- Parameter Currently 19 minimum
-- --------- --------- ------------------
-- processes 150 300
--
-- The database upgrade process requires certain initialization parameters
-- to meet minimum values. The Oracle upgrade process itself has minimum
-- values which may be higher and are marked with an asterisk. After
-- upgrading, those asterisked parameter values may be reset if needed.
--
fixup_result := dbms_preup.run_fixup('parameter_min_val',1) AND fixup_result;
--
-- CHECK/FIXUP name: dictionary_stats
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 2. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
-- upgrade in off-peak time using:
--
-- EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
--
-- Dictionary statistics do not exist or are stale (not up-to-date).
--
-- Dictionary statistics help the Oracle optimizer find efficient SQL
-- execution plans and are essential for proper upgrade timing. Oracle
-- recommends gathering dictionary statistics in the last 24 hours before
-- database upgrade.
--
-- For information on managing optimizer statistics, refer to the 11.2.0.4
-- Oracle Database Performance Tuning Guide.
--
fixup_result := dbms_preup.run_fixup('dictionary_stats',2) AND fixup_result;
--
-- CHECK/FIXUP name: pre_fixed_objects
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 3. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
-- the command:
--
-- EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
--
-- None of the fixed object tables have had stats collected.
--
-- Gathering statistics on fixed objects, if none have been gathered yet, is
-- recommended prior to upgrading.
--
-- For information on managing optimizer statistics, refer to the 11.2.0.4
-- Oracle Database Performance Tuning Guide.
--
fixup_result := dbms_preup.run_fixup('pre_fixed_objects',3) AND fixup_result;
--
-- CHECK/FIXUP name: component_info
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 4. Here are ALL the components in this database registry:
--
-- Component Current Current Original Previous Component
-- CID Version Status Version Version Schema
-- --------- ----------- ----------- ----------- ----------- -----------
-- CATALOG 11.2.0.4.0 VALID SYS
-- CATJAVA 11.2.0.4.0 VALID SYS
-- CATPROC 11.2.0.4.0 VALID SYS
-- EXF 11.2.0.4.0 VALID EXFSYS
-- JAVAVM 11.2.0.4.0 VALID SYS
-- OWM 11.2.0.4.0 VALID WMSYS
-- RAC 11.2.0.4.0 VALID SYS
-- XML 11.2.0.4.0 VALID SYS
--
-- Review the information before upgrading.
--
fixup_result := dbms_preup.run_fixup('component_info',4) AND fixup_result;
--
-- CHECK/FIXUP name: tablespaces_info
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 5. To help you keep track of your tablespace allocations, the following
-- AUTOEXTEND tablespaces are expected to successfully EXTEND during the
-- upgrade process.
--
-- Min Size
-- Tablespace Size For Upgrade
-- ---------- ---------- -----------
-- TEMP 20 MB 150 MB
-- UNDOTBS1 355 MB 436 MB
--
-- Minimum tablespace sizes for upgrade are estimates.
--
fixup_result := dbms_preup.run_fixup('tablespaces_info',5) AND fixup_result;
--
-- CHECK/FIXUP name: exf_rul_exists
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 6. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
-- Database Oracle home to remove both EXF and RUL.
--
-- Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
--
-- Starting with Oracle Database release 12.1, the Expression Filter (EXF)
-- and Database Rules Manager (RUL) features are desupported, and are
-- removed during the upgrade process. This step can be manually performed
-- before the upgrade to reduce downtime.
--
fixup_result := dbms_preup.run_fixup('exf_rul_exists',6) AND fixup_result;
--
-- CHECK/FIXUP name: min_archive_dest_size
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 7. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
-- 2965 MB of archived logs. Check alert log during the upgrade that there
-- is no write error to the destination due to lack of disk space.
--
-- Archiving cannot proceed if the archive log destination is full during
-- upgrade.
--
-- Archive Log Destination:
-- Parameter : LOG_ARCHIVE_DEST_1
-- Destination : +RECO
--
-- The database has archiving enabled. The upgrade process will need free
-- disk space in the archive log destination(s) to generate archived logs to.
--
fixup_result := dbms_preup.run_fixup('min_archive_dest_size',7) AND fixup_result;
--
-- CHECK/FIXUP name: rman_recovery_version
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 8. Check the Oracle Backup and Recovery User's Guide for information on how
-- to manage an RMAN recovery catalog schema.
--
-- If you are using a version of the recovery catalog schema that is older
-- than that required by the RMAN client version, then you must upgrade the
-- catalog schema.
--
-- It is good practice to have the catalog schema the same or higher version
-- than the RMAN client version you are using.
--
fixup_result := dbms_preup.run_fixup('rman_recovery_version',8) AND fixup_result;
--
-- CHECK/FIXUP name: invalid_all_obj_info
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 9. Here is a count of invalid objects by users:
--
-- User Name Number of INVALID Objects
-- --------------------------- -------------------------
-- None None
--
-- Review the information before upgrading.
--
fixup_result := dbms_preup.run_fixup('invalid_all_obj_info',9) AND fixup_result;
--
-- clean recyclebin in case any of the above fixups left stuff there.
--
recyclebin_cleaned := dbms_preup.run_fixup_only('purge_recyclebin', check_result_xml);
IF fixup_result = FALSE THEN
dbms_output.put_line('');
dbms_output.put_line('The fixup scripts have been run and resolved what they can. However,');
dbms_output.put_line('there are still issues originally identified by the preupgrade that');
dbms_output.put_line('have not been remedied and are still present in the database.');
dbms_output.put_line('Depending on the severity of the specific issue, and the nature of');
dbms_output.put_line('the issue itself, that could mean that your database is not ready');
dbms_output.put_line('for upgrade. To resolve the outstanding issues, start by reviewing');
dbms_output.put_line('the preupgrade_fixups.sql and searching it for the name of');
dbms_output.put_line('the failed CHECK NAME or Preupgrade Action Number listed above.');
dbms_output.put_line('There you will find the original corresponding diagnostic message');
dbms_output.put_line('from the preupgrade which explains in more detail what still needs');
dbms_output.put_line('to be done.');
END IF;
END;
/
|
참고용. postupgrade_fixups 스크립트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
$ cat /oracle/app/oracle/cfgtoollogs/ORA11DB/preupgrade/postupgrade_fixups.sql
REM
REM Oracle POST-Upgrade Fixup Script
REM
REM Auto-Generated by: Oracle Preupgrade Script
REM Version: 19.0.0.0.0 Build: 13
REM Generated on: 2025-06-22 11:08:07
REM
REM Source Database: ORA11DB
REM Source Database Version: 11.2.0.4.0
REM For Upgrade to Version: 19.0.0.0.0
REM
REM
REM Setup Environment
REM
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
ALTER SESSION SET "_oracle_script" = TRUE;
VARIABLE admin_preupgrade_dir VARCHAR2(512);
REM
REM point PREUPGRADE_DIR to OH/rdbms/admin
REM
DECLARE
oh VARCHAR2(4000);
BEGIN
dbms_system.get_env('ORACLE_HOME', oh);
:admin_preupgrade_dir := dbms_assert.enquote_literal(oh || '/rdbms/admin');
END;
/
DECLARE
command varchar2(4000);
BEGIN
command := 'CREATE OR REPLACE DIRECTORY PREUPGRADE_DIR AS ' || :admin_preupgrade_dir;
EXECUTE IMMEDIATE command;
END;
/
@?/rdbms/admin/dbms_registry_extended.sql
REM
REM Execute the preupgrade_package from the PREUPGRADE_DIR
REM This is needed because the preupgrade_messages.properties file
REM lives there too, and is read by preupgrade_package.sql using
REM the PREUPGRADE_DIR.
REM
COLUMN directory_path NEW_VALUE admin_preupgrade_dir NOPRINT;
select directory_path from dba_directories where directory_name='PREUPGRADE_DIR';
set concat '.';
@&admin_preupgrade_dir./preupgrade_package.sql
COLUMN directory_path CLEAR;
DECLARE
db_name V$DATABASE.NAME%TYPE;
con_name VARCHAR2(128);
fixup_result BOOLEAN := TRUE;
BEGIN
--
-- Gather the current execution context
--
EXECUTE IMMEDIATE
'SELECT name FROM v$database' INTO db_name;
EXECUTE IMMEDIATE
'SELECT dbms_preup.get_con_name FROM sys.dual' INTO con_name;
--
-- Now validate that the current execution context
-- matches the context when this script was generated.
--
IF db_name <> 'ORA11DB' THEN
dbms_output.put_line('WARNING - This script was generated ' ||
'for database ORA11DB.');
END IF; -- if db_name is ORA11DB
dbms_output.put_line('Executing Oracle POST-Upgrade Fixup Script');
dbms_output.put_line(' ');
dbms_output.put_line('Auto-Generated by: Oracle Preupgrade Script');
dbms_output.put_line(' Version: 19.0.0.0.0 Build: 13');
dbms_output.put_line('Generated on: 2025-06-22 11:08:07 ');
dbms_output.put_line(' ');
dbms_output.put_line('For Source Database: ORA11DB');
dbms_output.put_line('Source Database Version: 11.2.0.4.0');
dbms_output.put_line('For Upgrade to Version: 19.0.0.0.0');
dbms_output.put_line(' ');
-- Starting DB ORA11DB
dbms_output.put_line('Preup Preupgrade ');
dbms_output.put_line('Action Issue Is ');
dbms_output.put_line('Number Preupgrade Check Name Remedied Further DBA Action');
dbms_output.put_line('------ ------------------------ ---------- --------------------------------');
--
-- CHECK/FIXUP name: old_time_zones_exist
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 10. Upgrade the database time zone file using the DBMS_DST package.
--
-- The database is using time zone file version 14 and the target 19 release
-- ships with time zone file version 32.
--
-- Oracle recommends upgrading to the desired (latest) version of the time
-- zone file. For more information, refer to "Upgrading the Time Zone File
-- and Timestamp with Time Zone Data" in the 19 Oracle Database
-- Globalization Support Guide.
--
fixup_result := dbms_preup.run_fixup('old_time_zones_exist',10) AND fixup_result;
--
-- CHECK/FIXUP name: post_dictionary
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 11. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
-- command:
--
-- EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
--
-- Oracle recommends gathering dictionary statistics after upgrade.
--
-- Dictionary statistics provide essential information to the Oracle
-- optimizer to help it find efficient SQL execution plans. After a database
-- upgrade, statistics need to be re-gathered as there can now be tables
-- that have significantly changed during the upgrade or new tables that do
-- not have statistics gathered yet.
--
fixup_result := dbms_preup.run_fixup('post_dictionary',11) AND fixup_result;
--
-- CHECK/FIXUP name: post_fixed_objects
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- 12. Gather statistics on fixed objects after the upgrade and when there is a
-- representative workload on the system using the command:
--
-- EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
--
-- This recommendation is given for all preupgrade runs.
--
-- Fixed object statistics provide essential information to the Oracle
-- optimizer to help it find efficient SQL execution plans. Those
-- statistics are specific to the Oracle Database release that generates
-- them, and can be stale upon database upgrade.
--
-- For information on managing optimizer statistics, refer to the 11.2.0.4
-- Oracle Database Performance Tuning Guide.
--
fixup_result := dbms_preup.run_fixup('post_fixed_objects',12) AND fixup_result;
IF fixup_result = FALSE THEN
dbms_output.put_line('');
dbms_output.put_line('The fixup scripts have been run and resolved what they can. However,');
dbms_output.put_line('there are still issues originally identified by the preupgrade that');
dbms_output.put_line('have not been remedied and are still present in the database.');
dbms_output.put_line('Depending on the severity of the specific issue, and the nature of');
dbms_output.put_line('the issue itself, that could mean that your database upgrade is not');
dbms_output.put_line('fully complete. To resolve the outstanding issues, start by reviewing');
dbms_output.put_line('the postupgrade_fixups.sql and searching it for the name of');
dbms_output.put_line('the failed CHECK NAME or Preupgrade Action Number listed above.');
dbms_output.put_line('There you will find the original corresponding diagnostic message');
dbms_output.put_line('from the preupgrade which explains in more detail what still needs');
dbms_output.put_line('to be done.');
END IF;
END;
/
ALTER SESSION SET "_oracle_script" = FALSE;
|
결론 :
preupgrade.jar를 이용해 업그레이드 단계를 보다 안전하게 점검할수 있음
업그레이드 전에는 preupgrade_fixups.sql 스크립트를 수행하고, 일부 제안 사항들을 수정한뒤
19c 업그레이드 완료 후 postupgrade_fixups.sql을 수행해주면됨
참고로 21c부터는 preupgrade.jar가 지원이 중단된다고함=>AutoUpgrade를 사용하라고 권고함(참고 2485457.1)
참조 :
How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)
AutoUpgrade Tool (Doc ID 2485457.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmsu/running-pre-upgrade-information-tool-for-non-cdb-checks.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 insert set 구문과 by name position 구문 (1) | 2025.07.26 |
|---|---|
| 오라클 19c(19.28) 신기능 (0) | 2025.07.22 |
| 오라클 23ai 신기능 smallfile 테이블스페이스 shrink (0) | 2025.06.21 |
| 오라클 19c tde 설정 방법 및 데이터파일 암호화 테스트 (0) | 2025.06.15 |
| 오라클 19c 로컬 파티션 인덱스의 기본 테이블스페이스 (0) | 2025.06.10 |