Database 12c: Create Container Database


To create a CDB there are two methods to do so:

  1. Create CDB with DBCA
  2. Create CDB manually

1. Create CDB with DBCA

To create a CDB with DBCA , there is nothing special to do. If you install in Basic Mode, you are able to configure CDB on the start site:

createcdb1

If you are installing in Advanced Mode you are able on the second site:

createcdb2

2. Create CDB manually or with “CREATE DATABASE”

1. Setup init file with the according parameters. In my example I used the OFM creation method:

*.db_create_file_dest='/opt/oracle/app/oradata'
*.enable_pluggable_database=TRUE

2. Execute the “CREATE DATABASE” statement with the “ENABLE PLUGGABLE DATABASE”:

CREATE DATABASE db12ee2
USER SYS IDENTIFIED BY welcome1
USER SYSTEM IDENTIFIED BY welcome1
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
   SEED
   SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   SYSAUX DATAFILES SIZE 100M;

3. Execute catcdb.sql

@?/rdbms/admin/catcdb.sql

This step is a problem right now, because the required file catcdb.sql is not shipped with the installation. The problem is already in progress:
Bug 17033183 – $OH/rdbms/admin/catcdb.sql is missing from 12c release (Doc ID 17033183.8)

Database 12c: Common and local users and roles


Since 12c “nothing” is like before. Today we are talking about the user creation. The user beavior in a Non-CDB is like in 11g and before. In CDBs and PDBs the concept slightly changes. In a multitenant database users will be divided into two different types: Local and common users:

commonuser

Common users

A common user is user which is present in the CDB and all PDBs. Here from documentation:

“A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.”

Special users are sys and system. Common users have the same characteristic in all instances (e.g. password, tablespace and so on)

Create a new common user:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create user C##TEST1 identified by test1 container = all;

User created.

Common users must start with c## or C##

SQL> create user TEST1 identified by bubu1 container=all;
create user TEST1 identified by bubu1 container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Changes to common users apply to all container:

SQL> create user C##TEST1 identified by test1 container = all default tablespace users;
create user C##TEST1 identified by test1 container = all default tablespace users
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1
ORA-00959: tablespace 'USERS' does not exist

The password is the same in CDB and all PDBs, when trying to change you get an error:

QL> alter user C##TEST1 identified by bubu1;
alter user C##TEST1 identified by bubu1
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

Changes to common users are only allowed in CDB

SQL> alter user C##TEST1 identified by bubu1 container=all;
alter user C##TEST1 identified by bubu1 container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

Local users

Local users are users which are only exsits in a PDB but not in all containers.

Create local users:

SQL> create user TEST1 identified by bubu1;

User created.

SQL> create user TEST2 identified by bubu1 container=current;

User created.

SQL> 

In CDBs no local users can be created:

SQL> create user TEST1 identified by bubu1 container=current;
create user TEST1 identified by bubu1 container=current
                                *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

The username for a local user must not start with c## or C##

SQL> create user C##TEST1 identified by bubu1;
create user C##TEST1 identified by bubu1
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

SQL> create user C##TEST1 identified by bubu1 container=current;
create user C##TEST1 identified by bubu1 container=current
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

Common roles

Common roles are like common users roles which are present in CDB and all PDBs. Important is that the privileges are granted at CDB or PDB level, but not over all containers, per default.

SQL> create role C##ROLE1 container=all;

Role created.

SQL> grant create session to C##ROLE1;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';
GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

no rows selected

SQL> 

To grant privileges over all containers:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> grant create table to C##ROLE1 container=all;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO
C##ROLE1                       CREATE TABLE                   YES

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE TABLE                   YES

SQL> 

Privileges granted in a pdb will only available in a pdb:

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE TABLE                   YES

SQL> grant create view to C##ROLE1;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE VIEW                    NO
C##ROLE1                       CREATE TABLE                   YES

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO
C##ROLE1                       CREATE TABLE                   YES

SQL> 

Database 12c: Convert Non-CDB with different character set to PDB


