If you ever have to restore the Oracle Virtual Machine Manager database using the provided RestoreDatabase.sh, chances are that you will end up with a bunch of corrupted or missing tables in the back-end MySQL database schema. Specifically, all tables with 0 rows will be dysfunctional after successful restore. In turn this prevents management of compute-nodes, virtual machines and other resources.
I don’t know which other versions of OVM might be affected, but the latest 2.0.2 software release for the Virtual Compute Appliance certainly is.
I wrote a workaround that will identify, drop and re-create the corrupted tables. No guarantees, but it did the trick here. Perhaps it can save someone a bit of a headache.
===== CUSTOMER BUG REPORT =====Description
———–
Assorted errors related to missing tables after restoring OVMM database using provided tools.
Example:
While trying to stop a VM server:
OVMAPI_6000E Internal Error: Caught during commit: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘ovs.Mgr_ServerStoppingEvent’ doesn’t exist
Affected Versions
—————–
Oracle Virtual Compute Appliance Software Release 2.0.2
Oracle VM Manager Software Release 3.2.8.x
Working Theory
————–
All ovs tables with 0 rows are corrupted during restore.
Steps to repdroduce
——————-
1. Restore OVMM database using /u01/app/oracle/ovm-manager-3/ovm_shell/tools/RestoreDatabase.sh
2. Query all tables in ovs schema
3. Look for ERROR 1146
Workaround
———-
1)
service ovmm stop ; service ovmm_mysql stop
2)
sudo -u oracle /bin/bash /u01/app/oracle/ovm-manager-3/ovm_shell/tools/RestoreDatabase.sh AutoFullBackup-20150510_0100
3)
service ovmm_mysql start
4)
This should identify, drop and re-create affected tables:
/usr/bin/mysql -D ovs -b -f -s -u root -pWelcome1 -S /u01/app/oracle/mysql/data/mysqld.sock -e “show tables;” | awk ‘{print “select count(*) from ” $1 “;” }’ | /usr/bin/mysql -D ovs -b -f -s -u root -pWelcome1 -S /u01/app/oracle/mysql/data/mysqld.sock 2>&1 >/dev/null | grep “ERROR 1146” | awk –field-separator=\’ ‘{print “drop table ” $2 “;\ncreate table ” $2″(m_id bigint,m_data longblob,primary key (m_id));”}’ | /usr/bin/mysql -D ovs -b -f -s -u root -pWelcome1 -S /u01/app/oracle/mysql/data/mysqld.sock
5)
service ovmm start
