System-Managed Sharded Database를 위한 스키마 생성
Shard Catalog DB에 접속해서 수행합니다. 스키마 유저, 테이블스페이스 (sharded 테이블용, duplicate 테이블용) 생성합니다.
[oracle@inst-scatalog ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 4 08:48:21 2019
Version 19.3.0.0.0
...
SQL> alter session enable shard ddl;
Session altered.
SQL> create user app_schema identified by Welcome123##;
User created.
SQL> grant all privileges to app_schema;
Grant succeeded.
SQL> grant gsmadmin_role to app_schema;
Grant succeeded.
SQL> grant select_catalog_role to app_schema;
Grant succeeded.
SQL> grant connect, resource to app_schema;
Grant succeeded.
SQL> grant dba to app_schema;
Grant succeeded.
SQL> grant execute on dbms_crypto to app_schema;
Grant succeeded.
SQL> CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto);
Tablespace created.
SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
Tablespace created.
테스트 유저(app_schema)에 sharded 테이블, duplicated 테이블 및 시퀀스를 생성합니다.
SQL> connect app_schema/Welcome123##
Connected.
SQL> ALTER SESSION ENABLE SHARD DDL;
Session altered.
SQL> CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
Class VARCHAR2(10),
Geo VARCHAR2(8),
CustProfile VARCHAR2(4000),
Passwd RAW(60),
CONSTRAINT pk_customers PRIMARY KEY (CustId),
CONSTRAINT json_customers CHECK (CustProfile IS JSON)
) TABLESPACE SET TSP_SET_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
Table created.
SQL> CREATE SHARDED TABLE Orders
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
OrderDate TIMESTAMP NOT NULL,
SumTotal NUMBER(19,4),
Status CHAR(4),
CONSTRAINT pk_orders PRIMARY KEY (CustId, OrderId),
CONSTRAINT fk_orders_parent FOREIGN KEY (CustId)
REFERENCES Customers ON DELETE CASCADE
) PARTITION BY REFERENCE (fk_orders_parent);
Table created.
SQL> CREATE SHARDED TABLE LineItems
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
ProductId INTEGER NOT NULL,
Price NUMBER(19,4),
Qty NUMBER,
CONSTRAINT pk_items PRIMARY KEY (CustId, OrderId, ProductId),
CONSTRAINT fk_items_parent FOREIGN KEY (CustId, OrderId)
REFERENCES Orders ON DELETE CASCADE
) PARTITION BY REFERENCE (fk_items_parent);
Table created.
SQL> CREATE DUPLICATED TABLE Products
(
ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(128),
DescrUri VARCHAR2(128),
LastPrice NUMBER(19,4)
) TABLESPACE products_tsp;
Table created.
SQL> CREATE SEQUENCE Orders_Seq;
Sequence created.
Shard Director에 접속해서 ddl 과정에서 에러가 없었는 지 확인합니다.
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------
5 grant connect, resource to app_schema
6 grant dba to app_schema
7 grant execute on dbms_crypto to app_s...
8 CREATE TABLESPACE SET TSP_SET_1 using...
9 CREATE TABLESPACE products_tsp datafi...
10 CREATE SHARDED TABLE Customers ( ...
11 CREATE SHARDED TABLE Orders ( O...
12 CREATE SEQUENCE Orders_Seq
13 CREATE SHARDED TABLE LineItems ( ...
14 CREATE MATERIALIZED VIEW "APP_SCHEMA"...
GDSCTL> config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: regionora
Connection string: inst-shard1.subnetshard.demovcn.oraclevcn.com:1521/sh1.subnetshard.demovcn.oraclevcn.com:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 19.0.0.0
Failed DDL:
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_rw_srvc Yes Enabled
GDSCTL> config shard -shard sh2
Name: sh2
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: regionora
Connection string: inst-shard2.subnetshard.demovcn.oraclevcn.com:1521/sh2.subnetshard.demovcn.oraclevcn.com:dedicated
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 19.0.0.0
Failed DDL:
DDL Error: ---
Failed DDL id:
Availability: ONLINE
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_rw_srvc Yes Enabled
각 Shard에 접속해서 테이블스페이스와 테이블이 생성되었는지 확인합니다. 아래는 shard1의 결과입니다. shard2에서도 동일하게 확인합니다.
[oracle@inst-shard1 ~]$ sqlplus "/as sysdba"
...
SQL> set lines 100
SQL> col tablespace_name for a30
SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
C001TSP_SET_1 100
C002TSP_SET_1 100
C003TSP_SET_1 100
C004TSP_SET_1 100
...
SYSAUX 550
SYSTEM 900
TSP_SET_1 100
UNDOTBS1 340
USERS 5
126 rows selected.
SQL> set linesize 140
SQL> column table_name format a20
SQL> column tablespace_name format a20
SQL> column partition_name format a20
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_gwm_db_unique_name string sh1
db_unique_name string sh1
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS CUSTOMERS_P1 C001TSP_SET_1
ORDERS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1
ORDERS CUSTOMERS_P2 C002TSP_SET_1
CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1
...
LINEITEMS CUSTOMERS_P120 C03CTSP_SET_1
ORDERS CUSTOMERS_P120 C03CTSP_SET_1
360 rows selected.
Shard Catalog DB에 접속해서 chunk가 균일하게 분산되었는지 확인합니다.
SQL> col shard for a20
SQL> SELECT a.name Shard, COUNT(b.chunk_number) Number_of_Chunks FROM gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b WHERE a.database_num=b.database_num GROUP BY a.name ORDER BY a.name;
SHARD NUMBER_OF_CHUNKS
-------------------- ----------------
sh3 120
sh4 120
샘플 테이블이 생성되었는지 Shard Catalog와 Shard1, Shard2에서 확인합니다. duplicate 테이블은 mview 형태로 구현된 것을 알 수 있습니다.
-- Shard Catalog
SQL> select table_name from dba_tables where owner='APP_SCHEMA';
TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
RUPD$_PRODUCTS
PRODUCTS
MLOG$_PRODUCTS
6 rows selected.
-- Shard1
SQL> select table_name from dba_tables where owner='APP_SCHEMA';
TABLE_NAME
--------------------
CUSTOMERS
ORDERS
LINEITEMS
USLOG$_PRODUCTS
PRODUCTS
SQL> select table_name from dba_tables where owner='APP_SCHEMA';
-- Shard2
TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
USLOG$_PRODUCTS
PRODUCTS
샘플 데이터 입력합니다. 여기서는 Shrard Director에서 서비스명으로 easy connect로 접속해서 확인했습니다.
[oracle@inst-sdirector ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-DEC-2019 11:47:49
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias SDIRECTOR
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 04-DEC-2019 13:06:59
Uptime 0 days 7 hr. 40 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19c/dbhome_1/network/admin/gsm.ora
Listener Log File /u01/app/oracle/diag/gsm/inst-sdirector/sdirector/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inst-sdirector.subnetsdirector.demovcn.oraclevcn.com)(PORT=1521)))
Services Summary...
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
Instance "SCATALOG", status READY, has 1 handler(s) for this service...
Service "GDS$COORDINATOR.oradbcloud" has 1 instance(s).
Instance "SCATALOG", status READY, has 1 handler(s) for this service...
Service "_MONITOR" has 1 instance(s).
Instance "SDIRECTOR", status READY, has 1 handler(s) for this service...
Service "_PINGER" has 1 instance(s).
Instance "SDIRECTOR", status READY, has 1 handler(s) for this service...
Service "oltp_rw_srvc.demo_sdb.oradbcloud" has 2 instance(s).
Instance "demo_sdb%1", status READY, has 1 handler(s) for this service...
Instance "demo_sdb%11", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@inst-sdirector ~]$ sqlplus app_schema/Welcome123##@132.145.146.238:1521/oltp_rw_srvc.demo_sdb.oradbcloud
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 4 11:48:04 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Dec 04 2019 11:29:51 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> insert into app_schema.customers values (1,'Snow','John','King','North',null, null);
1 row created.
SQL> commit;
Commit complete.
여기에서는 데이터가 shard1로 들어갔음을 알 수 있습니다.
-- 1번 shard
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2088182
SQL> select firstname from app_schema.customers;
FIRSTNAME
------------------------------------------------------------
Snow
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2088585
-- 2번 shard
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2088250
SQL> select firstname from app_schema.customers;
no rows selected
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2088572
<END>
'Database > Oracle Database' 카테고리의 다른 글
| 처음 사용자를 위한 Oracle Database In-Memory 사용 가이드 - #1. Introduction (0) | 2022.06.08 |
|---|---|
| 오라클 멀티테넌트 데이터베이스에서 PDB용 로컬 리스너 생성 방법 (0) | 2022.06.03 |
| 18c 신기능 - 오라클 샤딩 (Sharding) - #5 설치 사례 - System-Managed Sharded Database 생성 (0) | 2022.04.19 |
| 18c 신기능 - 오라클 샤딩 (Sharding) - #4 설치 사례 - Sharding Management & Routing Tier 설정 (0) | 2022.04.19 |
| 18c 신기능 - 오라클 샤딩 (Sharding) - #3 설치 사례 - Software, Catalog 설치 (0) | 2022.04.19 |
