What to do if you receieve an “ORA-00600: internal error code, arguments: [keltnfy-ldmInit]” when creating a new database

Posted by David Yahalom under Oracle

If you try and create a new Oracle database (either using DBCA or manually) and get the Oracle ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] error, this usually signifies that there was a problem resolving your server’s hostname.

Oracle needs to be able to resolve your server’s hostname from within the server on which you are installing Oracle. In case of the above error check your /etc/hosts file and verify that your server hostname is pingable.

How to resolve EXP-00056: ORACLE error 19206 encountered during an export

Posted by David Yahalom under Oracle

Sometimes you might receive the following error during an export of an entire schema in Oracle9i/10g:

EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 457
ORA-06512: at "SYS.DBMS_METADATA", line 1181
ORA-06512: at "SYS.DBMS_METADATA", line 1162
ORA-06512: at line 1
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 457
ORA-06512: at "SYS.DBMS_METADATA", line 1181
ORA-06512: at "SYS.DBMS_METADATA", line 1162
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

While an export of a single table might work, exporting an entire user (”schema“) will fail with the above error.

This problem is caused when the metadata tables needed to perform an export are missing or invalid and can sometimes occur if you have dropped or modified the XMLDB schema.

It is quite easily resolved, simply run the following script when connected as sysdba:

$ORACLE_HOME/rdbms/admin/catmeta.sql 

MySQL InnoDB statistics gathering (analyze table)

Posted by David Yahalom under MySQL

I’ve seen many MySQL DBAs completely unaware of the fact that like most modern RDBMS systems, MySQL also relies on using statistics for keeping track of data distribution in tables and for optimizing join statements.

The syntax for analyzing a table for statistics is very simple:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <em class="replaceable"><code>tbl_name</code></em> [, <em class="replaceable"><code>tbl_name</code></em>] ...

• You must hold insert and select privileges on the table you want to analyze.
• On MyISM tables this will result in a READ LOCK on the table.
• On InnoDB tables this will result in a WRITE LOCK on the table.

Analyzing data distribution in tables is relevant to both MyISM and InnoDB tables.

The equivalent to the above command is using:

myisamchk --analyze

…on MyISM tables and

mysqlcheck -Aa -uroot -p

or

mysqlcheck --analyze --all-databases<strong>

..on InnoDB tables.

One more thing, keep in mind that unlike myisamchk, mysqlcheck can run online while the database is open.