Create Pluggable Database using the seed database as a template

In my previous posts, I combined tools SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c for many different tasks. They allow me to connect to the pluggable database PDBORCL, to create PL/SQL subprograms and packages, to run SQL queries, DML, and DDL statements. I also showed how to make the PDBORCL pluggable database larger by adding a new tablespace to it.

All of that was the introduction to this post as I am going to demonstrate how to create a new pluggable database.

Oracle database version 12.1.0.2 introduces 3 new SQL DDL statements:
– CREATE PLUGGABLE DATABASE
– ALTER PLUGGABLE DATABASE
– DROP PLUGGABLE DATABASE

The CREATE PLUGGABLE DATABASE statement enables us to perform the following tasks:
1) Create a PDB by using the seed pluggable database PDBSEED as a template.
The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
2) Create a PDB by cloning an existing PDB or non-CDB.
The files associated with the existing PDB or non-CDB are copied to a new location    and the copied files are associated with the new PDB. Here, it should be noticed that creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (12.1.0.2).
3) Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB.
This option is done by  using an XML metadata file.

The pluggable database has four different open modes:
1) READ WRITE
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.
2) READ ONLY
A PDB in open read-only mode allows queries but does not allow user changes.
3) MIGRATE
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.
4) MOUNTED
When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible.

To remove a PDB from a CDB the following should be done:
– Unplug the PDB from a CDB.
– Drop the PDB.

task#11 Connect to the Container database CDB$ROOT as SYSDBA and create a pluggable database “BOOKSTORE” by using the seed pluggable database PDBSEED as a template.

