Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]Oracle12cR2_创建PDB

2017-09-11
Oracle
   

[原创]Oracle12cR2_创建PDB

PDB创建有很多方法，下面介绍一下比较常用的创建方法

Oracle12cR2创建PDB的方法

Technique Description
Create a PDB by using the seed Create a PDB in a CDB using the files of the CDB seed or application seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB.
Create a PDB by cloning an existing PDB or non-CDB Create a PDB by cloning a source PDB or non-CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, a PDB in a local or remote application container, or a non-CDB. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.
Create a PDB by relocating it to a different CDB Create a PDB by relocating it from one CDB to another. This technique moves the files associated with the PDB to a new location.
Create a PDB by plugging an unplugged PDB into a CDB Create a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.
Creating a PDB as a proxy PDB Create a PDB as a proxy PDB by referencing a different PDB with a database link. The referenced PDB can be in the same CDB as the proxy PDB, or it can be in a different CDB.
Create a PDB by using a non-CDB Create a PDB by using a non-CDB

PDB

下面演示手工创建的两种方法

  • 通过种子容器创建PDB

  • 通过本地PDB中克隆新的PDB

通过种子容器创建PDB

查看seed容器的数据文件路径等情况

sql语句：

set linesize 1000
col name for a50
select con_id,name from v$pdbs;
select file#,ts#,name,con_id from v$datafile;
select file#,ts#,name,con_id from v$datafile where con_id = 2;

例如：

SQL> select file#,ts#,name,con_id from v$datafile where con_id = 2;

	 FILE#        TS# NAME                                                   CON_ID
---------- ---------- -------------------------------------------------- ----------
		 2          0 /opt/oracle/oradata/pdbseed/system01.dbf                    2
		 4          1 /opt/oracle/oradata/pdbseed/sysaux01.dbf                    2
		 6          2 /opt/oracle/oradata/pdbseed/undotbs01.dbf                   2
		 8          4 /opt/oracle/oradata/pdbseed/deftbs01.dbf                    2
SQL>

通过create pluggable database和file_name_convert参数进行创建PDB

sql语句：

create pluggable database pdb1 admin user lin identified by oracle file_name_convert('','');

例如：

SQL> create pluggable database pdb1 admin user lin identified by oracle file_name_convert=('/opt/oracle/oradata/pdbseed/','/opt/oracle/oradata/pdb1/');     

	Pluggable database created.

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

	CON_ID NAME                                               OPEN_MODE
---------- -------------------------------------------------- ----------
		 2 PDB$SEED                                           READ ONLY
		 3 PDB1                                               MOUNTED

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           MOUNTED
SQL>

创建完pdb1之后，状态是mounted状态

另外，需要注意下面几个参数：FILE_NAME_CONVERT, PDB_FILE_NAME_CONVERT, db_create_file_dest

SQL> create pluggable database pdb2 admin user lin identified by oracle;                                                                                                                                                                     
create pluggable database pdb2 admin user lin identified by oracle
																 *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified


SQL> !oerr ora 65016                                                                                                                                                                                                                         
65016, 00000, "FILE_NAME_CONVERT must be specified"                                                                                                                                                                                          
// *Cause:  Data files, and possibly other files, needed to be copied as a
//          part of creating a pluggable database.  However, Oracle Managed 
//          Files (OMF) was not enabled, PDB_FILE_NAME_CONVERT was not defined,
//          and there was a failure to specify the FILE_NAME_CONVERT clause.
// *Action: Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before
//          issuing CREATE PLUGGABLE DATABASE statement, or specify 
//          FILE_NAME_CONVERT clause as a part of the statement.
//

SQL>

同样直接创建pdb时候，如果没有指定 db_create_file_dest 内容(即没有使OMF有效) 或者 没指定 PDB_FILE_NAME_CONVERT 时候，如下命令会报错。

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL> show parameter PDB_FILE_NAME_CONVERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string
SQL>

SQL> create pluggable database pdb3 admin user lin identified by oracle;
create pluggable database pdb3 admin user lin identified by oracle
																 *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

SQL>

通过create pluggable database和PDB_FILE_NAME_CONVERT参数来创建seed容器一样的pdb

上面的ORA-65016报错很明显提示了解决方案。

详细可以参考说明: 1.241 PDB_FILE_NAME_CONVERT

sql语句：

alter session set PDB_FILE_NAME_CONVERT='/opt/oracle/oradata/pdbseed/','/opt/oracle/oradata/pdb2/';
create pluggable database pdb2 admin user lin identified by oracle;

例如：

