Questions in synchronize records when activate oracle database

[yylqinghao]

I have two Oracle database in my cluster, with config meta-data-cache="eager" or meta-data-cache="shared-eager", both database's admin user is "system", and application user is "dbuser".
The startup steps I suppose are: in DatabaseClusterImpl.start(), flush the metadata. if flush is success, then the metadata will saved in EagerDatabaseMetaDataCache's map when meta-data-cache="eager" or saved in SharedEagerDatabaseMetaDataCache's properties when meta-data-cache="shared-eager". Note that in flush process, the used user is admin user, i.e "system", so I think the saved metadata is only with schema "system", exclude application user "dbuser", is it right?

If so, then in the activate steps: create a new object SynchronizationContextImpl, its "sourceDatabaseProperties" is obtained from the EagerDatabaseMetaDataCache or SharedEagerDatabaseMetaDataCache's cache which didn't contain "dbuser"'s metadata. But in PerTableSynchronizationStrategy.synchronize, the compare source tables are from "sourceDatabaseProperties", so, the real tables under schema "dbuser" won't be synchronized!

And there is another problem: even the saved metadata contains tables under schema "dbuser", but the one who use HA-JDBC may do not want to see that the tables under schema "system" be synchronized, because the tables under schema "system" are important system tables, not user tables.
fo

More question: if databases in cluster use different application user, which means different schema with Oracle. but in DifferentialSynchronizationStrategy.synchronize, the table name is source table's DML name, which use schema as its prefix, and the selectSQL is maked up by that too, so, when excute the selectSQL in the target database, may cause an error: table or view doesnot exist.

All above are raised by analyse, because I got an exception in synchronization: when get table system.DEF$_AQCALL's metadata, the Oracle JDBC raise exception:ORA-38029: object statistics are locked, and I didn't get the cause. so when meta-data-cache="eager", the synchronize was breaked off. The exception was raised in my two different clusters, and all databases are newly installed, then I thought it's not oracle's fault, but HA-JDBC's.

I hope I am wrong.

[Paul Ferraro]

OK - this is an issue with the OracleDialect.schemaPattern(DatabaseMetaData) method. Currently, this returns the user name of the connection. This is why the dbuser schema is not getting sync'ed.
Is there any reason not to configure ha-jdbc to use "dbuser" intead of "system"? The dbuser should have full access to all objects in the dbuser schema.
Otherwise, I'm not sure of the best way forward. The problem is that Oracle handles schemas differently than every other DBMS. If you really need to user a different user for HA-JDBC, then try using a custom dialect:

public class DBUserOracleDialectFactory implements DialectFactory
{
    @Override
    public String getId()
    {
        return "dbuser-oracle";
    }

    @Override
    public Dialect createDialect()
    {
        return new OracleDialect()
        {
            @Override
            protected String schemaPattern(DatabaseMetaData metaData) throws SQLException
            {
                return "dbuser";
            }
        };
    }
}

Then configure your cluster using dialect="dbuser-oracle".

[Justin Cranford]

Hi Paul. I had similar challenges with Oracle too. Oracle DB is expensive to deploy, so organizations host and manage it in a DBA group, and other groups connect to it using restricted credentials. In my particular application, I maintain two credentials to access the database: a DDL user and a DML user.

For my integration with HA-JDBC 3.0.x, I programmatically test if the DDL and DML credentials match. If so, then I enable the synchronization methods in HA-JDBC. If not, I have to disable them.

This my not help workaround the issue in this thread, but it should shed some light on the challenges the users of HA-JDBC face when using it in restricted privilege environments that are commom with Oracle, SQL Server, and DB2, and not common for MySQL or PostgreSQL deployments.

Actually, for some DDL and DML cases, I am able to squeak past the privilege restrictions by using "full" synchronization strategy, because only ALTER TABLE is required to drop/add FOREIGN KEYS. This is sometime's acceptable to the end user, to elevate the DML user to having ALTER TABLE permissions, but no CREATE/DROP TABLE/DATABASE permissions. For "dump-restore", all of those privileges are required, so "full" is sometimes a possible workaround.

[yylqinghao]

Thank you very much for your response.

I can configure ha-jdbc to use "dbuser" intead of "system", but I configured that according to your document, and I don't know what exact privileges do administrator user need, so I give the "system", maybe a bit unsafe, but I have no further infomation, can you give me that?

Then, for this issue, do we have a better way forward? Justin Cranford's suggestion may shed some light. Actually, My opinion is: Can HA-JDBC use "dbuser" when get DatabaseMetaDatas, not "system", And use "system" when executing synchronizition with Oracle? I don't have a lot about other databases, but I think that will be OK too, because all HA-JDBC's need from administrator user in synchronizition process is the execute privilege, not the datas, so we retrive datas via "dbuser", and execute via "system".

