Tuesday, August 13, 2013

The first bug on Oracle Database 12c: how to create a PDB by cloning a remote existing pluggable database

In this post I want to describe how to create a PDB by cloning a remote existing pluggable database: at the end of this process you should have a new PDB (in my case PDB103) contained in a target CDB (CDB001) cloned from a source PDB (PDB003) contained in a remote different CDB (CDBTEST).
In my case both source and target databases are located on the same machine.

When I took part at the presentation of the new Oracle Database 12c here in Rome I took some pictures, but I was not ready with my phone when an interesting slide was presented.

While testing the scenario of today I suddenly remembered about that missing pictures: luckily few days later I received from Oracle also the pdf of the event so I'm now able to show you the following screenshot:

















In 2006 I tested the beta release of Oracle Database 11gR1 because at that time my company needed to test new features for its products.
As you can read from the screenshot many test cases were conducted by Oracle and by beta testers, but a bug is always behind the corner when you consider a complex software solution as the Oracle Database and today I'm not able to close my thread with the usual "That's all" comment.

Anyway let's start describing the content of my current containers. My machine has two container databases named CDB001 and CBTEST.
[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:23 ora_smon_CDB001
oracle   16455     1  0 Jul17 ?        00:01:25 ora_smon_CDBTEST
oracle   26501 26466  0 14:08 pts/1    00:00:00 grep smon
On the target database CDB001 there is only one pluggable database (PDB101)
[oracle@vsi08devpom ~]$ env|grep SID
ORACLE_SID=CDB001
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL@CDB001> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB0101          READ WRITE
On the source database CDBTEST there are four pluggable databases (PDBTEST1, PDBTEST2, PDBTEST3 and PDB003)
[oracle@vsi08devpom ~]$ env|grep SID
ORACLE_SID=CDBTEST

SQL@CDBTEST> select NAME, OPEN_MODE FROM V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ WRITE
The PDB003 pluggable database contains few rows into MARCOV.T1 table:
SQL@CDBTEST> alter session set container=PDB003;

Session altered.

SQL@CDBTEST> select count(*) from marcov.T1;

  COUNT(*)
----------
       100
I'm going to create a database link into the CDB001 container database connecting to CDBTEST using the ezconnect method.
SQL@CDB001> create database link CDBTEST_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'vsi08devpom.mydomain.it:1521/CDBTEST';

Database link created.
I'm able to successfully test the new database link.
SQL@CDB001> select * from cdb_pdbs;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  3 PDB0101     532253118  532253118 E18E282148FD10A5E0430100007FC94D NORMAL  1734497   1
  2 PDB$SEED   4063453634 4063453634 E18E1CE36B940C96E0430100007FE168 NORMAL  1720746   1

SQL@CDB001> select * from cdb_pdbs@CDBTEST_AT_VSI08DEVPOM;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8 NORMAL      217   1
  3 PDBTEST1   3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC NORMAL  1547881   1
  4 PDBTEST2   2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671 NORMAL  1548944   1
  5 PDBTEST3   2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7 NORMAL  1550036   1
  6 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  2744910   1
Under the directory of the CDB001 container there are the following files and directories:
[oracle@vsi08devpom CDB001]$ pwd
/opt/app/oracle/oradata/CDB001
[oracle@vsi08devpom CDB001]$ ll
total 2286044
-rw-r----- 1 oracle oinstall   17973248 Aug  5 14:18 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Jul 15 15:52 PDB0101
drwxr-x--- 2 oracle oinstall       4096 Jul 15 15:48 pdbseed
-rw-r----- 1 oracle oinstall   52429312 Aug  5 14:00 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  5 14:18 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  5 02:20 redo03.log
-rw-r----- 1 oracle oinstall 1226842112 Aug  5 14:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  5 14:15 system01.dbf
-rw-r----- 1 oracle oinstall   62922752 Aug  5 13:52 temp01.dbf
-rw-r----- 1 oracle oinstall   94380032 Aug  5 14:15 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  5 14:06 users01.dbf
As usual to clone the remote pluggable database it must be in READ ONLY mode. The current open mode of PDB003 pluggable database is READ WRITE.
SQL@CDBTEST> select pdb_name, status from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDBTEST1   NORMAL
PDBTEST2   NORMAL
PDBTEST3   NORMAL
PDB003    NORMAL

SQL@CDBTEST> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ WRITE
I need to close and open it in READ ONLY mode.
SQL@CDBTEST> alter pluggable database PDB003 close immediate;

Pluggable database altered.

SQL@CDBTEST> alter pluggable database PDB003 open read only;

Pluggable database altered.
From the target container database I can now issue the following command to clone the pluggable database PDB003 contained in the remote database container CDBTEST.
SQL@CDB001> create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
On the alert log the following error is logged:
ORA-17628 signalled during: create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')...
Because the database link can connect to either the root of the remote container database or directly to the remote pluggable database, I decided, after several failed attempts, to create another database link referencing the remote pluggable database:
SQL@CDB001> create database link PDB003_AT_CDBTEST connect to SYSTEM identified by oracle using 'vsi08devpom.recupitalia.it:1521/pdb003';

Database link created.

SQL@CDB001> select * from cdb_pdbs@PDB003_AT_CDBTEST;

no rows selected
But even with the different database link the statement continues to fail:
SQL@CDB001> create pluggable database PDB103 from PDB003@PDB003_AT_CDBTEST file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@PDB003_AT_CDBTEST file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
Let's try using a different method to connect the two database containers and create another database link using the following CDBTEST tns entry:
CDBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.mydomain.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDBTEST)
    )
  )
