i've to move more than one instance to a new host. The export/import utilities from Oracle are a nice help for this. But not complete as the most DBA's found out ;).
My main work at the moment is to lookup the privileges of the schema users that I can write some scripts. After that I will/has executed the scripts so that imp will not stop because of missing schema-users. The databases were developed in-house some years ago and nobody ever documented something. Nice piece of work :(
My question is: Can DDL::Oracle can do this for me: Extract the schema users with all privileges (and all roles which are user-defined as an dependency) to an SQL/shell/etc script and then create this users in the new database? I don`t have the same datafiles for the tablespaces (we changed the concept over the years) as in the old database. And that was the only description in the docu I found for moving an instance to a new host.
If it can do that i would had finished some days ago. If not i would like have it. Or take some time and perl tutorials and write it myself and contribute it to your project.
When I've time i will test DDL::Oracle. Seems to be very nice/interesting.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, DDL::Oracle can do this for you. It's CREATE USER ddl includes all privileges: roles, system privs, and table privs. You probably also want to use the CREATE ROLE feature, so that the roles exist before you run the users. If you use the supplied "query.pl" script, you can write simple queries which will select all roles, then users. In both of these cases, there is no "owner", so the query would look like:
SELECT
null
, username
FROM
dba_users
and
SELECT
null
, role
FROM
dba_roles
Hope this helps.
Richard
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'd suppose the easiest way to copy an instance is to copy the database files, the control files, the redo logs and the init.ora file to the target machine. If things are different on the target machine (different file structure, different database name), you'll have to create a new set of control files and a new init.ora. Also you need to be able (allowed) to shut the source database down.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
i've to move more than one instance to a new host. The export/import utilities from Oracle are a nice help for this. But not complete as the most DBA's found out ;).
My main work at the moment is to lookup the privileges of the schema users that I can write some scripts. After that I will/has executed the scripts so that imp will not stop because of missing schema-users. The databases were developed in-house some years ago and nobody ever documented something. Nice piece of work :(
My question is: Can DDL::Oracle can do this for me: Extract the schema users with all privileges (and all roles which are user-defined as an dependency) to an SQL/shell/etc script and then create this users in the new database? I don`t have the same datafiles for the tablespaces (we changed the concept over the years) as in the old database. And that was the only description in the docu I found for moving an instance to a new host.
If it can do that i would had finished some days ago. If not i would like have it. Or take some time and perl tutorials and write it myself and contribute it to your project.
When I've time i will test DDL::Oracle. Seems to be very nice/interesting.
Yes, DDL::Oracle can do this for you. It's CREATE USER ddl includes all privileges: roles, system privs, and table privs. You probably also want to use the CREATE ROLE feature, so that the roles exist before you run the users. If you use the supplied "query.pl" script, you can write simple queries which will select all roles, then users. In both of these cases, there is no "owner", so the query would look like:
SELECT
null
, username
FROM
dba_users
and
SELECT
null
, role
FROM
dba_roles
Hope this helps.
Richard
I'd suppose the easiest way to copy an instance is to copy the database files, the control files, the redo logs and the init.ora file to the target machine. If things are different on the target machine (different file structure, different database name), you'll have to create a new set of control files and a new init.ora. Also you need to be able (allowed) to shut the source database down.