migrate database with imp exp
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 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.
Posted by Laurent Schneider at 9/01/2005 03:56:00 PM
Permalink : migrate database with imp exp
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
8 Comments:
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 <==
, I believe there may be an error.
An synonym is public if the owner is public. Otherwise it is a private synonym.
SELECT 'create or replace synonym "'
|| owner
|| '"'
|| '.'
|| '"'
|| 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;
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?
Post a Comment
<< Home