分类目录归档：数据迁移

XTTS迁移11g到12c

发表评论

初始化安装

配置跨平台增量备份的环境

目标主机上安装数据库

在目标主机上安装数据库软件和实例，创建要导入表空间对象所在的用户。

-- 源端建相关对象
create tablespace ts1 datafile '/u01/app/oracle/oradata/ora11g/ts101.dbf' size 20m;
create tablespace ts2 datafile '/u01/app/oracle/oradata/ora11g/ts201.dbf' size 20m;

create user xtts identified by xtts default tablespace ts1;
grant dba to xtts;

conn xtts/xtts
create table t_xtts (id number,name varchar2(10));
insert into t_xtts values (1,'xb');
commit;

-- 目标端创建目标pdb
create pluggable database xtts admin user admin identified by oracle 
    file_name_convert=('/u01/app/oracle/oradata/ora12c/pdbseed','/u01/app/oracle/oradata/ora12c/xtts');

create user xtts identified by xtts;
grant dba to xtts;

确定传输的表空间

基本上除了系统表空间其他都需要传输

源端安装xttconvert脚本

脚本下载地址

测试环境两个数据库在一台机器上，所以建了两个目录

[oracle@xb ~]mkdir -p s_xtts d_xtts

[oracle@xb s_xtts]$ unzip rman_xttconvert_VER4.zip 
Archive:  rman_xttconvert_VER4.zip
  inflating: xtt.newproperties       
  inflating: xtt.properties          
  inflating: xttcnvrtbkupdest.sql    
  inflating: xttdbopen.sql           
  inflating: xttdriver.pl            
  inflating: xttprep.tmpl            
 extracting: xttstartupnomount.sql

创建目录

  1. 源端：
    • xtt.properties文件里src_scratch_location参数定义的备份目录
  2. 目标端：
    • xtt.properties文件里dest_scratch_location参数定义的备份目录
    • dest_datafile_location参数定义的数据文件存放目录

存放备份文件的目录可以为同一个，比如我测试的环境两个数据库是在一台机器上，或者可以指向NFS地址，这里为了区分，还是建了2个不同的目录 

[root@xb backup]# ll
total 0
drwxr-xr-x. 2 oracle dba 6 Apr  4 14:46 dest
drwxr-xr-x. 2 oracle dba 6 Apr  4 14:46 source
[root@xb backup]# pwd
/backup

修改xtt.properties文件

以下参数为必须修改，其他则为可选

  • tablespaces
  • platformid
  • src_scratch_location
  • dest_scratch_location
  • dest_datafile_location
  • (if using 12c) — usermantransport=1

拷贝xtts脚本和配置文件到目标端

[oracle@xb s_xtts]$ ls
xttcnvrtbkupdest.sql  xttdbopen.sql  xttdriver.pl  xtt.newproperties  xttprep.tmpl  xtt.properties  xttstartupnomount.sql
[oracle@xb s_xtts]$ cp * ../d_xtts/

配置TMPDIR环境变量

在源端和目标端都需要将TMPDIR设置成脚本所在目录，如果没设则生成的文件会默认到/tmp目录下

echo "export TMPDIR=/home/oracle/s_xtts" >> /home/oracle/.11g
echo "export TMPDIR=/home/oracle/d_xtts" >> /home/oracle/.12c

准备阶段

在准备阶段过程中，表空间中要被传输的数据文件会备份到源端，然后在目标端通过xttdriver.pl脚本恢复出来

源端执行备份

