oracle12c pdb迁移,Oracle 12C R2-新特性-迁移PDB

1

说明

Relocating a PDB

You can create a PDB by relocating it. After the operation is complete, the PDB is removed from its current CDB and is added to a different CDB.

在12.2中，可以通过迁移PDB，将一个PDB迁移到另一个CDB上，然后源CDB中的这个PDB将会被删除。https://www.cndba.cn/Expect-le/article/2495

https://www.cndba.cn/Expect-le/article/2495

In addition,the PDB being relocated can be opened in read/write mode and fully functional during the relocation process.This technique automatically quiesces the PDB in its old location while transporting and applying redo data to the PDB in its new location.Finally, when the PDB is ready, this technique brings it online in its new location. While the PDB is being relocated,current DML and DDL operations will pause while they are redirected to the PDB’s new location. Queries continue to run with no pause.

在迁移过程中，源PDB是保持读写。只有在最后完成迁移操作时，DML,DDL操作会被暂停，但是查询不受影响。毫无疑问这个方法将停机窗口时间大大缩短，其原理类似于XTTS，但是这一切操作由Oracle自动完成，不需要手动干预，我们要做的就是巧个命令。

1.1

迁移PDB示意图

https://www.cndba.cn/Expect-le/article/2495

1.2

在迁移时有两个“可用性”选项

l

AVAILABILITY MAXclause to redirect connections from the old location of the PDB to the new location.https://www.cndba.cn/Expect-le/article/2495

l

AVAILABILITY MAXclause is specified, the relocate operation configures the listener to relocate connections to the new location.

https://www.cndba.cn/Expect-le/article/2495

从源PDB重新指向新PDB，你有三个选择：

l

If your system is using Oracle Internet Directory (Oracle’s LDAP directory service), then connection information can be updated in a central location rather than in multiple client configuration files.

l

If you usethe same listener for the PDB in its old and new locations, then new connections are automatically routed to the PDB’s new location when relocation is complete.

l

If the PDBs use different listeners, and you employ cross-registration of their respective listeners through configuration of thelocal_listener and remote_listenerparameters, then relocation is seamless because the availability and location of the PDB’s services areautomatically registered with the listeners.

更多详细信息，请查看官方文档：

2

迁移PDB

2.1

准备工作

l

The CDB must exist.

l

The CDB must be inread/write mode.

l

The current usermust be a common userwhose current container is the CDB root or an application container.

l

The current user must have theCREATE PLUGGABLE DATABASE system privilege.

l

You must decide ona unique container name for each container.Eachcontainer name must be unique in a single CDB, and each container name must be unique within the scope of all the CDBs whose instances are reached through a specific listener.

The PDB name is used to distinguish a PDB from other PDBs in the CDB. PDB names follow the same rules asservice names, which includes being case-insensitive. See Oracle Database Net Services Reference forinformation about the rules for service names.

l

If you are creating a PDB in an Oracle Data Guard configuration with a physical standby database, then additional tasks must be completed before creating a PDB. See Oracle Data Guard Concepts and Administration for more information.

l

If you are creating a PDB that includes data that was encrypted with Transparent Data Encryption, then follow the instructions in Oracle Database Advanced Security Guide.

l

If you are creating a Database Vault-enabled PDB, then follow the instructions in Oracle Database Vault Administrator's Guide.

2.2

前提条件

l

完成“准备工作”

l

源CDB必须是local undo模式。

l

如果源CDB不是归档模式，那么源CDB必须以只读模式打开。

l

Dblink必须是连接到PDB所在的CDB上，而不是PDB。

l

源CDB和目标CDB必须是相同的字节顺序(endianness)。

l

如果源CDB的字符集不是AL32UTF8，那么源CDB和目标CDB的字符集和国家字符集必须要兼容。

2.3

开始迁移

2.3.1

环境

2.3.1.1

源库环境

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ORCLPDB MOUNTED

字符集：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.AL32UTF8

UNDO模式:https://www.cndba.cn/Expect-le/article/2495

SQL>col PROPERTY_NAME for a20

SQL> col PROPERTY_VALUE for a20

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

归档模式：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

2.3.1.2

目标库环境

#创建测试数据

SQL> alter session set container=orclpdb;

Session altered.

SQL> create table test(id int,name varchar2(50));

Table created.

SQL> insert into test values(1,'www.cndba.cn');

1 row created.

SQL> commit;

Commit complete.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBCNDBA READ WRITE NO

字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.ZHS16GBK

2.3.2

配置tnsname

mycdb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

2.3.3

创建DBLINK

SQL> CREATE PUBLIC DATABASE LINK mycdb CONNECT TO c##relpdb IDENTIFIED BY oracle USING 'MYCDB';

Database link created.

可以使用其他普通用户，默认是c##开头的用户名。拥有权限SYSOPER和CREATE PLUGGABLE DATABASE。

SQL> create user c##relpdb identified by oracle;

User created.

SQL> grant SYSOPER,CREATE PLUGGABLE DATABASE,connectTO c##relpdb container=all;

Grant succeeded.

2.3.4

参数设置

当启用OMF时或设置了PDB_FILE_NAME_CONVERT，则FILE_NAME_CONVERT和CREATE_FILE_DEST参数不用设置。

--启用OMF

https://www.cndba.cn/Expect-le/article/2495https://www.cndba.cn/Expect-le/article/2495

alter system set db_create_file_dest='/u01/app/oracle/oradata/cndba/';

2.3.5

执行迁移命令

SQL> CREATE PLUGGABLE DATABASE orclpdb FROM orclpdb@mycdb RELOCATE AVAILABILITY MAX;

Pluggable database created.

2.3.5.1

查看源库的PDB状态

SQL> select pdb_name, status from cdb_pdbs

PDB_NAME STATUS

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

PDB$SEED NORMAL

ORCLPDB RELOCATING

2.3.5.2

打开目标库的PDB

SQL> alter pluggable database orclpdb open;

--观察源库日志：

ORCLPDB(3): Reason = PDB close immediate --关闭了源库的PDB

ORCLPDB(3): Mode = KILL HARD FORCE -/-/-

ORCLPDB(3): Requestor = USER (orapid = 53, ospid = 15784, inst = 1)

ORCLPDB(3): Owner = Process: USER (orapid = 33, ospid = 15232)

ORCLPDB(3): Result = ORA-0

2017-12-18T18:38:18.167216+08:00

Pluggable database ORCLPDB closed

ORCLPDB(3):JIT: pid 15784 requesting stop

2017-12-18T18:38:19.591552+08:00

Pluggable database ORCLPDB closed

2017-12-18T18:38:31.198011+08:00

Deleted file /u01/app/oracle/oradata/orcl/orclpdb/users01.dbf Deleted file /u01/app/oracle/oradata/orcl/orclpdb/temp01.d

2.3.6

执行期间，在源库执行插入操作

SQL> insert into test values(1,'sihong');

1 row created.

SQL> commit;

Commit complete.

可以正常执行。

2.3.7

目标库查看PDB

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBCNDBA READ WRITE NO

4 ORCLPDB MOUNTED

2.3.8

查看表的数据是否一致

SQL> select * from test;

ID NAME

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

1 www.cndba.cn --第一次插入的数据

1 sihong --迁移期间插入的数据

https://www.cndba.cn/Expect-le/article/2495

数据同步正常。至此就完成了PDB的迁移。

版权声明：本文为博主原创文章，未经博主允许不得转载。

迁移PDB