ODA light (ODA X7-2S, X7-2M, X8-2S, X8-2M) come with an internal derby database to manage ODA metadata. From time to time, there is a need to check or update some information within it, as for example when facing database deletion issue. I would like to strongly advise that updating manually the ODA repository should only be done after getting Oracle support guidance and agreement to do so. Neither the author (that’s me 🙂 ) nor dbi services 😉 would be responsible for any issue or consequence following commands described in this blog. This would be your own responsability. 😉
This blog is more intended to show how to connect to this internal derby database.

Performing a backup of the derby database

Breaking the derby database would damage the ODA, and having as consequence to reimage the ODA.

To backup the derby database, we need to stop the dcs agent.

[root@ODA03 derbyjar]# initctl stop initdcsagent
initdcsagent stop/waiting

[root@ODA03 derbyjar]# ps -ef | grep dcs-agent | grep -v grep
[root@ODA03 derbyjar]#

Then we can backup the repository :

[root@ODA03 derbyjar]# cd /opt/oracle/dcs/repo/

[root@ODA03 repo]# ls -l
total 4
drwxr-xr-x 4 root root 4096 Jun 12 14:05 node_0

[root@ODA03 repo]# cp -rp node_0 node_0_BKP_12.06.2019

Connecting to the derby database

To connect to the backup previously done, use the following connect string :

[root@ODA03 repo]# /usr/java/jdk1.8.0_161/db/bin/ij
ij version 10.11
ij> connect 'jdbc:derby:node_0_BKP_12.06.2019';

To connect to the running derby database use the following connect string (dcs agent needs to be stopped) :

[root@ODA03 repo]# /usr/java/jdk1.8.0_161/db/bin/ij
ij version 10.11
ij> connect 'jdbc:derby:node_0';

Running commands

The language used to interact with the derby database is common SQL language :

ij> select id,name,dbname,dbid,status,DBSTORAGE from db where dbname='test';
ID                                                                                                                              |NAME                                                                                                                            |DBNAME                                                                                                                          |DBID                                                                                                                            |STATUS                                                                                                                          |DBSTORAGE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c7db612e-f237-4491-9a30-ff2c2b75831f                                                                                            |test                                                                                                                            |test                                                                                                                            |032306496044                                                                                                                    |Deleting                                                                                                                        |Acfs

1 row selected
ij>

To list all the tables, you can run :

ij> show tables;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS
------------------------------------------------------------------------
SYS                 |SYSALIASES                    |
SYS                 |SYSCHECKS                     |
...
...
...
APP                 |CPUCORES                      |
APP                 |DATABASE                      |
APP                 |DATABASEHOME                  |
APP                 |DB                            |
APP                 |DBHOME                        |
APP                 |DBNODE                        |
APP                 |DBSTORAGEDETAILS              |
APP                 |DBSTORAGEDETAILS_VOLS         |
APP                 |DBSTORAGELOCATIONS            |
APP                 |DCSPARAMETERS                 |
APP                 |DCS_USER                      |
APP                 |DG_CONFIGURATION              |
APP                 |DG_CONFIGURATION_REPLICATION_&|
APP                 |DISK                          |
APP                 |DISKGROUP                     |
APP                 |DISKINFO                      |
APP                 |FILEMULTIUPLOADPARTSRECORD    |
APP                 |FILEMULTIUPLOADRECORD         |
APP                 |FS                            |
APP                 |GI                            |
APP                 |GIHOME                        |
APP                 |GROUPENTITY                   |
APP                 |IDEMPOTENCYMAP                |
APP                 |JOBEXECUTION                  |
APP                 |JOBSCHEDULE                   |
APP                 |JOB_REPORT                    |
APP                 |JOB_RESOURCE_INFO             |
APP                 |LOGCLEANPOLICY                |
APP                 |LOGCLEANUPSUMMARY             |
APP                 |NETSECURITYRULES              |
APP                 |NETSECURITY_ENCRYPTIONALGORIT&|
APP                 |NETSECURITY_INTEGRITYALGORITH&|
APP                 |NETWORK                       |
APP                 |NETWORKINTERFACE              |
APP                 |NETWORKINTERFACE_INTERFACEMEM&|
APP                 |NETWORK_NETWORKTYPE           |
APP                 |OBJECTSTORESWIFT              |
APP                 |OBSERVER                      |
...
...
...

93 rows selected
ij>

Exiting derby database connection

To exit the tool, use following command :

ij> exit;

Getting last version of derby jar

One time, the derby jar file provided by the oracle support team was not the correct version. As per there guidance, I had to run :

[root@ODA03 repo]# java -cp /root/derbyjar/derby.jar:/root/derbyjar/derbytools.jar org.apache.derby.tools.ij
ij version 10.11
ij> connect 'jdbc:derby:node_0';

I got following errors :

ERROR XJ040: Failed to start database 'node_0' with class loader sun.misc.Launcher$AppClassLoader@42a57993, see the next exception for details.
ERROR XSLAN: Database at /opt/oracle/dcs/repo/node_0 has an incompatible format with the current version of the software.  The database was created by or upgraded by version 10.14.

I could download the last and appropriate derby jar from the apache web site. I could then successfully connect using correct version of derby jar related to the current ODA database.

[root@ODA03 repo]# java -cp /root/derbyjar/derby.jar:/root/derbyjar/derbytools.jar org.apache.derby.tools.ij
ij version 10.11
ij> connect 'jdbc:derby:node_0';
ij>

Conclusion

It might be interesting to know how to connect to metadata database from the ODA in order to check, troubleshoot and understand some internal ODA behavior. BUT, remember, that no update should be done in this database without Oracle support agreement and without running previously a backup of the repository.