Database 12c: Get back from PDB to Non-CDB


I’vh written many articles about converting a Non-CDB to a PDB. I’m going to check the way back to a Non-CDB now.

First check what a convert to PDB is. The plug-in process of a Non-CDB consists of two parts:

  1. (PHYSICAL) Migration of the necessary datafiles
  2. (LOGICAL) Conversion of the data dictionary with the noncdb_to_pdb.sql script

How it works?

“The non-CDB’s datafiles, together with the manually created manifest, can now be treated as
if they were an ordinarily unplugged PDB and simply plugged in to the target CDB and
opened as a PDB. However, as will be explained immediately, it must be opened with the
Restricted status set to YES. The tablespaces holding quota-consuming data are immediately
viable, just as if this had been a Data Pump import using transportable tablespaces. However,
the former non-CDB’s data dictionary so far has a full description of the Oracle system. This
is now superfluous, and so it must simply be removed. While still with the Restricted status set
to YES, the noncdb_to_pdb.sql script (found on the admin_directory under Oracle Home) must be
run. Now the PDB may be closed and then opened ordinarily.” –> Found here

The seconds step make all physical revert step impossible, because there is datafile 1 or real SYSTEM tablespace any more. These steps are:

  1. RMAN Full or Point-in-time-recovery will restore the whole CDB and not only the PDB. Further this is no PDB to Non-CDB conversion
  2. Transportable Database is not usable because datafile 1 is not the real SYSTEM tablespace

All in all the root problem is, that no datafile 1 or independent SYSTEM tablespace exists any more.

And now the ways to get back:

  1. Transportable Tablespace (this is the smoothest way)
  2. DataPump full export/import
  3. Other logical migration ways like Oracle GoldenGate

I’vh tested old export tool (exp) too, but exporting a PDB will end with the error “EXP-00062: invalid source statements for an object type”

If there are more ways I will appreciate a comment 🙂

Database 12c: Clone PDBs (offline and online)


In 12c it is possible to clone PDB’s. Here an example:

1. Bring source PDB in correct state:

SQL> select con_id,name,open_mode,restricted from v$pdbs;

    CON_ID NAME                           OPEN_MODE  RES GUID
---------- ------------------------------ ---------- --- --------------------------------
         2 PDB$SEED                       READ ONLY  NO  E1E189D14D2C2049E0430100007FAB5B
         3 PDB1                           READ WRITE NO  E1F01A2ED12F718AE0430100007F25BA

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL>

2. Clone PDB

SQL> create pluggable database pdb2 from pdb1;

Pluggable database created.

    CON_ID NAME                 OPEN_MODE  RES GUID
---------- -------------------- ---------- --- --------------------------------
         2 PDB$SEED             READ ONLY  NO  E1E189D14D2C2049E0430100007FAB5B
         3 PDB1                 READ ONLY  NO  E1EE338E12A16C3EE0430100007F1A4F
         4 PDB2                 MOUNTED        E1F01A2ED12F718AE0430100007F25BA

SQL> select con_id,name from v$datafile order by 1;
    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /opt/oracle/app/oradata/CDB12C1/system01.dbf
         1 /opt/oracle/app/oradata/CDB12C1/sysaux01.dbf
         1 /opt/oracle/app/oradata/CDB12C1/undotbs01.dbf
         1 /opt/oracle/app/oradata/CDB12C1/users01.dbf
         2 /opt/oracle/app/oradata/CDB12C1/pdbseed/sysaux01.dbf
         2 /opt/oracle/app/oradata/CDB12C1/pdbseed/system01.dbf
         3 /opt/oracle/app/oradata/CDB12C1/E1EE338E12A16C3EE0430100007F1A4F/datafile/o1_mf_system_8ynlfvd5_.dbf
         3 /opt/oracle/app/oradata/CDB12C1/E1EE338E12A16C3EE0430100007F1A4F/datafile/o1_mf_sysaux_8ynlfx2p_.dbf
         4 /opt/oracle/app/oradata/CDB12C1/E1F01A2ED12F718AE0430100007F25BA/datafile/o1_mf_sysaux_8yntf0xb_.dbf
         4 /opt/oracle/app/oradata/CDB12C1/E1F01A2ED12F718AE0430100007F25BA/datafile/o1_mf_system_8yntdzmf_.dbf

3. Bring both PDB’s in correct state

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>

As you can see you have to bring a PDB in READ ONLY mode to clone it. I think this is not really suitable for production environments.

