使用传输表空间的方法来迁移oracle 数据库从SPARC到X86-64

这篇文章是记录2012年将数据库从SPARC迁移到X86-64上的过程. 由于是异构平台迁移, 能够使用的方法只有expdp和数据文件转化了. 由于公司开发数据库数量多,而且总的容量也不少,需要迁移的数据库大约有2T. 考虑后决定使用传输表空间的方法,而且由于数据库多, 允许的时间仅是周末,为了测试和正式升迁考虑,就使用脚本来做了.大的思路确定后,就是写脚本,按照测试的情况将升迁中遇到的一些bug,和其它问题一一解决后,又将脚本跑了一遍,重复测试保证没有遇到问题后,在周末顺利升迁.

脚本的大致思路是

1. 使用DBMS_TTS.TRANSPORT_SET_CHECK检查表空间.

2. 导出数据库的meta data.

3. 将表空间只读,

4. 执行expdp导出传输表空间相应控制信息.

5. 使用rman转化对应的数据文件.

6. 在新数据库中导入传输表空间. 由于这个步骤需要在另外机器中执行,所以机器之间需要设置ssh的信任关系.使用ssh执行临时产生的脚本.

 

具体脚本如下:

#!/usr/bin/bash. ~/.profile# wan qisheng 2012.3.5# to use the script to convert db, it's must create a empty db at destination server, and the name is same with original,# and the username is same with original db# to use ssh and avoid the password input, it's need setup trust between the servers.# The original server ,destination server mount NAS at same point, so we can keep the script, temporary file at NAS,# when we ssh to destination server, we entry the point to access the these files.# when we upgrade db cross platform, assume the original server and destination server mount the NAS# at same mount, and assume have right privilageORACLE_SID=$1DP_UPGRADE_DIRECTORY=$2DESTINATION_DATAFILE_DIRECTORY=$DP_UPGRADE_DIRECTORY/$ORACLE_SIDDESTINATION_SERVER=$3DESTINATION_USER=$4DESTINATION_ORACLE_BASE=$5DESTINATION_ORACLE_HOME=$6SSH=/usr/local/bin/sshSCP=/opt/wmi/sbin/scpexport ORACLE_SIDcd $DP_UPGRADE_DIRECTORYrm -f expdp_full_norows.dmp expdp_full_norows.logrm -f expdp_tts.par expdp_tts.dmp expdp_tts.logrm -f convert.tablespaces.rmanrm -f impdp_tts.par impdp_tts.logrm -f alter.user.sqlrm -f impdp.tts.sh impdp_full_norows.log# 1. Check if the tablespaces to transport can be made, then check whether file name is unique#    if the ORA- error has raised, we will exit the shell script.#    we check all db at first, so we can grep ORA- from the log file.#    2012.3.6, finish the 192.168.0.109 dbs prepare check.# sqlplus -s '/ as sysdba' @transport.set.check.sql# 2. export the meta data.#    to avoid some mistake or forget, so we create the directory at here, don't create it at prepare script.sqlplus -s '/ as sysdba' <<EOFdrop directory dp_upgrade;create directory dp_upgrade as '$DP_UPGRADE_DIRECTORY';EOFexpdp \'/ as sysdba\' directory=dp_upgrade dumpfile=expdp_full_norows.dmp logfile=expdp_full_norows.log full=y content=metadata_only# 3. set the tablespaces entry readonly.sqlplus -s '/ as sysdba' @set.tablespace.readonly.sql# 4. generate the expdp_tts.par parameter file, it's get the all tablespace that we will transport.echo 'userid="/ as sysdba"'                              >expdp_tts.parecho "directory=dp_upgrade"                             >>expdp_tts.parecho "dumpfile=expdp_tts.dmp"                           >>expdp_tts.parecho "logfile=expdp_tts.log"                            >>expdp_tts.parecho "transport_full_check=y"                           >>expdp_tts.parecho "transport_tablespaces=("                          >>expdp_tts.parsqlplus -s '/ as sysdba' @get.transport.tablespaces.sql >>expdp_tts.parecho ")"                                                >>expdp_tts.parexpdp parfile=expdp_tts.par# 5. convert datafiles to destination.#    at step 1, we checked that the file name is unique, so we only set the remap directory.#    we don't use FORMAT statement(%N-%f.dbf,or %U).sqlplus -s '/ as sysdba' @convert.tablespaces.sql $DESTINATION_DATAFILE_DIRECTORY >convert.tablespaces.rmanrman target / @convert.tablespaces.rman# 6. import the transport tablespaces.#    to reduce the upgrade time, we have created the database at first through script.#    it's need keep right block size.#    first we prepare some temporary script file, then we ssh to destination server to run it.echo 'userid="/ as sysdba"'                              >impdp_tts.parecho "directory=dp_upgrade"                             >>impdp_tts.parecho "dumpfile=expdp_tts.dmp"                           >>impdp_tts.parecho "logfile=impdp_tts.log"                            >>impdp_tts.parecho "transport_datafiles=("                            >>impdp_tts.parsqlplus -s '/ as sysdba' @get.transport.datafiles.sql $DESTINATION_DATAFILE_DIRECTORY  >>impdp_tts.parecho ")"                                                >>impdp_tts.parsqlplus -s '/ as sysdba'  <<EOF        >alter.user.sqlset pagesize 0set feedback offset linesize 500set trimspool onselect 'alter user '||username||' default tablespace '||a.DEFAULT_TABLESPACE||';'  from dba_users a where username not in ('SYS','SYSTEM','DBSNMP','OUTLN');prompt exitEOFecho '#!/usr/bin/bash'                                   >impdp.tts.shecho ""                                                 >>impdp.tts.shecho ". ~/.profile"                                     >>impdp.tts.shecho "export ORACLE_SID=$ORACLE_SID"                    >>impdp.tts.shecho "cd $DP_UPGRADE_DIRECTORY"                         >>impdp.tts.shecho "impdp parfile=impdp_tts.par"                      >>impdp.tts.shecho "sqlplus -s '/ as sysdba' @alter.user.sql"         >>impdp.tts.shecho "sqlplus -s '/ as sysdba' @set.tablespace.readwrite.sql"  >>impdp.tts.shecho "impdp \'/ as sysdba\' directory=dp_upgrade dumpfile=expdp_full_norows.dmp logfile=impdp_full_norows.log full=y content=metadata_only table_exists_action=skip"  >>impdp.tts.shecho "sqlplus -s '/ as sysdba' <<EOF "                  >>impdp.tts.shecho "@?/rdbms/admin/utlrp.sql"                         >>impdp.tts.shecho exit                                               >>impdp.tts.shecho EOF                                                >>impdp.tts.sh# 7. set datafiles right,so at destination server we can access it.chmod 666 $DESTINATION_DATAFILE_DIRECTORY/*chmod 777 $DP_UPGRADE_DIRECTORY/*$SSH -n $DESTINATION_USER@$DESTINATION_SERVER $DP_UPGRADE_DIRECTORY/impdp.tts.sh# 8. set original db tablespaces to read write.#    it's only for test ,when true convert db , the line need be remove.sqlplus -s '/ as sysdba' @set.tablespace.readwrite.sql

 