Today I want to discuss converting of a Non-CDB with different character set to a PDB. I think this a very interesting part, because it is possible. So you are able to provide multiple Character Sets in one database (maybe). Like in every characterset migration it depends on the character set you want to migrate to.

Here the limits for PDB migration:

  • The character set is the same as the national character set of the CDB. In this case, the plugging operation succeeds (as far as the national character set is concerned).
  • The character set is not the same as the national character set of the CDB. In this case, the new PDB can only be opened in restricted mode for administrative tasks and cannot be used for production. Unless you have a tool that can migrate the national character set of the new PDB to the character set of the CDB, the new PDB is unusable.

“If you cannot migrate your existing databases prior to consolidation, then you have to partition them into sets with plug-in compatible database character sets and plug each set into a separate CDB with the appropriate superset character set”:

  • US7ASCII, WE8ISO8859P1, and WE8MSWIN1252 into a WE8MSWIN1252 CDB
  • WE8ISO8859P15 into a WE8ISO8859P15 CDB
  • JA16SJISTILDE into a JA16SJISTILDE CDB
  • JA16EUC into a JA16EUC CDB
  • KO16KSC5601, KO16MSWIN949 into a KO16MSWIN949 CDB
  • UTF8 and AL32UTF8 into an AL32UTF8 CDB

Here an example of PDB_PLUG_IN_VIOLATIONS for a compatible PDB:

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
DB12CEE4                       Database CHARACTER SET WARNING   Character set mismatch: PDB character set UTF8 CDB character set AL32UTF8.                           PENDING
DB12CEE4                       Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
DB12CEE4                       Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 2768240640                             PENDING
DB12CEE4                       Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 917504000                    PENDING

Migrating from UTF8 to AL32UTF8, after plug-in the database characterset has been changed automatical (it seems to be -> maybe during dictionary check):

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               AL32UTF8

Here an example of PDB_PLUG_IN_VIOLATIONS for a non compatible PDB:

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
DB12CEE1                       Database CHARACTER SET ERROR     Character set mismatch: PDB character set WE8MSWIN1252 CDB character set AL32UTF8.                   PENDING
DB12CEE1                       Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
DB12CEE1                       Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 2768240640                             PENDING
DB12CEE1                       Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 917504000                    PENDING

Migrating from WE8MSWIN1252 to AL32UTF8, after plug-in the database characterset nothing changed:

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               WE8MSWIN1252

As documentated plug-in and convert of both were possible. Example 1 successfully and example 2 with errors in cause of the characterset. Notice: Productive use of the second example is not supported, but possible. Why?

  • R/W open only in restricted session mode –> Maybe user connect as SYSDBA?
  • R/O open possible –> Maybe you can use it for Reporting, without changing characterset?
  • RMAN Backup is possible

Why do I mention this? If you doesn’t take care on the CS during migration to PDB the way back is not as easy as it should be. Of course in this case I have to export and import in another PDB, but I can’t physically migrate back. I have to two options to get to an usable result (WE8MSWIN1252 –>AL32UTF8)

  1. AL32UTF8 is a logical superset and migrationable with export/import procedure
  2. Migration with replication

Directly convert with plug-in is possible but only a usable step for this case, because I’vh to do the migration again in a logical form.

Conslusion: Take care on the characterset before migration to PDB. Not convertable charactersets need an convert be for an logical and no physical or partly physical migration way.

Database 12c: Convert Non-CDB to PDB


To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB or use Datapump or a tool for logical migration. The conversion process is similar to unplug and plug of a pdb.

I’vh tried the following ways, which are all possible:

  • 11.2.0.3 –> 12.1.0.1 (First upgrade to a Non-CDB and then convert)
  • 12.1.0.1 EE Non-CDB –> 12.1.0.1 EE
  • 12.1.0.1 SE Non-CDB –> 12.1.0.1 EE
  • 12.1.0.1 EE Non-CDB –> 12.1.0.1 SE
  • 12.1.0.1 SE Non-CDB –> 12.1.0.1 SE

Ok, let’s start.

1. Bring Non-CDB in a consistent state:

