Oracle 12C Sharding deployment and testing 01/28 Update SLTechnology News&Howtos

Oracle 12C Sharding deployment and testing

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Oracle 12C Sharding deployment and testing

Date: 2019-02-26

Author: Chen Juchao

Catalogue

One: environmental description. 4

Second, install GSM/Shard catalog/ORACLE 12.2 RDBMS. five

2.1 Gsm installation. 5

2.2 ORACLE 12.2 RDBMS installation. 8

2.3 create and start monitoring. 8

2.4.Create Shard Catalog database. eight

Third, configure GSM/Shard director. eight

3.1 unlock user. 8

Create an administrative user mygds. eight

3.3.Starting listener. eight

3.4.Create shard catalog configuration remote scheduler agent. eight

3. 5 create and start shard director. nine

3.6 add operating system authentication. nine

3. 7 execute Agent on all shard nodes. ten

3.8 create the data file storage path for the shard database. 11

Four: create System-Managed SDB. eleven

4.1Connect gsm.. eleven

4.2 add shardgroup. eleven

4. 3 create shard. eleven

4.4 check the configuration. 12

4.5 deployment / deploy. thirteen

4.6 check configuration information. 14

4. 7 create service. sixteen

Five: create users and objects. 16

5.1 create a business user in the catalog database. 16

5.2 create a tablespace collection. 16

5.3 create tablespaces for duplicated tables. 17

5.4.Create root table Customers. seventeen

5. 5 create other sharded table Orders. eighteen

5.6 create a sequence for the OrderId column. 18

5.7Create SHARDED TABLE LineItems. eighteen

5.8Create duplicated tables. eighteen

5.9 check for errors. 19

VI: verification environment-tablespace / chunks. twenty

6.1 check chunks information on db01. 20

6.2 check tablespace and chunks information on the sd1,sd2 node. 20

6.3 check chunks information in the catalog database. 23

6.4 verify the environment. 23

Seven: visit Shard. twenty-four

7.1access a single shard. twenty-four

7. 2 visit multiple shard. twenty-six

Eight: shard database start and stop. 27

8.1 turn off. 27

8.2 start

Nine: frequently asked questions. 29

9.1 create shard-shardgroup error. 29

9.2 catalog creates users and tables that cannot be synchronized to the shard db library. 29

9.3 GSM failed to start with exception

Ten: references

One: environmental description

OS: CentOS Linux release 7.5

DB: Oracle 12.2.0.1.0

GSMOCI: 2.2.1

This time, three virtual machines are used for installation:

Where:

(1) install Shard Director and Shard catalog on the db01 host, that is, install GSM, ORACLE 12.2 RDBMS and non-cdb Database respectively (GSM and ORACLE 12.2 environment variables need to be manually switched)

(2) Shard1 (ORACLE 12.2 RDBMS) is installed on the db02 host, and no database needs to be created.

(3) Shard2 (ORACLE 12.2 RDBMS) is installed on the db03 host. No database needs to be created.

Mainframe

module

SID

Oracle_Home

Db01

Shard Director

/ u01/app/oracle/product/12.2.0/gsm_1

Db01

Shard catalog

Db01

/ u01/app/oracle/product/12.2.0/db_1

Db02

Shard1

Sh2

/ u01/app/oracle/product/12.2.0/db_1

Db03

Shard2

Sh3

/ u01/app/oracle/product/12.2.0/db_1

The download link to the installation file is as follows:

Https://www.oracle.com/technetwork/cn/database/options/partitioning/downloads/index.html

Oracle software download

GSM download

II: install GSM/Shard catalog/ORACLE 12.2 RDBMS2.1 Gsm installation

2.2 ORACLE 12.2 RDBMS installation

Slightly

2.3 create and start snooping

Netca

2.4 create Shard Catalog database

Create the non-cdb database on the Shard catalog server gsm1. The creation process is the same as a normal database.

Three: configure GSM/Shard director

Https://blogs.oracle.com/database4cn/12c-oracle-sharding

3.1 unlock the user