SQL> alter session set PDB_FILE_NAME_CONVERT='/opt/oracle/oradata/pdbseed/','/opt/oracle/oradata/pdb2/';

Session altered.

SQL> create pluggable database pdb2 admin user lin identified by oracle;

Pluggable database created.

SQL>  
SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           MOUNTED
		 4 PDB2                           MOUNTED
SQL>

通过本地PDB中克隆新的PDB

克隆本地pdb和克隆seed容器一样，使用file_name_convert=’string1’,’string2’ 语句来克隆创建

  • 查看路径

过程如下：

SQL> col name for a50
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select file#,ts#,name,con_id from v$datafile order by con_id;

	 FILE#        TS# NAME                                                   CON_ID
---------- ---------- -------------------------------------------------- ----------
		 7          4 /opt/oracle/oradata/PRODCDB/deftbs01.dbf                    1
		 5          2 /opt/oracle/oradata/PRODCDB/undotbs01.dbf                   1
		 3          1 /opt/oracle/oradata/PRODCDB/sysaux01.dbf                    1
		 1          0 /opt/oracle/oradata/PRODCDB/system01.dbf                    1
		 6          2 /opt/oracle/oradata/pdbseed/undotbs01.dbf                   2
		 4          1 /opt/oracle/oradata/pdbseed/sysaux01.dbf                    2
		 2          0 /opt/oracle/oradata/pdbseed/system01.dbf                    2
		 8          4 /opt/oracle/oradata/pdbseed/deftbs01.dbf                    2
		 9          0 /opt/oracle/oradata/pdb1/system01.dbf                       3
		10          1 /opt/oracle/oradata/pdb1/sysaux01.dbf                       3
		11          2 /opt/oracle/oradata/pdb1/undotbs01.dbf                      3
		12          4 /opt/oracle/oradata/pdb1/deftbs01.dbf                       3
		13          0 /opt/oracle/oradata/pdb2/system01.dbf                       4
		14          1 /opt/oracle/oradata/pdb2/sysaux01.dbf                       4
		15          2 /opt/oracle/oradata/pdb2/undotbs01.dbf                      4
		16          4 /opt/oracle/oradata/pdb2/deftbs01.dbf                       4

16 rows selected.

SQL>
  • 执行sql语句

注意点：克隆的pdb需要pen(read write或者read only状态)

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
SQL> alter pluggable database pdb1 close immediate;
		
Pluggable database altered.

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           MOUNTED
SQL> create pluggable database pdb2 from pdb1 file_name_convert=('/opt/oracle/oradata/pdb1/','/opt/oracle/oradata/pdb2/');
create pluggable database pdb2 from pdb1 file_name_convert=('/opt/oracle/oradata/pdb1/','/opt/oracle/oradata/pdb2/')                                                                                                                         
*
ERROR at line 1:
ORA-65036: pluggable database PDB1 not open in required mode


SQL> alter pluggable database pdb1 open;
			   
Pluggable database altered.

SQL> create pluggable database pdb2 from pdb1 file_name_convert=('/opt/oracle/oradata/pdb1/','/opt/oracle/oradata/pdb2/');

Pluggable database created.

SQL> set pagesize 1000 linesize 1000
SQL> col name for a50
SQL> select file#,ts#,name,con_id from v$datafile order by con_id;

	 FILE#        TS# NAME                                                   CON_ID
---------- ---------- -------------------------------------------------- ----------
		 7          4 /opt/oracle/oradata/PRODCDB/deftbs01.dbf                    1
		 5          2 /opt/oracle/oradata/PRODCDB/undotbs01.dbf                   1
		 3          1 /opt/oracle/oradata/PRODCDB/sysaux01.dbf                    1
		 1          0 /opt/oracle/oradata/PRODCDB/system01.dbf                    1
		 6          2 /opt/oracle/oradata/pdbseed/undotbs01.dbf                   2
		 4          1 /opt/oracle/oradata/pdbseed/sysaux01.dbf                    2
		 2          0 /opt/oracle/oradata/pdbseed/system01.dbf                    2
		 8          4 /opt/oracle/oradata/pdbseed/deftbs01.dbf                    2
		 9          0 /opt/oracle/oradata/pdb1/system01.dbf                       3
		10          1 /opt/oracle/oradata/pdb1/sysaux01.dbf                       3
		11          2 /opt/oracle/oradata/pdb1/undotbs01.dbf                      3
		12          4 /opt/oracle/oradata/pdb1/deftbs01.dbf                       3
		17          0 /opt/oracle/oradata/pdb2/system01.dbf                       5
		18          1 /opt/oracle/oradata/pdb2/sysaux01.dbf                       5
		19          2 /opt/oracle/oradata/pdb2/undotbs01.dbf                      5
		20          4 /opt/oracle/oradata/pdb2/deftbs01.dbf                       5

