Cloning Pluggable Database

Well, the first question is what is a pluggable database. In Oracle 12c, Oracle has introduced a multi-tenancy database methodology, and you will see two acronyms all the time “CDB” meaning “Container Database” and PDB is an acronym for “Pluggable Database”. A quick analogy would be to think of a train to understand the difference, you have a train with an engine “CDB” and lets say up to 250 rail cars the “PDB” as working databases. Each car has specific content targeted for specific customers and they are packed and sealed independently specific to the customer. This methodology allows for a better use of resources and controls for large Enterprise setups or to be able to run multiple large enterprise setups within the same architecture. So cloning a database within this type of environment is actually very straight forward.

The Steps:

1. Log into your container database.
2. Close the Pluggable Database you want to clone: alter pluggable database <pdb_name> close immediate;
3. Open the Pluggable Database in read only mode: alter pluggable database <pdb_name> open read only;
4. Create a new directory to place the oracle database files: /u01/newpdb
5. In SQL*Plus: alter system set db_create_file_dest=’/u01/newpdb’;
6. Now its time to clone:
   

   CREATE PLUGGABLE DATABASE newpdb FROM oldpdb
   FILE_NAME_CONVERT=(‘/u01/oldpdb/’,’/u01/newpdb/’)
   PATH_PREFIX = ‘/u01/newpdb’;

7. alter pluggable database newpdb open;

Oracle 12c – Where am I

In the good old days it was relatively easy to figure out what database you were in.  If you do a quick select on v$instance, you are good.

However, in 12c, when we introduce pluggable container databases things get a little more complicated.  When you issue a sqlplus / as sysdba, you will be placed parent container.

Once you are logged in you can change to a pluggable database by issuing:

alter session set container=<my_pluggable_db_name>;

if you issue the command:

show con_id con_name

It will report the container id & container name:

Now that you are in the pluggable database you can use the new view v$pdbs which will report information for the pluggable database that v$instance does for the container database.

If you log into the container database you can select from v$pdbs and it will report all the pluggable databases within the container.

Panda + Community Christmas 2016

Santa, a crazy tree dude, and The Panda in an ugly Christmas Sweater! Always a good time to be had at the Rotary Christmas Party this year.

We just finished up an amazing run of projects over the holiday season.  The one I am most proud of is that we were able to join forces with Great Lakes Farms and get 5000 pounds (17,000) Apples delivered to Christmas Care and packaged up into over 1500 individual packages to go into the hampers.  It took over 30 Rotary folks, family and friends to pull this project off this year.  The feedback from the folks receiving the hampers has been great, as the apples have been missing for the past several years. The Smart Panda is thrilled to make this project a reality again.

This year, another 112 bears were sold in this year’s Bears for Christmas Care campaign, and 64 of them were donated back to Christmas Care and $846.00 was donated back from the proceeds of the the sales. It was a hard sell through the month of November, but December saw an amazing effort put forth and we were able to make a lot of kids smile this year, in just a couple of hours all the effort will have been worth it!

Salvation Army allowed us to work with them on the Kettles again this year.  I personally must have wished 5,000 people a Merry Christmas during my shifts this year.  The Kettles raise a significant sum of money, all of which stays local in our community, and allows many great programs to run throughout the year.

We were also able to start the holiday season off in early November but take a day to Remember. A group of us from Rotary were out collecting donations for Poppies.  All of the proceeds we raised helps go back into the community to support Veterans.  Its great to give back to the Veterans since so much has been sacrificed for us to be able to enjoy all of what we have.

Looking forward to seeing what 2017 will bring! All The Best……

Panda + Community – September 2016

Okay so you might be saying are those Panda’s dancing? Yes, yes they are. This Dancing Panda video by hongmogu on youtube is awesome! So you might be asking why are the Panda’s dancing? Well, that is a really good question, I honestly didn’t think Panda’s could dance, but it turns out they can.

