Starting and Stopping Pluggable Databases In 12c

Starting and Stopping Pluggable Databases In 12c

Starting the pdbs:

Check the status of the pdb's

sqlplus '/ as sysdba'

SQL> select name, open_mode from v$pdbs;

SQL> show pdbs

Note: All the PDBs are in MOUNT state.

Start a single pdb

show pdbs

alter pluggable database lub1 open;

Start all pdb's in single command

show pluggable

alter pluggable database all open;

Note: While starting the pdbs database characterset and pluggable database status information will be written in alertlog.

Stopping the pdbs:

Check the status of the pdb's

sqlplus '/ as sysdba'

SQL> select name, open_mode from v$pdbs;

SQL> show pdbs

Note: All the PDBs are in READ WRITE state.

Stop a single pdb

show pdbs

alter pluggable database lub1 close immediate;

Stop all pdb's in single command

show pluggable

alter pluggable database all close immediate;

Note: While closing the pdb's buffer cache will be flushed.

Create a trigger to open all pluggable databases.

sqlplus '/ as sysdba'

CREATE OR REPLACE TRIGGER pdb_startup AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'alter pluggable database all open';

END pdb_startup;

/

12c Multitenant Activities

12c Multitenant Activities

1.Backup and Recovery of CDB & PDB databases in 12c.


Backup:

How to take backup of container and pluggable database in command?
rman target /
backup database plus archivelog;
first it will root container, pluggable database and then seed database.

How to take the backup of root container database?
rman>backup database root;

How to take the backup of a individual pluggable database?
rman>backup pluggable database pdb1;
or
rman target=sys@pdb1
rman>backup database;

Recovery:

To do a full recovery of PDB database ?
rman target=/
run{
alter pluggable database pdb1 close;
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}

PITR in pluggable database ?

rman target=/
run{
alter pluggable database pdb1 close;
set until time "SYSDATA - 10/24/2019"; (we can give either TIME, SCN or SEQUENCE)
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}


2.How to clone a local pluggable database (PDB) ?


-connect to root container database
conn / as sysdba

-check the pdb's
select name, open_mode from v$pdbs order by name;

-switch the source PDB to read-only mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;

-clone the PDB
-if you are not using OMF, convert the paths
sql>create pluggable database pdb2 from pdb1
file_name_convert=('/u01/app/oradata/cdb1/pdb1/','/u01/app/oradata/cdb1/pdb2');
alter pluggable database pdb2 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name; (here only pdb2 will be read write mode)

-switch the source PDB to read-write mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name;

How to remove the clone PDB's ?

alter pluggable database pdb2 close;
drop pluggable database pdb2 including datafiles;



How to clone a remote pluggable database (PDB) ?


-connect to remote PDB
conn sys/password@pdb5 as sysdba

-check database name
select name from v$database;

-create & grant a user we can point a database link to
create user remote_clone_user identified by remote_clone_user;
grant create session, create pluggable database to remote_clone_user;

-switch the remote PDB to read-only mode
alter pluggable database pdb5 close;
alter pluggable database pdb5 open read only;

-connect to root container in the local CDB
conn sys/password@CDB1 as sysdba

-check the database name
select name from v$database;

-create a database link to the remote PDB
create database link clone_link connect to remote_clone_user identified by abc123 using 'pdb5';

-clone the remote database over the database link
create pluggable database pdb5new from pdb5@clone_link file_name_convert=('/u01/aap/oradata/cdb3/pdb5/','/u01/app/oradata/cdb1/pdb5new/');

alter pluggable database pdb5new open;

-check the pdb's
select name, open_mode from v$pdbs order by name;

-open the remote PDB in read-write mode again
alter pluggable database pdb5 close;
alter pluggable database pdb5 open;


How to connect to PDB & CDB database's ?


-connect to CDB using OS authentication
conn / as sysdba

-connect to CDB using a local connection
conn system/manger

