OS 환경 : Oracle Linux 6.8, 8.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 19.27.0.0
방법 : 오라클 11gR2 Raw Device RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법
본문에서는 11gR2 RAW RAC환경에서 rman 백업받은 백업셋을 이용해 19c ASM RAC에서 복구 및 업그레이드 시키는 방법을 설명함
이전에 싱글 db로 업그레이드 하는 방법을 한번 작성 했었지만 RAW RAC + 내용을 보강해서 작성함
참고 : 오라클 11gR2 to 19c Rman 백업셋 이용 업그레이드 방법 ( https://positivemh.tistory.com/736 )
참고 : 오라클 11gR2 ASM RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 ( https://positivemh.tistory.com/1234 )
그리고 본문에서는 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
|
사전에 아카이브로그가 쌓일 클러스터 공유 파일시스템 생성
아래 링크에서 IV. 공유 스토리지 설정의 "3. FRA 영역을 위한 OCFS2 설정" 부분 참고하여 설정
나의 경우 /backup 이라는 경로로 설정함
|
1
2
3
|
$ df -h /backup
Filesystem Size Used Avail Use% Mounted on
/dev/sdc1 100G 2.3G 98G 3% /backup
|
11gR2 아카이브모드 설정
|
1
2
3
4
5
6
7
8
9
10
|
$ mkdir -p /backup/ora11db
SQL>
alter system set log_archive_format = 'ora11db_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_dest_1 = 'location=/backup/ora11db' scope=spfile;
$ srvctl stop database -d ora11db
$ srvctl start database -d ora11db -o mount
SQL> alter database archivelog; --node 1
SQL> alter database archivelog; --node 2
SQL> alter database open; --node 1
SQL> alter database open; --node 2
|
양쪽 노드에서 로그스위치 테스트
|
1
2
3
4
5
6
7
|
SQL> alter system switch logfile; --node1
System altered.
SQL> alter system switch logfile; --node2
System altered.
|
아카이브로그 확인
|
1
2
3
4
5
6
|
$ ls -al /backup/ora11db/
total 6604
drwxr-xr-x 2 oracle oinstall 3896 Jun 20 23:49 .
drwxrwx--- 5 oracle dba 3896 Jun 20 23:41 ..
-rw-r----- 1 oracle oinstall 4147200 Jun 20 23:49 ora11db_1_19_1202251355.arc
-rw-r----- 1 oracle oinstall 2603520 Jun 20 23:49 ora11db_2_3_1202251355.arc
|
정상적으로 생성됨
테스트
11gR2에서 샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
|
SQL> create table rawtbl tablespace users as select object_id, object_name from dba_objects;
Table created.
SQL> select count(*) from rawtbl;
COUNT(*)
----------
14016
|
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
|
$ mkdir -p /backup/rman
$ rman target /
RMAN>
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/backup/rman/db_ctl_%F';
configure channel device type disk format '/backup/rman/%d_%U_%T_bckset.bk';
RMAN> backup database plus archivelog format '/backup/rman/%d_%U_%T_bckset.bk';
Starting backup at 2025-06-21 00:00:08
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 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=3 RECID=2 STAMP=1204328987
input archived log thread=1 sequence=19 RECID=1 STAMP=1204328979
input archived log thread=1 sequence=20 RECID=3 STAMP=1204329497
input archived log thread=2 sequence=4 RECID=4 STAMP=1204329497
input archived log thread=1 sequence=21 RECID=5 STAMP=1204329512
input archived log thread=2 sequence=5 RECID=6 STAMP=1204329512
input archived log thread=1 sequence=22 RECID=7 STAMP=1204329585
input archived log thread=2 sequence=6 RECID=8 STAMP=1204329585
input archived log thread=1 sequence=23 RECID=9 STAMP=1204329609
input archived log thread=2 sequence=7 RECID=10 STAMP=1204329612
channel ORA_DISK_1: starting piece 1 at 2025-06-21 00:00:18
channel ORA_DISK_1: finished piece 1 at 2025-06-21 00:00:19
piece handle=/backup/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk tag=TAG20250621T000018 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-06-21 00:00:19
Starting backup at 2025-06-21 00:00:19
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=00001 name=/dev/raw/raw4
input datafile file number=00002 name=/dev/raw/raw5
input datafile file number=00003 name=/dev/raw/raw8
input datafile file number=00004 name=/dev/raw/raw9
input datafile file number=00005 name=/dev/raw/raw6
channel ORA_DISK_1: starting piece 1 at 2025-06-21 00:00:20
channel ORA_DISK_1: finished piece 1 at 2025-06-21 00:00:23
piece handle=/backup/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk tag=TAG20250621T000019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2025-06-21 00:00:23
Starting backup at 2025-06-21 00:00:23
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=1 sequence=24 RECID=12 STAMP=1204329624
input archived log thread=2 sequence=8 RECID=11 STAMP=1204329624
channel ORA_DISK_1: starting piece 1 at 2025-06-21 00:00:24
channel ORA_DISK_1: finished piece 1 at 2025-06-21 00:00:25
piece handle=/backup/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk tag=TAG20250621T000024 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-06-21 00:00:25
Starting Control File and SPFILE Autobackup at 2025-06-21 00:00:25
piece handle=/backup/rman/db_ctl_c-357213915-20250621-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-21 00:00:27
|
백업이 완료됨
백업파일 확인
|
1
2
3
4
5
6
7
8
|
$ ls -al /backup/rman/
total 486020
drwxr-xr-x 2 oracle oinstall 3896 Jun 21 00:00 .
drwxrwx--- 6 oracle dba 3896 Jun 20 23:58 ..
-rw-r----- 1 oracle oinstall 18579456 Jun 21 00:00 db_ctl_c-357213915-20250621-00
-rw-r----- 1 oracle oinstall 7801344 Jun 21 00:00 ORA11DB_053sh74i_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 471285760 Jun 21 00:00 ORA11DB_063sh74j_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 6144 Jun 21 00:00 ORA11DB_073sh74o_1_1_20250621_bckset.bk
|
19c db서버로 전송
|
1
2
3
4
5
6
7
8
9
10
11
|
$ scp ./* 192.168.137.10:/oracle/app/oracle/rman/
The authenticity of host '192.168.137.10 (192.168.137.10)' can't be established.
ECDSA key fingerprint is SHA256:FbHVM102/zIQ3O3252eJQQanObVRX22NSqL+Mg46wkQ.
ECDSA key fingerprint is MD5:84:9c:04:a8:c8:97:8d:2f:ec:dd:b4:75:2d:b5:b9:0c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.10' (ECDSA) to the list of known hosts.
oracle@192.168.137.10's password:
db_ctl_c-357213915-20250621-00 100% 18MB 105.1MB/s 00:00
ORA11DB_053sh74i_1_1_20250621_bckset.bk 100% 7619KB 34.9MB/s 00:00
ORA11DB_063sh74j_1_1_20250621_bckset.bk 100% 449MB 132.9MB/s 00:03
ORA11DB_073sh74o_1_1_20250621_bckset.bk 100% 6144 99.3KB/s 00:00
|
전송됨
확인(19c 서버에서)
|
1
2
3
4
5
6
7
8
9
|
$ cd /oracle/app/oracle/rman
$ ls -al
total 486012
drwxr-xr-x 2 oracle oinstall 185 Jun 21 00:16 .
drwxrwxr-x. 12 oracle dba 154 Jun 18 21:14 ..
-rw-r----- 1 oracle oinstall 18579456 Jun 21 00:16 db_ctl_c-357213915-20250621-00
-rw-r----- 1 oracle oinstall 7801344 Jun 21 00:16 ORA11DB_053sh74i_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 471285760 Jun 21 00:16 ORA11DB_063sh74j_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 6144 Jun 21 00:16 ORA11DB_073sh74o_1_1_20250621_bckset.bk
|
정상적으로 받아짐
pfile 생성(11g)
|
1
2
3
|
SQL> create pfile from spfile;
File created.
|
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 18084
drwxr-xr-x 2 oracle oinstall 115 Jun 20 23:58 .
drwxr-xr-x 74 oracle oinstall 4096 May 27 22:25 ..
-rw-rw---- 1 oracle oinstall 1544 Jun 21 00:13 hc_ORA11DB1.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 1420 Jun 21 00:19 initORA11DB1.ora
-rw-r----- 1 oracle oinstall 1536 May 27 22:42 orapwORA11DB1
-rw-r----- 1 oracle oinstall 18497536 Jun 21 00:00 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
36
|
$ 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='/dev/raw/raw10','/dev/raw/raw11' 기존
*.control_files='+DATA' 수정
*.db_block_size=8192
*.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=/backup/ora11db' 기존
*.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' 수정(주석)
*.db_create_file_dest='+DATA'
|
rac 파라미터 주석 및 cluster_database 파라미터 false 처리, control_files, remote_listener 파라미터 변경, db_create_file_dest 파라미터 추가
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
13
|
$ 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-357213915-20250621-00';
restore controlfile from '/oracle/app/oracle/rman/db_ctl_c-357213915-20250621-00';
Starting restore at 2025-06-21 00:38:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 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.295.1204331931
Finished restore at 2025-06-21 00:38:52
|
컨트롤파일이 +DATA/ORA11DB/CONTROLFILE/current.295.1204331931로 restore됨
spfile의 control_files 파라미터 수정
|
1
2
3
|
SQL> alter system set control_files='+DATA/ORA11DB/CONTROLFILE/current.295.1204331931' 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 "/backup/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
|
RMAN> list backup;
list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
5 7.44M DISK 00:00:00 2025-06-21 00:00:18
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000018
Piece Name: /backup/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 19 274359 2025-06-20 23:35:57 281569 2025-06-20 23:49:39
1 20 281569 2025-06-20 23:49:39 282301 2025-06-20 23:58:17
1 21 282301 2025-06-20 23:58:17 282323 2025-06-20 23:58:32
1 22 282323 2025-06-20 23:58:32 282408 2025-06-20 23:59:45
1 23 282408 2025-06-20 23:59:45 282548 2025-06-21 00:00:09
2 3 274357 2025-06-20 23:35:58 281575 2025-06-20 23:49:46
2 4 281575 2025-06-20 23:49:46 282304 2025-06-20 23:58:17
2 5 282304 2025-06-20 23:58:17 282326 2025-06-20 23:58:32
2 6 282326 2025-06-20 23:58:32 282412 2025-06-20 23:59:45
2 7 282412 2025-06-20 23:59:45 282552 2025-06-21 00:00:12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6 Full 449.45M DISK 00:00:03 2025-06-21 00:00:22
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000019
Piece Name: /backup/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw4
2 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw5
3 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw8
4 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw9
5 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw6
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7 5.50K DISK 00:00:00 2025-06-21 00:00:24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000024
Piece Name: /backup/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 24 282548 2025-06-21 00:00:09 282585 2025-06-21 00:00:24
2 8 282552 2025-06-21 00:00:12 282582 2025-06-21 00:00:24
|
현재 rman 백업파일이 /backup/rman에 있다고 알고 있음
그리고 full backup의 마지막 scn은 282562임
19c 서버에서 rman 백업파일 다시 카탈로깅
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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-357213915-20250621-00
File Name: /oracle/app/oracle/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk
File Name: /oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
File Name: /oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.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-357213915-20250621-00
File Name: /oracle/app/oracle/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk
File Name: /oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
File Name: /oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk
|
이전 경로로 보이는 백업 목록 삭제(crosscheck 후 expired delete)
|
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
|
RMAN> crosscheck backup;
crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk RECID=5 STAMP=1204329618
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/app/oracle/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk RECID=9 STAMP=1204332384
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk RECID=6 STAMP=1204329620
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk RECID=10 STAMP=1204332384
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk RECID=7 STAMP=1204329624
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk RECID=11 STAMP=1204332384
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/app/oracle/rman/db_ctl_c-357213915-20250621-00 RECID=8 STAMP=1204332384
Crosschecked 7 objects
RMAN> delete expired backup;
delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 EXPIRED DISK /backup/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk
6 6 1 1 EXPIRED DISK /backup/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
7 7 1 1 EXPIRED DISK /backup/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk
|
다시 백업 확인
|
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
|
RMAN> list backup;
list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
5 7.44M DISK 00:00:00 2025-06-21 00:00:18
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000018
Piece Name: /oracle/app/oracle/rman/ORA11DB_053sh74i_1_1_20250621_bckset.bk
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 19 274359 2025-06-20 23:35:57 281569 2025-06-20 23:49:39
1 20 281569 2025-06-20 23:49:39 282301 2025-06-20 23:58:17
1 21 282301 2025-06-20 23:58:17 282323 2025-06-20 23:58:32
1 22 282323 2025-06-20 23:58:32 282408 2025-06-20 23:59:45
1 23 282408 2025-06-20 23:59:45 282548 2025-06-21 00:00:09
2 3 274357 2025-06-20 23:35:58 281575 2025-06-20 23:49:46
2 4 281575 2025-06-20 23:49:46 282304 2025-06-20 23:58:17
2 5 282304 2025-06-20 23:58:17 282326 2025-06-20 23:58:32
2 6 282326 2025-06-20 23:58:32 282412 2025-06-20 23:59:45
2 7 282412 2025-06-20 23:59:45 282552 2025-06-21 00:00:12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6 Full 449.45M DISK 00:00:03 2025-06-21 00:00:22
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000019
Piece Name: /oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw4
2 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw5
3 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw8
4 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw9
5 Full 282562 2025-06-21 00:00:20 NO /dev/raw/raw6
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7 5.50K DISK 00:00:00 2025-06-21 00:00:24
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000024
Piece Name: /oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 24 282548 2025-06-21 00:00:09 282585 2025-06-21 00:00:24
2 8 282552 2025-06-21 00:00:12 282582 2025-06-21 00:00:24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8 Full 17.70M DISK 00:00:00 2025-06-21 00:46:24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20250621T000026
Piece Name: /oracle/app/oracle/rman/db_ctl_c-357213915-20250621-00
SPFILE Included: Modification time: 2025-06-20 23:48:28
SPFILE db_unique_name: ORA11DB
Control File Included: Ckp SCN: 282594 Ckp time: 2025-06-21 00:00:26
|
rman 백업파일 경로가 정상적으로 /oracle/app/oracle/rman으로 보임
그리고 full backup의 마지막 scn은 282562임
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
|
RMAN>
run
{
set until scn 282562;
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 00:49:20
using channel ORA_DISK_1
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: reading from backup piece /oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_063sh74j_1_1_20250621_bckset.bk tag=TAG20250621T000019
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 00:49:27
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1204332567 file name=+DATA/ORA11DB/DATAFILE/system.296.1204332561
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1204332568 file name=+DATA/ORA11DB/DATAFILE/sysaux.293.1204332561
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1204332568 file name=+DATA/ORA11DB/DATAFILE/undotbs1.292.1204332561
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1204332568 file name=+DATA/ORA11DB/DATAFILE/undotbs2.291.1204332561
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1204332568 file name=+DATA/ORA11DB/DATAFILE/users.290.1204332561
renamed tempfile 1 to +DATA in control file
Starting recover at 2025-06-21 00:49:28
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2025-06-21 00:49:29
|
restore 및 recover 완료됨
11g에서 테이블에 데이터 추가 삽입
|
1
2
3
|
SQL>
insert into rawtbl select * from rawtbl;
commit;
|
건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from rawtbl;
COUNT(*)
----------
28032
|
로그스위치 및 체크포인트 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
|
$ rman target /
RMAN> backup archivelog all not backed up;
Starting backup at 2025-06-21 00:47:08
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 instance=ORA11DB1 device type=DISK
skipping archived logs of thread 1 from sequence 19 to 24; already backed up
skipping archived logs of thread 2 from sequence 3 to 8; 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=2 sequence=9 RECID=13 STAMP=1204329758
input archived log thread=1 sequence=25 RECID=15 STAMP=1204332397
input archived log thread=2 sequence=10 RECID=14 STAMP=1204329759
input archived log thread=2 sequence=11 RECID=19 STAMP=1204332403
input archived log thread=1 sequence=26 RECID=16 STAMP=1204332400
input archived log thread=1 sequence=27 RECID=17 STAMP=1204332400
input archived log thread=1 sequence=28 RECID=18 STAMP=1204332400
input archived log thread=1 sequence=29 RECID=20 STAMP=1204332404
input archived log thread=2 sequence=12 RECID=22 STAMP=1204332430
input archived log thread=1 sequence=30 RECID=21 STAMP=1204332428
channel ORA_DISK_1: starting piece 1 at 2025-06-21 00:47:19
channel ORA_DISK_1: finished piece 1 at 2025-06-21 00:47:20
piece handle=/backup/rman/ORA11DB_093sh9sn_1_1_20250621_bckset.bk tag=TAG20250621T004719 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-06-21 00:47:20
Starting Control File and SPFILE Autobackup at 2025-06-21 00:47:20
piece handle=/backup/rman/db_ctl_c-357213915-20250621-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-21 00:47:21
|
아카이브로그 백업파일 11g에서 19c로 복사(11g)
|
1
2
3
4
5
6
7
8
9
10
11
|
$ cd /backup/rman
$ ls -ltr
total 505596
-rw-r----- 1 oracle oinstall 7801344 Jun 21 00:00 ORA11DB_053sh74i_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 471285760 Jun 21 00:00 ORA11DB_063sh74j_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 6144 Jun 21 00:00 ORA11DB_073sh74o_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 18579456 Jun 21 00:00 db_ctl_c-357213915-20250621-00
-rw-r----- 1 oracle oinstall 1472000 Jun 21 00:47 ORA11DB_093sh9sn_1_1_20250621_bckset.bk
-rw-r----- 1 oracle oinstall 18579456 Jun 21 00:47 db_ctl_c-357213915-20250621-01
$ scp ./ORA11DB_093sh9sn_1_1_20250621_bckset.bk 192.168.137.10:/oracle/app/oracle/rman/
ORA11DB_093sh9sn_1_1_20250621_bckset.bk 100% 1438KB 22.2MB/s 00:00
|
카탈로그 등록(19c)(본문처럼 백업이후 추가로 발생된 아카이브로그가 존재하는 경우 이 경로에 복사 후 카탈로깅)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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/ORA11DB_093sh9sn_1_1_20250621_bckset.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/ORA11DB_093sh9sn_1_1_20250621_bckset.bk
|
백업 확인
|
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
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
5 B A A DISK 2025-06-21 00:00:18 1 1 NO TAG20250621T000018
6 B F A DISK 2025-06-21 00:00:22 1 1 NO TAG20250621T000019
7 B A A DISK 2025-06-21 00:00:24 1 1 NO TAG20250621T000024
8 B F A DISK 2025-06-21 00:46:24 1 1 NO TAG20250621T000026
9 B A A DISK 2025-06-21 00:53:58 1 1 NO TAG20250621T004719
|
새로 추가한 아카이브로그 백업셋이 9번에 추가됨(TAG20250621T004719)
백업 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
|
RMAN> list backup tag=TAG20250621T004719;
list backup tag=TAG20250621T004719;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
9 1.40M DISK 00:00:00 2025-06-21 00:53:58
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20250621T004719
Piece Name: /oracle/app/oracle/rman/ORA11DB_093sh9sn_1_1_20250621_bckset.bk
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 25 282585 2025-06-21 00:00:24 306512 2025-06-21 00:46:37
1 26 306512 2025-06-21 00:46:37 306516 2025-06-21 00:46:40
1 27 306516 2025-06-21 00:46:40 306519 2025-06-21 00:46:40
1 28 306519 2025-06-21 00:46:40 306522 2025-06-21 00:46:40
1 29 306522 2025-06-21 00:46:40 306529 2025-06-21 00:46:44
1 30 306529 2025-06-21 00:46:44 306559 2025-06-21 00:47:08
2 9 282582 2025-06-21 00:00:24 302584 2025-06-21 00:02:38
2 10 302584 2025-06-21 00:02:38 302586 2025-06-21 00:02:38
2 11 303248 2025-06-21 00:13:57 306526 2025-06-21 00:46:43
2 12 306526 2025-06-21 00:46:43 306563 2025-06-21 00:47:10
|
정상적으로 아카이브로그 파일 백업본이 인식됨
마지막 scn이 306526임
추가 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 306526;
recover database;
}
executing command: SET until clause
Starting recover at 2025-06-21 00:58:45
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=1 sequence=24
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=8
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_073sh74o_1_1_20250621_bckset.bk tag=TAG20250621T000024
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_24.409.1204333127 thread=1 sequence=24
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_8.410.1204333127 thread=2 sequence=8
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=9
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=10
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=28
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/rman/ORA11DB_093sh9sn_1_1_20250621_bckset.bk
channel ORA_DISK_1: piece handle=/oracle/app/oracle/rman/ORA11DB_093sh9sn_1_1_20250621_bckset.bk tag=TAG20250621T004719
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_2_seq_9.414.1204333129 thread=2 sequence=9
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_25.411.1204333129 thread=1 sequence=25
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_10.415.1204333129 thread=2 sequence=10
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_2_seq_11.412.1204333129 thread=2 sequence=11
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_26.413.1204333129 thread=1 sequence=26
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_27.416.1204333129 thread=1 sequence=27
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_28.417.1204333129 thread=1 sequence=28
archived log file name=+RECO/ORA11DB/ARCHIVELOG/2025_06_21/thread_1_seq_29.418.1204333129 thread=1 sequence=29
media recovery complete, elapsed time: 00:00:02
Finished recover at 2025-06-21 00:58:51
|
recover가 완료됨
redo 경로 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
---------- ------------------------------------------------------------ --- ---------------- ----------
1 /dev/raw/raw12 NO CURRENT 50
2 /dev/raw/raw13 YES ACTIVE 50
3 /dev/raw/raw14 NO CURRENT 50
4 /dev/raw/raw15 YES ACTIVE 50
|
필요시 redo 경로를 변경(만약 동일 서버에서 이 작업을 진행하는 경우 기존 redo를 덮어쓸수 있기때문에 resetlogs 기동 전 mount 상태에서 redo 경로를 변경해줘야함)
|
1
2
3
4
|
SQL> alter database rename file '/dev/raw/raw12' to '+DATA';
SQL> alter database rename file '/dev/raw/raw13' to '+DATA';
SQL> alter database rename file '/dev/raw/raw14' to '+DATA';
SQL> alter database rename file '/dev/raw/raw15' to '+DATA';
|
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
|
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 1 +DATA/ORA11DB/ONLINELOG/group_1.284.1204333259 NO CURRENT 50
1 2 +DATA/ORA11DB/ONLINELOG/group_2.289.1204333259 YES UNUSED 50
2 3 +DATA/ORA11DB/ONLINELOG/group_3.288.1204333259 YES INACTIVE 50
2 4 +DATA/ORA11DB/ONLINELOG/group_4.287.1204333259 YES UNUSED 50
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1 3234 27-MAY-25 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192
+DATA/ORA11DB/TEMPFILE/temp.286.1204333263
0
|
redo 크기가 50mb로 너무 작기 때문에 재생성
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
alter database add logfile thread 1 group 5 '+DATA' size 200m;
alter database add logfile thread 1 group 6 '+DATA' size 200m;
alter database add logfile thread 1 group 7 '+DATA' size 200m;
alter database add logfile thread 2 group 8 '+DATA' size 200m;
alter database add logfile thread 2 group 9 '+DATA' size 200m;
alter database add logfile thread 2 group 10 '+DATA' size 200m;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
|
추가는 group 번호를 신규번호로 해서 잘되겠지만 drop은 alter system log switch 및 alter system checkpoint 명령을 이용해 전환하면서 삭제해야함
redo 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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 7 +DATA/ORA11DB/ONLINELOG/group_7.298.1204333773 YES UNUSED 200
1 6 +DATA/ORA11DB/ONLINELOG/group_6.297.1204333773 NO CURRENT 200
1 5 +DATA/ORA11DB/ONLINELOG/group_5.285.1204333759 YES INACTIVE 200
2 10 +DATA/ORA11DB/ONLINELOG/group_10.301.1204333787 YES UNUSED 200
2 9 +DATA/ORA11DB/ONLINELOG/group_9.300.1204333787 YES UNUSED 200
2 8 +DATA/ORA11DB/ONLINELOG/group_8.299.1204333785 YES UNUSED 200
2 3 +DATA/ORA11DB/ONLINELOG/group_3.288.1204333259 YES INACTIVE 50
7 rows selected.
|
redo group 1,2,4는 지워졌지만 3은 현재 삭제가 불가능해서 놔둠(1번노드만 기동한거라 괜찮음)
system, sysaux 사이즈 증가
|
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 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;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 +DATA/ORA11DB/DATAFILE/system.296.1204332561 .68 32 YES AVAILABLE SYSTEM
SYSAUX 2 +DATA/ORA11DB/DATAFILE/sysaux.293.1204332561 .59 32 YES AVAILABLE ONLINE
UNDOTBS1 3 +DATA/ORA11DB/DATAFILE/undotbs1.292.1204332561 .2 32 YES AVAILABLE ONLINE
UNDOTBS2 4 +DATA/ORA11DB/DATAFILE/undotbs2.291.1204332561 .2 32 YES AVAILABLE ONLINE
USERS 5 +DATA/ORA11DB/DATAFILE/users.290.1204332561 0 32 YES AVAILABLE ONLINE
SQL> alter database datafile 1 resize 1g;
Database altered.
SQL> alter database datafile 2 resize 1g;
Database altered.
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 +DATA/ORA11DB/DATAFILE/system.296.1204332561 1 32 YES AVAILABLE SYSTEM
SYSAUX 2 +DATA/ORA11DB/DATAFILE/sysaux.293.1204332561 1 32 YES AVAILABLE ONLINE
UNDOTBS1 3 +DATA/ORA11DB/DATAFILE/undotbs1.292.1204332561 .2 32 YES AVAILABLE ONLINE
UNDOTBS2 4 +DATA/ORA11DB/DATAFILE/undotbs2.291.1204332561 .2 32 YES AVAILABLE ONLINE
USERS 5 +DATA/ORA11DB/DATAFILE/users.290.1204332561 0 32 YES AVAILABLE ONLINE
|
정상적으로 resize 됨
수동 업그레이드 스크립트 실행
*작업 전 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/upgrade20250621011810]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20250621011810/catupgrd_catcon_63764.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250621011810/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20250621011810/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/upgrade20250621011812/catupgrd_catcon_63764.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/catupgrd_*.lst] files for spool files, if any
Log file directory = [/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812]
Parallel SQL Process Count = 4
Components in [ORA11DB]
Installed [CATALOG CATPROC OWM RAC]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM SDO WK XDB XML XOQ]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2025_06_21 01:18:13]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORA11DB] Files:1 Time: 65s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORA11DB] Files:5 Time: 29s
Restart Phase #:2 [ORA11DB] Files:1 Time: 0s
*********** 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: 11s
***************** Catproc Start ****************
Serial Phase #:6 [ORA11DB] Files:1 Time: 8s
***************** 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: 19s
Restart Phase #:10 [ORA11DB] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [ORA11DB] Files:1 Time: 44s
Restart Phase #:12 [ORA11DB] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [ORA11DB] Files:95 Time: 4s
Restart Phase #:14 [ORA11DB] Files:1 Time: 1s
Parallel Phase #:15 [ORA11DB] Files:122 Time: 7s
Restart Phase #:16 [ORA11DB] Files:1 Time: 0s
Serial Phase #:17 [ORA11DB] Files:29 Time: 2s
Restart Phase #:18 [ORA11DB] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [ORA11DB] Files:31 Time: 12s
Restart Phase #:20 [ORA11DB] Files:1 Time: 1s
Serial Phase #:21 [ORA11DB] Files:3 Time: 8s
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: 97s
Restart Phase #:26 [ORA11DB] Files:1 Time: 0s
Serial Phase #:27 [ORA11DB] Files:1 Time: 0s
Serial Phase #:28 [ORA11DB] Files:8 Time: 3s
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: 0s
Serial Phase #:34 [ORA11DB] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORA11DB] Files:300 Time: 15s
Serial Phase #:36 [ORA11DB] Files:1 Time: 0s
Restart Phase #:37 [ORA11DB] Files:1 Time: 1s
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: 41s
Restart Phase #:41 [ORA11DB] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORA11DB] Files:13 Time: 94s
Restart Phase #:43 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:44 [ORA11DB] Files:11 Time: 7s
Restart Phase #:45 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:46 [ORA11DB] Files:3 Time: 0s
Restart Phase #:47 [ORA11DB] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [ORA11DB] Files:1 Time: 66s
Restart Phase #:49 [ORA11DB] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORA11DB] Files:1 Time: 19s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORA11DB] Files:1 Time: 0s
Restart Phase #:52 [ORA11DB] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [ORA11DB] Files:2 Time: 44s
***************** Upgrading XDB ****************
Restart Phase #:54 [ORA11DB] Files:1 Time: 0s
Serial Phase #:56 [ORA11DB] Files:3 Time: 0s
Serial Phase #:57 [ORA11DB] Files:3 Time: 0s
Parallel Phase #:58 [ORA11DB] Files:10 Time: 1s
Parallel Phase #:59 [ORA11DB] Files:25 Time: 0s
Serial Phase #:60 [ORA11DB] Files:4 Time: 0s
Serial Phase #:61 [ORA11DB] Files:1 Time: 0s
Serial Phase #:62 [ORA11DB] Files:32 Time: 1s
Serial Phase #:63 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:64 [ORA11DB] Files:6 Time: 0s
Serial Phase #:65 [ORA11DB] Files:2 Time: 0s
Serial Phase #:66 [ORA11DB] Files:3 Time: 1s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [ORA11DB] Files:1 Time: 0s
Serial Phase #:69 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:70 [ORA11DB] Files:2 Time: 0s
Restart Phase #:71 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:72 [ORA11DB] Files:2 Time: 1s
Serial Phase #:73 [ORA11DB] Files:2 Time: 0s
***************** 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: 1s
Restart Phase #:78 [ORA11DB] Files:1 Time: 0s
Serial Phase #:79 [ORA11DB] Files:1 Time: 0s
Restart Phase #:80 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:81 [ORA11DB] Files:3 Time: 1s
Restart Phase #:82 [ORA11DB] Files:1 Time: 0s
Serial Phase #:83 [ORA11DB] Files:1 Time: 0s
Restart Phase #:84 [ORA11DB] Files:1 Time: 0s
Serial Phase #:85 [ORA11DB] Files:1 Time: 1s
Restart Phase #:86 [ORA11DB] Files:1 Time: 0s
Parallel Phase #:87 [ORA11DB] Files:4 Time: 0s
Restart Phase #:88 [ORA11DB] Files:1 Time: 0s
Serial Phase #:89 [ORA11DB] Files:1 Time: 1s
Restart Phase #:90 [ORA11DB] Files:1 Time: 0s
Serial Phase #:91 [ORA11DB] Files:2 Time: 0s
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: 1s
Restart Phase #:96 [ORA11DB] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [ORA11DB] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:98 [ORA11DB] Files:1 Time: 66s
******************* Migration ******************
Serial Phase #:99 [ORA11DB] Files:1 Time: 26s
*** 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: 52s
***************** Post Upgrade *****************
Serial Phase #:103 [ORA11DB] Files:1 Time: 40s
**************** Summary report ****************
Serial Phase #:104 [ORA11DB] Files:1 Time: 0s
*** 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 01:34:32]
------------------------------------------------------
Grand Total Time: 980s
LOG FILES: (/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/catupgrd*.log)
Upgrade Summary Report Located in:
/oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/upg_summary.log
Grand Total Upgrade Time: [0d:0h:16m:20s]
|
위 스크립트 완료 후 db가 종료됨
업그레이드 요약 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ cat /oracle/app/oracle/product/19c/cfgtoollogs/ORA11DB/upgrade20250621011812/upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 06-21-2025 01:34:0
Database Name: ORA11DB
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.27.0.0.0 00:11:54
Oracle Workspace Manager UPGRADED 19.27.0.0.0 00:00:42
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:00:57
Final Actions 00:01:31
Post Upgrade 00:00:35
Total Upgrade Time: 00:14:55
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:16m:20s]
|
정상임, 타임존은 업데이트가 필요함
다시 기동(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/upgrade20250621011812/catupgrd*.log | grep -A 7 "BEGIN catuppst"
01:33:30 SQL> Rem BEGIN catuppst.sql
01:33:30 SQL> Rem *********************************************************************
01:33:30 SQL> Rem Set identifier to POSTUP for errorlogging
01:33:30 SQL>
01:33:30 SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
01:33:30 SQL>
01:33:30 SQL> -- DBUA_TIMESTAMP: db shutdown/startup is finished by now
01:33:30 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 09:51:48
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 09:55:57
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
|
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
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
|
컴포넌트 상태 확인2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> @?/rdbms/admin/utlusts.sql
Oracle Database Release 19 Post-Upgrade Status Tool 06-21-2025 10:22:1
Database Name: ORA11DB
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.27.0.0.0 00:11:54
Oracle Workspace Manager VALID 19.27.0.0.0 00:00:42
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:00:57
Final Actions 00:01:31
Post Upgrade 00:00:35
Post Compile 00:04:09
Total Upgrade Time: 00:19:04
Database time zone version is 14. It is older than current release time
zone version 44. Time zone " - rest of line ignored.he 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 01:32:00 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 553648128 bytes
Database Buffers 1946157056 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 553648128 bytes
Database Buffers 1946157056 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.295.1204331931
|
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/app/oracle/product/19c/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.295.1204331931' 수정
*.db_block_size=8192
*.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='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' 수정(주석해제)
*.db_create_file_dest='+DATA'
|
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.284.1204377467
|
pfile에 asm spfile 경로 삽입
|
1
2
|
$ mv $ORACLE_HOME/dbs/initORA11DB1.ora $ORACLE_HOME/dbs/initORA11DB1.ora.bak
$ echo "spfile='+data/ora11db/parameterfile/spfile.284.1204377467'" > $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 리소스 등록
|
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.284.1204377467'
|
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.287.1204378401
|
정상적으로 생성됨
ASMCMD-9465 에러가 발생했지만 무시가능함, pwcreate 구문에서 패스워드를 평문으로 노출되게 작성해서 발생한 에러임
19c부터는 deprecated 되었기때문에 추후버전에서는 아마 pwcreate 구문에서 엔터입력 후 패스워드를 입력받는 방식을 사용해야하는듯함
db 기동
|
1
|
$ srvctl start database -d ORA11DB
|
ora11db 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.284.1204377467
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
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
--------------------------------------------------------------------------------
|
모두 정상임
db 접속 후 샘플 데이터 확인
|
1
2
3
4
5
|
SQL> select count(*) from rawtbl;
COUNT(*)
----------
28032
|
데이터도 정상적으로 보임
추가. 아까 지우지 못했던 redo 3번 그룹 삭제
|
1
|
SQL> alter database drop logfile group 3;
|
업그레이드 이후에는 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' 카테고리의 다른 글
| 오라클 19c 통계정보 대량 이관시 병렬 처리 (0) | 2025.08.10 |
|---|---|
| 오라클 19c 통계정보 이관 방법 (0) | 2025.08.10 |
| 오라클 11gR2 ASM RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |
| 오라클 19c to 19c datapump impdp network_link 옵션 사용 (0) | 2025.05.18 |
| 오라클 19c 핫백업을 이용한 다운타임 최소화 이관 (1) | 2025.05.11 |