On the db01 server (catalog database / shard director), connect to the Sharding catalog database, unlock the GSMCATUSER user, and shard director connects to the shard catalog database through the GSMCATUSER user

SQL > alter user gsmcatuser identified by oracle account unlock

3.2 create an administrative user mygds

In the catalog database, an administrative user mygds is created, the user mygds is used to store Sharding management information, and the GDSCTL interface connects to the catalog database through the user mygds.

SQL > create user mygds identified by oracle

SQL > grant connect, create session, gsmadmin_role to mygds

SQL > grant inherit privileges on user SYS to GSMADMIN_INTERNAL

3.3.Starting listener

On the db01 server (catalog database / shard director), start listener

3.4 create shard catalog configuration remote scheduler agent

On the db01 server (catalog database / shard director), create the shard catalog and configure remote scheduler agent. Exe in shard catalog.

Parameter meaning:

-user: specify the administrative user, the catalog database administrative user mygds created in the previous step

-database: specify catalog database information. Hostname of catalog database: listener port: catalog database db_name

-sdb: specify sharded database name

-agent_port: sets the port for shard node agent to connect to GSM

-agent_password: sets the password for shard node agent to connect to GSM

If the-sharding parameter is not specified, the default is to create the system-managed (default) type

[oracle@db01 ~] $export ORACLE_BASE=/u01/app/oracle

[oracle@db01 ~] $export ORACLE_HOME=/u01/app/oracle/products/12.2.0/gsm_1

[oracle@db01 ~] $

Export PATH=/u01/app/oracle/products/12.2.0/gsmh_11/bin:$PATH:$HOME/bin

[oracle@db01 ~] $gdsctl

GDSCTL: Version 12.2.0.1.0-Production on Mon Feb 25 10:38:54 CST 2019

Welcome to GDSCTL, type "help" for information.

Warning: current GSM name is not set automatically because gsm.ora contains zero or several GSM entries. Use "set gsm" command to set GSM for the session.

Current GSM is set to GSMORA

GDSCTL > create shardcatalog-database db01:1521:db01-chunks 12-user mygds/oracle-sdb shdb-region region1, region2-agent_port 8080-agent_password oracle

Catalog is created

3.5 create and start shard director

Parameter meaning:

-gsm: specify the shard director name

-listener: specify the listening port of shard director. Note that it cannot conflict with the listener port of the database.

-catalog: specify catalog database information. Hostname of catalog database: listener port: catalog database db_name

GDSCTL > add gsm-gsm sharddirector1-listener 1522-pwd oracle-catalog db01:1521:db01-region region1

GSM successfully added

GDSCTL > start gsm-gsm sharddirector1

GSM started successfully

3.6 add operating system authentication

GDSCTL > add credential-credential cre_reg1-osaccount oracle-ospassword oracle

The operation has been completed successfully

3.7 execute Agent on all shard nodes

-execute on db02 node

[oracle@db02 ~] $schagent-start

Scheduler agent started using port 24409

[oracle@db02 ~] $schagent-status

Agent running with PID 8537

Agent_version:12.2.0.1.2

Running_time:00:00:11

Total_jobs_run:0

Running_jobs:0

Platform:Linux

ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1

ORACLE_BASE:/u01/app/oracle

Port:24409

Host:db02

-execute on db03 node

[oracle@db03 ~] $schagent-start

Scheduler agent started using port 19249

[oracle@db03 ~] $schagent-status

Agent running with PID 8344

Agent_version:12.2.0.1.2

Running_time:00:00:05

Total_jobs_run:0

Running_jobs:0

Platform:Linux

ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1

ORACLE_BASE:/u01/app/oracle

Port:19249

Host:db03

-password oracle and port 8080

[oracle@db02 ~] $echo oracle | schagent-registerdatabase 192.168.2.226 8080

Agent Registration Password?

Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent

Agent Registration Successful!

[oracle@db03 ~] $echo oracle | schagent-registerdatabase 192.168.2.226 8080

Agent Registration Password?

Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent

Agent Registration Successful!