-check available services
select name, pdb from v$services order by name;
all services are also registered with listener

-connect to CDB using EZCONNECT & tnsnames.ora
conn system/manager@//localhost:1521/cdb1
conn system/manager@cdb1

-display the container name and ID usign sqlplus
show con_name
show con_id

-switch to a user created pluggable database
alter session set container=pdb1;
show con_name

-switch back to root container
alter session set container=cdb$root;

-connect to pdb using tnsnames.ora
conn system/password@pdb1


How to create a empty pluggable database ?


-connect to root container
conn / as sysdba

-create a pdb based on seed
create pluggable database pdb2 admin user pdb_adm identifed by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed)',/u01/pp/oradata/cdb2/pdb3/';
(or)
-set file_name_convert at session level
alter session set pdb_file_name_convert='/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb4/';
create pluggable database pdb4 admin user pdb_adm identified by password1;

Alertlog show below message:

create pluggable database pdb4 admin user pdb_adm identified by *
Tue Dec 31 02:02:26 2019
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.APEX$_WS_NOTES (CONTENT) - CLOB populated
Tue Dec 31 02:03:17 2019
****************************************************************
Pluggable Database PDB4 with pdb id - 5 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 PDB4 is WE8MSWIN1252
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#13 to file$(old file#5)
Adding new file#14 to file$(old file#7)
Successfully created internal service pdb4 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=5 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB4 with pdb id - 5 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb4 admin user pdb_adm identified by *


-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NEW
select name, open_mode from v$pdbs;

-finish pdb creation by opening the PDB
alter pluggable database pdb2 open read write;

-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NORMAL

If you want to drop the newly created PDB's
alter pluggable database pdb3 close;
drop pluggable database pdb3 including datafiles;


How to manage tablespaces in a CDB & PDB ?


connect to root container
conn / as sysdba

show con_name
CDB$ROOT

create a tablespace for the CDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

create a tablespace for PDB:
alter session set container=pdb1;
(or)
conn sys/password@pdb1 as sysdba

show con_name
PDB1

create a tablespace for the PDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

Note:

A PDB doesn't have an undo tablespace, and we can't see it
We can see the undo datafile for the CDB from v$datafile

create temporary tablespace in the PDB:
create temporary tablespace temp2 tempfile '/u01/app/oradata/temp02.dbf' size 10m autoextend on next 10m;

remove a temporary tablespace:
drop tablespace temp2 including contents and datafiles;

There are 2 options for setting default tablespaces in PDB:

alter database default tablespace users;
alter database default temporary tablespace temp;
(or)
use PDB syntax for clarity
alter pluggable database default tablespace user;
alter pluggable database default temporary tablespace temp;


How to managing users and roles for CDB's and PDB's ?


connect to root container database

creating a common user:
create user c##test_user1 identified by password1 container=all;
grant create session to c##test_user1 container=all;

container=all default when issued from root container

create user c##test_user2 identified by password1;
grant create session to c##test_user2;

Switch container while connected to a common user or switch to PDB, alter session set container=PDB1;
conn sys/password1@pdb1 as sysdba

create the local user using the container clause:
create user test_user3 identified by password1 container=current;
grant create session to test_user3 container=current;

container=current default when issued from non-root container.
create user test_user4 identified by password1;
grant create session to test_user4;

Make a local user a DBA role:
grant PDB_DBA to test_user4;

create a common role
conn / as sysdba
create role c##test_role1;
grant it to a common user
grant c##test_role1to c##test_user1 container=all;

grant it to a lcoal user
conn sys/password1@pdb1
grant c##test_role1 to test_user3;

create local role:
create role test_role1;
grant create session to test_role1;


How to pluggable database PDB's archive files ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display the current PDB's:

show pdbs

pdb1 read write

unplug:

alter pluggable database <pdb1> close immediate;
alter pluggable database <pdb1> unplug into '/u01/mqm/pdb1.pdb';