[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 18 23:30:06 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2296280 bytes
Variable Size 692061736 bytes
Database Buffers 343932928 bytes
Redo Buffers 5595136 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

2. Create the export XML

SQL>
BEGIN
 DBMS_PDB.DESCRIBE(
 pdb_descr_file => '/opt/oracle/unplug/db12c1_convert.xml');
END;
/
PL/SQL procedure successfully completed.

Take a look into the XML File, check the last important part (bold marked):

<?xml version="1.0" encoding="UTF-8"?>
<PDB>
 <pdbname>DB12C1</pdbname>
 <cid>0</cid>
 <byteorder>1</byteorder>
 <vsn>202375168</vsn>
 <dbid>4126847348</dbid>
 <cdbid>4126847348</cdbid>
 <guid>E1CE01B6E8AB0B36E0430100007F25C7</guid>
 <uscnbas>1759919</uscnbas>
 <uscnwrp>0</uscnwrp>
 <rdba>4194824</rdba>
 <tablespace>
 <name>SYSTEM</name>
 <type>0</type>
 <tsn>0</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_system_8yjcbjpj_.dbf</path>
 <afn>1</afn>
 <rfn>1</rfn>
 <createscnbas>7</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>99840</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>SYSAUX</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_sysaux_8yjc93j2_.dbf</path>
 <afn>3</afn>
 <rfn>3</rfn>
 <createscnbas>3922</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>89600</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>TEMP</name>
 <type>1</type>
 <tsn>3</tsn>
 <status>1</status>
 <issft>0</issft>
 <bmunitsize>128</bmunitsize>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_temp_8yjcdrw0_.tmp</path>
 <afn>1</afn>
 <rfn>1</rfn>
 <createscnbas>1720182</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>7680</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <autoext>1</autoext>
 <maxsize>4194302</maxsize>
 <incsize>80</incsize>
 </file>
 </tablespace>
 <tablespace>
 <name>USERS</name>
 <type>0</type>
 <tsn>4</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_users_8yjccxy4_.dbf</path>
 <afn>6</afn>
 <rfn>6</rfn>
 <createscnbas>26029</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>640</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <optional>
 <csid>178</csid>
 <ncsid>2000</ncsid>
 <options>
 <option>APS=12.1.0.1.0</option>
 <option>CATALOG=12.1.0.1.0</option>
 <option>CATJAVA=12.1.0.1.0</option>
 <option>CATPROC=12.1.0.1.0</option>
 <option>CONTEXT=12.1.0.1.0</option>
 <option>DV=12.1.0.1.0</option>
 <option>JAVAVM=12.1.0.1.0</option>
 <option>OLS=12.1.0.1.0</option>
 <option>ORDIM=12.1.0.1.0</option>
 <option>OWM=12.1.0.1.0</option>
 <option>SDO=12.1.0.1.0</option>
 <option>XDB=12.1.0.1.0</option>
 <option>XML=12.1.0.1.0</option>
 <option>XOQ=12.1.0.1.0</option>
 </options>
 <olsoid>0</olsoid>
 <dv>0</dv>
 <ncdb2pdb>1</ncdb2pdb>
 <APEX>4.2.0.00.27:1</APEX>
 <parameters>
 <parameter>processes=300</parameter>
 <parameter>memory_target=1048576000</parameter>
 <parameter>db_block_size=8192</parameter>
 <parameter>compatible=12.1.0.0.0</parameter>
 <parameter>open_cursors=300</parameter>
 </parameters>
 <tzvers>
 <tzver>primary version:18</tzver>
 <tzver>secondary version:0</tzver>
 </tzvers>
 <walletkey>0</walletkey>
 </optional>
</PDB>

3. Shutdown source database and login in the CDB
4. Check compatibility

SET SERVEROUTPUT ON
DECLARE
 compatible CONSTANT VARCHAR2(3) := 
 CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
 pdb_descr_file => '/opt/oracle/unplug/db12c1_convert.xml',
 pdb_name => 'NCDB1')
 WHEN TRUE THEN 'YES'
 ELSE 'NO'
END;
BEGIN
 DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO

PL/SQL procedure successfully completed.

