Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Schema Refresh in oracle

 Schema Refersh steps:
 =====================
 1. take expdport of a schema in source database(ctsp).
 expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja
 Note: Dont give semicolon(;) at the end of the above command.

 2. move the dumpfile to target database(ctsd) using scp.if both the database running in same server this steps is not required.
scp ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle
 3. create the new user in target database(if already existed drop and recreate)
 select username from dba_users;
 drop user raja cascade;
 create user raja identified by raja;
 4. Befor doing import(impdp) check the used space of a schema(user) in source database and freespace in the target database then
 target database tablespaces should be more than or equal to source database tablespaces.

 Source:
 select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner='RAJA' group by tablespace_name;
 Target:
 select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;
5. Start the import(impdp) in taget database.
impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja
6. once completed compare the object counts between source and target databases.
 select object_type,count(*) from dba_objects where owner='RAJA' and status='VALID' group by object_type;
#!/bin/sh -x

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart

Top 20 Highest Paying URL Shortener Companies - Best URL Shorteners To Earn Fast

PostgreSQL pgBadger