Detailed and simple steps for Schema Migration using exp/imp,pipe,gz between two databases

Detailed and simple steps for Schema Migration using exp/imp,pipe,gz between two databases

Start of Migration

On the Source Database
==================
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
SOURCE    READ WRITE

1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by owner;

2. SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by tablespace_name;

3. SQL> select default_tablespace,username from dba_users where username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');

4. SQL> select owner,status,object_type,count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by object_type,owner,status;

5. SQL> select object_type,count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by object_type;

6. SQL> select count(*),status from dba_objects where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by status;

7. SQL> select object_type,count(*),status from dba_objects where owner in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by status,object_type;

8. SQL> select tablespace_name,sum(bytes)/1024/1024/1000 from dba_data_files  where tablespace_name in('TS1','TS2','TS3','TS4','TS5','TS6','TS7') group by tablespace_name;

9. Export Schemas:
  
   vi exp_SOURCE_schemas.sh

   #!/bin/ksh
   export NLS_LANG=AMERICAN_AMERICA.UTF8
   mknod /tmp/exp_pipe1 p
   gzip -cNf SOURCE_schemas.dmp.gz &
   exp system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURCE_schemas.log statistics=none owner=SCHEMA1,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA5
   rm -f /tmp/exp_pipe1
   unset NLS_LANG

10. nohup ./exp_SOURCE_schemas.sh > exp_SOURCE_schemas.log 2>&1

11. Copy the SOURCE_schemas.dmp.gz to Target Database server.
    scp -p SOURCE_schemas.dmp.gz ganesh@target_server:/oracle/export


On the Target Database
==================
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
TARGET    READ WRITE


1. Create tablespaces as per the output from Step 7 you got on Source database

2. Create users on Target database
   Run the following script on the source database and execute the resultant script on the Target database

   set linesize 300
   set pagesize 300
   spool create_users.sql
   select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_tablespace||' temporary tablespace temp profile '||profile||';' from dba_users where username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   spool off

3. Grant Quotas on Target database
   Run the following script on the source database and execute the resultant script on the Target database

   spool create_tablespace_quota.sql
   select 'alter user '||username||' quota unlimited on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES='-1' and username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   select 'alter user '||username||' quota '||max_bytes||' on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES!='-1' and username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   spool off

4. Grant Roles on Target database
   Run the following script on the source database and execute the resultant script on the Target database
   spool create_grant_roles.sql
   select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';' from dba_role_privs where ADMIN_OPTION='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');    
   select 'grant '||GRANTED_ROLE||' to '||GRANTEE||' with admin option;' from dba_role_privs where ADMIN_OPTION='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   spool off

5. Grant System privs on Target database
   spool create_sys_privs.sql
   Run the following script on the source database and execute the resultant script on the Target database
   select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs where  ADMIN_OPTION='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   select 'grant '||PRIVILEGE||' to '||GRANTEE||'  with admin option;' from dba_sys_privs where ADMIN_OPTION='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
   spool off


6. Import Schemas

   vi imp_TARGET_schemas.sh

   #!/bin/ksh
   export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   mknod /tmp/imp_pipe1 p
   gunzip -c SOURCE_schemas.dmp.gz >/tmp/imp_pipe1 &
   imp system/manager99 file=/tmp/imp_pipe1  buffer=20971520 log=imp_TARGET_schemas.log full=y
   rm -f /tmp/imp_pipe1
   unset NLS_LANG

7. Verify the logs

8. Do step 1 to step 7 that you have done on the source database.

9.  Grant Table privs on the Target
    Run the following script on the source database and execute the resultant script on the Target database
    spool create_tab_privs.sql
    select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs where GRANTABLE='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
    select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant option;' from dba_tab_privs where GRANTABLE='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
    spool off


9. EXEC DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA1'); -> Do for all schemas that you have imported

10. exec dbms_stats.gather_schema_stats('SCHEMA1', cascade=>TRUE); -> Do for all schemas that you have imported

End of Migration