After some bainstorming I found a way to clone PDB’s online. All you need is some storage. Here the plan to clone PDBs online:

  1. Duplicate target PDB to a auxiliary PDB
    –> Bring the PDB to an non production environment
  2. Create a clone PDB from the auxiliary PDB
    –> The GUID of the PDB must change to replug in old CDB. I don’t found another way to change the GUID right now.
  3. Unplug an plug the clone PDB in the target CDB
    –> Transport metadata back to target
  4. Cleanup the shadow CDB
    –> Cleanup all

I’vh read that the real online cloning functionality will come in the next release.

Database 12c: Pluggable database autostart like HAS


Today I discussed with a college the problem of a simple autostart trigger like I posted here.

After some brainstorming I’vh written a new example that acts like oracle HAS (High Availability Service). The pluggable database should have the same state as after a reboot sequence.

Here the coding example:

First create a table in the CDB:

create table pdbs_autostart (
 con_id      number,
 dbid        number,
 name        varchar2(30),
 open_mode   varchar2(10),
 restricted  varchar2(3),
 locked      varchar2(3),
 error       varchar2(200)
);

Further create a startup and shutdown trigger:
Startup trigger:

CREATE OR REPLACE TRIGGER start_pdbs
   AFTER STARTUP ON DATABASE
DECLARE
  opencmd varchar2(100);
  v_errm  varchar2(200);
BEGIN
  for db in (select * from pdbs_autostart) loop
    if db.locked != 'YES' and db.name !='PDB$SEED' and db.open_mode != 'MOUNTED' then
      opencmd := 'alter pluggable database '||db.name||' OPEN '||db.open_mode;
      if db.restricted = 'YES' then
        opencmd := opencmd||' RESTRICTED';
      end if;

      BEGIN
        execute immediate opencmd;
        update pdbs_autostart set error='NO ERROR' where name = db.name;
      EXCEPTION WHEN OTHERS THEN
        v_errm := SUBSTR(SQLERRM, 1 , 200);
        update pdbs_autostart set error=v_errm where name = db.name;
      END;
    else
      update pdbs_autostart set error='Skipped' where name = db.name;
    end if;
  end loop;
  commit;
END;
/

Shutdown tigger:

CREATE OR REPLACE TRIGGER shutdown_pdbs
  BEFORE SHUTDOWN ON DATABASE
BEGIN 
  merge into  pdbs_autostart a
  using       v$pdbs         b
  on          (a.dbid = b.dbid)
  when matched then
    update set a.con_id = b.con_id,a.name = b.name,a.open_mode=b.open_mode,a.restricted=nvl(b.restricted,'NO'),error='' where a.locked = 'NO'
    delete where b.dbid not in (select dbid from v$pdbs )
  when not matched then
    insert values (b.con_id,b.dbid,b.name,b.open_mode,b.restricted,'NO','')
  ;  
  commit;
END; 
/

At every shutdown the state of the database will be written into the table pdbs_autostart. After startup of database the trigger starts all database as saved in table.

I’vh also added an LOCKED column to prevent state change at shutdown. This is useful if you want to permanent save your configuration. Alternativ you can disable the shutdown trigger.

If an error occurs during startup, it will be written in the error column.

Here the construction in action:

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           MOUNTED
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

Check the actual state, then start PDB3 and PDB5.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> alter pluggable database pdb5 open;

Pluggable database altered.

Check configuration. PDB3 state should be changed at shutdown and should startup at next restart. PDB5 ist LOCKED and should not be overruled at next startup. It will not be started at next time.

SQL> select * from pdbs_autostart;

    CON_ID       DBID NAME                           OPEN_MODE  RES LOCKED ERROR
---------- ---------- ------------------------------ ---------- --- ------ -----------------------------------------------------------------
         3 3328312323 PDB3                           MOUNTED    NO  NO  
         2 4062846046 PDB$SEED                       READ ONLY  NO  NO  
         4 3887199318 PDB2                           READ ONLY  NO  NO  
         7 3881397024 PDB5                           MOUNTED        YES 
         6 3831633646 PDB4                           READ WRITE NO  NO  
         5 3328633065 PDB1                           READ WRITE NO  NO  

6 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1970864128 bytes
Fixed Size                  2289928 bytes
Variable Size             587206392 bytes
Database Buffers         1375731712 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           READ WRITE
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

SQL>

