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
step#6 Open the list Datafiles in the DBA View and show that there is no additional datafiles for CDB$ROOT.
Datafiles
step#7 Open the list Tablespaces in the DBA View and show that there is no added tablespaces for CDB$ROOT either.
Tablespaces
step#8 Create a new connection to BOOKSTORE database in the Connection View
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
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.
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
step#11 Open the Database Configuration Assistant and create EM12c port 5504 as the entry point for BOOOKSTORE database.
The Configuration Assistant
step#12 Open the browser with “localhost:5504/em” and login to BOOKSTORE as SYSDBA
BOOKSTORE EM12c
step#13 Show how many tablespaces are there in the BOOKSTORE database
Tablespaces Of BOOKSTORE
step#14 Create a new 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
step#16 Back to SQL Developer again, the BOOKSTORE database is ready for the new elements.
SQL Developer worksheet
step#17 Click on the BOOKSTORE connection in the Connect View and choose the option Create User.
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
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
step#20 Use SQL*Plus and connect to the BOOKSTORE database as user code/code@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.
Climbing Olympus 