OS 환경 : Oracle Linux 7.7 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 19.19.0.0
방법 : 오라클 11g to 19c 업그레이드 시 restore point 이용 복원 테스트(compatible 변경)
본문에서는 이전 게시글과 동일하게 11g to 19c 업그레이드 시 restore point를 만들고 19c 업그레이드 후 restore point로 돌아가는 방법을 설명함
하지만 19c 업그레이드 후 compatible 파라미터를 19로 변경한 뒤 restore point로 flashback 시도해봄
이전게시물 참고 : 오라클 11g to 19c 업그레이드 시 restore point 이용 복원 테스트 ( https://positivemh.tistory.com/1280 )
현재 11gR2(11.2.0.4)가 설치되어 있고 19.19 엔진이 설치되어 있는 상태임, 그리고 pfile이 19c 엔진 홈/dbs에 복제된 상태임
테스트
db_recovery_file_dest, db_recovery_file_dest_size 파라미터 설정
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ cd /app/oracle/product/19c/dbs
$ vi initORA11FS.ora
*.audit_file_dest='/app/oracle/admin/ORA11FS/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/app/oracle/oradata/ORA11FS/control01.ctl','/app/oracle/oradata/ORA11FS/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11FS'
*.diagnostic_dest='/app/oracle'
*.log_archive_dest_1='location=/app/oracle/arch'
*.log_archive_format='ORA11FS_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=834666496
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2503999488
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=30g
*.db_recovery_file_dest='/app/oracle/reco'
|
11g 엔진으로 db 종료
|
1
2
3
4
|
$ /app/oracle/product/11g/bin/sqlplus / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
|
19c 엔진으로 db upgrade 모드로 기동
|
1
2
3
4
5
6
7
8
9
10
11
|
$ /app/oracle/product/19c/bin/sqlplus / as sysdba
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
|
restore point가 존재하는지 확인
|
1
2
3
|
SQL> select name from v$restore_point;
no rows selected
|
없음
restore point 생성
|
1
2
3
|
SQL> create restore point before_db2 guarantee flashback database;
Restore point created.
|
restore point 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200
col name for a15
select name, scn, to_char(time,'yyyy-mm-dd hh24:mi:ss') "time",
database_incarnation# di,guarantee_flashback_database, storage_size
from v$restore_point
where guarantee_flashback_database='YES';
NAME SCN time DI GUA STORAGE_SIZE
--------------- ---------- ------------------- ---------- --- ------------
BEFORE_DB2 403883 2025-09-28 16:01:02 2 YES 209715200
|
db 업그레이드
|
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
|
$ cd /app/oracle/product/19c/bin/
$ nohup ./dbupgrade > /home/oracle/upg/dbupgrade2.log &
$ tail -300f /home/oracle/upg/dbupgrade2.log
Argument list for [/app/oracle/product/19c/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.19.0.0.0DBRU_LINUX.X64_230321.1]
/app/oracle/product/19c/rdbms/admin/orahome = [/app/oracle/product/19c]
/app/oracle/product/19c/bin/orabasehome = [/app/oracle/product/19c]
catctlGetOraBaseLogDir = [/app/oracle/product/19c]
Analyzing file /app/oracle/product/19c/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20250928160122]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20250928160122/catupgrd_catcon_13033.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250928160122/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250928160122/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = ORA11FS
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123/catupgrd_catcon_13033.lst]
catcon::set_log_file_base_path: catcon: See [/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123/catupgrd_*.lst] files for spool files, if any
Log file directory = [/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123]
Parallel SQL Process Count = 4
Components in [ORA11FS]
Installed [CATALOG CATPROC OWM]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XDB XML XOQ]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2025_09_28 16:01:24]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORA11FS] Files:1 Time: 53s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORA11FS] Files:5 Time: 30s
Restart Phase #:2 [ORA11FS] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ORA11FS] Files:19 Time: 16s
Restart Phase #:4 [ORA11FS] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [ORA11FS] Files:7 Time: 14s
***************** Catproc Start ****************
Serial Phase #:6 [ORA11FS] Files:1 Time: 8s
***************** Catproc Types ****************
Serial Phase #:7 [ORA11FS] Files:2 Time: 6s
Restart Phase #:8 [ORA11FS] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [ORA11FS] Files:71 Time: 19s
Restart Phase #:10 [ORA11FS] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [ORA11FS] Files:1 Time: 44s
Restart Phase #:12 [ORA11FS] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [ORA11FS] Files:95 Time: 5s
Restart Phase #:14 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:15 [ORA11FS] Files:122 Time: 9s
Restart Phase #:16 [ORA11FS] Files:1 Time: 0s
Serial Phase #:17 [ORA11FS] Files:26 Time: 2s
Restart Phase #:18 [ORA11FS] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [ORA11FS] Files:32 Time: 11s
Restart Phase #:20 [ORA11FS] Files:1 Time: 0s
Serial Phase #:21 [ORA11FS] Files:3 Time: 9s
Restart Phase #:22 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:23 [ORA11FS] Files:25 Time: 126s
Restart Phase #:24 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:25 [ORA11FS] Files:12 Time: 88s
Restart Phase #:26 [ORA11FS] Files:1 Time: 0s
Serial Phase #:27 [ORA11FS] Files:1 Time: 0s
Serial Phase #:28 [ORA11FS] Files:7 Time: 3s
Serial Phase #:29 [ORA11FS] Files:1 Time: 0s
Restart Phase #:30 [ORA11FS] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [ORA11FS] Files:1 Time: 0s
Restart Phase #:32 [ORA11FS] Files:1 Time: 0s
Serial Phase #:34 [ORA11FS] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORA11FS] Files:297 Time: 16s
Serial Phase #:36 [ORA11FS] Files:1 Time: 0s
Restart Phase #:37 [ORA11FS] Files:1 Time: 1s
Serial Phase #:38 [ORA11FS] Files:10 Time: 3s
Restart Phase #:39 [ORA11FS] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [ORA11FS] Files:3 Time: 37s
Restart Phase #:41 [ORA11FS] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORA11FS] Files:13 Time: 90s
Restart Phase #:43 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:44 [ORA11FS] Files:11 Time: 6s
Restart Phase #:45 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:46 [ORA11FS] Files:3 Time: 1s
Restart Phase #:47 [ORA11FS] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [ORA11FS] Files:1 Time: 64s
Restart Phase #:49 [ORA11FS] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORA11FS] Files:1 Time: 13s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORA11FS] Files:1 Time: 0s
Restart Phase #:52 [ORA11FS] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [ORA11FS] Files:2 Time: 41s
***************** Upgrading XDB ****************
Restart Phase #:54 [ORA11FS] Files:1 Time: 0s
Serial Phase #:56 [ORA11FS] Files:3 Time: 0s
Serial Phase #:57 [ORA11FS] Files:3 Time: 0s
Parallel Phase #:58 [ORA11FS] Files:10 Time: 1s
Parallel Phase #:59 [ORA11FS] Files:25 Time: 0s
Serial Phase #:60 [ORA11FS] Files:4 Time: 0s
Serial Phase #:61 [ORA11FS] Files:1 Time: 0s
Serial Phase #:62 [ORA11FS] Files:32 Time: 0s
Serial Phase #:63 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:64 [ORA11FS] Files:6 Time: 1s
Serial Phase #:65 [ORA11FS] Files:2 Time: 0s
Serial Phase #:66 [ORA11FS] Files:3 Time: 0s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [ORA11FS] Files:1 Time: 0s
Serial Phase #:69 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:70 [ORA11FS] Files:2 Time: 1s
Restart Phase #:71 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:72 [ORA11FS] Files:2 Time: 0s
Serial Phase #:73 [ORA11FS] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:74 [ORA11FS] Files:1 Time: 0s
Serial Phase #:76 [ORA11FS] Files:1 Time: 1s
Serial Phase #:77 [ORA11FS] Files:2 Time: 0s
Restart Phase #:78 [ORA11FS] Files:1 Time: 0s
Serial Phase #:79 [ORA11FS] Files:1 Time: 0s
Restart Phase #:80 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:81 [ORA11FS] Files:3 Time: 1s
Restart Phase #:82 [ORA11FS] Files:1 Time: 0s
Serial Phase #:83 [ORA11FS] Files:1 Time: 0s
Restart Phase #:84 [ORA11FS] Files:1 Time: 0s
Serial Phase #:85 [ORA11FS] Files:1 Time: 1s
Restart Phase #:86 [ORA11FS] Files:1 Time: 0s
Parallel Phase #:87 [ORA11FS] Files:4 Time: 0s
Restart Phase #:88 [ORA11FS] Files:1 Time: 0s
Serial Phase #:89 [ORA11FS] Files:1 Time: 0s
Restart Phase #:90 [ORA11FS] Files:1 Time: 0s
Serial Phase #:91 [ORA11FS] Files:2 Time: 1s
Restart Phase #:92 [ORA11FS] Files:1 Time: 0s
Serial Phase #:93 [ORA11FS] Files:1 Time: 0s
Restart Phase #:94 [ORA11FS] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [ORA11FS] Files:1 Time: 0s
Restart Phase #:96 [ORA11FS] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [ORA11FS] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [ORA11FS] Files:1 Time: 51s
******************* Migration ******************
Serial Phase #:99 [ORA11FS] Files:1 Time: 24s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [ORA11FS] Files:1 Time: 0s
Serial Phase #:101 [ORA11FS] Files:1 Time: 0s
Serial Phase #:102 [ORA11FS] Files:1 Time: 40s
***************** Post Upgrade *****************
Serial Phase #:103 [ORA11FS] Files:1 Time: 23s
**************** Summary report ****************
Serial Phase #:104 [ORA11FS] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [ORA11FS] Files:1 Time: 1s
Serial Phase #:106 [ORA11FS] Files:1 Time: 0s
Serial Phase #:107 [ORA11FS] Files:1 Time: 21s
------------------------------------------------------
Phases [0-107] End Time:[2025_09_28 16:16:11]
------------------------------------------------------
Grand Total Time: 888s
LOG FILES: (/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123/catupgrd*.log)
Upgrade Summary Report Located in:
/app/oracle/product/19c/cfgtoollogs/ORA11FS/upgrade20250928160123/upg_summary.log
Grand Total Upgrade Time: [0d:0h:14m:48s]
|
업그레이드 완료됨
db 기동후 스크립트 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> startup
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlusts.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @dbupgdiag.sql
SQL> @/home/oracle/upg/Preupgrade/postupgrade_fixups.sql
|
타임존 버전 확인
|
1
2
3
4
5
|
SQL> select * from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
|
타임존 업그레이드 수행
|
1
2
|
SQL> @?/rdbms/admin/utltz_upg_check.sql
SQL> @?/rdbms/admin/utltz_upg_apply.sql
|
타임존 버전 확인
|
1
2
3
4
5
|
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_41.dat 41 0
|
14에서 41로 업그레이드 됨
db 버전 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col patch_id for 999999999
col action_time for a30
col status for a20
col description for a60
select action_time, action, patch_id, status, description
from dba_Registry_Sqlpatch
order by 1;
ACTION_TIME ACTION PATCH_ID STATUS DESCRIPTION
------------------------------ --------------- ---------- -------------------- ------------------------------------------------------------
28-SEP-25 04.14.14.734283 PM APPLY 35042068 SUCCESS Database Release Update : 19.19.0.0.230418 (35042068)
|
19.19로 업그레이드 됨
아직 compatible 파라미터는 19로 올리지 않음
|
1
2
3
4
5
|
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
|
db 종료 후 pfile에서 compatible 수정
|
1
2
3
4
5
6
7
8
9
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cd $ORACLE_HOME/dbs
$ vi initORA11FS.ora
#*.compatible='11.2.0.4.0'
*.compatible='19.0.0'
|
pfile로 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup pfile='initORA11FS.ora';
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 19.0.0.0.0 due to
guaranteed restore points
|
restore point가 존재하기때문에 compatible을 19로 올릴수 없음
restore point 제거
|
1
2
3
4
5
|
SQL> drop restore point before_db2;
drop restore point before_db
*
ERROR at line 1:
ORA-01507: database not mounted
|
mount 모드가 아니라 제거가 안됨
현재 db는 nomount 상태임
|
1
2
3
4
5
|
SQL> select status from v$instance;
STATUS
------------
STARTED
|
db 종료 후 pfile에서 compatible 다시 원복
|
1
2
3
4
5
6
7
8
|
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
$ cd $ORACLE_HOME/dbs
$ vi initORA11FS.ora
*.compatible='11.2.0.4.0'
#*.compatible='19.0.0'
|
pfile로 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup pfile='initORA11FS.ora';
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
|
정상 기동됨
restore point 제거
|
1
2
3
|
SQL> drop restore point before_db2;
Restore point dropped.
|
정상적으로 제거됨
다시 db 종료 후 pfile에서 compatible 수정
|
1
2
3
4
5
6
7
8
9
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cd $ORACLE_HOME/dbs
$ vi initORA11FS.ora
#*.compatible='11.2.0.4.0'
*.compatible='19.0.0'
|
pfile로 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup pfile='initORA11FS.ora';
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
|
타임존 버전 확인
|
1
2
3
4
5
|
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_41.dat 41 0
|
14에서 41로 업그레이드 됨
db 버전 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col patch_id for 999999999
col action_time for a30
col status for a20
col description for a60
select action_time, action, patch_id, status, description
from dba_Registry_Sqlpatch
order by 1;
ACTION_TIME ACTION PATCH_ID STATUS DESCRIPTION
------------------------------ --------------- ---------- -------------------- ------------------------------------------------------------
30-SEP-25 04.14.14.734283 PM APPLY 35042068 SUCCESS Database Release Update : 19.19.0.0.230418 (35042068)
|
19.19로 업그레이드 됨
compatible 파라미터 확인
|
1
2
3
4
5
|
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
|
compatible이 19가 됨
결론 :
업그레이드 전 restore point를 생성하고, db 업그레이드 및 타임존까지 업그레이드 한 상태에서 flashback database를 이용해 업그레이드 이전으로 돌아갈 수 있음
이때 compatible을 19로 변경한뒤 기동하려하면 ORA-38880가 발생함, restore point를 제거해야만 compatible을 19로 올릴수 있음
이말은 compatible을 19로 변경하면 다시 11g로 돌아갈수 없음을 의미함
flashback database restore point 기능을 이용하면 편리하게 db를 작업 이전으로 되돌릴 수 있음, 하지만 compatible을 올려야하는 경우 restore point로 돌아갈 수 없음
참조 :
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c rman 일부 테이블스페이스 백업 후 복구 시나리오 (0) | 2025.12.05 |
|---|---|
| 오라클 19c rman 증분 백업 및 복구 시나리오 (2) | 2025.11.27 |
| 오라클 11g to 19c 업그레이드 시 restore point 이용 복원 테스트 (0) | 2025.10.08 |
| 오라클 19c 핫백업시 redo 발생량 차이 확인 (0) | 2025.10.03 |
| 오라클 19c rman 커맨드 빠른 참조 (0) | 2025.07.21 |