we can safely drop the PDB after archiving
drop pluggable database pdb1 including datafiles;

We can check the content of the pdb archive files using unzip -l command
It will show the .dbf files plus .xml file


plug-in:

check its safe to plugin.

set serveroutput on
declare
1_result BOOLEAN;
BEGIN
1_result := DBMS_PDB.check_plug_compatibility(pdb_descr_file => '/tmp/pdb1.pdb',
      pdb_name =>'pdb1');
IF 1_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/

plugin from an archive file.
create pluggable database pdb1 using '/u01/mqm/pdb1.pdb';
alter pluggable database pdb1 open read write;

show pdbs


How to refresh pluggable database PDB's ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display current pdb's

show pdbs

pdb5 read write

create link user for DBlink
create user c##remote_clone_user identified by remote_clone_user container=all;
grant create session, create pluggable database to c##remote_clone_user container=all;

connect to CDB1 and create database link.
conn sys/password1@cdb1 as sysdba
create database link clone_link connect to c##remote_clone_user identified by remote_clone_user using 'cdb3';

create refreshable read-only PDB
create pluggable database pdb5_ro from pdb5@clone_link refresh mode manual;
alter pluggable database pdb5_ro open read only;

Make changes to the source PDB.
conn sys/password1@cdb3 as sysdba
alter session set container=pdb5;
create tablespace test_tbs datafile size 10m autoextend on next 10m;

check for the TEST_TBS tablespace in the refreshable PDB.
conn sys/password1@cdb1 as sysdba
alter session set container=pdb5_ro;
select tablespace_name from dba_tablespaces;
It will not show the test_tbs tablespace.

Refresh the read-only PDB.
alter session set container=pdb5_ro;
alter pluggable database close immediate;
alter pluggable database refresh;
alter pluggable database open read only;
select tablespace_name from dba_tablespaces;
Now it will show the test_tbs tablespace.

alter the refreshable PDB refresh mode.
alter pluggable database pdb5_ro refresh mode every 120 minutes;
select pdb_id, pdb_name, refresh_mode, refresh_interval from dba_pdbs order by 1;


How to startup & shutdown of PDB's & CDB's database's ?


CDB shutdown:
conn  / as sysdba
shutdown immediate;

CDB startup:
conn / as sysdba
startup

using alter command.
alter pluggable datababase <pdb1_name,pdb2_name,or all> open or close;

