I prefer to use exp/imp to migrate databases.
I first create a fresh new database with a new spfile, a new system tablespace, a new undo, locally managed tablespace, automatic segment space management.
I do not do a full exp. I prefer a schema export. It only exports the schema that I want, not WMSYS or PERFSTAT… I do not want to have old stuff in my system tablespace neither.
What is missing by schema import ? profiles, roles, tablespaces, users, public synonym, public database link, privileges
1) generate create statement profiles, roles, tablespaces, users, public synonym, public database link, privileges
for example with toad or with dbms_metadata. I am using sql + spool. Also possible is PLSQL.
2) export database with OWNER=user1,user2,… so all your users but not SYS, SYSTEM, PERFSTAT, DBSNMP, WMSYS, TSMSYS. Only your own users, not the one created by oracle
3) create a new db
4) create profiles, roles, tablespaces, users on the new db
5) grant dba to public !!! yes. a bit creasy, but it is convenient to do the import without warning/errors.
6) import
7) create the public synonym, public database link, privileges
8) revoke dba from public (!)
9) recompile the db
Well, I have written all that in a script, so migrating a db is no longer a problem to me 🙂 I can do 7.3 –> 10.2 migration. And I am sure my db is clean. I have undo and temporary tablespace. I can have Java or ASM. I have only 10.2 system objects in my 10.2 database. Since I am using exp/imp, it is no problem to change os/server/domain/bitwordsize.
I did something similar a month ago. Yours has far more detail, thanks!
http://thinkoracle.blogspot.com/2005/08/import-export.html
Good job. So where is your script? 😉
well, my whole script is very specific to my environment here and quite hudge, but to generate the sql, I give you the sql files.
==> spool_privileges.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_privilege.sql’ char prompt ‘Sql file [/tmp/create_privilege.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_privilege.log’ char prompt ‘Log file [/tmp/create_privilege.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
select
text
from
(
— system privilege
select grantee, 1 num, ‘SYS’ owner, ‘PROMPT Grant System Privileges to ‘||grantee||’…’ text from dba_sys_privs
union
select grantee, 2, ‘SYS’, ‘grant ‘||privilege||’ to “‘||grantee||'”‘||
decode(ADMIN_OPTION,’YES’, ‘ WITH ADMIN OPTION’)||’;’
from dba_sys_privs
union
select grantee, 3 num, ‘SYS’ owner, chr(10)||chr(10) from dba_sys_privs
union
— role privilege
select grantee, 4, ‘SYS’, ‘PROMPT Grant Role Privileges to ‘||grantee||’…’ from dba_role_privs
union
select grantee, 5, ‘SYS’, ‘grant ‘||granted_role||’ to “‘||grantee||'”‘||
decode(ADMIN_OPTION,’YES’, ‘ WITH ADMIN OPTION’)||’;’
from dba_role_privs
union
select grantee, 6, ‘SYS’, chr(10)||chr(10) from dba_role_privs
union
— tablespace quota
select username, 7, ‘SYS’, ‘PROMPT Grant tablespace quotas to ‘||username||’…’ from dba_ts_quotas
union
select username, 8, ‘SYS’, ‘alter user “‘||username||'” quota ‘||
decode(MAX_BYTES,-1, ‘unlimited’, (MAX_BYTES/1024)||’K ‘)||
‘ on “‘||TABLESPACE_NAME||'”;’
from dba_ts_quotas
union
select username, 9, ‘SYS’, chr(10)||chr(10) from dba_ts_quotas
union
— object grants
select grantee, 10, owner, ‘PROMPT Grant ‘||owner||’ object privileges to ‘||grantee||’…’ from dba_tab_privs
union
select grantee, 11, owner, ‘grant ‘||PRIVILEGE||’ on “‘||owner||'”.”‘||TABLE_NAME||'” to “‘||grantee||'” ‘||
decode(GRANTABLE,’YES’, ‘WITH GRANT OPTION’)||’;’
from dba_tab_privs
union
select grantee, 13, owner, chr(10)||chr(10) from dba_tab_privs
union
select grantee, 10, owner, ‘PROMPT Grant ‘||owner||’ object privileges to ‘||grantee||’…’ from dba_col_privs
union
select grantee, 12, owner, ‘grant ‘||PRIVILEGE||’ (“‘||COLUMN_NAME||'”) on “‘||owner||'”.”‘||TABLE_NAME||'” to “‘||grantee||'” ‘||
decode(GRANTABLE,’YES’, ‘WITH GRANT OPTION’)||’;’
from dba_col_privs
union
select grantee, 13, owner, chr(10)||chr(10) from dba_col_privs
)
where grantee not in (‘ADAMS’,’ANONYMOUS’,’BLAKE’,’CLARK’,’CTXSYS’,’DBSNMP’,’DIP’,’DMSYS’,’EXFSYS’,’HR’,’JONES’,’MDDATA’,’MDSYS’,’MGMT_VIEW’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PERFSTAT’,’PM’,’QS’,’QS_ADM’,’QS_CB’,’QS_CBADM’,’QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’SCOTT’,’SH’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSTEM’,’TRACESVR’,’TSMSYS’, ‘WKPROXY’,’WKSYS’,’WK_TEST’,’WMSYS’,’XDB’
,
‘AQ_ADMINISTRATOR_ROLE’,’AQ_USER_ROLE’,’AUTHENTICATEDUSER’,’CONNECT’,’CTXAPP’,’DBA’,’DELETE_CATALOG_ROLE’,’EJBCLIENT’,’EXECUTE_CATALOG_ROLE’,’EXP_FULL_DATABASE’,’GATHER_SYSTEM_STATISTICS’,’GLOBAL_AQ_USER_ROLE’,’HS_ADMIN_ROLE’,’IMP_FULL_DATABASE’,’JAVADEBUGPRIV’,’JAVAIDPRIV’,’JAVASYSPRIV’,’JAVAUSERPRIV’,’JAVA_ADMIN’,’JAVA_DEPLOY’,’LOGSTDBY_ADMINISTRATOR’,’OEM_MONITOR’,’PLUSTRACE’,’RECOVERY_CATALOG_OWNER’,’RESOURCE’,’SCHEDULER_ADMIN’,’SELECT_CATALOG_ROLE’,’SNMPAGENT’,’TKPROFER’,’WKUSER’,’WM_ADMIN_ROLE’,’XDBADMIN’
,’PUBLIC’)
or
owner not in (‘ADAMS’,’ANONYMOUS’,’BLAKE’,’CLARK’,’CTXSYS’,’DBSNMP’,’DIP’,’DMSYS’,’EXFSYS’,’HR’,’JONES’,’MDDATA’,’MDSYS’,’MGMT_VIEW’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PERFSTAT’,’PM’,’QS’,’QS_ADM’,’QS_CB’,’QS_CBADM’,’QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’SCOTT’,’SH’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSTEM’,’TRACESVR’,’TSMSYS’, ‘WKPROXY’,’WKSYS’,’WK_TEST’,’WMSYS’,’XDB’)
;
prompt spool off
prompt
spool off
==> spool_profiles.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_profile.sql’ char prompt ‘Sql file [/tmp/create_profile.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_profile.log’ char prompt ‘Log file [/tmp/create_profile.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
select
text
from
(
— 1 : header
select profile, 1 num, ‘PROMPT Create Profile ‘||profile||’…’ text from dba_profiles
union
— 2 : create tablespace
select profile, 2, ‘create profile “‘||profile||'” limit’ from dba_profiles
union
select profile, 3+rownum, ‘ ‘||resource_name||’ ‘||limit from dba_profiles
union
— 99999 : strichpunkt + newline
select profile, 999999, ‘;’||chr(10)||chr(10) from dba_profiles
)
where profile not in (‘DEFAULT’)
;
prompt spool off
prompt
spool off
==> spool_public_db_links.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
prompt
prompt !!! WARNING !!! Clear text password in Sql file !!! WARNING !!!
prompt
accept sqlfile default ‘/tmp/create_public_db_link.sql’ char prompt ‘Sql file [/tmp/create_public_db_link.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_public_db_link.log’ char prompt ‘Log file [/tmp/create_public_db_link.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
col text fold_a
select
‘PROMPT Create public database link ‘||l.name||’…’ text,
‘create public database link “‘||l.name||'” ‘||
decode(l.userid,null,null,’CURRENT_USER’,’ connect to current_user ‘,
‘ connect to “‘||USERID||'” identified by “‘||l.PASSWORD||'” ‘)||
decode(l.host,null,null,’ using ”’||l.host||””)||’;’ text
from sys.link$ l, sys.user$ u
where u.user# = l.owner#
;
prompt
prompt spool off
prompt
spool off
prompt
prompt !!! WARNING !!! Clear text password in Sql file &SQLFILE !!! WARNING !!!
prompt
host chmod go-rw &SQLFILE
col text clear
==> spool_public_synonyms.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_public_synonym.sql’ char prompt ‘Sql file [/tmp/create_public_synonym.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_public_synonym.log’ char prompt ‘Log file [/tmp/create_public_synonym.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
col text fold_a
select
‘PROMPT Create public synonym ‘||synonym_name||’…’ text,
‘create public synonym “‘||synonym_name||'” for ‘||
decode(TABLE_OWNER,null,null,'”‘||TABLE_OWNER||'”.’)||'”‘||TABLE_NAME||'”‘||
decode(DB_LINK,null,null,’@”‘||DB_LINK||'”‘)||’;’ text
from dba_synonyms
where table_owner not in (‘ADAMS’,’ANONYMOUS’,’BLAKE’,’CLARK’,’CTXSYS’,’DBSNMP’,’DIP’,’DMSYS’,’EXFSYS’,’HR’,’JONES’,’MDDATA’,’MDSYS’,’MGMT_VIEW’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PERFSTAT’,’PM’,’QS’,’QS_ADM’,’QS_CB’,’QS_CBADM’,’QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’SCOTT’,’SH’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSTEM’,’TRACESVR’,’TSMSYS’, ‘WKPROXY’,’WKSYS’,’WK_TEST’,’WMSYS’,’XDB’)
or db_link is not null
;
prompt
prompt spool off
prompt
spool off
set feedb 6 pages 50000
col text clear
==> spool_roles.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_role.sql’ char prompt ‘Sql file [/tmp/create_role.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_role.log’ char prompt ‘Log file [/tmp/create_role.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
col text fold_a
select
‘PROMPT Create Role ‘||NAME||’…’ text,
‘create role “‘||name||'”‘||
decode(PASSWORD, ‘EXTERNAL’,’ identified externally’,null,null,’ identified by values ”’||PASSWORD||”’ ‘)||’;’ text
from sys.user$, dba_roles
where name=role and name not in (‘AQ_ADMINISTRATOR_ROLE’,’AQ_USER_ROLE’,’AUTHENTICATEDUSER’,’CONNECT’,’CTXAPP’,’DBA’,’DELETE_CATALOG_ROLE’,’EJBCLIENT’,’EXECUTE_CATALOG_ROLE’,’EXP_FULL_DATABASE’,’GATHER_SYSTEM_STATISTICS’,’GLOBAL_AQ_USER_ROLE’,’HS_ADMIN_ROLE’,’IMP_FULL_DATABASE’,’JAVADEBUGPRIV’,’JAVAIDPRIV’,’JAVASYSPRIV’,’JAVAUSERPRIV’,’JAVA_ADMIN’,’JAVA_DEPLOY’,’LOGSTDBY_ADMINISTRATOR’,’OEM_MONITOR’,’PLUSTRACE’,’RECOVERY_CATALOG_OWNER’,’RESOURCE’,’SCHEDULER_ADMIN’,’SELECT_CATALOG_ROLE’,’SNMPAGENT’,’TKPROFER’,’WKUSER’,’WM_ADMIN_ROLE’,’XDBADMIN’)
;
prompt
prompt spool off
prompt
spool off
set feedb 6 pages 50000
col text clear
==> spool_tablespaces.sql <==
def SPACE_MANAGEMENT_COL=”null”
col SPACE_MANAGEMENT_COL new_v SPACE_MANAGEMENT_COL nopri
select column_name SPACE_MANAGEMENT_COL from dba_tab_columns where table_name = ‘DBA_TABLESPACES’ and column_name = ‘SEGMENT_SPACE_MANAGEMENT’;
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_tablespace.sql’ char prompt ‘Sql file [/tmp/create_tablespace.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_tablespace.log’ char prompt ‘Log file [/tmp/create_tablespace.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
select
text
from
(
— 1 : header
select tablespace_name, 1 num, ‘PROMPT Create Tablespace ‘||TABLESPACE_NAME||’…’ text
from dba_tablespaces where contents=’PERMANENT’
union
— 2 : create tablespace
select tablespace_name, 2, ‘create tablespace “‘||tablespace_name||'” datafile’ from dba_data_files
union
— n.99 : comma before second, third, fourth… files
select tablespace_name, 2.99+file_id, ‘,’ from dba_data_files o where exists
(select * from dba_data_files where file_id<o.file_id and tablespace_name=o.tablespace_name)
union
— n : file with autoextend clause
select tablespace_name, 3+file_id, ””||file_name||”’ size ‘||bytes/1024||’K ‘||
decode(AUTOEXTENSIBLE, ‘YES’, ‘ autoextend on next ‘||ceil(INCREMENT_BY*BYTES/blocks/1024/1024)||’M MAXSIZE ‘||
decode(MAXBLOCKS, 4194302,’UNLIMITED’, ceil(MAXBYTES/1024/1024)||’M ‘))
from dba_data_files
union
— 500000 : extent management
select tablespace_name, 500000, decode(allocation_type,
‘USER’, ‘ extent management dictionary default storage (initial ‘||INITIAL_EXTENT/1024||’K NEXT ‘||NEXT_EXTENT/1024||’K PCTINCREASE ‘||PCT_INCREASE||’)’,
‘UNIFORM’, ‘ extent management local uniform’||
decode(MIN_EXTLEN,null,null,’ size ‘||MIN_EXTLEN/1024||’K’),
‘SYSTEM’, ‘ extent management local autoallocate’)
from dba_tablespaces where contents=’PERMANENT’
union
— 600000 : segment space management
select tablespace_name, 600000, decode(&SPACE_MANAGEMENT_COL,
‘AUTO’, ‘ segment space management auto ‘,
‘MANUAL’, ‘ segment space management manual ‘,
‘– segment space management { manual | auto }’)
from dba_tablespaces where contents=’PERMANENT’
union
— 999999 : strichpunkt + newline
select tablespace_name, 999999, ‘;’||chr(10)||chr(10) from dba_tablespaces where contents=’PERMANENT’
)
where tablespace_name not in ( ‘EXAMPLE’, ‘INDX’, ‘PERFSTAT’, ‘RBS’, ‘SYSAUD’, ‘SYSAUX’, ‘SYSTEM’, ‘TEMP’, ‘TOOLS’, ‘UNDOTBS1’, ‘USERS’, ‘XDB’)
;
prompt spool off
prompt
spool off
==> spool_users.sql <==
set lin 10000 trimsp on pages 0 echo off ver off feedb off termout on head off emb on
accept sqlfile default ‘/tmp/create_user.sql’ char prompt ‘Sql file [/tmp/create_user.sql] : ‘
prompt
prompt sqlfile ==> &SQLFILE
prompt
accept logfile default ‘/tmp/create_user.log’ char prompt ‘Log file [/tmp/create_user.log] : ‘
prompt
prompt logfile ==> &LOGFILE
prompt
spool &SQLFILE
prompt
prompt set lin 80 feedb 6
prompt spool &LOGFILE
prompt
col text fold_a
select
‘PROMPT Create User ‘||username||’…’ text,
‘create user “‘||username||'” identified ‘||
decode(PASSWORD, ‘EXTERNAL’,’externally ‘,’by values ”’||PASSWORD||”’ ‘)||
decode(DEFAULT_TABLESPACE,’SYSTEM’,null,’ default tablespace “‘||DEFAULT_TABLESPACE||'” ‘)||
decode(TEMPORARY_TABLESPACE,’SYSTEM’,null,’ temporary tablespace “‘||TEMPORARY_TABLESPACE||'” ‘)||
decode(profile,’DEFAULT’, null, ‘ profile “‘||PROFILE||'” ‘)||’;’ text
from dba_users
where username not in (‘ADAMS’,’ANONYMOUS’,’BLAKE’,’CLARK’,’CTXSYS’,’DBSNMP’,’DIP’,’DMSYS’,’EXFSYS’,’HR’,’JONES’,’MDDATA’,’MDSYS’,’MGMT_VIEW’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PERFSTAT’,’PM’,’QS’,’QS_ADM’,’QS_CB’,’QS_CBADM’,’QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’SCOTT’,’SH’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSTEM’,’TRACESVR’,’TSMSYS’,’WKPROXY’,’WKSYS’,’WK_TEST’,’WMSYS’,’XDB’)
;
prompt
prompt spool off
prompt
spool off
set feedb 6 pages 50000
col text clear
note that it is not fully automated. I always control the generated script, especially /tmp/create_tablespaces.sql, resize the datafiles, change extent management clause if necessary.
I really appreciate that you are sharing your script.
In the section, ==> spool_public_synonyms.sql
the private synonyms are exported and therefore, I do not need to generate script for them. but thanks to your remark, I noticed that for the public synonyms, there is truely a missing AND OWNER=’PUBLIC’ condition.
nice scripts! I’m missing user quotas:
select ‘alter user ‘ || username || ‘ quota ‘ || decode(max_bytes,-1,’unlimited’,to_char(max_bytes/1024)||’K’) || ‘ on ‘ || tablespace_name ||’;’
from dba_ts_quotas;
Martin
Really good stuff! I was trying to migrate a database using database upgrade assistant from oracle 9.2 to oracle 10.2, but could not do it. Could you please tell me the steps?