프린트 하기

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

 

방법 : 오라클 23ai pdb 기동순서 조정

오라클 23ai 부터 pdb(pluggable database) 들의 기동 순서를 지정할 수 있음
이 기능을 사용하게 되면 업무 중요도가 높은 pdb를 덜 중요한 pdb보다 먼저 기동하여 중요한 애플리케이션을 사용할 수 있게 되는 시간이 단축시킬 수 있음
v$pdbs의 priority 컬럼으로 기동 순서를 확인할 수 있음

 

 

테스트
기존 db 확인

1
2
3
4
5
6
7
8
9
10
SQL>
set lines 200 pages 1000
col name for a20
select con_id, name, open_mode, to_char(open_time, 'yyyy/mm/dd hh24:mi:ss') open_time, dbid from v$containers;
 
    CON_ID NAME                 OPEN_MODE  OPEN_TIME                 DBID
---------- -------------------- ---------- ------------------- ----------
         1 CDB$ROOT             READ WRITE 2024/05/03 20:55:43 1440066549
         2 PDB$SEED             READ ONLY  2024/05/03 20:55:43  266233594
         3 FREEPDB1             READ WRITE 2024/05/03 20:55:44 1497054454

cdb 1개, pdb seed 1개, pdb 1개가 존재함

 

 

cdb 접속 후 pdb$seed로 접속하여 datafile 경로 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$ sqlplus sys/oracle@localhost:1521/FREE as sysdba
SQL> alter session set container=PDB$SEED;
 
Session altered.
 
SQL> show con_name
 
CON_NAME
------------------------------
PDB$SEED
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/FREE/pdbseed/undotbs01.dbf
/app/oracle/oradata/FREE/pdbseed/system01.dbf
/app/oracle/oradata/FREE/pdbseed/sysaux01.dbf

 

 

테스트용 pdb 생성

pdb$seed를 이용해서 신규 pdb를 생성함

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> alter session set container=CDB$ROOT;
 
Session altered.
 
SQL> show con_name
 
CON_NAME
------------------------------
CDB$ROOT
 
SQL> create pluggable database FREEPDB2 admin user pdb_adm identified by oracle
file_name_convert=('/app/oracle/oradata/FREE/pdbseed/', '/app/oracle/oradata/FREE/FREEPDB2/');
 
Pluggable database created.

 

 

pdb 생성시 alert log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ tail -300f /opt/oracle/diag/rdbms/free/FREE/trace/alert_FREE.log
2024-05-03T20:56:46.469153+09:00
create pluggable database FREEPDB2 admin user pdb_adm identified by *
file_name_convert=('/app/oracle/oradata/FREE/pdbseed/', '/app/oracle/oradata/FREE/FREEPDB2/')
2024-05-03T20:56:46.806713+09:00
PDB$SEED(2): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2024-05-03T20:56:49.455168+09:00
****************************************************************
Pluggable Database FREEPDB2 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000130
****************************************************************
(4):--ATTENTION--
(4):PARALLEL_MAX_SERVERS (with value 1) is insufficient. This may affect transaction recovery performance.
Modify PARALLEL_MAX_SERVERS parameter to a value > 4 (= parallel servers count computed from parameter FAST_START_PARALLEL_ROLLBACK) in PDB ID 4
FREEPDB2(4):Autotune of undo retention is turned on.
FREEPDB2(4):JIT: pid 18335 requesting stop
FREEPDB2(4):Buffer Cache flush started: 4
FREEPDB2(4):Buffer Cache flush finished: 4
Completed: create pluggable database FREEPDB2 admin user pdb_adm identified by *
file_name_convert=('/app/oracle/oradata/FREE/pdbseed/', '/app/oracle/oradata/FREE/FREEPDB2/')

 

 

pdb open

1
2
3
SQL> alter pluggable database FREEPDB2 open;
 
Pluggable database altered.

 

 

pdb open 시 alert log

1
2
3
4
5
$ tail -300f /opt/oracle/diag/rdbms/free/FREE/trace/alert_FREE.log
2024-05-03T20:57:10.544451+09:00
FREEPDB2(4):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB2 opened read write
Completed: alter pluggable database FREEPDB2 open

 

 