How to unplug and plugin pluggable database (PDB's) ?

check the availabe pdb's by connecting root container database.
conn / as sysdba
select name, open_mode from v$pdbs order by name;

create a new PDB to test the unplug/plugin.
create pluggable database pdb2 admin user pdb_adm identified by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb2/');
alter pluggable database pdb2 open read write;

unplug the PDB:
alter pluggable database pdb2 close;
alter pluggable database pdb2 uplug to '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml';

check the pdbs are present.
select name, open_mode from v$pdbs order by name;
Here pdb2 should be mount stage.

Drop the PDB, but keep the files.
drop pluggable database pdb2 keep datafiles;

check again the pdb status.
select name, open_mode from v$pdbs order by name;
It will drop the PDB2 database.

check the compatibility.

plugin and rename the PDB:
create pluggable database pdb5 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml' file_name_convert=('/u01/app/oradata/cdb1/pdb2/','/u01/app/oradata/cdb1/pdb5/');
alter pluggable database pdb5 open;

check the status
select name, open_mode from v$pdbs order by name;

drop the new PBD
alter pluggable database pdb5 close;
alter pluggable database pdb5 including datafiles;

plugin the database using the same name and file position.
create pluggable database pdb2 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml'
nocopy
tempfile reuse;
alter pluggable database pdb2 open read write;

check the status
select name, open_mode from v$pdbs order by name;

The Multitenant Architecture

The Multitenant Architecture

The Multitenant architecture allows a single super container (“Container” alias CDB) to shelter multiple other containers (“Pluggable” alias PDB). From the server subsystem, there is only one database i.e. the container database, and therefore only one instance (and one SID) is available i.e. the CDB, while each application sees only the specific PDB to which it connects to. There is no application code change required while connecting to a PDB. Each PDB runs as a service within the CDB but ensures complete security and isolation amongst them.

Traditionally, an Oracle database consists of its instance and the file structure. An Oracle database instance is made up of the memory and the background process. The file structure comprises of the datafiles (.dbf), control file, spfile, password file, redo logs, archived logs, undo segment and temp tablespace. Oracle 12c Multitenant architecture simply reorganizes these components and categorizes them as “sharable” component and “application component”. The “sharable” components are instance level pieces and the system pieces of a database like SPFILE (parameter file), control file, SYSTEM and SYSAUX tablespace, redo logs, archived log files, UNDO and TEMP. These sharable components exist at the root container (CDB) level. The user or application data for each database is the application component and therefore, each PDB just holds its own user data. Each PDB plugs into the CDB with its application data only while shares the memory, redo logs, archived logs, UNDO segment, and control file information with other pluggable databases in the same container.

A multitenant container database has three types of containers:

*The Oracle supplied container is called the root container (CDB$ROOT) and consists of just Oracle metadata (and maybe a little bit of user data) and common users. Each CDB has one root.

*The seed container is named PDB$SEED, and there is one of these per CDB. The purpose of the seed container isn’t to store user data—it’s there for you as a template to create PDBs.

*The user container , which is actually called a pluggable database (or PDB), consists of user metadata and user data.

Each of these—the root, the seed, and the PDB(s)—is called a container, and each container has a unique container ID (CON_ID) and container name (CON_NAME). Each PDB also has a globally unique identifier (GUID). The idea behind the concept of a container is to separate Oracle metadata and user data by placing the two types of data into separate containers. That is, the system and user data are separated. There’s a SYSTEM tablespace in both the central container and the PDB containers, however, they contain different types of data. The root container consists of Oracle metadata whereas the PDB container’s SYSTEM tablespace contains just user metadata. The Oracle metadata isn’t duplicated by storing it in each PDB—it’s stored in a central location for use by all the PDBs that are part of that CDB. The CDBs contain pointers to the Oracle metadata in the root container, thus allowing the PDBs to access these system objects without duplicating them in the PDBs. A CDB has similar background processes and files as a normal non-CDB database. However, some of the processes and files are common for both a CDB and its member PDB databases, and some aren’t.

Common Entities between CDB and PDBs

*Background processes There’s a single set of background processes for the CDB. The PDBs don’t have any background processes attached to them.

*Redo log files These are common for the entire CDB, with Oracle annotating the redo data with the identity of the specific PDB associated with the change. There’s one active online redo log for a single-instance CDB or one active online redo log for each instance of an Oracle RAC CDB. A CDB also has a single set of archived redo log files.

*Memory You allocate memory only to the CDB, because that’s the only instance you need in a multitenant database.

*Control files These are common for the entire CDB and will contain information that reflects the changes in each PDB.

*Oracle metadata All Oracle-supplied packages and related objects are shared.

*Temporary tablespace There’s a common temporary tablespace for an entire CDB. Both the root and all the PDBs can use this temporary tablespace. This common tablespace acts as the default TEMP tablespace. In addition, each PDB can also have a separate temporary tablespace for its local users.

*Undo tablespace All PDBs use the same undo tablespace. There’s one active undo tablespace for a single-instance CDB or one active undo tablespace for each instance of an Oracle RAC CDB.

A CDB contains a set of system data files for each container and a set of user-created data files for each PDB. Also CDB contains a CDB resource manager plan that allows resources management among the PDBs in that CDB.

Entities Exclusive for PDBs

*Tablespaces for the applications tables and indexes These application tablespaces that you’ll create are specific to a PDB and aren’t shared with other PDBs, or the central CDB. The data files that are part of these tablespaces constitute the primary physical difference between a CDB and a non-CDB. Each data file is associated to a specific container.

*Local temporary tablespaces Although the temporary tablespace for the CDB is common to all containers, each PDB can also create and use its own temporary tablespaces for its local users.

*Local users and local roles Local users can connect only to the PDB where the users were created. A common user can connect to all the PDBs that are part of a CDB.

*Local metadata The local metadata is specific to each application running in a PDB and therefore isn’t shared with other PDBs.

*PDB Resource Manager Plan These plans allow resource management within a specific PDB. There is separate resource management at the CDB level.

The PDB containers have their own SYSTEM and SYSAUX tablespaces. However, they store only user metadata in the SYSTEM tablespace and not Oracle metadata.

Data files are associated with a specific container. A permanent tablespace can be associated with only one container. When you create a tablespace in a container, that tablespace will always be associated with that container.

Backup of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c

Backup of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c

1. Container Database (CDB) Backup

Backup of a Container Database (CDB) is essentially the same as a non-Container Database. The main thing to remember is, by doing a full backup of the CDB you are also doing a full backup of all PDBs.

Connect to RMAN using OS authentication and take a full backup using the following command. This means you are connecting to the root container with "AS SYSDBA" privilege.

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Note:

The datafiles associated with the CBD (cdb1) and all the PDBs (pdb1, pdb2, pdb$seed) are included in the backup.

2. Root Container Backup

A backup of the root container is a backup of the CDB, excluding any of the PDBs.

Connect to RMAN using OS authentication and backup the root container using the following command. This means you are connecting to the root container with "AS SYSDBA" privilege.

$ rman target=/

RMAN> BACKUP DATABASE ROOT;

Note:

The datafiles associated with the CBD (cdb1) are included, but all the PDBs (pdb1, pdb2, pdb$seed) are not included in the backup.

3. Pluggable Database (PDB) Backup

There are two ways to back up pluggable databases. When connected to RMAN as the root container, you can backup one or more PDBs using the following command.

$ rman target=/

RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;

Note:
You can see this includes the datafiles for both referenced PDBs.

Alternatively, connect to a specific PDB and issue the following command.

$ rman target=sys@pdb1

RMAN> BACKUP DATABASE;

4. Tablespace and Datafile Backups

Multiple PDBs in the same CDB can have a tablespace with the same name, for example SYSTEM, SYSAUX and USERS. One way to remove that ambiguity is connect to the appropriate PDB. Once RMAN is connected to the PDB, the tablespace backup commands is unchanged compared to previous versions.

$ rman target=sys@pdb1

RMAN> BACKUP TABLESPACE system, sysaux, users;
Alternatively, you can remove the ambiguity by qualifying the PDB name with the tablespace name when connected to the root container.

$ rman target=sys@cdb1

RMAN> BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;
Datafiles have unique file numbers and fully qualified names, so they can be backed up from the root container or the individual PDB.

$ rman target=/

# Or

$ rman target=sys@pdb1

RMAN> BACKUP DATAFILE 8, 9, 10;
If you are connecting to a PDB, only the files belonging to that PDB can be backed up. So for example, when connected as PDB1, we get an error if we try to backup the SYSTEM datafile from the root container.

RMAN> BACKUP DATAFILE 1;

Starting backup at 23-DEC-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

12c Pluggable Databases FAQ's

12c Pluggable Databases FAQ's

1.Can Point-In-Time recovery be conducted at Pluggable Database (PDB) level?
Yes, it will be possible.

2. Can database services be created inside a Pluggable Database (PDB)?
Of course you can have many services within a PDB. Each will denote the PDB within which it is defined as the initial current container. Use the normal methods to create, maintain, and drop additional services in a PDB. A PDB’s default service must not be dropped. Of course, service names should be unique across all the databases that are registered with a particular listener.

3. Can I have local TEMPORARY tablespace at Pluggable Database (PDB) level?
In spite, during the PDB creation only SYSTEM and SYSAUX tablespaces created, you can create local temporary tablespace later for every PDB. UNDO tablespace can NOT be local and stays on the CDB level.

4. Is conventional database mode will be still available in 12c database and later versions?
Yes, on Oracle 12c you will have two options of creating a usual old-fashioned Oracle database (non-CDB) or creating a Container Database (CDB) that will hold all your Pluggable Databases (PDB) that you will create or plug later. However, based on my information non-CDB mode will be available only in 12c release 1 for compatibility reasons and will be discontinued in 12cR2.

5. Will Oracle 12c database have only one container?
Yes and No! In fact, Oracle considers a CDB as well as all PDBs as containers. Every container has own name. For example a single Container Database (CDB) has a container name CDB$Root. Every Pluggable database has a container name similar to the PDB name. On CDB level by default your current container is CDB$Root but you can switch between PDBs’ containers also. On CDB level you can change current container with following SQL:

alter session set container = my_cdb

or verify current container with below SQL:

select Sys_Context('Userenv', 'Con_Name') "current container" from dual;

6. Is cloning a PDB from one in a different CDB supported?
I have not seen it in 12c Beta 2, but Oracle intends to support it in the final version. You’ll identify the source CDB by creating a database link to it from the CDB in which you will create the clone.

7.Is online PDB cloning supported?
Not in 12c Beta2, but Oracle intents to remove this restriction in the final version by using the same approach that allows online RMAN backup: by re-constructing any blocks that change during the time it takes to copy the source PDB’s datafiles.

8.Why switching PDB Open_Mode/Restricted modes requires 2 SQL commands?
Not sure why, but in final 12c release it will be possible to go from any one Open_Mode/Restricted combination to another with a single use of the alter pluggable database statement.

9.Will it be possible to allocate/restrict resources (CPU/Memory) of PDB’s (Pluggable)?
Yes, it will be possible using new functionality of Oracle 12c Resource Manager.
Using 12c Resource manager you will be able control CPU, Exadata I/O, sessions and parallel servers. A new 12c CDB Resource Manager Plan will use so-called “Shares” (resource allocations) to specify how CPU is distributed between PDBs. A CDB Resource Manager Plan also can use “utilization limits” to limit the CPU usage for a PDB. With a default directive, you do not need to modify the resource plan for each PDB plug and unplug. However I’m not aware of a possibility to restrict memory of PDBs, since they will share one single SGA

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB)

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) 