#!/usr/bin/bash# call convert.db.sh to transform all dbs.# the toracle is version 10.2.0.3# the noracle is version 10.2.0.1# the doracle is version 10.2.0.4ORIGNIAL_USERS=(toracle)DESTINATION_USERS=(qoracle)DESTINATION_ORACLE_BASES=(/u01/qoracle)DESTINATION_ORACLE_HOMES=(/u01/qoracle/oracle/product/11.2.0/db_5)DP_UPGRADE_DIRECTORY=/dbfiles_ssz0009dwwDESTINATION_SERVER=192.168.0.109#ps -eo user,comm|grep ora_pmon|grep -v grep|sed 's/ora_pmon_//'|#while read user dbps -eo user,comm|grep ora_pmon|grep -v noracle|grep -v grep|sed 's/ora_pmon_//'|awk '{print $1":"$2}' >/tmp/dbs.logfor user_db in `cat /tmp/dbs.log`do  user=`echo $user_db|awk '{FS=":";print $1}'`  db=`echo $user_db|awk '{FS=":";print $2}'`  index=0  for cur_user in ${ORIGNIAL_USERS[*]}  do    if [ "$cur_user" = "$user" ]    then      echo "begin process $user:$db"      DESTINATION_USER=${DESTINATION_USERS[index]}      DESTINATION_ORACLE_BASE=${DESTINATION_ORACLE_BASES[index]}      DESTINATION_ORACLE_HOME=${DESTINATION_ORACLE_HOMES[index]}            su - $user -c "$DP_UPGRADE_DIRECTORY/convert.db.sh $db $DP_UPGRADE_DIRECTORY $DESTINATION_SERVER $DESTINATION_USER $DESTINATION_ORACLE_BASE $DESTINATION_ORACLE_HOME" >$user.$db.log 2>&1    fi    index=`expr $index + 1`  donedonerm /tmp/dbs.log

 

