PDB的移动

1.1 PDB的移动

1.1.1 PDB在相同的CDB内的拔出和插入（位置不变）

本例中，将在CDB（tcdb）内拔出和插入PDB（pdb2）

 

用sysdba进行登录

[oracle@rac1 db_home]$ export ORACLE_SID=tcdb

[oracle@rac1 db_home]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:59:41 2013

 

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

 

Enter user-name: sys as sysdba

Enter password:

 

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

 

查看各种ID

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED   2        4065159988       4065159988       E31EE23FE4340E49E0433300A8C01AD7

PDB1        3        3330272355       3330272355       E31F5B4DC25B11FEE0433300A8C07605

PDB2        4         3888812375 3888812375   E31F8C34A7E31371E0433300A8C000F9

 

查看PDB2的状态

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           READ WRITE

 

关闭PDB2

SQL> alter pluggable database PDB2 close immediate;

Pluggable database altered.

 

查看PDB2的状态，MOUNTED说明PDB已经关闭

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

拔出PDB2

SQL> alter pluggable database PDB2 unplug into '/test_data/pdb2.xml';

Pluggable database altered.

SQL>

 

删除PDB2，但是不要删除数据文件

SQL> drop pluggable database PDB2 keep datafiles;

 

Pluggable database dropped.

 

SQL>

 

PDB2已经不存在

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

重新插入PDB2

SQL> create pluggable database PDB2 using '/test_data/pdb2.xml'

  2  nocopy

  3  tempfile reuse;

 

 

Pluggable database created.

nocopy 表示没有数据文件的移动

 

PDB2处于MOUNTED状态

SQL>  select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

打开PDB2

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

 

再次查看

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2 4065159988 4065159988 E31EE23FE4340E49E0433300A8C01AD7

PDB1              3 3330272355 3330272355 E31F5B4DC25B11FEE0433300A8C07605

PDB2              4 3888812375 3888812375 E31F8C34A7E31371E0433300A8C000F9

仔细观察，PDB DBID, GUID保持不变

 

 

 

1.1.2 PDB在相同的CDB内的拔出和插入（位置不变），产生新ID

本例中，将在CDB（tcdb）内拔出和插入PDB（pdb2）,但是，重新产生ID。

 

用sysdba进行登录

[oracle@rac1 db_home]$ export ORACLE_SID=tcdb

[oracle@rac1 db_home]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:59:41 2013

 

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

 

Enter user-name: sys as sysdba

Enter password:

 

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

 

查看各种ID

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED   2        4065159988       4065159988       E31EE23FE4340E49E0433300A8C01AD7

PDB1        3        3330272355       3330272355       E31F5B4DC25B11FEE0433300A8C07605

PDB2        4         3888812375 3888812375   E31F8C34A7E31371E0433300A8C000F9

 

查看PDB的状态

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           READ WRITE

 

关闭PDB2

SQL> alter pluggable database PDB2 close immediate;

Pluggable database altered.

 

查看PDB2的状态，MOUNTED说明PDB已经关闭

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

拔出PDB2

SQL> alter pluggable database PDB2 unplug into '/test_data/pdb2.xml';

Pluggable database altered.

SQL>

 

删除PDB2，但是不要删除数据文件

SQL> drop pluggable database PDB2 keep datafiles;

 

Pluggable database dropped.

 

SQL>

 

PDB2已经不存在

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

重新插入PDB2

SQL> create pluggable database PDB2 AS CLONE using '/test_data/pdb2.xml'

  2  nocopy

  3  tempfile reuse;

 

Pluggable database created.

AS CLONE 会重新生成ID。

tempfile reuse表示重用临时文件

 

 

PDB2处于MOUNTED状态

SQL> select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

打开PDB2

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

 

再次查看ID

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 

NAME         CON_ID       DBID   CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2    065159988         065159988 E31EE23FE4340E49E0433300A8C01AD7

