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.

8 thoughts on “migrate database with imp exp

  1. Laurent Schneider

    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

  2. Laurent Schneider

    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.

  3. Michael Dinh

    I really appreciate that you are sharing your script.

    In the section, ==> spool_public_synonyms.sql

  4. Laurent Schneider

    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.

  5. Anonymous

    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

  6. sarwar

    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?

Comments are closed.