SQL> 
/

If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB1'

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
NCDB1                          OPTION                 WARNING   Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
NCDB1                          Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 1979711488                             PENDING
NCDB1                          Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 655360000                    PENDING

6 rows selected.

There are WARNING’s only. We can continue.

5. Plug-in Non-CDB:
5.1 Use the NOCOPY Mode:

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' NOCOPY;
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file
'/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_temp_8yjcdrw0_.tmp'

If use NOCOPY you can delete the tempfile or reuse it

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmpwox6_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmptylz_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/pdbseed/system01.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_users_8xmpyf7f_.dbf
/opt/oracle/app/oradata/pdbseed/sysaux01.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_system_8yjcbjpj_.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_sysaux_8yjc93j2_.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_users_8yjccxy4_.dbf

9 rows selected.

5.2 Use the COPY mode

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' COPY;

Pluggable database created.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmpwox6_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmptylz_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/pdbseed/system01.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_users_8xmpyf7f_.dbf
/opt/oracle/app/oradata/pdbseed/sysaux01.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_system_8yjr6xjs_.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_sysaux_8yjr7262_.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_users_8yjr766m_.dbf

6. Switch containert to PDB and start convert script:

SQL> alter session set container = NCDB1;

Session altered.

SQL> @?/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> WHENEVER SQLERROR EXIT;
SQL> 
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01403: no data found"
DOC> error if we're not in a PDB.
DOC> This script is intended to be run right after plugin of a PDB,
DOC> while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> 
SQL> VARIABLE cdbname VARCHAR2(128)
SQL> VARIABLE pdbname VARCHAR2(128)
SQL> BEGIN
 2 SELECT sys_context('USERENV', 'CDB_NAME')
 3 INTO :cdbname
 4 FROM dual
 5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
 6 SELECT sys_context('USERENV', 'CON_NAME')
 7 INTO :pdbname
 8 FROM dual
 9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
 10 END;
 11 /

PL/SQL procedure successfully completed.

SQL> 
SQL> COLUMN pdbname NEW_VALUE pdbname
SQL> COLUMN pdbid NEW_VALUE pdbid
SQL> 
SQL> select :pdbname pdbname from dual;

PDBNAME
--------------------------------------------------------------------------------
NCDB1

1 row selected.
...
script is running some very interesting things
...
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> WHENEVER SQLERROR CONTINUE;
SQL>

7. Now you can open the PDB, there may occur an error:

SQL> show con_name

CON_NAME
------------------------------
NCDB1

SQL> alter database open read write;
alter database open read write
*
ERROR at line 1:
ORA-24344: success with compilation error

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

1 row selected.

In alertlog you will see
********************************************** ******************
WARNING: Pluggable Database NCDB1 with pdb id - 3 is altered with warnings.
Please look into PDB_PLUG_IN_VIOLATIONS view for more details.
****************************************************************

8. To correct the ORA-24344 check PDB_PLUG_IN_VIOLATIONS, correct them and resync

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter pluggable database ncdb1 close;

Pluggable database altered.

SQL> alter pluggable database ncdb1 open restricted;

Pluggable database altered.

SQL> exec dbms_pdb.sync_pdb();

PL/SQL procedure successfully completed.

SQL> alter pluggable database ncdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database ncdb1 open;

Pluggable database altered.

9. Recheck open problems

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB1';

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
NCDB1                          Non-CDB to PDB         ERROR     PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      RESOLVED
NCDB1                          OPTION                 WARNING   Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING

9. Done. PDB is ready to use. Don’t forget to backup the new database.

Database 12c: Pluggable database autostart like HAS


Today I discussed with a college the problem of a simple autostart trigger like I posted here.

After some brainstorming I’vh written a new example that acts like oracle HAS (High Availability¬†Service). The pluggable database should have the same state as after a reboot sequence.

Here the coding example:

First create a table in the CDB:

create table pdbs_autostart (
 con_id      number,
 dbid        number,
 name        varchar2(30),
 open_mode   varchar2(10),
 restricted  varchar2(3),
 locked      varchar2(3),
 error       varchar2(200)
);

