Thread: Issue with Rowid's during Oracle to Postgres convertion:

Hi Gurus,

We have an Oracle 8i standby database and the archive logs were applied everyhour to keep the database in sync with production. Since the standby

database is read only, we dont have the flexibility to change and add additional functionality to meet our needs for reporting and reserach. Therefore,

we decided to maintain another database (postgres) and add functionality, modify schema/indexes so that we have full control over the database.

We, then started with importing the Oracle data into postgres and there were no Issues. The second stage is to apply the Oracle archive log information

to Postgres database using Oracle's Logminer utility. We were able to break the archive logs and extract sql's using Logminer but stuck with ROWID's

in the DML transactions. Postgres does not support Rowid's at all, but there is a concept called OID and it is not helpful to us. I wonder how to resolve

the issue with Rowid's in Sql statements that came out of Oracle Logminer. All I wanted is to use the sql's ( comming out of Logminer ) and apply them

to Postgres database so that the database is always current and in sync with the Oracle standby database, and simultaniously it is available to users

without downtime. Since oracle Rowid is dynamic, it is difficult to keep track of it. I believe there is an Issue with Oracle 8i Logminer in extracting the

DML transactions. It looks like some of them have already started maintaining the postgres database using the above mentioned method, but dont know how.

Can someone please advice us in this matter so that we can continue with our database migration.

- Sravan.

"Bodanapu, Sravan" <Sravan.Bodanapu@NextelPartners.com> writes:

> We, then started with importing the Oracle data into postgres and there were
> no Issues. The second stage is to apply the Oracle archive log information
> to Postgres database using Oracle's Logminer utility. We were able to break
> the archive logs and extract sql's using Logminer but stuck with ROWID's
> in the DML transactions. Postgres does not support Rowid's at all, but there
> is a concept called OID and it is not helpful to us. I wonder how to resolve

This is an interesting approach. It seems like it will be a lot of work
though. There isn't any better way? Oracle can do cross-database queries, so
you could for example query the read-only database and insert the results into
a table in an entirely separate read-write database.

The best bet sounds to me like looking at your initial import process to see
if there's a way to add rowid to the initially exported data. If you can
arrange to have rowid exported and create a real varchar rowid column in
postgres then the logminer transactions might work. You'll have to make an
index on the rowid column though.

Perhaps you should send some examples of logminer transactions, just one
example of each of UPDATE, INSERT, DELETE ?

--
greg