Sunday, July 21, 2013

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
and 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
That's all.

11 comments:

Leonardo Bissoli said...

Hi,

What is the advantage to use cloning database if you must close the source (my production database for example), put in read only, cloning and after that open in read write mode?

That means: my production database will be ONLY available for users during the cloning for READ ONLY?

Example:

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

Then clone the PDB1 to PDB3.

SQL> create pluggable database PDB3 from PDB1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdb1','/u01/app/oracle/oradata/T12/pdb3');

Pluggable database created.

And open both databases read-write.

SQL> alter pluggable database PDB3 open;

Pluggable database altered.

SQL> alter pluggable database PDB1 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

Marco V. said...

As always it depends on the complexity of the system. Cloning a pluggable database from your production database using the create pluggable database could be an option if you can bounce the production instance. I found the process to clone a pluggable database very useful in a development environment of course. Otherwise when you need to clone your database without close it you can use RMAN and DUPLICATE command (using the active database or its backups); or eventually try to configure GoldenGate to align your databases. As always it depends on your system, your SLA and money. Regards, Marco

Anonymous said...

I was able to find good info from your blog articles.


Also visit my website; Aeria Points generator

Anonymous said...

"Outlander" iis the story of Claire Beauchamp Randall.
These phyaical devices bridge the gap to the virtual environments the Morpheys headset creates.
Burn Cards are similar to boosts that are used in other EA titles, such as Masss Effect's multiplayer, and all of EA's sports titles.

WORK TOGETHER BUILD TOGETHER said...

For the lovers of art be it oil paintings, acrylics, water paints, abstracts, still life or just swirls of your imagination on the canvas. Find more art at
http://www.youtube.com/watch?v=PgWYZyShA3c

Anonymous said...

I'm gone to ssay to my little brother, that he should also payy a quick visit this
web site on regular basis to obtain updated from
most recent information.

my blog post - patio blinds

Anonymous said...

I was suggested this web site by my cousin. I am not sure
whether this post is written by him as nobody else know such detailed about my problem.
You are amazing! Thanks!

my webpage - click here ()

Anonymous said...

Do you have a spam problem on this site; I also am
a blogger, and I was wanting to know your situation; many
of us have developed some nice procedures and we are looking to trade solutions with others, be sure to shoot me an email if interested.



Also visit my web page :: Buckinghamshire escorts ()

Anonymous said...

Wow. Interesting point of view. I liked how you covered this subject....
A couple things I dont agree with but hi... thats a different outlook.
I'm super-keen to study your following post.
Can you make the next one more thorough? Thanks :)

Also visit my webpage - cheap tickets to luke bryan - lukebryantickets.hamedanshahr.com,

Anonymous said...

I visited various blogs however the audio feature for
audio songs present at this web site is really marvelous.


Here is my blog post muscle building diet

Anonymous said...

The shot of Cassie laying on the bed was made with a Canon flash
positioned on a light stand to fire into
an umbrella from the photographer's right. The same applies to slips and slip
skirts – there’s not much you can do to improve or modify them, so they haven’t changed all that much
for a hundred years. Wide ranges of designer lingerie collection are available that live up
to the expectation of every fashion savvy woman.

Feel free to surf to my blog post - how to find a reliable sex shop