How to migrate the schema from one database to another database



Last updated on December 21st, 2015 at 05:50 am

Here ,I will give u Detailed steps for Schema Migration using exp/imp,pipe,gz between two databases

How to migrate the schema from one database to another database

The below steps need to be done on the Source Database

Metadata about schema

1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in (‘EXAMPLE_SCH’) group by owner;

2. SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where owner in (‘EXAMPLE_SCH’) group by tablespace_name;

3. SQL> select default_tablespace,username from dba_users where username in (‘EXAMPLE_SCH’);

4. SQL> select owner,status,object_type,count(*) from dba_objects where owner in (‘EXAMPLE_SCH’) group by object_type,owner,status;

5. SQL> select object_type,count(*) from dba_objects where owner in (‘EXAMPLE_SCH’) group by object_type;

6. SQL> select count(*),status from dba_objects where owner in (‘EXAMPLE_SCH’) group by status;

7. SQL> select object_type,count(*),status from dba_objects where owner in (‘EXAMPLE_SCH’) group by status,object_type;

8. SQL> select tablespace_name,sum(bytes)/1024/1024/1000 from dba_data_files where tablespace_name in(‘TS1’) group by tablespace_name;

Export the schema
9. Export Schemas:

vi exp.sh

#!/bin/ksh
export NLS_LANG=AMERICAN_AMERICA.UTF8
mknod /tmp/exp_pipe1 p
gzip -cNf SOURCE_schemas.dmp.gz &
exp system/password file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURCE_schemas.log statistics=none owner=EXAMPLE_SCH
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 orac@host1:/home/oracle/export

On the Target Database
==================

Create metadata in Target database

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 (‘EXAMPLE_SCH’);
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 (‘EXAMPLE_SCH’);
select ‘alter user ‘||username||’ quota ‘||max_bytes||’ on ‘||tablespace_name||’;’ from dba_ts_quotas where MAX_BYTES!=’-1′ and username in (‘EXAMPLE_SCH’);
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 (‘EXAMPLE_SCH’);
select ‘grant ‘||GRANTED_ROLE||’ to ‘||GRANTEE||’ with admin option;’ from dba_role_privs where ADMIN_OPTION=’YES’ and grantee in (‘EXAMPLE_SCH’);
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 (‘EXAMPLE_SCH’);
select ‘grant ‘||PRIVILEGE||’ to ‘||GRANTEE||’ with admin option;’ from dba_sys_privs where ADMIN_OPTION=’YES’ and grantee in (‘EXAMPLE_SCH’);
spool off

Import schema in Target database
6. Import Schemas

vi imp.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/password 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 (‘EXAMPLE_SCH’);
select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’ with grant option;’ from dba_tab_privs where GRANTABLE=’YES’ and grantee in (‘EXAMPLE_SCH’);
spool off

9. EXEC DBMS_UTILITY.COMPILE_SCHEMA (‘EXAMPLE_SCH’); -> Do for all schemas that you have imported

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


Leave a Reply