web analytics
How to create a pluggable database? Options
codeling
Posted: Tuesday, December 6, 2016 2:21:10 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 784
Points: 3288
By default, there are two PDBs created. One is the default database created during installation (PDBORCL12C) and the other one is a seed database which you can use to create new PDBs. To find out the list of PDBs, you can query the v$pdb or use the show pdbs command: 
SQL> show pdbs
   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDBORCL12C                     MOUNTED
 
SQL> select name, open_mode,con_id from v$pdbs;

NAME         OPEN_MODE      CON_ID
------------ ---------- ----------
PDB$SEED     READ ONLY           2
PDBORCL12C   MOUNTED             3

SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
  2  from v$datafile d, v$tablespace ts
  3  where d.ts#=ts.ts#
  4  and d.con_id=ts.con_id
  5  order by 4,3;
     FILE# NAME                TS#     CON_ID
---------- ------------ ---------- ----------
         1 SYSTEM                0          1
         3 SYSAUX                1          1
         5 UNDOTBS1              2          1
         6 USERS                 4          1
         2 SYSTEM                0          2
         4 SYSAUX                1          2
         7 SYSTEM                0          3
         8 SYSAUX                1          3
         9 USERS                 3          3
        10 EXAMPLE               4          3
10 rows selected.
The second SQL query shows the database files associated with each database. You can see that the CDB (CON_ID=1) has SYSTEM, SYSAUX, UNDOTBS1 and USERS files. The seed PDB, pdb$seed(CON_ID=2),  has two SYSETM and SYSAUX files. The pdborcl12c database created by default (CON_ID=3) has SYSTEM, SYSAUX, USERS and EXAMPLE data files.  


If we query the v$datafile view, we can verify the details for each PDBs. The following example list the database files for PDB 
SQL> select name from v$datafile where con_id = 3;
NAME
----------------------------------------------------------------------------
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSTEM01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSAUX01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SAMPLE_SCHEMA_USERS01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\EXAMPLE01.DBF
Step 2: Create a new pluggable database 

Now, we can create a pluggable database. If you are not sure that you are in the container database, you can use the following command:
SQL> alter session set container=cdb$root;
Session altered.
The we can create a new pluggable database from the seed database. You can also copy from an existing PDB. 

Why do I get the "ORA-65081: database or pluggable database is not open in read only mode." error?
You need to make sure the exisiting database is in open and read only mode before creating PDB from it. The following command will be helpful.
SQL>alter pluggable database pdb1 close;
Pluggable database altered.
SQL>alter pluggable database pdb2 open read only;
Pluggable database altered.

SQL> create pluggable database pdbdemo admin user admin identified by admin
file_name_convert = ('pdbseed', 'pdbdemo');
Pluggable database created.

SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDBORCL12C
PDB$SEED
PDBDEMO

SQL> col name format a30
SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        MOUNTED             1

SQL> alter pluggable database pdbdemo open;
Pluggable database altered.

SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        READ WRITE          1

SQL> select name, con_id from v$active_services order by 1;
NAME                               CON_ID
------------------------------ ----------
SYS$BACKGROUND                          1
SYS$USERS                               1
orcl12c.us.oracle.com                   1
orcl12cXDB                              1
pdbdemo.us.oracle.com                   4
pdborcl12c.us.oracle.com                3
6 rows selected.
If logging into a PDB, the operations are pretty much the same as a database in Oracle Database 11g.  The default tablespace for the newly created PDB from the seed  is SYSTEM. 
SQL> alter session set container=pdbdemo;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDBDEMO

SQL> show user
USER is "SYS"

SQL> select property_value from database_properties
     where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
-------------------------------------------------------------------------
SYSTEM

Why do I get the "ORA-65016: FILE_NAME_CONVERT must be specified" error when creating pluggable database?
The file_name_convert specifies how new data filenames are derived from those of the seed or from an exsiting PDB. During the PDB creation Oracle Database 13c actually only copies 2 database files, for the System and the Sysaux tablespaces, to the location of new PDB. The other database files for undo, redo, and so on are global for the whole CDB and belongs to the container called CDB$Root.

Step 3: Dropping PDBs 

If you want to remove a PDB created, you can run the following command in the CDB. The PDB to be deleted have to be closed before deletion: 
SQL> alter session set container=cdb$root;
Session altered.

SQL> drop pluggable database pdbdemo including datafiles;
drop pluggable database seed_copy including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDBDEMO is not closed on all instances.

SQL> alter pluggable database pdbdemo close;
Pluggable database altered.

SQL> drop pluggable database pdbdemo including datafiles;
Pluggable database dropped.
 
Sponsor
Posted: Tuesday, December 6, 2016 2:21:10 PM
 
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.