简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

Home  >  Article  >  Database  >  ArcSDE10.2.1 for Oracle12c的数据迁移

ArcSDE10.2.1 for Oracle12c的数据迁移

WBOY
WBOYOriginal
2016-06-07 15:50:55882browse

对ArcSDE for Oracle数据库来说，数据迁移包括的方式比较多：逻辑迁移、冷备份、热备份、RMAN，当然也包括ArcGIS本身的Copy/Paste方式，那么在Oracle12c这个新的版本也引入了PDB和CDB的概念，感兴趣的用户可以查看Oracle12c的官方帮助。 今天介绍的就是使用O

对ArcSDE for Oracle数据库来说，数据迁移包括的方式比较多：逻辑迁移、冷备份、热备份、RMAN，当然也包括ArcGIS本身的Copy/Paste方式，那么在Oracle12c这个新的版本也引入了PDB和CDB的概念，感兴趣的用户可以查看Oracle12c的官方帮助。


今天介绍的就是使用Oracle12c新特性的方法来京CDB中的PDB迁移到另外一个CDB中。对PDB的概念，一般大家认为是可插入式的数据库，就像你使用U盘原来在别人的电脑上，现在从别人电脑上拔出，然后插入你自己的电脑上，这种方式也就是相当于数据的迁移。


测试环境：

源环境：

Redhat 5.5 、Oracle12.1.0.1（CDB:orcl 、PDB:pdborcl)、  ArcSDE10.2.1 、192.168.220.203

目标环境：

Windows Server2012、Oracle12.1.0.1（CDB：orcl、PDB：pdborcl1和pdborcl2 ）、ArcSDE10.2.1、192.168.100.213


我的Linux环境下的ArcSDE10.2.1 for Oracle12c的业务数据已经导入，而且可读可写，所在的PDB为pdborcl，希望将这个pdborcl迁移到Window环境下。

----------------------------------------------------------------------------------


Blog:               http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

迁移原理：将Linux环境的PDB生成一个描述该PDB的XML元数据文件，然后将该PDB的相关数据文件和该XML元数据文件拷贝到Windows机器上，在创建新的PDB使用这些XML元数据文件和数据文件即可。

ArcSDE10.2.1 for Oracle12c的数据迁移


1：查看Linux环境的PDB状态

[oracle@oracle12c orcl]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 15 06:30:09 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/system01.dbf
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf
/home/oracle/app/oracle/oradata/orcl/users01.dbf
/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf
/home/oracle/app/oracle/product/orcl/pdborcl/sde01

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/esri.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/esri.dbf
/home/oracle/app/oracle/oradata/orcl/pdborcl/esri2.dbf

14 rows selected.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4062262812 PDB$SEED                       READ ONLY
         3 2266793495 PDBORCL                        MOUNTED

2：导出Linux环境的PDB的元数据文件

SQL> alter pluggable database pdborcl unplug into '/home/oracle/pdborcl.xml';

Pluggable database altered.

SQL> !ls /home/oracle
app  database  Desktop  pdborcl.xml

3：将Linux环境的导出的XML文件和PDBORCL的数据文件物理拷贝到Windows环境下。默认的路径为c:\pdborcl.xml和c:\pdborcl\*.dbf。

4：打开相关的pdborcl.xml

<?xml version="1.0" encoding="UTF-8"?>
<pdb>
  <pdbname>PDBORCL</pdbname>
  <cid>3</cid>
  <byteorder>1</byteorder>
  <vsn>202375168</vsn>
  <dbid>2266793495</dbid>
  <cdbid>1347416737</cdbid>
  <guid>E07706C816463582E043CB64A8C060C6</guid>
  <uscnbas>2025944</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf</path>
      <afn>8</afn>
      <rfn>1</rfn>
      <createscnbas>1735344</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>35840</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf</path>
      <afn>9</afn>
      <rfn>4</rfn>
      <createscnbas>1735347</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>80640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>TEMP</name>
    <type>1</type>
    <tsn>2</tsn>
    <status>1</status>
    <issft>0</issft>
    <bmunitsize>128</bmunitsize>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/pdborcl_temp01.dbf</path>
      <afn>3</afn>
      <rfn>1</rfn>
      <createscnbas>1735345</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>0</status>
      <fileblocks>2560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>80</incsize>
    </file>
  </tablespace>
  <tablespace>
    <name>USERS</name>
    <type>0</type>
    <tsn>3</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf</path>
      <afn>10</afn>
      <rfn>9</rfn>
      <createscnbas>1735350</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>EXAMPLE</name>
    <type>0</type>
    <tsn>4</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf</path>
      <afn>11</afn>
      <rfn>10</rfn>
      <createscnbas>1735352</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>45840</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SDE</name>
    <type>0</type>
    <tsn>5</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/sde01</path>
      <afn>13</afn>
      <rfn>13</rfn>
      <createscnbas>1750711</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>51200</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>ESRI</name>
    <type>0</type>
    <tsn>6</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/esri.dbf</path>
      <afn>15</afn>
      <rfn>15</rfn>
      <createscnbas>1833964</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>128000</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>ESRI2</name>
    <type>0</type>
    <tsn>7</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/home/oracle/app/oracle/oradata/orcl/pdborcl/esri2.dbf</path>
      <afn>16</afn>
      <rfn>16</rfn>
      <createscnbas>1842823</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>12800</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>2266793495</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2025942</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819636003</frlt>
    </file>
  </tablespace>
  <optional>
    <csid>178</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>APS=12.1.0.1.0</option>
      <option>CATALOG=12.1.0.1.0</option>
      <option>CATJAVA=12.1.0.1.0</option>
      <option>CATPROC=12.1.0.1.0</option>
      <option>CONTEXT=12.1.0.1.0</option>
      <option>DV=12.1.0.1.0</option>
      <option>JAVAVM=12.1.0.1.0</option>
      <option>OLS=12.1.0.1.0</option>
      <option>ORDIM=12.1.0.1.0</option>
      <option>OWM=12.1.0.1.0</option>
      <option>SDO=12.1.0.1.0</option>
      <option>XDB=12.1.0.1.0</option>
      <option>XML=12.1.0.1.0</option>
      <option>XOQ=12.1.0.1.0</option>
    </options>
    <olsoid>0</olsoid>
    <dv>0</dv>
    <ncdb2pdb>0</ncdb2pdb>
    <apex>4.2.0.00.27:1</apex>
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>memory_target=843055104</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible=12.1.0.0.0</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
    </parameters>
    <tzvers>
      <tzver>primary version:18</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
  </optional>
