转载

oracle xtts数据库迁移方法测试之一

测试结论
由于步骤过多，具体过程见下

测试思路
oracle xtts数据库迁移方法测试之一

测试明细

1，源与目标数据库的概况

编号数据库类型数据库名称数据库版本数据库IP地址操作系统平台
1 oracle单实例 mygirl 11.2.0.4 10.0.0.5 redhat 6.5

2 oracle单实例 esbdb 11.2.0.4 10.0.0.39 suse 11

2，创建源端数据库的测试表空间及测试用户并创建测试表和插入数据
SQL> set linesize 300
SQL> col name for a50
SQL> select file#,name from v$datafile
2 ;

FILE# NAME
---------- --------------------------------------------------
1 /oracle/mygirl/system01.dbf
2 /oracle/mygirl/sysaux01.dbf
3 /oracle/mygirl/undotbs01.dbf
4 /oracle/mygirl/users01.dbf
5 /oracle/admin/mygirl/dpdump/tbs_father01.dbf
6 /oracle/admin/mygirl/dpdump/tbs_mother01.dbf
7 /oracle/admin/mygirl/dpdump/tbs_sun01.dbf
8 /oracle/mygirl/tbs_zxy01.dbf
9 /oracle/mygirl/tbs_obj01.dbf
10 /oracle/mygirl/tbs_wife01.dbf

10 rows selected.

SQL> create tablespace tbs_dead datafile '/oracle/mygirl/tbs_dead01.dbf' size 10m;

Tablespace created.

SQL> create user user_dead identified by system account unlock default tablespace tbs_dead;

User created.

SQL> grant resource,connect to user_dead;

Grant succeeded.

SQL> conn user_dead/system
Connected.

SQL> create table t_test(a int);

Table created.

SQL> insert into t_test values(1);

1 row created.

SQL> commit;

Commit complete.

3，在源端数据库对data file 11进行data file copy
SQL> conn /as sysdba
Connected.
SQL> select file#,name from v$datafile where file#=11;

FILE# NAME
---------- --------------------------------------------------
11 /oracle/mygirl/tbs_dead01.dbf

