Plugging the Unplugged Databases in 12c.

There are three ways to plugging the unplugged database.

1. NOCOPY method.
2. COPY method.
3. AS CLONE MOVE method.

I'll show you all the three ways to plugging the unplugged database.

Also I'll create some tables in Pluggable database before unplug and then will plug that unplugged database into container database and will cross check the table records over those plugged databases.

SQL> select CON_ID,NAME,OPEN_MODE from v$pdbs;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 PDBORCL              READ WRITE
         4 PDBTEST              READ WRITE

Lets create two more pluggable database.

SQL> !mkdir -p /u01/app/oracle/oradata/cdb12c/db1/ /u01/app/oracle/oradata/cdb12c/db2/

SQL> create pluggable database db1

  2  admin user testadmin identified by oracle

  3  roles=(dba)

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdbseed/','/u01/app/oracle/oradata/cdb12c/db1/');

Pluggable database created.

SQL> create pluggable database db2

  2  admin user testadmin identified by oracle

  3  roles=(dba)

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdbseed/','/u01/app/oracle/oradata/cdb12c/db2/');

Pluggable database created.

Make an entry for Pluggable Databases DB1 and DB2 into Tnsnames.ora and Listener.ora files. For the same follow my previous post under 12c category. 

 

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

DB1                            MOUNTED

PDBTEST                        READ WRITE

DB2                            MOUNTED

PDBORCL                        READ WRITE

SQL> alter pluggable database db1 open;

Pluggable database altered.

SQL> alter pluggable database db2 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

DB1                            READ WRITE

PDBTEST                        READ WRITE

DB2                            READ WRITE

PDBORCL                        READ WRITE

Lets create Test tables into Pluggable databases which will be unplug.

SQL> conn sys/Oracle12@pdborcl as sysdba

Connected.

SQL> conn scott/tiger@pdborcl

Connected.

SQL> create table test

  2  as

  3  select * from emp;

Table created.

SQL> begin

  2  for i in 1..10 loop

  3  insert into test

  4  select * from emp;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)

----------

       154

Create the tablespace 'USERS' for user scott into Pluggable Databse DB1 and DB2.

SQL> create tablespace users

  2  datafile '/u01/app/oracle/oradata/cdb12c/db1/users01.dbf'

  3  size 100M;

Tablespace created.

SQL> create user scott identified by tiger

  2  default tablespace users

  3  temporary tablespace temp;

User created.

SQL> create tablespace users

  2  datafile '/u01/app/oracle/oradata/cdb12c/db2/users01.dbf'

  3  size 100M;

Tablespace created.

SQL> create user scott identified by tiger

  2  default tablespace users;

User created.

Grant the 'DBA' privilege to user 'scott' .

SQL> grant dba to scott;

Grant succeeded.

Now create the test table.

SQL> conn scott/tiger@db1

Connected.

SQL> create table test

  2  (

  3  id number(3),

  4  name varchar2(15)

  5  );

Table created.

SQL> insert into test

  2  values(1,'rohit');

1 row created.

SQL> insert into test

  2  values(2,'dba');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME

---------- ---------------

         1 rohit

         2 dba

SQL> conn scott/tiger@db2

Connected.

SQL> create table test1

  2  (

  3  id number(3),

  4  name varchar2(10)

  5  );

Table created.

SQL> insert into test1

  2  values(1,'test');

1 row created.

SQL> insert into test1

  2  values(2,'orcale');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID NAME

---------- ----------

         1 test

         2 orcale

Lets Unplug the Pluggable database PDBORCL, DB1 and DB2.

SQL> conn /as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter pluggable database pdborcl close immediate;

Pluggable database altered.

SQL> alter pluggable database pdborcl unplug into '/u01/app/oracle/oradata/cdb12c/pdborcl/pdborcl.xml';

Pluggable database altered.

SQL> drop pluggable database pdborcl keep datafiles;

Pluggable database dropped.

SQL> alter pluggable database db1 close immediate;

Pluggable database altered.

SQL> alter pluggable database db2 close immediate;

Pluggable database altered.

SQL>alter pluggable database db1 unplug into '/u01/app/oracle/oradata/cdb12c/db1/db1.xml';

Pluggable database altered.

SQL> alter pluggable database db2 unplug into '/u01/app/oracle/oradata/cdb12c/db2/db2.xml';

Pluggable database altered.

SQL> drop pluggable database db1 keep datafiles;

Pluggable database dropped.

SQL> drop pluggable database db2 keep datafiles;

Pluggable database dropped.

Check the container status.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME   STATUS

---------- -------------

PDB$SEED   NORMAL

PDBTEST    NORMAL

SQL> select NAME,CON_NAME,CON_ID from v$active_services;

NAME                           CON_NAME                           CON_ID

------------------------------ ------------------------------ ----------

pdbtest.oracle.com             PDBTEST                                 4

cdb12cXDB                      CDB$ROOT                                1

cdb12c.oracle.com              CDB$ROOT                                1