16 rows selected.

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
		 5 PDB2                           MOUNTED
SQL>  

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>

另外：PDBs是在open read only状态下克隆

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
		 5 PDB2                           READ ONLY  NO
SQL>

SQL> create pluggable database pdb3 from pdb2 file_name_convert=('/opt/oracle/oradata/pdb2/','/opt/oracle/oradata/pdb3/');

Pluggable database created.

SQL>

删除PDB

删除pdb操作：

drop pluggable database pdb2 including datafiles;

注意语法语句

SQL> show pdbs

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
		 4 PDB2                           MOUNTED
SQL> drop pluggable database pdb2;
drop pluggable database pdb2
*
ERROR at line 1:
ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

SQL> drop pluggable database pdb2 including datafile;
drop pluggable database pdb2 including datafile
									   *
ERROR at line 1:
ORA-02000: missing DATAFILES keyword


SQL> drop pluggable database pdb2 including datafiles;

Pluggable database dropped.

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
SQL>    
SQL> !ls -l /opt/oracle/oradata/pdb2
total 0

SQL>

常用pdb操作命令

# 启动停止pdb
alter pluggable database pdb1 open;
alter pluggable database pdb1 close;

# 切换pdb,然后停止pdb
show pdbs;
alter session set container=pdb2;
alter database open;
shu immediate;

# 显示当前连接
show con_name; 

# 显示pdb信息
show pdbs;

# 启动所有pdb
alter pluggable database all open;

# 关闭所有pdb
alter pluggable database all close immediate;

PDB的监听

Database Net Services Reference / 7 Oracle Net Listener Parameters in the listener.ora File

监听文件：(静态监听)

[oracle@databasevm admin]$ pwd
/opt/oracle/product/12.2.0.1/db_1/network/admin
[oracle@databasevm admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
	(SID_DESC =
	  (GLOBAL_DBNAME = ORCLCDB)
	  (SID_NAME = ORCLCDB)
	  (ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)
	)
	(SID_DESC =
	  (GLOBAL_DBNAME = PRODCDB)
	  (SID_NAME = PRODCDB)
	  (ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)
	)

	(SID_DESC =      (GLOBAL_DBNAME = PDB1)      (SID_NAME = PRODCDB)      (ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)    )
	(SID_DESC =      (GLOBAL_DBNAME = PDB2)      (SID_NAME = PRODCDB)      (ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)    )
	(SID_DESC =      (GLOBAL_DBNAME = PDB3)      (SID_NAME = PRODCDB)      (ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)    )


  )

LISTENER =
  (DESCRIPTION_LIST =
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
	  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
	)
  )
  
DEDICATED_THROUGH_BROKER_LISTENER=ON
DEFAULT_SERVICE_LISTENER = (ORCLCDB)
[oracle@databasevm admin]$

测试链接如下：

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> conn sys/oracle@127.0.0.1:1521/pdb1 as sysdba
Connected.                                                                                                                                                                                                                                   
SQL> conn sys/oracle@127.0.0.1:1521/pdb2 as sysdba
Connected.                                                                                                                                                                                                                                   
SQL> conn sys/oracle@127.0.0.1:1521/pdb3 as sysdba
Connected.                                                                                                                                                                                                                                   
SQL> show con_name

CON_NAME
------------------------------
PDB3
SQL>

tnsnames.ora内容参考：8.1.1 Examples of Easy Connect Naming Method 搜索关键字: dedicated

参考：

PDB1 = 
 (DESCRIPTION=
  (CONNECT_DATA=
	  (SERVICE_NAME=PDB1)
	  (INSTANCE_NAME=PRODCDB)
	  (SERVER=dedicated))
  (ADDRESS=
	  (PROTOCOL=TCP)
	  (HOST=127.0.0.1)
	  (PORT=1521)))

PDB2 = 
 (DESCRIPTION=
  (CONNECT_DATA=
	  (SERVICE_NAME=PDB2)
	  (INSTANCE_NAME=PRODCDB)
	  (SERVER=dedicated))
  (ADDRESS=
	  (PROTOCOL=TCP)
	  (HOST=127.0.0.1)
	  (PORT=1521)))

测试：

[oracle@databasevm ~]$ sqlplus sys/oracle@pdb1 as sysdba                                                                         

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 22 12:49:34 2017

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

