How to create a pluggable database PDB in a multitenant container database CDB using the files of the seed PDB$SEED
The new architecture provided by Oracle Database 12c enables an Oracle database to function as a
multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.
A multitenant container database (CDB) is formed by the following components:
- the root container (and exactly one root) named CDB$ROOT that stores Oracle-supplied metadata and common users
(a database user known in every container);
- the seed template (and exactly one seed) named PDB$SEED used, if you want, to create new PDBs.
It's not possible to add objects to or modify objects in the seed: it works only in READ ONLY mode;
- pluggable databases (zero, one, or many PDBs) named as you prefer that are your "old idea of databases"
before Oracle Database 12c. A pluggable database contains the data and code required by your software
application and is fully backward compatible with Oracle Database releases before Oracle Database 12c.
The options for creating a pluggable database (the so called PDB) fall into two main categories:
copying and plugging in as you will see in this post and in the next posts.
Talking about copying, you have two options to copy a pluggable database:
create a pluggable database (PDB) in a multitenant container database (CDB) using the files of the seed (PDB$SEED);
create it by cloning a source PDB and plugging the clone into the CDB (bear in mind that the source PDB can be in the local CDB or in a remote CDB).
Today I'm going to describe how to copy a pluggable database using the files of the seed from the SQL*Plus command line.
If you want to see how to create a pluggable database copying the files of the seed using DBCA you can simply watch the following video (I will create a post about it using screenshot as soon as possible).
To create a new pluggable database copying the files of the seed you have to ensure that the current container is the root:
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
your multitenant container database CDB must be also in read/write mode. My CDB is called CDB001:
SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;
NAME CDB CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB001 YES 0 READ WRITE
Your script must include the CREATE PLUGGABLE DATABASE statement and eventually some other clauses, such as the following I used in my script:
- STORAGE: specify the limit of the amount of storage the PDB can use. Omitting this clause is equivalent to specify an unlimited amount;
- DEFAULT TABLESPACE: specify a default permanent tablespace for non-SYSTEM users. When you omit this clause the SYSTEM tablespace will be used as default permanent tablespace for non-SYSTEM users and this is not recommended;
- FILE_NAME_CONVERT: specify the target locations of the data files whereas the source files are those associated with the seed.
This parameter is required when Oracle Managed Files is not enabled and the PDB_FILE_NAME_CONVERT initialization parameter is not set
- other clauses you can use are: ROLES, TEMPFILE REUSE and PATH_PREFIX.
SQL> CREATE PLUGGABLE DATABASE PDB002
2 ADMIN USER PDB002_ADMIN IDENTIFIED BY oracle
3 storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
4 DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
5 file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf');
Pluggable database created.
From the alert log (vi /app/oracle/diag/rdbms/cdb001/CDB001/trace/alert_CDB001.log):
Sat Jul 20 16:21:48 2013
CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
Sat Jul 20 16:22:35 2013
****************************************************************
Pluggable Database PDB002 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 pdb002 at open
CREATE TABLESPACE USERS DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE USERS DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO
Sat Jul 20 16:22:49 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB002 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
As you can see when you create a pluggable database from the SQL*Plus command line it is open in MOUNTED mode.
This is a different behaviour compared with the same operation performed by DBCA: in the final step DBCA is able to open the new pluggable database in READ WRITE mode.
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB001 READ WRITE 3
PDB002 MOUNTED 4
To open your new pluggable database in READ WRITE mode execute the following alter pluggable database command:
SQL> alter pluggable database pdb002 open read write;
Pluggable database altered.
Now query again the V$PDBS view: your pluggable database is now available to the application.
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB001 READ WRITE 3
PDB002 READ WRITE 4
Create Pluggable Database Manually using SQL Prompt
we will have a look & hands-on on creating a PDB manually with the help of SQL Prompt.
We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter,
In following example i am going to create new Pluggable Database, name: ‘newpdb’
[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:
SQL> select global_name from global_name;
GLOBAL_NAME
——————————————————————————–
ORCL
// Create new pluggable database by following set of SQL queries:
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/pdbseed/’,’/u01/app/oracle/oradata/newpdb/’;
Session altered.
SQL> CREATE PLUGGABLE DATABASE newpdb ADMIN USER pdbadmin IDENTIFIED BY Admin123;
Pluggable database created.
// Pluggable database has been created successfully, ensure changes by issuing the following query:
column pdb_name FORMAT A25;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB MOUNTED
OR
column pdb_name FORMAT A25;
SQL>SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
PDB_NAME STATUS
————————- ————-
NEWPDB NEW
PDB$SEED NORMAL
// Above result shows that, newly created pluggable database is in mount state,
Pluggable databases automatically created in mount state, Issue the following query to open pluggable database:
SQL> alter pluggable database newpdb open;
Pluggable database altered.
// Ensure the changes by following SQL query, Now newly created pluggable database is in READ WRITE (open) state.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB READ WRITE
Connect your pluggable database with following:
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> alter session set container=newpdb;
Session altered.
SQL> show con_name
CON_NAME
——————————
NEWPDB
You are now connected to your newly created PDB.
The new architecture provided by Oracle Database 12c enables an Oracle database to function as a
multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.
A multitenant container database (CDB) is formed by the following components:
- the root container (and exactly one root) named CDB$ROOT that stores Oracle-supplied metadata and common users
(a database user known in every container);
- the seed template (and exactly one seed) named PDB$SEED used, if you want, to create new PDBs.
It's not possible to add objects to or modify objects in the seed: it works only in READ ONLY mode;
- pluggable databases (zero, one, or many PDBs) named as you prefer that are your "old idea of databases"
before Oracle Database 12c. A pluggable database contains the data and code required by your software
application and is fully backward compatible with Oracle Database releases before Oracle Database 12c.
The options for creating a pluggable database (the so called PDB) fall into two main categories:
copying and plugging in as you will see in this post and in the next posts.
Talking about copying, you have two options to copy a pluggable database:
create a pluggable database (PDB) in a multitenant container database (CDB) using the files of the seed (PDB$SEED);
create it by cloning a source PDB and plugging the clone into the CDB (bear in mind that the source PDB can be in the local CDB or in a remote CDB).
Today I'm going to describe how to copy a pluggable database using the files of the seed from the SQL*Plus command line.
If you want to see how to create a pluggable database copying the files of the seed using DBCA you can simply watch the following video (I will create a post about it using screenshot as soon as possible).
To create a new pluggable database copying the files of the seed you have to ensure that the current container is the root:
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
your multitenant container database CDB must be also in read/write mode. My CDB is called CDB001:
SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;
NAME CDB CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB001 YES 0 READ WRITE
Your script must include the CREATE PLUGGABLE DATABASE statement and eventually some other clauses, such as the following I used in my script:
- STORAGE: specify the limit of the amount of storage the PDB can use. Omitting this clause is equivalent to specify an unlimited amount;
- DEFAULT TABLESPACE: specify a default permanent tablespace for non-SYSTEM users. When you omit this clause the SYSTEM tablespace will be used as default permanent tablespace for non-SYSTEM users and this is not recommended;
- FILE_NAME_CONVERT: specify the target locations of the data files whereas the source files are those associated with the seed.
This parameter is required when Oracle Managed Files is not enabled and the PDB_FILE_NAME_CONVERT initialization parameter is not set
- other clauses you can use are: ROLES, TEMPFILE REUSE and PATH_PREFIX.
SQL> CREATE PLUGGABLE DATABASE PDB002
2 ADMIN USER PDB002_ADMIN IDENTIFIED BY oracle
3 storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
4 DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
5 file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf');
Pluggable database created.
From the alert log (vi /app/oracle/diag/rdbms/cdb001/CDB001/trace/alert_CDB001.log):
Sat Jul 20 16:21:48 2013
CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
Sat Jul 20 16:22:35 2013
****************************************************************
Pluggable Database PDB002 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 pdb002 at open
CREATE TABLESPACE USERS DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE USERS DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO
Sat Jul 20 16:22:49 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB002 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
As you can see when you create a pluggable database from the SQL*Plus command line it is open in MOUNTED mode.
This is a different behaviour compared with the same operation performed by DBCA: in the final step DBCA is able to open the new pluggable database in READ WRITE mode.
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB001 READ WRITE 3
PDB002 MOUNTED 4
To open your new pluggable database in READ WRITE mode execute the following alter pluggable database command:
SQL> alter pluggable database pdb002 open read write;
Pluggable database altered.
Now query again the V$PDBS view: your pluggable database is now available to the application.
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB001 READ WRITE 3
PDB002 READ WRITE 4
Create Pluggable Database Manually using SQL Prompt
we will have a look & hands-on on creating a PDB manually with the help of SQL Prompt.
We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter,
In following example i am going to create new Pluggable Database, name: ‘newpdb’
[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:
SQL> select global_name from global_name;
GLOBAL_NAME
——————————————————————————–
ORCL
// Create new pluggable database by following set of SQL queries:
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/pdbseed/’,’/u01/app/oracle/oradata/newpdb/’;
Session altered.
SQL> CREATE PLUGGABLE DATABASE newpdb ADMIN USER pdbadmin IDENTIFIED BY Admin123;
Pluggable database created.
// Pluggable database has been created successfully, ensure changes by issuing the following query:
column pdb_name FORMAT A25;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB MOUNTED
OR
column pdb_name FORMAT A25;
SQL>SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
PDB_NAME STATUS
————————- ————-
NEWPDB NEW
PDB$SEED NORMAL
// Above result shows that, newly created pluggable database is in mount state,
Pluggable databases automatically created in mount state, Issue the following query to open pluggable database:
SQL> alter pluggable database newpdb open;
Pluggable database altered.
// Ensure the changes by following SQL query, Now newly created pluggable database is in READ WRITE (open) state.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB READ WRITE
Connect your pluggable database with following:
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> alter session set container=newpdb;
Session altered.
SQL> show con_name
CON_NAME
——————————
NEWPDB
You are now connected to your newly created PDB.
No comments:
Post a Comment