SYS$BACKGROUND                 CDB$ROOT                                1

SYS$USERS                      CDB$ROOT                                1

Now we will start Plugging those Unplugged Databases.

Oracle has provide the script to test the compatibility of Unplugged database which you will be going to plug into the Container Database.

We will use that script to check the compatibility of those unplugged databases one by one.

SQL> set serveroutput on

SQL>

SQL> DECLARE

  2     compatible BOOLEAN := FALSE;

  3  BEGIN

  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

  5          pdb_descr_file => '/u01/app/oracle/oradata/cdb12c/pdborcl/pdborcl.xml');

  6     if compatible then

  7        DBMS_OUTPUT.PUT_LINE('Is pluggable pdborcl compatible? YES');

  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable pdborcl compatible? NO');

  9     end if;

 10  END;

 11  /

Is pluggable pdborcl compatible? YES

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     compatible BOOLEAN := FALSE;
  3  BEGIN
  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5          pdb_descr_file => '/u01/app/oracle/oradata/cdb12c/db1/db1.xml');
  6     if compatible then
  7        DBMS_OUTPUT.PUT_LINE('Is pluggable db1 compatible? YES');
  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable db1 compatible? NO');
  9     end if;
 10  END;
 11  /
Is pluggable db1 compatible? YES

PL/SQL procedure successfully completed.

SQL> set serveroutput on

DECLARE
SQL> SQL>   2     compatible BOOLEAN := FALSE;
  3  BEGIN
  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5          pdb_descr_file => '/u01/app/oracle/oradata/cdb12c/db2/db2.xml');
  6     if compatible then
  7        DBMS_OUTPUT.PUT_LINE('Is pluggable db2 compatible? YES');
  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable db2 compatible? NO');
   end if;
  9   10  END;
 11  /
Is pluggable db2 compatible? YES

PL/SQL procedure successfully completed.

So we can see that those Unplugged Databases are compatible to plug in into the Container database.

METHOD 1: NOCOPY 

SQL> create pluggable database db1

  2  using '/u01/app/oracle/oradata/cdb12c/db1/db1.xml'

  3  nocopy

  4  tempfile reuse;

Pluggable database created.

METHOD 2: COPY

SQL> !mkdir -p /u01/app/oracle/oradata/cdb12c/db2_copy/

SQL> create pluggable database db2

  2  using '/u01/app/oracle/oradata/cdb12c/db2/db2.xml'

  3  copy

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/db2/','/u01/app/oracle/oradata/cdb12c/db2_copy/');

Pluggable database created.

METHOD 3: AS CLONE MOVE

SQL> !mkdir -p /u01/app/oracle/oradata/cdb12c/pdborcl_move/

SQL> create pluggable database orcl

  2  using '/u01/app/oracle/oradata/cdb12c/pdborcl/pdborcl.xml'

  3  move

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdborcl','/u01/app/oracle/oradata/cdb12c/pdborcl_move');

create pluggable database orcl

*

ERROR at line 1:

ORA-65122: Pluggable database GUID conflicts with the GUID of an existing

container.

I could follow the below steps as well.

SQL> create pluggable database orcl

  2  as clone using '/u01/app/oracle/oradata/cdb12c/pdborcl/pdborcl.xml'

  3  move

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdborcl','/u01/app/oracle/oradata/cdb12c/pdborcl_move');

But since I'm using the original pluggable database name so "As Clone" would not needed here.

 

SQL> create pluggable database pdborcl

  2  using '/u01/app/oracle/oradata/cdb12c/pdborcl/pdborcl.xml'

  3  move

  4  file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdborcl','/u01/app/oracle/oradata/cdb12c/pdborcl_move');

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

DB1                            MOUNTED

PDBTEST                        READ WRITE

DB2                            MOUNTED

PDBORCL                        MOUNTED

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME   STATUS

---------- -------------

DB2        NEW

PDB$SEED   NORMAL

PDBTEST    NORMAL

DB1        NEW

PDBORCL    NEW

SQL> alter pluggable database pdborcl open;

Pluggable database altered.

SQL> alter pluggable database db1 open;

Pluggable database altered.

SQL> alter pluggable database db2 open;

Pluggable database altered.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME   STATUS

---------- -------------

DB2        NORMAL

PDB$SEED   NORMAL

PDBTEST    NORMAL

DB1        NORMAL

PDBORCL    NORMAL

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

DB1                            READ WRITE

PDBTEST                        READ WRITE

DB2                            READ WRITE

PDBORCL                        READ WRITE

SQL> conn scott/tiger@db1

Connected.

SQL> select * from test;

        ID NAME

---------- ------------------------------

         1 rohit

         2 dba

SQL> conn scott/tiger@db2

Connected.

SQL> select * from test1;

        ID NAME

---------- ------------------------------

         1 test

         2 orcale

SQL> conn scott/tiger@pdborcl

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

       154

Here we seen that the rows which were created before unplugging the databases are intact.  

Thanks to visit here. :)