Further create a startup and shutdown trigger:
Startup trigger:

CREATE OR REPLACE TRIGGER start_pdbs
   AFTER STARTUP ON DATABASE
DECLARE
  opencmd varchar2(100);
  v_errm  varchar2(200);
BEGIN
  for db in (select * from pdbs_autostart) loop
    if db.locked != 'YES' and db.name !='PDB$SEED' and db.open_mode != 'MOUNTED' then
      opencmd := 'alter pluggable database '||db.name||' OPEN '||db.open_mode;
      if db.restricted = 'YES' then
        opencmd := opencmd||' RESTRICTED';
      end if;

      BEGIN
        execute immediate opencmd;
        update pdbs_autostart set error='NO ERROR' where name = db.name;
      EXCEPTION WHEN OTHERS THEN
        v_errm := SUBSTR(SQLERRM, 1 , 200);
        update pdbs_autostart set error=v_errm where name = db.name;
      END;
    else
      update pdbs_autostart set error='Skipped' where name = db.name;
    end if;
  end loop;
  commit;
END;
/

Shutdown tigger:

CREATE OR REPLACE TRIGGER shutdown_pdbs
  BEFORE SHUTDOWN ON DATABASE
BEGIN 
  merge into  pdbs_autostart a
  using       v$pdbs         b
  on          (a.dbid = b.dbid)
  when matched then
    update set a.con_id = b.con_id,a.name = b.name,a.open_mode=b.open_mode,a.restricted=nvl(b.restricted,'NO'),error='' where a.locked = 'NO'
    delete where b.dbid not in (select dbid from v$pdbs )
  when not matched then
    insert values (b.con_id,b.dbid,b.name,b.open_mode,b.restricted,'NO','')
  ;  
  commit;
END; 
/

At every shutdown the state of the database will be written into the table pdbs_autostart. After startup of database the trigger starts all database as saved in table.

I’vh also added an LOCKED column to prevent state change at shutdown. This is useful if you want to permanent save your configuration. Alternativ you can disable the shutdown trigger.

If an error occurs during startup, it will be written in the error column.

Here the construction in action:

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           MOUNTED
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

Check the actual state, then start PDB3 and PDB5.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> alter pluggable database pdb5 open;

Pluggable database altered.

Check configuration. PDB3 state should be changed at shutdown and should startup at next restart. PDB5 ist LOCKED and should not be overruled at next startup. It will not be started at next time.

SQL> select * from pdbs_autostart;

    CON_ID       DBID NAME                           OPEN_MODE  RES LOCKED ERROR
---------- ---------- ------------------------------ ---------- --- ------ -----------------------------------------------------------------
         3 3328312323 PDB3                           MOUNTED    NO  NO  
         2 4062846046 PDB$SEED                       READ ONLY  NO  NO  
         4 3887199318 PDB2                           READ ONLY  NO  NO  
         7 3881397024 PDB5                           MOUNTED        YES 
         6 3831633646 PDB4                           READ WRITE NO  NO  
         5 3328633065 PDB1                           READ WRITE NO  NO  

6 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1970864128 bytes
Fixed Size                  2289928 bytes
Variable Size             587206392 bytes
Database Buffers         1375731712 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           READ WRITE
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

SQL>

It works! Have fun!

Database 12c: Plug and Unplug PDB


To unplug pdb the pdb must be close. So you can close the original pdb or clone and unplug the database. The basic unplug and plug mechanism I’m going to describe in this article:

1. Prepare to unplug

[bash]$ mkdir /opt/oracle/unplug
[bash]$ set ORACLE_SID=CDB12C1
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

2. Now unplug database

SQL> alter pluggable database pdb1 unplug into '/opt/oracle/unplug/pdb1.xml';

Pluggable database altered.

Take a look into the new generated XML file

