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.

24 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.

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

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

JS said...

You have great article.

Do check my site http://techgoeasy.com

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose

Unknown said...

Nothing at all is free these days, but make sure that you Buy facebook reviews only from genuine sources for profitable recognition online. facebook reviews

انجين محمد said...

شركة نقل عفش واثاث بمكة شركة النور الدولية شركة مقاولات عامة
شركة النور الدولية من اشهر الشركات التى تعمل فى مجال نقل الاثاث والعفش والمقاولات بصفة عامة وهذا لانها تمتلك عمالة ماهرة تعمل بجد واتقان فى هذا المجال ويسعدنا ان نقدم لكم المزيد من خدماتنا

شركة نقل عفش بمكة
تتميز شركة نقل عفش بمكة بكونها واحدة من أهم الشركات التي تقوم بتقديم خدمة نقل العفش فنحن نمتلك مجموعة من السيارات المجهزة على أعلى مستوى لنقل العفش من مكان لآخر بدون أي تلف يحدث له وكذلك مجموعة من العمالة المدربة على أعلى مستوى للتعامل مع قطع العفش المختلفة بمنتهى الدقة.
شركة نقل عفش بمكة
أفضل شركة مقاولات بمكة
شركة نقل اثاث بمكة

بيت العز said...

شركة صيانة مكيفات بالرياض
التكيف أصبح جزء لا يتجزأ من روتين حياتنا اليومية وذلك لارتفاع درجة الحرارة الخارجية في الشوارع ففي بلاد الخليج العربي تصل درجة الحرارة إلي معدلات عالية جدا لا يتحملها الإنسان دون التكيف لذلك توفر شركة صيانة مكيفات بالرياض خدمة الصيانة للجهاز مع توفير كافة قطع الغيار الأصلية التي تستخدم في صيانة الجهاز ومن خلال أفضل الأسعار
شركة مقاولات عامة بالرياض
تهتم شركة مقاولات عامة بالرياض بتوفير كافة مواد البناء والمقاولات بأفضل الأسعار الممكنة كما تضمن لك الشركة أعلي مستوي من الخامات التي تستخدم في عملية البناء وذلك بشكل خاص حرصا منها علي سلامة عملائها الكرام كما توفر أيضا مواد التشطيب من دهانات وخلافها من المواد المختلفة التي تستخدم في عملية البناء

شركة تنظيف خزانات بالرياض
شركة عزل فوم بالرياض
شركة عزل اسطح بالرياض
شركة عزل خزانات بالرياض
شركة صيانة مكيفات بالرياض
شركة مقاولات عامة بالرياض


ciitnoida said...

Thanks for sharing the information. It is very useful for my future. keep sharing.

Best Salesforce Training Institute in Noida
Best Salesforce Admin Training Institute in Noida

BroddyAdams said...

Hey! Do you know if they make any plugins to assist with SEO? I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good success. If you know of any please share. Thanks!Import Project Budgets

QuickBooks Payroll Support said...

Nice Blog We are providing technical support in Quickbooks Support Phone Number +1-800-986-4607. if you are Expand your business to a new hike, with progressive approach. Seeking for the best accounting software? Then, get QuickBooks installed in your system. The software proves to be more profitable to the business.

yanmaneee said...

nfl jerseys
curry 4 shoes
jordan shoes
balenciaga sneakers
reebok outlet
nfl store
nfl jerseys
christian louboutin
paul george shoes
moncler jacket

Anonymous said...

I truly appreciate that you've posted an nice article. Much obliged to you for sharing this good post. Likewise visit our website.
123.hp.com || 123.hp.com/setup || 123HP Setup || hp.com/setup || hp.com/123 || 123.hp.com setup || 123 HP Printer Setup || 123 HP Printer Support || 123 HP Setup and Install || 123hpcom || 123 HP Printer Install || 123hpcomsetup || 123 HP Wireless Setup || 123 HP Install || hpcom/123 || 123hpcominstall || 123HP Setup || 123 HP Smart App || Install 123 HP Printer || HP 123 Setup Scanner

The Qb Payroll said...


If you are one among the users of QuickBooks & is located in Oregon, then contact Quickbooks Support Phone Number 1-833-780-0086 for anytime help. For any kind of Problems you are dealing in QuickBooks, get the right consultancy from our Experienced QuickBooks Professionals. For More : https://g.page/qb-support-oregon?gm

The Qb Payroll said...

Become stress-free from any kind of technical problems of the software. Now, you can focus on your work efficiently due to the presence of QuickBooks Support Number 1-833-325-0220. Our dedicated team of experts deployed on the support to give reliable assistance. For More: https://g.page/qb-support-number-hawaii

Mai said...

الجميرة
شركة تنظيف ستائر بدبى
شركة مكافحة الرمة دبى
خدمات دبي

Mai said...

خدمات تلال
شركات تعقيم مكاتب من الكورونا بالعين
شركات تعقيم فنادق ضد فيروس كورونا بالعين