为SDB创建模式用户，表空间集，分片表和重复表。验证DDL已传播到所有分片，并在连接到分片时，通过快速启动故障转移验证Data Guard Broker的自动配置。
1.连接到分片目录数据库，创建应用程序模式用户，并向该用户授予特权和角色
#目錄db上
[oracle@sc01 ~]$ sqlplus / as sysdba
SQL>
alter session enable shard ddl;
create user app_schema identified by 123456;
grant all privileges to app_schema;
grant gsmadmin_role to app_schema;
grant select_catalog_role to app_schema;
grant connect, resource to app_schema;
grant dba to app_schema;
grant execute on dbms_crypto to app_schema;
2.为分片表创建一个表空间集
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
);
3.为重复的表创建一个表空间在此示例中，重复的表是示例Customers-Orders-Products模式中的Products表
SQL>
create tablespace products_tsp datafile size 100m autoextend on next
10M maxsize unlimited extent management local uniform size 1m;
4.为根表创建一个分片表 在此示例中，根表是示例Customers-Orders-Products模式中的Customers表
SQL> CONNECT app_schema/123456
SQL> ALTER SESSION ENABLE SHARD DDL;
SQL>
CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
Class VARCHAR2(10),
Geo VARCHAR2(8),
Passwd RAW(60),
CONSTRAINT pk_customers PRIMARY KEY (CustId)
) TABLESPACE SET TSP_SET_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
5.为表族中的其他表创建一个分片表
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);
#创建用于OrderId列的序列
SQL> CREATE SEQUENCE Orders_Seq;
#为LineItems创建分片表
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);
6.创建任何必需的重复表
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;
7.跨分片生成唯一的序列号
Oracle分片允许您为非主键列跨分片生成全局唯一的序列号，并由分片的数据库处理。
当customer_id是分片键时，客户通常需要为非主键列生成唯一的ID，例如order_id。对于这种情况，此功能可让您跨分片生成唯一的序列号，而无需管理应用程序中给定非主键列的全局唯一性。
新对象（）支持此功能SHARDED SEQUENCE。分片序列是在分片目录上创建的，但每个分片上都有一个实例。每个实例生成一个单调递增的数字，该数字属于一个范围，该范围与其他分片上使用的范围不重叠。因此，每个生成的数字都是全局唯一的。
例如，可以使用分片序列为由客户ID分片的表生成唯一的订单号。使用客户ID作为键建立到分片的连接的应用程序可以使用分片序列的本地实例来生成全局唯一的订单号。
请注意，由分片序列生成的数字不能立即用作要插入该分片的新行的分片键，因为键值可能属于另一个分片，并且插入将导致错误。要插入新行，应用程序应首先生成分片密钥的值，然后使用它来连接到适当的分片。生成分片密钥新值的典型方法是在分片目录上使用常规（非分片）序列。
如果单个分片密钥生成器成为瓶颈，则可以将分片序列用于此目的。在这种情况下，应用程序应连接到随机分片（使用全局服务而不指定分片密钥），从分片序列中获取唯一的键值，然后使用该键值连接到适当的分片。
为了支持此功能，对象DDL语法中包括新的SEQUENCE对象子句SHARD和，如以下语句语法所示。
CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
| { SHARD {EXTEND | NOEXTEND} | NOSHARD}
]
#创建 跨分片生成唯一的序列号
SQL> CREATE SEQUENCE SHARD_Seq SHARD EXTEND;
[oracle@sc01 ~]$ sqlplus app_schema/123456
SQL> select to_char(SHARD_Seq.nextval) from dual;
TO_CHAR(SHARD_SEQ.NEXTVAL)
----------------------------------------
10000000000000000000000000000001
=============================================================================
[oracle@sd01 ~]$ sqlplus app_schema/123456
SQL> select to_char(SHARD_Seq.nextval) from dual;
TO_CHAR(SHARD_SEQ.NEXTVAL)
----------------------------------------
10010000000000000000000000000001
=============================================================================
[oracle@sd03 oradata]$ sqlplus app_schema/123456
SQL> select to_char(SHARD_Seq.nextval) from dual;
TO_CHAR(SHARD_SEQ.NEXTVAL)
----------------------------------------
10030000000000000000000000000001
8.在分片导向器主机上，验证在创建表空间期间是否没有故障(gsm01、gsm02)
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------
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"...
15 CREATE SEQUENCE SHARD_Seq SHARD EXTEND
9.验证每个分片上没有DDL错误
GDSCTL> config shard -shard sh1
Name: sh1
Shard Group: primary_shgrp
Status: Ok
State: Deployed
Region: region1
Connection string: sd01:1521/sh1: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_ro_srvc Yes Enabled
oltp_rw_srvc Yes Enabled
GDSCTL> config shard -shard sh2
Name: sh2
Shard Group: standby_shgrp
Status: Ok
State: Deployed
Region: region2
Connection string: sd02:1521/sh2: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: READ ONLY
Rack:
Supported services
------------------------
Name Preferred Status
---- --------- ------
oltp_ro_srvc Yes Enabled
oltp_rw_srvc Yes Enabled
GDSCTL> config chunks
Chunks
------------------------
Database From To
-------- ---- --
sh1 1 6
sh2 1 6
sh3 7 12
sh4 7 12
10.验证在所有分片上都创建了为分片表系列创建的表空间集的表空间以及为重复表创建的表空间
#表空间集中的表空间数基于您在create shardcatalog命令中指定的块数。
#在分片目录创建示例中指定的表空间集包含12个表中的前6个块，并且在以下示例中显示了重复的Products表空间
#对配置中的所有分片重复此步骤。
[oracle@sd01 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
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
C005TSP_SET_1 100
C006TSP_SET_1 100
PRODUCTS_TSP 100
SYSAUX 680
SYSTEM 920
TSP_SET_1 100
UNDOTBS1 335
USERS 5
11.验证已在所有分片上创建了块和块表空间
#对配置中的所有分片重复此步骤。
[oracle@sd01 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------------- ----------- ------------------------------
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
CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1
ORDERS CUSTOMERS_P1 C001TSP_SET_1
CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1
ORDERS CUSTOMERS_P2 C002TSP_SET_1
LINEITEMS CUSTOMERS_P2 C002TSP_SET_1
CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1
LINEITEMS CUSTOMERS_P3 C003TSP_SET_1
ORDERS CUSTOMERS_P3 C003TSP_SET_1
LINEITEMS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1
ORDERS CUSTOMERS_P4 C004TSP_SET_1
CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1
ORDERS CUSTOMERS_P5 C005TSP_SET_1
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
LINEITEMS CUSTOMERS_P5 C005TSP_SET_1
CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1
ORDERS CUSTOMERS_P6 C006TSP_SET_1
LINEITEMS CUSTOMERS_P6 C006TSP_SET_1
18 rows selected.
12.连接到分片目录数据库，并验证块均匀分布
[oracle@sc01 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
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
sh1 6
sh2 6
sh3 6
sh4 6
13.验证是否已创建分片表和重复表。
#以应用程序模式用户身份登录碎片目录数据库和每个碎片。
#以下示例显示以app_schema用户身份查询数据库碎片上的表
[oracle@sc01 ~]$ sql app_schema/123456
SQL> SET SQLFORMAT ansiconsole
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
MLOG$_PRODUCTS
RUPD$_PRODUCTS
14.验证是否已完成Data Guard Broker自动快速启动故障转移配置
#sd01
[oracle@sd01 ~]$ dgmgrl / as sysdba
DGMGRL> show configuration
Configuration - sh1
Protection Mode: MaxPerformance
Members:
sh1 - Primary database
sh2 - (*) Physical standby database
Fast-Start Failover: Enabled in Potential Data Loss Mode
Configuration Status:
SUCCESS (status updated 24 seconds ago)
DGMGRL> show database sh1
Database - sh1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
sh1
Database Status:
SUCCESS
DGMGRL> show database sh2
Database - sh2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 3.00 KByte/s
Real Time Query: ON
Instance(s):
sh2
Database Status:
SUCCESS
DGMGRL> show fast_start failover
Fast-Start Failover: Enabled in Potential Data Loss Mode
Protection Mode: MaxPerformance
Lag Limit: 30 seconds
Threshold: 30 seconds
Active Target: sh2
Potential Targets: "sh2"
sh2 valid
Observer: gsm02
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Write Errors YES
Oracle Error Conditions:
(none)
15.找到快速启动故障转移观察器
#连接到分片目录数据库并运行以下命令
#sc01
[oracle@sc01 ~]$ sqlplus / as sysdba
SQL> SELECT observer_state FROM gsmadmin_internal.broker_configs;
OBSERVER_STATE
--------------------------------------------------------------------------------
GSM server SHARDDIRECTOR2. Observer started. Log files at '/u05/app/oracle/produ
ct/19.3.0/gsm_1/network/admin/gsm_observer_1.log'.
GSM server SHARDDIRECTOR2. Observer started. Log files at '/u05/app/oracle/produ
ct/19.3.0/gsm_1/network/admin/gsm_observer_2.log'.
16.索引测试1
#目錄db上
[oracle@sc01 ~]$ sqlplus / as sysdba
SQL> CONNECT app_schema/123456
SQL> ALTER SESSION ENABLE SHARD DDL;
create tablespace set UD using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set I_UD using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
SQL>
CREATE SHARDED TABLE test_tab
(
range_tab_no varchar2(20),
range_tab_name varchar2(20),
range_tab_type varchar2(1),
range_tab_date date,
CONSTRAINT pk_test_tab primary key (range_tab_no)
) TABLESPACE SET UD PARTITION BY CONSISTENT HASH (range_tab_no) PARTITIONS AUTO;
SQL>
create index inx_test_tab_1 on test_tab(range_tab_name) local tablespace set I_UD;
#or
SQL>
CREATE SHARDED TABLE test_tab2
(
range_tab_no varchar2(20) not null,
range_tab_name varchar2(20),
range_tab_type varchar2(1),
range_tab_date date
) TABLESPACE SET UD PARTITION BY CONSISTENT HASH (range_tab_no) PARTITIONS AUTO;
SQL>
alter table test_tab2 add constraint pk_test_tab2 primary key (range_tab_no) using index tablespace set I_UD;
create index inx_test_tab_1 on test_tab2(range_tab_name) local tablespace set I_UD;
--error
insert into TEST_TAB2(RANGE_TAB_NO,RANGE_TAB_NAME,RANGE_TAB_TYPE,RANGE_TAB_DATE)
select 'no' || rownum RANGE_TAB_NO,
'name_' || rownum RANGE_TAB_NAME,
'type_a',sysdate
from dual connect by level<=100;
--ok
declare v_i number;
begin
v_i:=1;
while (v_i<100) loop
insert into TEST_TAB2(RANGE_TAB_NO,RANGE_TAB_NAME,RANGE_TAB_TYPE,RANGE_TAB_DATE)
values('no_' || to_char(v_i),'name_' || to_char(v_i),'a',sysdate);
v_i:=v_i+1;
end loop;
commit;
end;
[oracle@sd05 ~]$ sql app_schema/123456
SQL> SET SQLFORMAT ansiconsole
SQL> select t.* from TEST_TAB2 t where t.range_tab_no='no_50';
RANGE_TAB_NO RANGE_TAB_NAME RANGE_TAB_TYPE RANGE_TAB_DATE
no_50 name_50 a 27-MAR-20
SQL> explain plan for select t.* from TEST_TAB2 t where t.range_tab_no='no_50';
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2417085537
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 0 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 48 | 0 (0)| 00:00:01 | 11 | 11 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_TAB2 | 1 | 48 | 0 (0)| 00:00:01 | 11 | 11 |
|* 3 | INDEX UNIQUE SCAN | PK_TEST_TAB2 | 1 | | 0 (0)| 00:00:01 | 11 | 11 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
3 - access("T"."SYS_HASHVAL"=3810365396 AND "T"."RANGE_TAB_NO"='no_50')
17.索引测试2
create tablespace set P1901 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set P1902 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set P1903 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set I_P1901 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set I_P1902 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
create tablespace set I_P1903 using template
(
datafile size 100m autoextend on next 10M maxsize unlimited
extent management local segment space management auto
);
CREATE SHARDED TABLE test_range_tab
(
range_tab_no varchar2(20) not null,
range_tab_name varchar2(20),
range_tab_type varchar2(1),
range_tab_date date,
CONSTRAINT pk_test_range_tab PRIMARY KEY(range_tab_no, range_tab_date)
) PARTITION BY CONSISTENT HASH (range_tab_no)
SUBPARTITION by range (range_tab_date)
SUBPARTITION TEMPLATE
(
subpartition SP1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace set P1901,
subpartition SP1902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')) tablespace set P1902,
subpartition SP1903 values less than (TO_DATE('2019-04-01', 'YYYY-MM-DD')) tablespace set P1903
) PARTITIONS AUTO;
#error
create index ind_test_range_tab_1 on test_range_tab(range_tab_no,range_tab_date,range_tab_name)
local
(
PARTITION TEST_RANGE_TAB_P1
(
subpartition SP1901 TABLESPACE SET I_P1901,
subpartition SP1902 TABLESPACE SET I_P1902,
subpartition SP1903 TABLESPACE SET I_P1903
)
);
#ok
create index ind_test_range_tab_1 on test_range_tab(range_tab_no,range_tab_date,range_tab_name) local;
#ok2
create index ind_test_range_tab_1 on test_range_tab(range_tab_no,range_tab_date,range_tab_name) local tablespace set I_P1901;
declare v_i number;
begin
v_i:=1;
while (v_i<100) loop
insert into test_range_tab(RANGE_TAB_NO,RANGE_TAB_NAME,RANGE_TAB_TYPE,RANGE_TAB_DATE)
values('no_' || to_char(v_i),'name_' || to_char(v_i),'a',to_date('2019-01-01','yyyy-mm-dd')+0.5*v_i);
v_i:=v_i+1;
end loop;
commit;
end;
SQL> explain plan for select * from test_range_tab t where t.range_tab_date =date'2019-01-16';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 400915719
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 35 | 1 (0)| 00:00:01 | 1 | 12 |
| 2 | PARTITION RANGE SINGLE | | 1 | 35 | 1 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_RANGE_TAB | 1 | 35 | 1 (0)| 00:00:01 | | |
|* 4 | INDEX SKIP SCAN | IND_TEST_RANGE_TAB_1 | 1 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
4 - access("T"."RANGE_TAB_DATE"=TO_DATE(' 2019-01-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("T"."RANGE_TAB_DATE"=TO_DATE(' 2019-01-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL> explain plan for select * from test_range_tab t where t.range_tab_date =date'2019-01-16' and t.range_tab_no='no_30';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2124229029
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 0 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 48 | 0 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE | | 1 | 48 | 0 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_RANGE_TAB | 1 | 48 | 0 (0)| 00:00:01 | 1 | 1 |
|* 4 | INDEX UNIQUE SCAN | PK_TEST_RANGE_TAB | 1 | | 0 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
4 - access("T"."SYS_HASHVAL"=219508427 AND "T"."RANGE_TAB_NO"='no_30' AND "T"."RANGE_TAB_DATE"=TO_DATE('
2019-01-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
网友评论