As part of our local hospital here in St Thomas, they are in the process of raising funds to expand and grow to meet all the needs of our local community. Wade and Lisa have been asked to dance with Ian and Jennifer in the open dance number at the Hospital’s Gala Night event.  So in addition to working an insane amount of hours the past 6 weeks, the dance floor has been busy trying to show a Panda with absolutely NO rhythm how to move his feet to music and actually not look like a bumbling idiot doing so.  (Not convinced it is working).

In addition we are busy gearing up for Christmas – yes I know – but literally it is just 2 months away. Considering how fast the months are going now that is about 6 blinks away. Oh wait, 5, I just blinked.  The Panda’s will be assisting Christmas Care this year, as Wade has joined the border of Christmas Care and will be working hard to sell Bears-4-Christmas-Care again this year.  This year The Smart Panda Corporation is going to try and bring back an old tradition that has been missing in the hampers for the past several years – Apples.  Yes Apples!  What Christmas isn’t complete without an apple in your stocking.  There appears to be all sorts of theories behind the giving of apples and/or oranges at Christmas, but for me it just reminds me of Christmas morning opening your stocking.  We are trying to get 5000 pounds of apples that will be packaged up and put in each of the 1500 hampers.

Speaking of Apples, Eat-2-Learn which is a program here in our community that helps ensure that kids from 38 different schools have food to eat so that they can learn at school.  The eat-2-learn program is incredible offering up breakfasts, lunches, and snacks to kids from kindergarten to teenage high-school students.  The best part of the program is it isn’t limited to one kid, it is for the entire school.  This inclusion helps make sure that all kids have the right food to learn. Our Rotary Club here has helped with the program at several schools and we were recognized at the Principal’s breakfast in September where each of the schools got their funding to ramp up the programs for the school year. My kids love the program and I know the kids that are actually hungry love it too.  There is some pretty amazing statistics around the program when it comes to the power of food in regards to learning – It really makes a difference.

Lastly, it may have been stupid warm here today, but soon enough the cold weather will be back upon us, and we are gearing up for another season working the kitchen at Inn-Out-Of-The-Cold serving up some hot delicious food to some of locals that need a little help during those cold months.

Until Next Month…… (I honestly can’t find any pictures —– next month we will have some!)

Kill Application Engine (AE)

It amazes me that in the world of these high end databases that simple data handling can go for a crap simply because data was bulk loaded into the system.  Usually during big changes to an environment: data conversions, upgrades,  archiving or other aggressive changes, data will become out of sync with the statistics that are stored on the tables.

What are Database Statistics? In a nut shell it is data about data or  simply metadata. Oracle statistics is metadata about your data. There are several kinds of statistics in Oracle mainly: Object statistics, System statistics and fixed table statistics.

So if you have bad statistics your Application Engine may not be able to execute code correctly.  It will simply appear to be hung.  I have seen processes run for days and it will never return a result because of bad statistics.  After updating the statistics those exact same processes that ran for days will run in seconds, sometimes faster.  Okay, so now the catch is you have to “kill” the process in order for the process to be re-ran and pick up the correct statistics. This isn’t the easiest process in the world to do, because if you try to just cancel the job through the process scheduler it may or may not cancel nicely. However, if you “kill” the SQL at the database level that is hung the application engine for go to ERROR, and you can restart the AE from the last committed point that it executed to.  This is much cleaner than any other method I have found, especially if you have a huge amount of stuff already done by the application engine prior to running into this issue.  Upgrades are notorious for this, which is why they have “update statististics” steps all throughout the upgrade process now.

So in Oracle SQL we need to find the SQL that is causing the issue:

select * from v$session where program like ‘%PSAESRV%’;  –See SQL running by Application Engine

select * from v$sql where sql_id = ‘SQL_ID from v$Session’;  — Confirm you have the write SQL

alter system kill session ‘{sid},{serial#}’;

It may take a few minutes to find the SQL causing the problems, but don’t worry, it will still be running after you find it.  Once it is killed, the AE will go to error, and you can restart it.  It will start up at the last commit point, which you can usually see if you query the AE Control table:

select * from ps_aeruncontrol;

Rerun your statistics for the schema or for the tables in question.

exec dbms_stats.gather_schema_stats(‘SYSADM’, cascade=>true);

Good luck out there!