Tag Archives: Shard database

Check error on a Shard during execution of DDL commands with recovery

Checking the error on a Shard during DDL command execution with recovery

Case 1: In this case, user attempt to create tablespace on Shard database but one location is not having write permission on shards.

1. Connect with the shard user

SQL> connect example_user/ password1
Connected

2. Try to create a tablespace on shard Database it will apply on all shard (local or remote database)

SQL> create tablespace set tbsset;
Tablespace created.

3. Check the status of command is executed and pending on any shard

GDSCTL> show ddl

id DDL Text                                      Failed shards
-- --------------------------------------------  -------------
1  create user example_user identified by *****
2  create tablespace set tbsset                  shard01 

4. Fetch the detail information about the error

GDSCTL> config shard -shard shard01
Conversion = ':'Name: shard01
Shard Group: dbs1
Status: Ok
State: Deployed
Region: east
Connection string: (DESCRIPTION=(ADDRESS=(HOST=shard01-host)(PORT=1521)
(PROTOCOL=tcp))
(CONNECT_DATA=(SID=shard01)))
SCAN address:
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 18.0.0.0
Failed DDL: create tablespace set tbsset
DDL Error: ORA-02585: create tablepsace set failure, one of its tablespaces not
created
ORA-01119: error in creating database file \'/ade/b/3667445372/oracle/rdbms/dbs/
SHARD01/datafile/o1_mf_tbsset_%u_.dbf\'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1 \(ngsmoci_execute\)
Failed DDL id: 2
Availability: ONLINE

5. From this we find the Shard01 is not having write permission, so login to server and give it permission.

6. Run the following command on Shard01 for recover

GDSCTL> recover shard -shard shard01

7. Check with show command:

GDSCTL> show ddl

id DDL Text                                      Failed shards
-- --------------------------------------------  -------------
1  create user example_user identified by *****
2  create tablespace set tbsset

CASE 2: The user attempts to create another tablespace and DDL fails on a shard because there is already an existing local tablespace with the same name.

1. connect with Shard Catalog:

SQL> create tablespace set tbs_set;

2. On checking the Status:

GDSCTL> show ddl

id DDL Text                                      Failed shards
-- --------------------------------------------  -------------
1  create user example_user identified by *****
2  create tablespace set tbsset
3  create tablespace set tbs_set                 shard01 

3. check the complete error detail with config command:

GDSCTL> config shard -shard shard01
Conversion = ':'Name: shard01
……
Failed DDL: create tablespace set tbs_set
DDL Error: ORA-02585: create tablespace set failure, one of its tablespaces not
created
ORA-01543: tablespace \'TBS_SET\' already exists \(ngsmoci_execute\)

4. In this one case drop the tablespace on shard01 and fire the recovery process as done in CASE 1 and other case is drop the new created tablespace on all other shards and create new one on shard database.
Note: We are working on other 2nd case. first one is already done in CASE 1.

5. Now we fired drop and create tablespace command

SQL> drop tablespace set tbs_set;
SQL> create tablespace set tbs_set2

6. Then check the status with show command:

GDSCTL> show ddl

id DDL Text                                      Failed shards
-- --------------------------------------------  -------------
1  create user example_user identified by *****
2  create tablespace set tbsset
3  create tablespace set tbs_set                 shard01
4  drop tablespace set tbs_set
5  create tablespace set tbsset2 


Note: DDLs 4 and 5 are not attempted on shard01 because DDL 3 failed there.

7. To overcome, we need to fire the recovery command.
Skip the 3 DDL command to run on Shard01 with -ignore_first option. In this we have tablespace on all other shards(database) but not on shard01 node.

GDSCTL> recover shard -shard shard01 –ignore_first

8. Now it come to 4 DDL command that is drop command of same tablespace but it will again produced error because of tablespace not present on Shard01.

GDSCTL> show ddl
id DDL Text                                Failed shards
-- --------------------------------------  -------------
1  create user sidney identified by *****
2  create tablespace set tbsset
3  create tablespace set tbs_set
4  drop tablespace set tbs_set             shard01
5  create tablespace set tbsset2 

9. Skip the 4 DDL command on Shard01 as above command because tablespace is already not present on shard01.

GDSCTL> recover shard -shard shard01 –ignore_first

10. Check the status of all commands and its executed on all shard(nodes or databases) without error:

GDSCTL> show ddl
id DDL Text Failed shards
-- -------------------------------------- -------------
1 create user sidney identified by *****
2 create tablespace set tbsset
3 create tablespace set tbs_set
4 drop tablespace set tbs_set
5 create tablespace set tbsset2

Note: No failed Shards means ddl command executed on all shards

Advertisements

Sharding Database in Oracle

Sharding Database in Oracle

Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in such a configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB). Oracle Sharding is implemented based on the Oracle Database partitioning feature.

SHARDED DATABASE:
Database is host independently with all resources like CPU, disk, memory as standalone server. This database is called shared.
All together make a single logical database called sharded database.
SHAREDED TABLE:
Splitting a table across shards so that each shard contains the table with the same columns but a different subset of rows.
A table split up in this manner is also known as a sharded table.
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Note: Oracle Database supports scaling up to 1000 shards.

Terms Used in Shard Environment

Sharded database (SDB) – A SDB is collection of shards represent as a single logical Oracle Database.
Shards – independent physical Oracle databases
Global service – database services that provide access to data in an SDB
Shard catalog – Stored SDB configuration data and manage centralized of sharded database. shard catalog also contains the master copy of all duplicated tables in an SDB. The shard catalog uses materialized views to automatically replicate changes to duplicated tables in all shards.
Shard directors – network listeners that enable high performance connection routing based on a sharding key.
Connection pools – at runtime, act as shard directors by routing database requests across pooled connections
Management interfaces – GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI)

Defining and creating process of Objects in Shared

Shared table
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Example
creates a sharded table having horizontally partitioning the table across shards based on sharding key cust_id:


CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, region VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET ts1;

Note: Global index is not supported in Shared.
Oracle Sharding creates and manages tablespaces as a unit called a tablespace set.
The PARTITIONS AUTO clause specifies that the number of partitions should be automatically determined.

CHUNK
The unit of data migration between shards is a chunk. A chunk is a set of tablespaces that store corresponding partitions of all tables in a table family.

Table Family
parent-child relationship between database tables with a referential constraint (foreign key). A set of such tables is referred to as a table family. A
table in a table family that has no parent is called the root table.
In this example customer table is parent and order table is child

CREATE SHARDED TABLE Customers
( CustNo NUMBER NOT NULL
, Name VARCHAR2(50)
, Address VARCHAR2(250)
, region VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;
CREATE SHARDED TABLE Orders
( OrderNo NUMBER
, CustNo NUMBER NOT NULL
, OrderDate DATE
)
PARENT Customers
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

Duplicate Table
A table with the same contents in each shard is called a duplicated table. It simple presents on all the database(shards)
Oracle Sharding synchronizes the contents of duplicated tables using Materialized View Replication. A duplicated table on each shard is represented by a materialized view.
Note: refresh frequency of all duplicated tables is controlled by the database initialization parameter SHRD_DUPL_TABLE_REFRESH_RATE. Default value is 60 seconds

CREATE DUPLICATED TABLE Products
( StockNo NUMBER PRIMARY KEY
, Description VARCHAR2(20)
, Price NUMBER(6,2))
);