[oracle@mygirl ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 15 19:21:58 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: MYGIRL (DBID=2527996082)

RMAN> backup as copy datafile 11 tag 'datafile 11 copy note' format '/home/oracle/tbs_dead01.copy';

Starting backup at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
output file name=/home/oracle/tbs_dead01.copy tag=DATAFILE 11 COPY NOTE RECID=16 STAMP=946754806
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

4,传输源端数据库测试表空间DATA FILE COPY到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/tbs_dead01.copy oracle@10.0.0.39:/home/oracle
Password:
tbs_dead01.copy 100% 10MB 10.0MB/s 00:01
[oracle@mygirl ~]$

5，在目标端数据库对自源端数据库传输过来的测试表空间DATA FILE COPY进行字节序转换
---源端
SQL> set linesize 300
SQL> r
1* select platform_id,platform_name,endian_format from v$transportable_platform

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little

20 rows selected.

---目标端

oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll tbs_dead01.copy
-rw-r----- 1 oracle oinstall 10493952 2017-06-15 19:27 tbs_dead01.copy
oracle@suse11:~> rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 15 19:29:46 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ESBDB (DBID=3277468929)

RMAN> convert datafile '/home/oracle/tbs_dead01.copy' format '/home/oracle/tbs_dead01.dbf' from platform 'Linux x86 64-bit';

Starting conversion at target at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tbs_dead01.copy
converted datafile=/home/oracle/tbs_dead01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 15-JUN-17

RMAN>

6,在源端数据库查询测试表空间data file copy的SCN
SQL> conn /as sysdba
Connected.
SQL> set linesize 300
SQL> col name for a50
SQL> select recid,name,file#,incremental_level,checkpoint_time,checkpoint_change# from v$datafile_copy where file#=11;

RECID NAME FILE# INCREMENTAL_LEVEL CHECKPOIN CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- ----------------- --------- ------------------
16 /home/oracle/tbs_dead01.copy 11 15-JUN-17 6065041

7,在源端数据库对测试表空间进行数据库事务变化
SQL> conn user_dead/system
Connected.
SQL> insert into t_test values(2);

1 row created.

SQL> commit;

Commit complete.

8,在源端数据库基于上述的DATA FILE COPY的SCN进行增量RMAN备份

RMAN> backup incremental from scn 6065041 datafile 11 format '/home/oracle/incr_datafile11_first_bak_6065041_%u_%d.bak' tag='datafile11_first_incr_bak';

Starting backup at 15-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak tag=DATAFILE11_FIRST_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_first_bak_6065041_21s6slnj_MYGIRL.bak tag=DATAFILE11_FIRST_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

9,在源端数据库获取最新的SCN用于下次增量RMAN备份的基础
SQL> select file#,checkpoint_change# from v$datafile where file#=11;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
11 6065307

10,传输源端上述的增量RMAN备份到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak oracle@10.0.0.39:/home/oracle
Password:
incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak 100% 48KB 48.0KB/s 00:00
[oracle@mygirl ~]$

11,在目标端数据库对自源端数据库传输过来的增量RMAN备份集进行字节序转换

oracle@suse11:~> ls -l /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak
-rw-r----- 1 oracle oinstall 49152 2017-06-15 21:52 /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak
oracle@suse11:~>

oracle@suse11:~> sqlplus '/as sysdba'

DECLARE
handle varchar2(512);
comment varchar2(80);
media varchar2(80);
concur boolean;
recid number;
stamp number;
pltfrmfr number;
devtype VARCHAR2(512);
BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(bpname => '/home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak',fname => '/home/oracle/first_incr_bak.bak',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=>13);
END;
END;
/

PL/SQL procedure successfully completed.

oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll first_incr_bak.bak
-rw-r----- 1 oracle oinstall 49152 2017-06-15 21:56 first_incr_bak.bak

12,在目标端数据库对上述的DATA FILE COPY以及初次的增量RMAN备份集进行前滚应用

set serveroutput on;
DECLARE
outhandle varchar2(512) ;
outtag varchar2(30) ;
done boolean ;
failover boolean ;
devtype VARCHAR2(512);
BEGIN

DBMS_OUTPUT.put_line('Entering RollForward');

-- Now the rolling forward.
devtype := sys.dbms_backup_restore.deviceAllocate;

sys.dbms_backup_restore.applySetDatafile(
check_logical => FALSE, cleanup => FALSE) ;

DBMS_OUTPUT.put_line('After applySetDataFile');

sys.dbms_backup_restore.applyDatafileTo(
dfnumber => 11,
toname => '/home/oracle/tbs_dead01.dbf',
fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,
recid => 0, stamp => 0);

DBMS_OUTPUT.put_line('Done: applyDataFileTo');

DBMS_OUTPUT.put_line('Done: applyDataFileTo');

-- Restore Set Piece
sys.dbms_backup_restore.restoreSetPiece(
handle => '/home/oracle/first_incr_bak.bak',
tag => null, fromdisk => true, recid => 0, stamp => 0) ;

DBMS_OUTPUT.put_line('Done: RestoreSetPiece');

-- Restore Backup Piece
sys.dbms_backup_restore.restoreBackupPiece(
done => done, params => null, outhandle => outhandle,
outtag => outtag, failover => failover);

DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;

/

Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

13,继续在源端数据库对测试表空间进行数据库事务变化
[oracle@mygirl ~]$ sqlplus user_dead/system

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:00:39 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t_test values(3);

1 row created.

SQL> commit;

Commit complete.

14,在源端数据库配置测试表空间为READ ONLY
(注：现在就是真正开始停机的时间)
SQL> conn /as sysdba
Connected.
SQL> alter tablespace tbs_dead read only;

Tablespace altered.

15,在源端数据库基于上述的SCN进行增量RMAN备份
[oracle@mygirl ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 15 22:02:44 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: MYGIRL (DBID=2527996082)

RMAN> backup incremental from scn 6065307 datafile 11 format '/home/oracle/incr_datafile11_end_bak_6065041_%u_%d.bak' tag='datafile11_end_incr_bak';

Starting backup at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak tag=DATAFILE11_END_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_end_bak_6065041_23s6suc8_MYGIRL.bak tag=DATAFILE11_END_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

16,传输源端上述的RMAN备份到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak oracle@10.0.0.39:/home/oracle
Password:
incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak 100% 48KB 48.0KB/s 00:00
[oracle@mygirl ~]$

17,在目标端数据库对自源端数据库传输过来的RMAN增量备份集进行字节序转换
DECLARE
handle varchar2(512);
comment varchar2(80);
media varchar2(80);
concur boolean;
recid number;
stamp number;
pltfrmfr number;
devtype VARCHAR2(512);
BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(bpname => '/home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak',fname => '/home/oracle/end_incr_bak.bak',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=>13);
END;
END;
/

PL/SQL procedure successfully completed.

18,在目标端数据库对上述的增量RMAN备份进行前滚应用
set serveroutput on;
DECLARE
outhandle varchar2(512) ;
outtag varchar2(30) ;
done boolean ;
failover boolean ;
devtype VARCHAR2(512);
BEGIN

DBMS_OUTPUT.put_line('Entering RollForward');

-- Now the rolling forward.
devtype := sys.dbms_backup_restore.deviceAllocate;

sys.dbms_backup_restore.applySetDatafile(
check_logical => FALSE, cleanup => FALSE) ;

DBMS_OUTPUT.put_line('After applySetDataFile');

sys.dbms_backup_restore.applyDatafileTo(
dfnumber => 11,
toname => '/home/oracle/tbs_dead01.dbf',
fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,
recid => 0, stamp => 0);

DBMS_OUTPUT.put_line('Done: applyDataFileTo');

DBMS_OUTPUT.put_line('Done: applyDataFileTo');

-- Restore Set Piece
sys.dbms_backup_restore.restoreSetPiece(
handle => '/home/oracle/end_incr_bak.bak',
tag => null, fromdisk => true, recid => 0, stamp => 0) ;

DBMS_OUTPUT.put_line('Done: RestoreSetPiece');

-- Restore Backup Piece
sys.dbms_backup_restore.restoreBackupPiece(
done => done, params => null, outhandle => outhandle,
outtag => outtag, failover => failover);

DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;

Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

19,在源端数据库导出测试表空间的元数据
[oracle@mygirl ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:14:09 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col directory_name for a50
SQL> col directory_path for a50
SQL> set linesize 300
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------- --------------------------------------------------
XMLDIR /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
DATA_PUMP_DIR /oracle/admin/mygirl/dpdump/
ORACLE_OCM_CONFIG_DIR2 /oracle/product/11.2.0/db_1/ccr/state

[oracle@mygirl ~]$ expdp /'sys/system as sysdba/' dumpfile=expdp_tbs_dead.dmp directory=data_pump_dir transport_tablespaces=tbs_dead logfile=expdp_tbs_dead.log

Export: Release 11.2.0.4.0 - Production on Thu Jun 15 22:16:21 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=expdp_tbs_dead.dmp directory=data_pump_dir transport_tablespaces=tbs_dead logfile=expdp_tbs_dead.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/oracle/admin/mygirl/dpdump/expdp_tbs_dead.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS_DEAD:
/oracle/mygirl/tbs_dead01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jun 15 22:17:06 2017 elapsed 0 00:00:44

[oracle@mygirl ~]$

20,传输源端导出的测试表空间元数据到目标端数据库对应目录

---目标端
oracle@suse11:~> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:18:25 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col directory_name for a50
SQL> col directory_path for a50
SQL> set linesize 300
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------- --------------------------------------------------
XMLDIR /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR /oracle/product/11.2.0/db_1/ccr/hosts/suse11/state
DATA_PUMP_DIR /oracle/admin/esbdb/dpdump/
ORACLE_OCM_CONFIG_DIR2 /oracle/product/11.2.0/db_1/ccr/state

---源端
[oracle@mygirl ~]$ scp /oracle/admin/mygirl/dpdump/expdp_tbs_dead.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump
Password:
expdp_tbs_dead.dmp 100% 88KB 88.0KB/s 00:00
[oracle@mygirl ~]$

21,在目标端数据库基于源端测试用户及角色构建测试用户
oracle@suse11:~> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:20:54 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user user_dead identified by system account unlock;

User created.

SQL> grant resource,connect to user_dead;

Grant succeeded.

22,在目标端数据库导入测试表空间

oracle@suse11:~> impdp /'/as sysdba/' directory=DATA_PUMP_DIR transport_datafiles='/home/oracle/tbs_dead01.dbf' dumpfile=expdp_tbs_dead.dmp logfile=impdp_tbs_dead.log

Import: Release 11.2.0.4.0 - Production on Thu Jun 15 22:23:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR transport_datafiles=/home/oracle/tbs_dead01.dbf dumpfile=expdp_tbs_dead.dmp logfile=impdp_tbs_dead.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Jun 15 22:23:39 2017 elapsed 0 00:00:07

oracle@suse11:~>

23,在目标端数据库验证迁移表空间是否导入成功
oracle@suse11:~> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:24:07 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_DEAD

6 rows selected.

SQL> col name for a50

SQL> r
1* select file#,name from v$datafile

FILE# NAME
---------- --------------------------------------------------
1 /oracle/esbdb/system01.dbf
2 /oracle/esbdb/sysaux01.dbf
3 /oracle/esbdb/undotbs01.dbf
4 /oracle/esbdb/users01.dbf
5 /home/oracle/tbs_dead01.dbf

24,在目标端数据库变更迁移表空间为read write以及变更测试用户的默认表空间为迁移表空间
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS_DEAD READ ONLY

6 rows selected.

SQL> alter tablespace tbs_dead read write;

Tablespace altered.

SQL> select username,default_tablespace from dba_users where username='USER_DEAD';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
USER_DEAD USERS

SQL> alter user user_dead default tablespace tbs_dead;

User altered.

25,在目标端数据库验证迁移表空间的数据一致性
SQL> conn user_dead/system
Connected.
SQL> select * from t_test;

A
----------
2
1
3

26,在源端和目标端导出导入其它的数据库特殊对象类型比如:trigger,sequence
请参考旧文如下：

20170609星期五之如何基于oracle 11.2.0.4数据库版本在数据库间迁移非表特殊对象类比如序列及存储过程测试明细

27，在目标端数据库启动数据库监听器
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JUN-2017 22:31:33

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
oracle@suse11:~> lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JUN-2017 22:31:35

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Starting /oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 15-JUN-2017 22:31:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
The listener supports no services
The command completed successfully
oracle@suse11:~> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:31:43 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JUN-2017 22:31:50

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 15-JUN-2017 22:31:35
Uptime 0 days 0 hr. 0 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
Services Summary...
Service "esbdb" has 1 instance(s).
Instance "esbdb", status READY, has 1 handler(s) for this service...
Service "esbdbXDB" has 1 instance(s).
Instance "esbdb", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@suse11:~>

28,通知应用厂商连接数据库进行业务验证

29，确认业务运行正常后，清除目标端XTTS相关的临时文件
oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll
total 20772
drwxr-xr-x 3 oracle oinstall 4096 2015-12-28 19:02 11204software
drwxr-xr-x 2 oracle oinstall 4096 2017-06-08 19:40 after_diff_dir
drwxr-xr-x 2 oracle oinstall 4096 2017-06-07 18:33 db_full_bak
drwxr-xr-x 2 oracle oinstall 4096 2017-05-29 14:06 Desktop
drwxr-xr-x 2 oracle oinstall 4096 2017-06-09 00:51 dest_convert_dir
drwx------ 2 oracle oinstall 4096 2017-05-29 14:07 Documents
-rw-r----- 1 oracle oinstall 49152 2017-06-15 22:05 end_incr_bak.bak
drwxr-xr-x 2 oracle oinstall 4096 2017-06-08 19:47 ending_dir
drwxr-xr-x 2 oracle oinstall 4096 2017-06-08 19:34 every_dir
-rw-r----- 1 oracle oinstall 49152 2017-06-15 21:56 first_incr_bak.bak
drwxr-xr-x 2 oracle oinstall 4096 2017-06-12 02:04 from_incr_dir
drwxr-xr-x 2 oracle oinstall 4096 2017-06-11 22:30 from_source_datafilecopy_dir
-rw-r----- 1 oracle oinstall 49152 2017-06-15 22:04 incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak
-rw-r----- 1 oracle oinstall 49152 2017-06-15 21:52 incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak
-rw-r----- 1 oracle oinstall 10493952 2017-06-15 19:27 tbs_dead01.copy
-rw-r----- 1 oracle oinstall 10493952 2017-06-15 22:27 tbs_dead01.dbf
drwxr-xr-x 2 oracle oinstall 4096 2017-06-09 00:03 temp_dir
-rw-r--r-- 1 oracle oinstall 687 2017-06-07 18:42 use_des.ora
drwxr-xr-x 2 oracle oinstall 4096 2017-06-09 00:27 xtts_convert_dir
oracle@suse11:~> rm -Rf first_incr_bak.bak end_incr_bak.bak incr_datafile11_* tbs_dead01.copy
oracle@suse11:~>

正文到此结束