EnterpriseDB: The Enterprise Postgres Company Postgres Plus Forums: The PostgreSQL Open Source Database from EnterpriseDB
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing   [Groups] Back to home page 

Error migrating from Oracle 10g to PPAS 9.1  XML

Forum Index » Postgres Plus Advanced Server
Author Message
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

I have tried to migrate a database schema from Oracle 10g to PPAS 9.1, from Migration Studio 9.0. Tables, triggers, views, procedures and functions are OK, but I got the below error:


...
Getting Roles Definitions...
Error: The user/role migration failed due to insufficient privileges.
Grant the user SELECT privilege on the following Oracle catalogs:
DBA_ROLES
DBA_USERS
DBA_TAB_PRIVS
DBA_PROFILES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_SYS_PRIVS
Stack Trace:
java.sql.SQLException: The user/role migration failed due to insufficient privileges.
Grant the user SELECT privilege on the following Oracle catalogs:
DBA_ROLES
DBA_USERS
DBA_TAB_PRIVS
DBA_PROFILES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_SYS_PRIVS
at com.edb.MigrationToolkit.migrateUsers(MigrationToolkit.java:2661)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1477)
at com.enterprisedb.migration.MigrationDialog$MigrationThread.run(MigrationDialog.java:1619)

One or more schema objects could not be imported during the migration process. Please review the migration output for more details.
...



Any idea about this error? Why did I get this error? Is very severe? What kind of things won't work?

Thanks in advance,
Marcos.
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Any idea about this problem?

We are doing a demo of migration to a customer. It's very important for us solve this problem and answer to the customer.
raghu_r

Senior member

Joined: 28 Jan 2009 23:23:25
Messages: 184
Location: Andhra Pradesh,India
Offline

MigrationTool Kit requires a SELECT privileges on specific Oracle System catalogs as a part of Migration Process.
You are receiving these errors due to insufficient privileges,hence user roles/privileges are not migrated from Oracle to PPAS.

--Raghu

Thanks & Regards,

Raghu Ram
skypeID: raghu.ramedb

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/
[Email] [WWW]
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Hi raghu_r.

What kind of privileges should this user have to be able to SELECT on system catalogs? In other words, what GRANT statement should I execute to grant privileges of SELECT in system catalogs to this user?

I tried to do the migration with SYS user but I got an error about privileges.

Thanks,
Marcos
raghu_r

Senior member

Joined: 28 Jan 2009 23:23:25
Messages: 184
Location: Andhra Pradesh,India
Offline

Please share steps you have followed for migration from Oracle to PPAS?

--Raghu

Thanks & Regards,

Raghu Ram
skypeID: raghu.ramedb

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/
[Email] [WWW]
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Hi raghu_r. I'm sorry but I couldn't answer you yesterday.

I´ve followed steps in this tutorial http://www.enterprisedb.com/docs/en/9.1/migrate/Table%2520of%2520Contents.htm. This steps are:

1) Configuring the file toolkit.properties with appropriate data (Oracle and PPAS users aren't superuser)
2) Running runMTK.bat schema_name (user's schema configured in toolkit.properties)

Thanks,
Marcos.
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

If I try to connect to Oracle database with sys user for migrating all schemas (or migrating correctly some schemas) I get the following error:

Connecting with source Oracle database server...
Error: java.lang.Exception: ORA-01017: invalid username/password; logon denied

Stack Trace:
java.sql.SQLException: java.lang.Exception: ORA-01017: invalid username/password; logon denied

at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:2607)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1286)


Thanks,
Marcos.
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Please, somebody knows how can I solve this problem? and how can I migrate a whole database instance from Oracle to PPAS (migration's user, user's privileges,...)?

This is critical for my client. We're accumulating delays due to this problem.

Thanks,
Marcos.
raghu_r

Senior member

Joined: 28 Jan 2009 23:23:25
Messages: 184
Location: Andhra Pradesh,India
Offline

I have performed test migration with Oracle 11g database dump. Could you please try with below steps and let me know:

1. Create User on Oracle Database

SQL> create user thdb identified by thdb;

2. Provide access to read all Catalog tables

SQL> grant dba,connect,resource to thdb;

3. Restored Oracle dump into "thdb" user.

4. Started the listener in Oracle Database.

