Tag Archives: config

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

Create and Drop Oracle Enterprise Manager Console on Windows Server

Configure Oracle Enterprise Manager Console on Windows Server

1. Check the sysman user is present in the database.

select username from dba_users where username ='SYSMAN';

1.jpg

2. If exists command to drop existing setting otherwise go to step 4

emca -deconfig dbcontrol db -repos drop

2.jpg

3. After step 2 check the user sysman is dropped from database.

select username from dba_users where username ='SYSMAN';

3

4. Start configuring Enterprise manager repository

emca -repos create

4

5. Configure db control service

emca -config dbcontrol db

 
4

It’s failed due to user name is locked, So need to reset and unlock username for DBSNMP and SYSMAN.

5

6. Again start the db control command:

emca -config dbcontrol db

1.png

6
Installation is completed.

Step 7: check status of Enterprise manage tool as

emctl status dbconsole

Step 8: Start the enterprise manager tool

emctl start dbconsole

step 9: Stop the enterprise manager tool

emctl stop dbconsole

If you received following errors:
The OracleDBConsolePEGA service could not be started.
A service specific error occurred: 2.
More help is available by typing NET HELPMSG 3547.
 
Solution:
Then set the following parameters:

set oracle_home=
Set PATH=
set oracle_sid=
set oracle_hostname=localhost
set oracle_unqname=pega


Try to start the em console. Go to following link:
https://smarttechways.com/2014/09/25/environment-variable-oracle_unqname-not-defined-in-em-status/

emctl start dbconsole

Note: If it’s not started then set upper parameter and start from step 1 from reconfigure again.