프린트 하기

OS 환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.21.0.0

 

방법 : 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트

오라클 19c 환경의 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업 및 복구(cold backup) 했을때 제대로 동작하는지 확인해봄
참고로 db는 노아카이브 모드 상태임
에디션별 기능 차이는 아래 게시물 참고하길 바람
참고 : 오라클 19c 스탠다드 에디션, 엔터프라이즈 에디션 기능 차이 ( https://positivemh.tistory.com/1050 )

 

 

테스트
스탠다드 에디션 확인

1
2
3
4
5
6
7
8
9
10
11
SQL> 
set lines 200
col product for a50
col version for a15
col version_full for a15
col status for a15
select * from product_component_version;
 
PRODUCT                                            VERSION         VERSION_FULL    STATUS
-------------------------------------------------- --------------- --------------- ---------------
Oracle Database 19c Standard Edition 2             19.0.0.0.0      19.21.0.0.0     Production

Standard Edition 2 에디션에 19.21 DB임

 

 

샘플 테이블 생성

1
2
3
SQL> create table cntck as select * from dba_objects;
 
Table created.

 

 

count 확인

1
2
3
4
5
SQL> select count(*) from cntck;
 
  COUNT(*)
----------
     23722

 

 

audit 경로 확인

1
2
3
4
5
SQL> show parameter audit_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /ORA19/app/oracle/admin/oracle19/adump

 

 

v$option 확인(엔터프라이즈 에디션)

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
SQL> 
set lines 200 pages 1000
col parameter for a50
col value for a10
select parameter, value from v$option order by 1;
 
PARAMETER                                          VALUE
-------------------------------------------------- ----------
ASM Proxy Instance                                 FALSE
Active Data Guard                                  FALSE
Adaptive Execution Plans                           FALSE
Advanced Analytics                                 FALSE
Advanced Compression                               FALSE
Advanced Index Compression                         FALSE
Advanced replication                               FALSE
Application Role                                   FALSE
Automatic Data Optimization                        FALSE
Automatic Storage Management                       FALSE
Backup Encryption                                  FALSE
Basic Compression                                  FALSE
Bit-mapped indexes                                 FALSE
Block Change Tracking                              FALSE
Block Media Recovery                               FALSE
Cache Fusion Lock Accelerator                      FALSE
Centrally Managed User                             FALSE
Change Data Capture                                FALSE
Coalesce Index                                     TRUE
Connection multiplexing                            TRUE
Connection pooling                                 TRUE
Cross Transportable Backups                        FALSE
DICOM                                              TRUE
Data Mining                                        FALSE
Data Redaction                                     FALSE
Database queuing                                   TRUE
Database resource manager                          FALSE
Deferred Segment Creation                          FALSE
Duplexed backups                                   FALSE
Enterprise User Security                           FALSE
Exadata Discovery                                  FALSE
Export transportable tablespaces                   FALSE
Fast-Start Fault Recovery                          FALSE
File Mapping                                       FALSE
Fine-grained Auditing                              FALSE
Fine-grained access control                        FALSE
Flashback Data Archive                             TRUE
Flashback Database                                 FALSE
Flashback Table                                    FALSE
Global Data Services                               FALSE
Heat Map                                           FALSE
I/O Server                                         FALSE
In-Memory Aggregation                              FALSE
In-Memory Column Store                             FALSE
Incremental backup and recovery                    TRUE
Instead-of triggers                                TRUE
Java                                               TRUE
Join index                                         FALSE
Managed Standby                                    FALSE
Management Database                                FALSE
Materialized view rewrite                          FALSE
OLAP                                               FALSE
OLAP Window Functions                              TRUE
Objects                                            TRUE
Online Index Build                                 FALSE
Online Redefinition                                FALSE
Oracle Data Guard                                  FALSE
Oracle Database Vault                              FALSE
Oracle Label Security                              FALSE
Parallel backup and recovery                       FALSE
Parallel execution                                 FALSE
Parallel load                                      TRUE
Partitioning                                       FALSE
Plan Stability                                     TRUE
Point-in-time tablespace recovery                  FALSE
Privilege Analysis                                 FALSE
Proxy authentication/authorization                 TRUE
Real Application Clusters                          FALSE
Real Application Security                          FALSE
Real Application Testing                           FALSE
Result Cache                                       FALSE
SQL Plan Management                                FALSE
Sample Scan                                        TRUE
SecureFiles Encryption                             FALSE
Server Flash Cache                                 FALSE
Snapshot time recovery                             FALSE
Spatial                                            FALSE
Streams Capture                                    FALSE
Table Clustering                                   FALSE
Transparent Application Failover                   TRUE
Transparent Data Encryption                        FALSE
Transparent Sensitive Data Protection              FALSE
Trial Recovery                                     FALSE
Unified Auditing                                   FALSE
Unused Block Compression                           FALSE
XStream                                            TRUE
Zone Maps                                          FALSE
 
87 rows selected.

대부분 옵션이 FALSE 임

 

 

db 종료

1
2
3
4
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

 

controlfile, redo, datafile 확인(cdr)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ cd /ORA19/app/oracle/oradata/ORACLE19
$ ls -al
total 6107936
drwxr-x--- 2 oracle oinstall        220 Mar 29 01:12 .
drwxr-x--- 3 oracle oinstall         22 Dec  5 18:39 ..
-rw-r----- 1 oracle oinstall   10600448 Mar 29 01:36 control01.ctl
-rw-r----- 1 oracle oinstall   10600448 Mar 29 01:36 control02.ctl
-rw-r----- 1 oracle oinstall 2147491840 Mar 29 01:36 imsits01.dbf
-rw-r----- 1 oracle oinstall  536871424 Mar 29 00:29 redo01.log
-rw-r----- 1 oracle oinstall  536871424 Mar 29 01:36 redo02.log
-rw-r----- 1 oracle oinstall  536871424 Mar 29 00:29 redo03.log
-rw-r----- 1 oracle oinstall 1073750016 Mar 29 01:36 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 29 01:36 system01.dbf
-rw-r----- 1 oracle oinstall  209723392 Mar 29 01:14 temp01.dbf
-rw-r----- 1 oracle oinstall  209723392 Mar 29 01:36 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Mar 29 01:36 users01.dbf

 

 

scp 명령으로 엔터프라이즈 에디션 db 서버로 cdr 파일들 복사
(대상서버에서 /ORA19/app/oracle/oradata/ORACLE19 폴더 생성 필요)

1
2
3
4
5
6
7
8
9
10
11
12
13
$ scp ./* oracle@192.168.137.101:/ORA19/app/oracle/oradata/ORACLE19
oracle@192.168.137.101's password:
control01.ctl                                      100%   10MB  11.3MB/s   00:00
control02.ctl                                      100%   10MB  37.1MB/s   00:00
imsits01.dbf                                       100% 2048MB  46.4MB/s   00:44
redo01.log                                         100%  512MB  78.9MB/s   00:06
redo02.log                                         100%  512MB  89.8MB/s   00:05
redo03.log                                         100%  512MB 104.9MB/s   00:04
sysaux01.dbf                                       100% 1024MB  88.7MB/s   00:11
system01.dbf                                       100% 1024MB  85.4MB/s   00:11
temp01.dbf                                         100%  200MB  68.2MB/s   00:02
undotbs01.dbf                                      100%  200MB  32.9MB/s   00:06
users01.dbf                                        100% 5128KB  70.4MB/s   00:00

정상적으로 전송됨

 

 

엔터프라이즈 db 서버에서 파일 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ cd /ORA19/app/oracle/oradata/ORACLE19
$ ls -al
total 6202652
drwxr-xr-x 2 oracle oinstall        220 Mar 29 01:42 .
drwxr-xr-x 3 oracle oinstall         22 Mar 29 01:40 ..
-rw-r----- 1 oracle oinstall   10600448 Mar 29 01:41 control01.ctl
-rw-r----- 1 oracle oinstall   10600448 Mar 29 01:41 control02.ctl
-rw-r----- 1 oracle oinstall 2147491840 Mar 29 01:42 imsits01.dbf
-rw-r----- 1 oracle oinstall  536871424 Mar 29 01:42 redo01.log
-rw-r----- 1 oracle oinstall  536871424 Mar 29 01:42 redo02.log
-rw-r----- 1 oracle oinstall  536871424 Mar 29 01:42 redo03.log
-rw-r----- 1 oracle oinstall 1073750016 Mar 29 01:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 29 01:42 system01.dbf
-rw-r----- 1 oracle oinstall  209723392 Mar 29 01:42 temp01.dbf
-rw-r----- 1 oracle oinstall  209723392 Mar 29 01:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Mar 29 01:42 users01.dbf

정상적으로 모두 존재함

 

 

scp 명령으로 엔터프라이즈 에디션 db 서버로 파라미터 파일 복사

1
2
3
$ scp spfileoracle19.ora oracle@192.168.137.101:/ORA19/app/oracle/product/19c/dbs
oracle@192.168.137.101's password:
spfileoracle19.ora                                 100% 3584     2.1MB/s   00:00

정상적으로 전송됨

 

 

엔터프라이즈 db 서버에서 파일 확인

1
2
3
4
5
6
7
$ cd $ORACLE_HOME/dbs
$ ls -al
total 12
drwxr-xr-x  2 oracle oinstall   48 Mar 29 01:46 .
drwxr-xr-x 71 oracle oinstall 4096 Mar 29 01:46 ..
-rw-r--r--  1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r-----  1 oracle oinstall 3584 Mar 29 01:45 spfileoracle19.ora

정상적으로 파라미터 파일을 받음

 

 

엔터프라이즈 db 서버에서 audit 경로 생성

1
$ mkdir -p /ORA19/app/oracle/admin/oracle19/adump

 

 

엔터프라이즈 db 기동 시도

1
2
3
4
5
6
7
8
9
10
SQL> startup
ORACLE instance started.
 
Total System Global Area 1644163704 bytes
Fixed Size    8925816 bytes
Variable Size 1023410176 bytes
Database Buffers  603979776 bytes
Redo Buffers    7847936 bytes
Database mounted.
Database opened.

정상적으로 open 됨

 

 

v$option 확인(엔터프라이즈 에디션)

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
SQL> 
set lines 200 pages 1000
col parameter for a50
col value for a10
select parameter, value from v$option order by 1;
 
PARAMETER                                          VALUE
-------------------------------------------------- ----------
ASM Proxy Instance                                 FALSE
Active Data Guard                                  TRUE
Adaptive Execution Plans                           TRUE
Advanced Analytics                                 TRUE
Advanced Compression                               TRUE
Advanced Index Compression                         TRUE
Advanced replication                               TRUE
Application Role                                   TRUE
Automatic Data Optimization                        TRUE
Automatic Storage Management                       FALSE
Backup Encryption                                  TRUE
Basic Compression                                  TRUE
Bit-mapped indexes                                 TRUE
Block Change Tracking                              TRUE
Block Media Recovery                               TRUE
Cache Fusion Lock Accelerator                      TRUE
Centrally Managed User                             TRUE
Change Data Capture                                TRUE
Coalesce Index                                     TRUE
Connection multiplexing                            TRUE
Connection pooling                                 TRUE
Cross Transportable Backups                        TRUE
DICOM                                              TRUE
Data Mining                                        TRUE
Data Redaction                                     TRUE
Database queuing                                   TRUE
Database resource manager                          TRUE
Deferred Segment Creation                          TRUE
Duplexed backups                                   TRUE
Enterprise User Security                           TRUE
Exadata Discovery                                  TRUE
Export transportable tablespaces                   TRUE
Fast-Start Fault Recovery                          TRUE
File Mapping                                       TRUE
Fine-grained Auditing                              TRUE
Fine-grained access control                        TRUE
Flashback Data Archive                             TRUE
Flashback Database                                 TRUE
Flashback Table                                    TRUE
Global Data Services                               TRUE
Heat Map                                           TRUE
I/O Server                                         FALSE
In-Memory Aggregation                              TRUE
In-Memory Column Store                             TRUE
Incremental backup and recovery                    TRUE
Instead-of triggers                                TRUE
Java                                               TRUE
Join index                                         TRUE
Managed Standby                                    TRUE
Management Database                                FALSE
Materialized view rewrite                          TRUE
OLAP                                               TRUE
OLAP Window Functions                              TRUE
Objects                                            TRUE
Online Index Build                                 TRUE
Online Redefinition                                TRUE
Oracle Data Guard                                  TRUE
Oracle Database Vault                              FALSE
Oracle Label Security                              FALSE
Parallel backup and recovery                       TRUE
Parallel execution                                 TRUE
Parallel load                                      TRUE
Partitioning                                       TRUE
Plan Stability                                     TRUE
Point-in-time tablespace recovery                  TRUE
Privilege Analysis                                 TRUE
Proxy authentication/authorization                 TRUE
Real Application Clusters                          FALSE
Real Application Security                          TRUE
Real Application Testing                           TRUE
Result Cache                                       TRUE
SQL Plan Management                                TRUE
Sample Scan                                        TRUE
SecureFiles Encryption                             TRUE
Server Flash Cache                                 TRUE
Snapshot time recovery                             TRUE
Spatial                                            TRUE
Streams Capture                                    TRUE
Table Clustering                                   TRUE
Transparent Application Failover                   TRUE
Transparent Data Encryption                        TRUE
Transparent Sensitive Data Protection              TRUE
Trial Recovery                                     TRUE
Unified Auditing                                   FALSE
Unused Block Compression                           TRUE
XStream                                            TRUE
Zone Maps                                          TRUE
 
87 rows selected.

대부분 옵션이 TRUE로 변경됨

 

 

ash 뷰 확인
ash 뷰 count 조회(스탠다드 에디션)

1
2
3
4
5
SQL> select count(*) from v$active_session_history;
 
  COUNT(*)
----------
         0

0건임(데이터가 쌓이지 않음)

 

 

ash 뷰 count 조회(엔터프라이즈 에디션)

1
2
3
4
5
SQL> select count(*) from v$active_session_history;
 
  COUNT(*)
----------
        44

데이터가 이제 막 생기고 있음

 

 

엔터프라이즈 서버에 복구한 db의 에디션 확인

1
2
3
4
5
6
7
8
9
10
11
SQL> 
set lines 200
col product for a50
col version for a15
col version_full for a15
col status for a15
select * from product_component_version;
 
PRODUCT                                            VERSION         VERSION_FULL    STATUS
-------------------------------------------------- --------------- --------------- ---------------
Oracle Database 19c Enterprise Edition             19.0.0.0.0      19.21.0.0.0     Production

Enterprise Edition 에디션에 19.21 DB임

 

 

스탠다드 에디션 db가 cold 백업 복구를 했더니 엔터프라이즈 에디션 db가 됨
ORACLE_HOME의 에디션이 달라 db 기동시 내부적으로 변경하는 작업을 진행한듯함

 

 

alert log에서 특이사항 확인

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/diag/rdbms/oracle19/oracle19/trace
$ vi alert_oracle19.log
**********************************************************************
2024-03-29T01:47:57.296825+09:00
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
2024-03-29T01:47:57.342693+09:00
Initial number of CPU is 1
Number of processor cores in the system is 1
Number of processor sockets in the system is 1
Shared memory segment for instance monitoring created <<< 인스턴스 모니터링용 쉐어드 메모리 세그먼트 생성
Capability Type : Network
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Runtime Environment
capabilities requested : 400000FF detected : 40000000 Simulated : 0
Capability Type : Engineered Systems
capabilities requested : F detected : 0 Simulated : 0
Capability Type : Database Test
capabilities requested : 3 detected : 0 Simulated : 0
Capability Type : Database Editions
capabilities requested : 1CC detected : 8 Simulated : 0
Using LOG_ARCHIVE_DEST_1 parameter default value as /ORA19/app/oracle/product/19c/dbs/arch
.... (PID:): Enable RFS client [kcrlc.c:604]
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Cluster configuration type = NONE [2]
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production <<< 엔터프라이즈 에디션
Version 19.21.0.0.0.
ORACLE_HOME:    /ORA19/app/oracle/product/19c

alert 확인결과 위 2개 메세지 외 나머지 특이한 메세지는 보이지 않음

 

 

결론 :
스탠다드 에디션 db가 cold 백업 복구를 하면 자동으로 엔터프라이즈 에디션 db로 변환됨
ORACLE_HOME의 에디션이 달라 db 기동시 내부적으로 변경하는 작업을 진행한듯함
엔터프라이즈 에디션에서 사용가능한 view 들도 데이터가 쌓이고
v$option에 나열되어있는 다른 옵션들도 사용가능한 상태로 변하는듯함

 

 

하지만 반대로
엔터프라이즈 에디션에서 스탠다드 에디션으로 마이그레이션 하려는 경우에는
단순히 본문처럼 엔진만 변경하는 경우 스탠다드 에디션 db 엔진에는 일부 딕셔너리 오브젝트들이 존재하지 않아 에러가 발생할 수 있음
이 때는 datapump 를 사용해야함
datapump 는 SYS 스키마의 오브젝트를 export 하지 않기 떄문에
엔터프라이즈 에디션에서 사용하던 딕셔너리 오브젝트로 인한 에러가 발생하지 않음
스탠다드 에디션에 import 이후에 단순히 엔터프라이즈 에디션 기능과 관련된 사용자 스키마만 삭제하면 작업이 완료됨
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-enterprise-to-standard-database.html#GUID-FD2DB65B-0E54-4ECE-8DF3-8E8F12FBD005

 

 

참조 : 

2089230.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-standard-to-enterprise--database.html#GUID-294262A1-9E53-4C76-8FFA-086BD6CC4EE1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-enterprise-to-standard-database.html#GUID-FD2DB65B-0E54-4ECE-8DF3-8E8F12FBD005
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87
https://positivemh.tistory.com/440
https://positivemh.tistory.com/808
https://positivemh.tistory.com/810
https://positivemh.tistory.com/1017
오라클 19c 스탠다드 에디션, 엔터프라이즈 에디션 기능 차이 ( https://positivemh.tistory.com/1050 )