How to handle ODA dangling job entries from a failed ODA odacli operation

odacli delete db issues

On Oracle Database Appliance ODA X7-HA (12.2.1.2), I created a database call TEST database using odacli create-database command, then I ripped out the current “freshly created” datafiles and used RMAN duplicate command to restored the datafiles. It went alright without any issues. However last week, I decided to delete this TEST database , and it hang for a long time on “Deleting Datafiles” steps. I have a feeling that odacli didn’t recognize the “restored” database files. 

The only reason I know it was “Deleting Datafiles” steps is because I did “odacli describe-job”. 

I wish there is a force delete dbhome command from odacli, or unregister database command. This database I was trying to delete is the one that I cloned from our TEST EBS database. 

[root@vnanode0 ~]# odacli list-dbstorages 

DCS-10032:Resource db storage is not found.

Unable to delete (cloned database from non-oda platform) database., odacli delete database hangs 

[root@vnanode0 ~]# odacli delete-database -i d488be55-d2af-4615-a766-5f230fc48aff -fd 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“status” : “Running”, 

“message” : null, 

“reports” : [ { 

“taskId” : “TaskZJsonRpcExt_2126”, 

“taskName” : “Validate db d488be55-d2af-4615-a766-5f230fc48aff for deletion”, 

“taskResult” : “Success”, 

“startTime” : “June 05, 2018 15:33:41 PM EDT”, 

“endTime” : “June 05, 2018 15:33:41 PM EDT”, 

“status” : “Success”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info”, 

“updatedTime” : “June 05, 2018 15:33:41 PM EDT” 

} ], 

“createTimestamp” : “June 05, 2018 15:33:41 PM EDT”, 

“resourceList” : [ ], 

“description” : “Database service deletion with db name: TEST with id : d488be55-d2af-4615-a766-5f230fc48aff”, 

“updatedTime” : “June 05, 2018 15:33:41 PM EDT” 

[root@vnanode0 ~]# odacli list-databases 

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID 

—————————————- ———- ——– ——————– ———- ——– ——– ———- ———— —————————————- 

d488be55-d2af-4615-a766-5f230fc48aff TEST RacOne 12.1.0.2 false IMDB odb16 ACFS Deleting da48243e-2d6f-47e5-ae8a-7f958cf5bbbf

[root@vnanode0 ~]# odacli describe-job -i 8bb544f6-4f24-4e18-900a-71bf5ef7f383 

Job details 

—————————————————————- 

ID: 8bb544f6-4f24-4e18-900a-71bf5ef7f383 

Description: Database service deletion with db name: TEST with id : d488be55-d2af-4615-a766-5f230fc48aff 

Status: Failure 

Created: June 4, 2018 7:56:47 AM EDT 

Message: DCS-10011:Input parameter ‘ACFS Device for delete’ cannot be NULL. 

Task Name Start Time End Time Status 

————————————————– ———————————– ———————————– ———- 

database Service deletion for d488be55-d2af-4615-a766-5f230fc48aff June 4, 2018 7:56:47 AM EDT June 4, 2018 7:56:52 AM EDT Failure 

database Service deletion for d488be55-d2af-4615-a766-5f230fc48aff June 4, 2018 7:56:47 AM EDT June 4, 2018 7:56:52 AM EDT Failure 

Validate db d488be55-d2af-4615-a766-5f230fc48aff for deletion June 4, 2018 7:56:47 AM EDT June 4, 2018 7:56:47 AM EDT Success 

Database Deletion June 4, 2018 7:56:47 AM EDT June 4, 2018 7:56:48 AM EDT Success 

Unregister Db From Cluster June 4, 2018 7:56:48 AM EDT June 4, 2018 7:56:48 AM EDT Success 

Kill Pmon Process June 4, 2018 7:56:48 AM EDT June 4, 2018 7:56:48 AM EDT Success 

Database Files Deletion June 4, 2018 7:56:48 AM EDT June 4, 2018 7:56:48 AM EDT Success 