PDB1              3      330272355         330272355 E31F5B4DC25B11FEE0433300A8C07605

PDB2              4      888998145         888998145 E3535F462013121AE0433300A8C01515

从上面可以看出，" AS CLONE "子句将重新产生各种新的ID。

 

 

1.1.3 PDB在相同的CDB内的拔出和插入（位置发生了变化）

 

本例中，将在CDB（tcdb）内拔出和插入PDB（pdb2）,但是，重新产生ID。

 

用sysdba进行登录

[oracle@rac1 db_home]$ export ORACLE_SID=tcdb

[oracle@rac1 db_home]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:59:41 2013

 

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

 

Enter user-name: sys as sysdba

Enter password:

 

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

 

查看ID

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 

NAME         CON_ID       DBID   CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2    065159988         065159988 E31EE23FE4340E49E0433300A8C01AD7

PDB1              3      330272355         330272355 E31F5B4DC25B11FEE0433300A8C07605

PDB2              4      888998145         888998145 E3535F462013121AE0433300A8C01515

 

查看PDB2的数据文件的位置

SQL>  select file_name from cdb_data_files where con_id=4;

 

FILE_NAME

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

/taryartar/12c/db_base/oradata/TCDB/E31F8C34A7E31371E0433300A8C000F9/datafile/o1

_mf_system_8zwm4rmz_.dbf

 

/taryartar/12c/db_base/oradata/TCDB/E31F8C34A7E31371E0433300A8C000F9/datafile/o1

_mf_sysaux_8zwm4r3l_.dbf

 

/taryartar/12c/db_base/oradata/TCDB/E31F8C34A7E31371E0433300A8C000F9/datafile/o1

_mf_users_8zwm7b64_.dbf

 

查看PDB的状态

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           READ WRITE

 

关闭PDB2

SQL> alter pluggable database PDB2 close immediate;

Pluggable database altered.

 

查看PDB2的状态，MOUNTED说明PDB已经关闭

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

拔出PDB2

SQL> alter pluggable database PDB2 unplug into '/test_data/pdb2.xml';

Pluggable database altered.

SQL>

 

删除PDB2，但是不要删除数据文件

SQL> drop pluggable database PDB2 keep datafiles;

 

Pluggable database dropped.

 

SQL>

 

PDB2已经不存在

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

查看当前数据库是否在使用OMF(Oracle Managed Files)

SQL> show parameter DB_CREATE_FILE_DEST;

NAME                                 TYPE        VALUE

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

db_create_file_dest                  string      /taryartar/12c/db_base/oradata

当前数据库正在使用OMF。

 

如果当前数据库正在使用OMF，可以改变PDB的路径

SQL> alter system set DB_CREATE_FILE_DEST = '/test_data' scope=both;

System altered.

SQL>

 

 

 

重新插入PDB2

SQL> create pluggable database PDB2 AS CLONE using '/test_data/pdb2.xml'

  2  copy

  3  tempfile reuse;

 

 

Pluggable database created.

 

SQL>

 

如果没有使用OMF，则可以利用FILE_NAME_CONVERT改变pdb2的数据文件的物理位置，详细的语法如下：

create pluggable database PDB2 AS CLONE using '/test_data/pdb2.xml'

copy

FILE_NAME_CONVERT = ('/taryartar/12c/db_base/oradata/TCDB/', '/test_data/')

tempfile reuse;

/taryartar/12c/db_base/oradata/TCDB/ 数据文件原来的路径

/test_data/ 数据文件的新路径

 

PDB2处于MOUNTED状态

SQL> select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

打开PDB2

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

 

查看/test_data下面是否已经创建了新的文件

SQL> !ls -R /test_data

/test_data:

pdb2.xml  TCDB

 

/test_data/TCDB:

E3535F462016121AE0433300A8C01515

 

/test_data/TCDB/E3535F462016121AE0433300A8C01515:

datafile

 

/test_data/TCDB/E3535F462016121AE0433300A8C01515/datafile:

