Baan IVc4, Oracle 7.3.4.4, Solaris 2.6

Currently, we have our tools company 000, residing in the same database as the data companies. I would like to separate the tools company data into its own database. What is going to be the easiest way to achieve this? My guess is the following:

1. create a new db instance for the tools company to reside in
2. export company 000 via oracle's export tool
3. import company 000 into the new database
3. create a new database definition in baan
4. point baan via session ttaad4111m000, assign tables to database to the new database definition.

Specifically, what entries need to be made in the tables/module portion of ttaad4111m000? That session looks like you have to assign each table to a database on a table by table basis. Is there a way to assign all tables in company 000 to a database?

I appreciate the help!

You have to create the users as well in the db, especially the user baan. Furthermore you have to add a role r_baan, add dba rights and assign the role to the user baan.
Also the normal rights like connect etc. should be given to the user baan.

Don't forget that if you are going to do this that you will have to create your users from now on in two instances!

You can assign all tables from one company to a specific database definition. That will work.


Regards,
Han

Han,

I don't think you need to assign DBA to 'r_baan'. Seems awfully dangerous to me. I think the grants can be migrated alright when exporting company as long as you use the correct export parameters for Oracle's EXP utility. And yes, user management will be a bit of a nightmare though.

What I was trying to accomplish is faster refreshing of development data. The data that I have in the development server is serveral months old. At my disposal, I have an EMC Symmetrix to take a copy of production and import it into development. I would just overlay the existing database, but all the code that is being developed has table pointers located in company 000. So I wanted to split out company 000, so I could just overlay the data every week/month, etc.

How do other sites refresh the data for their development servers?

Thanks!

Pat,

I agree that assigning DBA to r_baan is dangerous, but that the way baan installs it standard!

I haven't tried it myself but what are the consequences if you remove it. If you recreate tables you should do it then at least if you'r logged on as the owner of the tables.

Rgrds,
Han

Han,

I think maybe we are talking different things here? I am talking about the Oracle role 'DBA' as it normally standard owned by users sys and system. If I check on a typical Baan server:

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
OPS$ORACLE DBA NO YES
OPS$ROOT DBA NO YES
SYS DBA YES YES
SYSTEM DBA YES YES
TEMPDBA DBA NO YES

5 rows selected.
No 'DBA' is granted to 'R_BAAN'. What makes up the 'R_BAAN' role is a select, insert, update and delete on each baan table individually granted to R_BAAN by user 'baan'. These can be queried in dba_tab_privs:
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
---------- ---------- ------------------------------ ---------- --------------- ---
R_BAAN BAAN TTTAAD000000 BAAN DELETE NO
R_BAAN BAAN TTTAAD000000 BAAN INSERT NO
R_BAAN BAAN TTTAAD000000 BAAN SELECT NO
R_BAAN BAAN TTTAAD000000 BAAN UPDATE NO
R_BAAN BAAN TTTAAD050000 BAAN DELETE NO
R_BAAN BAAN TTTAAD050000 BAAN INSERT NO
R_BAAN BAAN TTTAAD050000 BAAN SELECT NO
R_BAAN BAAN TTTAAD050000 BAAN UPDATE NO
R_BAAN BAAN TTTAAD100000 BAAN DELETE NO
R_BAAN BAAN TTTAAD100000 BAAN INSERT NO
R_BAAN BAAN TTTAAD100000 BAAN SELECT NO
etc.

Table drop and creations are always done by user 'baan' inside the database as user 'baan' is the only one who has 'resource' assigned to it:
SQL> select * from dba_role_privs where grantee = 'BAAN';

GRANTEE GRANTED_ROLE ADM DEF
---------- ------------------------------ --- ---
BAAN CONNECT NO YES
BAAN RESOURCE NO YES
BAAN R_BAAN YES YES
For this reason we need to have all users connected to the 'baan' group in the $BSE/lib/ora/ora_groups file! Correct me if I am wrong :)

Pat,

Is seems that my configuration is slightly different, dba is assigned to r_baan on my system. However I have to tell you that this configuration is pretty old (more than 2 years) could be that it's no longer needed or added by someone.

I have to pick a time to perform some test to see what happens if I remove it.

Thanks for the hint.

Han

I think the most commonly used method is probably the 1-to-1 company company within Baan itself. If your data volume is not too big and number of companies not too high, it is a easy and simple way.