XTTS数据迁移方案

SQL> select dbtimezone from dual;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> set linesize 120

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';

 select 'opatch',comments from dba_registry_history;

 SQL> Select comp_name from dba_registry;

 Select index_name,table_name from dba_indexes where compression='ENABLED';

SQL> select table_name, owner, tablespace_name from dba_tables where tablespace_name not in('SYSTEM','SYSAUX') and owner in ('SYS','SYSTEM');

SQL> show parameter compatible;

SQL> show parameter recyclebin;

SQL> alter system set recyclebin='off' scope=spfile;

SQL> Select distinct status from v$datafile;

SQL> col PLATFORM_NAME format a50

SQL> select * from v$transportable_platform;

 pwd

mkdir xtts

ls

cd xtts/

ll

mkdir source_back

mkdir source_incr_back

mkdir target_data

mkdir target_back

mkdir target_incr_back

unzip rman_xttconvert_v3.zip

[oracle@source xtts]$ ls

su

chmod -R 775 xtts/

ll

tablespaces=MYTBS      # 表示需要迁移的表空间

platformid=13    # 表示源OS平台ID

#srclink=TTSLINK   #表示从目标库指向源库的db_link,由于这里采用的是rman方式，所以不需要db_link

dfcopydir=/u01/app/oracle/xtts/source_back #表示源库备份文件存放目录

backupformat=/u01/app/oracle/xtts/source_incr_back #表示源库增备文件存放目录

stageondest=/u01/app/oracle/xtts/target_back #表示目标库备份文件存放目录

storageondest=/u01/app/oracle/xtts/target_data  #表示目标库正式文件存放目录

backupondest=/u01/app/oracle/xtts/target_incr_back #表示目标库增备文件存放目录

parallel=3  #表示备份转化并行度

rollparallel=2 #表示增备的并行度

getfileparallel=4  #表示使用 dbms_file_transfer 方式的并行度

scp -rp /u01/app/oracle/xtts/ root@192.168.238.21:/u01/app/oracle/xtts

su - oracle

vi .bash_profile

export TMPDIR=/u01/app/oracle/xtts

source .bash_profile

SQL> archive log list

目标端修改为归档模式

SQL> alter system switch logfile;

SQL> archive log list

cd /u01/app/oracle/xtts/

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/xtts/xttdriver.pl -p

[oracle@oramig source_back]$  scp -p /u01/app/oracle/xtts/source_back/*  root@192.168.238.21:/u01/app/oracle/xtts/target_back、

目标端给权限

scp -p rmanconvert.cmd root@192.168.238.21:/u01/app/oracle/xtts/

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/xtts/xttdriver.pl -c

转换成功之后会生成 xttnewdatafiles.txt文件。

SQL> alter tablespace MYTBS read only;

Pwd

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/xtts/xttdriver.pl -i

cd source_incr_back/

Ls

scp -p 0f2ntcso_1_1 root@192.168.238.21:/u01/app/oracle/xtts/target_back

cd ..

scp -p xttplan.txt tsbkupmap.txt root@192.168.238.21:/u01/app/oracle/xtts

目标端修改权限

$ORACLE_HOME/perl/bin/perl /u01/app/oracle/xtts/xttdriver.pl -r

mkdir -p /u01/app/oracle/data

SQL> create directory source_dbdata as '/u01/app/oracle/data';

SQL> grant read,write on directory source_dbdata to system;

SQL> select * from dba_directories;

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('MYTBS', TRUE);

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

cd //u01/app/oracle/data

expdp system/123456 directory=source_dbdata dumpfile=MYTBS.dmp transport_tablespaces=MYTBS transport_full_check=y

ls

scp -p /u01/app/oracle/data/MYTBS.dmp root@192.168.238.21:/u01/app/oracle/data

创建zgc用户，但是不要创建表空间

SQL> create user zgc identified by 123456;

SQL> grant dba to zgc;

SQL> create directory t_dbdata as '/u01/app/oracle/data';

SQL> grant read,write on directory t_dbdata to zgc;

SQL> grant exp_full_database,imp_full_database to zgc;

目标端导入元数据

pwd

impdp zgc/123456 DUMPFILE=MYTBS.dmp DIRECTORY=t_dbdata TRANSPORT_DATAFILES=/u01/app/oracle/xtts/target_data/MYTBS_8.dbf ,/u01/app/oracle/xtts/target_data/MYTBS_9.dbf 、

SQL> alter user zgc default tablespace MYTBS;

更改表空间zgc状态为读写

SQL> ALTER TABLESPACE MYTBS read write;

源端

目标端

system 用户没设置密码 设置一下就行了

目标端创建zgc用户时

SELECT DEFAULT_TABLESPACE FROM USER_TABLESPACES WHERE USERNAME = ‘zgc’;

说无效的用户名

解决方法：

ALTER SESSION SET "_oracle_script"=true;