o1_mf_sysaux_903sybst_.dbf  o1_mf_system_903sy2or_.dbf  o1_mf_temp_903sz9ks_.dbf  o1_mf_users_903sz8wr_.dbf

 

SQL>

新文件已经产生。

 

SQL> COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 

SQL>

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2 4065159988 4065159988 E31EE23FE4340E49E0433300A8C01AD7

PDB1              3 3330272355 3330272355 E31F5B4DC25B11FEE0433300A8C07605

PDB2              4 3889004930 3889004930 E3535F462016121AE0433300A8C01515

 

查看pdb2的数据文件的位置

SQL> select file_name from cdb_data_files where con_id=4;

FILE_NAME

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

/test_data/TCDB/E3535F462016121AE0433300A8C01515/datafile/o1_mf_system_903sy2or_

.dbf

 

/test_data/TCDB/E3535F462016121AE0433300A8C01515/datafile/o1_mf_sysaux_903sybst_

.dbf

 

/test_data/TCDB/E3535F462016121AE0433300A8C01515/datafile/o1_mf_users_903sz8wr_.

dbf

从上面可以看出，pdb2原先的位置是/taryartar/12c/db_base/oradata，现在变成了/test_data/。

 

 

1.1.4 PDB在不同CDB之间的移动

本例中，将在从CDB（tcdb）内拔出PDB（pdb2），然后，插入到另外一个CDB（tcdb2）。

 

用sysdba进行登录tcdb

[oracle@rac1 db_home]$ export ORACLE_SID=tcdb

[oracle@rac1 db_home]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:59:41 2013

 

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

 

Enter user-name: sys as sysdba

Enter password:

 

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

 

查看ID

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1  883454903          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2 4065159988 4065159988 E31EE23FE4340E49E0433300A8C01AD7

PDB1              3 3330272355 3330272355 E31F5B4DC25B11FEE0433300A8C07605

PDB2              4 3889022347 3889022347 E3535F462018121AE0433300A8C01515

 

查看PDB2的数据文件的位置

SQL>  select file_name from cdb_data_files where con_id=4;

 

FILE_NAME

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

/taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1

_mf_system_903vpd8l_.dbf

 

/taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1

_mf_sysaux_903vprlv_.dbf

 

/taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1

_mf_users_903vqsxw_.dbf

 

查看PDB的状态

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           READ WRITE

 

关闭PDB2

SQL> alter pluggable database PDB2 close immediate;

Pluggable database altered.

 

查看PDB2的状态，MOUNTED说明PDB已经关闭

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

 

拔出PDB2

SQL> alter pluggable database PDB2 unplug into '/test_data/pdb2.xml';

Pluggable database altered.

SQL>

 

删除PDB2，但是不要删除数据文件

SQL> drop pluggable database PDB2 keep datafiles;

 

Pluggable database dropped.

 

SQL>

 

PDB2已经不存在

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

========================================================

用sysdba登录tcdb2（本例中，tcdb和tcdb2都在同一台机器）

[oracle@rac1 db_home]$ export ORACLE_SID=tcdb2

[oracle@rac1 db_home]$ sqlplus

 

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:59:41 2013

 

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

 

Enter user-name: sys as sysdba

Enter password:

 

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>

 

查看当前数据库是否在使用OMF(Oracle Managed Files)

SQL> show parameter DB_CREATE_FILE_DEST;

NAME                                 TYPE        VALUE

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

db_create_file_dest                  string      /taryartar/12c/db_base/oradata

当前数据库正在使用OMF。

 

如果当前数据库正在使用OMF，可以通过下面的方式改变数据文件的存放路径

SQL> alter system set DB_CREATE_FILE_DEST = '/test_data' scope=both;

System altered.

SQL>

 

在操作系统中查看原来的数据文件

[root@rac1 test_data]# ll /taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1_mf_system_903vpd8l_.dbf

