Tag Archives: show ddl

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

DDL Execution in Oracle Sharded Database

DDL Execution in Oracle Sharded Database
The Shard catalog database has copies of all the objects present in sharded database. If catalog validate the DDL then it applied on all shards(database).
If new shard is added then from catalog database it will propagate the ddls to all the shards (databases).

Two ways you can issue DDLs in an SDB:

1. GDSCTL sql command.
When you execute a command , all shards are updated and return status of execution. A master copy is present in shard catalog.


GDSCTL> sql "create tablespace set tbsset"

2. Connect to the shard catalog database using SQL*Plus using the GDS$CATALOG.sdbname service
When you execute the command, local shards response on execution and all other shards are working inbackgroud.

SQL> create tablespace set tbsset;

For SQL PLUS two type of object is created. SDB object and local object. Local objects are the object that exist in Shard catalog and used for administrative purpose.
Note: type of object (SDB or local) that is created in a SQL*Plus session depends on whether the SHARD DDL mode is enabled in the session. Mode is enabled default if connect with catalog database of SDB user.

--For Disable create local object
alter session disable shard ddl;

--For enable create shared objects
alter session enable shard ddl;

Note: If you direct connected with shared user or catalog, you donot need to enable shared it automatic enabled.

Check Status of DDL Commands:
SHOW DDL and CONFIG SHARD is used from GDSCTL. It will tell you failed shards on which command is failed.

GDSCTL> show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****

Note: IF DDL failed on one shard all further DDL are blocked until failure is resolved (GDSCTL recover shard)

Example For checking the DDL command executed on all shreds:

-- Enable the Shard DDL Session
SQL> alter session enable shard ddl;

-- Fire the Command for creating a User
SQL> CREAT USER example_user IDENTRIFIED BY password1;
CREATE USER example_user IDENTRIFIED BY password1
*
ERROR at line 1:
ORA-00922: missing or invalid option

-- Check the Command show in List.
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------

Note: it show command is not listed means not executed in any Shard(database)

--Fire the create user command again
SQL> CREATE USER example_user IDENTIFIED BY password1;
User created.

-- Show the command is executed successfully
GDSCTL>show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****