반응형
COMPATIBLE 파라미터 확인 및 변경
COMPATIBLE은 오라클이 디스크에 데이터를 저장하는 내부 포맷의 버전이고 Binary 벡터 타입은 '23.5.0.0.0'이상, Sparse 벡터 타입은 '23.7.0.0.0' 이상 이어함
주의 : COMPATIBLE 값은 한 번 올리면 절대 못 내림
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL> -- 이 CDB 안에 어떤 컨테이너(PDB)들이 있는지 전체 목록 확인
SQL> COLUMN NAME FORMAT A15
SQL> SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;
CON_ID NAME OPEN_MODE
---------- --------------- ----------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE
SQL>
SQL> -- 현재 COMPATIBLE 값 확인
SQL> col VALUE for a30
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
NAME VALUE
--------------- ------------------------------
compatible 23.6.0
SQL>
--COMPATIBLE 변경이 필요한 경우에만 실행
--COMPATIBLE 변경
SQL>
SQL> ALTER SYSTEM SET COMPATIBLE = '23.7.0' SCOPE=SPFILE;
System altered.
SQL> -- COMPATIBLE 변경은 SPFILE에만 반영되며, 재시작해야 실제로 적용됨
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 3251890192 bytes
Fixed Size 5031952 bytes
Variable Size 771751936 bytes
Database Buffers 2466250752 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 23.7.0
noncdb_compatible boolean FALSE
SQL>
SQL> -- COMPATIBLE 변경 확인
SQL> col NAME for a20
SQL> col VALUE for a30
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
NAME VALUE
-------------------- ------------------------------
compatible 23.7.0
SQL> exit
Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0
[oracle@rhel10nm ~]$
Vector Memory Pool
Oracle DB 26ai부터는 여기에 Vector Memory Pool 추가 됨. Vector Memory Pool은 벡터 인덱스(HNSW 그래프 구조 등)를 디스크가 아니라 메모리에 올려놓고 빠르게 검색하기 위한 전용 공간
Vector Memory Pool 공간의 크기를 정하는 파라미터가 VECTOR_MEMORY_SIZE이고, CDB에서 설정하는 것과 PDB에서 설정하는 것의 의미가 다름
- CDB 레벨: Oracle DB 26ai 전체에서 사용 가능한 공간(Size)
- PDB 레벨: Oracle DB 26ai 전체에서 사용 가능한 공간에서 각각 PDB가 할당 받은 공간(Size)
[oracle@rhel10nm ~]$ --CDB Level에서 Vector Memory Pool 설정
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show parameter VECTOR_MEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 0
SQL>
SQL> -- CDB 전체에서 AI VECTOR_MEMORY_SIZE Parameter 512MB로 설정
SQL> ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 512M SCOPE=SPFILE;
System altered.
SQL> --Oracle DB 재기동
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 3251890192 bytes
Fixed Size 5031952 bytes
Variable Size 587202560 bytes
Database Buffers 2113929216 bytes
Redo Buffers 8855552 bytes
Vector Memory Area 536870912 bytes --> Vector Memory Area 추가 생성 됨
Database mounted.
Database opened.
SQL>
SQL> --VECTOR_MEMORY_SIZE 설정값 확인
SQL> SHOW PARAMETER VECTOR_MEMORY_SIZE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 512M
SQL>
SQL> exit
Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ --각각 PDB Level에서 Vector Memory Area 설정
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL>
SQL> --PDB1/PDB2 Open
SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
Pluggable database altered.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> -- 재부팅해도 자동 Open 등록
SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE pdb2 SAVE STATE;
Pluggable database altered.
SQL>
SQL> --PDB1으로 변경 및 VECTOR_MEMORY_SIZE 파리미터 변경
SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show con_id
CON_ID
------------------------------
3
SQL>
SQL> show parameter VECTOR_MEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 512M
SQL>
SQL> ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 256M SCOPE=BOTH;
System altered.
SQL> show parameter VECTOR_MEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 256M
SQL>
SQL> --PDB으로 변경 및 VECTOR_MEMORY_SIZE 파리미터 변경
SQL> ALTER SESSION SET CONTAINER = PDB2;
Session altered.
SQL> SHOW PARAMETER VECTOR_MEMORY_SIZE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 512M
SQL> ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 128M SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER VECTOR_MEMORY_SIZE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 128M
SQL>
SQL> --전체 컨테이너의 Vector Memory Pool 상태
SQL> --CDB Root에서 실행
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL> COLUMN POOL FORMAT A12
SQL> SELECT con_id, pool, ROUND(alloc_bytes/1024/1024, 1) AS alloc_mb,
2 ROUND(used_bytes/1024/1024, 1) AS used_mb, populate_status
3 FROM v$vector_memory_pool ORDER BY con_id, pool;
CON_ID POOL ALLOC_MB USED_MB POPULATE_STATUS
---------- ------------ ---------- ---------- --------------------------
1 1MB POOL 447 0 DONE
1 64KB POOL 48 0 DONE
2 1MB POOL 448 0 DONE
2 64KB POOL 48 0 DONE
3 1MB POOL 224 0 DONE
3 64KB POOL 24 0 DONE
4 1MB POOL 112 0 DONE
4 64KB POOL 12 0 DONE
8 rows selected.
SQL>
SQL> exit
Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0
[oracle@rhel10nm ~]$
반응형