Create/Delete/Rename/Clone/Convert to PDB-Oracle 12c+-ohsdba

You can use the Oracle Multitenant option to configure and manage a multitenant environment. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

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>

Cloning an existing PDB to a new PDB in the same container database
在同一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 CDB

SQL> 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

Create/Delete/Rename/Clone/Convert to PDB

相关文章

推荐文章

联系我们