cat /opt/oracle/unplug/pdb1.xml 
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
 <pdbname>PDB1</pdbname>
 <cid>3</cid>
 <byteorder>1</byteorder>
 <vsn>202375168</vsn>
 <dbid>3328312323</dbid>
 <cdbid>4018805978</cdbid>
 <guid>E157C0F3C35907C7E0430100007F6C6A</guid>
 <uscnbas>2082581</uscnbas>
 <uscnwrp>0</uscnwrp>
 <rdba>4194824</rdba>
 <tablespace>
 <name>SYSTEM</name>
 <type>0</type>
 <tsn>0</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf</path>
 <afn>51</afn>
 <rfn>1</rfn>
 <createscnbas>2077841</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>33280</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>SYSAUX</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0vf56k_.dbf</path>
 <afn>52</afn>
 <rfn>4</rfn>
 <createscnbas>2077844</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>81920</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>TEMP</name>
 <type>1</type>
 <tsn>2</tsn>
 <status>1</status>
 <issft>0</issft>
 <bmunitsize>128</bmunitsize>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_temp_8y0vf96l_.dbf</path>
 <afn>3</afn>
 <rfn>1</rfn>
 <createscnbas>2077842</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>0</status>
 <fileblocks>12800</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <autoext>1</autoext>
 <maxsize>262144</maxsize>
 <incsize>6400</incsize>
 </file>
 </tablespace>
 <optional>
 <csid>873</csid>
 <ncsid>2000</ncsid>
 <options>
 <option>APS=12.1.0.1.0</option>
 <option>CATALOG=12.1.0.1.0</option>
 <option>CATJAVA=12.1.0.1.0</option>
 <option>CATPROC=12.1.0.1.0</option>
 <option>CONTEXT=12.1.0.1.0</option>
 <option>DV=12.1.0.1.0</option>
 <option>JAVAVM=12.1.0.1.0</option>
 <option>OLS=12.1.0.1.0</option>
 <option>ORDIM=12.1.0.1.0</option>
 <option>OWM=12.1.0.1.0</option>
 <option>SDO=12.1.0.1.0</option>
 <option>XDB=12.1.0.1.0</option>
 <option>XML=12.1.0.1.0</option>
 <option>XOQ=12.1.0.1.0</option>
 </options>
 <olsoid>0</olsoid>
 <dv>0</dv>
 <ncdb2pdb>0</ncdb2pdb>
 <APEX>4.2.0.00.27:1</APEX>
 <parameters>
 <parameter>processes=300</parameter>
 <parameter>sga_target=1979711488</parameter>
 <parameter>db_block_size=8192</parameter>
 <parameter>compatible=12.1.0.0.0</parameter>
 <parameter>open_cursors=300</parameter>
 <parameter>pga_aggregate_target=655360000</parameter>
 <parameter>enable_pluggable_database=TRUE</parameter>
 </parameters>
 <tzvers>
 <tzver>primary version:18</tzver>
 <tzver>secondary version:0</tzver>
 </tzvers>
 <walletkey>0</walletkey>
 </optional>
</PDB>

As you can see is the unplug an plug process only an XML? No normally you should clone the pdb or restore the datafiles to an alternativ host and/or location. In my example I’m going to leave the datafiles on the same location.

3. Check state of PDB in source CDB

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520
         3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB1                           MOUNTED        12-JUL-13 11.58.34.430 PM                                                      2077841          0

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

SQL> drop pluggable database pdb1;

Pluggable database dropped.

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520

4. Now plug-in the PDB in another CDB

[bash]$ set ORACLE_SID=CDB12C2
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If the database plug-in has been done all the Datafiles will be copied:

SQL> select con_id,name from v$datafile order by 1;

 CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vsncx_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vr78k_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_undotbs1_8y0vv2ls_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_users_8y0vv1ht_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vvx50_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vvx4q_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0zcmfn_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0zcmfc_.dbf

8 rows selected.

To compare the datafile of the SYSTEM tablespace from source:

/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf

Notice: The GUID of the PDB will be the same in the new CDB!

Side effect, replug

An interesting side effect is, that you are able to replug the database after it has been dropped:

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> drop pluggable database pdb2;

Pluggable database dropped.

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If you are unintentionally try to plugin the same PDB again an error will be raised:

SQL> create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml';
create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.