In my first questions, there is another issue, I will post it in a new topic.

[Paul Ferraro]

The permissions required by the user defined in your ha-jdbc configuration is specific to the synchronization strategies used. Both diff and full strategies require drop/create sequence and drop/create constraint privileges. Full strategy requires truncate privileges. Dump/restore is not yet implemented for Oracle, but if it were, more extensive privileges would be required.
Ideally each synchronization strategy would publish the permissions it requires so that it could validate that a given strategy is usable upon startup - but the JDBC API is a little weak in this area.

The best fix for the whole Oracle schema business, I think, is to make the schema name configurable somehow. Ideally, the configured value would be accessible from the DatabaseMetaData, but worst case scenario, I think, is that we resort to a system property.
e.g.

@Override
protected String schemaPattern(DatabaseMetaData metaData) throws SQLException
{
    return System.getProperty("ha-jdbc.oracle.schema", metaData.getUserName());
}

I'll probably need to change the method signature so that the schema can be specified per cluster, but you get the idea. WDYT?

[Paul Ferraro]

Actually, I don't think it was a good idea to force the user name as the schema name. I changed this in response to:
https://sourceforge.net/p/ha-jdbc/discussion/383397/thread/06e91de0/

Instead, I'd like to be able to filter out system schemas/tables via meta data if possible. Can someone with ready access to Oracle suggest how we might do this via DatabaseMetaData? For example, when calling DatabaseMetaData.getTables(...) one of the columns that is returned is TABLE_TYPE, which might potentially indicate which tables should be returned (e.g. user tables) and which should be skipped (e.g. internal/system tables). Alternatively, I can have the OracleDialect skip specific named schemas if these are known to be internal schemas.

[yylqinghao]

For Oracle, the schema name is force equal to the username, and can't change for existing versions.

In the discussion you mentioned, I can't to get the point, is that in version 3.0.0, the schema was't passed to metaData.getTables()?

But I know the reason why lazy metadata cache work fine but eager metadata cache throw the exception, that's because with eager metadata cache, it flushes the metadata on startup, use user "system", it throw excepion, but was ingored and metadata didn't cached. Then when execute sql, the isSelectForUpdate() method try to get the metadata cache, but not found, then to fetch the metadata again, but the used user is "dbuser" now! Laze metadata is the same.

Why don't you flush metadata on startup use user "dbuser"? As I said above: retrive datas via "dbuser", and execute via "system".

[Paul Ferraro]

I can't flush metadata on startup using "dbuser" because HA-JDBC knows nothing about this user or its credentials until your application tries to establish a connection.

As I said previously, why don't you just configure your ha-jdbc.xml to use "dbuser"? That way connections made internally by HA-JDBC and connections made by your application will always use the same schema.

[Paul Ferraro]

In 3.0.0, the calls to DatabaseMetaData.getTables(...) returned tables for all schemas. This is the desirable behavior for most other dialects. Oracle now overrides this logic to use the user name of the connection associated with the DatbaseMetaData.

[yylqinghao]

What metaData.getUserName() returns depend on the user used in driver.connect(location, user, password).

[yylqinghao]

Oh, I thought that the flushMetaDataCache's caller DatabaseClusterImpl.start() is only triggerd by DriverManager.getConnection() or net.sf.hajdbc.sql.DataSource.getConnection, both are used by application to establish a connection. Is there any other triggers?

But in your design, the administrator user is used to do some management, So I insist that my opinion: retrive all datas(include metadata) via "dbuser", and execute via "system" exactly conform to the design, for all types of database.

Use "dbuser" as administrator is only a provisional measure. And just as Justin Cranford said, if the actual producation enviroment using restricted credentials, the "dbuser" can't have enough priveleges, then game over.

Is there any other way to solve it? The idea you mentioned above: filter out system/internal tables may be effiective, but must more than that: you can only keep the datas that owned by "dbuser", can't keep other user's. The Oracle GoldenGate can detail to table level, HA-JDBC can't run up to that the moment, but at least, we must archive the schema level.

Then, first, we must can retrive all user's metadata on startup's flushMetaDataCache(Only when we can't get "dbuser" at this moment), then when we get "dbuser", we filter out the datas belong's to "dbuser", and drop others. And in first step, there may raise some exceptions like I posted in the first floor, that normal, you must skip it and continue.

The second step is easy, but I am not sure whether the first step can be archived or not. I think at least the administrator user must have the privelege to query all the user names in the database.

Or, simply, you can make it a constraint with Oracle to use the same administrator user and application user, if there aren't any good way to solve it.