moving all of the users from one db instance to another 2

[ddiamond]

Is it possible to export all of the users from one database instance and then import them into another database instance while preserving their passwords?

 

[LKBrwnDBA]

Yes, not perfect, but for users do this:

col db new_value sid noprint
accept fr_usr prompt "        From user: "
accept to_usr prompt "To user (''=same): "
col fr_usr    new_value fr_usr noprint
col to_usr    new_value to_usr noprint
col cr_type   new_value cr_typ noprint
select UPPER('&&fr_usr')    fr_usr
     , UPPER(NVL('&&to_usr','&&fr_usr')) to_usr
     , name db from v$database;
select decode('&&to_usr','&&fr_usr'
     ,'Migrate_&&fr_usr','Create_&&to_usr') cr_type from dual;
set pages 0 lin 80 feed off ver off recsep off trims on
set term on
col sq noprint
spo /tmp/&&sid._&&cr_typ..sql
Select username||to_char(100+rownum) sq,
 'Create User '||decode('&&to_usr','&&fr_usr'
,u.username||' identified by values '||chr(39)||password||chr(39)
,'&&to_usr identified by &&to_usr'||to_char(sysdate,'DD'))||chr(10)
||'    Default tablespace '||default_tablespace||chr(10)
||'    Temporary tablespace '||temporary_tablespace||';'||chr(10)
  from dba_users u
 where username = '&&fr_usr'
UNION
Select username||to_char(200+rownum) sq, 'Alter User &&to_usr'
||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes/1024||'K')
||' ON '||tablespace_name||';'||chr(10)
  from dba_ts_quotas q
 where username = '&&fr_usr'
order by 1
/
spo off
prompt Script is at: /tmp/&&sid._&&cr_typ..sql

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

I gave it a try and got the following errors:

SQL> ,'Migrate_&&fr_usr','Create_&&to_usr') cr_type from dual;
SP2-0734: unknown command beginning ",'Migrate_..." - rest of line ignored.
SQL> set pages 0 lin 80 feed off ver off recsep off trims on
SQL> set term on
SQL> col sq noprint
SQL> spo /tmp/&&sid._&&cr_typ..sql
Enter value for sid: Select username||to_char(100+rownum) sq,

Enter value for cr_typ:  'Create User '||decode('&&to_usr','&&fr_usr'
SP2-0333: Illegal spool file name: "/tmp/Select username||to_char(100+rownum) sq,_ 'Create User '||d
SQL> ,u.username||' identified by values '||chr(39)||password||chr(39)
SP2-0734: unknown command beginning ",u.usernam..." - rest of line ignored.
SQL> ,'&&to_usr identified by &&to_usr'||to_char(sysdate,'DD'))||chr(10)
SP2-0734: unknown command beginning ",'&&to_usr..." - rest of line ignored.
SQL> ||'    Default tablespace '||default_tablespace||chr(10)
SP2-0734: unknown command beginning "||'          Def..." - rest of line ignored.
SQL> ||'    Temporary tablespace '||temporary_tablespace||';'||chr(10)
SP2-0734: unknown command beginning "||'          Tem..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   from dba_users u
SP2-0734: unknown command beginning "from dba_u..." - rest of line ignored.
SQL>  where username = '&&fr_usr'
SP2-0734: unknown command beginning "where user..." - rest of line ignored.
SQL> UNION
SP2-0042: unknown command "UNION" - rest of line ignored.
SQL> Select username||to_char(200+rownum) sq, 'Alter User &&to_usr'
  2  ||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes/1024||'K')
  3  ||' ON '||tablespace_name||';'||chr(10)
  4    from dba_ts_quotas q
  5   where username = '&&fr_usr'
  6  order by 1
  7  /
Select username||to_char(200+rownum) sq, 'Alter User select decode('&&to_usr','&&fr_usr''
                                                                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> spo off
