Post Redgate Webinar on Database 12c Pluggable Databases

Thanks to everyone that tuned in for Redgate’s All things Oracle Webinar I did on Database 12c today.  I know we went way over on time and appreciate everyone staying on for the extra time as we went through and demo’d how many ways we can create, copy and clone DB12c pluggable databases.

As promised, here’s the demo playbook and the links to the Virtualbox image with the DB12c installation files on it.  You will need to do the following to set everything up:

1.  Set the host name in the /etc/hosts file, (in case you want to rename your host, this has not been set…)

2.  Install DB12c on the box from the installation files located on the /u01 volume.

3.  Refer to my blog post on copying the RPM file needed for flash player if you want EM Express to work in Firefox.

You can download the Virtualbox OVA file from here.  This will be downloaded from my dropbox public folder and will function after simply importing into a Virtualbox installation or other compatible VM Player.

For those that can’t use the original link to download the file, here’s a link to try downloading it from Trend Micro-  It was a slow upload, so I can’t guarantee any kind of speed on the download… 🙁  To download the VM image from Trend Micro, click here.

The passwords for Oracle/Root on this image are set to “oracle”

The slides for the presentation can be found here.

This is the playbook of what I did in the demonstration today-

——————————————————————-

1. Log into the DB12c Container Database with SYSDBA privs.

Create a standard, pluggable database, (PDB) with an admin user, so the PDB has an admin to manage it’s needs at that level, without requiring access or privileges at the CDB, (Container Database) level.

create pluggable database tstkp12c3
admin user app_Admin identified by t3st1ng;

alter pluggable database tstkp12c3 open;

2. We will then use this same database, unplug it into an XML formatted file:

alter pluggable database pgtst12c2 close;

alter pluggable database pgtst12c2 unplug into '/home/oracle/unplug/pgtst12c2.xml';

3.  Check to see exists for PDB’s by checking the V$PDBS view:

select * from v$pdbs;

4.  Let’s create a copy of the pgtst12c2 database, using the xml file we just created:

create pluggable database pgtst12c4 using '/home/oracle/unplug/pgtst12c2.xml'
copy;

alter pluggable database pgtst12c4 open;

5.  We then will create a clone, using the same XML file:

create pluggable database pgtst12c5 as clone
using
'/home/oracle/unplug/pgtst12c2.xml'
copy;

If you notice in all of my scripts, there is no file name convert.  This is due to my container configuration, which is using OMF, (Oracle Managed Files).  Oracle is actually creating, setting names, directories, etc…

And we always need to remember to alter our PDB and open it post the clone/copy/create-

alter pluggable database <PDB_NAME> open;

———————————————————-
So, what happened to the unplugged PDB that we used to create our XML file with?

select con_id, name, open_mode from v$pdbs;

We will note that the pgtst12c2 pluggable database shows as read only and can not be opened.  We have a choice.  We can plug it in or we can drop it.  In this playbook, we are going to drop it, but we’ll keep the datafiles around, as we are going to recreate it all…

alter pluggable database pgtst12c2 close;

drop pluggable database pgtst12c2 keep datafiles;

Recreate it from the XML:

create pluggable database pgtst12c2 using '/home/oracle/unplug/pgtst12c2.xml'
copy
tempfile reuse;

alter pluggable database pgtst12c2 open;

now query:

select name, con_id, open_mode from v$pdbs;

Now the PDB, pgtst12c2 is open and happy.  We’ve recreated it that easily….

For the final demo, we cloned a PDB in a way that was dependent upon a parameter change at the CDB level.
We’ll clone one existing PDB to a second one.

1. Make the PDB read-only:

alter pluggable database pgtst12c2 close immediate;
alter pluggable database pgtst12c2 open read only;

2. Creat a directory to hold the new clone structure:

cd /u01/app/oracle/oradata/TSTKP12C
mkdir cltst12c
cd cltst12c

3. We need to adjust our source db’s parameter for the db_create_file_dest to the directory we’ve created to now house the new clone:

show parameter db_create_file

4. Current settings at the CDB level. Remember, you’ll need to set this back, after you have completed the cloning task!!

SQL> show parameter db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata

Update the parameter to reflect the directory path you just created to hold your new database files for your clone:

alter system set db_create_file_dest='/u01/app/oracle/oradata/TSTKP12C/cltst12c';

Now you’ll see that the parameter is set and ready to be used by the read_only database to create the clone.

show parameter db_create_file

NAME TYPE VALUE
------------------------------------ -----------
db_create_file_dest string /home/oracle/oradata/cltst12c

Time to create the clone with one simple command, creating a new PDB from an existing PDB in read only mode:

create pluggable database cltst12c from pgtst12c2;
alter pluggable database cltst12c open;

——————————————————

Ready to clean up from all your playing with PDB’s today?
To drop a PDB, then:

alter pluggable database cltst12c close;

drop pluggable database cltst12c including datafiles;

^–This of course, will remove all your datafiles, so if you want to retain your PDB, unplug it first to an XML file!!

I will be answering questions as I have time on the “All Things Oracle” site and hopefully this data, the slides and the VM image will help everyone out there become familiar with Oracle Database 12c.

Thank you again for all who attended and thank you to All Things Oracle for allowing me to present!