[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_15_03_25_770//Apr4_Thu_15_03_25_770_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------


--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------

sqlplus -L -s  / as sysdba  @/home/oracle/s_xtts/backup_Apr4_Thu_15_03_25_770//diff.sql //u01/app/oracle/oradata/ora12c/xtts/

--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------

Prepare source for Tablespaces:
                  'TS1'  /backup/dest/
xttpreparesrc.sql for 'TS1' started at Thu Apr  4 15:03:25 2019
xttpreparesrc.sql for  ended at Thu Apr  4 15:03:25 2019
Prepare source for Tablespaces:
                  'TS2'  /backup/dest/
xttpreparesrc.sql for 'TS2' started at Thu Apr  4 15:03:28 2019
xttpreparesrc.sql for  ended at Thu Apr  4 15:03:28 2019

--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated
Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================

传输文件到目标端

  • src_scratch_location下的备份文件到目标端的dest_scratch_location目录
  • 源端$TMPDIR下的res.txt文件到目标端$TMPDIR
[oracle@xb source]$ pwd
/backup/source
[oracle@xb source]$ cp * ../dest/

[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

目标端恢复数据文件

[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

[oracle@xb xtts]$ pwd
/u01/app/oracle/oradata/ora12c/xtts
[oracle@xb xtts]$ ls
sysaux01.dbf  system01.dbf  temp012019-03-13_09-14-08-192-AM.dbf  TS1_5.dbf  TS2_6.dbf  undotbs01.dbf

前滚阶段

在这个阶段过程中，在源端创建增量备份，然后传输到目标端，转换成目标端字节顺序格式，然后应用于转换后的目标数据文件副本，进行前滚。这个阶段可能要重复执行多次，每次成功的增量备份都将会使目标端文件越接近源端数据。

源端可以多次执行增量备份，而不需要等待其应用到目标端，备份文件和res.txt必须要拷贝到目标端，然后才能执行–restore操作

对表空间进行增量备份

-- 再插入一些数据
XTTS@ora11g> insert into t_xtts values(2,'dba');

1 row created.

XTTS@ora11g> commit;

Commit complete.

[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_15_49_27_152//Apr4_Thu_15_49_27_152_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated

传输备份文件和res.txt

[oracle@xb source]$ cp * ../dest/

[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

目标端应用增量备份

[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

前滚的时候会连接到目标数据库，然后对每一个传输的数据文件进行增量备份的应用

重复上面几个步骤或进入下一阶段

这里有两个选择

  • 如果想让目标端文件与源端更加接近，那就多重复几次前滚阶段
  • 否则则进入最后一次增量备份

最终增量备份

在这个阶段过程中，源端的数据文件会置为只读，通过应用最后一次的增量备份，目标端的数据文件会跟源端保持一致。然后只需要将源端的对象元数据导出，再导入到目标端数据库，整个操作完成之前，源端数据必须一致保持在只读状态。

修改数据文件为只读

alter tablespace TS1 read only;
alter tablespace TS2 read only;

最终增量备份

[oracle@xb s_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
============================================================
trace file is /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//Apr4_Thu_16_07_50_775_.log
=============================================================

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

Added fname here 1:/backup/dest//TS1_5.tf
Added fname here 1:/backup/dest//TS2_6.tf
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

Prepare newscn for Tablespaces: 'TS1'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,                    <<<<====这些只读的警告可以忽略
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//xttpreparenextiter.sql
####################################################################
Prepare newscn for Tablespaces: 'TS2'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-06512: at line 284

####################################################################
Warning:
------
Warnings found in executing /home/oracle/s_xtts/backup_Apr4_Thu_16_07_50_775//xttpreparenextiter.sql
####################################################################
New /home/oracle/s_xtts/xttplan.txt with FROM SCN's generated

传输增量备份和res.txt

[oracle@xb source]$ cp * ../dest/
[oracle@xb s_xtts]$ cp incrbackups.txt ../d_xtts/
[oracle@xb s_xtts]$ cp res.txt ../d_xtts/

应用增量备份

[oracle@xb d_xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

传输元数据

源端:

expdp system/oracle dumpfile=xttdump.dmp directory=DATA_PUMP_DIR statistics=NONE transport_tablespaces=TS1,TS2 transport_full_check=y logfile=xtts_export.log

目标端：

impdp system/oracle@xtts dumpfile=xttdump.dmp directory=DATA_PUMP_DIR transport_datafiles='/u01/app/oracle/oradata/ora12c/xtts/TS1_5.dbf','/u01/app/oracle/oradata/ora12c/xtts/TS2_6.dbf'

验证数据

检查表空间错误

当前目标库中表空间都还是只读状态，通过Rman的VALIDATE TABLESPACE来检查物理和逻辑错误

RMAN> validate tablespace TS1, TS2 check logical;

修改表空间状态

alter tablespace TS1 read write;
alter tablespace TS2 read write;

检查表数据

sys@ORA12C> conn xtts/xtts@xtts
Connected.

xtts@XTTS> select * from t_xtts;

        ID NAME
---------- ------------------------------
         2 dba
         1 xb

将11g数据库插入到12c成为pdb

发表评论

为了将以前的旧库整合起来，需要将旧库都插入到12c数据库当中统一管理

这篇文章主要介绍如何将原有的11g数据库作为pdb插到已有的12c数据库当中去

现有环境

SID 版本 CDB
Test 11.2.0.4 NO
Ora12c 12.2.0.1 YES

升级11g数据库

参考通过DBUA升级11g到12.2，不过多介绍

从NO-CDB插入到CDB示意图

确认源库信息

sys@TEST> show parameter db_name

PARAMETER_NAME                                               TYPE                              VALUE
------------------------------------------------------------ --------------------------------- -----------------------------------------------
db_name                                                      string                            test
sys@TEST> select name, cdb from v$database;

NAME                        CDB
--------------------------- ---------
TEST                        NO

sys@TEST> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
test

DBMS_PDB包

这个包的作用主要是用于生成源数据库的元数据信息，通过它允许一个非CDB的库插入到一个已经存在的CDB当中

首先要保持源库的干净，没有数据处于恢复状态等等情况，可以正常关闭然后启动到read only状态

sys@TEST> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST> startup open read only

查看数据文件位置，后面FILE_NAME_CONVERT会用到

sys@TEST> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/users01.dbf

执行DBMS_PDB.DESCRIBE,生成一个XML文件，其实就跟拔下一个PDB的操作一样

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/u01/app/oracle/oradata/test/test.xml');
END;
/

正常关闭源库

sys@TEST> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

连到目标库上

sys@ORA12C> select name, CDB from v$database;

NAME                        CDB
--------------------------- ---------
ORA12C                      YES

检查源库是否能被插入到目标库

set serveroutput on
DECLARE
   compatible BOOLEAN := FALSE;
BEGIN   
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/test/test.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('YES');
   else DBMS_OUTPUT.PUT_LINE('NO');
   end if;
END;
/

如果返回NO,则需要检查原因

col cause for a30
col name for a10
col message for a80
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TEST';

用刚才生成的XML文件创建一个新的PDB

CREATE PLUGGABLE DATABASE test USING '/u01/app/oracle/oradata/test/test.xml'
  COPY
  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/test/', '/u01/app/oracle/oradata/ora12c/test/');

Pluggable database created.

切换到新建的PDB，通过$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql语句清除一些PDB不需要的信息

ALTER SESSION SET CONTAINER=test;

@?/rdbms/admin/noncdb_to_pdb.sql

详细日志信息可以查看这里

启动pdb

sys@ORA12C> alter session set container=test;

Session altered.

sys@ORA12C> alter pluggable database open;

Pluggable database altered.

sys@ORA12C> select name,open_mode from v$PDBS;

NAME       OPEN_MODE
---------- ------------------------------
TEST       READ WRITE

至此，原有的11g库正式转成了12c的一个pdb。