not spooling currently
SQL> prompt Script is at: /tmp/&&sid._&&cr_typ..sql
Script is at: /tmp/Select username||to_char(100+rownum) sq,_ 'Create User '||decode('&&to_usr','&&fr
SQL>

 

[SantaMufasa]

DDiamond,

I have a few questions that require clarifying answers:[ul][li]Is your objective (at this point) simply to setup identical application users (versus Oracle-created users such as 'SYS','SYSTEM','OUTLN','CTXSYS','DBSNMP','MDSYS', et cetera) on two separate databases (sans data)?[li][/li]Do you have identical tablespaces to the source database already created on the target database?[/li][li]Do you need to also duplicate all system privileges and object privileges?[/li][/ul]If the answers to the first two questions are "yes", and if we ignore the last question, then this script (similar to LK's worthwhile script, but without prompts), which I named "users_clone.sql", should approximate what you need:

set pagesize 0
set linesize 200
set trimspool on
spool temp_clone_users.sql
select 'set feedback on'||chr(10)||'set echo on'||chr(10)||
       'spool temp_clone_users.log'
from dual;
select 'REM ==== Creating User "'||username||'" ============================'||chr(10)||
'create user '||username||' identified by values '''||password||''''||chr(10)||
'default tablespace '||default_tablespace||chr(10)||
'temporary tablespace '||temporary_tablespace||chr(10)||
'quota unlimited on '||default_tablespace||chr(10)||
'/'||chr(10)||
'grant connect, resource to '||username||chr(10)||
'/'||chr(10)||
'grant create view to '||username||chr(10)||
'/'
from dba_users
where username not in ('SYS','SYSTEM','APEX_PUBLIC_USER','BI','CTXSYS','DBSNMP','EXFSYS','IX','MDDATA','MDSYS'
                      ,'OE','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','PM','SCOTT'
                      ,'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYSMAN'
                      ,'TMSYS','WMSYS','XDB')
order by username
/
select 'spool off'||chr(10)||'prompt wrote logfile: "temp_clone_users.log"' from dual;
prompt
prompt Wrote script 'temp_clone_users.sql'
prompt

Here are the invocation and results of running "users_clone.sql":

SQL> @users_clone
set feedback on
set echo on
spool temp_clone_users.log

REM ==== Creating User "YADA4" ============================
create user YADA4 identified by values '12F8EACC8D03829E'
default tablespace DATA1
temporary tablespace TEMP
quota unlimited on DATA1
/
grant connect, resource to YADA4
/
grant create view to YADA4
/

REM ==== Creating User "YADA5" ============================
create user YADA5 identified by values 'C54F3C5E3601C239'
default tablespace DATA1
temporary tablespace TEMP
quota unlimited on DATA1
/
grant connect, resource to YADA5
/
grant create view to YADA5
/

spool off
prompt wrote logfile: "temp_clone_users.log"


Wrote script 'temp_clone_users.sql'

Notice that invocation of "users_clone.sql" creates a script entitled, "temp_clone_users.sql". If you run "temp_clone_users.sql" from your intended target Oracle instance, then here are the results:

SQL> @temp_clone_users
SQL> spool temp_clone_users.log
SQL> 
SQL> REM ==== Creating User "YADA4" ============================
SQL> create user YADA4 identified by values '12F8EACC8D03829E'
  2  default tablespace DATA1
  3  temporary tablespace TEMP
  4  quota unlimited on DATA1
  5  /

User created.

SQL> grant connect, resource to YADA4
  2  /

Grant succeeded.

SQL> grant create view to YADA4
  2  /

Grant succeeded.

SQL> 
SQL> REM ==== Creating User "YADA5" ============================
SQL> create user YADA5 identified by values 'C54F3C5E3601C239'
  2  default tablespace DATA1
  3  temporary tablespace TEMP
  4  quota unlimited on DATA1
  5  /

User created.

SQL> grant connect, resource to YADA5
  2  /

Grant succeeded.

SQL> grant create view to YADA5
  2  /

Grant succeeded.

SQL> 
SQL> spool off
SQL> prompt wrote logfile: "temp_clone_users.log"
wrote logfile: "temp_clone_users.log"
SQL>

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

The script I provided was to migrate one (1) user.

Now, Dave of Sandy provided the sdcript to migrate all users.

Good luck!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

Thanks Santa,

That takes care of the 1st 2 objectives that you listed. I also need to duplicate the user's system and object privileges.

- Dan

 

[LKBrwnDBA]

Ok, try this script for the grants:

------------------------------------------------------------------
def ORATMP=/tmp
cl breaks col
col db new_value sid
select name db from v$database;
set echo off head off pages 0 lin 120 timing off feed off
set termout off verify off trims on recsep off
spool &&ORATMP/&sid._migrate_grants.sql
set termout off
col sq noprint
select 'Grant '||granted_role||' to '||grantee
||decode(admin_option,'YES',' With ADMIN OPTION;',';')||chr(10)
from (
select username
  from dba_users u
 where lock_date is null
     ) x, dba_role_privs r
 where r.grantee = x.username
   and r.grantee not in
	     ('ANONYMOUS','CTXSYS','DBSNMP','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS'
       ,'SYS','SYSTEM','WKSYS','WKUSER01','WMSYS')
 order by 1
/
select 'Grant '||privilege||' to '||grantee
||decode(admin_option,'YES',' With ADMIN OPTION;',';')||chr(10)
from (
select username
  from dba_users u
 where lock_date is null
     ) x, dba_sys_privs s
 where s.grantee = x.username
   and s.grantee not in
	     ('ANONYMOUS','CTXSYS','DBSNMP','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS'
       ,'SYS','SYSTEM','WKSYS','WKUSER01','WMSYS')
 order by 1
/
spool off
exit

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Above would migrate the system grants, this one migrated the "user" (object) grants. You need also to have present the famous db_su.sql script:

-- --------------------------------------
-- db_su.sql : Become another user.
--
set term off
store set sqlplus_env rep
set pages 0 feed off ver off echo off term off
DEF user=&1
spo temp&1..sql
conn / 
select 'alter user &user identified by values '''||password||''';'
  from dba_users where username = UPPER('&user')
/
spo off
alter user &user identified by temp;
connect &user/temp
START temp&1..sql
@sqlplus_env
set term on

Which is used by this script:

-- Set the following to the location (path) of db_su.sql:
def ORASQL=~/dba/scripts/sql
def ORATMP=/tmp
cl breaks col
col db new_value sid
select name db from v$database;
set echo off head off pages 0 lin 120 timing off feed off
set termout off verify off trims on recsep off
col sq noprint
spool &&ORATMP/&sid._migrate_user_grants.sql
select x.rn*10000+p.rn sq,
||'@&&ORASQL/db_su '||x.schema||chr(10),NULL)
decode(p.rn,1,'@&&ORASQL/db_su '||x.schema||chr(10),NULL)
||'Grant '||privilege||' on '||grantor||'.'||table_name
||' to '||grantee||';'||chr(10) sql
from (
select username schema, row_number() over(order by username) rn
 from ( select username from dba_users u
         where --lock_date is null and
      				 u.username not in
	            ('ANONYMOUS','CTXSYS','DBSNMP','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS'
              ,'SYS','SYSTEM','WKSYS','WKUSER01','WMSYS')
UNION select role from dba_roles )
) x
,(
  select grantor, grantee, privilege, table_name
       , row_number() over(partition by grantor order by table_name) rn
    from dba_tab_privs ) p
where p.grantor=x.schema
order by p.grantor, p.table_name
/
spo off

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Here, then, is a script (similar in structure to "users_clone.sql", above), which I named "users_privs_clone.sql":

set pagesize 0
set linesize 200
set trimspool on
set echo off
set feedback off
spool temp_clone_users_privs.sql
select 'set feedback on'||chr(10)||'set echo on'||chr(10)||
       'spool temp_clone_users.log'
from dual;
col grantee noprint
break on grantee skip 1
select 'grant '||privilege||' to '||grantee||';'
      ,grantee
from (select grantee, privilege from dba_sys_privs
      union
      select grantee, granted_role from dba_role_privs)
    ,dba_users
where username not in ('SYS','SYSTEM','APEX_PUBLIC_USER','BI','CTXSYS','DBSNMP','EXFSYS','IX','MDDATA','MDSYS'
                      ,'OE','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','PM','SCOTT'
                      ,'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYSMAN'
                      ,'TMSYS','WMSYS','XDB')
  and username = grantee
order by username
/
set termout off
select 'spool off'||chr(10)||'prompt wrote logfile: "temp_clone_users_privs.log"' from dual;
spool off
set termout on
prompt
prompt Wrote script 'temp_clone_users_privs.sql'
prompt

It's invocation and results appear as:

SQL> @users_privs_clone
set feedback on
set echo on
spool temp_clone_users.log

grant CONNECT to YADA4;
grant CREATE VIEW to YADA4;
grant RESOURCE to YADA4;
grant UNLIMITED TABLESPACE to YADA4;

grant CONNECT to YADA5;
grant CREATE VIEW to YADA5;
grant RESOURCE to YADA5;
grant UNLIMITED TABLESPACE to YADA5;


Wrote script 'temp_clone_users_privs.sql'

Execute "temp_clone_users_privs" on the target instance to produce the system privileges and role memberships that exist for each user on the source instance. (Note: roles to which this script grants memberships, must exist on the target instance prior to running "temp_clone_users_privs.sql".)

It is useless to grant privileges on objects if you have just created empty users on the target database, since the objects do not yet exist to which privileges need granting. To achieve object grants, I recommend that you conduct Oracle schema-level exports ("exp") and imports ("imp"), ensuring that you set "GRANTS=Y". This process does the following:[ul][li]CREATES Objects[/li][li]Populates (INSERTs) data into objects[/li][li]GRANTs object privileges that exist on the source database[/li][/ul]

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Thanks Dave. That was exactly what I was looking for.

 

[ddiamond]

How do I capture the users security profile?

 

[ddiamond]

In security manager, the cloned users look identical, but when I try to login with one of them, I get the following error:

ERROR:
ORA-12560: TNSrotocol adapter error

 

[SantaMufasa]

That error, DDiamond, usually results from your not correctly specifying the $ORACLE_HOME variable for your session with which you are connecting to Oracle. (I cannot make specific code suggestions for confirming your $ORACLE_HOME variable until I know which operating system you are using for your client connection.)

Also, confirm that your Oracle target instance is running and the database is OPEN.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

My client is Windows XP professional. The server is Windows 2003 server. Oracle version 8.0.5.0.

I can connect to the oracle instance using accounts that I manual created using security manager, so that verifies that the instance is running. I only get the error with users created using the above script.

 

[SantaMufasa]

DDiamond,

On the client side, I recommend the following navigation on your XP to setup the proper system-variable value for ORACLE_HOME:

[Start] -> [Control Panel] -> [System] -> [Advanced] -> [Environment Variables] -> (system Variables frame) [New] -> (Variable Name) "ORACLE_HOME" -> (Variable Value) "<your specific Oracle Home, example: C:\Oracle\Ora8...whatever" -> [OK] -> [OK] -> [OK]

BTW, by what software and entries (exactly) are you attempting to connect from your remote client to the Oracle server?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

BTW, by what software and entries (exactly) are you attempting to connect from your remote client to the Oracle server?

SQL Plus