Deleting FileSystem June 4, 2018 7:56:50 AM EDT June 4, 2018 7:56:52 AM EDT Failure <<<<<<<<<<<<<<<<<<<<<<<<<<< Shows Failure in Deleting FileSystem 

Even odacli delete-database -i <dbid> -fd.   Also Failed

Bug 27048646 was filed to support deleting and cleaning up the database entries. 

[root@vnanode0 ~]# odacli list-databases 

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID 

—————————————- ———- ——– ——————– ———- ——– ——– ———- ———— —————————————- 

d488be55-d2af-4615-a766-5f230fc48aff TEST RacOne 12.1.0.2 false IMDB odb16 ACFS Deleting da48243e-2d6f-47e5-ae8a-7f958cf5bbbf 

From dcs-agent.log: 

————————- 

2018-06-04 07:56:49,405 DEBUG [Stopping FileSyestem] [] c.o.d.c.u.CommonsUtils: 

run: cmd= ‘[su, 

-, 

oracle, 

-c, 

export ORACLE_SID=+ASM1; 

export ORACLE_HOME=/u01/app/12.2.0.1/oracle; 

/u01/app/12.2.0.1/oracle/bin/asmcmd –nocp volinfo -G Data datTEST]’ 

2018-06-04 07:56:50,225 DEBUG [Stopping FileSyestem] [] c.o.d.c.u.c.CommandExecutor: Return code: 0 

2018-06-04 07:56:50,225 DEBUG [Stopping FileSyestem] [] c.o.d.c.u.CommonsUtils: Output : 

volume datTEST not found in diskgroup Data 

2018-06-04 07:56:50,225 ERROR [Stopping FileSyestem] [] c.o.d.a.r.s.s.VolumeUtils: Failed to get the acfs device for the volume datTEST in the disk group Data

… 

2018-06-06 10:01:00,902 INFO [dw-2755 – GET /jobs/bb7bc2e3-94f2-4523-b901-32257f493e68] [] c.o.d.a.r.JobsApi: Received GET request to getJobDetail on JobsApi with jobid = bb7bc2e3-94f2-4523-b901-32257f493e68 

2018-06-06 10:01:00,908 DEBUG [dw-2755 – GET /jobs/bb7bc2e3-94f2-4523-b901-32257f493e68] [] c.o.d.c.t.r.ReportApi: Job Report: 

“updatedTime” : 1528227225929, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“status” : “Failure”, 

“message” : “DCS-10011:Input parameter ‘ACFS Device for delete’ cannot be NULL.”, 

