环境:
|
原 库 |
目的库 |
||
|
Db版本 |
ip |
Db版本 |
ip |
|
11.2.0.4 |
192.168.1.85 |
12.2.0.1 新创建pdb为: ORA12CPDB1 |
192.168.1.134 |
1.1 表空间传输介绍
表空间传输的步骤需要将表空间设置为只读模式下拷贝数据文件，但是生产环境下不允许将表空间设置为只读，这里我们采用rman的transport tablespace将表空间的文件导出来，然后拷贝到目的机器进行导入
1.2 检查字节顺序
原库(11g)
|
SQL> set linesize 1000 SQL> column PLATFORM_NAME format a64 SQL> column ENDIAN_FORMAT format a16 SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------------------------------- ---------------- Linux x86 64-bit Little |
目的库(12c pdb)
|
SQL> set linesize 1000 SQL> column PLATFORM_NAME format a64 SQL> column ENDIAN_FORMAT format a16 SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------------------------------- ---------------- Linux x86 64-bit Little |
这里的字节顺序一致不需要转换
1.3 检查字符集
原库:
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
目的库:
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
1.4 检查Compatible参数
原库:
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
目的库:
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
1.5 源端创建表空间(用户、表以及测试数据)
查看当前的数据文件分布
|
SQL> column file_name format a64 SQL> column tablespace_name format a16 SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------- ---------------- /u01/app/oracle/oradata/slnngkdg/users01.dbf USERS /u01/app/oracle/oradata/slnngkdg/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/slnngkdg/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/slnngkdg/system01.dbf SYSTEM /u01/app/oracle/oradata/slnngkdg/tps_goldengate01.dbf TPS_GOLDENGATE |
|
SQL> connect / as sysdba Connected. SQL> create tablespace tps_hxl datafile '/u01/app/oracle/oradata/slnngkdg/tps_hxl01.dbf' size 100m autoextend on; SQL> create user hxl identified by oracle default tablespace tps_hxl account unlock; SQL> grant resource,connect to hxl; SQL> connect hxl/oracle Connected. SQL> create table tb_test01 as select * from hxl01.tb_test01; ##(这里hxl需要有访问该表权限) Table created. SQL> select count(1) from tb_test01; COUNT(1) ---------- 790000 |
1.6 源端表空间自包含(独立性)检查
|
SQL> connect / as sysdba Connected. SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TPS_HXL',TRUE,TRUE); PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; no rows selected |
查询无返回结果说明检查通过，否则需要根据violation字段的说明解决各类参照完整性问题，比如说主键、外键约束、分区等问题，一般这些问题是指对象不在同一表空间.
1.7 创建目录原库
目的pdb下创建
Os创建目录
mkdir -p /u01/dumpdir
|
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 SQL> alter session set container=ORA12CPDB1; Session altered. SQL> create directory datapump_dir as '/u01/dumpdir'; Directory created.
|
1.8 全备数据库
原库进行全备份
run
{
allocate channel ch1 device type disk;
backup full database format '/u01/rmanbak/db_fullbackup_%d_%s_%p_%T';
backup current controlfile format '/u01/rmanbak/ctl_%d_%s_%p_%T';
backup archivelog all delete input format '/u01/rmanbak/arch_%d_%s_%p_%T';
backup spfile format '/u01/rmanbak/spfile_%d_%s_%p_%T';
release channel ch1;
}
1.9 生成传输集(源数据库上面执行的操作)
mkdir -p /u01/td ##产生的数据文件和元数据dump文件目录
mkdir -p /u01/ad ## auxiliary实例目录
rman target /
transport tablespace tps_hxl tablespace destination '/u01/td' auxiliary destination '/u01/ad';
执行该步骤的时候必须先进行备份，否则报如下的错误
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 02/20/2020 14:39:33
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore
执行完成后发现相应的目录下有三个文件
[oracle@localhost td]$ pwd
/u01/td
[oracle@localhost td]$ ls
dmpfile.dmp impscrpt.sql tps_hxl01.dbf
[oracle@localhost td]$
dmpfile.dmp 为元数据文件 等会我们要传输到目的库进行导入
impscrpt.sql 脚本文件
tps_hxl01.dbf 表空间对应的数据文件
1.10 将相应文件拷贝到目的库
scp dmpfile.dmp oracle@192.168.1.134:/u01/dumpdir/
scp impscrpt.sql oracle@192.168.1.134:/u01/dumpdir/
scp tps_hxl01.dbf oracle@192.168.1.134:/u01/dumpdir/
1.11 数据文件转换
我这里源库和目的库是相同的操作系统，不需要进行转换，需要转换的话可以参考
https://www.cnblogs.com/hxlasky/p/12334747.html
1.12 目标库手工调制文件目录
从库拷贝过来的数据文件目前是放在/u01/dumpdir/,我们需要放到pdbs所在的目录下
首先查看当前pdbs的数据文件路径
|
SQL> alter session set container=ORA12CPDB1; SQL> set linesize 1000 SQL> column file_name format a64 SQL> column tablespace_name format a16 SQL> select file_name,tablespace_name from dba_data_files; SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME ---------------------------------------------------------------- ---------------- /u01/app/oracle/oradata/ora12c/ora12cpdb1/system01.dbf SYSTEM /u01/app/oracle/oradata/ora12c/ora12cpdb1/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/ora12c/ora12cpdb1/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/ora12c/ora12cpdb1/users01.dbf USERS /u01/app/oracle/oradata/ora12c/ora12cpdb1/tps_goldengate01.dbf TPS_GOLDENGATE /u01/app/oracle/oradata/ora12c/ora12cpdb1/fda101.dbf FDA1
6 rows selected. |
拷贝文件
[oracle@localhost dumpdir]$ cp /u01/dumpdir/tps_hxl01.dbf /u01/app/oracle/oradata/ora12c/ora12cpdb1/
1.13 目标库创建用户并进行导入
SQL> alter session set container=ORA12CPDB1;
Session altered.
SQL> create user hxl identified by oracle;
User created.
SQL> grant connect ,resource to hxl;
Grant succeeded.
|
impdp system/oracle@ORA12CPDB1 dumpfile=dmpfile.dmp directory=datapump_dir transport_datafiles='/u01/app/oracle/oradata/ora12c/ora12cpdb1/tps_hxl01.dbf' logfile=import.log |
1.14 表空间设置为可读写
目标库导入完成后表空间模式是只读的，需要将该表空间设置为可读写
alter tablespace tps_hxl read write
-- The End --