내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8, 8.1 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 19.3.0.0
방법 : 오라클 11g R2 에서 오라클 19c Rman 백업셋 이용 업그레이드 방법
11gR2에서 rman 백업받은 백업셋을 이용해 19c 에서 복구 및 업그레이드 시키는 방법을 설명함
사전에 아카이브모드가 활성화 되어있어야함
테스트1. 11gR2 rman 백업셋 백업 후 19c 에서 restore, recover, 업그레이드
테스트2. 11gR2 rman 백업셋 백업 후 19c 에서 duplicate 후 업그레이드
서버 정보
db 버전 / 호스트네임 / IP / sid
11g R2 / ora11 / 192.168.137.11 / orcl
19c / ora19 / 192.168.137.19 / oracle19
사전 작업
/etc/hosts에 각 서버 IP, HOSTNAME 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
11gR2
# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.11 ora11
192.168.137.19 ora19
19c
# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.19 ora19
192.168.137.11 ora11
|
방화벽 해제(11g,19c 모두)
|
1
2
3
4
5
6
7
8
9
|
11gR2
# /etc/init.d/iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
19c
# systemctl stop firewalld
# systemctl disable firewalld
|
백업 경로 생성(11g, 19c 모두)
|
1
2
|
$ mkdir -p /app/oracle/rman
$ mkdir -p /app/oracle/arch
|
샘플 유저 및 데이터 생성(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
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
|
테이블스페이스 생성
SQL> create tablespace migts datafile '/app/oracle/oradata/orcl/migts01.dbf' size 5g;
Tablespace created.
유저생성 및 권한 부여
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.
테이블 생성
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) 총 11번 반복 => 약 2gb
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..11 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.
용량확인(sys)
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 1920
|
테스트1. 11gR2 rman 백업셋 백업 후 19c 에서 restore, recover, 업그레이드
rman 환경설정 및 백업(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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/app/oracle/rman/db_ctl_%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/app/oracle/rman/%d_%U_%T.bk';
RMAN> backup database plus archivelog;
Starting backup at 10-JUN-21
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 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=1 sequence=142 RECID=1 STAMP=1074892918
input archived log thread=1 sequence=143 RECID=2 STAMP=1074892919
input archived log thread=1 sequence=144 RECID=3 STAMP=1074892921
input archived log thread=1 sequence=145 RECID=4 STAMP=1074892967
channel ORA_DISK_1: starting piece 1 at 10-JUN-21
channel ORA_DISK_1: finished piece 1 at 10-JUN-21
piece handle=/app/oracle/rman/ORCL_01013458_1_1_20210610.bk tag=TAG20210610T212247 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JUN-21
Starting backup at 10-JUN-21
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=00005 name=/app/oracle/oradata/orcl/migts01.dbf
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JUN-21
channel ORA_DISK_1: finished piece 1 at 10-JUN-21
piece handle=/app/oracle/rman/ORCL_02013459_1_1_20210610.bk tag=TAG20210610T212249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 10-JUN-21
Starting backup at 10-JUN-21
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=146 RECID=5 STAMP=1074892984
channel ORA_DISK_1: starting piece 1 at 10-JUN-21
channel ORA_DISK_1: finished piece 1 at 10-JUN-21
piece handle=/app/oracle/rman/ORCL_0301345o_1_1_20210610.bk tag=TAG20210610T212304 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JUN-21
Starting Control File and SPFILE Autobackup at 10-JUN-21
piece handle=/app/oracle/rman/db_ctl_c-1448972421-20210610-00 comment=NONE
Finished Control File and SPFILE Autobackup at 10-JUN-21
|
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
15
|
$ cd /app/oracle/rman/
$ ls -al
total 3095212
drwxr-xr-x. 2 oracle oinstall 4096 Jun 10 21:23 .
drwxr-xr-x. 13 oracle oinstall 4096 Jun 10 21:20 ..
-rw-r-----. 1 oracle oinstall 9830400 Jun 10 21:23 db_ctl_c-1448972421-20210610-00
-rw-r-----. 1 oracle oinstall 1502720 Jun 10 21:22 ORCL_01013458_1_1_20210610.bk
-rw-r-----. 1 oracle oinstall 3158147072 Jun 10 21:23 ORCL_02013459_1_1_20210610.bk
-rw-r-----. 1 oracle oinstall 3072 Jun 10 21:23 ORCL_0301345o_1_1_20210610.bk
$ scp ./* ora19:/app/oracle/rman/
oracle@ora19's password:
db_ctl_c-1448972421-20210610-00 100% 9600KB 9.4MB/s 00:01
ORCL_01013458_1_1_20210610.bk 100% 1468KB 1.4MB/s 00:00
ORCL_02013459_1_1_20210610.bk 100% 3012MB 77.2MB/s 00:39
ORCL_0301345o_1_1_20210610.bk 100% 3072 3.0KB/s 00:00
|
pfile 및 패스워드 파일 11g에서 19c로 복사(11g)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ cd $ORACLE_HOME/dbs
$ ls -al
total 9552
drwxr-xr-x. 2 oracle oinstall 4096 Jun 10 21:41 .
drwxr-xr-x. 75 oracle oinstall 4096 Aug 29 2016 ..
-rw-rw----. 1 oracle oinstall 1544 Jun 10 21:21 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r--. 1 oracle oinstall 993 Jun 10 21:41 initorcl.ora
-rw-r-----. 1 oracle oinstall 24 Aug 29 2016 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Aug 29 2016 orapworcl
-rw-r-----. 1 oracle oinstall 9748480 Jun 10 21:23 snapcf_orcl.f
-rw-r-----. 1 oracle oinstall 3584 Jun 10 21:22 spfileorcl.ora
$ scp initorcl.ora orapworcl ora19:/ORA19/app/oracle/product/19.0.0/db_1/dbs/
oracle@ora19's password:
initorcl.ora 100% 993 1.0KB/s 00:00
orapworcl 100% 1536 1.5KB/s 00:00
|
19c 에서 pfile 수정(19c)
|
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 /ORA19/app/oracle/product/19.0.0/db_1/dbs
$ vi initorcl.ora
orcl.__db_cache_size=654311424
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__pga_aggregate_target=704643072
orcl.__sga_target=956301312
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=234881024
orcl.__streams_pool_size=0
*.audit_file_dest='/app/oracle/admin/orcl/adump' 기존
*.audit_file_dest='/ORA19/app/oracle/admin/orcl/adump' 수정
*.audit_trail='db'
*.compatible='11.2.0.4.0' 기존
*.compatible='19.0.0' 수정
*.control_files='/app/oracle/oradata/orcl/control01.ctl','/app/oracle/fast_recovery_area/orcl/control02.ctl' 기존
*.control_files='/ORA19/app/oracle/oradata/orcl/control01.ctl','/ORA19/app/oracle/fast_recovery_area/orcl/control02.ctl' 수정
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/app/oracle/fast_recovery_area' 기존
*.db_recovery_file_dest='/ORA19/app/oracle/fast_recovery_area' 수정
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/app/oracle' 기존
*.diagnostic_dest='/ORA19/app/oracle' 수정
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest=''
*.log_archive_dest_1='location=/app/oracle/arch' 기존
*.log_archive_dest_1='location=/ORA19/app/oracle/arch' 수정
*.log_archive_format='orcl_%t_%s_%r.arc'
*.memory_target=1652555776
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
|
필수 경로 생성(19c)
|
1
2
3
4
5
|
$ mkdir -p /ORA19/app/oracle/admin/orcl/adump
$ mkdir -p /ORA19/app/oracle/oradata/orcl/
$ mkdir -p /ORA19/app/oracle/fast_recovery_area/orcl/
$ mkdir -p /ORA19/app/oracle/arch
$ mkdir -p /app/oracle/oradata/orcl
|
orcl db nomount 기동 및 spfile 생성(19c)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ export ORACLE_SID=orcl
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 989855744 bytes
Database Buffers 654311424 bytes
Redo Buffers 7876608 bytes
SQL> create spfile from pfile;
File created.
|
컨트롤파일 restore(19c)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ rman target /
RMAN> restore controlfile from '/app/oracle/rman/db_ctl_c-1448972421-20210610-00';
Starting restore at 10-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/ORA19/app/oracle/oradata/orcl/control01.ctl
output file name=/ORA19/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 10-JUN-21
|
db mount 상태로 변경
|
1
2
3
4
|
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
|
카탈로그 저장
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
RMAN> catalog start with '/app/oracle/rman/';
searching for all files that match the pattern /app/oracle/rman/
List of Files Unknown to the Database
=====================================
File Name: /app/oracle/rman/db_ctl_c-1448972421-20210610-00
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: /app/oracle/rman/db_ctl_c-1448972421-20210610-00
|
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
RMAN>
run
{
set newname for datafile 1 to '/ORA19/app/oracle/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/ORA19/app/oracle/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/ORA19/app/oracle/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/ORA19/app/oracle/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/ORA19/app/oracle/oradata/orcl/migts01.dbf';
restore database;
switch datafile all;
recover database;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 10-JUN-21
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 /ORA19/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /ORA19/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ORA19/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /ORA19/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ORA19/app/oracle/oradata/orcl/migts01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/ORCL_02013459_1_1_20210610.bk
channel ORA_DISK_1: piece handle=/app/oracle/rman/ORCL_02013459_1_1_20210610.bk tag=TAG20210610T212249
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 10-JUN-21
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1074863476 file name=/ORA19/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1074863476 file name=/ORA19/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1074863476 file name=/ORA19/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1074863476 file name=/ORA19/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1074863476 file name=/ORA19/app/oracle/oradata/orcl/migts01.dbf
Starting recover at 10-JUN-21
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=146
channel ORA_DISK_1: reading from backup piece /app/oracle/rman/ORCL_0301345o_1_1_20210610.bk
channel ORA_DISK_1: piece handle=/app/oracle/rman/ORCL_0301345o_1_1_20210610.bk tag=TAG20210610T212304
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ORA19/app/oracle/arch/orcl_1_146_921191688.arc thread=1 sequence=146
unable to find archived log
archived log thread=1 sequence=147
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/10/2021 13:11:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 147 and starting SCN of 1073382
|
set newname 과 datafile restore 등은 제대로 되었지만
recover 작업이 일부 실패함
146번째 아카이브 파일까지만 11g에서 백업되어서 147번째 아카이브는 없어서 발생한 문제
resetlogs로 기동
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 4643
Session ID: 38 Serial number: 4621
|
위 에러메세지 발생 이후 db는 abort됨
startup upgrade 명령으로 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 989855744 bytes
Database Buffers 654311424 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
|
정상 기동됨
redo와 temp 파일이 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
|
SQL>
set lines 200
set 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 /app/oracle/oradata/orcl/redo01.log YES INACTIVE 50
2 /app/oracle/oradata/orcl/redo02.log YES INACTIVE 50
3 /app/oracle/oradata/orcl/redo03.log NO CURRENT 50
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ----------
NAME
-------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1 925781 29-AUG-16 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192
/app/oracle/oradata/orcl/temp01.dbf
0
|
리두, temp 경로 변경
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ cd /app/oracle/oradata/orcl/
$ ls
redo01.log redo02.log redo03.log temp01.dbf
$ cp -av ./*.log /ORA19/app/oracle/oradata/orcl/
'./redo01.log' -> '/ORA19/app/oracle/oradata/orcl/redo01.log'
'./redo02.log' -> '/ORA19/app/oracle/oradata/orcl/redo02.log'
'./redo03.log' -> '/ORA19/app/oracle/oradata/orcl/redo03.log'
SQL> alter database rename file '/app/oracle/oradata/orcl/redo01.log' to '/ORA19/app/oracle/oradata/orcl/redo01.log';
SQL> alter database rename file '/app/oracle/oradata/orcl/redo02.log' to '/ORA19/app/oracle/oradata/orcl/redo02.log';
SQL> alter system switch logfile;
SQL> alter database rename file '/app/oracle/oradata/orcl/redo03.log' to '/ORA19/app/oracle/oradata/orcl/redo03.log';
SQL> create temporary tablespace temp2 tempfile '/ORA19/app/oracle/oradata/orcl/temp02.dbf' size 200m;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles;
SQL> create temporary tablespace temp tempfile '/ORA19/app/oracle/oradata/orcl/temp01.dbf' size 200m;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace temp2 including contents and datafiles;
|
redo 파일 복사 후 rename으로 경로 변경
temp tablespace 재생성
변경된 경로 확인
|
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 lines 200
set 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 /ORA19/app/oracle/oradata/orcl/redo01.log NO CURRENT 50
2 /ORA19/app/oracle/oradata/orcl/redo02.log YES INACTIVE 50
3 /ORA19/app/oracle/oradata/orcl/redo03.log YES INACTIVE 50
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1 1185031 10-JUN-21 3 1 ONLINE READ WRITE 209715200 25600 209715200 8192
/ORA19/app/oracle/oradata/orcl/temp01.dbf
0
|
*아래 작업 전 redo 사이즈 및 system, sysaux 크기를 여유롭게 조정해 놓는것이 좋음
수동 업그레이드 스크립트 실행
|
1
2
|
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
|
로그
더보기
로그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
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
|
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
Argument list for [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 = 4
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.3.0.0.0DBRU_LINUX.X64_190417]
/ORA19/app/oracle/product/19.0.0/db_1/rdbms/admin/orahome = [/ORA19/app/oracle/product/19.0.0/db_1]
/ORA19/app/oracle/product/19.0.0/db_1/bin/orabasehome = [/ORA19/app/oracle/product/19.0.0/db_1]
catctlGetOraBaseLogDir = [/ORA19/app/oracle/product/19.0.0/db_1]
Analyzing file /ORA19/app/oracle/product/19.0.0/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20210610140058]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210610140058/catupgrd_catcon_5129.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210610140058/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210610140058/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = orcl
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108/catupgrd_catcon_5129.lst]
catcon::set_log_file_base_path: catcon: See [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108/catupgrd_*.lst] files for spool files, if any
Log file directory = [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108]
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2021_06_10 14:01:17]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 35s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 20s
Restart Phase #:2 [orcl] Files:1 Time: 3s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 11s
Restart Phase #:4 [orcl] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 8s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 7s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 5s
Restart Phase #:8 [orcl] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:67 Time: 21s
Restart Phase #:10 [orcl] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 27s
Restart Phase #:12 [orcl] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:94 Time: 7s
Restart Phase #:14 [orcl] Files:1 Time: 1s
Parallel Phase #:15 [orcl] Files:120 Time: 12s
Restart Phase #:16 [orcl] Files:1 Time: 2s
Serial Phase #:17 [orcl] Files:22 Time: 3s
Restart Phase #:18 [orcl] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 15s
Restart Phase #:20 [orcl] Files:1 Time: 1s
Serial Phase #:21 [orcl] Files:3 Time: 7s
Restart Phase #:22 [orcl] Files:1 Time: 2s
Parallel Phase #:23 [orcl] Files:25 Time: 80s
Restart Phase #:24 [orcl] Files:1 Time: 3s
Parallel Phase #:25 [orcl] Files:12 Time: 42s
Restart Phase #:26 [orcl] Files:1 Time: 2s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:3 Time: 3s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 2s
Restart Phase #:32 [orcl] Files:1 Time: 1s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:293 Time: 9s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 1s
Serial Phase #:38 [orcl] Files:6 Time: 4s
Restart Phase #:39 [orcl] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 24s
Restart Phase #:41 [orcl] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 55s
Restart Phase #:43 [orcl] Files:1 Time: 2s
Parallel Phase #:44 [orcl] Files:11 Time: 10s
Restart Phase #:45 [orcl] Files:1 Time: 1s
Parallel Phase #:46 [orcl] Files:3 Time: 3s
Restart Phase #:47 [orcl] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 5s
Restart Phase #:49 [orcl] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 10s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 2s
Restart Phase #:52 [orcl] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 225s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 2s
Serial Phase #:56 [orcl] Files:3 Time: 15s
Serial Phase #:57 [orcl] Files:3 Time: 5s
Parallel Phase #:58 [orcl] Files:10 Time: 5s
Parallel Phase #:59 [orcl] Files:25 Time: 6s
Serial Phase #:60 [orcl] Files:4 Time: 7s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:32 Time: 5s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 8s
Serial Phase #:65 [orcl] Files:2 Time: 13s
Serial Phase #:66 [orcl] Files:3 Time: 35s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 3s
Serial Phase #:69 [orcl] Files:1 Time: 5s
Parallel Phase #:70 [orcl] Files:2 Time: 29s
Restart Phase #:71 [orcl] Files:1 Time: 2s
Parallel Phase #:72 [orcl] Files:2 Time: 4s
Serial Phase #:73 [orcl] Files:2 Time: 4s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcl] Files:1 Time: 2s
Serial Phase #:76 [orcl] Files:1 Time: 19s
Serial Phase #:77 [orcl] Files:2 Time: 5s
Restart Phase #:78 [orcl] Files:1 Time: 2s
Serial Phase #:79 [orcl] Files:1 Time: 12s
Restart Phase #:80 [orcl] Files:1 Time: 0s
Parallel Phase #:81 [orcl] Files:3 Time: 42s
Restart Phase #:82 [orcl] Files:1 Time: 2s
Serial Phase #:83 [orcl] Files:1 Time: 6s
Restart Phase #:84 [orcl] Files:1 Time: 0s
Serial Phase #:85 [orcl] Files:1 Time: 8s
Restart Phase #:86 [orcl] Files:1 Time: 1s
Parallel Phase #:87 [orcl] Files:4 Time: 66s
Restart Phase #:88 [orcl] Files:1 Time: 2s
Serial Phase #:89 [orcl] Files:1 Time: 5s
Restart Phase #:90 [orcl] Files:1 Time: 1s
Serial Phase #:91 [orcl] Files:2 Time: 7s
Restart Phase #:92 [orcl] Files:1 Time: 2s
Serial Phase #:93 [orcl] Files:1 Time: 2s
Restart Phase #:94 [orcl] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcl] Files:1 Time: 13s
Restart Phase #:96 [orcl] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [orcl] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcl] Files:1 Time: 113s
******************* Migration ******************
Serial Phase #:99 [orcl] Files:1 Time: 23s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcl] Files:1 Time: 2s
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 35s
***************** Post Upgrade *****************
Serial Phase #:103 [orcl] Files:1 Time: 45s
**************** Summary report ****************
Serial Phase #:104 [orcl] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcl] Files:1 Time: 2s
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 30s
------------------------------------------------------
Phases [0-107] End Time:[2021_06_10 14:22:42]
------------------------------------------------------
Grand Total Time: 1286s
LOG FILES: (/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108/catupgrd*.log)
Upgrade Summary Report Located in:
/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610140108/upg_summary.log
Grand Total Upgrade Time: [0d:0h:21m:26s]
|
위 스크립트 완료 후 db가 종료됨
다시 기동(startup upgrade가 아닌 일반모드로 기동)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 1157627904 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
|
업그레이드 후 추가 스크립트 실행
|
1
2
|
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catuppst.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
|
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catuppst.sql
Argument list for [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 = 4
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.3.0.0.0DBRU_LINUX.X64_190417]
/ORA19/app/oracle/product/19.0.0/db_1/rdbms/admin/orahome = [/ORA19/app/oracle/product/19.0.0/db_1]
/ORA19/app/oracle/product/19.0.0/db_1/bin/orabasehome = [/ORA19/app/oracle/product/19.0.0/db_1]
catctlGetOraBaseLogDir = [/ORA19/app/oracle/product/19.0.0/db_1]
Analyzing file /ORA19/app/oracle/product/19.0.0/db_1/rdbms/admin/catuppst.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20210610235720]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210610235720/catuppst_catcon_14588.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210610235720/catuppst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210610235720/catuppst_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = orcl
DataBase Version = 19.0.0.0.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610235732/catuppst_catcon_14588.lst]
catcon::set_log_file_base_path: catcon: See [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610235732/catuppst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610235732/catuppst_*.lst] files for spool files, if any
Log file directory = [/ORA19/app/oracle/product/19.0.0/db_1/cfgtoollogs/orcl/upgrade20210610235732]
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-0] Start Time:[2021_06_10 23:57:35]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:3 Time: 4s
------------------------------------------------------
Phases [0-0] End Time:[2021_06_10 23:57:39]
------------------------------------------------------
Grand Total Time: 4s
|
utlrp 실행(invaild object 확인)
|
1
|
SQL> @?/rdbms/admin/utlrp.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
|
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
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Cluste 19.0.0.0.0 OPTION O
FF
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- --------
XDB Oracle XML Database 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
AMD OLAP Catalog 11.2.0.4.0 OPTION O
FF
APEX Oracle Application Express 3.2.1.00.1 VALID
15 rows selected.
|
정상적으로 19c로 업그레이드 됨
데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> conn miguser/miguser
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- ------------- ----------
MIGTABLE TABLE
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 1920
|
데이터도 정상적으로 마이그레이션됨
테스트2 작업 전 19c 에서 datafile 등 삭제
|
1
2
3
4
5
|
$ cd /ORA19/app/oracle/oradata/orcl/
$ ls
control01.ctl migts01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
$ rm -rf ./*
$ ls
|
테스트2. 11gR2 rman 백업셋 백업 후 19c 에서 duplicate 후 업그레이드
참조 : 2022820.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/index.html
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmquick.htm
http://www.nazimcricket.com/wiki/index.php?title=Restore_in_between_Upgrades
https://oracledbwr.com/upgrade-oracle-database-from-11g-to-12c-using-the-rman-backup/
https://positivemh.tistory.com/567
rman 백업 정리 및 스크립트
OS환경 : Oracle Linux 7.5 (64bit) DB 환경 : Oracle Database 19.7.0.0 방법 : rman 백업 스크립트 및 정리 rman 이란? recovery manager (RMAN)은 오라클에서 제공하는 백업 및 복구 작업을 수행하고 백업 전..
positivemh.tistory.com
https://positivemh.tistory.com/147
오라클 아카이브 모드 설정 및 경로 설정
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 10.2.0.4 방법 : 오라클 아카이브 모드 설정 및 경로 설정 오라클 아카이브로그 경로 변경, 아카이브 변경 LOG_ARCHIVE_DEST 을 LOG_ARCHIVE_DEST_..
positivemh.tistory.com
https://h391106.tistory.com/298
https://positivemh.tistory.com/107
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 GRID OLR 백업 복구 (0) | 2021.10.20 |
|---|---|
| 오라클 11g R2 XML데이터 update 로그마이너 복구 테스트 (0) | 2021.10.06 |
| 오라클 11g R2 hot 백업 후 until scn 을 이용한 복구 방법 (0) | 2021.04.23 |
| 오라클 11g R2 rman 백업 후 until scn 을 이용한 복구 방법 (2) | 2021.04.23 |
| 오라클 11g R2 엔진 백업 복구 테스트(windows) (0) | 2021.02.06 |