Project Target:
- Source
- Server: local-ip: 10.10.10.11,10.10.10.12
- virt-ip: 10.10.10.13,10.10.10.14
- scan-ip: 10.10.10.15
- DB: db-rac01, db-rac02 Running Oracle GI, DB 11.2.0.4 on AIX 6.1L
- Schemas: 'FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC'
- Target
- Sever: local-ip: 10.10.10.21,10.10.10.22
- virt-ip: 10.10.10.23,10.10.10.24
- scan-ip: 10.10.10.25
- DB: db-rac03, db-rac04 Running Oracle GI, DB 11.2.0.4 on AIX 6.1L
- Schemas: 'FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC'
Planning:
Of course, you should make a plan for the migration.
You should take care of points below:
- a. DB directory, user with enough privillage to export the schemas
- If not, create it or grant dba role to it!
- b. Database NLS_LANG should be same
- c. The Edition and help of DataPump(expdb, impdp)
- d. Check the data size and types of the schemas
- Is there any static data could be migration when the applications are using DB.
- If yes, migrat it first.
- Is there enough time and network bandwidth to move the dump file to target server.
- If not, maybe you need to think about manually building the Index on the target DB instead of export it.
- Is there any objects are "INVALID" in the source DB.
- If yes, make note.
- e. Try to export the data and import the data before you really need to do it.
- Now, you might face a lot of problem, resolve them.
- This time, you will be able to estimate the time it need to finish the Migration.
- In the course of this test, you could find some option could be optimized, 4 example "PARALLEL".
- f. Prepare a database link from soruce DB to target DB.
- You had better to create the db link in source DB because of the possibility of the listener of source DB \
- would be diabled in the process of migration.
- Compare the objects in source DB and target DB after migration.
- g. Something before finishing.
Executing:
Create DB user, directory for migration:
- SQL> create user jackson identified by "jackson_mig" account unlock; -- execute in both
- SQL> grant dba to jackson; -- execute in both
- SQL> grant create session to jackson; -- execute in both
- SQL> create directory EXPDUMP as '/dmpfiles'; -- execute in Source DB
- SQL> create directory IMPDUMP as '/dmpfiles'; -- execute in Target DB
@ Mount the fs on target server with root
- # mount -o rw,bg,hard,intr,proto=tcp,vers=3,rsize=65536,wsize=65536,timeo=600 10.10.10.11:/dmpfiles /dmpfiles;
@ You can alse use scp to cp the dumpfiles to target server, but it take more times.
- $ scp /dmpfiles/expdp_*.dmp oracle@10.10.10.21:/dmpfiles/
- $ cd /dmpfiles; tar -cf - expdp_*.dmp | ssh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for Unix and linux
- tar -cO expdp_*.dmp | ssh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for Linux
- tar -cf - expdp_*.dmp | rsh 10.10.10.21 "tar -xv -C /dmpfiles"; -- for rsh
- SQL> select (select upper(value) from nls_database_parameters where parameter='NLS_LANGUAGE')
- || '_' ||
- (select upper(value) from nls_database_parameters where parameter='NLS_TERRITORY')
- || '.' ||
- (select upper(value) from nls_database_parameters where parameter='NLS_CHARACTERSET') NLS_LANG
- from dual;
-
- NLS_LANG
- -----------------------
- AMERICAN_AMERICA.ZHS16GBK
- $ expdp help=yes;
- $ impdp help=yes;
Collect data information:
@ Talk with application developer about the static data and down time.
@ Talk with network administrator about the network bandwidth.
@ collect information of schemas and data.
Source Tablespaces Size:
- SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as "TBS_Size (GB)"
- from dba_data_files group by tablespace_name order by 2 desc;
- SQL> select owner, sum(bytes)/1024/1024 as "DataSize (MB)"
- from dba_segments where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- group by owner order by 2 desc;
- SQL> select username, account_status, default_tablespace, temporary_tablespace from dba_users
- where username in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
- SQL> select owner, object_name, object_type, status
- from dba_objects where owner in
- ('FJZKXT','FJZP','LOOTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and status = 'INVALID';
- SQL> select count(*) from dba_objects
- where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
- SQL> create database link LK_TARGET connect to jackson identified by "jackson_mig"
- using '(DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.23)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = TARGET_DB)
- )
- )';
- SQL> select instance_name from sys.v$instance@LK_TARGET;
Create the tablespaces before migration in Target DB:
- SQL> create tablespace TBS_NAME datafile '+DATA' size 30720M( according to the real size of Source DB);
- SQL> set long 1000;
- SQL> set line 500;
- SQL> set pagesize 99;
- SQL> select dbms_metadata.get_ddl('USER',U.username)
- from dba_users U where U.username
- in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
- SQL> select * from dba_sys_privs
- where grantee in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
- SQL> select * from dba_role_privs
- where grantee in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
Create test shell script for export data and import data:
@ Why shell script not execute expdp/impdp directly?
1. Execute directly you will show others the password of jackson(dba) in command history.
2. You can easily to edit the command in the script file.
3. It will be easy to executed with "nohup" command for unstable network or time limited connection.
@ Create the shell script and parfile for export data.
- $ cat > expdp_data.sh << EOF
- #!/bin/sh
-
- # file name: expdp_data.sh
- # execute directory: /dumpfiles
- # you'd better referrence to the user oracle's profile on Source server
-
- ORACLE_BASE=/app/oracle
- ORACLE_HOME=/app/oracle/product/11.2.0/db_1
- ORACLE_SID=source2 # sid of Source DB
- NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
- PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/bin:/etc:/usr/lbin:/usr/bin/X11:/usr/local/bin:$PATH
- export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG LD_LIBRARY_PATH PATH
-
- echo "`date`"
-
- # when it is large than 2, the third file can not be created on the NFS.
- expdp jackson/oracle \
- SCHEMAS=('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC') \
- PARALLEL=2 \
- directory=EXPDUMP \
- dumpfile=expdp_data_%U.dmp \
- logfile=expdp_data_.log \
- parfile=/dmpfiles/exp_data.par
-
- echo "`date`"
- EOF
- ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- End Of File expdp_data.sh ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
- $ cat > exp_data.par << EOF
- # attach=expdp_data_20160521 # if you set this, it will be easy to interact with the expdp process. Or it will be
- # created automaticly by system. You can find it in the logfile or on the screen.
- # schemas=('USER1',...,'USERn') # if you want to export more than one schemas.
- # parallel=2 # define threads to export the same time.
- # dumpfile=expdp_data #_%U.dmp %U will be instead of 01..99 automaticly when files created
- # directory=Dir1,Dir2,Dir3 # if your files are more than 99, you need to set multi values here. Each dir will
- # appear files 01..99.
- # content=all # "all" is default value, "metadata_only" will not export data, "data_only" will
- # only export data.
- # compression # don't use it! Trust me! That will be too slow.
- # full # Y, if you want to export full database.
- # exclude=schema:\"=\'HR\'\" # if you don't want the schema "HR", '\' is very important
- # reuse_dumpfiles=Y # if the dumpfile is exist, it will be overwrited.
- # parfile=exp_data.par # this file is parfile, if the statment is too long, the parfile will be useful.
- EOF
- ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~ End Of File exp_data.par ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
- $ nohup sh exp_data.sh >> nohup_exp_data.log &
- $ tail -f nohup_exp_data.log -- follow the log of exporting.
- $ expdp jackson/oracle attach=jackson.expdp_data_20160521 -- Now some one may see your password in command history.
- $ expdp attach=jackson.expdp_data_20160521
- username:
- password: -- It would be safe like this.
- Export> status -- it'll show you the status of the job, you can try other command by referrencing "expdp help=yes".
- -- you can also interact with impdp like this.
- $ cat > impdp_data.sh << EOF
- #!/bin/sh
-
- # file name: impdp_data.sh
- # execute directory: /dumpfiles
- # you'd better referrence to the user oracle's profile on Target server
-
- ORACLE_BASE=/app/oracle
- ORACLE_HOME=/app/oracle/product/11.2.0/db_1
- ORACLE_SID=target2 -- sid of Target DB
- NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
- PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/bin:/etc:/usr/lbin:/usr/bin/X11:/usr/local/bin:$PATH
- export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG LD_LIBRARY_PATH PATH
-
- echo "`date`"
-
- impdp jackson/oracle \
- PARALLEL=2 \
- directory=IMPDUMP \
- dumpfile=impdp_data_%U.dmp \
- logfile=impdp_data_.log \
- parfile=/dmpfiles/imp_data.par
-
- echo "`date`"
- EOF
- ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- End Of File impdp_data.sh ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
- $ cat > imp_data.par << EOF
- # attach=impdp_data_20160521 # if you set this, it will be easy to interact with the expdp process. Or it will
- # be created automaticly. You can find it in the logfile or on the screen.
- # schemas=('USER1',...,'USERn') # if you don't want to import all schemas exported before.
- # dumpfile=expdp_data_%U.dmp # %U will be instead of 01..99 automaticly when files created
- # directory=Dir1,Dir2,Dir3 # according to the PATH in Source DB or the PATH you copy the dumpfiles to.
- # content=all # "all" is default value, "metadata_only" will not export data, "data_only" will
- # only export data.
- # remap_tablespace=tbs1:tbs2,tbs3:tbs4 # if you want to change the tablespace of the objects in Target DB.
- # remap_schema # change the schemas of data in Target DB.
- # remap_table # change table name, I recommend you don't make it to complicated.
- # parfile=exp_data.par # this file is parfile, if the statment is too long, the parfile will be useful.
- EOF
- ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~ End Of File imp_data.par ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
- $ nohup sh imp_data.sh >> nohup_imp_data.log &
- $ tail -f nohup_imp_data.log -- follow the log of importing.
Comparing:
Compare the logs of expdp and impdp:
@ Make sure the shell script was executed succesfully.
- $ tail -n 5 /dumpfils/impdp_data.log
- $ grep rows /dumpfiles/expdp_data.log | wc -l
- $ grep rows /dumpfiles/impdp_data.log | wc -l
Compare the data of the two DB:
@ Compare objects
- SQL> select count(*) from dba_objects
- where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC');
- SQL> select owner,object_name,object_type
- from dba_objects where owner in
- ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and object_type not in ('INDEX','LOB')
- minus
- select owner,object_name,object_type
- from dba_objects@lk_target where owner in
- ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and object_type not in ('INDEX','LOB');
- SQL> select owner, count(*) from dba_objects
- where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and object_type in ('INDEX','LOB') group by owner;
- SQL> select owner, count(*) from dba_objects@lk_target
- where owner in ('FJZKXT','FJZP','LOTTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and object_type in ('INDEX','LOB') group by owner;
- SQL> select table_name, count(*) from dba_indexes where owner = 'SCHEMA1' group by table_name;
- SQL> select table_name, count(*) from dba_indexes@lk_target where owner = 'SCHEMA1' group by table_name;
- SQL> select table_name, count(*) from dba_lobs where owner = 'SCHEMA1' group by table_name;
- SQL> select table_name, count(*) from dba_lobs@lk_target where owner = 'SCHEMA1' group by table_name;
- SQL> select owner, table_name, index_name, uniqueness from dba_indexes
- where owner = 'SCHEMA1' and table_name = 'TABLE1';
- SQL> select owner, table_name, index_name, column_name from dba_ind_columns
- where owner = 'SCHEMA1' and table_name = 'TABLE1';
- SQL> select owner, table_name, index_name, colunm_name from dba_lobs
- where owner = 'SCHEMA1' and table_name = 'TABLE1';
@ Find out "INVALID" objects.
- SQL> select owner, object_name, object_type, status
- from dba_objects where owner in
- ('FJZKXT','FJZP','LOOTERY','FJYDIM','FJIHOME','SECKILL','FJYDWSC')
- and status = 'INVALID';
- SQL> alter PACKAGE xxx compile;
- SQL> alter PACKAGE xxx compile body;
- SQL> alter PROCEDURE xxx compile;
- SQL> alter FUNCTION xxx compile;
- SQL> alter TRIGGER xxx compile;
- SQL> alter VIEW xxx compile;
- SQL> alter trigger xxx compile;
-
- SQL> @?/rdbms/admin/utlrp.sql; -- Or, just do this with sys.
Some Clear:
@ maybe you want to lock the user in the Source DB.
- SQL> drop database link lk_target;
- SQL> alter user FJZKXT account lock;
- SQL> alter user FJZP account lock;
- SQL> alter user LOOTERY account lock;
- SQL> alter user FJYDIM account lock;
- SQL> alter user FJIHOME account lock;
- SQL> alter user SECKILL account lock;
- SQL> alter user FJYDWSC account lock;
- SQL> alter user JACKSON identified by "a password even I don't know what it is." account lock; -- in both DB
- SQL> drop user JACKSON;
End:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25521690/viewspace-2104719/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25521690/viewspace-2104719/