step#1 Create BOOKSTORE database

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN"
4  file_name_convert = ('C:\app\orauser\oradata\orcl\pdbseed\',
5  'C:\app\orauser\oradata\orcl\bookstore\');

Pluggable database created.

The other way to create the pluggable database “BOOKSTORE” is to omitt the clause FILE_NAME_CONVERT.
In that case, the database will attempt to use the Oracle Managed Files or the PDB_FILE_NAME_CONVERT initialization parameter.
In my environment, the Oracle Managed Files are not specified, so the database will attempt to use the parameter PDB_FILE_NAME_CONVERT which could be defined by using statements ALTER SYSTEM or ALTER SESSION. For this task I will be using ALTER SESSION statement

SYS@orcl > ALTER SESSION SET PDB_FILE_NAME_CONVERT = ('C:\app\orauser\oradata\orcl\pdbseed\',
2  'C:\app\orauser\oradata\orcl\bookstore\');

Session altered.

Now, the CREATE PLUGGABLE DATABASE statement is simple

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN";

Pluggable database created.

If the parameter PDB_FILE_NAME_CONVERT is not set, then an error will occur after the CREATE PLUGGABLE DATABASE statement.
step#2 Open BOOKSTORE database in READ WRITE mode

SYS@orcl > alter pluggable database "BOOKSTORE" open read write;

Pluggable database altered.

step#3 Prove that CDB$ROOT now contains the seed and two PDBs

SYS@orcl > show PDBS

CON_ID      CON_NAME             PEN MODE  RESTRICTED
---------- --------------------  ---------- ----------
2          PDB$SEED              READ ONLY  NO
3          PDBORCL               READ WRITE NO
4          BOOKSTORE             READ WRITE NO

step4# Show the open time of PDBs

SYS@orcl > select substr(name,1,10) name
, con_id, open_mode
, to_char(open_time,'DD-MON-YYYY') open_time
,total_size
from v$pdbs;

NAME           CON_ID OPEN_MODE  OPEN_TIME   TOTAL_SIZE
---------- ---------- ---------- ----------- ----------
PDB$SEED            2 READ ONLY  10-SEP-2015  975175680
PDBORCL             3 READ WRITE 10-SEP-2015 2747269120
BOOKSTORE           4 READ WRITE 10-SEP-2015  996147200

step5# Start SQL Developer and show that CDB$ROOT has two pluggable databases: PDBORCL and BOOKSTORE

The Container Database

The Container Database

step#6 Open the list Datafiles in the DBA View and show that there is no additional datafiles for CDB$ROOT.

Datafiles

Datafiles

step#7 Open the list Tablespaces in the DBA View and show that there is no added tablespaces for CDB$ROOT either.

Tablespaces

Tablespaces

step#8 Create a new connection to BOOKSTORE database in the Connection View

Create Connection

Create Connection

The following elements should be specified in order to create a connection: the connection name,  the user name,  the password,  connection type and role, the hostname, the port, and the service name.

The Connection has been Created

The Connection has been Created

We are connected to the BOOKSTORE database which is empty of the user elements for now.

step#9 Click on the bookstore connection and choose the option Manage Database to show BOOKSTORE Tablespaces.

ManageDatabase

Manage Database

step#10 BOOKSTORE contains the following tablespaces: SYSTEM, SYSAUX, and TEMP. In order to create any schema, BOOKSTORE needs a new tablespace for users tables and other schema elements.

BOOKSTORE tablespaces

BOOKSTORE tablespaces

step#11 Open the Database Configuration Assistant and create EM12c port 5504 as the entry point for BOOOKSTORE database.

The Configuration Assistant

The Configuration Assistant

step#12 Open the browser with “localhost:5504/em” and login to BOOKSTORE as SYSDBA

BOOKSTORE EM12c

BOOKSTORE EM12c

step#13 Show how many tablespaces are there in the BOOKSTORE database

Tablespaces Of BOOKSTORE

Tablespaces Of BOOKSTORE

step#14 Create a new tablespace “USER”

Create Tablespace USER

Create Tablespace USER

step#15 The USER tablespace is created and we are ready to create the very first schema in the BOOKSTORE database.

The USER tablespace

The USER tablespace

step#16 Back to SQL Developer again, the BOOKSTORE database is ready for the new elements.

SQL Developer worksheet

SQL Developer worksheet

step#17 Click on the BOOKSTORE connection in the Connect View and choose the option Create User.

Create Schema

Create Schema

step#18 Enter the name, the password, roles, privileges, quotas, and default and temporary tablespace for the new user.

Enter details about the schema

Enter details about the schema

The following SQL CREATE USER statement will be automatically created and executed and the user “CODE” will be created

CREATE USER code IDENTIFIED BY code
DEFAULT TABLESPACE "USER"
TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO code ;
GRANT "RESOURCE" TO code ;
ALTER USER code DEFAULT ROLE "CONNECT","RESOURCE";

step#19 Open the Oracle Network Manager and create the service @bookstore,
enter the connection type, protocol, the host name, and the port number.

Net Manager

Net Manager

step#20 Use SQL*Plus and connect to the BOOKSTORE database as user code/code@bookstore

@bookstore

@bookstore

step#21 Show The Oracle Listener configuration

C:\temp>lsnrctl

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 10-SEP-2015 23:13:18

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start           stop            status          services
version         reload          save_config     trace
quit            exit            set*            show*

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                10-SEP-2015 15:04:56
Uptime                    0 days 8 hr. 8 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\orauser\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\orauser\diag\tnslsnr\HPblue\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5502))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HPblue)(PORT=5504))(Security=(my_wallet_directory=C:\APP\ORAUSER\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:68 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: HPBLUE, pid: 2564>
(ADDRESS=(PROTOCOL=tcp)(HOST=HPblue)(PORT=49169))
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:50 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL>

Summary

The Oracle Database 12c SQL statement CREATE PLUGGABLE DATABASE allows us to create a new pluggable database  by using the seed pluggable database PDBSEED as a template. The new database does not contain any user tablespace for schemas and the schema elements ( tables, indexes, etc.) and it should be created next.

The other ways to create pluggable databases are by cloning an existing PDB or non-CDB, or by plugging an unplugged PDB or a non-CDB into a CDB.

Beside SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c,  two new tools are introduced : the Oracle Net Manager and the Database Configuration Assistant.

Advertisements

EM12c comes to the rescue when you need a Tablespace for the new Application

I like to combine all Oracle Database 12c Tools: SQL*Plus, SQL Developer, and Oracle Enterprise manager Database Express 12 – EM12c. While I am working on a project, this allows me to switch fast from one tool to the other especially when I need to run any DDL statements, then go back to SQL Developer and create and test some PL/SQL code. Usually, EM12c is extremely fast for the tablespace creation or its management, especially when the new project starts.

task#9 In this task I will demonstrate the fastest way to create a tablespace when you  needed it the most.

step# Connect to the pluggable database PDBORCL through EM12c using address “localhost:5502/em”. Ports 5500 and 5502 are by default reserved and assigned to the CDB$ROOT and PDBORCL databases accordingly on my Oracle Database 12c.

EM12c Connect To PDBORCL

EM12c Connect To PDBORCL

step#2 Open the Tablespace control window.

The Pluggable Database Tablespaces

The Pluggable Database Tablespaces

At the moment, we have 6 tablespaces: SYSTEM, SYSAUX, USERS, TEMP, EXAMPLE, and COMPANYAB.

step#3 Click on the Action Button and choose the Create option.

The Action Button

The Action Button

There are other options too, we can Drop any of the existing tablespaces, change the Status of the tablespace, Add a new datafile to the existing tablespace, and set a tablespace as Default one.

step#4 In the open Create tablespace window, enter the name of the new tablespace, in this case “COMPANYCD”, then click “>” button and follow the procedure.

Create a New Tablespace

Create a New Tablespace

step#5 See what SQL “CREATE TABLESPACE” statement will be executed, and make sure that all parameters are correct, such as the name of the datafile, size, logging, and compression. And if it is all as it should be, press OK button to execute that DDL statement.

The SQL CREATE TABLESPACE statement

The SQL CREATE TABLESPACE statement

step#6 The new tablespace has been created successfully.

The Tablespace Successful Creation

The Tablespace Successful Creation

step#7 Finally, The list of tablespaces  has a new member, the tablespace “COMPANYCD”, and we are ready to start a new app.

The List Of the Current Tablespaces

The List Of the Current Tablespaces

step#8 Check with SQL*Plus that the tablespace datafile “COMPANYCD.DBF” is in the right directory on your computer

c:\TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 00:42:10 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > select dbms_xdb.getHttpPort() from dual;

DBMS_XDB.GETHTTPPORT()

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

0

SYS@orcl > select dbms_xdb_config.getHttpsPort() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()

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

5500

SYS@orcl > host

Microsoft Windows [Version 6.3.9600]

(c) 2013 Microsoft Corporation. All rights reserved.

c:\TEMP>dir C:\app\orauser\oradata\orcl\pdborcl

Volume in drive C has no label.

Volume Serial Number is C2DD-F7B2

Directory of C:\app\orauser\oradata\orcl\pdborcl

09/09/2015 01:00  .

09/09/2015 01:00  ..

06/09/2015 22:41 104,865,792 COMPANYAB.DBF

09/09/2015 01:00 104,865,792 COMPANYCD.DBF

06/09/2015 22:41 1,456,218,112 EXAMPLE01.DBF

06/09/2015 22:32 20,979,712 PDBORCL_TEMP012014-12-09_02-14-58-AM.DBF

07/09/2015 18:54 22,290,432 SAMPLE_SCHEMA_USERS01.DBF

06/09/2015 22:41 734,011,392 SYSAUX01.DBF

06/09/2015 22:41 304,095,232 SYSTEM01.DBF

7 File(s) 2,747,326,464 bytes

2 Dir(s) 562,110,873,600 bytes free

c:\TEMP>exit

SYS@orcl >

Summary

The Oracle Corporation recommends that the separate tablespaces should be created for any customer new applications in order to guarantee isolation, security, and easy management of the application and its tablespaces.

In this task I have shown how fast and easy is to create a tablespace on the fly using EM12c when you are desperate to start a new project.

76% increase of developer’s productivity with Oracle EM12c, Enterprise Manager Database Express 12c

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.

task#6 Use Enterprise Manager Database Express 12c ( EM12c) and alter the state of the pluggable database PDBORCL from open to close, and again from close to open.
step#1 – Connect to EM 12c. Open the browser with “localhost:5500/em” and enter SYS username/password.

The Enterprise Manager Start Page

The Enterprise Manager Start Page

Then open the window that shows pluggable databases PDBs state.

The List of Oracle 12c Pluggable Databases

The List of Oracle 12c Pluggable Databases

The pluggable database PDBORCL is open in Read Write mode, it has been running for 6 hours and 31 minutes, and its size is 2GB.
step#2 – Open the List of possible Actions on pluggable databases.

The Action List

The Action List

The pluggable database PDBORCL could be:
-Cloned
-Plugged or Unplugged
-Dropped
-Closed

step#3 – Click on the Close option to close the pluggable database PDBORCL

The Pluggable Database is Closed

The Pluggable Database is Closed

The pluggable database pdborcl is now closed.
step#4 – Open the pluggable database pdborcl in Read Write mode.

The Pluggable Database

The Pluggable Database

step#5 – Open SQL window and see the SQL statements that will be executed to open the pluggable database pdborcl in Read Write mode.

SQL statements that will be executed

SQL statements that will be executed

step#6 – See the confirmation message that the pluggable database pdborcl is successfully opened.

The Pluggable Database is OPEN

The Pluggable Database is OPEN

Summary
The Enterprise Manager Database Express 12c allows the DBAs and Oracle Developers clean, fast, and effortless control over the root CDB and pluggable PDBs.

Reference

Oracle 12c documentation

EM12c