SCHEMA REFRESH
In this Post , Let us discuss the steps involved during schema refresh using datapump.
Prerequisite to check on Before Export
on SOURCE SIDE
1. Check the Schema Exist or Not. Schema must be there in source database.
col username for a10
col account_status for a10
col created for a20
select username, account_status, created from dba_users where username='<schema-name>’;
2. Check the schema size
select sum(bytes/1024/1024) “in MB” from dba_segments where owner='<schema-name>’;
select sum(bytes/1024/1024/1024) “in GB” from dba_segments where owner='<schema-name>’;
3. check the privileges and roles
system privilege
set pagesize 0
set linesize 200
select ‘grant ‘||privilege||’ to ‘||grantee||’ ; ‘ from dba_sys_privs where grantee in (‘<schema-name>’);
object privilege
select ‘grant ‘||privilege||’ on ‘||OWNER||’.’||table_name||’ to ‘||grantee||’ ; ‘ from dba_tab_privs WHERE OWNER in (‘<schema-name>’);
roles
set pagesize 0
set linesize 200
select ‘grant ‘||granted_role||’ to ‘||grantee||’ ; ‘ from dba_role_privs where grantee in (‘<schema-name>’);
4. have to check the default tablespace of user
col username for a10
col default_tablespace for a20
col temporary_tablespace for a20
select username,default_tablespace,temporary_tablespace from dba_users where username='<schema-name>’;
to check where the schema data are present across available tablespaces
select distinct tablespace_name,sum(bytes)/(1024*1024) MB
from dba_segments
where owner = ‘&a’
group by tablespace_name;
5. quotas
select * from dba_ts_quotas where username='<schema-name>’;
6. check the objects count of user.
select object_type,count(*) from dba_objects where owner='<schema-name>’ group by object_type;
7. Before going to take the export,
thing you have to check is mount point size where you’re going to store the export dumpfile, if mount point doesn’t have sufficient space export job gets fail.
8. create directory
Before creating directory make sure the directory exists in os level
create directory test_dump_dir as ‘/datapump/schemaref’;
9. Create Directory Object and Grant Read and Write Privileges on The Directory
Directory object is a pointer pointing over a directory(which is created above) which you want your expdp utility to use when storing all exported files.
grant read,write on DIRECTORY test_dump_dir to hr;
(grant read,write permission to user you want to perform export. Above I mentioned ‘hr’ user for example)
10. Now you can start the actual export operation for schema
for single schema
expdp username/password directory=test_dmp_dir SCHEMAS=<schema-name> dumpfile=<dumpname>.dmp logfile=<logname>.log
for exporting multiple schema
expdp username/password directory=test_dmp_dir SCHEMAS=<schema-name1>,<schema-name2> dumpfile=<dumpname>.dmp logfile=<logname>.log
Move the DUMPFILES to TARGET using scp server where you want to import
Note: Dont give password in above command instead mention it during runtime.
On TARGET SIDE
1. Check the mount point size, it should be more than schema size.
2. Create Directory Object and Grant Read and Write Privileges on The Directory for import purpose
3. Now we can start import
impdp directory=test_dmp_dir SCHEMAS=<schema-name> dumpfile=<dumpname>.dmp logfile=<logname>.log
4. After import Compare the Object Count with source database.
select object_type,count(*) from dba_objects where owner='<schema-name>’ group by object_type;
5. Check for invalid objects count
Run UTLRP script to recompile invalid objects if necessary
@?/rdbms/admin/utlrp.sql
