XTTS+Rman迁移

Phase 1 - Initial Setup phase

1. Install the desired oracle database software (oracle version:11.2.0.4 and later) on the destination system

2. From the source database, check its characterset, sga, processes, hidden parameters .etc for destination instance creation:

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER like '%CHARACTERSET%';

set lines 400;

col name for a30;

col value for a10;

select

x.ksppinm  name,

y.ksppstvl  value,

y.ksppstdf  isdefault,

decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,

decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj

from

sys.x$ksppi x,

sys.x$ksppcv y

where

x.inst_id = userenv('Instance') and

y.inst_id = userenv('Instance') and

x.indx = y.indx   and y.ksppstdf='FALSE'  

order by translate(x.ksppinm, ' _', ' ');

3. Use dbca to create database on destination system according to the parameters in step 2 and config the backup and clear archive log scripts

4. Create listener for new instance and copy tnsname.ora, sqlnet.ora from source to destination . In destination system, add a tns item for destination to connect source

CC_XTTS =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.45.7.128)(PORT = 1521))

  (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = cc)

  )

)

5. Create a database link and a dump directory in the destination database

create public database link ttslink connect to system identified by *** using 'CC_XTTS';

create directory  export as '/oracle12/backup';

6. Config the .profile or .bash_profile with the proper ORACLE_HOME,ORACLE_SID and TMPDIR (TMPDIR is for XTTS temp files and logs) on both source and destination system

eg: export TMPDIR=/home/oracle/tmp

7. From source database , get platform_id for setting xtt.properties  

select platform_id from V$DATABASE;

8. Identify and check tablespaces to be transported, the to-be transported tablespaces must self-contained from the source system:

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TEST,IDX_TEST', TRUE);

select * from TRANSPORT_SET_VIOLATIONS; --no records is expected

9. Check the detailed configuration for the to-be transported user—TEST

select username from dba_users where default_tablespace in('TBS_TEST','IDX_TEST');

SELECT * FROM dba_ts_quotas where username='TEST';

select granted_role from dba_role_privs where grantee='TEST';

select privilege from dba_sys_privs where grantee='TEST';

select USER#,NAME,PASSWORD,CTIME,EXPTIME,LTIME,RESOURCE$,ASTATUS from user$ where NAME='TEST';

10. Create user test with identified by values ‘’ on the destination database

11. 由于 XTTS 最后导入元数据时候不支持临时表，所以需要提前查出系统临时表信息。

select dbms_metadata.get_ddl('TABLE',TABLE_NAME,owner) from dba_tables where  TEMPORARY='Y' and owner=”TEST’;

Phase 2 – Install xttconvert Scripts  

1. Create directories for xtts scripts ,backup files and tmp on both source and destination system

cd /home/oracle

mkdir xtts

mkdir tmp

mkdir backup

2. Unzip rman_xttconvert_v3.zip and Config the xtt.properties on source which is guided file for transport tablespace

 oracle@rac3[/home/oracle/xtts]$grep -v "^#" xtt.properties|grep -v "^$"

tablespaces=TBS_TEST,IDX_TEST   # the tablespaces

platformid=13                   # the platform_id of source system

dfcopydir=/home/oracle/backup    # location of initial datafile copies on source

backupformat=/home/oracle/backup #d incremental backup location on source stageondest=/oracle12/backup    #the datafile copies are placed on destination  

storageondest=/oracle12/oradata/cc_new #the final location of the datafiles in database

backupondest=/oracle12/backup   # incremental backup location on destination

parallel=3

rollparallel=2

getfileparallel=4

3. Scp the xtts scripts to destination  

scp -r xtts/ oracle12@172.16.23.171:/oracle12

Phase 3 Prepare Phase for RMAN Backup Method

1. Make a full backup for source database

cd /home/oracle/xtts

$ORACLE_HOME/perl/bin/perl xttdriver.pl -p

There will be datafile copies under /home/oracle/backup and there are two tmp files generated under /home/oracle/tmp

xttplan.txt

rmanconvert.cmd

2. Transfer Datafile Copies and generated command file to the Destination System

cd /home/oracle/backup

scp TBS_TEST_5.tf IDX_TEST_6.tf oracle12@172.16.23.171:/oracle12/backup

scp /home/oracle/tmp/rmanconvert.cmd oracle12@172.16.23.171:/oracle12/tmp

As transference of datafile copies consumes lots of time, we can create a shared disk between source and destination by mount/umount the shared disk so that we can save time of transferring datafile copies  