-rw-r----- 1 oracle oinstall 283123712 Aug  7 02:57 /taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1_mf_system_903vpd8l_.dbf

数据文件存在

 

查看新路径下有没有数据文件

[root@rac1 test_data]# ll /test_data

total 8

-rw-r--r-- 1 oracle oinstall 4244 Aug  7 02:57 pdb2.xml

[root@rac1 test_data]#

只有一个文件，没有数据文件。

 

重新插入PDB2

SQL> create pluggable database PDB2_V2 AS CLONE using '/test_data/pdb2.xml'

  2  move

  3  tempfile reuse;

Pluggable database created.

 

move会把原来位置上的数据文件移动（而不是拷贝）到新的位置

重新插入pdb2，并命名为PDB2_V2。

 

如果没有使用OMF，则可以利用FILE_NAME_CONVERT改变pdb2的数据文件的物理位置，详细的语法如下：

create pluggable database  PDB2_V2 AS CLONE using '/test_data/pdb2.xml'

move

FILE_NAME_CONVERT = ('/taryartar/12c/db_base/oradata/TCDB/', '/test_data/')

tempfile reuse;

/taryartar/12c/db_base/oradata/TCDB/ 数据文件原来的路径

/test_data/ 数据文件的新路径

 

 

PDB2_V2处于MOUNTED状态

SQL>  select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB3                           MOUNTED

PDB4                           MOUNTED

PDB2_V2                        MOUNTED

 

打开PDB2_V2

SQL> alter pluggable database PDB2_V2 open;

Pluggable database altered.

 

查看/test_data下面是否已经创建了新的文件

SQL> !ls -R /test_data

/test_data:

pdb2.xml  TCDB2

 

/test_data/TCDB2:

E357324FEC4821FDE0433300A8C0B6DD

 

/test_data/TCDB2/E357324FEC4821FDE0433300A8C0B6DD:

datafile

 

/test_data/TCDB2/E357324FEC4821FDE0433300A8C0B6DD/datafile:

o1_mf_sysaux_903wkst9_.dbf  o1_mf_system_903wkstb_.dbf  o1_mf_temp_903wm8m0_.dbf  o1_mf_users_903wljwg_.dbf

新文件已经产生。

 

 

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 

 

NAME         CON_ID       DBID    CON_UID GUID

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

CDB$ROOT          1 2019279893          1 DD7C48AA5A4504A2E04325AAE80A403C

PDB$SEED          2 4065152146 4065152146 E3248235F4082FA2E0433300A8C0A61B

PDB3              3 3938652388 3938652388 E324F5B46388341EE0433300A8C09AC8

PDB4              4 3833224900 3833224900 E32580EB506A38C6E0433300A8C02AC2

PDB2_V2           5 3169057529 3169057529 E357324FEC4821FDE0433300A8C0B6DD

 

查看PDB2_V2的数据文件的位置

SQL> select file_name from cdb_data_files where con_id=5;

FILE_NAME

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

/test_data/TCDB2/E357324FEC4821FDE0433300A8C0B6DD/datafile/o1_mf_system_903wkstb

_.dbf

 

/test_data/TCDB2/E357324FEC4821FDE0433300A8C0B6DD/datafile/o1_mf_sysaux_903wkst9

_.dbf

 

/test_data/TCDB2/E357324FEC4821FDE0433300A8C0B6DD/datafile/o1_mf_users_903wljwg_

.dbf

从上面可以看出，pdb2（新名字叫PDB2_V2）原先的位置是/taryartar/12c/db_base/oradata，现在变成了/test_data/。

 

再次查看原来的数据文件

[root@rac1 test_data]# ll /taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1_mf_system_903vpd8l_.dbf

ls: cannot access /taryartar/12c/db_base/oradata/TCDB/E3535F462018121AE0433300A8C01515/datafile/o1_mf_system_903vpd8l_.dbf: No such file or directory

原来的数据文件已经不存在，说明数据文件已经被移动（不是拷贝）到新的位置。