Creation and testing of the database link using the CDBTEST tns entry.
SQL@CDB001> create database link CDBTEST_TNS_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'CDBTEST';

Database link created.

SQL@CDB001> select * from cdb_pdbs@CDBTEST_TNS_AT_VSI08DEVPOM;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8 NORMAL      217   1
  3 PDBTEST1   3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC NORMAL  1547881   1
  4 PDBTEST2   2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671 NORMAL  1548944   1
  5 PDBTEST3   2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7 NORMAL  1550036   1
  6 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  2744910   1
The statement using the CDBTEST_TNS_AT_VSI08DEVPOM database link still continues to fail:
SQL@CDB001> create pluggable database PDB103 from PDB003@CDBTEST_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@CDBTEST_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
Let's create the latest database link connected to the following tns entry:
PDB003_CDBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.mydomain.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb003)
    )
  )
Creation and testing of the database link using the PDB003_CDBTEST tns entry.
SQL@CDB001> create database link PDB003_TNS_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'PDB003_CDBTEST';

Database link created.

SQL@CDB001> select * from cdb_pdbs@PDB003_TNS_AT_VSI08DEVPOM;

no rows selected
The statement continues to fail even using the latest available database link: PDB003_TNS_AT_VSI08DEVPOM
SQL@CDB001> create pluggable database PDB103 from PDB003@PDB003_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@PDB003_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
The available database links created and used are the following:
SQL@CDB001> select DB_LINK, HOST, OWNER from dba_db_links;

DB_LINK          HOST          OWNER
------------------------------ --------------------------------------------- -----
CDBTEST_AT_VSI08DEVPOM        vsi08devpom.mydomain.it:1521/CDBTEST      SYS
PDB003_AT_CDBTEST        vsi08devpom.mydomain.it:1521/pdb003      SYS
CDBTEST_TNS_AT_VSI08DEVPOM     CDBTEST          SYS
PDB003_TNS_AT_VSI08DEVPOM      PDB003_CDBTEST         SYS
And there is still no way to create a pluggable database to a remote target destination from a local source. You can see on the "SQL Language Reference" which CREATE PLUGGABLE DATABASE syntax is allowed:
SQL@CDBTEST> create pluggable database PDB103@CDB001_AT_VSI08DEVPOM from PDB003;
create pluggable database PDB103@CDB001_AT_VSI08DEVPOM from PDB003;
                                *
ERROR at line 1:
ORA-00922: missing or invalid option
Even tracing the unix Oracle server process with the strace system util I was not able to get useful information to know how successfully complete the statement and why it's failing.

The end of the story: after I have contacted My Oracle Support the Oracle engineer confirmed the issue, tracked as bug 15931910 and it is still being worked upon by the development team.
Other platforms could be affected by this bug but I have no reason and time to test it.

So at the moment there is no way to clone a remote pluggable database using a database link, but as a workaround you can follow this post to manually create it to the new target destination

8 comments:

Anonymous said...

Terrific article! This is the type of information that should be shared across
the internet. Shame on Google for not positioning this publish upper!
Come on over and visit my site . Thanks =)

Have a look at my web site - Fake Louis Vuitton Bags

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

Unknown said...

Create a DB Link from CDB to non-CDB.

Try using the below modified command as SYS user in CDB:

create pluggable database PDB103 from NON$CDB@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003/','/opt/app/oracle/oradata/CDB001/PDB103/');

Changes are use of NON$CDB instead of CDB name and slash (/) at the end of ASM disk groups.

In case of RAC environments you need to define tns entries in tnsnames.ora file of both the RAC servers.

In case the above does not work drop your dblink, and use tnsnames instead of ezconnect.

Hope this helps.

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

yanmaneee said...

kyrie 5 shoes
yeezy boost 350 v2
nike air force
stephen curry shoes
nhl jerseys
adidas ultra
kyrie shoes
yeezy
longchamp handbags
golden goose

jasonbob said...

supreme clothing
lebron 17
off white
air jordan
kd 13
bape clothing
yeezy
stephen curry shoes
golden goose starter
adidsas yeezy