1. Container Database (CDB)
Startup and shutdown of the container database is the same as it has always been for regular instances. The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user. Some typical values are shown below.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

2. Pluggable Database (PDB)
Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

SQL*Plus Commands
The following SQL*Plus commands are available to start and stop a pluggable database, when connected to that pluggable database as a privileged user.
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
Some examples are shown below.

STARTUP FORCE;
SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;
SHUTDOWN;

STARTUP;
SHUTDOWN IMMEDIATE;
ALTER PLUGGABLE DATABASE
The ALTER PLUGGABLE DATABASE command can be used from the CDB or the PDB.

The following commands are available to open and close the current PDB when connected to the PDB as a privileged user.

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
Some examples are shown below.

ALTER PLUGGABLE DATABASE OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
The following commands are available to open and close one or more PDBs when connected to the CDB as a privileged user.

ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
The <pdb-name-clause> clause can be any of the following:

One or more PDB names, specified as a comma-separated list.
The ALL keyword to indicate all PDBs.
The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.

Some examples are shown below.

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

12c Database Commands

12c Database Commands

1. Create and open a new Oracle 12c Pluggable database (PDB)

Create a pdb using the seed:

Now we will create and open a new Pluggable database (PDB) called My_PDB.
Every CDB has a standard template of a PDB whose name is PDB$Seed. We actually create a new PDB by cloning this Seed. See below a SQL example :