3.8 create the data file storage path of the shard database

[oracle@db02 ~] $mkdir / u01/app/oracle/oradata

[oracle@db02 ~] $mkdir / u01/app/oracle/fast_recovery_area

[oracle@db03 ~] $mkdir / u01/app/oracle/oradata

[oracle@db03 ~] $mkdir / u01/app/oracle/fast_recovery_area

Four: create a System-Managed SDB4.1 connection gsm

Connect to the shard director/GSM server (db01) at the Shard server sd1

[oracle@db02 ~] $ssh oracle@db01

……

Oracle@db01's password:

Last login: Mon Feb 25 10:45:20 2019 from 192.168.70.249

[oracle@db01 ~] $source .bash _ profile

[oracle@db01 ~] $gdsctl

GDSCTL: version 12.2.0.1.0-Production, time: Mon Feb 25 11:16:04 CST 2019

Welcome to GDSCTL. Type "help" for information.

The current GSM is set to SHARDDIRECTOR1

GDSCTL > set gsm-gsm sharddirector1

GDSCTL > connect mygds/oracle

Directory connection established

4.2 add shardgroup

Shardgroup is a collection of shard. The name of the shardgroup is primary_shardgroup, and-deploy_as primary means that the shard in this group is the main library.

GDSCTL > add shardgroup-shardgroup primary_shardgroup-deploy_as primary-region region1

The operation has been completed successfully

4. 3 create shard

Add each shard address to catalog's valid node checking for registration (VNCR) list and create a shard

GDSCTL > add invitednode db02

GDSCTL > create shard-shardgroup primary_shardgroup-destination db02-credential cre_reg1-sys_password oracle

The operation has been completed successfully

Unique name of the database: sh2

GDSCTL > add invitednode db03

GDSCTL > create shard-shardgroup primary_shardgroup-destination db03-credential cre_reg1-sys_password oracle

The operation has been completed successfully

Unique name of the database: sh3

4.4 check the configuration

GDSCTL > config

Region

Region1

Region2

GSM

Sharddirector1

Sharddirector3

Sliced database

Shdb

Database

Sh2

Sh3

Split into groups

Primary_shardgroup

Piecewise space

Shardspaceora

Service

GDSCTL hold request

Command object statu

Global attribute

Name: oradbcloud

Main GSM: sharddirector1

DDL serial number: 0

GDSCTL > config shardspace

Segmented space block

Shardspaceora 12

GDSCTL > config shardgroup

Segmented block area sliced space

Primary_shardgroup 12 region1 shardspaceora

GDSCTL > config vncr

Name group ID

192.168.2.226

Db02

Db03

GDSCTL > config shard

Name shard group status status area availability

Sh2 primary_shardgroup U has no region1-

Sh3 primary_shardgroup U has no region1-

4.5 deployment / deploy

After deploy, the shard database is automatically deployed in silent installation on db02 and db03 servers, which is slow.

GDSCTL > deploy

Deploy: examining configuration...

Deploy: deploying primary shard 'sh2'...

Deploy: network listener configuration successful at destination 'db02'

Deploy: starting DBCA at destination 'db02' to create primary shard' sh2'.

Deploy: deploying primary shard 'sh3'...

Deploy: network listener configuration successful at destination 'db03'

Deploy: starting DBCA at destination 'db03' to create primary shard' sh3'.

Deploy: waiting for 2 DBCA primary creation job (s) to complete...

Deploy: DBCA primary creation job succeeded at destination 'db02' for shard' sh2'

Deploy: waiting for 1 DBCA primary creation job (s) to complete...

Deploy: DBCA primary creation job succeeded at destination 'db03' for shard' sh3'

Deploy: requesting Data Guard configuration on shards via GSM

Deploy: shards configured successfully

The operation has been completed successfully

4.6 check configuration information

GDSCTL > config shard

Name shard group status status area availability

Sh2 primary_shardgroup determines that region1 ONLINE has been deployed

Sh3 primary_shardgroup determines that region1 ONLINE has been deployed