테스트를 위해 pdb 2개 더 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create pluggable database FREEPDB3 admin user pdb_adm identified by oracle
file_name_convert=('/app/oracle/oradata/FREE/pdbseed/', '/app/oracle/oradata/FREE/FREEPDB3/');
 
Pluggable database created.
 
SQL> create pluggable database FREEPDB4 admin user pdb_adm identified by oracle
file_name_convert=('/app/oracle/oradata/FREE/pdbseed/', '/app/oracle/oradata/FREE/FREEPDB4/');
 
Pluggable database created.
 
SQL> alter pluggable database all open;
 
Pluggable database altered.

 

 

기동 우선순위 priority 설정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> alter pluggable database FREEPDB1 priority 1;
 
Pluggable database FREEPDB1 altered.
 
SQL> alter pluggable database FREEPDB2 priority 2;
 
Pluggable database FREEPDB2 altered.
 
SQL> alter pluggable database FREEPDB3 priority 3;
 
Pluggable database FREEPDB3 altered.
 
SQL> alter pluggable database FREEPDB4 priority 4;
 
Pluggable database FREEPDB4 altered.

 

 

priority 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
set lines 200 pages 1000
col name for a20
select con_id, name, open_mode, to_char(open_time, 'yyyy/mm/dd hh24:mi:ss') open_time, total_size/1024/1024/1024 gb, priority from v$pdbs;
 
        CON_ID NAME                 OPEN_MODE  OPEN_TIME                   GB   PRIORITY
---------- -------------------- ---------- ------------------- ---------- ----------
         2 PDB$SEED             READ ONLY  2024/05/03 20:55:43     .78125          1
         3 FREEPDB1             READ WRITE 2024/05/03 20:55:44 .815429688          1
         4 FREEPDB2             READ WRITE 2024/05/03 20:57:07     .78125          2
         5 FREEPDB3             READ WRITE 2024/05/03 20:57:48     .78125          3
         6 FREEPDB4             READ WRITE 2024/05/03 20:57:51     .78125          4

priority가 정상적으로 설정됨

 

 

pdb 모두 종료

1
2
3
SQL> alter pluggable database all close immediate;
 
Pluggable database altered.

 

 

all close 시 alert log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ tail -300f /opt/oracle/diag/rdbms/free/FREE/trace/alert_FREE.log
2024-05-03T20:58:39.758062+09:00
alter pluggable database all close immediate
2024-05-03T20:58:39.779540+09:00
FREEPDB1(3):JIT: pid 18053 requesting stop
FREEPDB2(4):JIT: pid 18053 requesting stop
FREEPDB2(4):TABLE SYS.ACTIVITY_TABLE$: ADDED INTERVAL PARTITION SYS_P343 (1) VALUES LESS THAN (106)
FREEPDB3(5):JIT: pid 18053 requesting stop
FREEPDB3(5):TABLE SYS.ACTIVITY_TABLE$: ADDED INTERVAL PARTITION SYS_P343 (1) VALUES LESS THAN (106)
FREEPDB4(6):JIT: pid 18053 requesting stop
FREEPDB4(6):TABLE SYS.ACTIVITY_TABLE$: ADDED INTERVAL PARTITION SYS_P343 (1) VALUES LESS THAN (106)
Buffer Cache flush started: 3-6
Buffer Cache flush finished: 3-6
Completed: Pluggable database FREEPDB1 closed services=None
Completed: Pluggable database FREEPDB2 closed services=None
Completed: Pluggable database FREEPDB3 closed services=None
2024-05-03T20:58:40.760218+09:00
Completed: Pluggable database FREEPDB4 closed services=None
2024-05-03T20:58:40.765369+09:00
Completed: alter pluggable database all close immediate

 

 

pdb 다시 기동

1
2
3
SQL> alter pluggable database all open;
 
Pluggable database altered.

 

 