5. Create toolkit.properties file under /opt/PostgresPlus/9.1AS/etc

-bash-4.1$ more toolkit.properties
SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl
SRC_DB_USER=thdb
SRC_DB_PASSWORD=thdb

TARGET_DB_URL=jdbc:edb://localhost:5444/edb
TARGET_DB_USER=enterprisedb
TARGET_DB_PASSWORD=adminedb

6. Executed runMTK.sh to migrate from Oracle to PPAS.

/opt/PostgresPlus/9.1AS/bin/runMTK.sh thdb


Thanks & Regards,

Raghu Ram
skypeID: raghu.ramedb

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/
[Email] [WWW]
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Hi raghu_r.

With your steps, I did the following:

1) I've a user (user1) with all tables, procedures, package,... I'd like to migrate. Really, I'd like to migrate the whole database.
2) I've granted this privileges: dba,connect,resource

grant select on dba,connect,resource to user1;

3) Oracle listener is up
4) I've updated toolkit.properties, it looks like this:

SRC_DB_URL=jdbc:oracle:thin:@172.16.16.76:1521:orcl
SRC_DB_USER=user1
SRC_DB_PASSWORD=password_user1
TARGET_DB_URL=jdbc:edb://172.16.16.74:5444/bbdd_migration
TARGET_DB_USER=migrator
TARGET_DB_PASSWORD=password_migrator


5) I've executed runMTK.sh and I've got this log file (error with package is because I've imported manually that package because otherwise the migration of procedures fail because they're imported before than package).


Thanks,
Marcos
 Filename mtk_20120417074410.log [Disk] Download
 Description Migration log file from Oracle 10g to PPAS 9.1
 Filesize 39 Kbytes
 Downloaded:  328 time(s)

marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

PD.: From TOAD, if I connet to user1 like Normal user I can't select from dba_roles, dba_role_privs,... but if I connect to user1 like SYSDBA I can select from dba_roles, dba_role_privs,... Is there any option in MTK to say how Oracle user connects to Database?
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

This is another log file following the same steps but using PPAS superuser. I got the same error:

...
Getting Roles Definitions...
Error: The user/role migration failed due to insufficient privileges.
Grant the user SELECT privilege on the following Oracle catalogs:
DBA_ROLES
DBA_USERS
DBA_TAB_PRIVS
DBA_PROFILES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_SYS_PRIVS
Stack Trace:
java.sql.SQLException: The user/role migration failed due to insufficient privileges.
Grant the user SELECT privilege on the following Oracle catalogs:
DBA_ROLES
DBA_USERS
DBA_TAB_PRIVS
DBA_PROFILES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_SYS_PRIVS
at com.edb.MigrationToolkit.migrateUsers(MigrationToolkit.java:2732)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1527)

One or more schema objects could not be imported during the migration process. Please review the migration output for more details.
...


Thanks,
Marcos.
 Filename mtk_20120417085451.log [Disk] Download
 Description Oracle migration log file
 Filesize 40 Kbytes
 Downloaded:  268 time(s)

raghu_r

Senior member

Joined: 28 Jan 2009 23:23:25
Messages: 184
Location: Andhra Pradesh,India
Offline

Could you please share your gtalk or skypeID to fix this issue and my skypeID is raghu.ramedb.

Thanks & Regards,

Raghu Ram
skypeID: raghu.ramedb

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/
[Email] [WWW]
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Hi raghu_r.

I've sent you a invitation to skype. When you accept, we can text or talk about this problem. My english isn't good but I'll try to understand.

Thanks,
Marcos
raghu_r

Senior member

Joined: 28 Jan 2009 23:23:25
Messages: 184
Location: Andhra Pradesh,India
Offline

Hola Marcos, Accepted the invitation on skype. Once you are online,will discuss about the issue.

Thanks & Regards,

Raghu Ram
skypeID: raghu.ramedb

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/
[Email] [WWW]
marcos.rodriguez

Member

Joined: 23 Mar 2012 08:56:37
Messages: 70
Offline

Hi.

raghu_r has solved this problem for me. Thanks so much.

Thanks again,
Marcos
 
Forum Index » Postgres Plus Advanced Server
Go to:   
Powered by JForum 2.1.8 © JForum Team