Pluggable database

Il concetto di Pluggable Database è presente a partire da Oracle 12c.
Più in basso consideriamo un po’ di casi per capire alcune funzionalità in merito alla flessibilità dei
database multitenant
Creare un Pluggable Database all’interno di un root container
Grazie al concetto di Pluggable Database, è possibile creare un database vuoto all’interno di un
Container in pochi minuti
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
 
Dal file alert:
 
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.
Anche l’eliminazione un database è un lavoro di pochi secondi.
Nell’esempio sotto è mostrato come cancellare completamente un Pluggable Database senza
mantenere i datafile.
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
Dall’alert vediamo
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
La clonazione di un Database è questione di pochi minuti.
Possiamo, per motivi di test, creare un clone di un database a partire da una restored o gold image
 
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
Spostare un Pluggable Database da un Container ad un altro è semplice, basta scollegare la copia
dalla destinazione remota e collegarla al nuovo 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’

Potrebbero interessarti anche...