Sunday, January 2, 2011

Executing Export and import jobs

Hi,
Below are the methods you can use to run your export and import jobs when you are working in any unix environment like linux,solaris,hp/ux and ibm-aix,When you want to transfer the data from one database to other in same machine you can follow the below steps 1 & step 3,If you want to do transfer the data from one database running in one machine to other database running in different machine than you can use ftp or winscp.

Step 1: Export
In the below example I have exported five schemas from one database and imported to other Database.


Exporting the Data:
-------------------


1)starmig:A user whose data we have to export.
exp system/database@orcl.world file='/work/Rafi/exp_orcl_starmig_23jun10.dmp' log=/work/Rafi/exp_orcl_starmig_23jun10.log owner=starmig statistics=none
vi imp_starmig.sh =>Add the exp script here
To run in background without interupt:
nohup sh imp_starmig.sh > a.out &

2)startxn:A user whose data we have to export.
exp system/database@orcl.world file='/work/Rafi/exp_orcl_startxn_23jun10.dmp' log=/work/Rafi/exp_orcl_startxn_23jun10.log owner=startxn statistics=none
nohup sh imp_startxn.sh > b.out &

3)startxnapp:A user whose data we have to export.
exp system/database@orcl.world file='/work/Rafi/exp_orcl_startxnapp_23jun10.dmp' log=/work/Rafi/exp_orcl_startxnapp_23jun10.log owner=startxnapp statistics=none
nohup sh imp_startxnapp.sh > c.out &

4)starrep:A user whose data we have to export.
exp system/database@orcl.world file='/work/Rafi/exp_orcl_starrep_25jun10.dmp' log=/work/Rafi/exp_orcl_starrep_25jun10.log owner=starrep statistics=none
nohup sh imp_starrep25.sh > r.out &

5)starrepapp:A user whose data we have to export.
exp system/database@orcl.world file='/work/Rafi/exp_orcl_starrepapp_23jun10.dmp' log=/work/Rafi/exp_orcl_starrepapp_23jun10.log owner=starrepapp statistics=none
nohup sh imp_starrepapp.sh > e.out &

Step2:Copy the dumps using ftp or winscp
Note if you are using winscp,Copy the file using binary mode of winscp.

Importing the Data :
1)starmig:
imp system/star@STARDEV file='/work/Rafi/exp_orcl_starmig_23jun10.dmp' log=imp_starmig_stardev23jun10.log fromuser=starmig touser=starmig
nohup sh imp_starmigDEV.sh > f.out &

2)startxn:
imp system/star@STARDEV file='/work/Rafi/exp_orcl_startxn_23jun10.dmp' log=imp_startxn_stardev23jun10.log fromuser=startxn touser=startxn
nohup sh imp_startxnDEV.sh > g.out &

3)startxnapp:
imp system/star@STARDEV file='/work/Rafi/exp_orcl_startxnapp_23jun10.dmp' log=imp_startxnapp_stardev23jun10.log fromuser=startxnapp touser=startxnapp
nohup sh imp_startxnappDEV.sh > h.out &

4)starrep:
imp system/star@STARDEV file='/work/Rafi/exp_orcl_starrep_25jun10.dmp' log=imp_starrep_stardev25jun10.log fromuser=starrep touser=starrep
nohup sh imp_starrepDEV.sh > i.out &

5)starrepapp:
imp system/star@STARDEV file='/work/Rafi/exp_orcl_starrepapp_23jun10.dmp' log=imp_starrepapp_stardev23jun10.log fromuser=starrepapp touser=starrepapp
nohup sh imp_starrepappDEV.sh > j.out &

Step4:
Verified the objects and tables that are imported.
-------
Connect to SQL PLUS with each user:

1)starmig:
SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;
Compared this with starmig Schema in STARDEV Database.

2)startxn:
SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;
Compared this with startxn Schema in STARDEV Database.

3)Startxnapp:
SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;
Compared this with startxnapp Schema in STARDEV Database.

4)Starrep:
SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;
Compared this with starrep Schema in STARDEV Database.

5)Starrepapp:
SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;
Compared this with starrepapp Schema in STARDEV Database.

Hope this helps while doing your export import activities,which we do frequently as DBA.


Best regards,

Rafi.

No comments:

Post a Comment