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.

Oracle Sequences

Sometimes in the middle of the night you will be working on a conversion effort and you will need to populate a field with a sequence of values.  This is easily done using Excel but if you are dynamically loading data you may want to have a more flexible feature.  Oracle sequences have that ability.

Create A Sequence:

CREATE SEQUENCE adjustment_fix START WITH 1001 INCREMENT BY 1 NOCACHE NOCYCLE;

So this sequence will start are 1001 and each additional next value will by greater by 1 — so 1002, 1003, 1004….etc.  I had an oddball situation where I needed an 18 digit number that increased by 10000000. So in my case it was:

CREATE SEQUENCE adjustment_fix START WITH 300000000010000000 INCREMENT BY 10000000 NOCACHE NOCYCLE;

Now that you have the sequence, you need to populate the rows with the sequence.  So I created a temporary table and loaded all the values in the table.  Then I had another field for the sequence number.  I tried to insert the sequence while doing the insert into the staging table of the values I needed staged for the adjustment but it whined and complained.  So I did the insert and then did an update of the rows:

SQL:    UPDATE STAGING_TABLE_4_ADJ SET ADJ_SEQ = adjustment_fix.nextval;

BAM!  Now I have 2200 rows of adjustment data with a unique sequence number on each row so that I can load the staging data tables.

Audit Create Session Logins

I had an interesting security breach the other day where a developer who had sql access to the production environment gave out his access to basically “everybody”. There was a huge panic and at the end of the day, I found a tip from Burleson at dba-oracle.com that showed that you can easily turn on auditing for session creation and it gives a wonder account of the people that are failing to login because of the changed credentials.
First you need to set the audit parameters on and bounce the system if it isn’t.

audit_trail=true
audit_file_dest=’/dest/to/my/audit/logs/’

from SQL*Plus: audit create session whenever not successful;

Now when you query: Select * from dba_audit_trail; you will get a listing of the folks that have failed to create a session because of incorrect credentials.

Retrieve Deleted Rows from Oracle Database 12c

Okay so last night I went to clear a table of historical data except for the last 7 days.  When I ran the command, it deleted the rows and I committed the transaction and then realized that I deleted the last 7 days and left the other 360 days worth that I had wanted to delete.  Greater Than vs Less Than, lesson there is to not write code on the fly when your tired at 2am in the morning.

I have flashback turned on and archive logging, but I found a neat little SQL method to get my missing rows back:

Insert into TABLE_I_DELETED_ROWS_FROM
(SELECT * FROM TABLE_I_DELETED_ROWS_FROM AS OF TIMESTAMP TO_TIMESTAMP('2014-07-06 10:45:10 PM','YYYY-MM-DD HH:MI:SS PM')
WHERE MYDATEFIELD > TO_DATE('2016-06-29','YYYY-MM-DD'))

I executed this at about 10:55 pm, and it put the 10,000 rows I deleted back into my system that were there at between 06-29 and 07-06.

Nice and easy.