OS 환경 : Oracle Linux 6.8, 8.8 (64bit)
DB 환경 : Oracle Database 11.2.0.0.4, 19.27.0.0
방법 : 오라클 11gR2 ASM RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법
본문에서는 11gR2 ASM RAC환경에서 rman 백업받은 백업셋을 이용해 19c ASM RAC에서 복구 및 업그레이드 시키는 방법을 설명함
이전에 싱글 db로 업그레이드 하는 방법을 한번 작성 했었지만 RAC + 내용을 보강해서 작성함
참고 : 오라클 11gR2 to 19c Rman 백업셋 이용 업그레이드 방법 ( https://positivemh.tistory.com/736 )
사전에 아카이브모드가 활성화 되어있어야함
참고 : 오라클 아카이브 모드 설정 및 경로 설정 ( https://positivemh.tistory.com/147 )
그리고 본문에서는 preupgrade.jar와 postupgrade_fixups.sql 실행은 생략함
preupgrade.jar는 19c 엔진의 rdbms/admin에도 존재하고 884522.1에서도 받을수 있음
먼저 11g에서 이 스크립트를 수행한 뒤 출력결과로 나오는 postupgrade_fixups.sql을 19c 업그레이드 완료 후 실행해주면됨
참고 : 오라클 11gR2 to 19c 업그레이드 시 preupgrade.jar 스크립트 사용법 ( https://positivemh.tistory.com/1233 )
11gR2 서버에는 db 엔진과 db가 존재하고 19c서버에는 db 엔진만 설치되어 있는 상태임
테스트
현재 버전 확인
|
1
2
3
4
5
|
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.4.0
|
샘플 테이블스페이스 생성
|
1
2
3
|
SQL> create tablespace migts datafile size 2g;
Tablespace created.
|
유저생성 및 권한 부여
|
1
2
3
4
5
6
7
|
SQL> create user miguser identified by miguser account unlock default tablespace migts quota unlimited on migts;
User created.
SQL> grant resource, connect, dba to miguser;
Grant succeeded.
|
테이블 생성
|
1
2
3
4
5
6
|
SQL> conn miguser/miguser
create table migtable(cola varchar2(20), colb number, colc number,
cold varchar2(30), cole varchar2(30), colf varchar2(30),
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30));
Table created.
|
샘플 데이터 삽입(DECLARE문 1번당 197mb) 총 5번 반복 => 약 880mb
|
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>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF MIGTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for d in 1..5 loop
FOR i IN 1..1000000 LOOP
w_ins(i).COLA :=i;
w_ins(i).COLB :=300000;
w_ins(i).COLC :=999;
w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
w_ins(i).COLG :=9999999;
w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
FORALL i in 1..1000000 INSERT INTO MIGTABLE VALUES w_ins(i);
COMMIT;
DBMS_OUTPUT.PUT_LINE(d);
end loop;
END;
/
PL/SQL procedure successfully completed.
|
로그스위치 및 체크포인트 5회씩 수행
|
1
2
|
SQL> alter system switch logfile; --5 times
SQL> alter system checkpoint; --5 times
|
용량확인(sys)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn / as sysdba
set lines 200
col owner for a10
col segment_name for a20
select owner, sum(bytes)/1024/1024 as MB
from dba_segments
where owner='MIGUSER'
GROUP BY owner;
OWNER MB
---------- ----------
MIGUSER 880
|
asm diskgroup 확인
|
1
2
3
4
5
6
|
$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 29696 20597 0 20597 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCRVOTE/
MOUNTED EXTERN N 512 4096 1048576 101376 100371 0 100371 0 N RECO/
|
rman 백업
|
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
|
$ mkdir -p /oracle/app/oracle/rman/
$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/app/oracle/rman/db_ctl_%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/app/oracle/rman/%d_%U_%T.bk';
RMAN> backup database plus archivelog;
Starting backup at 2025-06-21 17:18:48
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 instance=ORA11DB1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=74 RECID=136 STAMP=1204391878
input archived log thread=1 sequence=148 RECID=134 STAMP=1204391873
input archived log thread=1 sequence=149 RECID=135 STAMP=1204391877
input archived log thread=1 sequence=150 RECID=137 STAMP=1204391882
input archived log thread=2 sequence=75 RECID=140 STAMP=1204391890
input archived log thread=1 sequence=151 RECID=138 STAMP=1204391886
input archived log thread=1 sequence=152 RECID=139 STAMP=1204391888
input archived log thread=1 sequence=153 RECID=141 STAMP=1204391891
input archived log thread=2 sequence=76 RECID=145 STAMP=1204391899
input archived log thread=1 sequence=154 RECID=142 STAMP=1204391894
input archived log thread=1 sequence=155 RECID=143 STAMP=1204391894
input archived log thread=1 sequence=156 RECID=144 STAMP=1204391898
input archived log thread=1 sequence=157 RECID=147 STAMP=1204391931
input archived log thread=2 sequence=77 RECID=146 STAMP=1204391929
channel ORA_DISK_1: starting piece 1 at 2025-06-21 17:18:59
channel ORA_DISK_1: finished piece 1 at 2025-06-21 17:19:02
piece handle=/oracle/app/oracle/rman/ORA11DB_0d3sj402_1_1_20250621.bk tag=TAG20250621T171858 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2025-06-21 17:19:02
Starting backup at 2025-06-21 17:19:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ora11db/datafile/migts.268.1204391855
input datafile file number=00001 name=+DATA/ora11db/datafile/system.259.1201860715
input datafile file number=00002 name=+DATA/ora11db/datafile/sysaux.260.1201860717
input datafile file number=00003 name=+DATA/ora11db/datafile/undotbs1.261.1201860719
input datafile file number=00004 name=+DATA/ora11db/datafile/undotbs2.263.1201860723
input datafile file number=00005 name=+DATA/ora11db/datafile/users.264.1201860723
channel ORA_DISK_1: starting piece 1 at 2025-06-21 17:19:02
channel ORA_DISK_1: finished piece 1 at 2025-06-21 17:19:09
piece handle=/oracle/app/oracle/rman/ORA11DB_0e3sj406_1_1_20250621.bk tag=TAG20250621T171902 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2025-06-21 17:19:09
Starting backup at 2025-06-21 17:19:09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=78 RECID=149 STAMP=1204391950
input archived log thread=1 sequence=158 RECID=148 STAMP=1204391949
channel ORA_DISK_1: starting piece 1 at 2025-06-21 17:19:12
channel ORA_DISK_1: finished piece 1 at 2025-06-21 17:19:13
piece handle=/oracle/app/oracle/rman/ORA11DB_0f3sj40g_1_1_20250621.bk tag=TAG20250621T171912 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-06-21 17:19:13
Starting Control File and SPFILE Autobackup at 2025-06-21 17:19:13
piece handle=/oracle/app/oracle/rman/db_ctl_c-356823271-20250621-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-21 17:19:14
|
pfile 생성(11g)
|
1
2
3
|
SQL> create pfile from spfile;
File created.
|
rman 백업파일 11g에서 19c로 복사(11g)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ cd /oracle/app/oracle/rman/
$ ls -al
total 2369576
drwxr-xr-x 2 oracle oinstall 4096 Jun 21 17:02 .
drwxrwxr-x 11 grid oinstall 143 Jun 10 12:54 ..
-rw-r----- 1 oracle dba 18841600 Jun 21 17:19 db_ctl_c-356823271-20250621-02
-rw-r----- 1 oracle dba 931962368 Jun 21 17:19 ORA11DB_0d3sj402_1_1_20250621.bk
-rw-r----- 1 oracle dba 1482448896 Jun 21 17:19 ORA11DB_0e3sj406_1_1_20250621.bk
-rw-r----- 1 oracle dba 3072 Jun 21 17:19 ORA11DB_0f3sj40g_1_1_20250621.bk
$ scp ./* 192.168.137.10:/oracle/app/oracle/rman/
db_ctl_c-356823271-20250621-02 100% 18MB 128.3MB/s 00:00
ORA11DB_0d3sj402_1_1_20250621.bk 100% 889MB 164.7MB/s 00:05
ORA11DB_0e3sj406_1_1_20250621.bk 100% 1414MB 153.0MB/s 00:09
ORA11DB_0f3sj40g_1_1_20250621.bk 100% 3072 5.6MB/s 00:00
|
pfile 및 패스워드 파일 11g에서 19c로 복사(11g)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ cd $ORACLE_HOME/dbs
$ ls -al
total 18344
drwxr-xr-x 2 oracle oinstall 153 Jun 21 15:56 .
drwxr-xr-x 74 oracle oinstall 4096 May 23 10:08 ..
-rw-rw---- 1 oracle dba 1544 Jun 21 15:56 hc_ORA11DB1.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 1305 Jun 21 17:20 initORA11DB1.ora
-rw-r----- 1 oracle oinstall 1536 May 23 10:11 orapwORA11DB1
-rw-r----- 1 oracle dba 18759680 Jun 21 17:01 snapcf_ORA11DB1.f
$ scp initORA11DB1.ora orapwORA11DB1 192.168.137.10:/oracle/app/oracle/product/19c/dbs/
oracle@192.168.137.10's password:
initORA11DB1.ora 100% 1305 1.1MB/s 00:00
orapwORA11DB1 100% 1536 205.1KB/s 00:00
|
이제부터 19c에서 작업
pfile 수정(19c 1번 노드)
|
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
|
$ cd /oracle/app/oracle/product/19c/dbs
$ vi initORA11DB1.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORA11DB/adump'
*.audit_trail='none'
*.cluster_database=true 기존
*.cluster_database=false 수정
*.compatible='11.2.0.4.0'
*.control_files='+DATA/ora11db/controlfile/current.256.1201860715' 기존
*.control_files='+DATA' 수정
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='ORA11DB'
*.diagnostic_dest='/oracle/app/oracle'
ORA11DB1.instance_number=1 기존
#ORA11DB1.instance_number=1 수정(주석)
ORA11DB2.instance_number=2 기존
#ORA11DB2.instance_number=2 수정(주석)
*.log_archive_dest_1='location=+RECO'
*.log_archive_format='ora11db_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=834666496
*.processes=150
*.remote_listener='ora11rac-scan:1521' 기존
*.remote_listener='ora19rac-scan:1521' 수정
*.remote_login_passwordfile='exclusive'
*.sga_target=2503999488
ORA11DB1.thread=1 기존
#ORA11DB1.thread=1 수정(주석)
ORA11DB2.thread=2 기존
#ORA11DB2.thread=2 수정(주석)
ORA11DB1.undo_tablespace='UNDOTBS1' 기존
#ORA11DB1.undo_tablespace='UNDOTBS1' 수정(주석)
ORA11DB2.undo_tablespace='UNDOTBS2' 기존
#ORA11DB2.undo_tablespace='UNDOTBS2' 수정(주석)
|
rac 파라미터 주석 및 cluster_database 파라미터 false 처리, control_files, remote_listener 파라미터 변경
audit 경로 생성(양쪽 노드)
|
1
|
$ mkdir -p /oracle/app/oracle/admin/ORA11DB/adump
|
현재 19c grid 상태확인
|
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
|
$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.chad
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.net1.network
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.ons
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE ora19rac1 STABLE
OFFLINE OFFLINE ora19rac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE ora19rac2 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.VOTE.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 Started,STABLE
2 ONLINE ONLINE ora19rac2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.cvu
1 ONLINE ONLINE ora19rac2 STABLE
ora.ora19db.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.ora19rac1.vip
1 ONLINE ONLINE ora19rac1 STABLE
ora.ora19rac2.vip
1 ONLINE ONLINE ora19rac2 STABLE
ora.qosmserver
1 ONLINE ONLINE ora19rac2 STABLE
ora.scan1.vip
1 ONLINE ONLINE ora19rac2 STABLE
--------------------------------------------------------------------------------
|
모두 정상임, ora19db라는 db가 존재하지만 내려놓고 작업진행함
ORA11DB1 db spfile 생성 및 nomount 기동(19c 1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ export ORACLE_SID=ORA11DB1
SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7831552 bytes
|
컨트롤파일 restore(19c)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ rman target /
RMAN> restore controlfile from '/oracle/app/oracle/rman/db_ctl_c-356823271-20250621-02';
restore controlfile from '/oracle/app/oracle/rman/db_ctl_c-356823271-20250621-02';
Starting restore at 2025-06-21 17:29:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ORA11DB/CONTROLFILE/current.302.1204392593
Finished restore at 2025-06-21 17:29:54
|
컨트롤파일이 +DATA/ORA11DB/CONTROLFILE/current.302.1204392593 로 restore됨
spfile의 control_files 파라미터 수정
|
1
2
3
|
SQL> alter system set control_files='+DATA/ORA11DB/CONTROLFILE/current.302.1204392593' scope=spfile;
System altered.
|
db 종료 및 mount 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> startup force mount
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7831552 bytes
Database mounted.
|
rman 설정 변경
|
1
2
3
4
|
$ rman target /
RMAN>
configure controlfile autobackup format for device type disk to '/oracle/app/oracle/rman/db_ctl_%F';
configure channel device type disk format '/oracle/app/oracle/rman/%d_%U_%T_bckset.bk';
|
참고로 rman configure 설정을 변경하지 않고 "alter database open resetlogs upgrade;"로 기동하게 되면
컨트롤파일 오토백업이 /backup/rman(예시) 이라는곳에(기존경로) 생성될수없기 때문에 alert log에 아래와 같은 에러가 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ tail -300f /oracle/app/oracle/diag/rdbms/ora11db/ORA11DB1/trace/alert_ORA11DB1.log
******************** WARNING **************************
The errors during server control file autobackup are not
fatal, as it is attempted after sucessful completion of
the command. However, it is recomended to take an RMAN
control file backup as soon as possible because the
autobackup failed with the following error:
ORA-19624: operation failed, retry possible
ORA-19504: failed to create file "/11g서버_rman_백업경로/db_ctl_c-357213915-20250621-01"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
******************** END OF WARNING *******************
|
백업 확인
|
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
|
RMAN> list backup;
list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
12 888.79M DISK 00:00:03 2025-06-21 17:19:01
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20250621T171858
Piece Name: /oracle/app/oracle/rman/ORA11DB_0d3sj402_1_1_20250621.bk
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 148 885324 2025-06-21 17:16:47 888381 2025-06-21 17:17:52
1 149 888381 2025-06-21 17:17:52 890303 2025-06-21 17:17:55
1 150 890303 2025-06-21 17:17:55 892673 2025-06-21 17:18:00
1 151 892673 2025-06-21 17:18:00 894978 2025-06-21 17:18:05
1 152 894978 2025-06-21 17:18:05 896190 2025-06-21 17:18:07
1 153 896190 2025-06-21 17:18:07 896197 2025-06-21 17:18:10
1 154 896197 2025-06-21 17:18:10 896200 2025-06-21 17:18:13
1 155 896200 2025-06-21 17:18:13 896203 2025-06-21 17:18:14
1 156 896203 2025-06-21 17:18:14 896208 2025-06-21 17:18:18
1 157 896208 2025-06-21 17:18:18 896279 2025-06-21 17:18:51
2 74 885321 2025-06-21 17:16:45 891780 2025-06-21 17:17:57
2 75 891780 2025-06-21 17:17:57 896194 2025-06-21 17:18:09
2 76 896194 2025-06-21 17:18:09 896214 2025-06-21 17:18:19
2 77 896214 2025-06-21 17:18:19 896275 2025-06-21 17:18:49
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
13 Full 1.38G DISK 00:00:04 2025-06-21 17:19:06
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20250621T171902
Piece Name: /oracle/app/oracle/rman/ORA11DB_0e3sj406_1_1_20250621.bk
List of Datafiles in backup set 13
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/system.259.1201860715
2 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/sysaux.260.1201860717
3 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/undotbs1.261.1201860719
4 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/undotbs2.263.1201860723
5 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/users.264.1201860723
6 Full 896292 2025-06-21 17:19:02 NO +DATA/ora11db/datafile/migts.268.1204391855
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
14 2.50K DISK 00:00:00 2025-06-21 17:19:12
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20250621T171912
Piece Name: /oracle/app/oracle/rman/ORA11DB_0f3sj40g_1_1_20250621.bk
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 158 896279 2025-06-21 17:18:51 896302 2025-06-21 17:19:09
2 78 896275 2025-06-21 17:18:49 896306 2025-06-21 17:19:10
|
full backup의 마지막 scn은 896292임
rman restore 및 recover 진행(한줄씩 입력후 엔터)
|
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
|
RMAN>
run
{
set until scn 896292;
set newname for database to '+DATA';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2025-06-21 17:33:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=155 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: restoring datafile 00006 to +DATA
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/rman/ORA11DB_0e3sj406_1_1_20250621.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_0e3sj406_1_1_20250621.bk tag=TAG20250621T171902
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2025-06-21 17:33:23
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/system.284.1204392797
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/sysaux.287.1204392797
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/undotbs1.295.1204392797
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/undotbs2.286.1204392797
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/users.301.1204392797
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1204392803 file name=+DATA/ORA11DB/DATAFILE/migts.289.1204392797
renamed tempfile 1 to +DATA in control file
Starting recover at 2025-06-21 17:33:24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2025-06-21 17:33:25
|
restore 및 recover 완료됨
11g에서 테이블에 데이터 추가 삽입
|
1
2
3
4
|
SQL>
conn miguser/miguser
insert into migtable select * from migtable;
commit;
|
건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from migtable;
COUNT(*)
----------
10000000
|
정상적으로 삽입됨
용량확인(sys)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn / as sysdba
set lines 200
col owner for a10
col segment_name for a20
select owner, sum(bytes)/1024/1024 as MB
from dba_segments
where owner='MIGUSER'
GROUP BY owner;
OWNER MB
---------- ----------
MIGUSER 1792
|
정상적으로 삽입됨
로그스위치 및 체크포인트 5회씩 수행
|
1
2
|
SQL> alter system switch logfile; --5 times
SQL> alter system checkpoint; --5 times
|
11g 아카이브 백업
|
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
|
$ rman target /
RMAN> backup archivelog all not backed up;
Starting backup at 2025-06-21 17:35:54
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 instance=ORA11DB1 device type=DISK
skipping archived logs of thread 1 from sequence 148 to 158; already backed up
skipping archived logs of thread 2 from sequence 74 to 78; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=159 RECID=150 STAMP=1204392548
input archived log thread=2 sequence=79 RECID=153 STAMP=1204392556
input archived log thread=1 sequence=160 RECID=151 STAMP=1204392551
input archived log thread=1 sequence=161 RECID=152 STAMP=1204392554
input archived log thread=1 sequence=162 RECID=154 STAMP=1204392559
input archived log thread=2 sequence=80 RECID=157 STAMP=1204392628
input archived log thread=1 sequence=163 RECID=155 STAMP=1204392568
input archived log thread=1 sequence=164 RECID=156 STAMP=1204392571
input archived log thread=1 sequence=165 RECID=159 STAMP=1204392695
input archived log thread=2 sequence=81 RECID=161 STAMP=1204392700
input archived log thread=1 sequence=166 RECID=158 STAMP=1204392695
input archived log thread=1 sequence=167 RECID=160 STAMP=1204392695
input archived log thread=1 sequence=168 RECID=163 STAMP=1204392957
input archived log thread=2 sequence=82 RECID=162 STAMP=1204392955
channel ORA_DISK_1: starting piece 1 at 2025-06-21 17:36:06
channel ORA_DISK_1: finished piece 1 at 2025-06-21 17:36:09
piece handle=/oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk tag=TAG20250621T173605 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2025-06-21 17:36:09
Starting Control File and SPFILE Autobackup at 2025-06-21 17:36:09
piece handle=/oracle/app/oracle/rman/db_ctl_c-356823271-20250621-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-21 17:36:10
|
백업 완료됨
아카이브로그 백업파일 11g에서 19c로 복사(11g)
|
1
2
3
4
5
6
7
8
9
10
11
|
$ cd /oracle/app/oracle/rman/
total 3304776
-rw-r----- 1 oracle dba 931962368 Jun 21 17:19 ORA11DB_0d3sj402_1_1_20250621.bk
-rw-r----- 1 oracle dba 1482448896 Jun 21 17:19 ORA11DB_0e3sj406_1_1_20250621.bk
-rw-r----- 1 oracle dba 3072 Jun 21 17:19 ORA11DB_0f3sj40g_1_1_20250621.bk
-rw-r----- 1 oracle dba 18841600 Jun 21 17:19 db_ctl_c-356823271-20250621-02
-rw-r----- 1 oracle dba 931987968 Jun 21 17:36 ORA11DB_0h3sj506_1_1_20250621.bk
-rw-r----- 1 oracle dba 18841600 Jun 21 17:36 db_ctl_c-356823271-20250621-03
$ scp ./ORA11DB_0h3sj506_1_1_20250621.bk 192.168.137.10:/oracle/app/oracle/rman/
oracle@192.168.137.10's password:
ORA11DB_0h3sj506_1_1_20250621.bk 100% 889MB 162.6MB/s 00:05
|
카탈로그 등록(19c)(본문처럼 백업이후 추가로 발생된 아카이브로그가 존재하는 경우 이 경로에 복사 후 카탈로깅)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
RMAN> catalog start with '/oracle/app/oracle/rman/';
catalog start with '/oracle/app/oracle/rman/';
searching for all files that match the pattern /oracle/app/oracle/rman/
List of Files Unknown to the Database
=====================================
File Name: /oracle/app/oracle/rman/db_ctl_c-356823271-20250621-02
File Name: /oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk
Do you really want to catalog the above files (enter YES or NO)? yes <<-- yes 입력
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/app/oracle/rman/db_ctl_c-356823271-20250621-02
File Name: /oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk
|
새로운 rman 백업파일이 카탈로깅됨
백업 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
RMAN> list backup summary;
list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
12 B A A DISK 2025-06-21 17:19:01 1 1 NO TAG20250621T171858
13 B F A DISK 2025-06-21 17:19:06 1 1 NO TAG20250621T171902
14 B A A DISK 2025-06-21 17:19:12 1 1 NO TAG20250621T171912
15 B F A DISK 2025-06-21 17:44:29 1 1 NO TAG20250621T171913
16 B A A DISK 2025-06-21 17:44:29 1 1 NO TAG20250621T173605
|
새로 추가한 아카이브로그 백업셋이 16번에 추가됨(TAG20250621T173605)
백업 tag로 상세 확인
|
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
|
RMAN> list backup tag=TAG20250621T173605;
list backup tag=TAG20250621T173605;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16 888.81M DISK 00:00:00 2025-06-21 17:44:29
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20250621T173605
Piece Name: /oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 159 896302 2025-06-21 17:19:09 898845 2025-06-21 17:29:07
1 160 898845 2025-06-21 17:29:07 901179 2025-06-21 17:29:09
1 161 901179 2025-06-21 17:29:09 903460 2025-06-21 17:29:12
1 162 903460 2025-06-21 17:29:12 905735 2025-06-21 17:29:15
1 163 905735 2025-06-21 17:29:15 907624 2025-06-21 17:29:27
1 164 907624 2025-06-21 17:29:27 907628 2025-06-21 17:29:31
1 165 907628 2025-06-21 17:29:31 907670 2025-06-21 17:31:34
1 166 907670 2025-06-21 17:31:34 907673 2025-06-21 17:31:34
1 167 907673 2025-06-21 17:31:34 907676 2025-06-21 17:31:35
1 168 907676 2025-06-21 17:31:35 907920 2025-06-21 17:35:57
2 79 896306 2025-06-21 17:19:10 905587 2025-06-21 17:29:15
2 80 905587 2025-06-21 17:29:15 907642 2025-06-21 17:30:27
2 81 907642 2025-06-21 17:30:27 907701 2025-06-21 17:31:40
2 82 907701 2025-06-21 17:31:40 907916 2025-06-21 17:35:55
|
마지막 scn이 907701임
추가 recover 진행(한줄씩 입력후 엔터)
|
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
|
RMAN>
run {
set until scn 907701;
recover database;
}
executing command: SET until clause
Starting recover at 2025-06-21 17:45:56
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=78
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=158
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/rman/ORA11DB_0f3sj40g_1_1_20250621.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_0f3sj40g_1_1_20250621.bk tag=TAG20250621T171912
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_158.407.1204393559 thread=1 sequence=158
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_78.408.1204393557 thread=2 sequence=78
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=159
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=79
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=160
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=161
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=162
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=80
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=163
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=164
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=165
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=81
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=166
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=167
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=168
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_0h3sj506_1_1_20250621.bk tag=TAG20250621T173605
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_159.403.1204393559 thread=1 sequence=159
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_79.397.1204393559 thread=2 sequence=79
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_160.404.1204393559 thread=1 sequence=160
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_161.405.1204393559 thread=1 sequence=161
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_162.406.1204393559 thread=1 sequence=162
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_80.396.1204393559 thread=2 sequence=80
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_163.402.1204393559 thread=1 sequence=163
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_164.401.1204393559 thread=1 sequence=164
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_165.400.1204393559 thread=1 sequence=165
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_81.399.1204393559 thread=2 sequence=81
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_166.395.1204393559 thread=1 sequence=166
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_167.394.1204393559 thread=1 sequence=167
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_168.398.1204393559 thread=1 sequence=168
media recovery complete, elapsed time: 00:00:12
Finished recover at 2025-06-21 17:46:18
|
recover가 완료됨
redo 경로 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col member for a60
select l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1;
GROUP# MEMBER ARC STATUS MB
---------- ------------------------------------------------------------ --- ---------------- ----------
5 +DATA/ora11db/onlinelog/group_5.269.1204388583 NO CURRENT 200
6 +DATA/ora11db/onlinelog/group_6.270.1204388583 YES INACTIVE 200
7 +DATA/ora11db/onlinelog/group_7.271.1204388583 YES INACTIVE 200
8 +DATA/ora11db/onlinelog/group_8.272.1204388583 NO CURRENT 200
9 +DATA/ora11db/onlinelog/group_9.273.1204388585 YES INACTIVE 200
10 +DATA/ora11db/onlinelog/group_10.274.1204388585 YES INACTIVE 200
6 rows selected.
|
필요시 redo 경로를 변경(만약 동일 서버에서 이 작업을 진행하는 경우 기존 redo를 덮어쓸수 있기때문에 resetlogs 기동 전 mount 상태에서 redo 경로를 변경해줘야함)
본문에서는 서버가 달라지고 diskgroup 이름은 동일하기 때문에 변경하지 않음
|
1
2
3
|
SQL> alter database rename file '+DATA' to '+newDGname';
SQL> alter database rename file '+DATA' to '+newDGname';
SQL> alter database rename file '+DATA' to '+newDGname';
|
alter database open resetlogs upgrade로 기동
|
1
2
3
|
SQL> alter database open resetlogs upgrade;
altered.
|
정상 기동됨
redo 경로 및 temp 경로 재확인
|
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
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 5 +DATA/ORA11DB/ONLINELOG/group_5.300.1204393741 NO CURRENT 200
1 6 +DATA/ORA11DB/ONLINELOG/group_6.299.1204393741 YES UNUSED 200
1 7 +DATA/ORA11DB/ONLINELOG/group_7.298.1204393741 YES UNUSED 200
2 8 +DATA/ORA11DB/ONLINELOG/group_8.297.1204393741 YES INACTIVE 200
2 9 +DATA/ORA11DB/ONLINELOG/group_9.285.1204393743 YES UNUSED 200
2 10 +DATA/ORA11DB/ONLINELOG/group_10.288.1204393743 YES UNUSED 200
6 rows selected.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1 3274 23-MAY-25 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192
+DATA/ORA11DB/TEMPFILE/temp.290.1204393747
0
|
수동 업그레이드 스크립트 실행
*작업 전 redo 사이즈 및 system, sysaux 크기를 여유롭게 조정해 놓는것이 좋음(system, sysaux는 최소 1gb이상, 업그레이드중 autoextend 안될정도로)
* dbupgrade 대신 $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.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
|
$ export ORACLE_SID=ORA11DB1
$ cd $ORACLE_HOME/bin
$ pwd
/oracle/app/oracle/product/19c/bin
$ ./dbupgrade
Argument list for [/oracle/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.27.0.0.0DBRU_LINUX.X64_250405]
/oracle/app/oracle/product/19c/rdbms/admin/orahome = [/oracle/app/oracle/product/19c]
/oracle/app/oracle/product/19c/bin/orabasehome = [/oracle/app/oracle/product/19c]
catctlGetOraBaseLogDir = [/oracle/app/oracle/product/19c]
Analyzing file /oracle/app/oracle/product/19c/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20250621175148]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20250621175148/catupgrd_catcon_71710.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250621175148/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250621175148/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = ORA11DB
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/catupgrd_catcon_71710.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/catupgrd_*.lst] files for spool files, if any
Log file directory = [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149]
Parallel SQL Process Count = 4
Components in [ORA11DB]
Installed [CATALOG CATJAVA CATPROC JAVAVM OWM RAC XML]
Not Installed [APEX APS CONTEXT DV EM MGW ODM OLS ORDIM SDO WK XDB XOQ]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2025_06_21 17:51:50]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORA11DB] Files:1 Time: 77s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORA11DB] Files:5 Time: 30s
Restart Phase #:2 [ORA11DB] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ORA11DB] Files:19 Time: 15s
Restart Phase #:4 [ORA11DB] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [ORA11DB] Files:7 Time: 14s
***************** Catproc Start ****************
Serial Phase #:6 [ORA11DB] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [ORA11DB] Files:2 Time: 6s
Restart Phase #:8 [ORA11DB] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [ORA11DB] Files:72 Time: 21s
Restart Phase #:10 [ORA11DB] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [ORA11DB] Files:1 Time: 48s
Restart Phase #:12 [ORA11DB] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [ORA11DB] Files:95 Time: 6s
Restart Phase #:14 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:15 [ORA11DB] Files:122 Time: 12s
Restart Phase #:16 [ORA11DB] Files:1 Time: 0s
Serial Phase #:17 [ORA11DB] Files:29 Time: 3s
Restart Phase #:18 [ORA11DB] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [ORA11DB] Files:31 Time: 17s
Restart Phase #:20 [ORA11DB] Files:1 Time: 0s
Serial Phase #:21 [ORA11DB] Files:3 Time: 9s
Restart Phase #:22 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:23 [ORA11DB] Files:25 Time: 137s
Restart Phase #:24 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:25 [ORA11DB] Files:12 Time: 95s
Restart Phase #:26 [ORA11DB] Files:1 Time: 0s
Serial Phase #:27 [ORA11DB] Files:1 Time: 0s
Serial Phase #:28 [ORA11DB] Files:8 Time: 4s
Serial Phase #:29 [ORA11DB] Files:1 Time: 0s
Restart Phase #:30 [ORA11DB] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [ORA11DB] Files:1 Time: 0s
Restart Phase #:32 [ORA11DB] Files:1 Time: 1s
Serial Phase #:34 [ORA11DB] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORA11DB] Files:300 Time: 18s
Serial Phase #:36 [ORA11DB] Files:1 Time: 0s
Restart Phase #:37 [ORA11DB] Files:1 Time: 0s
Serial Phase #:38 [ORA11DB] Files:10 Time: 3s
Restart Phase #:39 [ORA11DB] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [ORA11DB] Files:3 Time: 43s
Restart Phase #:41 [ORA11DB] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORA11DB] Files:13 Time: 96s
Restart Phase #:43 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:44 [ORA11DB] Files:11 Time: 6s
Restart Phase #:45 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:46 [ORA11DB] Files:3 Time: 1s
Restart Phase #:47 [ORA11DB] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [ORA11DB] Files:1 Time: 71s
Restart Phase #:49 [ORA11DB] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORA11DB] Files:1 Time: 20s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORA11DB] Files:1 Time: 1s
Restart Phase #:52 [ORA11DB] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [ORA11DB] Files:2 Time: 284s
***************** Upgrading XDB ****************
Restart Phase #:54 [ORA11DB] Files:1 Time: 0s
Serial Phase #:56 [ORA11DB] Files:3 Time: 1s
Serial Phase #:57 [ORA11DB] Files:3 Time: 0s
Parallel Phase #:58 [ORA11DB] Files:10 Time: 0s
Parallel Phase #:59 [ORA11DB] Files:25 Time: 1s
Serial Phase #:60 [ORA11DB] Files:4 Time: 0s
Serial Phase #:61 [ORA11DB] Files:1 Time: 0s
Serial Phase #:62 [ORA11DB] Files:32 Time: 0s
Serial Phase #:63 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:64 [ORA11DB] Files:6 Time: 0s
Serial Phase #:65 [ORA11DB] Files:2 Time: 1s
Serial Phase #:66 [ORA11DB] Files:3 Time: 0s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [ORA11DB] Files:1 Time: 0s
Serial Phase #:69 [ORA11DB] Files:1 Time: 1s
Parallel Phase #:70 [ORA11DB] Files:2 Time: 0s
Restart Phase #:71 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:72 [ORA11DB] Files:2 Time: 0s
Serial Phase #:73 [ORA11DB] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [ORA11DB] Files:1 Time: 0s
Serial Phase #:76 [ORA11DB] Files:1 Time: 0s
Serial Phase #:77 [ORA11DB] Files:2 Time: 0s
Restart Phase #:78 [ORA11DB] Files:1 Time: 1s
Serial Phase #:79 [ORA11DB] Files:1 Time: 0s
Restart Phase #:80 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:81 [ORA11DB] Files:3 Time: 0s
Restart Phase #:82 [ORA11DB] Files:1 Time: 1s
Serial Phase #:83 [ORA11DB] Files:1 Time: 0s
Restart Phase #:84 [ORA11DB] Files:1 Time: 0s
Serial Phase #:85 [ORA11DB] Files:1 Time: 0s
Restart Phase #:86 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:87 [ORA11DB] Files:4 Time: 1s
Restart Phase #:88 [ORA11DB] Files:1 Time: 0s
Serial Phase #:89 [ORA11DB] Files:1 Time: 0s
Restart Phase #:90 [ORA11DB] Files:1 Time: 0s
Serial Phase #:91 [ORA11DB] Files:2 Time: 1s
Restart Phase #:92 [ORA11DB] Files:1 Time: 0s
Serial Phase #:93 [ORA11DB] Files:1 Time: 0s
Restart Phase #:94 [ORA11DB] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [ORA11DB] Files:1 Time: 5s
Restart Phase #:96 [ORA11DB] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [ORA11DB] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:98 [ORA11DB] Files:1 Time: 79s
******************* Migration ******************
Serial Phase #:99 [ORA11DB] Files:1 Time: 29s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [ORA11DB] Files:1 Time: 0s
Serial Phase #:101 [ORA11DB] Files:1 Time: 0s
Serial Phase #:102 [ORA11DB] Files:1 Time: 56s
***************** Post Upgrade *****************
Serial Phase #:103 [ORA11DB] Files:1 Time: 56s
**************** Summary report ****************
Serial Phase #:104 [ORA11DB] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [ORA11DB] Files:1 Time: 0s
Serial Phase #:106 [ORA11DB] Files:1 Time: 0s
Serial Phase #:107 [ORA11DB] Files:1 Time: 25s
------------------------------------------------------
Phases [0-107] End Time:[2025_06_21 18:13:42]
------------------------------------------------------
Grand Total Time: 1313s
LOG FILES: (/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/catupgrd*.log)
Upgrade Summary Report Located in:
/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/upg_summary.log
Grand Total Upgrade Time: [0d:0h:21m:53s]
|
위 스크립트 완료 후 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
|
$ cat /oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 06-21-2025 18:13:1
Database Name: ORA11DB
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.27.0.0.0 00:12:42
JServer JAVA Virtual Machine UPGRADED 19.27.0.0.0 00:03:16
Oracle XDK UPGRADED 19.27.0.0.0 00:00:29
Oracle Database Java Packages UPGRADED 19.27.0.0.0 00:00:10
Oracle Workspace Manager UPGRADED 19.27.0.0.0 00:00:46
Oracle Real Application Clusters UPGRADED 19.27.0.0.0 00:00:00
Oracle XML Database VALID 19.27.0.0.0 00:00:00
Datapatch 00:01:11
Final Actions 00:01:47
Post Upgrade 00:00:50
Total Upgrade Time: 00:20:20
Database time zone version is 14. It is older than current release time
zone version 44. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time: [0d:0h:21m:53s]
|
정상임, 타임존은 업데이트가 필요함
다시 기동(startup upgrade가 아닌 일반모드로 기동)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 637534208 bytes
Database Buffers 1862270976 bytes
Redo Buffers 7831552 bytes
Database mounted.
Database opened.
|
업그레이드 후 추가 스크립트 실행(이미 수행됨)
xxx$ cd $ORACLE_HOME/rdbms/admin
xxx$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catuppst.sql
*catuppst.sql : 수동 업그레이드를 진행했다면, 이 스크립트는 반드시 실행해야 함. DBUA를 사용한 경우에는 DBUA가 자동으로 실행해주기 때문에 따로 수행할 필요는 없음
이 스크립트는 UPGRADE 모드에서 실행하면 안됨, 반드시 ORACLE_HOME/rdbms/admin 디렉토리에 있는 catuppst.sql 파일을 통해 실행해야 함
이 스크립트는 데이터베이스가 UPGRADE 모드가 아닐 때 수행해야 하는 나머지 업그레이드 작업을 처리함
만약 Oracle 홈 디렉토리에 번들 패치나 PSU/BP가 설치되어 있다면, 해당 패치도 자동으로 적용됨
주의: 수동 업그레이드 후 catuppst.sql을 실행하지 않으면 시간이 지날수록 데이터베이스 성능이 저하될 수 있음
*사실 이 스크립트는 별도로 수행하지 않아도됨, dbupgrade 스크립트 안에서 이미 실행되었음, 업그레이드 로그를 확인하면 "BEGIN catuppst" 부분에서 찾아볼수 있음
dbupgrade의 로그 /oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/catupgrd*.log)
|
1
2
3
4
5
6
7
8
9
|
$ cat /oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621175149/catupgrd*.log | grep -A 7 "BEGIN catuppst"
18:12:26 SQL> Rem BEGIN catuppst.sql
18:12:26 SQL> Rem *********************************************************************
18:12:26 SQL> Rem Set identifier to POSTUP for errorlogging
18:12:26 SQL>
18:12:26 SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
18:12:26 SQL>
18:12:26 SQL> -- DBUA_TIMESTAMP: db shutdown/startup is finished by now
18:12:26 SQL> SELECT dbms_registry_sys.time_stamp('DBRESTART') as timestamp from dual;
|
utlrp 실행(invaild object 확인)
|
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
|
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-06-21 20:01:26
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 2025-06-21 20:06:09
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
|
컴포넌트 상태 확인1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status
from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- --------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and T 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
RAC Oracle Real Application Cluste 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
8 rows selected.
|
컴포넌트 상태 확인2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL> @?/rdbms/admin/utlusts.sql
Oracle Database Release 19 Post-Upgrade Status Tool 06-21-2025 20:08:0
Database Name: ORA11DB
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.27.0.0.0 00:12:42
JServer JAVA Virtual Machine VALID 19.27.0.0.0 00:03:16
Oracle XDK VALID 19.27.0.0.0 00:00:29
Oracle Database Java Packages VALID 19.27.0.0.0 00:00:10
Oracle Workspace Manager VALID 19.27.0.0.0 00:00:46
Oracle Real Application Clusters VALID 19.27.0.0.0 00:00:00
Oracle XML Database VALID 19.27.0.0.0 00:00:00
Datapatch 00:01:11
Final Actions 00:01:47
Post Upgrade 00:00:50
Post Compile 00:04:42
Total Upgrade Time: 00:25:02
Database time zone version is 14. It is older than current release time
zone version 44. Time zone upgrade is needed using the DBMS_DST package.
|
upg_summary.log와 결과가 동일함, 이 스크립트도 dbupgrade 스크립트 마지막에 포함되어 있는듯함
패치 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set linesize 200
col action_time for a20
col action for a10
col status for a10
col description for a60
col version for a10
select to_char(action_time, 'yyyymmdd hh24:mi:ss') as action_time
,action
,status
,description
,source_version
,target_version
,patch_id
from dba_registry_sqlpatch
order by action_time;
ACTION_TIME ACTION STATUS DESCRIPTION SOURCE_VERSION TARGET_VERSION PATCH_ID
-------------------- ---------- ---------- ------------------------------------------------------------ --------------- --------------- ----------
20250621 18:10:47 APPLY SUCCESS Database Release Update : 19.27.0.0.250415 (37642901) 19.1.0.0.0 19.27.0.0.0 37642901
|
타임존 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 14 0
SQL>
set lines 200 pages 1000
col property_name for a30
col value for a30
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
|
타임존 업그레이드전 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL> @?/rdbms/admin/utltz_upg_check.sql
Session altered.
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 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv14 .
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 DSTv44 .
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 utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
|
타임존 업그레이드
*타임존 업그레이드시 자동으로 db가 2번 재기동됨
|
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
|
SQL> @?/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv44 .
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 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 654311424 bytes
Database Buffers 1845493760 bytes
Redo Buffers 7831552 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 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 654311424 bytes
Database Buffers 1845493760 bytes
Redo Buffers 7831552 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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv44 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
|
완료됨
타임존 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_44.dat 44 0
SQL>
set lines 200 pages 1000
col property_name for a30
col value for a30
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 44
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
|
타임존이 14에서 44로 업그레이됨
control_files 파라미터 확인
|
1
2
3
4
5
|
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/ORA11DB/CONTROLFILE/current.302.1204392593
|
rac 상태로 만들기 위해 다시 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
29
30
31
32
33
34
|
$ cd $ORACLE_HOME/dbs
$ vi initORA11DB1.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORA11DB/adump'
*.audit_trail='none'
*.cluster_database=false 기존
*.cluster_database=true 수정
*.compatible='11.2.0.4.0'
*.control_files='+DATA' 기존
*.control_files='+DATA/ORA11DB/CONTROLFILE/current.302.1204392593' 수정
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='ORA11DB'
*.diagnostic_dest='/oracle/app/oracle'
#ORA11DB1.instance_number=1 기존(주석)
#ORA11DB2.instance_number=2 기존(주석)
ORA11DB1.instance_number=1 수정(주석 해제)
ORA11DB2.instance_number=2 수정(주석 해제)
*.log_archive_dest_1='location=+RECO'
*.log_archive_format='ora11db_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=834666496
*.processes=150
*.remote_listener='ora19rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=2503999488
#ORA11DB1.thread=1 기존(주석)
#ORA11DB2.thread=2 기존(주석)
#ORA11DB1.undo_tablespace='UNDOTBS1' 기존(주석)
#ORA11DB2.undo_tablespace='UNDOTBS2' 기존(주석)
ORA11DB1.thread=1 수정(주석 해제)
ORA11DB2.thread=2 수정(주석 해제)
ORA11DB1.undo_tablespace='UNDOTBS1' 수정(주석 해제)
ORA11DB2.undo_tablespace='UNDOTBS2' 수정(주석 해제)
|
rac 파라미터 주석 해제 및 cluster_database 파라미터 true 처리, control_files 파라미터 변경
spfile ASM에 생성
|
1
2
3
4
5
6
7
8
|
SQL> shutdown immediate
SQL> create spfile='+DATA' from pfile;
create spfile='+DATA' from pfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
|
이유는 모르겠지만 에러남
일단 pfile로 db 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 603979776 bytes
Database Buffers 1895825408 bytes
Redo Buffers 7831552 bytes
Database mounted.
Database opened.
|
spfile ASM에 생성
|
1
2
3
|
SQL> create spfile='+DATA' from pfile;
File created.
|
이제 정상적으로 생성됨
생성된 spfile 확인
|
1
2
|
$ asmcmd ls +data/ora11db/parameterfile
spfile.293.1204402935
|
pfile에 asm spfile 경로 삽입
|
1
2
|
$ mv $ORACLE_HOME/dbs/initORA11DB1.ora $ORACLE_HOME/dbs/initORA11DB1.ora.bak
$ echo "spfile='+data/ora11db/parameterfile/spfile.293.1204402935'" > $ORACLE_HOME/dbs/initORA11DB1.ora
|
2번노드에도 pfile 복제
|
1
2
3
|
$ cd $ORACLE_HOME/dbs/
$ scp initORA11DB1.ora 192.168.137.20:/oracle/app/oracle/product/19c/dbs/initORA11DB2.ora
initORA11DB1.ora 100% 59 68.3KB/s 00:00
|
grid에 instance 리소스 추가 등록(2번 노드)
|
1
2
3
|
$ srvctl add database -d ORA11DB -o $ORACLE_HOME
$ srvctl add instance -d ORA11DB -i ORA11DB1 -n ora19rac1
$ srvctl add instance -d ORA11DB -i ORA11DB2 -n ora19rac2
|
grid에 db spfile 경로 반영
|
1
|
$ srvctl modify database -d ORA11DB -spfile '+data/ora11db/parameterfile/spfile.293.1204402935'
|
db 기동
|
1
|
$ srvctl start database -d ORA11DB
|
grid 확인
|
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
|
$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.chad
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.net1.network
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.ons
ONLINE ONLINE ora19rac1 STABLE
ONLINE ONLINE ora19rac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE ora19rac1 STABLE
OFFLINE OFFLINE ora19rac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE ora19rac2 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.VOTE.dg(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 Started,STABLE
2 ONLINE ONLINE ora19rac2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE ora19rac1 STABLE
2 ONLINE ONLINE ora19rac2 STABLE
ora.cvu
1 ONLINE ONLINE ora19rac2 STABLE
ora.ora11db.db
1 ONLINE ONLINE ora19rac1 Open,HOME=/oracle/ap
p/oracle/product/19c
,STABLE
2 ONLINE ONLINE ora19rac2 Open,HOME=/oracle/ap
p/oracle/product/19c
,STABLE
ora.ora19db.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.ora19rac1.vip
1 ONLINE ONLINE ora19rac1 STABLE
ora.ora19rac2.vip
1 ONLINE ONLINE ora19rac2 STABLE
ora.qosmserver
1 ONLINE ONLINE ora19rac2 STABLE
ora.scan1.vip
1 ONLINE ONLINE ora19rac2 STABLE
--------------------------------------------------------------------------------
|
11g였던 db(ora11db)가 정상적으로 올라옴
password 파일 ASM으로 이동
|
1
2
3
4
5
|
$ asmcmd pwcreate --dbuniquename ORA11DB --format 12 +DATA/ORA11DB/PASSWORD/orapwdORA11DB oracle
ASMCMD-9465
$ asmcmd ls +data/ora11db/password
orapwdora11db
pwdora11db.296.1204403095
|
db config 확인
|
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
|
$ srvctl config database -d ora11db
Database unique name: ORA11DB
Database name:
Oracle home: /oracle/app/oracle/product/19c
Oracle user: oracle
Spfile: +data/ora11db/parameterfile/spfile.293.1204402935
Password file: +DATA/ORA11DB/PASSWORD/orapwdora11db
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: ORA11DB1,ORA11DB2
Configured nodes: ora19rac1,ora19rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
|
모두 정상임
데이터 확인
|
1
2
3
4
5
6
|
SQL> conn miguser/miguser
SQL> select count(*) from migtable;
COUNT(*)
----------
10000000
|
정상적으로 확인됨
용량확인(sys)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn / as sysdba
set lines 200
col owner for a10
col segment_name for a20
select owner, sum(bytes)/1024/1024 as MB
from dba_segments
where owner='MIGUSER'
GROUP BY owner;
OWNER MB
---------- ----------
MIGUSER 1792
|
정상적으로 삽입됨
데이터도 정상적으로 마이그레이션됨
업그레이드 이후에는 fixed_objects와 dictionary, 일반 테이블들의 통계를 수집해주는것이 권장됨
|
1
2
3
|
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_database_stats;
|
업그레이드 후 문제가 없는 경우 compatible 파라미터를 19.0.0으로 변경하면됨
|
1
2
3
|
SQL> alter system set compatible='19.0.0' scope=spfile;
$ srvctl stop database -d ORA11DB
$ srvctl start database -d ORA11DB
|
참고로 테스트한 db를 제거하고 싶은경우 아래 게시글을 참고해서 삭제하면됨
참고 : 오라클 19c RAC db 수동 삭제 가이드 ( https://positivemh.tistory.com/1235 )
참조 :
790559.1
Oracle 19c - Complete checklist for Manual Upgrade for upgrading Oracle 12.x, 18c Container database (CDB) to Oracle 19c (19.x) (Doc ID 2549866.1)
How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)
Database Preupgrade tool (via autoupgrade.jar) check list (Doc ID 2380601.1)
https://dataforum.io/pages/viewpage.action?pageId=10684201
https://positivemh.tistory.com/158
https://gavinsoorma.com.au/knowledge-base/rman-recovery-using-the-switch-database-to-copy-command/
https://dataforum.io/pages/viewpage.action?pageId=10684201
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/tasks-to-prepare-for-oracle-database-upgrades.html#GUID-3C319260-B5CD-4C50-B606-36D060FDF0BE
http://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/upgrading-parallel-upgrade-utility-catctl-pl.html#GUID-9AA7ADC1-E2F7-4C26-9E91-A583D1D694C5
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/upgrade-scenarios-non-cdb-oracle-databases.html
https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/oracle-database-upgrade-utilities.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/dbupgrade-scripts-catupgrd.sql-earlier-release.html
https://docs.oracle.com/en/error-help/db/asmcmd-09465/index.html?r=19c
https://mikedietrichde.com/2018/05/07/new-preupgrade-jar-and-changes-in-mos-note884522-1/
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 11gR2 Raw Device RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |
|---|---|
| 오라클 19c to 19c datapump impdp network_link 옵션 사용 (0) | 2025.05.18 |
| 오라클 19c 핫백업을 이용한 다운타임 최소화 이관 (0) | 2025.05.11 |
| 오라클 10g R2 에서 오라클 11g R2 수동 업그레이드 마이그레이션 방법 (0) | 2024.08.05 |
| 오라클 11g R2 에서 오라클 19c datapump schemas 옵션 이관시 주의사항 (2) | 2022.02.23 |