Components of a CDB
A CDB includes the following components:
Root
The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. A CDB has exactly one root.
Seed
The seed, named PDB$SEED, is a template that you can use to create new PDBs. You cannot add objects to or modify objects in the seed. A CDB has exactly one seed.
PDBs
A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.
You can easily plug a PDB into a CDB and unplug a PDB from a CDB. When you plug in a PDB, you associate the PDB with a CDB. When you unplug a PDB, you disassociate the PDB from a CDB. An unplugged PDB consists of an XML file that describes the PDB and the PDB's files (such as the data files and wallet file).
You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.
Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB's files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.
Common Users and Local Users
A CDB supports common users. A common user is a user that has the same identity in the root and in every existing and future PDB. A common user can log in to the root and any PDB in which it has privileges. The operations that a common user can perform depend on the privileges granted to the common user. Some administrative tasks, such as creating a PDB or unplugging a PDB, must be performed by a common user. A CDB also supports local users. A local user is a user that exists in exactly one PDB.
Separation of Duties in CDB and PDB Administration
Some database administrators manage the entire CDB. These database administrators connect to the CDB as common users, and they manage attributes of the entire CDB and the root, as well as some attributes of PDBs. For example, these database administrators can create, unplug, plug in, and drop PDBs. They can also specify the temporary tablespace for the entire CDB and the default tablespace for the root, and they can change the open mode of one or more PDBs.
Database administrators can also connect to a specific PDB as a local user and then perform a subset of management tasks on the PDB that a database administrator performs on a non-CDB. The subset of tasks are those required for the PDB to support an application. For example, these can include management of tablespaces and schemas in a PDB, specification of storage parameters for that PDB, and changing the open mode of the current PDB.
以上为官方文档中关于CDB的介绍,我们有多种方法创建PDB,下图为官方文档中创建pdb方法的图片。本文将介绍如何创建、删除、克隆、删除、重命名PDB
首先,我们看看从DBCA生成的脚本中看看创建pdb脚本
[oracle@db1 scripts]$ pwd/u01/oracle/admin/cdb1/scripts [oracle@db1 scripts]$ ls -ltr |grep pdb11 -rw-r----- 1 oracle oinstall 617 Mar 10 10:25 plug_pdb11.sql -rw-r----- 1 oracle oinstall 420 Mar 10 10:25 postPDBCreation_pdb11.sql[oracle@db1 scripts]$ cat plug_pdb11.sql
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/oracle/admin/cdb1/scripts/plugDatabase.log append select 'database_running' from dual; spool /u01/oracle/admin/cdb1/scripts/plugDatabase.log append startup ; host mkdir -p /oradata/cdb1/pdb11; host mkdir -p /oradata/cdb1/pdb11; select name from v$datafile where con_id=2; select name from v$tempfile where con_id =2; CREATE PLUGGABLE DATABASE pdb11 ADMIN USER PDBADMIN IDENTIFIED BY "oracle" ROLES=(CONNECT) file_name_convert=('/oradata/cdb1/pdbseed','/oradata/cdb1/pdb11'); alter pluggable database pdb11 open; alter system register;[oracle@db1 scripts]$ cat postPDBCreation_pdb11.sql
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA alter session set container=pdb11; set echo on spool /u01/oracle/admin/cdb1/scripts/postPDBCreation.log append CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/oradata/cdb1/pdb11/pdb11_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DEFAULT TABLESPACE "USERS";
[oracle@db1 scripts]$
可以看到dbca生成的创建pdb脚本是从pdbseed中创建
包含数据文件,通过DBCA创建的脚本可以看到
CREATE PLUGGABLE DATABASE PDB$SEED AS CLONE USING '/u01/oracle/product/121/assistants/dbca/templates//pdbseed.xml' source_file_name_convert = ('/oradata/seeddata/pdbseed/temp01.dbf','/oradata/cdb1/pdbseed/pdbseed_temp012016-03-10_10-25-57-AM.dbf',
'/oradata/seeddata/pdbseed/system01.dbf','&&pdbfile0',
'/oradata/seeddata/pdbseed/sysaux01.dbf','&&pdbfile1') NOCOPY;
不包含数据文件,通过DBCA创建的脚本可以看到
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
'/u01/app/oracle/oradata/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.
创建数据库时,如果不指定 ENABLE PLUGGABLE DATABASE,新建的数据库是NON-CDB,她不能再创建PDB。看能否创建pdb,可以通过下面的语句查询
SQL> select name,cdb from v$database; NAME CDB --------- --- CDB3 NO SQL>
Plugging in a PDB (not cloning) from another CDB
下面通过在CDB1中unplug pdb12,然后CDB2中plug
CDB1上的操作
SQL> select cdb,name from v$database; CDB NAME --- --------- YES CDB1 SQL> SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 PDB12 READ WRITE NO SQL> alter session set container=PDB11; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/cdb1/undotbs01.dbf /oradata/cdb1/pdb11/system01.dbf /oradata/cdb1/pdb11/sysaux01.dbf /oradata/cdb1/pdb11/pdb11_users01.dbf SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB11 READ WRITE NO SQL> conn / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 PDB12 READ WRITE NO SQL> alter pluggable database pdb12 close immediate; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 PDB12 MOUNTED SQL> alter pluggable database pdb12 unplug into '/oradata/cdb1_pdb12.xml'; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 PDB12 MOUNTED
SQL> drop pluggable database pdb12 keep datafiles;
Pluggable database dropped.
使用keep datafiles选项不会删除数据文件,以后还可以plug
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO SQL> SQL> select tablespace_name, file_name from cdb_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------- SYSTEM /oradata/cdb1/pdb11/system01.dbf SYSAUX /oradata/cdb1/pdb11/sysaux01.dbf USERS /oradata/cdb1/pdb11/pdb11_users01.dbf USERS /oradata/cdb1/users01.dbf UNDOTBS1 /oradata/cdb1/undotbs01.dbf SYSTEM /oradata/cdb1/system01.dbf SYSAUX /oradata/cdb1/sysaux01.dbf 7 rows selected. SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@db1 oradata]$ ls -l /oradata/*xml -rw-r--r-- 1 oracle oinstall 5231 Jun 3 21:52 /oradata/cdb1_pdb12.xml [oracle@db1 oradata]$
CDB2上的操作
SQL> select cdb,name from v$database; CDB NAME --- --------- YES CDB2 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/cdb2/system01.dbf /oradata/cdb2/sysaux01.dbf /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdbseed/system01.dbf /oradata/cdb2/users01.dbf /oradata/cdb2/pdbseed/sysaux01.dbf 6 rows selected. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL>
set serveroutput on; begin if not sys.dbms_pdb.check_plug_compatibility('/oradata/cdb1_pdb12.xml') then raise_application_error (-20000, 'incompatible'); end if; end; /
首先要检测从cdb1中unplug的pdb是否和cdb2兼容
SQL> begin
2 if not sys.dbms_pdb.check_plug_compatibility('/oradata/cdb1_pdb12.xml')3 then
4 raise_application_error (-20000, 'incompatible');
5 end if;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-20000: incompatible
ORA-06512: at line 4
SQL>
SQL> SELECT MESSAGE, ACTION FROM PDB_PLUG_IN_VIOLATIONS;
MESSAGE--------------------------------------------------------------------------------
ACTION
--------------------------------------------------------------------------------
Character set mismatch: PDB character set AL32UTF8. CDB character set WE8MSWIN1252.
Convert the character set of the PDB to match the CDB or plug the PDB in a CDB with compatible character set
CDB parameter memory_target mismatch: Previous 760M Current 0
Please check the parameter in the current CDB
SQL>
SQL> select name, userenv('language') from v$database;
NAME USERENV('LANGUAGE')
--------- ----------------------------------------------------
CDB1 AMERICAN_AMERICA.AL32UTF8
SQL>
SQL> select name, userenv('language') from v$database;
NAME USERENV('LANGUAGE')
--------- ----------------------------------------------------
CDB2 AMERICAN_AMERICA.WE8MSWIN1252
SQL>
创建cdb2时使用了包含数据文件的选项,字符集默认为WE8MSWIN1252,和cdb1中字符集不一样,所以不兼容,不能做转化。因为cdb2是空库,所以在cdb2中做了字符集转化,因为是空库所以没做过多验证,通过下面的步骤做了转化。正常的做法是懂DMU(Database Migration for Unicode),详解请打开http://docs.oracle.com/cd/E64126_01/index.htm
把CDB2的字符集转化为AL32UTF8
conn / as sysdba shutdown immediate; startup mount; alter system enable restricted session; alter system set job_queue_processes=0; alter system set aq_tm_processes=0; alter database open; alter database character set internal_use AL32UTF8; shutdown immediate; startup; shutdown; SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 394264576 bytes Fixed Size 2924832 bytes Variable Size 264244960 bytes Database Buffers 121634816 bytes Redo Buffers 5459968 bytes Database mounted. SQL> alter system enable restricted session; System altered. SQL> alter system set job_queue_processes=0; System altered. SQL> alter system set aq_tm_processes=0; System altered. SQL> alter database open; Database altered. SQL> alter database character set internal_use AL32UTF8; Database altered. SQL> select name, userenv('language') from v$database; NAME USERENV('LANGUAGE') --------- ---------------------------------------------------- CDB2 AMERICAN_AMERICA.AL32UTF8 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 394264576 bytes Fixed Size 2924832 bytes Variable Size 264244960 bytes Database Buffers 121634816 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> select name, userenv('language') from v$database; NAME USERENV('LANGUAGE') --------- ---------------------------------------------------- CDB2 AMERICAN_AMERICA.AL32UTF8 SQL>
再次检测,成功执行。如果是SGA,PGA,Memory等内存大小不匹配,可以忽略,别的错误要慎重对待
SQL> set serveroutput on; SQL> begin 2 if not sys.dbms_pdb.check_plug_compatibility('/oradata/cdb1_pdb12.xml') 3 then 4 raise_application_error (-20000, 'incompatible'); 5 end if; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> SELECT MESSAGE, ACTION FROM PDB_PLUG_IN_VIOLATIONS; MESSAGE -------------------------------------------------------------------------------- ACTION -------------------------------------------------------------------------------- CDB parameter memory_target mismatch: Previous 760M Current 0 Please check the parameter in the current CDB SQL>
SQL> alter system set db_create_file_dest='/oradata/cdb2'; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /oradata/cdb2
SQL> create pluggable database pdb12 using '/oradata/cdb1_pdb12.xml' move;
Pluggable database created.
SQL>
可以使用move,copy,nocopy,file_name_convert等,了解更多详情请打开https://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13855
[oracle@db1 pdb12]$ pwd /oradata/cdb1/pdb12 [oracle@db1 pdb12]$ ls -l total 0 [oracle@db1 pdb12]$ SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12 MOUNTED SQL> SQL> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------------------------------------- /oradata/cdb2/system01.dbf /oradata/cdb2/sysaux01.dbf /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdbseed/system01.dbf /oradata/cdb2/users01.dbf /oradata/cdb2/pdbseed/sysaux01.dbf /oradata/cdb2/CDB2/2DA9B0A41976142FE0537800000AE145/datafile/o1_mf_system_co398wgq_.dbf /oradata/cdb2/CDB2/2DA9B0A41976142FE0537800000AE145/datafile/o1_mf_sysaux_co398wh5_.dbf /oradata/cdb2/CDB2/2DA9B0A41976142FE0537800000AE145/datafile/o1_mf_users_co398wh7_.dbf 9 rows selected. SQL> SQL> col PDB_NAME for a20 SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB12 NEW PDB$SEED NORMAL SQL> SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12 MOUNTED SQL> alter pluggable database pdb12 open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12 READ WRITE NO SQL> SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB12 NORMAL PDB$SEED NORMAL SQL> SQL> drop pluggable database pdb12 including datafiles; Pluggable database dropped. SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------- /oradata/cdb2/system01.dbf /oradata/cdb2/sysaux01.dbf /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdbseed/system01.dbf /oradata/cdb2/users01.dbf /oradata/cdb2/pdbseed/sysaux01.dbf 6 rows selected. SQL> !ls -l /oradata/cdb2/CDB2/2DA9B0A41976142FE0537800000AE145/datafile total 0 SQL>上面的测试使用move,可以看到cdb1下面的pdb12数据文件被移走了
Creating a new PDB from SEED with file_name_convert
通过pdbseed创建pdb数据库pdb11
从seed中创建 SQL> select name,cdb from v$database; NAME CDB -------------------- --- CDB2 YES SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> SQL> create pluggable database pdb11 admin user pdb_admin identified by oracle file_name_convert = ('pdbseed', 'pdb11'); Pluggable database created. SQL> SQL> select name from v$datafile; NAME ------------------------------------------------------------ /oradata/cdb2/system01.dbf /oradata/cdb2/sysaux01.dbf /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdbseed/system01.dbf /oradata/cdb2/users01.dbf /oradata/cdb2/pdbseed/sysaux01.dbf /oradata/cdb2/pdb11/system01.dbf /oradata/cdb2/pdb11/sysaux01.dbf 8 rows selected. SQL> SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB11 NEW PDB$SEED NORMAL SQL> alter pluggable database pdb11 open; Pluggable database altered. SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB11 NORMAL PDB$SEED NORMAL SQL>
在同一CDB中,从现有pdb中克隆一新pdb
create pluggable database pdb12 from pdb11 file_name_convert = ('pdb11', 'pdb12'); SQL> create pluggable database pdb12 from pdb11 file_name_convert = ('pdb11', 'pdb12'); Pluggable database created. SQL> select name from v$datafile; NAME ------------------------------------------------- /oradata/cdb2/system01.dbf /oradata/cdb2/sysaux01.dbf /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdbseed/system01.dbf /oradata/cdb2/users01.dbf /oradata/cdb2/pdbseed/sysaux01.dbf /oradata/cdb2/pdb11/system01.dbf /oradata/cdb2/pdb11/sysaux01.dbf /oradata/cdb2/pdb12/system01.dbf /oradata/cdb2/pdb12/sysaux01.dbf 10 rows selected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 PDB12 MOUNTED SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB11 NORMAL PDB$SEED NORMAL PDB12 NEW SQL> alter pluggable database pdb12 open; Pluggable database altered. SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB11 NORMAL PDB$SEED NORMAL PDB12 NORMAL SQL> alter session set container=PDB12; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------- /oradata/cdb2/undotbs01.dbf /oradata/cdb2/pdb12/system01.dbf /oradata/cdb2/pdb12/sysaux01.dbf SQL> SQL> alter pluggable database pdb12 close immediate; Pluggable database altered. SQL> drop pluggable database pdb12 including datafiles; Pluggable database dropped. SQL>
Unplug and plug pdb in the same Container
在同一CDB种unplug,plug pdb数据库
alter pluggable database pdb11 close immediate; alter pluggable database pdb11 unplug into '/home/oracle/cdb2_pdb11.xml'; drop pluggable database pdb11; create pluggable database pdb11 using '/home/oracle/cdb2_pdb11.xml' nocopy; SQL> alter pluggable database pdb11 unplug into '/home/oracle/cdb2_pdb11.xml'; Pluggable database altered. SQL> drop pluggable database pdb11; Pluggable database dropped. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable database pdb11 using '/home/oracle/cdb2_pdb11.xml' nocopy; Pluggable database created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 MOUNTED SQL>
Rename PDB
根据需要,有时我们需要重命名pdb名字。重命名时需要启用限制模式
alter pluggable database pdb11 close immediate; alter pluggable database pdb11 open restricted; select con_id, name, open_mode, restricted from v$pdbs; select name from v$services; alter pluggable database rename global_name to ohsdb; select name from v$services; alter pluggable database close immediate; alter pluggable database open; select con_id, name, open_mode, restricted from v$pdbs; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO SQL> alter pluggable database pdb11 close immediate; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 MOUNTED SQL> alter pluggable database pdb11 open restricted; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE YES SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- ------------------------------ ---------- --- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE YES SQL> select name from v$services; NAME ---------------------------------------------------------------- pdb11 cdb2XDB cdb2 SYS$BACKGROUND SYS$USERS
SQL>
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2016 11:23:14
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 13-MAR-2016 20:22:47
Uptime 84 days 15 hr. 0 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/oracle/diag/tnslsnr/db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db1)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb3" has 1 instance(s).
Instance "cdb3", status READY, has 1 handler(s) for this service...
Service "cdb3XDB" has 1 instance(s).
Instance "cdb3", status READY, has 1 handler(s) for this service...
Service "pdb11" has 2 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
SQL>
SQL> alter pluggable database rename global_name to ohsdb;
alter pluggable database rename global_name to ohsdb
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database
SQL> alter session set container=pdb11; Session altered. SQL> alter pluggable database rename global_name to ohsdb; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 OHSDB READ WRITE YES SQL> select name from v$services; NAME ---------------------------------------------------------------- ohsdb SQL> alter pluggable database close immediate; Pluggable database altered. SQL> alter pluggable database open; Pluggable database altered. SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- ------------------------------ ---------- --- 3 OHSDB READ WRITE NO SQL> SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2016 11:25:25 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 13-MAR-2016 20:22:47 Uptime 84 days 15 hr. 2 min. 37 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/oracle/diag/tnslsnr/db1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db1)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb2" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "cdb2XDB" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "cdb3" has 1 instance(s). Instance "cdb3", status READY, has 1 handler(s) for this service... Service "cdb3XDB" has 1 instance(s). Instance "cdb3", status READY, has 1 handler(s) for this service... Service "ohsdb" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "pdb11" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... The command completed successfully SQL>
注意:必须在pdb内完成,在cdb中无法修改,对于RAC,要先关闭其他节点,然后才能修改,修改后,服务器和监听中信息会变更
Delete PDB
删除PDB,有2个选项,默认是保留数据文件When dropping a PDB, you can either keep or delete the PDB's data files by using one of the following clauses:
A.KEEP DATAFILES, the default, retains the data files. The PDB's temp file is removed even when KEEP DATAFILES is specified because the temp file is no longer needed. B.INCLUDING DATAFILES removes the data files from disk. If a PDB was created with the SNAPSHOT COPY clause, then you must specify INCLUDING DATAFILES when you drop the PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 MOUNTED
SQL> drop pluggable database pdb11 including datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
Clone NON-CDB to PDB by DBLINK
通过dblink把NON-CDB转化为CDB数据库的pdb。CDB3是非cdb数据库,cdb1是cdb数据库,通过在cdb1上建立dblink,可以把cdb3克隆成功cdb1的一个pdb
非cdb数据库CDB3上的操作
select cdb,name from v$database; create user clone_admin identified by oracle; grant create session, create pluggable database to clone_admin; shutdown immediate; alter database open read only; SQL> select name,cdb from v$database; NAME CDB --------- --- CDB3 NO SQL> create user clone_admin identified by oracle; User created. SQL> grant create session, create pluggable database to clone_admin; Grant succeeded. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 394264576 bytes Fixed Size 2924832 bytes Variable Size 260050656 bytes Database Buffers 125829120 bytes Redo Buffers 5459968 bytes Database mounted. SQL> alter database open read only; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/cdb3/system01.dbf /oradata/cdb3/sysaux01.dbf /oradata/cdb3/undotbs01.dbf /oradata/cdb3/users01.dbf SQL> SQL> exit; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@db1 ~]$
cdb数据库CDB1上的操作
select name, pdb from v$services order by pdb, name;
select name,open_mode,restricted from gv$containers;
select name, open_mode, inst_id from gv$pdbs;
create database link clone_none_cdb connect to clone_admin identified by oracle using '(description= (address=(protocol=tcp)(host=db1) (port=1521)) (connect_data=(service_name=cdb3)))';
SQL> select name, pdb from v$services order by pdb, name; NAME PDB ---------------------------------------- ------------------------------ SYS$BACKGROUND CDB$ROOT SYS$USERS CDB$ROOT cdb1 CDB$ROOT cdb1XDB CDB$ROOT pdb11 PDB11 SQL> SQL> select name,open_mode,restricted from gv$containers; NAME OPEN_MODE RES ---------------------------------------- ---------- --- CDB$ROOT READ WRITE NO PDB$SEED READ ONLY NO PDB11 READ WRITE NO SQL> SQL> select name, open_mode, inst_id from gv$pdbs; NAME OPEN_MODE INST_ID ---------------------------------------- ---------- ---------- PDB$SEED READ ONLY 1 PDB11 READ WRITE 1 SQL> SQL> select name,cdb from v$database; NAME CDB --------- --- CDB1 YES SQL> create database link clone_none_cdb connect to clone_admin identified by oracle using '(description= (address=(protocol=tcp)(host=db1) (port=1521)) (connect_data=(service_name=cdb3)))'; Database link created. SQL> select * from dual@clone_none_cdb; D - X SQL> SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/cdb1/system01.dbf /oradata/cdb1/sysaux01.dbf /oradata/cdb1/undotbs01.dbf /oradata/cdb1/pdbseed/system01.dbf /oradata/cdb1/users01.dbf /oradata/cdb1/pdbseed/sysaux01.dbf /oradata/cdb1/pdb11/system01.dbf /oradata/cdb1/pdb11/sysaux01.dbf /oradata/cdb1/pdb11/pdb11_users01.dbf 9 rows selected. SQL>
create pluggable database cdb2pdb from non$cdb@clone_none_cdb file_name_convert = ('/oradata/cdb3/', '/oradata/cdb1/cdb2pdb/');
SQL> create pluggable database cdb2pdb from non$cdb@clone_none_cdb file_name_convert = ('/oradata/cdb3/', '/oradata/cdb1/cdb2pdb/'); Pluggable database created. SQL>
数据库alert日志
create pluggable database cdb2pdb from non$cdb@clone_none_cdb file_name_convert = ('/oradata/cdb3/', '/oradata/cdb1/cdb2pdb/') 2016-06-06 13:09:15.004000 +08:00 **************************************************************** Pluggable Database CDB2PDB with pdb id - 4 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped **************************************************************** Database Characterset for CDB2PDB is AL32UTF8 Deleting old file#1 from file$ Deleting old file#2 from file$ Deleting old file#3 from file$ Deleting old file#4 from file$ Deleting old file#5 from file$ Deleting old file#6 from file$ Adding new file#14 to file$(old file#1) Adding new file#15 to file$(old file#3) Adding new file#16 to file$(old file#6) Marking tablespace #2 invalid since it is not present in the describe file 2016-06-06 13:09:16.025000 +08:00 Successfully created internal service cdb2pdb at open ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local **************************************************************** Post plug operations are now complete. Pluggable database CDB2PDB with pdb id - 4 is now marked as NEW. **************************************************************** Completed: create pluggable database cdb2pdb from non$cdb@clone_none_cdb file_name_convert = ('/oradata/cdb3/', '/oradata/cdb1/cdb2pdb/')执行noncdb_to_pdb.sql
select name, open_mode, inst_id from gv$pdbs; alter session set container=db12c2pdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO 4 CDB2PDB MOUNTED SQL> select name, open_mode, inst_id from gv$pdbs; NAME OPEN_MODE INST_ID ---------------------------------------- ---------- ---------- PDB$SEED READ ONLY 1 PDB11 READ WRITE 1 CDB2PDB MOUNTED 1 SQL> alter session set container=CDB2PDB; Session altered. SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql SQL> SET SERVEROUTPUT ON SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> ... ... ... SQL> alter session set container = "&pdbname"; Session altered. SQL> SQL> -- leave the PDB in the same state it was when we started SQL> BEGIN 2 execute immediate '&open_sql &restricted_state'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 BEGIN 6 IF (sqlcode <> -900) THEN 7 RAISE; 8 END IF; 9 END; 10 END; 11 / PL/SQL procedure successfully completed. SQL> SQL> SQL> WHENEVER SQLERROR CONTINUE; SQL> SQL> SQL> SQL> SQL> SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 CDB2PDB MOUNTED SQL>
数据库alert文件内容
alter pluggable database "CDB2PDB" open upgrade Pluggable database CDB2PDB dictionary check beginning Tablespace 'UNDOTBS1' #2 found in data dictionary, but not in the controlfile. Adding to controlfile. Pluggable Database CDB2PDB Dictionary check complete Database Characterset for CDB2PDB is AL32UTF8 Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32 ... ... ... 2016-06-06 13:29:53.672000 +08:00 Opening pdb CDB2PDB (4) with no Resource Manager plan active ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; Resource Manager disabled during database migration: plan '' not set ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY; ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY; Pluggable database CDB2PDB opened read write Completed: alter pluggable database "CDB2PDB" open upgrade 2016-06-06 13:29:54.890000 +08:00 alter pluggable database "CDB2PDB" close ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local Pluggable database CDB2PDB closed Completed: alter pluggable database "CDB2PDB" close
注意:如果是从单节点非CDB到RAC CDB,你应该运行脚本$ORACLE_HOME/rdbms/admin/catclust.sql
更多详情,请参考http://docs.oracle.com/database/121/RACAD/cvt2rac.htm#RACAD8860
Drop CDBSQL> startup mount restrict; ORACLE instance started. Total System Global Area 394264576 bytes Fixed Size 2924832 bytes Variable Size 264244960 bytes Database Buffers 121634816 bytes Redo Buffers 5459968 bytes Database mounted. SQL> select name,cdb from v$database; NAME CDB --------- --- CDB2 YES SQL> drop database; Database dropped. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> 2016-06-06 13:46:31.221000 +08:00 Create Relation ADR_CONTROL Create Relation ADR_INVALIDATION Create Relation INC_METER_IMPT_DEF Create Relation INC_METER_PK_IMPTS USER (ospid: 24222): terminating the instance Instance terminated by USER, pid = 24222 2016-06-06 13:46:36.667000 +08:00 Deleted file /oradata/cdb2/control01.ctl Deleted file /oradata/cdb2/control02.ctl Completed: drop database Shutting down instance (abort) License high water mark = 1 Instance shutdown complete
Reference
https://docs.oracle.com/database/121/ADMIN/cdb_intro.htm#ADMIN13507
https://docs.oracle.com/database/121/CNCPT/toc.htm
https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234
https://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13549