“reports” : [ { 

“updatedTime” : 1528227225922, 

“startTime” : 1528227221448, 

“endTime” : 1528227225922, 

“taskId” : “TaskZLockWrapper_2123”, 

“status” : “Failure”, 

“taskResult” : “DCS-10011:Input parameter ‘ACFS Device for delete’ cannot be NULL.”, 

“taskName” : “database Service deletion for d488be55-d2af-4615-a766-5f230fc48aff”, 

“taskDescription” : null, 

“parentTaskId” : “TaskServiceRequest_2122”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Error” 

}, { 

“updatedTime” : 1528227225917, 

“startTime” : 1528227221467, 

“endTime” : 1528227225917, 

“taskId” : “TaskSequential_2124”, 

“status” : “Failure”, 

“taskResult” : “DCS-10011:Input parameter ‘ACFS Device for delete’ cannot be NULL.”, 

“taskName” : “database Service deletion for d488be55-d2af-4615-a766-5f230fc48aff”, 

“taskDescription” : null, 

“parentTaskId” : “TaskZLockWrapper_2123”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Error” 

}, { 

“updatedTime” : 1528227221491, 

“startTime” : 1528227221484, 

“endTime” : 1528227221491, 

“taskId” : “TaskZJsonRpcExt_2126”, 

“status” : “Success”, 

“taskResult” : “Success”, 

“taskName” : “Validate db d488be55-d2af-4615-a766-5f230fc48aff for deletion”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info” 

}, { 

“updatedTime” : 1528227222042, 

“startTime” : 1528227221581, 

“endTime” : 1528227222042, 

“taskId” : “TaskZJsonRpcExt_2138”, 

“status” : “Success”, 

“taskResult” : “database deleted successfully”, 

“taskName” : “Database Deletion”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info” 

}, { 

“updatedTime” : 1528227222224, 

“startTime” : 1528227222050, 

“endTime” : 1528227222224, 

“taskId” : “TaskZJsonRpcExt_2140”, 

“status” : “Success”, 

“taskResult” : “database unregistered from cluster successfully”, 

“taskName” : “Unregister Db From Cluster”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info” 

}, { 

“updatedTime” : 1528227222371, 

“startTime” : 1528227222316, 

“endTime” : 1528227222371, 

“taskId” : “TaskZJsonRpcExt_2144”, 

“status” : “Success”, 

“taskResult” : “pmon process of database killed successfully”, 

“taskName” : “Kill Pmon Process”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info” 

}, { 

“updatedTime” : 1528227222739, 

“startTime” : 1528227222561, 

“endTime” : 1528227222739, 

“taskId” : “TaskZJsonRpcExt_2148”, 

“status” : “Success”, 

“taskResult” : “database files deleted successfully”, 

“taskName” : “Database Files Deletion”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Info” 

}, { 

“updatedTime” : 1528227225908, 

“startTime” : 1528227224546, 

“endTime” : 1528227225908, 

“taskId” : “TaskZJsonRpcExt_2152”, 

“status” : “Failure”, 

“taskResult” : “DCS-10011:Input parameter ‘ACFS Device for delete’ cannot be NULL.”, 

“taskName” : “Deleting FileSystem”, 

“taskDescription” : null, 

“parentTaskId” : “TaskSequential_2124”, 

“jobId” : “bb7bc2e3-94f2-4523-b901-32257f493e68”, 

“tags” : [ ], 

“reportLevel” : “Error” 

} ], 

“createTimestamp” : 1528227221408, 

“resourceList” : [ ], 

“description” : “Database service deletion with db name: TEST with id : d488be55-d2af-4615-a766-5f230fc48aff” 

————————- 

Resolution – NOTE!!! Only do this with the advisement and clearance of Oracle Support (I did this with OSupport on WebEX)

This required us to manually cleanup the orphan job entires

First, download db-derby-10.11.1.1-lib.zip from the following URL: 

http://db.apache.org/derby/releases/release-10.11.1.1.cgi 

1. download and unzip db-derby-10.11.1.1-lib.zip 

2. Shut down dcsagent: 

# initctl stop initdcsagent 

3. cd /opt/oracle/dcs/repo/ (there must be node_0 under it) 

ls 

node_0 

4. Connect to database and run sql commands to query/remove the rows in the metadata tables. 

# java -cp /root/db-derby-10.11.1.1-bin/lib/derbytools.jar:/root/db-derby-10.11.1.1-bin/lib/derbyclient.jar:/root/db-derby-10.11.1.1-bin/lib/derby.jar org.apache.derby.tools.ij 

ij>connect ‘jdbc:derby:node_0’; 

5. ij>show tables; 

6. ij>select id,name,dbname,dbid,status,DBSTORAGE from db where dbname=’&DELETEDDATABASENAME’; 

>> It should return 1 row for the deleted database 

7. ij> delete from db where dbname=’&DELETEDDATABASENAME’; 

8. ij> commit; 

9. Start dcsagent 

initctl start initdcsagent 

Note we will iterate through steps 6 and 7 to remove everything that shouldn’t be there – may be storage details and locations as well. 

Basically remove anything in the table relating to the old database. 

[root@vnanode0 ~]# odacli list-databases 

DCS-10032:Resource database is not found. 

[root@vnanode0 ~]# odacli list-dbhomes 

ID Name DB Version Home Location Status 

—————————————- ——————– —————————————- ——————————————— ———- 

5afc2dd7-35c2-4ffa-a0cc-2c81ef190e9f OraDB12102_home2 12.1.0.2.171017 (26914423, 26717470) /u01/app/oracle/product/12.1.0.2/dbhome_2 Configured