It works! Have fun!

Database 12c: Plug and Unplug PDB


To unplug pdb the pdb must be close. So you can close the original pdb or clone and unplug the database. The basic unplug and plug mechanism I’m going to describe in this article:

1. Prepare to unplug

[bash]$ mkdir /opt/oracle/unplug
[bash]$ set ORACLE_SID=CDB12C1
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

2. Now unplug database

SQL> alter pluggable database pdb1 unplug into '/opt/oracle/unplug/pdb1.xml';

Pluggable database altered.

Take a look into the new generated XML file

cat /opt/oracle/unplug/pdb1.xml 
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
 <pdbname>PDB1</pdbname>
 <cid>3</cid>
 <byteorder>1</byteorder>
 <vsn>202375168</vsn>
 <dbid>3328312323</dbid>
 <cdbid>4018805978</cdbid>
 <guid>E157C0F3C35907C7E0430100007F6C6A</guid>
 <uscnbas>2082581</uscnbas>
 <uscnwrp>0</uscnwrp>
 <rdba>4194824</rdba>
 <tablespace>
 <name>SYSTEM</name>
 <type>0</type>
 <tsn>0</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf</path>
 <afn>51</afn>
 <rfn>1</rfn>
 <createscnbas>2077841</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>33280</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>SYSAUX</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0vf56k_.dbf</path>
 <afn>52</afn>
 <rfn>4</rfn>
 <createscnbas>2077844</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>81920</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>3328312323</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>2082579</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>820192925</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>TEMP</name>
 <type>1</type>
 <tsn>2</tsn>
 <status>1</status>
 <issft>0</issft>
 <bmunitsize>128</bmunitsize>
 <file>
 <path>/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_temp_8y0vf96l_.dbf</path>
 <afn>3</afn>
 <rfn>1</rfn>
 <createscnbas>2077842</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>0</status>
 <fileblocks>12800</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <autoext>1</autoext>
 <maxsize>262144</maxsize>
 <incsize>6400</incsize>
 </file>
 </tablespace>
 <optional>
 <csid>873</csid>
 <ncsid>2000</ncsid>
 <options>
 <option>APS=12.1.0.1.0</option>
 <option>CATALOG=12.1.0.1.0</option>
 <option>CATJAVA=12.1.0.1.0</option>
 <option>CATPROC=12.1.0.1.0</option>
 <option>CONTEXT=12.1.0.1.0</option>
 <option>DV=12.1.0.1.0</option>
 <option>JAVAVM=12.1.0.1.0</option>
 <option>OLS=12.1.0.1.0</option>
 <option>ORDIM=12.1.0.1.0</option>
 <option>OWM=12.1.0.1.0</option>
 <option>SDO=12.1.0.1.0</option>
 <option>XDB=12.1.0.1.0</option>
 <option>XML=12.1.0.1.0</option>
 <option>XOQ=12.1.0.1.0</option>
 </options>
 <olsoid>0</olsoid>
 <dv>0</dv>
 <ncdb2pdb>0</ncdb2pdb>
 <APEX>4.2.0.00.27:1</APEX>
 <parameters>
 <parameter>processes=300</parameter>
 <parameter>sga_target=1979711488</parameter>
 <parameter>db_block_size=8192</parameter>
 <parameter>compatible=12.1.0.0.0</parameter>
 <parameter>open_cursors=300</parameter>
 <parameter>pga_aggregate_target=655360000</parameter>
 <parameter>enable_pluggable_database=TRUE</parameter>
 </parameters>
 <tzvers>
 <tzver>primary version:18</tzver>
 <tzver>secondary version:0</tzver>
 </tzvers>
 <walletkey>0</walletkey>
 </optional>
</PDB>

As you can see is the unplug an plug process only an XML? No normally you should clone the pdb or restore the datafiles to an alternativ host and/or location. In my example I’m going to leave the datafiles on the same location.

3. Check state of PDB in source CDB

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520
         3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB1                           MOUNTED        12-JUL-13 11.58.34.430 PM                                                      2077841          0

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

SQL> drop pluggable database pdb1;

Pluggable database dropped.

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
         2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED                       READ ONLY  NO  12-JUL-13 11.14.26.658 PM                                                      1720729  283115520

4. Now plug-in the PDB in another CDB