Pre-requisite
connect to root with CREATE PLUGGABLE DATBASE privileges.
configure a storage location (and ensure disk space) for the new database’s files.
select a unique name for the new database.

create pluggable database My_PDB admin user App_Admin identified by pass file_name_convert = ('/pdbseed/', '/my_pdb/');

Ex:
create pluggable database lub admin user pdbmqm identified by oracle file_name_convert=('/u02/app/oracle/oradata/orcl/pdbseed', '/u02/app/oracle/oradata/orcl/lub');

The file_name_convert clause specifies how names for the new files are derived from those of the seed using the same scheme as you know from RMAN. During the PDB creation Oracle copies just 2 database files for System and Sysaux tablespaces to the new location of our PDB. The rest of database files for undo, redo, and so on is global for the whole CDB and belongs to the special container called CDB$Root.

The admin user clause is mandatory. In an extended form, it lets you grant privileges and roles to the new user. This user is able to start a new session only within My_PDB.

On completion of a create pluggable database command, the new PDB will be in MOUNTED mode. Before you can start a session in the new PDB, it must be open. So, let’s open our PDB using below SQL :

alter pluggable database My_PDB open;

2. Check Container Database (CDB) and Pluggable database (PDB) files

select con_id, tablespace_name, file_name from cdb_data_files where file_Name like '%/cdb1/pdbseed/%' or file_Name like '%/cdb1/my_pdb/%' order by 1, 2;