Last Successful login time: Fri Sep 22 2017 12:45:40 +08:00                                                                      

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  
SQL> show con_name;                                                                                                              

CON_NAME
------------------------------
PDB1
SQL>

远程克隆PDB

其实很简单，编辑好tnsnames.ora的简单连接，然后创建DBlink，之后create pluggable database语句来测试

源 cdb ORCLPDB pdb orclpdb1 /opt/oracle/oradata/ORCLCDB/ORCLPDB1
目标 cdb PRODCDB pdb 新的pdb： pdb_new /opt/oracle/oradata/PRODCDB/pdb_new 
[oracle@databasevm]$ ps -ef | grep smon | grep -v grep
oracle    3382     1  0 10:56 ?        00:00:00 ora_smon_PRODCDB
oracle    5722     1  0 12:35 ?        00:00:00 ora_smon_ORCLCDB
[oracle@databasevm]$ 

#编辑tnsname.ora
	ORCL_PDB1 =
	 (DESCRIPTION=
	  (CONNECT_DATA=
		  (SERVICE_NAME=ORCLPDB1)
		  (INSTANCE_NAME=ORCLCDB)
		  (SERVER=dedicated))
	  (ADDRESS=
		  (PROTOCOL=TCP)
		  (HOST=127.0.0.1)
		  (PORT=1521)))
#测试：
[oracle@databasevm admin]$ sqlplus sys/oracle@orcl_pdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 30 12:38:46 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit 

#创建public database link
SQL>  create public database link orcdb_pdb1_link connect to lin  identified by oracle using 'orcl_pdb1';

Database link created.

SQL> 
SQL> select * from t1@orcdb_pdb1_link;
TEXT
--------------------
orclpdb1

SQL>

# 从远程 clone db



SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
		 4 PDB3                           MOUNTED
		 5 PDB2                           MOUNTED
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database pdb_new from orclpdb1@orcdb_pdb1_link file_name_convert=('/opt/oracle/oradata/ORCLCDB/ORCLPDB1','/opt/oracle/oradata/PRODCDB/pdb_new');

Pluggable database created.

SQL>show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           READ WRITE NO
		 4 PDB3                           MOUNTED
		 5 PDB2                           MOUNTED
		 7 PDB_NEW                        MOUNTED
SQL>

特别注意：pdb_new/orclpdb1@orcdb_pdb1_link的名称，不然会如下错误

ERROR at line 1:
ORA-17628: Oracle error 65011 returned by remote Oracle server
ORA-65011: Pluggable database  does not exist.

另外，关于 ORA-14694 错误

ERROR at line 1:
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

[oracle@databasevm ~]$ oerr ora 14694                                                                                                                                                                                                        
14694, 00000, "database must in UPGRADE mode to begin MAX_STRING_SIZE migration"
// *Cause:  An attempt was made to update the MAX_STRING_SIZE parameter
//          to EXTENDED when the database was not in UPGRADE mode.
//* Action: Restart the database in UPGRADE mode, modify the parameter, 
//          run the utl32k.sql script in $ORACLE_HOME/rdbms/admin, and
//          restart the database in normal mode.
[oracle@databasevm ~]$

解决方案参考如下：

参考： 1.155 MAX_STRING_SIZE 中的 – Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB

SQL> alter session set container=pdb_new;

Session altered.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL> alter system set max_string_size=extended scope=spfile;

System altered.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL> alter pluggable database pdb_new open upgrade;

Pluggable database altered.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> 
SQL> !find /opt/oracle -name utl32k.sql

/opt/oracle/product/12.2.0.1/db_1/rdbms/admin/utl32k.sql

SQL> @/opt/oracle/product/12.2.0.1/db_1/rdbms/admin/utl32k.sql

SP2-0042: unknown command "aRem" - rest of line ignored.

Session altered.

~省略~

重启下PDB，测试如下：
SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 7 PDB_NEW                        READ WRITE YES
SQL> select * from lin.t1;

TEXT
--------------------
orclpdb1

SQL>

另外如果是从non-cdb远程clone成本地pdb的 跟这上面操作类似，只是最后需要执行如下脚本

@?/rdbms/admin/noncdb_to_pdb.sql

其他

参考资料

Home / Database / Oracle Database 12c Release 2 / Administration / 38.1 About Creating and Removing PDBs

~~~ 待续 LinHong 2017/09/11 ~~~~

Similar Posts

上一篇 [原创]Oracle12cR2_手工创建CDB

下一篇 [原创]Oracle12cR2_通过控制文件查看cdb的结构

Comments

Content