环境情况
源库:
os:Centos 7
db版本:12.2.0.1
endianness格式: little
当前运行模式:单机
目的库:
os:Centos 7
db版本:18.3.0.0
endianness格式: little
当前运行模式:单机(带dataguard,单机->2节点的rac)
1.查看操作系统endianness格式
源端:
SELECT d.PLATFORM_NAME,
ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit Little
目标端:
SQL> set linesize 1000;
SQL> SELECT d.PLATFORM_NAME,
2 ENDIAN_FORMAT
3 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
4 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit Little
2.在源端和目标端创建backup的目录
源端:
[oracle@localhost ~]$ mkdir -p /home/oracle/backup
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 15 15:50:34 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA12CPDB1 READ WRITE NO
4 ORA12CPDB2 READ WRITE NO
6 ORA12CPDB4 READ WRITE NO
7 ORA12CPDB5 READ WRITE YES
SQL> alter session set container=ORA12CPDB4;
Session altered.
SQL> create directory backup as '/home/oracle/backup';
Directory created.
SQL> grant read, write on directory backup to hxl;
Grant succeeded.
目标端:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 READ WRITE NO
7 PDB5 READ WRITE NO
8 PDB6 READ WRITE NO
9 PDB7 READ WRITE NO
10 PDB8 READ WRITE NO
SQL> alter session set container=pdb4;
Session altered.
SQL> create directory backup as '/home/oracle/backup';
Directory created.
SQL> grant read, write on directory backup to hxl;
Grant succeeded.
3.检查表空间自包含(就是改表空间里的数据没有和其他表空间数据有关联,如果有关联会报错)
源端操作
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORA12CPDB4 READ WRITE NO
SQL> execute dbms_tts.transport_set_check('TPS_HXL', TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39907: Index HXL.IDX_N2 in tablespace TPS_HXL points to table HXL.TB_TEST_CP in tablespace SYSTEM.
ORA-39907: Index HXL.UNIQ_IDX_N1 in tablespace TPS_HXL points to table HXL.TB_TEST_CP in tablespace SYSTEM.
这里有输出,说明该表空间的里的对象用到了另外的表空间
检查TB_TEST_CP对象所在的表空间
SQL> Select tablespace_name From dba_segments Where segment_name='TB_TEST_CP';
TABLESPACE_NAME
------------------------------
SYSTEM
将该对象迁移到TPS_HXL表空间
SQL> alter table hxl.tb_test_cp move tablespace TPS_HXL;
Table altered.
再次检查
SQL> execute dbms_tts.transport_set_check('TPS_HXL', TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
没有输出,说明自检查已经通过了.
4.将表空间TPS_HXL设置成read only --no done
在源端将表空间TPS_HXL设置为只读模式
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORA12CPDB4 READ WRITE NO
SQL> alter tablespace tps_hxl read only;
Tablespace altered.
不设置只读也可以做表空间传输，但是会导致数据不一致
5.expdp导出的表空间源数据
源库导出语句:
expdp hxl/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
ORA12CPDB4是连接到ORA12CPDB4的tns名称,如下所示:
ORA12CPDB4 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora12cpdb4)
)
)
[oracle@localhost admin]$ expdp hxl/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
Export: Release 12.2.0.1.0 - Production on Tue Jun 15 16:37:43 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "HXL.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1161
ORA-01647: tablespace 'TPS_HXL' is read-only, cannot allocate space in it
ORA-06512: at "SYS.KUPV$FT", line 1054
ORA-06512: at "SYS.KUPV$FT", line 1042
这里错误,因为hxl用户使用的表空间是tps_hxl,换成system用户执行
expdp system/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
[oracle@localhost backup]$ expdp system/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
Export: Release 12.2.0.1.0 - Production on Wed Jun 16 15:17:36 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/backup/TPS_HXL.dmp
******************************************************************************
Datafiles required for transportable tablespace TPS_HXL:
/u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl01.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jun 16 15:18:47 2021 elapsed 0 00:01:10
6.将源端导出的元数据文件和表空间TPS_HXL对应的数据文件拷贝到目标端
scp /home/oracle/backup/TPS_HXL.dmp 192.168.56.113:/home/oracle/backup
查看表空间对应的数据文件
SQL> Select file_name From Dba_Data_Files Where tablespace_name='TPS_HXL';
FILE_NAME
---------------------------------------------------
/u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl01.dbf
这里该表空间只有一个数据文件,若是有多个数据文件,需要全部拷贝到目的端
scp /u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl01.dbf 192.168.56.113:/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/
scp /u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl02.dbf 192.168.56.113:/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/
源端数据文件拷贝到目的端后可以将源端的表空间设置为读写了
alter tablespace TPS_HXL read write;
注意拷贝过去后需要修改权限为oracle用户,我这里是将这些文件都放在backup目录下了
[root@18c_single oracle]# chown -R oracle:oinstall /home/oracle/backup/
拷贝到数据文件目录
[oracle@18c_single backup]$ cp /home/oracle/backup/hxl01.dbf /u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/
[oracle@18c_single backup]$ cp /home/oracle/backup/hxl02.dbf /u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/
7.在目标端系统上import表空间的metadata(使用hxl用户,若用户不相同需要用到remap_schema)
impdp hxl/oracle@tnspdb4 directory=backup dumpfile=TPS_HXL.dmp transport_datafiles=/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/hxl01.dbf,/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/hxl02.dbf logfile=TPS_HXL.log
8.查看并修改表空间状态
目的端执行将表空间online
alter tablespace TPS_HXL read write;
我这里主库完成表空间传输之后发现备库报错误：
备库报错误:
Recovered data files to a consistent state at change 217332196
2021-06-15T21:44:41.891799-04:00
stopping change tracking
2021-06-15T21:44:41.916683-04:00
Errors in file /u01/oracle/app/diag/rdbms/slnngk/slnngk1/trace/slnngk1_mrp0_24725.trc:
ORA-01157: cannot identify/lock data file 64 - see DBWR trace file
ORA-01110: data file 64: '+DATA/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/hxl01.dbf'
2021-06-15T21:44:41.916793-04:00
Background Media Recovery process shutdown (slnngk1)
2021-06-15T21:44:47.688409-04:00
Process termination requested for pid 27907 [source = rdbms], [info = 2] [request issued by pid: 23610, uid: 1000]
解决办法,若该表空间有多个数据文件，逐一执行如下的命令拷贝到备库
1.主库备份该文件
RMAN> backup as copy datafile 64 format '/home/oracle/hxl01.dbf';
2.拷贝到目的端
scp /home/oracle/hxl01.dbf oracle@192.168.56.111:/home/oracle/
3.asmcmd(grid用户)
[grid@rac01 ~]$ asmcmd
ASMCMD> cp /home/oracle/hxl01.dbf +DATA/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/
copying /home/oracle/hxl01.dbf -> +DATA/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/hxl01.dbf
4.重新应用日志(oracle账号)
SQL> alter database recover managed standby database using current logfile disconnect from session;