CON_ID Tablespace_Name File_Name
------ ------------ -------------------------------------------------
2 SYSAUX /opt/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
2 SYSTEM /opt/oracle/oradata/cdb1/pdbseed/system01.dbf
3 SYSAUX /opt/oracle/oradata/cdb1/My_PDB/sysaux01.dbf
3 SYSTEM /opt/oracle/oradata/cdb1/My_PDB/system01.dbf

3. Open all Oracle 12c Pluggable Databases (PDB)

Each PDB within a CDB has its own Open_Mode and Restricted status in each instance of a RAC. The possible values Open_Mode are MOUNTED, READ ONLY, and READ WRITE; and the possible values for the Restricted status are YES and NO when the PDB is open and otherwise null.

Starting an instance (which opens the entire CDB) does not cause PDBs to be opened. The alter pluggable database statement is used to set the Open_Mode. You give in SQL either the name of a particular PDB or use the keyword all, thus:

alter pluggable database all open;

4. Close all Oracle 12c Pluggable Databases (PDB) in CDB
This SQL statement closes all the PDBs in the CDB:

alter pluggable database all close;

Show the open time of PDBs

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

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME   OPEN_MODE
---------- ------------------------------ ----------
 2 PDB$SEED   READ ONLY
 3 ORCL   READ WRITE
 4 PROD   READ WRITE

or

show pdbs

How to set the pdb database:

SQL> alter session set container=PROD;
SQL> show con_name

CON_NAME
------------------------------
PROD
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/cdb1/prod/system01.dbf
/home/oracle/app/oracle/oradata/cdb1/prod/sysaux01.dbf

How to close the PDB database:
alter pluggable database devpdb1 close immediate;

How to set the container database:
alter session set container = <db_name>;

How to check the container database:
show con_name

How to drop the PDB database:
drop pluggable database My_PDB keep datafiles;

How to Tell If the Database Is a CDB
select name, cdb, con_id from v$database;

Viewing Information About Containers
select name, con_id, db_id from v$containers;

Viewing Information About the PDBs
select pdb_id, pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB
select name, open_mode, restricted, open_time from v$pdbs;

Viewing the History of PDBs
select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property;

Viewing all the tablespaces in a CDB
select tablespace_name, con_id from cdb_tablespaces;</em>

Note:

