Tuesday, July 30, 2013

How to create a pluggable database by cloning an existing local PDB

Using the CREATE PLUGGABLE DATABASE ... FROM command you can clone an existing pluggable database (the source pdb) to create a new pdb (the clone pdb).
The source pdb could be in the current local container or it can be located in a remote container (in a next post): during a clone of a remote pdb you need to use a database link referencing the remote container in the FROM clause.

Let's start cloning a local pluggable database. Be sure the current container is the root
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
Here are my current pluggable databases and I want to clone PDB001 which contains the user MARCOV and the table T1 (with 100 rows)
SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE

SQL> alter session set container=PDB001;

Session altered.

SQL> create user marcov identified by marcov quota unlimited on users;

User created.

SQL> grant create session to marcov;

Grant succeeded.

SQL> grant create table to marcov;

Grant succeeded.

SQL> connect marcov/marcov@PDB001
Connected.
SQL> show user
USER is "MARCOV"
SQL> show con_name

CON_NAME
------------------------------
PDB001

SQL> create table T1 (a number);

Table created.

SQL> create table T1 (a number);

Table created.

SQL> insert into T1 select level from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
Here is the content of my tnsnames.ora file
CDB001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB001)
    )
  )

PDB001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB001)
    )
  )
To clone the source pluggable database PDB001 I need to open it in READ ONLY mode
SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE

SQL> alter pluggable database PDB001 open read only force;

Pluggable database altered.
On my file system I have the following directories and files.
[oracle@localhost CDB001]$ pwd
/app/oracle/oradata/CDB001
[oracle@localhost CDB001]$ ll
total 2163224
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:12 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PDB001
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PDB002
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:12 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:11 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:06 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:11 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:10 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf
Here is the content of PDB001 directory.
[oracle@localhost CDB001]$ ll PDB001/
total 922516
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:08 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:08 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:08 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 21 17:51 temp01.dbf
I'm going to create a new pluggable database called PDB003, cloning PDB001. The command to clone a pluggable database locally is the following:
SQL> create pluggable database PDB003 from PDB001 file_name_convert=('/app/oracle/oradata/CDB001/PDB001','/app/oracle/oradata/CDB001/PDB003');

Pluggable database created.
New files and directories are created. Note also the same name of the datafile PDB001_users01.dbf used as default tablespace for PDB003.
[oracle@localhost CDB001]$ ll
total 2163228
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:19 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PDB001
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PDB002
drwxr-x---. 2 oracle oinstall      4096 Jul 30 13:17 PDB003
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:18 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:17 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf
[oracle@localhost CDB001]$ ll PDB003
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:18 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:18 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 30 13:17 temp01.dbf
After the command is sucessfully completed the status and the open mode of the new pluggable database are NEW and MOUNTED.
SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDB001    NORMAL
PDB002    NORMAL
PDB003    NEW

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ ONLY
PDB002          READ WRITE
PDB003          MOUNTED
A new service is created too.
SQL> select name, pdb from V$SERVICES order by creation_date;

NAME       PDB
-------------------- ------------------------------
CDB001XDB      CDB$ROOT
SYS$BACKGROUND      CDB$ROOT
CDB001       CDB$ROOT
SYS$USERS      CDB$ROOT
pdb001       PDB001
pdb002       PDB002
pdb003       PDB003
You can now open both pluggable databases with one simply command (have a look at this post for more information about the syntax and examples)
SQL> alter pluggable database PDB001,PDB003 open READ WRITE FORCE;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME       OPEN_MODE
-------------------- ----------
PDB$SEED      READ ONLY
PDB001       READ WRITE
PDB002       READ WRITE
PDB003       READ WRITE

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDB001    NORMAL
PDB002    NORMAL
PDB003    NORMAL
Add the following entry on the tnsnames.ora file.
PDB003 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB003)
    )
  )
Let's see my data on PDB003 and on PDB001
SQL> connect marcov/marcov@PDB003
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB003
SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

SQL> connect marcov/marcov@PDB001
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB001
SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100
It is not possible to create a new pdb cloning a local or a remote seed: to create a new pdb from the seed you have to follow this post. If you try to clone from the seed template you will receive the ORA-65000 error, described below:
SQL> create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004');
create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004')
                          *
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name

[oracle@localhost pdbseed]$ oerr ora 65000
65000, 00000, "missing or invalid pluggable database name"
// *Cause:  A valid pluggable database name was not present where required 
//     by the syntax of a CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE
//          DATABASE or DROP PLUGGABLE DATABASE statement.
// *Action: Reissue the statement with a valid pluggable database name.

That's all.

21 comments:

Anonymous said...

It ǥoes աithout saying that any king of stalking behaѵiour such as "silent calls", usіng your friendѕ as spiеs or insessant texting
is оut of question. The first step to getting
a guy back is to determine where you actually stɑnd with him.
But even if you are wanting to get back with үour ex, you sɦould not simply jump
into thіngs.

Feel free to surf to my web-site :: pull your ex back free download

Anonymous said...

Hello There. I found your blog using msn. This
is a really well written article. I will be sure to bookmark
it and return to read more of your useful info.
Thanks for the post. I will definitely comeback.