all open 시 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
$ tail -300f /opt/oracle/diag/rdbms/free/FREE/trace/alert_FREE.log
2024-05-03T20:58:57.498824+09:00
alter pluggable database all open
(3):--ATTENTION--
(3):PARALLEL_MAX_SERVERS (with value 1) is insufficient. This may affect transaction recovery performance.
Modify PARALLEL_MAX_SERVERS parameter to a value > 4 (= parallel servers count computed from parameter FAST_START_PARALLEL_ROLLBACK) in PDB ID 3
FREEPDB1(3):Autotune of undo retention is turned on.
2024-05-03T20:58:58.078102+09:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB1 opened read write
(4):--ATTENTION--
(4):PARALLEL_MAX_SERVERS (with value 1) is insufficient. This may affect transaction recovery performance.
Modify PARALLEL_MAX_SERVERS parameter to a value > 4 (= parallel servers count computed from parameter FAST_START_PARALLEL_ROLLBACK) in PDB ID 4
FREEPDB2(4):Autotune of undo retention is turned on.
FREEPDB2(4):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB2 opened read write
(5):--ATTENTION--
(5):PARALLEL_MAX_SERVERS (with value 1) is insufficient. This may affect transaction recovery performance.
Modify PARALLEL_MAX_SERVERS parameter to a value > 4 (= parallel servers count computed from parameter FAST_START_PARALLEL_ROLLBACK) in PDB ID 5
FREEPDB3(5):Autotune of undo retention is turned on.
2024-05-03T20:58:59.480561+09:00
FREEPDB3(5):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2024-05-03T20:58:59.543996+09:00
Completed: Pluggable database FREEPDB3 opened read write
(6):--ATTENTION--
(6):PARALLEL_MAX_SERVERS (with value 1) is insufficient. This may affect transaction recovery performance.
Modify PARALLEL_MAX_SERVERS parameter to a value > 4 (= parallel servers count computed from parameter FAST_START_PARALLEL_ROLLBACK) in PDB ID 6
FREEPDB4(6):Autotune of undo retention is turned on.
FREEPDB4(6):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB4 opened read write
2024-05-03T20:59:00.289913+09:00
Completed: alter pluggable database all open

priority 순서대로 기동됨

 

 

pdb open_time 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
set lines 200 pages 1000
col name for a20
select con_id, name, open_mode, to_char(open_time, 'yyyy/mm/dd hh24:mi:ss') open_time, total_size/1024/1024/1024 gb, priority from v$pdbs;
 
    CON_ID NAME                 OPEN_MODE  OPEN_TIME                   GB   PRIORITY
---------- -------------------- ---------- ------------------- ---------- ----------
         2 PDB$SEED             READ ONLY  2024/05/03 20:55:43     .78125          1
         3 FREEPDB1             READ WRITE 2024/05/03 20:58:57 .815429688          1
         4 FREEPDB2             READ WRITE 2024/05/03 20:58:58     .78125          2
         5 FREEPDB3             READ WRITE 2024/05/03 20:58:58     .78125          3
         6 FREEPDB4             READ WRITE 2024/05/03 20:58:59     .78125          4

priority 순서대로 기동됨

 

 

참조 : 

https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1676
https://docs.oracle.com/en/database/oracle/oracle-database/23/multi/administering-pdbs-with-sql-plus.html#MULTI-GUID-C68E55D5-52D6-4E64-BF8C-4DE3C8FA5131
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-PDBS.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PLUGGABLE-DATABASE.html
https://oracle-base.com/articles/12c/multitenant-startup-and-shutdown-cdb-and-pdb-12cr1
https://oracle-base.com/articles/12c/multitenant-create-and-configure-pluggable-database-12cr1#manual-plugin-pdb

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 OCI 2024 Generative AI Professional 무료 강의 및 무료 자격증  (0) 2024.05.02
오라클 23ai update 시 default on null for update  (0) 2024.04.26
오라클 19c 체크포인트 발생 상황 테스트(기타 체크포인트)  (0) 2024.04.12
오라클 19c 체크포인트 발생 상황 테스트(테이블스페이스 체크포인트)  (0) 2024.04.12
오라클 19c 체크포인트 발생 상황 테스트(스레드 체크포인트)  (0) 2024.04.10

티스토리툴바