</pdb>

----------------------------------------------------------------------------------


Blog:               http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------


5：使用创建PDB语句

C:\Users\Administrator>sqlplus sys/oracle@localhost/orcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期五 2月 28 22:59:53 2014

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4083344342 PDB$SEED                       READ ONLY
         3 1246209042 PDBORCL1                       MOUNTED
         4 1218627613 PDBORCL2                       MOUNTED


SQL> create pluggable database pdbsde using 'C:\pdborcl.xml' copy file_name_conv
ert=('/home/oracle/app/oracle/oradata/orcl/pdborcl/','C:\app\oracle\oradata\orcl
\pdborcl\');
create pluggable database pdbsde using 'C:\pdborcl.xml' copy file_name_convert=(
'/home/oracle/app/oracle/oradata/orcl/pdborcl/','C:\app\oracle\oradata\orcl\pdbo
rcl\')
*
第 1 行出现错误:
ORA-19505:
无法识别文件"/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf"
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 3) 系统找不到指定的路径。

6：将pdborcl.xml文件里面的数据文件路径修改为存储在Windows环境的实际路径，如c:\pdborcl\sde01等，而且在数据文件参数设置也应该设置为Windows路径下，而且这些数据文件的目的存储为C:\app\oracle\oradata\orcl\pdborcl\

该步骤涉及到数据文件的拷贝，根据数据量大小时间有所不一样

SQL> create pluggable database pdbsde using 'C:\pdborcl.xml' copy file_name_conv
ert=('c:\pdborcl\','C:\app\oracle\oradata\orcl\pdborcl\');

插接式数据库已创建。

7：打开新的PDB（pdbsde）并查看状态

[oracle@oracle12c orcl]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 15 06:30:09 2014

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4083344342 PDB$SEED                       READ ONLY
         3 1246209042 PDBORCL1                       MOUNTED
         4 1218627613 PDBORCL2                       MOUNTED
         5 2266793495 PDBSDE                         MOUNTED

SQL> alter pluggable database pdbsde open;

插接式数据库pdbsde已打开

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4083344342 PDB$SEED                       READ ONLY
         3 1246209042 PDBORCL1                       MOUNTED
         4 1218627613 PDBORCL2                       MOUNTED
         5 2266793495 PDBSDE                         READ WRITE
8：使用sqlplus 连接sde用户
SQL> conn sde/sde@192.168.100.213/pdbsde
ERROR:
ORA-01035: ORACLE ？？？êDí？？óD RESTRICTED SESSION
è¨？Tμ？ó？？§ê1ó？


警告: 您不再连接到 ORACLE。
提示ORA-01035错误，需要赋予RESTRICTED SESSION权限
SQL> conn system/oracle@192.168.100.213/pdbsde
已连接。
SQL> grant restricted session to sde;

授权成功。

SQL> conn sde/sde@192.168.100.213/pdbsde
已连接。
9：使用ArcCatalog10.2.1进行读写测试

ArcSDE10.2.1 for Oracle12c的数据迁移


总结：个人感觉，该方面有点类似于Oracle的冷备份，不过在实施过程中操作比较简单，尤其是ArcGIS版本化数据的迁移，既保证了简单操作性又保持了高效，值得用户进行参考！

----------------------------------------------------------------------------------


Blog:               http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：怎么在ArcSDE中使用Oracle分区表Next article：oracle基础语法(一)

Related articles

See more