3. Convert the Datafile Copies on the Destination System

$ORACLE_HOME/perl/bin/perl xttdriver.pl -c

Phase 4 - Roll Forward Phase

1. Create an Incremental Backup of the Tablespaces on source

cd /home/oracle/xtts

$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

There will be incremental backups under /home/oracle/backup and there are two tmp files generated under /home/oracle/tmp

tsbkupmap.txt

incrbackups.txt

2. Transfer Incremental backups and two generated tmp files to the Destination System(No need if umount/mount can be used

scp `cat /home/oracle/tmp/incrbackups.txt` oracle12@172.16.23.171:/oracle12/backup

scp /home/oracle/tmp/xttplan.txt /home/oracle/tmp/tsbkupmap.txt oracle12@172.16.23.171:/oracle12/tmp

3. Convert the Incremental Backup and apply to the Datafile Copies on the destination

cd /oracle12/xtts

$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

4. Determine the FROM_SCN for the Next Incremental Backup on source

$ORACLE_HOME/perl/bin/perl xttdriver.pl -s

Phase 5 - Transport Phase

0. Repeat Phase 4 if large data gap existed, otherwise, stop the application and make the final incremental backup as below

1. Make the tablespaces read only on source

alter tablespace TBS_TEST read only

alter tablespace IDX_TEST read only;

select status ,tablespace_name from dba_tablespaces ;

2. Create the Final Incremental Backup, Transfer and apply them to datafile copies just like steps 1,2,3 in Phase 4

source:

cd /home/oracle/xtts

$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

scp `cat /home/oracle/tmp/incrbackups.txt` oracle12@172.16.23.171:/oracle12/backup

scp /home/oracle/tmp/xttplan.txt  /home/oracle/tmp/tsbkupmap.txt oracle12@172.16.23.171:/oracle12/tmp

destination:

cd /oracle12/xtts

$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

3. Generate an impdp command for transport tablespaces on destination

$ORACLE_HOME/perl/bin/perl xttdriver.pl -e

There will be a xttplugin.txt under /oracle12/tmp

oracle12@shard22[/oracle12/tmp]$cat xttplugin.txt

impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \

network_link=<ttslink> transport_full_check=no \

transport_tablespaces=TBS_TEST,IDX_TEST \

transport_datafiles='/oracle12/oradata/cc_new/TBS_TEST_5.dbf','/oracle12/oradata/cc_new/IDX_TEST_6.dbf'

we fill in the command by replace <DATA_PUMP_DIR>,<tts_imp.log>,<ttslink> and add STATISTICS=none

The final impdp command as below and run it on destination

impdp system/**** directory=export logfile=20190905_tts_tbs_idx_test.log \

network_link=ttslink transport_full_check=no STATISTICS=none \

transport_tablespaces=TBS_TEST,IDX_TEST \

transport_datafiles='/oracle12/oradata/cc_new/TBS_TEST_5.dbf','/oracle12/oradata/cc_new/IDX_TEST_6.dbf'

4. Execute an impdp command to import metadata (including procedures, views,dblinks… )

impdp system/*** directory=export logfile=20190905_tts_others_test.log network_link=ttslink CONTENT=metadata_only exclude=statistics SCHEMAS=TEST’

5. Compare several tables from source and destination and run some remaining commands

select count(*) from test.TEST1;

select count(*) from test.TEST2;

select count(*) from test.TEST3;

select count(*) from test.TEST4;

select count(*) from test.TEST5;

 select count(*),object_type from dba_objects where owner='TEST' group by object_type;

select count(*)  from dba_objects where owner='TEST' and status='INVALID';

destination:

select status ,tablespace_name from dba_tablespaces ;

alter user test default tablespace tbs_test  quota unlimited on tbs_test quota unlimited on idx_test;

compile the invalid objects  

@?/rdbms/admin/utlrp.sql

6. Validate the Transported Data

RMAN> validate tablespace TBS_TEST, IDX_TEST check logical;

7. Make tablespaces read write  

alter tablespace TBS_TEST read write;

alter tablespace IDX_TEST read write;

8. Restart the destination database and switch to archivelog mode

Shutdown immediate

Startup mount

Alter database archivelog;

Alter database open;

9. Collecting statistics for TEST schema

exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'TEST',estimate_percent=>1,method_opt=>'FOR ALL INDEXED COLUMNS',degree=>10,cascade=>true,options=>'GATHER AUTO');

Phase 6 Start application and test

If testing all good, we can use the new environment;  

If not, just make the source tablespaces read write and use the old databas