my web page ... how to lose 20 pounds in 2 weeks ()

Anonymous said...

Hmm iѕ anyone elѕe encountering problems with the pictures оn tɦis blog loading?
I'm tгying to find outt іf іtѕ a ƿroblem
on my end օr if it's tҺе blog. Any feedback աould be greatly appreciated.



Review mʏ site ... Hay Day Cheats

Anonymous said...

replica louis vuitton handbags online
Wow, this paragraph is nice, my younger sister is analyzing these things,
thus I am going to let know her.

Anonymous said...

Hello, i think that i saw you visited my website thus
i came to “return the favor”.I'm attempting to find things to enhance
my website!I suppose its ok to use some of your ideas!!


My web-site; cheapoair coupon code

Anonymous said...

Thanks for the auspicious writeup. It if truth be told was a amusement account it.
Glance advanced to more introduced agreeable from you!
However, how can we be in contact?

Feel free to visit my homepage ... green coffee fat burner

Anonymous said...

Yesterday, while I was at work, my cousin stole my iPad and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My apple ipad
is now broken and she has 83 views. I know this is totally off topic but I had to share it with someone!


Here is my weblog; เชียงคาน

Anonymous said...

Hello there! I just would like to offer you a huge thumbs up for your excellent information you
have got here on this post. I am returning too yourr site for more soon.

Look at my web blog :: drawing online classes -
learning2Drawportraits.com,

Anonymous said...

I love what you guys tend to be up too. This sort
of clever work and exposure! Keep up the great works guys I've added you guys to my blogroll.



Also visit my site; lasik surgery reviews

Anonymous said...

Today, I went to the beachfront with my kids. I found a sea shell and gave it to my
4 year old daughter and said "You can hear the ocean if you put this to your ear." She put
the shell to her ear and screamed. There was a hermit crab inside
and it pinched her ear. She never wants to go back! LoL I know this is totally off topic
but I had to tell someone!

my blog ... wellness coach certification online

Anonymous said...

If you are free bodybuilding not disclosed. Multiply your lean body bulk includes all
aspects of anabolic steroids or weight. Bodybuilders should also be
incorporated to increase the vascularity.
Pure body and sport are greater as well.
Get an i-pod together with the temptation of the perfect program for athletes and are
resenting those that take care of yourself to miss meals, and thus prevent any muscle tissue.
This supplement increases your fat increase. What are we
going to present you with both programs, one needs to.


Also visit my weblog ... tall bodybuilders

Anonymous said...

Pourquoi se payer des ltc sans trace via réseau internet en 5 minutes

Here is my webpage: acheter litecoin

Anonymous said...

Hello! I just wanted to ask if you ever have any problems with hackers?

My last blog (wordpress) was hacked and I ended up losing several weeks
of hard work due to no data backup. Do you have any
solutions to prevent hackers?

My homepage Tv Video Production Pricing Guide Toronto ON

Anonymous said...

Please let me know if you're looking for a article writer for your site.
You have some really great posts and I believe I would be a good asset.
If you ever want to take some of the load off, I'd absolutely
love to write some material for your blog in exchange for a link back to mine.
Please blast me an email if interested. Cheers!

my homepage :: hair Straightening products

Anonymous said...

The switch to LED lighting can save your money as its maintenance cost is very low.
First, let's take a look at your property and to think about what you need done.
Maintaining hygienic living conditions in your premises is dependent
a lot upon the way in which you manage you landscapes.


Here is my webpage - ideas4landscaping

Anonymous said...

ԝhoah this blog iѕ wondеrful i like studying yоur articlеs.
Stay uƿ the good work! Yoս recognize, many people arе ѕearching around foor this
information, you can help thjem greаtly.

Feel free to surf to my homepage :: thrombosed bleeding hemorrhoids images

Anonymous said...

The proposed regulations might additionally require (desire) more openness together with the government.
Allow it to media and finish "Done" at the very top right
place. It permits you to install 3rd-party computer software.


my web-site - download Redsn0w (http://www.startthis.com)

Anonymous said...

I blog quite often and I seriously thank you for your content.
This article has truly peaked my interest.

I'm going to book mark your blog and keep checking for
new details about once per week. I subscribed to your
RSS feed too.

Feel free to visit my homepage :: eavestrough ottawa

Anonymous said...

A shoulder bag is choose to one pictured above, whilst offering the regarding being eager to be gotten into quickly and easily.
They use this "bumping" diversion to distract you what they best.
However, when using punching bags, one have a need to punch purposely.
Plastic bags raise the pollution to water,
air and score. Most retailers sell the basic reusable bags for roughly $1.


My web site ... コーチ

Anonymous said...

Terrific article! This is the kind of information that are supposed to be
shared across the web. Disgrace on the search engines for no
longer positioning this submit upper! Come on over and
consult with my website . Thank you =)

My site :: paint inside

Anonymous said...

Heya! I just wanteԀ to ask if you ever have anny trouble
with hackers? My last blօg (wordpresѕ) ѡaas hacked andd
I endеd up losing a feww months of hɑrd worҝ due to no bаckup.
Do yoս have ɑny solutions to stop hackers?

Feel free to viѕit my webpage - firm breasts