GDSCTL > databases

Database: "sh2" Registration time: y status: determine ONS: N. Role: PRIMARY instance: 1 area: region1

Registered instances:

Shdb%1

Database: "sh3" Registration time: y status: determine ONS: N. Role: PRIMARY instance: 1 area: region1

Registered instances:

Shdb

GDSCTL > config shard-shard sh2

Name: sh2

Split group: primary_shardgroup

Status: OK

Status: deployed

Zone: region1

Connection string: db02:1521/sh2:dedicated

SCAN address:

ONS remote port: 0

Disk threshold, millisecond: 20

CPU threshold,%: 75

Version: 12.2.0.0

DDL that failed last time:

DDL error:

Failed DDL ID:

Availability: ONLINE

Rack:

Supported servic

Name preference status

GDSCTL > config shard-shard sh3

Name: sh3

Split group: primary_shardgroup

Status: OK

Status: deployed

Zone: region1

Connection string: db03:1521/sh3:dedicated

SCAN address:

ONS remote port: 0

Disk threshold, millisecond: 20

CPU threshold,%: 75

Version: 12.2.0.0

DDL that failed last time:

DDL error:

Failed DDL ID:

Availability: ONLINE

Rack:

Supported servic

Name preference status

4.7 create service

GDSCTL > add service-service oltp_rw_srvc-role primary

The operation has been completed successfully

GDSCTL > start service-service oltp_rw_srvc

The operation has been completed successfully

GDSCTL > status service

The service "oltp_rw_srvc.shdb.oradbcloud" has 2 instances. Relationship: ANYWHERE

Instance "shdb%1", name: "sh2", database: "sh2", area: "region1", status: ready.

Instance "shdb", name: "sh3", database: "sh3", area: "region1", status: ready.

Create users and objects 5.1 create business users in the catalog database

-db01

SQL > alter session enable shard ddl

SQL > create user app_schema identified by oracle

SQL > grant all privileges to app_schema

SQL > grant gsmadmin_role to app_schema

SQL > grant select_catalog_role to app_schema

SQL > grant connect, resource to app_schema

SQL > grant dba to app_schema

SQL > grant execute on dbms_crypto to app_schema

5.2 create a tablespace collection

-db01

SQL > conn app_schema

Enter password:

Connected.

SQL > alter session enable shard ddl

Session altered.

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.

GDSCTL > set gsm-gsm sharddirector1

GDSCTL > config shard

Directory connection established

Name shard group status status area availability

Sh2 primary_shardgroup determines DDL error region1 ONLINE

Sh3 primary_shardgroup determines DDL error region1 ONLINE

5.3 create tablespaces for duplicated tables

-duplicated table is Products table in this test.

SQL > CREATE TABLESPACE products_tsp datafile'/ u01 size autoextend on next autoextend on next 10m maxsize unlimited extent management local uniform size 1m

Tablespace created.

5.4 create root table Customers

SQL > CONNECT app_schema / oracle

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)

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

5. 5 create other sharded table Orders.

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)

5.6 create a sequence for the OrderId column

SQL > CREATE SEQUENCE Orders_Seq

Sequence created.

5.7Create SHARDED TABLE 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)

5.8Create duplicated tables.

-In this example, the Products table is a duplicated object.

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

5.9 check for errors

GDSCTL > set gsm-gsm sharddirector1

GDSCTL > show ddl

Failed fragmentation of ID DDL text

108 grant dba to app_schema

109 grant execute on dbms_crypto to app_s...

110 CREATE TABLESPACE SET TSP_SET_1 using...

111 CREATE TABLESPACE products_tsp dataf...

112 CREATE SHARDED TABLE Customers.