1. After creating a PDB database open it atleast once in read-write mode to complete the integration with the CDB.

2. Memory and processes are required at container database level only.

Components of the CDB are as follows

Components of the CDB are as follows

* One or more PDBs

* CDB$ROOT, the keeper of all the PDBs that are part of the collection

* PDB$SEED, a template from which new PDBs can be created

Types of Databases in 12c

Types of Databases in 12c 

1.Container Database (CDB): The primary database that contains multiple plugged-in databases. Many operations can be performed at the container level to reduce management costs. A database is created as either a CDB or a non-CDB.

2.Pluggable Database (PDB): A set of schemas, objects, and non-schema objects that can be plugged and unplugged from a container database. The PDB appears to OracleNet and end users as a database in and of itself but is actually managed within a container that may have many PDBs.

3.Seed Database (Seed PDB): A default PDB that the system uses as a template to quickly provision other user-created PDBs. Internally, it’s called PDB$SEED.

12c Pluggable Database feature capability

12c Pluggable Database feature capability

* You can have many Pluggable Databases (PDBs) inside a single container database (CDB)
* PDB is backwards compatible with an ordinary pre-12.1 database;
* PDB is transparent to applications * you don’t change the client code or your database objects
* Each instance in a RAC opens CDB as a whole (so the CDB and all the PDBs in it are at the same Oracle Database version)
* A session sees only the single PDB it connects to
* You can unplug a PDB from one CDB and plug it into another CDB
* You can clone a PDB, both within the same CDB or from one CDB to another one
* Resource Manager is extended with new PDB capabilities
* The operations on PDBs as entities (creating, unplugging, plugging in, cloning, dropping, and setting the Open_Mode) are implemented as SQL statements
* CDB’s administrator executes these operations when connected to its so-called root
* Pluggable Databases functionality is fully interoperable with all the database options
* All PDBs can be backed up at once, but recovered separately

12c Pluggable Database feature benefits

12c Pluggable Database feature benefits

* Consolidate many PDBs onto a single platform.
* Fast provisioning of a new PDB or of a clone of an existing PDB;
* Fast redeployment, by unplug and plug, of an existing database to a new platform
* Patch or upgrade the Oracle Database version for many PDBs quickly by doing it just once
* Patch or upgrade a single PDB by unplugging it and plugging it into a different CDB at a later version
* Separation of the content of one PDB from that of peer PDBs in the same CDB
* Separation of the duties of the application administrators of these PDBs

Oracle 12c Pluggable Database features

Oracle 12c Pluggable Database features 

* Each PDB has its own private data dictionary for customer-created database objects; CDB on the other hand as a whole has the data dictionary for the Oracle-supplied system each data dictionary defines its own namespace. In other words, there is global Data dictionary (CDB level) and local one (PDB level).
* There is a new split data dictionary architecture that allows a PDB to be quickly unplugged from one CDB and plugged into a different CDB
* Each PDB sees a read-only definition of the Oracle-supplied system
* There are global database initialisation parameters on CDB level and local ones. PDB parameters belong only to you particular PDB and will be persistent even after you unplug you PDB.
* Database users can be global (CDB) or local (PDB only). SYS and SYSTEM users exist in both DBs right at the beginning. If you create a new user in CDB you will see it in PDB also. In case of creation of a user on PDB level it will stay local.
* Temporary tablespaces can be global or local
* Redo logs and Undo tablspace are only global (on CDB level)
* Data Guard acts on the CDB as a whole; RMAN scheduled backups are done for the CDB as a whole; you can back up a selected PDB whenever you want to; you can do point-in-time
* An application connects, with no code changes, to a PDB; the system administrator connects to the CDB; the service named in the connect string specifies the destination PDB
* A PDB allows a clear declarative definition of an application; one PDB knows nothing of the other PDBs within the same CDB; each PDB is a hermetically sealed container. That ensures new level of DB independence and robust security