transport.set.check.sql文件如下:

-- wan qisheng 2012-03-05-- do all database prepare check.set serveroutput on size 1000000set feedback offbegin  dbms_tts.transport_set_check('tbs_wqs',TRUE);EXCEPTION  when others then    if SQLCODE=-1001 then      dbms_output.put_line('First create TRANSPORT_SET_VIOLATIONS.');    else      dbms_output.put_line('ORA-20001, when first run dbms_tts.transport_set_check to create TRANSPORT_SET_VIOLATIONS.');      dbms_output.put_line(SQLCODE||':'||SUBSTR(SQLERRM, 1 , 64));    end if;end;/declare  cursor c_TRANSPORT_SET_VIOLATIONS is select violations from TRANSPORT_SET_VIOLATIONS;  tablespace_names varchar2(30000);  type tslist is table of dba_tablespaces.tablespace_name%type;  ts tslist;  type cursor_ref is ref cursor;  ts_cur cursor_ref;  i integer;  v_instance_name varchar2(30);begin  select instance_name into v_instance_name from v$instance;  dbms_output.put_line('Starting to check tablespaces as specified');  open ts_cur for 'select tablespace_name from dba_tablespaces                   where tablespace_name not in (''SYSTEM'', ''SYSAUX'')                     and contents not in (''UNDO'', ''TEMPORARY'')';   fetch ts_cur bulk collect into ts;   close ts_cur;  tablespace_names :='';  for i in ts.first .. ts.last  loop     if ( i = ts.first)     then       tablespace_names := ts(i);     else       tablespace_names := tablespace_names || ', ' || ts(i);     end if;  end loop;  dbms_output.put_line(tablespace_names);  DBMS_TTS.TRANSPORT_SET_CHECK(tablespace_names, TRUE, TRUE);  for c_cur in c_TRANSPORT_SET_VIOLATIONS loop    --output to instance name, so we can grep ORA- to get the db from log file.    dbms_output.put_line('ORA-20002 '||v_instance_name||': '||c_cur.violations);  end loop;  select count(*) into i    from (select substr(file_name,instr(file_name,'/',-1)),count(*)            from dba_data_files           group by substr(file_name,instr(file_name,'/',-1))          having count(*)>1         );  if i>0 then    --output to instance name, so we can grep ORA- to get the db from log file.    dbms_output.put_line('ORA-20003 '||v_instance_name||': the file name is not uniquely.');  end if;end;/exit

set.tablespace.readonly.sql如下:

set line 500set trimspool onset feedback offset heading offset pagesize 0alter database end backup;spool readonly.sqlselect 'alter system kill session '''||b.sid||','||b.serial#||''';'  from v$transaction a,v$session b where a.SES_ADDR=b.SADDR;select 'COMMIT FORCE '''||LOCAL_TRAN_ID||''';'  from DBA_2PC_PENDING;  select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces a where tablespace_name not in ('SYSTEM', 'SYSAUX')   and contents not in ('UNDO', 'TEMPORARY');spool off@@readonly.sqlhost rm readonly.sqlexit

get.transport.tablespaces.sql如下:

--get.transport.tablespaces.sqlset line 500set trimspool onset serveroutput on size 1000000set feedback offdeclare  type tablespacetyp is table of dba_tablespaces%rowtype;  tslist tablespacetyp;  type cursor_ref is ref cursor;  c_cur cursor_ref;begin  open c_cur for 'select * from dba_tablespaces                  where tablespace_name not in (''SYSTEM'', ''SYSAUX'')                    and contents not in (''UNDO'', ''TEMPORARY'')';  fetch c_cur bulk collect into tslist;  close c_cur;  for i in tslist.first .. tslist.last-1  loop    dbms_output.put_line(tslist(i).tablespace_name ||',');  end loop;  dbms_output.put_line(tslist(tslist.last).tablespace_name);end;/exit