[bash]$ set ORACLE_SID=CDB12C2
[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 23:58:10 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If the database plug-in has been done all the Datafiles will be copied:

SQL> select con_id,name from v$datafile order by 1;

 CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vsncx_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vr78k_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_undotbs1_8y0vv2ls_.dbf
 1 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_users_8y0vv1ht_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_system_8y0vvx50_.dbf
 2 /opt/oracle/app/oradata/CDB12C2/datafile/o1_mf_sysaux_8y0vvx4q_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0zcmfn_.dbf
 3 /opt/oracle/app/oradata/CDB12C2/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_sysaux_8y0zcmfc_.dbf

8 rows selected.

To compare the datafile of the SYSTEM tablespace from source:

/opt/oracle/app/oradata/CDB12C/E157C0F3C35907C7E0430100007F6C6A/datafile/o1_mf_system_8y0vf3kn_.dbf

Notice: The GUID of the PDB will be the same in the new CDB!

Side effect, replug

An interesting side effect is, that you are able to replug the database after it has been dropped:

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> drop pluggable database pdb2;

Pluggable database dropped.

SQL> create pluggable database pdb2 using '/opt/oracle/unplug/pdb1.xml';

Pluggable database created.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from v$pdbs;

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
 2 4063213762 4063213762 E157DF67E3A80D46E0430100007F0AFD PDB$SEED READ ONLY NO 12-JUL-13 11.12.26.653 PM 1720729 283115520
 3 3328312323 3328312323 E157C0F3C35907C7E0430100007F6C6A PDB2 READ WRITE NO 13-JUL-13 12.04.45.481 AM 2083522 283115520

SQL>

If you are unintentionally try to plugin the same PDB again an error will be raised:

SQL> create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml';
create pluggable database pdb3 using '/opt/oracle/unplug/pdb1.xml'
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

Database 12c: Flashback technology


Today I’vh have been asked what flashback technologies are possbile in 12c PDB:

Here the overview:

  • Flashback query/versions query
    • is UNDO based and work in PDB’s
  • Flashback transaction query/flashback transaction
    • is UNDO and REDO based and work in PDB’s
  • Flashback data archive
    • UNDO gernerated archives
  • Flashback table
    • before drop –> Rename segment name of recylebin, which is based on UNDO, work in PDB’s
    • flashback table to –> is UNDO based, work in PDB’s
  • Flashback database
    • doesn’t work in PDB’s:
RMAN> flashback pluggable database pdb1 to time "to_date('23:15 12-07-2013','hh24:mi dd-mm-yyyy')";

Starting flashback at 12-JUL-13
using channel ORA_DISK_1
using channel ORA_DISK_2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 07/12/2013 23:20:45
RMAN-05108: Command is not supported for pluggable database

RMAN>

The RMAN command already exists, so there will be a plan to support flashback pluggable databases also. To do a point in time recovery, the “traditional” way via restore/recovery should be used. See here

Database 12c: Pluggable Database autostart


I’vh searched for several hours, but there is no out of the box autostart mechnism for pluggable databases. Here a simple solution with a trigger:

SQL> startup
ORACLE instance started.

Total System Global Area 1970864128 bytes
Fixed Size                  2289928 bytes
Variable Size             587206392 bytes
Database Buffers         1375731712 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.

SQL> set pages 32000 lines 210
SQL> select con_id,name,guid,open_mode from v$pdbs order by 1;

    CON_ID NAME                           GUID                             OPEN_MODE
---------- ------------------------------ -------------------------------- ----------
         2 PDB$SEED                       E0F388EB55323E35E0430100007FF91C READ ONLY
         3 PDB1                           E1433B62351A0F0DE0430100007F3E51 MOUNTED

SQL>

Add trigger after Database start to start all PDB’s:

SQL> 
CREATE OR REPLACE TRIGGER start_pdbs
   AFTER STARTUP ON DATABASE
BEGIN
   execute immediate 'alter pluggable database all open';
END;
  6  /

Trigger created.

SQL>

Test the autostart now.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1970864128 bytes
Fixed Size                  2289928 bytes
Variable Size             587206392 bytes
Database Buffers         1375731712 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SQL> select con_id,name,guid,open_mode from v$pdbs order by 1;

    CON_ID NAME                           GUID                             OPEN_MODE
---------- ------------------------------ -------------------------------- ----------
         2 PDB$SEED                       E0F388EB55323E35E0430100007FF91C READ ONLY
         3 PDB1                           E1433B62351A0F0DE0430100007F3E51 READ WRITE

See the advanced method also