Pluggable database
Pluggable database is the new database concept from Oracle 12c.
Below we consider some basis case in order to understand some functionality about flexible and multitenant database.
Create a new pluggable database in root container
With pluggable database concept , it is possible to create an empty database in one container in just few minutes.
SET SERVEROUTPUT ON
COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 MOUNTED n/a NORMAL
SQL> create pluggable database PDB12p01
admin user app_Admin identified by manager;
Pluggable database created.
Elapsed: 00:03:12.14
From alert log
create pluggable database PDB12p01
admin user app_Admin identified by *
Fri Jul 12 11:08:35 2013
****************************************************************
Pluggable Database PDB12P01 with pdb id – 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#10 to file$(old file#2)
Adding new file#11 to file$(old file#4)
Successfully created internal service pdb12p01 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB12P01 with pdb id – 4 is now marked as NEW.
****************************************************************
Completed: create pluggable database PDB12p01
admin user app_Admin identified by *
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 MOUNTED n/a NORMAL
PDB12P01 MOUNTED n/a NEW
SQL> alter pluggable database pdb12p01 open;
Pluggable database altered.
SQL> SET SERVEROUTPUT ON
COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 MOUNTED n/a NORMAL
PDB12P01 READ WRITE NO NORMAL
Alter session is enough to connect to a specific pluggable database.
SQL> alter session set container =PDB1201;
Session altered.
Event drop a database is a matter of few seconds.
In the example below is showed hot to drop completely a pluggable database, without keeping datafiles.
SQL> alter pluggable database PDB12P01 close;
Pluggable database altered.
SQL> COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 READ WRITE NO NORMAL
PDB12P01 MOUNTED n/a NORMAL
SQL> drop pluggable database PDB12P01 including datafiles;
Pluggable database dropped.
SQL> COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 READ WRITE NO NORMAL
from alertlog
Fri Jul 12 11:35:49 2013
alter pluggable database PDB12P01 close
Fri Jul 12 11:35:50 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
Pluggable database PDB12P01 closed
Completed: alter pluggable database PDB12P01 close
drop pluggable database PDB12P01 including datafiles
Fri Jul 12 11:36:35 2013
Deleted Oracle managed file /u02/oradata/DB12/E14DC52348D609F9E045000000000001/datafile/o1_mf_temp_8xzkwmn5_.dbf
Deleted Oracle managed file /u02/oradata/DB12/E14DC52348D609F9E045000000000001/datafile/o1_mf_sysaux_8xzktwbr_.dbf
Deleted Oracle managed file /u02/oradata/DB12/E14DC52348D609F9E045000000000001/datafile/o1_mf_system_8xzktw6s_.dbf
Completed: drop pluggable database PDB12P01 including datafiles
Database Clone is a matter of minutes.
In case of test database we can create a clone of a restored / gold image database
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 READ WRITE NO NORMAL
SQL> alter pluggable database pdb12 close;
Pluggable database altered.
Elapsed: 00:00:00.77
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 MOUNTED n/a NORMAL
SQL> alter pluggable database pdb12 open read only;
Pluggable database altered.
Elapsed: 00:00:00.91
SQL> SET SERVEROUTPUT ON
COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 READ ONLY NO NORMAL
Elapsed: 00:00:00.01
SQL> create pluggable database pmigrate from pdb12;
SQL> create pluggable database pmigrate from pdb12;
Pluggable database created.
Elapsed: 00:02:42.81
create pluggable database pmigrate from pdb12
Fri Jul 26 17:02:48 2013
****************************************************************
Pluggable Database PMIGRATE with pdb id – 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#7 from file$
Deleting old file#8 from file$
Deleting old file#9 from file$
Deleting old file#13 from file$
Adding new file#14 to file$(old file#7)
Adding new file#15 to file$(old file#8)
Adding new file#16 to file$(old file#9)
Adding new file#17 to file$(old file#13)
Successfully created internal service pmigrate at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PMIGRATE with pdb id – 4 is now marked as NEW.
****************************************************************
Completed: create pluggable database pmigrate from pdb12
Transport pluggable database to another container is simple, just unplug copy to remote destination and plug in to new container
SQL> alter pluggable database pmigrate unplug into ‘/app/oracle/admin/pmigrate.xml’;
Pluggable database altered.
Elapsed: 00:00:01.08
SQL> drop pluggable database pmigrate keep datafiles;
Pluggable database dropped.
Elapsed: 00:00:00.37
SQL> COLUMN “RESTRICTED” FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/
NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL
PDB12 READ WRITE NO NORMAL
Elapsed: 00:00:00.01
SQL> create pluggable database pmigrate_new using ‘/app/oracle/admin/pmigrate.xml’;
Pluggable database created.
Elapsed: 00:02:33.21
create pluggable database pmigrate_new using ‘/app/oracle/admin/pmigrate.xml’
Fri Jul 26 17:21:47 2013
****************************************************************
Pluggable Database PMIGRATE_NEW with pdb id – 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#18 from file$
Deleting old file#19 from file$
Deleting old file#20 from file$
Deleting old file#21 from file$
Adding new file#22 to file$(old file#18)
Adding new file#23 to file$(old file#19)
Adding new file#24 to file$(old file#20)
Adding new file#25 to file$(old file#21)
Successfully created internal service pmigrate_new at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PMIGRATE_NEW with pdb id – 4 is now marked as NEW.
****************************************************************
Completed: create pluggable database pmigrate_new using ‘/app/oracle/admin/pmigrate.xml’