OS 환경 : Oracle Linux 4.8, 6.8 (64bit)
DB 환경 : Oracle Database 10.2.0.3, 11.2.0.4
방법 : 오라클 10g R2 에서 오라클 11g R2 수동 업그레이드 마이그레이션 방법
오라클 10.2.0.3 환경의 싱글 db에서 11.2.0.4 버전으로 수동 업그레이드 하는 방법을 설명함
asis 환경 : Oracle Linux 4.8, Oracle Database 10.2.0.3
참고 : Oracle Linux 4.8에 Oracle 10g R2 설치 가이드 ( https://positivemh.tistory.com/1138 )
tobe 환경 : Oracle Linux 6.8, Oracle Database 11.2.0.4(엔진만 설치)
작업방식 요약 :
todb 서버에 11gR2 엔진만 설치해둔 상태로 asis 서버에서 datafile, controlfile, redo log file을 tobe 서버로 가져와 수동 업그레이드 진행
총 2개의 서버를 사용함
그리고 oracle datafile 용 disk 를 하나 추가해서 /oradata1로 마운트 시켜놓은 상태임
참고 : Oracle Linux 4 새로넣은 디스크 mount 시키기 ( https://positivemh.tistory.com/741 )
이 작업 방식을 동일 서버에서 사용하는 경우 db 버전에 따라 os 업그레이드도 필요할 수 있음
os 버전이 높아서 db만 업그레이드 해도 된다면 동일서버에서 신규 엔진을 설치하여 업그레이드 가능함
작업 전 효율을 높이기 위해 리두로그 크기를 50mb에서 300mb로 증가시킴
참고 : 오라클 19c redo log 크기 자동 변경 스크립트 ( https://positivemh.tistory.com/1047 )
목차
**사전 설정 및 샘플 데이터 생성
**업그레이드 전 스크립트 수행
**데이터 전송
**db 업그레이드
**타임존 업그레이드
**통게정보 수집
**사전 설정 및 샘플 데이터 생성
전체 log switch 진행
|
1
2
3
4
|
SQL> alter system switch logfile;
/
/
/
|
업그레이드 전 system, sysaux, temp, undo 데이터파일들 크기 증가
기존 크기 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 /oradata1/oracle10/system01.dbf .46 32 YES AVAILABLE SYSTEM
TEMP 1 /oradata1/oracle10/temp01.dbf .02 32 YES AVAILABLE
UNDOTBS1 2 /oradata1/oracle10/undotbs01.dbf .02 32 YES AVAILABLE ONLINE
SYSAUX 3 /oradata1/oracle10/sysaux01.dbf .24 32 YES AVAILABLE ONLINE
USERS 4 /oradata1/oracle10/users01.dbf 0 32 YES AVAILABLE ONLINE
|
각각 1~2gb로 증가
|
1
2
3
4
5
|
SQL>
alter database datafile 1 resize 1g;
alter database datafile 2 resize 2g;
alter database datafile 3 resize 1g;
alter database tempfile 1 resize 1g;
|
데이터 검증용 샘플 테이블스페이스 유저 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
drop tablespace migdata including contents and datafiles;
create tablespace migdata datafile '/oradata1/oracle10/migdata01.dbf' size 2048m;
alter tablespace migdata add datafile '/oradata1/oracle10/migdata02.dbf' size 2048m;
alter tablespace migdata add datafile '/oradata1/oracle10/migdata03.dbf' size 2048m;
SQL> create user imsi identified by imsi default tablespace migdata quota unlimited on migdata;
User created.
SQL> grant resource, connect to imsi;
Grant succeeded.
|
샘플 테이블 및 데이터 생성
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
conn imsi/imsi
drop table sample_t1 purge;
create table sample_t1
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
|
테이터 삽입(500만건)
|
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
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..500000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..500000 INSERT INTO sample_t1 VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
row 수 확인
|
1
2
3
4
5
|
SQL> select count(*) from sample_t1;
COUNT(*)
----------
5000000
|
정상적으로 500만건이 삽입됨
테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
conn / as sysdba
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
from dba_segments
where segment_name = 'SAMPLE_T1';
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
SAMPLE_T1 59392 464
|
464mb임
추가 테이블 더 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
conn imsi/imsi
create table sample_t2 nologging as select * from sample_t1;
create table sample_t3 nologging as select * from sample_t1;
create table sample_t4 nologging as select * from sample_t1;
create table sample_t5 nologging as select * from sample_t1;
create table sample_t6 nologging as select * from sample_t1;
create table sample_t7 nologging as select * from sample_t1;
create table sample_t8 nologging as select * from sample_t1;
create table sample_t9 nologging as select * from sample_t1;
create table sample_t10 nologging as select * from sample_t1;
|
모든 테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
conn / as sysdba
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, round(sum(bytes/1024/1024/1024) over(),2) all_gb
from dba_segments
where segment_name like 'SAMPLE_T%';
SEGMENT_NAME BLOCKS MB ALL_GB
-------------------- ---------- ---------- ----------
SAMPLE_T1 59392 464 4.53
SAMPLE_T10 59392 464 4.53
SAMPLE_T2 59392 464 4.53
SAMPLE_T3 59392 464 4.53
SAMPLE_T4 59392 464 4.53
SAMPLE_T5 59392 464 4.53
SAMPLE_T6 59392 464 4.53
SAMPLE_T7 59392 464 4.53
SAMPLE_T8 59392 464 4.53
SAMPLE_T9 59392 464 4.53
10 rows selected.
|
총 10개의 테이블을 만들었고 전체합쳐서 모두 4.5gb임
아카이브 모드 설정
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> alter system set log_archive_dest_1 = 'location=/app/oracle/arch' scope=spfile;
System altered.
SQL> alter system set log_archive_format = 'oracle10_%t_%s_%r.arc' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
|
아카이브 로그 모드 확인
|
1
2
3
4
5
6
7
|
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/arch
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
|
정상적으로 아카이브모드 설정됨
10g pfile 확인 및 백업
|
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
|
SQL> create pfile from spfile;
$ cat $ORACLE_HOME/dbs/initoracle10.ora
oracle10.__db_cache_size=872415232
oracle10.__java_pool_size=16777216
oracle10.__large_pool_size=16777216
oracle10.__shared_pool_size=285212672
oracle10.__streams_pool_size=0
*.audit_file_dest='/app/oracle/admin/oracle10/adump'
*.background_dump_dest='/app/oracle/admin/oracle10/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oradata1/oracle10/control01.ctl','/oradata1/oracle10/control02.ctl','/oradata1/oracle10/control03.ctl'
*.core_dump_dest='/app/oracle/admin/oracle10/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='oracle10'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle10XDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/app/oracle/arch'
*.log_archive_format='oracle10_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1207959552
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/admin/oracle10/udump'
|
**업그레이드 전 스크립트 수행
11gR2에 있는 utlu112i.sql 파일을 10g 서버로 가져와서 실행
|
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
|
$ sqlplus / as sysdba
SQL>
spool preupgrade_info.log
@utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 07-20-2024 09:46:41
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORACLE10
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux 64-bit for AMD
--> timezone file: V3
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 973 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 721 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.3.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
|
스크립트에서 나온 사항 조치 및 10g 찌꺼기 파일 제거
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> truncate table SYS.AUD$;
Table truncated.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
no rows selected
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
no rows selected
|
dbupgdiag.sql 실행(현재 패치 상태를 상세하게 확인 가능)
(https://github.com/fatdba/Oracle-Database-Scripts/blob/main/dbupgdiag.sql 에서 다운로드)
|
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
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
|
SQL> @dbupgdiag.sql
$ cat db_upg_diag_oracle10_20_Jul_2024_0927.log
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 07-20-2024 14:27:56
===============
Hostname
===============
ora10
===============
Database Name
===============
ORACLE10
===============
Database Uptime
===============
14:20 19-JUL-24
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
=============
Compatibility
=============
Compatibility is set as 10.2.0.3.0
================
Archive Log Mode
================
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /app/oracle/admin/oracle10/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
================
Cluster Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
OWNER TABLESPACE_NAME
------------ ------------------------------
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
*
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
0
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is NOT installed at database level
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_SRVNTFN_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_JOBQTAB - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
3
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
IMSI TEMP MIGDATA
OUTLN TEMP SYSTEM
MGMT_VIEW TEMP SYSTEM
MDSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
CTXSYS TEMP SYSAUX
ANONYMOUS TEMP SYSAUX
EXFSYS TEMP SYSAUX
DMSYS TEMP SYSAUX
DBSNMP TEMP SYSAUX
WMSYS TEMP SYSAUX
SYSMAN TEMP SYSAUX
XDB TEMP SYSAUX
ORDPLUGINS TEMP SYSAUX
SI_INFORMTN_SCHEMA TEMP SYSAUX
OLAPSYS TEMP SYSAUX
MDDATA TEMP USERS
DIP TEMP USERS
SCOTT TEMP USERS
TSMSYS TEMP USERS
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD OLAP Catalog VALID 10.2.0.3.0
APS OLAP Analytic Workspace VALID 10.2.0.3.0
CATALOG Oracle Database Catalog Views VALID 10.2.0.3.0
CATJAVA Oracle Database Java Packages VALID 10.2.0.3.0
CATPROC Oracle Database Packages and Types VALID 10.2.0.3.0
CONTEXT Oracle Text VALID 10.2.0.3.0
EM Oracle Enterprise Manager VALID 10.2.0.3.0
EXF Oracle Expression Filter VALID 10.2.0.3.0
JAVAVM JServer JAVA Virtual Machine VALID 10.2.0.3.0
ODM Oracle Data Mining VALID 10.2.0.3.0
ORDIM Oracle interMedia VALID 10.2.0.3.0
OWM Oracle Workspace Manager VALID 10.2.0.1.0
RUL Oracle Rules Manager VALID 10.2.0.3.0
SDO Spatial VALID 10.2.0.3.0
XDB Oracle XML Database VALID 10.2.0.3.0
XML Oracle XDK VALID 10.2.0.3.0
XOQ Oracle OLAP API VALID 10.2.0.3.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
-------- -----------------------------------
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
COUNT(1)
----------
0
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE DBMS_REPCAT_AUTH
DOC>PACKAGE BODY DBMS_REPCAT_AUTH
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
Querying for password protected roles ....
================
JVM Verification
================
JAVAVM - Installed properly
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 6 JAVA related roles
Roles
ROLE
------------------------------
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
JAVA_DEPLOY
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
JAVAVM TESTING
---------------
foo
===================================
Oracle Multimedia/InterMedia status
===================================
.
Oracle Multimedia/interMedia is installed and listed with the following version: 10.2.0.3.0 and status: VALID
.
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)
PL/SQL procedure successfully completed.
*** End of LogFile ***
|
db 정상 종료
|
1
2
3
4
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
|
**데이터 전송
datafile, controlfile, redo log 등 tobe 서버로 전송
|
1
2
|
$ cd /oradata1/oracle10/
$ scp ./* 192.168.137.11:/oradata1/oracle10/
|
**db 업그레이드
지금부터 tobe 서버에서 진행
파일 및 권한 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
# cd /oradata1/
# ls -al
total 12
drwxr-xr-x. 3 500 500 4096 Jul 16 13:43 .
dr-xr-xr-x. 28 root root 4096 Jul 17 01:10 ..
drwxr-xr-x. 2 500 oinstall 4096 Jul 16 13:55 oracle10
# cd oracle10/
# ls -al
total 13671932
-rw-r-----. 1 500 oinstall 7356416 Jul 16 15:50 control01.ctl
-rw-r-----. 1 500 oinstall 7356416 Jul 16 15:50 control02.ctl
-rw-r-----. 1 500 oinstall 7356416 Jul 16 15:50 control03.ctl
-rw-r-----. 1 500 oinstall 2147491840 Jul 16 15:50 migdata01.dbf
-rw-r-----. 1 500 oinstall 2147491840 Jul 16 15:50 migdata02.dbf
-rw-r-----. 1 500 oinstall 2147491840 Jul 16 15:50 migdata03.dbf
-rw-r-----. 1 500 oinstall 1073742336 Jul 16 15:50 redo01.log
-rw-r-----. 1 500 oinstall 1073742336 Jul 16 15:13 redo02.log
-rw-r-----. 1 500 oinstall 1073742336 Jul 16 15:14 redo03.log
-rw-r-----. 1 500 oinstall 1073750016 Jul 16 15:50 sysaux01.dbf
-rw-r-----. 1 500 oinstall 1073750016 Jul 16 15:50 system01.dbf
-rw-r-----. 1 500 oinstall 20979712 Jul 16 13:47 temp01.dbf
-rw-r-----. 1 500 oinstall 2147491840 Jul 16 15:50 undotbs01.dbf
-rw-r-----. 1 500 oinstall 5251072 Jul 16 15:50 users01.dbf
|
기존10g 서버의 oracle 유저 gid와 11g 서버 oracle 유저의 gid가 달라서 제대로 표시되지 않음
권한(owner) 재부여
|
1
2
|
# chmod -R 744 /oradata1/
# chown -R oracle:dba /oradata1/
|
arch, adump 디렉토리 생성
|
1
2
|
$ mkdir -p /app/oracle/admin/oracle10/adump
$ mkdir -p /app/oracle/arch
|
오라클 유저 .bash_profile 확인(ORACLE_HOME, PATH가 11gR2 엔진 경로 인것 확인)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
# su - oracle
$ vi .bash_profile
export PATH
TMP=/app/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle10; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
export DISPLAY=192.168.137.1:0.0;
stty erase ^H
alias ss="sqlplus / as sysdba"
alias bdump="cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace"
alias alert='vi $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias alertf='tail -300f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias dbs="cd $ORACLE_HOME/dbs"
|
pfile 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ cd $ORACLE_HOME/dbs
$ vi initoracle10.ora
*.audit_file_dest='/app/oracle/admin/oracle10/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/oradata1/oracle10/control01.ctl','/oradata1/oracle10/control02.ctl','/oradata1/oracle10/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracle10'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle10XDB)'
*.sga_target=1g
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_1='location=/app/oracle/arch'
*.log_archive_format='oracle10_%t_%s_%r.arc'
|
$ORACLE_HOME/rdbms/admin위치로 이동
|
1
|
$ cd $ORACLE_HOME/rdbms/admin
|
*실행할 스크립트들의 목적 및 역할
catupgrd.sql : data dictionary 테이블들을 upgrade 하는 버전에 맞게 생성하고 변경하며, 새로운 component 들을 설치하거나 업그레이드하는 스크립트
utlu112s.sql : upgrade 후 컴포넌트들의 버전을 확인하는 스크립트
catuppst.sql : 새로 적용된 psu 를 자동적으로 최신 패치까지 적용시키는 스크립트. (UPGRADE mode 가 아니어도 무방)
utlrp.sql : PL/SQL block 은 UTL_RECOMP 를 데이터베이스 내에 있는 invalid objects 를 recompile 하기위해 호출하는 스크립트
db upgrade 모드로 기동
|
1
2
3
4
5
6
7
8
9
10
11
|
$ sqlplus / as sysdba
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 331350920 bytes
Database Buffers 729808896 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
|
참고용 : startup 시 발생가능한 에러 및 해결 방안
ORA-00401: the value for parameter compatible is not supported by this release
초기화 COMPATIBLE매개변수는 보다 작은 값으로 설정됩니다 10.0.0.
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
초기화 매개변수가 대신 CLUSTER_DATABASE으로 설정되었습니다 .TRUEFALSE
ORA-39700: database must be opened with UPGRADE option
키워드 없이 명령 STARTUP이 내려졌습니다 UPGRADE.
ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks
리두 로그 파일 크기가 4MB 미만인 경우:
temp 테이블스페이스 생성(존재하면 따로 생성하지 않아도 됨)
|
1
|
SQL> create temporary tablespace "temp" tempfile '/oradata1/oracle10/temp01.dbf' size 1g;
|
업그레이드 로그 스풀링 후 catupgrd 실행
catupgrd.sql : data dictionary 테이블들을 upgrade 하는 버전에 맞게 생성하고 변경하며, 새로운 component 들을 설치하거나 업그레이드하는 스크립트
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
SPOOL upgrade.log
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') start_dt from dual; --시작 시간 확인용
start_dt
-------------------
2024/07/20 10:01:52
@catupgrd.sql
..
..
..
|
대략 40분 정도 소요됨
catupgrd.sql 종료후 sqlplus 자동 접속 종료됨
종료 시간 확인용 date 조회
|
1
2
|
$ date
Stu Jul 20 10:40:39 KST 2024
|
참고 : 만약 catupgrd 실행시 사전에 utlu112i.sql을 실행하지 않은 상태라면 아래 에러가 발생함
이 경우 shutdown abort 로 내리고 10g 엔진에서(asis 서버) utlu112i.sql 실행해준 준뒤 다시 실행해야함
|
1
2
3
|
ORA-00942: table or view does not exist
ORA-00904: "TZ_VERSION": invalid identifier
ORA-01722: invalid number
|
pfile 을 spfile 로 변환
|
1
2
3
|
SQL> create spfile from pfile;
File created.
|
db 기동
|
1
|
SQL> startup
|
컴포넌트 버전확인 스크립트 실행
utlu112s.sql : upgrade 후 컴포넌트들의 버전을 확인하는 스크립트
|
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
|
SQL> @utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool 07-20-2024 10:55:13
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:05:54
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:04:23
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:25
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:11
OLAP Catalog
. VALID 11.2.0.4.0 00:00:17
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:21
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:02:57
Oracle XDK
. VALID 11.2.0.4.0 00:00:53
Oracle Text
. VALID 11.2.0.4.0 00:00:21
Oracle XML Database
. VALID 11.2.0.4.0 00:01:59
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:07
Oracle Multimedia
. VALID 11.2.0.4.0 00:02:05
Spatial
. VALID 11.2.0.4.0 00:03:09
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:08
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:07
Final Actions
. 00:00:34
Total Upgrade Time: 00:24:00
PL/SQL procedure successfully completed.
|
catuppst.sql 실행(UPGRADE mode 가 아니어도 무방함)
catuppst.sql : 새로 적용된 psu 를 자동적으로 최신 패치까지 적용시키는 스크립트. (UPGRADE mode 가 아니어도 무방)
|
1
2
3
|
SQL> @catuppst.sql
..
..
|
invalid objects 컴파일하는 스크립트 수행
utlrp.sql : PL/SQL block 은 UTL_RECOMP 를 데이터베이스 내에 있는 invalid objects 를 recompile 하기위해 호출하는 스크립트
utlrp 실행 후 나오는 쿼리들을 이용해 컴파일 진행상황을 확인할 수 있음
|
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
|
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2024-07-20 10:57:47
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-07-20 11:01:07
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
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 errors 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>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
|
완료됨
utlrp 종료 후 invalid objects 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
SQL> select distinct object_name from dba_invalid_objects;
no rows selected
|
0이 아닌 경우 dba_invalid_objects 에서 어떤 오브젝트인지 확인 후 조치
**타임존 업그레이드
1585343.1에서 DBMS_DST_scriptsV1.9.zip 다운로드 후 서버 업로드
기존 타임존 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ sqlplus / as sysdba
SQL> select version from v$timezone_file;
VERSION
----------
3
SQL>
set lines 200 pages 1000
col value for a10
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%'
order by property_name;
PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 3
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
|
현재 timezone 버전은 3임
DST_PRIMARY_TT_VERSION 값은 v$timezone_file의 버전과 일치해야함
DST_SECONDARY_TT_VERSION 값은 0 이어야함(0이 아닌 경우 11gR2 환경은 977512.1 문서를, 12c 환경은 1509653.1 문서를 확인해야함)
DST_UPGRADE_STATE 값은 NONE 이어야함(NONE이 아닌 경우 11gR2 환경은 977512.1 문서를, 12c 환경은 1509653.1 문서를 확인해야함)
0. countstatsTSTZ.sql 실행(선택사항이라 뺌)
1. 타임존 확인 스크립트 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 11.2.0.4 .
INFO: Database RDBMS DST version is DSTv3 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv14 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
|
2. 타임존 업데이트 스크립트 실행(db 2번 재기동됨)
실행 전 참고
- RAC 의 경우 단일 인스턴스만 기동해야함
- TSTZ 데이터에 액세스하거나 저장하는 모든 애플리케이션이 중지되어 있어야함
- upg_tzv_apply.sql은 아무런 확인 없이 DB를 2번 다시 시작함
- 일반적으로 upg_tzv_apply.sql은 upg_tzv_check.sql 보다 시간이 덜 걸림
- Multitenant db의 CDB에 대해 실행하면 모든 PDB가 Close 됨
실행
|
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
|
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv14 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 822086696 bytes
Database Buffers 822083584 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 822086696 bytes
Database Buffers 822083584 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv14 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
|
완료됨
타임존 업데이트 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ sqlplus / as sysdba
SQL> select version from v$timezone_file;
VERSION
----------
14
SQL>
set lines 200 pages 1000
col value for a10
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%'
order by property_name;
PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
|
timezone 버전이 14로 업데이트됨
DBMS_STATS.CREATE_STAT_TABLE 로 만든 통계테이블 존재하는 경우 아래 구문 실행(난 아님)
|
1
|
SQL> execute dbms_stats.upgrade_stat_table('sys','dictstattab');
|
패스워드 접속 확인
만약 잘 안될경우 아래 파라미터 false로 변경 후 {선택(패스워드 재설정)}
|
1
2
3
4
5
|
SQL> show parameter sec_case_sensitive_logon
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
|
Fixed Object 통계수집(피크시간이 아닌 시간에 통계를 수집해야함)
|
1
2
3
|
SQL> execute dbms_stats.gather_fixed_objects_stats;
PL/SQL procedure successfully completed.
|
**통게정보 수집
가장 큰 테이블 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select * from (
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb, round(bytes/1024/1024/1024,2) gb
from dba_segments
where owner = 'IMSI'
order by 4 desc,1,2
)
where rownum <= 10;
|
개별 테이블 통계정보 수집
* 사이즈가 큰 테이블의 경우 estimate_percent와 degree 조정
|
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 400 pages 1000
set long 2000
col cmd for a400
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => ''' || OWNER || ''', tabname => ''' || TABLE_NAME || ''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ''FOR ALL COLUMNS SIZE 1'', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);'
AS CMD
FROM DBA_TABLES
WHERE OWNER = 'IMSI'
ORDER BY TABLE_NAME;
|
db 사이즈가 크지 않거나, 시간이 많은 경우 유저별 전체 통계정보 수동 수집
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'IMSI',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE
);
END;
/
|
통계정보 확인(rownum 수 조절하여 사용)
|
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
|
SQL>
set lines 200 pages 1000
col owner for a15
col index_type for a10
col index_name for a20
col table_name for a20
SELECT * FROM (
SELECT
t.owner,
t.table_name,
to_char(t.last_analyzed, 'yyyy/mm/dd hh24:mi:ss') AS table_last_analyzed,
t.num_rows AS table_num_rows,
i.index_name,
to_char(i.last_analyzed, 'yyyy/mm/dd hh24:mi:ss') AS index_last_analyzed,
i.num_rows AS index_num_rows
FROM
dba_tables t
LEFT JOIN dba_indexes i
ON t.owner = i.owner
AND t.table_name = i.table_name
WHERE 1=1
AND t.owner = 'IMSI'
--AND t.last_analyzed >= to_date('2024/07/08 15:46:00', 'yyyy/mm/dd hh24:mi:ss')
ORDER BY 1,2,5
) WHERE rownum <= 10000;
|
자동 통계 잡 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> select client_name, status, mean_job_duration, total_cpu_last_7_days from dba_autotask_client;
CLIENT_NAME STATUS MEAN_JOB_DURATION
---------------------------------------------------------------- -------- ---------------------------------------------------------------------------
TOTAL_CPU_LAST_7_DAYS
---------------------------------------------------------------------------
auto optimizer stats collection ENABLED +000000000 00:08:35.000000000
+000000000 00:02:19.130000000
auto space advisor ENABLED +000000000 00:00:11.000000000
+000000000 00:00:01.860000000
sql tuning advisor ENABLED +000000000 00:00:26.000000000
+000000000 00:00:07.540000000
|
튜닝 어드바이저 비활성화(선택사항)
|
1
2
3
4
5
6
7
8
9
|
SQL>
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL
);
END;
/
|
자동 옵티마이저 통계 수집기 비활성화(선택사항)
|
1
2
3
4
5
6
7
8
9
|
SQL>
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
|
자동 공간 어드바이저 비활성화(선택사항)
|
1
2
3
4
5
6
7
8
9
|
SQL>
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL
);
END;
/
|
자동 통계 잡 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> select client_name, status, mean_job_duration, total_cpu_last_7_days from dba_autotask_client;
CLIENT_NAME STATUS MEAN_JOB_DURATION
---------------------------------------------------------------- -------- ---------------------------------------------------------------------------
TOTAL_CPU_LAST_7_DAYS
---------------------------------------------------------------------------
auto optimizer stats collection DISABLED +000000000 00:08:35.000000000
+000000000 00:02:19.130000000
auto space advisor DISABLED +000000000 00:00:11.000000000
+000000000 00:00:01.860000000
sql tuning advisor DISABLED +000000000 00:00:26.000000000
+000000000 00:00:07.540000000
|
샘플 테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
conn / as sysdba
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, round(sum(bytes/1024/1024/1024) over(),2) all_gb
from dba_segments
where segment_name like 'SAMPLE_T%';
SEGMENT_NAME BLOCKS MB ALL_GB
-------------------- ---------- ---------- ----------
SAMPLE_T1 59392 464 4.53
SAMPLE_T10 59392 464 4.53
SAMPLE_T2 59392 464 4.53
SAMPLE_T3 59392 464 4.53
SAMPLE_T4 59392 464 4.53
SAMPLE_T5 59392 464 4.53
SAMPLE_T6 59392 464 4.53
SAMPLE_T7 59392 464 4.53
SAMPLE_T8 59392 464 4.53
SAMPLE_T9 59392 464 4.53
10 rows selected.
|
모두 정상적으로 들어옴
row 수 확인
|
1
2
3
4
5
6
7
|
SQL>
conn imsi/imsi
select count(*) from sample_t1;
COUNT(*)
----------
5000000
|
정상적으로 500만건이 삽입됨
본문 내용 이외에도 잡이나, 스케줄러, 오브젝트 비교등의 작업이 추가로 필요함
참조 :
https://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm
https://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#BABCEIFB
https://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD00350
https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#CACFFHCJ
https://docs.oracle.com/cd/E25178_01/server.1111/e10897/software.htm
Complete Checklist for Manual Upgrade to Oracle Database 11gR2 (11.2) (Doc ID 837570.1)
Oracle 11gR2 Upgrade Companion (Doc ID 785351.1)
Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
https://dataonair.or.kr/upload//20091123/1258941686863.pdf
https://blog.naver.com/hanajava/220751802939
https://blog.naver.com/hanajava/220455542058
https://gyh214.tistory.com/117
https://jsp5247.tistory.com/40
https://github.com/fatdba/Oracle-Database-Scripts/blob/main/dbupgdiag.sql
https://dataonair.or.kr/upload//20091123/1258941686863.pdf
https://oracle-base.com/articles/misc/update-database-time-zone-file#google_vignette
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 11g R2 에서 오라클 19c datapump schemas 옵션 이관시 주의사항 (2) | 2022.02.23 |
|---|---|
| 오라클 9i 에서 오라클 19c로 정통 export, import 마이그레이션 (1) | 2021.11.24 |
| 오라클 11g R2 에서 오라클 19c Datapump 마이그레이션 방법 (3) | 2021.06.09 |
| Windows Server 2012 에 SQL Server 2012에서 Oracle Linux 7.6 에 12c R1 db 링크 (0) | 2020.07.20 |
| Oracle Linux 7.6 에 12c R1에서 Windows Server 2012 에 SQL Server 2012 db 링크 (4) | 2020.07.17 |