113 CREATE SHARDED TABLE Orders (O...

114 CREATE SHARDED TABLE LineItems (...

115 CREATE MATERIALIZED VIEW "APP_SCHEMA"...

116 CREATE OR REPLACE FUNCTION PasswCreat...

117 CREATE OR REPLACE FUNCTION PasswCheck...

GDSCTL > config shard-shard sh2

Name: sh2

Split group: primary_shardgroup

Status: OK

Status: deployed

Zone: region1

Connection string: db02:1521/sh2:dedicated

SCAN address:

ONS remote port: 0

Disk threshold, millisecond: 20

CPU threshold,%: 75

Version: 12.2.0.0

Last failed DDL: CREATE MATERIALIZED VIEW "APP_...

DDL error: ORA-00942: table or view does not exist

ORA-06512: at "SYS.EXECASUSER", line 44

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529

ORA-06512: at "SYS.EXECASUSER", line 31

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1\ (ngsmoci_execute\)

Failed DDL ID: 57

Availability: ONLINE

Rack:

Supported servic

Name preference status

Oltp_rw_srvc is enabled

VI: verify the environment-tablespace / chunks6.1 check chunks information in db01

When you created the shardcatalog earlier, you specified chunks as 12, so the subsequent creation of shard table allocates 12 chunks

GDSCTL > config chunks

Block

Database from to

Sh2 1 6

Sh3 7 12

6.2 check tablespace and chunks information at the sd1,sd2 node

-db02

-- tablespace

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 480

SYSTEM 810

TSP_SET_1 100

UNDOTBS1 70

TABLESPACE_NAME MB

USERS 5

12 rows selected.

Six tablespaces are created, which are C001TSP_SET_1 ~ tablespace C006TSP_SET_1, because each shard has 6 chunks because chunks=12 is set.

Each tablespace has a datafile of 100m size, which is the datafile 100m set when the tablespace set is created.

-- check chunks

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

Db_unique_name string sh2

SQL > select table_name, partition_name, tablespace_name from dba_tab_partitions

Where tablespace_name like'C% TSPs setup 1' order by tablespace_name; 2

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

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.

Check tablespace and chunks information at the sd2 node

-- tablespace

SQL > select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name

TABLESPACE_NAME MB

C007TSP_SET_1 100

C008TSP_SET_1 100

C009TSP_SET_1 100

C00ATSP_SET_1 100

C00BTSP_SET_1 100

C00CTSP_SET_1 100

PRODUCTS_TSP 100

SYSAUX 480

SYSTEM 810

TSP_SET_1 100

UNDOTBS1 65

USERS 5

12 rows selected.

Six tablespaces are created, which are C007TSP_SET_1 ~ tablespace C00CTSP_SET_1, because each shard has 6 chunks because chunks=12 is set.

Each tablespace has a datafile of 100m size, which is the datafile 100m set when the tablespace set is created.

-- check chunks

SQL > set linesize 140

SQL > column table_name format A20

SQL > column tablespace_name format A20

SQL > column partition_name format A20

SQL > select table_name, partition_name, tablespace_name from dba_tab_partitions

Where tablespace_name like'C% TSPs setup 1' order by tablespace_name

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

ORDERS CUSTOMERS_P7 C007TSP_SET_1

LINEITEMS CUSTOMERS_P7 C007TSP_SET_1

CUSTOMERS CUSTOMERS_P7 C007TSP_SET_1

ORDERS CUSTOMERS_P8 C008TSP_SET_1

CUSTOMERS CUSTOMERS_P8 C008TSP_SET_1

LINEITEMS CUSTOMERS_P8 C008TSP_SET_1

LINEITEMS CUSTOMERS_P9 C009TSP_SET_1

ORDERS CUSTOMERS_P9 C009TSP_SET_1

CUSTOMERS CUSTOMERS_P9 C009TSP_SET_1

LINEITEMS CUSTOMERS_P10 C00ATSP_SET_1

ORDERS CUSTOMERS_P10 C00ATSP_SET_1

CUSTOMERS CUSTOMERS_P10 C00ATSP_SET_1

ORDERS CUSTOMERS_P11 C00BTSP_SET_1

LINEITEMS CUSTOMERS_P11 C00BTSP_SET_1

CUSTOMERS CUSTOMERS_P11 C00BTSP_SET_1

LINEITEMS CUSTOMERS_P12 C00CTSP_SET_1

CUSTOMERS CUSTOMERS_P12 C00CTSP_SET_1

ORDERS CUSTOMERS_P12 C00CTSP_SET_1

18 rows selected.

6.3 check chunks information in catalog database

SQL > set echo off

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

SHARD NUMBER_OF_CHUNKS

Sh2 6

Sh3 6

6.4 Verification environment

-- catalog database

SQL > conn app_schema/oracle

Connected.

SQL > select table_name from user_tables

TABLE_NAME

PRODUCTS

MLOG$_PRODUCTS

CUSTOMERS

ORDERS

LINEITEMS

RUPD$_PRODUCTS

6 rows selected.

-- shard nodes sd1 and sd2

SQL > conn app_schema/oracle

Connected.

SQL > select table_name from user_tables

TABLE_NAME

CUSTOMERS

ORDERS

LINEITEMS

PRODUCTS

Seven: visit Shard7.1 to access a single shard

If sharding key is specified in the connection string, the GSM/shard director connects the request to the corresponding shard

Parameter meaning:

App_schema-is a business user

(host=gsm1) (port=1522)-is the GSM/shard director listening address

Service_name=oltp_rw_srvc.shdb.oradbcloud-is the global service created earlier

[oracle@db01 ~] $sqlplus app_schema/oracle@' (description= (address= (protocol=tcp) (host=192.168.2.226) (port=1522)) (connect_data= (service_name=oltp_rw_srvc.shdb.oradbcloud) (region=region1) (SHARDING_KEY=chenjch)'

SQL > select db_unique_name from v$database

DB_UNIQUE_NAME

Sh2

-insert data

SQL > INSERT INTO Customers (CustId, FirstName, LastName, CustProfile

Class, Geo, Passwd) VALUES ('chenjch',' chen', 'jch'

NULL, 'Gold',' east', hextoraw ('8d1c00e'); 2 3

1 row created.

SQL > commit

Commit complete.

SQL > select custid, FirstName, LastName, class, geo from customers where custid = 'chenjch'

CUSTID FIRSTNAME LASTNAME CLASS GEO

Chenjch chenjch Gold east

-- query SHARDING_KEY=tom.david and connect to sd2:

[oracle@db01 ~] $sqlplus app_schema/oracle@' (description= (address= (protocol=tcp) (host=db01) (port=1522))

(connect_data= (service_name=oltp_rw_srvc.shdb.oradbcloud) (region=region1) (SHARDING_KEY=ch.jc))'

SQL > select db_unique_name from v$database

DB_UNIQUE_NAME

Sh3

SQL > INSERT INTO Customers (CustId, FirstName, LastName, CustProfile

Class, Geo, Passwd) VALUES ('chjc',' ch', 'jc'

NULL, 'Gold',' east', hextoraw ('8d1c00a'))

1 row created.

SQL > commit

Commit complete.

-query data

-catalog local query

SQL > conn app_schema/oracle

SQL > column custid format A20

SQL > column firstname format A15

SQL > column lastname format A15

SQL > select custid, FirstName, LastName, class, geo from customers

CUSTID FIRSTNAME LASTNAME CLASS GEO

Chenjch chenjch Gold east

Chjc chjc Gold east

-sh2 local query

SQL > conn app_schema/oracle

Connected.

SQL > column custid format A20

SQL > column firstname format A15

SQL > column lastname format A15

SQL > select custid, FirstName, LastName, class, geo from customers

CUSTID FIRSTNAME LASTNAME CLASS GEO

Chenjch chenjch Gold east

-sh3 local query

SQL > conn app_schema/oracle

Connected.

SQL > column custid format A20

SQL > column firstname format A15

SQL > column lastname format A15

SQL > select custid, FirstName, LastName, class, geo from customers

CUSTID FIRSTNAME LASTNAME CLASS GEO

Chjc chjc Gold east

Summary:

1. Each shard node can only query the data of the local node shard table

2. You can query the aggregated data of shard nodes in the catalog database,

3. The client can specify the way of sharding key to obtain data directionally.

7.2 access to multiple shard

If sharding key is specified in the connection string, GSM/shard director connects the request to a corresponding shard.

If sharding key is not specified, session and coordinator database (shard catalog) establish a connection, then query in the shard that needs (prund) separately, and finally integrate.

The optimizer determines whether to access one shard or multiple shard.

-- Link to catalog database query

[oracle@db01 ~] $sqlplus app_schema/oracle@db01:1521/GDS\ $CATALOG.oradbcloud

SQL > conn app_schema/oracle

Connected.

SQL > set termout on

SQL > set linesize 120

SQL > set echo on

SQL > column firstname format A20

SQL > column lastname format A20

SQL > explain plan for SELECT FirstName,LastName, geo, class FROM Customers

WHERE CustId in ('Scott.Tiger@x.bogus',' Mary.Parker@x.bogus') AND class! = 'free' ORDER

BY geo, class

Eight: shard database start and stop

-reference

Https://blog.csdn.net/kiral07/article/details/86923508#Shard_Catalog_Database_63

8.1 shutdown

(1) close director on the catalog database side

GDSCTL > stop gsm-gsm SHARDDIRECTOR1

GSM stopped successfully

(2) disable catalog snooping

[oracle@db01 ~] $lsnrctl stop

(3) close the catalog database

SQL > shutdown immediate

(4) shard Node 1 and Node 2 turn off agent

[oracle@db02 trace] $schagent-stop

[oracle@db03 trace] $schagent-stop

(5) shard node 1 and node 2 turn off monitoring

[oracle@db02 trace] $lsnrctl stop

[oracle@db03 trace] $lsnrctl stop

(6) shard node 1 and node 2 shut down the database

SQL > shutdown immediate

8.2 start

(1) start the database and listen on the catalog side

SQL > startup

[oracle@db01 ~] $lsnrctl start

(2) all shard nodes start the database, listen, and agent

SQL > startup

[oracle@db02 ~] $lsnrctl start

[oracle@db03 ~] $lsnrctl start

(3) start director on the catalog side

GDSCTL > start gsm-gsm SHARDDIRECTOR1

GSM started successfully

(4) check

GDSCTL > connect mygds/oracle

Directory connection established

GDSCTL > config shard

Name shard group status status area availability

Sh2 primary_shardgroup determines DDL error region1 ONLINE

Sh3 primary_shardgroup determines DDL error region1 ONLINE

GDSCTL > config service

Name Network name Pool started all preferred

Oltp_rw_srvc oltp_rw_srvc.shdb.oradbcloud shdb, yes.

GDSCTL > databases

Database: "sh2" Registration time: y status: determine ONS: N. Role: PRIMARY instance: 1 area: region1

Service: "oltp_rw_srvc" Global start: y start time: y

Scan: y enable: y preferred: y

Registered instances:

Shdb%1

Database: "sh3" Registration time: y status: determine ONS: N. Role: PRIMARY instance: 1 area: region1

Service: "oltp_rw_srvc" Global start: y start time: y

Scan: y enable: y preferred: y

Registered instances:

Shdb

Nine: FAQ 9.1 create shard-shardgroup error

GDSCTL > create shard-shardgroup primary_shardgroup-destination db02-credential cre_reg1-sys_password oracle

GSM-45029: SQL error

ORA-03710: directory does not exist in the target or cannot be written to: $ORACLE_BASE/fast_recovery_area

ORA-06512: in "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 7469

ORA-06512: in "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: in "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4978

ORA-27436: scheduler agent operation failed with message: Agent Error: / u01/app/oracle/fast_recovery_area/shard_check.txt (No such file or directory)

ORA-06512: in "SYS.DBMS_ISCHED", line 3638

ORA-06512: in "SYS.DBMS_ISCHED", line 10444

ORA-06512: in "SYS.DBMS_ISCHED", line 5344

ORA-06512: in "SYS.DBMS_ISCHED", line 10439

ORA-06512: in "SYS.DBMS_ISCHED", line 3631

ORA-06512: in "SYS.DBMS_SCHEDULER", line 2414

ORA-06512: in "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 164,

ORA-27436: scheduler agent operation failed with message: Agent Error: / u01/app/oracle/fast_recovery_area/shard_check.txt (No such file or directory)

ORA-06512: in "SYS.DBMS_ISCHED", line 3638

ORA-06512: in "SYS.DBMS_ISCHED", line 10444

ORA-06512: in "SYS.DBMS_ISCHED", line 5344

ORA-06512: in "SYS.DBMS_ISCHED", line 10439

ORA-06512: in "SYS.DBMS_ISCHED", line 3631

ORA-06512: in "SYS.DBMS_SCHEDULER", line 2414

ORA-06512: in "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 157

ORA-06512: in "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 4965

ORA-06512: in line 1

-the error message is obvious. There is no fast_recovery_area directory, so you need to create a fast_recovery_area directory on the db02 and db03 servers in advance.

[oracle@db02 ~] $mkdir $ORACLE_BASE/fast_recovery_area

9.2 catalog creates users and tables that cannot be synchronized to the shard db library

There are many reasons for errors, and you need to analyze specific problems. Generally, you need to use the following commands and logs to analyze problems:

(1) recover shard-shard sh2

(2) config shard-shard sh2

(3) alert_sh2.log

[oracle@db02 trace] $pwd

/ u01/app/oracle/diag/rdbms/sh2/sh2/trace

[oracle@db02 trace] $tail-f alert_sh2.log

For example:

GDSCTL > recover shard-shard sh2

GSM error:

Primary_shardgroup sh2:ORA-01119: error in creating database file\'/ u01/app/oracle/oradata/db01/products_tsp01.dbf\'

ORA-06512: at "SYS.EXECASUSER", line 44

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1529

ORA-06512: at "SYS.EXECASUSER", line 31

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 (ngsmoci_execute)

GDSCTL > recover shard-shard sh2

GSM error:

Primary_shardgroup sh2:ORA-03715: invalid user APP_SCHEMA

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.EXECASUSER", line 22

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1 (ngsmoci_execute)

GDSCTL > config shard-shard sh3

Name: sh3

Split group: primary_shardgroup

Status: OK

Status: deployed

Zone: region1

Connection string: db03:1521/sh3:dedicated

SCAN address:

ONS remote port: 0

Disk threshold, millisecond: 20

CPU threshold,%: 75

Version: 12.2.0.0

Last failed DDL: drop tablespace TSP_SET_1 incl...

DDL error: ORA-03715: invalid user APP_SCHEMA

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.EXECASUSER", line 22

ORA-06512: at "GSMADMIN_INTERNAL.EXECUTEDDL", line 70

ORA-06512: at line 1\ (ngsmoci_execute\)

9.3 GSM failed to start with exception or not running

Problem phenomenon:

Start GSM to show that the instance is running, stop GSM to show exception or not run

Troubleshoot the reason:

View alarm log

[oracle@db01 trace] $pwd

/ u01/app/oracle/diag/gsm/db01/sharddirector1/trace

[oracle@db01 trace] $vi alert_sharddirector.log

The cause of the problem:

When you create a sharddirector, the specified port is already occupied by other applications, but it can be created successfully. If you start GSM later, it will fail. Just delete and create it.

GDSCTL > add gsm-gsm sharddirector1-listener 1522-pwd oracle-catalog db01:1521:db01-region region1

Ten: references

"what's new in 12c-introduction to Oracle Sharding"

Https://blogs.oracle.com/database4cn/12c-oracle-sharding

"Sharded Database Deployment"

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/sharding-deployment.html#GUID-61B495D3-4482-47E2-937E-B7E03286F565

"Oracle 12C Sharding fragmentation Database"

Https://blog.csdn.net/kiral07/article/details/86923508#OracleSharding_Manage_80

"Oracle Sharding-Troubleshooting Tips and Techniques (document ID 2180259.1)"

Https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=151coor2ou_9&_afrLoop=249031377428729

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.