convert.tablespaces.sql如下:

--convert.tablespaces.sqlset veri offset line 500set trimspool onset serveroutput on size 1000000set feedback offdeclare  type tablespacetyp is table of dba_tablespaces%rowtype;  tslist tablespacetyp;  type filedirtyp    is table of varchar2(1024);  filedirlist filedirtyp;  type cursor_ref is ref cursor;  c_cur cursor_ref;  v_platform varchar2(200);begin  --because the 10.2.0.4 platform name change to Solaris Operating System (x86-64)  --so it's need modify the script.  select decode(version,'10.2.0.3.0','  TO PLATFORM ''Solaris Operating System (AMD64)''',                                     '  TO PLATFORM ''Solaris Operating System (x86-64)''')         into v_platform    from dba_registry   where comp_id='CATALOG';  open c_cur for 'select * from dba_tablespaces                  where tablespace_name not in (''SYSTEM'', ''SYSAUX'')                    and contents not in (''UNDO'', ''TEMPORARY'')';  fetch c_cur bulk collect into tslist;  close c_cur;  open c_cur for 'select substr(file_name,1,instr(file_name,''/'',-1)-1) file_dir                    from dba_data_files                   group by substr(file_name,1,instr(file_name,''/'',-1)-1)';  fetch c_cur bulk collect into filedirlist;  close c_cur;  dbms_output.put_line('run {');  for i in tslist.first .. tslist.last  loop    dbms_output.put_line('CONVERT tablespace "'||tslist(i).tablespace_name||'"');    dbms_output.put_line(v_platform);    dbms_output.put_line('  DB_FILE_NAME_CONVERT=(');    for j in filedirlist.first .. filedirlist.last-1    loop      dbms_output.put_line('  '||chr(39)||filedirlist(j)||chr(39)||','||chr(39)||'&1'||chr(39)||',');    end loop;    dbms_output.put_line('  '||chr(39)||filedirlist(filedirlist.last)||chr(39)||','||chr(39)||'&1'||chr(39));    dbms_output.put_line(');');  end loop;  dbms_output.put_line('}');end;/exit

get.transport.datafiles.sql如下:

--get.transport.datafiles.sql--set veri offset line 500set trimspool onset serveroutput on size 1000000set feedback offdeclare  type datafiletyp is table of varchar2(4096);  fslist datafiletyp;  cursor c_cur is     select '&1'||substr(file_name,instr(file_name,'/',-1))      from dba_data_files a     where tablespace_name in (select tablespace_name from dba_tablespaces                                where tablespace_name not in ('SYSTEM', 'SYSAUX')                                  and contents not in ('UNDO', 'TEMPORARY')                              );begin  open c_cur ;  fetch c_cur bulk collect into fslist;  close c_cur;  for i in fslist.first .. fslist.last-1  loop    dbms_output.put_line(''''||fslist(i)||''',');  end loop;  dbms_output.put_line(''''||fslist(fslist.last)||'''');end;/exit

set.tablespace.readwrite.sql如下:

--set.tablespace.readwrite.sqlset line 500set trimspool onset serveroutput on size 1000000set feedback offdeclare  string varchar2(4096);  type tablespacetyp is table of dba_tablespaces%rowtype;  tslist tablespacetyp;  type cursor_ref is ref cursor;  c_cur cursor_ref;begin  open c_cur for 'select * from dba_tablespaces                  where tablespace_name not in (''SYSTEM'', ''SYSAUX'')                    and contents not in (''UNDO'', ''TEMPORARY'')';  fetch c_cur bulk collect into tslist;  close c_cur;  for i in tslist.first .. tslist.last  loop    execute immediate 'alter tablespace '||tslist(i).tablespace_name ||' read write';    dbms_output.put_line('Tablespace ' || tslist(i).tablespace_name ||' read write');  end loop;end;/exit

alter.user.sql如下:

alter user ENVMGR default tablespace TBL_ENVMGR;alter user ANONYMOUS default tablespace SYSAUX;alter user XS$NULL default tablespace SYSTEM;alter user ORACLE_OCM default tablespace SYSTEM;alter user DIP default tablespace SYSTEM;alter user APPQOSSYS default tablespace SYSAUX;alter user XDB default tablespace SYSAUX;exit